How to run DTS from Stored Procedure

You can run DTS package from the client PC by these ways.

• Run DTSRUN.exe (It is provided as DTS utility.)
• Call the method in DTS object
• Call stored procedure (Stored procedure is necessary to be prepared in server side.)

The first 2 ways are necessary to install component in client. So I'll introduce the last way this time. There are two ways to run DTS from stored procedure.

Run DTS by xp_CmdShell

1. Create the command file (.bat or .vbs and so on) to execute DTSRUN
2. Create the stored procedure to call xp_CmdShell to execute the command file which is created in step1
3. Call the stored procedure

Run DTS by SQLServer Agent

1. Create SQLServer Agent job to run DTSRUN
2. Create the stored procedure to call sp_start_job
3. Call the stored procedure

CREATE PROCEDURE [ExecuteDTS_TestPackage] AS
EXECUTE msdb..sp_start_job @job_name = 'TestPackage'

GO

SQLServer : How to count the length of byte

SQLServer have no funtion to count the length of byte like LENB. This SQL provide same function. *TEST:VARCHAR(25)

DATALENGTH(CONVERT(VARCHAR(25), TEST))

SQLServer : Lock Status

You can see the lock status by sp_lock.

EXEC sp_lock
GO

Type on the result of sp_lock

Type	Description
DB	Database
FIL	File
IDX	Index
PG	Page
KEY	Key
TAB	Table
EXT	Extent
RID	Row ID