September 3, 2014 at 8:53 am
Hello all,
I am trying to import a DB from on premises to Azure but i keep getting error on the below....what do i need to change to get this to work?
Could not import package.
Warning SQL0: A project which specifies SQL Server 2012 as the target platform may experience compatibility issues with SQL Azure.
Error SQL72014: .Net SqlClient Data Provider: Msg 40512, Level 16, State 1, Procedure DELIVERY, Line 20 Deprecated feature 'Multiple table hints without comma' is not supported in this version of SQL Server.
Error SQL72045: Script execution error. The executed script:
Here is the procedure...
CREATE PROCEDURE [dbo].[DELIVERY]
@d_w_id int,
@d_o_carrier_id int,
@TIMESTAMP datetime2(0)
AS
BEGIN
DECLARE
@d_no_o_id int,
@d_d_id int,
@d_c_id int,
@d_ol_total int
BEGIN TRANSACTION
BEGIN TRY
DECLARE
@loop_counter int
SET @loop_counter = 1
WHILE @loop_counter <= 10
BEGIN
SET @d_d_id = @loop_counter
SELECT TOP (1) @d_no_o_id = NEW_ORDER.NO_O_ID FROM dbo.NEW_ORDER WITH (serializable updlock) WHERE NEW_ORDER.NO_W_ID = @d_w_id AND NEW_ORDER.NO_D_ID = @d_d_id
DELETE dbo.NEW_ORDER WHERE NEW_ORDER.NO_W_ID = @d_w_id AND NEW_ORDER.NO_D_ID = @d_d_id AND NEW_ORDER.NO_O_ID = @d_no_o_id
SELECT @d_c_id = ORDERS.O_C_ID FROM dbo.ORDERS WHERE ORDERS.O_ID = @d_no_o_id AND ORDERS.O_D_ID = @d_d_id AND ORDERS.O_W_ID = @d_w_id
UPDATE dbo.ORDERS SET O_CARRIER_ID = @d_o_carrier_id WHERE ORDERS.O_ID = @d_no_o_id AND ORDERS.O_D_ID = @d_d_id AND ORDERS.O_W_ID = @d_w_id
UPDATE dbo.ORDER_LINE SET OL_DELIVERY_D = @TIMESTAMP WHERE ORDER_LINE.OL_O_ID = @d_no_o_id AND ORDER_LINE.OL_D_ID = @d_d_id AND ORDER_LINE.OL_W_ID = @d_w_id
SELECT @d_ol_total = sum(ORDER_LINE.OL_AMOUNT) FROM dbo.ORDER_LINE WHERE ORDER_LINE.OL_O_ID = @d_no_o_id AND ORDER_LINE.OL_D_ID = @d_d_id AND ORDER_LINE.OL_W_ID = @d_w_id
UPDATE dbo.CUSTOMER SET C_BALANCE = CUSTOMER.C_BALANCE + @d_ol_total WHERE CUSTOMER.C_ID = @d_c_id AND CUSTOMER.C_D_ID = @d_d_id AND CUSTOMER.C_W_ID = @d_w_id
IF @@TRANCOUNT > 0
COMMIT WORK
'D: '
+
ISNULL(CAST(@d_d_id AS nvarchar(max)), '')
+
'O: '
+
ISNULL(CAST(@d_no_o_id AS nvarchar(max)), '')
+
'time '
+
ISNULL(CAST(@TIMESTAMP AS nvarchar(max)), '')
SET @loop_counter = @loop_counter + 1
END
SELECT@d_w_id as N'@d_w_id', @d_o_carrier_id as N'@d_o_carrier_id', @TIMESTAMP as N'@TIMESTAMP'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END
GO
September 3, 2014 at 9:18 am
Let's start with some formatting so we can read it. You can use these types of windows by looking the IFCode Shortcuts on the left side when posting.
CREATE PROCEDURE [dbo].[DELIVERY] @d_w_id INT
,@d_o_carrier_id INT
,@TIMESTAMP DATETIME2(0)
AS
BEGIN
DECLARE @d_no_o_id INT
,@d_d_id INT
,@d_c_id INT
,@d_ol_total INT
BEGIN TRANSACTION
BEGIN TRY
DECLARE @loop_counter INT
SET @loop_counter = 1
WHILE @loop_counter <= 10
BEGIN
SET @d_d_id = @loop_counter
SELECT TOP (1) @d_no_o_id = NEW_ORDER.NO_O_ID
FROM dbo.NEW_ORDER WITH (SERIALIZABLE UPDLOCK)
WHERE NEW_ORDER.NO_W_ID = @d_w_id
AND NEW_ORDER.NO_D_ID = @d_d_id
DELETE dbo.NEW_ORDER
WHERE NEW_ORDER.NO_W_ID = @d_w_id
AND NEW_ORDER.NO_D_ID = @d_d_id
AND NEW_ORDER.NO_O_ID = @d_no_o_id
SELECT @d_c_id = ORDERS.O_C_ID
FROM dbo.ORDERS
WHERE ORDERS.O_ID = @d_no_o_id
AND ORDERS.O_D_ID = @d_d_id
AND ORDERS.O_W_ID = @d_w_id
UPDATE dbo.ORDERS
SET O_CARRIER_ID = @d_o_carrier_id
WHERE ORDERS.O_ID = @d_no_o_id
AND ORDERS.O_D_ID = @d_d_id
AND ORDERS.O_W_ID = @d_w_id
UPDATE dbo.ORDER_LINE
SET OL_DELIVERY_D = @TIMESTAMP
WHERE ORDER_LINE.OL_O_ID = @d_no_o_id
AND ORDER_LINE.OL_D_ID = @d_d_id
AND ORDER_LINE.OL_W_ID = @d_w_id
SELECT @d_ol_total = sum(ORDER_LINE.OL_AMOUNT)
FROM dbo.ORDER_LINE
WHERE ORDER_LINE.OL_O_ID = @d_no_o_id
AND ORDER_LINE.OL_D_ID = @d_d_id
AND ORDER_LINE.OL_W_ID = @d_w_id
UPDATE dbo.CUSTOMER
SET C_BALANCE = CUSTOMER.C_BALANCE + @d_ol_total
WHERE CUSTOMER.C_ID = @d_c_id
AND CUSTOMER.C_D_ID = @d_d_id
AND CUSTOMER.C_W_ID = @d_w_id
IF @@TRANCOUNT > 0
COMMIT WORK
PRINT 'D: ' + ISNULL(CAST(@d_d_id AS NVARCHAR(max)), '') + 'O: ' + ISNULL(CAST(@d_no_o_id AS NVARCHAR(max)), '') + 'time ' + ISNULL(CAST(@TIMESTAMP AS NVARCHAR(max)), '')
SET @loop_counter = @loop_counter + 1
END
SELECT @d_w_id AS N'@d_w_id'
,@d_o_carrier_id AS N'@d_o_carrier_id'
,@TIMESTAMP AS N'@TIMESTAMP'
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END
GO
Why do you need a loop here? I was trying to decipher this and my head is spinning. What is this procedure trying to do?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 3, 2014 at 9:26 am
Sorry i guess i should have given some background.
This is just the procedure that was created when i am using the tool called "hammerDB", i am sure you know it. It is a bench-marking tool.
I am try to copy the same data and procedure over to Azure, so we can run a benchmark there as well. and while moving the DB i get the that error.
i am using the below blog to move the DB over to azure...
September 4, 2014 at 6:54 am
Here's the issue:
dbo.NEW_ORDER WITH (serializable updlock)
You need to change it to:
dbo.NEW_ORDER WITH (serializable, updlock)
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2014 at 10:39 am
Thank you Grant, that did the trick for me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply