## Wednesday, July 13, 2016

### Using SSRS ReportServer database in SQL Multi-Subnet Availability Group

Using SSRS report data sources on a SQL Availability Group that is behind a multi-subnet listener is supported, and it mostly works.

Having the SSRS ReportServer database on a multi-subnet AG, however, isn't supported, as detailed here.  To wit:
The use of MultiSubnetFailover, with the report server databases, is not supported.

So.  What's one to do if you need a ReportServer DB to follow an application DB that needs to be protected with a Multi-Subnet AG?

Script it.

Happily, when SQL Server transitions to the primary replica in an Availability Group, it writes this to the event log with a specific event ID:  41074.  We can create a scheduled task to trigger from that event and remotely run the RSCONFIG utility to point SSRS to the new primary database server.

Then, to make sure our AG-hosted report data sources get picked up correctly, we'll restart the SSRS service, too.

Here's what the scheduled task looks like:

For the action, we have a batch file that runs.  Here's the contents of that.

After the batch file runs, we use sc.exe to stop the SSRS service, and then start it (restart isn't an option with SC).

This ensures that RS is always pointing to the primary replica instance of Availability Group, but it doesn't rely on the AG listener, so the system continues to function.

## Monday, June 27, 2016

### Using powershell to call arguments with no spaces

Sometimes you have an application (like 7 Zip) that uses command line switches without a space between the switch and the value.  If you're using a variable for the value, sometimes PowerShell won't parse the values out right, so the thing will fail.

In this case, double quotes are your friend.  So instead of something like this:

path\to\7z.exe -w$tempDir -tzip -mx1 a .... you'd do this: path\to\7z.exe "-w$tempDir" -tzip -mx1 a ...

PowerShell will parse between the double quotes to replace the $tempDir variable appropriately. You can, FWIW, also escape the hyphen with a back tick (  ) to do the same thing. I happen to think the quotes option is more readable, but either works well. path\to\7z.exe -w$tempDir -tzip -mx1 a ...

## Tuesday, May 31, 2016

### PowerShell exit codes, or "Why does SQL Server think my PowerShell script succeeded?"

If you search for PowerShell logging, or PowerShell output, you'll find lots of folks who are frustrated by PowerShell's error handling and logging capabilities.
It turns out, they're pretty robust, but because PowerShell has separate error and standard output paths, we don't always see the errors in the way we'd expect.  This is especially true with automation.

Here's a quick example, just to demonstrate one problem that caught us once.

Our SQL Server instances run under accounts that have never logged in to the server interactively.  We had a PowerShell script that was processing some web service APIs, and it wasn't returning any information.  Here's a simplified script:
C:\windows\system32\WindowsPowerShell\v1.0\powershell.exe -command "invoke-webrequest https://www.microsoft.com"

If we ran this manually (or, say, in a debug SSIS window), all works well.  No errors.

If we ran this as a SQL Agent job, also no errors.  But no output.

## Trap the Errors

There's a simple addition we can make to trap our errors and control the exit code:  trap.  Just put this at the beginning of your script:

## Why didn't this cause my job to fail?

Ah, now there's a problem worth addressing.  This *was* in fact an error, and we normally would want such errors to result in, well, an error.
PowerShell, however, has different classes of errors, and this one isn't a terminating error.  So PowerShell sees it as an error, but PowerShell still exits cleanly, and so it's runstatus at the end is zero (success).

See this post for some details on fixing that particular problem.

## Thursday, May 5, 2016

### Fighting sp_send_dbmail error: "Failed to initialize sqlcmd library with error number -2147467259."

We recently encountered a bunch of problems with sp_send_dbmail, and they all returned this:
Failed to initialize sqlcmd library with error number -2147467259.
Super irritating, that error, because it seems to suggest that you're doing something you're not. In fact, this error only indicates that there's some generic problem sending mail with sp_send_dbmail. Here are the problems we uncovered, and the troubleshooting steps for them.

## 1.  The SQL Server service account didn't have sufficient privileges on the domain.

As a rule, we don't allow our domain accounts to query the properties of other accounts.  This is true for service accounts, as well.  SQL, to do a fair number of things (not least to ensure the SQL Agent job owner is a valid domain member) requires the ability to query domain account properties.

We discovered this in troubleshooting:  when trying to use the
execute as user=
statement to make sure we didn't have a problem with database permissions, we received this error:
Could not obtain information about Windows NT group/user...

Aha: domain permissions problem.  This was causing the "Failed to initialize sqlcmd library" error.
Granting the service account additional privileges on the domain fixed this problem.

## 2.  The executing user didn't have sufficient privileges in the query database.

Having fixed the "execute as user" problem, we can now impersonate a DB user to execute the query in SSMS.  If you have sufficient permissions on the instance.  We're assuming you have sysadmin access, here.

When we run the sp_send_dbmail, and the executing user doesn't have permissions on the target database, we will see the "Failed to initialize sqlcmd library with error number -2147467259." error.

We can uncover this is the problem by just executing the query portion of the sp_send_dbmail stored proc, and using the "execute as user=" statement beforehand.  Specify the SQL Agent service account user in this statement, and if the user doesn't have permissions, you'll get an error message that's actually useful.

## 3.  The query is executing in the wrong database.

You actually see this pretty often, and the solution to this is most frequently listed possibility for fixing the "Failed to initialize sqlcmd library with error number -2147467259" error.

The easiest solution to this problem is to specify the target database in sp_send_dbmail, like this:

EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailList
,@execute_query_database = 'target_db_name'
,@subject = 'Subject'
,@body='Here's the body of the email'
,@query = @querytext
,@profile_name='email_profile'

I hope this helps; we spent far too long on tracking down #1 above.