variable in EXECUTE ... AT linked_server

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 2005 Forums

Transact-SQL (2005)

variable in EXECUTE ... AT linked_server

Author

Topic

marty1976
Starting Member

22 Posts

Posted - 2011-08-04 : 11:20:41

Hi all,

in my script I declare and set a variable. Can I later use that variable in a remote EXEC'd statement?
Or are my variables invisible in the EXEC?

DECLARE @company_SID integer
SET @company_SID = 123
EXECUTE ('SELECT * FROM Companies WHERE company_SID = @company_SID') AT [linked_srv]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-04 : 11:33:50

That would be "out of scope" to the exec'd statement. This would work though:

--print 'SELECT * FROM Companies WHERE company_SID = ' + convert(varchar(12), @company_SID)
EXECUTE ('SELECT * FROM Companies WHERE company_SID = ' + convert(varchar(12), @company_SID) ) AT [linked_srv]

Be One with the Optimizer
TG

marty1976
Starting Member

22 Posts

Posted - 2011-08-05 : 04:04:28

But of course! Thank you for making me see what's quite obvious... It's on occasions like this I realize I'm a bloody DBA rookie :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources