Accessing a remote SQL Server

Currently, I access a database and table that is not the current
database using Admin..CASES
INSERT INTO Admin..CASES
(CASE_CODE,CASE_NAME,CASE_CLASS_PERIOD_B
EG_DATE,CASE_CLASS_PERIOD_END_DATE,
CASE_DISTRIBUTION_DATE,CASE_URL) SELECT M.MailCode AS
CaseCode,C.CaseName, C.ClassPeriodBegDate, C.ClassPeriodEndDate,
C.DistributionDate, M.WebsiteAddr FROM CaseList C INNER JOIN
MailCode M ON M.CaseRef = C.CaseRef WHERE Admin='Gilardi & Co., LLC'
Admin is the DB and CASES is the table.
I now have a situation where Admin is on a remote Server at the
IP of 192.168.100.5.
What syntax should I use for the above query?
ToddIf you are able to register the remote server as a 'Linked Server', it will
be relatively simple. See Books Online for sp_AddLinkedServer.
Then, using a 'four-part' object name, with the Server name first (you are
currently using a three-part object name), try something like this:
INSERT INTO MyServer.Admin..CASES
etc.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Todd Cary" <todd@.aristesoftware.com> wrote in message
news:45257B52.9040208@.aristesoftware.com...
> Currently, I access a database and table that is not the current database
> using Admin..CASES
> INSERT INTO Admin..CASES
> (CASE_CODE,CASE_NAME,CASE_CLASS_PERIOD_B
EG_DATE,CASE_CLASS_PERIOD_END_DATE
,
> CASE_DISTRIBUTION_DATE,CASE_URL) SELECT M.MailCode AS CaseCode,C.CaseName,
> C.ClassPeriodBegDate, C.ClassPeriodEndDate, C.DistributionDate,
> M.WebsiteAddr FROM CaseList C INNER JOIN MailCode M ON M.CaseRef =
> C.CaseRef WHERE Admin='Gilardi & Co., LLC'
> Admin is the DB and CASES is the table.
> I now have a situation where Admin is on a remote Server at the IP of
> 192.168.100.5.
> What syntax should I use for the above query?
> Todd|||Arnie Rowland wrote:
> If you are able to register the remote server as a 'Linked Server', it wil
l
> be relatively simple. See Books Online for sp_AddLinkedServer.
> Then, using a 'four-part' object name, with the Server name first (you are
> currently using a three-part object name), try something like this:
> INSERT INTO MyServer.Admin..CASES
> etc.
>
When I bring up the Registered SQL Server Properties in
Enterprise manager, I am not sure how to "Register" the server;
what to put in the Server edit box. If I put the IP in the
Server edit box, it does create an entry.
The docs speak of @.server= and @.datasrc=
Todd|||Arnie Rowland wrote:
> If you are able to register the remote server as a 'Linked Server', it wil
l
> be relatively simple. See Books Online for sp_AddLinkedServer.
> Then, using a 'four-part' object name, with the Server name first (you are
> currently using a three-part object name), try something like this:
> INSERT INTO MyServer.Admin..CASES
> etc.
>
I have tried
sp_AddLinkedServer @.server='GilardiDEV',
@.srvproduct='SQL Server',
@.datasrc='192.168.100.5'
But I get the error
"You must specify a provider name with this set of properties."
yet the docs say that for "SQL Server", a provider is not require.
Hmmmm....
Todd|||Not sure about the docs but you need to add
@.provider='SQLOLEDB'
to create the linked server.
However...the bigger issue is that it looks like you have
linked server named ADMIN already created. So wouldn't your
question really be how do I change the linked server ADMIN
to point to a new IP address?
You can change the data source through Enterprise Manager
for the linked server. It will update the system tables
(sysservers) where the information is stored.
-Sue
On Thu, 05 Oct 2006 17:53:25 -0700, Todd Cary
<todd@.aristesoftware.com> wrote:

>Arnie Rowland wrote:
>I have tried
>sp_AddLinkedServer @.server='GilardiDEV',
>@.srvproduct='SQL Server',
>@.datasrc='192.168.100.5'
>But I get the error
>"You must specify a provider name with this set of properties."
>yet the docs say that for "SQL Server", a provider is not require.
>Hmmmm....
>Todd