Solved

SQL openquery and quotes

Posted on 2011-03-22
9
701 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 69

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating SQL script using SQL data and SQL script 8 37
SQL View nearest date 5 36
Tsql query 6 21
Deal with apostrophe in stored procedures 8 42
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 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