Open Query Syntax Problem - 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)

Open Query Syntax Problem

Author

Topic

flugelboy
Starting Member

2 Posts

Posted - 2006-12-04 : 10:28:26

I'm using OpenQuery to execute a procedure on my local server so I can get the results from that procedure and then run some calcs on them. The problem is the the procedure I'm running uses parameters and I'm having some problems with the syntax. The error I'm getting is...

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '+'.

Here is my code...

declare @begindate smalldatetime,
@enddate smalldatetime,
@tablename varchar(50)

set @begindate='10/15/06'
set @enddate='2/7/07'
set @tablename='customer_master'

if exists (select * from master..sysservers where srvname = 'loopback')
exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = @@servername
go

select * into #temp from openquery(loopback, 'exec Staging_GetWeeklyCompletedPercentTrend ''' + @begindate + ''', ''' + @enddate + ''', ''' + @tablename + '''')

--this select will be changed to run calcs on returned results
select * from #temp

drop table #temp

Any help would be GREATLY appreciated. I've been working on this since last Friday and just can't seem to get it.

Thanks!

Kristen
Test

22859 Posts

Posted - 2006-12-04 : 11:12:49

You can't use an Expression in OPENQUERY, you'll need to assemble the Expression first in an @StringVariable, and then use that as a parameter in the OPENQUERY

SELECT @strSQL = 'exec Staging_GetWeeklyCompletedPercentTrend ''' + @begindate + ''', ''' + @enddate + ''', ''' + @tablename + ''''
select * into #temp from openquery(loopback, @strSQL)

Kristen

flugelboy
Starting Member

2 Posts

Posted - 2006-12-04 : 12:11:11

Thanks for the reply Kristen. I actually did try that during my troubleshooting but with that syntax I get the error...

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@strSQL'.

Here is my code with your example...

declare @begindate varchar(50),
@enddate varchar(50),
@tablename varchar(50),
@strSQL varchar(1000)

set @begindate='10/15/06'
set @enddate='2/7/07'
set @tablename='customer_master'

SELECT @strSQL = 'exec Staging_GetWeeklyCompletedPercentTrend ''' + @begindate + ''', ''' + @enddate + ''', ''' + @tablename + ''''

if exists (select * from master..sysservers where srvname = 'loopback')
exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = @@servername
go

select * into #temp from openquery(loopback, @strSQL)

--this select will be changed to run calcs on returned results
select * from #temp

drop table #temp

Kristen
Test

22859 Posts

Posted - 2006-12-04 : 14:14:07

a DECLARE variable isn't in scope after a subsequent GO

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources