Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
MS SQL Linked Server using Open Query causing catastrophic failure
Here is my query:
Select AQ.hwt_user, AQ.Cust_Div_Def_Definit,
AQ.hwt_action, AQ.hwt_entity_id,
AQ.Order_H_PO, AQ.FirstName + ' ' + AQ.LastName as UserName,
AQ.hwt_part_number, AQ.hwt_location_id_wasp,
AQ.hwt_asset_tag, AQ.hwt_serial_number,
AQ.hwt_quantity, AQ.hwt_create_date,
AQ.hwt_create_time, AQ.hwt_entry_date,
AQ.hwt_entry_time, AQ.hwt_processed,
AQ.Prod_Description, location.code as location_code
From
OPENQUERY(CommPlete,
'Select hh_warehouse_trans.hwt_use
hh_warehouse_trans.hwt_act
Order_Header.Order_H_PO,
Rtrim(User_Code_Table.User
hh_warehouse_trans.hwt_par
hh_warehouse_trans.hwt_ass
hh_warehouse_trans.hwt_qua
hh_warehouse_trans.hwt_cre
hh_warehouse_trans.hwt_ent
Product_Catalog.Prod_Descr
From hh_warehouse_trans
Inner Join Customer_Division_De
On Customer_Division_De.Cust_
And Customer_Division_De.Cust_
Inner Join Product_Catalog
On Product_Catalog.Prod_Custo
And Product_Catalog.Prod_Custo
And Product_Catalog.Prod_Mfr_P
Left Outer Join Order_Header
On Order_Header.Order_H_Order
Inner Join User_Code_Table
On User_Code_Table.User_ID = hh_warehouse_trans.hwt_use
Where hh_warehouse_trans.hwt_ass
And hh_warehouse_trans.hwt_act
And hh_warehouse_trans.hwt_pro
') As AQ Left Outer Join
location On location.location_id = AQ.hwt_location_id_wasp
Order By AQ.hwt_entry_date Desc,
AQ.hwt_entry_time Desc
This works on an older 2003 sp2 server running MS SQL 2005 but fails with a catastrophic failure on a 2012 R2 server running MS SQL 2012.
Select AQ.hwt_user, AQ.Cust_Div_Def_Definit,
AQ.hwt_action, AQ.hwt_entity_id,
AQ.Order_H_PO, AQ.FirstName + ' ' + AQ.LastName as UserName,
AQ.hwt_part_number, AQ.hwt_location_id_wasp,
AQ.hwt_asset_tag, AQ.hwt_serial_number,
AQ.hwt_quantity, AQ.hwt_create_date,
AQ.hwt_create_time, AQ.hwt_entry_date,
AQ.hwt_entry_time, AQ.hwt_processed,
AQ.Prod_Description, location.code as location_code
From
OPENQUERY(CommPlete,
'Select hh_warehouse_trans.hwt_use
hh_warehouse_trans.hwt_act
Order_Header.Order_H_PO,
Rtrim(User_Code_Table.User
hh_warehouse_trans.hwt_par
hh_warehouse_trans.hwt_ass
hh_warehouse_trans.hwt_qua
hh_warehouse_trans.hwt_cre
hh_warehouse_trans.hwt_ent
Product_Catalog.Prod_Descr
From hh_warehouse_trans
Inner Join Customer_Division_De
On Customer_Division_De.Cust_
And Customer_Division_De.Cust_
Inner Join Product_Catalog
On Product_Catalog.Prod_Custo
And Product_Catalog.Prod_Custo
And Product_Catalog.Prod_Mfr_P
Left Outer Join Order_Header
On Order_Header.Order_H_Order
Inner Join User_Code_Table
On User_Code_Table.User_ID = hh_warehouse_trans.hwt_use
Where hh_warehouse_trans.hwt_ass
And hh_warehouse_trans.hwt_act
And hh_warehouse_trans.hwt_pro
') As AQ Left Outer Join
location On location.location_id = AQ.hwt_location_id_wasp
Order By AQ.hwt_entry_date Desc,
AQ.hwt_entry_time Desc
This works on an older 2003 sp2 server running MS SQL 2005 but fails with a catastrophic failure on a 2012 R2 server running MS SQL 2012.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You have the linked server "CommPlete" defined on the MS SQL 2012 server?
Can you be more specific concerning what sort of catastrophic failure you are encountering? (Burning smells? Parts flying out of the screen, or just an error msg?)
Can you be more specific concerning what sort of catastrophic failure you are encountering? (Burning smells? Parts flying out of the screen, or just an error msg?)
Yes "CommPlete" is defined in the Linked Server area and I can see all the tables from that database.
The error message is: The OLE DB provider "MSDASQL" for linked server "CommPlete" reported an error. The provider reported an unexpected catastrophic failure.
I meant to include that in the original post and then forgot.
The interesting thing is it returns the header row just no data.
The error message is: The OLE DB provider "MSDASQL" for linked server "CommPlete" reported an error. The provider reported an unexpected catastrophic failure.
I meant to include that in the original post and then forgot.
The interesting thing is it returns the header row just no data.
Are any of your columns particularly long? On the linked server, do you have 'Allow Inprocess' checked?
I guess it depends on your definition of particularly long. One of the column is a 100 character alpha field. As to the 'Allow Inprocess', I don't know where to look for that. I checked the properties of the linked server but don't find that.
> I guess it depends on your definition of particularly long. One of the column is a 100 character alpha field.
I was thinking in terms of ntext, text, etc.
> As to the 'Allow Inprocess', I don't know where to look for that.
It'll in under 'Providers' below Linked Servers... check the properties of MSDASQL. (And hopefully this is a test instance, or the only linked server you have using the MSDASQL provider, so it doesn't matter if we test the allow inprocess setting...)
I was thinking in terms of ntext, text, etc.
> As to the 'Allow Inprocess', I don't know where to look for that.
It'll in under 'Providers' below Linked Servers... check the properties of MSDASQL. (And hopefully this is a test instance, or the only linked server you have using the MSDASQL provider, so it doesn't matter if we test the allow inprocess setting...)
The 100 character field is the longest of the selected fields. I guess with the combination of the name field it might be longer but I doubt it.
Found Allow Inprocess and it was already checked.
This is the only linked server and yes this is in our test environment. The older server that I mentioned running Win 2003 and SQL 2005 is our current dev environment.
It is confusing to me that it would work on an older operating system and version of SQL but fail on newer version.
Found Allow Inprocess and it was already checked.
This is the only linked server and yes this is in our test environment. The older server that I mentioned running Win 2003 and SQL 2005 is our current dev environment.
It is confusing to me that it would work on an older operating system and version of SQL but fail on newer version.
Unchecked it, ran it again and it failed with the same message. Took longer to fail but it still failed.
I'm pretty sure it's a flaw in the data connector driver. Unfortunately I can't reproduce the issue, but reading what other folks have encountered with similar issues, I'd suggest trying:
Upgrade to at least SP2 possibly SP2 CU7. (In either event, be sure to pick up MS15-058 for security...)
I see one other suggestion to remove the linked server object, and recreate the linked server after making the necessary changes to the provider.
It seems that it's the number of columns or size of columns returned which is where things break. Ironically several folks have reported that they've had better luck with returning '*' rather than specifying specific columns, but that appears problematic in this case. (Might be worth testing/confirming whether a test query returning 'hh_warehouse_trans.*' returns rather than errors.)
And, yes, it seems to track to versions after SQL Server 2008.
Upgrade to at least SP2 possibly SP2 CU7. (In either event, be sure to pick up MS15-058 for security...)
I see one other suggestion to remove the linked server object, and recreate the linked server after making the necessary changes to the provider.
It seems that it's the number of columns or size of columns returned which is where things break. Ironically several folks have reported that they've had better luck with returning '*' rather than specifying specific columns, but that appears problematic in this case. (Might be worth testing/confirming whether a test query returning 'hh_warehouse_trans.*' returns rather than errors.)
And, yes, it seems to track to versions after SQL Server 2008.
We have completed the updates and are no longer getting the catastrophic failure. So that is good. However, we are now getting the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "CommPlete" reported an error. Provider caused a server fault in an external process.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "CommPlete".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "CommPlete" reported an error. Provider caused a server fault in an external process.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "CommPlete".
For the linked server provider, look at the "Allow inprocess" option. Maybe adjust that and try it again -- disclaimer: naturally, you should technically read up on the potential implications of that change before making it.
Scott
We have tried it with Allow inprocess checked and unchecked. Doesn't make any difference. I found a MS article stating this was a know bug that would ultimately get fixed. It provided two workarounds.
You can workaround the problem using either of the following methods:
Workaround 1: Configure the OLEDB provider to run in-process.
Note: You cannot use this workaround for OLEDB providers that are based on .Net versions that are earlier than 4.0.
Workaround 2:Assign the users the "Create global objects" user right. To do this, follow these steps:
Click Start, point to Administrative Tools, and then click Local Security Policy.
Expand Local Policies, and then click User Rights Assignment.
In the right pane, double-click Create global objects.
In the Local Security Policy Setting dialog box, click Add.
In the Select Users or Group dialog box, click the user accounts that you want to add, click Add, and then click OK.
Click OK.
I have tried both without success.
One note to mention is that under providers I found SQLOLEDB Provider. Wasn't sure if this was the same provider the work around was referencing. If it was, I have checked Allow Inprocess.
We have tried it with Allow inprocess checked and unchecked. Doesn't make any difference. I found a MS article stating this was a know bug that would ultimately get fixed. It provided two workarounds.
You can workaround the problem using either of the following methods:
Workaround 1: Configure the OLEDB provider to run in-process.
Note: You cannot use this workaround for OLEDB providers that are based on .Net versions that are earlier than 4.0.
Workaround 2:Assign the users the "Create global objects" user right. To do this, follow these steps:
Click Start, point to Administrative Tools, and then click Local Security Policy.
Expand Local Policies, and then click User Rights Assignment.
In the right pane, double-click Create global objects.
In the Local Security Policy Setting dialog box, click Add.
In the Select Users or Group dialog box, click the user accounts that you want to add, click Add, and then click OK.
Click OK.
I have tried both without success.
One note to mention is that under providers I found SQLOLEDB Provider. Wasn't sure if this was the same provider the work around was referencing. If it was, I have checked Allow Inprocess.
Can you make a test without the OPENQUERY function?
I just replace it with a CTE so I'm expecting this to run:
I just replace it with a CTE so I'm expecting this to run:
WITH AQ AS
(Select hh_warehouse_trans.hwt_user, Customer_Division_De.Cust_Div_Def_Definit,
hh_warehouse_trans.hwt_action, hh_warehouse_trans.hwt_entity_id,Order_Header.Order_H_PO,
Rtrim(User_Code_Table.User_First_Name) as FirstName, Rtrim(User_Code_Table.User_Last_Name) as LastName,
hh_warehouse_trans.hwt_part_number, hh_warehouse_trans.hwt_location_id_wasp,
hh_warehouse_trans.hwt_asset_tag, hh_warehouse_trans.hwt_serial_number,
hh_warehouse_trans.hwt_quantity, hh_warehouse_trans.hwt_create_date,
hh_warehouse_trans.hwt_create_time, hh_warehouse_trans.hwt_entry_date,
hh_warehouse_trans.hwt_entry_time, hh_warehouse_trans.hwt_processed, Product_Catalog.Prod_Description
From CommPlete.databasename.dbo.hh_warehouse_trans
Inner Join CommPlete.databasename.dbo.Customer_Division_De On Customer_Division_De.Cust_Div_Def_Custome = ''2009010101'' And Customer_Division_De.Cust_Div_Def_Divisio = hh_warehouse_trans.hwt_warehouse
Inner Join CommPlete.databasename.dbo.Product_Catalog On Product_Catalog.Prod_Customer_No = ''2009010101'' And Product_Catalog.Prod_Customer_Div = hh_warehouse_trans.hwt_warehouse And Product_Catalog.Prod_Mfr_Part_No = hh_warehouse_trans.hwt_part_number
Left Outer Join CommPlete.databasename.dbo.Order_Header On Order_Header.Order_H_Order_No = hh_warehouse_trans.hwt_entity_id
Inner Join CommPlete.databasename.dbo.User_Code_Table On User_Code_Table.User_ID = hh_warehouse_trans.hwt_user
Where hh_warehouse_trans.hwt_asset_tag = ''AM00163779'' And hh_warehouse_trans.hwt_action <> ''Move'' And hh_warehouse_trans.hwt_processed = 1)
Select AQ.hwt_user, AQ.Cust_Div_Def_Definit,
AQ.hwt_action, AQ.hwt_entity_id,
AQ.Order_H_PO, AQ.FirstName + ' ' + AQ.LastName as UserName,
AQ.hwt_part_number, AQ.hwt_location_id_wasp,
AQ.hwt_asset_tag, AQ.hwt_serial_number,
AQ.hwt_quantity, AQ.hwt_create_date,
AQ.hwt_create_time, AQ.hwt_entry_date,
AQ.hwt_entry_time, AQ.hwt_processed,
AQ.Prod_Description, location.code as location_code
From AQ
Left Outer Join location On location.location_id = AQ.hwt_location_id_wasp
Order By AQ.hwt_entry_date Desc, AQ.hwt_entry_time Desc
In SQL Management Studiogo to Server Object / Linked Servers / Provider and check if any (specially SQLOLEDB) has the “zero level only” property checked. If afirmative then uncheck it and restart SQL Server service.
Now you should be able to run the query again.
Now you should be able to run the query again.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial