QUERIES VS STOREDPROCEDURES

I use visual studio 2005 standard edition and sql express 2005.I would like to know if there is any difference using queries(designed in dataset xsd) or stored procedures.

Also I am having the following problem :Whenever I make a change on a table in my database (add columns etc) and use the wizard to retrieve the changes i loose all the queries for that table (I have to write them again).Am i doing something wrong?

Thanks in advance!!!

There is quite a bit of difference between VS queries and SQL Server Stored Procedures.

First, Stored Procedures are actually stored in SQL Server. They don't just 'vanish' -as you are discovering happens to your VS queries. (If you make changes to the tables that cause the stored procedures to fail, they will still be there -just error when executed (until repaired.)

Stored procedures, when called, are compiled and an execution plan is prepared on SQL Server. That execution plan can be reused the next time the stored procedure is called -saving time.

Security can be applied to stored procedures, allowing or denying the 'right' to use them.

Developers don't have to know where the stored procedures are getting their data, and the application doesn't have to be changed when the database changes -just change the stored procedures.

And the list goes on...

|||

Stored procedures are easier to maintain than in-line queries. They are more secure than in-line queries, since they are less vulnerable to SQL Injection attacks. They can also reduce network traffic (incoming to the DB server).

As long as you don't change the interface of a stored procedure (name, parameters, and return values). This will insulate you from the problems you are seeing now when you make table changes.

|||

Hi,

If you seperate the database queries from your application codes by using stored procedures, it will be easier to modify the sql queries for purposes of improving performance, updating queries, etc. Otherwise you should make those changes in your application.

Eralper

http://www.kodyaz.com

|||

Thank you!

I can see now the benefits of stored procedures.

QUERIES VS STOREDPROCEDURES

I use visual studio 2005 standard edition and sql express 2005.I would like to know if there is any difference using queries(designed in dataset xsd) or stored procedures.

Also I am having the following problem :Whenever I make a change on a table in my database (add columns etc) and use the wizard to retrieve the changes i loose all the queries for that table (I have to write them again).Am i doing something wrong?

Thanks in advance!!!

There is quite a bit of difference between VS queries and SQL Server Stored Procedures.

First, Stored Procedures are actually stored in SQL Server. They don't just 'vanish' -as you are discovering happens to your VS queries. (If you make changes to the tables that cause the stored procedures to fail, they will still be there -just error when executed (until repaired.)

Stored procedures, when called, are compiled and an execution plan is prepared on SQL Server. That execution plan can be reused the next time the stored procedure is called -saving time.

Security can be applied to stored procedures, allowing or denying the 'right' to use them.

Developers don't have to know where the stored procedures are getting their data, and the application doesn't have to be changed when the database changes -just change the stored procedures.

And the list goes on...

|||

Stored procedures are easier to maintain than in-line queries. They are more secure than in-line queries, since they are less vulnerable to SQL Injection attacks. They can also reduce network traffic (incoming to the DB server).

As long as you don't change the interface of a stored procedure (name, parameters, and return values). This will insulate you from the problems you are seeing now when you make table changes.

|||

Hi,

If you seperate the database queries from your application codes by using stored procedures, it will be easier to modify the sql queries for purposes of improving performance, updating queries, etc. Otherwise you should make those changes in your application.

Eralper

http://www.kodyaz.com

|||

Thank you!

I can see now the benefits of stored procedures.

Queries returning Multiple instances of the same record

I am running SQL 2005 and have created a simple database for interfacing with Visual Web Dev 2005 and Visual Studio 2005. I noticed that my applications where returning multiple instances of the same records in the queries. I went back to the SQL server and created a query, and sure enough, I'm getting each record returned 3 times. Where do I start to resolve this issue?DId you have the records already duplicated in the database tables ? Or did you just made a mistake in your join, defining the wrong joined keys. THe best thing would be to provide some information like DDL and some sample data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

My DB and query are much simpler than what you are imagining:

The DB Structure is:

<MemberID, Int,> - Primary Key Autoincrement

<FirstName, nvarchar(30),>

<LastName, nvarchar(30),>

<Salutation, nvarchar(20),>

<MemberType, nvarchar(20),>

<IsNeighbor, tinyint,>

<Title, nvarchar(30),>

<Address, nvarchar(60),>

<Address2, nvarchar(60),>

<City, nvarchar(30),>

<State, nvarchar(2),>

<Zip, nvarchar(9),>

<Phone, nvarchar(10),>

<Email, nvarchar(50),>

<DateJoined, datetime,>

<ExpirationDate, datetime,>

<SubMemberTo, int,>

<Fax, nvarchar(10),>

<Cellphone, nvarchar(10),>

The SELECT query is:

SELECT [MemberID]

,[FirstName]

,[LastName]

,[Salutation]

,[MemberType]

,[IsNeighbor]

,[Title]

,[Address]

,[Address2]

,[City]

,[State]

,[Zip]

,[Phone]

,[Email]

,[DateJoined]

,[ExpirationDate]

,[SubMemberTo]

,[Fax]

,[Cellphone]

FROM [FriendsSQL].[dbo].[Members]

WHERE FirstName = 'ANNE' and LastName = 'REIS'

Without the WHERE clause the query returns the entire DB without duplication, however, when the WHERE clause is included the output is:

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

Notice that the single record is returned 3 times.

|||DOH! You were right. The records were duplicated. Apparently using the SET Insert Unique ON and not having the Primary Key set allowed the duplications. I've cleaned up the mess and I'll try not to shoot off any more toes. Sorry for the bother. I should have caught that one.

QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)

I am getting the following message when I attempt to Export data in SQL Management Studio from my workstation. The error occurs as soon as I click Next to go to the Source Data Sources page of the Export wizzard. I am using SQL Server 2005 Management Studio. I get a similar error in Visual Studio 2005 when I attempt to open a SSIS project. When I click on the link in the error message the page at Micorsoft Help says there is no explanation. Any ideas on what might be wrong or what settings I am missing?

The error text is:

This wizard will close because it encountered the following error: (Microsoft SQL Server)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

===================================

QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)

Program Location:

at System.Runtime.InteropServices.CustomMarshalers.EnumeratorToEnumVariantMarshaler.MarshalNativeToManaged(IntPtr pNativeData)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSDBProviderInfos90.GetEnumerator()
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceCombo.PopulateProviders(Boolean fSources, WizardInputs wizardInputs)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnInitializePage(EventArgs e)
at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage nextPage)
at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender, EventArgs e)

Review this thread http://groups-beta.google.com/group/microsoft.public.dotnet.framework.setup/browse_thread/thread/a2ed96d68cb349f3/db810a1bcf8f66e4?q=qi+ienumvariant&_done=%2Fgroups%3Fhl%3Den%26lr%3Dlang_en%26safe%3Doff%26c2coff%3D1%26q%3Dqi+ienumvariant%26qt_s%3DSearch+Groups%26&_doneTitle=Back+to+Search&&d#db810a1bcf8f66e4 in other groups that might help to resolve the issue.

HTH

|||This may be due to InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)

I am getting the following message when I attempt to Export data in SQL Management Studio from my workstation. The error occurs as soon as I click Next to go to the Source Data Sources page of the Export wizzard. I am using SQL Server 2005 Management Studio. I get a similar error in Visual Studio 2005 when I attempt to open a SSIS project. When I click on the link in the error message the page at Micorsoft Help says there is no explanation. Any ideas on what might be wrong or what settings I am missing?

The error text is:

This wizard will close because it encountered the following error: (Microsoft SQL Server)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

===================================

QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)

Program Location:

at System.Runtime.InteropServices.CustomMarshalers.EnumeratorToEnumVariantMarshaler.MarshalNativeToManaged(IntPtr pNativeData)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSDBProviderInfos90.GetEnumerator()
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceCombo.PopulateProviders(Boolean fSources, WizardInputs wizardInputs)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnInitializePage(EventArgs e)
at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage nextPage)
at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender, EventArgs e)

Review this thread http://groups-beta.google.com/group/microsoft.public.dotnet.framework.setup/browse_thread/thread/a2ed96d68cb349f3/db810a1bcf8f66e4?q=qi+ienumvariant&_done=%2Fgroups%3Fhl%3Den%26lr%3Dlang_en%26safe%3Doff%26c2coff%3D1%26q%3Dqi+ienumvariant%26qt_s%3DSearch+Groups%26&_doneTitle=Back+to+Search&&d#db810a1bcf8f66e4 in other groups that might help to resolve the issue.

HTH

|||This may be due to InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194