insert into temp using exec - 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 2000 Forums

Transact-SQL (2000)

insert into temp using exec

Author

Topic

ebee
Starting Member

2 Posts

Posted - 2006-04-28 : 16:59:48

The SP below works but when used to insert to a temp table, I am getting this error :

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

How do you insert into temp using exec. . or is this possible at all?

thank you
Erwin

==============================================================
create procedure sp_occ
@lname char(50)
, @fname char(50)
as
DECLARE
@TSQL as varchar(8000)

SELECT @TSQL = 'SELECT OccupantId FROM OPENQUERY(sacdev_cmaddress,''SELECT * FROM pub.occupant
WHERE lastname = ''''' + @lname + '''''
and firstname = ''''' + @fname + ''''''')'
EXEC (@TSQL)

==================================================================

Create table #tmp
( OccupantID int
)

insert into #tmp(occupantid)
exec sp_occ @lname = 'Smith', @fname ='Robert'

Erwin

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-28 : 17:50:11

The problem here doesn't have anything to do with the temp table. The problem is the insert based on the results of a call to a different server. (distributed transaction - dependant implicit transactions on different servers).

There is a way to configure both servers to correct this but I don't recall what you need to do for that. Something about having both server agent accounts the same name and some other stuff...I don't remember.

However, one solution could be pretty easy. Does your SP return just a single OccupantID? If so, here is one possibility:

create a sproc on sacdev_cmaddress that returns the occupantid as an output parameter. Then make the remote procedure call to get the output value as a local parameter then insert that to your table. That method isolates the 2 transactions. However if your open query call can sometimes return multiple values then this won't work.

Be One with the Optimizer
TG

ebee
Starting Member

2 Posts

Posted - 2006-04-28 : 18:27:27

no. . it is possible that it will return multiple records. . which is then used to create another temp table. There are going to be joins for the final table. Since my data source is a linked server. I had to retrieve records in pieces for spead. Unless there is a way to join them now and still have acceptable performance.

Erwin

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-28 : 23:46:30

I'm not sure if this will help, but try your stored procedure like this:


create procedure sp_occ
	@lname char(50) ,
	@fname char(50)
as
DECLARE @TSQL as varchar(8000)

Create table #tmp_2 ( OccupantID int )

SELECT @TSQL = 
'
insert into #tmp_2 ( OccupantID )
SELECT OccupantId 
FROM 
OPENQUERY(sacdev_cmaddress,''SELECT * FROM pub.occupant 
WHERE lastname = ''''' + @lname + ''''' 
and firstname = ''''' + @fname + ''''''')'

EXEC (@TSQL)

select * from #tmp_2

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-04-29 : 02:10:20

Death by single quotes!

We tend to construct the inner SQL first, then use REPLACE to double up the quote, then add the outer wrapper. Depends on how complicated the query is of course ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources