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 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 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,
Sometimes I find that VS forgets where it is, as though it looses the context that the SSIS designer is active, so SSIS specific stuff is just not available. When this happens try clicking on the package design surface to set the focus back to SSIS, then re-open the Variables window or other window that is giving you trouble.|||I'm having a similar problem. I cannot even open the Variables window. If I right click the design surface and select Variables, nothing happens. I've tried creating a new package and starting from scratch but I get the same problem. I even uninstalled SQL Server and all of the client tools and reinstalled and applied all of the service packs but I still get nothing when I try to create a variable. I'm willing to try just about anything at this point.|||I had a similar problem with Visual Studio once, and using the /resetuserdata switch fixed it. It resets all your VS settings back as if you had just installed it. See this link for a little more information: http://msdn2.microsoft.com/en-us/library/bb245788(vs.80).aspx|||Thanks a million!!!! That worked.