Hi All
I am trying to create a linked server to MYSQL and it is not listed in the
Linked Server providers in SQL Server 2005. How do I go about adding a
provider that is not in the existing provider list?
Any ideas on how to either add the provider or create the linked server
would be appreciated.
Thanks
- DavidYou need to obtain the needed drivers or providers for MySQL
and install it on the SQL Server box. There are third party
drivers but you can download free drivers from:
http://dev.mysql.com/downloads/
-Sue
On Wed, 30 Aug 2006 06:11:02 -0700, David
<David@.discussions.microsoft.com> wrote:
>Hi All
>I am trying to create a linked server to MYSQL and it is not listed in the
>Linked Server providers in SQL Server 2005. How do I go about adding a
>provider that is not in the existing provider list?
>Any ideas on how to either add the provider or create the linked server
>would be appreciated.
>Thanks
>- Davidsql
Showing posts with label provider. Show all posts
Showing posts with label provider. Show all posts
Friday, March 30, 2012
Linked Server Provider
Hi All
I am trying to create a linked server to mysql and it is not listed in the
Linked Server providers in SQL Server 2005. How do I go about adding a
provider that is not in the existing provider list?
Any ideas on how to either add the provider or create the linked server
would be appreciated.
Thanks
- DavidYou need to obtain the needed drivers or providers for MySQL
and install it on the SQL Server box. There are third party
drivers but you can download free drivers from:
http://dev.mysql.com/downloads/
-Sue
On Wed, 30 Aug 2006 06:11:02 -0700, David
<David@.discussions.microsoft.com> wrote:
>Hi All
>I am trying to create a linked server to mysql and it is not listed in the
>Linked Server providers in SQL Server 2005. How do I go about adding a
>provider that is not in the existing provider list?
>Any ideas on how to either add the provider or create the linked server
>would be appreciated.
>Thanks
>- David
I am trying to create a linked server to mysql and it is not listed in the
Linked Server providers in SQL Server 2005. How do I go about adding a
provider that is not in the existing provider list?
Any ideas on how to either add the provider or create the linked server
would be appreciated.
Thanks
- DavidYou need to obtain the needed drivers or providers for MySQL
and install it on the SQL Server box. There are third party
drivers but you can download free drivers from:
http://dev.mysql.com/downloads/
-Sue
On Wed, 30 Aug 2006 06:11:02 -0700, David
<David@.discussions.microsoft.com> wrote:
>Hi All
>I am trying to create a linked server to mysql and it is not listed in the
>Linked Server providers in SQL Server 2005. How do I go about adding a
>provider that is not in the existing provider list?
>Any ideas on how to either add the provider or create the linked server
>would be appreciated.
>Thanks
>- David
Linked Server Problems from SQL2005
When I run linked server queries from SQL2005 it fails.
Query Example :
SELECT * FROM [servername].master.dbo.sysobjects
Error:
OLE DB provider "SQLNCLI" for linked server " servername" returned
message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote
host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
Notes:
The linked server is configured to use the current logins security
context. It does not matter whether I connect to SQL2000 or SQL2005.
I have a case with MS (6 hours on the phone yesterday), but no help
yet. All spn settings are as recommended by Microsoft. The MS engineer
states that SQL is not passing the login as indicated in a capture file
from netcap.
Any help is appreciated.
TerryHi
Is the remote server SQL2005? Have you tried using a SQL Server login? Can
you use openquery to return the result?
John
"Terry" wrote:
> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>|||Terry wrote:
> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>
You are connecting to ServerA using Windows Authentication, not a SQL
login. The linked server connection is attempting to pass your SQL
login over to ServerB. You'll have to login to ServerA using a true SQL
login, or setup an explicit user mapping in the linked server definition.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I should be able to use Windows Authenitication, and have always in the
past with SQL2000. Auditing concerns require I know who is connecting.
Mapped logins do work but this is not a solution.
The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
Terry
Tracy McKibben wrote:
> Terry wrote:
> You are connecting to ServerA using Windows Authentication, not a SQL
> login. The linked server connection is attempting to pass your SQL
> login over to ServerB. You'll have to login to ServerA using a true SQL
> login, or setup an explicit user mapping in the linked server definition.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||On the 2000 server, run the instcat.sql from the latest service pack you
have installed. This fixes the connect strings. You have to edit the
instcat.sql to put in the correct SQL version. This is documented in the
instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
your problem.
Jackie
"Terry" <tduffy@.calamos.com> wrote in message
news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...
>I should be able to use Windows Authenitication, and have always in the
> past with SQL2000. Auditing concerns require I know who is connecting.
> Mapped logins do work but this is not a solution.
> The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
> Terry
> Tracy McKibben wrote:
>|||Thank you for the responses...
instcat.sql fixes the schema binding errors on 2005->2000 servers. It
does not resolve my issues. instcat has already been run on my 2000
boxes.
Terry
Jackie Brophy wrote:[vbcol=seagreen]
> On the 2000 server, run the instcat.sql from the latest service pack you
> have installed. This fixes the connect strings. You have to edit the
> instcat.sql to put in the correct SQL version. This is documented in the
> instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
> your problem.
> Jackie
> "Terry" <tduffy@.calamos.com> wrote in message
> news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...|||Hi Terry
I assume that you are using the latest service packs on both instances?
Previously this error has been the result of a virus checker or firewall
blocking communications or possibly a faulty network. Do you get any command
s
running on the remote system?
Windows authentication should not require the user to map a linked server
login, but you can run it with the remote server name parameter only to make
sure these are mapped.
You may want to try and check what protocols you are using and try a
different one!
John
"Terry" wrote:
> Thank you for the responses...
> instcat.sql fixes the schema binding errors on 2005->2000 servers. It
> does not resolve my issues. instcat has already been run on my 2000
> boxes.
> Terry
> Jackie Brophy wrote:
>|||We are still on RTM SQL05, but similar issues (double hops only) exist
on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
Firewall and anti-virus are not any issue. I am not sure what you mean
by "commands
running on the remote system." Also, any ideas what might be "faulty"
in the network.
Linked servers with mapped logins works.
Thanks
John Bell wrote:[vbcol=seagreen]
> Hi Terry
> I assume that you are using the latest service packs on both instances?
> Previously this error has been the result of a virus checker or firewall
> blocking communications or possibly a faulty network. Do you get any comma
nds
> running on the remote system?
> Windows authentication should not require the user to map a linked server
> login, but you can run it with the remote server name parameter only to ma
ke
> sure these are mapped.
> You may want to try and check what protocols you are using and try a
> different one!
> John
> "Terry" wrote:
>|||Hi Terry
Use SQL profiler on both machines to try and see what is happening. Did you
check the protocols being used?
http://msdn2.microsoft.com/en-us/library/ms175496.aspx
You may want to make sure that this still occurs on the latest hotfixes for
each version. If that fails you may want to raise an incident with PSS for
this.
John
"Terry" wrote:
> We are still on RTM SQL05, but similar issues (double hops only) exist
> on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
> Firewall and anti-virus are not any issue. I am not sure what you mean
> by "commands
> running on the remote system." Also, any ideas what might be "faulty"
> in the network.
> Linked servers with mapped logins works.
> Thanks
>
> John Bell wrote:
>
Query Example :
SELECT * FROM [servername].master.dbo.sysobjects
Error:
OLE DB provider "SQLNCLI" for linked server " servername" returned
message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote
host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
Notes:
The linked server is configured to use the current logins security
context. It does not matter whether I connect to SQL2000 or SQL2005.
I have a case with MS (6 hours on the phone yesterday), but no help
yet. All spn settings are as recommended by Microsoft. The MS engineer
states that SQL is not passing the login as indicated in a capture file
from netcap.
Any help is appreciated.
TerryHi
Is the remote server SQL2005? Have you tried using a SQL Server login? Can
you use openquery to return the result?
John
"Terry" wrote:
> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>|||Terry wrote:
> When I run linked server queries from SQL2005 it fails.
> Query Example :
> SELECT * FROM [servername].master.dbo.sysobjects
> Error:
> OLE DB provider "SQLNCLI" for linked server " servername" returned
> message "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18452, Level 14, State 1, Line 0
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Notes:
> The linked server is configured to use the current logins security
> context. It does not matter whether I connect to SQL2000 or SQL2005.
> I have a case with MS (6 hours on the phone yesterday), but no help
> yet. All spn settings are as recommended by Microsoft. The MS engineer
> states that SQL is not passing the login as indicated in a capture file
> from netcap.
> Any help is appreciated.
> Terry
>
You are connecting to ServerA using Windows Authentication, not a SQL
login. The linked server connection is attempting to pass your SQL
login over to ServerB. You'll have to login to ServerA using a true SQL
login, or setup an explicit user mapping in the linked server definition.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I should be able to use Windows Authenitication, and have always in the
past with SQL2000. Auditing concerns require I know who is connecting.
Mapped logins do work but this is not a solution.
The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
Terry
Tracy McKibben wrote:
> Terry wrote:
> You are connecting to ServerA using Windows Authentication, not a SQL
> login. The linked server connection is attempting to pass your SQL
> login over to ServerB. You'll have to login to ServerA using a true SQL
> login, or setup an explicit user mapping in the linked server definition.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||On the 2000 server, run the instcat.sql from the latest service pack you
have installed. This fixes the connect strings. You have to edit the
instcat.sql to put in the correct SQL version. This is documented in the
instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
your problem.
Jackie
"Terry" <tduffy@.calamos.com> wrote in message
news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...
>I should be able to use Windows Authenitication, and have always in the
> past with SQL2000. Auditing concerns require I know who is connecting.
> Mapped logins do work but this is not a solution.
> The problem occurs when I connect to SQL2000 or SQL2005 from SQL2005.
> Terry
> Tracy McKibben wrote:
>|||Thank you for the responses...
instcat.sql fixes the schema binding errors on 2005->2000 servers. It
does not resolve my issues. instcat has already been run on my 2000
boxes.
Terry
Jackie Brophy wrote:[vbcol=seagreen]
> On the 2000 server, run the instcat.sql from the latest service pack you
> have installed. This fixes the connect strings. You have to edit the
> instcat.sql to put in the correct SQL version. This is documented in the
> instcat.sql file. This fixed my 2005->2000 linked server problems. Same as
> your problem.
> Jackie
> "Terry" <tduffy@.calamos.com> wrote in message
> news:1152881495.272827.29100@.h48g2000cwc.googlegroups.com...|||Hi Terry
I assume that you are using the latest service packs on both instances?
Previously this error has been the result of a virus checker or firewall
blocking communications or possibly a faulty network. Do you get any command
s
running on the remote system?
Windows authentication should not require the user to map a linked server
login, but you can run it with the remote server name parameter only to make
sure these are mapped.
You may want to try and check what protocols you are using and try a
different one!
John
"Terry" wrote:
> Thank you for the responses...
> instcat.sql fixes the schema binding errors on 2005->2000 servers. It
> does not resolve my issues. instcat has already been run on my 2000
> boxes.
> Terry
> Jackie Brophy wrote:
>|||We are still on RTM SQL05, but similar issues (double hops only) exist
on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
Firewall and anti-virus are not any issue. I am not sure what you mean
by "commands
running on the remote system." Also, any ideas what might be "faulty"
in the network.
Linked servers with mapped logins works.
Thanks
John Bell wrote:[vbcol=seagreen]
> Hi Terry
> I assume that you are using the latest service packs on both instances?
> Previously this error has been the result of a virus checker or firewall
> blocking communications or possibly a faulty network. Do you get any comma
nds
> running on the remote system?
> Windows authentication should not require the user to map a linked server
> login, but you can run it with the remote server name parameter only to ma
ke
> sure these are mapped.
> You may want to try and check what protocols you are using and try a
> different one!
> John
> "Terry" wrote:
>|||Hi Terry
Use SQL profiler on both machines to try and see what is happening. Did you
check the protocols being used?
http://msdn2.microsoft.com/en-us/library/ms175496.aspx
You may want to make sure that this still occurs on the latest hotfixes for
each version. If that fails you may want to raise an incident with PSS for
this.
John
"Terry" wrote:
> We are still on RTM SQL05, but similar issues (double hops only) exist
> on a dev box with sp1. SQL 2000 is a mix of SP3 and SP4.
> Firewall and anti-virus are not any issue. I am not sure what you mean
> by "commands
> running on the remote system." Also, any ideas what might be "faulty"
> in the network.
> Linked servers with mapped logins works.
> Thanks
>
> John Bell wrote:
>
LINKED SERVER Problems
Hi @.ll
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this?
For any kind of information... many thanks
klaus
Hi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this?
>
> For any kind of information... many thanks
>
> klaus
>
>
>
|||Hi Mike
that is what we looking for!!
BUT HOW?
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>
|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas
"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW?
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this?
For any kind of information... many thanks
klaus
Hi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this?
>
> For any kind of information... many thanks
>
> klaus
>
>
>
|||Hi Mike
that is what we looking for!!
BUT HOW?
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>
|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas
"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW?
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>
LINKED SERVER Problems
Hi @.ll
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this'
For any kind of information... many thanks
klausHi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this'
>
> For any kind of information... many thanks
>
> klaus
>
>
>|||Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>> Hi @.ll
>> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
>> Oracle SERVER
>> During big problems with the Provider and a lot of testing we looking for
>> a way to change the LINKED Server to an ODBC Connection!
>> Is there any way to do this'
>>
>> For any kind of information... many thanks
>>
>> klaus
>>
>>
>>
>|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas
"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>> Hi @.ll
>> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
>> Oracle SERVER
>> During big problems with the Provider and a lot of testing we looking for
>> a way to change the LINKED Server to an ODBC Connection!
>> Is there any way to do this'
>>
>> For any kind of information... many thanks
>>
>> klaus
>>
>>
>>
>
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this'
For any kind of information... many thanks
klausHi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this'
>
> For any kind of information... many thanks
>
> klaus
>
>
>|||Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>> Hi @.ll
>> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
>> Oracle SERVER
>> During big problems with the Provider and a lot of testing we looking for
>> a way to change the LINKED Server to an ODBC Connection!
>> Is there any way to do this'
>>
>> For any kind of information... many thanks
>>
>> klaus
>>
>>
>>
>|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas
"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>> Hi @.ll
>> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
>> Oracle SERVER
>> During big problems with the Provider and a lot of testing we looking for
>> a way to change the LINKED Server to an ODBC Connection!
>> Is there any way to do this'
>>
>> For any kind of information... many thanks
>>
>> klaus
>>
>>
>>
>
linked server problems
I do the following to link a server:
EXEC sp_addlinkedserver 'MainLinkedServer', 'SQLServer OLEDB Provider',
'SQLOLEDB', 'MATT', NULL, NULL, 'WEEK'
It says it links fine (no errors), but when I do a select on a table I know
exists
select distinct T1."Institution" c1 from 'MainLinkedServer'...myTable T1
I get the following error:
OLE DB provider 'MainLinkedServer'does not contain table 'myTable'. The
table either does not exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='MainLinkedServer', TableName='myTable'].
any ideas?
I figured it out:
SELECT * FROM linked_server.database.dbo.table_name tn
"Matt Tapia" <mtapia@.inbizservices.com> wrote in message
news:ebXRQoASEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I do the following to link a server:
> EXEC sp_addlinkedserver 'MainLinkedServer', 'SQLServer OLEDB Provider',
> 'SQLOLEDB', 'MATT', NULL, NULL, 'WEEK'
> It says it links fine (no errors), but when I do a select on a table I
know
> exists
> select distinct T1."Institution" c1 from 'MainLinkedServer'...myTable T1
> I get the following error:
> OLE DB provider 'MainLinkedServer'does not contain table 'myTable'. The
> table either does not exist or the current user does not have permissions
on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain
> the table: ProviderName='MainLinkedServer', TableName='myTable'].
> any ideas?
>
>
>
EXEC sp_addlinkedserver 'MainLinkedServer', 'SQLServer OLEDB Provider',
'SQLOLEDB', 'MATT', NULL, NULL, 'WEEK'
It says it links fine (no errors), but when I do a select on a table I know
exists
select distinct T1."Institution" c1 from 'MainLinkedServer'...myTable T1
I get the following error:
OLE DB provider 'MainLinkedServer'does not contain table 'myTable'. The
table either does not exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='MainLinkedServer', TableName='myTable'].
any ideas?
I figured it out:
SELECT * FROM linked_server.database.dbo.table_name tn
"Matt Tapia" <mtapia@.inbizservices.com> wrote in message
news:ebXRQoASEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I do the following to link a server:
> EXEC sp_addlinkedserver 'MainLinkedServer', 'SQLServer OLEDB Provider',
> 'SQLOLEDB', 'MATT', NULL, NULL, 'WEEK'
> It says it links fine (no errors), but when I do a select on a table I
know
> exists
> select distinct T1."Institution" c1 from 'MainLinkedServer'...myTable T1
> I get the following error:
> OLE DB provider 'MainLinkedServer'does not contain table 'myTable'. The
> table either does not exist or the current user does not have permissions
on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain
> the table: ProviderName='MainLinkedServer', TableName='myTable'].
> any ideas?
>
>
>
LINKED SERVER Problems
Hi @.ll
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this'
For any kind of information... many thanks
klausHi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this'
>
> For any kind of information... many thanks
>
> klaus
>
>
>|||Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas
"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>
Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
Oracle SERVER
During big problems with the Provider and a lot of testing we looking for a
way to change the LINKED Server to an ODBC Connection!
Is there any way to do this'
For any kind of information... many thanks
klausHi
Remove the linked server, and re-create it using the ODBC driver from
Oracle.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi @.ll
> Currently we use an OLE DB Provider for our LINKED SERVER to a couple of
> Oracle SERVER
> During big problems with the Provider and a lot of testing we looking for
> a way to change the LINKED Server to an ODBC Connection!
> Is there any way to do this'
>
> For any kind of information... many thanks
>
> klaus
>
>
>|||Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>|||Make sure you have applied the latest MDAC release for your platform. Then,
make sure you've installed the Oracle client relevant to your Oracle server.
If you use the ODBC Administrator, you can check to verify the ODBC drivers
you have installed.
You can also check the registry keys for the currently loaded drivers under
HKLM\Software\ODBC.
Sincerely,
Anthony Thomas
"Klaus" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
news:uferaFcZFHA.2768@.tk2msftngp13.phx.gbl...
Hi Mike
that is what we looking for!!
BUT HOW'
in the Dialog Box we see only the OLEDB Provider and nothing like ODBC..
Klaus
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:utxgSZUZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi
> Remove the linked server, and re-create it using the ODBC driver from
> Oracle.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "news.microsoft.com" <Klaus.Bilger@.C-S-L.BIZ> wrote in message
> news:uKnc2cSZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>
Monday, March 26, 2012
Linked Server oracle 0x80004005 error
Linked Server oracle 0x80004005 error
Has anyone gotten around the .....
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ]. ...
error when creating a linked server to oracle using the oracle drivers ?
Have no problem with the linked server ms oracle driver other than the fact
that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
plus.
Any ideas greatly appreciated ! Thanks, Steve.Hi
0x80004005 = Access Denied. Check that your credentials are correct that you
are supplying. I have also seen this when there are not enough connections
available on the Oracle side to connect.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Note that linked servers are dangerous if you are using the four part naming
method. It is very very very easy to end up pulling the whole table over and
the where clause being processed locally rather than on Oracle. If you are
going to use linked server then use the OpenQuery method instead of 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
> fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Bruce,
That's an interesting point you raise. I use linked servers with 4 part
names heavily. They are all MS SQL 2000 servers and I have no problems
at all with this. Are you suggesting this is a problem with all linked
servers or specifically Oracle?
Having said that I want the report server doing the processing rather
than loading the linked servers.
Chris
Bruce L-C [MVP] wrote:
> Note that linked servers are dangerous if you are using the four part
> naming method. It is very very very easy to end up pulling the whole
> table over and the where clause being processed locally rather than
> on Oracle. If you are going to use linked server then use the
> OpenQuery method instead of 4 part naming.|||I am using linked servers against Sybase but it makes no difference. It
could be that your tables are small enough that you haven't seen it. Another
SQL MVP did some testing for me. This gets better in Yukon. But he saw this
a whole lot. I suggest you take some of your queries and do get the query
plan for them and see what is happening. I would be surprised if some of
them aren't bringing all the data over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:uHCpg%23JIFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Bruce,
> That's an interesting point you raise. I use linked servers with 4 part
> names heavily. They are all MS SQL 2000 servers and I have no problems
> at all with this. Are you suggesting this is a problem with all linked
> servers or specifically Oracle?
> Having said that I want the report server doing the processing rather
> than loading the linked servers.
>
> Chris
> Bruce L-C [MVP] wrote:
>> Note that linked servers are dangerous if you are using the four part
>> naming method. It is very very very easy to end up pulling the whole
>> table over and the where clause being processed locally rather than
>> on Oracle. If you are going to use linked server then use the
>> OpenQuery method instead of 4 part naming.
>|||Grutzi Mike,
Can connect to the oracle server with the dsn from ms
access on the server with no problems. Can also see all the tables and views
for this linked server from the enterprise manager console.
Used these instructions to set it up.How to use Microsoft SQL Analyzer with
the Oracle Provider for OLE DB
---
Configuration: ============== Microsoft SQL Server 2000 SP 1 or 2 Microsoft
Windows 2000 Server SP 2 Oracle Provider for OLE DB 8.1.7.3.0 Oracle Client
8.1.7.3.0 Oracle Database 8.1.7.3.0 Instructions to Setup the Linked Server
Connection: =================================================== 1. Open SQL
Server Enterprise Manager 2. Click on the + to expand the tree for the
database server. You will need to expand it three times. Note: If the
SQL Server does not appear here, you will need to right click and go
through the Wizard for SQL Server Registration. - The first level of
the tree is called Microsoft Sql Server, The second level of the tree is
called SQL Server Group, and the third level of the tree is your SQL
Server Database. For these instructions we will call our SQL Server
Database, 'Scott'. 3. Under the Scott Database, click on the + to expand
the tree control for 'Security' 4. Under Security, right click on Linked
Servers and select "New Linked Server" 5. In the Linked Server Properties
do the following: a. Type in a Linked Server Name, for this example we
will call it 'Tiger' b. Under Server Type select Other data source
- For the provider name select Oracle Provider for OLE DB. c. In the
Data Source field, type in the Oracle Service name (SQL Net Alias)
d. Leave the Product Name and Provider String blank. e. Click on the
Provider Options button. - Check the check boxes for "Dynamic
Parameters" and "Allow InProcess" f. Click Apply & then click OK. - The
Provider Options dialog closes. 6. Go to the "Security" tab in the Linked
Server Properties. a. Select the radio button "Be made using this security
context" at the bottom of the box. b. Type in the User ID (your
Oracle login), ie. Scott in the "Remote login" field and your
Password, ie. Tiger in the "With Password" field. 7. Go to the "Server
Options" tab in the Linked Server Properties. a. While leaving the
default selections selected, also check the checkbox for "Collation
Compatible". b. Click OK This will create your Linked Server named Tiger.
Test the Linked Connection ================================== To test the
Linked Server, open up Microsoft Query Analyzer (It is found under the
Microsoft SQL Server menu option). 1. Select your SQL Server in the Connect
to SQL Server box. a. Since we have the SQL Server installed on the same
machine, we select (local). b. Select the appropriate Connect using
option. This is determined when you Installed/Created your SQL Server
database. For our example here we use Windows Authentication. And click
on the Ok button. 2. In the Query screen you can type a query. The
query will follow this format: SELECT * FROM <Linked Server Name>..<Oracle
user name in all CAPS>.<Oracle Table Name in all CAPS> IE. SELECT * FROM
TIGER..SCOTT.EMP 3. If you have the default scott/tiger schema installed in
your Oracle database you can use the following query: SELECT * FROM
TIGER..SCOTT.EMP 4. Type it into the Query box. 5. Click on the Green Arrow
to run the query. 6. You should see the contents of the Emp table in the
Grid.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 0x80004005 = Access Denied. Check that your credentials are correct that you
> are supplying. I have also seen this when there are not enough connections
> available on the Oracle side to connect.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Hi Bruce,
Thank you so much for the reply. Created the VMFO linked server
again using the Oracle driver and tried the openquery method and... still
got the ...
SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: ]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ].
"Bruce L-C [MVP]" wrote:
> Note that linked servers are dangerous if you are using the four part naming
> method. It is very very very easy to end up pulling the whole table over and
> the where clause being processed locally rather than on Oracle. If you are
> going to use linked server then use the OpenQuery method instead of 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> ..
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> > fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Go to your linked server in Enterprise Manager. Right mouse click,
properties. Go to the Security tab. Do you have a username and password set
for it?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> Hi Bruce,
> Thank you so much for the reply. Created the VMFO linked
server
> again using the Oracle driver and tried the openquery method and... still
> got the ...
> SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: ]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ].
> "Bruce L-C [MVP]" wrote:
> > Note that linked servers are dangerous if you are using the four part
naming
> > method. It is very very very easy to end up pulling the whole table over
and
> > the where clause being processed locally rather than on Oracle. If you
are
> > going to use linked server then use the OpenQuery method instead of 4
part
> > naming.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > ..
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > Linked Server oracle 0x80004005 error
> > >
> > > Has anyone gotten around the .....
> > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > [OLE/DB provider returned message: Unspecified error]
> > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > returned 0x80004005: ]. ...
> > > error when creating a linked server to oracle using the oracle drivers
?
> > > Have no problem with the linked server ms oracle driver other than the
> > > fact
> > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
sql
> > > plus.
> > >
> > > Any ideas greatly appreciated ! Thanks, Steve.
> > >
> >
> >
> >|||Hi Bruce,
Thanks for your reply. Yes, there is a user/pw set. Also, all
the tables and views for the linked server appear perfectly. Do not think
this would be possible without that. Steve.
"Bruce L-C [MVP]" wrote:
> Go to your linked server in Enterprise Manager. Right mouse click,
> properties. Go to the Security tab. Do you have a username and password set
> for it?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> > Hi Bruce,
> > Thank you so much for the reply. Created the VMFO linked
> server
> > again using the Oracle driver and tried the openquery method and... still
> > got the ...
> >
> > SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> >
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: ]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ].
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Note that linked servers are dangerous if you are using the four part
> naming
> > > method. It is very very very easy to end up pulling the whole table over
> and
> > > the where clause being processed locally rather than on Oracle. If you
> are
> > > going to use linked server then use the OpenQuery method instead of 4
> part
> > > naming.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > > ..
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > > Linked Server oracle 0x80004005 error
> > > >
> > > > Has anyone gotten around the .....
> > > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > > [OLE/DB provider returned message: Unspecified error]
> > > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > > returned 0x80004005: ]. ...
> > > > error when creating a linked server to oracle using the oracle drivers
> ?
> > > > Have no problem with the linked server ms oracle driver other than the
> > > > fact
> > > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
> sql
> > > > plus.
> > > >
> > > > Any ideas greatly appreciated ! Thanks, Steve.
> > > >
> > >
> > >
> > >
>
>sql
Has anyone gotten around the .....
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ]. ...
error when creating a linked server to oracle using the oracle drivers ?
Have no problem with the linked server ms oracle driver other than the fact
that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
plus.
Any ideas greatly appreciated ! Thanks, Steve.Hi
0x80004005 = Access Denied. Check that your credentials are correct that you
are supplying. I have also seen this when there are not enough connections
available on the Oracle side to connect.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Note that linked servers are dangerous if you are using the four part naming
method. It is very very very easy to end up pulling the whole table over and
the where clause being processed locally rather than on Oracle. If you are
going to use linked server then use the OpenQuery method instead of 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
> fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Bruce,
That's an interesting point you raise. I use linked servers with 4 part
names heavily. They are all MS SQL 2000 servers and I have no problems
at all with this. Are you suggesting this is a problem with all linked
servers or specifically Oracle?
Having said that I want the report server doing the processing rather
than loading the linked servers.
Chris
Bruce L-C [MVP] wrote:
> Note that linked servers are dangerous if you are using the four part
> naming method. It is very very very easy to end up pulling the whole
> table over and the where clause being processed locally rather than
> on Oracle. If you are going to use linked server then use the
> OpenQuery method instead of 4 part naming.|||I am using linked servers against Sybase but it makes no difference. It
could be that your tables are small enough that you haven't seen it. Another
SQL MVP did some testing for me. This gets better in Yukon. But he saw this
a whole lot. I suggest you take some of your queries and do get the query
plan for them and see what is happening. I would be surprised if some of
them aren't bringing all the data over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:uHCpg%23JIFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Bruce,
> That's an interesting point you raise. I use linked servers with 4 part
> names heavily. They are all MS SQL 2000 servers and I have no problems
> at all with this. Are you suggesting this is a problem with all linked
> servers or specifically Oracle?
> Having said that I want the report server doing the processing rather
> than loading the linked servers.
>
> Chris
> Bruce L-C [MVP] wrote:
>> Note that linked servers are dangerous if you are using the four part
>> naming method. It is very very very easy to end up pulling the whole
>> table over and the where clause being processed locally rather than
>> on Oracle. If you are going to use linked server then use the
>> OpenQuery method instead of 4 part naming.
>|||Grutzi Mike,
Can connect to the oracle server with the dsn from ms
access on the server with no problems. Can also see all the tables and views
for this linked server from the enterprise manager console.
Used these instructions to set it up.How to use Microsoft SQL Analyzer with
the Oracle Provider for OLE DB
---
Configuration: ============== Microsoft SQL Server 2000 SP 1 or 2 Microsoft
Windows 2000 Server SP 2 Oracle Provider for OLE DB 8.1.7.3.0 Oracle Client
8.1.7.3.0 Oracle Database 8.1.7.3.0 Instructions to Setup the Linked Server
Connection: =================================================== 1. Open SQL
Server Enterprise Manager 2. Click on the + to expand the tree for the
database server. You will need to expand it three times. Note: If the
SQL Server does not appear here, you will need to right click and go
through the Wizard for SQL Server Registration. - The first level of
the tree is called Microsoft Sql Server, The second level of the tree is
called SQL Server Group, and the third level of the tree is your SQL
Server Database. For these instructions we will call our SQL Server
Database, 'Scott'. 3. Under the Scott Database, click on the + to expand
the tree control for 'Security' 4. Under Security, right click on Linked
Servers and select "New Linked Server" 5. In the Linked Server Properties
do the following: a. Type in a Linked Server Name, for this example we
will call it 'Tiger' b. Under Server Type select Other data source
- For the provider name select Oracle Provider for OLE DB. c. In the
Data Source field, type in the Oracle Service name (SQL Net Alias)
d. Leave the Product Name and Provider String blank. e. Click on the
Provider Options button. - Check the check boxes for "Dynamic
Parameters" and "Allow InProcess" f. Click Apply & then click OK. - The
Provider Options dialog closes. 6. Go to the "Security" tab in the Linked
Server Properties. a. Select the radio button "Be made using this security
context" at the bottom of the box. b. Type in the User ID (your
Oracle login), ie. Scott in the "Remote login" field and your
Password, ie. Tiger in the "With Password" field. 7. Go to the "Server
Options" tab in the Linked Server Properties. a. While leaving the
default selections selected, also check the checkbox for "Collation
Compatible". b. Click OK This will create your Linked Server named Tiger.
Test the Linked Connection ================================== To test the
Linked Server, open up Microsoft Query Analyzer (It is found under the
Microsoft SQL Server menu option). 1. Select your SQL Server in the Connect
to SQL Server box. a. Since we have the SQL Server installed on the same
machine, we select (local). b. Select the appropriate Connect using
option. This is determined when you Installed/Created your SQL Server
database. For our example here we use Windows Authentication. And click
on the Ok button. 2. In the Query screen you can type a query. The
query will follow this format: SELECT * FROM <Linked Server Name>..<Oracle
user name in all CAPS>.<Oracle Table Name in all CAPS> IE. SELECT * FROM
TIGER..SCOTT.EMP 3. If you have the default scott/tiger schema installed in
your Oracle database you can use the following query: SELECT * FROM
TIGER..SCOTT.EMP 4. Type it into the Query box. 5. Click on the Green Arrow
to run the query. 6. You should see the contents of the Emp table in the
Grid.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 0x80004005 = Access Denied. Check that your credentials are correct that you
> are supplying. I have also seen this when there are not enough connections
> available on the Oracle side to connect.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Hi Bruce,
Thank you so much for the reply. Created the VMFO linked server
again using the Oracle driver and tried the openquery method and... still
got the ...
SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: ]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ].
"Bruce L-C [MVP]" wrote:
> Note that linked servers are dangerous if you are using the four part naming
> method. It is very very very easy to end up pulling the whole table over and
> the where clause being processed locally rather than on Oracle. If you are
> going to use linked server then use the OpenQuery method instead of 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> ..
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> > fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Go to your linked server in Enterprise Manager. Right mouse click,
properties. Go to the Security tab. Do you have a username and password set
for it?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> Hi Bruce,
> Thank you so much for the reply. Created the VMFO linked
server
> again using the Oracle driver and tried the openquery method and... still
> got the ...
> SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: ]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ].
> "Bruce L-C [MVP]" wrote:
> > Note that linked servers are dangerous if you are using the four part
naming
> > method. It is very very very easy to end up pulling the whole table over
and
> > the where clause being processed locally rather than on Oracle. If you
are
> > going to use linked server then use the OpenQuery method instead of 4
part
> > naming.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > ..
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > Linked Server oracle 0x80004005 error
> > >
> > > Has anyone gotten around the .....
> > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > [OLE/DB provider returned message: Unspecified error]
> > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > returned 0x80004005: ]. ...
> > > error when creating a linked server to oracle using the oracle drivers
?
> > > Have no problem with the linked server ms oracle driver other than the
> > > fact
> > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
sql
> > > plus.
> > >
> > > Any ideas greatly appreciated ! Thanks, Steve.
> > >
> >
> >
> >|||Hi Bruce,
Thanks for your reply. Yes, there is a user/pw set. Also, all
the tables and views for the linked server appear perfectly. Do not think
this would be possible without that. Steve.
"Bruce L-C [MVP]" wrote:
> Go to your linked server in Enterprise Manager. Right mouse click,
> properties. Go to the Security tab. Do you have a username and password set
> for it?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> > Hi Bruce,
> > Thank you so much for the reply. Created the VMFO linked
> server
> > again using the Oracle driver and tried the openquery method and... still
> > got the ...
> >
> > SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> >
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: ]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ].
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Note that linked servers are dangerous if you are using the four part
> naming
> > > method. It is very very very easy to end up pulling the whole table over
> and
> > > the where clause being processed locally rather than on Oracle. If you
> are
> > > going to use linked server then use the OpenQuery method instead of 4
> part
> > > naming.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > > ..
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > > Linked Server oracle 0x80004005 error
> > > >
> > > > Has anyone gotten around the .....
> > > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > > [OLE/DB provider returned message: Unspecified error]
> > > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > > returned 0x80004005: ]. ...
> > > > error when creating a linked server to oracle using the oracle drivers
> ?
> > > > Have no problem with the linked server ms oracle driver other than the
> > > > fact
> > > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
> sql
> > > > plus.
> > > >
> > > > Any ideas greatly appreciated ! Thanks, Steve.
> > > >
> > >
> > >
> > >
>
>sql
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
Regards,
Jamie
Forgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
Regards,
Jamie
Forgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
LINKED SERVER NESTED OPENQUERY SAMPLE FROM TIPS AND TRICKS DOESN'T APPEARS TO FAIL WITH MSO
Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.
I'm generally following theTips and Tricks article.
"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.
e.g.
SELECT * FROM OPENQUERY(DMServer,
'select … FROM Modell PREDICTION JOIN OPENQUERY…')
The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.
OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".
Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query
SELECT * FROM OPENQUERY(DMServer,
'SELECT
t.[CardTransactionID],
t.[PostingDate],
[Misuse Abuse Profile].[Even Dollar Purchase],
PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,
PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,
t.[BillingAmount]
FROM
[Misuse Abuse Profile]
PREDICTION JOIN
OPENQUERY([Athena Dev],
''SELECT
[CardTransactionID],
[PostingDate],
[BillingAmount],
[AccountNumber],
[SupplierStateProvinceCode],
[MerchantCategoryCode],
[PurchaseIDFormat],
[TransactionTime],
[TaxAmountIncludedCode],
[Tax2AmountIncludedCode],
[OrderTypeCode],
[MemoPostFlag],
[EvenDollarPurchase]
FROM
[dbo].[vMisuseAbuseProfile]
'') AS t
ON
[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND
[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND
[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND
[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND
[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND
[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND
[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND
[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND
[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND
[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]
')
In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?
OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".
It appears the default Analysis Services Data Source wizard selection of SQL Native Client Data Source provider cannot prepare an OPENQUERY if passed from a linked server? I changed the Datasource to Provider=SQLOLEDB.1 and the query now works.
|||this is very odd - we will look into it - thanks for reporting.|||Hi Bill,
Could you give more details about the setup. Is the SQL server where you defined the linked server DMTest on the same machine with the Analysis Server it refers too? And also is the SQL server refered on the inner openquery datasource [Athena Dev] on a different machine than the machines with AS and SQL server with added linked server?
Thanks,
Dana Cristofor
Friday, March 23, 2012
Linked Server issue
I have a problem querying a linked server connected via the OleDB provider for ODBC.
This is the DataDirect 32-BIT SequeLink 5.4 client driver connecting to a FileMaker Pro 8 database. I've tested this in VB.net 2005 by adding an ODBC connection in server explorer and a data grid to a form. I can run the tooty and the data loads in the grid with no errors what so ever.
So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.
SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')
The result I get is this;
OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".
Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.
I also can't use four part naming with out getting an error as follows;
I give the statement;
SELECT * FROM DNA...Stock_DNA
and get this;
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".
Any help or work arounds would be greatly appreciated.
It sounds like the problem is not related to the ODBC connection string, but with the Linked servers that are defined within the SQL server itself. When you invoke the linked server query the actual schema does not match the schema stored by the linked server information.
I have seen this twice - and i had to use a different solution each time:
1) define an index on the table you are selecting from - sometimes SQL can get confused without one.
or
2) define a view for the data you want and select from the view with the ODBC connection string.
Hope this helps - let me know how you get on.
Mike
|||I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?
|||I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?
Here is my attempt to load a DataGridView with Filemaker Pro data:
string sConnString = "DRIVER={DataDirect 32-BIT SequeLink 5.4};SDSN=Sample10;HST=localHost;PRT=2399;UID=admin;";
odbcConnection1 = new OdbcConnection(sConnString);
try
{
odbcConnection1.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//connection opens okay
//The following doesn't work:
string strSQL = "select * from FirstName";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, odbcConnection1);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds, "FirstName"); //ERROR [HY000] Unable to retrieve error message from backend ODBC Driver.
dataGridView1.DataSource = ds.Tables["FirstName"];
|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||
Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.
Types in table that can be loaded:
VARCHAR
Types in table the cannot be loaded:
DOUBLE, VARCHAR, DATE
I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?
EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.
|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||
I'm at the same point as Brandon C...whose question seems to not have been really addressed.
To add to his post, if I try to use 4-part naming to query the linked server (FILEMAKER8), and just make up a schema name (dbschema) and run the query "SELECT Profiles.* FROM FILEMAKER8.dbschema..Profile Profiles", I get the following error:
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.
I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).
Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)
|||Just an FYI,I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.
Using the OdbcDataAdapter I am able to populate and return a DataSet regardless of the Data Types in the table.|||I think you are using filemaker as a linked server, if so ensure you relevant compatible drivers to perform the connection.|||
Yes, the DataDirect 32-BIT SequeLink 5.4 driver is the correct one for Filemaker. You should find the installer in your Filemaker folder ->\Extensions\xDBC Support\bin. Thanks for that tip, Brandon, I couldn't pull out some Filemaker data types, like DATE, for example.
|||Just a little more follow up.I had to create a connection for each table or 'FORM' in FileMaker.
The code I'm using return the data set would be similar to the following;
Code Snippet
<WebMethod(Description:="This method takes a stock number and returns the" & _
" record from the Stock_DNA table.")> _
Public Function ReturnStockDNARecord(ByVal StockNo As String) As DataSet
Dim sSQL As String = ""
Dim sConnect As String = ""
Dim oConnection As New Odbc.OdbcConnection
oConnection.ConnectionString = "DSN=ABRC_DNA;UID=SYSTEM;PWD=SYSTEM;SDSN=Stock_DNA"
Try
oConnection.Open()
Catch e As Exception
LogAppError(e.Message & vbTab & e.Source & vbTab & _
e.StackTrace & vbTab & CStr(Now))
Return Nothing
End Try
sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"
Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function
Simple but it does the job.
|||
Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?
Linked Server issue
I have a problem querying a linked server connected via the OleDB provider for ODBC.
This is the DataDirect 32-BIT SequeLink 5.4 client driver connecting to a FileMaker Pro 8 database. I've tested this in VB.net 2005 by adding an ODBC connection in server explorer and a data grid to a form. I can run the tooty and the data loads in the grid with no errors what so ever.
So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.
SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')
The result I get is this;
OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".
Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.
I also can't use four part naming with out getting an error as follows;
I give the statement;
SELECT * FROM DNA...Stock_DNA
and get this;
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".
Any help or work arounds would be greatly appreciated.
It sounds like the problem is not related to the ODBC connection string, but with the Linked servers that are defined within the SQL server itself. When you invoke the linked server query the actual schema does not match the schema stored by the linked server information.
I have seen this twice - and i had to use a different solution each time:
1) define an index on the table you are selecting from - sometimes SQL can get confused without one.
or
2) define a view for the data you want and select from the view with the ODBC connection string.
Hope this helps - let me know how you get on.
Mike
|||I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?
|||I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?
Here is my attempt to load a DataGridView with Filemaker Pro data:
string sConnString = "DRIVER={DataDirect 32-BIT SequeLink 5.4};SDSN=Sample10;HST=localHost;PRT=2399;UID=admin;";
odbcConnection1 = new OdbcConnection(sConnString);
try
{
odbcConnection1.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//connection opens okay
//The following doesn't work:
string strSQL = "select * from FirstName";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, odbcConnection1);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds, "FirstName"); //ERROR [HY000] Unable to retrieve error message from backend ODBC Driver.
dataGridView1.DataSource = ds.Tables["FirstName"];
|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||
Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.
Types in table that can be loaded:
VARCHAR
Types in table the cannot be loaded:
DOUBLE, VARCHAR, DATE
I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?
EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.
|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||
I'm at the same point as Brandon C...whose question seems to not have been really addressed.
To add to his post, if I try to use 4-part naming to query the linked server (FILEMAKER8), and just make up a schema name (dbschema) and run the query "SELECT Profiles.* FROM FILEMAKER8.dbschema..Profile Profiles", I get the following error:
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.
I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).
Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)
|||Just an FYI,I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.
Using the OdbcDataAdapter I am able to populate and return a DataSet regardless of the Data Types in the table.|||I think you are using filemaker as a linked server, if so ensure you relevant compatible drivers to perform the connection.|||
Yes, the DataDirect 32-BIT SequeLink 5.4 driver is the correct one for Filemaker. You should find the installer in your Filemaker folder ->\Extensions\xDBC Support\bin. Thanks for that tip, Brandon, I couldn't pull out some Filemaker data types, like DATE, for example.
|||Just a little more follow up.I had to create a connection for each table or 'FORM' in FileMaker.
The code I'm using return the data set would be similar to the following;
Code Snippet
<WebMethod(Description:="This method takes a stock number and returns the" & _
" record from the Stock_DNA table.")> _
Public Function ReturnStockDNARecord(ByVal StockNo As String) As DataSet
Dim sSQL As String = ""
Dim sConnect As String = ""
Dim oConnection As New Odbc.OdbcConnection
oConnection.ConnectionString = "DSN=ABRC_DNA;UID=SYSTEM;PWD=SYSTEM;SDSN=Stock_DNA"
Try
oConnection.Open()
Catch e As Exception
LogAppError(e.Message & vbTab & e.Source & vbTab & _
e.StackTrace & vbTab & CStr(Now))
Return Nothing
End Try
sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"
Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function
Simple but it does the job.
|||
Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?
Linked Server issue
I have a problem querying a linked server connected via the OleDB provider for ODBC.
This is the DataDirect 32-BIT SequeLink 5.4 client driver connecting to a FileMaker Pro 8 database. I've tested this in VB.net 2005 by adding an ODBC connection in server explorer and a data grid to a form. I can run the tooty and the data loads in the grid with no errors what so ever.
So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.
SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')
The result I get is this;
OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".
Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.
I also can't use four part naming with out getting an error as follows;
I give the statement;
SELECT * FROM DNA...Stock_DNA
and get this;
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".
Any help or work arounds would be greatly appreciated.
It sounds like the problem is not related to the ODBC connection string, but with the Linked servers that are defined within the SQL server itself. When you invoke the linked server query the actual schema does not match the schema stored by the linked server information.
I have seen this twice - and i had to use a different solution each time:
1) define an index on the table you are selecting from - sometimes SQL can get confused without one.
or
2) define a view for the data you want and select from the view with the ODBC connection string.
Hope this helps - let me know how you get on.
Mike
|||I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?
|||I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?
Here is my attempt to load a DataGridView with Filemaker Pro data:
string sConnString = "DRIVER={DataDirect 32-BIT SequeLink 5.4};SDSN=Sample10;HST=localHost;PRT=2399;UID=admin;";
odbcConnection1 = new OdbcConnection(sConnString);
try
{
odbcConnection1.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//connection opens okay
//The following doesn't work:
string strSQL = "select * from FirstName";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, odbcConnection1);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds, "FirstName"); //ERROR [HY000] Unable to retrieve error message from backend ODBC Driver.
dataGridView1.DataSource = ds.Tables["FirstName"];
|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||
Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.
Types in table that can be loaded:
VARCHAR
Types in table the cannot be loaded:
DOUBLE, VARCHAR, DATE
I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?
EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.
|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||
I'm at the same point as Brandon C...whose question seems to not have been really addressed.
To add to his post, if I try to use 4-part naming to query the linked server (FILEMAKER8), and just make up a schema name (dbschema) and run the query "SELECT Profiles.* FROM FILEMAKER8.dbschema..Profile Profiles", I get the following error:
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.
I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).
Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)
|||Just an FYI,I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.
Using the OdbcDataAdapter I am able to populate and return a DataSet regardless of the Data Types in the table.|||I think you are using filemaker as a linked server, if so ensure you relevant compatible drivers to perform the connection.|||
Yes, the DataDirect 32-BIT SequeLink 5.4 driver is the correct one for Filemaker. You should find the installer in your Filemaker folder ->\Extensions\xDBC Support\bin. Thanks for that tip, Brandon, I couldn't pull out some Filemaker data types, like DATE, for example.
|||Just a little more follow up.I had to create a connection for each table or 'FORM' in FileMaker.
The code I'm using return the data set would be similar to the following;
Code Snippet
<WebMethod(Description:="This method takes a stock number and returns the" & _
" record from the Stock_DNA table.")> _
Public Function ReturnStockDNARecord(ByVal StockNo As String) As DataSet
Dim sSQL As String = ""
Dim sConnect As String = ""
Dim oConnection As New Odbc.OdbcConnection
oConnection.ConnectionString = "DSN=ABRC_DNA;UID=SYSTEM;PWD=SYSTEM;SDSN=Stock_DNA"
Try
oConnection.Open()
Catch e As Exception
LogAppError(e.Message & vbTab & e.Source & vbTab & _
e.StackTrace & vbTab & CStr(Now))
Return Nothing
End Try
sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"
Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function
Simple but it does the job.
|||
Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?
Linked Server issue
I have a problem querying a linked server connected via the OleDB provider for ODBC.
This is the DataDirect 32-BIT SequeLink 5.4 client driver connecting to a FileMaker Pro 8 database. I've tested this in VB.net 2005 by adding an ODBC connection in server explorer and a data grid to a form. I can run the tooty and the data loads in the grid with no errors what so ever.
So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.
SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')
The result I get is this;
OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".
Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.
I also can't use four part naming with out getting an error as follows;
I give the statement;
SELECT * FROM DNA...Stock_DNA
and get this;
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".
Any help or work arounds would be greatly appreciated.
It sounds like the problem is not related to the ODBC connection string, but with the Linked servers that are defined within the SQL server itself. When you invoke the linked server query the actual schema does not match the schema stored by the linked server information.
I have seen this twice - and i had to use a different solution each time:
1) define an index on the table you are selecting from - sometimes SQL can get confused without one.
or
2) define a view for the data you want and select from the view with the ODBC connection string.
Hope this helps - let me know how you get on.
Mike
|||I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?
|||I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?
Here is my attempt to load a DataGridView with Filemaker Pro data:
string sConnString = "DRIVER={DataDirect 32-BIT SequeLink 5.4};SDSN=Sample10;HST=localHost;PRT=2399;UID=admin;";
odbcConnection1 = new OdbcConnection(sConnString);
try
{
odbcConnection1.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//connection opens okay
//The following doesn't work:
string strSQL = "select * from FirstName";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, odbcConnection1);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds, "FirstName"); //ERROR [HY000] Unable to retrieve error message from backend ODBC Driver.
dataGridView1.DataSource = ds.Tables["FirstName"];
|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||
Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.
Types in table that can be loaded:
VARCHAR
Types in table the cannot be loaded:
DOUBLE, VARCHAR, DATE
I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?
EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.
|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||
I'm at the same point as Brandon C...whose question seems to not have been really addressed.
To add to his post, if I try to use 4-part naming to query the linked server (FILEMAKER8), and just make up a schema name (dbschema) and run the query "SELECT Profiles.* FROM FILEMAKER8.dbschema..Profile Profiles", I get the following error:
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.
I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).
Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)
|||Just an FYI,I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.
Using the OdbcDataAdapter I am able to populate and return a DataSet regardless of the Data Types in the table.|||I think you are using filemaker as a linked server, if so ensure you relevant compatible drivers to perform the connection.|||
Yes, the DataDirect 32-BIT SequeLink 5.4 driver is the correct one for Filemaker. You should find the installer in your Filemaker folder ->\Extensions\xDBC Support\bin. Thanks for that tip, Brandon, I couldn't pull out some Filemaker data types, like DATE, for example.
|||Just a little more follow up.I had to create a connection for each table or 'FORM' in FileMaker.
The code I'm using return the data set would be similar to the following;
Code Snippet
<WebMethod(Description:="This method takes a stock number and returns the" & _
" record from the Stock_DNA table.")> _
Public Function ReturnStockDNARecord(ByVal StockNo As String) As DataSet
Dim sSQL As String = ""
Dim sConnect As String = ""
Dim oConnection As New Odbc.OdbcConnection
oConnection.ConnectionString = "DSN=ABRC_DNA;UID=SYSTEM;PWD=SYSTEM;SDSN=Stock_DNA"
Try
oConnection.Open()
Catch e As Exception
LogAppError(e.Message & vbTab & e.Source & vbTab & _
e.StackTrace & vbTab & CStr(Now))
Return Nothing
End Try
sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"
Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function
Simple but it does the job.
|||
Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?
Linked Server issue
I have a problem querying a linked server connected via the OleDB provider for ODBC.
This is the DataDirect 32-BIT SequeLink 5.4 client driver connecting to a FileMaker Pro 8 database. I've tested this in VB.net 2005 by adding an ODBC connection in server explorer and a data grid to a form. I can run the tooty and the data loads in the grid with no errors what so ever.
So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.
SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')
The result I get is this;
OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".
Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.
I also can't use four part naming with out getting an error as follows;
I give the statement;
SELECT * FROM DNA...Stock_DNA
and get this;
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".
Any help or work arounds would be greatly appreciated.
It sounds like the problem is not related to the ODBC connection string, but with the Linked servers that are defined within the SQL server itself. When you invoke the linked server query the actual schema does not match the schema stored by the linked server information.
I have seen this twice - and i had to use a different solution each time:
1) define an index on the table you are selecting from - sometimes SQL can get confused without one.
or
2) define a view for the data you want and select from the view with the ODBC connection string.
Hope this helps - let me know how you get on.
Mike
|||I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?
|||I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?
Here is my attempt to load a DataGridView with Filemaker Pro data:
string sConnString = "DRIVER={DataDirect 32-BIT SequeLink 5.4};SDSN=Sample10;HST=localHost;PRT=2399;UID=admin;";
odbcConnection1 = new OdbcConnection(sConnString);
try
{
odbcConnection1.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//connection opens okay
//The following doesn't work:
string strSQL = "select * from FirstName";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, odbcConnection1);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds, "FirstName"); //ERROR [HY000] Unable to retrieve error message from backend ODBC Driver.
dataGridView1.DataSource = ds.Tables["FirstName"];
|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||
Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.
Types in table that can be loaded:
VARCHAR
Types in table the cannot be loaded:
DOUBLE, VARCHAR, DATE
I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?
EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.
|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||
I'm at the same point as Brandon C...whose question seems to not have been really addressed.
To add to his post, if I try to use 4-part naming to query the linked server (FILEMAKER8), and just make up a schema name (dbschema) and run the query "SELECT Profiles.* FROM FILEMAKER8.dbschema..Profile Profiles", I get the following error:
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.
I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).
Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)
|||Just an FYI,I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.
Using the OdbcDataAdapter I am able to populate and return a DataSet regardless of the Data Types in the table.|||I think you are using filemaker as a linked server, if so ensure you relevant compatible drivers to perform the connection.|||
Yes, the DataDirect 32-BIT SequeLink 5.4 driver is the correct one for Filemaker. You should find the installer in your Filemaker folder ->\Extensions\xDBC Support\bin. Thanks for that tip, Brandon, I couldn't pull out some Filemaker data types, like DATE, for example.
|||Just a little more follow up.I had to create a connection for each table or 'FORM' in FileMaker.
The code I'm using return the data set would be similar to the following;
Code Snippet
<WebMethod(Description:="This method takes a stock number and returns the" & _
" record from the Stock_DNA table.")> _
Public Function ReturnStockDNARecord(ByVal StockNo As String) As DataSet
Dim sSQL As String = ""
Dim sConnect As String = ""
Dim oConnection As New Odbc.OdbcConnection
oConnection.ConnectionString = "DSN=ABRC_DNA;UID=SYSTEM;PWD=SYSTEM;SDSN=Stock_DNA"
Try
oConnection.Open()
Catch e As Exception
LogAppError(e.Message & vbTab & e.Source & vbTab & _
e.StackTrace & vbTab & CStr(Now))
Return Nothing
End Try
sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"
Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function
Simple but it does the job.
|||
Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?
|||I have a 3-part integration project:SQL 2005 -> Filemaker 8 -> SQL 2005
All parts ideally should work seamlessly. Part of my process is to run a query of data form SQL2005 and attempt to join in data from FileMaker. Unfortunately, I've never been able to get this to work via the FABULOUS drivers designed for supposedly just this sort of task. What I've ended up having to do is do all of my joining and subsequent querying in CODE (I'm using .NET as well, works fine). This is a less than ideal situation, especially as both the filemaker server and first sql2k5 server are on the same physical machine.
Linked Server issue
I have a problem querying a linked server connected via the OleDB provider for ODBC.
This is the DataDirect 32-BIT SequeLink 5.4 client driver connecting to a FileMaker Pro 8 database. I've tested this in VB.net 2005 by adding an ODBC connection in server explorer and a data grid to a form. I can run the tooty and the data loads in the grid with no errors what so ever.
So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.
SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')
The result I get is this;
OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".
Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.
I also can't use four part naming with out getting an error as follows;
I give the statement;
SELECT * FROM DNA...Stock_DNA
and get this;
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".
Any help or work arounds would be greatly appreciated.
It sounds like the problem is not related to the ODBC connection string, but with the Linked servers that are defined within the SQL server itself. When you invoke the linked server query the actual schema does not match the schema stored by the linked server information.
I have seen this twice - and i had to use a different solution each time:
1) define an index on the table you are selecting from - sometimes SQL can get confused without one.
or
2) define a view for the data you want and select from the view with the ODBC connection string.
Hope this helps - let me know how you get on.
Mike
|||I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?
|||I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?
Here is my attempt to load a DataGridView with Filemaker Pro data:
string sConnString = "DRIVER={DataDirect 32-BIT SequeLink 5.4};SDSN=Sample10;HST=localHost;PRT=2399;UID=admin;";
odbcConnection1 = new OdbcConnection(sConnString);
try
{
odbcConnection1.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//connection opens okay
//The following doesn't work:
string strSQL = "select * from FirstName";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, odbcConnection1);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds, "FirstName"); //ERROR [HY000] Unable to retrieve error message from backend ODBC Driver.
dataGridView1.DataSource = ds.Tables["FirstName"];
|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||
Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.
Types in table that can be loaded:
VARCHAR
Types in table the cannot be loaded:
DOUBLE, VARCHAR, DATE
I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?
EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.
|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||
I'm at the same point as Brandon C...whose question seems to not have been really addressed.
To add to his post, if I try to use 4-part naming to query the linked server (FILEMAKER8), and just make up a schema name (dbschema) and run the query "SELECT Profiles.* FROM FILEMAKER8.dbschema..Profile Profiles", I get the following error:
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.
I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).
Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)
|||Just an FYI,I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.
Using the OdbcDataAdapter I am able to populate and return a DataSet regardless of the Data Types in the table.|||I think you are using filemaker as a linked server, if so ensure you relevant compatible drivers to perform the connection.|||
Yes, the DataDirect 32-BIT SequeLink 5.4 driver is the correct one for Filemaker. You should find the installer in your Filemaker folder ->\Extensions\xDBC Support\bin. Thanks for that tip, Brandon, I couldn't pull out some Filemaker data types, like DATE, for example.
|||Just a little more follow up.I had to create a connection for each table or 'FORM' in FileMaker.
The code I'm using return the data set would be similar to the following;
Code Snippet
<WebMethod(Description:="This method takes a stock number and returns the" & _
" record from the Stock_DNA table.")> _
Public Function ReturnStockDNARecord(ByVal StockNo As String) As DataSet
Dim sSQL As String = ""
Dim sConnect As String = ""
Dim oConnection As New Odbc.OdbcConnection
oConnection.ConnectionString = "DSN=ABRC_DNA;UID=SYSTEM;PWD=SYSTEM;SDSN=Stock_DNA"
Try
oConnection.Open()
Catch e As Exception
LogAppError(e.Message & vbTab & e.Source & vbTab & _
e.StackTrace & vbTab & CStr(Now))
Return Nothing
End Try
sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"
Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function
Simple but it does the job.
|||
Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?
Subscribe to:
Posts (Atom)