GeekZilla

Bug in VS2005? Using Typed DataSets with SQL 2005 and stored procedures

I have been a fan of typed datasets since I started using them in VS2005. Using the feature with SQL Express/stored procedures, Express/SQL Statements, SQL Server/SQL Statements all generate my DAL just fine.

However, I have found a couple of problems with them when using them with full SQL Server 2005 and stored procedures.

Problem 1:

The first problem is basically that the wizard generates stored procedures in the Schema of the user in the connection string. Regardless of whether SQL user or Windows Authentication are used.

You probably wil not have created this schema in the database and so the wizard works fine until the finish step where you see the following message...

You might change the default Schema to dbo for the user, the wizard ignores this and performs 'CREATE PROCEDURE user.spName...', very annoying.

So, you create a schema in your database with the name of the user (and the user access to it) and this problem is resolved.

Problem 2:

The next problem you will see is the message:

The reason for this is that the stored procedures are created in the user schema whilst the GetData/Fill/insert/update/delete methods still use the dbo Schema. I couldn't find a way to stop this other than changing the properties of the table adapter manually after configuring.

However you may note that GenerateDBDirect methods is still set to true in the table adapter properties and the methods are infact generated.

This results in ...

Problem 3:

When you run your code you see a run time error "Could not find stored procedure 'dbo.yourProcName' "

What I ended up doing was to run a script (http://www.geekzilla.co.uk/View4384ACEC-8C9D-452A-83F6-230FFE43C17B.htm) to move my user.spXXX to dbo.spXXX , not ideal but the Dataset works fine after this.

All sorted...

Once you aware of what the wizard is doing - creating in user schema and referring to dbo schema - it becomes quite easy to cope with (if not agree with).

For a while its behaviour seemed quite random, especially as I had some existing procs in the dbo schema and so some tables worked fine as they created new procs in the user schema and pointed at existing ones in the dbo schema.

My final thought is - I get the impression Typed Datasets wasn't tested with SQL Server and stored procedures. But this won't stop me from using them. I hope this is fixed in a service pack soon.

Author Dave Howard

I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.

Comments

Add Comment

Latest Articles