SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 INSERT-EXEC
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/19/2007 :  20:11:49  Show Profile  Reply with Quote
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

Edited by - ravilobo on 04/19/2007 20:15:06

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/19/2007 :  21:29:42  Show Profile  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 04/19/2007 :  23:23:29  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/23/2007 :  12:19:40  Show Profile  Reply with Quote
[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
Go to Top of Page

rsbutterfly16
Starting Member

6 Posts

Posted - 01/28/2010 :  17:14:23  Show Profile  Reply with Quote
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].
Go to Top of Page

dbapac999
Starting Member

4 Posts

Posted - 07/14/2010 :  16:47:24  Show Profile  Reply with Quote
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.






Go to Top of Page

rdjabarov
Starting Member

USA
8 Posts

Posted - 07/15/2010 :  15:12:02  Show Profile  Click to see rdjabarov's MSN Messenger address  Send rdjabarov a Yahoo! Message  Reply with Quote
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."
Go to Top of Page

dbapac999
Starting Member

4 Posts

Posted - 07/15/2010 :  19:16:41  Show Profile  Reply with Quote
Thanks but I've tried that and still can't get it to work.
Go to Top of Page

Hany2100
Starting Member

Egypt
1 Posts

Posted - 01/06/2011 :  09:34:09  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000