Scripting SQL Server Error Log Location in TSQL Queries

While it's possible to view the SQL Server error log location in the SSMS GUI, sometimes you want to build a script that'll write a log to the same directory as the error log; we don't want to have to modify our script for every server it runs on.

Enter the ServerProperty function.  There is at least one undocumented server property 'errorLogFilename' that will give us the full path to the SQL ERRORLOG.  This is nice, as we can simply run this to return that:

SELECT SERVERPROPERTY('ErrorLogFileName');

Executing SQL Server Batch Statements Within One Exec(sql) Call

When you need to GO in your batch

Sometimes you'll run into a situation where you want to script a series of SQL batch statements dynamically.  For instance, you might want to create a schema, then create a user to use that schema, then populate that schema with tables, and do that in each database that runs on a SQL instance.


If you weren't wanting to do this dynamically, you'd simply do something like this:

use [dbname];
go
Create schema [schema_name];
go
create user [username] for login [loginname] with default_schema=[schema_name];
go

That's not that big of a deal, really.  But if we want to create a cursor to loop through all databases and run this SQL dynamically, we have a problem:  we can't have the create schema statement in the same batch as the USE command, so we don't end up creating the schema in the correct database.