Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69

    Question Unanswered: Linked 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,

    chris

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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?
    Yup, you are transfering all the records and THEN filtering, try:

    Code:
    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:

    Code:
    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.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69
    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 ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •