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

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!