Monday, June 29, 2015

ADO.NET Dataset MS SQL procedure: Invalid object name #TempTable

This is commonly known error, but if you try to google it then hardly you will find immediately result. Hope this page will have better index in search engine.

So you create procedure in MS SQL with temporary table, e.g.:


CREATE PROCEDURE my_proc
AS
BEGIN
    create table #TempTable(ID int)
    
   -- insert, select data, etc

  return select * from #TempTable 

END

An attempt to add tableAdapter in DataSet will show error:  Invalid object name '#MyTempTable'. 

The solution is to come around ADO.NET with FMTONLY statement, it does not allow to run procedure. Adding IF 1= 0 will not run inside MS SQL, but will run on ADO.NET request.


CREATE PROCEDURE my_proc
AS
BEGIN
   IF 1= -- hint for ADO.NET
   BEGIN
     SET FMTONLY OFF
   END
    create table #TempTable(ID int)
    
   -- insert, select data, etc

  return select * from #TempTable 

END

2 comments:

  1. Packers and Movers Gurgaon Provide Reliable, Safe and Certified Service Provider list, Get Free ***Best Price Quotaition and Compare Charges. ???Hassle free Household Shifting Services, High Quality packing Material, Office Relocation, Car Transportaion, ###Local and Domestic Shifting Service @ Packers And Movers Gurgaon

    ReplyDelete
  2. Get Packers and Movers Jaipur List of Top Reliable, 100% Affordable, Verified and Secured Service Provider. Get Free ###Packers and Movers Jaipur Price Quotation instantly and Save Cost and Time. Packers and Movers Jaipur ✔✔✔Reviews and Compare Charges for household Shifting, Home/Office Relocation, ***Car Transportation, Pet Relocation, Bike SHifting @ Packers And Movers Jaipur

    ReplyDelete