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 ;-)

server via ODBC and stored procedure

Hi

We're trying to use call a stored procedure to update information in a
remote Ingres database. We've linked the server, and can read
information using SELECT * FROM OPENQUERY (........), but we can't
find a suitable syntax for executing a procedure.

Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
string gives a message about not returning any columns - not surprising
as there aren't any, and trying to execute the procedure more directly
using:-

EXECUTE abrs..vipdba.ats_reader_pi0 ......

Gives the error

Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
[OLE/DB provider returned message: Parameter type cannot be determined
for at least one variant parameter.]

Any bright ideas?

Chloe(chloe.crowder@.bl.uk) writes:
> We're trying to use call a stored procedure to update information in a
> remote Ingres database. We've linked the server, and can read
> information using SELECT * FROM OPENQUERY (........), but we can't
> find a suitable syntax for executing a procedure.
> Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
> string gives a message about not returning any columns - not surprising
> as there aren't any, and trying to execute the procedure more directly
> using:-
> EXECUTE abrs..vipdba.ats_reader_pi0 ......
> Gives the error
> Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
> [OLE/DB provider returned message: Parameter type cannot be determined
> for at least one variant parameter.]

Assuming that you are on SQL 2000:

Does the procedure have any "difficult" parameters?

First of all, I would examine whether there is an OLE DB provider
for Ingres, rather than using the MSDASQL provider.

If there is no OLE DB provider available, I would first try a parameterless
stored procedure. If this fails, then it seems that the ODBC driver
have problems to retrieve parameter information at all.

If there is a tool similar to Profiler on the Ingres side, you could
use that to see what calls the ODBC driver makes.

One thing that looks suspicious to me is that the third component is
empty, but I don't know Ingres, so this may be alright.

Unfortuantely, linked servers to other products can be a bit of trial
and error. There is a generic OLE DB layer which you have little control
over.

If you are on SQL 2005, there may be an easy way out. To wit you
can say:

EXEC('ingres-SQL here') AT abrs

to send a pass-through query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx