Database Scripts Execution Issue

(ayan.biswas) #1

Hi Team,

We are facing issue, while executing multiple Database Scripts.

If we have 15 change scripts for execution as follows:-
1.Demo.sql
2.Dummy.sql
3.Test.sql and so on till 15.Demo2.sql

Octopus deploy tool executed above changes scripts as
1.Demo.sql
10.Script.sql
11.Script2.sql and so on.

However, we want to execute change scripts as per number sequence
1.Demo.sql
2.Dummy.sql
3.Test.sql and so on till 15.Demo2.sql

PFB screenshot for reference.

Please look into this and advise accordingly.

Best Regards,
Ayan

#3

Hi Ayan,

Thanks for getting in touch!

The quickest way around this issue would be to rename the scripts with a leading 0, e.g. 01, 02, 03 etc. This down to how the file names are encoded, specifically as they are encoded as strings this ordering will happen.

If you can let me know which step you are using I can investigate further and see if there is anything we can do on the encoding front, however that will take some time.

Thanks Ayan, let me know if there is anything else you need help with.

Regards,
Alex

(ayan.biswas) #4

Hi Alex,

Thanks for quick reply.

We will rename the scripts with a leading 0, e.g. 01 , 02 , 03 etc.

If we will face any issue then will contact you again.

Best Regards,
Ayan

(ayan.biswas) #6

Hi Alex,

I would request you to validate if below suggestion will help us to deploy script in correct order -

• If scripts need to be executed in particular sequence, then scripts have to organize chronologically, tool will always run them in the correct order
For Eg: Naming scripts as below will give us the correct order :
Script0001xxx.sql / Script0001-GetCustomTask.sql
Script0002xxx.sql / Script0002-GetGlobalSearch.sql
Script0003xxx.sql / Script0003-UsersAccess.sql

• Ensure file name shouldn’t start with ‘ ) ‘. For Eg: 1.) ABC_OEMS.sql
Error line from Log File:
Invoke-ExecuteSQLScript : System.Management.Automation.RuntimeException: The regular expression pattern
1)XYZ_OEMS.sql is not valid. —> System.ArgumentException: parsing “1)ABC_OEMS.sql” - Too many )'s
• Would suggest refraining from using any of these characters [^$.|?*+() in your filenames, as they have special meaning in Regex

Please let me know if you want to add or modify here.

Best Regards,
Ayan

(Mark Siedle) #7

Hi Ayan,

That all seems logical to us. We use a similar convention for our database scripts internally. E.g. Script0001-ScriptName.sql etc.

Are you finding that this is working for your scenario?

The best way to validate this is to create a test project in Octopus with a test dacpac package, then you can test various combinations and ensure you have correct naming conventions in place. You can use empty/dummy SQL scripts in your package and run this against a test database, then watch the output to ensure you get the expected order.

Hope this helps. Let us know how you go.

Cheers
Mark

(ayan.biswas) #8

Hi Mark,

Thanks for your suggestion.

We forwarded the same information to our Development team.

Regards,
Ayan

(system) closed #10

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.