RE: An insert exec statement cannot be nested. Pls HELP.

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: BlueDolphin (BlueDolphin_at_discussions.microsoft.com)
Date: 11/04/04 

Date: Thu, 4 Nov 2004 01:44:01 -0800

Hello David,
Please ignore my previous message, I managed to settle the
sp_addlinkedserver issue in the stored proc.

However, another problem arise which I can't figure out what is wrong.
The error encountered is as indicated below:
Server: Msg 911, Level 16, State 1, Line 41
Could not locate entry in sysdatabases for database 'SELECT * FROM
OPENQUERY(LOCALSERVER,'SET NOCOUNT ON SET FMTONLY OFF EXEC DATA'. No entry
found with that name. Make sure that the name is entered correctly.

I'm trying to use OPENQUERY with a dynamic sql string so that i can pass in
a variable to the stored proc that I'm calling.

My stored proc is as shown below:

CREATE PROCEDURE GetList
        @PID varchar(20)
AS
DECLARE @CmdString varchar(1000)
DECLARE @remotesql nvarchar(4000)

SET @remotesql = 'SET NOCOUNT ON SET FMTONLY OFF EXEC
DATA.dbo.EMRX_Retrieve_Test 4, ' + dbo.quotestring(@PID) + ', NULL'

SET @CmdString = 'SELECT * FROM OPENQUERY(LOCALSERVER,' +
dbo.quotestring(@remotesql) + ')'

IF (Select count(*) From OptOutPatient where patientid = @PID) = 0
BEGIN
        CREATE TABLE #Temp_List
        (
                ID numeric,
                StartDtm varchar(32),
                FacilityCode varchar(10)
        )

        SET ANSI_NULLS ON
        SET ANSI_WARNINGS ON

        IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'LOCALSERVER')
        EXEC sp_dropserver LOCALSERVER, 'droplogins'

        EXEC sp_addlinkedserver @server='LOCALSERVER', @srvproduct='',
@provider='SQLOLEDB', @datasrc=@@servername

        INSERT INTO #Temp_List (ID, StartDtm, FacilityCode)
        EXEC @CmdString

        SELECT CONVERT(datetime, CONVERT(char(10), StartDtm, 101), 101) AS
StartDtm, FacilityCode
        FROM #Temp_List
        GROUP BY CONVERT(datetime, CONVERT(char(10), StartDtm, 101), 101),
FacilityCode
        ORDER BY CONVERT(datetime, CONVERT(char(10), StartDtm, 101), 101) DESC

        DROP TABLE #Temp_List

        EXEC sp_dropserver LOCALSERVER, 'droplogins'

END
ELSE
BEGIN
        RETURN 1
END
GO

The CmdString value when selected out looks like this :

SELECT * FROM OPENQUERY(LOCALSERVER,'SET NOCOUNT ON SET FMTONLY OFF EXEC
DATA.dbo.EMRX_Retrieve_Test 4, ''S4567891A'', NULL')

Could you kindly please advise me what could be the mistake and how to
resolve it?

Much Thanks.

=====================================================
"David Portas" wrote:

> http://www.sommarskog.se/share_data.html
>
> --
> David Portas
> SQL Server MVP
> --
>

Relevant Pages

  • Re: Can I optimize this query?
    ... One of which is using dynamic sql or building the query in the ... > I have written a stored proc that supports a MS access front-end app> which allows to pass 4 or a combo of any four parameters. ... > declare @contracttemp varchar ...
    (microsoft.public.sqlserver.programming)
  • Re: Transactions and SQL
    ... declare @error int ... If in a step I execute another stored proc and> that does not execute properly then does this stored proc catch the error> or do i need to return an error in the second proc? ... > DECLARE @ErrorSave INT ... > ROLLBACK TRAN ...
    (microsoft.public.sqlserver.programming)
  • Show pending merge changes
    ... I have changed Paul Ibison's stored proc a little bit. ... -- Fetch the rowguidcol of the article ... declare @articlename sysname ... FETCH NEXT FROM PBS_cs_mergearticles INTO @articlename ...
    (microsoft.public.sqlserver.replication)
  • Stored Proc date range not producing a RS
    ... When I run the following Stored Proc in SQL Analyzer, ... DECLARE @StartMonth int ... DECLARE @StartDate varchar ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Proc Parameter
    ... Thanks for the reply Alejandro... ... declare an input variable of type TEXT ... >> The data is returned as XML and is processed by a stored proc, ... The only thing I can think of is processing the XML ...
    (microsoft.public.sqlserver.programming)