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
413 reads
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 […]
2002-11-30
1,042 reads
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
329 reads
This script will find and replace specified text within all ActiveXScript tasks with a certain text string in the description. It was designed on SQL 2000, but I think it will run on SQL 7 as it doesn't reference any SQL 2000 specific properties. It opens a package object and then loops through the steps […]
2002-10-31
601 reads
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,590 reads
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
807 reads
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,516 reads
By Steve Jones
As we try to get through these strange times in the world, I am...
By Will Assaf
Geeked to be speaking 2x on Saturday May 14, my second time speaking at...
By Steve Jones
I started to add a daily coping tip to the SQLServerCentral newsletter and to...
Hi, I hope someone can help. I will try to summarise as best as...
I work on sql server I need to rewrite statement after where statement so...
Hi Everyone. I'm hoping someone can take a look at my code here to...
Ask SSC