Eric Sherman
asked on
Linked Server in SQL 2005 Query Problem???
I have the following query in M/S SQL Server 2005 Express which runs against a Linked MySQL Server. Nothing fancy just a select query that joins the product order details table with the work order table.
select* from openquery(linked_mysql, 'SELECT PRODUCT_ORDER_DETAILS.PROD UCTID, PRODUCT_ORDER_DETAILS.UNIT PRICE, PRODUCT_ORDER_DETAILS.QUAN TITY, WORK_ORDERS.* FROM WORK_ORDERS INNER JOIN PRODUCT_ORDER_DETAILS ON WORK_ORDERS.WO_ID = PRODUCT_ORDER_DETAILS.WO_I D')
I get the following error when it executes ...
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discard
Any help will be appreciated.
ET
select* from openquery(linked_mysql, 'SELECT PRODUCT_ORDER_DETAILS.PROD
I get the following error when it executes ...
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discard
Any help will be appreciated.
ET
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
EvilPostIt ....
I tried this ....
SELECT * FROM linked_mysql.localhost.MyS erverName. product_or der_detail s
and got this error ...
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "linked_mysql". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
ET
I tried this ....
SELECT * FROM linked_mysql.localhost.MyS
and got this error ...
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "linked_mysql". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
ET
>A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
as the error indicates, you cannot use the 4-dot-naming with MySQL providers so far.
as the error indicates, you cannot use the 4-dot-naming with MySQL providers so far.
ASKER
angelIII
The only field that would be the same between the two tables is the WO_ID but I'm not selecting it from the product_order_details tbl.
I will try your suggestion and select the individual fields from Work_Orders rather than the * to see if this makes a difference.
ET
The only field that would be the same between the two tables is the WO_ID but I'm not selecting it from the product_order_details tbl.
I will try your suggestion and select the individual fields from Work_Orders rather than the * to see if this makes a difference.
ET
Sorry havnt played with MySQL providers. I will let you 2 get on with it. :-)
ASKER
>>>>as the error indicates, you cannot use the 4-dot-naming with MySQL providers so far.<<<<<<
Well, that makes sense .... just a question on this method though ....
Using the 4-dot-naming and say if I was trying to connect to a remote server something like 11.111.111.11
How would you specify the remote server address (replacing the localhost) using the 4-dot-naming as shown below???
SELECT * FROM linked_mysql.localhost.MyS erverName. product_or der_detail s
Thanks,
ET
Well, that makes sense .... just a question on this method though ....
Using the 4-dot-naming and say if I was trying to connect to a remote server something like 11.111.111.11
How would you specify the remote server address (replacing the localhost) using the 4-dot-naming as shown below???
SELECT * FROM linked_mysql.localhost.MyS
Thanks,
ET
you can leave, with some providers, the part empty of what is not needed.
as the "linked_mysql" already specified to which server you connect, you don't need the info (ip address) at all any longer
as the "linked_mysql" already specified to which server you connect, you don't need the info (ip address) at all any longer
ASKER
Ok, that makes sense also ...
Regarding my original question .... When I specify the individual fields seems like the error is generated when I included fields of "LongText" from MySQL.
Any ideas on how to get around this as those are comment fields which will need to be included in the query.
Thanks,
ET
Regarding my original question .... When I specify the individual fields seems like the error is generated when I included fields of "LongText" from MySQL.
Any ideas on how to get around this as those are comment fields which will need to be included in the query.
Thanks,
ET
LongText sounds like "blob" ... can you cast that or use LEFT() function to get a shorter string?
ASKER
Yes, that can be done. I used this work around to accomplish what you suggested.
In the MySQL ODBC Data Source there is an option to "Limit column size to signed 32-bit range".
This worked and all seems to be ok.
Thanks for all your help.
ET
In the MySQL ODBC Data Source there is an option to "Limit column size to signed 32-bit range".
This worked and all seems to be ok.
Thanks for all your help.
ET
ASKER
Thanks and you were correct on the blob or longtext fields.
Seems like the M/S Provider is unable to handle data types longer than 32bit.
ET
Seems like the M/S Provider is unable to handle data types longer than 32bit.
ET
Open in new window
Do this error?