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
On Thu, Jan 14, 2021 at 7:20 AM Jean-Yves LENHOF <[email protected]>
wrote:
> 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
>
>
> Le 14/01/2021 à 16:01, 'Praveen Kumar Singh' via Ansible Project a écrit :
>
> 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
>
>
> On Thu, Jan 14, 2021 at 5:42 AM Dick Visser <[email protected]> wrote:
>
>> On Thu, 14 Jan 2021 at 14:28, 'Praveen Kumar Singh' via Ansible
>> Project <[email protected]> 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
>>
>> --
>> 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 [email protected].
>> 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
>> .
>>
> --
> 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 [email protected].
> 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
> <https://groups.google.com/d/msgid/ansible-project/CAJK1W%3Dy_JeuC0fVgW%3D%3DAsG9ST-dLtNd4w%3D--uZj8uzgrNfM2rg%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
> --
> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ansible-project/e2c1931b-ba4a-e551-4f73-cc7b2db48ae5%40lenhof.eu.org
> <https://groups.google.com/d/msgid/ansible-project/e2c1931b-ba4a-e551-4f73-cc7b2db48ae5%40lenhof.eu.org?utm_medium=email&utm_source=footer>
> .
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/ansible-project/CAJK1W%3DzSpV_fz_mhJsu8Bt5xy7X9YesJcbxGrjiY57rqA07n%2Bg%40mail.gmail.com.
