INSERT-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 2005 Forums

SQL Server Administration (2005)

INSERT-EXEC

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 20:11:49

I need get the o/p of a system sp into a table. I am doing the following,

insert #repl_monitor
exec [distribution].sys.sp_replmonitorhelpsubscription @publisher = 
N'FGRWA0508', @publisher_db = N'DB_Name', @publication = N'publication'

Code is really not important. Any sys SP can replace the above code.

I am getting the following error
Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested.

I have seen the following link which discuss this issue,
http://www.sommarskog.se/share_data.html
But there is no solution there.

I tried with sp_executesql and EXEC(), but unable to get the result. Can anyone put some light?

------------------------
I think, therefore I am - Rene Descartes

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-19 : 21:29:42

You can try something like this:


use tempdb
go

select
	a.*
into
	MyTemp
from
	openrowset('SQLOLEDB','SERVER=(local);Trusted_Connection=yes;',
	'
	SET FMTONLY OFF;
	EXEC SP_HELPDB
	') a

select * from MyTemp

drop table MyTemp

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-19 : 23:23:29

any proc that uses insert-exec can't be called with insert-exec. apparently sp_replmonitorhelpsubscription uses insert-exec, so your call fails.

MVJ's workaround above is the only one I know of.

www.elsasoft.org

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-23 : 12:19:40

[quote]Originally posted by Michael Valentine Jones

You can try something like this:


use tempdb
go

select
	a.*
into
	MyTemp
from
	openrowset('SQLOLEDB','SERVER=(local);Trusted_Connection=yes;',
	'
	SET FMTONLY OFF;
	EXEC SP_HELPDB
	') a

select * from MyTemp

drop table MyTemp

CODO ERGO SUM
[/quote

Thank you MVJ! That helped. Great code!

------------------------
I think, therefore I am - Rene Descartes

rsbutterfly16
Starting Member

6 Posts

Posted - 2010-01-28 : 17:14:23

I tried this but get the following error:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].

dbapac999
Starting Member

4 Posts

Posted - 2010-07-14 : 16:47:24

I am trying to insert the result set returned from a stored proc into a runtime table (or temp table or cursor, does not matter), code snippet and error follows:

Insert @pub_state
exec sys.sp_replmonitorhelppublication @publisher = 'pcctdbcwt', @publication = 'CAS_PAC_Meters'

error message:
Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested.

I have seen several posts that say the way to deal with this is to SET FMTONLY OFF doing something like this:

select * from
openrowset('SQLOLEDB','pcctdbcwt';'UID’;'PWD',
'
SET FMTONLY OFF;
EXEC SP_HELPDB
')
This works and the result set from the SP is returned. However, if I try this:
select * from
openrowset('SQLOLEDB','pcctdbcwt';'UID';'PWD',
'
SET FMTONLY OFF;
EXEC sys.sp_replmonitorhelppublication @publisher = 'pcctdbcwt', @publication = 'CAS_PAC_Meters')'

It starts complaining about the systax of the SP parameters such as:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'pcctdbcwt'.

I believe the actual issue here is that this ends up being a quoted literal string:
'
SET FMTONLY OFF;
EXEC sys.sp_replmonitorhelppublication @publisher = 'pcctdbcwt', @publication = 'CAS_PAC_Meters')

And thus the SP parameters never evaluate to the supplied values. I just can’t see a way around this…

Bottom line seems to be the openrowset-SET FMTONLY OFF technique will not work with a SP requiring parameters.
I just cannot seem to find a way to get the results of this SP into a table!!! Any help GREATLY appreciated.

rdjabarov
Starting Member

8 Posts

Posted - 2010-07-15 : 15:12:02

Make sure your OPENROWSET contains 3 parameters, and each apostrophe needs to be escaped by another apostrophe.

"The data in a record depends on the Key to the record, the Whole Key, and nothing but the Key, so help me Codd."

dbapac999
Starting Member

4 Posts

Posted - 2010-07-15 : 19:16:41

Thanks but I've tried that and still can't get it to work.

Hany2100
Starting Member

1 Post

Posted - 2011-01-06 : 09:34:09

restriction of using sql-exec

1- It can't nest. If some_sp tries to call some_other_sp with INSERT-EXEC, you will get an error message. Thus, you can only have one INSERT-EXEC active at a time. This is a restriction in SQL Server.

2- There is a serious maintenance problem. If someone changes the result set in the callee, the INSERT-EXEC statement will fail, because the column list must match the INSERT statement exactly. Keep in mind that the person who changes the callee may not even be aware of that you are calling it with INSERT-EXEC. In SQL 2008, this can be handled by using a table type, as I will discuss in the next section.

3- The procedure is executed in the context of a transaction. Since the procedure is called as part of an INSERT statement, there will always be an open transaction when the procedure is executed. This is generally not a cause for any greater concern, but occasionally it can cause surprises when you call system procedures. It can also be a hassle if you call a procedure in a linked server and you get errors about distributed transactions.

4- A ROLLBACK aborts the batch. If the called procedure issues a ROLLBACK statement, you get an error message saying you cannot do this. The transaction is rolled back, though. (Since the batch is aborted.) Warning: on SQL 6.5, this is a lot worse. When I made an experiment, my SQL Server process crashed, and when I reconnected, tempdb, where I had run the test, was corrupted.

5- INSERT-EXEC does not start an implicit transaction on some SQL Server versions. This applies only if: 1) you have SQL 2000 SP3 or earlier or SQL 7, and 2) you run with SET IMPLICIT_TRANSACTIONS ON. With this setting, an INSERT statement should start a transaction, but because of a bug this does not happen with INSERT-EXEC. This bug is fixed in SQL 2000 SP4 and SQL 2005. It is not present in SQL 6.5.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources