PowerShell Get-WinEvent Bug Workaround on Windows 2008 R2 Server — Importing Windows Forwarded Events into SQL Server using PowerShell
This is sort of a continuation of my earlier post, Importing Windows Forwarded Events into SQL Server using PowerShell, where I mentioned that I was unable to get the script to work on Windows 2008 R2 due to a known bug in Get-WinEvents. I had to end up deploying my solution to a Windows 2008 R2 Server and was required to write a workaround -- here it is. As always, I prefer using natively available commands, so I eschewed LogParser and used wevtutil.exe instead.
# Grab events from the last 65 minutes
[xml]$xml = (wevtutil /r:dc qe Application /e:Events)
# build the sql data connection
$connectionString = "Data Source=SQLSERVER;Integrated Security=true;Initial Catalog=EventCollections;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "Events"
#/q:"*[System[TimeCreated[timediff(@SystemTime) <= 3900000]]]"
# build the datatable
$dt = New-Object "System.Data.DataTable"
$null = $dt.Columns.Add("ID")
$null = $dt.Columns.Add("LevelDisplayName")
$null = $dt.Columns.Add("LogName")
$null = $dt.Columns.Add("MachineName")
$null = $dt.Columns.Add("Message")
$null = $dt.Columns.Add("ProviderName")
$null = $dt.Columns.Add("RecordID")
$null = $dt.Columns.Add("TaskDisplayName")
$null = $dt.Columns.Add("TimeCreated")
# populate data table
$xml.Events.Event | ForEach-Object {
$row = $dt.NewRow()
$eventID = $_.System.EventID."#text"
if (!$eventID) { $eventID = $_.System.EventID }
$row.Item("ID") = $eventID
$eventlevel = $_.System.Level
switch ($eventlevel)
{
1 {$eventLevel = "Critical"}
2 {$eventLevel = "Error"}
3 {$eventLevel = "Warning"}
4 {$eventLevel = "Information"}
}
$row.Item("LevelDisplayName") = $eventLevel
$row.Item("LogName") = $_.System.Channel
$row.Item("MachineName") = $_.System.Computer
$row.Item("Message") = $_.RenderingInfo.Message
$row.Item("ProviderName") = $_.System.Provider.Name
$row.Item("RecordID") = $_.System.EventRecordID
$row.Item("TaskDisplayName") = $_.RenderingInfo.Task
$row.Item("TimeCreated") = [datetime]$_.System.TimeCreated.SystemTime
$dt.Rows.Add($row)
}
# Write to the database!
$bulkCopy.WriteToServer($dt)
This code imports events from the last 65 minutes. For the initial import, set $xml to wevtutil.exe qe ForwardedEvents /e:Events. As an aside, I was surprised to see that wevtutil is FAR faster than PowerShell's Get-WinEvent, especially during the initial import of a large logs.
PS C:\Scripts> Measure-Command {c:\scripts\final-getwinevent.ps1}
Days : 0
Hours : 0
Minutes : 1
Seconds : 19
Milliseconds : 293
Ticks : 792930218
TotalDays : 0.00091774330787037
TotalHours : 0.0220258393888889
TotalMinutes : 1.32155036333333
TotalSeconds : 79.2930218
TotalMilliseconds : 79293.0218
PS C:\Scripts> Measure-Command {c:\scripts\final-wevtutil.ps1}
Days : 0
Hours : 0
Minutes : 0
Seconds : 4
Milliseconds : 957
Ticks : 49571333
TotalDays : 5.73742280092593E-05
TotalHours : 0.00137698147222222
TotalMinutes : 0.0826188883333333
TotalSeconds : 4.9571333
TotalMilliseconds : 4957.1333
From 79 seconds to 5 for 5500 records! Looks like having to rewrite this was a good thing, after all.
Quick and Dirty: Backup SQL Server Express Instances on Enterprise Networks
I know the ideal Enterprise network won't have any SQL Server Express instances, but every large network that I've ever worked on has at least a few. Many times, it's powering apps like SolarWinds or BackupExec. Since SQL Express doesn't run SQL Agent, it can be a pain to backup and maintain the systems.
There are a lot of blog posts out there that use PowerShell or some other scheduled script to perform the backups, but it occurred to me the other day that I can totally run all of my backup and maintenance scripts from another SQL Server's SQL Agent. Duh! The key is to use the Connections tab to connect to other SQL instances.
I'll be honest, because these servers may come and go and their databases are unpredictable, I don't want to install anything local like Ola Hallengren's Backup and Maintenance Scripts, so I setup simple Maintenance Plans with the following parameters:
- I made one plan with multiple subplans.
- Weekly: Full backup, integrity checks, index rebuild, Maintenance cleanup task (4 weeks), History cleanup task (4 weeks)
- Daily: Differential, Maintenance cleanup task (4 weeks)
- Staggered schedules between server backups -- I don't expect SQL Express databases to take any longer than 30 minutes, so every instance backup is scheduled 30 minutes apart.
- The backups go to the same network share as the rest of the SQL instances which kinda look like this: \\sqlbackupserver\backups\HOSTNAME\full, \\sqlbackupserver\backups\HOSTNAME\diff.
- No transaction log backups are scheduled -- so far, I haven't found a SQL Express instance with databases that are set to anything but the SIMPLE recovery model.
Make sure the SQL Server Agent account has adequate access to the SQL Server Express, and that you don't check "Compress backups." Otherwise, your backups will fail with a Backup Error: 3041, Severity: 16, State: 1 error code as SQL Express doesn't support compressed backups.
Safely Enable SQL Server Agent MultiServer Administration using PowerShell
Update: You can't even independently schedule slave jobs. Count my organization as yet another that won't be implementing MultiServer Administration. Grrr.
I always forget about Multiserver Administration. I've actually never worked in an environment that uses it, even though it seems to have a lot of potential. I think one of the biggest reasons is that most organizations do not use SSL encryption between SQL Servers, yet out of the box, Multiserver Administration requires SSL encryption for communication between the master and the targets.
Want to change this option? You'll have to modify the registry. Come on, Microsoft: nobody wants to touch a production SQL Server's registry and I think this is the biggest roadblock to mass adoption of Multiserver Administration.
The registry subkey that needs to be changed is MsxEncryptChannelOptions. There isn't a whole lot of information about this subkey (such as what other services it impacts) but I'm hoping that since its prefixed with "Msx" that and sits in the SQLAgent key, the change will be isolated to Multiserver Administration. So here are the 3 options:
| 0 | Disables encryption between this target server and the master server. Choose this option only when the channel between the target server and master server is secured by another means. |
| 1 | Enables encryption only between this target server and the master server, but no certificate validation is required. |
| 2 | Enables full SSL encryption and certificate validation between this target server and the master server. This setting is the default. |
Like the table says, 2 (Encryption+SSL required) is the default. Most blogs I've seen change their option to 0 (No encryption), but I tested it with 1 (Encryption enabled+ SSL not required) and default out of the box SQL encryption settings and it worked. Microsoft says this about the default encryption:
Credentials (in the login packet) that are transmitted when a client application connects to SQL Server are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials.
I always prefer encryption if it's not disruptive, so this is the setting I will recommend, and the setting that is default in the script below. This script asks for the SQL Server version (SQL2k5 is not supported because I no longer use it and instance paths are more challenging.)
Write-Host "***** Set SQL Agent Encryption Options on Target Servers *****`n "
# Menu for SQL Server Version. SQL Server 2005 could work in theory, but
# it's registry values are unpredictable and I didn't want to mess.
[int]$menuChoice = 0
while ( $menuChoice -lt 1 -or $menuChoice -gt 3 ){
Write-host "1. SQL Server 2008"
Write-host "2. SQL Server 2008 R2"
Write-host "3. SQL Server 2012"
[Int]$menuChoice = read-host "Select your SQL Server version" }
Switch( $menuChoice ){
1{$SQLVersion = "10"}
2{$SQLVersion = "10_50"}
3{$SQLVersion = "11"}
default{$SQLVersion = "10_50"}
}
# Enter the name of your SQL Server
Write-Host "Enter the hostname of SQL Server (do not include instance name)"
$ServerName = Read-Host "If you are using a cluster, enter the individual node name"
$ServerName = $ServerName.ToUpper()
# And the instance
$Instance = Read-Host "Enter Instance Name (leave blank for default)"
if (!$Instance) {$Instance = "MSSQLSERVER" }
$Instance = $Instance.ToUpper()
Write-Host "`nOptions for Encryption`n"
[int]$menuChoice = -1
while ( $menuChoice -lt 0 -or $menuChoice -gt 2 ){
Write-host "0. Disables encryption between this target server and the master server."
Write-host "1. Enables encryption only between this target server and the master server, but no certificate validation is required."
Write-host "2. Enables full SSL encryption and certificate validation between this target server and the master server. "
[Int]$menuChoice = read-host "Select Encryption Option for SQL Agent Master/Target Communication" }
Switch( $menuChoice ){
0{$EncryptionOption = "0"}
1{$EncryptionOption = "1"}
2{$EncryptionOption = "2"}
default{$EncryptionOption = "1"}
}
$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LocalMachine", $ServerName)
$regKey= $reg.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL$SQLVersion.$Instance\SQLServerAgent",$true)
if ($regkey -ne $null) {
$oldValue = $regKey.GetValue("MsxEncryptChannelOptions")
$regKey.SetValue("MsxEncryptChannelOptions","0000000$EncryptionOption",[Microsoft.Win32.RegistryValueKind]::DWORD)
Write-Host "Done"
$newValue = $regKey.GetValue("MsxEncryptChannelOptions")
Write-Host "Server: $Servername`nOld value: $oldValue`nNew value: $newValue"
} else
{ "No match. Make sure you typed in the proper hostname and instance name." }
(Thanks to quickclix for the easy PowerShell menu code.)
Once you've run this script and modified the settings on your Target servers, you can easily setup Multiserver Administration. Note that the default setting may create a SQL Server login for the target server automatically. I uncheck that option because I'm trying to get away from local SQL Server logins and all of my SQL Agents run under the same domain account anyway.
Importing Windows Forwarded Events into SQL Server using PowerShell
Over the past couple weeks, I've looked into a number of ways of parsing and importing Windows Forwarded Events into SQL Server: from using SSIS to LogParser to PowerShell to setting up a linked server to the "Forwarding Events.evtx" file.
Ultimately, the only thing that worked was PowerShell's Get-WinEvent cmdlet. And then, it only worked in one specific case for me -- if the events are collected and parsed on a Windows 2012 server. As of today, there's an unresolved bug in Get-WinEvent that often results in NULL LevelDisplayName, Message, and TaskDisplayName columns. I copied the exact code below on a Win2k8 R2 server and a Win 8 workstation and ran into the NULLs issue repeatedly. Your results may vary, however, as some users have reported success by tweaking a few things in Win2k8 R2 Server.
So, fire up a Windows 2012 box, setup your SQL Server and let's get started:
The SQL Part
After looking at the data returned by Get-WinEvent, I found the following columns to be the most useful: ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated. Then I created a table using those columns:
CREATE DATABASE EventCollections
GO
USE EventCollections
GO
-- the table name loosely relates to the name of my Win Event Subscription name
CREATE TABLE [dbo].[GeneralEvents](
[Id] [int] NULL,
[LevelDisplayName] [varchar](50) NULL,
[LogName] [varchar](50) NULL,
[MachineName] [varchar](255) NULL,
[Message] [varchar](max) NULL,
[ProviderName] [varchar](255) NULL,
[RecordID] [bigint] NULL,
[TaskDisplayName] [varchar](50) NULL,
[TimeCreated] [smalldatetime] NULL
)
-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates in sqlbulk imports
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-EventCombo] ON [dbo].[GeneralEvents]
(
[RecordID] ASC,
[MachineName] ASC,
[LogName] ASC
) WITH (IGNORE_DUP_KEY = ON)
GO
In order to avoid duplicates during the hourly imports, I created the table using a unique index with IGNORE_DUP_KEY = ON on 3 columns: RecordID, MachineName and LogName.
Next I had to decide how I'd get the data from PowerShell into SQL Server. After reading up on sqlservercentral.com and technet, I decided on hourly imports using sqlbulkcopy.
The PowerShell Part
Forwarded Events are a tricky thing. For some reason, the way that one would usually filter Get-WinEvent results using FilterHasTable kept returning the result Get-WinEvent : No events were found that match the specified selection criteria. I found a number of others who ran into this issue, too and similar errors occurred when people attempted to use LogParser. After all that, I didn't have much hope in FilterXML working, but it actually did! So we're going to use that after we perform our initial import.
Here's the code for the initial import which gathers ALL events in Forwarded Events.
$events = Get-WinEvent ForwardedEvents | Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated
$connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "GeneralEvents"
$dt = New-Object "System.Data.DataTable"
# build the datatable
$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name
foreach ($col in $cols) {$null = $dt.Columns.Add($col)}
foreach ($event in $events)
{
$row = $dt.NewRow()
foreach ($col in $cols) { $row.Item($col) = $event.$col }
$dt.Rows.Add($row)
}
# Write to the database!
$bulkCopy.WriteToServer($dt)
You may noticed that I manually built a datatable instead of using Out-DataTable.ps1, which appears to be a fan favorite. I felt the code above kept things a little more tidy and the performance is still quite good.
Since Event Collection is an on-going thing, you'll likely want to import them on a regular basis. I built the necessary XML query by right clicking on Forwarded Events in Event Viewer -> Filter Current Log... -> Logged: (Change to one hour) -> Click XML tab at top -> Copy/Paste -> Voila.
Actually, using the syntax of this query, I figured out the syntax for FilterHashTable but having the GUI build my query makes it easy, so I stuck with that. Here is the code for the hourly import that you can setup in Task Scheduler.
# While this script is intended to run on an hourly basis, the filter is set for going back 65 minutes.
# This allows the script to run for 5 minutes without any missing any events. Because we setup the
# table using the IGNORE_DUPE_KEY = ON, duplicate entries are ignored in the database.
$xml = @'
<QueryList>
<Query Id="0" Path="ForwardedEvents">
<Select Path="ForwardedEvents">*[System[TimeCreated[timediff(@SystemTime) <= 3900000]]]</Select>
</Query>
</QueryList>
'@
$events = Get-WinEvent -FilterXml $xml | Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated
$connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "GeneralEvents"
$dt = New-Object "System.Data.DataTable"
# build the datatable
$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name
foreach ($col in $cols) {$null = $dt.Columns.Add($col)}
foreach ($event in $events)
{
$row = $dt.NewRow()
foreach ($col in $cols) { $row.Item($col) = $event.$col }
$dt.Rows.Add($row)
}
# Write to the database!
$bulkCopy.WriteToServer($dt)
With any luck, your SQL output should look something like this:
Woo.
EDIT: If you care about speed, check out this post where I write about using wevtutil instead of Get-WinEvent.
Leveraging SQL Database Snapshots
I've known about SQL Database Snapshots for a while, but never really took the time to use the feature. Lately, I've gotten into mirroring again and decided to play around with Database Snapshots. I should clarify at this point that I'm speaking about the native SQL snapshot capabilities, and not a filer technology (such as NetApp Snaps).
There are probably many good reasons to use a snapshot, but I'm going to cover three scenarios.
Application Upgrades
Often times prior to an application upgrade I am asked to run a full backup for the database. My typical technique for large databases is to schedule a full backup a bit earlier than the maintenance period and then run a differential backup once the application services have been shut down. This will dramatically speed up the time it takes to back up the database, while still providing the ability to fail back if the upgrade goes awry. This has been successful for me in the past, but I decided maybe I should try to take a database snapshot instead and if a fail back is needed, I can restore the database from the snapshot.
I am a little apprehensive to use this method, simply because I'm not comfortable with it. And since data is so precious (and I'd get fired for losing it), I typically have continued to employ the full/differential backup method. I will, however, consider in a test environment testing this methodology on databases of various sizes to increase my comfort level.
Assuming you are comfortable with giving it a shot, here's what you would do...
Prior to your application upgrade, once all app services have been shut down and the database is no longer experiencing changes by users, create the database snapshot.
In this example, taken from MSDN, a snapshot is taken of the AdventureWorks database. The value shown for NAME = <logical_data_file>, is the actual logical data file name of the database you want to make the snapshot from. This is not a new name for your snapshot.
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
GO
Once your snapshot is successful, you can access it from the "Database Snapshots" folder in Management Studio. It's a point-in-time copy of your database and is created instantly. Then, once your application upgrade is complete, you can delete it, or restore back to it if necessary. I have not recovered a large database from a snapshot, but I believe it would be extremely fast as well. From what I know of snapshots, it maintains pointers to the original data, and when new data blocks are modified, they are written to new blocks on the storage system. So I assume that restoring from a snapshot will revert the pointers back to that state, allowing for an instant restore.
USE master;
-- Reverting AdventureWorks to AdventureWorks_dbss1800
RESTORE DATABASE AdventureWorks from
DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';
GOMirroring
Another handy way to leverage snapshots is when mirroring. As you probably know, when you are mirroring a database to another server, the target database on the mirror server cannot be read. If you need to verify that something made it over, or whatever the circumstance may be, you can create a snapshot of that mirrored database and then query it as a normal database. Depending on performance impact, you can most likely use this as a reporting server as well if your data does not have to be real-time.
Nightly Refreshes in a Training Environment
Many training environments have the desire to revert to a clean state prior to each training session. The main way to revert may be to set up a SQL Agent job to restore from a flat file, but this is another scenario that I want to restore from a snapshot. When my training environment is in it's "gold copy" state, I took a flat file backup just to cover my bases. Then a snapshot. Instead of setting up a job to overwrite the database from the flat file, I just execute my restore from snapshot command, and the database reverts back. It's easier and there are less moving parts.
Things to Consider
As I stated earlier, from my knowledge of snapshots, a snapshot is just pointers to blocks on a file system. That's why the snapshot is instant. The downside to this, however, is that when you query your snapshot, you are hitting the same blocks on the disk as the live database. If you create too much I/O load against your snapshot, you will also be affecting the live database and can impact performance.
When the live database modifies data, it will write new blocks to the file system. If your system has a high rate of change and snapshots exist, you will have to account for extra storage. As new blocks are written, storage usage will increase, even if the database is not actually growing in size.
SQL Server Discovery Tools and Scripts
Each time I start a new contract, the first thing I do is search for all SQL Servers across any network/subnet to which I have access. While the documentation I get is usually paltry or non-existent. When it does exist, it's usually only for the production SQL Servers. Sometimes, most production SQL Servers aren't even documented; one of my clients only had 20 documented servers, and I found over 60 (including SQL Express instances, of course.)
So here I am again, starting a new contract and I'm out looking for SQL Discovery Tools. Ultimately, here are the 5 I ended up finding and using, in no particular order:
Idera SQL Discovery is a really great tool within the Idera SQL Toolbox. That one's good and , which has been in beta for years.
A new tool I discovered this time around is the Microsoft Assessment and Planning Toolkit. Free (as in beer), of course.
Next is a quick lil script that grabs all SQL Servers in Active Directory that contain the word "SQL"
import-module activedirectory
get-adcomputer -filter "Name -like '*SQL*'" |select Name
Next is a more thorough script written by Colin Smith which grabs a list of servers within a text file and probes their services. I created the list with the script above, though I replaced "*SQL*" with "*Server*"
#####################################################################################
## Author: Colin Smith
## Script: Get_Intstance_names.ps1
## Purpose: This script will read in a file of hotnames that has been
## Provided of servers with SQL Server running on them. This
## script will then look at the services on that host to find
## the instance name if the instance is named. If the instance
## is a default instance the script will also report that.
#####################################################################################
$Servers = get-content "C:\servers.txt"
echo "Server, Instance" >> "C:\sqltab.txt"
foreach ($server in $servers)
{
$instances = Get-WmiObject -ComputerName $server win32_service | where {$_.name -like "MSSQL*"}
if (!$?)
{
echo "Failure to connect on $server" >> "C:\failures.txt"
echo "Failure to connect on $server"
}
else
{
$instancenames = @()
foreach ($name in $instances)
{
if (($name.name -eq "MSSQLSERVER") -or ($name.name -like "MSSQL$*"))
{
$instancenames += $name.name
}
}
foreach ($iname in $instancenames)
{
echo "$server, $iname" >> "C:\sqltab.txt"
echo "$server, $iname"
}
}
}
Next up, I'd like to investigate the SQL Server Active Directory Helper service and see what that's all about.. then get all these SQL Servers registered.
A Possible Infinite Recompile was Detected – SQL Server Bug with Linked Servers
I recently assisted in migrating a very large system from SQL Server 2005 SP3 to SQL Server 2008 R2 SP1. The actual migration of the database went off without a hitch, but we soon encountered an odd error with the application querying a view over a linked server.
The application was web-based and began throwing an error trying to load a view. After some investigation, we discovered that the view was going across a linked server. Every time the error was encountered, the Windows Event Viewer displayed the following error:
A possible infinite recompile was detected for SQLHANDLE 0x020000004840002608194C0756C4E44307B50A91623589FD, PlanHandle 0x060012004840002640616630050000000000000000000000, starting offset 32, ending offset -1. The last recompile reason was 6.
I took a look at the SQL Text using the supplied handle and matched it to what the web page was calling. The recompile reason of type 6 turned out to be a problem with remote rows changing on the linked server. We attempted different things to fix the issue, and thought maybe a maintenance run would assist. In the end, restarting the main SQL Server instance (where the linked server was configured) resolved the issue.
The system ran great for a week, but then we encountered the same issue the next weekend. After engaging Microsoft, we were pointed to a KB article about a bug with linked servers and synonyms across SQL Versions.
The article can be read here.
Cumulative updates are available for different versions of SQL Server to resolve the issue. However, for those of you who cannot just take down a system to apply the patch, there is a temporary fix.
exec sp_refreshview <view name>
On the main server that is executing the query across the linked server, the views pulling data over the linked server connection must be refreshed AFTER re-indexing or other schema-changing maintenance on the linked server.
We currently have a weekly job in place to refresh our relevant views until we can schedule the CU updates. I only wish I had been able to find more information on this bug when I was experiencing it. It would have saved us 8 hours in the middle of the night trying to figure it out.
SQL Server 2012 PowerPivot Services: Service Account Keeps Getting Locked Out
Okay, I've had to call Helpdesk an embarrassing 8 times today because my PowerPivot upgrade is going horribly wrong and locking out my AD account that, admittedly, I use as a "service" account. I'm really left with no choice -- I'm only granted one account on this network. Restrictive networks are so restrictive 
For dev machines, I like to let PowerPivot setup my farm. Today, I was upgrading my SQL Server 2008 R2 instance to SQL Server 2012 and ultimately decided to just wipe out my entire 2008 instance and setup a new farm. Everything went well until the Validation area where it would say that my password isn't valid. Here's the workaround that worked for me:
- Running the validation once (which always resulted in a lockout)
- Unlocking the account
- Entering my Service Account Password
- Making the farm pass phrase the same thing
- Validating once more. If it fails...
- Type in the account password again but *click elsewhere* before hitting Validate so that the password box pretends it has additional characters.
- Hit validate, party.
When xp_delete_file doesn’t work as expected…
Whether you use a Maintenance Cleanup Task as part of a SQL 2005 or 2008 Maintenance Plan, or script out your own cleanup t-SQL, you are probably using the xp_delete_file extended procedure. For quite a while, I've occasionally come across old TLog or backup files that were missed. Without questioning it, I would clean them up manually.
Recently I had to disable my 3rd party compression utility due to issues and noticed that all of the SQL instances using that compression utility stopped cleaning up their TLog files. I decided I had to investigate why this was happening. My original assumption with xp_delete_file was that it went to a directory that you specify, looked for the file extension and deleted any matches older than the date. But there is a missing component here.
Let's look at the syntax for xp_delete_file in my particular case...
EXECUTE master.dbo.xp_delete_file
0, -- Either a 0 (Backup File) or 1 (Maintenance Plan Log File)
N'', -- Location of Files. I use a remote SAN storage location.
N'TRN', -- Extension of file to clean up.
N'2012-03-28T09:38:06', -- Date threshold to delete files older than...
1 -- 0 (Current Directory) or 1 (Recursively through subdirectories)
The secret to this procedure is the first argument, the specification of the type of file you are deleting. You may wonder why it would care what kind of file it is, as long as you can match up the extension, but the procedure is actually reading the header of the files to determine that they are indeed backups.
This is why my backups stopped cleaning up after I disabled my compression software. The native ability of SQL Server could not read the header of my TLog files, and therefore could not verify that they are backup files. So it did not delete them. The issues I had occasionally with missed files were with corrupted backup files, usually aborted during writing for one reason or another.
In order to get around the issue, I tried telling the procedure that the files were just logs, but that was not successful in cleaning up my backups either. So the decision has to be made on if this is acceptable behavior, or if I want to go through the trouble of crafting a new method of deletion for all of my SQL installs. At this point, I think I can live with it, even though forcing a deletion would be handy.
[SOLVED] PowerPivot for SharePoint 2010: An error occurred connecting to this data source.
I'm currently working on a project where I need to quickly make pretty graphs and charts for data in small but important SharePoint lists. After looking through a number of books and consulting with my SharePoint buddies, PowerPivot for SharePoint 2010 seems to be the best tool for the job.
Getting it to work hasn't been easy, I believe because there's so many components that come into play. You've got the PowerPivot add-on for Excel 2010, the PowerPivot service for SharePoint, Excel Services for SharePoint, SQL Server Analysis Services and PerformancePoint Dashboards. Here's how it all started...
- I was assigned to make pretty charts for a site
- I installed the Excel + the PowerPivot add-on
- Exported the SharePoint list
- Opened the exported list, enabled Data Connections, and made a PowerPivot chart thing
- Checked to make sure PerformancePoint & Excel Services features were enabled
- Created a BI site
- Saved the Excel file to SharePoint
- Launched Dashboard Designer using the BI page
- From there, I attempted to access the cube in a PerformancePoint dashboard, per Microsoft's instructions
- Run Dashboard Designer
- Create
- Data Source
- Analysis Services
- Use the following connection: http://sharepoint/Documents/localpivot.xlsx
I left the authentication as default and received the following errors:
- You do not have permissions to see this data or the server is unavailable. Additional details have been logged for your administrator. Contact the administrator for more details.
- An error occurred connecting to this data source. Please check the data source for any unsaved changes and click on Test Data Source button to confirm connection to the data source.
Supposedly, installing ASADOMD10 from the SQL Server 2008 R2 Feature Pack would fix the issue, but it didn't. This farm is running on my local workstation, so I check Event Viewer and see: The Unattended Service Account "AD\yo.momma" does not have access to the server specified by the data source connection string. But.. but.. I'm the administrator of this whole thing. I 0wn this farm, what gives? So I check the IIS log:
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/client.svc/ProcessQuery - 80 - 127.0.0.1 - 401 2 5 0
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/client.svc/ProcessQuery - 80 - 127.0.0.1 - 401 1 2148074254 0
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/client.svc/ntlm/ProcessQuery - 80 AD\yo.momma 127.0.0.1 - 200 0 0 343
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/PPS/PPSAuthoringService.asmx - 80 - 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5448) 401 1 2148074254 0
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/PPS/PPSAuthoringService.asmx - 80 AD\yo.momma 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5448) 200 0 0 15
2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/PPS/PPSAuthoringService.asmx - 80 - 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5448) 401 2 5 0
2012-03-06 18:52:41 172.16.0.125 POST /_vti_bin/PowerPivot/redirector.svc/ DataSource=%2FDocuments%2Flocalpivot.xlsx 80 - 172.16.0.125 ADOMD.NET 401 2 5 0
2012-03-06 18:52:41 172.16.0.125 POST /_vti_bin/PowerPivot/redirector.svc/ DataSource=%2FDocuments%2Flocalpivot.xlsx 80 - 172.16.0.125 ADOMD.NET 401 1 2148074254 0
You can see the myriad 403 Forbidden -- maybe it's some messed up NTLM permissions? So I check /_vti_bin/PowerPivot/ and, voila! The files don't exist.
That's not a Forbidden, that's really a 404 Not Found. I never even installed PowerPivot for SharePoint in the first place. You can enable references to it around SharePoint if I recall, but the service is part of the SQL Server install. Well, damn.
I decided to start entirely from scratch by removing my workstation from the farm, and uninstalling the two versions of SQL Server, Express and Denali CTP, on my workstation. Then I setup a new farm using SQL Server's Installation interface (I didn't even realize I could do that, bad admin!) I won't lie..the install was so clean -- it created the local Central Admin instance as well as a new Site Collection with all the PowerPivot features already enabled. Delicious.
If the above was not your issue, you may need to:
- Make sure SQL Server PowerPivot is setup as a Service Application: Central Admin -> Manage Service Applications
- Make sure it's associated with the web app: Centeral Admin -> Application Management -> Configure Service Application Associations -> Application Proxies
- Make sure the PowerPivot Service has started: Central Admin -> Manage services on server
Also, check your Event Viewer logs, that's been helpful for me.
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Chrissy LeMaire
Tweet to @cl
Brandon Abshire
Categories
- Oracle
- OS X & iDevices
- PowerShell
- Security
- SharePoint
- SQL Server
- VBScript
- Virtualization
- Windows
- Wordpress
