SQL Query and Temp table - SQL Server Forums

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)

SQL Query and Temp table

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2007-11-19 : 13:37:58

I have a linked server set up to a db2 data base. We are running MS Sequel Server 2005.

1. If I run the following everything works fine and resulset is displayed:

Exec ('Call QGPL.get_companies (?,?)', o_drky, o_drdl01) AT AS400SRV_MSDASQL

2. When I try to get the result into a temp table the query is just running and never stops. I manually have to abort. Any ideas?

drop table #temptable
create table #tempTable (o_drky char(10), o_drdl01 char(50))
insert into #temptable (o_drky, o_drdl01) Exec ('Call QGPL.get_companies (?,?)', o_drky, o_drdl01) AT AS400SRV_MSDASQL
select * from #temptable

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 15:00:51

the problem could be due to locking since a distributed transaction is required.
how long does the exec take to return the full resultset?

have you tried using a form of openrowset?
select * from openquery(as400srv_msdasql, 'qgpl.get_companies')

have you tried not making the table temporary (#)?
create table myTable (o_drky char(10), o_drdl01 char(50))

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2007-11-19 : 15:08:48

The run time is approx 1 second.

Tried creating the table not as a temp table. Still same problem.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 15:23:00

did you create the table is a different batch...
create table xxx
go
insert xxx
exec
go

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2007-11-19 : 16:32:54

This is what I did:

drop table mytable
create table mytable (o_drky char(10), o_drdl01 char(50))
insert into mytable (o_drky, o_drdl01) Exec ('Call QGPL.get_companies (?,?)', o_drky, o_drdl01) AT AS400SRV_MSDASQL
select * from mytable

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 16:39:28

try not interweaving the DDL and DML...
drop table mytable
go
create table mytable (o_drky char(10), o_drdl01 char(50))
go
insert into mytable (o_drky, o_drdl01) Exec ('Call QGPL.get_companies (?,?)', o_drky, o_drdl01) AT AS400SRV_MSDASQL
go
select * from mytable
go

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2007-11-20 : 09:00:57

I tried to execute each "go" segment individually. When it gets to the "insert into..." it takes forevever and I have to manually abort the query, it tells me "Executing query ...." but never stops. When I run the "Exec ('Call...." by itself, it returns 26908 records in less a second and shows the result. Any ideas why table is not updated.

Here is my sp on the db2:

create procedure get_companies
result set 1
language sql
reads sql data
begin
declare c1 scroll cursor with return for
select drky, drdl01 from vgiprdcom/f0005;
open c1;
set result sets cursor c1;
end;

Here is my current code on the sql server:

drop table mytable
go
create table mytable (drky char(10), drdl01 char(50))
go
insert into mytable (drky, drdl01) Exec ('Call QGPL.get_companies', drky, drdl01) AT AS400SRV_MSDASQL
go
select * from mytable
go

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 12:55:28

You're running a SELECT INSERT using a cursor method - I don't think this will ever work and even if it does, you may havenoticed it's crazy slow-ness!

Try this instead.
[CODE]
INSERT INTO myTable(drky, drdl01)
SELECT drky
, drdl01
FROM vgiprdcom
[/CODE]

George
<3Engaged!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources