[BCP] When native isn't exactly what you put in

An oddity I wanted to mention:

I BCPd out the contents of a table using QUERYOUT, in NATIVE format (/n).
I then proceeded to import the file into an identical table using
BULK INSERT WITH ( DATAFILETYPE = 'native', KEEPIDENTITY )
and used SQL Data Compare (plug!) to compare the two tables.

To my surprise, out of 1.5m rows, 21 were different. In this case, instead of a dash (0x2D), I wound up with what looks like a dash but isn't (0x96).

If anybody's heard of this, please chime in. Both were varchar(40), though the source was 2008 and the target was 2005.

[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.

[Jobs] Quick & Dirty - is the job running (another approach)

Cribbed from Gregory A. Larsen.

http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2/Detecting-The-State-of-a-SQL-Server-Agent-Job.htm

SET NOCOUNT ON
create table #enum_job ( 
Job_ID uniqueidentifier, 
Last_Run_Date int, 
Last_Run_Time int, 
Next_Run_Date int, 
Next_Run_Time int, 
Next_Run_Schedule_ID int, 
Requested_To_Run int, 
Request_Source int, 
Request_Source_ID varchar(100), 
Running int, 
Current_Step int, 
Current_Retry_Attempt int, 
State int 
)       
insert into #enum_job 
     exec master.dbo.xp_sqlagent_enum_jobs 0,sa,@job_id

IF (select COUNT(*) from #enum_job WHERE running = 1) = 1
PRINT 'running'
drop table #enum_job
SET NOCOUNT OFF

[Code] Using INFORMATION_SCHEMA with temp tables.

Was recently trying to parse a temp table and do things based on the columns. I'd come up with this....

  SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME LIKE '#yourtemptablehere%'

But then I came across this post from Michael Valentine Jones (a pseudonym?), on the SQLTeam forums. Many thanks, Michael

select
 *
from
 tempdb.information_schema.columns
where
 object_id('tempdb..#yourtemptablehere') = object_id('tempdb..'+TABLE_NAME)

[Powershell] Basic SQL query exported to CSV

Invoke-Sqlcmd -query "select getdate(), getdate()+1" -serverinstance "yourservername"|Export-Csv c:\temp\testps2.txt –notypeinformation

or, broken down by line so you can see all of it...

Invoke-Sqlcmd 
 -query "select getdate(), getdate()+1" 
 -serverinstance "yourservername"
 |Export-Csv 
 c:\test\test.txt 
 –notypeinformation

-query: the query.
-serverinstance: server name
-notypeinformation: removes the “#TYPE System.Data.DataRow” line at the top.

And if you don't want a header row... you have to use a different export process, and then tell a different process to iterate through the array and write to disk. Really, guys? Too hard to add a -noheader option?

(and all this is on one line; you can use a ` to split it across lines.

Invoke-Sqlcmd -query "select getdate(), getdate(); select getdate()+1, getdate()+1" -serverinstance "yourservername"|ConvertTo-Csv -notypeinformation -outvariable outdata; $outdata[1..($outdata.count-1)] |ForEach-Object {Add-Content -value $_ -path "c:\temp\test.txt"}

or 
Invoke-Sqlcmd -query "select getdate(), getdate(); select getdate()+1, getdate()+1" `
-serverinstance "ftw-sv-db-03"|ConvertTo-Csv -notypeinformation -outvariable outdata;`
 $outdata[1..($outdata.count-1)] |ForEach-Object {Add-Content -value $_ -path "c:\temp\test.txt"}
 (then hit enter again to tell it you're done for realsies)

Oh, and it for some reason outputs the full file to console, but saves what you want to a file.

[Powershell] Basics to run a SQL query

Putting this here for when the new guy starts. The learning curve can suck at certain points, like the installer. See my other post about it. Grr.

• Install Powershell 2
  
• Install SQL Server 2008 Feature Pack: Powershell Extensions http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52
  
• Install SQL Powershell Extensions: http://sqlpsx.codeplex.com/
  
• Add this line to My Documents\WindowsPowerShell\profile.ps1: "add-pssnapin SqlServerCmdletSnapin100; add-pssnapin SqlServerProviderSnapin100;" (no quotes)
  
• Start Powershell and see if it works:
  Set-ExecutionPolicy RemoteSigned (or
  Invoke-Sqlcmd -query "select getdate(), @@version" -serverinstance "yourservername"|Export-Csv c:\testps.txt –notypeinformation
  

[Maintenance] Checking age of statistics

Simple stuff, saved here in case anybody needs it

SELECT objects.name AS object_name, indexes.name AS index_name,
STATS_DATE(indexes.OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
INNER JOIN sys.objects ON indexes.object_id = objects.object_id
--WHERE objects.OBJECT_ID = OBJECT_ID('dbo.yourtablename')
GO

[Replication] Nasty bug and reinitializing without a snapshot

So, there's a bug in SQL Server 2005 SP2 _and_ SQL Server 2005 SP3 (fixed in SP2 CU12 and SP3 CU3), where a commonly-used replication SP can inadvertently reset your subscription, and you get the dread "The initial snapshot for publication...is not yet available" error. In my case I couldn't easily snapshot the data across, so a workaround is to sync the data, then recreate the subscription, specifying that it does not need to be initialized.

Bug: MS KB967192
http://support.microsoft.com/kb/967192/EN-US

Fix:
http://technet.microsoft.com/en-us/library/ms151705.aspx

Essentially, what you need to do is drop the subscription, sync the data, then recreate the subscription, making sure to uncheck "Initialize" (if using SSMS).

[Backups] Verify your backups physically exist

We've been using Rodney Landrum's SSIS package to monitor our environment (As Seen In SQL Server Mag). We're running the old version, which doesn't deal AT ALL with servers not being available. (I assume the latest version does, but haven't had time to check.)

And I came across an issue recently - missing backups. Because of the various retentions set via our backup jobs, we would occasionally have a file vanish. Eek!

So, cue this code. It'll grab the most-recent backup for each server/database, and make sure the file physically exists. It doesn't check the veracity of the backup, just that there's a file there. It also uses xp_fileexists, an undocumented (and therefore it can change - though it's been the same since SQL Server 2000) SP.

If you don't use Rodney's code, you can still use this, but it'll be a _little_ more work. Take the below code, have it run on each machine and dump into a central table (I'll leave those details up to you), then run the second set of code against it.

SELECT server_name, 
  database_name, 
  physical_device_name, 
  backup_start_date,
  'FULL' as backup_type 
from msdb.dbo.backupmediafamily 
inner join msdb.dbo.backupset 
 on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14
and physical_device_name NOT LIKE 'VDI_%'
and physical_device_name like '%BAK' --or whatever your backups are named.

And here's the full code:

SET NOCOUNT ON
USE DBA_Rep
if object_id('tempdb..#backup_list') is not null
    drop table #backup_list;
CREATE TABLE #backup_list (id int IDENTITY, server sysname, database_name sysname, physical_device_name VARCHAR(520), backup_start_date DATETIME, file_exists BIT)
DECLARE @minid INT, @maxid int
DECLARE @does_it_exist INT
DECLARE @filename VARCHAR(500)

--using dba_rep's copy that Rodney Landrum's SSIS code pulls, get a list of the most recent backup for each db in past 2 weeks
INSERT INTO #backup_list
        ( server ,
          database_name ,
          physical_device_name,
          backup_start_date 
        )
SELECT Backup_History.server, 
  Backup_History.database_name, 
  Backup_History.physical_device_name, 
  Backup_History.backup_start_date 
FROM Backup_History
INNER JOIN 
(
SELECT server, database_name, 
 MAX(backup_start_date) AS max_start_date
FROM Backup_History 
WHERE backup_type <>'LOG' 
 AND backup_start_date > GETDATE()-14
GROUP BY server, database_name
)most_recent
ON most_recent.SERVER = Backup_History.Server
AND most_recent.database_name = Backup_History.database_name
AND most_recent.max_start_date = Backup_History.backup_start_date
AND Backup_History.physical_device_name NOT LIKE 'SQLsafe%'
AND Backup_History.backup_type <> 'LOG'

--Fixing the names of local backups so that we can get them over the network.
UPDATE #backup_list 
SET physical_device_name = REPLACE(physical_device_name,LEFT(physical_device_name,2), '\\' + LTRIM(RTRIM(server)) + '\' + LEFT(physical_device_name,1) + '$')
WHERE physical_device_name LIKE '%:%'

SELECT @minid = MIN(id) , @maxid = MAX(id) FROM #backup_list

--Walk the list, checking each file and updating the table
WHILE @minid < @maxid
BEGIN
 SET @does_it_exist = 0

 SELECT @filename = physical_device_name 
 FROM #backup_list 
 WHERE id = @minid

 EXEC Master.dbo.xp_fileexist @filename, @does_it_exist OUTPUT
 UPDATE #backup_list 
 SET file_exists = @does_it_exist 
 WHERE id = @minid

 IF @minid % 10 = 0 PRINT @minid

 SET @minid = @minid+1 
END

SELECT * FROM #backup_list WHERE file_exists = 0