Re: Variable server names

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

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.

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?



On Jul 2, 4:11 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Rick (rick0...@xxxxxxxxxxx) writes:
Yup--it's a linked server but I should have mentioned we're still
using SQL Server 2000.  I think SYNONYM was introduced in SQL Server
2005?

Management is insisting that we not include server names at all in our
procedures.  Instead we're asked to pass them in as parameters which
are stored by the job scheduler and passed to the proc.

Which only goes to prove that management should not stick their head into
technical issues!

Am I stuck with dynamic SQL?

Not necessarily. I didn't see the original post, but if the job runs
with sufficient privileges, you can wrap your procedures in an outer
procedure that does:

   EXEC sp_addlinkedserver THISSERVER, '', 'SQLOLEDB', @servername
   EXEC inner_procedure
   EXEC sp_dropserver THISSERVER

That is, you add the server definitions on the fly.

You need to put the server definitions in an outer procedure, or else
the procedure will not compile, as deferred named resolution does not
apply to linked tables.

Admittedly, this solution may not only require technical skills, but
also political when talking to management!

--
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

.

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: 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: Setting up Linked server to MsAccess
    ... I'm running SQL Server\Express in "Windows Authenication Mode" (Do I have to ... they are accessing my MS Access linked server? ... Containing folders with MSAccess Database X.mdb ... SQl Express has linked server X mapped via UNC to Database X.mdb ...
    (microsoft.public.sqlserver.security)
  • Re: Advice Needed: Asynchronous Replication/Mirroring to central o
    ... Looking for a SQL Server replication book? ... geographically-dispersed remote locations. ...
    (microsoft.public.sqlserver.replication)