1

I have the task of getting the free space in the database each month, and emailing the results using Database mail.

Database Mail is setup and working. I have a query (copied from Greg Robidoux) that gets the data.

Select DB_Name() as DB,
name as DB_File,
size/128.0 as Size_MB,
size/128.0 - cast(FileProperty(Name, 'SpaceUsed') as Int)/128.0 as Free_MB
From sys.database_files;

When I try to add the query in "Job Step Properties" I get a parse error.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Incorrect syntax near 'SpaceUsed'. (Microsoft SQL Server, Error: 102)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-102-database-engine-error

The command I added is below

@query = N'Select DB_Name() as DB,
name as DB_File,
size/128.0 as Size_MB,
size/128.0 - cast(FileProperty(Name, 'SpaceUsed') as Int)/128.0 as Free_MB
From sys.database_files;'

Clearly doesn't like 'SpaceUsed'. How can I workaround this problem?

Improve this question
1
  • @BrentOzar doesn't like "database" and "email" in the same sentence :) Commented Oct 15 at 18:20

1 Answer 1

Reset to default
1

You need to escape single quotes inside SQL Server strings. Just add double '' around 'Space Used':

@query = N'Select DB_Name() as DB, name as DB_File, size/128.0 as Size_MB, 
    size/128.0 - cast(FileProperty(Name, ''SpaceUsed'') as Int)/128.0 as Free_MB From sys.database_files;'

See if that helps.

Explanation

The string assigned to @query is enclosed in single quotes. The first quote inside the string terminates the outside quote. That is why you need to escape the inner quotes.

Improve this answer
5
  • The why would be useful too? Commented Oct 14 at 11:14
  • Yes, that worked. The command was successfully parsed. Commented Oct 14 at 13:07
  • @GeneralAccident great! Please, accept my EDIT to your question text and mark/check this answer as an Accepted Answer pls. This is how this site works. Commented Oct 14 at 13:10
  • Now, I can +1 it Commented Oct 24 at 15:33
  • @RohitGupta appreciate you coming back to this and voting up :). I know such comments generally should be deleted. I will del it soon. Commented Oct 24 at 15:51

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.