sp_executesql help
-
I have always had good intentions to use sp_executesql instead of exec but I usually just get frustrated and give up. I'm back to try again but have hit this roadblock. Does anyone know why the following command doesn't work:
DECLARE @admin-2 nvarchar(35), @SQLString nvarchar(max)
SET @admin-2=N'['+@@SERVERNAME+N'\Administrator]'
SET @SQLString='CREATE LOGIN @server FROM WINDOWS WITH DEFAULT_DATABASE=[master]'
EXEC sp_executesql @SQLString, N'@Server nvarchar(35)', @server=@Admin
Thanks for your help.
-
First thing I would like to know is what is the error you are receiving? The syntax of your statement appears correct, except I would probably use a variable to define the parameters for the query rather than define it directly in the exec sp_executesql statement.
-
I suspect the problem is that CREATE LOGIN does not accept a variable for the login_name so a variable will not work within sp_executesql. (I do not have access to SQL 2005 at the moment to check this.)
sp_executesql is like a dynamic stored procedure in that it allows reuse of the query plan where variables are possible.
eg
EXEC 'SELECT * FROM TestTable WHERE Col = ''Junk1'''
followed by
EXEC 'SELECT * FROM TestTable WHERE Col = ''Junk2'''
will produce two different plans.
EXEC @RetVal = sp_executesql N'SELECT * FROM TestTable WHERE Col = @ColName'
,N'@ColName varchar(30)'
,'Junk1'
followed by
EXEC @RetVal = sp_executesql N'SELECT * FROM TestTable WHERE Col = @ColName'
,N'@ColName varchar(30)'
,'Junk2'
will produce one plan.
-
It was as I expected and as you mentioned Ken, I was not able to declare a variable to add a login. @Admin looks fine when I just run a 'SELECT @admin-2' statement within @SQLString. I will go with your advice Lynn. Thanks to both of you for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply