Sunday, February 19, 2012

cannot get variable value

Hello,
I need to get the value of one variable to make one test inside one cursor
but I can get it. I need to verify if the server passed by the cursor has th
e
login to be changed but i cant do it. All the other steps inside the cursor
work fine.
Can you help me?
/ ****************************************
*******************/
cursor declared…
fetch next from cursor into @.hostname
while @.@.fetch_status = 0
begin
.
-- create linked server
set @.sql = 'exec sp_addlinkedserver' + ' ' + nchar(39) + @.hostname +
nchar(39) + ', ' + 'N' + nchar(39) + 'SQL Server' + nchar(39)
exec sp_executesql @.sql
set @.sql = 'exec sp_addlinkedsrvlogin @.rmtsrvname = ' + ' ' + nchar(39) +
@.hostname + nchar(39) + ', @.useself = ' + nchar(39) + 'true' + nchar(39)
exec sp_executesql @.sql
.
declare @.i int
/*PROBLEM:*/ set @.i = (select count(1) from + @.hostname +
.master.dbo.syslogins where [name] = + nchar(39) + @.usr + nchar(39)
if @.i >= 1
begin
set @.sql = 'exec ' + @.hostname + '.master..'+ 'sp_password NULL, @.new =
' + nchar(39) + @.new + nchar(39) + ', @.loginame = ' + nchar(39) + @.usr +
nchar(39)
exec sp_executesql @.sql
end
else
print 'Login ' + nchar(39) + upper(@.usr) + nchar(39) + 'does not exists
on server: ' + nchar(39) + @.hostname + nchar(39)
-- drop linked server…………….
fetch next from cursor into @.hostname
end
close cursor
deallocate cursor
/ ****************************************
*******************/
Best regards,Hi
sp_executesql can return parameters. For an example see
http://www.sommarskog.se/dynamic_sql.html#sp_executesql
John
"CC&JM" wrote:

> Hello,
> I need to get the value of one variable to make one test inside one cursor
> but I can get it. I need to verify if the server passed by the cursor has
the
> login to be changed but i cant do it. All the other steps inside the curso
r
> work fine.
> Can you help me?
> / ****************************************
*******************/
> cursor declared…
> fetch next from cursor into @.hostname
> while @.@.fetch_status = 0
> begin
> .
> -- create linked server
> set @.sql = 'exec sp_addlinkedserver' + ' ' + nchar(39) + @.hostname +
> nchar(39) + ', ' + 'N' + nchar(39) + 'SQL Server' + nchar(39)
> exec sp_executesql @.sql
> set @.sql = 'exec sp_addlinkedsrvlogin @.rmtsrvname = ' + ' ' + nchar(39) +
> @.hostname + nchar(39) + ', @.useself = ' + nchar(39) + 'true' + nchar(39)
> exec sp_executesql @.sql
> .
> declare @.i int
> /*PROBLEM:*/ set @.i = (select count(1) from + @.hostname +
> .master.dbo.syslogins where [name] = + nchar(39) + @.usr + nchar(39)
> if @.i >= 1
> begin
> set @.sql = 'exec ' + @.hostname + '.master..'+ 'sp_password NULL, @.new
=
> ' + nchar(39) + @.new + nchar(39) + ', @.loginame = ' + nchar(39) + @.usr +
> nchar(39)
> exec sp_executesql @.sql
> end
> else
> print 'Login ' + nchar(39) + upper(@.usr) + nchar(39) + 'does not exists
> on server: ' + nchar(39) + @.hostname + nchar(39)
> -- drop linked server…………….
> fetch next from cursor into @.hostname
> end
> close cursor
> deallocate cursor
> / ****************************************
*******************/
> Best regards,

No comments:

Post a Comment