Ramoji Ryali's Fundas

Could not load package because of error 0xC0011002. Failed to open package file due to error 0x80070005 "Access is denied."

2011-01-06T13:23:00Z

Came across this error while executing a SSIS package through a SQL Agent Job on a SQL Server 2008 server.The job is running under a windows security account and the SSIS package is stored on file system of the server.

The exact error message received was as shown below.

Executed as user: Domain\User. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:11:37 PM Error: 2011-01-03 23:11:37.14 Code: 0xC0011007 Source: {1BDBB290-5831-476E-97F5-78DA45F876F4} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2011-01-03 23:11:37.14 Code: 0xC0011002 Source: {1BDBB290-5831-476E-97F5-78DA45F876F4} Description: Failed to open package file "C:\SSIS\MyPackage.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "D:\SSIS\MyPackage.dtsx" because of error 0xC0011002. Description: Failed to open package file "D:\SSIS\MyPackage.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. Source: {1BDBB290-5831-476E-97F5-78DA45F876F4} Started: 11:11:37 PM Finished: 11:11:37 PM Elapsed: 0.031 seconds. Process Exit Code 4. . NOTE: The step was retried the requested number of times (5) without succeeding. The step failed.

Since the error message is talking about access, verified whether the security account with which SQL Agent Service is running has access to the server folder in which SSIS package is stored. Noticed that the security account doesn't have the access and the job started working after giving the read & write permission on the folder.

Let me know if this has helped you.

Comparing data between two tables in SQL Server

2010-07-01T13:58:00Z

As a database developer sometime or the other you might have come across this requirement and it can be achieved using many ways including built-in features like Checksum and TableDiff utility. However, I would like to share an alternate way to achieve the objective, which might be useful in some situations.

Usually the intention of comparison is to find out the missing rows from either of the tables or both. This where the operator "EXCEPT" comes very handy. Let us see it with an example.

--Create two Tables--

CREATE TABLE TableA(ID Int, Name Varchar(256))

CREATE TABLE TableB(ID Int, Name Varchar(256))

INSERT INTO TableA VALUES(1,'A'),(2,'B'),(3,'D')

INSERT INTO TableB VALUES(1,'A'),(2,NULL),(3,'C')

--Get rows from TableA that are not found in TableB--

SELECT * FROM TableA

EXCEPT

SELECT * FROM TableB

--Get rows from TableB that are not found in TableA--

SELECT * FROM TableB

EXCEPT

SELECT * FROM TableA

I have used the above concept for comparing data between heavy tables and the performance is satisfactory. I could get the result in 3 Min 55 sec when I compared a table having 1.18 million rows. The comparison can't be performed for some of the data types of the columns like XML, Text and Image etc. So, I have written a generic stored procedure that does the comparison between two tables by excluding the columns that can't be compared. It also allows to compare tables across databases and schemas. Validations and error handling are not added as the intention is to just present the idea.

----------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE CompareTableData

(

@SourceDB sysname

,@SourceSchema sysname

,@SourceTable sysname

,@TargetDB sysname

,@TargetSchema sysname

,@TargetTable sysname

)

BEGIN

SET NOCOUNT ON

DECLARE @SQL NVarchar(Max)

DECLARE @ColList Varchar(Max)

--Concatenate the column list by excluding the data types that can't be used in comparision--

SET @SQL = 'SELECT @ColList = CASE WHEN @ColList IS NULL THEN '''' ELSE @ColList + '','' END + SC.Name FROM ' + @SourceDB + '.sys.columns SC '

SET @SQL = @SQL + ' INNER JOIN ' + @SourceDB + '.sys.Types ST ON SC.system_type_id = ST.system_type_id WHERE object_id = object_id(@SourceTable) '

SET @SQL = @SQL + ' AND ST.Name NOT IN (''xml'',''Text'',''Image'',''Geometry'',''Geography'')'

EXEC sp_executesql @SQL,N'@ColList varchar(Max) OUTPUT,@SourceTable sysname',@ColList OUTPUT,@Sourcetable

--Get the rows that are missing from Target table--

SET @SQL = 'SELECT ''' + @TargetTable + ''' as RowMissingFrom,' + @ColList + ' FROM ' + @SourceDB + '.' + @SourceSchema + '.' + @SourceTable

SET @SQL = @SQL + ' EXCEPT '

SET @SQL = @SQL + ' SELECT ''' + @TargetTable + ''' as RowMissingFrom,' + @ColList + ' FROM ' + @TargetDB + '.' + @TargetSchema + '.' + @TargetTable

EXEC sp_executesql @SQL,N'@ColList varchar(Max)',@ColList

--Get the rows that are missing from Source table--

SET @SQL = ' SELECT ''' + @SourceTable + ''' as RowMissingFrom,' + @ColList + ' FROM ' + @TargetDB + '.' + @TargetSchema + '.' + @TargetTable

SET @SQL = @SQL + ' EXCEPT '

SET @SQL = @SQL + 'SELECT ''' + @SourceTable + ''' as RowMissingFrom,' + @ColList + ' FROM ' + @SourceDB + '.' + @SourceSchema + '.' + @SourceTable

EXEC sp_executesql @SQL,N'@ColList varchar(Max)',@ColList

END

-----------------------------------------------------------------------------------------------------------

Sample usgae of the procedure is: EXEC CompareTableData 'DB1','dbo','TableA','DB2','HR','TableB'

Please share your feedback if you find this interesting.

Message "TFS10139: The following check-in policies have not been satisfied" comes in VSTS2010

2010-06-14T07:12:49Z

Got this message after the upgrade from VSTS 2008 to VSTS 2010 while checking in a file. The message disappeared after installing "Team Foundation Server Power Tools April 2010" from the below location.

http://visualstudiogallery.msdn.microsoft.com/en-us/3e8c9b68-6e39-4577-b9b7-78489b5cb1da

Hope this helps :-)

Error while enabling CDC on a table. The server principal "xyz" is not able to access the database "msdb" under the current security context.

2010-05-13T06:01:00Z

We came across the following error after successfully enabling CDC on a Database and then trying to enable CDC on a table in SQL Server 2008 SP1 (Version 10.0.2531.0)

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 607

Could not update the metadata that indicates table [dbo].[MyTable] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 916: 'The server principal "S-1-9-3-3045654011-13150378417-261222020-2009464159." is not able to access the database "msdb" under the current security context.'. Use the action and error to determine the cause of the failure and resubmit the request.

Since the failure message is saying the error occured while creating the Capture job, we tried explicitly creating the same using the system procedure "sp_cdc_add_job" as shown below and it solved the issue for us.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

USE MyDatabase

--Enable CDC on the database

EXECUTE sys.sp_cdc_enable_db

--Manually create Capture job to avoid the job creation error

EXEC [sys].[sp_cdc_add_job] @job_type = N'capture'

--Enable CDC on the tables

EXECUTE sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = 'AdjustmentReason', @role_name = N'cdc_admin', @supports_net_changes = 1, @index_name = null, @captured_column_list = null, @filegroup_name = N'PRIMARY'

If you want more details on the above error, you can refer to the link http://msdn.microsoft.com/en-us/library/ee342155.aspx.

Please drop me an encouraging note if this work around has helped in solving your error. Thanks in advance.

SSIS Data Flow Task fails with Unspecified Error

2010-01-05T14:01:00Z

We have few SQL Agent jobs on SQL Server 2008 (10.0.2531) and these jobs are executing SSIS packages, which are created using BIDS 2008. The jobs are scheduled to run daily at a low frequency. The intention is to run these jobs like a demon service.

We started getting the "Unspecified Error" at random intervals of time and the jobs always failed at the Data Flow Task only.

The job's error log has the following message. However, when the error is captured using an event handler in the package, an error code of "-2147467259" is reported.

Code: 0x80004005

Source: <Data Flow Task Name> SSIS.Pipeline

Description: Unspecified error End Error

DTExec: The package execution returned DTSER_FAILURE (1)

After struggling for two weeks with the error, we finally fixed it by limiting the SQL Server Memory usage. We restricted SQL Server to consume 50% at the max. The reason for doing this is to leave enough memory to other services like Integration Services. Otherwise, over a period of time, SQL Server consumes almost all the available memory and releases it only when demanded by OS.

Here is the exact script we used, which you can tweak based on the percentage you want to allocate depending on your project requirement. Let me know if this blog has helped you.

DECLARE @TotalMemInGB int

DECLARE @MinMemInMB int

DECLARE @MaxMemInMB int

select @TotalMemInGB =(physical_memory_in_bytes/(1024*1024)/1000) from sys.dm_os_sys_info

SET @MinMemInMB= (@TotalMemInGB * 0.5) * 1024 -- taking 50% of available memory.

SET @MaxMemInMB= (@TotalMemInGB * 0.5) * 1024 -- taking 50% of available memory.

EXEC sp_configure 'show advanced options',1

RECONFIGURE

EXEC sp_configure 'max server memory', @MaxMemInMB

RECONFIGURE

EXEC sp_configure 'min server memory',@MinMemInMB

RECONFIGURE

SSIS package fails with Protocol error in TDS stream

2009-12-30T13:02:00Z

Encountered this error while running a SSIS package through a SQL Agent Job on SQL Server 2008. The processing in the package is a pretty simple and the purpose is to export data from few tables from one SQL Server to another. Both the source and target servers are on SQL Server 2008 (10.0.2531) and Data Flow Task (with OLEDB source and destination tasks) is used for data export. The package is created using Visual Studio 2008 and the job is running on the destination server.

The job continuously failed with the following error and in each run the failure occured at a different table.

Source: DFT_MyTable Source - MyTable

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005

Description: "Protocol error in TDS stream". An OLE DB record is available

The issue is solved when the Network Protocol used to connect to the source SQL Server is changed from TCP/IP to NamedPipes. Basically, an alias for the source is created on the destination server using the below steps.

1. Go to Start--> All Programs--> Microsoft SQL Server 2008--> Configuration Tools --> SQL Server Configuration Manager

2. Expand the node SQL Native Client 10.0 Configuration

3. Right click on "Aliases" and then select "New Alias..."

4. Provide the IP address of the Source SQL server against the "Server" field and specify the Source server name against the "Alias Name" field.

5. Choose the protocol as "NamedPipes" and click OK

Please drop me a note if you have come across this error and this blog has helped you in resolving the issue.

Could not update the metadata that indicates database is enabled for Change Data Capture. The failure occurred when executing the command SetCDCTracked(Value = 1)

2009-11-20T06:59:00Z

This error message comes when you are trying to enable CDC on a SQL Server 2008 database for which the owner is not "sa". The exact error message will be as shown below.

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186

Could not update the metadata that indicates database [XXXXX] is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15404: 'Could not obtain information about Windows NT group/user 'Domain\user', error code 0x5.'. Use the action and error to determine the cause of the failure and resubmit the request.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db_internal, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

The fix for this is to change the databse owner to "sa" by executing the below script.

USE <Database>

EXEC sp_changedbowner 'sa'

Read the below KB article for more details.

http://support.microsoft.com/kb/913423

Explicit value must be specified for identity column in table [XXXXXX] either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column

2009-11-13T09:37:00Z

I encountered this error when explicitly inserting values in an Identity column of a Table that is created in a SQL Server 2008 Database and the DB is not part of any kind of Replication. I have made sure that "Identity_Insert" is turned on for that table before performing the insert. Also I have checked that "NOT FOR REPLICATION" is disabled for that Identity column (Obviously, it is in disabled state in my case as there is no Replication).

After struggling for some time, I noticed that an "Instead of Insert" Trigger exist on the Table. The interesting thing is, no change is happening to the Idenity column in the Trigger definition. However, disabling that Trigger fixed the issue miraculously :-)

Let me know if this fix has helped you too.

Failed to open package file due to error 0x80070020. The process cannot access the file because it is being used by another process. This happens when loading a package and the file cannot be opened or loaded correctly into the XML document.

2009-08-28T18:00:00Z

This error message comes while performing one (Or both) of the following actions.

1. Run SSIS Package using dtexec.exe (Either from a command file or through a Job running on SQL Server 2008)

2. Open SSIS package using VS2008 or BIDS

If you have a log file for the job, the exact error essage you see would be as follows:

Microsoft (R) SQL Server Execute Package Utility
Version 10.0.1600.22 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Error:
   Code: 0xC0011007
   Source: {95765D16-E9AA-4DDA-9932-9DF1281E1208}
   Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error
Error:
   Code: 0xC0011002
   Source: {95765D16-E9AA-4DDA-9932-9DF1281E1208}
   Description: Failed to open package file "E:\MyPackage.dtsx" due to error 0x80070020 "The process cannot access the file because it is being used by another process.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
Could not load package "E:\MyPackage.dtsx" because of error 0xC0011002.
Description: Failed to open package file "E:\MyPackage.dtsx" due to error 0x80070020 "The process cannot access the file because it is being used by another process.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
Source: {95765D16-E9AA-4DDA-9932-9DF1281E1208}

Fix: The fix is same as the one I explained in my other blog. The physical path of SQL Server 2008's DTEXEC.exe should appear first in the PATH environment variable.

Checkin cannot proceed because the policy requirements have not been satisfied.

2009-08-26T16:45:00Z

I got this message while checking-in a file after the fresh installation of Visual Studio 2008 Professional and TFS client.

This message disappeared after installing VSTS 2008 Power Tools. The download link is provided for your quick reference here.

http://www.microsoft.com/downloads/details.aspx?FamilyId=FBD14EEA-781F-45A1-8C46-9F6BA2F68BF0&displaylang=en

Please share your feedback if the same fix has helped you.

Transfer permissions between objects in SQL Server 2008

2009-07-02T16:39:00Z

Sometimes we have to transfer the permissions from one object to another. Here the object could be a table or view. We can write a straight forward script using GRANT and REVOKE statements if this requirement is for one or two objects. But what if more number of objects are involved and the permissions that exist at the time of development might change by the time you deploy the code in production. What I mean is, if you get a list of permissions that already exist on these tables from production and create scripts based on that, there is no guarantee that the same permissions exist when this script goes to production. Obviously, this results in the failure of the script. So, we need an intelligent script rather than a static script in this scenario.

Let us consider a scenario in which a bunch of tables are there and access to these tables by down stream systems is controlled by some database roles. Now, these tables have to be renamed due to some business reason without breaking the downstream systems. What we typically do in this case is, rename the tables using sp_rename and create views with the old table names on top of the renamed tables. The backward compatibility is maintained as we are creating the views with old table names. The permissions given to the database roles on the old table names will be automatically carried over to the newly renamed tables as a part of the renaming itself. But downstream systems still cannot access the views because the permissions exist on the newly renamed tables have to be explicitly transferred to the views. This is where the need comes for a dynamic script that does the permissions transfer from one object to another.

So, here is a generic script that would come handy in this type of scenarios. This SP transfers the permissions from an object (@FromObjectName parameter) to another object (@ToObjectName parameter). Appreciate your feedback if you feel this is useful.

CREATE PROC [dbo].[ResetPermsOnObject] (
@DBName sysname,
@SchemaName sysname = 'dbo',
@ToObjectName sysname,
@FromObjectName sysname
)
WITH RECOMPILE
AS

BEGIN

SET NOCOUNT ON

DECLARE @ObjectName sysname
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @SQL nvarchar(4000)
DECLARE @RetVal Int
DECLARE @CurCount Int
DECLARE @MaxCount Int
DECLARE @Grantee sysname
DECLARE @Action NVarchar(20)
DECLARE @PermList Table
(
Id Int IDENTITY(1,1),
[Owner] sysname,
[Object] sysname,
Grantee sysname,
Gantor sysname,
ProtectType NVarchar(10),
[Action] NVarchar(20),
Grantor sysname
)

-------------Validate arguments----------------------

IF(@DBName IS NULL)
BEGIN
SELECT @ErrorMessage = 'Database Name must be supplied.'
GOTO ABEND
END

IF(@ToObjectName IS NULL)
BEGIN
SELECT @ErrorMessage = 'Old Object Name must be supplied.'
GOTO ABEND
END

IF(@FromObjectName IS NULL)
BEGIN
SELECT @ErrorMessage = 'New Object Name must be supplied.'
GOTO ABEND
END

--Check for the existence of the Database
IF NOT EXISTS(SELECT Name FROM sys.databases where Name =@DBName)
BEGIN
SET @ErrorMessage = 'The specified Database does not exist'
GOTO ABEND
END

--Check for the existence of the Schema
IF(upper(@SchemaName) <> 'DBO')
BEGIN
SET @SQL = 'SELECT @RetVal = COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.schemas WHERE name = ''' + @SchemaName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH
IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No Schema with the name ' + @SchemaName + ' exists in the Database ' + @DBName
GOTO ABEND
END
END

-------------Check for the validity of all the Objects----------------------

--Check for existence of the Old object
SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Objects WHERE name = ''' + @ToObjectName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH

IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No object with the name ' + @ToObjectName + ' exists in the Database ' + @DBName
GOTO ABEND
END

--Check for existence of the New object
SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Objects WHERE name = ''' + @FromObjectName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH

IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No object with the name ' + @FromObjectName + ' exists in the Database ' + @DBName
GOTO ABEND
END

-------Check whether any DBRoles are given permissions or not --------------------------------------------

SET @SQL = 'SELECT @RetVal = COUNT(sdp.name) FROM ' + QUOTENAME(@DBName) + '.sys.sysprotects sp '
    + ' INNER JOIN ' + QUOTENAME(@DBName) + '.sys.sysobjects so ON sp.id = so.id '
    + ' INNER JOIN ' + QUOTENAME(@DBName) + '.sys.database_principals sdp ON sdp.Principal_Id = sp.uid'
    + ' WHERE so.Name = ''' + @FromObjectName + ''''

EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT
IF(@RetVal <= 0)
BEGIN
  PRINT 'No roles are granted permissions on the object ' + @FromObjectName
  GOTO FINAL
END

----------Get the permissions defined on the old object------------------------------------------------------
SET @SQL = 'Use ' + QUOTENAME(@DBName) + '; EXEC sp_helprotect ' + QUOTENAME(@SchemaName + '.' + @FromObjectName)

INSERT INTO @PermList
EXEC sp_executesql @SQL

----Now loop through all the roles and assign the permissions to the old Object ------------------------------

SELECT @CurCount = Min(Id),@MaxCount = Max(Id) FROM @PermList

WHILE(@CurCount <= @MaxCount)
BEGIN
  SELECT @Action = [Action], @Grantee = Grantee FROM @PermList WHERE Id = @CurCount

  SET @SQL = 'USE ' + QUOTENAME(@DBName) + ';GRANT ' + @Action + ' ON OBJECT::' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ToObjectName) + ' TO ' + QUOTENAME(@Grantee)
  EXEC(@SQL)
  IF(@@ERROR <> 0)
  BEGIN
   SET @ErrorMessage = 'Unbale to grant permissions on ' + QUOTENAME(@ToObjectName) + ' to the role ' + QUOTENAME(@Grantee)
   GOTO ABEND
  END

  SET @SQL = 'USE ' + QUOTENAME(@DBName) + ';REVOKE ' + @Action + ' ON OBJECT::' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@FromObjectName) + ' FROM ' + QUOTENAME(@Grantee)
  EXEC (@SQL)
  IF(@@ERROR <> 0)
  BEGIN
   SET @ErrorMessage = 'Unbale to revoke permissions on Object ' + QUOTENAME(@ToObjectName) + ' from the role ' + QUOTENAME(@Grantee)
   GOTO ABEND
  END

  PRINT 'Permissions are successfully applied on ' + QUOTENAME(@ToObjectName) + ' TO the role ' + QUOTENAME(@Grantee)
  SET @CurCount = @CurCount + 1
END

RETURN

ABEND:
RAISERROR 500001 @ErrorMessage
FINAL:
RETURN
END

The AcquireConnection method call to the connection manager failed with error code 0xC0202009

2009-03-10T08:15:00Z

Sometimes the actual error lies somewhere and the error message directs us to look at something else. We end up wasting time in this process as the error message diverted us. Recently, we came across one such kind of scenario while calling a SSIS package in one of the Job steps on SQL Server 2008. The details are as follows.

Error: Login failed for user 'Domain\alias'. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Cannot open database "MyDatabase" requested by the login. The login failed.". End Error Error: 2009-02-26 03:08:23.93 Code: 0xC020801C Source: DFT-MyTask OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MyServer.MyDatabase" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2009-02-26 03:08:23.93 Code: 0xC0047017 Source: DFT-MyTask SSIS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Progress: 2009-02-26 03:08:23.93 Source: DFT-MyTask Validating: 50% complete End Progress Error: 2009-02-26 03:08:23.93 Code: 0xC004700C Source: DFT-MyTask SSIS.Pipeline Description: One or more component failed validation. End Error Error

Job Logic: We are using a script task to dynamically set the connection manager. We are pulling data from a source server using a select query and putting it into a table on a destination server. We are surprised to see login failed error in the first part of the message. Because, the account "Domain\alias" is a sysadmin on the source as well as destination server and the job is also running under the same account.

Analysis: Since the account is sysadmin, the error message is misleading. Also, the second message was of no use as it didn't detail why it couln't acquire connection inspite of having the right permissions. Interestingly, the crux lies in the last part of the message where it says validation failed.

Fix: After breaking our heads why the validation has failed, we couldn't find any reason but noticed that the DFT-MyTask has "DelayValidation" property set to false in the package. So, we changed the DelayValidation property of DFT-MyTask to true and re-ran the job and it succeeded.

Root Cause: As per our understanding, if DelayValidation is not set to true, SSIS engine uses the design time values of a task until it actually runs a task. Since we are dynamically setting the connection manager, instead of using the dynamically set value (Which it uses anayway while running the task), it tried to validate the task with the design time values set for the connection manager. Unfortunately, the service account with which the job is running doesn't have access to the server and database set during the design time for the connection manager. So, the validation of the task failed causing the job to fail.

General Network error. Check your network documentation.

2008-12-24T09:42:00Z

This is a proper netwrok error and has nothing to do with the coding if any of your jobs/queries/packages fail with this message.

Please refer the following article for the workaround.

http://support.microsoft.com/kb/942861/en-us

Another work around we tried and succeeded in our project was as follows:

There is something called Network Speed setting which is usually set to AUTO or a fixed value. You can check this from Start->Control Panel->Network Connections->Local Area Connection->Properties->Configure->Advanced->Speed &Duplex property. Change it to 100 MBPS full or whatever value that matches the configuration of the n/w port on the switch side. It solved the issue for us.

Package migration from version 3 to version 2 failed with error 0xC001700A. The version number in the package is not valid. The version number cannot be greater than current version number.

2008-12-24T09:05:00Z

I guess at least once every SQL developer might have come across this error on SQL Server 2008 while running a SQL job that calls a package.

Reason for the error: Old version of the DTEXEC is picked up by SQL Server instead of the new one. That means, the exe shipped with 2005 is picked up when it is expected to use the one shipped with 2008. So, obviously this happens when SQL Server 2008 is running along with SQL Server 2005 on the same machine. As a result of this, we end up with two versions of DTEXEC executables. One residing in SQL Server 2005 path ("C:\Program Files\Microsoft SQL Server\90\DTS\Binn") and the other in SQL Server 2008 path ("C:\Program Files\Microsoft SQL Server\100\DTS\Binn").

Fix for the error: There are three workarounds to correct this.

1. Hard code the path of SQL Server 2008's DTEXEC while calling the SSIS package as shown below.

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTEXEC.exe /F "D:\MyFolder\MyPackage.dtsx"

2. Rename the old exe in the 2005 path to a different name (Example:- C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEXEC_Old.exe)

3. Go to PATH environmental variable and edit it in such a way that "C:\Program Files\Microsoft SQL Server\100\DTS\Binn" path appears well before the "C:\Program Files\Microsoft SQL Server\90\DTS\Binn" path.

Please drop me a note if this has helped you.

How to open DTS packages in SQL Server 2008?

2008-10-16T08:11:00Z

An error occurs if you try to open a DTS package from Leagcy --> Data Transformation Services in SSMS of 2008. The message says, SQL Server 2000 DTS Designer Components are required to edit DTS packages. Install the special web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExploreUI).

Downloading the following software from Microsoft's download center and Installing fixes this issue in most of the cases.

1. Microsoft SQL Server 2005 Backward compatibility

2. SQL Server 2000 DTS Designer Components

However, in some cases it doesn't solve the problem and the error re-appears even after installing the above software.

Reason for this:

SQL Server uses the library (sqlgui.dll) to open DTS package in GUI mode. This DLL comes with katmai (Located in <SQL Path>\100\Toos\Binn) also but this shouldn’t be used. The DLL that comes with SQL server 2000 (Located in in <SQL Path>\80\Toos\Binn) should be used. SQL server looks for the path of this DLL in the order in which the paths are defined in PATH environment variable. So, we need to make sure 80 comes before 100.

Perform the following steps to fix the issue in such situation. Please note that this workaround should be used in Development environments only. Playing with the environment variable on Production systems might hamper some or the other application and hence avoid this in such cases.

Make sure all the SQL Server Management Studio (SSMS) sessions are closed
Go to ControlPanel --> System --> Advanced --> Environment variables
Update the PATH environment variable in such a way that the path “C:\Program Files\Microsoft SQL Server\80\Tools\Binn\” appears before the “C:\Program Files\Microsoft SQL Server\100\...” paths. On the server, the folder might be Program Files X(86) instead of Program Files. Use the path accordingly.
Launch SSMS and open a DTS package from Legacy à Data Transformation Services
You will get some errors. Click OK. The package opens but the icons can’t be seen.
Just hover the mouse over the icons till you notice icons as white boxes
Exit SSMS
Again launch SSMS and open the same DTS package from Legacy à Data Transformation Services.
Package gets opened properly

Sometimes steps from 4 to 8 have to be performed more than once if the error persists.

Reading DTS and SSIS packages programmatically

2008-09-29T15:33:00Z

Reading a DTS/SSIS programmatically helps a lot in doing impact analysis. Consider a scenario in which all the packages containing a particular table or stored procedure need to identified. Let me give another usage scenario for this. The sql native clinet provider SQLNCLI.1 is no longer supported in SQL server 2008 and it has to be changed to SQLNCLI10.1. If you are migrating to SQL Server 2008 and the provider name has to be changed across all packages, dynamically accessing the DTS/SSIS package using .NET assemblies is the best way to achieve this. Otherwise all the packages have to be opened manually for verifying and modifying the tasks.

Let us see how we can do this using C#. Add the following references for accessing DTS/SSIS object model.

1. Microsoft.sqlserver.Pipelinewrap (Physical location--> C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll)

2. Microsoft.sqlserver.ManagedDTS (Physical location--> C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll)

3. Microsoft.sqlserver.ScriptTask (Physical location--> C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.ScriptTask.dll)

4. Microsoft.sqlserver.VSAHosting (Physical location--> C:\Program Files\Microsoft SQL Server\90\DTS\Binn\Microsoft.SqlServer.VSAHosting.dll)

5. Select "Microsoft DTSPackage Object Library" from the COM tab in the references (Physical Location --> C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DtsPkg.dll)

6. Microsoft.sqlserver.Exec80PackageTask (Physical location--> C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.Exec80PackageTask.dll)

Use the following namespaces:

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask;

using Microsoft.SqlServer.Dts.Tasks.ScriptTask;

Now Load and access the DTS package that is physically stored on the file system:

string PkgLocation = "E\\Test\\abcd.dts";

string PkgPwd = "abc123";

DTS.PackageClass dtsPkg = new DTS.PackageClass();

object pVarPersistStgOfHost = null;

dtsPkg.LoadFromStorageFile(PkgLocation, PkgPwd, null, null, null, ref pVarPersistStgOfHost);

MessageBox.Show(dtsPkg.Name); //Get Package Name

MessageBox.Show(dtsPkg.Tasks.Count.ToString()); //Get number of tasks present in the package

for (int i = 1; i <= dtsPkg.Tasks.Count; i++)

{

MessageBox.Show(dtsPkg.Tasks.Item(i).Name); //Get Task Name

MessageBox.Show(dtsPkg.Tasks.Item(i).Description); //Get Task Description

for (int j = 1; j <= dtsPkg.Tasks.Item(i).Properties.Count; j++)

{

MessageBox.Show(dtsPkg.Tasks.Item(i).Properties.Item(j).Name); //Get property name

if (dtsPkg.Tasks.Item(i).Properties.Item(j).Value != null)

{

MessageBox.Show(dtsPkg.Tasks.Item(i).Properties.Item(j).Value.ToString()); //Get Property Value

}

Now Load and access the SSIS package that is physically stored on the file system:

string PkgLocation = "E\\Test\\abcd.dtsx";

string PkgPwd = "abc123";

string strNewPackage = "Newabcd";

Microsoft.SqlServer.Dts.Runtime.Application ssisApplication;

ssisApplication = new Microsoft.SqlServer.Dts.Runtime.Application();

Package dtsPkg;

if (PkgPwd != null)

{

dtsApp.PackagePassword = PkgPwd;

}

PkgLocation = @"" + PkgLocation;

dtsPkg = dtsApp.LoadPackage(PkgLocation, null);

foreach (Executable e1 in exes)

{

TaskHost T1 = (TaskHost)e1;

DtsProperties dp = T1.Properties;

foreach (DtsProperty p in dp)

{

MessageBox.Show(T1.Name.ToString()); //Get Task Name

MessageBox.Show(p.Name.ToString()); //Get Task's property Name

if (p.Get == true)

{

MessageBox.Show(p.GetValue(T1).ToString()); //get Task's property value

}

if (T1.InnerObject.ToString() == "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask") //This block is specific to Script Task

{

ScriptTask ST = (ScriptTask)T1.InnerObject;

ScriptTaskCodeProvider scp = ST.CodeProvider;

string strMoniker = "dts://Scripts/" + ST.VsaProjectName + "/ScriptMain.vsaitem";

scp.GetSourceCode(strMoniker);

//Let us change the provider name

scp.PutSourceCode(strMoniker, scp.GetSourceCode(strMoniker).Replace("SQLNCLI.1", "SQLNCLI10.1"));

//Save the package in the default folder (C:\Program Files\Microsoft SQL Server\90\DTS\Packages) with a new name. strNewPackage variable holds the new name

dtsApp.SaveToDtsServer(dtsPkg, null, @"File System\" + strNewPackage, ".");

}

if (T1.InnerObject.ToString() == "System.__ComObject") //This block is specific to the inner task (Ex:- DataFlow Task will have inner tasks for source and destination)

{

MainPipe m = (MainPipe)T1.InnerObject;

IDTSComponentMetaDataCollection90 mdc = m.ComponentMetaDataCollection;

foreach (IDTSComponentMetaData90 md in mdc)

{

foreach (IDTSCustomProperty90 cprop in md.CustomPropertyCollection)

{

MessageBox.Show(T1.Name.ToString());

MessageBox.Show(md.Name.ToString()); //Get Inner Task Name

MessageBox.Show(cprop.Name.ToString()); //Get Inner Task's property Name

MessageBox.Show(cprop.Value.ToString()); //Get Inner Task's property Value

}

Hope you find it interesting.

Moving indexes from one filegroup to another in SQL Server

2008-09-26T14:53:00Z

This is a pretty simple task but I would like to provide a generic method for doing this. It will help if the requirement is one (or all) of the folowing. I have only considered non-clustered indexes along with the Inclued columns and filtered indexes. No point in moving clustered indexes as moving them would move the data as well. This is because the leaf level of a clustered index is nothing but a data page.

1. Move all the Indexes of a Table/View

2. Move specific Indexes of a Table/View

2. Move all the indexes present in a Database

Here is a SP to achieve this and I haven't added validations as the idea is to present how to do this. Hope this is useful.

I am assuming that you have already created a user-defined FileGroup to which you would like to move the indexes. The required parameters for this SP are Database name, scehma name, FileGroup name and minimum of one table/view name for the @ObjectNameList parameter. If you would like to move indexes from more than one object, specify a comma separated string (Ex:- @ObjectNameList = 'Table1,Table2'). Specify the Index name argument only when you want to move a single index from that table/view.

CREATE PROC [dbo].[MoveIndexToFileGroup] (
@DBName sysname,
@SchemaName sysname = 'dbo',
@ObjectNameList Varchar(Max),
@IndexName sysname = null,
@FileGroupName varchar(100)
)
WITH RECOMPILE
AS

BEGIN

SET NOCOUNT ON

DECLARE @IndexSQL NVarchar(Max)
DECLARE @IndexKeySQL NVarchar(Max)
DECLARE @IncludeColSQL NVarchar(Max)
DECLARE @FinalSQL NVarchar(Max)

DECLARE @CurLoopCount Int
DECLARE @MaxLoopCount Int
DECLARE @StartPos Int
DECLARE @EndPos Int

DECLARE @ObjectName sysname
DECLARE @IndName sysname
DECLARE @IsUnique Varchar(10)
DECLARE @Type Varchar(25)
DECLARE @IsPadded Varchar(5)
DECLARE @IgnoreDupKey Varchar(5)
DECLARE @AllowRowLocks Varchar(5)
DECLARE @AllowPageLocks Varchar(5)
DECLARE @FillFactor Int
DECLARE @ExistingFGName Varchar(Max)
DECLARE @FilterDef NVarchar(Max)

DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @SQL nvarchar(4000)
DECLARE @RetVal Bit

DECLARE @ObjectList Table(Id Int Identity(1,1),ObjectName sysname)

DECLARE @WholeIndexData Table
(
ObjectName sysname
,IndexName sysname
,Is_Unique Bit
,Type_Desc Varchar(25)
,Is_Padded Bit
,Ignore_Dup_Key Bit
,Allow_Row_Locks Bit
,Allow_Page_Locks Bit
,Fill_Factor Int
,Is_Descending_Key Bit
,ColumnName sysname
,Is_Included_Column Bit
,FileGroupName Varchar(Max)
,Has_Filter Bit
,Filter_Definition NVarchar(Max)
)

DECLARE @DistinctIndexData Table
(
Id Int IDENTITY(1,1)
,ObjectName sysname
,IndexName sysname
,Is_Unique Bit
,Type_Desc Varchar(25)
,Is_Padded Bit
,Ignore_Dup_Key Bit
,Allow_Row_Locks Bit
,Allow_Page_Locks Bit
,Fill_Factor Int
,FileGroupName Varchar(Max)
,Has_Filter Bit
,Filter_Definition NVarchar(Max)
)

-------------Validate arguments----------------------

IF(@DBName IS NULL)
BEGIN
SELECT @ErrorMessage = 'Database Name must be supplied.'
GOTO ABEND
END

IF(@ObjectNameList IS NULL)
BEGIN
SELECT @ErrorMessage = 'Table or View Name(s) must be supplied.'
GOTO ABEND
END

IF(@FileGroupName IS NULL)
BEGIN
SELECT @ErrorMessage = 'FileGroup Name must be supplied.'
GOTO ABEND
END

--Check for the existence of the Database
IF NOT EXISTS(SELECT Name FROM sys.databases where Name = @DBName)
BEGIN
SET @ErrorMessage = 'The specified Database does not exist'
GOTO ABEND
END

--Check for the existence of the Schema
IF(upper(@SchemaName) <> 'DBO')
BEGIN
SET @SQL = 'SELECT @RetVal = COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.schemas WHERE name = ''' + @SchemaName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH
IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No Schema with the name ' + @SchemaName + ' exists in the Database ' + @DBName
GOTO ABEND
END
END

--Check for the existence of the FileGroup
SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.filegroups WHERE name = ''' + @FileGroupName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH

IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No FileGroup with the name ' + @FileGroupName + ' exists in the Database ' + @DBName
GOTO ABEND
END

----------Get the objects from the concatenated list----------------------------------------------------

SET @StartPos = 0
SET @EndPos = 0

WHILE(@EndPos >= 0)
BEGIN

SELECT @EndPos = CHARINDEX(',',@ObjectNameList,@StartPos)
IF(@EndPos = 0) --Means, separator is not found
BEGIN
INSERT INTO @ObjectList
SELECT SUBSTRING(@ObjectNameList,@StartPos,(LEN(@ObjectNameList) - @StartPos)+1)

BREAK
END

INSERT INTO @ObjectList
SELECT SUBSTRING(@ObjectNameList,@StartPos,(@EndPos - @StartPos))

SET @StartPos = @EndPos + 1

END

-------------Check for the validity of all the Objects----------------------

SET @StartPos = 1
SELECT @EndPos = COUNT(*) FROM @ObjectList

WHILE(@StartPos <= @EndPos)
BEGIN

SELECT @ObjectName = ObjectName FROM @ObjectList WHERE Id = @StartPos

--Check for existence of the object
SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Objects WHERE type IN (''U'',''V'') AND name = ''' + @ObjectName + ''''
BEGIN TRY
EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH

IF(@RetVal = 0)
BEGIN
SELECT @ErrorMessage = 'No Table or View with the name ' + @ObjectName + ' exists in the Database ' + @DBName
GOTO ABEND
END

--Check for existence of Index
IF(@IndexName IS NOT NULL)
BEGIN
SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Indexes si INNER JOIN ' + QUOTENAME(@DBName) + '.sys.Objects so '
SET @SQL = @SQL + ' ON si.Object_Id = so.Object_Id WHERE so.Schema_id = ' + CAST(Schema_Id(@Schemaname) as varchar(25))
SET @SQL = @SQL + ' AND so.name = ''' + @ObjectName + ''' AND si.name = ''' + @IndexName + ''''

BEGIN TRY
   EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT
END TRY
BEGIN CATCH
   SELECT @ErrorMessage = ERROR_MESSAGE()
   GOTO ABEND
END CATCH

IF(@RetVal = 0)
BEGIN
   SELECT @ErrorMessage = 'No Index with the name ' + @IndexName + ' exists on the Object ' + @ObjectName
   GOTO ABEND
END
END

SET @StartPos = @StartPos + 1
END

-------------Loop till all the Objects are processed----------------------

SET @StartPos = 1
SELECT @EndPos = COUNT(*) FROM @ObjectList

WHILE(@StartPos <= @EndPos)
BEGIN

SELECT @ObjectName = ObjectName FROM @ObjectList WHERE Id = @StartPos

-------------Build the SQL to get the index data based on the inputs provided----------------------

SET @IndexSQL =
'SELECT so.Name as ObjectName, si.Name as IndexName,si.Is_Unique,si.Type_Desc'
+ ',si.Is_Padded,si.Ignore_Dup_Key,si.Allow_Row_Locks,si.Allow_Page_Locks,si.Fill_Factor,sic.Is_Descending_Key'
+ ',sc.Name as ColumnName,sic.Is_Included_Column,sf.Name as FileGroupName,si.Has_Filter,si.Filter_Definition FROM '
+ QUOTENAME(@DBName) + '.sys.Objects so INNER JOIN ' + QUOTENAME(@DBName) + '.sys.Indexes si ON so.Object_Id = si.Object_id INNER JOIN '
+ QUOTENAME(@DBName) + '.sys.FileGroups sf ON sf.Data_Space_Id = si.Data_Space_Id INNER JOIN '
+ QUOTENAME(@DBName) + '.sys.Index_columns sic ON si.Object_Id = sic.Object_Id AND si.Index_id = sic.Index_id INNER JOIN '
+ QUOTENAME(@DBName) + '.sys.Columns sc ON sic.Column_Id = sc.Column_Id and sc.Object_Id = sic.Object_Id '
+ ' WHERE so.Name = ''' + @ObjectName + ''''
+ ' AND so.Schema_id = ' + CAST(Schema_Id(@Schemaname) as varchar(25)) + ' AND si.Type_Desc = ''NONCLUSTERED'' '

IF(@IndexName IS NOT NULL)
BEGIN
SET @IndexSQL = @IndexSQL + ' AND si.Name = ''' + @IndexName + ''''
END

SET @IndexSQL = @IndexSQL + ' ORDER BY ObjectName, IndexName'

--PRINT @IndexSQL

-------------Insert the Index Data in to a variable----------------------

BEGIN TRY
INSERT INTO @WholeIndexData
EXEC sp_executesql @IndexSQL
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
GOTO ABEND
END CATCH

--Check if any indexes are there on the object. Otherwise exit
IF (SELECT COUNT(*) FROM @WholeIndexData) = 0
BEGIN
SELECT 'Object does not have any nonclustered indexes to move'
GOTO FINAL
END

-------------Get the distinct index rows in to a variable----------------------

INSERT INTO @DistinctIndexData
SELECT DISTINCT
ObjectName,IndexName,Is_Unique,Type_Desc,Is_Padded,Ignore_Dup_Key,Allow_Row_Locks,Allow_Page_Locks,Fill_Factor,FileGroupName,Has_Filter,Filter_Definition
FROM @WholeIndexData
WHERE ObjectName = @ObjectName

SELECT @CurLoopCount = Min(Id), @MaxLoopCount = Max(Id) FROM @DistinctIndexData WHERE ObjectName = @ObjectName

--SELECT @CurLoopCount, @MaxLoopCount

-------------Loop till all the indexes are processed----------------------

WHILE(@CurLoopCount <= @MaxLoopCount)
BEGIN

SET @IndexKeySQL = ''
SET @IncludeColSQL = ''

-------------Get the current index row to be processed----------------------
SELECT
   @IndName   = IndexName
   ,@Type   = Type_Desc
   ,@ExistingFGName = FileGroupName
   ,@IsUnique  = CASE WHEN Is_Unique = 1 THEN 'UNIQUE ' ELSE '' END
   ,@IsPadded  = CASE WHEN Is_Padded = 0 THEN 'OFF,' ELSE 'ON,' END
   ,@IgnoreDupKey = CASE WHEN Ignore_Dup_Key = 0 THEN 'OFF,' ELSE 'ON,' END
   ,@AllowRowLocks = CASE WHEN Allow_Row_Locks = 0 THEN 'OFF,' ELSE 'ON,' END
   ,@AllowPageLocks = CASE WHEN Allow_Page_Locks = 0 THEN 'OFF,' ELSE 'ON,' END
   ,@FillFactor  = CASE WHEN Fill_Factor = 0 THEN 100 ELSE Fill_Factor END
   ,@FilterDef  = CASE WHEN Has_Filter = 1 THEN (' WHERE ' + Filter_Definition) ELSE '' END
FROM @DistinctIndexData
WHERE Id = @CurLoopCount

-------------Check if the index is already not part of that FileGroup----------------------

IF(@ExistingFGName = @FileGroupName)
BEGIN
   PRINT 'Index ' + @IndName + ' is NOT moved as it is already part of the FileGroup ' + @FileGroupName + '.'
   SET @CurLoopCount = @CurLoopCount + 1
   CONTINUE
END

------- Construct the Index key string along with the direction--------------------
SELECT
   @IndexKeySQL =
   CASE
   WHEN @IndexKeySQL = '' THEN (@IndexKeySQL + QUOTENAME(ColumnName) + CASE WHEN Is_Descending_Key = 0 THEN ' ASC' ELSE ' DESC' END)
   ELSE (@IndexKeySQL + ',' + QUOTENAME(ColumnName) + CASE WHEN Is_Descending_Key = 0 THEN ' ASC' ELSE ' DESC' END)
   END
FROM @WholeIndexData
WHERE ObjectName = @ObjectName
AND IndexName = @IndName
AND Is_Included_Column = 0

--PRINT @IndexKeySQL

------ Construct the Included Column string --------------------------------------
SELECT
   @IncludeColSQL =
   CASE
   WHEN @IncludeColSQL = '' THEN (@IncludeColSQL + QUOTENAME(ColumnName))
   ELSE (@IncludeColSQL + ',' + QUOTENAME(ColumnName))
   END
FROM @WholeIndexData
WHERE ObjectName = @ObjectName
AND IndexName = @IndName
AND Is_Included_Column = 1

--PRINT @IncludeColSQL

-------------Construct the final Create Index statement----------------------
SELECT
@FinalSQL = 'CREATE ' + @IsUnique + @Type + ' INDEX ' + QUOTENAME(@IndName)
+ ' ON ' + QUOTENAME(@DBName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)
+ '(' + @IndexKeySQL + ') '
+ CASE WHEN LEN(@IncludeColSQL) <> 0 THEN 'INCLUDE(' + @IncludeColSQL + ') ' ELSE '' END
+ @FilterDef
+ ' WITH ('
+ 'PAD_INDEX = ' + @IsPadded
+ 'IGNORE_DUP_KEY = ' + @IgnoreDupKey
+ 'ALLOW_ROW_LOCKS = ' + @AllowRowLocks
+ 'ALLOW_PAGE_LOCKS = ' + @AllowPageLocks
+ 'SORT_IN_TEMPDB = OFF,'
+ 'DROP_EXISTING = ON,'
+ 'ONLINE = OFF,'
+ 'FILLFACTOR = ' + CAST(@FillFactor AS Varchar(3))
+ ') ON ' + QUOTENAME(@FileGroupName)

--PRINT @FinalSQL

-------------Execute the Create Index statement to move to the specified filegroup----------------------
BEGIN TRY
   EXEC sp_executesql @FinalSQL
END TRY
BEGIN CATCH
   SELECT @ErrorMessage = ERROR_MESSAGE()
   GOTO ABEND
END CATCH
PRINT 'Index ' + @IndName + ' on Object ' + @ObjectName + ' is moved successfully.'

SET @CurLoopCount = @CurLoopCount + 1

END

SET @StartPos = @StartPos + 1
END
SELECT 'The procedure completed successfully.'
RETURN

ABEND:
RAISERROR 500001 @ErrorMessage
FINAL:
RETURN
END

Creating new tables and loading data in SQL server by reading an Xml file

2008-09-18T12:25:00Z

This kind of requirement may come if VSTS is used for test automation and the test results have to be loaded to a Database. Since the output of test automation is an xml file, let us look at how we can create tables and load them with data by reading an xml file.

The logic at a high level is as follows.

1. Load the Xml file into a DataSet

2. Use SMO objects to access the target SQL Server and DB

3. Read the DataTables one by one from DataSet and create corresponding tables in target Server

4. Use SMO's BulkCopy to load the data from DataTable to the newly created table in target server

Now, create a windows form with three textboxes for capturing target DB server name, DB name and Xml file name. Add a button to proceed with the execution.

Use the following SMO name spaces in the code. Make sure the assemblies "Microsoft.SqlServer.Smo" and "Microsoft.SqlServer.ConnectionInfo" are added to project references in Visual Studio 2005.

using Microsoft.SqlServer.Management;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Server;

using Microsoft.SqlServer;

Add the following code to the button click event.

private void button1_Click(object sender, EventArgs e)

{

DataSet ds = new DataSet();

//ds.ReadXmlSchema("E:\\Temp\\SampleWT1.xsd");

ds.ReadXml(txtXmlLoc.Text);

//ds.ReadXml("E:\\Temp\\Sample.xml");

Server objServer = new Server(txtServer.Text);

Database objDB = objServer.Databases[txtDatabase.Text];

string strCon = "Data Source=" + txtServer.Text + ";Initial Catalog=Rnd;Integrated Security=SSPI";

SqlConnection objCon = new SqlConnection(strCon);

objCon.Open();

int intTotalTables = ds.Tables.Count;

MessageBox.Show("Total Tables = " + intTotalTables.ToString());

Table T;

for (int intTables = 0; intTables < intTotalTables; intTables++)

{

T = new Table(objDB, "sqlCC_" + ds.Tables[intTables].TableName);

Column C = new Column();

//MessageBox.Show(ds.Tables[intTables].Rows.Count.ToString());

foreach (DataColumn dc in ds.Tables[intTables].Columns)

{

C = new Column(T, dc.ColumnName);

C.DataType = MapDataType(dc.DataType.ToString(), dc.MaxLength);

T.Columns.Add(C);

}

T.Create();

SqlBulkCopy objBulkcopy = new SqlBulkCopy(objCon);

objBulkcopy.DestinationTableName = "sqlCC_" + ds.Tables[intTables].TableName;

objBulkcopy.WriteToServer(ds.Tables[intTables]);

objBulkcopy.Close();

}

objCon.Close();

MessageBox.Show("All tables are successfully loaded");

}

Add a routine for mapping the data types.

public DataType MapDataType(string dataType, int dataTypeLength)

{

DataType DTTemp = null;

switch (dataType)

{

case ("System.Decimal"):

DTTemp = new DataType(SqlDataType.Decimal, 10, 2);

break;

case ("System.String"):

DTTemp = new DataType(SqlDataType.VarChar, dataTypeLength);

break;

case ("System.Int32"):

DTTemp = new DataType(SqlDataType.Int);

break;

case ("System.Byte"):

DTTemp = new DataType(SqlDataType.Bit);

break;

case ("System.DateTime"):

DTTemp = new DataType(SqlDataType.DateTime);

break;

case ("System.Boolean"):

DTTemp = new DataType(SqlDataType.Int);

break;

case ("System.SByte"):

DTTemp = new DataType(SqlDataType.Bit);

break;

case ("System.UInt32"):

DTTemp = new DataType(SqlDataType.Int);

break;

}

return DTTemp;

}

I am just using a prefix "sqlCC" while creating the target table. This is just for easy identification of the newly created tables in my target DB.

How to get missing values from an Identity column?

2008-09-17T12:56:00Z

Let us assume that there is an Employee table with EmployeeId as the Identity column in which some values are missing. The following batch will show all the missing values in that Identity column. The logic I preferred is to create another table with the same increment as that of the parent table but without any gaps. Finally, subtracting the parent table rows from the newly created table gives us the missed values. Rest of the script is pretty straight forward and self explanatory.

Declare @MinEmpId int

Declare @MaxEmpId int

Declare @sql nvarchar(250)

select @MinEmpId = Min(EmployeeID),@MaxEmpId = Max(EmployeeID) from Employee

SET @sql = 'Create Table Temp1(Id int IDENTITY(' + cast(@MinEmpId as varchar(25))+ ',1),DummyCol int)'

EXEC sp_executesql @sql

while(@MinEmpId <= @MaxEmpId)

begin

Insert into Temp1(DummyCol) Values(1)

set @MinEmpId = @MinEmpId + 1

end

Select Id from Temp1

Except

Select EmployeeId from Employee

Drop Table Temp1

The above script can be made reusable by making it a stored procedure using Dynamic Sql as shown below. It just accepts table name as a parameter and does the rest of the job.

CREATE PROC GetMissingIds(@TableName Varchar(50))

BEGIN

Declare @MinEmpId int

Declare @MaxEmpId int

Declare @Increment int

Declare @sql nvarchar(250)

SET @sql = 'SELECT @Increment=IDENT_INCR(''' + @TableName + ''')'

EXEC sp_executesql @sql, N'@Increment Int OUTPUT', @Increment OUTPUT

SET @sql = 'select @MinEmpId=Min($Identity),@MaxEmpId=Max($Identity) from ' + @TableName

EXEC sp_executesql @sql, N'@MinEmpId Int OUTPUT,@MaxEmpId Int OUTPUT',@MinEmpId OUTPUT,@MaxEmpId OUTPUT

SET @sql = 'Create Table Temp1(Id int IDENTITY(' + cast(@MinEmpId as varchar(25))+ ',' + cast(@Increment as Varchar(10)) + '),DummyCol int)'

EXEC sp_executesql @sql

while(@MinEmpId <= @MaxEmpId)

begin

Insert into Temp1(DummyCol) Values(1)

set @MinEmpId = @MinEmpId + @Increment

end

SET @sql = 'Select Id from Temp1 EXCEPT Select $Identity From ' + @TableName

EXEC sp_executesql @sql

Drop Table Temp1

END

Hope this helps.