Schema fill throws an exception

First time I have seen this when filling a schema for a DataTable using the SqlDataAdapter.

I have a SPROC which internally creates a SQL String and uses sp_executesql to execute that SQL String.
When I run the SPROC - all works fine.

But, I want to fill a data table with the schema however I get an error thrown from the Fill command:

Quote:

---

Incorrect syntax near the keyword 'LEFT'

---

But when I execute that SPROC with the same parameters.... all works fine. If I do NOT fill the schema in the datatable but just do a fill(), it works fine and I get the data fine.... but when you do the fillschema... that is when the exception happens.

any ideas?

This is the query the SPROC generates before it does an sp_executesql on it:

Quote:

---

SELECT DISTINCT [Control].CustID, [Control].ControlNumber,Manufacturer.MfrName,Model.ModelNumber, Model.ModelDescription, [Control].ControlSerialNumber, [Control].ControlBarCode, Customer.CustID, Customer.CustShipToCompany, Customer.CustShipToAddress1,Customer.CustShipToContact1,Customer.CustShipToCity,Customer.CustShipToZ ip, [Control].ControlItemID FROM [Control] INNER JOIN WorkOrder ON [Control].ControlNumber = WorkOrder.ControlNumber INNER JOIN Model ON [Control].ModelId = Model.ModelId INNER JOIN Manufacturer ON Model.MfrCode = manufacturer.mfrcode LEFT OUTER JOIN Customer ON [Control].CustID = Customer.CustID WHERE ([Control].CustId IN (SELECT CustID FROM DocSS WHERE UserName = @userID)) AND Manufacturer.MfrName LIKE @manufacturer UNION SELECT DISTINCT [Control].CustID, [Control].ControlNumber,Manufacturer.MfrName,Model.ModelNumber, Model.ModelDescription, [Control].ControlSerialNumber, [Control].ControlBarCode, Customer.CustID, Customer.CustShipToCompany, Customer.CustShipToAddress1,Customer.CustShipToContact1,Customer.CustShipToCity,Customer.CustShipToZ ip, [Control].ControlItemID FROM [Control] INNER JOIN WOChild ON [Control].COntrolNumber = WOChild.ControlNumber INNER JOIN WorkOrder ON WoChild.WOrkOrderiD = WorkOrder.WorkOrderiD INNER JOIN Model ON [Control].ModelId = Model.ModelId INNER JOIN Manufacturer ON Model.MfrCode = manufacturer.mfrcode LEFT OUTER JOIN Customer ON [Control].CustID = Customer.CustID WHERE ([Control].CustId IN (SELECT CustID FROM DocSS WHERE UserName = @userID)) AND Manufacturer.MfrName LIKE @manufacturer

---

Have you done it with a dynamic statement before?

MSDN mentioned the following:

Quote:

---

When using FillSchema, the .NET Framework Data Provider for SQL Server appends a FOR BROWSE clause to the statement being executed. The user should be aware of potential side effects, such as interference with the use of SET FMTONLY ON statements. See SQL Server Books Online for more information.

---

Not sure if that would impact what you are doing though. It does hit it has to put it in there somewhere an it seems you are excuting a sp to just execute a "dynamic" sp.

not sure....
but I found another workaround for it. not the best though. Basically I appended a parameter into the SPROC which will spit out the SQL string it is generating, so then the client can get that and then execute it when filling the datatable instead of executing the SPROC. it works... not the best but, it works.