Posts Tagged 'SQL Server 2005'



Using SSIS to Export a Fixed Width Flat File

To begin, open Business Intelligence Development Studio (or Visual Studio, if you have that installed) and under Business Intelligence projects, create a new Integration Services Package. You will also need a directory at c:\export to contain the output flat file.
Start by adding a new database connection on the Connection Manager tab (this is usually at the bottom of the SSIS Package Designed). For the purposes of this tutorial I will be using the AdventureWorks database my local machine. This connection will be the source of the data for the flat file.
Database Connection

Database Connection

After you create the database connection, go ahead and create the flat file destination connection. I used the c:\export directory and named my export file Export.txt, but anything will work. On the format drop down, select “Fixed width.”

Data Destination

Data Destination

Please ignore the “Column” sub-menu on the right for now. Select “Advanced” from the sub-menu on the right. Now we will create all of our columns. For this tutorial I created a mail merge type export using First Name, Last Name, Address, City, State and Zip. Add one column to handle the line feed/carriage return. Without this column the package output will concatenated into on one row.

Data Destination Columns

Data Destination Columns

On the Control Flow tab of the SSIS Package, add a data flow task. After adding the task, select the Data Flow tab. Add an OLE DB Source. On the OLE DB Connection Manager, the database connection we created earlier should already be selected. If it is not, select the connection we created at the beginning of the tutorial. The T-SQL used to select the data is below. Please note that there is no data formatting in the T-SQL, only the inclusion the column to handle the line feed/carriage return.

ANSI decimal character 10 is line feed, CHAR(10) in T-SQL, and ANSI decimal character 13 is a carriage return, CHAR(13) in T-SQL. Sometimes these characters are used in conjunction, depending on the format, but for our purposes we must use only one or the other.

SELECT
    pc.LastName,
    pc.FirstName,
    pa.AddressLine1,
    pa.City,
    psp.StateProvinceCode,
    pa.PostalCode,
    CHAR(10) CRLF
FROM
    Person.Contact pc
INNER JOIN HumanResources.Employee hre
    ON pc.ContactID = hre.ContactID
INNER JOIN HumanResources.EmployeeAddress hrea
    ON hre.EmployeeID = hrea.EmployeeID
INNER JOIN Person.Address pa
    ON hrea.AddressID = pa.AddressID
INNER JOIN Person.StateProvince psp
    ON pa.StateProvinceID = psp.StateProvinceID
WHERE
    psp.CountryRegionCode = 'US' ;
Data Source

Data Source

One of the issues that you will face with any fixed width export are columns whose width is greater than the fixed width destination. We are going to handle this with a Data Conversion task. Be sure to give the converted column a friendly name, other than “Copy of ” and the column and set the length of the converted field to the same length of the destination fixed width column.

Data Conversion

Data Conversion

To make sure that the package succeeds if the width of the origin column is greater than the destination column, we will set the conversion to ignore all truncation errors. For these purposes it is important not to confuse width and length. A column with a data type of nchar(4) has a width of 4 but may have a length of 0 to 4. The data length may be less than or equal to destination, but if the width is greater it may prove problematic.

Error Handling

Error Handling

After adding the data conversions and error handling, let’s map the converted columns to our flat file destination columns.

Flat File Mapping

Flat File Mapping

That’s it. Run the package and you will see the fruits of your labor.
~Ron

UPDATE: The zipped solution can be downloaded from here. WordPress does not allow files of this type, so it is available via Dropbox.

Advertisements

Scripting Role and User Permissions

I recently had to copy a large number of databases to a development environment. There was several hundred gigabytes of files, so I went with stop sql services, copy, restart services and attach new databases route.

Before copying the files, I migrated the users. After attaching the database files to the new instance, I ran the orphaned user scripts. Everything seemed to have swimmingly.

A couple of hours later, a developer called me up and said that they were running into permission errors executing stored procedures. There were a lot of scripts out there for user permissions, but none of them seemed to fit my specific needs, especially since this was SQL 2000. So, on that note, here is my addition to that myriad of scripts for SQL Server permissions.

/* SQL 2005/2008 */
DECLARE
 @Login varchar(50),
 @Role varchar(50);

SET @Login = NULL;
SET @Role = NULL;

SELECT
 perm.state_desc
 + SPACE(1)
 + perm.permission_name
 + ' on ['
 + s.name
 + '].['
 + o.name
 + '] to ['
 + prin.name
 + '];'
 + CHAR(10)
 + 'GO'
 + CHAR(10)
 COLLATE LATIN1_General_CI_AS
FROM
 sys.database_permissions perm
INNER JOIN
 sys.objects o ON
 perm.major_id = o.OBJECT_ID
INNER JOIN
 sys.schemas s ON
 o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN sys.database_principals prin ON
 perm.grantee_principal_id = prin.principal_id
WHERE
 ((prin.type = 'U' AND prin.name = @Login) OR @Login IS NULL) AND
 ((prin.type = 'R' AND prin.name = @Role) OR @Role IS NULL);

/* SQL 2000 */
DECLARE
 @Login varchar(50),
 @Role varchar(50),
 @ObjectName varchar(50);

SET @Login = NULL;
SET @Role = NULL;
SET @ObjectName = '[admin_getStoreOrderTimes]';

SELECT
 CASE ProtectType
 WHEN 204 THEN 'GRANT_W_GRANT'
 WHEN 205 THEN 'GRANT'
 WHEN 206 THEN 'DENY' END
 + SPACE(1)
 + CASE Action
 WHEN 26 THEN 'REFERENCES'
 WHEN 178 THEN 'CREATE FUNCTION'
 WHEN 193 THEN 'SELECT'
 WHEN 195 THEN 'INSERT'
 WHEN 196 THEN 'DELETE'
 WHEN 197 THEN 'UPDATE'
 WHEN 198 THEN 'CREATE TABLE'
 WHEN 203 THEN 'CREATE DATABASE'
 WHEN 207 THEN 'CREATE VIEW'
 WHEN 222 THEN 'CREATE PROCEDURE'
 WHEN 224 THEN 'EXECUTE'
 WHEN 228 THEN 'BACKUP DATABASE'
 WHEN 233 THEN 'CREATE DEFAULT'
 WHEN 235 THEN 'BACKUP LOG'
 WHEN 236 THEN 'CREATE RULE' END
 + SPACE(1)
 + 'ON'
 + SPACE(1)
 + '['
 + USER_NAME(o.uid)
 + '].['
 + OBJECT_NAME(o.id)
 + ']'
 + SPACE(1)
 + 'TO'
 + SPACE(1)
 + '['
 + u.name
 + '];'
 + CHAR(10)
 + 'GO'
 + CHAR(10)
FROM
 sysusers u
INNER JOIN
 sysprotects p ON
 u.uid = p.uid
INNER JOIN
 sysobjects o ON
 p.id = o.id
WHERE
 ((IsLogin = 1 AND u.name = @Login) OR @Login IS NULL) AND
 (((u.IsSQLRole = 1 OR u.IsAppRole = 1) AND u.name = @Role) OR @Role IS NULL) AND
 (OBJECT_NAME(o.id) = @ObjectName OR @ObjectName IS NULL) AND
 u.name NOT IN ('public','db_owner','db_accessadmin','db_securityadmin','db_ddladmin',
 'db_backupoperator','db_datareader','db_datawriter','db_denydatareader',
 'db_denydatawriter','guest');

~Ron

Script Column Default Values

Another database developer had copied several database from our model office back to our development environment. When we started to wire up out applications I noticed that there were no indexes, defaults, keys, or constraints of any kind. Just heaps.

Adding these constraints is fairly straigtforward, and there are many tools out there to do that. But I could not find a tool that would script just the default constraints without also dropping and/or creating the table. It took a couple of days to get the databases moved over in the first place, but there were over a thousand default constraints, which could also take several days if done manually.

I was sure that there was a way to script this, but after over an hour of searching I decided that I burned enough time and that I would write one myself. I don’t know how often anyone would come across this issue, but here it is anyways.

SELECT
    'ALTER TABLE dbo.' + OBJECT_NAME(s.id) + ' ADD CONSTRAINT DF_' + OBJECT_NAME(s.id) + '_' + sc.name + ' DEFAULT ' + c.text + ' FOR ' + sc.name
FROM
    dbo.sysconstraints s
INNER JOIN syscolumns sc
	ON s.id = sc.id AND
	   s.colid = sc.colid
INNER JOIN dbo.syscomments c
    ON s.constid = c.id
WHERE
    s.status & 5 = 5

Debugging Stored Prcedures in Visual Studio

I was speaking with a developer friend of mine the other day and the topic of debugging stored procedures came up. My friend was unaware that this was even possible, so I thought that I would share.

In SQL2000 debugging was as simple as right clicking on a stored procedure in Query Analyzer and selecting Debug from the context menu. This was a simple, yet effective method, but is very limited in its capabilities. That feature was removed in SSMS (SQL Server Management Studio).

For SQL 2005 and above, this can be done in Visual Studio. In server explorer create a data connection. Drill down to the stored procedure that you want to debug and click “Step Into Stored Procedure.” From here you can step though the stored procedure.

Alternatively, you can right click on the stored procedure and select “Open” from the context menu. From there you can set breakpoints, change SQL blocks, execute SQL blocks and step into the stored procedure.

My two favorite features of debugging stored procedures in Visual Studio are the ability to see and change variables as you step through the code and the ability to see the managed code when you open a CLR procedure.

For a step by step explanation (with pictures) you can check out SQL Team‘s article, which explains the steps better than I.

Happy debugging!

~Ron

Helpful SQL Server Date Functions

One of my common duties is to create and/or update reports in SSRS (SQL Server Reporting Services). A large number of these reports ran from very unclean data. To compensate for this, the original developers handled the data in a variety of inconsistent manners, mostly around dates. Although this may have gotten the job done originally, it does nothing for the quality of the report.

As these reports were updated, we began using standard date functions to improve development time and report quality.


/* First Day of the Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

/* Last Day of the Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,GETDATE())),0))

/* First Day of Next Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,GETDATE())),0)

/* Last Day of Next Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,2,GETDATE())),0))

/* First Day of Last Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())),0)

/* Last Day of Last Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

/* First Day of the Year */
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

/* Last Day of the Year */
SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,DATEADD(yy,1,GETDATE())),0))

/* First Day of the Quarter */
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)

/* Last Day of the Quarter */
SELECT DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,DATEADD(qq,1,GETDATE())),0))

/* First Monday of the Month */
SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,6 - DATEPART(DAY,GETDATE()),GETDATE())),0)

~Ron

Export To Excel

This is a pretty simple console application which I wrote for exporting query results into excel. We prepare a lot of reports which we send it as excel files. It was getting tedious for us to copy the results and then paste it in excel. So Ron asked me to create a utility to export the query results directly into Excel. I used the Office 2007 excel interop. Given below is the code for the utility:

using System;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;

namespace ExportToExcel
{
    class Program
    {
        //Usage:
        //ExportToExcel -c, ConnectionString,-q, Query, -d, Directory (exclude the last \),-f FileName (add .xls)
        static void Main(string[] args)
        {
            Console.WriteLine(“Initializing Variables…..”);
            ApplicationClass objExcel = new ApplicationClass();
            Workbook objWorkbook;
            Worksheet objWorksheet;
            DirectoryInfo objDirectoryInfo;
            try
            {
                if (args != null)
                {
                    object objMissingValue = Missing.Value;
                    if (args.Length != 8)
                    {
                        Console.WriteLine(“\nIncorrect number of arguments. (-c connectionstring -q query -d directory -f filename)”);
                        return;
                    }
                   
                    string ConnectionString = “”;
                    string Query = “”;
                    string Directory = “”;
                    string Filename = “”;

                    for (int counter = 0; counter <= args.Length - 1; counter++)                     {                         switch (args[counter])                         {                             case "-c":                                 ConnectionString = args[counter + 1];                                 break;                             case "-q":                                 Query = args[counter + 1];                                 break;                             case "-d":                                 Directory = args[counter + 1];                                 break;                             case "-f":                                 Filename = args[counter + 1];                                 break;                             default:                                 break;                         }                     }                     Console.WriteLine("Initializing Excel.....");                     objWorkbook = objExcel.Workbooks.Add(objMissingValue);                     objWorksheet = (Worksheet)objWorkbook.Worksheets.Add(objMissingValue, objMissingValue, objMissingValue, objMissingValue);                     objWorksheet.Name = "Query Data";                     Console.WriteLine("Opening Data Connection.....");                     SqlConnection objConn = new SqlConnection(ConnectionString);                     objConn.Open();                     SqlCommand objCommand = new SqlCommand(Query, objConn);                     SqlDataAdapter objAdapter = new SqlDataAdapter(objCommand);                     DataSet objDataset = new DataSet();                     Console.WriteLine("Filling Dataset.....");                     objAdapter.Fill(objDataset);                     System.Data.DataTable objDatatable;                     objDatatable = objDataset.Tables[0];                     int ColCounter = 0;                     int RowCounter = 0;                     int Column, Row;                     Row = 1;                     Column = 1;                     Console.WriteLine("Setting Header Information.....");                     //Settting the header                     while (ColCounter <= objDatatable.Columns.Count - 1)                     {                         objWorksheet.Cells[Row, Column] = objDatatable.Columns[ColCounter].ColumnName;                         Column++;                         ColCounter++;                     }                     Console.WriteLine("Generating Data.....");                     //Generating the data                                Row++;                     while (RowCounter <= objDatatable.Rows.Count - 1)                     {                         Column = 1;                         ColCounter = 0;                         while (ColCounter <= objDatatable.Columns.Count - 1)                         {                             objWorksheet.Cells[Row, Column] = objDatatable.Rows[RowCounter][ColCounter].ToString();                             Column++;                             ColCounter++;                         }                         Row++;                         RowCounter++;                     }                     Console.WriteLine("Checking for Directory Existence and Saving file.....");                     objDirectoryInfo = new DirectoryInfo(Directory);                     //Updated on 05/08/2009 by Raja                     //Checking for the existence of Directory before saving the file since Excel is not automatically creating the file                     //Starts here                     if (objDirectoryInfo.Exists) //all is well 🙂                         objWorkbook.SaveAs(Directory + "\\" + Filename, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue, XlSaveAsAccessMode.xlNoChange, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue);                     else                     {                         //oops....create the directory and then save the file                         objDirectoryInfo.Create();                         objWorkbook.SaveAs(Directory + "\\" + Filename, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue, XlSaveAsAccessMode.xlNoChange, objMissingValue, objMissingValue, objMissingValue, objMissingValue, objMissingValue);                     }                     //Ends here                     objWorkbook.Close(false, objMissingValue, objMissingValue);                     Console.WriteLine("Successfully exported the data to the given file");                                    }             }             catch (Exception ex)             {                 objExcel.Quit();                 objWorksheet = null;                 objWorkbook = null;                 objExcel = null;                 objDirectoryInfo = null;                 Console.WriteLine(ex.Message);             }                     }             } } [/sourcecode] As always I would appreciate your feedback. Hope this helps. Happy Programming!!! Cheers, Raja

How to Upload and Download Image/BLOB Data in SQL Server

There was a very interesting post over on SQL Authority on how to upload image data to SQL Server. The question was posed, “How do you then retrieve that data from SQL Server.”

You could build an application and retrieve the data through a GUI, but that would be a rather cumbersome process if you had multiple images to download.

Within SQL Server itself, this requires a multi-step process of created a BCP format file and then using BCP QueryOut to create the actual image files. Fast and efficient, but it could be better.

My preferred method would be to use CLR. Once you have registered the assembly, making sure to mark it for external access, this task can be accomplished in a single step.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void usp_RetrieveBlobData(string query, string outpath)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(query, connection);
            SqlDataReader reader = command.ExecuteReader();

            using (reader)
            {
                while (reader.Read())
                {
                    FileStream fs = File.Create(outpath);
                    BinaryWriter bw = new BinaryWriter(fs);

                    byte[] file = (byte[])reader.GetSqlBinary(0);

                    bw.Write(file);

                    bw.Close();
                    fs.Close();
                }
            }
        }
    }
};

NOTE: You will also need to enable CLR on the server.

~Ron