Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1312
  • Last Modified:

SQL SErver: Openquery

Not able to to perform openquery with criteria.
****************************************************
declare @CDN nvarchar(500)
declare @sql varchar(400)
set @CDN = 'CDN: 3785'
set @sql = 'select * from openquery(SERVER,
''Select * from TABLEA where Destination = "' + @CDN + '"'')'
exec (@sql)
********************************************************
This does work however...I am trying to combine them both.
select * from openquery (SERVER,
'Select * from TABLEA
where len(EventData) > 12')

0
TechMonster
Asked:
TechMonster
  • 7
  • 6
1 Solution
 
SQL_SERVER_DBACommented:
set @sql = 'select * from openquery([SERVER],
''Select * from TABLEA where Destination = '" & @CDN & "'")'
0
 
TechMonsterAuthor Commented:
I get
 Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ' & @CDN & '.
0
 
Chris MangusDatabase AdministratorCommented:
declare @CDN nvarchar(500)
declare @sql varchar(400)
set @CDN = 'CDN: 3785'
set @sql = 'select * from openquery(SERVER,
''Select * from TABLEA where Destination = "' + @CDN + ''''')'''
exec (@sql)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TechMonsterAuthor Commented:
Cmangus:  Get error,,,

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'Select * from dbo.eCallByCallStat20071012_SCCS_DCF where Destination = "CDN: 3785')'.
0
 
Chris MangusDatabase AdministratorCommented:
Did you copy and paste what I posted?  The error message indicates that you missed some of the single quotes I posted.
0
 
TechMonsterAuthor Commented:
Yeah,,I copied and pasted...seems that I needed to include a port number...I believe the code you send works ok......
I still have an issue with inserting a variable for part of a table name.
my reportDate returns 20071016  which is what I need in my table...can you help..?
********************************************************************************************************
declare @date nvarchar(8), @ReportDate nvarchar(8), @sqlstring nvarchar(4000)

set @date =  convert(nvarchar(10),getdate(),112)
set @ReportDate = convert(varchar(8), dateadd(day,-1, convert(datetime, @date , 112)) , 112)
select @date
select @ReportDate
select * from openquery([SERVER, PORT#], 'Select * from .dbo.eCallByCallStat' + @ReportDate + '_SCCS_DCF
                                                                  where Destination = ''CDN: 3785''
                                                                        and len(EventData) > ''12''')
0
 
Chris MangusDatabase AdministratorCommented:
Is your table actually named dbo.eCallByCallStat20071016_SCCS_DCF?

You also have an extra period in front of dbo.eCallByCallStat.

declare @date nvarchar(8), @ReportDate nvarchar(8), @sqlstring nvarchar(4000)

set @date =  convert(nvarchar(10),getdate(),112)
set @ReportDate = convert(varchar(8), dateadd(day,-1, convert(datetime, @date , 112)) , 112)
--select @date
--select @ReportDate
Set @sqlstring = '
select * from openquery([SERVER, PORT#], ''Select * from dbo.eCallByCallStat' + @ReportDate + '_SCCS_DCF
                                                                  where Destination = ''CDN: 3785''
                                                                        and len(EventData) > ''12'''')'
Exec (@sqlstring)
0
 
TechMonsterAuthor Commented:
Yes...Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'CDN:'.
0
 
Chris MangusDatabase AdministratorCommented:
Ok...we don't have it delimited right...try:

declare @date nvarchar(8), @ReportDate nvarchar(8), @sqlstring nvarchar(4000)

set @date =  convert(nvarchar(10),getdate(),112)
set @ReportDate = convert(varchar(8), dateadd(day,-1, convert(datetime, @date , 112)) , 112)
--select @date
--select @ReportDate
Set @sqlstring = '
select * from openquery([SERVER, PORT#], ''Select * from .dbo.eCallByCallStat' + @ReportDate + '_SCCS_DCF
                                                                  where Destination = ''''CDN: 3785''''
                                                                        and len(EventData) > ''''12'''')'''
Exec (@sqlstring)
0
 
Chris MangusDatabase AdministratorCommented:
Oops...forgot to remove the extra period in front of your table owner...try:

declare @date nvarchar(8), @ReportDate nvarchar(8), @sqlstring nvarchar(4000)

set @date =  convert(nvarchar(10),getdate(),112)
set @ReportDate = convert(varchar(8), dateadd(day,-1, convert(datetime, @date , 112)) , 112)
--select @date
--select @ReportDate
Set @sqlstring = '
select * from openquery([SERVER, PORT#], ''Select * from dbo.eCallByCallStat' + @ReportDate + '_SCCS_DCF
                                                                  where Destination = ''''CDN: 3785''''
                                                                        and len(EventData) > ''''12'''')'''
Exec (@sqlstring)
0
 
TechMonsterAuthor Commented:
Still no go...How come you need so many little astericks before and after each variable?  
I know that if the variable is text than it takes 2 single astericks...Are those maybe quotes?
0
 
Chris MangusDatabase AdministratorCommented:
Ok...here's another version.  I had the delimiters wrong again.  You need all the single quote marks so you can properly delimit the string to run with OPENQUERY because an OPENQUERY statement can't take dynamic variable data.  

Everything I've posted should be single quotes, no double quotes.  So, if you PRINT @sqlstring instead of EXEC'ing it, it should read:

select * from openquery([SERVER, PORT#], 'Select * from dbo.eCallByCallStat20071016_SCCS_DCF
                                                                  where Destination = ''CDN: 3785''
                                                                        and len(EventData) > ''12'')

declare @date nvarchar(8), @ReportDate nvarchar(8), @sqlstring nvarchar(4000)

set @date =  convert(nvarchar(10),getdate(),112)
set @ReportDate = convert(varchar(8), dateadd(day,-1, convert(datetime, @date , 112)) , 112)
--select @date
--select @ReportDate
Set @sqlstring = '
select * from openquery([SERVER, PORT#], ''Select * from dbo.eCallByCallStat' + @ReportDate + '_SCCS_DCF
                                                                  where Destination = ''''CDN: 3785''''
                                                                        and len(EventData) > ''''12'''')'
Exec (@sqlstring)
0
 
TechMonsterAuthor Commented:
Error>>>>>
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string 'Select * from dbo.eCallByCallStat20071017_SCCS_DCF
                                                                  where Destination = 'CDN: 3785'
                                                                        and len(EventData) > '12')'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'Select * from dbo.eCallByCallStat20071017_SCCS_DCF
                                                                  where Dest'
0
 
Chris MangusDatabase AdministratorCommented:
If you do a PRINT on @sqlstring instead of EXEC, what is the output?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now