server, heterogenous query

Hi all!

I have to pull new data from an Ingres Server over to my MSSQL server. I set the Ingres box up as a linked server and I can do the following:

declare @.start as datetime

set @.startdate = (select start from synctimes)

select * from openquery(INGRES, 'select * from ingrestable') where date > @.start

This does exactly what I want in my testsetup. In real life, the two boxen are connected via a slow connection and I wonder if that way *all* records are transferred and filtered on the MSSQL side, thus I transfer 99.5% rubbish. Is that true and ifso, is there any way to get around this?

in addition, I want to acheive this

CREATE PROCEDURE import_all AS
declare @.myVariable as Cursor
declare @.start as datetime
declare @.tblname as nvarchar(50)

declare myCursor Cursor for
SELECT tblname from synctables

open myCursor

set @.myVariable = myCursor

fetch next from @.myvariable into @.tblname
while @.@.fetch_status = 0
begin
select * from openquery(INGRES, 'select * from @.table where date > @.start')
fetch next from @.myVariable into @.tblname
end
close myCursor
deallocate myCursor

Is this possible?

TIA,

chrisI wonder if that way *all* records are transferred and filtered on the MSSQL side, thus I transfer 99.5% rubbish. Is that true and ifso, is there any way to get around this?
Yup, you are transfering all the records and THEN filtering, try:

declare @.startdate as datetime, @.TSQL as varchar(255)
select @.startdate = getdate()
set @.TSQL = 'select * from openquery(INGRES, ''select * from ingrestable where date > ''' + convert(varchar(25),@.startdate,121) + ''')'
print @.TSQL
exec(@.TSQL)

I don't exactly recall what date format Ingres uses but you can adjust the convert as needed.

as for your sp, I think you are on the right track but I see some problems. Consider:

CREATE PROCEDURE import_all(
@.Debug bit = 0)
AS

declare @.start as datetime
, @.tblname as nvarchar(50)
, @.TSQL as varchar(255)

select @.tblname = min(tblname from synctables
while (@.tblname is not null) begin
set @.TSQL = 'select * from openquery(INGRES, ''select * from ' + @.table + ' where date ''' + convert(varchar(25),@.startdate,121) + ''')'
if (@.Debug > 0)
raiserror('%s',0,1,@.TSQL)
else
exec(@.TSQL)
select @.tblname = min(tblname from synctables where tblname > @.tblname
end

return 0

Of course I have no way of test this, on your first run you may want to set @.Debug to 1 to check the resulting code.|||Thanks alot. Seems to be just what I needed. Couldn't test it though because of a defective ingres odbc driver I won't reinstall before monday or tuesday. I'll post my results afterwards ;-)