openquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

openquery

Author

Topic

iminore
Posting Yak Master

141 Posts

Posted - 2006-09-12 : 08:23:41

What's wrong with this:
exec('select flda from openquery([server2],''select flda from db1.dbo.table1''')

I keep getting:
incorrect syntax near 'select flda from db1.dbo.table1'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 08:28:55

You don't need EXEC here.
Use select flda from openquery([server2],'select flda from db1.dbo.table1') direct.

Peter Larsson
Helsingborg, Sweden

iminore
Posting Yak Master

141 Posts

Posted - 2006-09-12 : 09:03:54

The problem is that the servername and database are variable. So I do need to use exec, but the statement throws that error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 09:15:49

Why don't you tell me what you have this far, so I know what is dynamic and not?

Peter Larsson
Helsingborg, Sweden

iminore
Posting Yak Master

141 Posts

Posted - 2006-09-12 : 09:40:46

I thought I'd described the problem but here goes. A procedure that has:

declare @server varchar(80), @db varchar(80)
set @server = 'server2'
set @db = 'db2'
exec('select flda from openquery([' + @server + '], ''select flda from ' + @db + '.dbo.table2''')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 09:52:07

Concatenate the string before using it in EXEC might help.
You also need a ending paranthesis in the openquery syntax.

declare	@server varchar(80),
	@db varchar(80),
	@sqlcmd varchar(1000)

select	@server = 'server2',
	@db = 'db2',
	@sqlcmd = 'select flda from openquery([' + @server + '], ''select flda from ' + @db + '.dbo.table2'')'
-- print @sqlcmd
exec (@sqlcmd)

It is a VERY good practice to replace exec(@sqlcmd) with a PRINT @sqlcmd, to see what statement is going to be executed.

Peter Larsson
Helsingborg, Sweden

iminore
Posting Yak Master

141 Posts

Posted - 2006-09-12 : 10:17:36

Peter

You are a star. Yes, well said about print.

Next time I'm in Helsingborg I'll buy you a beer.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 10:20:02

Next time? It indicates you have been here before.
Ok, just send me an email and you can buy me a beer.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources