Now you've got the quoting wrong
On Thu, 14 Jan 2021 at 20:57, 'Praveen Kumar Singh' via Ansible Project < [email protected]> wrote: > 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 > <https://groups.google.com/d/msgid/ansible-project/CAJK1W%3DzSpV_fz_mhJsu8Bt5xy7X9YesJcbxGrjiY57rqA07n%2Bg%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- Sent from a mobile device - please excuse the brevity, spelling and punctuation. -- 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/CAL8fbwM64fzeo39hciBkQgD1uLSHmBOA8MbtyOB6xNEcwRtfJg%40mail.gmail.com.
