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

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

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

Linking Access to SQL Server

Hi,

I have an existing Access DB with many Tables, Forms, Queries and Reports. I need to put a SQL Server Back end on this database.

Do I need to code a mirror images of these objects or is there some way to export these into SQL Server?

Also, where do I deal with assigning usernames and passwords? Access or SQL Server. I need to have different views for different users based upon their rights (user, admin)

Thanks,

Mark

There is a full upsizing process that will handle all this for you. Read more here:

http://www.microsoft.com/sql/solutions/migration/access/default.mspx

|||

Hi Mark:

I am not sure if I understand your questions entirely.

If you are looking a way to convert your access database to sql database, following is the link to MSDN article that may help:

http://msdn2.microsoft.com/en-us/library/aa164792(office.10).aspx

About second part of your questions (usernames and password). Could you please provide more information?

Thanks.

Linking a report to a dataset

I copy a report by viewing the code and pasting into a new report. The
problem is that the fields do not display for expressions etc. It gives the
following error "report item not linked to a dataset". How do I link the
report to a dataset so I can see the list of available dataset fields?
ThanksCopy and paste the report instead. Right mouse click on report, copy. Right
mouse click on project (not report folder) and paste. Then rename to
whatever you want. That gets the whole report including the dataset.
Not very discoverable. I would expect to be able to paste into the reports
folder.
Bruce L-C
"Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
news:%23p5b5pcmEHA.952@.TK2MSFTNGP14.phx.gbl...
> I copy a report by viewing the code and pasting into a new report. The
> problem is that the fields do not display for expressions etc. It gives
the
> following error "report item not linked to a dataset". How do I link the
> report to a dataset so I can see the list of available dataset fields?
> Thanks
>|||Is there anyway to link the report to a dataset if I did it the other way?
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXYb8HdmEHA.1672@.TK2MSFTNGP14.phx.gbl...
> Copy and paste the report instead. Right mouse click on report, copy.
> Right
> mouse click on project (not report folder) and paste. Then rename to
> whatever you want. That gets the whole report including the dataset.
> Not very discoverable. I would expect to be able to paste into the reports
> folder.
> Bruce L-C
> "Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
> news:%23p5b5pcmEHA.952@.TK2MSFTNGP14.phx.gbl...
>> I copy a report by viewing the code and pasting into a new report. The
>> problem is that the fields do not display for expressions etc. It gives
> the
>> following error "report item not linked to a dataset". How do I link the
>> report to a dataset so I can see the list of available dataset fields?
>> Thanks
>>
>|||Yes, create a dataset (I have to do this anytime I use the wizard because I
don't like the name the wizard gives and I change the name of the dataset).
Then click on the table/matrix, whatever the dataset is bound to,
properties, datasetname.
Bruce L-C
"Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
news:ebmM3HmmEHA.392@.tk2msftngp13.phx.gbl...
> Is there anyway to link the report to a dataset if I did it the other way?
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OXYb8HdmEHA.1672@.TK2MSFTNGP14.phx.gbl...
> > Copy and paste the report instead. Right mouse click on report, copy.
> > Right
> > mouse click on project (not report folder) and paste. Then rename to
> > whatever you want. That gets the whole report including the dataset.
> >
> > Not very discoverable. I would expect to be able to paste into the
reports
> > folder.
> >
> > Bruce L-C
> >
> > "Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
> > news:%23p5b5pcmEHA.952@.TK2MSFTNGP14.phx.gbl...
> >> I copy a report by viewing the code and pasting into a new report. The
> >> problem is that the fields do not display for expressions etc. It gives
> > the
> >> following error "report item not linked to a dataset". How do I link
the
> >> report to a dataset so I can see the list of available dataset fields?
> >>
> >> Thanks
> >>
> >>
> >
> >
>

servers

Hi,
We have the following code to automatically create the linked server in one
of our upgrade scripts.
declare @.Security_dbname nvarchar(128) -- This is the security
database name
declare @.Security_Server nvarchar(128) -- This is the Security
Server Name
declare @.Instance nvarchar(128) -- This is the
SQL Server instance name
declare @.UserID nvarchar(128) -- This is the
SQL Server administrator user
declare @.Pwd nvarchar(128) -- This is the
SQL Server administrator user's password
/**********Begin User input required section ***************/
-- DCBR 1206
set @.Security_dbname = ''
set @.Security_Server = ''
set @.Instance = ''
set @.UserID = ''
set @.Pwd = ''
/**************End User inputs ****************************/
Create Table ##temp_variables (
Security_dbname nvarchar(128),
Security_Server nvarchar(128),
Instance nvarchar(128),
UserID nvarchar(128),
Pwd nvarchar(128))
Insert into ##temp_variables Values (@.Security_dbname, @.Security_Server,
@.Instance, @.UserID, @.Pwd)
set @.Security_dbname = (select Security_dbname from ##temp_variables)
set @.Security_Server = (select Security_Server from ##temp_variables)
set @.Instance = (select Instance from ##temp_variables)
set @.UserID = (select UserID from ##temp_variables)
set @.Pwd = (select Pwd from ##temp_variables)
BEGIN
declare @.badded int
set @.badded = 0
if len(@.Security_Server) <> 0
BEGIN
if len(@.Instance) <> 0
BEGIN
declare @.Security_Server2 nvarchar(128)
set @.Security_Server2 = @.Security_Server
set @.Security_Server = @.Security_Server2 + '\' + @.Instance
set @.Instance = @.Security_Server2 + '\' + @.Instance
END
else
BEGIN
set @.Instance = @.Security_Server
END
if not exists(select * from master..sysservers where srvname = @.Security_Server)
BEGIN
EXEC sp_addlinkedserver @.Security_Server, '', 'SQLOLEDB', @.Instance
EXEC sp_addlinkedsrvlogin @.Security_Server, 'false', NULL, @.UserID, @.Pwd
set @.badded = 1
END
END
--Making sure user has entered a value for the security database name
IF (@.Security_dbname IS NULL or len(@.Security_dbname) = 0)
BEGIN
RAISERROR('Please enter the security database name',16,1)
END
ELSE
BEGIN
DECLARE @.sUpGradeScript nvarchar(1024)
set @.sUpGradeScript = 'UPDATE Project SET ProjectOwnerUserID = (SELECT
WindowsAccountID FROM '
IF len(@.Security_Server) <> 0
set @.sUpGradeScript = @.sUpGradeScript + '[' + @.Security_Server + ']' + '.'
set @.sUpGradeScript = @.sUpGradeScript + @.Security_dbname + '.dbo.Users as
U WHERE U.UserID COLLATE database_default= p.CreateUserID COLLATE
database_default ) FROM Project p WHERE ProjectOwnerUserID is null'
exec(@.sUpGradeScript)
if @.badded = 1
BEGIN
exec sp_droplinkedsrvlogin @.Security_Server, null
exec sp_dropserver @.Security_Server
END
END
drop table ##temp_variables
END
This code updates a table in a database in one machine with a value from a
column in another table from another database that exists in another machine.
Obviously, we have to create a linked server in order to do this. In trying
to automate this as much as we can with minimal manual user input, we keep
getting the following error:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
What are we doing wrong? How can we get rid of the error?
Thank you in advance,
DeeI would like to add that I've tried creating a client network alias using
both Named Pipes and TCP/IP. In addition, I've used the IP address rather
than the machine name and still no luck.
"bpdee" wrote:
> Hi,
> We have the following code to automatically create the linked server in one
> of our upgrade scripts.
> declare @.Security_dbname nvarchar(128) -- This is the security
> database name
> declare @.Security_Server nvarchar(128) -- This is the Security
> Server Name
> declare @.Instance nvarchar(128) -- This is the
> SQL Server instance name
> declare @.UserID nvarchar(128) -- This is the
> SQL Server administrator user
> declare @.Pwd nvarchar(128) -- This is the
> SQL Server administrator user's password
> /**********Begin User input required section ***************/
> -- DCBR 1206
> set @.Security_dbname = ''
> set @.Security_Server = ''
> set @.Instance = ''
> set @.UserID = ''
> set @.Pwd = ''
> /**************End User inputs ****************************/
>
> Create Table ##temp_variables (
> Security_dbname nvarchar(128),
> Security_Server nvarchar(128),
> Instance nvarchar(128),
> UserID nvarchar(128),
> Pwd nvarchar(128))
> Insert into ##temp_variables Values (@.Security_dbname, @.Security_Server,
> @.Instance, @.UserID, @.Pwd)
> set @.Security_dbname = (select Security_dbname from ##temp_variables)
> set @.Security_Server = (select Security_Server from ##temp_variables)
> set @.Instance = (select Instance from ##temp_variables)
> set @.UserID = (select UserID from ##temp_variables)
> set @.Pwd = (select Pwd from ##temp_variables)
> BEGIN
> declare @.badded int
> set @.badded = 0
> if len(@.Security_Server) <> 0
> BEGIN
> if len(@.Instance) <> 0
> BEGIN
> declare @.Security_Server2 nvarchar(128)
> set @.Security_Server2 = @.Security_Server
> set @.Security_Server = @.Security_Server2 + '\' + @.Instance
> set @.Instance = @.Security_Server2 + '\' + @.Instance
> END
> else
> BEGIN
> set @.Instance = @.Security_Server
> END
> if not exists(select * from master..sysservers where srvname => @.Security_Server)
> BEGIN
> EXEC sp_addlinkedserver @.Security_Server, '', 'SQLOLEDB', @.Instance
> EXEC sp_addlinkedsrvlogin @.Security_Server, 'false', NULL, @.UserID, @.Pwd
> set @.badded = 1
> END
> END
> --Making sure user has entered a value for the security database name
> IF (@.Security_dbname IS NULL or len(@.Security_dbname) = 0)
> BEGIN
> RAISERROR('Please enter the security database name',16,1)
> END
> ELSE
> BEGIN
> DECLARE @.sUpGradeScript nvarchar(1024)
> set @.sUpGradeScript = 'UPDATE Project SET ProjectOwnerUserID = (SELECT
> WindowsAccountID FROM '
> IF len(@.Security_Server) <> 0
> set @.sUpGradeScript = @.sUpGradeScript + '[' + @.Security_Server + ']' + '.'
> set @.sUpGradeScript = @.sUpGradeScript + @.Security_dbname + '.dbo.Users as
> U WHERE U.UserID COLLATE database_default= p.CreateUserID COLLATE
> database_default ) FROM Project p WHERE ProjectOwnerUserID is null'
> exec(@.sUpGradeScript)
> if @.badded = 1
> BEGIN
> exec sp_droplinkedsrvlogin @.Security_Server, null
> exec sp_dropserver @.Security_Server
> END
> END
> drop table ##temp_variables
> END
> This code updates a table in a database in one machine with a value from a
> column in another table from another database that exists in another machine.
> Obviously, we have to create a linked server in order to do this. In trying
> to automate this as much as we can with minimal manual user input, we keep
> getting the following error:
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
>
> What are we doing wrong? How can we get rid of the error?
> Thank you in advance,
> Dee|||Looks like a security issue to me. Try doing the basic definitions by hand
to sort out the security issues, then you can sort out the script or its
invocation once you figure the problem.
It's very hard to help with security issues given the information you're
provided.
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:E42FA181-4248-4D7D-862D-6C88AE88E8A8@.microsoft.com...
>I would like to add that I've tried creating a client network alias using
> both Named Pipes and TCP/IP. In addition, I've used the IP address rather
> than the machine name and still no luck.
> "bpdee" wrote:
>> Hi,
>> We have the following code to automatically create the linked server in
>> one
>> of our upgrade scripts.
>> declare @.Security_dbname nvarchar(128) -- This is the security
>> database name
>> declare @.Security_Server nvarchar(128) -- This is the Security
>> Server Name
>> declare @.Instance nvarchar(128) -- This is
>> the
>> SQL Server instance name
>> declare @.UserID nvarchar(128) -- This is
>> the
>> SQL Server administrator user
>> declare @.Pwd nvarchar(128) -- This is
>> the
>> SQL Server administrator user's password
>> /**********Begin User input required section ***************/
>> -- DCBR 1206
>> set @.Security_dbname = ''
>> set @.Security_Server = ''
>> set @.Instance = ''
>> set @.UserID = ''
>> set @.Pwd = ''
>> /**************End User inputs ****************************/
>>
>> Create Table ##temp_variables (
>> Security_dbname nvarchar(128),
>> Security_Server nvarchar(128),
>> Instance nvarchar(128),
>> UserID nvarchar(128),
>> Pwd nvarchar(128))
>> Insert into ##temp_variables Values (@.Security_dbname, @.Security_Server,
>> @.Instance, @.UserID, @.Pwd)
>> set @.Security_dbname = (select Security_dbname from ##temp_variables)
>> set @.Security_Server = (select Security_Server from ##temp_variables)
>> set @.Instance = (select Instance from ##temp_variables)
>> set @.UserID = (select UserID from ##temp_variables)
>> set @.Pwd = (select Pwd from ##temp_variables)
>> BEGIN
>> declare @.badded int
>> set @.badded = 0
>> if len(@.Security_Server) <> 0
>> BEGIN
>> if len(@.Instance) <> 0
>> BEGIN
>> declare @.Security_Server2 nvarchar(128)
>> set @.Security_Server2 = @.Security_Server
>> set @.Security_Server = @.Security_Server2 + '\' + @.Instance
>> set @.Instance = @.Security_Server2 + '\' + @.Instance
>> END
>> else
>> BEGIN
>> set @.Instance = @.Security_Server
>> END
>> if not exists(select * from master..sysservers where srvname =>> @.Security_Server)
>> BEGIN
>> EXEC sp_addlinkedserver @.Security_Server, '', 'SQLOLEDB', @.Instance
>> EXEC sp_addlinkedsrvlogin @.Security_Server, 'false', NULL, @.UserID, @.Pwd
>> set @.badded = 1
>> END
>> END
>> --Making sure user has entered a value for the security database name
>> IF (@.Security_dbname IS NULL or len(@.Security_dbname) = 0)
>> BEGIN
>> RAISERROR('Please enter the security database name',16,1)
>> END
>> ELSE
>> BEGIN
>> DECLARE @.sUpGradeScript nvarchar(1024)
>> set @.sUpGradeScript = 'UPDATE Project SET ProjectOwnerUserID = (SELECT
>> WindowsAccountID FROM '
>> IF len(@.Security_Server) <> 0
>> set @.sUpGradeScript = @.sUpGradeScript + '[' + @.Security_Server + ']' +
>> '.'
>> set @.sUpGradeScript = @.sUpGradeScript + @.Security_dbname + '.dbo.Users as
>> U WHERE U.UserID COLLATE database_default= p.CreateUserID COLLATE
>> database_default ) FROM Project p WHERE ProjectOwnerUserID is null'
>> exec(@.sUpGradeScript)
>> if @.badded = 1
>> BEGIN
>> exec sp_droplinkedsrvlogin @.Security_Server, null
>> exec sp_dropserver @.Security_Server
>> END
>> END
>> drop table ##temp_variables
>> END
>> This code updates a table in a database in one machine with a value from
>> a
>> column in another table from another database that exists in another
>> machine.
>> Obviously, we have to create a linked server in order to do this. In
>> trying
>> to automate this as much as we can with minimal manual user input, we
>> keep
>> getting the following error:
>> Server: Msg 17, Level 16, State 1, Line 1
>> SQL Server does not exist or access denied.
>>
>> What are we doing wrong? How can we get rid of the error?
>> Thank you in advance,
>> Dee

servers

Hi,
We have the following code to automatically create the linked server in one
of our upgrade scripts.
declare @.Security_dbname nvarchar(128) -- This is the security
database name
declare @.Security_Server nvarchar(128) -- This is the Security
Server Name
declare @.Instance nvarchar(128) -- This is the
SQL Server instance name
declare @.UserID nvarchar(128) -- This is the
SQL Server administrator user
declare @.Pwd nvarchar(128) -- This is the
SQL Server administrator user's password
/**********Begin User input required section ***************/
-- DCBR 1206
set @.Security_dbname = ''
set @.Security_Server = ''
set @.Instance = ''
set @.UserID = ''
set @.Pwd = ''
/**************End User inputs ****************************/
Create Table ##temp_variables (
Security_dbname nvarchar(128),
Security_Server nvarchar(128),
Instance nvarchar(128),
UserID nvarchar(128),
Pwd nvarchar(128))
Insert into ##temp_variables Values (@.Security_dbname, @.Security_Server,
@.Instance, @.UserID, @.Pwd)
set @.Security_dbname = (select Security_dbname from ##temp_variables)
set @.Security_Server = (select Security_Server from ##temp_variables)
set @.Instance = (select Instance from ##temp_variables)
set @.UserID = (select UserID from ##temp_variables)
set @.Pwd = (select Pwd from ##temp_variables)
BEGIN
declare @.badded int
set @.badded = 0
if len(@.Security_Server) <> 0
BEGIN
if len(@.Instance) <> 0
BEGIN
declare @.Security_Server2 nvarchar(128)
set @.Security_Server2 = @.Security_Server
set @.Security_Server = @.Security_Server2 + '\' + @.Instance
set @.Instance = @.Security_Server2 + '\' + @.Instance
END
else
BEGIN
set @.Instance = @.Security_Server
END
if not exists(select * from master..sysservers where srvname =
@.Security_Server)
BEGIN
EXEC sp_addlinkedserver @.Security_Server, '', 'SQLOLEDB', @.Instance
EXEC sp_addlinkedsrvlogin @.Security_Server, 'false', NULL, @.UserID, @.Pwd
set @.badded = 1
END
END
--Making sure user has entered a value for the security database name
IF (@.Security_dbname IS NULL or len(@.Security_dbname) = 0)
BEGIN
RAISERROR('Please enter the security database name',16,1)
END
ELSE
BEGIN
DECLARE @.sUpGradeScript nvarchar(1024)
set @.sUpGradeScript= 'UPDATE Project SET ProjectOwnerUserID = (SELECT
WindowsAccountID FROM '
IF len(@.Security_Server) <> 0
set @.sUpGradeScript= @.sUpGradeScript + '[' + @.Security_Server + ']' + '.'
set @.sUpGradeScript= @.sUpGradeScript + @.Security_dbname + '.dbo.Users as
U WHERE U.UserID COLLATE database_default= p.CreateUserID COLLATE
database_default ) FROM Project p WHERE ProjectOwnerUserID is null'
exec(@.sUpGradeScript)
if @.badded = 1
BEGIN
exec sp_droplinkedsrvlogin @.Security_Server, null
exec sp_dropserver @.Security_Server
END
END
drop table ##temp_variables
END
This code updates a table in a database in one machine with a value from a
column in another table from another database that exists in another machine.
Obviously, we have to create a linked server in order to do this. In trying
to automate this as much as we can with minimal manual user input, we keep
getting the following error:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
What are we doing wrong? How can we get rid of the error?
Thank you in advance,
Dee
I would like to add that I've tried creating a client network alias using
both Named Pipes and TCP/IP. In addition, I've used the IP address rather
than the machine name and still no luck.
"bpdee" wrote:

> Hi,
> We have the following code to automatically create the linked server in one
> of our upgrade scripts.
> declare @.Security_dbname nvarchar(128) -- This is the security
> database name
> declare @.Security_Server nvarchar(128) -- This is the Security
> Server Name
> declare @.Instance nvarchar(128) -- This is the
> SQL Server instance name
> declare @.UserID nvarchar(128) -- This is the
> SQL Server administrator user
> declare @.Pwd nvarchar(128) -- This is the
> SQL Server administrator user's password
> /**********Begin User input required section ***************/
> -- DCBR 1206
> set @.Security_dbname = ''
> set @.Security_Server = ''
> set @.Instance = ''
> set @.UserID = ''
> set @.Pwd = ''
> /**************End User inputs ****************************/
>
> Create Table ##temp_variables (
> Security_dbname nvarchar(128),
> Security_Server nvarchar(128),
> Instance nvarchar(128),
> UserID nvarchar(128),
> Pwd nvarchar(128))
> Insert into ##temp_variables Values (@.Security_dbname, @.Security_Server,
> @.Instance, @.UserID, @.Pwd)
> set @.Security_dbname = (select Security_dbname from ##temp_variables)
> set @.Security_Server = (select Security_Server from ##temp_variables)
> set @.Instance = (select Instance from ##temp_variables)
> set @.UserID = (select UserID from ##temp_variables)
> set @.Pwd = (select Pwd from ##temp_variables)
> BEGIN
> declare @.badded int
> set @.badded = 0
> if len(@.Security_Server) <> 0
> BEGIN
> if len(@.Instance) <> 0
> BEGIN
> declare @.Security_Server2 nvarchar(128)
> set @.Security_Server2 = @.Security_Server
> set @.Security_Server = @.Security_Server2 + '\' + @.Instance
> set @.Instance = @.Security_Server2 + '\' + @.Instance
> END
> else
> BEGIN
> set @.Instance = @.Security_Server
> END
> if not exists(select * from master..sysservers where srvname =
> @.Security_Server)
> BEGIN
> EXEC sp_addlinkedserver @.Security_Server, '', 'SQLOLEDB', @.Instance
> EXEC sp_addlinkedsrvlogin @.Security_Server, 'false', NULL, @.UserID, @.Pwd
> set @.badded = 1
> END
> END
> --Making sure user has entered a value for the security database name
> IF (@.Security_dbname IS NULL or len(@.Security_dbname) = 0)
> BEGIN
> RAISERROR('Please enter the security database name',16,1)
> END
> ELSE
> BEGIN
> DECLARE @.sUpGradeScript nvarchar(1024)
> set @.sUpGradeScript= 'UPDATE Project SET ProjectOwnerUserID = (SELECT
> WindowsAccountID FROM '
> IF len(@.Security_Server) <> 0
> set @.sUpGradeScript= @.sUpGradeScript + '[' + @.Security_Server + ']' + '.'
> set @.sUpGradeScript= @.sUpGradeScript + @.Security_dbname + '.dbo.Users as
> U WHERE U.UserID COLLATE database_default= p.CreateUserID COLLATE
> database_default ) FROM Project p WHERE ProjectOwnerUserID is null'
> exec(@.sUpGradeScript)
> if @.badded = 1
> BEGIN
> exec sp_droplinkedsrvlogin @.Security_Server, null
> exec sp_dropserver @.Security_Server
> END
> END
> drop table ##temp_variables
> END
> This code updates a table in a database in one machine with a value from a
> column in another table from another database that exists in another machine.
> Obviously, we have to create a linked server in order to do this. In trying
> to automate this as much as we can with minimal manual user input, we keep
> getting the following error:
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
>
> What are we doing wrong? How can we get rid of the error?
> Thank you in advance,
> Dee
|||Looks like a security issue to me. Try doing the basic definitions by hand
to sort out the security issues, then you can sort out the script or its
invocation once you figure the problem.
It's very hard to help with security issues given the information you're
provided.
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:E42FA181-4248-4D7D-862D-6C88AE88E8A8@.microsoft.com...[vbcol=seagreen]
>I would like to add that I've tried creating a client network alias using
> both Named Pipes and TCP/IP. In addition, I've used the IP address rather
> than the machine name and still no luck.
> "bpdee" wrote:

servers

Hi,
We have the following code to automatically create the linked server in one
of our upgrade scripts.
declare @.Security_dbname nvarchar(128) -- This is the security
database name
declare @.Security_Server nvarchar(128) -- This is the Security
Server Name
declare @.Instance nvarchar(128) -- This is the
SQL Server instance name
declare @.UserID nvarchar(128) -- This is the
SQL Server administrator user
declare @.Pwd nvarchar(128) -- This is the
SQL Server administrator user's password
/**********Begin User input required section ***************/
-- DCBR 1206
set @.Security_dbname = ''
set @.Security_Server = ''
set @.Instance = ''
set @.UserID = ''
set @.Pwd = ''
/**************End User inputs ****************************/
Create Table ##temp_variables (
Security_dbname nvarchar(128),
Security_Server nvarchar(128),
Instance nvarchar(128),
UserID nvarchar(128),
Pwd nvarchar(128))
Insert into ##temp_variables Values (@.Security_dbname, @.Security_Server,
@.Instance, @.UserID, @.Pwd)
set @.Security_dbname = (select Security_dbname from ##temp_variables)
set @.Security_Server = (select Security_Server from ##temp_variables)
set @.Instance = (select Instance from ##temp_variables)
set @.UserID = (select UserID from ##temp_variables)
set @.Pwd = (select Pwd from ##temp_variables)
BEGIN
declare @.badded int
set @.badded = 0
if len(@.Security_Server) <> 0
BEGIN
if len(@.Instance) <> 0
BEGIN
declare @.Security_Server2 nvarchar(128)
set @.Security_Server2 = @.Security_Server
set @.Security_Server = @.Security_Server2 + '' + @.Instance
set @.Instance = @.Security_Server2 + '' + @.Instance
END
else
BEGIN
set @.Instance = @.Security_Server
END
if not exists(select * from master..sysservers where srvname =
@.Security_Server)
BEGIN
EXEC sp_addlinkedserver @.Security_Server, '', 'SQLOLEDB', @.Instance
EXEC sp_addlinkedsrvlogin @.Security_Server, 'false', NULL, @.UserID, @.Pwd
set @.badded = 1
END
END
--Making sure user has entered a value for the security database name
IF (@.Security_dbname IS NULL or len(@.Security_dbname) = 0)
BEGIN
RAISERROR('Please enter the security database name',16,1)
END
ELSE
BEGIN
DECLARE @.sUpGradeScript nvarchar(1024)
set @.sUpGradeScript = 'UPDATE Project SET ProjectOwnerUserID = (SELECT
WindowsAccountID FROM '
IF len(@.Security_Server) <> 0
set @.sUpGradeScript = @.sUpGradeScript + '[' + @.Security_Server + ']' + '
.'
set @.sUpGradeScript = @.sUpGradeScript + @.Security_dbname + '.dbo.Users as
U WHERE U.UserID COLLATE database_default= p.CreateUserID COLLATE
database_default ) FROM Project p WHERE ProjectOwnerUserID is null'
exec(@.sUpGradeScript)
if @.badded = 1
BEGIN
exec sp_droplinkedsrvlogin @.Security_Server, null
exec sp_dropserver @.Security_Server
END
END
drop table ##temp_variables
END
This code updates a table in a database in one machine with a value from a
column in another table from another database that exists in another machine
.
Obviously, we have to create a linked server in order to do this. In trying
to automate this as much as we can with minimal manual user input, we keep
getting the following error:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
What are we doing wrong? How can we get rid of the error?
Thank you in advance,
DeeI would like to add that I've tried creating a client network alias using
both Named Pipes and TCP/IP. In addition, I've used the IP address rather
than the machine name and still no luck.
"bpdee" wrote:

> Hi,
> We have the following code to automatically create the linked server in on
e
> of our upgrade scripts.
> declare @.Security_dbname nvarchar(128) -- This is the security
> database name
> declare @.Security_Server nvarchar(128) -- This is the Security
> Server Name
> declare @.Instance nvarchar(128) -- This is the
> SQL Server instance name
> declare @.UserID nvarchar(128) -- This is the
> SQL Server administrator user
> declare @.Pwd nvarchar(128) -- This is the
> SQL Server administrator user's password
> /**********Begin User input required section ***************/
> -- DCBR 1206
> set @.Security_dbname = ''
> set @.Security_Server = ''
> set @.Instance = ''
> set @.UserID = ''
> set @.Pwd = ''
> /**************End User inputs ****************************/
>
> Create Table ##temp_variables (
> Security_dbname nvarchar(128),
> Security_Server nvarchar(128),
> Instance nvarchar(128),
> UserID nvarchar(128),
> Pwd nvarchar(128))
> Insert into ##temp_variables Values (@.Security_dbname, @.Security_Server,
> @.Instance, @.UserID, @.Pwd)
> set @.Security_dbname = (select Security_dbname from ##temp_variables)
> set @.Security_Server = (select Security_Server from ##temp_variables)
> set @.Instance = (select Instance from ##temp_variables)
> set @.UserID = (select UserID from ##temp_variables)
> set @.Pwd = (select Pwd from ##temp_variables)
> BEGIN
> declare @.badded int
> set @.badded = 0
> if len(@.Security_Server) <> 0
> BEGIN
> if len(@.Instance) <> 0
> BEGIN
> declare @.Security_Server2 nvarchar(128)
> set @.Security_Server2 = @.Security_Server
> set @.Security_Server = @.Security_Server2 + '' + @.Instance
> set @.Instance = @.Security_Server2 + '' + @.Instance
> END
> else
> BEGIN
> set @.Instance = @.Security_Server
> END
> if not exists(select * from master..sysservers where srvname =
> @.Security_Server)
> BEGIN
> EXEC sp_addlinkedserver @.Security_Server, '', 'SQLOLEDB', @.Instance
> EXEC sp_addlinkedsrvlogin @.Security_Server, 'false', NULL, @.UserID, @.Pw
d
> set @.badded = 1
> END
> END
> --Making sure user has entered a value for the security database name
> IF (@.Security_dbname IS NULL or len(@.Security_dbname) = 0)
> BEGIN
> RAISERROR('Please enter the security database name',16,1)
> END
> ELSE
> BEGIN
> DECLARE @.sUpGradeScript nvarchar(1024)
> set @.sUpGradeScript = 'UPDATE Project SET ProjectOwnerUserID = (SELECT
> WindowsAccountID FROM '
> IF len(@.Security_Server) <> 0
> set @.sUpGradeScript = @.sUpGradeScript + '[' + @.Security_Server + ']
' + '.'
> set @.sUpGradeScript = @.sUpGradeScript + @.Security_dbname + '.dbo.Users a
s
> U WHERE U.UserID COLLATE database_default= p.CreateUserID COLLATE
> database_default ) FROM Project p WHERE ProjectOwnerUserID is null'
> exec(@.sUpGradeScript)
> if @.badded = 1
> BEGIN
> exec sp_droplinkedsrvlogin @.Security_Server, null
> exec sp_dropserver @.Security_Server
> END
> END
> drop table ##temp_variables
> END
> This code updates a table in a database in one machine with a value from a
> column in another table from another database that exists in another machi
ne.
> Obviously, we have to create a linked server in order to do this. In try
ing
> to automate this as much as we can with minimal manual user input, we keep
> getting the following error:
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
>
> What are we doing wrong? How can we get rid of the error?
> Thank you in advance,
> Dee|||Looks like a security issue to me. Try doing the basic definitions by hand
to sort out the security issues, then you can sort out the script or its
invocation once you figure the problem.
It's very hard to help with security issues given the information you're
provided.
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:E42FA181-4248-4D7D-862D-6C88AE88E8A8@.microsoft.com...[vbcol=seagreen]
>I would like to add that I've tried creating a client network alias using
> both Named Pipes and TCP/IP. In addition, I've used the IP address rather
> than the machine name and still no luck.
> "bpdee" wrote:
>

server to Oracle doesnt fetch correct values

I am using the open query method to connect a Oracle server.
Below is my code to connect to oracle,when I execute the same query in oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
I have tried only one record based on id..sqlserver query returns 0 rows..whereas the oracle returns 4 rows..Can some one tell me what will be the problem

SET QUOTED_IDENTIFIER OFF
declare @.sql varchar(750)
select @.sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed', 'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not null ORDER BY A.CNUMBER
" + '")'
EXEC (@.SQL)

thanks
PriyaSo you're saying that PTTSTATUS is an Oracle server linked to from SQLServer, and when you execute the query "SELECT A.PROJECT_ID... ORDER BY A.CNUMBER" from SQLServer it returns less rows than when you execute the same query on Oracle?

What happens if you execute the query from SQL directly?

server to oracle doesn't fetch correct datavalues

I am using the open query method to connect a Oracle server.
Below is my code to connect to oracle,when I execute the same query in
oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
I have tried only one record based on id..sqlserver query returns 0
rows..whereas the oracle returns 4 rows..Can some one tell me what will be
the problem
SET QUOTED_IDENTIFIER OFF
declare @.sql varchar(750)
select @.sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT
A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed',
'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not null
ORDER BY A.CNUMBER
" + '")'
EXEC (@.SQL)
thanks
GP
Sorry I am connecting to two different databases.That's why the results are
different.
"GP" wrote:

> I am using the open query method to connect a Oracle server.
> Below is my code to connect to oracle,when I execute the same query in
> oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
> I have tried only one record based on id..sqlserver query returns 0
> rows..whereas the oracle returns 4 rows..Can some one tell me what will be
> the problem
> SET QUOTED_IDENTIFIER OFF
> declare @.sql varchar(750)
> select @.sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT
> A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
> FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed',
> 'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not null
> ORDER BY A.CNUMBER
> " + '")'
> EXEC (@.SQL)
> thanks
> GP

server to oracle doesn't fetch correct datavalues

I am using the open query method to connect a Oracle server.
Below is my code to connect to oracle,when I execute the same query in
oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
I have tried only one record based on id..sqlserver query returns 0
rows..whereas the oracle returns 4 rows..Can some one tell me what will be
the problem
SET QUOTED_IDENTIFIER OFF
declare @.sql varchar(750)
select @.sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT
A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed',
'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not nul
l
ORDER BY A.CNUMBER
" + '")'
EXEC (@.SQL)
thanks
GPSorry I am connecting to two different databases.That's why the results are
different.
"GP" wrote:

> I am using the open query method to connect a Oracle server.
> Below is my code to connect to oracle,when I execute the same query in
> oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
> I have tried only one record based on id..sqlserver query returns 0
> rows..whereas the oracle returns 4 rows..Can some one tell me what will be
> the problem
> SET QUOTED_IDENTIFIER OFF
> declare @.sql varchar(750)
> select @.sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT
> A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
> FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed'
,
> 'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not n
ull
> ORDER BY A.CNUMBER
> " + '")'
> EXEC (@.SQL)
> thanks
> GP