tag:blogger.com,1999:blog-17142833847906107042024-03-17T22:03:30.451-05:00Lane's Tech BlogWhat works, why, and how.Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.comBlogger73125tag:blogger.com,1999:blog-1714283384790610704.post-10568286788164428622019-09-12T16:24:00.002-05:002019-09-12T16:26:41.185-05:00.ics subscription HTML embed displayI'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.<br />
<br />
<a href="http://www.instantcal.com/">InstantCal</a> 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.<br />
<br />
There's a cool dhtmlx solution called <a href="https://github.com/niccokunzmann/open-web-calendar">Open Web Calendar</a>, 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.<br />
<br />
I finally came across--I believe it actually was through a paid post somewhere--<a href="https://30boxes.com/">30 Boxes</a>, 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: <a href="https://30boxes.com/boxed">https://30boxes.com/boxed</a><br />
<a href="https://30boxes.com/boxed">https://30boxes.com/boxed</a><br />
<br />
Free, easy, and reasonably customizable. It's exactly what I was looking for. I hope this makes it easier for you to find, too.<br />
<br />Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com3tag:blogger.com,1999:blog-1714283384790610704.post-54831185289140622992016-07-13T10:22:00.000-05:002016-07-13T10:22:46.411-05:00Using SSRS ReportServer database in SQL Multi-Subnet Availability GroupUsing SSRS report data sources on a SQL Availability Group that is behind a multi-subnet listener is supported, and it mostly works. <br />
<br />
Having the SSRS ReportServer database on a multi-subnet AG, however, <i>isn't</i> supported, as detailed <a href="https://msdn.microsoft.com/en-us/library/hh882437.aspx">here</a>. To wit:<br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;">The use of MultiSubnetFailover, with the report server databases, is not supported.</span><br />
<br />
<b></b><i></i><u></u><sub></sub><sup></sup><strike></strike>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?<br />
<br />
Script it.<br />
<br />
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.<br />
<br />
Then, to make sure our AG-hosted report data sources get picked up correctly, we'll restart the SSRS service, too.<br />
<br />
<h2>
The Scheduled Task</h2>
Here's what the scheduled task looks like:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim4G2kinh4EopC3c1_vTgzIymXYObrgLcEUj_9M3lezPm7g12mIW7VjvUBetRE6i9A-_H1hsz6jaEbBz0y_o-8e3FO01OsSDvGx5Di1ayTq5OXS5ZVGdVInVAzm9abFCFWVOVmEjupM7O4/s1600/41074.PNG" imageanchor="1"><img border="0" height="302" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim4G2kinh4EopC3c1_vTgzIymXYObrgLcEUj_9M3lezPm7g12mIW7VjvUBetRE6i9A-_H1hsz6jaEbBz0y_o-8e3FO01OsSDvGx5Di1ayTq5OXS5ZVGdVInVAzm9abFCFWVOVmEjupM7O4/s400/41074.PNG" width="400" /></a><br />
<br />
For the action, we have a batch file that runs. Here's the contents of that.
<div class="codesnippet">rsconfig -c -m RSserver.fqdn.com -s DBServer.fqdn.com -d reportserver_DB -a Windows -u domain\username -p <password></div>
<br />
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).<br />
<br />
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.<br />
<br />
<br />Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com10tag:blogger.com,1999:blog-1714283384790610704.post-13871224091551878782016-06-27T15:48:00.001-05:002016-06-27T15:48:55.260-05:00Using powershell to call arguments with no spacesSometimes 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.<br />
<br />
In this case, double quotes are your friend. So instead of something like this:<br />
<br />
<span class="codesnippet">path\to\7z.exe -w$tempDir -tzip -mx1 a ....</span><br />
<br />
you'd do this:<br />
<br />
<span class="codesnippet">path\to\7z.exe "-w$tempDir" -tzip -mx1 a ...</span><br />
<br />
PowerShell will parse between the double quotes to replace the $tempDir variable appropriately.<br />
<br />
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.<br />
<br />
<span class="codesnippet">path\to\7z.exe `-w$tempDir -tzip -mx1 a ...</span><br />
<br />
<b></b><i></i><u></u><sub></sub><sup></sup><strike></strike><br />Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com0tag:blogger.com,1999:blog-1714283384790610704.post-24191096099106460012016-05-31T21:41:00.001-05:002016-05-31T21:41:30.368-05:00PowerShell 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.<br />
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.<br />
<br />
Here's a quick example, just to demonstrate one problem that <a href="http://tech.lanesnotes.com/2016/05/powershell-invoke-webrequest-error.html">caught us once</a>.<br />
<br />
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:<br />
<span style="font-family: "courier new" , "courier" , monospace;">C:\windows\system32\WindowsPowerShell\v1.0\powershell.exe -command "invoke-webrequest https://www.microsoft.com"</span><br />
<br />
If we ran this manually (or, say, in a debug SSIS window), all works well. No errors.<br />
<br />
If we ran this as a SQL Agent job, also no errors. But no output. <br />
<br />
<h2>
Trap the Errors</h2>
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:<br />
<span style="font-family: "courier new" , "courier" , monospace;">trap { write-output $_ exit 1 }</span><br />
<br />
When we run our script with this addition, we get something altogether different:<br />
<span style="font-family: "courier new" , "courier" , monospace;">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.</span><br />
<br />
Error handling, even in OS scripts, is really important.Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com0tag:blogger.com,1999:blog-1714283384790610704.post-8591762651882919632016-05-31T21:24:00.002-05:002016-06-01T08:39:41.178-05:00Powershell 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.<br />
<br />
First, the error:<br />
<span style="font-family: "courier new" , "courier" , monospace;">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.</span><br />
<br />
Now, why wouldn't we have gotten that error in our testing, or in our logs?<br />
That, it turns out is two separate questions. <br />
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.<br />
<br />
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.<br />
<br />
When we add the -UseBasicParsing parameter (<span style="font-family: "courier new" , "courier" , monospace;">invoke-webrequest $URL </span><span style="background-color: yellow; color: black; font-family: "courier new" , "courier" , monospace;">-UseBasicParsing</span>), all worked well.<br />
<br />
<h2>
Why didn't this cause my job to fail?</h2>
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.<br />
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).<br />
<br />
See this post for some details on <a href="http://tech.lanesnotes.com/2016/05/powershell-exit-codes-or-why-does-sql.html">fixing that particular problem</a>.<br />
<br />
<br />Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com0tag:blogger.com,1999:blog-1714283384790610704.post-91605291773310453622016-05-05T10:34:00.001-05:002016-05-05T10:34:46.125-05:00Fighting 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:
<br />
<blockquote>
Failed to initialize sqlcmd library with error number -2147467259.</blockquote>
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.
<br />
<br />
<h2>
1. The SQL Server service account didn't have sufficient privileges on the domain.</h2>
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.<br />
<br />
We discovered this in troubleshooting: when trying to use the <br />
<pre>execute as user=</pre>
statement to make sure we didn't have a problem with database permissions, we received this error:<br />
<pre>Could not obtain information about Windows NT group/user...</pre>
<br />
Aha: domain permissions problem. This was causing the "Failed to initialize sqlcmd library" error.<br />
Granting the service account additional privileges on the domain fixed this problem.<br />
<br />
<h2>
2. The executing user didn't have sufficient privileges in the query database.</h2>
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.<br />
<br />
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.<br />
<br />
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.<br />
<br />
<h2>
3. The query is executing in the wrong database.</h2>
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. <br />
<br />
The easiest solution to this problem is to specify the target database in sp_send_dbmail, like this:<br />
<br />
<pre>EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailList
,@execute_query_database = <span style="background-color: yellow;">'target_db_name'</span>
,@subject = 'Subject'
,@body='Here's the body of the email'
,@query = @querytext
,@profile_name='email_profile'</pre>
<br />
<br />
I hope this helps; we spent far too long on tracking down #1 above.<br />
<br />Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com3tag:blogger.com,1999:blog-1714283384790610704.post-26749128791969506572015-01-29T08:47:00.000-06:002015-12-12T19:04:12.606-06:00Paste SQL (and other) text into OneNote 2013 with syntax highlighting (really)<a href="http://tech.lanesnotes.com/2012/05/pasting-code-to-onenote-with-formatting.html">I've been searching for a way to get SQL scripts into OneNote while retaining syntax highlighting</a> 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. <br />
<br />
So pasted text doesn't generally work right. <br />
<br />
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.<br />
<br />
Here's the best option available, I think, for OneNote 2013: <span id="goog_1060478501"></span><a href="http://notehighlight2013.codeplex.com/"> http://notehighlight2013.codeplex.com/<span id="goog_1060478502"></span></a>. I've used it, and it's just like the <span id="goog_1060478489"></span><a href="http://tech.lanesnotes.com/2012/05/pasting-code-to-onenote-with-formatting.html">NoteHighlight 2010<span id="goog_1060478490"></span></a> plug in, with the advantage that the installer is in English. I love this, and I'm grateful to <a href="http://www.codeplex.com/site/users/view/smsmith0" id="UpdatedByUserAnchor">smsmith0</a> for porting this.<br />
<br />
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.<br />
<br />
<br />Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com8tag:blogger.com,1999:blog-1714283384790610704.post-16263992759809661732014-12-10T13:36:00.001-06:002014-12-10T13:37:34.831-06:00SQL Agent Connection Failure after Cluster Reconfiguration<p>We had some unfortunate event befall one of our SQL clusters lately, and it’s led to some interesting fallout.</p> <p>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.</p> <p>We had a new TempDB LUN presented in reasonably short order, and SQL Server was running again without any apparent problems. </p> <p>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.</p> <p>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:</p> <div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4"><pre id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><p>[sqsrvres] CheckAndChangeVirtualServerName: Could not obtain the Virtual Server Name (status 138f)<br>[sqsrvres] GetVirtualServerName, Unable to obtain next Resource from Cluster Resource Enumerator. Error: 0.</p></pre><br></div>
<p>Those are both event ID 19019. So I wondered: what might have changed about the cluster resources?</p>
<p>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):</p>
<p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdyw_h7vm8p6nKW_6rtOg7qaC_wAng5q5jkuHAtN3vEyQL3LhGIwC2nunqtoO88lxuyYvP3D_DXqKZaLZZQt-k_EbqoFMcalj9PtEuMvJzq0S19ek0mICUTGP0apjVZIvOHlMEZTTwyu1t/s1600-h/image%25255B4%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj61aVD1woCJA6vdQfGmGnZIdy0sztr7iQPKBYHCJdlbpYw5854WXrcpViYE41dePy0bIT-H970RFZ1inBLqcdUMMoI2XtvT7QBvC11XINvuvLkpAUCIFX7cAu7zkfNATh5KxGt5pfq3YUE/?imgmax=800" width="420" height="502"></a></p>
<p>You’ll also want to make sure the appropriate network names are set as dependencies.</p> Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com0tag:blogger.com,1999:blog-1714283384790610704.post-729461061746942872014-11-12T19:28:00.000-06:002014-11-12T21:09:44.061-06:00Who 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. <br />
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.<br />
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?”<br />
<a name='more'></a><h1>
</h1>
<h1>
TL;DR</h1>
If you want to skip to the scripts and whatnot, <a href="http://tech.lanesnotes.com/2014/11/who-is-using-this-database-is-it-in-use.html#deployme">here you go</a>. And check out <a href="https://www.sqlskills.com/blogs/jonathan/tracking-sql-server-database-usage/" target="_blank">Jonathan’s blog</a> for tons of information.<br />
<br />
<h1>
Thanks</h1>
First, credit where credit is due: I attended Jonathan Kehayias’ PASS Summit session on extended events, and it was quite helpful. He’s really one of <em>the</em> go-to guys for this stuff. We’ll be using a fair bit from his post on <a href="https://www.sqlskills.com/blogs/jonathan/tracking-sql-server-database-usage/" target="_blank">tracking SQL Server database usage</a>. The central pieces of the scripts we’re dealing with come from his post. We’re adding a bit on to it, and changing it up a bit for ease of deployment. You’ll definitely want to check out his blog posts on xevents for some great ideas and some real insight into how that system works.<br />
<br />
<h1>
Extended Events</h1>
Extended Events (or xevents) are the eventual replacement for SQL profiler, so you’ll want to start working on getting a grasp on them and how they work. In general, think of them as a very lightweight (much more so than profiler) set of processes for capturing information based on criteria.<br />
<br />
<h2>
Tools</h2>
If you’re working with SQL 2008 or SQL 2008 R2, you’ll want to use the <a href="http://extendedeventmanager.codeplex.com/" target="_blank">SSMS extended events add-in</a>. If you’re using SSMS 2012 (or later) to manage SQL 2008 systems, you’ll still need an add-in to get a GUI view of xevents on SQL 2008 environments. <a href="https://www.sqlskills.com/free-tools/sql-server-2012-extended-events-add-in/" target="_blank">SQLSkills.com provides one</a>, based on the above SQL 2008 add-in. Both are free. (Thanks, y’all!)<br />
<br />
<h2>
Introduction to extended events</h2>
The learning curve can be a little steep, so here’s a primer:<br />
Extended Events are composed of: <br />
<ul>
<li><strong>Event </strong>– the triggering event you’re looking at
<li><strong>Action</strong> – the data you want to collect
<li><strong>Predicate</strong> – the “where” clause for your session: what needs to be the case before you’re interested in the data
<li><strong>Target</strong> – where you store the data you’ve gathered
<li><strong>Session</strong> – Think of a session as a trace: the session is composed of all of the other pieces above, and can be turned on and off at will. When the session is off, no data is gathered.</li>
</li>
</li>
</li>
</li>
</ul>
When you create an extended event session, you create the session, then add events, actions, a where clause (predicate), and target(s).<br />
<br />
<h1>
Database usage</h1>
In this case, we’re interested in whether a DB is being used by a user process (not a system process). All database use puts a shared lock on the database. As it happens, there’s an event for that: sqlserver.log_acquired.<br />
So what we want to do is create a <strong>Session</strong> that looks at shared database lock <strong>Events</strong>. We want to collect the app name, username, and other details about the <strong>Action</strong>, <strong>Predicated </strong>on the lock being on a particular database and the user not being a system account.<br />
Finally, we write the relevant data to a <strong>Target</strong> location.<br />
<a href="https://www.blogger.com/null" name="script"></a> <br />
<h1>
The Script</h1>
Here we’ll look at it a piece at a time, then we’ll put it all together in a <a href="http://tech.lanesnotes.com/2014/11/who-is-using-this-database-is-it-in-use.html#deployme">package that’s easy to deploy</a>.<br />
<br />
<h2>
Session</h2>
First, let’s look at the session and event definition:<br />
<div>
<pre id="codeSnippet" style="background-color: #f4f4f4; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding: 0px; text-align: left; width: 100%;"><span style="color: green;">-- For SQL 2012</span>
<span style="color: green;">-- DatabaseUsage Event Session</span>
CREATE EVENT <span style="color: blue;">SESSION</span> [User_DBUsage] <span style="color: blue;">ON</span> SERVER
<span style="color: blue;">ADD</span> EVENT sqlserver.lock_acquired ( <span style="color: green;">-- EVENT is when a lock is acquired</span>
<span style="color: blue;">SET</span> collect_database_name = (1) <span style="color: green;">-- We want to get the DB name, too</span>
<span style="color: blue;">ACTION</span>(sqlserver.client_app_name <span style="color: green;">-- and the app name</span>
, sqlserver.client_hostname <span style="color: green;">-- and the host name</span>
, sqlserver.database_id
, sqlserver.database_name
, sqlserver.server_principal_name <span style="color: green;">-- the DB login used</span>
) </pre>
</div>
<div>
Some of the above details change a bit for SQL 2008; we’ll provide the full script for it below. Here’s how this runs down:</div>
<div>
</div>
<div>
We create an session, and then add an event to that. Then we list the actions we want to look at. So far, this is stuff that’s not too hard to suss out; if we look at the GUI (in SQL 2012, here), we can see those listed out this way:</div>
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL0bn2RvJGeWrHjKlsAaDQKg7NR8n0SLowR-Bz1jHtx3cinvFK5-r9CM1yQFVqTJVYEGrwN7ZbWfx_HbMZ-Ex5DPUMs0UCm3HGyfsTe0oU1369anDjxNhqGO8uE8T28JV8-wnSeDa8CeZM/s1600-h/image%25255B19%25255D.png"><img alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmasOdY74vPVpdlL9U2KvwwxJ9Gw8NW7HjzRDSJzTA50mK1SJfz4QD_acbvyDxMqkr299uhNgZhYLvM3paOu27zR8GAXIsH-UOtalscN2p0yQP9ozzAim-wbleU6GrPPGlxdGydQ85x3fs/?imgmax=800" height="302" style="border-width: 0px; display: inline;" title="image" width="644" /></a> </div>
<div>
First we pick the event (lock_acquired) and move it to the selected events. Then we configure it:</div>
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMVcW4-TcF9Jdcmm3Tn_0Eyd36Jf78-iulWS7wJ0orf-jpyUiksMuYPhqpeq6mUKOmW8NjzOYM3BOBmG8i374as3z3PR4DYrCA-pQ4sSgkBwXX_xZcHUeg_lU787gqftBkmwXdw3KG0gSY/s1600-h/image%25255B11%25255D.png"><img alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpMwKfYsCBQcffPFEwA4WCw9Pv_wardHCM26ryssiKkG5_DWLSdQF-kzAhyphenhyphenopwpTgldMpoZ9pGrVWkFMFXapmo06zlPsduxY6angynIUAcgZAAS5NIFsY_tjdOw03Fp9pCOp_l3U-7SQm4/?imgmax=800" height="329" style="border-width: 0px; display: inline;" title="image" width="640" /></a></div>
<div>
and pick the actions we want to collect. <br />
This is what the GUI looks like if you were to define (or edit) this session there.</div>
<div>
</div>
<div>
We don’t want to stop there, though, because we haven’t defined any predicates. If we were to go with the definition above, by itself, we’d be gathering information about every connection to every database, all the time. We don’t want that at all.</div>
<div>
</div>
<h2>
Predicates</h2>
<div>
So we add our predicates:</div>
<div id="codeSnippetWrapper" style="background-color: #f4f4f4; border: 1px solid silver; cursor: text; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 20px 0px 10px; max-height: 200px; overflow: auto; padding: 4px; text-align: left; width: 97.5%;">
<pre id="codeSnippet" style="background-color: #f4f4f4; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding: 0px; text-align: left; width: 100%;"><span style="color: green;">-- For SQL 2012</span>
<span style="color: green;">-- DatabaseUsage Event Session</span>
<span style="color: blue;">CREATE</span> EVENT <span style="color: blue;">SESSION</span> [User_DBUsage] <span style="color: blue;">ON</span> SERVER
<span style="color: blue;">ADD</span> EVENT sqlserver.lock_acquired ( <span style="color: green;">-- EVENT is when a lock is acquired </span>
<span style="color: blue;">SET</span> collect_database_name = (1) <span style="color: green;">-- We want to get the DB name, too </span>
<span style="color: blue;">ACTION</span>(sqlserver.client_app_name <span style="color: green;">-- and the app name</span>
, sqlserver.client_hostname <span style="color: green;">-- and the host name</span>
, sqlserver.database_id
, sqlserver.database_name
, sqlserver.server_principal_name <span style="color: green;">-- the DB login used</span>
)
<span style="color: blue;">WHERE</span> (
[package0].[equal_uint64]([owner_type], (4)) <span style="color: green;">-- This is a shared lock</span>
<span style="color: blue;">AND</span> [package0].[equal_uint64]([resource_type], (2)) <span style="color: green;">-- This is a DB lock</span>
<span style="color: green;">-- AND [package0].[greater_than_uint64]([database_id], (4)) -- This would give info on all non-system DBs</span>
<span style="color: blue;">AND</span> [package0].[equal_boolean]([sqlserver].[is_system], (0)) <span style="color: green;">-- exclude system access</span>
<span style="color: blue;">AND</span> database_id = 5 <span style="color: green;">-- DB_ID of the target database</span>
<span style="color: blue;">AND</span> [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[server_principal_name], N<span style="color: #006080;">'DOMAIN\sqlSvcAcct'</span>) <span style="color: green;">-- exclude svc account</span>
)
)</pre>
</div>
Now we get into some stuff that is much less obvious<br />
First, we see that there’s this <strong>equal_uint64</strong> and <strong>equal_boolean</strong> kinds of stuff. Those are the xevent engine predicate comparison methods. These are predicate comparisons for predicate sources with different data types, which we can use in addition to our standard +, –, =, <, and >.<br />
A couple of important details are here. First, we’re looking for a lock on a database with ID # 5. This will change in your environment, of course. <br />
The second is the last line of the script. We want to exclude the operations not just of the SQL engine (DB startups, for instance), but also for maintenance tasks that occur in the context of the SQL Agent. To do this, we also tell it that we don’t want to include any locks taken out by the server principal of the SQL Agent login.<br />
If you’re interested in the details of identifying the type of lock and the object of the lock (owner_type and resource_type, above), see <a href="https://www.sqlskills.com/blogs/jonathan/tracking-sql-server-database-usage/" target="_blank">Jonathan’s discussion</a> of how the system stores that information.<br />
<br />
<h2>
Targets</h2>
Finally, we want to store this information somewhere that we can review it at a later date, so we add targets. In this example we’re adding two targets for demonstration purposes; you’ll likely only want to use one of these at a time, depending on your needs.<br />
<div id="codeSnippetWrapper" style="background-color: #f4f4f4; border: 1px solid silver; cursor: text; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 20px 0px 10px; max-height: 200px; overflow: auto; padding: 4px; text-align: left; width: 97.5%;">
<pre id="codeSnippet" style="background-color: #f4f4f4; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding: 0px; text-align: left; width: 100%;"><span style="color: green;">-- For SQL 2012</span>
<span style="color: green;">-- DatabaseUsage Event Session</span>
<span style="color: blue;">CREATE</span> EVENT <span style="color: blue;">SESSION</span> [User_DBUsage] <span style="color: blue;">ON</span> SERVER
<span style="color: blue;">ADD</span> EVENT sqlserver.lock_acquired ( <span style="color: green;">-- EVENT is when a lock is acquired </span>
<span style="color: blue;">SET</span> collect_database_name = (1) <span style="color: green;">-- We want to get the DB name, too </span>
<span style="color: blue;">ACTION</span>(sqlserver.client_app_name <span style="color: green;">-- and the app name</span>
, sqlserver.client_hostname <span style="color: green;">-- and the host name</span>
, sqlserver.database_id
, sqlserver.database_name
, sqlserver.server_principal_name <span style="color: green;">-- the DB login used</span>
)
<span style="color: blue;">WHERE</span> (
[package0].[equal_uint64]([owner_type], (4)) <span style="color: green;">-- This is a shared lock</span>
<span style="color: blue;">AND</span> [package0].[equal_uint64]([resource_type], (2)) <span style="color: green;">-- This is a DB lock</span>
<span style="color: green;">-- AND [package0].[greater_than_uint64]([database_id], (4)) -- This would give info on all non-system DBs</span>
<span style="color: blue;">AND</span> [package0].[equal_boolean]([sqlserver].[is_system], (0)) <span style="color: green;">-- exclude system access</span>
<span style="color: blue;">AND</span> database_id = 5 <span style="color: green;">-- DB_ID of the target database</span>
<span style="color: blue;">AND</span> [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[server_principal_name], N<span style="color: #006080;">'DOMAIN\sqlSvcAcct'</span>) <span style="color: green;">-- exclude svc account</span>
)
)
<span style="color: blue;">ADD</span> TARGET package0.histogram ( <span style="color: green;">-- This is like a bucket to drop info into</span>
<span style="color: blue;">SET</span> filtering_event_name = N<span style="color: #006080;">'sqlserver.lock_acquired'</span>
,slots = (1) <span style="color: green;">-- number of databases you're looking for</span>
,source = N<span style="color: #006080;">'database_id'</span>
,source_type = (0)
)
,<span style="color: blue;">ADD</span> TARGET package0.event_file (<span style="color: blue;">SET</span> filename = <span style="color: #006080;">'c:\path\to\log_file.xel'</span>) <span style="color: green;">-- we're also saving more data to a file</span>
<span style="color: blue;">WITH</span> (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = <span style="color: blue;">NONE</span>
,TRACK_CAUSALITY = <span style="color: blue;">OFF</span>
,STARTUP_STATE = <span style="color: blue;">OFF</span>
)</pre>
<br /></div>
We’re first using the target that Jonathan included in his script, which is essentially a counter bucket. The histogram target allows us to keep track of the database and the number of connections that the xevent has recorded. This is fantastic for a quick-and-dirty look at whether the database has been used at all: If it doesn’t show up in the list, it isn’t in use. <br />
We can query the data in this histogram target this way:<br />
<div id="codeSnippetWrapper" style="background-color: #f4f4f4; border: 1px solid silver; cursor: text; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 20px 0px 10px; max-height: 200px; overflow: auto; padding: 4px; text-align: left; width: 97.5%;">
<pre id="codeSnippet" style="background-color: #f4f4f4; color: black; direction: ltr; font-family: "Courier New", courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding: 0px; text-align: left; width: 100%;"><span style="color: blue;">SELECT</span> slot.<span style="color: blue;">value</span>(<span style="color: #006080;">'./@count'</span>, <span style="color: #006080;">'int'</span>) <span style="color: blue;">AS</span> [<span style="color: blue;">Count</span>] ,
DB_NAME(slot.query(<span style="color: #006080;">'./value'</span>).<span style="color: blue;">value</span>(<span style="color: #006080;">'.'</span>, <span style="color: #006080;">'int'</span>)) <span style="color: blue;">AS</span> [<span style="color: blue;">Database</span>]
<span style="color: blue;">FROM</span>
(
<span style="color: blue;">SELECT</span> <span style="color: blue;">CAST</span>(target_data <span style="color: blue;">AS</span> XML) <span style="color: blue;">AS</span> target_data
<span style="color: blue;">FROM</span> sys.dm_xe_session_targets <span style="color: blue;">AS</span> t
<span style="color: blue;">INNER</span> <span style="color: blue;">JOIN</span> sys.dm_xe_sessions <span style="color: blue;">AS</span> s
<span style="color: blue;">ON</span> t.event_session_address = s.address
<span style="color: blue;">WHERE</span> s.name = <span style="color: #006080;">'User_DBUsage'</span>
<span style="color: blue;">AND</span> t.target_name = <span style="color: #006080;">'histogram'</span>) <span style="color: blue;">AS</span> tgt(target_data)
<span style="color: blue;">CROSS</span> APPLY target_data.nodes(<span style="color: #006080;">'/HistogramTarget/Slot'</span>) <span style="color: blue;">AS</span> bucket(slot)
<span style="color: blue;">ORDER</span> <span style="color: blue;">BY</span> slot.<span style="color: blue;">value</span>(<span style="color: #006080;">'./@count'</span>, <span style="color: #006080;">'int'</span>) <span style="color: blue;">DESC</span>;
<span style="color: blue;">GO</span></pre>
<br /></div>
I like using this query because it’s very fast, and it gives me a nice summary. Here’s what it looks like:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjSPmtM0UnAvCpNMpoKRNinj-aDlP5BM24jGBTfHjv1YqJbgBpPLqUtoYZXGUQcQ9WNMZlAhLgcK7441JK3M2iDddS-0bpXtTLmJx7nPpMVayGlMb3Xl-LkpTKkkQxS__Tqnxaa0zUSW24/s1600-h/image%25255B26%25255D.png"><img alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwmUUQ2O4zPybUDONykU35qZ4hw9Ufg3FhqRcy5jYeNPVd1ZrqTwzDpU9QL2qy-S6NZ_V3JZGejgdrD0km9g7db95tzyqmaFpdSVXdxtu6PggvuQtaX1FpPssRnoiHcQqlJvNqr_YikC9R/?imgmax=800" height="330" style="border-width: 0px; display: inline;" title="image" width="577" /></a> <br />
<br />
Note that the count isn’t the number of times the database has been used; it’s the number of shared locks taken out since the session started (this is cleared when the session stops and when SQL Server restarts). Different operations will produce a different number of locks.<br />
This *doesn’t*, however, tell us anything about the connections being made. So we’ve added another target to our session. <br />
This is the file target, and this gives us a lot of the details we might want about who is using a database. It’s a binary file, however, so you can’t just open it in notepad. SSMS provide what I think is the easiest method: simply go to the File menu, select <strong>Open –> File</strong> and browse to the XEL file you created. <br />
<br />
You’ll see something like this:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgShgRWRcsZGUMx5jwrWNyrXb1HpMhdjgwxI6XN1bn-uqLugyybCLhyphenhyphentIP_VVtCGGapRS3l8tZ4MPPbRtBZGjTE0347vfx4zEjn5oO_9wwmVjxuNNrwivyRDHSN8WXyqqT-gjf2bPgvdPzD/s1600-h/image%25255B34%25255D.png"><img alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMENYWkeQ3laDUHV0QVEUn3NqNONKnZ9HC5VYQIv6J885v7IGwJXghHHTIz8sBnMs3Vo_i3DTK-gj0qCpEihaGio-RWpfTZXR9WH_VFTQPo91kwx3oRaLZcG5E6SFYzw0fYHhhT9avxtQZ/?imgmax=800" height="667" style="border-width: 0px; display: inline;" title="image" width="458" /></a> <br />
Cool, huh? Note that the database name, client application, client host, and login all are present, so not only do you know that the database is in use, but who is using it.<br />
<br />
<h1>
Wrap-up</h1>
Extended events are a great tool, and I think this is a nice introductory use case for them. <br />
A couple of things to keep in mind:<br />
<ul>
<li>Xevents are lightweight, but they’re not free. It doesn’t make a ton of sense to create a job like this one on a really busy database, unless you’re looking for a particular client workstation or login; in that case, you’d change the predicates to look only for that targeted information.
<li>Because extended events are an evolving technology in SQL Server, there are and likely will continue to be changes to the events, actions, predicates, and targets available. This means you'll likely need to have version-specific xevent scripts around, depending on the SQL instance you're working on.
<li>We'll be looking some more at basic extended events usage; do check out all that Jonathan Kehayias has done. I promise you'll be pleased with all he's got.</li>
</li>
</li>
</ul>
<br />
<h1 id="deployme">
Deployment script</h1>
Here we've got a script that's set up with dynamic SQL to generate the extended event session for you. Note that it's not currently possible to use variables directly in a create session statement, so you have to generate the create statement dynamically, rather than just plug variables into it.<br />
<div id="codeSnippetWrapper">
<pre class="csharpcode"><span class="rem">-- SQL 2012</span>
<span class="kwrd">IF</span> <span class="kwrd">EXISTS</span> (
<span class="kwrd">SELECT</span> event_session_id
<span class="kwrd">FROM</span> sys.server_event_sessions
<span class="kwrd">WHERE</span> NAME = <span class="str">'User_DBUsage'</span>
)
<span class="kwrd">DROP</span> EVENT <span class="kwrd">SESSION</span> [User_DBUsage]
<span class="kwrd">ON</span> SERVER;
<span class="kwrd">GO</span>
<span class="rem">-- Set the DB name below</span>
<span class="kwrd">DECLARE</span> @dbid <span class="kwrd">INT</span> = (
<span class="kwrd">SELECT</span> db_id (<span class="str">'databaseName'</span>) <span class="rem">-- <-- here</span>
);
<span class="rem">-- Set the SQL Agent service account name below</span>
<span class="kwrd">DECLARE</span> @svcacct <span class="kwrd">VARCHAR</span>(100) = <span class="str">'DOMAIN\svcAccoutName'</span>;
<span class="rem">-- set the log file path below</span>
<span class="kwrd">DECLARE</span> @logfile <span class="kwrd">varchar</span>(300)=<span class="str">'c:\path\to\log_file.xel'</span>;
<span class="rem">-- ****** for routine deployment, no changes should be necessary below here *****</span>
<span class="kwrd">DECLARE</span> @sqltext <span class="kwrd">VARCHAR</span>(<span class="kwrd">max</span>) = <span class="str">' -- DatabaseUsage Event Session
CREATE EVENT SESSION [User_DBUsage] ON SERVER
ADD EVENT sqlserver.lock_acquired (
SET collect_database_name = (1)
ACTION(sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_id
, sqlserver.database_name
, sqlserver.server_principal_name
)
WHERE (
[package0].[equal_uint64]([owner_type], (4)) -- This is a SharedXactWorkspace lock
AND [package0].[equal_uint64]([resource_type], (2)) -- this is a DB-level lock
-- AND [package0].[greater_than_uint64]([database_id], (4)) -- this would exclude all system DBs
AND [package0].[equal_boolean]([sqlserver].[is_system], (0)) -- exclude system access
AND database_id = '</span> + <span class="kwrd">convert</span>(<span class="kwrd">VARCHAR</span>, @dbid) + <span class="str">' -- the ID of the database we want to monitor
AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[server_principal_name], N'</span><span class="str">''</span> + @svcacct + <span class="str">''</span><span class="str">') -- exclude the SQL Agent service account from all this.
)
)
ADD TARGET package0.histogram (
SET filtering_event_name = N'</span><span class="str">'sqlserver.lock_acquired'</span><span class="str">'
,slots = (1) -- number of databases you'</span><span class="str">'re looking for
,source = N'</span><span class="str">'database_id'</span><span class="str">'
,source_type = (0) )
,ADD TARGET package0.event_file(
SET filename='</span><span class="str">''</span>+@logfile+<span class="str">''</span><span class="str">')
WITH (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = OFF
,STARTUP_STATE = OFF
)'</span>;
--<span class="kwrd">print</span> @sqltext;
<span class="kwrd">EXEC</span> (@sqltext);
<span class="kwrd">GO</span>
<span class="kwrd">alter</span> event <span class="kwrd">session</span> [User_DBUsage] <span class="kwrd">on</span> server <span class="kwrd">state</span>=<span class="kwrd">start</span>;
go</pre>
<style type="text/css">.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
</style>
<br />
And SQL 2008 R2</div>
<pre class="csharpcode"><span class="rem">-- SQL 2008 R2</span>
<span class="kwrd">IF</span> <span class="kwrd">EXISTS</span> (
<span class="kwrd">SELECT</span> event_session_id
<span class="kwrd">FROM</span> sys.server_event_sessions
<span class="kwrd">WHERE</span> NAME = <span class="str">'User_DBUsage'</span>
)
<span class="kwrd">DROP</span> EVENT <span class="kwrd">SESSION</span> [User_DBUsage]
<span class="kwrd">ON</span> SERVER
<span class="kwrd">GO</span>
<span class="rem">-- Set the DB name below</span>
<span class="kwrd">DECLARE</span> @dbid <span class="kwrd">INT</span> = (
<span class="kwrd">SELECT</span> db_id(<span class="str">'DatabaseName'</span>) <span class="rem">-- <-- here</span>
);
<span class="rem">-- Set the SQL Agent service account name below</span>
<span class="kwrd">DECLARE</span> @svcacct <span class="kwrd">VARCHAR</span>(100) = <span class="str">'DOMAIN\svcAccountName'</span>
<span class="rem">-- set the log file path below</span>
<span class="kwrd">DECLARE</span> @logfile <span class="kwrd">varchar</span>(300)=<span class="str">'c:\path\to\log_file.xel'</span>;
<span class="rem">-- ****** for routine deployment, no changes should be necessary below here *****</span>
<span class="kwrd">DECLARE</span> @sqltext <span class="kwrd">VARCHAR</span>(<span class="kwrd">max</span>) = <span class="str">' -- DatabaseUsage Event Session
CREATE EVENT SESSION [User_DBUsage] ON SERVER
ADD EVENT sqlserver.lock_acquired (
ACTION(sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_id
, [sqlserver].[nt_username]
, sqlserver.username
)
WHERE (
[package0].[equal_uint64]([owner_type], (4))
AND [package0].[equal_uint64]([resource_type], (2))
-- AND [package0].[greater_than_uint64]([database_id], (4))
AND [package0].[equal_boolean]([sqlserver].[is_system], (0))
AND database_id = '</span> + <span class="kwrd">convert</span>(<span class="kwrd">VARCHAR</span>, @dbid) + <span class="str">'
AND [sqlserver].[not_equal_i_sql_unicode_string](sqlserver.nt_user, N'</span><span class="str">''</span> + @svcacct + <span class="str">''</span><span class="str">')
)
)
ADD TARGET package0.asynchronous_bucketizer (
SET filtering_event_name = N'</span><span class="str">'sqlserver.lock_acquired'</span><span class="str">'
,slots = (1) -- number of databases you'</span><span class="str">'re looking for
,source = N'</span><span class="str">'database_id'</span><span class="str">'
,source_type = (0)
)
,ADD TARGET package0.asynchronous_file_target(
SET filename='</span><span class="str">''</span>+@logfile+<span class="str">''</span><span class="str">')
WITH (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = OFF
,STARTUP_STATE = OFF
)'</span>;
--<span class="kwrd">print</span> @sqltext;
<span class="kwrd">EXEC</span> (@sqltext);
<span class="kwrd">GO</span>
<span class="kwrd">alter</span> event <span class="kwrd">session</span> [User_DBUsage] <span class="kwrd">on</span> server <span class="kwrd">state</span>=<span class="kwrd">start</span>;
</pre>
<style type="text/css">.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
</style> Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com0tag:blogger.com,1999:blog-1714283384790610704.post-42028723153434066302014-02-20T20:30:00.000-06:002014-11-12T15:48:32.004-06:00SQL Server Reporting Services Service Start Timeout SSRS will often timeout when starting under Windows Server:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifzvflTprRiJQbvka5UlwRLQyJUtruuMG_1KEYmDycIi-k3vP36sh8jkRxW6b79AkSf50s138CsDI53T0uXxL_bt4dyu6dhWSNoU3qJFUghuv96Fnuk7oMgwHE9tfYmlzPXH28kYYcaHsT/s1600/ssrs_timeout.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifzvflTprRiJQbvka5UlwRLQyJUtruuMG_1KEYmDycIi-k3vP36sh8jkRxW6b79AkSf50s138CsDI53T0uXxL_bt4dyu6dhWSNoU3qJFUghuv96Fnuk7oMgwHE9tfYmlzPXH28kYYcaHsT/s1600/ssrs_timeout.JPG" height="178" width="400" /></a></div>
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.<br />
<br />
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.<br />
<br />
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.<br />
<br />
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?<br />
<br />
So: disable CRL checking within SSRS. This is done with the <a href="http://msdn.microsoft.com/en-us/library/bb629393(v=vs.110).aspx" target="_blank">generatePublisherEvidence element</a> in the <a href="http://technet.microsoft.com/en-us/library/ms156479.aspx" target="_blank">ReportingServicesService.exe.config</a> file.<br />
<br />
By default, this file is in the <drive>:\Program Files\Microsoft SQL Server\MSRSxxx.MSSQLSERVER\Reporting Services\ReportServer\bin directory.<br />
<br />
To disable CRL checking, add the following just before the </runtime> tag at the bottom of the ReportingServicesService.exe.config file:<br />
<pre class="codesnippet"><generatePublisherEvidence enabled="false"/>
</pre>
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.Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com43tag:blogger.com,1999:blog-1714283384790610704.post-61404904078521862032014-02-20T20:06:00.000-06:002014-02-26T08:38:44.157-06:00Creating a Self-Signed SSL Certificate in Windows without IIS (for SSRS, for instance)Sometimes you have need for a SSL certificate on a Windows server when you don't have IIS installed. Like when you want to install SQL Server Reporting Services (SSRS).<br />
<br />
You could use OpenSSL, or download the 1+GB Windows SDK to get the <a href="http://msdn.microsoft.com/en-us/library/windows/desktop/aa386968(v=vs.85).aspx" target="_blank">makecert utility</a>. But those are troublesome, each in its own way.<br />
<br />
Happily, it's still possible to use the IIS 6 resource kit's SelfSSL, even without having IIS installed. And that's only a <a href="http://www.microsoft.com/en-us/download/details.aspx?id=17275" target="_blank">5MB download</a>. Plus, you can install only the SelfSSL piece, which is something like 50kB. Much, much better.<br />
<br />
So, download it from <a href="http://www.microsoft.com/en-us/download/details.aspx?id=17275" target="_blank">this link</a>. Install it on the server you want, and then run selfssl as below:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4nuFRualQXhjbbK9JzupVoy66NqX887V2iaqmfN5QtpdSxVa-7iv59LZiqJ-bUKVHc3wRF83Kz4CfZiH65A08VOH4guKyNTRqurxkpCUvH4lDeDmmiA0dUBOkIUvTQcU3EB4TiWSod82A/s1600/SelfSSL.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4nuFRualQXhjbbK9JzupVoy66NqX887V2iaqmfN5QtpdSxVa-7iv59LZiqJ-bUKVHc3wRF83Kz4CfZiH65A08VOH4guKyNTRqurxkpCUvH4lDeDmmiA0dUBOkIUvTQcU3EB4TiWSod82A/s1600/SelfSSL.PNG" height="318" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Note two things:</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<ol>
<li>Run the command prompt as administrator</li>
<li>You'll get an error, on account of not having IIS running on the server.</li>
</ol>
<div>
The error is OK: the certificate is installed in the computer personal certificate store. You can view it <a href="http://windows.microsoft.com/en-us/windows-vista/view-or-manage-your-certificates" target="_blank">this way</a>.</div>
<div>
<br /></div>
<div>
If trust is important on the server, you can use the /T switch for selfssl, and it will automatically add the certificate to the trusted root CA certificate folder, as well.</div>
<br />
<br />Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com17tag:blogger.com,1999:blog-1714283384790610704.post-69751737033480941662013-11-30T14:06:00.001-06:002013-12-29T10:15:27.334-06:000x80004005 error when launching Windows Store in Windows 8.1I ran into a problem recently that really had me scratching my head for awhile: when I launched the Windows Store on Windows 8.1 with a new user, the store itself failed with an error. The error message in the Store app read, "We weren't able to connect to the Store. This might have happened because of a server problem or the network connection timed out. Please wait a few minutes and try again. (0x80004005)."<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHYe1Mm8Z4BtDJ9QDHorxKbTao6clH5ybIvRbeM0LzVJwpVfVWpShPdT3uxebsg-Us3vosePTAjQRBmDM6sKytpjx63cDhIMco6gJFrzmVXQkDuBPenWVJXVG3bEe0la4ylmYuaMfHkvdI/s1600/WinStoreError.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="122" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHYe1Mm8Z4BtDJ9QDHorxKbTao6clH5ybIvRbeM0LzVJwpVfVWpShPdT3uxebsg-Us3vosePTAjQRBmDM6sKytpjx63cDhIMco6gJFrzmVXQkDuBPenWVJXVG3bEe0la4ylmYuaMfHkvdI/s400/WinStoreError.png" width="400" /></a></div>
<br />
The annoying thing about this (besides it not working) was that it pretty clearly wasn't trying to access the store: the app itself was failing before even having the chance to time out.<br />
<br />
This error persisted through reboots, and it only affected a new user. Creating another new user (local or MS account) had the same effect: the store errored out. New apps that attempted to install as part of a sync from another computer too, would fail in installing.<br />
<br />
<a name='more'></a><br />
<br />
I tried some of the easy-to-find solutions (they might work for you):<br />
<br />
<ol>
<li>wsreset.exe</li>
<li>a <a href="http://blogs.msdn.com/b/notime/archive/2013/10/18/fix-store-app-in-windows-8-1.aspx" target="_blank">Powershell script</a> </li>
<li>Deleting the user and creating a new one</li>
<li>switching over to a local (or MS) account</li>
<li>rebooting</li>
<li>running sfc /scannow (in an admin cmd prompt)</li>
</ol>
<div>
None of these would get the store to work.</div>
<div>
<br /></div>
<div>
I ran procmon and looked for access denied and other errors. I couldn't find a thing.</div>
<div>
<br /></div>
<div>
Finally, out of desperation, I tried the <a href="http://support.microsoft.com/kb/2714434" target="_blank">Windows Update troubleshooter</a>. I used the built-in troubleshooting control panel rather than the downloadable app.</div>
<div>
<br /></div>
<div>
This seemed like a long shot, as Windows update worked just fine. So did the store, for that matter, from other computer accounts.</div>
<div>
<br /></div>
<div>
The troubleshooter reported that it found two problems, and it fixed them. When I switched back to the new account, the store worked. Magic. </div>
<div>
<br /></div>
<div>
I don't know at all where the problem might have been, but perhaps this will help you, as well.</div>
<div>
<br /></div>
Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com4tag:blogger.com,1999:blog-1714283384790610704.post-71239330276333267342013-09-27T09:45:00.001-05:002013-12-29T10:16:15.806-06:00Scripting SQL Server Error Log Location in TSQL QueriesWhile 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.<br />
<br />
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:<br />
<br />
<pre class="codesnippet">SELECT SERVERPROPERTY('ErrorLogFileName');
</pre>
<br />
<a name='more'></a><br />
<br />
Now let's say we want, instead of the error log, simply the path to the error log directory, where we'll write our own log. We'll just pull out the error log filename (with a fun use of the reverse function):<br />
<br />
<pre class="codesnippet">declare @errorlog varchar (200)
select @errorlog=convert (varchar(200),SERVERPROPERTY('ErrorLogFileName'))
SELECT REVERSE(
Substring(
REVERSE(@errorlog)
, CHARINDEX('\', REVERSE(@errorlog)) + 1
, len(@errorlog)
)
)
</pre>
It's not strictly speaking necessary to set a variable to do the above, but it sure makes the query cleaner.<br />
<br />
That series of REVERSE calls, by the way, is simply a way to strip out everything after (and including) the last backslash. I've tried to format it here so it makes a little more sense than having it all on one line. Here's how it works:<br />
<br />
We reverse @errorlog within the substring so that it is backwards, and then we find the first occurrence of the character '\'. That's where we start our substring, and we don't truncate it on the end. Since we reversed it within the substring, we select the REVERSE of the substring, which gives us our original string.<br />
<br />
If you want to keep the trailing \ on the string, you simply can remove the ' + 1 ' at the end of the CHARINDEX line.Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com1tag:blogger.com,1999:blog-1714283384790610704.post-20601135374063799482013-09-25T11:53:00.003-05:002013-12-29T10:17:46.461-06:00Executing SQL Server Batch Statements Within One Exec(sql) Call<h2>
When you need to GO in your batch</h2>
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.<br />
<br />
<br />
If you weren't wanting to do this dynamically, you'd simply do something like this:<br />
<pre class="codesnippet">use [dbname];
go
Create schema [schema_name];
go
create user [username] for login [loginname] with default_schema=[schema_name];
go</pre>
<br />
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. <br />
<br />
<a name='more'></a><br />
<br />
This is because we can't use a GO command in an exec call: GO isn't valid SQL; it's a batch separator that SSMS (or sqlcmd, or osql) interprets. So if we put a GO command in our string to be executed, it'll fail.<br />
<br />
It turns out, however, we can nest EXEC commands, to create batches within batches. Instead of using the cursor for our first example, we'll simplify things, just putting all of this in one string to execute at the end:<br />
<br />
<pre class="codesnippet">Declare @sqlstm varchar(1800)
Declare @dbname sysname
set @sqlstm='
use [dbname];
exec(''Create schema [schema_name];'');
exec(''create user [username] for login [loginname] with default_schema=[schema_name];'');'
exec (@sqlstm)
go
</pre>
<br />
Just as a little example, you can see exactly how things work with this:<br />
<pre class="codesnippet">Declare @sqlstm varchar(1800)
set @sqlstm='
use [testdb];
exec(''Create schema [test_schema];'');
exec(''Create schema [test_schema2];'');
exec(''Create schema [test_schema3];'');
exec(''select name from sys.schemas;'');
exec(''drop schema [test_schema3];'');
exec(''select name from sys.schemas;'');'
exec (@sqlstm)
</pre>
<br />
So: multiple batches in a single dynamic SQL call. Enjoy!<br />
<br />Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com0tag:blogger.com,1999:blog-1714283384790610704.post-53938089744219068832013-04-01T16:54:00.000-05:002013-12-01T14:28:43.294-06:00Configuring a SQL Server Email Alert for SA Login Failures<h2>
SA Login <a href="http://upload.wikimedia.org/wikipedia/en/2/2f/Thumbs-down-icon.png" imageanchor="1" style="clear: left; display: inline !important; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="60" src="http://upload.wikimedia.org/wikipedia/en/2/2f/Thumbs-down-icon.png" width="66" /></a></h2>
SA Login authentication failures, essentially, should never happen. Mostly that's because we generally shouldn't be using the SA account, but rather should have dedicated application logins with appropriate privileges. Having those logins use Windows authentication instead of SQL authentication further removes SQL Server from the authentication process, <a href="http://www.sqlmag.com/blog/practical-sql-server-45/sql-server-2008-r2/staying-safe-coming-storm-140777" target="_blank">which is even better</a>.<br />
<br />
So if the SA account isn't disabled, I want to know when there's an SA login failure. <br />
[Generally, I want to know when there's an SA login success, as well, but we'll focus on the failures, here, because it's really low-hanging fruit.]<br />
<h2>
SQL Server Alerts</h2>
SQL Server Alerts are a very light-weight notification tool for lots of SQL Server events. Brent Ozar has a nice run-down of some <a href="http://www.brentozar.com/blitz/configure-sql-server-alerts/" target="_blank">basic alerts that you ought to consider implementing</a>; we'll add this one as another.<br />
<br />
It's possible--and frequent--to address this problem by using login triggers. That is certainly a valid, functional, and well-worn method. Login triggers can introduce problems, though, especially when we have <a href="http://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/" target="_blank">forgotten that they exist</a>. Moreover, this is what alerts are for, and there's something appealing to the simplicity of it all.<br />
<a name='more'></a><br />
<h3>
First, some alerts basics</h3>
Alerts use the SQL Server Agent to do their work, so it must be said at the outset that if you're using SQL Express, or if you don't have the SQL Server Agent configured, they won't work.<br />
<br />
Alerts fire off of event IDs and severities. So you can generate an alert for any event that shows up in the SQL error log. Using alerts is much, much better than reading and parsing a log file using xp_readerrorlog.<br />
<br />
Below you can see the GUI for defining an alert (we'll look at a script further down). You can see that you can specify databases, error numbers, a severity, and a substring filter.<br />
<pre class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKY0x9xr84fnYFn86Y2d5NEVwy_1Zy_J0HL9eMEnVJQJlv-Bdwp0JM0XMxmzbmc_SGW1pneKBdX1e6g_c9-DCHgI1c8wRsp5fImFbE3bX9txeLMONofKnaHveUtpLjipP6ul9vqjJ38qHM/s1600/SQLAlert1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="577" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKY0x9xr84fnYFn86Y2d5NEVwy_1Zy_J0HL9eMEnVJQJlv-Bdwp0JM0XMxmzbmc_SGW1pneKBdX1e6g_c9-DCHgI1c8wRsp5fImFbE3bX9txeLMONofKnaHveUtpLjipP6ul9vqjJ38qHM/s640/SQLAlert1.JPG" width="640" /></a></pre>
<br />
<h3>
SA Login Failure Alert Specifics</h3>
In our case, we want to raise an alert on error number 18456, which is a login failure. We also are only interested in SA logins. Because this is a substring match (like '%sa%'), if we simply put SA in this field, it'll also match on login failures for the login 'FDSA_User', or any login that has 'SA' in its name.<br />
<br />
Happily, the error text puts the login name in single quotes, so we can match on 'sa' (like '%''sa''%'). This will limit our login failure alerts to just the SA login.<br />
<h4>
SQL Alert Script</h4>
Here is what a SQL script to accomplish this will look like:<br />
<br />
<pre class="codesnippet">--Login Failure Alert
EXEC msdb.dbo.sp_add_alert @name=N'Login failure alert - SA',
@message_id=18456,
@severity=0,
@enabled=1,
@delay_between_responses=30,
@include_event_description_in=1,
@event_description_keyword=N'''sa''',
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
</pre>
<br />
And to add a notification email to this alert, we'd do the following:
<br />
<pre class="codesnippet">EXEC msdb.dbo.sp_add_notification @alert_name=N'Login failure alert - SA',
@operator_name='DBAs_oncall',
@notification_method = 1;
GO</pre>
<br />
The above assumes there is an operator with the name of 'DBAs_oncall'. You'd want to change that to match an operator already defined for your SQL Server Agent.<br />
<br />Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com0tag:blogger.com,1999:blog-1714283384790610704.post-43647496019055497462012-06-22T13:25:00.000-05:002014-02-21T14:45:55.415-06:00Importing a .SAS7BDAT file into SQL Server Using SSISI was tasked with importing a .SAS7BDAT file into SQL Server the other day, and there were enough non-intuitive pieces to it that I thought I'd write down the steps while they still were fresh.<br />
<br />
Some searching shows it's a problem with several solutions, but most of what shows up seems unnecessarily complicated, at least, for someone who's used to using Integration Services. It turns out that there's a very easy method for accessing the data in one of these files, and it doesn't require a SAS client. It does require a download from SAS, but that is freely available.<br />
<br />
<a name='more'></a><h2>
Download and Install the SAS OLEDB driver</h2>
<div>
The first step is to download the SAS OLEDB driver. This is freely available from the SAS web site, but it's not completely easy to get to. Here's the link to the information about the SAS driver: http://support.sas.com/documentation/tools/oledb/gs_olap_install.htm. When you go to that page, you'll see something that looks like this:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqJ4xp5NmwoeFKEq_BUA8GA-pZAbrmcTzYlD81jKAk-5RIlGqdYihfHSVeqA0ox6otyzya7ebTZacSU8Q5cgn7lKPuWStyCGDfeu_xYSJ_Vb7wHLA9D7NEGGXIzCxw1vDjFXLpdQIOI9Hi/s1600/SASDownload1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqJ4xp5NmwoeFKEq_BUA8GA-pZAbrmcTzYlD81jKAk-5RIlGqdYihfHSVeqA0ox6otyzya7ebTZacSU8Q5cgn7lKPuWStyCGDfeu_xYSJ_Vb7wHLA9D7NEGGXIzCxw1vDjFXLpdQIOI9Hi/s400/SASDownload1.JPG" height="70" width="400" /></a></div>
<div>
<br /></div>
<div>
You'll see that they give you the link to the download page, but they don't hyperlink it; curious, no? Here's a clickable link to the OLE DB Providers download page: <a href="http://support.sas.com/demosdownloads/setupcat.jsp?cat=SAS+Providers+for+OLE+DB">http://support.sas.com/demosdownloads/setupcat.jsp?cat=SAS+Providers+for+OLE+DB</a><br />
<br />
Unfortunately, SAS requires a login to access their downloads. Fortunately, there's no commitment involved in creating a login to their site. Download the latest version of the OLE DB providers. <br />
<br />
<div style="-moz-border-radius: 25px; border-radius: 25px; border: 2px solid #a1a1a1; padding: 10px 40px;">
NOTE: Business Intelligence Development Studio (BIDS) is a 32-bit application that does some funky stuff with providers. If you're running on a 64-bit system (and especially if you're going to be scheduling a regular job), you may well want to download both the 32-bit and 64-bit drivers. BIDS uses 32-bit providers during runtime and debug (design time), but can also utilize the 64-bit providers. If you're just doing a one-off data load, the 32-bit drivers may well be sufficient for your needs.</div>
<br />
Install the providers. Note the selection below: we only need to install the OLE DB providers.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_TKk7-jsDUyqJtQD4nZ5xaGtC7EONyAlymETqxPN8hec790soI5VXI1ikhjasTq0EifRo4PXeGNqLy10Ce5kkFu71LkiW7J24cd8-VCZyL13q4DybnN1Mm1GMzm5_5zzJatQi1CZjqi4X/s1600/SASInstall1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_TKk7-jsDUyqJtQD4nZ5xaGtC7EONyAlymETqxPN8hec790soI5VXI1ikhjasTq0EifRo4PXeGNqLy10Ce5kkFu71LkiW7J24cd8-VCZyL13q4DybnN1Mm1GMzm5_5zzJatQi1CZjqi4X/s400/SASInstall1.JPG" height="146" width="400" /></a></div>
<br />
I also typically deselect all but the minimum languages:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoAs4N_XzFDTEUYGw-RKsHsLzT5gOa9Dz7Cmk7ElgHaMn7ZhhqTznSQwJ3xQBppVLukDjNtmwEivSs32geulfgRaFcQFdFcavqJcqAL6DHTH63cV-3AxAjTXskRLcYK-bWTHKSGSNRvDT6/s1600/SASInstall2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoAs4N_XzFDTEUYGw-RKsHsLzT5gOa9Dz7Cmk7ElgHaMn7ZhhqTznSQwJ3xQBppVLukDjNtmwEivSs32geulfgRaFcQFdFcavqJcqAL6DHTH63cV-3AxAjTXskRLcYK-bWTHKSGSNRvDT6/s320/SASInstall2.JPG" height="182" width="320" /></a></div>
<br />
And after some checks, we're ready to install.
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIZ0FsUMPwhTpp2Q7cieeOGcn5rNASpifqc7emJfL1agXG2klOylYRMqQXUn6wMvViFt3HIKzgAdG7jjSdAipQnHB1kKDRK-Cn2XsErkj4iwU0LAsngv1PD1zsIjTiMH9IlKu-yqZMlXGB/s1600/SASInstall3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIZ0FsUMPwhTpp2Q7cieeOGcn5rNASpifqc7emJfL1agXG2klOylYRMqQXUn6wMvViFt3HIKzgAdG7jjSdAipQnHB1kKDRK-Cn2XsErkj4iwU0LAsngv1PD1zsIjTiMH9IlKu-yqZMlXGB/s400/SASInstall3.JPG" height="307" width="400" /></a></div>
<br />
Note that we're installing both the 32-bit and 64-bit providers here. This was included in the SASOLEDBWeb_xxxxx_win_xx_web_1 download.<br />
<br />
<h2>
Design and Run your SSIS Package</h2>
Now that the SAS OLEDB drivers are installed, it's time to use them in SSIS.<br />
<br />
The first step, once you've open BIDS and created a new project and package, is to create your connection manager. Right-click in the <b>Connection Managers</b> window and select <b>New OLE DB Connection...</b><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhe7kY5BkKNE4NzYf3mpIZ-zTVvnXkOoP29eMOiJk-B3cyF_tDgyhEl_zceVKc6SYaGlIuiNMviW3-xWvp59RyliCf_DAG-jh_rLjf0Usg2ddcs34VJeN8nIiiZqa-yg_3vSRvnAPbznusE/s1600/SASImport1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhe7kY5BkKNE4NzYf3mpIZ-zTVvnXkOoP29eMOiJk-B3cyF_tDgyhEl_zceVKc6SYaGlIuiNMviW3-xWvp59RyliCf_DAG-jh_rLjf0Usg2ddcs34VJeN8nIiiZqa-yg_3vSRvnAPbznusE/s1600/SASImport1.JPG" /></a></div>
<br />
One of the new options, now that you've installed the SAS providers, is "<b>SAS Local Data Provider</b>". This is what you want to select:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTE7OYtT7Hciq7IzxiETuTjCWMCw067co40YtcW7OOWQ6z63qNHQz8gfOttbeJAzDOClNGqNWOI8A8_lveCRrP47RM2qFfhHy3dvut1moXPZ1TxD3LVg87f90XkcaqkpDd0_oTIjpoHrNI/s1600/SASImport2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTE7OYtT7Hciq7IzxiETuTjCWMCw067co40YtcW7OOWQ6z63qNHQz8gfOttbeJAzDOClNGqNWOI8A8_lveCRrP47RM2qFfhHy3dvut1moXPZ1TxD3LVg87f90XkcaqkpDd0_oTIjpoHrNI/s400/SASImport2.JPG" height="400" width="387" /></a></div>
<br />
You'll then see a window like the one below:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh846vwzblAcNNeY1tq2xAGUOp2Nyw4uwKzlO4t6VvK0v7lupqJa1q1q8tZQhztWTq-5y9IRf8_q0afLq01ZmVAgVZZss7EVd1WiCQ__xSD9WjmSdMqt2IxLZIi884K1AIydYPbr5OKlswa/s1600/SASImport3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh846vwzblAcNNeY1tq2xAGUOp2Nyw4uwKzlO4t6VvK0v7lupqJa1q1q8tZQhztWTq-5y9IRf8_q0afLq01ZmVAgVZZss7EVd1WiCQ__xSD9WjmSdMqt2IxLZIi884K1AIydYPbr5OKlswa/s400/SASImport3.JPG" height="400" width="385" /></a></div>
And here's the non-intuitive part: the "server or file name" setting is the directory containing the .sas7bdat file. You don't specify the filename anywhere in the connection manager.<br />
<br />
<h2>
Using the SAS OLEDB Connection</h2>
Now that you've got the SAS connection created, create a new Data Flow task and within that create a new OLE DB Source. Select the connection manager you created in step one. In the table or view field, you'll see a list of all of the .SAS7BDAT files in the directory you specified in the connection manager (highlighted below); this is the table source for the rest of your job.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2NsRs-QiWmQcbL4YxwIw2qJ8s2RmdOaolPhhbA_-b6u4S5HSeewAM0i0Aulhyphenhyphen_E8_2qszp1o0yMjyvUZ9kMyxUD-jfpywc9PnKlyDv4ygSJipgPrMGC5QsTX7HA8mVcK1uaMD6q6bEwNl/s1600/SASImport4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2NsRs-QiWmQcbL4YxwIw2qJ8s2RmdOaolPhhbA_-b6u4S5HSeewAM0i0Aulhyphenhyphen_E8_2qszp1o0yMjyvUZ9kMyxUD-jfpywc9PnKlyDv4ygSJipgPrMGC5QsTX7HA8mVcK1uaMD6q6bEwNl/s400/SASImport4.JPG" height="390" width="400" /></a></div>
<br />
Now that you've got your source set up, you can create data transforms, conditional logic, and the like, just as you would with any SSIS package. Of course, most of us just want a straight data load, so we'd connect our SAS source directly to the DB destination, something like the picture below:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPWUHj2oq6goCtcTwJm7rSBr5q5HrR6lmCio2dhGD6bw4-Mos3v9P8fIZIqkXxAeQQwb7f7fz6omco9nfHLmZYRYgldpB1AY55jnd2obTpojjwpIbhRP4m7P-qcAZAW2IQwyNt-FNIrnFv/s1600/SASImport5.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPWUHj2oq6goCtcTwJm7rSBr5q5HrR6lmCio2dhGD6bw4-Mos3v9P8fIZIqkXxAeQQwb7f7fz6omco9nfHLmZYRYgldpB1AY55jnd2obTpojjwpIbhRP4m7P-qcAZAW2IQwyNt-FNIrnFv/s1600/SASImport5.JPG" /></a></div>
<br />
<br />
<br />
You'll notice there's a warning in the source object. That's the result of the SAS file not communicating code page information to BIDS; generally speaking, this shouldn't be a problem.<br />
<br />
Below is the actual warning window:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeUrG0VPWJzZJAYW19sHnrup3fdB-3x59Ni-h5_ovMc2KX4H_-SiDmLQPATggBPWLmeyNO3ZwiZY2GoYV1MvXGdohXvZIVJ6w0SZA4eSr4RwgmSKjP8Vz7RKm1cC7xdHzzWz2_MS1jElb9/s1600/SASImport6.JPG" imageanchor="1"><img alt="[SAS OLE DB Source [1]]: Cannot retrieve the cloumn code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used." border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeUrG0VPWJzZJAYW19sHnrup3fdB-3x59Ni-h5_ovMc2KX4H_-SiDmLQPATggBPWLmeyNO3ZwiZY2GoYV1MvXGdohXvZIVJ6w0SZA4eSr4RwgmSKjP8Vz7RKm1cC7xdHzzWz2_MS1jElb9/s400/SASImport6.JPG" height="156" title="SAS OLEDB Code Page Warning" width="400" /></a></div>
<br />
You can clear up the warning icon simply by telling BIDS to use the default code page in the source properties window, as below:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWlMbFOgSQhe-QHPCXMi8WeExXpGH6jbSKtxZTomIQBcqC5p_zCQR2I_7rG0133Vy7DKCT_oxK79xV2lLL4UfkKRxEYhwSICEWZ80mtIAcniG3tW_1m-KitwrZUs_reEErg1BoRpCbpuCu/s1600/SASImport7.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWlMbFOgSQhe-QHPCXMi8WeExXpGH6jbSKtxZTomIQBcqC5p_zCQR2I_7rG0133Vy7DKCT_oxK79xV2lLL4UfkKRxEYhwSICEWZ80mtIAcniG3tW_1m-KitwrZUs_reEErg1BoRpCbpuCu/s1600/SASImport7.JPG" /></a></div>
<br />
If that causes character encoding errors, and BIDS is unable to determine the code page, you'll need to specify the DefaultCodePage setting and change the AlwaysUseDefaultCodePage to true.<br />
<br />
And that should be it: you can import SAS data to SQL Server (or any other SSIS-supported destination, for that matter) without an additional SAS license or client install.</div>
Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com27tag:blogger.com,1999:blog-1714283384790610704.post-77947797676068030792012-05-27T16:53:00.001-05:002013-09-27T10:28:50.687-05:00Scheduling a SQL Agent Job to Run on a Calculated Day that isn’t included in the Regular Scheduler OptionsI’ve had a hard time coming up with a title for this post, because while the concept is easy to understand, it’s difficult to summarize.<br />
We refresh one of our development instances on the first Monday of each month. That’s an easy thing to schedule, as it’s an option within the SQL Agent scheduler:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKV3OMlabyWXHmPxZEYclBr6aN8Ty8CZwsar1iRe-wogktsU70f_A9UdZgQuwYhnLv2pb2O3BauEN5zOOOou4bK1CynfktMbX1msyASqx7e6GsKjx5ejf3cjRkOAKV8FUm5s3qYA4jzqdM/s1600-h/image%25255B6%25255D.png"><img alt="image" border="0" height="399" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9N-QH2IW-ChGemE_lATuHAO0V2JfuT-XpfTP6C-z4yaMYgfEC0iR8kmgkWMFAXp_AXDb2nsntUUHt7hlV57fjtg9RA6tkDKytH2VPchtnP6nbxevlAQpp6fCw2bOP7EQEXgKfo3WRdBK9/?imgmax=800" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="455" /></a><br />
We want to send a notification to the affected users beforehand, however, and since the refresh occurs in the wee hours of the morning, we can’t send out that alert on Monday. It has to occur on the Friday before the first Monday. <br />
That, friends and neighbors, isn’t a built-in option.<br />
It’s a little trickier (though in the end, not terrible) because the Friday before the first Monday could be in this month or in next month.<br />
<br />
<a name='more'></a><br />
So what to do? One good solution is to schedule a job to run every Friday. It checks to see if today is the Friday before the first Monday, and if it is, it sends an email.<br />
Here’s that the script looks like that accomplishes this:<br />
<pre class="codesnippet" div="div">
DECLARE @msgbody VARCHAR(500)
DECLARE @emaillist VARCHAR(100)
DECLARE @subj VARCHAR(100)
DECLARE @FONM DATETIME -- FirstofNextMonth
DECLARE @dateofrefresh VARCHAR(50)
SET @emaillist = <a href="mailto:'NotifcationList@mymail.edu'">'NotifcationList@mymail.edu'</a> --Email recipients, semicolon separated
SET @FONM = DateAdd(Month, 1 + DateDiff(Month, 0, GETDATE()), 0) --First day of the next month
SET @dateofrefresh = CONVERT(VARCHAR, DateAdd(day, (9 - DatePart(weekday, @FONM)) % 7, @FONM), 101) --First Monday of next month
/* @dateofrefresh is the 1st Monday of the month.
The IF statement below checks to see if 3 days from today is the first Monday.
If it is, the refresh will happen on Monday, so we send the email.
*/
IF CONVERT(VARCHAR, dateadd(day, 3, getdate()), 101) = @DateOfRefresh -- Friday is 3 days from Monday, so if these match, send the email
BEGIN
SET @subj = 'DB refresh scheduled for ' + @dateofrefresh
SET @msgbody = 'Your DB is scheduled for refresh on ' + @dateofrefresh + '.'
+ CHAR(13) + CHAR(10)
+ 'Please ensure that all of your data has been saved, as it will be overwritten by a copy of production.'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'email_profile'
,@recipients = @emaillist
,@subject = @subj
,@body = @msgbody
END </pre>
<h2>
</h2>
<h2>
Working through the rationale</h2>
The first thing we do is find the first day of next month, as we’ll do our following calculation based on that:<br />
<pre class="codesnippet">
SET @FONM = DateAdd(Month, 1 + DateDiff(Month, 0, GETDATE()), 0)</pre>
<br />
The inner part of this:<br />
<pre class="codesnippet">
DateDiff(Month, 0, GETDATE())</pre>
returns the number of months between date zero (1/1/1900) and the first of this month. The outer DateAdd piece adds one month to that number, which is the first of next month.<br />
<br />
Once we’ve got the first day of next month, we need to find the closest Monday to that date:<br />
<pre class="codesnippet">
CONVERT(VARCHAR, DateAdd(day, (9 - DatePart(weekday, @FONM)) % 7, @FONM), 101) </pre>
<br />
The inner-most part of this line<br />
<pre class="codesnippet">
DatePart(weekday, @FONM)</pre>
returns the integer 1 – 7 corresponding to Sunday – Saturday.<br />
<br />
The next piece <br />
<pre class="codesnippet">
DateAdd(day, (9 - DatePart(weekday, @FONM)) % 7, @FONM)</pre>
Does the actual math. What we’re doing is subtracting the datepart integer from 9, which gives us the number of days between the first of the month and the next Monday. The %7 forces SQL server to use Sunday as the first day of the week, so we don’t have to worry about systems with a non-default @@datefirst setting.<br />
<br />
Once we have the date for the first Monday of next month, we’ll check and see if today is three days from that date. If it is, then this is the Friday before the first Monday, and we send our email. If it isn’t, we simply end, doing nothing<br />
<pre class="codesnippet">
IF CONVERT(VARCHAR, dateadd(day, 3, getdate()), 101) = @DateOfRefresh -- Friday is 3 days from Monday, so if these match, send the email
BEGIN
SET @subj = 'DB refresh scheduled for ' + @dateofrefresh
SET @msgbody = 'Your DB is scheduled for refresh on ' + @dateofrefresh + '.'
+ CHAR(13) + CHAR(10)
+ 'Please ensure that all of your data has been saved, as it will be overwritten by a copy of production.' <br />
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'email_profile'
,@recipients = @emaillist
,@subject = @subj
,@body = @msgbody</pre>
<div>
<style>
.adslot-overlay {position: absolute; font-family: arial, sans-serif; background-color: rgba(0,0,0,0.65); border: 2px solid rgba(0,0,0,0.65); color: white !important; margin: 0; z-index: 2147483647; text-decoration: none; box-sizing: border-box; text-align: left;}.adslot-overlay-iframed {top: 0; left: 0; right: 0; bottom: 0;}.slotname {position: absolute; top: 0; left: 0; right: 0; font-size: 13px; font-weight: bold; padding: 3px 0 3px 6px; vertical-align: middle; background-color: rgba(0,0,0,0.45); text-overflow: ellipsis; white-space: nowrap; overflow: hidden;}.slotname span {text-align: left; text-decoration: none; text-transform: capitalize;}.revenue {position: absolute; bottom: 0; left: 0; right: 0; font-size: 11px; padding: 3px 0 3px 6px; vertial-align: middle; text-align: left; background-color: rgba(0,0,0,0.45); font-weight: bold; text-overflow: ellipsis; overflow: hidden; white-space: nowrap;}.revenue .name {color: #ccc;}.revenue .horizontal .metric {display: inline-block; padding-right: 1.5em;}.revenue .horizontal .name {padding-right: 0.5em;}.revenue .vertical .metric {display: block; line-height: 1.5em; margin-bottom: 0.5em;}.revenue .vertical .name, .revenue .vertical .value {display: block;}.revenue .square .metric, .revenue .button .metric {display: table-row;}.revenue .square .metric {line-height: 1.5em;}.revenue .square .name, .revenue .square .value, .revenue .button .value {display: table-cell;}.revenue .square .name {padding-right: 1.5em;}.revenue .button .name {display: block; margin-right: 0.5em; width: 1em; overflow: hidden; text-overflow: clip;}.revenue .button .name:first-letter {margin-right: 1.5em;}a.adslot-overlay:hover {border: 2px solid rgba(58,106,173,0.9);}a.adslot-overlay:hover .slotname {border-bottom: 1px solid rgba(81,132,210,0.9); background-color: rgba(58,106,173,0.9);}a.adslot-overlay:hover .revenue {border-top: 1px solid rgba(81,132,210,0.9); background-color: rgba(58,106,173,0.9);}div.adslot-overlay:hover {cursor: not-allowed; border: 2px solid rgba(64,64,64,0.9);}div.adslot-overlay:hover .slotname {border-bottom: 1px solid rgba(128,128,128,0.9); background-color: rgba(64,64,64,0.9);}div.adslot-overlay:hover .revenue {border-top: 1px solid rgba(128,128,128,0.9); background-color: rgba(64,64,64,0.9);}
</style></div>
Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com3tag:blogger.com,1999:blog-1714283384790610704.post-40085364526292401092012-05-25T20:23:00.001-05:002015-01-29T08:47:32.181-06:00Pasting Code to OneNote 2010 with Formatting and Colors[Update 1/2915]<br />
Head over <a href="http://tech.lanesnotes.com/2015/01/paste-sql-and-other-text-into-onenote.html">here</a> for a solution for OneNote 2013. Annoying, but it works!<br />
<br />
I use OneNote as a SQL script repository, and in most respects it’s a great solution: it’s searchable, the notebooks make organizing easy, and its integration with SharePoint (and SkyDrive) is pretty good.
There’s one problem, though, that has made it less than ideal: color formatting doesn’t come through from SSMS.
<a href="http://notepad-plus-plus.org/" target="_blank">Notepad++</a> is my go-to editor for long scripts (if you’ve not used it, you should check it out), and it has what ought to be a perfect combination of plug-ins for the job:
<br />
<ul>
<li>First is the excellent <a href="http://lanestechblog.blogspot.com/2011/12/sql-formatting-withing-ssms.html" target="_blank">Poor Mans’ T-Sql Formatter</a>. This is a first-rate SQL formatter that integrates with SSMS and Notepad++.</li>
<li>Second is NppExport (included with NPP), which allows you to copy the selected text to the clipboard as HTML. Unfortunately, OneNote doesn’t really handle HTML (even!) very gracefully: NppExport html is rendered by OneNote without any line breaks.
It should be noted that this appears to be a limitation in the rendering that OneNot does, not in the HTML generated by NppExport.</li>
</ul>
Rant aside, please allow me to say that *this should work!*
I’ve been on a crusade to figure out how to paste my code from SSMS to OneNote while retaining the color formatting, and it’s taken awhile to find a good solution.
<a href="http://www.blogger.com/blogger.g?blogID=1714283384790610704" name="more"></a>
After a lot of searching, I ran across <a href="http://translate.google.com/translate?sl=zh-CN&tl=en&js=n&prev=_t&hl=en&ie=UTF-8&layout=2&eotf=1&u=http%3A%2F%2Fnotehighlight.codeplex.com%2F" target="_blank">NoteHighlight</a> on CodePlex. The instructions (and the installer) are written in Chinese, so the hyperlink here is to the Google Translation link to that page. I have this to say about it: It. Works. Well. In OneNote 2010. If you're using ON 2007 or (like me, now) ON 2013, I'm afraid there's still not a good option.<br />
<br />
Here’s what my code looked like when pasting formatted SQL from NppExport:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizvgay7o9DhidzSAXVvfSJAAeAiAx21iVDVrrRvBl_-5xLItW2TjQQNRy-rJN_O9-j4Z3bII-RNgDLXtq3e5Z452shV0ykJQZiIKsYpk_wI02Sc3Mc6SEk_lQexZNnJxP7tOnHurl6MFkf/s1600-h/image%25255B3%25255D.png"><img alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4Enia1MeJSr_wHoiFWDbgoZPD-GJraasTx6bsZszMdwjnDjM1hImrQFQI_egPEfLXWyH0SQbodKKGgP0Gb-rZJ86nRZUitqnmV6gbFG6EtfxOr3D891gA8JXxUOTjllpBkipawDT9XMTp/?imgmax=800" height="281" style="background-image: none; border: 0px currentColor; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="425" /></a><br />
<div style="clear: both;">
I'm guessing, by the way, that this is a result of NPPExport using the White-space:pre tag in the exported HTML (css, really). It would appear that ON doesn't do full HTML. See <a href="https://onenote.uservoice.com/forums/256328-onenote-users/suggestions/6189896-respect-whitespace-tags-in-pasted-html-text-e">this connect</a> item for an upvote on fixing that. <br />
<br />
Update: Microsoft has removed Connect options for OneNote, so there's no longer a good way to submit or track bugs. <br />
<br />
<br />
Here is the same SQL when pasted from NoteHighlight:
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDq6RjrSPKX0rbblzQXfB9RlxJO2tj6ARGVsFm9h3HmRVqEMntePSGzEpQgWMWX9mbxQ04QY95a28yDRdGvGcBKWbW6U9TuFVmFUc0K9Bo1Hn34540a_HZE3rQf5mCaoDkdAQOMhq-ahUM/s1600-h/image%25255B8%25255D.png"><img alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkcKEr-8DiTODn_pTYPlZTspZDh0jN2iTsaqmUdfSyI-R9vRL1Y42nwxXansnChydS3q5gnZ10clURZUdmIcdEHd31ZI0WVfThkDnq-jKlpXeQsh6-FNPPG_jTl-cY0Twa1GGoGK1hZGvq/?imgmax=800" height="275" style="background-image: none; border: 0px currentColor; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="421" /></a><br />
Better, huh? Note that the colors aren’t the same: NoteHighlight actually does some parsing of the code and does its own coloring. You can choose from a variety of syntax color schemes.<br />
<br />
Update 8/26/12: Bad news for folks using ON 2007: this only appears to work with OneNote 2010. Thanks to <a href="https://plus.google.com/103968640394430697195/posts">+Edelman</a> for pointing that out. The NoteHighlight page on Codeplex appears to be pretty static, too, so I wouldn't hold out a lot of hope for feature requests and the like.<br />
<br />
<a name='more'></a></div>
<h2>
Installation</h2>
Since the installation is in Chinese (<a href="http://translate.google.com/translate?sl=zh-CN&tl=en&js=n&prev=_t&hl=en&ie=UTF-8&layout=2&eotf=1&u=http%3A%2F%2Fnotehighlight.codeplex.com%2Fwikipage%3Ftitle%3DInstall" target="_blank">documentation here</a>), it’s a little hard for us non-sinophones to follow it. Happily, the installation appears to allow for (mostly) default click-through.<br />
<b><br /></b>
<br />
<div style="-moz-border-radius: 5px; background: #dddddd; border-radius: 25px; border: 2px solid; padding: 10px 40px;">
Make sure you have the correct version: most of us run the 32-bit version of office, which is the second choice on the <a href="http://translate.google.com/translate?hl=en&sl=zh-CN&tl=en&u=http%3A%2F%2Fnotehighlight.codeplex.com%2Freleases&anno=2">download page</a>. If you install the wrong version, the plugin simply won’t appear in OneNote.</div>
<br />
When you run setup, you’ll see a window like the one below:<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiTsVSmlF4GgvoQkKI-CahQFA2VrdHy1si_W0yJknUSo1g36Sj5dXcq5BT6NRj024Qg1ujgt8cr5RGjnAPv5dSxQ3dvY0AbIa8ucsDzuJ2IrYW6cu1Q9l_LOf74rAZUn-Jj5DUKWgH0ac9/s1600-h/image%25255B12%25255D.png"><img align="left" alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-sNrCilPb112UIKoeVElb-L5_jZlQ-ZUpV0eakOSHNGs_h-5rUbEmOcQxS1GlGn_1-2K_tNI_gPxNu4XpJQsciu5h3RW7l0kiTDIdgU4Iq8RVwrpWozGBhRxJoAS2eopJ6qkFAhJt3nS-/?imgmax=800" height="386" style="background-image: none; border: 0px currentColor; display: inline; float: left; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="349" /></a><br />
<br />
This would appear to be a EULA. And in fact, if we paste this text into a translator, it appears to be the Microsoft .Net 3.5 EULA (more or less). Sure enough if we click accept (A) (not the default), we see dotnetfx35setup.exe run. So it’s checking to see if we’ve got .Net 3.5 installed.
<br />
<div style="clear: both;">
<br /></div>
<div style="clear: both;">
<br />
Next we get the main installation window:
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUaHx5lGGmLR9jUQsW5k6TkhSMMjw74WTDodunWgWd_HqSuecMIYXCLFHaG8WpAKc5ljbj59n56h1d60o_X0-JsRfIII1cZco-7WEim-kCHLEDHWhWjHdf1vBoGpOqTF24iRdUgTCUuT8S/s1600-h/image%25255B16%25255D.png"><img align="left" alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgkG0DMlayVfLL5ma8BunCtuFXktsDh-acI7dWDv2enLyL81xmrBHKsvsX4e1TFBtBdNFoANw9MnJZ3Vg2AMJiOt-9AYgIZ3XOJd6vhS2886zRR65sz6VVtIGxIwMu7iIjgeKwrKcpESO6/?imgmax=800" height="384" style="background-image: none; border: 0px currentColor; display: inline; float: left; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="476" /></a>
The default button (N) appears to be <strong>Next</strong>.
</div>
<div style="clear: both;">
<br />
The next window allows, at the least, for you to pick the installation location:
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUpctfNiq0li8VCVJGrgRDhP0d7xyCPX7WdyKzFT0JyWUWasJoA8s0G6V99bJCFQp9oOuuDQXL2XK2JFJHKhdSw2GW1gs9_DyNyCyreE7ZIGBKGZSyRped7Z9CofpKrBTKvN2Ja00WuU37/s1600-h/image%25255B20%25255D.png"><img align="left" alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtjVLSwo2a8WdM3iuoS-59ZvSPaV3hDJqKGHDNv5chdW0TBNZv5qAgSUzhaP7IxDe5AlB1e_X-T3xVKmb4c6L4YSCFGOr0T98YgkEsygOVi6xeeZnP98CqFnWGiGTi3Fk6U4cdOdtp7umN/?imgmax=800" height="384" style="background-image: none; border: 0px currentColor; display: inline; float: left; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="477" /></a></div>
<div style="clear: both;">
I’m guessing that the (M) and (E) choices refer to “Me” and “Everyone”. As in “Make this available to just me or to everyone who uses this computer.” I <em>can</em> verify that selecting the defaults worked just fine for me.</div>
<div style="clear: both;">
<br /></div>
<div style="clear: both;">
<br /></div>
<div style="clear: both;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6-tJt1kw-_uLrkqVroyKon5_byi4Nh_dPM1lsEXYGYXzzWTyNBcYatVuS4uEyh3b1toLR5dTnfwsPwFnHedgRaDO06Rf5XYmBommvALeIIG2fKENRqgbTiPW8zLRmq_rx72BOy9Dnn_RH/s1600-h/image%25255B24%25255D.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaPSEPvKleEwSP2uZQutKrFFVJXMixQ8v9qdMOXYn_dIc2q-t6RH2d_bbYg9Uu0Rm_68Trp2cGEEquWC47buBHmrud-QMXrHO40BBz9T-GylAFfCcX4qWiPLGqDLyMC1iFNobuhsybfT-E/?imgmax=800" height="318" style="background-image: none; border: 0px currentColor; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="393" /></a> Ready to install?
<br />
<div style="clear: both;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5OE2Uw2lEIVUiFexAtUrB9J2Sd9CA0dBDB3Ztfk29REa_XgSOyjbzpk-sV7KBRJf89xKRtnAFeC1ufeyBMU_ZLS6Q2Xkp3AKxQGr0fUuewoswGgZWufezMH29wMhQMBnC9rLPq7wc-lxX/s1600-h/image%25255B28%25255D.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5OE2Uw2lEIVUiFexAtUrB9J2Sd9CA0dBDB3Ztfk29REa_XgSOyjbzpk-sV7KBRJf89xKRtnAFeC1ufeyBMU_ZLS6Q2Xkp3AKxQGr0fUuewoswGgZWufezMH29wMhQMBnC9rLPq7wc-lxX/s1600-h/image%25255B28%25255D.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5OE2Uw2lEIVUiFexAtUrB9J2Sd9CA0dBDB3Ztfk29REa_XgSOyjbzpk-sV7KBRJf89xKRtnAFeC1ufeyBMU_ZLS6Q2Xkp3AKxQGr0fUuewoswGgZWufezMH29wMhQMBnC9rLPq7wc-lxX/s1600-h/image%25255B28%25255D.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a></div>
<div style="clear: both;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5OE2Uw2lEIVUiFexAtUrB9J2Sd9CA0dBDB3Ztfk29REa_XgSOyjbzpk-sV7KBRJf89xKRtnAFeC1ufeyBMU_ZLS6Q2Xkp3AKxQGr0fUuewoswGgZWufezMH29wMhQMBnC9rLPq7wc-lxX/s1600-h/image%25255B28%25255D.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigpE0clGZYWub9kFqJmg_wu76yvPcEgO2DPDwcFAmt1m7kRo6ZnRsGnQgLDYvwKjimAzOETHckInVaL5O7_L3ERMp8_pNQMF9rJYfCcTYmt8NyJQisiBXOHlr4w0Jcc306anUo0pIlSIda/?imgmax=800" height="321" style="background-image: none; border: 0px currentColor; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="398" /></a></div>
And complete.<br />
<div style="clear: both;">
<br /></div>
<div style="clear: both;">
Now, when I launch OneNote, I have new tab on the ribbon:
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1gqP7UUpkHIBHma1TXTf43MgTG4DQiyqPltCqkgeBfSZ_qhfkm3jZSh-z31FTTdgyzNIHRVWO0P1vGyLhmAOjwt3boJ0jIJiLp3tI9jRqLTlj6Ozy5OpGTPDyVKtgSqaYR79QoTMxXSiX/s1600-h/image%25255B32%25255D.png"><img alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtx-ftRnYP1xWHY2Nzd2xEP3i_pDhM9xONFYc4aDIAWmZgHexIuVQII62C5KFkmxQWdMt26Qnw_7OR2PZfAak7FIPEgabe1S1gJ4wPeb_y1YMzTwUGVSj0ojHd1IW_6GlTJSZqj7SRLqK4/?imgmax=800" height="106" style="background-image: none; border: 0px currentColor; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="464" /></a></div>
<div style="clear: both;">
<br /></div>
<div style="clear: both;">
<br /></div>
<div style="clear: both;">
<br /></div>
<div style="clear: both;">
<br /></div>
<div style="clear: both;">
You can see that you get a lot of language options. Because I only care about SQL, I’ve included that button in the Quick Access Toolbar (circled) so I don’t have to switch over to the NoteHighLight tab to get to it.
Now, you can click on the SQL (or other language of your choice) button and you’ll see a window like this: </div>
</div>
<div style="clear: both;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihtSBj1Q_bGA2zb1oVAezjvtl4M843a2MH681AG6Sn8ABjRenwI3enNl1uHdeRD-orA5hw6QrjqtWQOty4KhjaWJ-H5lOC9AwbseWbhgHs9b56kGTIp1053BglsAFK28WRKASbesjmYk41/s1600-h/image%25255B36%25255D.png"><img align="left" alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3YxQS5U0WiU8ipDijFHQqQkZlSW8F5D4-zfzTB9dEkFJZvwD8dXG5z4bco_IKPhi4snPCPClRMFmoKpgfYs-W7W09mE-DjAHFYOUfNYszTp3wfyfhctuThi6DcrKsdK6J-jByJuUuT4LQ/?imgmax=800" height="421" style="background-image: none; border: 0px currentColor; display: inline; float: left; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="426" /></a>
The Style drop-down allows you to choose the color scheme you’d like to use. I prefer the default colors for SQL.
The Line Number option actually includes the line numbers in the text. I don’t really like that option, as it keeps me from being able to copy and paste my scripts to SSMS. But I can see where it’d be useful, sometimes.
<br />
<div style="clear: both;">
<br />
When you click OK, your color-highlighted text is pasted into OneNote.
Do note that NoteHighLight is a syntax highlighter, not a text formatter: it’ll highlight your code nicely, but it won’t change the text layout at all.
This is pretty much the only option I’ve found that isn’t a Visual Studio (not SSMS) plugin. I’m pleased to have found it, and I’m grateful to the author. I hope it’s useful to you, too.</div>
</div>
Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com20tag:blogger.com,1999:blog-1714283384790610704.post-14772502523623652172012-02-17T11:02:00.001-06:002012-02-17T11:15:24.465-06:00SSIS and MSDB Packages in a Clustered EnvironmentThere seems to be a lot of confusion about SSIS administration in a clustered environment, and I think a lot of it comes down to SQL error messages that occur in a variety of situations.<br />
<div>
<br />
<div>
Take the following, when browsing the MSDB stored package folder when connected to an SSIS instance:</div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBjdH0c_LzdjE6eUNijdK4UkK_FrEi1nulG30ldiLnRtD_wQNrOZ10ergjEbrLpvrMxvwKj9mfp66km8DCQnepTOKlNIteuYYVZwB6-iM6LDVxzKT6PID1VIyI3u6PCU0rpS1TKMaUC_eg/s1600/ssis_err1.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="185" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBjdH0c_LzdjE6eUNijdK4UkK_FrEi1nulG30ldiLnRtD_wQNrOZ10ergjEbrLpvrMxvwKj9mfp66km8DCQnepTOKlNIteuYYVZwB6-iM6LDVxzKT6PID1VIyI3u6PCU0rpS1TKMaUC_eg/s400/ssis_err1.JPG" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><div style="background-color: white; border-bottom-style: none; border-color: initial; border-color: initial; border-image: initial; border-left-style: none; border-right-style: none; border-style: initial; border-top-style: none; border-width: initial; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; line-height: 16px; list-style-type: none; margin-bottom: 1em; margin-top: 1em; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: none;">
The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2008 Books Online.</div>
<div style="background-color: white; border-bottom-style: none; border-color: initial; border-color: initial; border-image: initial; border-left-style: none; border-right-style: none; border-style: initial; border-top-style: none; border-width: initial; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; line-height: 16px; list-style-type: none; margin-bottom: 1em; margin-top: 1em; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: none;">
Login timeout expired<br />
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.<br />
Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)</div>
<div style="background-color: white; border-bottom-style: none; border-color: initial; border-color: initial; border-image: initial; border-left-style: none; border-right-style: none; border-style: initial; border-top-style: none; border-width: initial; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; line-height: 16px; list-style-type: none; margin-bottom: 1em; margin-top: 1em; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: none;">
Login timeout expired<br />
<span style="background-color: white;">A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.</span><span style="background-color: white;">Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Server Native Client 10.0)</span></div>
</td></tr>
</tbody></table>
<div>
<a name='more'></a>This error, in short, says, "I can't access the SQL Server (not SSIS) instance where the MSDB database is stored. Fix that, and then we'll talk."<br />
<br />
Most of the solutions you'll find when searching the web on this error suggest that the problem is that you're using a named instance. If that's the case, the solution is simple: change the <servername> line in the MsDtsSrvr.ini.xml file (@ <?>:\Program Files\Microsoft SQL Server\100\DTS\Binn) from the default of<br />
<div class="codesnippet">
<ServerName>.</ServerName></div>
to<br />
<div class="codesnippet">
<ServerName>.\instancename</ServerName></div>
where instancename is the name of your named instance.<br />
<br />
When you're running in a clustered environment, however, we've got an additional wrinkle, and this is, I think, where a lot of the confusion comes in. <br />
<br />
Since <a href="http://msdn.microsoft.com/en-us/library/ms345193.aspx">SSIS isn't cluster-aware</a>, you connect to the SSIS instance on the individual cluster node (not the SQL cluster name). That node, however, isn't where the MSDB database resides. <br />
<br />
To fix this, we need to edit the MsDtsSrvr.ini.xml file<br />
(@ <?>:\Program Files\Microsoft SQL Server\100\DTS\Binn)<br />
and change the server name line to <u>reflect the name of the <i>SQL cluster</i></u> so that SSIS knows where to look for the MSDB database. Note that the SQL <i>cluster </i>name is not the cluster <i>node </i>(server) name.<br />
<br />
So the change would look something like this:<br />
<div class="codesnippet">
<ServerName>.</ServerName></div>
to<br />
<div class="codesnippet">
<ServerName>sqlclustername</ServerName></div>
<br />
where sqlclustername is the name of your SQL Server cluster. <br />
<br />
If you have a named instance, it would look like this<br />
<div class="codesnippet">
<ServerName>sqlclustername\instancename</ServerName> </div>
<br />
Note that you'll need to restart the SSIS service (on each node) after you update the configuration file.</div>
</div>Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com9tag:blogger.com,1999:blog-1714283384790610704.post-62157073923448689632012-01-23T19:38:00.000-06:002013-09-27T10:39:01.471-05:00SQL Agent job to query multiple SQL Server InstancesSSMS provides a nice way to query many SQL Server instances at one time using the Central Management Server (CMS) functionality. You can right-click on a server registration group and query all of the servers in that that group. Very handy. Unfortunately, there isn't anything that simple built in to the SQL Agent engine, so we have to use something else. Here we'll use powershell to run a series of queries against a CMS group of servers to collect a list of databases and save that list in a central location.<br />
<a name='more'></a><span style="font-size: large;"><br />
</span><br />
<span style="font-size: x-large;">The Script</span><br />
For those of you who are anxious simply to try it out, here's the PowerShell script in its entirety right now:<br />
<pre class="codesnippet"># Get the list of Instances
$instanceNameList = invoke-Sqlcmd -query "SELECT server_name as Name FROM msdb.dbo.sysmanagement_shared_registered_servers_internal RS
join msdb.dbo.sysmanagement_shared_server_groups_internal SG on
RS.server_group_id = SG.server_group_id where sg.name in ('SQL Server 2008','SQL Server 2005')" -serverinstance cmsserver1
# We're looking for CMS groups with specific names ('SQL Server 2008', etc), so you'll need to
# make sure these match what you have in your environment.
$results = @() #Initialze the Array
# Populate the array with instance and DB information
foreach($instanceName in $instanceNameList)
{$sqlversion=invoke-sqlcmd -query "exec sp_server_info 2" -ServerInstance $instanceName.Name
$SQLVersion="{0}" -f $sqlversion.attribute_value
$results += Invoke-Sqlcmd -query "select @@servername as instancename, name as dbname,'$sqlversion' as sqlversion from sys.databases where name not in ('master','model','tempdb','msdb','pubs','northwind')" -ServerInstance $instanceName.Name
}
# Since we're only interested in the latest information, truncate the DBList table
invoke-sqlcmd -query "use dba; truncate table DBList;" -serverinstance cmsserver1
# Write the results of the above query to the DBList table
foreach($db in $results) {
# Since SQL Agent doesn't handle PowerShell variables entirely well, we work around it
# by assigning our variables a little backwards
$instancename="{0}" -f $db.instancename
$dbname="{0}" -f $db.dbname
$SQLVersion="{0}" -f $db.sqlversion
$querytext="insert into dba.dbo.DBList (instancename,sqlversion,dbname) values ( '$instancename', '$sqlversion','$dbname');"
invoke-sqlcmd -query $querytext -serverinstance cmsserver1 }</pre>
<br />
Save this as a PowerShell SQL Agent job, and you'll be part-way there. You also will need to make sure that the SQL Agent service account has access to the servers you want to query. Finally, you'll need a table somewhere to store this information<br />
<br />
The table DBLIST looks like this referenced in the above query looks like this:<br />
<pre class="codesnippet">CREATE TABLE DBLIST (
<span class="Apple-tab-span" style="white-space: pre;"> </span>instancename VARCHAR(50)
<span class="Apple-tab-span" style="white-space: pre;"> </span>,dbname VARCHAR(200)
<span class="Apple-tab-span" style="white-space: pre;"> </span>,sqlversion VARCHAR(50)
<span class="Apple-tab-span" style="white-space: pre;"> </span>)</pre>
Of course, you can modify this to meet your needs.<br />
<br />
<br />
Now we'll dissect this a bit, so you can make changes to fit your environment.<br />
<br />
<span style="font-size: x-large;">Get a list of the SQL Server instances</span><br />
First we'll query the Central Management Server to get a list of the registered SQL Servers:<br />
<pre class="codesnippet">$instanceNameList = invoke-Sqlcmd -query "SELECT server_name as Name
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal RS join msdb.dbo.sysmanagement_shared_server_groups_internal SG
on RS.server_group_id = SG.server_group_id
where sg.name in ('SQL Server 2008','SQL Server 2005')" -serverinstance cmsserver1 </pre>
Here we create an array named instanceNameList that simply contains the names of all of the servers registered in the CMS. Note that these are stored in the MSDB database.<br />
<br />
You'll need to change the -serverinstance switch to point to your CMS server.<br />
<br />
We're using the invoke-sqlcmd cmdlet to run our queries; our later queries use syntax that isn't available on SQL Server 2000. Because of this, we're limiting our queries to server groups we've defined named "SQL Server 2008" and "SQL Server 2005". You'll need to change these values, as well, to match your environment and needs. Depending on what your environment looks like, you might not even need that where clause at all.<br />
<br />
As an aside, note that this is how you can get at the data in the CMS registration: query msdb.dbo.sysmanagement_shared_server_groups_internal.<br />
<br />
<span style="font-size: x-large;">Query the instances one at a time</span><br />
<br />
Now we'll initialize our results array ($results) and iterate through all of the instance names in our $InstanceNameList array. <br />
<br />
Because we want the SQL Server version text (SQL Server 2008, etc.) instead of the version number, we have to do a little more work, utilizing the sp_server_info stored procedure. We first assign that output text to the $sqlversion variable. The sp_server_info SP outputs more columns than we want, however, so in the next line we re-assign the $SQLVersion variable to contain only the data in the "attribute_value" column.<br />
Finally, we run our main query, including the $sqlversion variable as a static selection so that it is a third column in each of the rows.<br />
<pre class="codesnippet">$results = @()
foreach($instanceName in $instanceNameList)
{$sqlversion=invoke-sqlcmd -query "exec sp_server_info 2" -ServerInstance $instanceName.Name
$SQLVersion="{0}" -f $sqlversion.attribute_value
$results += Invoke-Sqlcmd -query "select @@servername as instancename, name as dbname,'$sqlversion' as sqlversion from sys.databases where name not in ('master','model','tempdb','msdb','pubs','northwind')" -ServerInstance $instanceName.Name
} </pre>
<br />
When this is complete, you'll have something like this:<br />
<br />
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 90%;"><colgroup> <col style="mso-width-alt: 3766; mso-width-source: userset; width: 77pt;" width="103"></col> <col style="mso-width-alt: 3401; mso-width-source: userset; width: 70pt;" width="93"></col> <col style="mso-width-alt: 8777; mso-width-source: userset; width: 200pt;" width="300"></col> </colgroup><tbody>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt; width: 77pt;" width="103"><b><u>instancename</u></b></td> <td class="xl65" style="width: 70pt;" width="93"><b><u>dbname</u></b></td> <td class="xl65" style="width: 250pt;" width="300"><b><u>SQLversion</u></b></td> </tr>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt;">SQLSRV1</td> <td class="xl65">ProdDB1</td> <td class="xl65">Microsoft SQL Server 2008 - 10.0.4064</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt;">SQLSRV1</td> <td class="xl65">ProdDB2</td> <td class="xl65">Microsoft SQL Server 2008 - 10.0.4064</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt;">SQLSRV1</td> <td class="xl65">ProdDB3</td> <td class="xl65">Microsoft SQL Server 2008 - 10.0.4064</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt;">SQLSRV2</td> <td class="xl65">DBA</td> <td class="xl65">Microsoft SQL Server 2008 R2 - 10.50.2500.0</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt;">SQLSRV2</td> <td class="xl65">TestDB1</td> <td class="xl65">Microsoft SQL Server 2008 R2 - 10.50.2500.0</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt;">SQLSRV2</td> <td class="xl65">TestDB2</td> <td class="xl65">Microsoft SQL Server 2008 R2 - 10.50.2500.0</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt;">SQLSRV3</td> <td class="xl65">CalendarDB</td> <td class="xl65">Microsoft SQL Server Yukon - 9.00.5000</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt;">SQLSRV4</td> <td class="xl65">WebDB1</td> <td class="xl65">Microsoft SQL Server Yukon - 9.00.5000</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt;">SQLSRV4</td> <td class="xl65">WebDB2</td> <td class="xl65">Microsoft SQL Server Yukon - 9.00.5000</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td class="xl65" height="20" style="height: 15.0pt;">SQLSRV4</td> <td class="xl65">WebDB3</td> <td class="xl65">Microsoft SQL Server Yukon - 9.00.5000</td> </tr>
</tbody></table>
<br />
Note that sp_server_info returns "Microsoft SQL Server Yukon" for SQL Server 2005 instances. Curious choice, that.<br />
<br />
<span style="font-size: x-large;">Write the results to the reporting table</span><br />
Now we'll iterate through all of the rows in the $results array. Normally we'd do something like this to generate our query text:<br />
<pre class="codesnippet">$querytext="insert into dba.db.dblist (instancename, sqlversion,dbname)
values ('$($db.instancename)','$($db.sqlversion)','$($db.dbname)');"</pre>
<br />
Unfortunately, the SQL Agent engine, for whatever reason, sees this syntax as an error, and it won't run, even though it runs fine from a SQLPS command prompt.<br />
Instead, we assign the relevant values to new variables , and it all works OK. Those "{0}" are formatting codes, BTW. You can see a discussion of how that works <a href="http://technet.microsoft.com/en-us/library/ee692795.aspx">here</a>.<br />
<pre class="codesnippet">foreach($db in $results) {
$instancename="{0}" -f $db.instancename
$dbname="{0}" -f $db.dbname
$SQLVersion="{0}" -f $db.sqlversion
$querytext="insert into dba.dbo.DBList (instancename,sqlversion,dbname) values ( '$instancename', '$sqlversion','$dbname');"
invoke-sqlcmd -query $querytext -serverinstance cmsserver1 }</pre>
Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com3tag:blogger.com,1999:blog-1714283384790610704.post-30771906006493301212011-12-22T09:44:00.002-06:002012-05-24T13:42:58.563-05:00SQL Formatting within SSMSI don't think I'm alone in having wished time and again for a good SQL formatting option from within SSMS. I've posted about <a href="http://lanestechblog.blogspot.com/2011/03/formatting-sql-server-tsql-with-oracles.html">using Oracle's SQL Developer as a decent SQL formatting tool</a>, but that's a second-rate option, given that it doesn't understand all of TSQL's unique syntax.<br />
<br />
I stumbled across a better option yesterday, one that (a) is free and (b) integrates nicely within SSMS.<br />
<br />
<a name='more'></a><br />
<span id="fullpost">First, the link: <a href="http://architectshack.com/PoorMansTSqlFormatter.ashx">http://architectshack.com/PoorMansTSqlFormatter.ashx</a>: "Poor Man's T-SQL Formatter."<br />
You'll find decent instructions for installing it on the site; the documentation is pretty good.</span><br />
[Update] This now is included within NPP's plugin manager, so installation is a snap!<br />
<br />
If you're using <a href="http://www.ssmstoolspack.com/">SSMS Tools</a>, you'll find that the SSMS Tools find function is mapped to the same default keystrokes as for the PoorSQL formatter. I ended up re-mapping the PoorSQL formatter to use another keystroke combination (CTRL+K, CTRL+Shift+F).<br />
<br />
I'm a fan: this integrates nicely into SSMS, it's free, and it works quite well. Check it out.<br />Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com0tag:blogger.com,1999:blog-1714283384790610704.post-48499292434077922862011-08-17T18:38:00.013-05:002012-03-22T14:24:35.488-05:00Estimate recent activity in a SQL Server DatabaseIt's a common problem SQL Server (especially) DBAs have: how do I find out when the last time this database was used? SQL Server is so prone to sprawl, and so many applications install so many databases; it's hard to keep track of what is in use and what is not. This is especially true when you're new on the job. <br />
<br />
Here we'll look at a quick-and-dirty method for getting a guess at whether a database has been used. This comes with a lot of caveats and cautions, but if you're looking for <span style="font-style: italic;">some kind</span> of evidence that a system has been used, index usage stats are one place to look.<br />
<br />
<a name='more'></a><br />
A little background is probably in order. Indexes are used to help the database engine find data. They organize (and sometimes order) the data within the database so that when you ask for something, the engine has an idea as to where it might be. That helps speed things up. The database engine keeps track of some information about the indexes in the database: last user seek, scan, and update, among other things. We can use this information to see if any indexes have been utilized in the recent past.<br />
<br />
You can see one of the problems you might run into with using this method immediately: what if there aren't any indexes to use? Then it won't work. True, too, if a user is accessing data that isn't indexed. Another problem is that index usage stats aren't persisted across SQL Server reboots, so no usage will show up that was prior to the last time SQL Server was restarted.<br />
<br />
So you see, it's not a perfect solution, but it might give you some confirmation of activity (or the lack thereof), instead of just relying on your gut.<br />
<br />
So, to the script: <br />
<br />
<br />
<div class="code">
<br />
declare @dbname varchar (100),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>@sql varchar (120)<br />
set @dbname='<DBNAME>' -- Change this to match the database you want to check.<br />
set @sql ='use ['+@dbname+'];'<br />
exec (@sql)<br />
select object_name(ius.object_id) as table_name<br />
, ind.name as index_name<br />
, obj.type_desc<br />
, last_user_seek<br />
, last_user_scan<br />
, last_user_update<br />
from sys.indexes ind<br />
join sys.objects obj on ind.object_id=obj.object_id<br />
left join sys.dm_db_index_usage_stats ius on ind.index_id=ius.index_id<br />
where database_id = (DB_ID(@dbname))<br />
and obj.is_ms_shipped=0<br />
order by last_user_seek DESC,<br />
last_user_scan DESC,<br />
last_user_update DESC</div>
<br />
In short, what we're doing is listing the index usage stats from the dm_db_index_usage_stats DMV. If the last user seek, scan, and update rows are all NULL for the indexes in the database, then those indexes have not been used by a user session. That would suggest, if it's a well-indexed database, that the database has not been used since SQL Server was started.<br />
<br />
<br />
On the other hand, if those columns do have dates in them, then you know that at least on that date, a user was querying or modifying indexed data, and you have confirmation that it is in use.Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com0tag:blogger.com,1999:blog-1714283384790610704.post-66614778458025181302011-04-04T11:34:00.018-05:002014-10-09T12:21:05.743-05:00Keeping a Minimum Number of SQL Server Backups Online using a SQL Agent Job<br />
<h3 0px="0px" 22px="22px" alatino="alatino" class="post-title entry-title" font-family:="font-family:" font-size:="font-size:" georgia="georgia" itemprop="name" linotype="linotype" margin:="margin:" palatino="palatino" position:="position:" relative="relative" serif="serif" utopia="utopia">
Keeping a Minimum Number of SQL Server Backups Online using a SQL Agent Job</h3>
<div 0px="0px" 1.6="1.6" 13px="13px" 1em="1em" alatino="alatino" class="post-header" font-family:="font-family:" font-size:="font-size:" georgia="georgia" line-height:="line-height:" linotype="linotype" margin:="margin:" palatino="palatino" serif="serif" utopia="utopia">
<div class="post-header-line-1">
</div>
</div>
<div 13px="13px" 18px="18px" 586px="586px" alatino="alatino" class="post-body entry-content" font-family:="font-family:" font-size:="font-size:" georgia="georgia" id="post-body-6661477845802518130" itemprop="articleBody" line-height:="line-height:" linotype="linotype" palatino="palatino" position:="position:" relative="relative" serif="serif" utopia="utopia" width:="width:">
First, a plug: there's a great "maintenance solution" that is a collection of stored procedures available at <a href="http://ola.hallengren.com/" style="color: #444444; text-decoration: none;">http://ola.hallengren.com/</a>. This is the basis for a lot of my database maintenance jobs. If you haven’t taken a look at this, I highly recommend that you do; it’s free, under active maintenance, robust, and easy to implement.<br />
When you run the script from that site, you get a variety of stored procedures and you can have it create SQL Agent jobs for you, as well. I’d recommend doing that, as it can give you a good idea of how the stored procedures work.<br />
<h2 style="border-bottom-color: rgb(0, 0, 0); border-bottom-style: solid; border-bottom-width: 1px; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14px; line-height: normal; margin: 0px -15px; padding: 0.6em 15px 0.5em; position: relative;">
<a href="http://www.blogger.com/blogger.g?blogID=1714283384790610704" name="SQLServerBackupScript-Thebackupjob" style="color: #444444; text-decoration: none;"></a>The backup job</h2>
When we perform a backup in a SQL Server instance, we want to perform a number of tasks:<br />
<ol>
<li style="margin: 0px 0px 0.25em; padding: 0px;">Delete old backups</li>
<li style="margin: 0px 0px 0.25em; padding: 0px;">Backup all of the current databases</li>
<li style="margin: 0px 0px 0.25em; padding: 0px;">Zip up the backup files</li>
<li style="margin: 0px 0px 0.25em; padding: 0px;">Copy the zipped backup file(s) to a share on a backup server<a href="http://www.blogger.com/blogger.g?blogID=1714283384790610704" name="more"></a></li>
</ol>
SQL Server (using xp_delete_file) provides a pretty simple method for deleting files that are older than a certain retention window (time-based retention). But time-based retention presents a problem: what if your backups haven’t been running for awhile? The next time your backup job runs, the older backup files (which are all of them) get deleted. Worse: what if there was something wrong with the most recent backup? Suddenly you don’t have any backups online anymore.<br />
I prefer a retention policy based on redundancy: I want to keep at least <em>n</em> copies online at all times, regardless of how old they are. It’s true that a combination of the two policies would be the best-case scenario: keep five days’ worth of backups online, and make sure that we never have fewer than five backup files available at any given time. This would allow us to, for instance, have five backups run in a single day without deleting the older backups that we also want to have available.<br />
But I’ve gone for the simpler route in this case: I want five backup files online at all times. SQL Server doesn’t give us an easy built-in way to do this, so we’ll turn to PowerShell for our process.<br />
<br />
<h2 style="border-bottom-color: rgb(0, 0, 0); border-bottom-style: solid; border-bottom-width: 1px; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14px; line-height: normal; margin: 0px -15px; padding: 0.6em 15px 0.5em; position: relative;">
Delete old backups</h2>
Our first step in the job is to delete the backups that aren't used anymore. In subsequent steps, we have turned off the archive bit on the files we do not need anymore, so we simply delete the files that don't have the archive bit set. The PowerShell script is below:<br />
<br />
<div class="codesnippet" style="background-color: white; border-style: dashed; border-width: 1px; overflow: auto; padding: 8px;">
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
$backup_dir="path:\to\Backup\dir"</div>
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
$files=get-childitem -path $backup_dir</div>
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
# we'll delete all files that don't have the archive bit set</div>
<span style="font-family: consolas, courier, monospace;"><span style="white-space: nowrap;"><br /></span></span>
<span style="font-family: consolas, courier, monospace;"><span style="white-space: nowrap;">if ( $backup -ne $null ) # only do the delete if the backup dir exists</span></span><br />
<span style="font-family: consolas, courier, monospace;"><span style="white-space: nowrap;">{</span></span><br />
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
Foreach($file in $files)</div>
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
{ If((Get-ItemProperty -Path $file.fullname).attributes -band [io.fileattributes]::archive)</div>
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
{ Write-output "$file is set to be retained" }</div>
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
ELSE {</div>
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
Write-output "$file does not have the archive bit set. Deleting."</div>
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
remove-item -recurse $file.fullname</div>
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
$output =$_.ErrorDetails } }</div>
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
#end Foreach</div>
<div style="color: black; font-family: consolas, courier, monospace; white-space: nowrap;">
} #End If </div>
</div>
Note that the $BACKUP_DIR variable needs to be set to the correct directory for the backups.<br />
<em>Any</em> file or directory in the backup directory that does not have the archive bit set will be removed. Do pay attention to this fact. You can put a file mask in the get-childitem cmdlet call to modify that behavior, if you choose.<br />
<h2 style="border-bottom-color: rgb(0, 0, 0); border-bottom-style: solid; border-bottom-width: 1px; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14px; line-height: normal; margin: 0px -15px; padding: 0.6em 15px 0.5em; position: relative;">
<a href="http://www.blogger.com/blogger.g?blogID=1714283384790610704" name="SQLServerBackupScript-RunDatabaseBackup" style="color: #444444; text-decoration: none;"></a>Run DatabaseBackup</h2>
DatabaseBackup is the name of the stored procedure (in the master DB) that backs up each of the databases on the instance. It is installed as a part of the maintenance solution referenced at the beginning of the page. Usage is as follows:<br />
<div class="codesnippet" style="background-color: white; border-style: dashed; border-width: 1px; color: black; font-family: consolas, courier, monospace; overflow: auto; padding: 8px; white-space: nowrap;">
EXECUTE [dbo].[DatabaseBackup]<br />
@Databases = 'USER_DATABASES',<br />
@Directory = @backup_dir,<br />
@BackupType = 'FULL',<br />
@Verify = 'Y',<br />
@CleanupTime = 24,<br />
@CheckSum = 'Y'</div>
@Databases can be one of:<br />
<ul style="line-height: 1.4; list-style-image: initial; list-style-position: initial; margin: 0.5em 0px; padding: 0px 2.5em;">
<li style="border: none; margin: 0px 0px 0.25em; padding: 0.25em 0px;">'USER_DATABASES' backs up all user databases</li>
<li style="border: none; margin: 0px 0px 0.25em; padding: 0.25em 0px;">'SYSTEM_DATABASES' backs up all system databases (master, model, msdb)</li>
</ul>
@BackupType can be one of<br />
<ul style="line-height: 1.4; list-style-image: initial; list-style-position: initial; margin: 0.5em 0px; padding: 0px 2.5em;">
<li style="border: none; margin: 0px 0px 0.25em; padding: 0.25em 0px;">'FULL' performs a full backup of the database data files</li>
<li style="border: none; margin: 0px 0px 0.25em; padding: 0.25em 0px;">'LOG' backs up the transaction log files</li>
<li style="border: none; margin: 0px 0px 0.25em; padding: 0.25em 0px;">'DIFF' creates a differential backup from the last full backup</li>
</ul>
So here’s what our next step looks like. It’s a T-SQL step:<br />
<div class="codesnippet" style="background-color: white; border-style: dashed; border-width: 1px; color: black; font-family: consolas, courier, monospace; overflow: auto; padding: 8px; white-space: nowrap;">
-- change the backup directory/drive appropriately<br />
declare @backup_dir varchar(100) ='path:\to\backup\dir'<br />
EXECUTE [dbo].[DatabaseBackup]<br />
@Databases = 'SYSTEM_DATABASES',<br />
@Directory = @backup_dir,<br />
@BackupType = 'FULL',<br />
@Verify = 'Y'<br />
<br />
EXECUTE [dbo].[DatabaseBackup]<br />
@Databases = 'USER_DATABASES',<br />
@Directory = @backup_dir,<br />
@BackupType = 'FULL',<br />
@Verify = 'Y'<br />
<br />
EXECUTE [dbo].[DatabaseBackup]<br />
@Databases = 'USER_DATABASES',<br />
@Directory = @backup_dir,<br />
@BackupType = 'LOG',<br />
@Verify = 'Y'</div>
<br />
The verify switch is quite nice: after each backup, it runs a 'RESTORE VERIFYONLY FROM DISK=..." to ensure that each file is recoverable.<br />
<br />
<i>Note: the DatabaseBackup stored procedure appends \computername to the backup directory by default, but if you're using a named instance, it uses \computername$instancename, instead. This makes sense, because you can have multiple instances on a server. If you're using a named instance, you'll need to account for this in the scripts below.</i><br />
Much thanks to Porro for pointing this out in the comments below!<br />
<br />
Basically, we’re backing up all of the system databases, all of the user databases, and then the t-log files from all of the user databases, and we’re saving those backups to a directory structure at the @backup_dir variable location we specified at the beginning.<br />
Note that this stored procedure puts its files in directory structure starting in the @backup_dir. The start of this structure is the server name, with directories under it for each database.<br />
<h2 style="border-bottom-color: rgb(0, 0, 0); border-bottom-style: solid; border-bottom-width: 1px; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14px; line-height: normal; margin: 0px -15px; padding: 0.6em 15px 0.5em; position: relative;">
<a href="http://www.blogger.com/blogger.g?blogID=1714283384790610704" name="SQLServerBackupScript-Zipthebackupfiles" style="color: #444444; text-decoration: none;"></a>Zip the backup files</h2>
We don’t want to keep the uncompressed backups online all the time, so we’ll compress them using 7-Zip.<br />
This PowerShell script is more complicated, so we'll go through it in some more detail. Note that this needs the 7zip executable (and associated .dll). This script will look for it in the c:\utils directory. Note that you can copy just the 7z.exe and 7z.dll to a directory; you don’t have to install the entire package in order to use the 7-Zip command line.<br />
First, here's the whole of our PowerShell script:<br />
<div class="codesnippet" style="background-color: white; border-style: dashed; border-width: 1px; color: black; font-family: consolas, courier, monospace; overflow: auto; padding: 8px; white-space: nowrap;">
$backup_dir="path:\to\backup\dir"<br />
$day= get-date -format "yyyyMMdd_HHmm"<br />
# Turn on the archive bit on the current backups directory<br />
# (so it won't get deleted at the next run if the zip process fails)<br />
attrib $backup_dir\$env:computername +a<br />
<br />
# Zip up the current backup(s)<br />
# destination for the zip file is $backup_dir\SQLBACKUP-<servername>-DATE_TIME.zip<br />
C:\utils\7z.exe -tzip -mx1 a $backup_dir\SQLBACKUP-$env:computername-$day.zip $backup_dir\$env:computername<br />
<br />
# if 7zip succeeded, we'll continue<br />
if ($LASTEXITCODE -gt 0)<br />
{Throw "7Zip failed" }<br />
ELSE {<br />
# When the zip is complete, turn off the archive bit on the current backup directory<br />
attrib $backup_dir\$env:computername -a<br />
<br />
# Now let's change the archive bit, such that only<br />
# the last five zipped backups will be kept online<br />
$delfiles=0<br />
$delfiles= (dir $backup_dir\SQLBACKUP*.zip).count-5<br />
if ($delfiles -gt 0)<br />
<br />
# If there are more than 5 zipped backups, we'll turn off the archive bit on them<br />
{dir $backup_dir\SQLBACKUP* | sort-object -property {$_.CreationTime} |<br />
select-object -first $delfiles |<br />
foreach-object { attrib $_.FULLNAME -A} }}</div>
So. The first line sets the backup directory to use. Next, we set a variable to hold today's date and time to use in creating the zip file.<br />
Next we turn on the archive bit for the directory created during the previous step, and then we run 7z.exe to create the zip file. All pretty straightforward up to this point, though do note that we’re using the –mx1 switch in 7Zip. This is important because 7Zip is optimized for compression, not for speed. Using the –mx1 switch tells 7zip to use its fastest (and least CPU-intensive) compression routines. Especially for large files, this is really important.<br />
Our next step is to check to make sure that 7zip succeeded. We do that with the $LASTEXITCODE variable:<br />
<div class="codesnippet" style="background-color: white; border-style: dashed; border-width: 1px; color: black; font-family: consolas, courier, monospace; overflow: auto; padding: 8px; white-space: nowrap;">
if ($LASTEXITCODE -gt 0) {Throw "7Zip failed" }</div>
This says, if 7Zip failed (returning an error code that is greater than zero), end (throw) with failure text "7Zip failed".<br />
If the exit code is zero, then we know 7Zip succeeded, and we'll continue.<br />
The next step is to turn off the archive bit on the directory we just zipped up; that way it'll be deleted when the job runs next.<br />
We also want to keep the five most recent backups online on the server. We don't want to just delete files that are older than five days, though: if the backup was failing, and there aren't backups from days 1-4, we'd suddenly have lost all of our backups. So we loop through the files and sort them by date. Then, if there are more than five files in the directory, we take the oldest files #6 - <em>n</em> and turn off the archive bit on them. That way, those files will be deleted the next time the job runs.<br />
This is the code that does this (thanks, BTW, to Spiceworks for the <a href="http://community.spiceworks.com/scripts/show/324-keep-latest-x-created-files-and-delete-all-other" style="color: #444444; text-decoration: none;" target="_blank">example script</a> on which this is based.):<br />
<div class="codesnippet" style="background-color: white; border-style: dashed; border-width: 1px; color: black; font-family: consolas, courier, monospace; overflow: auto; padding: 8px; white-space: nowrap;">
$delfiles=0 $delfiles= (dir $backup_dir\SQLBACKUP*.zip).count-5<br />
if ($delfiles -gt 0) # If there are more than 5 zipped backups, we'll turn off the archive bit on them<br />
{dir $backup_dir\SQLBACKUP* | sort-object -property {$_.CreationTime} |<br />
select-object -first $delfiles |<br />
foreach-object { attrib $_.FULLNAME -A} }</div>
What this does is the following:<br />
<ol>
<li style="margin: 0px 0px 0.25em; padding: 0px;">Count the number of .zip files in the backup directory</li>
<li style="margin: 0px 0px 0.25em; padding: 0px;">If the number of files in the backup directory is > 5, then:</li>
<li style="margin: 0px 0px 0.25em; padding: 0px;">Sort the directory (SQLBACKUP*) by creation time (oldest first)</li>
<li style="margin: 0px 0px 0.25em; padding: 0px;">Take the first <em>n</em> files in the sorted list (where <em>n</em> is the number of files that are greater than 5) and turn off the archive attribute on them.</li>
</ol>
<h2 style="border-bottom-color: rgb(0, 0, 0); border-bottom-style: solid; border-bottom-width: 1px; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14px; line-height: normal; margin: 0px -15px; padding: 0.6em 15px 0.5em; position: relative;">
<a href="http://www.blogger.com/blogger.g?blogID=1714283384790610704" name="SQLServerBackupScript-Copyfilestothebackupserver" style="color: #444444; text-decoration: none;"></a>Copy files to the backup server</h2>
Finally we'll copy the files to the backup server:<br />
<div class="codesnippet" style="background-color: white; border-style: dashed; border-width: 1px; color: black; font-family: consolas, courier, monospace; overflow: auto; padding: 8px; white-space: nowrap;">
# Make sure you change the backup directory appropriately<br />
$backup_dir= "path:\to\backup\dir"<br />
$day= get-date -format "yyyyMMdd_"<br />
# This will copy all of today's backups to the backup server<br />
copy-item $backup_dir\SQLBACKUP-$env:computername-$day*.zip \\server\sharename -force</div>
Note that the SQL Agent service account needs to have access to the share in order for this to succeed. Note, too, that this job will copy all files from today, so if there were multiple runs today, all of those files will get copied again (overwritten; that’s the need for the –force switch).<br />
So now we have a backup job that will keep the backups around not based on age but on the number of copies. When you put the scripts above together in a job, the steps look something like this:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOS6Fi7YYmuHvgABp954rkOGsdiLIFlfB1OwoGvrWl8YNFqwncRTKEUu9TRHKNV50r1Aml7umrUy_oL78FbHBxTS9AZ_NWeWFaoVLSWwyFylVSPYVsk_VBmKySOSDhACk3Zo7RIkPWPWBQ/s1600-h/image%5B3%5D.png" style="color: #cccccc;"><img alt="image" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_cwdZojA0N9KUq_joZXYytvX0alfEpcoqgJr1cepsk_muVyp8jni5WFzTZf8We4O_awLzimUPcUTBy67l_mypfUT5ZC0pDGC8VFv9Ef6vg3FYxDSlhPnYhsF8-LrcH64G4NXNjk2aZoaV/?imgmax=800" height="130" style="-webkit-box-shadow: rgba(0, 0, 0, 0.2) 0px 0px 0px; background-color: #222222; background-image: none; background-position: initial initial; background-repeat: initial initial; border-bottom-left-radius: 0px; border-bottom-right-radius: 0px; border-top-left-radius: 0px; border-top-right-radius: 0px; border: 0px; box-shadow: rgba(0, 0, 0, 0.2) 0px 0px 0px; display: inline; padding: 0px 0px 8px; position: relative;" title="image" width="598" /></a></div>
Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com7tag:blogger.com,1999:blog-1714283384790610704.post-66914383197800619612011-03-02T11:01:00.005-06:002012-02-17T19:48:24.688-06:00Formatting SQL Server TSQL with Oracle's SQL DeveloperNote that in the time since I wrote this post, <a href="http://lanestechblog.blogspot.com/2011/12/sql-formatting-withing-ssms.html?m=0">another (much better) option for formatting SQL Server script has come up.</a><br />
If you do much troubleshooting on Microsoft SQL Server, you inevitably will end up having to deal with a poorly-formatted (and hard-to-read) SQL statement from a query using sys.dm_exec_sql_text or the like. <br />
<div>
</div>
<div>
There are lots of online formatters out there, though I've had decidedly mixed results with them. There also are a lot of add-in and standalone products available that will do a good at this. Here's another one to add to your list, until SSMS includes a formatting feature: Oracle's SQL Developer.</div>
<span id="fullpost">SQL Developer is a free download (<a href="http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html">here</a>) that will, in fact, connect to SQL Server instances. While I do not use it for my day-to-day SQL Server administration tasks, I use it regularly to reformat SQL that I've pulled from the DMVs. </span><br />
<br />
<a name='more'></a><br />
<div>
Here's how it looks using the following SQL Agent task SQL.</div>
<div>
<div class="codesnippet">
<span id="fullpost">(@P1 int,@P2 uniqueidentifier,@P3 int)UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()), GetDate()), run_requested_source = CONVERT(sysname, @P1), queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = @P2 and session_id = @P3</span></div>
</div>
<div>
</div>
<div>
<span id="fullpost">First we paste this in to a new page in SQL Developer:</span></div>
<div>
<span id="fullpost"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5579535498058420818" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-mILuS3N_6zPmg2tZsfnBFfyTW_ekwrC7b23gywh387wRzi7i5KTwPZZ47l3DjvsClPDtBOc7vcJnonFPXEq_KjmzVlThpJW8ljnNn4QY5VgY5qIwx73rNaKnFwMc83Wb87JdxokDM-kj/s400/sqldev_format1.PNG" style="cursor: hand; cursor: pointer; margin: 0 10px 10px 0;" /></span></div>
<div>
</div>
<div>
<span id="fullpost">And then we hit CTRL-F7 (or right-click and select "Format"):</span></div>
<div>
<span id="fullpost"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5579536015001531330" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5wYu8bPeg0tkvrYiacGjictx75GbXTm1CKg8ApXDO3yViZFv1haIum0Lns9WxazAKHaCHvU9L8TcdlrcDnbmIpIvoheI38L6BFbQ84c68SVoZf-M_YsWEu-bb7EtyaWWB6Ec-6QsKulVw/s400/sqldev_format2.png" style="cursor: hand; cursor: pointer; height: 400px; width: 364px;" /></span></div>
<div>
</div>
<div>
<span id="fullpost">Which gives us very nicely formatted SQL. One gotcha here: SQL Developer doesn't know what to do with the 'GO' statement, so it puts it on the same line as other SQL commands. This will keep your code from running, so there's one piece of cleanup that is necessary when using SQL Developer.</span></div>
<div>
</div>
<div>
<span id="fullpost"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5579537205438552562" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2HHpHwI7-QYtMi2surmYJ8_d_fN6CX6BRBQuucibuy1EEbEmlqkOBwNFWQSgd-xQeVQXcbXNc5LqSlq60UVfbOZjM9cOcf68tDSLK5NT_uIpCc5A8dBu3Jl55TcCahzSRHx0CSxu9hBU8/s400/sqldev_format3.PNG" style="cursor: hand; cursor: pointer; height: 322px; width: 400px;" /></span></div>Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com2tag:blogger.com,1999:blog-1714283384790610704.post-3948056073065438162011-02-25T10:56:00.004-06:002013-04-10T08:52:29.992-05:00How to Change the Owner of All SQL Agent Jobs in a SQL Server InstanceEach job in a SQL Server instance has an owner, and you may run into a situation in which that owner needs to be changed. <br />
If there are a lot of jobs that were created by that owner, this can be a tedious task. <br />
Here we’re opening a cursor and looping through the SQL Agent Jobs in the instance that are owned by the old user (@olduser) and executing the sp_update_job stored procedure to change that to match @newuser.<br />
<div class="codesnippet">
USE MSDB<br />
GO<br />
declare @jobname varchar (200)
declare @oldusername varchar (30)
declare @newusername varchar(30)
set @oldusername='DOMAIN\oldusername'
set @newusername='DOMAIN\newusername'
declare cur_jobname cursor LOCAL
for select name from sysjobs
where suser_sname(sysjobs.owner_sid) =@oldusername
open cur_jobname
fetch next from cur_jobname
into @jobname
While @@FETCH_STATUS = 0
begin<br />
EXEC msdb.dbo.sp_update_job @job_name=@jobname,
@owner_login_name=@newusername
fetch next from cur_jobname
into @jobname
end
close cur_jobname
deallocate cur_jobname</div>
Lanehttp://www.blogger.com/profile/05548461086853951131noreply@blogger.com2