?
Solved

SQL openquery and quotes

Posted on 2011-03-22
9
Medium Priority
?
706 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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