Insert Into using Exec('Call....)

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)

Insert Into using Exec('Call....)

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-03-20 : 12:08:18

Running the query from the query analyzer (2005), the query never finishes. I have distributed transaction turned on. If I comment out the "Insert into...", I get result in less than a second (555 records). It uses a linked server to db2/400. I have the same problem if I use a linked server to sequel 2000. Any ideas?

declare @JobNumber1 char(12)
declare @ItemNumber1 char(8)
declare @JdeDateFrom int
declare @JdeDateTo int 
set @JobNumber1 = '     3505040'
set @ItemNumber1 = '01010    ' 
set @JdeDateFrom = 0
set @JdeDateTo = 0

If Object_ID('tempdb..#JDE_LaborHrsDetailTable1') Is Not Null    Drop Table #JDE_LaborHrsDetailTable1

create table	#JDE_LaborHrsDetailTable1
		(  jde_job_date datetime,
           jde_cost_code char(15),
		   jde_equipment_id char(10),
		   jde_equipment_name char(20),
           jde_employee_name char(15),
           jde_employee_hours decimal(8,2),
           jde_equipment_hours decimal(8,2) 
		   
                                           )

Set NoCount On

insert into #JDE_LaborHrsDetailTable1(jde_job_date, jde_cost_code, jde_equipment_id, jde_equipment_name,
                                   jde_employee_name, jde_employee_hours, jde_equipment_hours)
Exec ('Call QGPL.get_labdet(?,?,?,?)', @JobNumber1, @ItemNumber1, @JdeDateFrom, @JdeDateTo) AT AS400SRV_MSDASQL1

select * from #JDE_LaborHrsDetailTable1 order by jde_job_date

drop table #JDE_LaborHrsDetailTable1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 12:10:58

try using like...

SELECT * FROM OPENROWSET(AS400SRV_MSDASQL1,...,'EXEC QGPL.get_labdet @JobNumber1, @ItemNumber1, @JdeDateFrom, @JdeDateTo')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 12:12:12

see syntax of openrowset below

http://doc.ddart.net/mssql/sql70/oa-oz_5.htm

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-03-20 : 12:56:16

Getting: Incorrect syntax near '.'.

Do I need to include the "Call" since I'm calling a sp on the db2?

SELECT * FROM OPENROWSET(AS400SRV_MSDASQL1,...,'EXEC QGPL.get_labdet @JobNumber1, @ItemNumber1, @JdeDateFrom, @JdeDateTo')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 13:06:33

dont put ... fill it with relevant info as in link

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-21 : 04:25:18

Also refer http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-03-27 : 10:38:29

Tried this:

insert into #JDE_EqmTable
SELECT *
FROM OPENROWSET('AS400SRV_MSDASQL','Data Source=AS400s400;Trusted_Connection=yes; 
         Integrated Security=SSPI','EXEC QGPL.get_labdet @JobNumber1, @ItemNumber1, @JdeDateFrom, @JdeDateTo')

Error:

The OLE DB provider "AS400SRV_MSDASQL" has not been registered.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources