Dynamic Sql QUERY To use Dynamic openrowset

Question

Dynamic Sql QUERY To use Dynamic openrowset

syamala.popuri

Mr or Mrs. 500

Points: 597
More actions
November 29, 2010 at 7:13 am

#388327

Hi All,
i have 20 servers. each server contain store proc named as sp_dbdetails. From all servers i want to pull data into central server using openroset dynamically
for 1 server i used the fallowing opedata set query:
use master
Insert Into dbo.dbtable
select A.*
FROM
OPENROWSET ('SQLNCLI10', 'server=trescs;trusted_connection=yes',
'set fmtonly off exec [MASTER].[dbo].[SP_dbdetails')as A
how can i change the above query dynamically means
i want to get data into centraal table when any server name i given to above query.
Thansks in advance
Syamala

Viewing 4 posts - 1 through 3 (of 3 total)

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

sjimmo SSChampion Points: 11139 More actions · Answer 1

We have the same issue, rougly 80 servers on the network, and each needs to be able to pass data to the HQ.

What we do - declare a variable for the server. We have a table which lists all of the servers names and where they belong. We lookup the server name based upon the variable then pass the server name to another variable.

ie:

create Procedure [procedure_name]
@storenumbertinyint
As

Then we declare our other variables:

Declare @dateDateTime,
@cmdnvarchar(1000),
@server Varchar(20)

Now we get the server:

SELECT @server = RTrim( ServerName ) FROM database.dbo.table WITH (nolock) WHERE storenum = @storenumber

After we have identified the server, we execute the sql as:

Insert Into dbo.dbtable
select A.*
FROM
OPENROWSET ('SQLNCLI10', 'Server=' + @server + ';trusted_connection=yes',
'set fmtonly off exec [MASTER].[dbo].[SP_dbdetails')as A

Hope this helps.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

syamala.popuri Mr or Mrs. 500 Points: 597 More actions · Answer 2

declare @server as varchar(50)

SELECT @server = RTrim(ServerName) FROM master.dbo.ServerList1 WITH (nolock) WHERE ServerName = @server

print @server

Insert Into dbo.Server_Details_Table

select A.*

FROM

OPENROWSET ('SQLNCLI10', 'Server=''+ @server +'';trusted_connection=yes',

'set fmtonly off exec [MASTER].[dbo].[SP_SQL_SINFO]')as A

SERVERLIST CONTAINS 2 CLOUMNS

SERVERNUMBER,SERVERNAME .

WHEN EXECUTED IT IS GIVING ERROR THAT

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

Msg 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53].

Unable to find out error

Thanks in advance

sjimmo SSChampion Points: 11139 More actions · Answer 3

Is the startup service of all of your SQL Servers the same and are they Domain Admin?

What is the permission level of the account calling the stored procedure?

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan