Posts Tagged ‘stored procedure’

Force 5

Extreme Database Makeover

Wednesday, April 28th, 2010 by Force 5

Move…That…Database

Situation: Upgraded servers, so we needed to move database tables, views and stored procedures from a SQL Server 2005 machine to a SQL Server 2008 machine. We wanted to go through each table, view and stored procedure one by one in order to only move objects that are still in use. We needed a way to do that as accurately and quickly as possible.

Solution: We made use of the SQL Server Script Wizard. The Script Wizard generated the CREATE scripts for each object. We were able to either cut/paste and run the scripts we needed on the new server, or delete the scripts if we didn’t need them. This made the process seamless and straight-forward.

Details:

Accessing the Script Wizard

In SQL Server Management Studio right-click on a database. Go to Tasks > Generate Scripts… This will open up a wizard dialog box.

Using the Script Wizard

The first screen allows you to select the database you want to use. On this screen you can also fast-track to the finish by checking “Script all objects in the selected database”. This will create scripts for all Roles, Users, Schemas, Tables, Views and Stored Procedures. We won’t check that in this article, but that useful option is there if that is what you are trying to accomplish.

Choose Script Options

After you select your database and click Next you will come to an Options screen. We were able to use the default settings here. But there are some useful options to note.

  • Generate scripts for dependent objects(Default: False) – Use this to ensure dependent objects are also scripted for the objects that you select
  • Script Create(Default: True) - Cornerstone of this wizard. This enables creation of scripts for the objects you select.
  • Script Logins(Default: False) – Generates logins for the selected database
  • Script Object-Level Permissions(Default: False) – ***This is an important option*** Set this to True in order to generate the permission scripts (GRANT EXECUTE ON) if you are generating stored procedure scripts.
  • Script Data(Default: False) – Set to True if you want to script out INSERT statements for all data in selected tables.
  • Script Foreign Keys(Default: True) – Scripts foreign keys for tables selected
  • Script Primary Keys(Default: True) – Scripts primary keys for tables selected
  • Script Triggers(Default: False) – Scripts any triggers that are related to tables selected

Choose Object Types

This is where you select what object types you want to generate scripts for (Database roles, Schema, Stored procedures, Tables, Users, Views).

Following this you will be taken through screens for each object type you selected.  Here is where you select the specific objects whose scripts will be generated.

Output Option

Here you can either Script to a file, Script to Clipboard or Script to a New Query Window.  We scripted to a query window and that worked great.

Finished

Go to Finish and the wizard will generate the scripts that you selected.

Notes

The constraints and permission assignments are all located in the last section of the generated scripts.  This is to ensure that the objects have been created before trying to assign permissions to them.

MSDN Article: How to: Generate a Script

In Conclusion

This wizard is a powerful, yet simple tool that makes data migration more manageable.  Trying to do this task object by object would be very cumbersome and impractical.

If you are in need of any advice or have a data migration project that you need help with please feel free to contact us here at Force 5.

Tags: , , , , , ,
Posted in Technology | No Comments »

Force 5

SQL Case Study – Convert data rows to columns

Friday, March 5th, 2010 by Force 5

We recently had a project that involved putting together a survey.  This survey was comprised of almost 150 questions.  As we brainstormed the best way to construct the data tables to store this information, the thought of a table with 150 columns made us cringe.  Time constraints also called for something we could put together relatively quickly.  We decided to create a table that stored each question as a row of data.  Then we made a table that referenced the primary key of the question table along with the user’s answer to that question.  So instead of having a table with 150 columns, we have one table with 150 rows and another table that stores a data row for each question answered on the survey.  Now if a question needs to be added or removed from the survey all that needs to happen is add or remove a row from the questions table.

It also made collecting the survey data through a ASP.NET Web Site a lot easier, but that can be a future blog topic.

All of that was a setup for displaying the following solution that we created.  In order to display the data correctly for reporting purposes we needed to be able to transform the 150 rows of data in the questions table into a table with that data as column names.  In simpler terms, we needed to convert a set of data rows in table columns in a temporary table. Then we needed to be able to populate that table with the data from the answers table.

Here is the solution we came up with using the power of a stored procedures in Microsoft SQL Server.

CREATE PROCEDURE [dbo].[Survey_Answers]
AS
BEGIN
	SET NOCOUNT ON;
 
	-- Declare variables
	DECLARE @QuestionID VARCHAR(20), @SQL VARCHAR(MAX)
 
	-- Create empty temporary table with id column
	CREATE TABLE #tempTable (SurveyID INT NULL)
 
	---- Insert Columns into pivot table ----
	-- Declare cursor to loop through table
	DECLARE curQuestions CURSOR FOR
	SELECT     QuestionID
	FROM         Survey_Questions
 
	OPEN curQuestions
 
	FETCH NEXT FROM curQuestions INTO @QuestionID
	WHILE @@FETCH_STATUS=0
	BEGIN
		-- Defines each column
		SET @SQL = 'ALTER TABLE #tempTable ADD ' + @QuestionID + ' varchar(1024) NULL'
		-- Executes the command which creates the column in the temp table
		EXEC(@SQL)
		FETCH NEXT FROM curQuestions INTO @QuestionID
	END
 
	-- Clean up cursor
	CLOSE curQuestions
	DEALLOCATE curQuestions
	---- End of Insert Columns section ----
 
	---- Insert id values into pivot table ----
	-- Create rows in temp table using IDs from Survey table
	INSERT INTO [#tempTable] (SurveyID)
		SELECT     SurveyID
		FROM         Survey
 
	---- Insert data into pivot table ----
	-- Loop through each row in Survey_Answers
	-- Update values in pivot table
 
	-- Declare variables
	DECLARE @SurveyID INT, @QuestionID2 VARCHAR(20), @Answer VARCHAR(1024), @CurrentSurveyID INT
 
	-- Initialize variables
	SET @CurrentSurveyID = -1
	SET @SQL = ''
 
	-- Declare cursor to loop through table
	DECLARE curAnswers CURSOR FOR
	SELECT     Survey_Answers.SurveyID, Survey_Answers.QuestionID, Survey_Answers.Answer
	FROM         Survey_Answers INNER JOIN
						  Survey ON Survey_Answers.SurveyID = Survey.SurveyID
	ORDER BY Survey_Answers.SurveyID
 
	OPEN curAnswers
 
	FETCH NEXT FROM curAnswers INTO @SurveyID, @QuestionID2, @Answer
	WHILE @@FETCH_STATUS=0
	BEGIN
		IF @CurrentSurveyId<>@SurveyId
			BEGIN
				-- This will run at the end of a set of questions related to one survey
				-- And initializes variables for next set of questions
				IF @sql<>''
					BEGIN
						SET @SQL = STUFF(@SQL, LEN(@SQL), 1, ' WHERE (SurveyID = ' + CONVERT(VARCHAR, @CurrentSurveyId) + ');')
						EXEC(@SQL)
					END
				SET @SQL = 'UPDATE [#tempTable] SET'
				SET @CurrentSurveyId = @SurveyId
			END
 
		-- Update values in pivot table
		SET @SQL = @SQL + ' ' + @QuestionId2 + ' = ''' + @Answer + ''','
		FETCH NEXT FROM curAnswers INTO @SurveyID, @QuestionID2, @Answer
 
		-- This section takes care of the last row since it will not go through the IF @sql<>'' code above. Uses same code as that section
		IF @@FETCH_STATUS = -1
			BEGIN
				SET @SQL = STUFF(@SQL, LEN(@SQL), 1, ' WHERE (SurveyID = ' + CONVERT(VARCHAR, @CurrentSurveyId) + ');')
				EXEC(@SQL)
			END
	END
 
	-- Clean up answers cursor
	CLOSE curAnswers
	DEALLOCATE curAnswers
 
	-- Select values from created table
	SELECT     [#tempTable].*, Survey.DateCreated
	FROM         Survey INNER JOIN
		[#tempTable] ON Survey.SurveyID = [#tempTable].SurveyID
	ORDER BY Survey.SurveyID 
 
	-- Clean up the pivot table
	DROP TABLE #tempTable
END

Let us know what you think about our approach or if you have any questions.

Tags: , , , ,
Posted in Technology | No Comments »