I'v setuped a Oracle8.16 linked server througth oracle oledb provider:
OraOLEDB.Oracle. When I use this linked server to get data, SqlServer
gave the following error message:
"OLE DB provider 'OraOLEDB.Oracle' reported an error. The provider did not give any information about the error."
But when I change the OraOLEDB.Oracle provider to MSDAORA,
none error occured!
Can somebody pls. guide me to resolve the issue?
many thanks in advance?Here is the code I used to create a linked server and then select data
set nocount on
go
declare @.server sysname
declare @.userid varchar(10)
declare @.pswd varchar(10)
set @.server = 'ATHENA_ORA'
set @.userid = 'system'
set @.pswd= 'manager'
exec sp_dropserver @.server = @.server , @.droplogins ='droplogins'
EXEC sp_addlinkedserver
@.server = @.server,
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'DBS_Athena'
exec sp_addlinkedsrvlogin @.server, 'FALSE', NULL, @.userid, @.pswd
exec sp_linkedservers
GO
SELECT * FROM ATHENA_ORA..SCOTT.DEPT
A couple of points, @.datasrc = 'DBS_Athena', 'DBS_Athena' is defined in my TNSNAMES.ORA, which was done by using Net8 or SQL*Net. Also I had to put the schema.tablename in uppercase. SELECT * FROM ATHENA_ORA..SCOTT.DEPT worked, but SELECT * FROM ATHENA_ORA..SCOTT.dept or SELECT * FROM ATHENA_ORA..scott.DEPT did not work.
Showing posts with label oledb. Show all posts
Showing posts with label oledb. Show all posts
Friday, March 23, 2012
linked servet to informix error with oledb
hello everybody
I set up a linked server with odbc for infomix
but cant make a linked server with oledb
and I cant insert into infomix tablesCan you share out what is your linked server object configuration in your front end server?sql
I set up a linked server with odbc for infomix
but cant make a linked server with oledb
and I cant insert into infomix tablesCan you share out what is your linked server object configuration in your front end server?sql
Wednesday, March 21, 2012
servers on SQL 2000 64 bit
Is it possible to set up a linked server in SQL Server
2000 64 bit to Oracle? I installed the 64 bit version of
the Oracle Provider for OLEDB and was able to set up a
linked server to point to it. I can retrieve the table
listing okay when I click on it in Enterprise Manager;
however, I receive the following error when I try to do
anything over the link - "Provider caused a server fault
in an external process."
From what I can tell, Microsoft supports linking to Oracle
using the Microsoft OLEDB Provider for Oracle or the
Microsoft OLEDB Provider for ODBC - the only problem with
this is it looks like these have not been ported to 64 bit.
Has anyone had any luck trying to do something like this?
Thanks for your help.EM is still a 32 bit app and might have issues dealing directly with a 64bit
Linked Server. How does it work through QA?
--
Andrew J. Kelly SQL MVP
"Jason Brenson" <jbrenson@.hycite.com> wrote in message
news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
> Is it possible to set up a linked server in SQL Server
> 2000 64 bit to Oracle? I installed the 64 bit version of
> the Oracle Provider for OLEDB and was able to set up a
> linked server to point to it. I can retrieve the table
> listing okay when I click on it in Enterprise Manager;
> however, I receive the following error when I try to do
> anything over the link - "Provider caused a server fault
> in an external process."
> From what I can tell, Microsoft supports linking to Oracle
> using the Microsoft OLEDB Provider for Oracle or the
> Microsoft OLEDB Provider for ODBC - the only problem with
> this is it looks like these have not been ported to 64 bit.
> Has anyone had any luck trying to do something like this?
> Thanks for your help.|||Andrew-
I have linked the server through QA and Enterprise
Manager - I get the OLEDB error when I try running a query
against the remote oracle database using QA. The Oracle 64
bit connection from the server works fine using Oracle's
SQL Plus Worksheet.
The Oracle Provider for OLEDB driver doesn't seem to work
very well on a 32 bit SQL server either - I can only get
consistent results using the Microsoft OLEDB Provider for
Oracle.
>--Original Message--
>EM is still a 32 bit app and might have issues dealing
directly with a 64bit
>Linked Server. How does it work through QA?
>--
>Andrew J. Kelly SQL MVP
>
>"Jason Brenson" <jbrenson@.hycite.com> wrote in message
>news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
>> Is it possible to set up a linked server in SQL Server
>> 2000 64 bit to Oracle? I installed the 64 bit version of
>> the Oracle Provider for OLEDB and was able to set up a
>> linked server to point to it. I can retrieve the table
>> listing okay when I click on it in Enterprise Manager;
>> however, I receive the following error when I try to do
>> anything over the link - "Provider caused a server fault
>> in an external process."
>> From what I can tell, Microsoft supports linking to
Oracle
>> using the Microsoft OLEDB Provider for Oracle or the
>> Microsoft OLEDB Provider for ODBC - the only problem
with
>> this is it looks like these have not been ported to 64
bit.
>> Has anyone had any luck trying to do something like
this?
>> Thanks for your help.
>
>.
>
2000 64 bit to Oracle? I installed the 64 bit version of
the Oracle Provider for OLEDB and was able to set up a
linked server to point to it. I can retrieve the table
listing okay when I click on it in Enterprise Manager;
however, I receive the following error when I try to do
anything over the link - "Provider caused a server fault
in an external process."
From what I can tell, Microsoft supports linking to Oracle
using the Microsoft OLEDB Provider for Oracle or the
Microsoft OLEDB Provider for ODBC - the only problem with
this is it looks like these have not been ported to 64 bit.
Has anyone had any luck trying to do something like this?
Thanks for your help.EM is still a 32 bit app and might have issues dealing directly with a 64bit
Linked Server. How does it work through QA?
--
Andrew J. Kelly SQL MVP
"Jason Brenson" <jbrenson@.hycite.com> wrote in message
news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
> Is it possible to set up a linked server in SQL Server
> 2000 64 bit to Oracle? I installed the 64 bit version of
> the Oracle Provider for OLEDB and was able to set up a
> linked server to point to it. I can retrieve the table
> listing okay when I click on it in Enterprise Manager;
> however, I receive the following error when I try to do
> anything over the link - "Provider caused a server fault
> in an external process."
> From what I can tell, Microsoft supports linking to Oracle
> using the Microsoft OLEDB Provider for Oracle or the
> Microsoft OLEDB Provider for ODBC - the only problem with
> this is it looks like these have not been ported to 64 bit.
> Has anyone had any luck trying to do something like this?
> Thanks for your help.|||Andrew-
I have linked the server through QA and Enterprise
Manager - I get the OLEDB error when I try running a query
against the remote oracle database using QA. The Oracle 64
bit connection from the server works fine using Oracle's
SQL Plus Worksheet.
The Oracle Provider for OLEDB driver doesn't seem to work
very well on a 32 bit SQL server either - I can only get
consistent results using the Microsoft OLEDB Provider for
Oracle.
>--Original Message--
>EM is still a 32 bit app and might have issues dealing
directly with a 64bit
>Linked Server. How does it work through QA?
>--
>Andrew J. Kelly SQL MVP
>
>"Jason Brenson" <jbrenson@.hycite.com> wrote in message
>news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
>> Is it possible to set up a linked server in SQL Server
>> 2000 64 bit to Oracle? I installed the 64 bit version of
>> the Oracle Provider for OLEDB and was able to set up a
>> linked server to point to it. I can retrieve the table
>> listing okay when I click on it in Enterprise Manager;
>> however, I receive the following error when I try to do
>> anything over the link - "Provider caused a server fault
>> in an external process."
>> From what I can tell, Microsoft supports linking to
Oracle
>> using the Microsoft OLEDB Provider for Oracle or the
>> Microsoft OLEDB Provider for ODBC - the only problem
with
>> this is it looks like these have not been ported to 64
bit.
>> Has anyone had any luck trying to do something like
this?
>> Thanks for your help.
>
>.
>
servers on SQL 2000 64 bit
Is it possible to set up a linked server in SQL Server
2000 64 bit to Oracle? I installed the 64 bit version of
the Oracle Provider for OLEDB and was able to set up a
linked server to point to it. I can retrieve the table
listing okay when I click on it in Enterprise Manager;
however, I receive the following error when I try to do
anything over the link - "Provider caused a server fault
in an external process."
From what I can tell, Microsoft supports linking to Oracle
using the Microsoft OLEDB Provider for Oracle or the
Microsoft OLEDB Provider for ODBC - the only problem with
this is it looks like these have not been ported to 64 bit.
Has anyone had any luck trying to do something like this?
Thanks for your help.
EM is still a 32 bit app and might have issues dealing directly with a 64bit
Linked Server. How does it work through QA?
Andrew J. Kelly SQL MVP
"Jason Brenson" <jbrenson@.hycite.com> wrote in message
news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
> Is it possible to set up a linked server in SQL Server
> 2000 64 bit to Oracle? I installed the 64 bit version of
> the Oracle Provider for OLEDB and was able to set up a
> linked server to point to it. I can retrieve the table
> listing okay when I click on it in Enterprise Manager;
> however, I receive the following error when I try to do
> anything over the link - "Provider caused a server fault
> in an external process."
> From what I can tell, Microsoft supports linking to Oracle
> using the Microsoft OLEDB Provider for Oracle or the
> Microsoft OLEDB Provider for ODBC - the only problem with
> this is it looks like these have not been ported to 64 bit.
> Has anyone had any luck trying to do something like this?
> Thanks for your help.
|||Andrew-
I have linked the server through QA and Enterprise
Manager - I get the OLEDB error when I try running a query
against the remote oracle database using QA. The Oracle 64
bit connection from the server works fine using Oracle's
SQL Plus Worksheet.
The Oracle Provider for OLEDB driver doesn't seem to work
very well on a 32 bit SQL server either - I can only get
consistent results using the Microsoft OLEDB Provider for
Oracle.
>--Original Message--
>EM is still a 32 bit app and might have issues dealing
directly with a 64bit[vbcol=seagreen]
>Linked Server. How does it work through QA?
>--
>Andrew J. Kelly SQL MVP
>
>"Jason Brenson" <jbrenson@.hycite.com> wrote in message
>news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
Oracle[vbcol=seagreen]
with[vbcol=seagreen]
bit.[vbcol=seagreen]
this?
>
>.
>
2000 64 bit to Oracle? I installed the 64 bit version of
the Oracle Provider for OLEDB and was able to set up a
linked server to point to it. I can retrieve the table
listing okay when I click on it in Enterprise Manager;
however, I receive the following error when I try to do
anything over the link - "Provider caused a server fault
in an external process."
From what I can tell, Microsoft supports linking to Oracle
using the Microsoft OLEDB Provider for Oracle or the
Microsoft OLEDB Provider for ODBC - the only problem with
this is it looks like these have not been ported to 64 bit.
Has anyone had any luck trying to do something like this?
Thanks for your help.
EM is still a 32 bit app and might have issues dealing directly with a 64bit
Linked Server. How does it work through QA?
Andrew J. Kelly SQL MVP
"Jason Brenson" <jbrenson@.hycite.com> wrote in message
news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
> Is it possible to set up a linked server in SQL Server
> 2000 64 bit to Oracle? I installed the 64 bit version of
> the Oracle Provider for OLEDB and was able to set up a
> linked server to point to it. I can retrieve the table
> listing okay when I click on it in Enterprise Manager;
> however, I receive the following error when I try to do
> anything over the link - "Provider caused a server fault
> in an external process."
> From what I can tell, Microsoft supports linking to Oracle
> using the Microsoft OLEDB Provider for Oracle or the
> Microsoft OLEDB Provider for ODBC - the only problem with
> this is it looks like these have not been ported to 64 bit.
> Has anyone had any luck trying to do something like this?
> Thanks for your help.
|||Andrew-
I have linked the server through QA and Enterprise
Manager - I get the OLEDB error when I try running a query
against the remote oracle database using QA. The Oracle 64
bit connection from the server works fine using Oracle's
SQL Plus Worksheet.
The Oracle Provider for OLEDB driver doesn't seem to work
very well on a 32 bit SQL server either - I can only get
consistent results using the Microsoft OLEDB Provider for
Oracle.
>--Original Message--
>EM is still a 32 bit app and might have issues dealing
directly with a 64bit[vbcol=seagreen]
>Linked Server. How does it work through QA?
>--
>Andrew J. Kelly SQL MVP
>
>"Jason Brenson" <jbrenson@.hycite.com> wrote in message
>news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
Oracle[vbcol=seagreen]
with[vbcol=seagreen]
bit.[vbcol=seagreen]
this?
>
>.
>
servers on SQL 2000 64 bit
Is it possible to set up a linked server in SQL Server
2000 64 bit to Oracle? I installed the 64 bit version of
the Oracle Provider for OLEDB and was able to set up a
linked server to point to it. I can retrieve the table
listing okay when I click on it in Enterprise Manager;
however, I receive the following error when I try to do
anything over the link - "Provider caused a server fault
in an external process."
From what I can tell, Microsoft supports linking to Oracle
using the Microsoft OLEDB Provider for Oracle or the
Microsoft OLEDB Provider for ODBC - the only problem with
this is it looks like these have not been ported to 64 bit.
Has anyone had any luck trying to do something like this?
Thanks for your help.EM is still a 32 bit app and might have issues dealing directly with a 64bit
Linked Server. How does it work through QA?
Andrew J. Kelly SQL MVP
"Jason Brenson" <jbrenson@.hycite.com> wrote in message
news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
> Is it possible to set up a linked server in SQL Server
> 2000 64 bit to Oracle? I installed the 64 bit version of
> the Oracle Provider for OLEDB and was able to set up a
> linked server to point to it. I can retrieve the table
> listing okay when I click on it in Enterprise Manager;
> however, I receive the following error when I try to do
> anything over the link - "Provider caused a server fault
> in an external process."
> From what I can tell, Microsoft supports linking to Oracle
> using the Microsoft OLEDB Provider for Oracle or the
> Microsoft OLEDB Provider for ODBC - the only problem with
> this is it looks like these have not been ported to 64 bit.
> Has anyone had any luck trying to do something like this?
> Thanks for your help.|||Andrew-
I have linked the server through QA and Enterprise
Manager - I get the OLEDB error when I try running a query
against the remote oracle database using QA. The Oracle 64
bit connection from the server works fine using Oracle's
SQL Plus Worksheet.
The Oracle Provider for OLEDB driver doesn't seem to work
very well on a 32 bit SQL server either - I can only get
consistent results using the Microsoft OLEDB Provider for
Oracle.
>--Original Message--
>EM is still a 32 bit app and might have issues dealing
directly with a 64bit
>Linked Server. How does it work through QA?
>--
>Andrew J. Kelly SQL MVP
>
>"Jason Brenson" <jbrenson@.hycite.com> wrote in message
>news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
Oracle[vbcol=seagreen]
with[vbcol=seagreen]
bit.[vbcol=seagreen]
this?[vbcol=seagreen]
>
>.
>sql
2000 64 bit to Oracle? I installed the 64 bit version of
the Oracle Provider for OLEDB and was able to set up a
linked server to point to it. I can retrieve the table
listing okay when I click on it in Enterprise Manager;
however, I receive the following error when I try to do
anything over the link - "Provider caused a server fault
in an external process."
From what I can tell, Microsoft supports linking to Oracle
using the Microsoft OLEDB Provider for Oracle or the
Microsoft OLEDB Provider for ODBC - the only problem with
this is it looks like these have not been ported to 64 bit.
Has anyone had any luck trying to do something like this?
Thanks for your help.EM is still a 32 bit app and might have issues dealing directly with a 64bit
Linked Server. How does it work through QA?
Andrew J. Kelly SQL MVP
"Jason Brenson" <jbrenson@.hycite.com> wrote in message
news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
> Is it possible to set up a linked server in SQL Server
> 2000 64 bit to Oracle? I installed the 64 bit version of
> the Oracle Provider for OLEDB and was able to set up a
> linked server to point to it. I can retrieve the table
> listing okay when I click on it in Enterprise Manager;
> however, I receive the following error when I try to do
> anything over the link - "Provider caused a server fault
> in an external process."
> From what I can tell, Microsoft supports linking to Oracle
> using the Microsoft OLEDB Provider for Oracle or the
> Microsoft OLEDB Provider for ODBC - the only problem with
> this is it looks like these have not been ported to 64 bit.
> Has anyone had any luck trying to do something like this?
> Thanks for your help.|||Andrew-
I have linked the server through QA and Enterprise
Manager - I get the OLEDB error when I try running a query
against the remote oracle database using QA. The Oracle 64
bit connection from the server works fine using Oracle's
SQL Plus Worksheet.
The Oracle Provider for OLEDB driver doesn't seem to work
very well on a 32 bit SQL server either - I can only get
consistent results using the Microsoft OLEDB Provider for
Oracle.
>--Original Message--
>EM is still a 32 bit app and might have issues dealing
directly with a 64bit
>Linked Server. How does it work through QA?
>--
>Andrew J. Kelly SQL MVP
>
>"Jason Brenson" <jbrenson@.hycite.com> wrote in message
>news:038e01c470d8$e28aa380$3a01280a@.phx.gbl...
Oracle[vbcol=seagreen]
with[vbcol=seagreen]
bit.[vbcol=seagreen]
this?[vbcol=seagreen]
>
>.
>sql
Friday, March 9, 2012
server, oledb for odbc, slow performance on join to cache
I have a sql linked server pointed to a cache database. I created it
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
..
Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors') a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>
|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
..
Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors') a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>
|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)
server, oledb for odbc, slow performance on join to cache
I have a sql linked server pointed to a cache database. I created it
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
.Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors') a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)|||Hi
I did not see an example of the first query in your previous post!
You may also want to specify only the column names required instead of (*)
which will reduce the size of the data being retrieved
SELECT X.ID, X.Col1, X.Col2
FROM OPENQUERY(Cache_test01, 'SELECT ID, col1, col2 FROM Table01 WHERE
ID=22 ') AS X
JOIN dbo.tblTable01AddOn A ON X.ID = A.ID
Have you tried using a temporary table to store the data from the openquery?
If the openquery can be restricted that should be better, although it may
mean resorting to dynamic SQL.
John
"steven@.ironcube.com" wrote:
> Hi John,
> I got posts all over this group this week as well as the cache. Sorry,
> I didn't see that anyone got back to me on this one. I actually have
> tried both syntax you mentioned above. I saw the same from both of
> them. Very slow.
> I contacted cache over the weekend and I'm hoping to see an email from
> them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
> I'm going to post back just so other in the same boat know what
> happened.
> Thanks for taking the time to post. I appreciate it. Have a good one.
> :-)
>
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
.Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors') a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)|||Hi
I did not see an example of the first query in your previous post!
You may also want to specify only the column names required instead of (*)
which will reduce the size of the data being retrieved
SELECT X.ID, X.Col1, X.Col2
FROM OPENQUERY(Cache_test01, 'SELECT ID, col1, col2 FROM Table01 WHERE
ID=22 ') AS X
JOIN dbo.tblTable01AddOn A ON X.ID = A.ID
Have you tried using a temporary table to store the data from the openquery?
If the openquery can be restricted that should be better, although it may
mean resorting to dynamic SQL.
John
"steven@.ironcube.com" wrote:
> Hi John,
> I got posts all over this group this week as well as the cache. Sorry,
> I didn't see that anyone got back to me on this one. I actually have
> tried both syntax you mentioned above. I saw the same from both of
> them. Very slow.
> I contacted cache over the weekend and I'm hoping to see an email from
> them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
> I'm going to post back just so other in the same boat know what
> happened.
> Thanks for taking the time to post. I appreciate it. Have a good one.
> :-)
>
server, oledb for odbc, slow performance on join to cache
I have a sql linked server pointed to a cache database. I created it
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
.Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors')
a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
.Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors')
a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)
Wednesday, March 7, 2012
server using the OLEDB for ODBC Driver
I have an ODBC driver to a non-SQL database that contains legacy data that I
need to reference in my application/reports. I had originally hoped to use a
linked server using the OLEDB for ODBC provider to access the data and so be
able to create applications/reports using data stored in SQL Server 2000 and
the legacy database via a single connection.
The problem is that, according to the ODBC drive vendor, when I use the 4
part name to refer to my linked server, SQL Server only passes the driver
the base select statement without any where clause resulting in the entire
table being returned to SQL Server which then applies the filter. This gives
me an average time of 8 minutes to return a single record of a 46,000 row
table.
If I use the OPENQUERY function the same select statement takes about 2
seconds. Unfortunately though, OPENQUERY does not accept a variables as a
parameter and so the select statement must be a hard coded string which
makes it unsuitable for any but a static view.
Any suggestions on a workaround for this?You can build the entire SQL string, e.g. select * from
openquery(server, 'etc...') , and pass the string into an
EXEC(). You can find examples in this article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On Wed, 11 Aug 2004 12:37:48 -0500, "Charles J Ryan"
<charlesryan1@.msn.com> wrote:
>I have an ODBC driver to a non-SQL database that contains legacy data that
I
>need to reference in my application/reports. I had originally hoped to use
a
>linked server using the OLEDB for ODBC provider to access the data and so b
e
>able to create applications/reports using data stored in SQL Server 2000 an
d
>the legacy database via a single connection.
>The problem is that, according to the ODBC drive vendor, when I use the 4
>part name to refer to my linked server, SQL Server only passes the driver
>the base select statement without any where clause resulting in the entire
>table being returned to SQL Server which then applies the filter. This give
s
>me an average time of 8 minutes to return a single record of a 46,000 row
>table.
>If I use the OPENQUERY function the same select statement takes about 2
>seconds. Unfortunately though, OPENQUERY does not accept a variables as a
>parameter and so the select statement must be a hard coded string which
>makes it unsuitable for any but a static view.
>Any suggestions on a workaround for this?
>
need to reference in my application/reports. I had originally hoped to use a
linked server using the OLEDB for ODBC provider to access the data and so be
able to create applications/reports using data stored in SQL Server 2000 and
the legacy database via a single connection.
The problem is that, according to the ODBC drive vendor, when I use the 4
part name to refer to my linked server, SQL Server only passes the driver
the base select statement without any where clause resulting in the entire
table being returned to SQL Server which then applies the filter. This gives
me an average time of 8 minutes to return a single record of a 46,000 row
table.
If I use the OPENQUERY function the same select statement takes about 2
seconds. Unfortunately though, OPENQUERY does not accept a variables as a
parameter and so the select statement must be a hard coded string which
makes it unsuitable for any but a static view.
Any suggestions on a workaround for this?You can build the entire SQL string, e.g. select * from
openquery(server, 'etc...') , and pass the string into an
EXEC(). You can find examples in this article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On Wed, 11 Aug 2004 12:37:48 -0500, "Charles J Ryan"
<charlesryan1@.msn.com> wrote:
>I have an ODBC driver to a non-SQL database that contains legacy data that
I
>need to reference in my application/reports. I had originally hoped to use
a
>linked server using the OLEDB for ODBC provider to access the data and so b
e
>able to create applications/reports using data stored in SQL Server 2000 an
d
>the legacy database via a single connection.
>The problem is that, according to the ODBC drive vendor, when I use the 4
>part name to refer to my linked server, SQL Server only passes the driver
>the base select statement without any where clause resulting in the entire
>table being returned to SQL Server which then applies the filter. This give
s
>me an average time of 8 minutes to return a single record of a 46,000 row
>table.
>If I use the OPENQUERY function the same select statement takes about 2
>seconds. Unfortunately though, OPENQUERY does not accept a variables as a
>parameter and so the select statement must be a hard coded string which
>makes it unsuitable for any but a static view.
>Any suggestions on a workaround for this?
>
server using the OLEDB for ODBC Driver
I have an ODBC driver to a non-SQL database that contains legacy data that I
need to reference in my application/reports. I had originally hoped to use a
linked server using the OLEDB for ODBC provider to access the data and so be
able to create applications/reports using data stored in SQL Server 2000 and
the legacy database via a single connection.
The problem is that, according to the ODBC drive vendor, when I use the 4
part name to refer to my linked server, SQL Server only passes the driver
the base select statement without any where clause resulting in the entire
table being returned to SQL Server which then applies the filter. This gives
me an average time of 8 minutes to return a single record of a 46,000 row
table.
If I use the OPENQUERY function the same select statement takes about 2
seconds. Unfortunately though, OPENQUERY does not accept a variables as a
parameter and so the select statement must be a hard coded string which
makes it unsuitable for any but a static view.
Any suggestions on a workaround for this?
You can build the entire SQL string, e.g. select * from
openquery(server, 'etc...') , and pass the string into an
EXEC(). You can find examples in this article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On Wed, 11 Aug 2004 12:37:48 -0500, "Charles J Ryan"
<charlesryan1@.msn.com> wrote:
>I have an ODBC driver to a non-SQL database that contains legacy data that I
>need to reference in my application/reports. I had originally hoped to use a
>linked server using the OLEDB for ODBC provider to access the data and so be
>able to create applications/reports using data stored in SQL Server 2000 and
>the legacy database via a single connection.
>The problem is that, according to the ODBC drive vendor, when I use the 4
>part name to refer to my linked server, SQL Server only passes the driver
>the base select statement without any where clause resulting in the entire
>table being returned to SQL Server which then applies the filter. This gives
>me an average time of 8 minutes to return a single record of a 46,000 row
>table.
>If I use the OPENQUERY function the same select statement takes about 2
>seconds. Unfortunately though, OPENQUERY does not accept a variables as a
>parameter and so the select statement must be a hard coded string which
>makes it unsuitable for any but a static view.
>Any suggestions on a workaround for this?
>
need to reference in my application/reports. I had originally hoped to use a
linked server using the OLEDB for ODBC provider to access the data and so be
able to create applications/reports using data stored in SQL Server 2000 and
the legacy database via a single connection.
The problem is that, according to the ODBC drive vendor, when I use the 4
part name to refer to my linked server, SQL Server only passes the driver
the base select statement without any where clause resulting in the entire
table being returned to SQL Server which then applies the filter. This gives
me an average time of 8 minutes to return a single record of a 46,000 row
table.
If I use the OPENQUERY function the same select statement takes about 2
seconds. Unfortunately though, OPENQUERY does not accept a variables as a
parameter and so the select statement must be a hard coded string which
makes it unsuitable for any but a static view.
Any suggestions on a workaround for this?
You can build the entire SQL string, e.g. select * from
openquery(server, 'etc...') , and pass the string into an
EXEC(). You can find examples in this article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On Wed, 11 Aug 2004 12:37:48 -0500, "Charles J Ryan"
<charlesryan1@.msn.com> wrote:
>I have an ODBC driver to a non-SQL database that contains legacy data that I
>need to reference in my application/reports. I had originally hoped to use a
>linked server using the OLEDB for ODBC provider to access the data and so be
>able to create applications/reports using data stored in SQL Server 2000 and
>the legacy database via a single connection.
>The problem is that, according to the ODBC drive vendor, when I use the 4
>part name to refer to my linked server, SQL Server only passes the driver
>the base select statement without any where clause resulting in the entire
>table being returned to SQL Server which then applies the filter. This gives
>me an average time of 8 minutes to return a single record of a 46,000 row
>table.
>If I use the OPENQUERY function the same select statement takes about 2
>seconds. Unfortunately though, OPENQUERY does not accept a variables as a
>parameter and so the select statement must be a hard coded string which
>makes it unsuitable for any but a static view.
>Any suggestions on a workaround for this?
>
Friday, February 24, 2012
server to MYSQL using OLEDB Provider for MYSQL cherry
Good Morning
Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.
I can not get it to work.
I've created a UDL which tests fine. it looks like this
[oledb]
; Everything after this line is an OLE DB initstring
Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;
Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";
Initial Catalog=riverford_rhdx_20060822
Can any on help me convert this to corrrect syntax for sql stored procedure
sp_addlinkedserver
I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.
I used SQL server management studio to create the linked server then just scripted this out below.
I seem to be missing the user ID, but don't know where to put it in.
EXEC master.dbo.sp_addlinkedserver @.server = N'DATABRIDGE_OLEDB', @.srvproduct=N'mysql', @.provider=N'OleMySql.MySqlSource', @.datasrc=N'databridge', @.catalog=N'riverford_rhdx_20060822'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'rpc', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'rpc out', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'use remote collation', @.optvalue=N'false'
Many Thanks
David Hills
Have you tried to include password to initstring?|||No I have not, as there is no password set for testuser in the mysql database.|||
Have you tried to include user id like this @.UID='<my id>'? It shall work with MySQL OLE DB Provider|||
I got a reply from the software provider "cherry" they told me it won't work with
sqlserver 2005 as a linked server.
It should be quite straight forward to write a .net vb applet that uses the .net provider for mysql to
get the data out of mysql server, then use ado.net to write it into sqlserver 2005.
But what I wanted to do is to contain the code within sqlserver management studio so I don't
have external code.
Anyone know if I can write a VB.net or C## .net applet from with sqlserver 2005. It seems it's
the sort of intergrated solution that would be convient to be able to do?
|||I've setup a MySQL linked server in SQL 2005 using the ODBC driver for MySQL, and then using the OLEDB Provider for ODBC. Would you be apposed to doing it that way?server to MYSQL using OLEDB Provider for MYSQL cherry
Good Morning
Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.
I can not get it to work.
I've created a UDL which tests fine. it looks like this
[oledb]
; Everything after this line is an OLE DB initstring
Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;
Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";
Initial Catalog=riverford_rhdx_20060822
Can any on help me convert this to corrrect syntax for sql stored procedure
sp_addlinkedserver
I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.
I used SQL server management studio to create the linked server then just scripted this out below.
I seem to be missing the user ID, but don't know where to put it in.
EXEC master.dbo.sp_addlinkedserver @.server = N'DATABRIDGE_OLEDB', @.srvproduct=N'mysql', @.provider=N'OleMySql.MySqlSource', @.datasrc=N'databridge', @.catalog=N'riverford_rhdx_20060822'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'rpc', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'rpc out', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'use remote collation', @.optvalue=N'false'
Many Thanks
David Hills
Have you tried to include password to initstring?|||No I have not, as there is no password set for testuser in the mysql database.|||Have you tried to include user id like this @.UID='<my id>'? It shall work with MySQL OLE DB Provider|||
I got a reply from the software provider "cherry" they told me it won't work with
sqlserver 2005 as a linked server.
It should be quite straight forward to write a .net vb applet that uses the .net provider for mysql to
get the data out of mysql server, then use ado.net to write it into sqlserver 2005.
But what I wanted to do is to contain the code within sqlserver management studio so I don't
have external code.
Anyone know if I can write a VB.net or C## .net applet from with sqlserver 2005. It seems it's
the sort of intergrated solution that would be convient to be able to do?
|||I've setup a MySQL linked server in SQL 2005 using the ODBC driver for MySQL, and then using the OLEDB Provider for ODBC. Would you be apposed to doing it that way?
Subscribe to:
Posts (Atom)