SQL – dealing with a WTF moment

No clue on this one.

An engineer asked me to move his two databases from a DEV environment to his production environment.

No big deal. Let me take a backup, move it over, restore it, bam….done. Ah, crapola, looks like he was developing on a 2008 R2 instance and production is 2008. Well that’s not good but it’s still do-able.

I figured I could script out the database (and data) using the ‘generate scripts’ option in SSMS. I would make sure to check the 2008 option.

script

I copy my script to the second server, open it up, change the location, run it and we are……NOT ok.

huh?

This looks ok to me. What the hell is the problem with this one insert statement?

I copied the one insert statement over to a new query window and then it shows up like this:

huh x 2

W….T….F…Only half of the insert statement copied over.

I end up commenting this one line out and trying to run the script; zero problems.

I then went back and copied the insert statement into 2 parts, the part ending in ‘999 and the second part starting with ‘, N’Rest… and I have no problems.

So to double the crazy, the second database had one record in the same table, in the same column (varchar(100)) that did the exact same thing.

I checked the collations and they were the same. What kind of character or break could be after that 999 that would break the query? Let alone my clipboard?

Server 1 was a SQL express 2008 R2 w/ advance services and the second was a SQL standard 2008, does that matter? Both 64 bit-ers.

I’m stumped. Any ideas from the SQL world?

UPDATE:

I found out what was breaking it. Bill Fellows ( blog | @billinkc ) had a good idea of checking it in hex-editor. Turns out there was an invisible CREATE DATABASE statement shoved in there. NO idea why though – http://twitpic.com/5t88c2