Connect to Different Server

[ben1234zz]

Hi

We have a remote webserver running SQL Server 2005 that we need to connect to using TSQL and execute 2 SP's from our office SQL Server 2005.

We access the webserver using an IP address & different port number i.e. xxx.xxx.xxx.xx,1192 (the web server is not on our domain)

Is there any way we can run an SP on our office SQL server that will Execute 2 SP's on the web server.

Kind regards
B

 

[genomon]

In Access I would do this with a pass-through query in VBA. Not sure in T-SQL, but this may be helpful from BOL:

The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked server name. Thus, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. When executing remote stored procedures, OPENDATASOURCE should refer to another SQL Server. OPENDATASOURCE does not accept variables for its arguments.

Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources accessed infrequently. Define a linked server for any data sources accessed more than a few times. Neither OPENDATASOURCE, nor OPENROWSET provide all the functionality of linked server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided each time OPENDATASOURCE is called.

Illegitimi non carborundum.