Crossing Databases: Exec statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Crossing Databases: Exec statements

Author

Topic

Trent
Starting Member

39 Posts

Posted - 2006-01-06 : 12:53:41

I have a stored procedure in one db that needs to grab info from another database. Easy enough, but I can't figure out how to set that info from the second database equal to a variable in the stored procedure. I put some code below to illustrate what I'm trying to do:

DECLARE @TableName nvarchar(64)
DECLARE @SQLString nvarchar(1200)
DECLARE @test char(7)

select @TableName = 'S' + @StudyNo
select @SQLString = 'set '+@test+' = (select top 1 DatabaseID from SecondDB.dbo.' + @TableName + ')'
execute sp_ExecuteSQL @SQLString

select @test

When I do this, @test is left as NULL. I've tried the Select statement inside the quotes without the SET part, and it does return the value I'm looking for, I just can't get it to set to @test....

Thanks for any help!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 12:57:39

see
http://www.nigelrivett.net/SQLTsql/sp_executeSQL.html

select @TableName = 'S' + @StudyNo
select @SQLString = 'select top 1 @test = DatabaseID from SecondDB.dbo.' + @TableName + ')'
execute sp_ExecuteSQL @SQLString, N'@test char(7) out', @test out

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Trent
Starting Member

39 Posts

Posted - 2006-01-06 : 13:03:19

RESOLVED

Thank You, I haven't seen syntax like that before, very useful for future use!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources