SQL Server Dynamic Database Creation using Ansible

Archives Ansible Project
1

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.

2

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

3

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”}

4

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

5

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: (:slight_smile: , 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: (:slight_smile: , ParentContainsErrorRecordException”, " + FullyQualifiedErrorId : UnexpectedToken”], “stdout”: “”, “stdout_lines”: }

6

Now you’ve got the quoting wrong

7

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

Thanks and Regards
Praveen Singh

8

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