Hi,
I've got a Progress V9.10E database running on a Windows Server 2003 R2 machine. I've set up a linked server to this on a SQL Express 2008 32-bit database running on a WIndows Server 2008 R2 64-bit virtual machine.
I'd like to set up a db view on the SQL server, so I can use the data in another application. 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 in any reasonable time. I'm trying to use Openquery to improve things, but I have problems with the quotation marks, I think.
Here's my SQL 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 @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 using SQL Explorer, but I get an error from SQL. Here is the SQL output:
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
I've got a Progress V9.10E database running on a Windows Server 2003 R2 machine. I've set up a linked server to this on a SQL Express 2008 32-bit database running on a WIndows Server 2008 R2 64-bit virtual machine.
I'd like to set up a db view on the SQL server, so I can use the data in another application. 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 in any reasonable time. I'm trying to use Openquery to improve things, but I have problems with the quotation marks, I think.
Here's my SQL 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 @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 using SQL Explorer, but I get an error from SQL. Here is the SQL output:
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
