[Replication] PSA - when flipping IPs, STOP THE JOBS

After just going through a fire drill, let me add this handy piece of information.

IF YOU ARE CHANGING THE IP ADDRESS OF A SUBSCRIBER, FOR THE LOVE OF GOD STOP THE REPLICATION JOBS TO IT FIRST.

In our case, the agent (due to the magic of laggy DNS) managed to insert records before the proper subscription could - thereby forcing us to go through sp_browsereplcmds and find the new records, then delete them.

You live, you learn.

[Excel] Converting float and dealing with leading zeros

So, for the Nth time I've received an Excel file that has a field which should have leading zeros. And instead, I've received a file that uses the PESEL (Polish Social Security Number) formatting to make it LOOK like the data is correct. Which SQL Server promptly ignores. So you can fix the Excel column ( =text(B1,"00000000000")

If you try a straight up conversion, it fails.
Example:
Should be 00123456789
Looks like 00123456789
Actually is 123456789
"select convert(varchar,crap_field) from your_table" yields: 1.23456e+009
ALTER TABLE fails because the value is too large (numeric overflow).

Fix: double convert, then add leading zeros.

select convert(decimal(11,0),crap_field) from your_table --getting there
select convert(varchar(11),convert(decimal(11,0),crap_field)) --now it's a varchar
select right('00000000000' + --11 zeros
  convert(varchar(11),convert(decimal(11,0),crap_field))
  , 11) --add leading zeros. Done!

Note the table is the same, and you can't just UPDATE the table - the format is still wrong.