Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

dynamic linked server query Expand / Collapse
Author
Message
Posted Wednesday, November 12, 2008 11:05 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 2,841, Visits: 3,985
hi ,

Below is the linked server query ,here i m passing the server and linked server name dynamically
The problem is that its giving error for very last line (it is not taking linked server name dynamically)
but if i hard coded the linked server in last line its working

i need to have linked server dynamically

----------------------------------------------------------------------------
declare @servername varchar(20),
@Linked_server varchar(20),
@SQLCmd varchar(200),
@TableName varchar(50)

set @TableName = 'sysobjects'
SELECT @servername = @@servername
IF LEFT(@servername,2) ='D2'
SET @Linked_server = 'IN\DEV'
ELSE
SET @Linked_server = 'D2\DEV'
print @servername
print @Linked_server
SET @SQLCmd = 'SELECT top 10 * from [' + @servername + '].PROD.dbo.' + @TableName + ' WITH (NOLOCK)'
EXEC(@SQLCmd) AT @Linked_server
------------------------------------------------------------------------------


Please help


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #601861
Posted Thursday, November 13, 2008 7:39 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:44 AM
Points: 770, Visits: 1,597
Try without AT @Linked_Server, it will work, its working for me, what version of SQL are u using mate???
Post #602115
Posted Sunday, August 28, 2011 9:37 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
Don't suppose you know how to see SYNONYMS in a linked server.

I have SYNONYMS set up on server b and on server a set the user with view definition of SYNONYMS but cannot see them in linked server only see tables and views. Can't see SP either

Right now i can ony set up a view to see the data on servera and want the user to be able to run select * from mysynonym

Just curious if even possible.
Post #1166683
Posted Tuesday, August 30, 2011 2:27 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:29 AM
Points: 454, Visits: 646
Bhuvnesh (11/12/2008)
hi ,

Below is the linked server query ,here i m passing the server and linked server name dynamically
The problem is that its giving error for very last line (it is not taking linked server name dynamically)
but if i hard coded the linked server in last line its working

i need to have linked server dynamically

----------------------------------------------------------------------------
declare @servername varchar(20),
@Linked_server varchar(20),
@SQLCmd varchar(200),
@TableName varchar(50)

set @TableName = 'sysobjects'
SELECT @servername = @@servername
IF LEFT(@servername,2) ='D2'
SET @Linked_server = 'IN\DEV'
ELSE
SET @Linked_server = 'D2\DEV'
print @servername
print @Linked_server
SET @SQLCmd = 'SELECT top 10 * from [' + @servername + '].PROD.dbo.' + @TableName + ' WITH (NOLOCK)'
EXEC(@SQLCmd) AT @Linked_server
------------------------------------------------------------------------------


Please help



Use it Like this

------------------------------------------
declare @servername varchar(100),
@Linked_server varchar(100),
@SQLCmd varchar(200),
@TableName varchar(100)

set @TableName = 'sysobjects'
SELECT @servername = @@servername
--IF LEFT(@servername,2) ='D2'
--SET @Linked_server = 'IN\DEV'
--ELSE
SET @Linked_server = '[xx.xxx.x.xxx]'
print @servername
print @Linked_server
SET @SQLCmd = '(''SELECT top 10 * from abc.dbo.' + @TableName + ' WITH (NOLOCK)'') AT ' + @Linked_server

select @SQLCmd = 'EXEC ' + (@SQLCmd)

EXEC(@SQLCmd)



Regards
Ashok
Post #1167341
Posted Tuesday, August 30, 2011 7:40 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
Thanks for the script
Post #1167527
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse