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

Database Mail Formatting, Invalid Parameter Error!

Today I wanted to cover a database mail configuration issue I came across while attempting to send an email with an attachment through a SQL Server Agent job.

I have a t-sql script that calls msdb.dbo.sp_send_dbmail stored procedure in a job step that send the results of query as an attachment. When the job runs it fails with the following error message:

Executed as user: Domain\SQLAccount. Error formatting query,
probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

When I copied the t-sql script from the job step and ran it in a query window the query ran and the email sent with the attachment without issue. So I went to BOL, SQL Server Newsgroups and Google and through a combination of posts from different sources I found the solution to my problem. The issue was the domain account that the SQL Server Agent service is running under did not belong as a user in the msdb database. For some quirky reason, for sp_send_dbmail the login that executes this must be in the msdb database. If you come across this problem give this solution a try and let us know!...

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!