Re: Variable server names

THANK YOU. I can't use a regular '#' temp table because the proc is
being run by BCP in order to send its output to a text file, and BCP
can't see '#' temp tables.

My initial desire was to do this in a single stored proc. I'd pass
the remote server's name to it and then using dynamic SQL separately
insert data from tables on both servers. (INSERT INTO ... 'SELECT *
FROM ' + @remoteservername + 'dbo.remotetable'....)

Then I realized that mixing dynamic SQL and a table variable was going
to be too much to handle. So having two procs seemed like a good
solution, executing the remote from the local. My first insert would
insert local data into the table-variable from the local table, then
for the second insert I'd send the result set of the second proc into
a second INSERT INTO. It seemed your technique was going to work
perfectly for that, except that I'm now tripped up by SQL 2000's
restriction on INSERT-EXEC and table variables.

I'll figure this out yet...





On Jul 2, 5:38 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Rick (rick0...@xxxxxxxxxxx) writes:
Thanks for this.  We already have (permanent) linked server
relationships between the current server and all the the remote
servers I could possibly be passing in as a variable.  So I'm assuming
I don't need to execute the SP_ADDLINKEDSERVER and SP_DROPSERVER that
you indicate below.

As I said, I did not see your original post, so I may be missing some
context, but I understood it as management wanted to connect to a server
that was determined at run-time.

My idea was then you have have a fixed server name that you would
use in your code, but that yould define the definition to point at
the current linked server.





I have a table on the local server into which I need to insert data
from both (1) a table on the local server AND (2) a table on the
remote server.  My initial thought was to first populate the local
table with the local data, then execute the remote proc -- its named
passed in as a parm -- as you do here to insert the remote data into
the local.  The problem is that the table is declared as a table
variable, and when I do--

INSERT INTO @mytable
EXEC @Server_and_DB_and_ProcName

I'm warned that:

EXECUTE cannot be used as a source when inserting into a table
variable.

So my backup plan was to do this all in a single proc.  I was hoping
to avoid dynamic SQL since mixing dynamic SQL and a table variable
doesn't make me very happy.  Any thoughts?

The restriction on INSERT-EXEC and table variables has been lifted in
SQL 2005, but any reason you cannot use a temp table instead?

If all you need to do on the linked server is to run procedures,
building a string with the procedurename like above, is definitely the
best way to go.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • SQL 2005 Express Connection Problem
    ... The remote site is not a company ... this remote site and try to log in I get this error: ... An error has occurred while establishing a connection to the server. ... When connecting to SQL Server 2005, this failure may be caused by the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Advice Needed: Asynchronous Replication/Mirroring to central o
    ... Looking for a SQL Server replication book? ... geographically-dispersed remote locations. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2005 Express Connection Problem
    ... sql2005 support auto attaching a database in the connect string. ... The remote site is not a company ... An error has occurred while establishing a connection to the server. ... When connecting to SQL Server 2005, this failure may be caused by the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: C# - connection to a remote SQL server
    ... remove server ... want to authenticate with trusted auth or sql auth. ... storing a token for access to the remote pc. ... understand a lot about nt authentication and tokens. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: VBA - Create a recordset with no records for inserting
    ... directly into the server, I can run a PROC that gets files from my desktop. ... The ServiceBroker will construct the dynamic SQL statement to BULK INSERT the ...
    (microsoft.public.data.ado)