asked
Problem with querying a linked server
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
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.
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
