September 6, 2009 at 4:29 am
This is somewhat strange and i am at wits end trying to figure out the root cause.During our database installation process, we execute sp_addsrvrolemember to add a sql login created to the sysadmin server role. We have an install library written in C# using ADO.Net to connect to the database.This C# library performs the operation of executing this stored procedure against the master database.This has been working fine for many builds now on both SQL 2005 & SQL 2008.
However all of a sudden i am experiencing an error only on SQL 2008.
The error reads as follows:Parameter 3 is incorrect for this DBCC statement.
The strange part is that inspite of the error, the sql login gets successfully added to the sysadmin server role.
Now here comes the strange part. This was working fine in all of the builds , until we introduced a minor change in the SQL scripts which get executed during our database install. I have verified the scripts and i have not found anything to suggest that these could have caused the issue. The sql scripts do not do anything with the sql logins.
I have performed the following steps to debug this issue:
1)Monitored the T-SQL calls during the installation using SQL Profiler. When sp_addsrvrolemember is executed from with the C# library, i experience the error. However if i execute the same T-SQL call using SQL Management Studio. i do not experience this error.
What could be the issue here?
Is there anything within our SQL scripts which creates a condition resulting in this error only on SQL 2008?
2)I would like to step into the sp_addsrvrolemember stored procedure as it is invoked from my C# library? Is this possible? If so, how do i do it?
Any links which could help me. There are tons of links on the web and i did not seem any of those useful.
My environment details are as follows:
Windows 2003 SP2
SQL Server 2008
Any help would be very much appreciated.
Regards,
Kiran Hegde
September 7, 2009 at 11:55 pm
I think i finally figured it out. I have a sql login abc which i am attempting to add as member of the sysadmin server role.
When i call the C# dll from my installscript project, the string due to some strange reason is being passed to the C# function incorrectly as: "abc\0\0\0\0".
When sp_addsrvrolemember is invoked using ADO.Net with one of it's parameters as abc\0\0\0\0(example: sp_addsrvrolemember 'abc\0\0\0\0','symadmin'), it throws up the error : Parameter 3 is incorrect for this DBCC statement.
However the same stored procedure invoked directly from SQL Management studio, throws up an error stating that the login 'abc\0\0\0\0' could not be found(Remember the login abc is actually created in the SQL Server)
This means to say that the behavior of sp_addsrvrolemember when executed from ADO.Net and SQL Management studio is different. Strange huh?
Can anyone explain if this is indeed true?
September 14, 2009 at 1:01 pm
kiran.hegde (9/7/2009)
However the same stored procedure invoked directly from SQL Management studio, throws up an error stating that the login 'abc\0\0\0\0' could not be found(Remember the login abc is actually created in the SQL Server)
When you are looking at it in code, abc\0\0\0\0 means abc with four null characters after it. If you actually type 'abc\0\0\0\0' into SSMS, then you have abc followed by four repetitions of '\' and '0'. For it to be the same string in SSMS, you probably want to pass 'abc' + char(0) + char(0) + char(0) + char(0)
It's not intensely clear from your description though.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply