Dynamic SQl error - I might be doing something wrong.
-
Please run below code
----------------------------
set nocount on
declare @role varchar(255),@dbname varchar(255)
declare @rowid int
declare @user nvarchar(255)
declare @cmd varchar(8000)
set @dbname = 'Mydatabasedashboardaaaaaaaaa1111'
set @role = 'db_owner' -- Change database role name as appropriate
set @user = 'domain\devgroup'
select @cmd1 = 'USE '+@dbname+char(13)+
'if exists ( select [name] from sys.database_principals where [name] = '''+@user+''')'+char(13)+
'drop user '+'['+@user+']'+char(13)+
'CREATE USER ['+@user+'] FOR LOGIN ['+@user+']'+char(13)+
'EXEC sp_addrolemember '''+@role+''','''+@user+''''+char(13)
from sys.server_principals
where 1 =1
select @cmd
-----------------------
--Output:
/*
USE Mydatabasedashboardaaaaaaaaa1111
if exists ( select [name] from sys.database_principals where [name] = 'domain\devgroup')
drop user [domain\devgroup]
CREATE USER [domain\devgroup] FOR LOGIN [domain\devgroup]
EXEC sp_addrolemember 'db_owner','domain\de
The output is being truncated in last line even after declaring @cmd with varchar(8000) and nvarchar(max).
I might be doing something stupid ,Can you please help me with this issue?
*/
-
sqlbee19 (5/23/2012)
Please run below code----------------------------
set nocount on
declare @role varchar(255),@dbname varchar(255)
declare @rowid int
declare @user nvarchar(255)
declare @cmd varchar(8000)
set @dbname = 'Mydatabasedashboardaaaaaaaaa1111'
set @role = 'db_owner' -- Change database role name as appropriate
set @user = 'domain\devgroup'
select @cmd1 = 'USE '+@dbname+char(13)+
'if exists ( select [name] from sys.database_principals where [name] = '''+@user+''')'+char(13)+
'drop user '+'['+@user+']'+char(13)+
'CREATE USER ['+@user+'] FOR LOGIN ['+@user+']'+char(13)+
'EXEC sp_addrolemember '''+@role+''','''+@user+''''+char(13)
from sys.server_principals
where 1 =1
select @cmd
-----------------------
--Output:
/*
USE Mydatabasedashboardaaaaaaaaa1111
if exists ( select [name] from sys.database_principals where [name] = 'domain\devgroup')
drop user [domain\devgroup]
CREATE USER [domain\devgroup] FOR LOGIN [domain\devgroup]
EXEC sp_addrolemember 'db_owner','domain\de
The output is being truncated in last line even after declaring @cmd with varchar(8000) and nvarchar(max).
I might be doing something stupid ,Can you please help me with this issue?
*/
we had this on the forums a few days ago - why are you doing -
from sys.server_principals
where 1 =1
try removing this and your code will work
MVDBA
-
the multiple rows returned in your select statement are corrupting the @cmd value - in theory the value returned should be correct (although if you use any fields from sys.server_principals you will always set the value based on the last row returned)
MVDBA
-
michael vessey (5/23/2012)
the multiple rows returned in your select statement are corrupting the @cmd value - in theory the value returned should be correct (although if you use any fields from sys.server_principals you will always set the value based on the last row returned)Thanks. This is not exactly what I am running. I just editing it so that it will be easy to understand.
I am looking for why the output is being truncated?
It is being truncated even when it is selecting 1 row.
-
the truncation is because by default, to save memory, SSMS limits the Results-To-Text to the first 256 characters, regardless of the results.
change it in your options to 8000
not that no matter what size you put in there, the max limit is 8000 chars; so the data might have more, but SSMS will only display the first 8000.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
awesome Lowell.
It works. Thank you.
-
Check the setting in SSMS. Tools -> Options -> Query Results -> SQL Server -> Results to Grid and Results to Text.
-
sqlbee19 (5/23/2012)
michael vessey (5/23/2012)
the multiple rows returned in your select statement are corrupting the @cmd value - in theory the value returned should be correct (although if you use any fields from sys.server_principals you will always set the value based on the last row returned)Thanks. This is not exactly what I am running. I just editing it so that it will be easy to understand.
I am looking for why the output is being truncated?
It is being truncated even when it is selecting 1 row.
next time, please post the correct code so we don't spend our own time chasing up false negatives 😛
MVDBA
-
michael vessey (5/24/2012)
sqlbee19 (5/23/2012)
michael vessey (5/23/2012)
the multiple rows returned in your select statement are corrupting the @cmd value - in theory the value returned should be correct (although if you use any fields from sys.server_principals you will always set the value based on the last row returned)Thanks. This is not exactly what I am running. I just editing it so that it will be easy to understand.
I am looking for why the output is being truncated?
It is being truncated even when it is selecting 1 row.
next time, please post the correct code so we don't spend our own time chasing up false negatives 😛
oh and the reason the code was truncated when 1 row was returned is because of the bugs within select @= when you use a recordset - even with 1 row
MVDBA
-
michael vessey (5/24/2012)
michael vessey (5/24/2012)
sqlbee19 (5/23/2012)
michael vessey (5/23/2012)
the multiple rows returned in your select statement are corrupting the @cmd value - in theory the value returned should be correct (although if you use any fields from sys.server_principals you will always set the value based on the last row returned)Thanks. This is not exactly what I am running. I just editing it so that it will be easy to understand.
I am looking for why the output is being truncated?
It is being truncated even when it is selecting 1 row.
next time, please post the correct code so we don't spend our own time chasing up false negatives 😛
oh and the reason the code was truncated when 1 row was returned is because of the bugs within select @= when you use a recordset - even with 1 row
Can you please read the thread from begining,I have already got answers from "Lowell" and "Lynn Pettis" and I did acknowledged that it worked for me.
There is nothing wrong in code,The issue is with SSMS settings.
-
and please re-read the thread yourself - lowels reponse was to change the "query results to text" - which is nothing to do with a select to a grid (although lowel makes a good point, i'm not knocking him)
the issue you were having "can" (but not always) be as a result of useing select @= when there are multiple rows, which is why i suggested it- like i said - why post the bit saying "from ... where 1=1" when this is going to lead us up the garden path
hence i feel justified in asking you to post more accuratly next time
MVDBA
-
michael vessey (5/24/2012)
and please re-read the thread yourself - lowels reponse was to change the "query results to text" - which is nothing to do with a select to a grid (although lowel makes a good point, i'm not knocking him)the issue you were having "can" (but not always) be as a result of useing select @= when there are multiple rows, which is why i suggested it- like i said - why post the bit saying "from ... where 1=1" when this is going to lead us up the garden path
hence i feel justified in asking you to post more accuratly next time
agreed;
i had adapted his code below so I could test it like this, specifically because it wasn't the right syntax due to multiple rows.
When i saw he mentioned his truncated rusults, i counted characters and knew it was the 256 issue.
declare @role varchar(255),@dbname varchar(255)
declare @rowid int
declare @user nvarchar(255)
declare @cmd varchar(8000)
set @dbname = 'Mydatabasedashboardaaaaaaaaa1111'
set @role = 'db_owner' -- Change database role name as appropriate
set @user = 'domain\devgroup'
SELECT 'USE ' + @dbname + CHAR(13) + CHAR(10)
+ 'if exists ( select [name] from sys.database_principals where [name] = ''' + @user + ''')' + char(13) + CHAR(10)
+ 'drop user ' + '[' + @user + ']' + char(13) + + CHAR(10)
+ 'CREATE USER [' + @user + '] FOR LOGIN [' + @user + ']' + char(13) + + CHAR(10)
+ 'EXEC sp_addrolemember ''' + @role + ''',''' + @user + '''' + char(13)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I know "where 1 = 1" doesnt make sence but somehow I used it in thread. I was more concerned on the output being truncated then the bug in my code.
Thanks guys for your time.
Here is my actual script.
/****** Object: StoredProcedure [dbo].[stp_loginscript]] Script Date: 05/24/2012 13:15:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Stp_loginscript]
( @windowslogin nvarchar(100)
)
as
begin
set nocount on
declare @role varchar(255),@dbname varchar(255)
--set @dbname = 'AdventureWorks' -- Change database name as appropriate
declare @test-2 table (RowId int Identity(1,1),[name] varchar(255))
declare @dbcount int
declare @rowid int
declare @user nvarchar(255)
declare @cmd nvarchar(1000) ,@cmd1 nvarchar(4000)
select @user =@windowslogin
insert into @test-2([name]) select [name] from sys.databases where [name] not in ('master',
'tempdb',
'model',
'msdb',
'distribution')
select @dbcount = max(rowid) from @test-2
set @rowid = 1
if not exists ( select [name] from sys.server_principals where [name] = @user)
begin
select @cmd = 'use master '+char(13)+char(13)+'CREATE LOGIN ['+@user+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'+char(13)
--select @cmd
exec sp_executesql @cmd
end
while 1 = 1
BEGIN
select @dbname = name from @test-2 where rowid = @rowid
set @role = 'db_owner' -- Change database role name as appropriate
select @cmd1 = 'USE '+@dbname+char(13)+
'if exists ( select [name] from sys.database_principals where [name] = '''+@user+''')'+char(13)+
'drop user '+'['+@user+']'+char(13)+
'CREATE USER ['+@user+'] FOR LOGIN ['+@user+']'+char(13)+
'EXEC sp_addrolemember '''+@role+''','''+@user+''''+char(13)
from sys.server_principals
where type_desc in ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN')
and [name] =@user
set @rowid = @rowid +1
select @cmd1
if @rowid >@dbcount
BREAK
END
end
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply