Open Query Syntax with Nested Query

[arodri]

Hello,

I am trying to create a stored procedure that pulls data from a linked server. I've done this succesfully multiple times before but I've never had to do it to a nested query and I've tried for a really long time and can't seem to get the syntax correct. Any suggestions would be greatly appreciated! Here is the syntax I'm using:

@BeginDate           VarChar(12),
@EndDate            VarChar(12)

AS

BEGIN
 
declare @sql varchar(max)
set @sql='


 SELECT  Site,    
                      LogDate,
                      Item, 
                      Qty, 
                      Val, 
                      Amt



FROM OPENQUERY
            ( 
            LinkedServerName, ''
            
  B.STATS.SITE as Site,    
  B.STATS.LOGDATE as LogDate,
  B.ITEMS.ITEM as Item, 
  B.ITEMS.QTY as Qty, 
  B.ITEMS.VAL as Val, 
  B.ITEMS.AMT as Amt


            
From             

( 
            SELECT    STATS.SITE, 
                      STATS.SALEID, 
                      STATS.LOGDATE,
                      ITEMS.ITEM, 
                      ITEMS.QTY, 
                      ITEMS.VAL, 
                      ITEMS.AMT 

FROM STATS INNER JOIN ITEMS ON (STATS.SALEID = ITEMS.SALEID) AND (STATS.SITE = ITEMS.SITE)
WHERE (STATS.LOGDATE BETWEEN (''''' + CONVERT(char(10), @BeginDate, 101) + ''''') AND (''''' + CONVERT(char(10), @EndDate, 101) + ''''') ) 
AND items.item = ''''721134''''

) A

,

(

            
 SELECT            STATS.SITE as Site, 
                   STATS.SALEID as SaleID, 
                   STATS.LOGDATE as LogDate,
                   ITEMS.ITEM as Item, 
                   ITEMS.QTY as Qty, 
                   ITEMS.VAL as Val, 
                   ITEMS.AMT as Amt

FROM STATS INNER JOIN ITEMS ON (STATS.SALEID = ITEMS.SALEID) AND (STATS.SITE = ITEMS.SITE)
WHERE (STATS.LOGDATE BETWEEN (''''' + CONVERT(char(10), @BeginDate, 101) + ''''') AND (''''' + CONVERT(char(10), @EndDate, 101) + ''''') ) 

)B

Where

A.Site = B.site  and A.Saleid = B.saleid

 '')' 
 
Exec(@sql)
END

 

[markros]

Why do you need to execute OpenQuery inside the Dynamic Query?
Can you do this using just OpenQuery?

PluralSight Learning Library

 

[arodri]

All of our other stored procedures that we've done with a linked server have the dynamic query, and they've worked fine. So I'm following that format. The only thing that I change is the select statement for the Open Query.

 

[arodri]

I don't think that's the issue- it just seems like a syntax error beginning from the first Select Statement.

 

[markros]

I would start from using non dynamic OpenQuery and then convert to dynamic if needed.

PluralSight Learning Library

 

[arodri]

If you just look at the Select statements and open query syntax, is there anything that sticks out as incorrect?

 

[markros]

Try PRINT @SQL and then you'll be able to spot errors.
---------------
BTW, I wrote a longer reply a while back, but somehow it didn't get submitted.

PluralSight Learning Library

 

[arodri]

This is the only error message I get:

OLE DB provider "MSDASQL" for linked server "Sitewatch" returned message "[ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 4, char 1
(".

 

[markros]

Have you printed the SQL as I asked you? If so, what is the output? In other words, comment out execute (@SQL) and do instead

PRINT @SQL and then post output form the Messages pane.

PluralSight Learning Library

 

[arodri]

Hello,

I did what you asked, and here is what I get as my error message:

Msg 102, Level 15, State 1, Procedure vspUpsales, Line 35
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure vspUpsales, Line 46
Incorrect syntax near '721134'.
Msg 102, Level 15, State 1, Procedure vspUpsales, Line 66
Incorrect syntax near 'B'.

 

[markros]

Before attempting a dynamic SQL, write a SQL Statement itself first and test it.

Second step will be to add first round of extra single quotes and test it with execute @SQL.

Third step will be to double single quote one more time as you're doing dynamic SQL from OpenQuery.

PluralSight Learning Library

 

[arodri]

Hello,

That's a good idea- thank you so much. The problem is I can't get the syntax correct with just the SQL Statment itself. I'm really hoping I can get some input on that because I'm totally stuck. I'm not sure if I'm doing something wrong with the table aliases? Here is what I've been playing around with:

Select B.Site,    
                      B.LogDate,
                      B.Item, 
                      B.Qty, 
                      B.Val, 
                      B.Amt



FROM OPENQUERY
            ( 
            Sitewatch, ''
                     

( 
            SELECT    STATS.SITE, 
                      STATS.SALEID, 
                      STATS.LOGDATE,
                      ITEMS.ITEM, 
                      ITEMS.QTY, 
                      ITEMS.VAL, 
                      ITEMS.AMT 

FROM STATS INNER JOIN ITEMS ON (STATS.SALEID = ITEMS.SALEID) AND (STATS.SITE = ITEMS.SITE)
 
WHERE items.item = '721134'

) A 

,

(

            
            SELECT            STATS.SITE, 
                              STATS.SALEID, 
                              STATS.LOGDATE,
                      ITEMS.ITEM, 
                      ITEMS.QTY, 
                      ITEMS.VAL, 
                      ITEMS.AMT

FROM STATS INNER JOIN ITEMS ON (STATS.SALEID = ITEMS.SALEID) AND (STATS.SITE = ITEMS.SITE)

) B

Where

A.Site = B.site  and A.Saleid = B.saleid

 '')

Here is the error message I'm getting:

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'A'.
Msg 102, Level 15, State 1, Line 50
Incorrect syntax near 'B'.

 

[markros]

Try:

Select *



FROM OPENQUERY
            ( 
            Sitewatch, '
                     

 
            SELECT    STATS.SITE, 
                      STATS.SALEID, 
                      STATS.LOGDATE,
                      ITEMS.ITEM, 
                      ITEMS.QTY, 
                      ITEMS.VAL, 
                      ITEMS.AMT 

FROM STATS INNER JOIN ITEMS ON STATS.SALEID = ITEMS.SALEID AND STATS.SITE = ITEMS.SITE
 
WHERE items.item = ''721134''')

What I meant is - get the query that is referenced in the OpenQuery to work first (on that linked server). Then put it in the OpenQuery - in quotes. Then, if you still need to use dynamic SQL for OpenQuery, then try to not get confused in the quotes.

See this BOL reference about OpenQuery
BOL OpenQuery




PluralSight Learning Library

 

[arodri]

Hi,

Right- I think I undersand what you're saying. That was what I did when first starting this stored procedure. I got my queries to work fine on their own with OpenQuery and even on their own with dynamic SQL for Open Query (got the right amount of quotes) and I get the results that I need. The problem is when I try to put them together to form a nested select statment with table aliases (my very first post). That's where I'm running into syntax errors. Alone they work fine.

 

[markros]

Can you get the complex query (with derived tables) to work itself on your linked server? Start with the whole query first.

PluralSight Learning Library