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.
