Mashed Articles

Seems like unreasonable request.Well, we had a client who wanted by click on icon on his Desktop to launch a DTS Package which runs on another computer.I’d like to share with you the script that friend of mine created on a workstation that did not have SQL Server installed.

MD “\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033″

COPY sqlresld.dll “\Program Files\Microsoft SQL Server\80\Tools\Binn”

COPY dtsffile.dll “\Program Files\Microsoft SQL Server\80\Tools\Binn”
COPY dtsffile.rll “\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033″
regsvr32.exe “\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsffile.dll”

COPY dtspkg.dll “\Program Files\Microsoft SQL Server\80\Tools\Binn”
COPY dtspkg.rll “\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033″
regsvr32.exe “\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll”

COPY dtspump.dll “\Program Files\Microsoft SQL Server\80\Tools\Binn”
COPY dtspump.rll “\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033″
regsvr32.exe “\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspump.dll”

COPY axscphst.dll “\Program Files\Microsoft SQL Server\80\Tools\Binn”
COPY axscphst.rll “\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033″
regsvr32.exe “\Program Files\Microsoft SQL Server\80\Tools\Binn\axscphst.dll”

COPY dtsrun.exe “\Program Files\Microsoft SQL Server\80\Tools\Binn”

COPY dtsrun.rll “\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033″

COPY custtask.dll “\Program Files\Microsoft SQL Server\80\Tools\Binn”
COPY custtask.rll “\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033″
regsvr32.exe “\Program Files\Microsoft SQL Server\80\Tools\Binn\custtask.dll”

COPY sqlunirl.dll %SYSTEMROOT%\system32

PAUSE
————–
“\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe” /FProcessAppSalesCube.dts

PAUSE

Hi folks
If you are using SQL Server 2005 (SP) and used to build Maintanace Plans especially with adding subplans so you are probable seen the folowing error message
/*
Drop failed for Job ‘jobname’. (Microsoft.SqlServer.Smo)

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
*/

So if you create a MP,SQL Server will create a job and SSIS which is refernced to the subplan as well as inserts the data into system tables in msdb database. (sysmaintplan_subplans,sysjobs_view,sysjobschedules).

Intresting is that if you execute a job it makes more insert into log table called sysmaintplan_log. All of these tables are linked through FK and PK relationships. The problem is when you try to delete a job it gives a Foreign Key errors until you manually remove those entries by the SQL tables.

Please see the link written by Jonas Kempas http://gudenas.com/2007/04/20/sql-server-2005-delete-maintenance-plan-error/ explains step by step how to delete not associated jobs.

I tried it and it worked just fine.