Sql Linked Server: windows

Showing posts with label windows. Show all posts

Friday, March 30, 2012

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Help? I am an Oracle DBA struggling to support SQL Server.
Using EM I added another SQL Server database to Linked Servers with Windows
authentication. In Query Analyzer I attempt a simple SQL query and receive...
Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
I read thru BOL and Robert Vieira's book to setup the distributed query, and
I believe I am doing everything correctly. Scanning Google and MSKB, it
seems I hit a bug with SQL Server, but this is so simple I have a hard time
believing that.
Is my only recourse to call support' Is there anything I can check'
--
ChrisIt depends...the problem can be caused by a ton of
different things which is why it's often recommended to open
up a case with support.
What version of SQL Server? Are you on the latest service
packs?
Does this occur with any server and any database or just one
specific server and one database?
Make sure the servers, databases involved are running
correctly - check both servers server names with
@.@.Servername. Make sure the database you are accessing does
not have any consistency problems - run a dbcc on the
database you are accessing.
You may also want to check your mdac installation on the
servers. You can use the mdac component checker to do this.
It's available from:
http://msdn.microsoft.com/data/mdac/default.aspx
-Sue
On Fri, 12 Nov 2004 09:54:03 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:
>Help? I am an Oracle DBA struggling to support SQL Server.
>Using EM I added another SQL Server database to Linked Servers with Windows
>authentication. In Query Analyzer I attempt a simple SQL query and receive...
>Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
>I read thru BOL and Robert Vieira's book to setup the distributed query, and
>I believe I am doing everything correctly. Scanning Google and MSKB, it
>seems I hit a bug with SQL Server, but this is so simple I have a hard time
>believing that.
>Is my only recourse to call support' Is there anything I can check'|||Duh. I should have posted that.
We are currently running SQL Server 2000 with SP3a in Windows 2000 w SP4.
This occurs when I try to query tables on another instance on the same server.
We have a maintenance plan that performs the dbcc checks on a regular basis.
So no problem there.
Basically, I need to be able to compare the tables in the current (dev or
test) databases with a "baseline" database. So I decided to try the
distributed query from the baseline database against the dev database and it
works. But it fails the other way Or when I try to compare dev database
against test database it fails. So for now I'm ok. Later, I will
troubleshoot why I cant go from dev or test database to the baseline database.
Thanks Sue!!
"Sue Hoegemeier" wrote:
> It depends...the problem can be caused by a ton of
> different things which is why it's often recommended to open
> up a case with support.
> What version of SQL Server? Are you on the latest service
> packs?
> Does this occur with any server and any database or just one
> specific server and one database?
> Make sure the servers, databases involved are running
> correctly - check both servers server names with
> @.@.Servername. Make sure the database you are accessing does
> not have any consistency problems - run a dbcc on the
> database you are accessing.
> You may also want to check your mdac installation on the
> servers. You can use the mdac component checker to do this.
> It's available from:
> http://msdn.microsoft.com/data/mdac/default.aspx
> -Sue
> On Fri, 12 Nov 2004 09:54:03 -0800, "Chris"
> <Chris@.discussions.microsoft.com> wrote:
> >Help? I am an Oracle DBA struggling to support SQL Server.
> >
> >Using EM I added another SQL Server database to Linked Servers with Windows
> >authentication. In Query Analyzer I attempt a simple SQL query and receive...
> >
> >Process 54 generated fatal exception c00000005 EXCEPTION_ACCESS_VIOLATION.
> >
> >I read thru BOL and Robert Vieira's book to setup the distributed query, and
> >I believe I am doing everything correctly. Scanning Google and MSKB, it
> >seems I hit a bug with SQL Server, but this is so simple I have a hard time
> >believing that.
> >
> >Is my only recourse to call support' Is there anything I can check'
>

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 !

linked server problem with error message 7391

I have two computers both running Windows Server 2003 and SQL Server 2000. I
have a trigger on a table located on the first computer which includes a line
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what is
wrong.
After reading the many threads about this I have checked the following items:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.
See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/default...b;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:

> I have two computers both running Windows Server 2003 and SQL Server 2000. I
> have a trigger on a table located on the first computer which includes a line
> of code that inserts into a table on the second computer. The two computers
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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].
> I have not touched any of the code, so something in terms of the setup must
> have changed so that it doesn't work anymore, how ever I have no idea what is
> wrong.
> After reading the many threads about this I have checked the following items:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is run
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run over
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same error.
> So essentially I can run the code as a stored procedure but not as a trigger.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.

linked server problem with error message 7391

I have two computers both running Windows Server 2003 and SQL Server 2000.
I
have a trigger on a table located on the first computer which includes a lin
e
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what i
s
wrong.
After reading the many threads about this I have checked the following items
:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger
.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/defaul...kb;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:

> I have two computers both running Windows Server 2003 and SQL Server 2000.
I
> have a trigger on a table located on the first computer which includes a l
ine
> of code that inserts into a table on the second computer. The two compute
rs
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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 th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup mus
t
> have changed so that it doesn't work anymore, how ever I have no idea what
is
> wrong.
> After reading the many threads about this I have checked the following ite
ms:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is r
un
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run ov
er
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same erro
r.
> So essentially I can run the code as a stored procedure but not as a trigg
er.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.

Wednesday, March 28, 2012

Linked Server PROBLEM For MySQL Database

I created a ODBC DSN on my Windows Server for a remote UNIX box MySQL database. The TEST button returns the 'successful' message for the MySQL databaser. Also, when I use an ODBC DSNless string I found, that also successfully connects when I use it in some ASP pages.

However, I can not get a successful connection in the Enterprise Manager trying to use the DSN I created nor the DSNless string I used in the ASP pages. Can anyone tell me how to config the Enterprise Manager Linked Server settings listed below to connect to the MySQL database(s).

I added a new linked server, and selected the Microsoft OLEDB Provider for ODBC Drivers as the Provider name.

Does anyone know how do I configure the items below:

On the General tab:" option.
Product Name: xxxxxxxxxxxxxxxxxx
Data Source: xxxxxxx
Provider String: xxxxxxxx
Location: xxxxxxx
Catalog: xxxxx

On the Security tab:" option.
Remote Login: xxxxxx
With password: xxxxxx

Thanks.I found a wonderful solution to this problem...

http://bside.typepad.com/lifebsideben/2003/08/mysql_mssql_lin.html

In case the link is gone, here is the way to create a Linked Server to a MySQL database, in a nutshell...

1) Create a System DSN on the server running MSSQL, connecting to the MySQL database. Let's call it <the_new_mysql_dsn>.

2) Run the following stored procedure, with appropriate arguments...

EXEC sp_addlinkedserver @.server='<linked_server_label>', @.srvproduct='MySQL', @.provider='MSDASQL', @.datasrc='<the_new_mysql_dsn>'

-------

You can then open up ol' Enterprise Manager and see the new linked server added in. Right click on it to see the Properties. On mine, the settings showed up as...

Linked server: <linked_server_label>
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: MySQL
Data source: <the_new_mysql_dsn>

Everything else is blank.

-------

Incidentally, just to try it out, I deleted my new linked server, and then added a new one, using the following settings...

Linked server: <linked_server_label>
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Provider string: DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=<mysql_database>;SERVER=<mysql_server>;UID=;PORT=;OPTION=0;STMT=;

And it works as well (the connection string was generated within Visual Interdev). I am using no passwords yet, I'm still in the early development phases, but this should handle the basics of connecting to a MySQL database from SQL Server.

Linked server problem

Hi,

I am connecting two sql servers together as linked server, the servers are running windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.

However, my problem appeared when I tried to stress test my application, the connection on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
my transaction details:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) ro prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller), and then
5) The transaction is committed.

The above scenario is working properly for 53 and less cocurrent connection! In case more cocurrent connections are
issued, the connection are blocked almost immediately and never returns till the execution times out. The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.

Please note that:
1) the test was conduct separately from any other activity on the SQL servers. i.e. they was the only connections on the servers.
2) When we moved the the Linked Server database to the local server, i.e. we removed the linked server, all connections completed their commands execution successfully and without timeouts. Also, note that I have ran the test successfully, on the local server, with 256 and 512 connections.

I do highly appreciate any help or support on this.

Regards,Any one can help?|||This is beyond my ability to test. I've never seen the problem with any configuration that I use, so I'd suspect that it is either an interaction between the software versions that you are using or something specific to your code.

My advice is to open an incident with Microsoft Premier Support. If the problem is in your code, it will cost you roughly $1000 US to prove it. If the problem is in their code, it won't cost you a thing and you'll have had some pretty valuable review of your code and configuration, plus you'll get a solution (usually a patch) for the problem too!

-PatP|||You could set a max on the Query Timeout for the linked server. It would still take awhile to resolve. The bigger questions is what's causing the locking in the first place? Is it always one process that's causing this or one area such as a particular table?

Linked server problem

I linked two sql servers (one an x64 sql 2005 server the other a sql 2000 x86 server). The link is from the x64 box to the x86 box and I used Windows authentication to forge the links.

When the developer (who has Windows accounts on both boxes) logs in locally to the x64 box and runs a query, data is returned from the linked server. But when the same developer logs into his own client box and uses Management Studio to access the x64 box (via Windows authentication) and run the same query, he gets this error message"

"Login failed for user '(null)'. Reason: not associated with a trusted SQL Server Connection"

I presume that the developer should be able to access this data from his own box or can the linked data only be gotten from an account logged into the x64 box?

TIA,

barkingdog

I'm a little confused about what you mean by 'windows authentication' to forge the links.

when you create a linked server, you have the following options:

map a local login to a login on the remote server

connect using the user login's security context

connect using a specific (provided) login's security context

Whenever I've seen the above error, it has been because a developer had the server registered incorrectly in EM.

|||

Under SSMS, I thought that those options only applied when the connection trying to access the linked server was not listed in the top box ("Local server login to remote server login mappings".). So I ignored those options. Maybe I dun wrong!

The developer who is trying to access data on the linked server from SSMS from his own box is listed (using Windows authentication) in the Local Login section of the Linked server properties box of the x64 server. "Impersonate" is selected for that developer. No names are supplied for the Remote User\Remote password box as his Windows authentication will be used on the linked box. (Or so I think...)

TIA,

barkingdog

|||

I woul dfill in the other two boxes and see if that works.

I am struggling with my own linked server issues right now, related to windows authentication accross linked servers, but we would be using the

'connect using the user's login'.

You might try that and tell me if it works. ;)

|||

Here's what I have so far:

Suppose Server B is linked to Server A via a "Link Account". When I log on to Server B (using Remote Desktop) as "Link Account" I can successfully pull data from Server A, so the link is working. The problem: I logged into my own local workstation (not Server A or Server B) as "LinkAccount" fired up SSMS, connected to Server B as that account, and tried to pull the same data that I did earlier. The attempt fails saying that I am using a "null" account and no sql account exists.....blah, blah, blah.

I found this SQL 2005 BOL link that describes the situation:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9e302c29-639d-4509-ba1e-cf452582c5c3.htm

Apparently, what I wanted to do was not as simple as I thought it should be. I am creating a "double hop" and the servers need to be prepared as described in the help article. Otherwise, the error message is to be expected!

Friday, March 23, 2012

Linked Server Locks!

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali Salem
Could you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
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 Locks!

Hi,
I am connecting two sql servers together as linked servers, the servers are
running on windows 2003.
I call a stored procedure defined on the linked server as part of a transact
ion and it works properly.
However, my problem appeared when I tried to stress test my application, the
connections on the linked server
started to block and never returns, however sql server didn't show any deadl
ock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock)
to prevent other transactions from retireving the same value till the curre
nt transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the tra
nsaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections
! In case more cocurrent connections are issued, the connections are blocked
almost immediately and never returns till the execution times out, note tha
t it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL serv
ers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e.
we removed the linked server, all connections completed their commands' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached
by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemCould you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
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.sql

Linked Server Locks!

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemCould you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
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 Lockes - StressTesting

Hi,
I am connecting two sql servers together as linked servers, the servers are
running on windows 2003.
I call a stored procedure defined on the linked server as part of a transact
ion and it works properly.
However, my problem appeared when I tried to stress test my application, the
connections on the linked server
started to block and never returns, however sql server didn't show any deadl
ock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock)
to prevent other transactions from retireving the same value till the curre
nt transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the tra
nsaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections
! In case more cocurrent connections are issued, the connections are blocked
almost immediately and never returns till the execution times out, note tha
t it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL serv
ers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e.
we removed the linked server, all connections completed their commands' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached
by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemDo you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
thanks,
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 Lockes - StressTesting

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemDo you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
thanks,
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 Lockes - StressTesting

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali Salem
Do you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
thanks,
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 Lock Problem

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali Salem
You could perhaps reduce the size of the transaction. Locks are held till
the end of the transaction.
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.
sql

Linked Server Lock Problem

Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemYou could perhaps reduce the size of the transaction. Locks are held till
the end of the transaction.
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 Lock Problem

Hi,
I am connecting two sql servers together as linked servers, the servers are
running on windows 2003.
I call a stored procedure defined on the linked server as part of a transact
ion and it works properly.
However, my problem appeared when I tried to stress test my application, the
connections on the linked server
started to block and never returns, however sql server didn't show any deadl
ock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock)
to prevent other transactions from retireving the same value till the curre
nt transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the tra
nsaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections
! In case more cocurrent connections are issued, the connections are blocked
almost immediately and never returns till the execution times out, note tha
t it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL serv
ers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e.
we removed the linked server, all connections completed their commands' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached
by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemYou could perhaps reduce the size of the transaction. Locks are held till
the end of the transaction.
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: DBSCHEMA_COLUMNS

Hi All -
I'm hoping someone can help me with this recurring problem. I have a
server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
me the error.
Server: Msg 7351, Level 16, State 2, Line 1
OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
columns of object 'DBSCHEMA_COLUMNS'.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
ORDINAL=-1]].
In the past, I rebuilt the server and it worked for a couple of months.
When the problem started again, I contacted Microsoft PSS and Chandra
determined the
"instcat.sql
script in the service pack did not run correctly on my
system. We backed up the master database, ran the script,
and I now have access to the linked servers.
"
The same error has occurred again on the same server! None of my other
5 servers(same configuration) has ever received this error.
I have all the servers linked, using sa username and password to
connect so I have full admin rights.
I saw a thread on this issue from April 23, 2003 between Gunter Van
Hauwe and Vikram Jayaram(MS).
I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
the same output as I receive with the query. It happens no matter
which database or table I choose. It happens from all the linked
servers.
If I run the query with OpenQuery, I receive data and not the error,
but this isn't practical for all of our applications.
Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
that and I receive all the tables and columns from the master database
on sql02.
I'm not sure what to do next. I know I can fix it by reinstalling, but
reattaching 100 databases after the reinstall is getting a bit tiring.
Thanks for any help you can provide.
Michelle MorrisI have seen similar issues when the MDAC or other service packs between the
servers were too far apart, say 2.1 to 2.7 (I know, too much a spread, but it
has been a while.)
You might check that compatibility level.
"mimorr" wrote:
> Hi All -
> I'm hoping someone can help me with this recurring problem. I have a
> server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
> Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
> me the error.
> Server: Msg 7351, Level 16, State 2, Line 1
> OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
> columns of object 'DBSCHEMA_COLUMNS'.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
> ORDINAL=-1]].
>
> In the past, I rebuilt the server and it worked for a couple of months.
> When the problem started again, I contacted Microsoft PSS and Chandra
> determined the
> "instcat.sql
> script in the service pack did not run correctly on my
> system. We backed up the master database, ran the script,
> and I now have access to the linked servers.
> "
> The same error has occurred again on the same server! None of my other
> 5 servers(same configuration) has ever received this error.
> I have all the servers linked, using sa username and password to
> connect so I have full admin rights.
> I saw a thread on this issue from April 23, 2003 between Gunter Van
> Hauwe and Vikram Jayaram(MS).
> I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
> the same output as I receive with the query. It happens no matter
> which database or table I choose. It happens from all the linked
> servers.
> If I run the query with OpenQuery, I receive data and not the error,
> but this isn't practical for all of our applications.
> Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
> that and I receive all the tables and columns from the master database
> on sql02.
> I'm not sure what to do next. I know I can fix it by reinstalling, but
> reattaching 100 databases after the reinstall is getting a bit tiring.
> Thanks for any help you can provide.
> Michelle Morris
>|||OK, I gave that a try using Microsoft's cc_pkg.exe
http://support.microsoft.com/default.aspx?scid=kb;en-us;301202
and here are my results:
My client workstation where I run QA: MDAC 2.7 SP1 on Windows XP SP1
SQL02 that is the target in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
SQL03 that is the source in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
Since the servers are the same, I launched Query Analyzer on SQL03 and
ran a query to a SQL02 database. I got the same error as reported
above.
JRPM, thank you for the suggestion.
Michelle

Linked Server issue: DBSCHEMA_COLUMNS

Hi All -
I'm hoping someone can help me with this recurring problem. I have a
server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
me the error.
Server: Msg 7351, Level 16, State 2, Line 1
OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
columns of object 'DBSCHEMA_COLUMNS'.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
ORDINAL=-1]].
In the past, I rebuilt the server and it worked for a couple of months.
When the problem started again, I contacted Microsoft PSS and Chandra
determined the
"instcat.sql
script in the service pack did not run correctly on my
system. We backed up the master database, ran the script,
and I now have access to the linked servers.
"
The same error has occurred again on the same server! None of my other
5 servers(same configuration) has ever received this error.
I have all the servers linked, using sa username and password to
connect so I have full admin rights.
I saw a thread on this issue from April 23, 2003 between Gunter Van
Hauwe and Vikram Jayaram(MS).
I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
the same output as I receive with the query. It happens no matter
which database or table I choose. It happens from all the linked
servers.
If I run the query with OpenQuery, I receive data and not the error,
but this isn't practical for all of our applications.
Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
that and I receive all the tables and columns from the master database
on sql02.
I'm not sure what to do next. I know I can fix it by reinstalling, but
reattaching 100 databases after the reinstall is getting a bit tiring.
Thanks for any help you can provide.
Michelle Morris
I have seen similar issues when the MDAC or other service packs between the
servers were too far apart, say 2.1 to 2.7 (I know, too much a spread, but it
has been a while.)
You might check that compatibility level.
"mimorr" wrote:

> Hi All -
> I'm hoping someone can help me with this recurring problem. I have a
> server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
> Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
> me the error.
> Server: Msg 7351, Level 16, State 2, Line 1
> OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
> columns of object 'DBSCHEMA_COLUMNS'.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
> ORDINAL=-1]].
>
> In the past, I rebuilt the server and it worked for a couple of months.
> When the problem started again, I contacted Microsoft PSS and Chandra
> determined the
> "instcat.sql
> script in the service pack did not run correctly on my
> system. We backed up the master database, ran the script,
> and I now have access to the linked servers.
> "
> The same error has occurred again on the same server! None of my other
> 5 servers(same configuration) has ever received this error.
> I have all the servers linked, using sa username and password to
> connect so I have full admin rights.
> I saw a thread on this issue from April 23, 2003 between Gunter Van
> Hauwe and Vikram Jayaram(MS).
> I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
> the same output as I receive with the query. It happens no matter
> which database or table I choose. It happens from all the linked
> servers.
> If I run the query with OpenQuery, I receive data and not the error,
> but this isn't practical for all of our applications.
> Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
> that and I receive all the tables and columns from the master database
> on sql02.
> I'm not sure what to do next. I know I can fix it by reinstalling, but
> reattaching 100 databases after the reinstall is getting a bit tiring.
> Thanks for any help you can provide.
> Michelle Morris
>
|||OK, I gave that a try using Microsoft's cc_pkg.exe
http://support.microsoft.com/default...b;en-us;301202
and here are my results:
My client workstation where I run QA: MDAC 2.7 SP1 on Windows XP SP1
SQL02 that is the target in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
SQL03 that is the source in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
Since the servers are the same, I launched Query Analyzer on SQL03 and
ran a query to a SQL02 database. I got the same error as reported
above.
JRPM, thank you for the suggestion.
Michelle
sql

Friday, March 30, 2012

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Linked Server Problems HELP HELP HELP !

Linked Server Problems HELP HELP HELP !

Linked Server Problems HELP HELP HELP !

linked server problem with error message 7391

linked server problem with error message 7391

Wednesday, March 28, 2012

Linked Server PROBLEM For MySQL Database

Linked server problem

Linked server problem

Friday, March 23, 2012

Linked Server Locks!

Linked Server Locks!

Linked Server Locks!

Linked Server Lockes - StressTesting

Linked Server Lockes - StressTesting

Linked Server Lockes - StressTesting

Linked Server Lock Problem

Linked Server Lock Problem

Linked Server Lock Problem

Linked Server issue: DBSCHEMA_COLUMNS

Linked Server issue: DBSCHEMA_COLUMNS

Sql Linked Server

Blog Archive

About Me