SQL Server Dynamic Database Creation using Ansible

[Praveen Kumar Singh]

Hello All,

I am trying to create a new database on sql server using the below mentioned ansible playbook. 

But when I am executing the playbook and in the prompt option putting the database name. 

It is not taking that database name of the prompt and instead creating the database mentioned in the SQL query (in this case creating database TestDB).

So needed help in how I can create the database dynamically by putting the Database name in the prompt option.

---
 - hosts: SQLSERVER
   vars_prompt:
     - name: DB Config
       prompt: DbName
       default:
   tasks:
     - name: DB config
       win_shell: |
          $sql = "DECLARE @Query VARCHAR(MAX)=''
                  DECLARE @DbName VARCHAR(400) = 'TestDB'
                  DECLARE @DbFilePath VARCHAR(400) = 'E:\Database\'
                  SET @Query = @Query + 'CREATE DATABASE '+@DbName +' ON  PRIMARY '
                  SET @Query = @Query + '( NAME = '''+@DbName +''', FILENAME = '''+@DbFilePath+@DbName +'.mdf'' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
                  SET @Query = @Query + ' LOG ON '
                  SET @Query = @Query + '( NAME = '''+@DbName +'_log'', FILENAME = '''+@DbFilePath+@DbName +'_log.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
                  print @query
                  exec(@query)"
                  Invoke-SqlCmd -Query $sql

Thanks and Regards,

Praveen Singh

[Dick Visser]

On Thu, 14 Jan 2021 at 14:28, 'Praveen Kumar Singh' via Ansible
Project <ansible...@googlegroups.com> wrote:
>
> Hello All,
>
> I am trying to create a new database on sql server using the below mentioned ansible playbook.
>
> But when I am executing the playbook and in the prompt option putting the database name.
>
> It is not taking that database name of the prompt and instead creating the database mentioned in the SQL query (in this case creating database TestDB).

That is what you tell it to do, so this is expected behavior.

> So needed help in how I can create the database dynamically by putting the Database name in the prompt option.
>
> ---
> - hosts: SQLSERVER
> vars_prompt:
> - name: DB Config
> prompt: DbName
> default:
> tasks:
> - name: DB config
> win_shell: |
> $sql = "DECLARE @Query VARCHAR(MAX)=''
> DECLARE @DbName VARCHAR(400) = 'TestDB'

This is where you declare the var in windows language (or whatever you
call that).

> DECLARE @DbFilePath VARCHAR(400) = 'E:\Database\'
> SET @Query = @Query + 'CREATE DATABASE '+@DbName +' ON PRIMARY '
> SET @Query = @Query + '( NAME = '''+@DbName +''', FILENAME = '''+@DbFilePath+@DbName +'.mdf'' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
> SET @Query = @Query + ' LOG ON '
> SET @Query = @Query + '( NAME = '''+@DbName +'_log'', FILENAME = '''+@DbFilePath+@DbName +'_log.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
> print @query
> exec(@query)"
> Invoke-SqlCmd -Query $sql

Try jinja syntax, ie.

{{ DbName }}

instead of

@DbName




--
Dick Visser
Trust & Identity Service Operations Manager
GÉANT

[Praveen Kumar Singh]

Thanks Dick,

I tried jinja syntax and now am getting below error. The variable DbName is already declared and still getting the below error. So can you please suggest.

fatal: [SQLSERVER2]: FAILED! => {"msg": "The task includes an option with an undefined variable. The error was: 'DbName' is undefined\n\nThe error appears to be in '/local/cfjenkins/Ansible_Test_Project/DBconfig.yml': line 9, column 8, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n   tasks:\n     - name: DB config\n       ^ here\n"}

---
 - hosts: SQLSERVER2

   vars_prompt:
     - name: DB Config

       private: no

       prompt: DbName
       default:
   tasks:
     - name: DB config

       vars:
        DbName:

       win_shell: |
          $sql = "DECLARE @Query VARCHAR(MAX)=''

                  DECLARE "{{ DbName }}" VARCHAR(400) = ''

                  DECLARE @DbFilePath VARCHAR(400) = 'E:\Database\'

                  SET @Query = @Query + 'CREATE DATABASE '+"{{ DbName }}" +' ON  PRIMARY '
                  SET @Query = @Query + '( NAME = '''+"{{ DbName }}" +''', FILENAME = '''+@DbFilePath+@DbName +'.mdf'' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '

                  SET @Query = @Query + ' LOG ON '

                  SET @Query = @Query + '( NAME = '''+"{{ DbName }}" +'_log'', FILENAME = '''+@DbFilePath+@DbName +'_log.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'

                  print @query
                  exec(@query)"
                  Invoke-SqlCmd -Query $sql

Thanks and regards,

Praveen Singh

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAL8fbwN%3D9gvs5OsPtmZby%3DU_iLHhnK-9EOdv0A-LwThyR7K7ig%40mail.gmail.com.

[Jean-Yves LENHOF]

Hi,

Read docs a little !

https://docs.ansible.com/ansible/latest/user_guide/playbooks_prompts.html

DbName is the variable, so that should be the name

DB Config is the prompt written on the the screen, so that should be the prompt

try changing like this in your code :

   vars_prompt:
     - name: DbName
       private: no
       prompt: DB Config
       default:

You should probably have to make some verification of what you write, because if you just press enter the variable will be '', so I suppose your code will fail

Regards,

JYL

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAJK1W%3Dy_JeuC0fVgW%3D%3DAsG9ST-dLtNd4w%3D--uZj8uzgrNfM2rg%40mail.gmail.com.

[Praveen Kumar Singh]

Hello,

I tried your suggestion and now it's taking the database name which I am providing in the prompt option. But now I am getting a different error which is as below. 

It is showing as a parser error but I am not able to find it. So please help.

Also below is the playbook that I am using.

fatal: [SQLSERVER2]: FAILED! => {"changed": true, "cmd": "$sql= \"create database \"Praveen800\"\n      ON (NAME = \"Praveen800_dat\", FILENAME = E:\\Database\\\"Praveen800.mdf\", SIZE = 10MB,  MAXSIZE = 50MB, FILEGROWTH = 5MB)\n      LOG ON (NAME = \"Praveen800_log\", FILENAME = E:\\Database\\\"Praveen800.ldf\", SIZE = 5MB,  MAXSIZE = 25MB,  FILEGROWTH = 5MB)\"\n      Invoke-SqlCmd -Query $sql", "delta": "0:00:01.468781", "end": "2021-01-14 07:50:05.202033", "msg": "non-zero return code", "rc": 1, "start": "2021-01-14 07:50:03.733252", "stderr": "At line:1 char:89\r\n+ ... -Object Text.UTF8Encoding $false; $sql= \"create database \"Praveen800\"\r\n+                                                               ~~~~~~~~~~~\r\nUnexpected token 'Praveen800\"\r\n      ON (NAME = \"Praveen800_dat\", FILENAME = E:\\Database\\\"Praveen800.mdf\", SIZE = 10MB,  MAXSIZE = 50MB, FILEGROWTH = \r\n5MB)\r\n      LOG ON (NAME = \"Praveen800_log\", FILENAME = E:\\Database\\\"Praveen800.ldf\", SIZE = 5MB,  MAXSIZE = 25MB,  \r\nFILEGROWTH = 5MB)\"' in expression or statement.\r\n    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException\r\n    + FullyQualifiedErrorId : UnexpectedToken", "stderr_lines": ["At line:1 char:89", "+ ... -Object Text.UTF8Encoding $false; $sql= \"create database \"Praveen800\"", "+                                                               ~~~~~~~~~~~", "Unexpected token 'Praveen800\"", "      ON (NAME = \"Praveen800_dat\", FILENAME = E:\\Database\\\"Praveen800.mdf\", SIZE = 10MB,  MAXSIZE = 50MB, FILEGROWTH = ", "5MB)", "      LOG ON (NAME = \"Praveen800_log\", FILENAME = E:\\Database\\\"Praveen800.ldf\", SIZE = 5MB,  MAXSIZE = 25MB,  ", "FILEGROWTH = 5MB)\"' in expression or statement.", "    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException", "    + FullyQualifiedErrorId : UnexpectedToken"], "stdout": "", "stdout_lines": []}

---
 - hosts: SQLSERVER
   vars:
      DbName:

   vars_prompt:
     - name: DbName
       private: no
       prompt: DB Config
       default:

   tasks:
     - name: DB Create
       win_shell: |
           $sql= "create database "{{ DbName }}"
                 ON (NAME = "{{ DbName }}_dat", FILENAME = E:\Database\"{{ DbName }}.mdf", SIZE = 10MB,  MAXSIZE = 50MB, FILEGROWTH = 5MB)
                 LOG ON (NAME = "{{ DbName }}_log", FILENAME = E:\Database\"{{ DbName }}.ldf", SIZE = 5MB,  MAXSIZE = 25MB,  FILEGROWTH = 5MB)"
                 Invoke-SqlCmd -Query $sql

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/e2c1931b-ba4a-e551-4f73-cc7b2db48ae5%40lenhof.eu.org.

[Dick Visser]

Now you've got the quoting wrong

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAJK1W%3DzSpV_fz_mhJsu8Bt5xy7X9YesJcbxGrjiY57rqA07n%2Bg%40mail.gmail.com.

--

Sent from a mobile device - please excuse the brevity, spelling and punctuation.

[Praveen Kumar Singh]

Is there any tool or way I can find where I am putting the quotes wrong?

Thanks and Regards

Praveen Singh

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAL8fbwM64fzeo39hciBkQgD1uLSHmBOA8MbtyOB6xNEcwRtfJg%40mail.gmail.com.

[Jean-Yves LENHOF]

All good editors (you could to install some plugins) have syntax and
quoting coloring enabled....

vim and visual studio code are some examples, but there are quite a few

https://docs.ansible.com/ansible/latest/community/other_tools_and_programs.html

The documentation of ansible is quite good, so again, you should read it !

Regards,

JYL