Adding a linked server?

I have a huge table called "Clientdata" in a database called "Accounts" against which I have to run current month queries. Not only does this kill the box it runs on, but slows down everything in sight.

What I would like to do is export only the current month records from this database\table to another database\table (Statements\Clientdata) by means of a stored proc.

How do I setup the store proc as I have not done one yet ?

Thanksy dont u use a DTS package?

Adding a Linked Server.

Dear all,
I am doing the following code
EXEC sp_addlinkedserver
'MYSERVER',
N'SQL Server'
GO
And getting this error
Server: Msg 18452, Level 14, State 1, Line 12
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
What am I doing wrong ?Hi
Most likely the server is under Window Authentication. If you change the
server settings to Mixed (SQL Server and Windows) the problem will be gone.
"Patricia" <Patricia@.discussions.microsoft.com> wrote in message
news:AFD3B597-5DA3-4229-A03D-7ADDED58296C@.microsoft.com...
> Dear all,
> I am doing the following code
> EXEC sp_addlinkedserver
> 'MYSERVER',
> N'SQL Server'
> GO
> And getting this error
> Server: Msg 18452, Level 14, State 1, Line 12
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> What am I doing wrong ?
>|||Hello & Thanks for you reply.
I have checked both server, and both already had 'mixed' i.e. allow the log
on for both SQL Server and Windows accounts.
Thanks for your time
"Uri Dimant" wrote:

> Hi
> Most likely the server is under Window Authentication. If you change the
> server settings to Mixed (SQL Server and Windows) the problem will be gone
.
>
>
>
> "Patricia" <Patricia@.discussions.microsoft.com> wrote in message
> news:AFD3B597-5DA3-4229-A03D-7ADDED58296C@.microsoft.com...
>
>

Adding a Linked Server

HI,

There is a requirement to transfer a table data from a oracle view to a SQL Server table. These both reside on the same box. I am trying to add the oracle instance as a linked server, like this:

EXEC sp_addlinkedserver @.server = @.SOURCE_SERVER_NAME, @.srvproduct = '', @.provider = @.PROVIDER_NAME, @.datasrc = @.DATA_SOURCE_NAME

The server gets added. But When I use this string as part of a stored procedure (or even not use it there), which does the work of transfering the data as required, I get this error:

Server: Msg 7399, Level 16, State 1, Procedure UBS_PULL_HR_DATA, Line 125
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

I have added this Name in the System DSM also. I dont know how to go about it. Can any one please help?

Thanks a lot and let me know if the question is not clear.

Mannu.

When the server gets added, check the security tab. I'm guessing it's blank as you aren't adding the login information. Error 0x80004005 is access is denied. That looks like it's one of the problems anyway. If you want to script the login for the linked server, you would use sp_addlinkedsrvlogin.

-Sue

|||

Hi Sue, Thanks for the reply. I have overcome this problem. However, I am now getting this error:

The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

Any ideas to solve this? Thanks a lot.

Mannu.

sql

Adding a Linked Server

HI,

There is a requirement to transfer a table data from a oracle view to a SQL Server table. These both reside on the same box. I am trying to add the oracle instance as a linked server, like this:

EXEC sp_addlinkedserver @.server = @.SOURCE_SERVER_NAME, @.srvproduct = '', @.provider = @.PROVIDER_NAME, @.datasrc = @.DATA_SOURCE_NAME

The server gets added. But When I use this string as part of a stored procedure (or even not use it there), which does the work of transfering the data as required, I get this error:

Server: Msg 7399, Level 16, State 1, Procedure UBS_PULL_HR_DATA, Line 125
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

I have added this Name in the System DSM also. I dont know how to go about it. Can any one please help?

Thanks a lot and let me know if the question is not clear.

Mannu.

When the server gets added, check the security tab. I'm guessing it's blank as you aren't adding the login information. Error 0x80004005 is access is denied. That looks like it's one of the problems anyway. If you want to script the login for the linked server, you would use sp_addlinkedsrvlogin.

-Sue

|||

Hi Sue, Thanks for the reply. I have overcome this problem. However, I am now getting this error:

The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

Any ideas to solve this? Thanks a lot.

Mannu.

Added SP2 now have linked server error

I added SP2 to my 2005 server and now I can't connect to a previously working
linked Oracle server. I get this:
TITLE: Microsoft SQL Server Management Studio
"The test connection to the linked server failed."
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "ISRSREPLICATEDLINK".
OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
message "Error while trying to retrieve text for error ORA-01019
". (Microsoft SQL Server, Error: 7303)
What could have changed? Testing the connection via the ODBC works but
inside of SQL MAnagement Studio it fails.
Also somewhere in troubleshooting this I saw an error about unable to
allocate user side memory.
"TLuebke" wrote:

> I added SP2 to my 2005 server and now I can't connect to a previously working
> linked Oracle server. I get this:
> TITLE: Microsoft SQL Server Management Studio
> --
> "The test connection to the linked server failed."
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "ISRSREPLICATEDLINK".
> OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
> message "Error while trying to retrieve text for error ORA-01019
> ". (Microsoft SQL Server, Error: 7303)
> What could have changed? Testing the connection via the ODBC works but
> inside of SQL MAnagement Studio it fails.
>
>
|||TLuebke,
Have you tried reinstalling your Oracle provider? I did some research, but
no clear indication of what your problem is. One interesting thing is that
http://technet.microsoft.com/en-us/library/bb332055.aspx suggests using the
Oracle provider for OLE DB (OraOLEDB.Oracle.1) rather than MSDAORA, in case
that is useful to you.
RLF
"TLuebke" <TLuebke@.discussions.microsoft.com> wrote in message
news:57778530-E08D-46F2-ADD9-0D7D31236C74@.microsoft.com...[vbcol=seagreen]
> Also somewhere in troubleshooting this I saw an error about unable to
> allocate user side memory.
> "TLuebke" wrote:

Added SP2 now have linked server error

I added SP2 to my 2005 server and now I can't connect to a previously working
linked Oracle server. I get this:
TITLE: Microsoft SQL Server Management Studio
--
"The test connection to the linked server failed."
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "ISRSREPLICATEDLINK".
OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
message "Error while trying to retrieve text for error ORA-01019
". (Microsoft SQL Server, Error: 7303)
What could have changed? Testing the connection via the ODBC works but
inside of SQL MAnagement Studio it fails.Also somewhere in troubleshooting this I saw an error about unable to
allocate user side memory.
"TLuebke" wrote:
> I added SP2 to my 2005 server and now I can't connect to a previously working
> linked Oracle server. I get this:
> TITLE: Microsoft SQL Server Management Studio
> --
> "The test connection to the linked server failed."
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "ISRSREPLICATEDLINK".
> OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
> message "Error while trying to retrieve text for error ORA-01019
> ". (Microsoft SQL Server, Error: 7303)
> What could have changed? Testing the connection via the ODBC works but
> inside of SQL MAnagement Studio it fails.
>
>|||TLuebke,
Have you tried reinstalling your Oracle provider? I did some research, but
no clear indication of what your problem is. One interesting thing is that
http://technet.microsoft.com/en-us/library/bb332055.aspx suggests using the
Oracle provider for OLE DB (OraOLEDB.Oracle.1) rather than MSDAORA, in case
that is useful to you.
RLF
"TLuebke" <TLuebke@.discussions.microsoft.com> wrote in message
news:57778530-E08D-46F2-ADD9-0D7D31236C74@.microsoft.com...
> Also somewhere in troubleshooting this I saw an error about unable to
> allocate user side memory.
> "TLuebke" wrote:
>> I added SP2 to my 2005 server and now I can't connect to a previously
>> working
>> linked Oracle server. I get this:
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> "The test connection to the linked server failed."
>> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
>> linked server "ISRSREPLICATEDLINK".
>> OLE DB provider "MSDAORA" for linked server "ISRSREPLICATEDLINK" returned
>> message "Error while trying to retrieve text for error ORA-01019
>> ". (Microsoft SQL Server, Error: 7303)
>> What could have changed? Testing the connection via the ODBC works but
>> inside of SQL MAnagement Studio it fails.
>>
>>

Add/Drop LinkedServers using SMO

Anyone used SMO in SQL Server 2005 to add or drop Linked Server. I couldn't
find any sample or information on that.
ThanksHello,
I've never done this, but you probably need to use the LinkedServer
class in the Microsoft.SqlServer.Management.Smo namespace. Set the
desired properties of the LinkedServer, then use the Create method. For
more informations, see:
http://msdn2.microsoft.com/microsof...rs
.aspx
http://msdn2.microsoft.com/ms162171(en-US,SQL.90).aspx
Razvan|||Thanks for the response.
I solved the problem. If anyone interested heres the code for
Adding/Dropping Linked Servers using SMO. I use this in a Class.
public bool AddLinkServer(string SourceServer, string
DestinationServer,string UID,string PWD)
{
try
{
LinkedServer DestSrv;
ServerConnection SrvConn = new ServerConnection();
SrvConn.ServerInstance = SourceServer;
if ((UID == "") && (PWD == ""))
{
SrvConn.LoginSecure = true;
}
else
{
SrvConn.LoginSecure = false;
SrvConn.Login = UID;
SrvConn.Password = PWD;
}
Server SQLServer = new Server(SrvConn);
DestSrv = new LinkedServer(SQLServer, DestinationServer);
// Add Remote user/password for linking.
LinkedServerLogin LnkLogin = new LinkedServerLogin(DestSrv, UID);
if (!SrvConn.LoginSecure)
{
LnkLogin.RemoteUser = UID;
LnkLogin.SetRemotePassword(PWD);
LnkLogin.Create();
}
DestSrv.Create();
SrvConn.Disconnect();
return true;
}
catch
{
return false;
}
}
// drop link serverss...
public bool DropLinkServer(string SourceServer,string DropLinkServerName)
{
try
{
ServerConnection SrvConn = new ServerConnection();
SrvConn.ServerInstance = SourceServer;
SrvConn.LoginSecure = true;
Server SQLServer = new Server(SrvConn);
LinkedServerCollection LnkServerList = SQLServer.LinkedServers;
foreach (LinkedServer Lnk in LnkServerList)
{
if (Lnk.Name == DropLinkServerName)
{
Lnk.Drop(true);
break;
}
}
SrvConn.Disconnect();
return true;
}
catch
{
return false;
}
}
// Load all linked servers...
public bool LoadLinkServers(string SourceServer, ArrayList LinkServerList)
{
try
{
ServerConnection SrvConn = new ServerConnection();
SrvConn.ServerInstance = SourceServer;
SrvConn.LoginSecure = true;
Server SQLServer = new Server(SrvConn);
LinkedServerCollection LnkSrvList = SQLServer.LinkedServers;
foreach (LinkedServer Lnk in LnkSrvList)
{
LinkServerList.Add(Lnk.Name);
}
SrvConn.Disconnect();
return true;
}
catch
{
return false;
}
}
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138559727.598122.180660@.g44g2000cwa.googlegroups.com...
> Hello,
> I've never done this, but you probably need to use the LinkedServer
> class in the Microsoft.SqlServer.Management.Smo namespace. Set the
> desired properties of the LinkedServer, then use the Create method. For
> more informations, see:
> http://msdn2.microsoft.com/microsof...
rs.aspx
> http://msdn2.microsoft.com/ms162171(en-US,SQL.90).aspx
> Razvan
>

add server to sysservers

is there a way to add a remote server name to sysservers in the master DB.
i am not able to use linked server.Yes, use the sp_addserver stored procedure. Check books online for complete
syntax.
--Brian
(Please reply to the newsgroups only.)
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:%238ZZOXsuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> is there a way to add a remote server name to sysservers in the master DB.
> i am not able to use linked server.
>|||> Yes, use the sp_addserver stored procedure. Check books online for
> complete syntax.
it's the same thing as sp_addlinkedserver - and again, i can't do that.|||You'll need to provide more of a description of the problem. What exactly
are you trying to accomplish and what error message are you getting?
--Brian
(Please reply to the newsgroups only.)
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:u4ZfeusuFHA.3764@.TK2MSFTNGP09.phx.gbl...
> it's the same thing as sp_addlinkedserver - and again, i can't do that.
>|||> it's the same thing as sp_addlinkedserver - and again, i can't do that.
Can you provide more information? What are you going to do with a server in
sysservers that you can't add by using sp_addlinkedserver?|||why not? permissions? then no, you won't be able to do this by inserting
directly into the sysservers table,either [which is what i think you're
asking]
mcnewsxp wrote:

>it's the same thing as sp_addlinkedserver - and again, i can't do that.
>
>|||why not? permissions? then no, you won't be able to do this by inserting dir
ectly into the sysservers table,either [which is what i think you're asking]
[mc] that is what i needed to know.
mcnewsxp wrote:
Yes, use the sp_addserver stored procedure. Check books online for
complete syntax.
it's the same thing as sp_addlinkedserver - and again, i can't do that.|||>
> Can you provide more information? What are you going to do with a server
> in sysservers that you can't add by using sp_addlinkedserver?
>
do a select * from server.database.owner.table.
our dba won't allow linked servers.
but it turns out we moved stuff to two dbs and he allowed us to create one
linked server this time.|||> do a select * from server.database.owner.table.
> our dba won't allow linked servers.
His policy sounds more like he doesn't allow new rows in sysservers, so
whether you get it in there using sp_addlinkedserver or any other means,
you're still not allowed to have a linked server.

> but it turns out we moved stuff to two dbs and he allowed us to create one
> linked server this time.
Well, that's good, because using OPENQUERY / OPENROWSET and dynamically
specifying the server is not the best approach.|||>> do a select * from server.database.owner.table.
> His policy sounds more like he doesn't allow new rows in sysservers, so
> whether you get it in there using sp_addlinkedserver or any other means,
> you're still not allowed to have a linked server.
>
> Well, that's good, because using OPENQUERY / OPENROWSET and dynamically
> specifying the server is not the best approach.
what is the best approach?
>

Add linked server with remote source

How do i add linked server whoose source file is not located on server itself?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?
Mikus wrote:
> How do i add linked server whoose source file is not located on server itself?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen
|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:

> Mikus wrote:
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>
|||Mikus wrote:[vbcol=seagreen]
> This syntax work's and server is added, but when i try to see at the tables
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen

Add linked server with remote source

How do i add linked server whoose source file is not located on server itself?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?Mikus wrote:
> How do i add linked server whoose source file is not located on server itself?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:
> Mikus wrote:
> > How do i add linked server whoose source file is not located on server itself?
> >
> > I want to create linked server which uses excel file as it's source, but
> > this file is located on my pc(named "mix")
> >
> > I want to do something like this...
> >
> > EXEC sp_addlinkedserver 'MikusExcelSource',
> > 'Jet 4.0',
> > 'Microsoft.Jet.OLEDB.4.0',
> > '\\mix\silo\2005 9 Sep_new.xls',
> > NULL,
> > 'Excel 5.0'
> > GO
> >
> > any ideas ?
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>|||Mikus wrote:
> This syntax work's and server is added, but when i try to see at the tables
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
>> Mikus wrote:
>> How do i add linked server whoose source file is not located on server itself?
>> I want to create linked server which uses excel file as it's source, but
>> this file is located on my pc(named "mix")
>> I want to do something like this...
>> EXEC sp_addlinkedserver 'MikusExcelSource',
>> 'Jet 4.0',
>> 'Microsoft.Jet.OLEDB.4.0',
>> '\\mix\silo\2005 9 Sep_new.xls',
>> NULL,
>> 'Excel 5.0'
>> GO
>> any ideas ?
>> Have you tried to add the linked server with the syntax you suggest?
>> There're no problems as such to add a linked server using an UNC path.
>> The only limitation that I've seen, is that if the files for the linked
>> server isn't placed on the server it self, you'll have to use this
>> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
>> add a linked server on Server A having the files placed on Server B,
>> I'll have to connect to Server A with Remote Desktop and open up QA and
>> run my query. If I open QA on my own desktop and connect to server A,
>> I'll get an error if I try to use the linked server.
>> There might be ways around this issue, but I've never been able to find
>> a way to get around it.
>> Regards
>> Steen
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen

Add linked server with remote source

How do i add linked server whoose source file is not located on server itsel
f?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?Mikus wrote:
> How do i add linked server whoose source file is not located on server its
elf?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:

> Mikus wrote:
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>|||Mikus wrote:[vbcol=seagreen]
> This syntax work's and server is added, but when i try to see at the table
s
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
>
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen

add linked server problem

Hi, there,
I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
just a bald SQL Server.
I want to set up linked server from SQLA , so I can run query like "select *
from SQLB.dbname.dbo.tablename". The command I use is
sp_addlinkedserver @.server = 'SQLB'
After I ran this code, I span the "Linked servers" node of Enterprise
Manager, I saw an image of a linked server named "SQLB". Then I run the
query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
SQLA, I got the following errors
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I'm the system administrator of the two servers, and the account I ran the
query is sys admin account. I don't know why the command failed. I drop the
linked server and readd the linked server again. I got the same result.
Who can help a bit?
Thanks so much in advance,
JamieMake very sure that the folders involved (db location, etc) have full
read/write/execute access for the login account.
Randall Arnold
"Jamie" <gsadd@.yahoo.com> wrote in message
news:O5ALGyfIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi, there,
> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
> just a bald SQL Server.
> I want to set up linked server from SQLA , so I can run query like "select
> * from SQLB.dbname.dbo.tablename". The command I use is
> sp_addlinkedserver @.server = 'SQLB'
> After I ran this code, I span the "Linked servers" node of Enterprise
> Manager, I saw an image of a linked server named "SQLB". Then I run the
> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
> SQLA, I got the following errors
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I'm the system administrator of the two servers, and the account I ran the
> query is sys admin account. I don't know why the command failed. I drop
> the linked server and readd the linked server again. I got the same
> result.
> Who can help a bit?
> Thanks so much in advance,
> Jamie
>

add linked server problem

Hi, there,
I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
just a bald SQL Server.
I want to set up linked server from SQLA , so I can run query like "select *
from SQLB.dbname.dbo.tablename". The command I use is
sp_addlinkedserver @.server = 'SQLB'
After I ran this code, I span the "Linked servers" node of Enterprise
Manager, I saw an image of a linked server named "SQLB". Then I run the
query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
SQLA, I got the following errors
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I'm the system administrator of the two servers, and the account I ran the
query is sys admin account. I don't know why the command failed. I drop the
linked server and readd the linked server again. I got the same result.
Who can help a bit?
Thanks so much in advance,
Jamie
Make very sure that the folders involved (db location, etc) have full
read/write/execute access for the login account.
Randall Arnold
"Jamie" <gsadd@.yahoo.com> wrote in message
news:O5ALGyfIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi, there,
> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
> just a bald SQL Server.
> I want to set up linked server from SQLA , so I can run query like "select
> * from SQLB.dbname.dbo.tablename". The command I use is
> sp_addlinkedserver @.server = 'SQLB'
> After I ran this code, I span the "Linked servers" node of Enterprise
> Manager, I saw an image of a linked server named "SQLB". Then I run the
> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
> SQLA, I got the following errors
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I'm the system administrator of the two servers, and the account I ran the
> query is sys admin account. I don't know why the command failed. I drop
> the linked server and readd the linked server again. I got the same
> result.
> Who can help a bit?
> Thanks so much in advance,
> Jamie
>

Add Linked Server Failed

hi,
i have added a link server but it fails when i try to retrieve data.

SQL server in on the same machine
i have created the another SQL server instance

Main Server: SERVER1
Instance: SERVER1/NewServer

EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'

select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct

when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.

anyone have suggestion??
Aditry this
select * from [SERVER1/NewServer].db1.dbo.tbl_Prouduct|||i try this too but its still not working

Add Linked Server Failed

hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion?
Adi
This was just dicussed this week. The "/" is a special character not
normally allowed in an identifier. Either give the linked server a
"regular" name or delimit the name with brackets - [SERVER1/NewServer]. BOL
uses this same situation as an example.
"Adeel Ahmed" <adeel.ahmed@.pk.softechww.com> wrote in message
news:O0qoJPu0EHA.2876@.TK2MSFTNGP12.phx.gbl...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion?
> Adi
>
|||Thanks,
i had resolve the problem.
actually i try this too
select * from [Server1/Newserver].dbo.db1.tbl_product
but its still not work
but when i used back slash "\" to register new linked server and
retrieve records like that
select * from [Server1\Newserver].dbo.db1.tbl_product
its work fine
thanks
Adi
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Yeah, smart. Look it up. And do us a favor and don't multipost. I just answered this question in another thread just to come to find out the it was "discovered" or answered in another.
Don't waste people's time like that.
Sincerely,
Anthony Thomas

"Adeel Ahmed" <itsmeadeel@.hotmail.com> wrote in message news:ekkVr1G1EHA.1452@.TK2MSFTNGP11.phx.gbl...
Thanks,
i had resolve the problem.
actually i try this too
select * from [Server1/Newserver].dbo.db1.tbl_product
but its still not work
but when i used back slash "\" to register new linked server and
retrieve records like that
select * from [Server1\Newserver].dbo.db1.tbl_product
its work fine
thanks
Adi
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Add Linked Server Failed

hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion'
AdiThis was just dicussed this week. The "/" is a special character not
normally allowed in an identifier. Either give the linked server a
"regular" name or delimit the name with brackets - [SERVER1/NewServer]. BOL
uses this same situation as an example.
"Adeel Ahmed" <adeel.ahmed@.pk.softechww.com> wrote in message
news:O0qoJPu0EHA.2876@.TK2MSFTNGP12.phx.gbl...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion'
> Adi
>

Add Linked Server Failed

hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion'
AdiThis was just dicussed this week. The "/" is a special character not
normally allowed in an identifier. Either give the linked server a
"regular" name or delimit the name with brackets - [SERVER1/NewServer].
BOL
uses this same situation as an example.
"Adeel Ahmed" <adeel.ahmed@.pk.softechww.com> wrote in message
news:O0qoJPu0EHA.2876@.TK2MSFTNGP12.phx.gbl...
> hi,
> i have added a link server but it fails when i try to retrieve data.
> SQL server in on the same machine
> i have created the another SQL server instance
> Main Server: SERVER1
> Instance: SERVER1/NewServer
> EXEC sp_addlinkedserver @.server = SERVER1/NewServer, @.srvproduct = 'SQL
> Server'
> EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
> select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
> when i try to run the above query it gives me the error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '/'.
>
> anyone have suggestion'
> Adi
>|||Thanks,
i had resolve the problem.
actually i try this too
select * from [Server1/Newserver].dbo.db1.tbl_product
but its still not work
but when i used back slash "\" to register new linked server and
retrieve records like that
select * from [Server1\Newserver].dbo.db1.tbl_product
its work fine
thanks
Adi
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Yeah, smart. Look it up. And do us a favor and don't multipost. I just an
swered this question in another thread just to come to find out the it was "
discovered" or answered in another.
Don't waste people's time like that.
Sincerely,
Anthony Thomas
--
"Adeel Ahmed" <itsmeadeel@.hotmail.com> wrote in message news:ekkVr1G1EHA.1
452@.TK2MSFTNGP11.phx.gbl...
Thanks,
i had resolve the problem.
actually i try this too
select * from [Server1/Newserver].dbo.db1.tbl_product
but its still not work
but when i used back slash "\" to register new linked server and
retrieve records like that
select * from [Server1\Newserver].dbo.db1.tbl_product
its work fine
thanks
Adi
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Add linked server at Excel VBA

I had SQLDMO at reference library.

Private Sub test()
Dim s As SQLDMO.SQLServer
Dim ls As SQLDMO.LinkedServer
Set s = New SQLDMO.SQLServer
s.Connect "Server1", "ID", "Password"
Set ls = New SQLDMO.LinkedServer
With ls
.Name = "Server2"
.ProviderName = "SQLOLEDB"
.DataSource = "Server2"
' .ProviderString = ""

End With
s.LinkedServers.Add ls
s.Close

'End Sub

I block ProviderString as i don't know what is it.

I got an error message when it's running the line "s.LinkedServers.Add ls" that

"Run-time error '-2147206257 (80043b8f)':

Automation error"

My question:

1. What is providerstring? what should be put here?

2. How to fix the error

Hi there,

Provider string should look something like the following:

@.provstr=N'DRIVER={<driver name of your linked server, in this case Excel} ;SERVER=<address if your server where the driver resides>;DATABASE=<database name>; USER=<username>; PASSWORD=<password>; OPTION=3'

I found this here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304918&SiteID=1

Also, this may help: http://www.databasejournal.com/features/mssql/article.php/10894_3085211_2

Since you're not executing within the QA of management studio, I'm guessing that you only need to put Excel's driver name in your provider string, but don't take my word as I haven't tried doing this in VB.

Hope this helps!

Isa

add linked server

Hi ,
I have tried to add a linked server(sql2000) residing
at location B from Location A (a server computer ,using
SQL 7.0) but i got the error "general network error , pls
check network documentation" I have gotten the correct
login id & pwd from the user at the other end.
have also configured the port#(under Client Utility)
correctly for client connection and also triedthe
sp_addlinkedservers & sp_addsvrlogin already
Is there any dll file needed ? such as DBNET.dll ? to
add linked server from sql ver 7.0 to sql2000 ?
Does it need any specific version of the odbc driver
for SQL Server ? current it's version is 3.70.10.63 i have
tried to add and odbc link but still have the same error
I have no problem when using Query Analyzer from my
client computer AND the user from the other end is able to
add my sqlserver successfully as a linked server. The odbc
driver for SQL Server is 3.85.xx.xx
Appreciate if somebody could help me out as i am doing
some data migration which i have set up some DTS jobs to
copy data daily over instead of one big chunks the day
before migration
many thks
maxzim
Can you show us your creation script?
This example works without creating a linked server
declare @.pid varchar(30)
set @.pid='2450'
select id from openrowset('sqloledb',
'server';'sa';'pass',database.dbo.table)
where id > @.pid
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:680a01c493d3$071362b0$a501280a@.phx.gbl...
> Hi ,
> I have tried to add a linked server(sql2000) residing
> at location B from Location A (a server computer ,using
> SQL 7.0) but i got the error "general network error , pls
> check network documentation" I have gotten the correct
> login id & pwd from the user at the other end.
> have also configured the port#(under Client Utility)
> correctly for client connection and also triedthe
> sp_addlinkedservers & sp_addsvrlogin already
> Is there any dll file needed ? such as DBNET.dll ? to
> add linked server from sql ver 7.0 to sql2000 ?
> Does it need any specific version of the odbc driver
> for SQL Server ? current it's version is 3.70.10.63 i have
> tried to add and odbc link but still have the same error
> I have no problem when using Query Analyzer from my
> client computer AND the user from the other end is able to
> add my sqlserver successfully as a linked server. The odbc
> driver for SQL Server is 3.85.xx.xx
> Appreciate if somebody could help me out as i am doing
> some data migration which i have set up some DTS jobs to
> copy data daily over instead of one big chunks the day
> before migration
> many thks
>
|||Hi Uri ,
thks will try out urs
but for my creation script
i have used the Enterprise Manager and just specify the
remote user & pwd
for the query analyzer on the sql server i used :
sp_addlinkedserver 'serverA' -- it's using sql server so
i did not specify the rest of the connection
sp_addsvrlogin 'serverA' ,
false , 'sa', 'userid' , 'userpassword'
for the query analyzer from my local client computer ,
i simply enter the server name , enter the pwd & id and i
get connected
>--Original Message--
>maxzim
>Can you show us your creation script?
>This example works without creating a linked server
>declare @.pid varchar(30)
>set @.pid='2450'
>
>select id from openrowset('sqloledb',
>'server';'sa';'pass',database.dbo.table)
>where id > @.pid
>
>
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:680a01c493d3$071362b0$a501280a@.phx.gbl...
pls[vbcol=seagreen]
have[vbcol=seagreen]
to[vbcol=seagreen]
odbc
>
>.
>
|||Hi
Do you have specified Server Type in the first tab when you use EM?
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Uri ,
> thks will try out urs
> but for my creation script
> i have used the Enterprise Manager and just specify the
> remote user & pwd
> for the query analyzer on the sql server i used :
> sp_addlinkedserver 'serverA' -- it's using sql server so
> i did not specify the rest of the connection
> sp_addsvrlogin 'serverA' ,
> false , 'sa', 'userid' , 'userpassword'
> for the query analyzer from my local client computer ,
> i simply enter the server name , enter the pwd & id and i
> get connected
> message
> pls
> have
> to
> odbc
|||Hi Uri ,
i used SQL Server in the 1st tab

>--Original Message--
>Hi
>Do you have specified Server Type in the first tab when
you use EM?
>
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
the[vbcol=seagreen]
so[vbcol=seagreen]
computer ,[vbcol=seagreen]
i[vbcol=seagreen]
in[vbcol=seagreen]
residing[vbcol=seagreen]
computer ,using[vbcol=seagreen]
correct[vbcol=seagreen]
Utility)[vbcol=seagreen]
DBNET.dll ? to[vbcol=seagreen]
driver[vbcol=seagreen]
error[vbcol=seagreen]
able[vbcol=seagreen]
doing[vbcol=seagreen]
jobs to[vbcol=seagreen]
day
>
>.
>
|||Hi Uri ,
i have used the openrowset but it says "server does not
exist or access denied" which i am using the
administrator's id & pwd
rdgs
>--Original Message--
>Hi
>Do you have specified Server Type in the first tab when
you use EM?
>
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
the[vbcol=seagreen]
so[vbcol=seagreen]
computer ,[vbcol=seagreen]
i[vbcol=seagreen]
in[vbcol=seagreen]
residing[vbcol=seagreen]
computer ,using[vbcol=seagreen]
correct[vbcol=seagreen]
Utility)[vbcol=seagreen]
DBNET.dll ? to[vbcol=seagreen]
driver[vbcol=seagreen]
error[vbcol=seagreen]
able[vbcol=seagreen]
doing[vbcol=seagreen]
jobs to[vbcol=seagreen]
day
>
>.
>
|||Hi
Probably this is a permission issue. Make sure that you have full
permissions on destination server.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:673f01c493dc$63f279e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Uri ,
> i have used the openrowset but it says "server does not
> exist or access denied" which i am using the
> administrator's id & pwd
> rdgs
> you use EM?
> message
> the
> so
> computer ,
> i
> in
> residing
> computer ,using
> correct
> Utility)
> DBNET.dll ? to
> driver
> error
> able
> doing
> jobs to
> day

add linked server

Hi ,
I have tried to add a linked server(sql2000) residing
at location B from Location A (a server computer ,using
SQL 7.0) but i got the error "general network error , pls
check network documentation" I have gotten the correct
login id & pwd from the user at the other end.
have also configured the port#(under Client Utility)
correctly for client connection and also triedthe
sp_addlinkedservers & sp_addsvrlogin already
Is there any dll file needed ? such as DBNET.dll ? to
add linked server from sql ver 7.0 to sql2000 ?
Does it need any specific version of the odbc driver
for SQL Server ? current it's version is 3.70.10.63 i have
tried to add and odbc link but still have the same error
I have no problem when using Query Analyzer from my
client computer AND the user from the other end is able to
add my sqlserver successfully as a linked server. The odbc
driver for SQL Server is 3.85.xx.xx
Appreciate if somebody could help me out as i am doing
some data migration which i have set up some DTS jobs to
copy data daily over instead of one big chunks the day
before migration
many thksmaxzim
Can you show us your creation script?
This example works without creating a linked server
declare @.pid varchar(30)
set @.pid='2450'
select id from openrowset('sqloledb',
'server';'sa';'pass',database.dbo.table)
where id > @.pid
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:680a01c493d3$071362b0$a501280a@.phx.gbl...
> Hi ,
> I have tried to add a linked server(sql2000) residing
> at location B from Location A (a server computer ,using
> SQL 7.0) but i got the error "general network error , pls
> check network documentation" I have gotten the correct
> login id & pwd from the user at the other end.
> have also configured the port#(under Client Utility)
> correctly for client connection and also triedthe
> sp_addlinkedservers & sp_addsvrlogin already
> Is there any dll file needed ? such as DBNET.dll ? to
> add linked server from sql ver 7.0 to sql2000 ?
> Does it need any specific version of the odbc driver
> for SQL Server ? current it's version is 3.70.10.63 i have
> tried to add and odbc link but still have the same error
> I have no problem when using Query Analyzer from my
> client computer AND the user from the other end is able to
> add my sqlserver successfully as a linked server. The odbc
> driver for SQL Server is 3.85.xx.xx
> Appreciate if somebody could help me out as i am doing
> some data migration which i have set up some DTS jobs to
> copy data daily over instead of one big chunks the day
> before migration
> many thks
>|||Hi Uri ,
thks will try out urs
but for my creation script
i have used the Enterprise Manager and just specify the
remote user & pwd
for the query analyzer on the sql server i used :
sp_addlinkedserver 'serverA' -- it's using sql server so
i did not specify the rest of the connection
sp_addsvrlogin 'serverA' ,
false , 'sa', 'userid' , 'userpassword'
for the query analyzer from my local client computer ,
i simply enter the server name , enter the pwd & id and i
get connected
>--Original Message--
>maxzim
>Can you show us your creation script?
>This example works without creating a linked server
>declare @.pid varchar(30)
>set @.pid='2450'
>
>select id from openrowset('sqloledb',
>'server';'sa';'pass',database.dbo.table)
>where id > @.pid
>
>
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:680a01c493d3$071362b0$a501280a@.phx.gbl...
>> Hi ,
>> I have tried to add a linked server(sql2000) residing
>> at location B from Location A (a server computer ,using
>> SQL 7.0) but i got the error "general network error ,
pls
>> check network documentation" I have gotten the correct
>> login id & pwd from the user at the other end.
>> have also configured the port#(under Client Utility)
>> correctly for client connection and also triedthe
>> sp_addlinkedservers & sp_addsvrlogin already
>> Is there any dll file needed ? such as DBNET.dll ? to
>> add linked server from sql ver 7.0 to sql2000 ?
>> Does it need any specific version of the odbc driver
>> for SQL Server ? current it's version is 3.70.10.63 i
have
>> tried to add and odbc link but still have the same error
>> I have no problem when using Query Analyzer from my
>> client computer AND the user from the other end is able
to
>> add my sqlserver successfully as a linked server. The
odbc
>> driver for SQL Server is 3.85.xx.xx
>> Appreciate if somebody could help me out as i am doing
>> some data migration which i have set up some DTS jobs to
>> copy data daily over instead of one big chunks the day
>> before migration
>> many thks
>>
>
>.
>|||Hi
Do you have specified Server Type in the first tab when you use EM?
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
> Hi Uri ,
> thks will try out urs
> but for my creation script
> i have used the Enterprise Manager and just specify the
> remote user & pwd
> for the query analyzer on the sql server i used :
> sp_addlinkedserver 'serverA' -- it's using sql server so
> i did not specify the rest of the connection
> sp_addsvrlogin 'serverA' ,
> false , 'sa', 'userid' , 'userpassword'
> for the query analyzer from my local client computer ,
> i simply enter the server name , enter the pwd & id and i
> get connected
> >--Original Message--
> >maxzim
> >Can you show us your creation script?
> >This example works without creating a linked server
> >declare @.pid varchar(30)
> >set @.pid='2450'
> >
> >
> >select id from openrowset('sqloledb',
> >'server';'sa';'pass',database.dbo.table)
> >where id > @.pid
> >
> >
> >
> >
> >
> >
> >
> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:680a01c493d3$071362b0$a501280a@.phx.gbl...
> >> Hi ,
> >>
> >> I have tried to add a linked server(sql2000) residing
> >> at location B from Location A (a server computer ,using
> >> SQL 7.0) but i got the error "general network error ,
> pls
> >> check network documentation" I have gotten the correct
> >> login id & pwd from the user at the other end.
> >>
> >> have also configured the port#(under Client Utility)
> >> correctly for client connection and also triedthe
> >> sp_addlinkedservers & sp_addsvrlogin already
> >>
> >> Is there any dll file needed ? such as DBNET.dll ? to
> >> add linked server from sql ver 7.0 to sql2000 ?
> >>
> >> Does it need any specific version of the odbc driver
> >> for SQL Server ? current it's version is 3.70.10.63 i
> have
> >> tried to add and odbc link but still have the same error
> >>
> >> I have no problem when using Query Analyzer from my
> >> client computer AND the user from the other end is able
> to
> >> add my sqlserver successfully as a linked server. The
> odbc
> >> driver for SQL Server is 3.85.xx.xx
> >>
> >> Appreciate if somebody could help me out as i am doing
> >> some data migration which i have set up some DTS jobs to
> >> copy data daily over instead of one big chunks the day
> >> before migration
> >>
> >> many thks
> >>
> >>
> >
> >
> >.
> >|||Hi Uri ,
i used SQL Server in the 1st tab
>--Original Message--
>Hi
>Do you have specified Server Type in the first tab when
you use EM?
>
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
>> Hi Uri ,
>> thks will try out urs
>> but for my creation script
>> i have used the Enterprise Manager and just specify
the
>> remote user & pwd
>> for the query analyzer on the sql server i used :
>> sp_addlinkedserver 'serverA' -- it's using sql server
so
>> i did not specify the rest of the connection
>> sp_addsvrlogin 'serverA' ,
>> false , 'sa', 'userid' , 'userpassword'
>> for the query analyzer from my local client
computer ,
>> i simply enter the server name , enter the pwd & id and
i
>> get connected
>> >--Original Message--
>> >maxzim
>> >Can you show us your creation script?
>> >This example works without creating a linked server
>> >declare @.pid varchar(30)
>> >set @.pid='2450'
>> >
>> >
>> >select id from openrowset('sqloledb',
>> >'server';'sa';'pass',database.dbo.table)
>> >where id > @.pid
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:680a01c493d3$071362b0$a501280a@.phx.gbl...
>> >> Hi ,
>> >>
>> >> I have tried to add a linked server(sql2000)
residing
>> >> at location B from Location A (a server
computer ,using
>> >> SQL 7.0) but i got the error "general network error ,
>> pls
>> >> check network documentation" I have gotten the
correct
>> >> login id & pwd from the user at the other end.
>> >>
>> >> have also configured the port#(under Client
Utility)
>> >> correctly for client connection and also triedthe
>> >> sp_addlinkedservers & sp_addsvrlogin already
>> >>
>> >> Is there any dll file needed ? such as
DBNET.dll ? to
>> >> add linked server from sql ver 7.0 to sql2000 ?
>> >>
>> >> Does it need any specific version of the odbc
driver
>> >> for SQL Server ? current it's version is 3.70.10.63 i
>> have
>> >> tried to add and odbc link but still have the same
error
>> >>
>> >> I have no problem when using Query Analyzer from my
>> >> client computer AND the user from the other end is
able
>> to
>> >> add my sqlserver successfully as a linked server. The
>> odbc
>> >> driver for SQL Server is 3.85.xx.xx
>> >>
>> >> Appreciate if somebody could help me out as i am
doing
>> >> some data migration which i have set up some DTS
jobs to
>> >> copy data daily over instead of one big chunks the
day
>> >> before migration
>> >>
>> >> many thks
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Hi Uri ,
i have used the openrowset but it says "server does not
exist or access denied" which i am using the
administrator's id & pwd
rdgs
>--Original Message--
>Hi
>Do you have specified Server Type in the first tab when
you use EM?
>
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
>> Hi Uri ,
>> thks will try out urs
>> but for my creation script
>> i have used the Enterprise Manager and just specify
the
>> remote user & pwd
>> for the query analyzer on the sql server i used :
>> sp_addlinkedserver 'serverA' -- it's using sql server
so
>> i did not specify the rest of the connection
>> sp_addsvrlogin 'serverA' ,
>> false , 'sa', 'userid' , 'userpassword'
>> for the query analyzer from my local client
computer ,
>> i simply enter the server name , enter the pwd & id and
i
>> get connected
>> >--Original Message--
>> >maxzim
>> >Can you show us your creation script?
>> >This example works without creating a linked server
>> >declare @.pid varchar(30)
>> >set @.pid='2450'
>> >
>> >
>> >select id from openrowset('sqloledb',
>> >'server';'sa';'pass',database.dbo.table)
>> >where id > @.pid
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:680a01c493d3$071362b0$a501280a@.phx.gbl...
>> >> Hi ,
>> >>
>> >> I have tried to add a linked server(sql2000)
residing
>> >> at location B from Location A (a server
computer ,using
>> >> SQL 7.0) but i got the error "general network error ,
>> pls
>> >> check network documentation" I have gotten the
correct
>> >> login id & pwd from the user at the other end.
>> >>
>> >> have also configured the port#(under Client
Utility)
>> >> correctly for client connection and also triedthe
>> >> sp_addlinkedservers & sp_addsvrlogin already
>> >>
>> >> Is there any dll file needed ? such as
DBNET.dll ? to
>> >> add linked server from sql ver 7.0 to sql2000 ?
>> >>
>> >> Does it need any specific version of the odbc
driver
>> >> for SQL Server ? current it's version is 3.70.10.63 i
>> have
>> >> tried to add and odbc link but still have the same
error
>> >>
>> >> I have no problem when using Query Analyzer from my
>> >> client computer AND the user from the other end is
able
>> to
>> >> add my sqlserver successfully as a linked server. The
>> odbc
>> >> driver for SQL Server is 3.85.xx.xx
>> >>
>> >> Appreciate if somebody could help me out as i am
doing
>> >> some data migration which i have set up some DTS
jobs to
>> >> copy data daily over instead of one big chunks the
day
>> >> before migration
>> >>
>> >> many thks
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Hi
Probably this is a permission issue. Make sure that you have full
permissions on destination server.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:673f01c493dc$63f279e0$a601280a@.phx.gbl...
> Hi Uri ,
> i have used the openrowset but it says "server does not
> exist or access denied" which i am using the
> administrator's id & pwd
> rdgs
> >--Original Message--
> >Hi
> >Do you have specified Server Type in the first tab when
> you use EM?
> >
> >
> >
> >
> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
> >> Hi Uri ,
> >> thks will try out urs
> >> but for my creation script
> >>
> >> i have used the Enterprise Manager and just specify
> the
> >> remote user & pwd
> >>
> >> for the query analyzer on the sql server i used :
> >>
> >> sp_addlinkedserver 'serverA' -- it's using sql server
> so
> >> i did not specify the rest of the connection
> >>
> >> sp_addsvrlogin 'serverA' ,
> >> false , 'sa', 'userid' , 'userpassword'
> >>
> >> for the query analyzer from my local client
> computer ,
> >> i simply enter the server name , enter the pwd & id and
> i
> >> get connected
> >> >--Original Message--
> >> >maxzim
> >> >Can you show us your creation script?
> >> >This example works without creating a linked server
> >> >declare @.pid varchar(30)
> >> >set @.pid='2450'
> >> >
> >> >
> >> >select id from openrowset('sqloledb',
> >> >'server';'sa';'pass',database.dbo.table)
> >> >where id > @.pid
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:680a01c493d3$071362b0$a501280a@.phx.gbl...
> >> >> Hi ,
> >> >>
> >> >> I have tried to add a linked server(sql2000)
> residing
> >> >> at location B from Location A (a server
> computer ,using
> >> >> SQL 7.0) but i got the error "general network error ,
> >> pls
> >> >> check network documentation" I have gotten the
> correct
> >> >> login id & pwd from the user at the other end.
> >> >>
> >> >> have also configured the port#(under Client
> Utility)
> >> >> correctly for client connection and also triedthe
> >> >> sp_addlinkedservers & sp_addsvrlogin already
> >> >>
> >> >> Is there any dll file needed ? such as
> DBNET.dll ? to
> >> >> add linked server from sql ver 7.0 to sql2000 ?
> >> >>
> >> >> Does it need any specific version of the odbc
> driver
> >> >> for SQL Server ? current it's version is 3.70.10.63 i
> >> have
> >> >> tried to add and odbc link but still have the same
> error
> >> >>
> >> >> I have no problem when using Query Analyzer from my
> >> >> client computer AND the user from the other end is
> able
> >> to
> >> >> add my sqlserver successfully as a linked server. The
> >> odbc
> >> >> driver for SQL Server is 3.85.xx.xx
> >> >>
> >> >> Appreciate if somebody could help me out as i am
> doing
> >> >> some data migration which i have set up some DTS
> jobs to
> >> >> copy data daily over instead of one big chunks the
> day
> >> >> before migration
> >> >>
> >> >> many thks
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Hi Uri ,
i'll have to check wif the administrator at the other
end then
thks for all the help given =)
>--Original Message--
>Hi
>Probably this is a permission issue. Make sure that you
have full
>permissions on destination server.
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:673f01c493dc$63f279e0$a601280a@.phx.gbl...
>> Hi Uri ,
>> i have used the openrowset but it says "server does not
>> exist or access denied" which i am using the
>> administrator's id & pwd
>> rdgs
>> >--Original Message--
>> >Hi
>> >Do you have specified Server Type in the first tab when
>> you use EM?
>> >
>> >
>> >
>> >
>> >"maxzsim" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:695e01c493d8$fbf0ae50$a301280a@.phx.gbl...
>> >> Hi Uri ,
>> >> thks will try out urs
>> >> but for my creation script
>> >>
>> >> i have used the Enterprise Manager and just
specify
>> the
>> >> remote user & pwd
>> >>
>> >> for the query analyzer on the sql server i used :
>> >>
>> >> sp_addlinkedserver 'serverA' -- it's using sql
server
>> so
>> >> i did not specify the rest of the connection
>> >>
>> >> sp_addsvrlogin 'serverA' ,
>> >> false , 'sa', 'userid' , 'userpassword'
>> >>
>> >> for the query analyzer from my local client
>> computer ,
>> >> i simply enter the server name , enter the pwd & id
and
>> i
>> >> get connected
>> >> >--Original Message--
>> >> >maxzim
>> >> >Can you show us your creation script?
>> >> >This example works without creating a linked server
>> >> >declare @.pid varchar(30)
>> >> >set @.pid='2450'
>> >> >
>> >> >
>> >> >select id from openrowset('sqloledb',
>> >> >'server';'sa';'pass',database.dbo.table)
>> >> >where id > @.pid
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >"maxzsim" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> message
>> >> >news:680a01c493d3$071362b0$a501280a@.phx.gbl...
>> >> >> Hi ,
>> >> >>
>> >> >> I have tried to add a linked server(sql2000)
>> residing
>> >> >> at location B from Location A (a server
>> computer ,using
>> >> >> SQL 7.0) but i got the error "general network
error ,
>> >> pls
>> >> >> check network documentation" I have gotten the
>> correct
>> >> >> login id & pwd from the user at the other end.
>> >> >>
>> >> >> have also configured the port#(under Client
>> Utility)
>> >> >> correctly for client connection and also triedthe
>> >> >> sp_addlinkedservers & sp_addsvrlogin already
>> >> >>
>> >> >> Is there any dll file needed ? such as
>> DBNET.dll ? to
>> >> >> add linked server from sql ver 7.0 to sql2000 ?
>> >> >>
>> >> >> Does it need any specific version of the odbc
>> driver
>> >> >> for SQL Server ? current it's version is
3.70.10.63 i
>> >> have
>> >> >> tried to add and odbc link but still have the same
>> error
>> >> >>
>> >> >> I have no problem when using Query Analyzer
from my
>> >> >> client computer AND the user from the other end is
>> able
>> >> to
>> >> >> add my sqlserver successfully as a linked server.
The
>> >> odbc
>> >> >> driver for SQL Server is 3.85.xx.xx
>> >> >>
>> >> >> Appreciate if somebody could help me out as i am
>> doing
>> >> >> some data migration which i have set up some DTS
>> jobs to
>> >> >> copy data daily over instead of one big chunks the
>> day
>> >> >> before migration
>> >> >>
>> >> >> many thks
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>