I've been on something of a hunt for an easy (preferably already hosted) method by which I can display a calendar based on an ical (.ics) subscription. It's been hard.
InstantCal comes up in a lot of searches, but it doesn't work with most sources anymore, since most things have moved to utilizing https for their connections.
There's a cool dhtmlx solution called Open Web Calendar, and I love the flexibility and customizations that are possible with it. And there's also a Heroku container already deployed and ready to use. I used that for some time, but it recent quit working for me. I'm sure the calendar event provider made some change that broke something in what Open Web Calendar is expecting. Especially if you have a coding bent, this is a good solution.
I finally came across--I believe it actually was through a paid post somewhere--30 Boxes, which is a calendar hosting site that offers a lot of stuff that I wasn't really looking for. They also offer exactly what I was looking for, in what they call a calendar mashup: https://30boxes.com/boxed
https://30boxes.com/boxed
Free, easy, and reasonably customizable. It's exactly what I was looking for. I hope this makes it easier for you to find, too.
Thursday, September 12, 2019
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.
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.
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.
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.
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.
The Scheduled Task
Here's what the scheduled task looks like:
For the action, we have a batch file that runs. Here's the contents of that.
rsconfig -c -m RSserver.fqdn.com -s DBServer.fqdn.com -d reportserver_DB -a Windows -u domain\username -p <password>
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 ...
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 { write-output $_ exit 1 }
When we run our script with this addition, we get something altogether different:
The response content cannot be parsed because the Internet Explorer engine is not available, or Internet Explorer's first-launch configuration is not complete. Specify the UseBasicParsing parameter and try again.
Error handling, even in OS scripts, is really important.
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:trap { write-output $_ exit 1 }
When we run our script with this addition, we get something altogether different:
The response content cannot be parsed because the Internet Explorer engine is not available, or Internet Explorer's first-launch configuration is not complete. Specify the UseBasicParsing parameter and try again.
Error handling, even in OS scripts, is really important.
Powershell Invoke-Webrequest error "Internet Explorer engine is not available"
We encountered the following error in an SSIS job that launched a PowerShell script, and the job was executing (we thought) successfully. There was, though, zero output. A couple of things going on here, then.
First, the error:
The response content cannot be parsed because the Internet Explorer engine is not available, or Internet Explorer's first-launch configuration is not complete. Specify the UseBasicParsing parameter and try again.
Now, why wouldn't we have gotten that error in our testing, or in our logs?
That, it turns out is two separate questions.
We didn't get it in our testing, because we were testing as interactive users, and those interactive users had launched IE. So no problem.
Our service account--as a matter of course--has never logged in interactively on the server. So that user never launched IE. The error, then, gave us the solution, as well (as errors ought): use the -UseBasicParsing parameter with invoke-webrequest.
When we add the -UseBasicParsing parameter (invoke-webrequest $URL -UseBasicParsing), all worked well.
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.
First, the error:
The response content cannot be parsed because the Internet Explorer engine is not available, or Internet Explorer's first-launch configuration is not complete. Specify the UseBasicParsing parameter and try again.
Now, why wouldn't we have gotten that error in our testing, or in our logs?
That, it turns out is two separate questions.
We didn't get it in our testing, because we were testing as interactive users, and those interactive users had launched IE. So no problem.
Our service account--as a matter of course--has never logged in interactively on the server. So that user never launched IE. The error, then, gave us the solution, as well (as errors ought): use the -UseBasicParsing parameter with invoke-webrequest.
When we add the -UseBasicParsing parameter (invoke-webrequest $URL -UseBasicParsing), all worked well.
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:
We discovered this in troubleshooting: when trying to use the
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.
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.
The easiest solution to this problem is to specify the target database in sp_send_dbmail, like this:
I hope this helps; we spent far too long on tracking down #1 above.
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.
Thursday, January 29, 2015
Paste SQL (and other) text into OneNote 2013 with syntax highlighting (really)
I've been searching for a way to get SQL scripts into OneNote while retaining syntax highlighting from SSMS for a long time now. It's seems unnecessarily hard: OneNote doesn't support RTF-formatted text from the clipboard, which shouldn't be a problem, since it *does* support HTML-formatted text. Unfortunately, its HTML rendering seems to lack support for the <whitespace> tag.
So pasted text doesn't generally work right.
Lots of folks use MS Word as a go-between, which makes the text look right, but Word uses the NBSP (non-breaking space) character for many of the spaces, which SSMS doesn't like at all.
Here's the best option available, I think, for OneNote 2013: http://notehighlight2013.codeplex.com/. I've used it, and it's just like the NoteHighlight 2010 plug in, with the advantage that the installer is in English. I love this, and I'm grateful to smsmith0 for porting this.
It also appears that Outlook handles things differently, so it mostly works using an Outlook email body as an intermediary for your pasted text from SSMS. NoteHighlight is better.
So pasted text doesn't generally work right.
Lots of folks use MS Word as a go-between, which makes the text look right, but Word uses the NBSP (non-breaking space) character for many of the spaces, which SSMS doesn't like at all.
Here's the best option available, I think, for OneNote 2013: http://notehighlight2013.codeplex.com/. I've used it, and it's just like the NoteHighlight 2010 plug in, with the advantage that the installer is in English. I love this, and I'm grateful to smsmith0 for porting this.
It also appears that Outlook handles things differently, so it mostly works using an Outlook email body as an intermediary for your pasted text from SSMS. NoteHighlight is better.
Wednesday, December 10, 2014
SQL Agent Connection Failure after Cluster Reconfiguration
We had some unfortunate event befall one of our SQL clusters lately, and it’s led to some interesting fallout.
First, the TempDB LUN was unpresented accidentally during some SAN maintenance. As I’m not a SAN admin, I’m not yet sure what all happened with that, but of course SQL Server crashed immediately. It also appears that the other DB LUNs were briefly unavailable to the server, which is where things get interesting.
We had a new TempDB LUN presented in reasonably short order, and SQL Server was running again without any apparent problems.
Then patch week came. When the active node rebooted after patching, SQL Server would not start on the secondary node. Which meant SQL Server was offline pending manual intervention.
It took awhile to figure out why SQL Server wasn’t starting: all of the role resources were online, and everything looked fine. These errors in the event log gave us some clues:
[sqsrvres] CheckAndChangeVirtualServerName: Could not obtain the Virtual Server Name (status 138f)
[sqsrvres] GetVirtualServerName, Unable to obtain next Resource from Cluster Resource Enumerator. Error: 0.
Those are both event ID 19019. So I wondered: what might have changed about the cluster resources?
The answer? Dependencies. When the LUNs were unpresented, all of the dependencies for those also disappeared. Add those dependencies back, such that the SQL Server service resource depends on the appropriate resources (drives, for instance):
You’ll also want to make sure the appropriate network names are set as dependencies.
Wednesday, November 12, 2014
Who is using this database? Is it in use at all?
This is a question we get as SQL Server DBAs all the time: is this database in use? Most of us are in the habit of putting this question back on the application owners, or of using a simple scream test to see if it’s important to anyone.
Until SQL Server 2008, there weren’t a lot of great options: triggers would work, but they have a lot of downsides, and they’re reasonably resource-intensive for busy servers.
Extended events offer a much better solution, and we’ll use this question as a way to ease into their use; this will be a beginner’s introduction to SQL Server extended events (on both SQL 2008 R2 and SQL 2012). We’ll end with a pretty cool (and portable) solution to the question, “is this database in use?”
Until SQL Server 2008, there weren’t a lot of great options: triggers would work, but they have a lot of downsides, and they’re reasonably resource-intensive for busy servers.
Extended events offer a much better solution, and we’ll use this question as a way to ease into their use; this will be a beginner’s introduction to SQL Server extended events (on both SQL 2008 R2 and SQL 2012). We’ll end with a pretty cool (and portable) solution to the question, “is this database in use?”
Thursday, February 20, 2014
SQL Server Reporting Services Service Start Timeout
SSRS will often timeout when starting under Windows Server:
The error is: Windows could not start the SQL Server Reporting Services (MSSQLSERVER) service on local computer. Error 1053: The service did not respond to the start or control request in a timely fashion.
This problem is detailed here: http://support.microsoft.com/kb/2745448, and it's a result of the SSRS service checking for certificate revocation lists (CRL). If you're like most people, you've got a firewall blocking Internet access from your server, so each call for a CRL has to time out before SSRS will start.
The above KB article mentions a handful of ways of dealing with this problem; the most-frequently used method is to increase the service start timeout in the registry. This works, but it requires a server reboot. And it doesn't really get to the root of the problem, that SSRS is taking a long time to start.
Now, generally a CRL is a good thing, even if only in theory. But if your server can't get to the Internet, anyway, there's precious little danger of it getting to a revoked certificate. After all, you're not using the server to browse the web. Right?
So: disable CRL checking within SSRS. This is done with the generatePublisherEvidence element in the ReportingServicesService.exe.config file.
By default, this file is in the <drive>:\Program Files\Microsoft SQL Server\MSRSxxx.MSSQLSERVER\Reporting Services\ReportServer\bin directory.
To disable CRL checking, add the following just before the </runtime> tag at the bottom of the ReportingServicesService.exe.config file:
This problem is detailed here: http://support.microsoft.com/kb/2745448, and it's a result of the SSRS service checking for certificate revocation lists (CRL). If you're like most people, you've got a firewall blocking Internet access from your server, so each call for a CRL has to time out before SSRS will start.
The above KB article mentions a handful of ways of dealing with this problem; the most-frequently used method is to increase the service start timeout in the registry. This works, but it requires a server reboot. And it doesn't really get to the root of the problem, that SSRS is taking a long time to start.
Now, generally a CRL is a good thing, even if only in theory. But if your server can't get to the Internet, anyway, there's precious little danger of it getting to a revoked certificate. After all, you're not using the server to browse the web. Right?
So: disable CRL checking within SSRS. This is done with the generatePublisherEvidence element in the ReportingServicesService.exe.config file.
By default, this file is in the <drive>:\Program Files\Microsoft SQL Server\MSRSxxx.MSSQLSERVER\Reporting Services\ReportServer\bin directory.
To disable CRL checking, add the following just before the </runtime> tag at the bottom of the ReportingServicesService.exe.config file:
<generatePublisherEvidence enabled="false"/>If SSRS is slow to start as a result of CRL checking, this will dramatically speed the service startup. One benefit to doing this, too, is that it doesn't require a server reboot or anything to take effect: it'll work at the next SSRS start.
Subscribe to:
Posts (Atom)