[Resolved] Issue with insert using linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Issue with insert using linked server

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-08 : 12:12:51

Maybe this is not the right forum, but I will try anyway:

I am using a linked server connecting to db2/400 and running following query:

exec('Select date(digits(decimal(rcdgj + 1900000,7,0)))
       from VGITFRLIB.F551202A')
       AT AS400SRV_MSDASQL

This works fine.

Now I need to insert the result into a temp table and I am having issues:

This is the code I have:

create table	#JDE_ReceiptTable
		(jde_trans_date datetime)

exec('insert into #JDE_ReceiptTable(jde_trans_date)
      select date(digits(decimal(rcdgj + 1900000,7,0)))
      from VGITFRLIB.F551202A')
      AT AS400SRV_MSDASQL

Now I am getting message:
OLE DB provider "MSDASQL" for linked server "AS400SRV_MSDASQL" returned message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - #JDE_RECEIPTTABLE in QGPL type *FILE not found.".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'AS400SRV_MSDASQL'.

I think it tries to find the temp table on the db2/400 when it is created on the server.

I also tried this:

insert into #JDE_ReceiptTable(jde_trans_date)
exec('select date(digits(decimal(rcdgj + 1900000,7,0)))
      from VGITFRLIB.F551202A')
      AT AS400SRV_MSDASQL

Now the query runs and never stops.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-08 : 14:11:08

Got it working this way, using open query:

create table	#JDE_ReceiptTable
		(  
           jde_trans_date datetime,
           jde_ticket_no varchar(20),
           jde_tank_id varchar(16),
           jde_petroex_code varchar(3),
           jde_gross_qty dec(15,2),
           jde_net_qty dec(15,2)
                                     )

insert into #JDE_ReceiptTable(
                              jde_trans_date, 
                              jde_ticket_no, 
                              jde_tank_id, 
                              jde_petroex_code,
                              jde_gross_qty,
                              jde_net_qty 
                                               )
SELECT *
FROM OPENQUERY(AS400SRV_MSDASQL, 'select   date(digits(decimal(rcdgj + 1900000,7,0))) as transdate,
                                           rcodoc as ticketno,
                                           rctank as tankid,
                                           rcpex1 as petroex,
                                           sum(decimal(rcqty * .01, 31, 2)) as gross,
                                           sum(decimal(rcnetg * .01, 31, 2)) as net
                                  from     VGITFRLIB.F551202A
                                  where    rcodoc <> '' ''
                                  group by date(digits(decimal(rcdgj + 1900000,7,0))),
                                           rcodoc,
                                           rctank,
                                           rcpex1')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources