Solved

Linked Server in SQL 2005 Query Problem???

Posted on 2010-11-17
12
921 Views
Last Modified: 2012-05-10
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.PRODUCTID, PRODUCT_ORDER_DETAILS.UNITPRICE, PRODUCT_ORDER_DETAILS.QUANTITY, WORK_ORDERS.* FROM WORK_ORDERS INNER JOIN PRODUCT_ORDER_DETAILS ON WORK_ORDERS.WO_ID = PRODUCT_ORDER_DETAILS.WO_ID')


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
0
Comment
Question by:Eric Sherman
  • 6
  • 4
  • 2
12 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34154596
Try doing a direct select. When you have a linked server you dont need to use openquery.

SELECT * FROM LinkedServer.Database.Schema.Table

Open in new window


Do this error?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34154597
I see 2 potential issues:

the WORK_ORDERS.* could have a column name which is the same as for those for PRODUCT_ORDER_DETAILS that you name explicitly. don't use * here, but specify the columns explicitely, and make sure there are no duplicate column names returned.

there might be columns having data types that are not "understood" correctly by the linked server provider...
in which case, you might try to change the provider, or make sure the data types returned are "understood".
from oracle, I remember the following data types being a problem: BLOB, CLOB, etc, TIMESTAMP, NUMBER without size specification ...

in MySQL, it could be BLOB ...
what data types have your columns in the 2 tables ?

0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 34154706
EvilPostIt ....

I tried this ....

SELECT * FROM linked_mysql.localhost.MyServerName.product_order_details

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

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34154726
>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.
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 34154729
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
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34154797
Sorry havnt played with MySQL providers. I will let you 2 get on with it. :-)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Author Comment

by:Eric Sherman
ID: 34154803
>>>>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.MyServerName.product_order_details


Thanks,

ET
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34154948
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
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 34155151
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34155335
LongText sounds like "blob" ... can you cast that or use LEFT() function to get a shorter string?
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 34155493
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
0
 
LVL 19

Author Closing Comment

by:Eric Sherman
ID: 34155547
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now