Solved

SQL openquery and quotes

Posted on 2011-03-22
9
699 Views
Last Modified: 2012-05-11
I always get mixed up with how to use quotes when building an openquery string. Can someone please adivse how i do the following:

DECLARE      @st_dte      datetime
set @st_dte       = getdate()

SELECT * FROM OPENQUERY(dbserv,'select distinct A,''B'',' + @st_dte +',''C''  from  dbname.dbo.table ' )

Here's the error message:  Incorrect syntax near '+'.

thanks


0
Comment
Question by:tigerkraka
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 35194572
You cannot construct the SQL in OPENQUERY itself, you need to do that in advance.

DECLARE      @st_dte      datetime
set @st_dte       = getdate()
DECLARE @stmt nvarchar(1000)
set @stmt = 'select distinct A,''B'',' + @st_dte +',''C''  from  dbname.dbo.table '
SELECT * FROM OPENQUERY(dbserv, @stmt)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35194904
You will have to use Dynamic SQL to accomplish that.
0
 

Author Comment

by:tigerkraka
ID: 35194949
That helped a bit. It seems I have to convert getdate() to a string but I am still missing a quote after the date:

DECLARE      @st_dte      varchar(20)
set @st_dte       = cast(getdate() as varchar(20))
set @st_dte   = '''' + @st_dte + ''''
DECLARE @stmt nvarchar(1000)
set @stmt = 'select distinct A,''B'',' + @st_dte +',''C''  from  dbname.dbo.table '
print @stmt

select distinct A,'B','Mar 22 2011  5:15PM,'C'  from  dbname.dbo.table
0
 

Author Comment

by:tigerkraka
ID: 35195030
By using char(39) I''ve got the statement looking correct but I am still getting an error:

DECLARE      @st_dte      varchar(20)
set @st_dte       = cast(getdate() as varchar(20))
DECLARE @stmt nvarchar(1000)
set @stmt = 'select distinct A,''B'',' + char(39) + @st_dte + char(39) +',''C''  from  dbname.dbo.table '

SELECT * FROM OPENQUERY(ETL,@stmt)

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '@stmt'.


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:Sharath
ID: 35195044
try this.
DECLARE      @st_dte      varchar(20)
set @st_dte       = cast(getdate() as varchar(20))
set @st_dte   = '''' + @st_dte + ''''
DECLARE @stmt nvarchar(1000)
set @stmt = 'select distinct A,B,' + @st_dte +''',C  from  dbname.dbo.table ' 
print @stmt

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35195076
>>Incorrect syntax near '@stmt'.<<
And again, if you want to use OPENQUERY() then you are going to have to use Dynamic SQL.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35195085
OPENQUERY does not accept variables for its arguments.
DECLARE      @st_dte      varchar(20)
set @st_dte       = cast(getdate() as varchar(20))
set @st_dte   = '''' + @st_dte + ''''
DECLARE @stmt nvarchar(1000)
set @stmt = 'select distinct A,B,' + @st_dte +''',C  from  ETL.dbname.dbo.table ' 
print @stmt
exec(@stmt)

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35195148
>>OPENQUERY does not accept variables for its arguments.<<
Exactly.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 125 total points
ID: 35196562
Ok. I should have looked that up before I posted - you are right, no vars in OpenQuery.
http:#a35195085 shows the statements to use if you can use the server.db.schema.object syntax, which you should be able to - the remote server seems to be MSSQL, too. But it needs a small correction to be exactly the same as your query.
DECLARE      @st_dte      varchar(22)
set @st_dte       = '''' + cast(getdate() as varchar(20)) + ''''
DECLARE @stmt nvarchar(1000)
set @stmt = 'select distinct A,''B'',' + @st_dte +',''C''  from  ETL.dbname.dbo.table ' 
print @stmt
exec(@stmt)

Open in new window

I assume you are using a very simple example, because it doesn't make much sense not to use getdate() directly in the SQL:
set @stmt = 'select distinct A,''B'', getdate(),''C''  from  ETL.dbname.dbo.table ' 

Open in new window

but that would no longer have the need of using dynamic SQL ...
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now