linked server problems

I do the following to link a server:
EXEC sp_addlinkedserver 'MainLinkedServer', 'SQLServer OLEDB Provider',
'SQLOLEDB', 'MATT', NULL, NULL, 'WEEK'
It says it links fine (no errors), but when I do a select on a table I know
exists
select distinct T1."Institution" c1 from 'MainLinkedServer'...myTable T1
I get the following error:
OLE DB provider 'MainLinkedServer'does not contain table 'myTable'. The
table either does not exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='MainLinkedServer', TableName='myTable'].
any ideas?
I figured it out:
SELECT * FROM linked_server.database.dbo.table_name tn
"Matt Tapia" <mtapia@.inbizservices.com> wrote in message
news:ebXRQoASEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I do the following to link a server:
> EXEC sp_addlinkedserver 'MainLinkedServer', 'SQLServer OLEDB Provider',
> 'SQLOLEDB', 'MATT', NULL, NULL, 'WEEK'
> It says it links fine (no errors), but when I do a select on a table I
know
> exists
> select distinct T1."Institution" c1 from 'MainLinkedServer'...myTable T1
> I get the following error:
> OLE DB provider 'MainLinkedServer'does not contain table 'myTable'. The
> table either does not exist or the current user does not have permissions
on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain
> the table: ProviderName='MainLinkedServer', TableName='myTable'].
> any ideas?
>
>
>

Linked Server problem when querying

I have a linked SQL Server on another machine which is created using a stored procedure that executes the following:

EXEC sp_addlinkedserver @.server = 'Achilles\Mixed', @.srvproduct = ' ', @.provider = 'SQLNCLI', @.datasrc = 'Archilles\Mixed', @.catalog = 'DB_INTRANET'

The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:

SELECT * FROM DB_INTRANET...Employees

I get the following error:

OLE DB provider "SQLNCLI" for linked server "DB_Intranet" 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.

The logins are Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.

I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.

Does anyone have any suggestions please.

Hi,

the mapping of the logins did not work. What command did you use to use the WIndows login on the other server ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Please check two things:
1. make sure SQL 2005 allow remote access
2. make sure you set right userid/password for linked server.

Thanks

|||

Hi,

I have the same problem. But the distributed query seems to work on the management studio of the server but bot accross the network on other management studio with the same impersonated logins.

|||

I had to create a SQL authenticated login on the remote server (loginname) and then all local logins are mapped to the one remote SQL login using the Security page of the Linked Servers Properties dialogue.

I could not get it to pass Windows authenticated logins to the remote server at all.

In the Security Page of Linked Servers Properties dialogue.

For a Login not defined in the above list Connections will:

Be made using this security context:

Remote Login: Loginname

With Password: LoginPassword

linked server problem slow queries

We're using SS2000 SP4 on both servers. We've been running the following
query for 6 months or so. I'm not sure exactly how long it used to take
because the whole job has about 10 queries. But the whole job used to take
about 2 hours. Now this query alone can take 3 hours.
use branch_master
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from server2.promo2004.dbo.tblmail_resp t, branch_master..tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
However, if we change the query and put it on the remote server it runs in
under t seconds. Here is the query we use on the remote server.
use promo2004 --on maximus2
update t set t.branch = z.branch_new,
t.branch_old=z.branch_old,
t.reassign_date = getdate()
from tblmail_resp t, server1.branch_master.dbo.tblZipReassign_New z
where z.ziprequestid = 58
and t.prop_zip = z.zip
and t.branch= z.branch_old
and z.process='Y'
and z.update_type = 'A'
I don't know if it makes any difference but the tblmail_resp table on
server2 has about 8 million rows and the tblzipreassign_new table on server1
has about 2500 rows.
We have the same problem with a couple of other jobs have queries that go
from server1 to server2 and they have slowed down a lot of in some cases run
for over 8 hours before we stop them.
Any ideas?
Thanks,
--
Dan D.Hi Dan,
In order for me to better answer this I suggest you post the entire DDL
of the tables including indexes and the query plan.

Linked server problem

Hi,

I am running following script on server A. Server B is configured as linked server to A.

SET xact_abort ON
GO
begin tran
INSERT INTO B.table1.dbo.CUSTOMER (CUSTOMER_ID,PLAN_CODE)
VALUES (1001,100)
rollback tran

The above script when run run on server A gives following error:-
Error..
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Pls. advise why this error occurs. How to insert records in linked server.

Is the Distributed Transaction Coordinator (MSDTC) service running on both computers?|||yes|||

change begin tran to BEGIN DISTRIBUTED TRANSACTION and see

Madhu

|||that also doesnot work. Is it because A is windows 2003 & B is windows 2000 server?|||

what is the service pack on these box

Madhu

|||

Hi Shah,

If you still have tht problem, here is the link for the solution.

http://support.microsoft.com/?kbid=873160

Linked server problem

Hi,

I am running following script on server A. Server B is configured as linked server to A.

SET xact_abort ON
GO
begin tran
INSERT INTO B.table1.dbo.CUSTOMER (CUSTOMER_ID,PLAN_CODE)
VALUES (1001,100)
rollback tran

The above script when run run on server A gives following error:-
Error..
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Pls. advise why this error occurs. How to insert records in linked server.

Is the Distributed Transaction Coordinator (MSDTC) service running on both computers?|||yes|||

change begin tran to BEGIN DISTRIBUTED TRANSACTION and see

Madhu

|||that also doesnot work. Is it because A is windows 2003 & B is windows 2000 server?|||

what is the service pack on these box

Madhu

|||

Hi Shah,

If you still have tht problem, here is the link for the solution.

http://support.microsoft.com/?kbid=873160

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
Elecia
Are You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi
|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
Elecia
"saradhi" wrote:

> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:

> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>sql

Linked server from SQL2005 to SQL2000

Hi

I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.

Though the link server is created successfully, I am not able to se the tables under it so could not query anything.

-

USE [master]

EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'

Did you check permissions of remoteUser on the remote server instance? You can do this by just using the remote login to connect to the server using ISQLW and see what tables you can access or check your permissions.|||Yes, remoteUser is able to access RemoteServerName through Query Analizer.|||I didn't mean just accessing the server. That works since you didn't get any errors. What tables can you access? What happens if you try to select from a known table?|||

You have to use the Servername.Database.UserObject.Table on the query..

example,

Select * from [RemoteServer\MSSS2000].NorthWind.dbo.Orders

Linked server from SQL2005 to SQL2000

Linked server from SQL2005 to SQL2000

Hi

I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.

Though the link server is created successfully, I am not able to se the tables under it so could not query anything.

--

USE [master]

EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'

What do you mean by "I am not able to see the tables under it"... have you tried:

SELECT * FROM [targetServer].[master].dbo.SysObjects to see if you are actually connected (and can query) ?

|||No, this does not work, I am getting no object found|||Can you post your query to access the linked server, as well as the error message you got? Maybe you got right object name, but incorrect schema name?|||

Hey u can't see the link server objects in 2005 as u used to see in sql2000.

But u can query an object as u wish.

if u know the name of any table.

Try to query the link server like

Select * from LinkServerName...tablename.

You can also use openquery statement.

Good Luck

|||Do you mean you can't find Linked Server objects in Management Studio? Actually?Linked Server objects are moved to the "Server Objects" catalog in Management Studio, no longer under the Security branch as in Enterprise Manager

You can also get information about linker servers usingsp_linkedservers system procedure.

Linked server from 2005 to 2000 failed

Hi,
I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
Server 2000 (32 bit). I got error message as following: Cannot obtain the
schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
failure code when it is used.
I read the article 906954 and try to solve this problem but the osql didn't
run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
it installed named instance, can I use sa to run osql with instcat.sql? Can
someone give an example?
Regards!
Chen
Hi
Yes you can use osql to run instcat.sql and this is described in the second
set of examples that uses the -U parameter. Make sure that you have the
correct version of instcat.sql e.g. instcat.sql for service pack 4 will
insert a version number of 8.00.2039 for SYS_SPROC_VERSION into
spt_server_info
John
"Chen" wrote:

> Hi,
> I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
> Server 2000 (32 bit). I got error message as following: Cannot obtain the
> schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
> server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
> failure code when it is used.
> I read the article 906954 and try to solve this problem but the osql didn't
> run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
> it installed named instance, can I use sa to run osql with instcat.sql? Can
> someone give an example?
> Regards!
> Chen

Linked server from 2005 to 2000 failed

Hi,
I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
Server 2000 (32 bit). I got error message as following: Cannot obtain the
schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
failure code when it is used.
I read the article 906954 and try to solve this problem but the osql didn't
run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
it installed named instance, can I use sa to run osql with instcat.sql? Can
someone give an example?
Regards!
ChenHi
Yes you can use osql to run instcat.sql and this is described in the second
set of examples that uses the -U parameter. Make sure that you have the
correct version of instcat.sql e.g. instcat.sql for service pack 4 will
insert a version number of 8.00.2039 for SYS_SPROC_VERSION into
spt_server_info
John
"Chen" wrote:
> Hi,
> I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
> Server 2000 (32 bit). I got error message as following: Cannot obtain the
> schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked
> server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
> failure code when it is used.
> I read the article 906954 and try to solve this problem but the osql didn't
> run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
> it installed named instance, can I use sa to run osql with instcat.sql? Can
> someone give an example?
> Regards!
> Chen

Linked server from 2005 to 2000 failed

Hi,
I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
Server 2000 (32 bit). I got error message as following: Cannot obtain the
schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linke
d
server "SQL2000_SQM1BSI". The provider supports the interface, but returns a
failure code when it is used.
I read the article 906954 and try to solve this problem but the osql didn't
run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box,
it installed named instance, can I use sa to run osql with instcat.sql? Can
someone give an example?
Regards!
ChenHi
Yes you can use osql to run instcat.sql and this is described in the second
set of examples that uses the -U parameter. Make sure that you have the
correct version of instcat.sql e.g. instcat.sql for service pack 4 will
insert a version number of 8.00.2039 for SYS_SPROC_VERSION into
spt_server_info
John
"Chen" wrote:

> Hi,
> I made the linked server from SQL Server 2005 Windows 2003 (64 bit) to SQL
> Server 2000 (32 bit). I got error message as following: Cannot obtain the
> schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for lin
ked
> server "SQL2000_SQM1BSI". The provider supports the interface, but returns
a
> failure code when it is used.
> I read the article 906954 and try to solve this problem but the osql didn'
t
> run successfully in sql server 2000 (with SP3) box. In SQL Server 2000 box
,
> it installed named instance, can I use sa to run osql with instcat.sql? Ca
n
> someone give an example?
> Regards!
> Chen

Linked Server for Oracle

Hi

I need to migrate data from an Oracle database to a SQL Server 2005 database (Dev Edition),

I created a linked server object using the following : -

exec sp_addlinkedserver 'JAVAX', 'ORACLE','OraOLEDB.Oracle', 'JAVAX.world'

exec sp_addlinkedsrvlogin 'JAVAX','false','XXX','XXX','YYY'

GO

XXX login exists on sql server as well as Oracle.

However I am unable to see any of the tables under the linked server and when I try to access the query like below it returns an error "Invalid object name 'JAVA109.sfmfg.SFCORE_USER'."

Select * from JAVAX.sfmfg.SFCORE_USER

Insert a dot after "JAVAX":

Select * from JAVAX..sfmfg.SFCORE_USER

I'm assuming the server alias is "JAVAX", the schema owner is "sfmfg" and the table or view is "SFCORE_USER".

Linked Server Error

We had to reinstall MSSQL on one of our servers now when I set up a linked
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:

> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.

Linked Server Error

We had to reinstall MSSQL on one of our servers now when I set up a linked
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.
I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:

> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.

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

We had to reinstall MSSQL on one of our servers now when I set up a linked
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:
> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.

Linked Server Connection Issues...

I have the following connectivity issues when querying a
linked server:
Base Server[s]:
SQL Server 2000 (32 and 64-bit) sp3
SQL instances listening on port 1433
Windows Server 2000 and 2003 (32 and 64-bit) (clustered
and stand-alone configurations)
Reside on same subnet
Linked Server:
SQL Server 2000 (32-bit) sp3
Virtual SQL instance listening on port 1433
Windows Server 2000 (clustered)
Reside on a different subnet
When I expand the Linked Server and click on Tables, I
eventually get a General Network Error: Check your
Network Documentation message.
When I try a query using the OPENQUERY function, I get
the same error after a 35 second interval, regardless of
the base server that I am using. The remote query
timeout on the Linked Server is set for 1200 seconds.
This is not a login issue because I can connect via Query
Analyzer and register with Enterprise Manager.
I can also create a Linked Server going in the opposite
direction from the configuration listed above.
I can create a Linked Server from a different subnet and
use the OPENQUERY function without any errors.
There are no firewalls between the servers and no ACLs on
the router. All servers are connecting through the same
switch.
The only thing I have noticed different is the absence of
the VIA (Gigabit) [ssmsgnet.dll] and VIA (Server Net II)
[ssmssnet.dll] netowrk libraries on the Base Servers
listed above.
Any information or help will greatly be appreciated!
Thanks,
Greg
Hi Greg,
General network errors are often hard to diagnose in this format. I
would suggest making some network traces on both the client and server, and
opening up a Support Case with a SQL Engineer.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Linked Server Connection Issues...

I have the following connectivity issues when querying a
linked server:
Base Server[s]:
SQL Server 2000 (32 and 64-bit) sp3
SQL instances listening on port 1433
Windows Server 2000 and 2003 (32 and 64-bit) (clustered
and stand-alone configurations)
Reside on same subnet
Linked Server:
SQL Server 2000 (32-bit) sp3
Virtual SQL instance listening on port 1433
Windows Server 2000 (clustered)
Reside on a different subnet
When I expand the Linked Server and click on Tables, I
eventually get a General Network Error: Check your
Network Documentation message.
When I try a query using the OPENQUERY function, I get
the same error after a 35 second interval, regardless of
the base server that I am using. The remote query
timeout on the Linked Server is set for 1200 seconds.
This is not a login issue because I can connect via Query
Analyzer and register with Enterprise Manager.
I can also create a Linked Server going in the opposite
direction from the configuration listed above.
I can create a Linked Server from a different subnet and
use the OPENQUERY function without any errors.
There are no firewalls between the servers and no ACLs on
the router. All servers are connecting through the same
switch.
The only thing I have noticed different is the absence of
the VIA (Gigabit) [ssmsgnet.dll] and VIA (Server Net II)
[ssmssnet.dll] netowrk libraries on the Base Servers
listed above.
Any information or help will greatly be appreciated!
Thanks,
GregHi Greg,
General network errors are often hard to diagnose in this format. I
would suggest making some network traces on both the client and server, and
opening up a Support Case with a SQL Engineer.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.