Dear Microsoft. I am using the Phsycial Inventory Procedure and found that there is bug in the program of import procedure. This bug I have found and gone through using SQL Profile, what I investigate is the import procedure defined by you is have collaion error: Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation conflict for equal to operation.
The error is not raising if you not use the temporary table as you are using [##ST_Validation_1]. What I did is that I follow step by step process of SQL profile and create the table [ST_Validation_1] instead of [##ST_Validation_1]. then inserted the data and then try to run the Update Query it works!!
UPDATE [ST_Validation_1] SET ImportStatus = 3 , ItemID = Item.ID FROM Item INNER JOIN [ST_Validation_1] ON Item.ItemLookupCode [ST_Validation_1].ItemLookupCode
After more investigation I have found that you must use the database default for Item Lookupcode. So please use this
create table ##ST_Validation_1([ItemID] int null,[ImportStatus] int null,[ImportRow] int null,[ItemLookupCode] nvarchar(25) Collate Database_default NULL ,[Quantity] float null,[CountDate] datetime null)
it works fine, I can't know modify your code but please you can and then update me.
---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane.