Set logging properties for group of DTS packages

This script will set the Logging properties for a group of DTS packages. To run this script stand-alone, you'll need to set 6 variables. sSvr, sDb, sUid, sPwd, iSec and sPkgMatch. If you use this script in a DTS package, you can assign the values from Global Variables. The sPkgMatch variable is used as a […]

2003-12-01

411 reads

Delete old versions of DTS packages

This script will remove old versions of DTS packages. It accepts a single parameter which is used as a filter criteria on the package name. It will also remove package log records for the version that is deleted.EXAMPLE: EXEC usp_DelOldDTSPkgVersions @vcrPkgName = 'Devel'This will delete old versions for packages that have 'Devel' in the nameQuestions […]

5 (2)

2002-11-30

1,018 reads

Execute DTS package using OA procedures

This script will execute a DTS package using the sp_OA stored procedures. It accepts six parameters which specify the server name, security mode flag, userid, password, package name and optional package password.Eg:For SQL SecurityEXEC usp_ExecDTSPackage @vcrSrvr='Server', @bitIntSec=0, @vcrUID='UserID', @vcrPWD='Password', @vcrPkgName='Package Name', @vcrPkgPWD='Package Password'For Windows SecurityEXEC usp_ExecDTSPackage @vcrSrvr='Server', @bitIntSec=1, @vcrPkgName='Package Name', @vcrPkgPWD='Package Password'It uses a function […]

2002-10-31

324 reads

Report DTS Error Code and Description

This stored procedure extract the error code and description form the DTS package log tables in the msdb database.You just pass it the package name, step name and the time that the step started ( because you could end up with many entries in the log ) and it will return the Error Code and […]

2002-10-25

2,532 reads

Return formatted datetime as a string

Pass a datetime value and receive back a formatted string. Much like the Format$ function in Visual Basic.I put this together to provide a date string to add to filename. Also handy when you are creating text files that require datetime data to be output in weird character formats like yyddmm or yyyymm.eg: fnDate2Char(GetDate(), 'dmy', […]

2002-02-27

799 reads

Re-create Transaction log file

This stored procedure detaches and re-attaches a database without specifying a filename for the transaction log. This causes SQL Server to create log file with default size of 512kb.After playing around with SHRINKDATABASE, SHRINKFILE, forcing the virtual log to wrap around, etc... I found this was the quickest and most reliable method of reclaiming the […]

2002-02-25

2,474 reads

Blogs

How I passed the 70-761 certification exam

By

FUN FACT: I have been a Microsoft Certified Professional for SQL Server for over...

Azure Storage tips

By

As a followup to my blog post Azure Data Lake Store Gen2 is GA,...

Managing Agent Jobs on Availability Group Servers

By

As always, the scripts in this post can also be found on our Git...

Read the latest Blogs

Forums

What Write Ahead Logging Is and Why It Matters

By Kendra Little

Comments posted to this topic are about the item What Write Ahead Logging Is...

SSAS Error: Duplicate attribute key found when processing

By aveek22

Comments posted to this topic are about the item SSAS Error: Duplicate attribute key...

Using Calendars and Dates in Power BI

By Additional Articles

Comments posted to this topic are about the item Using Calendars and Dates in...

Visit the forum

Ask SSC

SQL Server Q&A from the SQLServerCentral community

Get answers