Re: Variable server names

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


Rick (rick0726@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, esquel@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

.

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: Fulltext failure on a 2 node cluster
    ... Server full-text search resource online: "SQL Cluster Resource 'Full Text' ...
    (microsoft.public.sqlserver.clustering)
  • Re: Stored procedure/trigger and scripts
    ... SQL Server has permissions to execute xp_cmdshell. ... Please read up on xp_cmdshell within Books Online (within the SQL Server program group). ... >> client to change their password they have to call the "Client Relations" ...
    (microsoft.public.sqlserver.programming)
  • Re: HELP PLEASE ~ ???
    ... You mentioned that it went ahead and added a SQL ... SQL Server 2000 database for all my data. ... find the connectionString in the newly recreated SQLExpress database. ... The connection string specifies a local Sql Server Express instance ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Problem with bulk load security.
    ... The only user that didn't have the bulkadmin roll was the ID that SQL ... Did you to EXECUTE AS USER or EXECUTE ... Links for SQL Server Books Online: ... DDL Triggers with Server Scope and logon triggers ...
    (microsoft.public.sqlserver.security)