I recently upgraded from 2005 Professional to 2008 Team System. Most things converted ok but there seems to be a problem with stored procedures. I have a database connection to SQL Server 2005 with several stored procedures, some of which have 'sysname' type parameters. It looks correct in the Properties window, but when I try to execute it, the cell under 'Type' is completely blank for each sysname parameter (this is in the 'Run Stored Procedure' popup).
When I run the procedure, it produces the following output:
Running [dbo].[GetInfo] ( @id = 1, @dbname = test ). Incorrect syntax near the keyword 'SET'. Incorrect syntax near ','. Must declare the scalar variable "@param2". No rows affected. (0 row(s) returned) @RETURN_VALUE = Finished running [dbo].[GetInfo].
@id is type 'int' and @dbname is type 'sysname'
Note that the procedure runs perfectly on the server and in the application that uses it, just not in VS2008. I think this is related to the fact that the 'Run Stored Procedure' popup cannot display the type for @dbname but I cannot figure out why. Any help will be appreciated.Thursday, July 08, 2010 5:55 PM
Do you use database project and convert it from VS2005 to VS2008?
Can you clarify your scenario a bit more?
Please remember to mark the replies as answers if they help and unmark them if they provide no help.Monday, July 12, 2010 9:09 AM
I was working on a project created in VS2005. It uses the Microsoft.Practices.EnterpriseLibrary.Data namespace to run stored procedures. Upon upgrading to VS2008, I converted the project (still in .NET 2.0) and the stored procedures still work as expected when running the application.
The problem only occurs when I execute them from the Server Explorer. I have re-created the Data Connections to the same server (SQL Server 2005) and most of the procedures still run fine, it's only the ones that have 'sysname' type parameters that don't work anymore.
I hope this image can further clarify what is happening:
JoshMonday, July 12, 2010 3:31 PM
Thanks for reporting this issue.
This seems to be a VS 2008 bug. I can reproduce it in VS 2008. It seems that sysname can't be identified by VS 2008. However, it works well in SQL Management Studio.
The issue has been fixed in VS 2010. The same stored procedure can be executed correctly in VS 2010 server explorer.
If VS 2010 is not an option for you, can you workaround the issue by changing sysname to nvarchar(128) instead? sysname is functionally equivalent to nvarcha(128).
You can report the issue in http://connect.microsoft.com to product group.
Hope it helps. Have a nice day.
Looking for TFS Hot Issues? Follow us at Twitter.
Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.Monday, July 19, 2010 3:16 AMModerator