I had replication errors recently with some subscriptions that were set up with replication support only (manually created and populated destination tables). These are transactional push subscriptions that are not updateable (i.e. they are one way only from the source to the destinations and the destinations must keep the same keys as the source).

The error was visible in the distribution.dbo.MSrepl_errors table:

Cannot insert explicit value for identity column in table 'TABLE_NAME' when IDENTITY_INSERT is set to OFF.

The cause of this is that in this style of replication most DBAs will either create the destination tables with a plain column instead of an identity column, or they will create the column with the NOT FOR REPLICATION flag. I had trouble finding explicitly what that flag would do in my scenario, but now I can boil it down to this:

• The flag doesn’t mean anything on the publisher.
• The flag means everything on the subscriber.
• If it’s set on a subscriber it means the replication agent can insert the identity column without doing a SET IDENTITY_INSERT command (which it would never do).

A good memory device would be NOT FOR REPLICATION means [IDENTITY COLUMNS INSERTS ARE] NOT [CHECKED] FOR [THE] REPLICATION [AGENT].

Turning off the identity columns was not an option because you can’t do that in SQL Server 2005+. I couldn’t just create and populate a new column either because of the dependencies, and it would also change the column ordering which would cause validation to fail. Nor could I recreate everything without turning off replication and losing all of the data in the middle and doing a lengthy initial sync again.

So NOT FOR REPLICATION is exactly what was needed. You can identify and update the tables to have this flag, with a command like this, on each of the subscribers:

DECLARE @TableID int = (
SELECT	TOP 1 sys.sysobjects.id AS Table_ID
FROM	sys.sysobjects
JOIN	sys.syscolumns 
ON	syscolumns.id = sysobjects.id
WHERE	sys.sysobjects.name = 'TABLE_NAME'
AND	(sys.syscolumns.status & 0x88) = 0x80 -- 0x80 = Identity, 0x08 = Not For Replication

ORDER BY 1)

IF @TableID IS NOT NULL
EXEC    sys.sp_identitycolumnforreplication @TableID, 1

After doing this and starting up the replication agents again, everything continued smoothly.

When creating snapshots for SQL Server replication there’s a tick box that allows you to store a second compressed copy somewhere.

I was logged in through SSMS and authenticated on the domain with full access to all of that server’s folders, however SSMS would not allow me to browse or set the directory remotely. I had to log directly onto the server with remote desktop to set it.

However after that it was hard to find information on exactly how fast (or in this case slow) that compression is. For reference my data set is about 12GB, highly compressible text and numbers, and takes 2 minutes to extract. Compressing this in 7-Zip takes an additional 3 minutes. But allowing SQL Server to extract and compress it using CAB takes over 10 minutes total.

I’d be extremely cautious about using it in production without further testing.

After setting up transactional replication on a set of servers with @sync_type = “replication support only” I was receiving errors on validation where the row count was correct but the checksum was not. Meanwhile SQL Data Compare was telling me the tables on each server were exactly the same. This was caused by two issues.

One was that Options -> Force Binary Collation (case-sensitive) is not on by default in SQL Data Compare, so one row of nvarchar data had different case between the servers. I had never seen that before (after more than a year of syncing) and so updated the relevant fields immediately. You might want to check the same if you use this tool.

The other bigger issue was that the built-in replication validation routines require your columns to be of the same type and in the same order on each server – all the types and user-defined types and null/not null settings seemed okay but some servers had columns in a different order. You can identify this by checking the offset field on each server from this query:

SELECT s.name, s1.name, s1.offset, s1.xoffset 
FROM sys.sysobjects s
INNER JOIN sys.syscolumns s1 ON s.id = s1.id
WHERE s.name = 'TABLE_NAME'
ORDER BY offset

In my case where I had columns A B C on one, I had columns A C B on the other. I wrote a quick query to resolve this specific situation where I could reshuffle the columns by removing C and re-adding it again:

ALTER TABLE TABLE_NAME ADD C_2
UPDATE TABLE_NAME SET C_2 = C
ALTER TABLE TABLE_NAME ALTER COLUMN C_2 NOT NULL
-- Remove any foreign keys
ALTER TABLE TABLE_NAME DROP COLUMN C
EXEC sys.sp_rename 'TABLE_NAME.C_2', 'C', 'column'
-- Re-add any foreign keys

If you have foreign keys or indexes or triggers you’d have to include your own code.

After this all columns have the same offset and validation completes successfully. I wasn’t sure this would be the case because Microsoft only guaranteed it if you used @sync_type = “auto”, and doesn’t explicitly state that creating them yourself in the right order will work. Well it does.

I’ll also note that Microsoft also states there can be collation issues that affect validation between databases but in my case I have different collations on the databases and they are still validating correctly, at least for my purposes.

No solutions today, only sadness and finger pointing.

Part 1

If you’re using PowerShell and the new SQL Server SQLServer PSProvider that gets installed in the newer version or otherwise comes free with one of the feature packs, you are probably dying to try out the new object-based SQL Server file system. Until you actually try to use it:

Import-Module SQLPS -DisableNameChecking

New-PSDrive -Name MYSERVER -PSProvider SQLServer -Root SQLSERVER:\SQL\MYSERVER\DEFAULT -Credential MySQLUserName

# WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'MYSERVER' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
# WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'MYSERVER' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
# WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'MYSERVER' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
# WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'MYSERVER' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
# New-PSDrive : SQL Server PowerShell provider error: Could not connect to 'MYSERVER\DEFAULT'. [Object reference not set to an instance of an object.]
# At line:1 char:1
# + New-PSDrive -Name MyServer -PSProvider SQLServer -Root SQLSERVER:\SQL\MYSERVER\D ...
# + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#     + CategoryInfo          : OpenError: (SQLSERVER:\SQL\MYSERVER\DEFAULT:SqlPath) [New-PSDrive], GenericProviderException
#     + FullyQualifiedErrorId : ConnectFailed,Microsoft.PowerShell.Commands.NewPSDriveCommand

Oops!

That’s because the Credential parameter, while technically allowing you to use the SQL Server authentication instead of domain authentication, still checks to make sure your domain credentials have access to WMI on the server you are connecting to.

Which makes it utterly useless for DBAs administering servers across multiple untrusteed domains.

What makes it even more infuriating is that in those situations, you likely have WMI access over other credentials. For example, this would work:

Get-WmiObject win32_Bios -ComputerName MYSERVER -Credential Administrator

# A few lines of BIOS information

But because you can’t tell the PSProvider to use those credentials, it will just fail. Bad Microsoft. Bad.

Part 2

And while investigating the above WMI testing, I found one server I could not connect to, where I would get this error message:

# Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
# At line:1 char:1
# + Get-WmiObject win32_bios -ComputerName MYSERVER -Credential Administrator
# # + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#     + CategoryInfo          : InvalidOperation: (:) [Get-WmiObject], COMException
#     + FullyQualifiedErrorId : GetWMICOMException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

Which was weird because the RPC services were running, I could run the command locally on that server and it was fine, and I could telnet over WMI TCP Port 135 showing that port was opened.

What followed was some digging into Wireshark and comparing between a server where WMI works and a server where WMI doesn’t work. This is the working version:

Source	Destination	Protocol	Length	Info
Local	Working Remote	TCP	66	49864 > epmap [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
Working Remote	Local	TCP	66	epmap > 49864 [SYN, ACK] Seq=0 Ack=1 Win=8192 Len=0 MSS=1452 WS=256 SACK_PERM=1
Local	Working Remote	TCP	54	49864 > epmap [ACK] Seq=1 Ack=1 Win=66560 Len=0
Local	Working Remote	DCERPC	170	Bind: call_id: 2 Fragment: Single, 2 context items, 1st IOXIDResolver V0.0
Working Remote	Local	DCERPC	138	Bind_ack: call_id: 2 Fragment: Single Unknown result (3), reason: Local limit exceeded
Local	Working Remote	IOXIDResolver	78	ServerAlive2 request
Working Remote	Local	IOXIDResolver	290	ServerAlive2 response[Long frame (2 bytes)]
Local	Working Remote	TCP	66	49865 > epmap [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
Local	Working Remote	TCP	54	49864 > epmap [ACK] Seq=141 Ack=321 Win=66304 Len=0
Working Remote	Local	TCP	66	epmap > 49865 [SYN, ACK] Seq=0 Ack=1 Win=8192 Len=0 MSS=1452 WS=256 SACK_PERM=1
Local	Working Remote	TCP	54	49865 > epmap [ACK] Seq=1 Ack=1 Win=66560 Len=0
Local	Working Remote	DCERPC	174	Bind: call_id: 3 Fragment: Single ISystemActivator V0.0, NTLMSSP_NEGOTIATE
Working Remote	Local	DCERPC	290	Bind_ack: call_id: 3 Fragment: Single, NTLMSSP_CHALLENGE accept max_xmit: 5840 max_recv: 5840
Local	Working Remote	DCERPC	532	AUTH3: call_id: 3 Fragment: Single, NTLMSSP_AUTH, User: REMOTEDOMAIN\\REMOTEUSER
Local	Working Remote	ISystemActivator	870	RemoteCreateInstance request
Working Remote	Local	TCP	60	epmap > 49865 [ACK] Seq=237 Ack=1415 Win=65280 Len=0
Working Remote	Local	ISystemActivator	1238	RemoteCreateInstance response[Malformed Packet]
Local	Working Remote	TCP	66	49868 > 49154 [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
Local	Working Remote	TCP	54	49865 > epmap [ACK] Seq=1415 Ack=1421 Win=65280 Len=0
Working Remote	Local	TCP	66	49154 > 49868 [SYN, ACK] Seq=0 Ack=1 Win=8192 Len=0 MSS=1452 WS=256 SACK_PERM=1

And this is the non-working version:

Source	Destination	Protocol	Length	Info
Local	Failing Remote	TCP	66	49884 > epmap [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
Failing Remote	Local	TCP	66	epmap > 49884 [SYN, ACK] Seq=0 Ack=1 Win=8192 Len=0 MSS=1452 WS=256 SACK_PERM=1
Local	Failing Remote	TCP	54	49884 > epmap [ACK] Seq=1 Ack=1 Win=66560 Len=0
Local	Failing Remote	DCERPC	170	Bind: call_id: 2 Fragment: Single, 2 context items, 1st IOXIDResolver V0.0
Failing Remote	Local	DCERPC	138	Bind_ack: call_id: 2 Fragment: Single Unknown result (3), reason: Local limit exceeded
Local	Failing Remote	IOXIDResolver	78	ServerAlive2 request
Failing Remote	Local	IOXIDResolver	266	ServerAlive2 response
Local	Failing Remote	TCP	66	49885 > epmap [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
Failing Remote	Local	TCP	66	epmap > 49885 [SYN, ACK] Seq=0 Ack=1 Win=8192 Len=0 MSS=1452 WS=256 SACK_PERM=1
Local	Failing Remote	TCP	54	49885 > epmap [ACK] Seq=1 Ack=1 Win=66560 Len=0
Local	Failing Remote	TCP	54	49884 > epmap [ACK] Seq=141 Ack=297 Win=66304 Len=0
Local	Failing Remote	DCERPC	174	Bind: call_id: 3 Fragment: Single ISystemActivator V0.0, NTLMSSP_NEGOTIATE
Local	Failing Remote	DCERPC	174	[TCP Retransmission] Bind: call_id: 3 Fragment: Single ISystemActivator V0.0, NTLMSSP_NEGOTIATE
Failing Remote	Local	DCERPC	300	Bind_ack: call_id: 3 Fragment: Single, NTLMSSP_CHALLENGE accept max_xmit: 5840 max_recv: 5840
Local	Failing Remote	DCERPC	528	AUTH3: call_id: 3 Fragment: Single, NTLMSSP_AUTH, User: REMOTEDOMAIN\\REMOTEUSER
Local	Failing Remote	ISystemActivator	870	RemoteCreateInstance request
Failing Remote	Local	TCP	66	[TCP Dup ACK 14#1] epmap > 49885 [ACK] Seq=247 Ack=121 Win=66560 Len=0 SLE=1 SRE=121
Failing Remote	Local	TCP	60	epmap > 49885 [ACK] Seq=247 Ack=1411 Win=65280 Len=0
Failing Remote	Local	ISystemActivator	1182	RemoteCreateInstance response[Malformed Packet]
Local	Failing Remote	TCP	66	49886 > 49154 [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
Failing Remote	Local	TCP	60	49154 > 49886 [RST, ACK] Seq=1 Ack=1 Win=0 Len=0
Local	Failing Remote	TCP	54	49885 > epmap [ACK] Seq=1411 Ack=1375 Win=65280 Len=0

The difference was apparent about a page down where trying to connect over another WMI TCP Port 49154. That was running on the server, but I could not connect to it. Just to be sure, I checked the Advanced Firewall settings on that server and did not find anything, and for a last check, ran this from a command prompt to make sure the port was open:

netstat -a -n


And then started up a session that will write a text log that should show if the Windows firewall has dropped a packet:

cd %userprofile%\desktop
netsh wfp capture start
...
netsh wfp capture stop


And it hadn’t. This pretty much confirmed there as a router configuration blocking access along the way.

Today I received this message while running a PowerShell script on a remote server:

System error 85 has occurred

There may be more information but sadly I didn’t get to see it. What does it mean? Most likely that you have a net use command, and that the drive it is trying to map has already been taken.

I work around it by checking if the drive exists first:

if (!(Test-Path Z:))
{ 
    net use z: $password "/user:$username"
} 

You could use New-PSDrive instead but unless you have credssp authentication set up (a pain) then you’re going to have a bad time.

I had a nasty message pop up in the middle of running an SSIS package with DTEXEC. That same package had been working fine for a long time, and was running in BIDS as recently as yesterday on the same machine with no problem.

Error: 2013-04-08 16:04:25.01
   Code: 0xC000F427
   Source: Postcode Zone SSIS.Pipeline
   Description: To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher.
End Error

I did a Google search and found other users had often reported this error in other situations where BIDS was not installed; which didn’t seem to apply. But one unanswered post caught my eye where someone had added a Derived Column task to the package and had it suddenly fail. That is exactly what I had done!

After some head-bashing I thought maybe there are different versions of DtExec installed locally and I’m using the wrong one:

Get-ChildItem c:\Progra*\dtexec.exe -Recurse | %{ "> " + $_.FullName ; $Version = &$_; $Version -like '*Version*'; Write-Host }

/*
> C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe
Version 11.0.2100.60 for 64-bit

> C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
Version 10.50.2500.0 for 32-bit

> C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe
Version 11.0.2100.60 for 32-bit
*/

I’d been using the first (2008R2) version for some reason. Switching to the last (2012 32-bit) version made it work.

I was scratching my head looking at a complex .NET object and wondering how to get all of the values out of it. For example:

 PS C:\> $date = New-Object System.DateTime

PS C:\> $date | Write-Host # Because I was doing it remotely in a job

Monday, 1 January 0001 12:00:00 AM

And yet there are so many more properties available on the object:

PS C:\> $date | Get-Member

   TypeName: System.DateTime
# ... Snipped for brevity
Date                 Property       datetime Date {get;}             
Day                  Property       int Day {get;}                   
DayOfWeek            Property       System.DayOfWeek DayOfWeek {get;}
DayOfYear            Property       int DayOfYear {get;}             
Hour                 Property       int Hour {get;}                  
Kind                 Property       System.DateTimeKind Kind {get;}  
Millisecond          Property       int Millisecond {get;}           
Minute               Property       int Minute {get;}                
Month                Property       int Month {get;}                 
Second               Property       int Second {get;}                
Ticks                Property       long Ticks {get;}                
TimeOfDay            Property       timespan TimeOfDay {get;}        
Year                 Property       int Year {get;}                  

The solution was so, so simple, although none came up on a Google search:

PS C:\Users\cody.konior> $date | Select-Object -Property * # You can omit -Property


DateTime    : Monday, 1 January 0001 12:00:00 AM
Date        : 1/01/0001 12:00:00 AM
Day         : 1
DayOfWeek   : Monday
DayOfYear   : 1
Hour        : 0
Kind        : Unspecified
Millisecond : 0
Minute      : 0
Month       : 1
Second      : 0
Ticks       : 0
TimeOfDay   : 00:00:00
Year        : 1

That’s it. It would probably need to be more complicated in order to iterate any properties that need to be expanded (objects of objects). But I don’t have an object complex enough to try it out on.

I have an array of objects and I want to pull out a set where a property matches any entry in an array of strings… with wildcards. I couldn’t find a solution anywhere (or even others having the same issue), so here’s the thought process I went through.

To start with, I explored -in:

$items = @("ANACONDA", "BACON", "CAT", "DOG")

$items | Where-Object { $_ -in "CAT" }
# CAT
$search = @("CAT", "DOG")
$items | Where-Object { $_ -in $search }
# CAT
# DOG
$items | Where-Object { $_ -in "CA*" }
# 

-in works with arrays, but it doesn’t work with wildcards. So I tried -like:

$search = @("C*", "D*")

$items | Where-Object { $_ -like "C*" }
# CAT
$items | Where-Object { $_ -like $search }
# 

-like works with wildcards, but doesn’t work with arrays. So here’s the solution I came up with.

Expand the wildcards into a second string array with the full matching names from the items we wanted to match against. There’s a bit of convolution to make sure we end up with a simple unique string array – without it you can end up with arrays within arrays.

$expanded = $search | 
    Select-Object @{Name="ExpandedItem";Expression={$items -Like $_}} | 
    Select-Object -ExpandProperty ExpandedItem -Unique
# CAT
# DOG

For the example that’s far enough, but for me remember I was looking for objects with certain properties. With this expanded non-wildcard search list, I could now fall back on using a simple -in:

$items | Where-Object {$_ -in $expanded}
# CAT
# DOG

I hope someone else finds this useful. If you know a better method please let me know.

When using Ace.OLEDB to load up files, I was having sporadic problems with the entire SQL Server process crashing with this exception. I believe it’s because you shouldn’t allow things to run in-process, so I tried to disable it:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0

But this just caused more errors with it being unable to initialize components. I ended up searching far and wide for a solution, and I found it here: Permissions needed to set up linked server with out-of-process provider.

It’s probably the most important stability-related article I’ve ever read. You have to jump through hoops to configure SQL Server to allow ODBC imports safely and it details the steps to do so. I’ve found it effective on a dozen different servers over the past few months with no ill effects.