An interesting topic of conversation came up the other day, which was using Nintex Workflow in a Database creation request list in SharePoint.
The idea was to have a list in SharePoint where a user would create an item by filling in some form. It would be a user requesting a database be generated for them on a SQL Server.
I tried a number of ways to get the Execute SQL action to do this for me, but I kept hitting road blocks. Without looking at the code of the action, I don’t really know why it was failing, other than it had something to do with the syntax. A Create Table query that would work in SQL Management Studio wouldn’t work in the Execute SQL action, and it seemed to have something to do with the single quotes.
The workaround, and I actually prefer this design, is that you create a Stored Procedure in a database and you get the Execute SQL action to call that stored procedure, passing in the details you want.
The dataase I wanted to create for my test was quite simple so the stored procedure looked like this:
CREATE PROCEDURE [dbo].[CreateMyDatabase] @dbname nvarchar(127) AS BEGIN SET NOCOUNT ON; DECLARE @iStatus int select @iStatus = 0 EXECUTE ('create database ' + @dbname + ' on ( name = ''' + @dbname + 'data'', filename = ''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\' + @dbname + 'data.mdf'', size = 10mb, filegrowth = 10% ) log on ( name = ''' + @dbname + 'log'', filename = ''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\' + @dbname + 'log.ldf'', size = 5mb, filegrowth = 10% )') EXECUTE ('USE [' + @dbname + '];CREATE TABLE [dbo].[tblAccounts]( [ID] [int] NOT NULL, [Account Name] [nvarchar](127) NOT NULL, [Created] [date] NOT NULL, CONSTRAINT [PK_tblAccounts] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ') EXECUTE (' USE [' + @dbname + ']; ALTER TABLE [dbo].[tblAccounts] ADD CONSTRAINT [DF_tblAccounts_Created] DEFAULT (getdate()) FOR [Created] ') select @iStatus = @@error if @iStatus = 0 print @dbname + ' has been created successfully' else print @dbname + ' failed to create, status ' + convert(varchar(10), @iStatus) END
What this Stored Procedure does is create a database, create a table in that database and then alter the table because I have a default value for a Date field.
The Execute SQL action looks quite simple, in that all it is doing is taking the Title field and passing it into the Stored Procedure call. This creates a database with that name.
Quite a simple solution, but the result is a very powerful piece of functionality.
There’s no reason why it must stop at creating databases. Here’s some ideas for the future.
Build some DB administration scripts that could be run from Nintex Workflow, where the user interface is via a nice SharePoint or Nintex Form. When requests come in, send approval requests to managers or IT staff.
1. allow the deletion of a database
2. add or remove permissions to a database
3. shrink a database log
All of the above are interesting stored procedures that could be built, together with forms and workflows that would run them. This would reduce the need to have DBAs do this work manually. They could simply get an Approval Request, and once they approve, the workflow can do the rest.