Generate query results and attach to email using SQL Server Database Mail

Question: How can I generate sql query results and attach to an email? I’d prefer to use the built in SQL Server Database Mail application Answer: To generate query results and attach to an email is easy through Database Mail. Use the sp_send_dbmail arguments @query and @attach_query_result_as_a_file. Note: You must use the value 1 , such as @attach_query_result_as_a_file = 1 This example executes a query based on details in the @query argument and attaches the output to an email EXEC msdb.dbo.sp_send_dbmail @recipients=N'[email protected]', @body= 'Some text for body', @subject = 'Some text for subject', @profile_name = 'MyProfile1', @query = ‘select name... Read more →

The specified DSN contains an architecture mismatch between the Driver and application

Question: When using a DSN for a 32 bit application on a 64 bit system, I’m getting the following error message: The specified DSN contains an architectureThe specified DSN contains mismatch between the Driver and application How can I fix? Answer: The error message point to an architecture mismatch – 32 bit versus 64 bit To create a data source name (DSN) that connects to a 32-bit driver on a 64-bit platform, use c:\windows\sysWOW64\odbcad32.exe. To create a DSN that connects to a 64-bit driver, use c:\windows\system32\odbcad32.exe. If you use the 64-bit odbcad32.exe to configure or remove a DSN that connects... Read more →

How to SET and check READ_COMMITTED_SNAPSHOT

Question: How can I set and check the isolation level READ_COMMITTED_SNAPSHOT ? I executed the command and it’s still running after 60 minutes, I’m worried I may have corrupted the database Answer: You probably haven’t corrupted the database, but the command you’re running is blocked by another active connection. SQL Server Books Online states that no other connections to the database are allowed while the command is executing. Although single-user mode is not required. Use sp_who2 to see who else is connected to the database. Read How to detect and troubleshoot blocking and deadlocks --to set read_commited_snapshot ON ALTER DATABASE... Read more →

VLDB and Zeroing out

Awhile ago I wrote a post SQL Server faster restores with instant file initialisation , which goes through the basics of instant file initialization - the characteristics and how to set it up for your SQL Server databases. Although the advice applies as a general rule, it can be tricky to incorporate with auto growth setting on VLDBs . Transaction Logs on VLDBs . Before you implement anything in Production , test out in the Development environments. The last thing you need is a nasty surprise – while Production locks up and users complain. For VLDB Transaction Logs , my... Read more →

Compatibility Level

Question: On a database audit , I noticed an old compatibility level on a database. If a database is it an older compatibility level - what does it mean for using the newer features? Answer: This is a good question because often on forums and conversation with other DBAs and developers – there is some disagreement on the purpose of database compatibility levels My general approach is to view database compatibility level as way of allowing SQL Server to behave similar to the older version. It doesn’t restrict you from exploiting new functions and features which are added to new... Read more →