Schema fill throws an exception-VBForums
Results 1 to 3 of 3

Thread: Schema fill throws an exception

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Aug 2003
    Location
    Edinburgh, UK
    Posts
    2,773

    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:

    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:



    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



    Last edited by Techno; Feb 13th, 2013 at 06:54 PM.

    MVP 2007-2010 any chance of a regain?
    Professional Software Developer and Infrastructure Engineer.

  2. #2
    Hyperactive Member
    Join Date
    Jan 2010
    Posts
    259

    Re: Schema fill throws an exception

    Have you done it with a dynamic statement before?

    MSDN mentioned the following:
    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.

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Aug 2003
    Location
    Edinburgh, UK
    Posts
    2,773

    Re: Schema fill throws an exception

    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.

    MVP 2007-2010 any chance of a regain?
    Professional Software Developer and Infrastructure Engineer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.