Sql Linked Server: message

Showing posts with label message. 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 queries getting EXCEPTION_ACCESS_VIOLATION message

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.

Monday, March 26, 2012

Linked server OPENQUERY, error 'The name <> is not a valid ide

Adam,
Thank you for replying to my first message.
Now, can I pass a variable of type integer into that dynamic SELECT
statement? I know it works with character variables, but I really need to
pass in a numeric field. Or do I have to resort to conversions?
declare @.id int
select @.id = 1
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE server=' + @.id + ')'
I get Incorrect syntax near '+'.
Thank you.
"Adam Machanic" wrote:

> "Alexis" <Alexis@.discussions.microsoft.com> wrote in message
> news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> Add some parens:
> EXEC (@.tsql)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>You'll have to build the string dynamically -- you can't pass any kind of
variable into OPENQUERY.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:9B3439B9-7AF2-4B81-A7A3-64BF5A5CAD8A@.microsoft.com...
> Adam,
> Thank you for replying to my first message.
> Now, can I pass a variable of type integer into that dynamic SELECT
> statement? I know it works with character variables, but I really need to
> pass in a numeric field. Or do I have to resort to conversions?
> declare @.id int
> select @.id = 1
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE server=' + @.id + ')'
> I get Incorrect syntax near '+'.
> Thank you.
>

Friday, March 23, 2012

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

Wednesday, March 21, 2012

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

Monday, March 19, 2012

Linked Server Fails w/Error: 7399

I'm trying to connnect from a SQL Server 7.0 db to an
Oracle 8.i instance. Connection is failing with the
following error message: Error 7399 OLE DB
Provider 'MSDAOR' reported an error.
I used the following ddl to create my linked server &
linked server logon:
exec
sp_addlinkedserver 'CNRDAPRD', 'ORACLE', 'MSADORA', 'cnrdap
rd'
exec sp_addlinkedsrvlogin 'CNRDAPRD', 'false', 'local SQL
Server logon', 'Oracle Server Logon', 'Oracle Server logon
password'
I can connect to the Oracle database from the box where
I'm running SQL Server.
When I issue: ping oracle server name from box running SQL
Server I can ping server.
When I issue tnsping oracle database name from box running
SQL server I can ping Oracle database.
When I issue sqlplus ORA logon/ORA password@.ORA dbname I
can logon to database so I know I can connect to the box
and I'm using the right logon & password.
Any help greatly appreciated. Thanks in advance.Check if these articles help:
http://support.microsoft.com/defaul...kb;en-us;280106
http://support.microsoft.com/defaul...kb;en-us;220915
http://support.microsoft.com/defaul...kb;en-us;277002
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"walter.williams@.rd.com" <anonymous@.discussions.microsoft.com> wrote in
message news:6f5901c4061d$a857ef90$a601280a@.phx.gbl...
> I'm trying to connnect from a SQL Server 7.0 db to an
> Oracle 8.i instance. Connection is failing with the
> following error message: Error 7399 OLE DB
> Provider 'MSDAOR' reported an error.
> I used the following ddl to create my linked server &
> linked server logon:
> exec
> sp_addlinkedserver 'CNRDAPRD', 'ORACLE', 'MSADORA', 'cnrdap
> rd'
> exec sp_addlinkedsrvlogin 'CNRDAPRD', 'false', 'local SQL
> Server logon', 'Oracle Server Logon', 'Oracle Server logon
> password'
> I can connect to the Oracle database from the box where
> I'm running SQL Server.
> When I issue: ping oracle server name from box running SQL
> Server I can ping server.
> When I issue tnsping oracle database name from box running
> SQL server I can ping Oracle database.
> When I issue sqlplus ORA logon/ORA password@.ORA dbname I
> can logon to database so I know I can connect to the box
> and I'm using the right logon & password.
> Any help greatly appreciated. Thanks in advance.|||I reviewed the Knowledge Base articles you suggested. This was a pre-existi
ng linked server definition that was working and has just failed. I've atte
mpted to redefine it. I'm not getting the message about the Oracle client a
nd networking comonents mis
sing so I'm ruling out 277002 & 220915. Article 280106 suggests that the ve
rsion of MDAC might be wrong (you need MDAC 2.5, or later, installed on SQL
Server computer). How do I check my version of MDAC to see if I'm at 2.5 or
later? TIA|||> How do I check my version of MDAC to see if I'm at 2.5 or later?
You can determine the installed MDAC version with the component checker
tool:
<http://www.microsoft.com/downloads/...a8df6-4a21-4b43
-bf53-14332ef092c9&displaylang=en>
Hope this helps.
Dan Guzman
SQL Server MVP
"walter.williams@.rd.com" <anonymous@.discussions.microsoft.com> wrote in
message news:89454DC5-D415-4284-95FD-760FA7B0C8EC@.microsoft.com...
> I reviewed the Knowledge Base articles you suggested. This was a
pre-existing linked server definition that was working and has just failed.
I've attempted to redefine it. I'm not getting the message about the Oracle
client and networking comonents missing so I'm ruling out 277002 & 220915.
Article 280106 suggests that the version of MDAC might be wrong (you need
MDAC 2.5, or later, installed on SQL Server computer). How do I check my
version of MDAC to see if I'm at 2.5 or later? TIA|||I downloaded the component checker and verified I have MDAC 2.5 sp3 installe
d on my client (SQL Server) computer. I took another look at one of the Kno
wledge Base Articles, #280106, that Dejan suggested. We checked the registr
y entries for Oracle client
software. We found that the registry entries were pointing to Oracle Client
versions for Oracle Client 7.3 event though the dlls were not present on th
e machine, Oracle Client 8.i is loaded onto the machine. That's the version
we expected to see in the
registry. We suspected that the registry entries were somehow corrupted or
overlaid. When we checked the event log for the server it was full of error
s, the server had lost it's connection to the domain server. We scheduled a
n emergecy reboot of the s
erver (server hadn't been stopped & restarted in over 100 days) and when the
server came back up we were able to query our linked server.

Linked Server error in Windows2003Server

Dear all,
I've got this error message on creating the linked server to the SQL Server
2000 (in Windows 2003 Server),
"Error 18452: Login failed for user 'sa'. Reason: Not associated with a
trusted SQL Server connection."
The client side is MSDE 2000 (in WinXP Pro).
But I can successfully create this linked server relationship when the
server side is SQL Server 2000 (in Windows 2000 Server or WinXP Pro). If the
OS in server side is changed to Windows 2003 Server, the above error
occurred. Why?
Thanks a lot.
Curtis
Curtis,
I very much doubt that the OS version has anything to do with your
problem. The error message clearly shows that the Server you are trying
to connect to expects a trusted Windows login. The sa account is a SQL
Server login and that's why the login attempt fails. Change the
authentification mode on the SQL server to mixed (SQL and Windows
authentification) and it should work.
Markus
|||Thanks for you reply.
But for the Windows2003Server, the authentification mode is already mixed
mode.
One more thing, this error message has not been shown if both are the SQL
Server 2000 (in Windows2003Server). It's only appeared with either one is
the WinXP Pro. So, I've suspected that anything goes wrong in the client.
P.S.: Also, I've just walked through different forums, and the solution is
same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
problem.
Curtis
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1141135359.765464.289340@.t39g2000cwt.googlegr oups.com...
> Curtis,
> I very much doubt that the OS version has anything to do with your
> problem. The error message clearly shows that the Server you are trying
> to connect to expects a trusted Windows login. The sa account is a SQL
> Server login and that's why the login attempt fails. Change the
> authentification mode on the SQL server to mixed (SQL and Windows
> authentification) and it should work.
> Markus
>
|||Lun wrote:
> Thanks for you reply.
> But for the Windows2003Server, the authentification mode is already mixed
> mode.
> One more thing, this error message has not been shown if both are the SQL
> Server 2000 (in Windows2003Server). It's only appeared with either one is
> the WinXP Pro. So, I've suspected that anything goes wrong in the client.
> P.S.: Also, I've just walked through different forums, and the solution is
> same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
> problem.
> Curtis
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1141135359.765464.289340@.t39g2000cwt.googlegr oups.com...
>
Have you created a linkedserver login? It sounds like you're trying to
connect with a SQL user but that you haven't linked this user to a login
on the linked server.
Try to look up sp_addlinkedserver and/or sp_addlinkedsrvlogin in Books
On Line.
Regards
Steen

Linked Server error in Windows2003Server

Dear all,
I've got this error message on creating the linked server to the SQL Server
2000 (in Windows 2003 Server),
"Error 18452: Login failed for user 'sa'. Reason: Not associated with a
trusted SQL Server connection."
The client side is MSDE 2000 (in WinXP Pro).
But I can successfully create this linked server relationship when the
server side is SQL Server 2000 (in Windows 2000 Server or WinXP Pro). If the
OS in server side is changed to Windows 2003 Server, the above error
occurred. Why?
Thanks a lot.
CurtisCurtis,
I very much doubt that the OS version has anything to do with your
problem. The error message clearly shows that the Server you are trying
to connect to expects a trusted Windows login. The sa account is a SQL
Server login and that's why the login attempt fails. Change the
authentification mode on the SQL server to mixed (SQL and Windows
authentification) and it should work.
Markus|||Thanks for you reply.
But for the Windows2003Server, the authentification mode is already mixed
mode.
One more thing, this error message has not been shown if both are the SQL
Server 2000 (in Windows2003Server). It's only appeared with either one is
the WinXP Pro. So, I've suspected that anything goes wrong in the client.
P.S.: Also, I've just walked through different forums, and the solution is
same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
problem.
Curtis
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
> Curtis,
> I very much doubt that the OS version has anything to do with your
> problem. The error message clearly shows that the Server you are trying
> to connect to expects a trusted Windows login. The sa account is a SQL
> Server login and that's why the login attempt fails. Change the
> authentification mode on the SQL server to mixed (SQL and Windows
> authentification) and it should work.
> Markus
>|||Lun wrote:
> Thanks for you reply.
> But for the Windows2003Server, the authentification mode is already mixed
> mode.
> One more thing, this error message has not been shown if both are the SQL
> Server 2000 (in Windows2003Server). It's only appeared with either one is
> the WinXP Pro. So, I've suspected that anything goes wrong in the client.
> P.S.: Also, I've just walked through different forums, and the solution is
> same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
> problem.
> Curtis
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
>> Curtis,
>> I very much doubt that the OS version has anything to do with your
>> problem. The error message clearly shows that the Server you are trying
>> to connect to expects a trusted Windows login. The sa account is a SQL
>> Server login and that's why the login attempt fails. Change the
>> authentification mode on the SQL server to mixed (SQL and Windows
>> authentification) and it should work.
>> Markus
>
Have you created a linkedserver login? It sounds like you're trying to
connect with a SQL user but that you haven't linked this user to a login
on the linked server.
Try to look up sp_addlinkedserver and/or sp_addlinkedsrvlogin in Books
On Line.
Regards
Steen

Linked Server error in Windows2003Server

Dear all,
I've got this error message on creating the linked server to the SQL Server
2000 (in Windows 2003 Server),
"Error 18452: Login failed for user 'sa'. Reason: Not associated with a
trusted SQL Server connection."
The client side is MSDE 2000 (in WinXP Pro).
But I can successfully create this linked server relationship when the
server side is SQL Server 2000 (in Windows 2000 Server or WinXP Pro). If the
OS in server side is changed to Windows 2003 Server, the above error
occurred. Why?
Thanks a lot.
CurtisCurtis,
I very much doubt that the OS version has anything to do with your
problem. The error message clearly shows that the Server you are trying
to connect to expects a trusted Windows login. The sa account is a SQL
Server login and that's why the login attempt fails. Change the
authentification mode on the SQL server to mixed (SQL and Windows
authentification) and it should work.
Markus|||Thanks for you reply.
But for the Windows2003Server, the authentification mode is already mixed
mode.
One more thing, this error message has not been shown if both are the SQL
Server 2000 (in Windows2003Server). It's only appeared with either one is
the WinXP Pro. So, I've suspected that anything goes wrong in the client.
P.S.: Also, I've just walked through different forums, and the solution is
same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
problem.
Curtis
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
> Curtis,
> I very much doubt that the OS version has anything to do with your
> problem. The error message clearly shows that the Server you are trying
> to connect to expects a trusted Windows login. The sa account is a SQL
> Server login and that's why the login attempt fails. Change the
> authentification mode on the SQL server to mixed (SQL and Windows
> authentification) and it should work.
> Markus
>|||Lun wrote:
> Thanks for you reply.
> But for the Windows2003Server, the authentification mode is already mixed
> mode.
> One more thing, this error message has not been shown if both are the SQL
> Server 2000 (in Windows2003Server). It's only appeared with either one is
> the WinXP Pro. So, I've suspected that anything goes wrong in the client.
> P.S.: Also, I've just walked through different forums, and the solution is
> same as yours, i.e. set to the mixed mode, but it seems doesn't work on my
> problem.
> Curtis
> "MarkusB" <m.bohse@.quest-consultants.com> wrote in message
> news:1141135359.765464.289340@.t39g2000cwt.googlegroups.com...
>
Have you created a linkedserver login? It sounds like you're trying to
connect with a SQL user but that you haven't linked this user to a login
on the linked server.
Try to look up sp_addlinkedserver and/or sp_addlinkedsrvlogin in Books
On Line.
Regards
Steen

Monday, March 12, 2012

Linked server connection to SQL Server 2005

I have an error message of connecting to a SQL Server 2005 from SQL Server
7.0. I tried to set up a linked server connection from SQL Server 2000 and
it works fine. It not working on SQL Server 7.0. Is there a restriction of
connecting to SQL Server 2005 from 7.0? Thanks!
Error Message: Error 2: Access denied
Hi
Usually this message is because there is security issue. Have you run
sp_addlinkedsrvlogin?
John
"KTN" wrote:

> I have an error message of connecting to a SQL Server 2005 from SQL Server
> 7.0. I tried to set up a linked server connection from SQL Server 2000 and
> it works fine. It not working on SQL Server 7.0. Is there a restriction of
> connecting to SQL Server 2005 from 7.0? Thanks!
> Error Message: Error 2: Access denied
|||I used both sp_addserver & sp_addlinkedsrvlogin. I tried these on SQL Server
2000 connecting to 2005 and it works. I then used the same script on SQL
Server 7.0 and it failed. Is this something Microsoft are not supporting on
version 7.0 with 2005? Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Usually this message is because there is security issue. Have you run
> sp_addlinkedsrvlogin?
> John
> "KTN" wrote:

Linked server connection to SQL Server 2005

I have an error message of connecting to a SQL Server 2005 from SQL Server
7.0. I tried to set up a linked server connection from SQL Server 2000 and
it works fine. It not working on SQL Server 7.0. Is there a restriction of
connecting to SQL Server 2005 from 7.0? Thanks!
Error Message: Error 2: Access deniedHi
Usually this message is because there is security issue. Have you run
sp_addlinkedsrvlogin?
John
"KTN" wrote:
> I have an error message of connecting to a SQL Server 2005 from SQL Server
> 7.0. I tried to set up a linked server connection from SQL Server 2000 and
> it works fine. It not working on SQL Server 7.0. Is there a restriction of
> connecting to SQL Server 2005 from 7.0? Thanks!
> Error Message: Error 2: Access denied|||I used both sp_addserver & sp_addlinkedsrvlogin. I tried these on SQL Server
2000 connecting to 2005 and it works. I then used the same script on SQL
Server 7.0 and it failed. Is this something Microsoft are not supporting on
version 7.0 with 2005? Thanks!
"John Bell" wrote:
> Hi
> Usually this message is because there is security issue. Have you run
> sp_addlinkedsrvlogin?
> John
> "KTN" wrote:
> > I have an error message of connecting to a SQL Server 2005 from SQL Server
> > 7.0. I tried to set up a linked server connection from SQL Server 2000 and
> > it works fine. It not working on SQL Server 7.0. Is there a restriction of
> > connecting to SQL Server 2005 from 7.0? Thanks!
> >
> > Error Message: Error 2: Access denied

Linked server connection to SQL Server 2005

I have an error message of connecting to a SQL Server 2005 from SQL Server
7.0. I tried to set up a linked server connection from SQL Server 2000 and
it works fine. It not working on SQL Server 7.0. Is there a restriction of
connecting to SQL Server 2005 from 7.0? Thanks!
Error Message: Error 2: Access deniedHi
Usually this message is because there is security issue. Have you run
sp_addlinkedsrvlogin?
John
"KTN" wrote:

> I have an error message of connecting to a SQL Server 2005 from SQL Server
> 7.0. I tried to set up a linked server connection from SQL Server 2000 an
d
> it works fine. It not working on SQL Server 7.0. Is there a restriction
of
> connecting to SQL Server 2005 from 7.0? Thanks!
> Error Message: Error 2: Access denied|||I used both sp_addserver & sp_addlinkedsrvlogin. I tried these on SQL Serve
r
2000 connecting to 2005 and it works. I then used the same script on SQL
Server 7.0 and it failed. Is this something Microsoft are not supporting on
version 7.0 with 2005? Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Usually this message is because there is security issue. Have you run
> sp_addlinkedsrvlogin?
> John
> "KTN" wrote:
>

Friday, March 30, 2012

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

Linked server queries getting EXCEPTION_ACCESS_VIOLATION message

linked server problem with error message 7391

linked server problem with error message 7391

Wednesday, March 28, 2012

Linked Server PROBLEM For MySQL Database

Monday, March 26, 2012

Linked server OPENQUERY, error 'The name <> is not a valid ide

Friday, March 23, 2012

Linked Server Issues

Linked Server Issues

Linked Server Issues

Wednesday, March 21, 2012

Linked server from 2005 to 2000 failed

Linked server from 2005 to 2000 failed

Linked server from 2005 to 2000 failed

Monday, March 19, 2012

Linked Server Fails w/Error: 7399

Linked Server error in Windows2003Server

Linked Server error in Windows2003Server

Linked Server error in Windows2003Server

Monday, March 12, 2012

Linked server connection to SQL Server 2005

Linked server connection to SQL Server 2005

Linked server connection to SQL Server 2005

Sql Linked Server

Blog Archive

About Me