Syntex Error in Distributed query

Question

Syntex Error in Distributed query

Nitin Gupta - SQL DBA

Ten Centuries

Points: 1058
More actions
November 11, 2009 at 1:17 pm

#73011

Hi All,
I'm try to connect multiple SQL server instances using distributed query ( OPENROWSET() ); whenever I'm executing connection string on its I'm getting syntex error on it.
set @conn= '''SQLOLEDB'', ''Server=' + @server +'; ''user id=' + @user id +'; ''Password=' +@pass'''
please correct me.
Thanks & Regards,
Nitin Gupta
SQl Server (7.0,2000,2005) - Sr. DBA
HCL Technologies, India

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are you building the connection dynamically inside the OpenRowset command? Or are you dynamically building an OpenRowset command? I really can't tell what you're doing from the little bit you posted.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Nitin Gupta - SQL DBA Ten Centuries Points: 1058 More actions · Answer 2

Hi, Basically I'm preparing a script to get backup details of all my servers (124 Physical Boxes in a single domain),

Steps:

1. Passing veriable name in @server and storing that script into a variable @query.

2. passind that @query into openrowset();

but when I'm seting connect string in @query like

@query = 'provider string with trusted connection' its showing syntex error on @Password

Thanks & Regards,

Nitin Gupta
SQl Server (7.0,2000,2005) - Sr. DBA
HCL Technologies, India

Nitin Gupta - SQL DBA Ten Centuries Points: 1058 More actions · Answer 3

Please find the same kind of code.

-- TO BE RUN ON SHCBBMS01 SERVER

-- For SQL 2000

set nocount on

go

declare @server varchar(256), @ctr int

declare @tbllist table (srvr varchar(256),id int identity)

insert @tbllist

select 'SHBAHYP01T' union

select 'SHBAHYP04' union

select 'SHBAINT02'

set @ctr = 1

while @ctr <= (select max(id) from @tbllist)

begin

select @server = srvr

from @tbllist

where id = @ctr

declare @conn varchar(200), @query varchar(8000), @sql varchar(8000)

set @conn= '''SQLOLEDB'', ''Server=' + @server +';Trusted_Connection=yes;'''

set @query = '''select @@SERVERNAME,name from master.sys.databases'''

select @sql = 'SELECT a.* FROM OPENROWSET('+@conn+','+@query+') AS a;'

exec( @sql )

select @ctr = @ctr + 1

end

go

In this script I want to replay trusted connection to user name & password.

as I'm new to query part please help me out.

Thanks in advance

Nitin

Thanks & Regards,

Nitin Gupta
SQl Server (7.0,2000,2005) - Sr. DBA
HCL Technologies, India