Problem with querying a linked server

Question

Hi, I've a SQL Express 2008 32-bit database running on a WIndows Server 2008 R2 64-bit virtual machine. I've got a linked server to a Progress V9.10E database, and I'd like to set up a db view on the SQL server. I've already done this on one of the smaller Progress tables and it works OK. However, when I try to query a large table, the query just runs and runs and doesn't return the data like it should. I'm trying to use Openquery to improve things, but I have problems with the quotation marks, I think.

Here's my stored procedure code:

@SQL1 varchar(500) = '', 
@SQL1a varchar(500) = '', 
@SQL1b varchar(500) = '', 
@SQL2 varchar(4000) = '',
@Warehouse varchar(4) = "GC",
@TrxType varchar(3) = "D"

SET @SQL1 = '''
SELECT a."chamber-code", 
a."receipt-date", 
a."gen-dec", 
b."fromto-cust"
FROM INDEXREADONLY..PUB.Entryheader AS a 
INNER JOIN INDEXREADONLY..PUB.Booking AS b ON a."booking-no" = b."booking-no" 
WHERE a.warehouse = '''

SET @SQL1a = ''' AND a."Trx-no" > 100000 AND a."Trx-type" = '''
SET @SQL1b = ''' AND a.Confirmed = 0'''
SET @SQL2 = 'SELECT * FROM OPENQUERY(INDEXREADONLY, '+ @SQL1 + @Warehouse + 
                                             @SQL1a + @TrxType + @SQL1b +')'
PRINT @SQL1
PRINT @SQL2

EXEC(@SQL2)

I broke the query statement into bits trying to overcome problems, but no luck. The query runs absolutely fine on the Progress DB itself, but here is the SQL output:

'SELECT a."chamber-code", 
a."receipt-date", 
a."gen-dec", 
b."fromto-cust" 
FROM INDEXREADONLY..PUB.Entryheader AS a 
INNER JOIN INDEXREADONLY..PUB.Booking AS b ON a."booking-no" = b."booking-no" 
WHERE a.warehouse = '

SELECT * 
FROM OPENQUERY(INDEXREADONLY, 'SELECT a."chamber-code",
                                      a."receipt-date", 
                                      a."gen-dec", b."fromto-cust" 
                                      FROM INDEXREADONLY..PUB.Entryheader AS a 
                                      INNER JOIN INDEXREADONLY..PUB.Booking AS b ON a."booking-no" = b."booking-no" 
                                      WHERE a.warehouse = 'GC' 
                                      AND a."Trx-no" > 100000 
                                      AND a."Trx-type" = 'D' 
                                      AND a.Confirmed = 0')
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'GC'.

Any help will be welcome! Thanks, Steve

Answer 1

0

The problem here is the string concatenation. You are missing quote marks around GC and D.

Take a look at the query that results from the command PRINT @SQL2. If you copy that into SSMS you will see that the last line looks like this:

WHERE a.warehouse = 'GC' AND a."Trx-no" > 100000 
  AND a."Trx-type" = 'D' AND a.Confirmed = 0')

Now, as you are wanting this to be one long text, you need to double-up the quote marks for GC and D, so that when they are added to this long string, they get the quote-marks that they require.

This is done by changing the code to set @SQL2 to the following:

SET @SQL2 = 'SELECT * 
             FROM OPENQUERY(INDEXREADONLY, '+ @SQL1 +
             QUOTENAME(@Warehouse,'''') + @SQL1a +
             QUOTENAME(@TrxType,'''') + @SQL1b +')'

Here you use the function QUOTENAME() and tell it to pack the variables @Warehouse and @TrxType inside quote-marks. The resulting code should then work.

answered Oct 01 '10 at 01:40 AM

WilliamD
25.3k ● 16 ● 18 ● 41

Hi William,

Thanks for your help. That is great, it works. I did have to change something else, I didn't realise that until I used your code. I was using the four part name INDEXREADONLY..PUB.Entryheader but didn't need to since the linked server name is in the Openquery code.

I was unaware of the QUOTENAME function, so thanks again!

Steve

Oct 01 '10 at 02:12 AM steveblakemore

(comments are locked)

Problem with querying a linked server

Follow this question

Related Questions