# SQL Server Dynamic Database Creation using Ansible **URL:** \ **Category:** Ansible Project\ **Created:** [January 14, 2021, 1:27pm UTC](https://forum.ansible.com/t/sql-server-dynamic-database-creation-using-ansible/34443 "2021-01-14T13:27:56Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Praveen\_Kumar\_Singh](https://avatars.discourse-cdn.com/v4/letter/p/977dab/32.png) [@Praveen\_Kumar\_Singh](https://forum.ansible.com/u/Praveen_Kumar_Singh)\ **Post date:** [January 14, 2021, 1:27pm UTC](https://forum.ansible.com/t/sql-server-dynamic-database-creation-using-ansible/34443/1 "2021-01-14T13:27:56Z")
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. ---
**Author:** ![Dick\_Visser](https://avatars.discourse-cdn.com/v4/letter/d/d9b06d/32.png) [@Dick\_Visser](https://forum.ansible.com/u/Dick_Visser)\ **Post date:** [January 14, 2021, 1:41pm UTC](https://forum.ansible.com/t/sql-server-dynamic-database-creation-using-ansible/34443/2 "2021-01-14T13:41:45Z")
> 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 ---
**Author:** ![Praveen\_Kumar\_Singh](https://avatars.discourse-cdn.com/v4/letter/p/977dab/32.png) [@Praveen\_Kumar\_Singh](https://forum.ansible.com/u/Praveen_Kumar_Singh)\ **Post date:** [January 14, 2021, 3:01pm UTC](https://forum.ansible.com/t/sql-server-dynamic-database-creation-using-ansible/34443/3 "2021-01-14T15:01:32Z")
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”} ---
**Author:** ![jean-yves](https://avatars.discourse-cdn.com/v4/letter/j/a587f6/32.png) [@jean-yves](https://forum.ansible.com/u/jean-yves)\ **Post date:** [January 14, 2021, 3:19pm UTC](https://forum.ansible.com/t/sql-server-dynamic-database-creation-using-ansible/34443/4 "2021-01-14T15:19:41Z")
Hi, Read docs a little ! [https://docs.ansible.com/ansible/latest/user\_guide/playbooks\_prompts.html](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 ---
**Author:** ![Praveen\_Kumar\_Singh](https://avatars.discourse-cdn.com/v4/letter/p/977dab/32.png) [@Praveen\_Kumar\_Singh](https://forum.ansible.com/u/Praveen_Kumar_Singh)\ **Post date:** [January 14, 2021, 7:56pm UTC](https://forum.ansible.com/t/sql-server-dynamic-database-creation-using-ansible/34443/5 "2021-01-14T19:56:43Z")
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”: } ---
**Author:** ![Dick\_Visser](https://avatars.discourse-cdn.com/v4/letter/d/d9b06d/32.png) [@Dick\_Visser](https://forum.ansible.com/u/Dick_Visser)\ **Post date:** [January 14, 2021, 10:22pm UTC](https://forum.ansible.com/t/sql-server-dynamic-database-creation-using-ansible/34443/6 "2021-01-14T22:22:10Z")
Now you’ve got the quoting wrong ---
**Author:** ![Praveen\_Kumar\_Singh](https://avatars.discourse-cdn.com/v4/letter/p/977dab/32.png) [@Praveen\_Kumar\_Singh](https://forum.ansible.com/u/Praveen_Kumar_Singh)\ **Post date:** [January 15, 2021, 4:48am UTC](https://forum.ansible.com/t/sql-server-dynamic-database-creation-using-ansible/34443/7 "2021-01-15T04:48:46Z")
Is there any tool or way I can find where I am putting the quotes wrong? Thanks and Regards Praveen Singh ---
**Author:** ![jean-yves](https://avatars.discourse-cdn.com/v4/letter/j/a587f6/32.png) [@jean-yves](https://forum.ansible.com/u/jean-yves)\ **Post date:** [January 15, 2021, 8:17am UTC](https://forum.ansible.com/t/sql-server-dynamic-database-creation-using-ansible/34443/8 "2021-01-15T08:17:39Z")
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](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