Hi masters:
I get the following error when i try to create a stored procedure that query
an SQL linked Server...
[SQLSTATE 42000]
"Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures
consistent query semantics. Enable these options and then reissue your query."
this is my stored procedure script
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
The funny thing is that the same code run without problems when i query the
linked server from the Query Analyzer... and i don't get the above error.
Please help
Thanks
Rodrigo
Hi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer
|||No, this is wrong (please verify your guesses before providing them).
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the same
values for the ANSI_NULLS & ANSI_WARNINGS settings. Both servers have
to have them enabled (turned on).
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc and
used when the proc is executed. So, when you create or alter the proc
(with the linked server reference) make sure that "SET ANSI_NULLS ON" is
there before the ALTER PROC or CREATE PROC statement (just to be sure).
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA session
it used ANSI_NULLS on but when you executed the proc from that same
session it used the ANSI_NULLS value that was saved with the proc (ie.
ANSI_NULLS off). That's my guess.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:
>Hi rodrigo,
>Stored procedure run locally means it will execute on the server were u
>will create it .
>so u cannot use linkedserver in a procedure.
>u have to run it outside the procedure.
>if u wanted to schedule it write ur code in dts or job window.
>hope this help
>from
>killer
>
>
|||You should put the Ansi_nulls and warnings settings in your sp...you can use
something like this in EM's sp-property window if that's what you're using:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
Greets, Lee-Z
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:F1CE4631-A9E7-4CB9-A560-9D1EDAE0774F@.microsoft.com...
> Hi masters:
> I get the following error when i try to create a stored procedure that
> query
> an SQL linked Server...
> [SQLSTATE 42000]
> "Heterogeneous queries require the ANSI_NULLS and
> ANSI_WARNINGS options to be set for the connection. This ensures
> consistent query semantics. Enable these options and then reissue your
> query."
> this is my stored procedure script
> CREATE PROCEDURE TEST1 AS
> select * from openquery(srvmis,'select * from PC_agrupa_cde')
> GO
> The funny thing is that the same code run without problems when i query
> the
> linked server from the Query Analyzer... and i don't get the above error.
> Please help
> Thanks
> Rodrigo
Showing posts with label following. Show all posts
Showing posts with label following. Show all posts
Friday, March 30, 2012
Linking Server problem
Hi masters:
I get the following error when i try to create a stored procedure that query
an SQL linked Server...
[SQLSTATE 42000]
"Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures
consistent query semantics. Enable these options and then reissue your query."
this is my stored procedure script
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
The funny thing is that the same code run without problems when i query the
linked server from the Query Analyzer... and i don't get the above error.
Please help
Thanks
RodrigoHi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer|||This is a multi-part message in MIME format.
--060600060209060500070603
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
No, this is wrong (please verify your guesses before providing them).
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the same
values for the ANSI_NULLS & ANSI_WARNINGS settings. Both servers have
to have them enabled (turned on).
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc and
used when the proc is executed. So, when you create or alter the proc
(with the linked server reference) make sure that "SET ANSI_NULLS ON" is
there before the ALTER PROC or CREATE PROC statement (just to be sure).
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA session
it used ANSI_NULLS on but when you executed the proc from that same
session it used the ANSI_NULLS value that was saved with the proc (ie.
ANSI_NULLS off). That's my guess.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:
>Hi rodrigo,
>Stored procedure run locally means it will execute on the server were u
>will create it .
>so u cannot use linkedserver in a procedure.
>u have to run it outside the procedure.
>if u wanted to schedule it write ur code in dts or job window.
>hope this help
>from
>killer
>
>
--060600060209060500070603
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>No, this is wrong (please verify your guesses before providing
them).<br>
<br>
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.<br>
<br>
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the
same values for the ANSI_NULLS & ANSI_WARNINGS settings. Both
servers have to have them enabled (turned on).<br>
<br>
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc
and used when the proc is executed. So, when you create or alter the
proc (with the linked server reference) make sure that "SET ANSI_NULLS
ON" is there before the ALTER PROC or CREATE PROC statement (just to be
sure).<br>
<br>
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA
session it used ANSI_NULLS on but when you executed the proc from that
same session it used the ANSI_NULLS value that was saved with the proc
(ie. ANSI_NULLS off). That's my guess.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
doller wrote:
<blockquote
cite="mid1123641009.412382.267360@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">Hi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer
</pre>
</blockquote>
</body>
</html>
--060600060209060500070603--|||You should put the Ansi_nulls and warnings settings in your sp...you can use
something like this in EM's sp-property window if that's what you're using:
---
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
---
Greets, Lee-Z
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:F1CE4631-A9E7-4CB9-A560-9D1EDAE0774F@.microsoft.com...
> Hi masters:
> I get the following error when i try to create a stored procedure that
> query
> an SQL linked Server...
> [SQLSTATE 42000]
> "Heterogeneous queries require the ANSI_NULLS and
> ANSI_WARNINGS options to be set for the connection. This ensures
> consistent query semantics. Enable these options and then reissue your
> query."
> this is my stored procedure script
> CREATE PROCEDURE TEST1 AS
> select * from openquery(srvmis,'select * from PC_agrupa_cde')
> GO
> The funny thing is that the same code run without problems when i query
> the
> linked server from the Query Analyzer... and i don't get the above error.
> Please help
> Thanks
> Rodrigosql
I get the following error when i try to create a stored procedure that query
an SQL linked Server...
[SQLSTATE 42000]
"Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures
consistent query semantics. Enable these options and then reissue your query."
this is my stored procedure script
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
The funny thing is that the same code run without problems when i query the
linked server from the Query Analyzer... and i don't get the above error.
Please help
Thanks
RodrigoHi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer|||This is a multi-part message in MIME format.
--060600060209060500070603
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
No, this is wrong (please verify your guesses before providing them).
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the same
values for the ANSI_NULLS & ANSI_WARNINGS settings. Both servers have
to have them enabled (turned on).
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc and
used when the proc is executed. So, when you create or alter the proc
(with the linked server reference) make sure that "SET ANSI_NULLS ON" is
there before the ALTER PROC or CREATE PROC statement (just to be sure).
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA session
it used ANSI_NULLS on but when you executed the proc from that same
session it used the ANSI_NULLS value that was saved with the proc (ie.
ANSI_NULLS off). That's my guess.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:
>Hi rodrigo,
>Stored procedure run locally means it will execute on the server were u
>will create it .
>so u cannot use linkedserver in a procedure.
>u have to run it outside the procedure.
>if u wanted to schedule it write ur code in dts or job window.
>hope this help
>from
>killer
>
>
--060600060209060500070603
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>No, this is wrong (please verify your guesses before providing
them).<br>
<br>
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.<br>
<br>
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the
same values for the ANSI_NULLS & ANSI_WARNINGS settings. Both
servers have to have them enabled (turned on).<br>
<br>
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc
and used when the proc is executed. So, when you create or alter the
proc (with the linked server reference) make sure that "SET ANSI_NULLS
ON" is there before the ALTER PROC or CREATE PROC statement (just to be
sure).<br>
<br>
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA
session it used ANSI_NULLS on but when you executed the proc from that
same session it used the ANSI_NULLS value that was saved with the proc
(ie. ANSI_NULLS off). That's my guess.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
doller wrote:
<blockquote
cite="mid1123641009.412382.267360@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">Hi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer
</pre>
</blockquote>
</body>
</html>
--060600060209060500070603--|||You should put the Ansi_nulls and warnings settings in your sp...you can use
something like this in EM's sp-property window if that's what you're using:
---
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
---
Greets, Lee-Z
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:F1CE4631-A9E7-4CB9-A560-9D1EDAE0774F@.microsoft.com...
> Hi masters:
> I get the following error when i try to create a stored procedure that
> query
> an SQL linked Server...
> [SQLSTATE 42000]
> "Heterogeneous queries require the ANSI_NULLS and
> ANSI_WARNINGS options to be set for the connection. This ensures
> consistent query semantics. Enable these options and then reissue your
> query."
> this is my stored procedure script
> CREATE PROCEDURE TEST1 AS
> select * from openquery(srvmis,'select * from PC_agrupa_cde')
> GO
> The funny thing is that the same code run without problems when i query
> the
> linked server from the Query Analyzer... and i don't get the above error.
> Please help
> Thanks
> Rodrigosql
Linking Server problem
Hi masters:
I get the following error when i try to create a stored procedure that query
an SQL linked Server...
[SQLSTATE 42000]
"Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures
consistent query semantics. Enable these options and then reissue your query
."
this is my stored procedure script
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
The funny thing is that the same code run without problems when i query the
linked server from the Query Analyzer... and i don't get the above error.
Please help
Thanks
RodrigoHi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer|||No, this is wrong (please verify your guesses before providing them).
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the same
values for the ANSI_NULLS & ANSI_WARNINGS settings. Both servers have
to have them enabled (turned on).
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc and
used when the proc is executed. So, when you create or alter the proc
(with the linked server reference) make sure that "SET ANSI_NULLS ON" is
there before the ALTER PROC or CREATE PROC statement (just to be sure).
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA session
it used ANSI_NULLS on but when you executed the proc from that same
session it used the ANSI_NULLS value that was saved with the proc (ie.
ANSI_NULLS off). That's my guess.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:
>Hi rodrigo,
>Stored procedure run locally means it will execute on the server were u
>will create it .
>so u cannot use linkedserver in a procedure.
>u have to run it outside the procedure.
>if u wanted to schedule it write ur code in dts or job window.
>hope this help
>from
>killer
>
>|||You should put the Ansi_nulls and warnings settings in your sp...you can use
something like this in EM's sp-property window if that's what you're using:
---
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
---
Greets, Lee-Z
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:F1CE4631-A9E7-4CB9-A560-9D1EDAE0774F@.microsoft.com...
> Hi masters:
> I get the following error when i try to create a stored procedure that
> query
> an SQL linked Server...
> [SQLSTATE 42000]
> "Heterogeneous queries require the ANSI_NULLS and
> ANSI_WARNINGS options to be set for the connection. This ensures
> consistent query semantics. Enable these options and then reissue your
> query."
> this is my stored procedure script
> CREATE PROCEDURE TEST1 AS
> select * from openquery(srvmis,'select * from PC_agrupa_cde')
> GO
> The funny thing is that the same code run without problems when i query
> the
> linked server from the Query Analyzer... and i don't get the above error.
> Please help
> Thanks
> Rodrigo
I get the following error when i try to create a stored procedure that query
an SQL linked Server...
[SQLSTATE 42000]
"Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures
consistent query semantics. Enable these options and then reissue your query
."
this is my stored procedure script
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
The funny thing is that the same code run without problems when i query the
linked server from the Query Analyzer... and i don't get the above error.
Please help
Thanks
RodrigoHi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer|||No, this is wrong (please verify your guesses before providing them).
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the same
values for the ANSI_NULLS & ANSI_WARNINGS settings. Both servers have
to have them enabled (turned on).
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc and
used when the proc is executed. So, when you create or alter the proc
(with the linked server reference) make sure that "SET ANSI_NULLS ON" is
there before the ALTER PROC or CREATE PROC statement (just to be sure).
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA session
it used ANSI_NULLS on but when you executed the proc from that same
session it used the ANSI_NULLS value that was saved with the proc (ie.
ANSI_NULLS off). That's my guess.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:
>Hi rodrigo,
>Stored procedure run locally means it will execute on the server were u
>will create it .
>so u cannot use linkedserver in a procedure.
>u have to run it outside the procedure.
>if u wanted to schedule it write ur code in dts or job window.
>hope this help
>from
>killer
>
>|||You should put the Ansi_nulls and warnings settings in your sp...you can use
something like this in EM's sp-property window if that's what you're using:
---
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
---
Greets, Lee-Z
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:F1CE4631-A9E7-4CB9-A560-9D1EDAE0774F@.microsoft.com...
> Hi masters:
> I get the following error when i try to create a stored procedure that
> query
> an SQL linked Server...
> [SQLSTATE 42000]
> "Heterogeneous queries require the ANSI_NULLS and
> ANSI_WARNINGS options to be set for the connection. This ensures
> consistent query semantics. Enable these options and then reissue your
> query."
> this is my stored procedure script
> CREATE PROCEDURE TEST1 AS
> select * from openquery(srvmis,'select * from PC_agrupa_cde')
> GO
> The funny thing is that the same code run without problems when i query
> the
> linked server from the Query Analyzer... and i don't get the above error.
> Please help
> Thanks
> Rodrigo
Linking Oracle Servers in SQL Server 2005 Express
Hi Everyone,
I am trying to link an Oracle server to my instance of SQL Server 2005
Express. I have used the following code to add the linked server:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = 'ActualNameOfOracleServer'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.rmtuser='USERNAME',
@.rmtpassword='PASSWORD'
GO
This all runs very nicely and completes without errors. I then try to
query the linked server I have just added, and the whole thing just
hangs there "executing" the query (Management Studio Express). I have
also tried this with the MSDAORA (Microsoft) provider, and that returns
a "cannot initialise object" error.
I can access the Oracle server with the Oracle tools that I have
installed, and I can access the tables in the Oracle server by linking
them in MS Access (using a User DSN). It just wont work in SQL Server
it seems.
Can anybody help with this one? I am truly stuck and desperately want
to avoid having to use Access for this (we still use Access 97 would
you believe as a corporate standard!).
Cheers, and thanks in Advance
The Frog
"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164125803.967272.173060@.f16g2000cwb.googlegr oups.com...
> Hi Everyone,
> I am trying to link an Oracle server to my instance of SQL Server 2005
> Express. I have used the following code to add the linked server:
> USE MASTER
> GO
> EXEC sp_addlinkedserver
> @.server = 'NameForLinkedServer',
> @.srvproduct = 'Oracle',
> @.provider = 'OraOLEDB.Oracle',
> @.datasrc = 'ActualNameOfOracleServer'
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
> GO
> Exec sp_addlinkedsrvlogin
> @.rmtsrvname='NameForLinkedServer',
> @.rmtuser='USERNAME',
> @.rmtpassword='PASSWORD'
> GO
> This all runs very nicely and completes without errors. I then try to
> query the linked server I have just added, and the whole thing just
> hangs there "executing" the query (Management Studio Express). I have
> also tried this with the MSDAORA (Microsoft) provider, and that returns
> a "cannot initialise object" error.
> I can access the Oracle server with the Oracle tools that I have
> installed, and I can access the tables in the Oracle server by linking
> them in MS Access (using a User DSN). It just wont work in SQL Server
> it seems.
> Can anybody help with this one? I am truly stuck and desperately want
> to avoid having to use Access for this (we still use Access 97 would
> you believe as a corporate standard!).
>
Remember to set AllowInProcess for OraOLEDB.Oracle.
EG
USE MASTER
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
sp_dropserver @.server=N'NameForLinkedServer', @.droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//HOSTNAME/SERVICE'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = N'NameForLinkedServer',
@.useself = N'False',
@.rmtuser = N'USERNAME',
@.rmtpassword = N'PASSWORD'
GO
GO
SELECT * FROM OPENQUERY(NameForLinkedServer,'SELECT 1 D FROM DUAL')
David
|||Thanks for that David,
I have given it a try, and the SQL instructions have run and made the
changes. Unfortunately there is still no action when trying to query
the linked server. I have had a quick look at the network performance
on the pc, and there is not even any network activity.
Do you know if there is a way that I could perhaps query the User DSN
from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
and two-step the solution since it doesnt seem to want to play nicely
with me...
Cheers
The Frog
|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164185311.741862.54970@.e3g2000cwe.googlegrou ps.com...
> Thanks for that David,
> I have given it a try, and the SQL instructions have run and made the
> changes. Unfortunately there is still no action when trying to query
> the linked server. I have had a quick look at the network performance
> on the pc, and there is not even any network activity.
> Do you know if there is a way that I could perhaps query the User DSN
> from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
> and two-step the solution since it doesnt seem to want to play nicely
> with me...
>
A couple of things to check out:
Make sure
-you have rebooted since installing the Oracle Client.
-the SQL Service account have permissions for the Oracle client folders.
David
|||Thanks again David,
I have checked the permissions and also rebooted the machine a few
times just to be sure. They all seem in order. I did notice something
when playing with the Oracle Client software however that may be
important: The server is an Oracle 9i server, but the client software
is 8.17.
I have downloaded an ODBC driver from Oracle, but have not been able to
try it because I need something called the Oracle Universal
Installer(?). Apparently this installer comes with the client
software, but I am unable to locate it (only the SQL Plus, and another
called Oracle ODBC Test).
Do you think it may be that my driver is simply too old? I would have
thought that MS Access would have also complained (but since it is
Access 97 maybe the 8.17 driver software is actually more sophistocated
than it is?). I was thinking that there may be an incompatability
between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
guess.
Thankyou so much for trying to help with this, it is greatly
appreciated.
Cheers
The Frog
David Browne wrote:[vbcol=seagreen]
> "The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
> news:1164185311.741862.54970@.e3g2000cwe.googlegrou ps.com...
<SNIP>
|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164201695.690139.214840@.b28g2000cwb.googlegr oups.com...
> Thanks again David,
> I have checked the permissions and also rebooted the machine a few
> times just to be sure. They all seem in order. I did notice something
> when playing with the Oracle Client software however that may be
> important: The server is an Oracle 9i server, but the client software
> is 8.17.
> I have downloaded an ODBC driver from Oracle, but have not been able to
> try it because I need something called the Oracle Universal
> Installer(?). Apparently this installer comes with the client
> software, but I am unable to locate it (only the SQL Plus, and another
> called Oracle ODBC Test).
> Do you think it may be that my driver is simply too old? I would have
> thought that MS Access would have also complained (but since it is
> Access 97 maybe the 8.17 driver software is actually more sophistocated
> than it is?). I was thinking that there may be an incompatability
> between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
> guess.
> Thankyou so much for trying to help with this, it is greatly
> appreciated.
>
That is a very old driver. I would the latest OleDb provider, available at
http://www.oracle.com/technology/tech/windows/ole_db/index.html.
David
|||Thankyou once again David,
I am downloading the product from the link now. I will install and
trial it today, and post here again with the results a little later on.
Just thinking about this issue logically, it probably is the driver
thats the cause of the problem. Its a large file, and the company
internet connection is a little slow, so I will have to come back on
this one.
Thanks again for all the help.
Cheers
The Frog
|||Hi David,
I have installed the software, and copied the TNSNames.ora file to the
network\admin directory. I seem to be getting network activity now, and
I am getting a response from the Oracle server. The message is an error
of some kind (ORA-12154), which when I look it up states that the issue
is with the TNSNAMES.ora file. I run the network configurator tool for
the product, and am able to test the connection - and it works (once it
has the right username and password).
I have tried to configure the Linked Server several different ways, but
basically the script we discussed earlier is what is being used to
create the server. I have noticed in the servername for the linked
server that it was not exactly the same as the name in the TNSNAMES.ora
file. The ora file has a name like this:
oracledatabase.sub-domain-domain. When I change the script to match
this the response is instant with the same error message above. If the
script drops the domain extensions from the name query takes a few
seconds before returning a 'not going to happen' response.
You have helped me so much with this, I cannot thank you enough for
getting me this far. I hope I am not impinging on your time and
patience too much with this. I am comfortable once I am in SQL Server,
but I am really not an Oracle guy (I have not had enough experience
with it to date).
Thankyou once again
The Frog
|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164294579.219182.26860@.h54g2000cwb.googlegro ups.com...
> Hi David,
> I have installed the software, and copied the TNSNames.ora file to the
> network\admin directory. I seem to be getting network activity now, and
> I am getting a response from the Oracle server. The message is an error
> of some kind (ORA-12154), which when I look it up states that the issue
> is with the TNSNAMES.ora file. I run the network configurator tool for
> the product, and am able to test the connection - and it works (once it
> has the right username and password).
> I have tried to configure the Linked Server several different ways, but
> basically the script we discussed earlier is what is being used to
> create the server. I have noticed in the servername for the linked
> server that it was not exactly the same as the name in the TNSNAMES.ora
> file. The ora file has a name like this:
> oracledatabase.sub-domain-domain. When I change the script to match
> this the response is instant with the same error message above. If the
> script drops the domain extensions from the name query takes a few
> seconds before returning a 'not going to happen' response.
> You have helped me so much with this, I cannot thank you enough for
> getting me this far. I hope I am not impinging on your time and
> patience too much with this. I am comfortable once I am in SQL Server,
> but I am really not an Oracle guy (I have not had enough experience
> with it to date).
>
Now, with the new driver, you can take advantage of one of the best new
features of the Oracle client: bypassing the tnsnames.ora file. With the
10g client you simply don't need a tnsnames.ora file. <<sound of much
rejoicing>>
Instead you specify //hostname/servicename where hostname is the IP address
(or DNS name) of the oracle server, and servicename is the instance.
David
|||David you are an absolute LEGEND!
It works like a charm. The script I used in the end is as follows:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.useself= FALSE,
@.rmtuser='remoteusername',
@.rmtpassword='remoteuserpassword'
GO
This was done with the Visual Studio Developer tools installed from the
link you provided above, to make sure all the drivers etc... were up to
date.
Its quick, clean, and scriptable all the way through. I cant thank you
enough. If you are ever in Germany, the beer and schnitzels are on me
Thankyou, thankyou, thankyou
Indebted to your services
The Frog
I am trying to link an Oracle server to my instance of SQL Server 2005
Express. I have used the following code to add the linked server:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = 'ActualNameOfOracleServer'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.rmtuser='USERNAME',
@.rmtpassword='PASSWORD'
GO
This all runs very nicely and completes without errors. I then try to
query the linked server I have just added, and the whole thing just
hangs there "executing" the query (Management Studio Express). I have
also tried this with the MSDAORA (Microsoft) provider, and that returns
a "cannot initialise object" error.
I can access the Oracle server with the Oracle tools that I have
installed, and I can access the tables in the Oracle server by linking
them in MS Access (using a User DSN). It just wont work in SQL Server
it seems.
Can anybody help with this one? I am truly stuck and desperately want
to avoid having to use Access for this (we still use Access 97 would
you believe as a corporate standard!).
Cheers, and thanks in Advance
The Frog
"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164125803.967272.173060@.f16g2000cwb.googlegr oups.com...
> Hi Everyone,
> I am trying to link an Oracle server to my instance of SQL Server 2005
> Express. I have used the following code to add the linked server:
> USE MASTER
> GO
> EXEC sp_addlinkedserver
> @.server = 'NameForLinkedServer',
> @.srvproduct = 'Oracle',
> @.provider = 'OraOLEDB.Oracle',
> @.datasrc = 'ActualNameOfOracleServer'
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
> GO
> Exec sp_addlinkedsrvlogin
> @.rmtsrvname='NameForLinkedServer',
> @.rmtuser='USERNAME',
> @.rmtpassword='PASSWORD'
> GO
> This all runs very nicely and completes without errors. I then try to
> query the linked server I have just added, and the whole thing just
> hangs there "executing" the query (Management Studio Express). I have
> also tried this with the MSDAORA (Microsoft) provider, and that returns
> a "cannot initialise object" error.
> I can access the Oracle server with the Oracle tools that I have
> installed, and I can access the tables in the Oracle server by linking
> them in MS Access (using a User DSN). It just wont work in SQL Server
> it seems.
> Can anybody help with this one? I am truly stuck and desperately want
> to avoid having to use Access for this (we still use Access 97 would
> you believe as a corporate standard!).
>
Remember to set AllowInProcess for OraOLEDB.Oracle.
EG
USE MASTER
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
sp_dropserver @.server=N'NameForLinkedServer', @.droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//HOSTNAME/SERVICE'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = N'NameForLinkedServer',
@.useself = N'False',
@.rmtuser = N'USERNAME',
@.rmtpassword = N'PASSWORD'
GO
GO
SELECT * FROM OPENQUERY(NameForLinkedServer,'SELECT 1 D FROM DUAL')
David
|||Thanks for that David,
I have given it a try, and the SQL instructions have run and made the
changes. Unfortunately there is still no action when trying to query
the linked server. I have had a quick look at the network performance
on the pc, and there is not even any network activity.
Do you know if there is a way that I could perhaps query the User DSN
from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
and two-step the solution since it doesnt seem to want to play nicely
with me...
Cheers
The Frog
|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164185311.741862.54970@.e3g2000cwe.googlegrou ps.com...
> Thanks for that David,
> I have given it a try, and the SQL instructions have run and made the
> changes. Unfortunately there is still no action when trying to query
> the linked server. I have had a quick look at the network performance
> on the pc, and there is not even any network activity.
> Do you know if there is a way that I could perhaps query the User DSN
> from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
> and two-step the solution since it doesnt seem to want to play nicely
> with me...
>
A couple of things to check out:
Make sure
-you have rebooted since installing the Oracle Client.
-the SQL Service account have permissions for the Oracle client folders.
David
|||Thanks again David,
I have checked the permissions and also rebooted the machine a few
times just to be sure. They all seem in order. I did notice something
when playing with the Oracle Client software however that may be
important: The server is an Oracle 9i server, but the client software
is 8.17.
I have downloaded an ODBC driver from Oracle, but have not been able to
try it because I need something called the Oracle Universal
Installer(?). Apparently this installer comes with the client
software, but I am unable to locate it (only the SQL Plus, and another
called Oracle ODBC Test).
Do you think it may be that my driver is simply too old? I would have
thought that MS Access would have also complained (but since it is
Access 97 maybe the 8.17 driver software is actually more sophistocated
than it is?). I was thinking that there may be an incompatability
between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
guess.
Thankyou so much for trying to help with this, it is greatly
appreciated.
Cheers
The Frog
David Browne wrote:[vbcol=seagreen]
> "The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
> news:1164185311.741862.54970@.e3g2000cwe.googlegrou ps.com...
<SNIP>
|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164201695.690139.214840@.b28g2000cwb.googlegr oups.com...
> Thanks again David,
> I have checked the permissions and also rebooted the machine a few
> times just to be sure. They all seem in order. I did notice something
> when playing with the Oracle Client software however that may be
> important: The server is an Oracle 9i server, but the client software
> is 8.17.
> I have downloaded an ODBC driver from Oracle, but have not been able to
> try it because I need something called the Oracle Universal
> Installer(?). Apparently this installer comes with the client
> software, but I am unable to locate it (only the SQL Plus, and another
> called Oracle ODBC Test).
> Do you think it may be that my driver is simply too old? I would have
> thought that MS Access would have also complained (but since it is
> Access 97 maybe the 8.17 driver software is actually more sophistocated
> than it is?). I was thinking that there may be an incompatability
> between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
> guess.
> Thankyou so much for trying to help with this, it is greatly
> appreciated.
>
That is a very old driver. I would the latest OleDb provider, available at
http://www.oracle.com/technology/tech/windows/ole_db/index.html.
David
|||Thankyou once again David,
I am downloading the product from the link now. I will install and
trial it today, and post here again with the results a little later on.
Just thinking about this issue logically, it probably is the driver
thats the cause of the problem. Its a large file, and the company
internet connection is a little slow, so I will have to come back on
this one.
Thanks again for all the help.
Cheers
The Frog
|||Hi David,
I have installed the software, and copied the TNSNames.ora file to the
network\admin directory. I seem to be getting network activity now, and
I am getting a response from the Oracle server. The message is an error
of some kind (ORA-12154), which when I look it up states that the issue
is with the TNSNAMES.ora file. I run the network configurator tool for
the product, and am able to test the connection - and it works (once it
has the right username and password).
I have tried to configure the Linked Server several different ways, but
basically the script we discussed earlier is what is being used to
create the server. I have noticed in the servername for the linked
server that it was not exactly the same as the name in the TNSNAMES.ora
file. The ora file has a name like this:
oracledatabase.sub-domain-domain. When I change the script to match
this the response is instant with the same error message above. If the
script drops the domain extensions from the name query takes a few
seconds before returning a 'not going to happen' response.
You have helped me so much with this, I cannot thank you enough for
getting me this far. I hope I am not impinging on your time and
patience too much with this. I am comfortable once I am in SQL Server,
but I am really not an Oracle guy (I have not had enough experience
with it to date).
Thankyou once again
The Frog
|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164294579.219182.26860@.h54g2000cwb.googlegro ups.com...
> Hi David,
> I have installed the software, and copied the TNSNames.ora file to the
> network\admin directory. I seem to be getting network activity now, and
> I am getting a response from the Oracle server. The message is an error
> of some kind (ORA-12154), which when I look it up states that the issue
> is with the TNSNAMES.ora file. I run the network configurator tool for
> the product, and am able to test the connection - and it works (once it
> has the right username and password).
> I have tried to configure the Linked Server several different ways, but
> basically the script we discussed earlier is what is being used to
> create the server. I have noticed in the servername for the linked
> server that it was not exactly the same as the name in the TNSNAMES.ora
> file. The ora file has a name like this:
> oracledatabase.sub-domain-domain. When I change the script to match
> this the response is instant with the same error message above. If the
> script drops the domain extensions from the name query takes a few
> seconds before returning a 'not going to happen' response.
> You have helped me so much with this, I cannot thank you enough for
> getting me this far. I hope I am not impinging on your time and
> patience too much with this. I am comfortable once I am in SQL Server,
> but I am really not an Oracle guy (I have not had enough experience
> with it to date).
>
Now, with the new driver, you can take advantage of one of the best new
features of the Oracle client: bypassing the tnsnames.ora file. With the
10g client you simply don't need a tnsnames.ora file. <<sound of much
rejoicing>>
Instead you specify //hostname/servicename where hostname is the IP address
(or DNS name) of the oracle server, and servicename is the instance.
David
|||David you are an absolute LEGEND!
It works like a charm. The script I used in the end is as follows:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.useself= FALSE,
@.rmtuser='remoteusername',
@.rmtpassword='remoteuserpassword'
GO
This was done with the Visual Studio Developer tools installed from the
link you provided above, to make sure all the drivers etc... were up to
date.
Its quick, clean, and scriptable all the way through. I cant thank you
enough. If you are ever in Germany, the beer and schnitzels are on me
Thankyou, thankyou, thankyou
Indebted to your services
The Frog
Linking Oracle Servers in SQL Server 2005 Express
Hi Everyone,
I am trying to link an Oracle server to my instance of SQL Server 2005
Express. I have used the following code to add the linked server:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = 'ActualNameOfOracleServer'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.rmtuser='USERNAME',
@.rmtpassword='PASSWORD'
GO
This all runs very nicely and completes without errors. I then try to
query the linked server I have just added, and the whole thing just
hangs there "executing" the query (Management Studio Express). I have
also tried this with the MSDAORA (Microsoft) provider, and that returns
a "cannot initialise object" error.
I can access the Oracle server with the Oracle tools that I have
installed, and I can access the tables in the Oracle server by linking
them in MS Access (using a User DSN). It just wont work in SQL Server
it seems.
Can anybody help with this one? I am truly stuck and desperately want
to avoid having to use Access for this (we still use Access 97 would
you believe as a corporate standard!).
Cheers, and thanks in Advance
The Frog :)"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164125803.967272.173060@.f16g2000cwb.googlegroups.com...
> Hi Everyone,
> I am trying to link an Oracle server to my instance of SQL Server 2005
> Express. I have used the following code to add the linked server:
> USE MASTER
> GO
> EXEC sp_addlinkedserver
> @.server = 'NameForLinkedServer',
> @.srvproduct = 'Oracle',
> @.provider = 'OraOLEDB.Oracle',
> @.datasrc = 'ActualNameOfOracleServer'
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
> GO
> Exec sp_addlinkedsrvlogin
> @.rmtsrvname='NameForLinkedServer',
> @.rmtuser='USERNAME',
> @.rmtpassword='PASSWORD'
> GO
> This all runs very nicely and completes without errors. I then try to
> query the linked server I have just added, and the whole thing just
> hangs there "executing" the query (Management Studio Express). I have
> also tried this with the MSDAORA (Microsoft) provider, and that returns
> a "cannot initialise object" error.
> I can access the Oracle server with the Oracle tools that I have
> installed, and I can access the tables in the Oracle server by linking
> them in MS Access (using a User DSN). It just wont work in SQL Server
> it seems.
> Can anybody help with this one? I am truly stuck and desperately want
> to avoid having to use Access for this (we still use Access 97 would
> you believe as a corporate standard!).
>
Remember to set AllowInProcess for OraOLEDB.Oracle.
EG
USE MASTER
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
sp_dropserver @.server=N'NameForLinkedServer', @.droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//HOSTNAME/SERVICE'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = N'NameForLinkedServer',
@.useself = N'False',
@.rmtuser = N'USERNAME',
@.rmtpassword = N'PASSWORD'
GO
GO
SELECT * FROM OPENQUERY(NameForLinkedServer,'SELECT 1 D FROM DUAL')
David|||Thanks for that David,
I have given it a try, and the SQL instructions have run and made the
changes. Unfortunately there is still no action when trying to query
the linked server. I have had a quick look at the network performance
on the pc, and there is not even any network activity.
Do you know if there is a way that I could perhaps query the User DSN
from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
and two-step the solution since it doesnt seem to want to play nicely
with me...
Cheers
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> Thanks for that David,
> I have given it a try, and the SQL instructions have run and made the
> changes. Unfortunately there is still no action when trying to query
> the linked server. I have had a quick look at the network performance
> on the pc, and there is not even any network activity.
> Do you know if there is a way that I could perhaps query the User DSN
> from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
> and two-step the solution since it doesnt seem to want to play nicely
> with me...
>
A couple of things to check out:
Make sure
-you have rebooted since installing the Oracle Client.
-the SQL Service account have permissions for the Oracle client folders.
David|||Thanks again David,
I have checked the permissions and also rebooted the machine a few
times just to be sure. They all seem in order. I did notice something
when playing with the Oracle Client software however that may be
important: The server is an Oracle 9i server, but the client software
is 8.17.
I have downloaded an ODBC driver from Oracle, but have not been able to
try it because I need something called the Oracle Universal
Installer(?). Apparently this installer comes with the client
software, but I am unable to locate it (only the SQL Plus, and another
called Oracle ODBC Test).
Do you think it may be that my driver is simply too old? I would have
thought that MS Access would have also complained (but since it is
Access 97 maybe the 8.17 driver software is actually more sophistocated
than it is?). I was thinking that there may be an incompatability
between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
guess.
Thankyou so much for trying to help with this, it is greatly
appreciated.
Cheers
The Frog
David Browne wrote:
> "The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
> news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> > Thanks for that David,
> >
<SNIP>|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164201695.690139.214840@.b28g2000cwb.googlegroups.com...
> Thanks again David,
> I have checked the permissions and also rebooted the machine a few
> times just to be sure. They all seem in order. I did notice something
> when playing with the Oracle Client software however that may be
> important: The server is an Oracle 9i server, but the client software
> is 8.17.
> I have downloaded an ODBC driver from Oracle, but have not been able to
> try it because I need something called the Oracle Universal
> Installer(?). Apparently this installer comes with the client
> software, but I am unable to locate it (only the SQL Plus, and another
> called Oracle ODBC Test).
> Do you think it may be that my driver is simply too old? I would have
> thought that MS Access would have also complained (but since it is
> Access 97 maybe the 8.17 driver software is actually more sophistocated
> than it is?). I was thinking that there may be an incompatability
> between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
> guess.
> Thankyou so much for trying to help with this, it is greatly
> appreciated.
>
That is a very old driver. I would the latest OleDb provider, available at
http://www.oracle.com/technology/tech/windows/ole_db/index.html.
David|||Thankyou once again David,
I am downloading the product from the link now. I will install and
trial it today, and post here again with the results a little later on.
Just thinking about this issue logically, it probably is the driver
thats the cause of the problem. Its a large file, and the company
internet connection is a little slow, so I will have to come back on
this one.
Thanks again for all the help.
Cheers
The Frog|||Hi David,
I have installed the software, and copied the TNSNames.ora file to the
network\admin directory. I seem to be getting network activity now, and
I am getting a response from the Oracle server. The message is an error
of some kind (ORA-12154), which when I look it up states that the issue
is with the TNSNAMES.ora file. I run the network configurator tool for
the product, and am able to test the connection - and it works (once it
has the right username and password).
I have tried to configure the Linked Server several different ways, but
basically the script we discussed earlier is what is being used to
create the server. I have noticed in the servername for the linked
server that it was not exactly the same as the name in the TNSNAMES.ora
file. The ora file has a name like this:
oracledatabase.sub-domain-domain. When I change the script to match
this the response is instant with the same error message above. If the
script drops the domain extensions from the name query takes a few
seconds before returning a 'not going to happen' response.
You have helped me so much with this, I cannot thank you enough for
getting me this far. I hope I am not impinging on your time and
patience too much with this. I am comfortable once I am in SQL Server,
but I am really not an Oracle guy (I have not had enough experience
with it to date).
Thankyou once again
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164294579.219182.26860@.h54g2000cwb.googlegroups.com...
> Hi David,
> I have installed the software, and copied the TNSNames.ora file to the
> network\admin directory. I seem to be getting network activity now, and
> I am getting a response from the Oracle server. The message is an error
> of some kind (ORA-12154), which when I look it up states that the issue
> is with the TNSNAMES.ora file. I run the network configurator tool for
> the product, and am able to test the connection - and it works (once it
> has the right username and password).
> I have tried to configure the Linked Server several different ways, but
> basically the script we discussed earlier is what is being used to
> create the server. I have noticed in the servername for the linked
> server that it was not exactly the same as the name in the TNSNAMES.ora
> file. The ora file has a name like this:
> oracledatabase.sub-domain-domain. When I change the script to match
> this the response is instant with the same error message above. If the
> script drops the domain extensions from the name query takes a few
> seconds before returning a 'not going to happen' response.
> You have helped me so much with this, I cannot thank you enough for
> getting me this far. I hope I am not impinging on your time and
> patience too much with this. I am comfortable once I am in SQL Server,
> but I am really not an Oracle guy (I have not had enough experience
> with it to date).
>
Now, with the new driver, you can take advantage of one of the best new
features of the Oracle client: bypassing the tnsnames.ora file. With the
10g client you simply don't need a tnsnames.ora file. <<sound of much
rejoicing>>
Instead you specify //hostname/servicename where hostname is the IP address
(or DNS name) of the oracle server, and servicename is the instance.
David|||David you are an absolute LEGEND!
It works like a charm. The script I used in the end is as follows:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.useself= FALSE,
@.rmtuser='remoteusername',
@.rmtpassword='remoteuserpassword'
GO
This was done with the Visual Studio Developer tools installed from the
link you provided above, to make sure all the drivers etc... were up to
date.
Its quick, clean, and scriptable all the way through. I cant thank you
enough. If you are ever in Germany, the beer and schnitzels are on me
:)
Thankyou, thankyou, thankyou
Indebted to your services
The Frog
I am trying to link an Oracle server to my instance of SQL Server 2005
Express. I have used the following code to add the linked server:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = 'ActualNameOfOracleServer'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.rmtuser='USERNAME',
@.rmtpassword='PASSWORD'
GO
This all runs very nicely and completes without errors. I then try to
query the linked server I have just added, and the whole thing just
hangs there "executing" the query (Management Studio Express). I have
also tried this with the MSDAORA (Microsoft) provider, and that returns
a "cannot initialise object" error.
I can access the Oracle server with the Oracle tools that I have
installed, and I can access the tables in the Oracle server by linking
them in MS Access (using a User DSN). It just wont work in SQL Server
it seems.
Can anybody help with this one? I am truly stuck and desperately want
to avoid having to use Access for this (we still use Access 97 would
you believe as a corporate standard!).
Cheers, and thanks in Advance
The Frog :)"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164125803.967272.173060@.f16g2000cwb.googlegroups.com...
> Hi Everyone,
> I am trying to link an Oracle server to my instance of SQL Server 2005
> Express. I have used the following code to add the linked server:
> USE MASTER
> GO
> EXEC sp_addlinkedserver
> @.server = 'NameForLinkedServer',
> @.srvproduct = 'Oracle',
> @.provider = 'OraOLEDB.Oracle',
> @.datasrc = 'ActualNameOfOracleServer'
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
> GO
> Exec sp_addlinkedsrvlogin
> @.rmtsrvname='NameForLinkedServer',
> @.rmtuser='USERNAME',
> @.rmtpassword='PASSWORD'
> GO
> This all runs very nicely and completes without errors. I then try to
> query the linked server I have just added, and the whole thing just
> hangs there "executing" the query (Management Studio Express). I have
> also tried this with the MSDAORA (Microsoft) provider, and that returns
> a "cannot initialise object" error.
> I can access the Oracle server with the Oracle tools that I have
> installed, and I can access the tables in the Oracle server by linking
> them in MS Access (using a User DSN). It just wont work in SQL Server
> it seems.
> Can anybody help with this one? I am truly stuck and desperately want
> to avoid having to use Access for this (we still use Access 97 would
> you believe as a corporate standard!).
>
Remember to set AllowInProcess for OraOLEDB.Oracle.
EG
USE MASTER
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
sp_dropserver @.server=N'NameForLinkedServer', @.droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//HOSTNAME/SERVICE'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = N'NameForLinkedServer',
@.useself = N'False',
@.rmtuser = N'USERNAME',
@.rmtpassword = N'PASSWORD'
GO
GO
SELECT * FROM OPENQUERY(NameForLinkedServer,'SELECT 1 D FROM DUAL')
David|||Thanks for that David,
I have given it a try, and the SQL instructions have run and made the
changes. Unfortunately there is still no action when trying to query
the linked server. I have had a quick look at the network performance
on the pc, and there is not even any network activity.
Do you know if there is a way that I could perhaps query the User DSN
from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
and two-step the solution since it doesnt seem to want to play nicely
with me...
Cheers
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> Thanks for that David,
> I have given it a try, and the SQL instructions have run and made the
> changes. Unfortunately there is still no action when trying to query
> the linked server. I have had a quick look at the network performance
> on the pc, and there is not even any network activity.
> Do you know if there is a way that I could perhaps query the User DSN
> from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
> and two-step the solution since it doesnt seem to want to play nicely
> with me...
>
A couple of things to check out:
Make sure
-you have rebooted since installing the Oracle Client.
-the SQL Service account have permissions for the Oracle client folders.
David|||Thanks again David,
I have checked the permissions and also rebooted the machine a few
times just to be sure. They all seem in order. I did notice something
when playing with the Oracle Client software however that may be
important: The server is an Oracle 9i server, but the client software
is 8.17.
I have downloaded an ODBC driver from Oracle, but have not been able to
try it because I need something called the Oracle Universal
Installer(?). Apparently this installer comes with the client
software, but I am unable to locate it (only the SQL Plus, and another
called Oracle ODBC Test).
Do you think it may be that my driver is simply too old? I would have
thought that MS Access would have also complained (but since it is
Access 97 maybe the 8.17 driver software is actually more sophistocated
than it is?). I was thinking that there may be an incompatability
between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
guess.
Thankyou so much for trying to help with this, it is greatly
appreciated.
Cheers
The Frog
David Browne wrote:
> "The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
> news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> > Thanks for that David,
> >
<SNIP>|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164201695.690139.214840@.b28g2000cwb.googlegroups.com...
> Thanks again David,
> I have checked the permissions and also rebooted the machine a few
> times just to be sure. They all seem in order. I did notice something
> when playing with the Oracle Client software however that may be
> important: The server is an Oracle 9i server, but the client software
> is 8.17.
> I have downloaded an ODBC driver from Oracle, but have not been able to
> try it because I need something called the Oracle Universal
> Installer(?). Apparently this installer comes with the client
> software, but I am unable to locate it (only the SQL Plus, and another
> called Oracle ODBC Test).
> Do you think it may be that my driver is simply too old? I would have
> thought that MS Access would have also complained (but since it is
> Access 97 maybe the 8.17 driver software is actually more sophistocated
> than it is?). I was thinking that there may be an incompatability
> between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
> guess.
> Thankyou so much for trying to help with this, it is greatly
> appreciated.
>
That is a very old driver. I would the latest OleDb provider, available at
http://www.oracle.com/technology/tech/windows/ole_db/index.html.
David|||Thankyou once again David,
I am downloading the product from the link now. I will install and
trial it today, and post here again with the results a little later on.
Just thinking about this issue logically, it probably is the driver
thats the cause of the problem. Its a large file, and the company
internet connection is a little slow, so I will have to come back on
this one.
Thanks again for all the help.
Cheers
The Frog|||Hi David,
I have installed the software, and copied the TNSNames.ora file to the
network\admin directory. I seem to be getting network activity now, and
I am getting a response from the Oracle server. The message is an error
of some kind (ORA-12154), which when I look it up states that the issue
is with the TNSNAMES.ora file. I run the network configurator tool for
the product, and am able to test the connection - and it works (once it
has the right username and password).
I have tried to configure the Linked Server several different ways, but
basically the script we discussed earlier is what is being used to
create the server. I have noticed in the servername for the linked
server that it was not exactly the same as the name in the TNSNAMES.ora
file. The ora file has a name like this:
oracledatabase.sub-domain-domain. When I change the script to match
this the response is instant with the same error message above. If the
script drops the domain extensions from the name query takes a few
seconds before returning a 'not going to happen' response.
You have helped me so much with this, I cannot thank you enough for
getting me this far. I hope I am not impinging on your time and
patience too much with this. I am comfortable once I am in SQL Server,
but I am really not an Oracle guy (I have not had enough experience
with it to date).
Thankyou once again
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164294579.219182.26860@.h54g2000cwb.googlegroups.com...
> Hi David,
> I have installed the software, and copied the TNSNames.ora file to the
> network\admin directory. I seem to be getting network activity now, and
> I am getting a response from the Oracle server. The message is an error
> of some kind (ORA-12154), which when I look it up states that the issue
> is with the TNSNAMES.ora file. I run the network configurator tool for
> the product, and am able to test the connection - and it works (once it
> has the right username and password).
> I have tried to configure the Linked Server several different ways, but
> basically the script we discussed earlier is what is being used to
> create the server. I have noticed in the servername for the linked
> server that it was not exactly the same as the name in the TNSNAMES.ora
> file. The ora file has a name like this:
> oracledatabase.sub-domain-domain. When I change the script to match
> this the response is instant with the same error message above. If the
> script drops the domain extensions from the name query takes a few
> seconds before returning a 'not going to happen' response.
> You have helped me so much with this, I cannot thank you enough for
> getting me this far. I hope I am not impinging on your time and
> patience too much with this. I am comfortable once I am in SQL Server,
> but I am really not an Oracle guy (I have not had enough experience
> with it to date).
>
Now, with the new driver, you can take advantage of one of the best new
features of the Oracle client: bypassing the tnsnames.ora file. With the
10g client you simply don't need a tnsnames.ora file. <<sound of much
rejoicing>>
Instead you specify //hostname/servicename where hostname is the IP address
(or DNS name) of the oracle server, and servicename is the instance.
David|||David you are an absolute LEGEND!
It works like a charm. The script I used in the end is as follows:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.useself= FALSE,
@.rmtuser='remoteusername',
@.rmtpassword='remoteuserpassword'
GO
This was done with the Visual Studio Developer tools installed from the
link you provided above, to make sure all the drivers etc... were up to
date.
Its quick, clean, and scriptable all the way through. I cant thank you
enough. If you are ever in Germany, the beer and schnitzels are on me
:)
Thankyou, thankyou, thankyou
Indebted to your services
The Frog
Linking Oracle Servers in SQL Server 2005 Express
Hi Everyone,
I am trying to link an Oracle server to my instance of SQL Server 2005
Express. I have used the following code to add the linked server:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = 'ActualNameOfOracleServer'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.rmtuser='USERNAME',
@.rmtpassword='PASSWORD'
GO
This all runs very nicely and completes without errors. I then try to
query the linked server I have just added, and the whole thing just
hangs there "executing" the query (Management Studio Express). I have
also tried this with the MSDAORA (Microsoft) provider, and that returns
a "cannot initialise object" error.
I can access the Oracle server with the Oracle tools that I have
installed, and I can access the tables in the Oracle server by linking
them in MS Access (using a User DSN). It just wont work in SQL Server
it seems.
Can anybody help with this one? I am truly stuck and desperately want
to avoid having to use Access for this (we still use Access 97 would
you believe as a corporate standard!).
Cheers, and thanks in Advance
The Frog"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164125803.967272.173060@.f16g2000cwb.googlegroups.com...
> Hi Everyone,
> I am trying to link an Oracle server to my instance of SQL Server 2005
> Express. I have used the following code to add the linked server:
> USE MASTER
> GO
> EXEC sp_addlinkedserver
> @.server = 'NameForLinkedServer',
> @.srvproduct = 'Oracle',
> @.provider = 'OraOLEDB.Oracle',
> @.datasrc = 'ActualNameOfOracleServer'
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
> GO
> Exec sp_addlinkedsrvlogin
> @.rmtsrvname='NameForLinkedServer',
> @.rmtuser='USERNAME',
> @.rmtpassword='PASSWORD'
> GO
> This all runs very nicely and completes without errors. I then try to
> query the linked server I have just added, and the whole thing just
> hangs there "executing" the query (Management Studio Express). I have
> also tried this with the MSDAORA (Microsoft) provider, and that returns
> a "cannot initialise object" error.
> I can access the Oracle server with the Oracle tools that I have
> installed, and I can access the tables in the Oracle server by linking
> them in MS Access (using a User DSN). It just wont work in SQL Server
> it seems.
> Can anybody help with this one? I am truly stuck and desperately want
> to avoid having to use Access for this (we still use Access 97 would
> you believe as a corporate standard!).
>
Remember to set AllowInProcess for OraOLEDB.Oracle.
EG
USE MASTER
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
sp_dropserver @.server=N'NameForLinkedServer', @.droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//HOSTNAME/SERVICE'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = N'NameForLinkedServer',
@.useself = N'False',
@.rmtuser = N'USERNAME',
@.rmtpassword = N'PASSWORD'
GO
GO
SELECT * FROM OPENQUERY(NameForLinkedServer,'SELECT 1 D FROM DUAL')
David|||Thanks for that David,
I have given it a try, and the SQL instructions have run and made the
changes. Unfortunately there is still no action when trying to query
the linked server. I have had a quick look at the network performance
on the pc, and there is not even any network activity.
Do you know if there is a way that I could perhaps query the User DSN
from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
and two-step the solution since it doesnt seem to want to play nicely
with me...
Cheers
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> Thanks for that David,
> I have given it a try, and the SQL instructions have run and made the
> changes. Unfortunately there is still no action when trying to query
> the linked server. I have had a quick look at the network performance
> on the pc, and there is not even any network activity.
> Do you know if there is a way that I could perhaps query the User DSN
> from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
> and two-step the solution since it doesnt seem to want to play nicely
> with me...
>
A couple of things to check out:
Make sure
-you have rebooted since installing the Oracle Client.
-the SQL Service account have permissions for the Oracle client folders.
David|||Thanks again David,
I have checked the permissions and also rebooted the machine a few
times just to be sure. They all seem in order. I did notice something
when playing with the Oracle Client software however that may be
important: The server is an Oracle 9i server, but the client software
is 8.17.
I have downloaded an ODBC driver from Oracle, but have not been able to
try it because I need something called the Oracle Universal
Installer(?). Apparently this installer comes with the client
software, but I am unable to locate it (only the SQL Plus, and another
called Oracle ODBC Test).
Do you think it may be that my driver is simply too old? I would have
thought that MS Access would have also complained (but since it is
Access 97 maybe the 8.17 driver software is actually more sophistocated
than it is?). I was thinking that there may be an incompatability
between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
guess.
Thankyou so much for trying to help with this, it is greatly
appreciated.
Cheers
The Frog
David Browne wrote:[vbcol=seagreen]
> "The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
> news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
<SNIP>|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164201695.690139.214840@.b28g2000cwb.googlegroups.com...
> Thanks again David,
> I have checked the permissions and also rebooted the machine a few
> times just to be sure. They all seem in order. I did notice something
> when playing with the Oracle Client software however that may be
> important: The server is an Oracle 9i server, but the client software
> is 8.17.
> I have downloaded an ODBC driver from Oracle, but have not been able to
> try it because I need something called the Oracle Universal
> Installer(?). Apparently this installer comes with the client
> software, but I am unable to locate it (only the SQL Plus, and another
> called Oracle ODBC Test).
> Do you think it may be that my driver is simply too old? I would have
> thought that MS Access would have also complained (but since it is
> Access 97 maybe the 8.17 driver software is actually more sophistocated
> than it is?). I was thinking that there may be an incompatability
> between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
> guess.
> Thankyou so much for trying to help with this, it is greatly
> appreciated.
>
That is a very old driver. I would the latest OleDb provider, available at
http://www.oracle.com/technology/te..._db/index.html.
David|||Thankyou once again David,
I am downloading the product from the link now. I will install and
trial it today, and post here again with the results a little later on.
Just thinking about this issue logically, it probably is the driver
thats the cause of the problem. Its a large file, and the company
internet connection is a little slow, so I will have to come back on
this one.
Thanks again for all the help.
Cheers
The Frog|||Hi David,
I have installed the software, and copied the TNSNames.ora file to the
network\admin directory. I seem to be getting network activity now, and
I am getting a response from the Oracle server. The message is an error
of some kind (ORA-12154), which when I look it up states that the issue
is with the TNSNAMES.ora file. I run the network configurator tool for
the product, and am able to test the connection - and it works (once it
has the right username and password).
I have tried to configure the Linked Server several different ways, but
basically the script we discussed earlier is what is being used to
create the server. I have noticed in the servername for the linked
server that it was not exactly the same as the name in the TNSNAMES.ora
file. The ora file has a name like this:
oracledatabase.sub-domain-domain. When I change the script to match
this the response is instant with the same error message above. If the
script drops the domain extensions from the name query takes a few
seconds before returning a 'not going to happen' response.
You have helped me so much with this, I cannot thank you enough for
getting me this far. I hope I am not impinging on your time and
patience too much with this. I am comfortable once I am in SQL Server,
but I am really not an Oracle guy (I have not had enough experience
with it to date).
Thankyou once again
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164294579.219182.26860@.h54g2000cwb.googlegroups.com...
> Hi David,
> I have installed the software, and copied the TNSNames.ora file to the
> network\admin directory. I seem to be getting network activity now, and
> I am getting a response from the Oracle server. The message is an error
> of some kind (ORA-12154), which when I look it up states that the issue
> is with the TNSNAMES.ora file. I run the network configurator tool for
> the product, and am able to test the connection - and it works (once it
> has the right username and password).
> I have tried to configure the Linked Server several different ways, but
> basically the script we discussed earlier is what is being used to
> create the server. I have noticed in the servername for the linked
> server that it was not exactly the same as the name in the TNSNAMES.ora
> file. The ora file has a name like this:
> oracledatabase.sub-domain-domain. When I change the script to match
> this the response is instant with the same error message above. If the
> script drops the domain extensions from the name query takes a few
> seconds before returning a 'not going to happen' response.
> You have helped me so much with this, I cannot thank you enough for
> getting me this far. I hope I am not impinging on your time and
> patience too much with this. I am comfortable once I am in SQL Server,
> but I am really not an Oracle guy (I have not had enough experience
> with it to date).
>
Now, with the new driver, you can take advantage of one of the best new
features of the Oracle client: bypassing the tnsnames.ora file. With the
10g client you simply don't need a tnsnames.ora file. <<sound of much
rejoicing>>
Instead you specify //hostname/servicename where hostname is the IP address
(or DNS name) of the oracle server, and servicename is the instance.
David|||David you are an absolute LEGEND!
It works like a charm. The script I used in the end is as follows:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.useself= FALSE,
@.rmtuser='remoteusername',
@.rmtpassword='remoteuserpassword'
GO
This was done with the Visual Studio Developer tools installed from the
link you provided above, to make sure all the drivers etc... were up to
date.
Its quick, clean, and scriptable all the way through. I cant thank you
enough. If you are ever in Germany, the beer and schnitzels are on me
Thankyou, thankyou, thankyou
Indebted to your services
The Frogsql
I am trying to link an Oracle server to my instance of SQL Server 2005
Express. I have used the following code to add the linked server:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = 'ActualNameOfOracleServer'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.rmtuser='USERNAME',
@.rmtpassword='PASSWORD'
GO
This all runs very nicely and completes without errors. I then try to
query the linked server I have just added, and the whole thing just
hangs there "executing" the query (Management Studio Express). I have
also tried this with the MSDAORA (Microsoft) provider, and that returns
a "cannot initialise object" error.
I can access the Oracle server with the Oracle tools that I have
installed, and I can access the tables in the Oracle server by linking
them in MS Access (using a User DSN). It just wont work in SQL Server
it seems.
Can anybody help with this one? I am truly stuck and desperately want
to avoid having to use Access for this (we still use Access 97 would
you believe as a corporate standard!).
Cheers, and thanks in Advance
The Frog
"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in messagenews:1164125803.967272.173060@.f16g2000cwb.googlegroups.com...
> Hi Everyone,
> I am trying to link an Oracle server to my instance of SQL Server 2005
> Express. I have used the following code to add the linked server:
> USE MASTER
> GO
> EXEC sp_addlinkedserver
> @.server = 'NameForLinkedServer',
> @.srvproduct = 'Oracle',
> @.provider = 'OraOLEDB.Oracle',
> @.datasrc = 'ActualNameOfOracleServer'
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
> GO
> Exec sp_addlinkedsrvlogin
> @.rmtsrvname='NameForLinkedServer',
> @.rmtuser='USERNAME',
> @.rmtpassword='PASSWORD'
> GO
> This all runs very nicely and completes without errors. I then try to
> query the linked server I have just added, and the whole thing just
> hangs there "executing" the query (Management Studio Express). I have
> also tried this with the MSDAORA (Microsoft) provider, and that returns
> a "cannot initialise object" error.
> I can access the Oracle server with the Oracle tools that I have
> installed, and I can access the tables in the Oracle server by linking
> them in MS Access (using a User DSN). It just wont work in SQL Server
> it seems.
> Can anybody help with this one? I am truly stuck and desperately want
> to avoid having to use Access for this (we still use Access 97 would
> you believe as a corporate standard!).
>
Remember to set AllowInProcess for OraOLEDB.Oracle.
EG
USE MASTER
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
sp_dropserver @.server=N'NameForLinkedServer', @.droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//HOSTNAME/SERVICE'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = N'NameForLinkedServer',
@.useself = N'False',
@.rmtuser = N'USERNAME',
@.rmtpassword = N'PASSWORD'
GO
GO
SELECT * FROM OPENQUERY(NameForLinkedServer,'SELECT 1 D FROM DUAL')
David|||Thanks for that David,
I have given it a try, and the SQL instructions have run and made the
changes. Unfortunately there is still no action when trying to query
the linked server. I have had a quick look at the network performance
on the pc, and there is not even any network activity.
Do you know if there is a way that I could perhaps query the User DSN
from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
and two-step the solution since it doesnt seem to want to play nicely
with me...
Cheers
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> Thanks for that David,
> I have given it a try, and the SQL instructions have run and made the
> changes. Unfortunately there is still no action when trying to query
> the linked server. I have had a quick look at the network performance
> on the pc, and there is not even any network activity.
> Do you know if there is a way that I could perhaps query the User DSN
> from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
> and two-step the solution since it doesnt seem to want to play nicely
> with me...
>
A couple of things to check out:
Make sure
-you have rebooted since installing the Oracle Client.
-the SQL Service account have permissions for the Oracle client folders.
David|||Thanks again David,
I have checked the permissions and also rebooted the machine a few
times just to be sure. They all seem in order. I did notice something
when playing with the Oracle Client software however that may be
important: The server is an Oracle 9i server, but the client software
is 8.17.
I have downloaded an ODBC driver from Oracle, but have not been able to
try it because I need something called the Oracle Universal
Installer(?). Apparently this installer comes with the client
software, but I am unable to locate it (only the SQL Plus, and another
called Oracle ODBC Test).
Do you think it may be that my driver is simply too old? I would have
thought that MS Access would have also complained (but since it is
Access 97 maybe the 8.17 driver software is actually more sophistocated
than it is?). I was thinking that there may be an incompatability
between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
guess.
Thankyou so much for trying to help with this, it is greatly
appreciated.
Cheers
The Frog
David Browne wrote:[vbcol=seagreen]
> "The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
> news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
<SNIP>|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164201695.690139.214840@.b28g2000cwb.googlegroups.com...
> Thanks again David,
> I have checked the permissions and also rebooted the machine a few
> times just to be sure. They all seem in order. I did notice something
> when playing with the Oracle Client software however that may be
> important: The server is an Oracle 9i server, but the client software
> is 8.17.
> I have downloaded an ODBC driver from Oracle, but have not been able to
> try it because I need something called the Oracle Universal
> Installer(?). Apparently this installer comes with the client
> software, but I am unable to locate it (only the SQL Plus, and another
> called Oracle ODBC Test).
> Do you think it may be that my driver is simply too old? I would have
> thought that MS Access would have also complained (but since it is
> Access 97 maybe the 8.17 driver software is actually more sophistocated
> than it is?). I was thinking that there may be an incompatability
> between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
> guess.
> Thankyou so much for trying to help with this, it is greatly
> appreciated.
>
That is a very old driver. I would the latest OleDb provider, available at
http://www.oracle.com/technology/te..._db/index.html.
David|||Thankyou once again David,
I am downloading the product from the link now. I will install and
trial it today, and post here again with the results a little later on.
Just thinking about this issue logically, it probably is the driver
thats the cause of the problem. Its a large file, and the company
internet connection is a little slow, so I will have to come back on
this one.
Thanks again for all the help.
Cheers
The Frog|||Hi David,
I have installed the software, and copied the TNSNames.ora file to the
network\admin directory. I seem to be getting network activity now, and
I am getting a response from the Oracle server. The message is an error
of some kind (ORA-12154), which when I look it up states that the issue
is with the TNSNAMES.ora file. I run the network configurator tool for
the product, and am able to test the connection - and it works (once it
has the right username and password).
I have tried to configure the Linked Server several different ways, but
basically the script we discussed earlier is what is being used to
create the server. I have noticed in the servername for the linked
server that it was not exactly the same as the name in the TNSNAMES.ora
file. The ora file has a name like this:
oracledatabase.sub-domain-domain. When I change the script to match
this the response is instant with the same error message above. If the
script drops the domain extensions from the name query takes a few
seconds before returning a 'not going to happen' response.
You have helped me so much with this, I cannot thank you enough for
getting me this far. I hope I am not impinging on your time and
patience too much with this. I am comfortable once I am in SQL Server,
but I am really not an Oracle guy (I have not had enough experience
with it to date).
Thankyou once again
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164294579.219182.26860@.h54g2000cwb.googlegroups.com...
> Hi David,
> I have installed the software, and copied the TNSNames.ora file to the
> network\admin directory. I seem to be getting network activity now, and
> I am getting a response from the Oracle server. The message is an error
> of some kind (ORA-12154), which when I look it up states that the issue
> is with the TNSNAMES.ora file. I run the network configurator tool for
> the product, and am able to test the connection - and it works (once it
> has the right username and password).
> I have tried to configure the Linked Server several different ways, but
> basically the script we discussed earlier is what is being used to
> create the server. I have noticed in the servername for the linked
> server that it was not exactly the same as the name in the TNSNAMES.ora
> file. The ora file has a name like this:
> oracledatabase.sub-domain-domain. When I change the script to match
> this the response is instant with the same error message above. If the
> script drops the domain extensions from the name query takes a few
> seconds before returning a 'not going to happen' response.
> You have helped me so much with this, I cannot thank you enough for
> getting me this far. I hope I am not impinging on your time and
> patience too much with this. I am comfortable once I am in SQL Server,
> but I am really not an Oracle guy (I have not had enough experience
> with it to date).
>
Now, with the new driver, you can take advantage of one of the best new
features of the Oracle client: bypassing the tnsnames.ora file. With the
10g client you simply don't need a tnsnames.ora file. <<sound of much
rejoicing>>
Instead you specify //hostname/servicename where hostname is the IP address
(or DNS name) of the oracle server, and servicename is the instance.
David|||David you are an absolute LEGEND!
It works like a charm. The script I used in the end is as follows:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.useself= FALSE,
@.rmtuser='remoteusername',
@.rmtpassword='remoteuserpassword'
GO
This was done with the Visual Studio Developer tools installed from the
link you provided above, to make sure all the drivers etc... were up to
date.
Its quick, clean, and scriptable all the way through. I cant thank you
enough. If you are ever in Germany, the beer and schnitzels are on me
Thankyou, thankyou, thankyou
Indebted to your services
The Frogsql
Linking Exchange Server 2003 to SQL Server 2000
Hi
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
Message posted via http://www.droptable.comLook at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via droptable.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.droptable.com
>
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
Message posted via http://www.droptable.comLook at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via droptable.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.droptable.com
>
Linking Exchange Server 2003 to SQL Server 2000
Hi
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
Message posted via http://www.sqlmonster.com
Look at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via SQLMonster.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.sqlmonster.com
>
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
Message posted via http://www.sqlmonster.com
Look at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via SQLMonster.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.sqlmonster.com
>
Linking Exchange Server 2003 to SQL Server 2000
Hi
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
--
Message posted via http://www.sqlmonster.comLook at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via SQLMonster.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.sqlmonster.com
>
The following commands in Query Analyzer:
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\localhost\public folders'
Go
SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
FROM ".\Public Folders\Company Contacts"')
produce the following results:
Server: Msg 7304, Level 16, State 2, Line 1
Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
IDBCreateSession::CreateSession returned 0x80150804].
I am trying to access contacts in public folders.
Can anyone help with this?
Is this the best way to read/modify these records?
What about using this to check/modify tasks and emails?
When I browse the folder structure in the Exchange System Manager the
following shows:
Folders -> Public Folders -> Public Folders -> Company Contacts
When I go to the properties of the "Company Contacts" folder in Exchange
System Manager I get:
/Public Folders/Company Contacts/
When I look at the same structure in Outlook I see the breakdown as:
Public Folders -> All Public Folders -> Public Folders -> Company
Contacts
Thanks
Roger
--
Message posted via http://www.sqlmonster.comLook at
http://support.microsoft.com/?kbid=887161
"Roger Ivy via SQLMonster.com" wrote:
> Hi
> The following commands in Query Analyzer:
> EXEC sp_addlinkedserver 'exchange',
> 'Exchange OLE DB provider',
> 'exoledb.DataSource.1',
> 'file:\\.\backofficestorage\localhost\public folders'
> Go
> SELECT Convert(nvarchar(30), "urn:schemas:contacts:sn") LastName
> FROM OpenQuery(Exchange, 'SELECT "urn:schemas:contacts:sn"
> FROM ".\Public Folders\Company Contacts"')
> produce the following results:
> Server: Msg 7304, Level 16, State 2, Line 1
> Could not create a new session on OLE DB provider 'exoledb.DataSource.1'.
> OLE DB error trace [OLE/DB Provider 'exoledb.DataSource.1'
> IDBCreateSession::CreateSession returned 0x80150804].
> I am trying to access contacts in public folders.
> Can anyone help with this?
> Is this the best way to read/modify these records?
> What about using this to check/modify tasks and emails?
> When I browse the folder structure in the Exchange System Manager the
> following shows:
> Folders -> Public Folders -> Public Folders -> Company Contacts
> When I go to the properties of the "Company Contacts" folder in Exchange
> System Manager I get:
> /Public Folders/Company Contacts/
> When I look at the same structure in Outlook I see the breakdown as:
> Public Folders -> All Public Folders -> Public Folders -> Company
> Contacts
> Thanks
> Roger
> --
> Message posted via http://www.sqlmonster.com
>
Wednesday, March 28, 2012
LinkeServer Problems...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.That's an initialization failure...a pretty generic OLEDB error. Sounds lik
e you have yet to configure the Security mappings correctly. If you are att
empting to use Windows Authentication Delegation, you have a LOT of work ahe
ad of you to configure Kerberos correctly for delegation.
You have to set the SPN for the SS server, the SS service accounts, and make
sure that the accounts are marked correctly for delegation purposes.
Sincerely,
Anthony Thomas
--
"Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message news:%23gTUNqx0
EHA.3704@.tk2msftngp13.phx.gbl...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initiali
ze
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.|||hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initial
ize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initializ
e
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
>
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.That's an initialization failure...a pretty generic OLEDB error. Sounds lik
e you have yet to configure the Security mappings correctly. If you are att
empting to use Windows Authentication Delegation, you have a LOT of work ahe
ad of you to configure Kerberos correctly for delegation.
You have to set the SPN for the SS server, the SS service accounts, and make
sure that the accounts are marked correctly for delegation purposes.
Sincerely,
Anthony Thomas
--
"Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message news:%23gTUNqx0
EHA.3704@.tk2msftngp13.phx.gbl...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initiali
ze
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.|||hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initial
ize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initializ
e
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
>
LinkeServer Problems...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.
That's an initialization failure...a pretty generic OLEDB error. Sounds like you have yet to configure the Security mappings correctly. If you are attempting to use Windows Authentication Delegation, you have a LOT of work ahead of you to configure Kerberos correctly for delegation.
You have to set the SPN for the SS server, the SS service accounts, and make sure that the accounts are marked correctly for delegation purposes.
Sincerely,
Anthony Thomas
"Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.
|||hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>
|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>
|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.
That's an initialization failure...a pretty generic OLEDB error. Sounds like you have yet to configure the Security mappings correctly. If you are attempting to use Windows Authentication Delegation, you have a LOT of work ahead of you to configure Kerberos correctly for delegation.
You have to set the SPN for the SS server, the SS service accounts, and make sure that the accounts are marked correctly for delegation purposes.
Sincerely,
Anthony Thomas
"Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.
|||hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>
|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>
|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
LinkeServer Problems...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
>>Hey NG,
>>I haved installed a linked server (SQL-Server200), but when I click on the
>>Table or views the following error occours:
>>--
>>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>>returned 0x80004005: ].
>>--
>>in my Firewall/Router I have open the Port 1433.
>>what should I do to make the LinkedServer running well.
>>
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
>>Hey NG,
>>I haved installed a linked server (SQL-Server200), but when I click on the
>>Table or views the following error occours:
>>--
>>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>>returned 0x80004005: ].
>>--
>>in my Firewall/Router I have open the Port 1433.
>>what should I do to make the LinkedServer running well.
>>
Monday, March 26, 2012
Linked View to SQL Server DB
I am using a linked view created in the SQL Server database. However, when
I
run it I get the following error.
ODBC--call failed.
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification (#0)
The help for this message indicates that it may be a network problem.
However, all other tables and views run correctly. This is the only one tha
t
fails. I have relinked, I have removed and recreated the link, and still
this one table view fails.
Any suggestions will be appreciated.Not sure what your other data source is but it looks like a
data type translation issue, not necessarily a network
issue. Try updating the ODBC driver - older versions don't
support newer data types.
If you are referring to something you are doing in MS
Access, you need to make sure to update your Jet drivers as
well.
-Sue
On Wed, 5 Jul 2006 07:23:02 -0700, Outdone
<Outdone@.discussions.microsoft.com> wrote:
>I am using a linked view created in the SQL Server database. However, when
I
>run it I get the following error.
>ODBC--call failed.
>[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
>specification (#0)
>The help for this message indicates that it may be a network problem.
>However, all other tables and views run correctly. This is the only one th
at
>fails. I have relinked, I have removed and recreated the link, and still
>this one table view fails.
>Any suggestions will be appreciated.|||I have a similar problem (and I'm new to SQL Server). I created a view in
SQL Server. I assigned select permissions for a user ID on that view. I
then created an odbc connection so I could link that view in my Access
database. When I try to open the view, I get the 'ODBC--call failed' error.
I can access all the other linked tables within Access. Is there a problem
with the permissions set on the view, or is it an odbc problem?
Thanks,
Melanie
"Sue Hoegemeier" wrote:
> Not sure what your other data source is but it looks like a
> data type translation issue, not necessarily a network
> issue. Try updating the ODBC driver - older versions don't
> support newer data types.
> If you are referring to something you are doing in MS
> Access, you need to make sure to update your Jet drivers as
> well.
> -Sue
> On Wed, 5 Jul 2006 07:23:02 -0700, Outdone
> <Outdone@.discussions.microsoft.com> wrote:
>
>|||Really can't say. ODBC call failed is just a generic error
message. You could try turning on ODBC tracing on the client
to try to get more information on the error. You would want
to make sure to turn it back off after you get the error as
it will really slow things down.
To turn on tracing, go to the ODBC Data Source Administrator
applet and go to the tracing tab. Just click on start
tracing now and note the location for the trace file. After
you hit the error, go back and click on the stop tracing now
button. Then you can go to the trace file and see what other
information you can get out of the trace file.
-Sue
On Wed, 5 Jul 2006 13:39:01 -0700, Melanie O
<MelanieO@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have a similar problem (and I'm new to SQL Server). I created a view in
>SQL Server. I assigned select permissions for a user ID on that view. I
>then created an odbc connection so I could link that view in my Access
>database. When I try to open the view, I get the 'ODBC--call failed' error
.
>I can access all the other linked tables within Access. Is there a problem
>with the permissions set on the view, or is it an odbc problem?
>Thanks,
>Melanie
>"Sue Hoegemeier" wrote:
>
I
run it I get the following error.
ODBC--call failed.
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification (#0)
The help for this message indicates that it may be a network problem.
However, all other tables and views run correctly. This is the only one tha
t
fails. I have relinked, I have removed and recreated the link, and still
this one table view fails.
Any suggestions will be appreciated.Not sure what your other data source is but it looks like a
data type translation issue, not necessarily a network
issue. Try updating the ODBC driver - older versions don't
support newer data types.
If you are referring to something you are doing in MS
Access, you need to make sure to update your Jet drivers as
well.
-Sue
On Wed, 5 Jul 2006 07:23:02 -0700, Outdone
<Outdone@.discussions.microsoft.com> wrote:
>I am using a linked view created in the SQL Server database. However, when
I
>run it I get the following error.
>ODBC--call failed.
>[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
>specification (#0)
>The help for this message indicates that it may be a network problem.
>However, all other tables and views run correctly. This is the only one th
at
>fails. I have relinked, I have removed and recreated the link, and still
>this one table view fails.
>Any suggestions will be appreciated.|||I have a similar problem (and I'm new to SQL Server). I created a view in
SQL Server. I assigned select permissions for a user ID on that view. I
then created an odbc connection so I could link that view in my Access
database. When I try to open the view, I get the 'ODBC--call failed' error.
I can access all the other linked tables within Access. Is there a problem
with the permissions set on the view, or is it an odbc problem?
Thanks,
Melanie
"Sue Hoegemeier" wrote:
> Not sure what your other data source is but it looks like a
> data type translation issue, not necessarily a network
> issue. Try updating the ODBC driver - older versions don't
> support newer data types.
> If you are referring to something you are doing in MS
> Access, you need to make sure to update your Jet drivers as
> well.
> -Sue
> On Wed, 5 Jul 2006 07:23:02 -0700, Outdone
> <Outdone@.discussions.microsoft.com> wrote:
>
>|||Really can't say. ODBC call failed is just a generic error
message. You could try turning on ODBC tracing on the client
to try to get more information on the error. You would want
to make sure to turn it back off after you get the error as
it will really slow things down.
To turn on tracing, go to the ODBC Data Source Administrator
applet and go to the tracing tab. Just click on start
tracing now and note the location for the trace file. After
you hit the error, go back and click on the stop tracing now
button. Then you can go to the trace file and see what other
information you can get out of the trace file.
-Sue
On Wed, 5 Jul 2006 13:39:01 -0700, Melanie O
<MelanieO@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have a similar problem (and I'm new to SQL Server). I created a view in
>SQL Server. I assigned select permissions for a user ID on that view. I
>then created an odbc connection so I could link that view in my Access
>database. When I try to open the view, I get the 'ODBC--call failed' error
.
>I can access all the other linked tables within Access. Is there a problem
>with the permissions set on the view, or is it an odbc problem?
>Thanks,
>Melanie
>"Sue Hoegemeier" wrote:
>
Linked tables
Hi,
I am new to SQL Server 2000 and am looking for examples of how to do the
following:
I have a set of 27 tables, 22 of which are supporting tables and 5 tables
that are transactional in nature. These are currently in the same database.
We are small and have only one server.
Everything is fine until.... My company decided to do out-source work for
other companies that are similar to us. This means that with the
application setup the way it is, I need to create a different set of tables
for each customer. The supporting tables are huge, so I would like to be
able to put them into their own database and make them accessible from every
company's database.
In Access XP I would click "Get external data","Link Tables" and select the
database and tables I wanted to link to the company specific tables. I
can't figure out how to do this in SQL Server.
Is there a way to do this in SQL Server?
Remember I am a big time novice, so keep it simple.
Thanks
LanceSQL Server supports linked servers, which aren't the same thing at all
(see the distributed querying topics in Books Online for more info).
You can create views and stored procedures in one database that
reference tables in another database by using the
databasename.ownername.objectname syntax as long as the databases are
all on the same server, which may be what you are looking for.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 16 Jan 2004 13:39:10 -0800, "Lance Geeck" <lgeeck@.cox.net>
wrote:
>Hi,
>I am new to SQL Server 2000 and am looking for examples of how to do the
>following:
>I have a set of 27 tables, 22 of which are supporting tables and 5 tables
>that are transactional in nature. These are currently in the same database.
>We are small and have only one server.
>Everything is fine until.... My company decided to do out-source work for
>other companies that are similar to us. This means that with the
>application setup the way it is, I need to create a different set of tables
>for each customer. The supporting tables are huge, so I would like to be
>able to put them into their own database and make them accessible from every
>company's database.
>In Access XP I would click "Get external data","Link Tables" and select the
>database and tables I wanted to link to the company specific tables. I
>can't figure out how to do this in SQL Server.
>Is there a way to do this in SQL Server?
>Remember I am a big time novice, so keep it simple.
>Thanks
>Lance
>
I am new to SQL Server 2000 and am looking for examples of how to do the
following:
I have a set of 27 tables, 22 of which are supporting tables and 5 tables
that are transactional in nature. These are currently in the same database.
We are small and have only one server.
Everything is fine until.... My company decided to do out-source work for
other companies that are similar to us. This means that with the
application setup the way it is, I need to create a different set of tables
for each customer. The supporting tables are huge, so I would like to be
able to put them into their own database and make them accessible from every
company's database.
In Access XP I would click "Get external data","Link Tables" and select the
database and tables I wanted to link to the company specific tables. I
can't figure out how to do this in SQL Server.
Is there a way to do this in SQL Server?
Remember I am a big time novice, so keep it simple.
Thanks
LanceSQL Server supports linked servers, which aren't the same thing at all
(see the distributed querying topics in Books Online for more info).
You can create views and stored procedures in one database that
reference tables in another database by using the
databasename.ownername.objectname syntax as long as the databases are
all on the same server, which may be what you are looking for.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 16 Jan 2004 13:39:10 -0800, "Lance Geeck" <lgeeck@.cox.net>
wrote:
>Hi,
>I am new to SQL Server 2000 and am looking for examples of how to do the
>following:
>I have a set of 27 tables, 22 of which are supporting tables and 5 tables
>that are transactional in nature. These are currently in the same database.
>We are small and have only one server.
>Everything is fine until.... My company decided to do out-source work for
>other companies that are similar to us. This means that with the
>application setup the way it is, I need to create a different set of tables
>for each customer. The supporting tables are huge, so I would like to be
>able to put them into their own database and make them accessible from every
>company's database.
>In Access XP I would click "Get external data","Link Tables" and select the
>database and tables I wanted to link to the company specific tables. I
>can't figure out how to do this in SQL Server.
>Is there a way to do this in SQL Server?
>Remember I am a big time novice, so keep it simple.
>Thanks
>Lance
>
Friday, March 23, 2012
servers: Access 97
Hello,
I am trying to create an Linked Server for MS Access in SQL 7.0. I have
executed the following stored procedures with no problem:
exec sp_addlinkedserver
@.server = "PECRM",
@.srvproduct = "Access 97",
@.Provider = "Microsoft.Jet.OLEDB.4.0",
@.datasrc = "\\apollo\sys\apps\datastor\appdata\contdata.mdb"
exec sp_addlinkedsrvlogin
@.rmtsrvname = "PECRM",
@.useself = "false",
@.locallogin = "sa",
@.rmtuser = "Admin",
@.rmtpassword = Null
When I execute the following SQL Statement
SELECT * FROM PECRM...ContAddr
I get the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\apollo\sys\apps\datastor\appdata\contdata.mdb'. It is
already opened exclusively by another user, or you need permission to view
its data.]
In SQL EM I get the dreaded 7399 error, I have followed the suggestions of
changing the SQL server login to an account that I know has access to a temp
directory but with the same results. Can any one help?
Thanks,
DanHello Solex (wonder if this is your real name, remember post with real names
here)
Seems to be that you are using a LDB file to secur your access database.
Information about this
file must be provided too. Look in the provider settings and provide the
path and name
to this security information file.
Jens Süßmeyer.|||Jens,
The access databaes is not using a WorkGroup database for security. In fact
the database is not secured at all.
Any other suggestions?
Thanks,
Dan
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:%23w%23XcV1ZDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hello Solex (wonder if this is your real name, remember post with real
names
> here)
> Seems to be that you are using a LDB file to secur your access database.
> Information about this
> file must be provided too. Look in the provider settings and provide the
> path and name
> to this security information file.
> Jens Süßmeyer.
>|||Hello Dan !
Did you set the database to exclusive ? Does another Lock-File exists, that
you can´t open the db. Look in the
directory of the mdb and search for a lck file. Delete it und try again.
Somethings Access hangs and stop responing which such suspects errors, and
there is nothing left than restarting the host system of the access mdb (if
killing all processes to the network share doesn´t help to fix it)
Jens Süßmeyer.
"solex" <solex@.nowhere.com> schrieb im Newsbeitrag
news:u4mjr41ZDHA.652@.tk2msftngp13.phx.gbl...
> Jens,
> The access databaes is not using a WorkGroup database for security. In
fact
> the database is not secured at all.
> Any other suggestions?
> Thanks,
> Dan
> "Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
> news:%23w%23XcV1ZDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > Hello Solex (wonder if this is your real name, remember post with real
> names
> > here)
> >
> > Seems to be that you are using a LDB file to secur your access database.
> > Information about this
> > file must be provided too. Look in the provider settings and provide the
> > path and name
> > to this security information file.
> >
> > Jens Süßmeyer.
> >
> >
>
I am trying to create an Linked Server for MS Access in SQL 7.0. I have
executed the following stored procedures with no problem:
exec sp_addlinkedserver
@.server = "PECRM",
@.srvproduct = "Access 97",
@.Provider = "Microsoft.Jet.OLEDB.4.0",
@.datasrc = "\\apollo\sys\apps\datastor\appdata\contdata.mdb"
exec sp_addlinkedsrvlogin
@.rmtsrvname = "PECRM",
@.useself = "false",
@.locallogin = "sa",
@.rmtuser = "Admin",
@.rmtpassword = Null
When I execute the following SQL Statement
SELECT * FROM PECRM...ContAddr
I get the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\apollo\sys\apps\datastor\appdata\contdata.mdb'. It is
already opened exclusively by another user, or you need permission to view
its data.]
In SQL EM I get the dreaded 7399 error, I have followed the suggestions of
changing the SQL server login to an account that I know has access to a temp
directory but with the same results. Can any one help?
Thanks,
DanHello Solex (wonder if this is your real name, remember post with real names
here)
Seems to be that you are using a LDB file to secur your access database.
Information about this
file must be provided too. Look in the provider settings and provide the
path and name
to this security information file.
Jens Süßmeyer.|||Jens,
The access databaes is not using a WorkGroup database for security. In fact
the database is not secured at all.
Any other suggestions?
Thanks,
Dan
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:%23w%23XcV1ZDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hello Solex (wonder if this is your real name, remember post with real
names
> here)
> Seems to be that you are using a LDB file to secur your access database.
> Information about this
> file must be provided too. Look in the provider settings and provide the
> path and name
> to this security information file.
> Jens Süßmeyer.
>|||Hello Dan !
Did you set the database to exclusive ? Does another Lock-File exists, that
you can´t open the db. Look in the
directory of the mdb and search for a lck file. Delete it und try again.
Somethings Access hangs and stop responing which such suspects errors, and
there is nothing left than restarting the host system of the access mdb (if
killing all processes to the network share doesn´t help to fix it)
Jens Süßmeyer.
"solex" <solex@.nowhere.com> schrieb im Newsbeitrag
news:u4mjr41ZDHA.652@.tk2msftngp13.phx.gbl...
> Jens,
> The access databaes is not using a WorkGroup database for security. In
fact
> the database is not secured at all.
> Any other suggestions?
> Thanks,
> Dan
> "Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
> news:%23w%23XcV1ZDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > Hello Solex (wonder if this is your real name, remember post with real
> names
> > here)
> >
> > Seems to be that you are using a LDB file to secur your access database.
> > Information about this
> > file must be provided too. Look in the provider settings and provide the
> > path and name
> > to this security information file.
> >
> > Jens Süßmeyer.
> >
> >
>
Wednesday, March 21, 2012
servers and sp_executeSQL
Hi,
I am trying to do something like the following within a Stored Procedure but have also tried it on its own within a batch:
--
go
Declare @.MySQL nvarchar(500)
@.MySQL = 'Select * from openquery(borders, ''Select * from
dba.organise'')'
exec sp_executeSQL @.MySQL
go
--
then I get Error 7405 'Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection...' I tried setting these in a SQL batch before the one above. Various sources say that you should set these before you create procedure but as you can see above I am not doing this within a create procedure - is it something to do with the sp_executeSQL ?? Help!!!EXEC and sp_executeSQL run on seperate threads so include the set statments in your string
@.MySQL = 'set ANSI_NULLS on
set ANSI_WARNINGS on
Select * from openquery(borders, ''Select * from dba.organise'')'
exec(@.MySQL)
Also, I have found that including the database name in the table reference will eliminate headaches.|||Thanks Paul - What I eventually did was this:
set ansi_nulls on
set ansi_warnings on
go
create procedure ....
... putting it in the SQL statement worked in Query Analyzer but not when it was within the stored procedure.
Thanks for the quick reply!
Regards
Anna|||I would suggest doing the SETs inside the sp, that way if someone extracts the code from your server and recompiles you will still have the correct environment for you linked server query|||Hi Paul,
I did try that but I couldn't seem to get that to work.
Cheers
Anna
I am trying to do something like the following within a Stored Procedure but have also tried it on its own within a batch:
--
go
Declare @.MySQL nvarchar(500)
@.MySQL = 'Select * from openquery(borders, ''Select * from
dba.organise'')'
exec sp_executeSQL @.MySQL
go
--
then I get Error 7405 'Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection...' I tried setting these in a SQL batch before the one above. Various sources say that you should set these before you create procedure but as you can see above I am not doing this within a create procedure - is it something to do with the sp_executeSQL ?? Help!!!EXEC and sp_executeSQL run on seperate threads so include the set statments in your string
@.MySQL = 'set ANSI_NULLS on
set ANSI_WARNINGS on
Select * from openquery(borders, ''Select * from dba.organise'')'
exec(@.MySQL)
Also, I have found that including the database name in the table reference will eliminate headaches.|||Thanks Paul - What I eventually did was this:
set ansi_nulls on
set ansi_warnings on
go
create procedure ....
... putting it in the SQL statement worked in Query Analyzer but not when it was within the stored procedure.
Thanks for the quick reply!
Regards
Anna|||I would suggest doing the SETs inside the sp, that way if someone extracts the code from your server and recompiles you will still have the correct environment for you linked server query|||Hi Paul,
I did try that but I couldn't seem to get that to work.
Cheers
Anna
Monday, March 19, 2012
servers "Client unable to establish connection"
We have several linked SQL servers running both SQL 7 (sp4) and SQL 2000 (sp3). Recently, when we run the following command from SQL 7 query analyzer to a SQL 2000 database, select * from remoteserver.database.dbo.tablename, we get this message:
[OLE/DB provider returned message: Client unable to establish connection]
[DBNMPNTW]ConnectionOpen (CreateFile()).
The databases are linked with specific remote login and password. The same command works the opposite direction, in other words from the SQL 2000 database to SQL 7 database. This was working last week but all of a sudden stopped. Of course, no one admits to changing anything on either of the servers. The SQL 2000 is on Windows 2003 server.
Thanks.
LccTom,
It is possible that some of the recent w2k3 patches disabled the file sharing and remote network connectivity on your w2k3 machine. Can you try if you can remote access to the w2k3 machine, such as file sharing? If you can't, you need to enable the file sharing because the namepipe provider[DBNMPNTW] depends on it.
Also, if you want TCP connection, you need to make firewall exception for the SQL tcp listening port, default to 1433 for a default instance.
|||We have no OS service packs installed but for some reason, it started working again Wednesday morning. Thanks for the reply and I'll make sure to look at the file sharing if it happens again. I reported it to my network people also.servers - provider click gives error
Hi,
I'm getting following error when I click on providers of Linked server objects. It's doesn't show any providers.
I'm using SQl server 2005 std edition.
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot insert the value NULL into column 'Description', table 'tempdb.dbo.#OLEDBProv_000000000021'; column does not allow nulls. INSERT fails.
The statement has been terminated. (Microsoft SQL Server, Error: 515)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476
BUTTONS:
OK
Thanks in Advance,
Rajesh
When you click the provider list the following code executes internally...
One of the providers parse name is returning null where as temp table is not allowing null..
Run EXECUTE master.dbo.xp_enum_oledb_providers procedure and see which provider is causing the problem..
create table #OLEDBProv ( Name sysname NOT NULL, ParseName sysname NOT
NULL,Description sysname NOT NULL ) insert #OLEDBProv EXECUTE master.dbo.xp_enum_oledb_providers
SELECT
op.Name AS [Name],
'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/OLEDBProvider[@.Name=' + quotename(op.Name,'''') + ']' AS [Urn]
FROM
#OLEDBProv op
ORDER BY
[Name] ASC
drop table #OLEDBProv
Thanks for you help..!!. I found provider that causing error MSDAORA.
Trying to figure out how to update value.
Subscribe to:
Posts (Atom)