SQL Server

Parent-Child Table Dependency Script

Aalam RangiLeave a comment

Requirement:

You need to delete and then insert data into tables which have parent-child relationships defined via primary and foreign keys. The delete and insert commands have to be executed in proper sequence.

The sequence for DELETE is Child-before-Parent.

The sequence for INSERT is Parent-before-Child.

I created a quick script in response to a forum post. The script is not the best out there because –

  • It does not handle circular references in relationships
  • It takes 30 seconds or more if run on a database with hundreds of tables

I’m sharing it for my future reference and probably others with smaller number of tables would find it useful too.

Download script at –
http://gallery.technet.microsoft.com/Parent-Child-Table-45fa5268

Further reference:
For a faster (but not with all these output columns) script, look at Erland Sommarskog’s response in the forum thread.

Script output would look like the example below –

TableDependencyReport

How to Fix Error Code: 80004005 in DTS Caused by 64-bit DB2 ODBC Driver

Aalam RangiLeave a comment

Issue:

Data Transformation Services packages (DTS) that use DB2 as an ODBC source, fail when the execution environment is upgraded to 64-Bit Windows Server 2008, Windows 7 etc.

Error Message:

The error logs of the DTS would show the following message –

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Cause:

DTS are 32-bit applications and need the Data Source Names (DSN) based on 32-bit ODBC drivers. A 64-bit Operating System points to the 64-bit drivers by default and uses the ODBC Data Source Administrator at –

C:WINDOWSsystem32odbcad32.exe

The standard DSN creation scripts used by system administrators might use the 64-bit version because it is at the default location.

The 32-bit ODBC Data Source Administrator is in –

C:WINDOWSSysWOW64odbcad32.exe

The required DSN should be created again using this 32-bit version and it will update the Windows registry or the environment path variables appropriately.

Fix:

Start the 32-bit ODBC Data Source Administrator Wizard from the SYSWOW64 directory.

C:WINDOWSSysWOW64odbcad32.exe

Go to System DSN tab and click on Add.

clip_image001

Select the available IBM ODBC driver.

clip_image002

Give a DSN name.

clip_image003

Confirm the action.

clip_image004

The last message box verifies if you want to overwrite the existing DSN alias in DB2CLI.INI file. This file is at the below location on a 64-bit machine –

C:ProgramDataIBMDB2DB2_V95db2cli.ini

Clicking on No will abort the wizard. Click on Yes to continue. The timestamp for the INI file would be updated. If you are just recreating an existing alias as in the example above, the file content would appear to be the same, though the ODBC Data Source Administrator Wizard will create or modify the appropriate registry and environment variable entries.

Summary:

Microsoft maintains a backward compatibility feature known as WOW64 (Windows-32-On-Windows-64) that lets older 32-bit applications to run smoothly. This is implemented by maintaining a separate copy of the necessary 32-bit run time resources (configuration utilities, drivers, registry settings, other DLLs etc.) in different folders/locations.

This example refers to an ODBC DSN for a DB2 server but the concept could be relevant in other such scenarios where there is a conflict between 32 and 64-bit drivers.

Reference:

How to point to 32-bit IBM DB2 ODBC driver on 64-bit Microsoft system.
http://www-01.ibm.com/support/docview.wss?uid=swg21384435

Use a One-Time Schedule for Adhoc Job Executions

Aalam RangiLeave a comment

If there is ever a need to run a SQL Server job on an adhoc basis outside of its normal schedule, don’t change its original schedule. Rather, create a new one-time only schedule for that job and leave it like that even after the the execution.

If you modify the original schedule then you’d have to remember to revert it back. This can be easily missed out and then the job would be running at the modified schedule after that! Oh, so you say you have a water-tight reminder mechanism and you’ll never forget to revert it?! Please read the next line.

If there was a requirement to run the job out-of-schedule once, then it could happen again too. The one-time schedule would be handy at that time and can be reused.

Oracle Sample Tables’ Script for SQL Server

Aalam Rangi4 Comments

I work primarily on SQL Server these days but I had originally started learning SQL on Oracle. I find the sample tables EMP, DEPT, BONUS, SALGRADE and DUMMY provided with Oracle to be useful for beginners to practice SQL commands. Oracle provides the script by the name of DEMOBLD.SQL to create these tables which can be found in the <OracleHome>sqlplusdemo folder of your Oracle installation or at this URL. Some more useful scripts for Oracle users can be found at this OraFAQ page.

I thought that few folks might like to have those tables on SQL Server so I made some changes to the script so that it can run on SQL Server too. It might prove useful if you don’t have access to an Oracle installation but want want to practice SQL commands with these tables. Of course, MS SQL Server does not support all the SQL syntax as supported by Oracle, but the tables can be handy in some cases.

The SQL Server compatible script is at the TechNet link below. The basic difference from the original script is in the datatypes and the datetime convert function. The script will drop any tables with the same names in the current database so be sure that existing tables do not have any critical data.

Download URL –
http://gallery.technet.microsoft.com/TSQL-Script-to-Create-6bc846bd

DTS to SSIS Conversion Effort Estimate Workbook

Aalam Rangi6 Comments

I had to work on providing an effort estimate to convert multiple DTS packages into corresponding SSIS packages. An automated conversion tool was not available to us so the SSIS packages were to be built from scratch based on the existing DTS packages.

I took this opportunity to catalog all our DTS packages and created an Excel workbook to aid in the estimation. The formulas in the workbook calculate the effort in person-days for the rewrite of the current DTS logic into a SSIS package.

I approached this requirement as follows –

  1. Open each DTS package and used the Excel workbook to make an inventory of the type and number of all the tasks and connections in the package. Obviously, this is a repetitive and the most time consuming activity in the whole process.
  2. Decide on an estimated number of minutes required to recreate each type of task/connection in SSIS. This time could vary depending on the developer’s experience and skill.
  3. Lastly, put in standard time additions for activities like analysis, error handling, logging and documentation.

You can try that estimation workbook by downloading it at the link give below and tweaking the values to suit your needs. While you are there, don’t forget to rate it! 🙂 Please let me know in the comments section of this post if there are any suggestions or questions.

Download from the Technet Gallery link –
DTS to SSIS Conversion Effort Estimation Template.xlsx  approx size: 30 KB
http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957

Do you have some of your own SSIS estimation tips? Please share in the comments section here or at this SSIS Development Effort Estimation Methodologies Wiki on TechNet.