Variables & Linked Server Querying

Question

Post reply

Variables & Linked Server Querying

Miguel Pereira-543757

Mr or Mrs. 500

Points: 524
More actions
January 16, 2008 at 2:01 pm

#378073

Alright, first post here so have mercy. The situation is as follows:
I am working on a storproc that can take variable input, pass it downstream to reference a linked server and return my data set. The t-sql part would look pretty much like this:
DECLARE @somebox as varchar(20)
SET @somebox = 'LINKEDBOX'
SELECT * FROM @somebox.master.dbo.sysobjects
As it is, QA is throwing:
Incorrect syntax near '.'.
Any ideas or direction will be greatly appreciated!
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

The variable has to be wrapped in a dynamic sql statement like such.

DECLARE @somebox as nvarchar(20), @sql NVARCHAR(500)

SET @somebox = '[LINKEDBOX]'

SET @sql = N'SELECT * FROM ' + @somebox + '.[master].[dbo].[sysobjects]'

EXEC (@sql)

My blog: http://jahaines.blogspot.com

Miguel Pereira-543757 Mr or Mrs. 500 Points: 524 More actions · Answer 2

Thanks! That works beatifuly...now the last conundrum I've left is that I am exceeding the maximum number of objects (linkedserver.db.table.field)

If there is no workaround...I'll start aliasing I guess.

Adam Haines (1/16/2008)
The variable has to be wrapped in a dynamic sql statement like such.
DECLARE @somebox as nvarchar(20), @sql NVARCHAR(500)
SET @somebox = '[LINKEDBOX]'
SET @sql = N'SELECT * FROM ' + @somebox + '.[master].[dbo].[sysobjects]'
EXEC (@sql)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

I do not know of any fix, if you have too many objects in your database catalog. What is the error message?

My blog: http://jahaines.blogspot.com

Miguel Pereira-543757 Mr or Mrs. 500 Points: 524 More actions · Answer 4

Adam, the problem was that I was referencing fields by using [remoteserver].[db].[owner].

.[field] and MSSQL wasn't liking it. All I had to do was alias the [remoteserver].[db].[owner] as [aliasname].

In other words, your solution worked beatifuly! I just struggled a little bit rewritting some portions of the queries to work well with the wrapper.:w00t:

Adam Haines (1/16/2008)
I do not know of any fix, if you have too many objects in your database catalog. What is the error message?

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

Gotcha, I misunderstood the problem.

I am glad everything worked out and thanks for the feedback.:)

My blog: http://jahaines.blogspot.com