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
Solved

SQL openquery and quotes

Posted on 2011-03-22
9
702 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
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.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

856 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