Hi I am using into statement and storing to new temp table using #???? as table name. I used this statement for couple of times on different table. when joined all the temp tables on a select statement I am getting error as Invalid object ID. Can any body pls clear where i am going wrong.

It is hard to suggest something without seeing the code, can you provide a little repro?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

You need specify database name where you run this script, for example USE yourDatabaseName GO and reference all tables in your script from other databases like [Yourdatabasename].[dbo].[TableName]Sergei

Segei Most likely he getting th error because the temporary table is out of scopeBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

thank for taking interest pls find below my statements IF OBJECT_ID('#stock1') IS NOT NULL BEGIN DROP TABLE #stock1 END IF OBJECT_ID('#purchase1') IS NOT NULL BEGIN DROP TABLE #purchase1 END IF OBJECT_ID('#creation1') IS NOT NULL BEGIN DROP TABLE #creation1 END IF OBJECT_ID('#convertion1') IS NOT NULL BEGIN DROP TABLE #convertion1 END IF OBJECT_ID('#br_receipts1') IS NOT NULL BEGIN DROP TABLE #br_receipts1 END IF OBJECT_ID('#consumption1') IS NOT NULL BEGIN DROP TABLE #consumption1 END IF OBJECT_ID('#br_delivery1') IS NOT NULL BEGIN DROP TABLE #br_delivery1 END IF OBJECT_ID('#stock') IS NOT NULL BEGIN DROP TABLE #stock END IF OBJECT_ID('#purchase') IS NOT NULL BEGIN DROP TABLE #purchase END IF OBJECT_ID('#creation') IS NOT NULL BEGIN DROP TABLE #creation END IF OBJECT_ID('#convertion') IS NOT NULL BEGIN DROP TABLE #convertion END IF OBJECT_ID('#br_receipts') IS NOT NULL BEGIN DROP TABLE #br_receipts END IF OBJECT_ID('#consumption') IS NOT NULL BEGIN DROP TABLE #consumption END IF OBJECT_ID('#br_delivery') IS NOT NULL BEGIN DROP TABLE #br_delivery END -- OPENING STOCK & Opening WtAvg----- select productid as product,DATE as date,LocationId as Location,Receiptsquantity-issuesquantity as Op_stk,dbo.CostOfGoodsWtAverageMonthly(DATEADD(mm,-1,@date),ProductId,LocationId)*(receiptsquantity-issuesquantity) as Op_wtavg into #stock1 from StkLedgerTable WHERE Date<@date and ProductId in (@product) and LocationId in (@location) select product as product,Location as location,SUM(op_stk) as Op_stk,sum(op_wtavg) as op_wtavg into #stock from #stock1 group by product,Location order by product,Location ---PURCHASE QTY AND VALUE ----- select p1.date as date,p.product as product,p2.locationid as location,p.quantity as Purchase_Qty,p.grossminusdiscount as Purchase_Value into #purchase1 from AB_MaterialReceipts_InvoiceDetails_Table_Ver1 p left join Transaction_GeneralDetails p1 on p.TransactionNo=p1.TransactionNo left join StorageBinTable p2 on p.StorageBin=p2.StorageBinId where p1.Date<=@date and p1.Date>=@FromDate and p.Product in (@product) and p2.LocationId in (@location) union all select p1.date as date,p.product as product,p2.locationid as location,p.quantity as Purchase_Qty,p.grossminusdiscount as Purchase_Value from AB_MaterialReceiptsAgainstOrder_InvoiceDetails_Table_Ver1 p left join Transaction_GeneralDetails p1 on p.TransactionNo=p1.TransactionNo left join StorageBinTable p2 on p.StorageBin=p2.StorageBinId where p1.Date<=@date and p1.Date>=@FromDate and p.Product in (@product) and p2.LocationId in (@location) union all select p1.date as date,p.product as product,p2.locationid as location,-p.quantity as Purchase_Qty,-p.grossminusdiscount as Purchase_Value from AB_MaterialReturns_InvoiceDetails_Table_Ver2 p left join Transaction_GeneralDetails p1 on p.TransactionNo=p1.TransactionNo left join StorageBinTable p2 on p.StorageBin=p2.StorageBinId where p1.Date<=@date and p1.Date>=@FromDate and p.Product in (@product) and p2.LocationId in (@location) union all select p1.date as date,p.product as product,p2.locationid as location,-p.quantity as Purchase_Qty,-p.grossminusdiscount as Purchase_Value from AB_PurchaseReturns_InvoiceDetails_Table_Ver1 p left join Transaction_GeneralDetails p1 on p.TransactionNo=p1.TransactionNo left join StorageBinTable p2 on p.StorageBin=p2.StorageBinId where p1.Date<=@date and p1.Date>=@FromDate and p.Product in (@product) and p2.LocationId in (@location) select product as Product,location as Location,SUM(purchase_qty)as Purchase_qty,SUM(purchase_value) as Purchase_value into #purchase from #purchase1 group by product,Location order by product,Location ---STOCK CREATION QTY--------- select p1.date as date,p.product as product,p2.locationid as location,p.quantity as Creation_Qty into #Creation1 from AB_StockCreation_ItemDetails_Table_Ver1 p left join Transaction_GeneralDetails p1 on p.TransactionNo=p1.TransactionNo left join StorageBinTable p2 on p.StorageBin=p2.StorageBinId where p1.Date<=@date and p1.Date>=@FromDate and p.Product in (@product) and p2.LocationId in (@location) select product as product,location as location,SUM(creation_qty) as creation_qty into #creation from #creation1 group by product,Location order by product,Location -------STOCK CONVERSION IN QTY & VALUE---------- select p1.date as date,p.product as product,p2.locationid as location,p.OutputQuantity as Convertion_Qty into #Convertion1 from AB_StockConversion_Output_Table_Ver1 p left join Transaction_GeneralDetails p1 on p.TransactionNo=p1.TransactionNo left join StorageBinTable p2 on p.StorageBin=p2.StorageBinId left join AB_StockConversion_Output_Table_Extra e on p.TransactionNo=e.TransactionNo and p.TransactionLineNo=e.TransactionLineNo where p1.Date<=@date and p1.Date>=@FromDate and p.Product in (@product) and p2.LocationId in (@location) select product as product ,Location as location,SUM(convertion_qty) as convertion_Qty into #convertion from #convertion1 group by product,Location order by product,Location ---- Branch Stock Receipts & Value--------- select p1.date as date,p.product as product,p2.locationid as location,p.quantity as Br_Receips_Qty,p.GrossMinusDiscount as receipts_value into #Br_Receipts1 from AB_BranchStockReceipts_ItemDetails_Table_Ver1 p left join Transaction_GeneralDetails p1 on p.TransactionNo=p1.TransactionNo left join StorageBinTable p2 on p.StorageBin=p2.StorageBinId where p1.Date<=@date and p1.Date>=@FromDate and p.Product in (@product) and p2.LocationId in (@location) select product as product ,Location as location,SUM(Br_receipts_qty) as Br_receipts_Qty,SUM(receipts_value)as receipts_value into #Br_Receipts from #Br_Receipts1 group by product,Location order by product,Location ----- consumption-------- select p1.date as date,p.product as product,p2.locationid as location,p.quantity as Consumption_Qty into #Consumption1 from AB_StockConsumption_ItemDetails_Table_Ver1 p left join Transaction_GeneralDetails p1 on p.TransactionNo=p1.TransactionNo left join StorageBinTable p2 on p.StorageBin=p2.StorageBinId where p1.Date<=@date and p1.Date>=@FromDate and p.Product in (@product) and p2.LocationId in (@location) select product as product ,Location as location,SUM(consumption_qty) as consumption_Qty into #consumption from #consumption1 group by product,Location order by product,Location -----Branch stock transfer outward-------- select p1.date as date,p.product as product,p2.locationid as location,p.quantity as Br_Delivery_Qty,p.GrossMinusDiscount as Delivery_Value into #Br_delivery1 from AB_BranchStockDeliveries_ItemDetails_Table_Ver1 p left join Transaction_GeneralDetails p1 on p.TransactionNo=p1.TransactionNo left join StorageBinTable p2 on p.StorageBin=p2.StorageBinId where p1.Date<=@date and p1.Date>=@FromDate and p.Product in (@product) and p2.LocationId in (@location) select product as product, location as location,sum(Br_Delivery_Qty) as Br_Delivery_qty,SUM(Delivery_Value) as Delivery_Value into #br_delivery from #br_delivery1 group by product,Location order by product,Location ------- FINAL ----------- select distinct dbo.mastername(S.product) as product, dbo.mastername(b.locationid) as Loc, dbo.CostOfGoodsWtAverageMonthly(@date,s.product,b.locationid) as value, sum(stk.op_stk) as Opening_stock, sum(stk.Op_wtavg) as Opening_Value, SUM(purchase_qty) as Purchase_qty, SUM(purchase_value) as Purchase_value, SUM(cr.creation_qty) as Creation_Qty, SUM(con.convertion_Qty) as Convertion_Qty, SUM(brr.Br_Receips_Qty) as Branch_receipts, sum(brr.receipts_value) as Branch_recpt_Value into #final from StockValuation_StockIn_Table S left join storagebintable b on s.storagebin=b.storagebinid left join #stock stk on stk.product=s.Product and stk.location=b.LocationId left join #purchase pur on pur.product=s.product and Pur.location=b.LocationId left join #creation cr on Cr.product=s.Product and Cr.location=b.LocationId left join #convertion con on con.product=s.Product and con.location=b.LocationId left join #br_receipts brr on brr.product=s.Product and brr.location=b.LocationId group by s.Product,b.LocationId having s.product in (@product) and b.locationid in (@location) select * from #final

Where do you get that error? Are you able to track down the table name? Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

when I created a report using visual studio. I am not getting any error when i use into statements individually. But the desired report would be will all the temp tables combined. For your reference Below is the error message I got "Could not update list of fields for the query. Verify that you can connect to the datasource and that your query syntax is correct. Invalid object name '#stock1' "

You need to create a stored procedure based on the script above and reference in your dataset in the report.Sergei

any other solutuion pls.

Can you wrap your script into the stored procedure and point to the data set in SSRS?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Yes I did it and worked.