Need Help For Best Method
-
Hi,
Here is my criteria.I need to connect to the remote servers and have to execute some queries in that remote servers.Can u people help me that which one is the best method to execute the queries in remote from our local server?
Any suggestions have to follow?
Thanks,
Rao Aregaddan.
-
EXEC RemoteServer.Database.dbo.StoredProcedure @Parameter1, @parameter2
_____________
Code for TallyGenerator -
Can u explain in detail..?
Concept:
---------------
Have to connect to more than 1000 servers....
Using test server we have to execute in remote ( remote tables are not available in this server)
First have to connect to the remote, select the right database after that execute the queries.
To do this we have to maintain 2 or 3 loops right?
Thanks,
-
Why?
One table with list of servers, databases, SP names and probably list of parameters will do it all.
Cursor through this table, build the query from names stored there and EXEC sp_executesql @Query
_____________
Code for TallyGenerator -
Just check out the below.I have developed like that only.Suggest me the better way than this if any.
ALTER PROCEDURE SP_LOOP_CONNETREMOTE
AS
DECLARE @IP VARCHAR(24)
DECLARE @Password VARCHAR(24)
DECLARE @DBName VARCHAR(24)
DECLARE @Stmt VARCHAR(200)
BEGIN
DECLARE Cursor_Connect CURSOR FOR SELECT IP,Pwd,DBName FROM ADP_Narsi_TestDB..CLIENT_DETAILS
--SELECT * FROM CLIENT_DETAILS
OPEN Cursor_Connect
FETCH NEXT FROM Cursor_Connect INTO @IP,@Password,@DBName
WHILE (@@Fetch_Status=0)
BEGIN
/*PRINT @ClientIPAddress
PRINT @ClientPassword
PRINT @DBNAME*/
set @stmt='osql -S '+@IP +' -U sa -P '+@Password +' -d master -Q"SELECT Name FROM sysdatabases"'--1
IF EXISTS (SELECT * FROM ADP_Narsi_TestDB..SYSOBJECTS WHERE TYPE='U' AND NAME='DBases')
BEGIN --2
drop table ADP_Narsi_TestDB..DBases
END --2
CREATE TABLE DBases
(
dbname SYSNAME NULL
 
; INSERT into DBases
EXEC master..xp_cmdshell @Stmt
SELECT * FROM DBASES
IF EXISTS ( SELECT 1 FROM DBases WHERE LTRIM(RTRIM(dbname)) = N'master')
BEGIN
DECLARE @BDC_Query VARCHAR(200)
DECLARE @Service_Query VARCHAR(200)
SET @BDC_Query='SELECT * FROM L_Versions'
SET @Service_Query='SELECT * FROM L_Versions'
--DECLARE @BDC_Query1 VARCHAR(200)
--DECLARE @Service_Query1 VARCHAR(200)
DECLARE @DROPSERVER VARCHAR(50)
SET @DROPSERVER =('SP_DROPSERVER ''LINK_REMOTE'',''DROPLOGINS''')
IF EXISTS (SELECT * FROM MASTER.DBO.SYSSERVERS WHERE SRVNAME LIKE 'LINK_REMOTE')
EXEC(@DROPSERVER)
Exec SP_ADDLINKEDSERVER
@server ='LINK_REMOTE',
@SRVPRODUCT = '',
@PROVIDER ='SQLOLEDB',
@CATALOG = @DBName,
@datasrc=@IP
Exec sp_addlinkedsrvlogin @rmtsrvname='LINK_REMOTE',
@useself='false',
@rmtuser='sa',
@rmtpassword=@Password
--SET @BDC_Query1=REPLACE(@BDC_Query,'''','''''')
--SET @Service_Query1=replace(@Service_Query,'''','''''')
IF EXISTS (SELECT Dbname FROM Dbases WHERE DBName LIKE '%_CRM%')
BEGIN --3
IF EXISTS (SELECT * FROM TempDB..SYSOBJECTS WHERE TYPE='U' AND NAME='BDC_Temp_Tab')
BEGIN
DROP TABLE TempDB..BDC_Temp_Tab
END
SET @BDC_Query = 'SELECT * INTO TEMPDB.DBO.BDC_Temp_Tab FROM OPENQUERY(LINK_REMOTE,''' + @BDC_Query + ''') '
EXEC(@BDC_Query)
PRINT'BDC Records'
SELECT * FROM TEMPDB..BDC_Temp_Tab
END --3
ELSE
BEGIN --4
IF EXISTS (SELECT * FROM TempDB..SYSOBJECTS WHERE TYPE='U' AND NAME='Service_Temp_Tab')
BEGIN
DROP TABLE TempDB..Service_Temp_Tab
END
SET @Service_Query = 'SELECT * INTO TEMPDB.DBO.Service_Temp_Tab FROM OPENQUERY(LINK_REMOTE,''' + @Service_Query + ''') '
EXEC(@Service_Query)
Print 'Service Records'
SELECT * FROM TEMPDB..Service_Temp_Tab
END --4
END
ELSE
BEGIN
PRINT 'No Connection To The Server'
END
FETCH NEXT FROM Cursor_Connect INTO @IP,@Password,@DBName
END
END
CLOSE Cursor_Connect
DEALLOCATE Cursor_Connect
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply