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

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

Date: Wed, 3 Nov 2004 18:34:03 -0800

Hello David,
Thanks for the useful information.

But one more question here:
Before we can use SELECT * FROM OPENQUERY(LOCALSERVER, 'EXEC SP....'),
we need to run the following 3 lines:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
EXEC sp_addlinkedserver @server='LOCALSERVER', @srvproduct='',
@provider='SQLOLEDB', @datasrc=@@servername

Do we put these 3 lines in the stored proc that is running the SELECT
OPENQUERY statement? Or do we just execute the 3 lines manually in query
analyser?

Please advise, cos' it seems like sp_addlinkedserver can't be called twice
and would give error of server already exists if executed a second time in
the stored proc.
Also, after the SELECT OPENQUERY statement, do we have to drop the linked
server LOCALSERVER?

Would it be possible to show an example of handling these 3 activation lines
on how to do this in the stored proc that is trying to call the 2nd stored
proc?

Thanks much for your valuable advise.

"David Portas" wrote:

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

Relevant Pages

  • Re: exec() gives output??
    ... > I'd like to execute a command on the server ... > particular command takes a long time to execute, ... A successful call to exec() replaces the Perl script with whatever execis ...
    (comp.lang.perl.misc)
  • Re: Database Restore on a Linked Server
    ... The select works but the stored proc fails after running for 8 min, ... and the error message i get when i execute the command from primary server ... > would verify that your linked server definition is in fact working. ... >> standby server, but i receive the error above, i am not sure if i have to ...
    (microsoft.public.sqlserver.server)
  • How to return value from Stored Procedure to label control?
    ... I created a stored proc look like this ... And when I execute ... exec usp_text 'This is a test' ... lblRetuenValue label with no value ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: BUILTINAdministrators Account!!!!!
    ... You can execute the following in Query Analyzer: ... the group is a member of the sysadmins sql ... EXEC sp_addsrvrolemember 'BUILTIN\Administrators', ... >> We've removed the builtin group from all our production servers without any issues and as referred ensure your login or referred logins has required privileges in order to hand SQL Server. ...
    (microsoft.public.sqlserver.security)
  • Re: MS03-031 and 818806
    ... I took a looke at the .sql files that were stored as part of the package ... execute sp_addextendedproc 'sp_setuserbylogin','' ... exec sp_MS_marksystemobject 'sp_setuserbylogin' ... > from another SQL2000 server that has not been updated. ...
    (microsoft.public.sqlserver.security)