Solved

SQL openquery and quotes

Posted on 2011-03-22
9
704 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 70

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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
 
LVL 41

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 41

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 70

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 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