Linking from SQL server to MySQL

Hi all, hoping someone can help.
I have created an ODBC connection to a remote MySQL database.
I can then go into DTS wizard and specify this ODBC connection, it all goes through fine and I can see the tables on the remote server and import data.
The problem arises when I try and create a linked server using the same ODBC connection. I get the following error, when I try to view the tables.
I am so stuck. Any help would be very much appreciated.My first guess would be a permissions issue in the MySQL login used within the DSN, or an IP permission problem in the MySQL login. I don't have any easy way to set up a test case this week, but those would be where I'd start looking.

-PatP|||Really don't know a lot about MySQL permissions so any more help would be great, so I can go back to are website designers with suggestions of what the problem might be on there end.|||The security and permissions for MySQL work a lot different than they do for SQL Server.

The IP address figures prominently into MySQL permissions. A login might have one set of permissions at one ip address or range (say inside the firewall), and drastically different permissions at others. If you ran your DTS package from a machine with an IP address that was "blessed", you could have one set of permissions, but when making a linked server connection on the SQL Server (which would have another address), the same MySQL id might have much less permission.

Another possibility is that the ODBC driver on the SQL Server might not be current. It has been relatively recent that the MySQL team has put any effort into making the ODBC drivers compatible with Windows 2003 Server.

The biggest problem at this point is that there are so many ways for the connection to go bad, and the MySQL ODBC drivers still aren't very good at giving usable diagnostics (as you've noticed!). The problem is almost certainly solvable, but without help from someone pretty technically knowledgable that understands both your NT and your MySQL configuration, it may take many tries to work out the problem and its solution.

-PatP

Linkedserver , Navision C/ODBC, Null columns Error

hello
i am having a few problems with Linkedserver and Navision Financials.
Most of the tables in navision (Customer, Prospect, Sales Line etc) have
DateTime columns that may contain nothing (null).
if i try to select these records i will get an error but i need access to
these columns so i can find out it the record has been updated recently.
the error is:
[Microsoft][ODBC Sql Server Driver][SQL Server]OLE DB error trace
[Non-interface error: Unexpected NULL value returned for the column:
ProviderName='MSDASQL', TableName'[Rowset_1]',ColumnName='Date_Completed'].
so how can i retrieve records with null columns? can i specify somewhere in
sql statements to ignore null columns or something? below is a sample sql
query.
also note that the funtion IFNULL dont work for me, i will still get an
error
example sql statement:
DBCC TRACEON (8765)
SELECT * FROM
OPENQUERY(NAVISION,'SELECT
* FROM Sales_Header WHERE Document_Type = ''Order'' ')
if i replace * with specific column names (No_ Name_ etc) it will work fine,
as long as Date_Completed isnt requested (in this case).
i am develping a navision client for users on the road (using XDA II ppc's)
that will connect over GPRS and webservices so this is why i require the
DateTime fields, so the user will know if the records are upto date in the
SqlCe database.
Regards,
Ricardo Meechan
I am having the exact same problem. Have you gotten any further in resolving this issue?
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Linkedserver , Navision C/ODBC, Null columns Error

hello
i am having a few problems with Linkedserver and Navision Financials.
Most of the tables in navision (Customer, Prospect, Sales Line etc) have
DateTime columns that may contain nothing (null).
if i try to select these records i will get an error but i need access to
these columns so i can find out it the record has been updated recently.
the error is:
[Microsoft][ODBC Sql Server Driver][SQL Server]OLE DB error trac
e
[Non-interface error: Unexpected NULL value returned for the column:
ProviderName='MSDASQL', TableName'& #91;Rowset_1]',ColumnName='Date_Complete
d
'].
so how can i retrieve records with null columns? can i specify somewhere in
sql statements to ignore null columns or something' below is a sample sql
query.
also note that the funtion IFNULL dont work for me, i will still get an
error
example sql statement:
DBCC TRACEON (8765)
SELECT * FROM
OPENQUERY(NAVISION,'SELECT
* FROM Sales_Header WHERE Document_Type = ''Order'' ')
if i replace * with specific column names (No_ Name_ etc) it will work fine,
as long as Date_Completed isnt requested (in this case).
i am develping a navision client for users on the road (using XDA II ppc's)
that will connect over GPRS and webservices so this is why i require the
DateTime fields, so the user will know if the records are upto date in the
SqlCe database.
Regards,
Ricardo MeechanI am having the exact same problem. Have you gotten any further in resolving
this issue?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Linked Tables in Microsoft Access

If SQL Server tables contained on a Server computer are used as linked table
s
in an Access database on a client computer via ODBC, and action queries
written in Access are run with Access specific functions and references to
controls on Access forms, is the processing done on the Server or the Client
?
Do you still run the risk of corrupting the "back-end" database if a write
operation is interrupted if you use tables in SQL Server database as linked
tables instead of using tables in an Access database as linked tables?> If SQL Server tables contained on a Server computer are used as linked
tables
> in an Access database on a client computer via ODBC, and action queries
> written in Access are run with Access specific functions and references to
> controls on Access forms, is the processing done on the Server or the Client?[/vbc
ol]
The processing works on the client. If your filter is written without
functions, the records to change will be gathered based on your filter. But
the routine actually executes one line at a time from the client.
[vbcol=seagreen]
> Do you still run the risk of corrupting the "back-end" database if a writ
e
> operation is interrupted if you use tables in SQL Server database as linke
d
> tables instead of using tables in an Access database as linked tables?
The transaction won't commit until the action query completes. If the
machine goes down then the transaction will rollback.

Linked Tables in Microsoft Access

If SQL Server tables contained on a Server computer are used as linked tables
in an Access database on a client computer via ODBC, and action queries
written in Access are run with Access specific functions and references to
controls on Access forms, is the processing done on the Server or the Client?
Do you still run the risk of corrupting the "back-end" database if a write
operation is interrupted if you use tables in SQL Server database as linked
tables instead of using tables in an Access database as linked tables?
> If SQL Server tables contained on a Server computer are used as linked
tables
> in an Access database on a client computer via ODBC, and action queries
> written in Access are run with Access specific functions and references to
> controls on Access forms, is the processing done on the Server or the Client?
The processing works on the client. If your filter is written without
functions, the records to change will be gathered based on your filter. But
the routine actually executes one line at a time from the client.

> Do you still run the risk of corrupting the "back-end" database if a write
> operation is interrupted if you use tables in SQL Server database as linked
> tables instead of using tables in an Access database as linked tables?
The transaction won't commit until the action query completes. If the
machine goes down then the transaction will rollback.

Linked Table Names

When linking tables from SQL 2K to Access 2K using ODBC, I
get the owner (usually dbo) as a qualifier. It appears
that this just started happening. If I do not disturb the
old link without a qualifir, I am usually OK, but if I
release a link and relink, the dbo_ comes in, and my
queries do not recognize the table. Can I get rid of the
qualifier, and why would this have started to occur?
Thanks.If you link the tables programmatically, you can set the
name of the linked table. You can find sample code in the
following article:
Relink ODBC tables from code
http://www.mvps.org/access/tables/tbl0010.htm
-Sue
On Thu, 5 Aug 2004 11:25:07 -0700, "W D Greene"
<anonymous@.discussions.microsoft.com> wrote:

>When linking tables from SQL 2K to Access 2K using ODBC, I
>get the owner (usually dbo) as a qualifier. It appears
>that this just started happening. If I do not disturb the
>old link without a qualifir, I am usually OK, but if I
>release a link and relink, the dbo_ comes in, and my
>queries do not recognize the table. Can I get rid of the
>qualifier, and why would this have started to occur?
>Thanks.

Linked Table Names

When linking tables from SQL 2K to Access 2K using ODBC, I
get the owner (usually dbo) as a qualifier. It appears
that this just started happening. If I do not disturb the
old link without a qualifir, I am usually OK, but if I
release a link and relink, the dbo_ comes in, and my
queries do not recognize the table. Can I get rid of the
qualifier, and why would this have started to occur?
Thanks.
If you link the tables programmatically, you can set the
name of the linked table. You can find sample code in the
following article:
Relink ODBC tables from code
http://www.mvps.org/access/tables/tbl0010.htm
-Sue
On Thu, 5 Aug 2004 11:25:07 -0700, "W D Greene"
<anonymous@.discussions.microsoft.com> wrote:

>When linking tables from SQL 2K to Access 2K using ODBC, I
>get the owner (usually dbo) as a qualifier. It appears
>that this just started happening. If I do not disturb the
>old link without a qualifir, I am usually OK, but if I
>release a link and relink, the dbo_ comes in, and my
>queries do not recognize the table. Can I get rid of the
>qualifier, and why would this have started to occur?
>Thanks.
sql

Linked SQL Server table in Access

Why does the ODBC Link Table utility prepend "dbo_" to each table I've
linked into an Access database? I realize dbo stands for database
owner, but why is this an issue? Can I get it to drop the "dbo_"?
Thanks
Max
You can link the tables in code, naming them yourself or
just rename the linked tables - it doesn't actually change
the table name, just the name seen by Access.
Access adds that as you can have two tables in SQL Server
with the same name and different owners. You can have a
dbo.Customers and someuser.Customers. Access adds the owner
to differentiate the objects.
-Sue
On Mon, 19 Dec 2005 12:47:39 -0500, Max Yaffe
<myaffe@.not.gamry.com> wrote:

>Why does the ODBC Link Table utility prepend "dbo_" to each table I've
>linked into an Access database? I realize dbo stands for database
>owner, but why is this an issue? Can I get it to drop the "dbo_"?
>Thanks
>Max
|||It's also really easy to just rename the table in Access if you need to.
Randall Arnold
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:tsleq1ha02a7ujoeo8oji2vo865g8m51bc@.4ax.com...
> You can link the tables in code, naming them yourself or
> just rename the linked tables - it doesn't actually change
> the table name, just the name seen by Access.
> Access adds that as you can have two tables in SQL Server
> with the same name and different owners. You can have a
> dbo.Customers and someuser.Customers. Access adds the owner
> to differentiate the objects.
> -Sue
> On Mon, 19 Dec 2005 12:47:39 -0500, Max Yaffe
> <myaffe@.not.gamry.com> wrote:
>

Linked SQL Server table in Access

Why does the ODBC Link Table utility prepend "dbo_" to each table I've
linked into an Access database? I realize dbo stands for database
owner, but why is this an issue? Can I get it to drop the "dbo_"?
Thanks
MaxYou can link the tables in code, naming them yourself or
just rename the linked tables - it doesn't actually change
the table name, just the name seen by Access.
Access adds that as you can have two tables in SQL Server
with the same name and different owners. You can have a
dbo.Customers and someuser.Customers. Access adds the owner
to differentiate the objects.
-Sue
On Mon, 19 Dec 2005 12:47:39 -0500, Max Yaffe
<myaffe@.not.gamry.com> wrote:

>Why does the ODBC Link Table utility prepend "dbo_" to each table I've
>linked into an Access database? I realize dbo stands for database
>owner, but why is this an issue? Can I get it to drop the "dbo_"?
>Thanks
>Max|||It's also really easy to just rename the table in Access if you need to.
Randall Arnold
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:tsleq1ha02a7ujoeo8oji2vo865g8m51bc@.
4ax.com...
> You can link the tables in code, naming them yourself or
> just rename the linked tables - it doesn't actually change
> the table name, just the name seen by Access.
> Access adds that as you can have two tables in SQL Server
> with the same name and different owners. You can have a
> dbo.Customers and someuser.Customers. Access adds the owner
> to differentiate the objects.
> -Sue
> On Mon, 19 Dec 2005 12:47:39 -0500, Max Yaffe
> <myaffe@.not.gamry.com> wrote:
>
>sql

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

servers to Lotus Notes

Hello,

I have successfully linked a Lotus Notes server to our SQL Server
database using an ODBC connection.
This works fine when wanting to select records
eg openquery(LOTUSNOTES2, 'select * from Person' )

The problem I have is when I try to update the record I get an error
eg
update openquery(LOTUSNOTES, 'select * from Person where
MailAddress=''nick.bloor@.sandh-ltd.com''' )
set JobTitle='Test'

Produces the following error
==========
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
===========

I have tested the ODBC connection using an Access database. The link
tables facilities enable me to select what I think should be the
primary key. If I select a primary key then I can use Access to update
the lotus notes database, however if I don'k select a primary key, the
table is not updateable.

I believe that if I can somehow create an index on the linked table
within SQL Server, I should be able to update the Lotus Notes database
from SQL Server, but I cannot find a way of doing so.

As the Lotus Notes software is third party, I cannot actually change
anything on that server.

Thanks for any help.

Regards

Nick BloorNickBlooruk (nickblooruk@.yahoo.com) writes:
> The problem I have is when I try to update the record I get an error
> eg
> update openquery(LOTUSNOTES, 'select * from Person where
> MailAddress=''nick.bloor@.sandh-ltd.com''' )
> set JobTitle='Test'

I don't know Lotus Notes, nor do I have much experience of updating
through OPENQUERY. But what happens if you move the WHERE to the
SQL Server side of things?

> I have tested the ODBC connection using an Access database. The link
> tables facilities enable me to select what I think should be the
> primary key. If I select a primary key then I can use Access to update
> the lotus notes database, however if I don'k select a primary key, the
> table is not updateable.

Is Person.MailAddress the primary key? If it's not it seems like a
difficult case.

> I believe that if I can somehow create an index on the linked table
> within SQL Server, I should be able to update the Lotus Notes database
> from SQL Server, but I cannot find a way of doing so.

You cannot create indexes on linked tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,
Thanks for your response.

Strictly speaking Lotus Notes is not a relational database. The Person
table therefore does not have a primary key.
There are a few records will null values in the MailAddress within
Lotus notes.
However the data I am interested in is unique within Lotus notes.

Access 2000 allows me to say that the MailAddress field is unique (even
though it isn't strictly). This allows me to update the Lotus Notes
table using Access' Query.

I cannot find a way of doing the same with MS SQL SERVER 2000, so the
table is currently not updateable.

I have tried using the WHERE on the SQL SERVER side
eg:
update openquery(LOTUSNOTES2, 'select * from Person ' )
set [JobTitle]='Test'
FROM
openquery(LOTUSNOTES2, 'select * from Person ' ) a
where a.MailAddress='NickBlooruk@.yahoo.com'

but the error is still the same.

Thanks for your advise.

Nick

Erland Sommarskog wrote:
> NickBlooruk (nickblooruk@.yahoo.com) writes:
> > The problem I have is when I try to update the record I get an
error
> > eg
> > update openquery(LOTUSNOTES, 'select * from Person where
> > MailAddress=''nick.bloor@.sandh-ltd.com''' )
> > set JobTitle='Test'
> I don't know Lotus Notes, nor do I have much experience of updating
> through OPENQUERY. But what happens if you move the WHERE to the
> SQL Server side of things?
> > I have tested the ODBC connection using an Access database. The
link
> > tables facilities enable me to select what I think should be the
> > primary key. If I select a primary key then I can use Access to
update
> > the lotus notes database, however if I don'k select a primary key,
the
> > table is not updateable.
> Is Person.MailAddress the primary key? If it's not it seems like a
> difficult case.
> > I believe that if I can somehow create an index on the linked table
> > within SQL Server, I should be able to update the Lotus Notes
database
> > from SQL Server, but I cannot find a way of doing so.
> You cannot create indexes on linked tables.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(nickblooruk@.yahoo.com) writes:
> Strictly speaking Lotus Notes is not a relational database. The Person
> table therefore does not have a primary key.

I kind of suspected that.

> Access 2000 allows me to say that the MailAddress field is unique (even
> though it isn't strictly). This allows me to update the Lotus Notes
> table using Access' Query.
> I cannot find a way of doing the same with MS SQL SERVER 2000, so the
> table is currently not updateable.

Access and SQL Server are not really the same sort of animals. Access
is a GUI and DBMS in once. SQL Server is only a DBMS.

Yet one more thing to try is something like:

UPDATE LOTUSNOTES2...Person
SET JobTitle = 'Test'
WHERE MailAdress = '...'

You would need to find out what to place between the dots in the four-
part notation, which is not always that easy.

> I have tried using the WHERE on the SQL SERVER side
> eg:
> update openquery(LOTUSNOTES2, 'select * from Person ' )
> set [JobTitle]='Test'
> FROM
> openquery(LOTUSNOTES2, 'select * from Person ' ) a
> where a.MailAddress='NickBlooruk@.yahoo.com'

I was more thinking of

update openquery(LOTUSNOTES2, 'select * from Person ' )
set [JobTitle]='Test'
FROM where MailAddress='NickBlooruk@.yahoo.com'

But I would guess you've already tried that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland for your help.

I have discovered that there is a unique reference number in Lotus
Notes called NoteID. This has all the characteristics of a primary
key, however as it is not a primary key in the strict sense of the
work as there is no built in integrity checks, I cannot update using
the openquery statement.

I think I will post a note on the lotus notes forum and also try to
find a different solution.

Thank you very much though

Regards

Nick Bloor.|||NickBlooruk (nickblooruk@.yahoo.com) writes:
> I have discovered that there is a unique reference number in Lotus
> Notes called NoteID. This has all the characteristics of a primary
> key, however as it is not a primary key in the strict sense of the
> work as there is no built in integrity checks, I cannot update using
> the openquery statement.
> I think I will post a note on the lotus notes forum and also try to
> find a different solution.

Good luck! :-)

I can only say that I'm glad that I don't have fight that battle.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

servers in SQL 2005.

The below works perfectly in SQL 2000.
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODBC.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. The
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/Brian
May check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:

> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, this
> dont work either)
> /Brian
>
|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com

servers in SQL 2005.

The below works perfectly in SQL 2000.
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODB
C.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. Th
e
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/BrianMay check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:

> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for O
DBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error.
The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, th
is
> dont work either)
> /Brian
>|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com

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.
:-)

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.
> :-)
>

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.
:-)

server via ODBC and stored procedure

Hi

We're trying to use call a stored procedure to update information in a
remote Ingres database. We've linked the server, and can read
information using SELECT * FROM OPENQUERY (........), but we can't
find a suitable syntax for executing a procedure.

Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
string gives a message about not returning any columns - not surprising
as there aren't any, and trying to execute the procedure more directly
using:-

EXECUTE abrs..vipdba.ats_reader_pi0 ......

Gives the error

Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
[OLE/DB provider returned message: Parameter type cannot be determined
for at least one variant parameter.]

Any bright ideas?

Chloe(chloe.crowder@.bl.uk) writes:
> We're trying to use call a stored procedure to update information in a
> remote Ingres database. We've linked the server, and can read
> information using SELECT * FROM OPENQUERY (........), but we can't
> find a suitable syntax for executing a procedure.
> Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
> string gives a message about not returning any columns - not surprising
> as there aren't any, and trying to execute the procedure more directly
> using:-
> EXECUTE abrs..vipdba.ats_reader_pi0 ......
> Gives the error
> Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
> [OLE/DB provider returned message: Parameter type cannot be determined
> for at least one variant parameter.]

Assuming that you are on SQL 2000:

Does the procedure have any "difficult" parameters?

First of all, I would examine whether there is an OLE DB provider
for Ingres, rather than using the MSDASQL provider.

If there is no OLE DB provider available, I would first try a parameterless
stored procedure. If this fails, then it seems that the ODBC driver
have problems to retrieve parameter information at all.

If there is a tool similar to Profiler on the Ingres side, you could
use that to see what calls the ODBC driver makes.

One thing that looks suspicious to me is that the third component is
empty, but I don't know Ingres, so this may be alright.

Unfortuantely, linked servers to other products can be a bit of trial
and error. There is a generic OLE DB layer which you have little control
over.

If you are on SQL 2005, there may be an easy way out. To wit you
can say:

EXEC('ingres-SQL here') AT abrs

to send a pass-through query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

server via ODBC - help!

Hi All
I have set up several linked SQL servers which work ok - but I'm having
trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
The ODBC points to an 'Alchemy' database and uses an Alchemy driver
ironically i can set up linked tables in an Access DB, via ODBC - and I can
pull the same data into Excel via ODBC - so there is no problem connecting t
o
Alchemy using other MS apps.
When I add the linked server in SQL, it appears to be added but no tables
are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
returns an error "Cannot initialize the data source object of OLE DB provide
r
'MSDASQL' "
Its driving me crazy...Anyone have any ideas'
Thanks in advanceMake sure the driver is compatible with the MDAC version on
the SQL Server box. Make sure you are using the latest
driver. Make sure the driver is correctly installed on the
SQL Server box.
Sometimes those issues can be related to issues specific to
the driver and interactions with service accounts. Try
logging onto the server using the service account for SQL
Server and install the driver while logged in under the
service account SQL Server is running under.
-Sue
On Thu, 28 Sep 2006 15:29:02 -0700, grasshopper
<nospam@.nospam.com> wrote:

>Hi All
>I have set up several linked SQL servers which work ok - but I'm having
>trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
>The ODBC points to an 'Alchemy' database and uses an Alchemy driver
>ironically i can set up linked tables in an Access DB, via ODBC - and I can
>pull the same data into Excel via ODBC - so there is no problem connecting
to
>Alchemy using other MS apps.
>When I add the linked server in SQL, it appears to be added but no tables
>are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
>returns an error "Cannot initialize the data source object of OLE DB provid
er
>'MSDASQL' "
>Its driving me crazy...Anyone have any ideas'
>Thanks in advance

server via ODBC - help!

Hi All
I have set up several linked SQL servers which work ok - but I'm having
trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
The ODBC points to an 'Alchemy' database and uses an Alchemy driver
ironically i can set up linked tables in an Access DB, via ODBC - and I can
pull the same data into Excel via ODBC - so there is no problem connecting to
Alchemy using other MS apps.
When I add the linked server in SQL, it appears to be added but no tables
are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
returns an error "Cannot initialize the data source object of OLE DB provider
'MSDASQL' "
Its driving me crazy...Anyone have any ideas?
Thanks in advance
Make sure the driver is compatible with the MDAC version on
the SQL Server box. Make sure you are using the latest
driver. Make sure the driver is correctly installed on the
SQL Server box.
Sometimes those issues can be related to issues specific to
the driver and interactions with service accounts. Try
logging onto the server using the service account for SQL
Server and install the driver while logged in under the
service account SQL Server is running under.
-Sue
On Thu, 28 Sep 2006 15:29:02 -0700, grasshopper
<nospam@.nospam.com> wrote:

>Hi All
>I have set up several linked SQL servers which work ok - but I'm having
>trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
>The ODBC points to an 'Alchemy' database and uses an Alchemy driver
>ironically i can set up linked tables in an Access DB, via ODBC - and I can
>pull the same data into Excel via ODBC - so there is no problem connecting to
>Alchemy using other MS apps.
>When I add the linked server in SQL, it appears to be added but no tables
>are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
>returns an error "Cannot initialize the data source object of OLE DB provider
>'MSDASQL' "
>Its driving me crazy...Anyone have any ideas?
>Thanks in advance

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?
>