query works in QA but not as a SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query works in QA but not as a SP

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2006-03-17 : 12:20:03

Hey all, i have a query:

use test1

Insert into IM_ITEM_MASTER_MC(Company_Code, Item_Code, Item_Description, Vendor_Code_List1, Vendor_Code_List2, Alternate_Item_Code1, Alternate_Item_Code2, Item_Category, Unit_Of_Measure1, Unit_Of_Measure2, Unit_Of_Measure_Conversion, Unit_Weight, Taxable_Flag, Discontinue_Flag, Commission_flag, Unit_Sales_List1, Unit_Sales_List2, Unit_Sales_List3, Item_Sales_Amount_List1, Item_Sales_Amount_List2, Item_Sales_Amount_List3, Cost_List1, Cost_List2, Cost_List3, Discount_Code, Price_List1, Price_List2, Price_List3, Price_List4, Price_List5, Date_Discontinued, Standard_Cost, Detail_Record_Count, Vendor_Part_Number, ABC_Class, Phase_Code, Cost_Type, Markup_Price, Average_Cost, Price_Per_Factor, Mix_Flag, Default_Cost_Category, Use_Tax_Flag, Price_Changed_Date, Cost_Changed_Date, Exclude_SI_Ticket, Exclude_SI_Freight)
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\DATA\Home\Matt\testing.xls;HDR=YES','SELECT Company_Code, Item_Code, Item_Description, Vendor_Code_List1, Vendor_Code_List2, Alternate_Item_Code1, Alternate_Item_Code2, Item_Category, Unit_Of_Measure1, Unit_Of_Measure2, Unit_Of_Measure_Conversion, Unit_Weight, Taxable_Flag, Discontinue_Flag, Commission_flag, Unit_Sales_List1, Unit_Sales_List2, Unit_Sales_List3, Item_Sales_Amount_List1, Item_Sales_Amount_List2, Item_Sales_Amount_List3, Cost_List1, Cost_List2, Cost_List3, Discount_Code, Price_List1, Price_List2, Price_List3, Price_List4, Price_List5, Date_Discontinued, Standard_Cost, Detail_Record_Count, Vendor_Part_Number, ABC_Class, Phase_Code, Cost_Type, Markup_Price, Average_Cost, Price_Per_Factor, Mix_Flag, Default_Cost_Category, Use_Tax_Flag, Price_Changed_Date, Cost_Changed_Date, Exclude_SI_Ticket, Exclude_SI_Freight FROM [Inv$]')
[/code]

That works just fine in Query Analyzer. Now, i want to make it a Stored Procedure so I can run it in VB.Net

When trying to enter the code in a SP, I get a error:

"Could not start a transaction for OLD DB Provider microsoft.jet.oledb.4.0"

Is this possible?

hueby
Posting Yak Master

127 Posts

Posted - 2006-03-17 : 12:22:22

Whooops, sorry my code tags didn't work.

here is the code formatted again:

CREATE PROCEDURE [spExcelInsert] AS

Insert into IM_ITEM_MASTER_MC(Company_Code, Item_Code, Item_Description, Vendor_Code_List1, Vendor_Code_List2, Alternate_Item_Code1, Alternate_Item_Code2, Item_Category, Unit_Of_Measure1, Unit_Of_Measure2, Unit_Of_Measure_Conversion, Unit_Weight, Taxable_Flag, Discontinue_Flag, Commission_flag, Unit_Sales_List1, Unit_Sales_List2, Unit_Sales_List3, Item_Sales_Amount_List1, Item_Sales_Amount_List2, Item_Sales_Amount_List3, Cost_List1, Cost_List2, Cost_List3, Discount_Code, Price_List1, Price_List2, Price_List3, Price_List4, Price_List5, Date_Discontinued, Standard_Cost, Detail_Record_Count, Vendor_Part_Number, ABC_Class, Phase_Code, Cost_Type, Markup_Price, Average_Cost, Price_Per_Factor, Mix_Flag, Default_Cost_Category, Use_Tax_Flag, Price_Changed_Date, Cost_Changed_Date, Exclude_SI_Ticket, Exclude_SI_Freight)
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\DATA\Home\Matt\testing.xls;HDR=YES','SELECT Company_Code, Item_Code, Item_Description, Vendor_Code_List1, Vendor_Code_List2, Alternate_Item_Code1, Alternate_Item_Code2, Item_Category, Unit_Of_Measure1, Unit_Of_Measure2, Unit_Of_Measure_Conversion, Unit_Weight, Taxable_Flag, Discontinue_Flag, Commission_flag, Unit_Sales_List1, Unit_Sales_List2, Unit_Sales_List3, Item_Sales_Amount_List1, Item_Sales_Amount_List2, Item_Sales_Amount_List3, Cost_List1, Cost_List2, Cost_List3, Discount_Code, Price_List1, Price_List2, Price_List3, Price_List4, Price_List5, Date_Discontinued, Standard_Cost, Detail_Record_Count, Vendor_Part_Number, ABC_Class, Phase_Code, Cost_Type, Markup_Price, Average_Cost, Price_Per_Factor, Mix_Flag, Default_Cost_Category, Use_Tax_Flag, Price_Changed_Date, Cost_Changed_Date, Exclude_SI_Ticket, Exclude_SI_Freight FROM [Inv$]')

TimS
Posting Yak Master

198 Posts

Posted - 2006-03-17 : 21:29:24

Have you tried to break it into two queries

CREATE PROCEDURE [spExcelInsert] AS

Select *
INTO #temp
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\DATA\Home\Matt\testing.xls;HDR=YES','SELECT Company_Code, Item_Code, Item_Description, Vendor_Code_List1, Vendor_Code_List2, Alternate_Item_Code1, Alternate_Item_Code2, Item_Category, Unit_Of_Measure1, Unit_Of_Measure2, Unit_Of_Measure_Conversion, Unit_Weight, Taxable_Flag, Discontinue_Flag, Commission_flag, Unit_Sales_List1, Unit_Sales_List2, Unit_Sales_List3, Item_Sales_Amount_List1, Item_Sales_Amount_List2, Item_Sales_Amount_List3, Cost_List1, Cost_List2, Cost_List3, Discount_Code, Price_List1, Price_List2, Price_List3, Price_List4, Price_List5, Date_Discontinued, Standard_Cost, Detail_Record_Count, Vendor_Part_Number, ABC_Class, Phase_Code, Cost_Type, Markup_Price, Average_Cost, Price_Per_Factor, Mix_Flag, Default_Cost_Category, Use_Tax_Flag, Price_Changed_Date, Cost_Changed_Date, Exclude_SI_Ticket, Exclude_SI_Freight FROM [Inv$]')

Insert into IM_ITEM_MASTER_MC(Company_Code, Item_Code, Item_Description, Vendor_Code_List1, Vendor_Code_List2, Alternate_Item_Code1, Alternate_Item_Code2, Item_Category, Unit_Of_Measure1, Unit_Of_Measure2, Unit_Of_Measure_Conversion, Unit_Weight, Taxable_Flag, Discontinue_Flag, Commission_flag, Unit_Sales_List1, Unit_Sales_List2, Unit_Sales_List3, Item_Sales_Amount_List1, Item_Sales_Amount_List2, Item_Sales_Amount_List3, Cost_List1, Cost_List2, Cost_List3, Discount_Code, Price_List1, Price_List2, Price_List3, Price_List4, Price_List5, Date_Discontinued, Standard_Cost, Detail_Record_Count, Vendor_Part_Number, ABC_Class, Phase_Code, Cost_Type, Markup_Price, Average_Cost, Price_Per_Factor, Mix_Flag, Default_Cost_Category, Use_Tax_Flag, Price_Changed_Date, Cost_Changed_Date, Exclude_SI_Ticket, Exclude_SI_Freight)
Select * FROM #temp

DROP TABLE #temp

Tim S

hueby
Posting Yak Master

127 Posts

Posted - 2006-03-20 : 09:07:26

That seems to give me the same error still. Maybe the problem is somewhere else then the syntax?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources