Linked Server Problems HELP HELP HELP !

having a LOT of problems with SQL server linked servers.
I have a server running Windows Server 2003. (Server A)
I want to run a query from a different server (Server B) using query
analyser.
E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
keep getting the following message
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
looked through various pages/groups etc.
my authentication mode is set to mixed.
still getting the message.
starting to annoy me - a LOT.!!
Help please!!!
THanks in advance.Paul
Can you post a script for set up linked server?
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegroups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>|||how?|||This example of set up linekd server to Access (Lookup these SP in the BOL)
USE master
GO
EXEC sp_addlinkedserver
@.server = 'test_Access1',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\WINDOWS\Desktop\uritennis.mdb'
GO
sp_linkedservers
sp_dropserver 'test_Access1', 'droplogins'
USE master
GO
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'test_Access1',
@.useself = false,
@.locallogin = null,
@.rmtuser ='admin',
@.rmtpassword =null
USE master
GO
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
<paul_30s@.hotmail.com> wrote in message
news:1140522241.310199.137970@.g47g2000cwa.googlegroups.com...
> how?
>|||done all this stuff. still same problem.
is there anything on the server that i need to check ?|||Have you ran it on linked server?
exec sp_serveroption 'SERVER','data access','true'
<paul_30s@.hotmail.com> wrote in message
news:1140523365.016551.291200@.g44g2000cwa.googlegroups.com...
> done all this stuff. still same problem.
> is there anything on the server that i need to check ?
>|||still the same.|||It seems like YOUR server is using mixed authorization but LINKED server is
not (trusted connections only)
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegroups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>|||both servers are using mixed authentication.|||paul_30s@.hotmail.com wrote:
> still the same.
>
Have you tried using a SQL user instead of an Windows/NT user account?
If you want to use integrated security (windows user) you have to ensure
that security account delegation is enabled (and that's something that
has to be set up in your Active Directory).
You can read more about security account delegation in Books On Line -
look for "Security Account Delegation".
Regards
Steen

Linked Server Problems HELP HELP HELP !

having a LOT of problems with SQL server linked servers.
I have a server running Windows Server 2003. (Server A)
I want to run a query from a different server (Server B) using query
analyser.
E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
keep getting the following message
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
looked through various pages/groups etc.
my authentication mode is set to mixed.
still getting the message.
starting to annoy me - a LOT.!!
Help please!!!
THanks in advance.
Paul
Can you post a script for set up linked server?
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegr oups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>
|||how?
|||This example of set up linekd server to Access (Lookup these SP in the BOL)
USE master
GO
EXEC sp_addlinkedserver
@.server = 'test_Access1',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\WINDOWS\Desktop\uritennis.mdb'
GO
sp_linkedservers
sp_dropserver 'test_Access1', 'droplogins'
USE master
GO
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'test_Access1',
@.useself = false,
@.locallogin = null,
@.rmtuser ='admin',
@.rmtpassword =null
USE master
GO
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
<paul_30s@.hotmail.com> wrote in message
news:1140522241.310199.137970@.g47g2000cwa.googlegr oups.com...
> how?
>
|||done all this stuff. still same problem.
is there anything on the server that i need to check ?
|||Have you ran it on linked server?
exec sp_serveroption 'SERVER','data access','true'
<paul_30s@.hotmail.com> wrote in message
news:1140523365.016551.291200@.g44g2000cwa.googlegr oups.com...
> done all this stuff. still same problem.
> is there anything on the server that i need to check ?
>
|||still the same.
|||It seems like YOUR server is using mixed authorization but LINKED server is
not (trusted connections only)
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegr oups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>
|||both servers are using mixed authentication.
|||paul_30s@.hotmail.com wrote:
> still the same.
>
Have you tried using a SQL user instead of an Windows/NT user account?
If you want to use integrated security (windows user) you have to ensure
that security account delegation is enabled (and that's something that
has to be set up in your Active Directory).
You can read more about security account delegation in Books On Line -
look for "Security Account Delegation".
Regards
Steen

Linked Server Problems HELP HELP HELP !

having a LOT of problems with SQL server linked servers.
I have a server running Windows Server 2003. (Server A)
I want to run a query from a different server (Server B) using query
analyser.
E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
keep getting the following message
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
looked through various pages/groups etc.
my authentication mode is set to mixed.
still getting the message.
starting to annoy me - a LOT.!!
Help please!!!
THanks in advance.Paul
Can you post a script for set up linked server?
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegroups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>|||how?|||This example of set up linekd server to Access (Lookup these SP in the BOL)
USE master
GO
EXEC sp_addlinkedserver
@.server = 'test_Access1',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\WINDOWS\Desktop\uritennis.mdb'
GO
sp_linkedservers
sp_dropserver 'test_Access1', 'droplogins'
USE master
GO
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'test_Access1',
@.useself = false,
@.locallogin = null,
@.rmtuser ='admin',
@.rmtpassword =null
USE master
GO
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
<paul_30s@.hotmail.com> wrote in message
news:1140522241.310199.137970@.g47g2000cwa.googlegroups.com...
> how?
>|||done all this stuff. still same problem.
is there anything on the server that i need to check ?|||Have you ran it on linked server?
exec sp_serveroption 'SERVER','data access','true'
<paul_30s@.hotmail.com> wrote in message
news:1140523365.016551.291200@.g44g2000cwa.googlegroups.com...
> done all this stuff. still same problem.
> is there anything on the server that i need to check ?
>|||still the same.|||It seems like YOUR server is using mixed authorization but LINKED server is
not (trusted connections only)
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegroups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>|||both servers are using mixed authentication.|||paul_30s@.hotmail.com wrote:
> still the same.
>
Have you tried using a SQL user instead of an Windows/NT user account?
If you want to use integrated security (windows user) you have to ensure
that security account delegation is enabled (and that's something that
has to be set up in your Active Directory).
You can read more about security account delegation in Books On Line -
look for "Security Account Delegation".
Regards
Steen|||tried this. still does not work.|||It works with a SQL server login.
I had our network administrator setup the active directory stuff but i
am still getting the same problem.|||Take a look at this link there is a setting in the DTC Configuration,
that might be your problem.
http://www.dotnetjunkies.com/WebLog/daveadamson/archive/2005/11/22/133928.aspxsql

Linked Server Problems from SQL2005

When I run linked server queries from SQL2005 it fails.
Query Example :
SELECT * FROM [servername].master.dbo.sysobjects
Error:
OLE DB provider "SQLNCLI" for linked server " servername" returned
message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote
host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
Notes:
The linked server is configured to use the current logins security
context. It does not matter whether I connect to SQL2000 or SQL2005.
I have a case with MS (6 hours on the phone yesterday), but no help
yet. All spn settings are as recommended by Microsoft. The MS engineer
states that SQL is not passing the login as indicated in a capture file
from netcap.
Any help is appreciated.
TerryHi
Is the remote server SQL2005? Have you tried using a SQL Server login? Can
you use openquery to return the result?
John
"Terry" wrote:

> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>|||Terry wrote:
> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>
You are connecting to ServerA using Windows Authentication, not a SQL
login. The linked server connection is attempting to pass your SQL
login over to ServerB. You'll have to login to ServerA using a true SQL
login, or setup an explicit user mapping in the linked server definition.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I should be able to use Windows Authenitication, and have always in the
past with SQL2000. Auditing concerns require I know who is connecting.
Mapped logins do work but this is not a solution.
The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
Terry
Tracy McKibben wrote:
> Terry wrote:
> You are connecting to ServerA using Windows Authentication, not a SQL
> login. The linked server connection is attempting to pass your SQL
> login over to ServerB. You'll have to login to ServerA using a true SQL
> login, or setup an explicit user mapping in the linked server definition.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||On the 2000 server, run the instcat.sql from the latest service pack you
have installed. This fixes the connect strings. You have to edit the
instcat.sql to put in the correct SQL version. This is documented in the
instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
your problem.
Jackie
"Terry" <tduffy@.calamos.com> wrote in message
news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...
>I should be able to use Windows Authenitication, and have always in the
> past with SQL2000. Auditing concerns require I know who is connecting.
> Mapped logins do work but this is not a solution.
> The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
> Terry
> Tracy McKibben wrote:
>|||Thank you for the responses...
instcat.sql fixes the schema binding errors on 2005->2000 servers. It
does not resolve my issues. instcat has already been run on my 2000
boxes.
Terry
Jackie Brophy wrote:[vbcol=seagreen]
> On the 2000 server, run the instcat.sql from the latest service pack you
> have installed. This fixes the connect strings. You have to edit the
> instcat.sql to put in the correct SQL version. This is documented in the
> instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
> your problem.
> Jackie
> "Terry" <tduffy@.calamos.com> wrote in message
> news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...|||Hi Terry
I assume that you are using the latest service packs on both instances?
Previously this error has been the result of a virus checker or firewall
blocking communications or possibly a faulty network. Do you get any command
s
running on the remote system?
Windows authentication should not require the user to map a linked server
login, but you can run it with the remote server name parameter only to make
sure these are mapped.
You may want to try and check what protocols you are using and try a
different one!
John
"Terry" wrote:

> Thank you for the responses...
> instcat.sql fixes the schema binding errors on 2005->2000 servers. It
> does not resolve my issues. instcat has already been run on my 2000
> boxes.
> Terry
> Jackie Brophy wrote:
>|||We are still on RTM SQL05, but similar issues (double hops only) exist
on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
Firewall and anti-virus are not any issue. I am not sure what you mean
by "commands
running on the remote system." Also, any ideas what might be "faulty"
in the network.
Linked servers with mapped logins works.
Thanks
John Bell wrote:[vbcol=seagreen]
> Hi Terry
> I assume that you are using the latest service packs on both instances?
> Previously this error has been the result of a virus checker or firewall
> blocking communications or possibly a faulty network. Do you get any comma
nds
> running on the remote system?
> Windows authentication should not require the user to map a linked server
> login, but you can run it with the remote server name parameter only to ma
ke
> sure these are mapped.
> You may want to try and check what protocols you are using and try a
> different one!
> John
> "Terry" wrote:
>|||Hi Terry
Use SQL profiler on both machines to try and see what is happening. Did you
check the protocols being used?
http://msdn2.microsoft.com/en-us/library/ms175496.aspx
You may want to make sure that this still occurs on the latest hotfixes for
each version. If that fails you may want to raise an incident with PSS for
this.
John
"Terry" wrote:

> We are still on RTM SQL05, but similar issues (double hops only) exist
> on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
> Firewall and anti-virus are not any issue. I am not sure what you mean
> by "commands
> running on the remote system." Also, any ideas what might be "faulty"
> in the network.
> Linked servers with mapped logins works.
> Thanks
>
> John Bell wrote:
>

Linked Server problem

I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
run a query from Server A on Server B but if I do it the other way I get an
error
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
Why am I getting this error? I can see the Servers entry in Enterprise
Manager in the Linked servers container.I would like to add that this problem started when I moved eevrything from
Server Z to Server A (all system and user databases) and the old name of
server Z was not retained. Earlier the link between Server Z and Server B was
working fine in both the direstions. The sa login and the password is till
the same.
"sharman" wrote:
> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> run a query from Server A on Server B but if I do it the other way I get an
> error
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> Why am I getting this error? I can see the Servers entry in Enterprise
> Manager in the Linked servers container.|||Did you use sp_dropserver and sp_addserver, LOCAL after the move?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>I would like to add that this problem started when I moved eevrything from
> Server Z to Server A (all system and user databases) and the old name of
> server Z was not retained. Earlier the link between Server Z and Server B was
> working fine in both the direstions. The sa login and the password is till
> the same.
> "sharman" wrote:
>> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> run a query from Server A on Server B but if I do it the other way I get an
>> error
>> Server: Msg 17, Level 16, State 1, Line 1
>> SQL Server does not exist or access denied.
>> Why am I getting this error? I can see the Servers entry in Enterprise
>> Manager in the Linked servers container.|||Which server do I have to do this? Is this to be done on the new server on
which I moved all the system and user databases from the old server? Thanks.
"Tibor Karaszi" wrote:
> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >I would like to add that this problem started when I moved eevrything from
> > Server Z to Server A (all system and user databases) and the old name of
> > server Z was not retained. Earlier the link between Server Z and Server B was
> > working fine in both the direstions. The sa login and the password is till
> > the same.
> >
> > "sharman" wrote:
> >
> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> run a query from Server A on Server B but if I do it the other way I get an
> >> error
> >>
> >> Server: Msg 17, Level 16, State 1, Line 1
> >> SQL Server does not exist or access denied.
> >>
> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> Manager in the Linked servers container.
>|||Yep. On the new server, SQL Server think its name is the old servers name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> Which server do I have to do this? Is this to be done on the new server on
> which I moved all the system and user databases from the old server? Thanks.
> "Tibor Karaszi" wrote:
>> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >I would like to add that this problem started when I moved eevrything from
>> > Server Z to Server A (all system and user databases) and the old name of
>> > server Z was not retained. Earlier the link between Server Z and Server B was
>> > working fine in both the direstions. The sa login and the password is till
>> > the same.
>> >
>> > "sharman" wrote:
>> >
>> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> error
>> >>
>> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> SQL Server does not exist or access denied.
>> >>
>> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> Manager in the Linked servers container.
>>|||Thanks for the reply. I did only the following after the move:
Change the name of the originating server in the field originating_server_id
on sysjobs table in msdb database to the new server because it still had the
name of the old server. I was not able to edit change job settings but after
the change I was able to.
Now I will also run sp_dropserver and sp_addserver, LOCAL
Is there anything else that I am forgetting that might cause a problem later
on? Thanks in advance.
"Tibor Karaszi" wrote:
> Yep. On the new server, SQL Server think its name is the old servers name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> > Which server do I have to do this? Is this to be done on the new server on
> > which I moved all the system and user databases from the old server? Thanks.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >I would like to add that this problem started when I moved eevrything from
> >> > Server Z to Server A (all system and user databases) and the old name of
> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> > working fine in both the direstions. The sa login and the password is till
> >> > the same.
> >> >
> >> > "sharman" wrote:
> >> >
> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> error
> >> >>
> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> SQL Server does not exist or access denied.
> >> >>
> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> Manager in the Linked servers container.
> >>
> >>
>|||I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
article on the subject, but I believe you covered what has to be done already:
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> Thanks for the reply. I did only the following after the move:
> Change the name of the originating server in the field originating_server_id
> on sysjobs table in msdb database to the new server because it still had the
> name of the old server. I was not able to edit change job settings but after
> the change I was able to.
> Now I will also run sp_dropserver and sp_addserver, LOCAL
> Is there anything else that I am forgetting that might cause a problem later
> on? Thanks in advance.
> "Tibor Karaszi" wrote:
>> Yep. On the new server, SQL Server think its name is the old servers name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
>> > Which server do I have to do this? Is this to be done on the new server on
>> > which I moved all the system and user databases from the old server? Thanks.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >> >I would like to add that this problem started when I moved eevrything from
>> >> > Server Z to Server A (all system and user databases) and the old name of
>> >> > server Z was not retained. Earlier the link between Server Z and Server B was
>> >> > working fine in both the direstions. The sa login and the password is till
>> >> > the same.
>> >> >
>> >> > "sharman" wrote:
>> >> >
>> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> >> error
>> >> >>
>> >> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> >> SQL Server does not exist or access denied.
>> >> >>
>> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> >> Manager in the Linked servers container.
>> >>
>> >>
>>|||I did as you have mentioned in the post and I also ran
sp_serveroption '<server_name>','data access','true'
Even after running these I get the following error when I try to parse a
query in an ExecuteSQL task in DTS that is connected to the this server ( The
DTS is on another server):
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
exist or access denied.
"Tibor Karaszi" wrote:
> I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
> article on the subject, but I believe you covered what has to be done already:
> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> > Thanks for the reply. I did only the following after the move:
> >
> > Change the name of the originating server in the field originating_server_id
> > on sysjobs table in msdb database to the new server because it still had the
> > name of the old server. I was not able to edit change job settings but after
> > the change I was able to.
> >
> > Now I will also run sp_dropserver and sp_addserver, LOCAL
> >
> > Is there anything else that I am forgetting that might cause a problem later
> > on? Thanks in advance.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Yep. On the new server, SQL Server think its name is the old servers name.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> >> > Which server do I have to do this? Is this to be done on the new server on
> >> > which I moved all the system and user databases from the old server? Thanks.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >> >I would like to add that this problem started when I moved eevrything from
> >> >> > Server Z to Server A (all system and user databases) and the old name of
> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> >> > working fine in both the direstions. The sa login and the password is till
> >> >> > the same.
> >> >> >
> >> >> > "sharman" wrote:
> >> >> >
> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> >> error
> >> >> >>
> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> >> SQL Server does not exist or access denied.
> >> >> >>
> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> >> Manager in the Linked servers container.
> >> >>
> >> >>
> >>
> >>
>|||I suggest you first logon to the local server from a query window and execute the statement that
accesses the linked server. This to simplify the scenario and try to localize where the problem
might be.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
>I did as you have mentioned in the post and I also ran
> sp_serveroption '<server_name>','data access','true'
> Even after running these I get the following error when I try to parse a
> query in an ExecuteSQL task in DTS that is connected to the this server ( The
> DTS is on another server):
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
> exist or access denied.
> "Tibor Karaszi" wrote:
>> I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
>> article on the subject, but I believe you covered what has to be done already:
>> http://www.karaszi.com/SQLServer/info_change_server_name.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
>> > Thanks for the reply. I did only the following after the move:
>> >
>> > Change the name of the originating server in the field originating_server_id
>> > on sysjobs table in msdb database to the new server because it still had the
>> > name of the old server. I was not able to edit change job settings but after
>> > the change I was able to.
>> >
>> > Now I will also run sp_dropserver and sp_addserver, LOCAL
>> >
>> > Is there anything else that I am forgetting that might cause a problem later
>> > on? Thanks in advance.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Yep. On the new server, SQL Server think its name is the old servers name.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
>> >> > Which server do I have to do this? Is this to be done on the new server on
>> >> > which I moved all the system and user databases from the old server? Thanks.
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >> >> >I would like to add that this problem started when I moved eevrything from
>> >> >> > Server Z to Server A (all system and user databases) and the old name of
>> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
>> >> >> > working fine in both the direstions. The sa login and the password is till
>> >> >> > the same.
>> >> >> >
>> >> >> > "sharman" wrote:
>> >> >> >
>> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> >> >> error
>> >> >> >>
>> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> >> >> SQL Server does not exist or access denied.
>> >> >> >>
>> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> >> >> Manager in the Linked servers container.
>> >> >>
>> >> >>
>> >>
>> >>
>>|||It is only when I try to parse a query in an ExecuteSQL task in DTS that is
connected to this server that I get an error.
When I run a select statement I do not get any error. Thanks.
"Tibor Karaszi" wrote:
> I suggest you first logon to the local server from a query window and execute the statement that
> accesses the linked server. This to simplify the scenario and try to localize where the problem
> might be.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
> >I did as you have mentioned in the post and I also ran
> > sp_serveroption '<server_name>','data access','true'
> >
> > Even after running these I get the following error when I try to parse a
> > query in an ExecuteSQL task in DTS that is connected to the this server ( The
> > DTS is on another server):
> >
> > Error Source: Microsoft OLE DB Provider for SQL Server
> > Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
> > exist or access denied.
> >
> > "Tibor Karaszi" wrote:
> >
> >> I think the two should take care of it, unless you are in a cluster or do replication. Here's ay
> >> article on the subject, but I believe you covered what has to be done already:
> >> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> >> > Thanks for the reply. I did only the following after the move:
> >> >
> >> > Change the name of the originating server in the field originating_server_id
> >> > on sysjobs table in msdb database to the new server because it still had the
> >> > name of the old server. I was not able to edit change job settings but after
> >> > the change I was able to.
> >> >
> >> > Now I will also run sp_dropserver and sp_addserver, LOCAL
> >> >
> >> > Is there anything else that I am forgetting that might cause a problem later
> >> > on? Thanks in advance.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Yep. On the new server, SQL Server think its name is the old servers name.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> >> >> > Which server do I have to do this? Is this to be done on the new server on
> >> >> > which I moved all the system and user databases from the old server? Thanks.
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >> >> >I would like to add that this problem started when I moved eevrything from
> >> >> >> > Server Z to Server A (all system and user databases) and the old name of
> >> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> >> >> > working fine in both the direstions. The sa login and the password is till
> >> >> >> > the same.
> >> >> >> >
> >> >> >> > "sharman" wrote:
> >> >> >> >
> >> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> >> >> error
> >> >> >> >>
> >> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> >> >> SQL Server does not exist or access denied.
> >> >> >> >>
> >> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> >> >> Manager in the Linked servers container.
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>|||So you have some query in a DTS ExecuteSQL task. If you take that query and run it from Query
Analyzer it executed OK. But executed from the ExecuteSQL task it fails. If so, you need to look at
the properties if the ExecuteSQL task. Is the servername correctly specified? Login attributes? You
might want to continue this discussion in a DTS group (I'm no DTS expert myself).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:6B13D344-B6A2-43AA-84B8-50489A9245B6@.microsoft.com...
> It is only when I try to parse a query in an ExecuteSQL task in DTS that is
> connected to this server that I get an error.
> When I run a select statement I do not get any error. Thanks.
> "Tibor Karaszi" wrote:
>> I suggest you first logon to the local server from a query window and execute the statement that
>> accesses the linked server. This to simplify the scenario and try to localize where the problem
>> might be.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
>> >I did as you have mentioned in the post and I also ran
>> > sp_serveroption '<server_name>','data access','true'
>> >
>> > Even after running these I get the following error when I try to parse a
>> > query in an ExecuteSQL task in DTS that is connected to the this server ( The
>> > DTS is on another server):
>> >
>> > Error Source: Microsoft OLE DB Provider for SQL Server
>> > Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
>> > exist or access denied.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I think the two should take care of it, unless you are in a cluster or do replication. Here's
>> >> ay
>> >> article on the subject, but I believe you covered what has to be done already:
>> >> http://www.karaszi.com/SQLServer/info_change_server_name.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
>> >> > Thanks for the reply. I did only the following after the move:
>> >> >
>> >> > Change the name of the originating server in the field originating_server_id
>> >> > on sysjobs table in msdb database to the new server because it still had the
>> >> > name of the old server. I was not able to edit change job settings but after
>> >> > the change I was able to.
>> >> >
>> >> > Now I will also run sp_dropserver and sp_addserver, LOCAL
>> >> >
>> >> > Is there anything else that I am forgetting that might cause a problem later
>> >> > on? Thanks in advance.
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Yep. On the new server, SQL Server think its name is the old servers name.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
>> >> >> > Which server do I have to do this? Is this to be done on the new server on
>> >> >> > which I moved all the system and user databases from the old server? Thanks.
>> >> >> >
>> >> >> > "Tibor Karaszi" wrote:
>> >> >> >
>> >> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >> >>
>> >> >> >>
>> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> >> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
>> >> >> >> >I would like to add that this problem started when I moved eevrything from
>> >> >> >> > Server Z to Server A (all system and user databases) and the old name of
>> >> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
>> >> >> >> > working fine in both the direstions. The sa login and the password is till
>> >> >> >> > the same.
>> >> >> >> >
>> >> >> >> > "sharman" wrote:
>> >> >> >> >
>> >> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
>> >> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
>> >> >> >> >> error
>> >> >> >> >>
>> >> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
>> >> >> >> >> SQL Server does not exist or access denied.
>> >> >> >> >>
>> >> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
>> >> >> >> >> Manager in the Linked servers container.
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>|||Thanks Tibor for all your help. I'll certainly post this in the DTS group.
"Tibor Karaszi" wrote:
> So you have some query in a DTS ExecuteSQL task. If you take that query and run it from Query
> Analyzer it executed OK. But executed from the ExecuteSQL task it fails. If so, you need to look at
> the properties if the ExecuteSQL task. Is the servername correctly specified? Login attributes? You
> might want to continue this discussion in a DTS group (I'm no DTS expert myself).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:6B13D344-B6A2-43AA-84B8-50489A9245B6@.microsoft.com...
> > It is only when I try to parse a query in an ExecuteSQL task in DTS that is
> > connected to this server that I get an error.
> >
> > When I run a select statement I do not get any error. Thanks.
> >
> > "Tibor Karaszi" wrote:
> >
> >> I suggest you first logon to the local server from a query window and execute the statement that
> >> accesses the linked server. This to simplify the scenario and try to localize where the problem
> >> might be.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> news:11010AFE-6664-4BD8-B2F5-DD504EEDD568@.microsoft.com...
> >> >I did as you have mentioned in the post and I also ran
> >> > sp_serveroption '<server_name>','data access','true'
> >> >
> >> > Even after running these I get the following error when I try to parse a
> >> > query in an ExecuteSQL task in DTS that is connected to the this server ( The
> >> > DTS is on another server):
> >> >
> >> > Error Source: Microsoft OLE DB Provider for SQL Server
> >> > Error Description:[DNETLIB][ConnectionOpen(Connect()).]SQL Server does not
> >> > exist or access denied.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> I think the two should take care of it, unless you are in a cluster or do replication. Here's
> >> >> ay
> >> >> article on the subject, but I believe you covered what has to be done already:
> >> >> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> news:59D1843B-9513-41EF-BB9A-533C0BE90B51@.microsoft.com...
> >> >> > Thanks for the reply. I did only the following after the move:
> >> >> >
> >> >> > Change the name of the originating server in the field originating_server_id
> >> >> > on sysjobs table in msdb database to the new server because it still had the
> >> >> > name of the old server. I was not able to edit change job settings but after
> >> >> > the change I was able to.
> >> >> >
> >> >> > Now I will also run sp_dropserver and sp_addserver, LOCAL
> >> >> >
> >> >> > Is there anything else that I am forgetting that might cause a problem later
> >> >> > on? Thanks in advance.
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Yep. On the new server, SQL Server think its name is the old servers name.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> >> news:B458BA70-E6F9-4A11-BF66-626D0872808A@.microsoft.com...
> >> >> >> > Which server do I have to do this? Is this to be done on the new server on
> >> >> >> > which I moved all the system and user databases from the old server? Thanks.
> >> >> >> >
> >> >> >> > "Tibor Karaszi" wrote:
> >> >> >> >
> >> >> >> >> Did you use sp_dropserver and sp_addserver, LOCAL after the move?
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> >> >> >> >> news:B86B4D1F-C098-473F-AF5E-7AB4B49077CB@.microsoft.com...
> >> >> >> >> >I would like to add that this problem started when I moved eevrything from
> >> >> >> >> > Server Z to Server A (all system and user databases) and the old name of
> >> >> >> >> > server Z was not retained. Earlier the link between Server Z and Server B was
> >> >> >> >> > working fine in both the direstions. The sa login and the password is till
> >> >> >> >> > the same.
> >> >> >> >> >
> >> >> >> >> > "sharman" wrote:
> >> >> >> >> >
> >> >> >> >> >> I have two SQL Servers running SQL 2000 SP4 and SP3 that are linked. I can
> >> >> >> >> >> run a query from Server A on Server B but if I do it the other way I get an
> >> >> >> >> >> error
> >> >> >> >> >>
> >> >> >> >> >> Server: Msg 17, Level 16, State 1, Line 1
> >> >> >> >> >> SQL Server does not exist or access denied.
> >> >> >> >> >>
> >> >> >> >> >> Why am I getting this error? I can see the Servers entry in Enterprise
> >> >> >> >> >> Manager in the Linked servers container.
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>

linked server problem

I am trying to run this in the SQL Query Analyzer. I am running it from SQL
Server 2000 which links to a MSDE 2000 Server.
declare @.StartDate as nvarchar(12)
declare @.EndDate as nvarchar(12)
declare @.EmpID as nvarchar(4000)
declare @.query1 as varchar(8000)
declare @.empWhere as varchar(4500)
set @.StartDate='7/22/2003'
set @.EndDate='7/22/2003'
set @.EmpID='123456'
if @.EmpID='ALL'
set @.empWhere=''
else
set @.empWhere= ' AND (bh.EmployeeNumber in (''' +
replace(@.EmpID,',',''',''') + ''')) '
set @.query1 = 'SELECT p.FirstName, p.LastName,
dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
p.Id, ' +
'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
'BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID ' +
'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
@.StartDate +
''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) '+ @.empWhere +
'UNION ALL ' +
'SELECT p.FirstName, p.LastName,
dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
p.Id, ' +
'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
'BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID ' +
'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
@.StartDate +
''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) ' + @.empWhere +
'ORDER BY displayname,PunchDate'
print @.query1
EXEC(@.query1)
SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate - 2) AS
Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
INNER JOIN BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
UNION ALL SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate -
2) AS Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
INNER JOIN BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
ORDER BY p.LastName,p.FirstName,PunchDate
This causes the following error:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
Server]Statement(s) could not be prepared.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
Server]Line 1: Incorrect syntax near 'Id'.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
Server]Unclosed quotation mark before the character string 'Id'.]
We have been able to determine that the error is only generated when we add
a where clause for a text based field. So, if we remove the "AND
(bh.EmployeeNumber in ('123456')) " or use a numeric based field, the query
works correctly. Also, if we run the query from the linked server, the
query works.I still don't know what is causing the error, but i have been able to find a
workaround by using openquery.
"Kent Elmer" <kent_elmer@.nospamforme.com> wrote in message
news:Ojap6xUUDHA.1052@.TK2MSFTNGP09.phx.gbl...
> I am trying to run this in the SQL Query Analyzer. I am running it from
SQL
> Server 2000 which links to a MSDE 2000 Server.
> declare @.StartDate as nvarchar(12)
> declare @.EndDate as nvarchar(12)
> declare @.EmpID as nvarchar(4000)
> declare @.query1 as varchar(8000)
> declare @.empWhere as varchar(4500)
> set @.StartDate='7/22/2003'
> set @.EndDate='7/22/2003'
> set @.EmpID='123456'
> if @.EmpID='ALL'
> set @.empWhere=''
> else
> set @.empWhere= ' AND (bh.EmployeeNumber in (''' +
> replace(@.EmpID,',',''',''') + ''')) '
> set @.query1 = 'SELECT p.FirstName, p.LastName,
> dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
> CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
> p.Id, ' +
> 'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
> BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
> 'BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id
=> bh.PersonID ' +
> 'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
> @.StartDate +
> ''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) '+ @.empWhere +
> 'UNION ALL ' +
> 'SELECT p.FirstName, p.LastName,
> dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
> CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
> p.Id, ' +
> 'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
> BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
> 'BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id
=> bh.PersonID ' +
> 'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
> @.StartDate +
> ''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) ' + @.empWhere +
> 'ORDER BY displayname,PunchDate'
> print @.query1
> EXEC(@.query1)
> SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate - 2) AS
> Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
> bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
> INNER JOIN BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id => bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
> AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
> UNION ALL SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate -
> 2) AS Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
> bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
> INNER JOIN BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id => bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
> AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
> ORDER BY p.LastName,p.FirstName,PunchDate
> This causes the following error:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'MSDASQL'.
> [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Statement(s) could not be prepared.]
> [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Line 1: Incorrect syntax near 'Id'.]
> [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Unclosed quotation mark before the character string 'Id'.]
> We have been able to determine that the error is only generated when we
add
> a where clause for a text based field. So, if we remove the "AND
> (bh.EmployeeNumber in ('123456')) " or use a numeric based field, the
query
> works correctly. Also, if we run the query from the linked server, the
> query works.
>sql

linked server permissions

I have a query on a web site that I'm trying to run. I get this error: Access
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
Dan D.
If you are using linked server, check this out....
http://databasejournal.com/features/...le.php/3116011
Thanks
GYK
"Dan D." wrote:

> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.
|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features/...le.php/3116011
> Thanks
> GYK
> "Dan D." wrote:

linked server permissions

I have a query on a web site that I'm trying to run. I get this error: Acces
s
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features...cle.php/3116011
Thanks
GYK
"Dan D." wrote:

> I have a query on a web site that I'm trying to run. I get this error: Acc
ess
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features...cle.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
>

linked server permissions

I have a query on a web site that I'm trying to run. I get this error: Access
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features/mssql/article.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
> If you are using linked server, check this out....
> http://databasejournal.com/features/mssql/article.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
> > I have a query on a web site that I'm trying to run. I get this error: Access
> > to the remote server is denied because no login-mapping exists.
> >
> > I can run the query as myself (sysadmin). The user that has problems only
> > has db_datareader and db_datawriter permissions. Does this user need
> > different permissions?
> >
> > Thanks,
> > --
> > Dan D.

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?

> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?
> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>sql

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
Kevin
Anyone? Buehler?

> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>

Linked server name

Microsoft computer names can have hyphens in the name.
When I run Sp_Addlinkedserver 'server-name' it works fine.
When I try to use it in something like "Select * from
server-name.<dbName>.dbo.<tablename>" I get a sytax error
on the hyphen. anyone see this before? did all kinds of
searches in the KB and didn't see anthing.You'll need to use [ ] to surround your server name e.g.
select * from [server-name].<dbName>.dbo.<tablename>
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:041801c3d620$4216bdb0$a101280a@.phx.gbl...
> Microsoft computer names can have hyphens in the name.
> When I run Sp_Addlinkedserver 'server-name' it works fine.
> When I try to use it in something like "Select * from
> server-name.<dbName>.dbo.<tablename>" I get a sytax error
> on the hyphen. anyone see this before? did all kinds of
> searches in the KB and didn't see anthing.|||Does it help to enclose the server name in square
brackets? (i.e., [server-name].dbname.dbo.tablename)
Tim
>--Original Message--
>Microsoft computer names can have hyphens in the name.
>When I run Sp_Addlinkedserver 'server-name' it works
fine.
>When I try to use it in something like "Select * from
>server-name.<dbName>.dbo.<tablename>" I get a sytax
error
>on the hyphen. anyone see this before? did all kinds of
>searches in the KB and didn't see anthing.
>.
>|||>--Original Message--
>Does it help to enclose the server name in square
>brackets? (i.e., [server-name].dbname.dbo.tablename)
>Tim
>
>>--Original Message--
>>Microsoft computer names can have hyphens in the name.
>>When I run Sp_Addlinkedserver 'server-name' it works
>fine.
>>When I try to use it in something like "Select * from
>>server-name.<dbName>.dbo.<tablename>" I get a sytax
>error
>>on the hyphen. anyone see this before? did all kinds of
>>searches in the KB and didn't see anthing.
>>.
>.
>|||Thanks Guys that did it!
>--Original Message--
>Does it help to enclose the server name in square
>brackets? (i.e., [server-name].dbname.dbo.tablename)
>Tim
>
>>--Original Message--
>>Microsoft computer names can have hyphens in the name.
>>When I run Sp_Addlinkedserver 'server-name' it works
>fine.
>>When I try to use it in something like "Select * from
>>server-name.<dbName>.dbo.<tablename>" I get a sytax
>error
>>on the hyphen. anyone see this before? did all kinds of
>>searches in the KB and didn't see anthing.
>>.
>.
>

Linked server issue

I have a SQL Server linked to a non-SQL server using ODBC on our
internal network. Every query I run on the linked server from SQL
Server takes exactly 1 minute longer than to run the query via ASP
page and ODBC. I'm suspecting some strange handshake is going on when
the server is linked, and was wondering if there was a way around it?
I'd rather not tap into the remote server via ASP pages, but rather
have the ASP pages connect to the SQL Server.
Thanks
Cate
Do you run this query using 4 part naming or OPENQUERY?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked server issue

I have a SQL Server linked to a non-SQL server using ODBC on our
internal network. Every query I run on the linked server from SQL
Server takes exactly 1 minute longer than to run the query via ASP
page and ODBC. I'm suspecting some strange handshake is going on when
the server is linked, and was wondering if there was a way around it?
I'd rather not tap into the remote server via ASP pages, but rather
have the ASP pages connect to the SQL Server.
Thanks
CateDo you run this query using 4 part naming or OPENQUERY?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked server issue

I have a SQL Server linked to a non-SQL server using ODBC on our
internal network. Every query I run on the linked server from SQL
Server takes exactly 1 minute longer than to run the query via ASP
page and ODBC. I'm suspecting some strange handshake is going on when
the server is linked, and was wondering if there was a way around it?
I'd rather not tap into the remote server via ASP pages, but rather
have the ASP pages connect to the SQL Server.
Thanks
CateDo you run this query using 4 part naming or OPENQUERY?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server gives Internal SQL Server error

Hi All,

I am accessing a linked server (also a SQL Server) from a stored
procedure. There is an insert statement that I run on a table in the
linked server. This statement causes the Internal SQL Server error. But
if I run the insert statement separately from Query Analyzer, it works
fine! Also, I put the stored procedure directly into the database on
the linked SQL Server and it ran fine on it. Has anybody come across
something of this sort?

Thanks,
Sanjeevmahajan.sanjeev@.gmail.com napisa(a):
> Hi All,
> I am accessing a linked server (also a SQL Server) from a stored
> procedure. There is an insert statement that I run on a table in the
> linked server. This statement causes the Internal SQL Server error. But
> if I run the insert statement separately from Query Analyzer, it works
> fine! Also, I put the stored procedure directly into the database on
> the linked SQL Server and it ran fine on it. Has anybody come across
> something of this sort?
> Thanks,
> Sanjeev

When you start procedure from query analyzer you do it as some user
but when this procedure is started by sql agent as job it is another
user which doesn't have an access to the database linked by linked
server, and this is the problem.

You can try to run this procedure from vbs for example with ado
connection the same as in query analyzer

Linked Server error

Hi, All
I have one default SQL Server 2005 "LIANG" and "LIANG\SQLEXPRESS".
After I set up a linked server "LING" in "LIANG\SQLEXPRESS"., I try to run a
query
select * from LIANG.Northwind.dbo.Customers
it gives me the following error:
OLE DB provider "SQLNCLI" for linked server "LIANG" returned message
"Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "LIANG" reported an error.
Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for
linked server "LIANG".
Please help
KaiHi
Have you added a linked server login? Can you connect to the remote server
directly with this login?
John
"kai" wrote:

> Hi, All
> I have one default SQL Server 2005 "LIANG" and "LIANG\SQLEXPRESS".
> After I set up a linked server "LING" in "LIANG\SQLEXPRESS"., I try to run
a
> query
> select * from LIANG.Northwind.dbo.Customers
> it gives me the following error:
> OLE DB provider "SQLNCLI" for linked server "LIANG" returned message
> "Invalid authorization specification".
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "SQLNCLI" for linked server "LIANG" reported an error.
> Authentication failed.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "SQLNCLI" for
> linked server "LIANG".
>
> Please help
>
> Kai
>
>

Linked Server error

I have 2 servers SQL7 and SQL2000.
When I run a query from 7 to 2000 (via link Server) I get the following
error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of
memory.
ANy help on this will be greatly appreciated.
Thanks,Drop thje Link Server and recreate. Is this on win 2000 ad server? If so do
you have sp4 installed? If not then install it. Also post your test query.
"XXX" wrote:

> I have 2 servers SQL7 and SQL2000.
> When I run a query from 7 to 2000 (via link Server) I get the following
> error:
>
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of
> memory.
>
> ANy help on this will be greatly appreciated.
>
> Thanks,
>
>

Linked Server error

I have 2 servers SQL7 and SQL2000.
When I run a query from 7 to 2000 (via link Server) I get the following
error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of
memory.
ANy help on this will be greatly appreciated.
Thanks,
Drop thje Link Server and recreate. Is this on win 2000 ad server? If so do
you have sp4 installed? If not then install it. Also post your test query.
"XXX" wrote:

> I have 2 servers SQL7 and SQL2000.
> When I run a query from 7 to 2000 (via link Server) I get the following
> error:
>
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of
> memory.
>
> ANy help on this will be greatly appreciated.
>
> Thanks,
>
>