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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy