Use DTExec To Replace DTSRun In SSIS!....

Hello there!...

Today I was tasked with migrating a SQL Server Agent job from SQL Server 2000 to SQL Server 2008. 

This job calls a VB script file and within this file executes the dtsrun.exe command and calls a DTS package.  So as I migrated the job to the new server I upgraded the DTS package to SSIS and copied over the VB script file.  So within the VB script file I had to modify the command to go from using DTSRun to DTExec with the new parameters.  So here is what dtsrun command looked like in SQL Server 2000:
        '*DTS executable and Parameters
        DTSAPPRUN = """C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe"" -S server
               name - E -N DTS package name

Here is what I had to change command to look like for a SSIS package:
        '*SSIS executable and Parameters
        DTSAPPRUN = """C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"" /sq SSIS
               package name /ser server name"

Now fortunately for me, the DTS package was a simple data import package so it was converted to SSIS without issue.  So, after making the changes to the VB script file and saving... I reran the SQL Server Agent job and the VB Script executed and the SSIS package ran without issue.  I hope you find this helpful and if you have another way of doing this please post here and let us know!

Until next time...
Crazed DBA

Access Blocked To xp_cmdshell

Hello and welcome back!...

Today I was attempting to execute a stored procedure in SQL Server 2008 R2 that calls xp_cmdshell to get a directory listing and I received the following error message:

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell'

Now there are 2 ways to enable xp_cmdshell in SQL Server 2008 R2 they are:
1. In a query window execute the following t-sql:
exec sp_configure 'xp_cmdshell', '1'
reconfigure with override
go
2. The other way is through SQL Server Management Studio (SSMS). Here are the
steps to enable xp_cmdshell through SSMS:

Step 1 - In Management Studio connect to a component of SQL Server
Step 2 - In Object Explorer, right-click the server, and then click Facets
Step 3 - In the View Facets dialog box, expand the Facet list, and select
Surface Area Configuration
Step 4 - The last in the list is XPCmdShellEnabled and you want to select this to read 'Yes'
and then click OK. Here is a screenshot of this Facet:

Quick Way To Find File Sizes For Your Database!

Hello and welcome to my blog!...

Here you will find what I like to call "nuggets" that I come across while working with SQL Server and other tools!...

Today I am going to share with you a quick way in T-SQL to determine the file sizes in your database. The script below will work in SQL Server 2000, 2005, 2008 and 2008 R2. You will need to run this in the context of the database you want the file sizes for.

SELECT FILEID,
CONVERT(decimal(12,2),ROUND([size]/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty([name],'SpaceUsed')/128.000,2))
as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND(([size]-fileproperty([name], 'SpaceUsed' )) /
128.000,2)) as [FREESPACEINMB],
[name] as [DATABASENAME],
[filename] as [FILENAME]
FROM dbo.sysfiles

Let us know if you know of another way to get file sizes fast!