Creating a database in MSSQL using SQL procedures

Question

Hi i am trying to create a database using below SQL procedure

Create_SQL_Proc_Statement = """
CREATE PROCEDURE db_creation_script
           @DBName nvarchar(50),
           @data_path nvarchar(50) ='',
           @data_file_size nvarchar(50) = '5000KB',
           @data_file_maxsize nvarchar(50) = 'UNLIMITED',
           @data_file_filegrowth nvarchar(50) = '1024KB',
           @log_path nvarchar(50) ='',
           @log_file_size nvarchar(50) = '5000KB',
           @log_file_maxsize nvarchar(50) = '1048576KB',
           @log_file_filegrowth nvarchar(50) = '1024KB'
AS

begin

           declare @l_variable_data as nvarchar(500);
           declare @l_variable_log as nvarchar(500);
           declare @complete_command as nvarchar(1000);
           declare @print_message as nvarchar(1000);
           declare @error as int;

           --Fetch data path

           if (@data_path ='' OR @data_path is null )
                          SELECT @data_path=cast(SERVERPROPERTY('InstanceDefaultDataPath') as nvarchar);
           if (@data_path ='' OR @data_path is null )

           begin
                          set @error=@@ERROR
                          if (@error<>0)
                          begin
                                         set @print_message=N'Error '+ RTRIM(cast(@error as nvarchar(10))) + ' - Message: '+ ERROR_MESSAGE();
                                         print(@print_message);
                          end
                          else
                                         print('No error is generated');
           end

           --Fetch log path

           if (@log_path ='' OR @log_path is null )
                          SELECT @log_path=cast(SERVERPROPERTY('InstanceDefaultLogPath') as nvarchar);
           if (@log_path ='' OR @log_path is null )
           begin
                          set @error=@@ERROR
                          if (@error<>0)
                          begin
                                         set @print_message=N'Error '+ RTRIM(cast(@error as nvarchar(10))) + ' - Message: '+ ERROR_MESSAGE();
                                         print(@print_message);
                          end
                          else
                                         print('No error is generated');
           end


            set @l_variable_data = concat('NAME =', '''',@DBName, '_DATA','''', ', FILENAME = ', '''', @data_path,'\',@DBName, '.mdf', '''', ', SIZE = ', @data_file_size, ', MAXSIZE = ', @data_file_maxsize,', FILEGROWTH = ',@data_file_filegrowth)
            set @l_variable_log  = concat('NAME =', '''',@DBName, '_LOG', '''', ', FILENAME = ', '''', @log_path, '\',@DBName, '.ldf', '''', ', SIZE = ', @log_file_size, ', MAXSIZE = ',  @log_file_maxsize,', FILEGROWTH = ',@log_file_filegrowth)

           set @complete_command = concat(' CREATE DATABASE ', @DBName , ' CONTAINMENT = NONE ON PRIMARY (', @l_variable_data, ')' , ' LOG ON (' , @l_variable_log , ')');


           exec(@complete_command);

end
"""

and, below is how i am dealing with the proc:

cursor.execute(Create_SQL_Proc_Statement)
cursor.execute('Exec db_creation_script  ?, ?, ?, ?, ?, ?, ?, ?, ?',(DB_Name,Data_Path,Data_File_Size,Data_File_Maxsize,Data_File_Filegrowth,Log_Path,Log_File_Size,Log_File_Maxsize,Log_File_Filegrowth))

i also tried the below:

cursor.execute('{ call db_creation_script  (?, ?, ?, ?, ?, ?, ?, ?, ?)}',(DB_Name,Data_Path,Data_File_Size,Data_File_Maxsize,Data_File_Filegrowth,Log_Path,Log_File_Size,Log_File_Maxsize,Log_File_Filegrowth))
cursor.execute('exec db_creation_script  {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}'.format(DB_Name,Data_Path,Data_File_Size,Data_File_Maxsize,Data_File_Filegrowth,Log_Path,Log_File_Size,Log_File_Maxsize,Log_File_Filegrowth))

but no luck, i always get following exception:

 cursor.execute('exec db_creation_script  {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}'.format(DB_Name,Data_Path,Data_File_Size,Data_File_Maxsize,Data_File_Filegrowth,Log_Path,Log_File_Size,Log_File_Maxsize,Log_File_Filegrowth))
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ','. (102) (SQLExecDirectW)")

Every time i run the above code, i get unsuccessful message, Could you please help me understand where exactly the issue is in my script? would be great if any one can let me know if there is any other way around to do this.

Note: i am using pyodbc python module to achieve the same

Please remove the try ... except block and give us the exception that is raised when you run the SQL producedure. — Martijn Pieters
– Martijn Pieters, Commented Apr 3, 2018 at 7:22
cursor.execute('exec #db_create ?, ?, ?, ?, ?, ?, ?',(DB_Name,Data_Path,Data_File_Size,Data_File_Maxsize, Log_Path,Log_File_Size,Log_File_Maxsize)) pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ','. (102) (SQLExecDirectW)") — Alpha123
– Alpha123, Commented Apr 3, 2018 at 7:39
Please edit your question to add the requested clarifications to it. — Martijn Pieters
– Martijn Pieters, Commented Apr 3, 2018 at 7:41
And now you know you have a syntax error in your SQL definition. — Martijn Pieters
– Martijn Pieters, Commented Apr 3, 2018 at 7:41
The first thing to do is remove the try/except block so you can see the full text of the error message from the database, which should help narrow it down. — FlipperPA
– FlipperPA, Commented Apr 3, 2018 at 9:52

Peter Tilsted · Accepted Answer · 2018-04-04 14:59:57Z

1

you need to make sure the string is valid to SQLServer

here i have made the nescessary changes, and the stored procedure is created on my instance, whether it works is another issue :-)

i have intentionally left out the 3 lines just above exec(complete command), because of the many quotes to be entered :-)

This script works in SSMS

       DECLARE @Create_SQL_Proc_Statement NVARCHAR(MAX);
       SELECT @Create_SQL_Proc_Statement = N'
       CREATE PROCEDURE db_creation_script
       @DBName nvarchar(50),
       @data_path nvarchar(50) ='''',
       @data_file_size nvarchar(50) = ''5000KB'',
       @data_file_maxsize nvarchar(50) = ''UNLIMITED'',
       @data_file_filegrowth nvarchar(50) = ''1024KB'',
       @log_path nvarchar(50) ='''',
       @log_file_size nvarchar(50) = ''5000KB'',
       @log_file_maxsize nvarchar(50) = ''1048576KB'',
       @log_file_filegrowth nvarchar(50) = ''1024KB''
       AS

       begin

       declare @l_variable_data as nvarchar(500);
       declare @l_variable_log as nvarchar(500);
       declare @complete_command as nvarchar(1000);
       declare @print_message as nvarchar(1000);
       declare @error as int;

       --Fetch data path

       if (@data_path ='''' OR @data_path is null )
                      SELECT @data_path=cast(SERVERPROPERTY(''InstanceDefaultDataPath'') as nvarchar);
       if (@data_path ='''' OR @data_path is null )

       begin
                      set @error=@@ERROR
                      if (@error<>0)
                      begin
                                     set @print_message=N''Error ''+ RTRIM(cast(@error as nvarchar(10))) + '' - Message: ''+ ERROR_MESSAGE();
                                     print(@print_message);
                      end
                      else
                                     print(''No error is generated'');
       end

       --Fetch log path

       if (@log_path ='''' OR @log_path is null )
                      SELECT @log_path=cast(SERVERPROPERTY(''InstanceDefaultLogPath'') as nvarchar);
       if (@log_path ='''' OR @log_path is null )
       begin
                      set @error=@@ERROR
                      if (@error<>0)
                      begin
                                     set @print_message=N''Error ''+ RTRIM(cast(@error as nvarchar(10))) + '' - Message: ''+ ERROR_MESSAGE();
                                     print(@print_message);
                      end
                      else
                                     print(''No error is generated'');
       end



       exec(@complete_command);

       end
       '           

       EXECUTE sp_executesql @Create_SQL_Proc_Statement

edited Apr 4, 2018 at 14:59

answered Apr 3, 2018 at 11:40

Peter Tilsted

2162 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alpha123 Over a year ago

i am having trouble executing the stored procedure, so i dont think it is going to help.

Peter Tilsted Over a year ago

Do you have access to SSMS og similar tool You need to make sure that the created string Create_SQL_Proc_Statement is valid to SQL Server your problem comes from the need of quotes inside the string this is the first few lines as i would write it in SSMS to be valid (see the doublequotes when one is needed: Create_SQL_Proc_Statement = N' CREATE PROCEDURE db_creation_script DBName nvarchar(50), data_path nvarchar(50) ='''', data_file_size nvarchar(50) = ''5000KB'',

Peter Tilsted Over a year ago

i am wondering, why do you need a stored procedure to create databases ?

Alpha123 Over a year ago

Hi @Peter, yes this answer worked in SSMS along with my python script, thanks a lot. Now i am facing a small issue with my python script dealing with the default values. My script is coded in such a way that while invoking this sql proc, i pass in all the parameters like below: cursor.execute('{ call db_creation_script_test (?, ?, ?, ?, ?, ?, ?, ?, ?)}',(A,B,C,D,E,F,G,H,I)) Now whenever a user does not key in a parameter my default values should be replaced, rather in my case only white spaces are being replaced with the defaults ('') which is not allowing me to proceed further.

Peter Tilsted Over a year ago

@alpha123 if i understand the python script correctly you are always passing a value to all parameters. Allthough it might be a NULL value, the stored procedure sees it as that is what you want instead of the default value. If you want the default value, the parameter must not be included, or, and that might be better in your case: add code inside the stored procedure that evaluates the parameters passed and if the passed value is NULL, set it to the default value.

Collectives™ on Stack Overflow

Creating a database in MSSQL using SQL procedures

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related