The below works perfectly in SQL 2000.
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODBC.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. The
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/Brian
May check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:
> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, this
> dont work either)
> /Brian
>
|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com
Showing posts with label setup. Show all posts
Showing posts with label setup. Show all posts
Wednesday, March 21, 2012
servers in SQL 2005.
The below works perfectly in SQL 2000.
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODB
C.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. Th
e
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/BrianMay check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:
> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for O
DBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error.
The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, th
is
> dont work either)
> /Brian
>|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODB
C.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. Th
e
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/BrianMay check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:
> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for O
DBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error.
The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, th
is
> dont work either)
> /Brian
>|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com
servers and Triggers...
I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.Because in Query Analyzer, depending on the environment parameters, you
might not be illiciting a Distributed Transaction. However, in a stored
procedure, and most assuradely a trigger, you will have to begin a
distributed transaction unless you explicitly override the creation of one.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.|||Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of one.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes back
> when I have a trigger setup to update a Pervasive table from MSSQL. However
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the 7391
> error.
> Why would they work fine in the query analyzer and not from a MSSQL trigger
> or stored procedure?
> Thank you for any help.
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_07D2_01C4F23F.A803CFA0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Explicitly set your environment parameters:
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
There is another one for remote procedure transactions that you can set off
as well. You'll need to look it up.
Also, make sure you do not create any locks on the remote read. Depending
on the platform, you need to set READ UNCOMMITTED isolation levels on your
sources.
The other option is to verify that the linked_server is configured properly
for distributed transactions.
However, I suspect that this worked in QA for you because you did not have
IMPLICIT_TRANSACTIONS set on. These are configurable from the interface
under Connection Properties or Current Query Properties.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:6281615A-F7FA-4EAB-BFEA-D0E92C52C3EF@.microsoft.com...
Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of
one.
>
> Sincerely,
>
>
> Anthony Thomas
>
>
> --
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers
and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes
back
> when I have a trigger setup to update a Pervasive table from MSSQL.
However
> if I run those queries in the query anaylzer they run fine.
>
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer
Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
>
> I have a trigger set up to run the update queries but it fails with the
7391
> error.
>
> Why would they work fine in the query analyzer and not from a MSSQL
trigger
> or stored procedure?
>
> Thank you for any help.
>
>
>
--=_NextPart_000_07D2_01C4F23F.A803CFA0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Explicitly set your environment parameters:
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS =OFF
There is another one for remote =procedure transactions that you can set off as well. You'll need to look it up.
Also, make sure you do not create any =locks on the remote read. Depending on the platform, you need to set READ UNCOMMITTED isolation levels on your sources.
The other option is to verify that =the linked_server is configured properly for distributed =transactions.
However, I suspect that this worked =in QA for you because you did not have IMPLICIT_TRANSACTIONS set on. These are configurable from the interface under Connection Properties or Current =Query Properties.
Sincerely,
Anthony Thomas
--
"Mike" wrote in message news:628=1615A-F7FA-4EAB-BFEA-D0E92C52C3EF@.microsoft.com...Anthony:So what I use to override the creation of one? =MCR"AnthonyThomas" wrote:> Because in Query Analyzer, depending on the =environment parameters, you> might not be illiciting a Distributed Transaction. However, in a stored> procedure, and most =assuradely a trigger, you will have to begin a> distributed transaction =unless you explicitly override the creation of one.> > =Sincerely,> > > Anthony Thomas> > > -- > = > "Mike" wrote in message> news:82A=D4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...> I have a read many of the threads that have to do with linked servers and> have not found out how to over come the 7391 error. I have =setup up> everything using both openrowset and openquery and the same =error comes back> when I have a trigger setup to update a Pervasive =table from MSSQL. However> if I run those queries in the query =anaylzer they run fine.> > Here is the openrowset query:> =update openrowset('MSDASQL','DSN=3DMAXDAT','select * from "Customer =Master"')> set UDFREF_23=3D'ABC' where custid_23=3D'2400047'> > => and here is the openquery query:> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer> Master"') set =UDFREF_23=3D'ZZZ' where custid_23=3D'2400047'> > I have a trigger set up =to run the update queries but it fails with the 7391> error.> => Why would they work fine in the query analyzer and not from a MSSQL trigger> or stored procedure?> > Thank you for =any help.> > >
--=_NextPart_000_07D2_01C4F23F.A803CFA0--
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.Because in Query Analyzer, depending on the environment parameters, you
might not be illiciting a Distributed Transaction. However, in a stored
procedure, and most assuradely a trigger, you will have to begin a
distributed transaction unless you explicitly override the creation of one.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.|||Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of one.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes back
> when I have a trigger setup to update a Pervasive table from MSSQL. However
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the 7391
> error.
> Why would they work fine in the query analyzer and not from a MSSQL trigger
> or stored procedure?
> Thank you for any help.
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_07D2_01C4F23F.A803CFA0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Explicitly set your environment parameters:
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
There is another one for remote procedure transactions that you can set off
as well. You'll need to look it up.
Also, make sure you do not create any locks on the remote read. Depending
on the platform, you need to set READ UNCOMMITTED isolation levels on your
sources.
The other option is to verify that the linked_server is configured properly
for distributed transactions.
However, I suspect that this worked in QA for you because you did not have
IMPLICIT_TRANSACTIONS set on. These are configurable from the interface
under Connection Properties or Current Query Properties.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:6281615A-F7FA-4EAB-BFEA-D0E92C52C3EF@.microsoft.com...
Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of
one.
>
> Sincerely,
>
>
> Anthony Thomas
>
>
> --
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers
and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes
back
> when I have a trigger setup to update a Pervasive table from MSSQL.
However
> if I run those queries in the query anaylzer they run fine.
>
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer
Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
>
> I have a trigger set up to run the update queries but it fails with the
7391
> error.
>
> Why would they work fine in the query analyzer and not from a MSSQL
trigger
> or stored procedure?
>
> Thank you for any help.
>
>
>
--=_NextPart_000_07D2_01C4F23F.A803CFA0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Explicitly set your environment parameters:
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS =OFF
There is another one for remote =procedure transactions that you can set off as well. You'll need to look it up.
Also, make sure you do not create any =locks on the remote read. Depending on the platform, you need to set READ UNCOMMITTED isolation levels on your sources.
The other option is to verify that =the linked_server is configured properly for distributed =transactions.
However, I suspect that this worked =in QA for you because you did not have IMPLICIT_TRANSACTIONS set on. These are configurable from the interface under Connection Properties or Current =Query Properties.
Sincerely,
Anthony Thomas
--
"Mike"
--=_NextPart_000_07D2_01C4F23F.A803CFA0--
servers and Triggers...
I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.
Because in Query Analyzer, depending on the environment parameters, you
might not be illiciting a Distributed Transaction. However, in a stored
procedure, and most assuradely a trigger, you will have to begin a
distributed transaction unless you explicitly override the creation of one.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.
|||Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of one.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes back
> when I have a trigger setup to update a Pervasive table from MSSQL. However
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the 7391
> error.
> Why would they work fine in the query analyzer and not from a MSSQL trigger
> or stored procedure?
> Thank you for any help.
>
>
|||Explicitly set your environment parameters:
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
There is another one for remote procedure transactions that you can set off
as well. You'll need to look it up.
Also, make sure you do not create any locks on the remote read. Depending
on the platform, you need to set READ UNCOMMITTED isolation levels on your
sources.
The other option is to verify that the linked_server is configured properly
for distributed transactions.
However, I suspect that this worked in QA for you because you did not have
IMPLICIT_TRANSACTIONS set on. These are configurable from the interface
under Connection Properties or Current Query Properties.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:6281615A-F7FA-4EAB-BFEA-D0E92C52C3EF@.microsoft.com...
Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of
one.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers
and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes
back
> when I have a trigger setup to update a Pervasive table from MSSQL.
However
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer
Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the
7391
> error.
> Why would they work fine in the query analyzer and not from a MSSQL
trigger
> or stored procedure?
> Thank you for any help.
>
>
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.
Because in Query Analyzer, depending on the environment parameters, you
might not be illiciting a Distributed Transaction. However, in a stored
procedure, and most assuradely a trigger, you will have to begin a
distributed transaction unless you explicitly override the creation of one.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.
|||Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of one.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes back
> when I have a trigger setup to update a Pervasive table from MSSQL. However
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the 7391
> error.
> Why would they work fine in the query analyzer and not from a MSSQL trigger
> or stored procedure?
> Thank you for any help.
>
>
|||Explicitly set your environment parameters:
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
There is another one for remote procedure transactions that you can set off
as well. You'll need to look it up.
Also, make sure you do not create any locks on the remote read. Depending
on the platform, you need to set READ UNCOMMITTED isolation levels on your
sources.
The other option is to verify that the linked_server is configured properly
for distributed transactions.
However, I suspect that this worked in QA for you because you did not have
IMPLICIT_TRANSACTIONS set on. These are configurable from the interface
under Connection Properties or Current Query Properties.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:6281615A-F7FA-4EAB-BFEA-D0E92C52C3EF@.microsoft.com...
Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of
one.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers
and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes
back
> when I have a trigger setup to update a Pervasive table from MSSQL.
However
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer
Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the
7391
> error.
> Why would they work fine in the query analyzer and not from a MSSQL
trigger
> or stored procedure?
> Thank you for any help.
>
>
servers and Triggers...
I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','selec
t * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.Because in Query Analyzer, depending on the environment parameters, you
might not be illiciting a Distributed Transaction. However, in a stored
procedure, and most assuradely a trigger, you will have to begin a
distributed transaction unless you explicitly override the creation of one.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','selec
t * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.|||Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of one
.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes ba
ck
> when I have a trigger setup to update a Pervasive table from MSSQL. Howeve
r
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','selec
t * from "Customer Master"'
)
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the 73
91
> error.
> Why would they work fine in the query analyzer and not from a MSSQL trigge
r
> or stored procedure?
> Thank you for any help.
>
>|||Explicitly set your environment parameters:
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
There is another one for remote procedure transactions that you can set off
as well. You'll need to look it up.
Also, make sure you do not create any locks on the remote read. Depending
on the platform, you need to set READ UNCOMMITTED isolation levels on your
sources.
The other option is to verify that the linked_server is configured properly
for distributed transactions.
However, I suspect that this worked in QA for you because you did not have
IMPLICIT_TRANSACTIONS set on. These are configurable from the interface
under Connection Properties or Current Query Properties.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:6281615A-F7FA-4EAB-BFEA-D0E92C52C3EF@.microsoft.com...
Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of
one.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers
and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes
back
> when I have a trigger setup to update a Pervasive table from MSSQL.
However
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','selec
t * from "Customer
Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the
7391
> error.
> Why would they work fine in the query analyzer and not from a MSSQL
trigger
> or stored procedure?
> Thank you for any help.
>
>
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','selec
t * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.Because in Query Analyzer, depending on the environment parameters, you
might not be illiciting a Distributed Transaction. However, in a stored
procedure, and most assuradely a trigger, you will have to begin a
distributed transaction unless you explicitly override the creation of one.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','selec
t * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.|||Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of one
.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes ba
ck
> when I have a trigger setup to update a Pervasive table from MSSQL. Howeve
r
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','selec
t * from "Customer Master"'
)
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the 73
91
> error.
> Why would they work fine in the query analyzer and not from a MSSQL trigge
r
> or stored procedure?
> Thank you for any help.
>
>|||Explicitly set your environment parameters:
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
There is another one for remote procedure transactions that you can set off
as well. You'll need to look it up.
Also, make sure you do not create any locks on the remote read. Depending
on the platform, you need to set READ UNCOMMITTED isolation levels on your
sources.
The other option is to verify that the linked_server is configured properly
for distributed transactions.
However, I suspect that this worked in QA for you because you did not have
IMPLICIT_TRANSACTIONS set on. These are configurable from the interface
under Connection Properties or Current Query Properties.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:6281615A-F7FA-4EAB-BFEA-D0E92C52C3EF@.microsoft.com...
Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of
one.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers
and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes
back
> when I have a trigger setup to update a Pervasive table from MSSQL.
However
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','selec
t * from "Customer
Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the
7391
> error.
> Why would they work fine in the query analyzer and not from a MSSQL
trigger
> or stored procedure?
> Thank you for any help.
>
>
servers and Triggers
I have two servers one on SQL Server 2000 one on SQL Server 7
I have setup the two servers so that they are linked and have added appropriate logins.
How it works is a record is inserted into a database on SQL 2000 which has a trigger on it that send the record to a stored procedure on the SQL 7 server, from there this places the record into a table, which calls a trigger. Now this all works fine when I use the query analyser however when I don't use it, the record does not get inserted anywhere. Now I have stepped through it and it works up until the last trigger, if I remove that everything works fine. However the code in this trigger works fine, as when I use the quuery analyser everything works just as it should.
Does anyone have any suggestions as to how I can get this to work?
Thanks :-)Look into heterogenous queries in BOL.|||Make sure the last trigger is compiled using requirements of heterogeneous queries.
I have setup the two servers so that they are linked and have added appropriate logins.
How it works is a record is inserted into a database on SQL 2000 which has a trigger on it that send the record to a stored procedure on the SQL 7 server, from there this places the record into a table, which calls a trigger. Now this all works fine when I use the query analyser however when I don't use it, the record does not get inserted anywhere. Now I have stepped through it and it works up until the last trigger, if I remove that everything works fine. However the code in this trigger works fine, as when I use the quuery analyser everything works just as it should.
Does anyone have any suggestions as to how I can get this to work?
Thanks :-)Look into heterogenous queries in BOL.|||Make sure the last trigger is compiled using requirements of heterogeneous queries.
Monday, March 12, 2012
server: Error 17: SQL Server does not exist or access denied.
I have two sql server 2000 with mixed mode authetication. I stand at one server and setup a linked server to the other using the same user id and password.
However when I click to the Tables icon of linked server in Enterprise manager, there is an error message:
Error 17 SQL Server does not exist or access denied.
And It does not show any table.
I register remote sql server in Enterprise manager fine.
Could any one help me ?
Thanks.
Hi,
What remote login did u used for linked servers.
The remote login should exists on both the server.
Regards
Mohd Sufian
|||S1 - local server
S2 - remote server
1. Ensure that the user account used has permissions to login to S2 directly and access desired table on S2.
2. Create linked server login mapping using sp_addlinkedsrvlogin on S1.
Try a simple select query on remote table using linked server name.
both S1 and S2 have the same user id and password.
I' ve created linked server login on S1. However when I run a select query on remote table using Query Analyzer, I get the same error message ERROR 17
Do you have any more suggesstion ?
|||Can you post the query that you executed on S1? Also, when you executed the query on S1, were you connected as the user that you set for the linked server connection?
Thanks
Laurentiu
Both servers 10.0.10.237 and 10.0.10.111 run Sql SERVER 2000 with mixed mode authentication user id: sa, password: vbsp
Linked server QLNSParent from 10.0.10.237 links to 10.0.10.111 with login mapping: local login: sa, remote user: sa, remote password: vbsp
At 10.0.10.237, I login Query Analyzer using user id sa, password: vbsp. Then I run the query:
select * from QLNSParent.qlns.dbo.hoso
It alway returns the error message:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
At 10.0.10.237, I run Enterprise Manager and I can register 10.0.10.111 fine. This means thats I can connnect to 10.0.10.111 OK but not using linked server.
Do you have any suggestion ?
Thanks.
|||Can you post the output of the following commands on the 237 server?
exec sp_helpserver 'QLNSParent'
exec sp_helplinkedsrvlogin 'QLNSParent'
Thanks
Laurentiu
the result of the first command:
name: QLNSParent, network_name: NULL, status: data access, use remote collation, ID: 1, connection_timeout: 0, query_timeout: 0
and the result of the second command:
Linked server: QLNSParent, local login: sa, Self mapping: 0, Remote login: sa
I think those results are ok but there is one problem of OS level security and I can't find exactly the cause.
can you help me ?
|||Dear ,
Do u have a named instance on sqlserver2005.
from
sufian
|||Both server run windows server 2003 enterprise edition.
In each server, we have a named instance of Sql server 2000.
|||Dear,
Connect by give the servername with instance.
ex: svt_test(server name\sql_2000(instance name)
from
sufian
|||I think the way you set up your linked server was incorrect. What was the sp_addlinkedserver command that you used?
Thanks
Laurentiu
sp_addlinkedserver @.server = N'QLNSParent',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'10.0.10.111',
@.catalog = N'qlns',
@.provstr = N''
GO
sp_addlinkedsrvlogin @.rmtsrvname = 'QLNSParent',
@.useself = false,
@.locallogin = 'sa',
@.rmtuser = 'sa',
@.rmtpassword = 'vbsp'
GO
do you have any more suggesstion ?
|||Is your SQLServer installation named QLNSParent? Can you connect to it using QA and the QLNSParent name?
If the name is ok, have you tried simply specifying:
sp_addlinkedserver N'QLNSParent', N'SQL Server'
Thanks
Laurentiu
server: Error 17: SQL Server does not exist or access denied.
I have two sql server 2000 with mixed mode authetication. I stand at one server and setup a linked server to the other using the same user id and password. However when I click to the Tables icon of linked server in Enterprise manager, there is an error message: Error 17 SQL Server does not exist or access denied. And It does not show any table. I register remote sql server in Enterprise manager fine. Could any one help me ? Thanks.
I have the same problem from linking servers, did you find solution of this problem ?
Thanks
Hi,
What remote login did u used for linked servers.
The remote login should exists on both the server.
Regards
Mohd Sufian
|||S1 - local server
S2 - remote server
1. Ensure that the user account used has permissions to login to S2 directly and access desired table on S2.
2. Create linked server login mapping using sp_addlinkedsrvlogin on S1.
Try a simple select query on remote table using linked server name.
both S1 and S2 have the same user id and password.
I' ve created linked server login on S1. However when I run a select query on remote table using Query Analyzer, I get the same error message ERROR 17
Do you have any more suggesstion ?
|||Can you post the query that you executed on S1? Also, when you executed the query on S1, were you connected as the user that you set for the linked server connection?
Thanks
Laurentiu
Both servers 10.0.10.237 and 10.0.10.111 run Sql SERVER 2000 with mixed mode authentication user id: sa, password: vbsp
Linked server QLNSParent from 10.0.10.237 links to 10.0.10.111 with login mapping: local login: sa, remote user: sa, remote password: vbsp
At 10.0.10.237, I login Query Analyzer using user id sa, password: vbsp. Then I run the query:
select * from QLNSParent.qlns.dbo.hoso
It alway returns the error message:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
At 10.0.10.237, I run Enterprise Manager and I can register 10.0.10.111 fine. This means thats I can connnect to 10.0.10.111 OK but not using linked server.
Do you have any suggestion ?
Thanks.
|||Can you post the output of the following commands on the 237 server?
exec sp_helpserver 'QLNSParent'
exec sp_helplinkedsrvlogin 'QLNSParent'
Thanks
Laurentiu
the result of the first command:
name: QLNSParent, network_name: NULL, status: data access, use remote collation, ID: 1, connection_timeout: 0, query_timeout: 0
and the result of the second command:
Linked server: QLNSParent, local login: sa, Self mapping: 0, Remote login: sa
I think those results are ok but there is one problem of OS level security and I can't find exactly the cause.
can you help me ?
|||Dear ,
Do u have a named instance on sqlserver2005.
from
sufian
|||Both server run windows server 2003 enterprise edition.
In each server, we have a named instance of Sql server 2000.
|||Dear,
Connect by give the servername with instance.
ex: svt_test(server name\sql_2000(instance name)
from
sufian
|||I think the way you set up your linked server was incorrect. What was the sp_addlinkedserver command that you used?
Thanks
Laurentiu
sp_addlinkedserver @.server = N'QLNSParent',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'10.0.10.111',
@.catalog = N'qlns',
@.provstr = N''
GO
sp_addlinkedsrvlogin @.rmtsrvname = 'QLNSParent',
@.useself = false,
@.locallogin = 'sa',
@.rmtuser = 'sa',
@.rmtpassword = 'vbsp'
GO
do you have any more suggesstion ?
|||Is your SQLServer installation named QLNSParent? Can you connect to it using QA and the QLNSParent name?
If the name is ok, have you tried simply specifying:
sp_addlinkedserver N'QLNSParent', N'SQL Server'
Thanks
Laurentiu
I have the same problem from linking servers, did you find solution of this problem ?
Thanks
Friday, March 9, 2012
server via secure connection
I am trying to setup a linked server to a MySQL database over at my hosting company.
I would like the communication between the SQL server and the MySQL database to be via a secure connection. anyone know how to do this?
my DB is SQL 2000Do you just want to manage the remote MySQL from your local console, or you want your SQL Server to perform actions over the remote MySQL as part of its work?|||basically, i would like to do selects and updates to the MySQL server with data from the SQL server.|||First you need a crtificate server (could be internal) and have both machines obtain certificates from it. I know there is a Certificate Server included in the MSDN subscription but I don't know how exactly to set it up. Shouldn't be a big deal.
Then in the Client Network Utility on the SQL Server machine check the Force Protocol Encryption box.|||thanks, i will try that
I would like the communication between the SQL server and the MySQL database to be via a secure connection. anyone know how to do this?
my DB is SQL 2000Do you just want to manage the remote MySQL from your local console, or you want your SQL Server to perform actions over the remote MySQL as part of its work?|||basically, i would like to do selects and updates to the MySQL server with data from the SQL server.|||First you need a crtificate server (could be internal) and have both machines obtain certificates from it. I know there is a Certificate Server included in the MSDN subscription but I don't know how exactly to set it up. Shouldn't be a big deal.
Then in the Client Network Utility on the SQL Server machine check the Force Protocol Encryption box.|||thanks, i will try that
Friday, February 24, 2012
server to Oracle via ODBC, return less records
I have an Oracle 9i server. To access the data in Oracle, I setup an ODBC connection to it and am able to return data from it using WinSQL (a general ODBC database client). The SQL statement is simply "SELECT * FROM COLOR" and all 133 records returned properly.
I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above.
The problem is: only 32 records returned.
May I ask if there is any problem to this linked server setup?
Thanks.
Does any one has any idea..please help.^^
|||I forwarded this to a linked-server guru here at Microsoft and here's the reply he gave:
Usually provider bugs. Updating the client versions for Oracle's code often fixes this class of issue.
Note that Kagera isn't really fully supported anymore either.
Oracle's native OLE/DB provider is the usual choice instead of ODBC.
Hope this is helpful.
|||Same problem here,
created a query within Oracle SQLplus that returns 344.743 records. When I use the same query within the SSIS package it only returns 60.000 records? I'm connected with OLE DB and installed the latest Oracle 9i client. When I add a count within the query in SSIS is also concludes that there are 344.743 records.
Urgent help needed because we are migrating from DTS to SSIS.
Any idea what the problem can be?
Thankz
Ron Killaars
IT Engineer Energie
server to Oracle via ODBC, return less records
I have an Oracle 9i server. To access the data in Oracle, I setup an ODBC connection to it and am able to return data from it using WinSQL (a general ODBC database client). The SQL statement is simply "SELECT * FROM COLOR" and all 133 records returned properly.
I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above.
The problem is: only 32 records returned.
May I ask if there is any problem to this linked server setup?
Thanks.
Does any one has any idea..please help.^^
|||I forwarded this to a linked-server guru here at Microsoft and here's the reply he gave:
Usually provider bugs. Updating the client versions for Oracle's code often fixes this class of issue.
Note that Kagera isn't really fully supported anymore either.
Oracle's native OLE/DB provider is the usual choice instead of ODBC.
Hope this is helpful.
|||Same problem here,
created a query within Oracle SQLplus that returns 344.743 records. When I use the same query within the SSIS package it only returns 60.000 records? I'm connected with OLE DB and installed the latest Oracle 9i client. When I add a count within the query in SSIS is also concludes that there are 344.743 records.
Urgent help needed because we are migrating from DTS to SSIS.
Any idea what the problem can be?
Thankz
Ron Killaars
IT Engineer Energie
Subscribe to:
Posts (Atom)