Use RAISERROR to return results in SSMS immediately

This courtesy of a coworker (JS), who found the idea online somewhere, possibly from Savjani here (http://blogs.msdn.com/b/sqlserverfaq/archive/2009/10/01/behavior-of-with-nowait-option-with-raiserror-in-sql-server.aspx)

If you run a while loop from SSMS, the results won't come back immediately, even if you're using a PRINT.  This is a clever way to return it immediately.

DECLARE @startdate DATETIME,

            @now DATETIME,

            @msg NVARCHAR(50)

SET @now = GETDATE()         

SET @startdate = GETDATE()- 60

WHILE (@startdate <= @now)

BEGIN

SET @msg = (select CONVERT(VARCHAR(10), @startdate, 101))

RAISERROR (@msg, 0, 1) WITH NOWAIT

SET @startdate = @startdate + 1

END

Using OUTPUT to set up foreign keys' data

So, I was looking at a process that inserts records into table A (one at a time), then uses SCOPE_IDENTITY() from each insert to get the ID and uses that for the insert into table B, in order to provide a cross-reference tabled (foreign key).  

Obviously, there's a better way - OUTPUT, usable since SQL Server 2005.

An example.  We populate “blah”, then use the values from that to insert into “blah2”, with a foreign key ID of the first table.  

CREATE TABLE blah (id INT IDENTITY, logon_name VARCHAR(50))

CREATE TABLE blah2 (id INT IDENTITY, blahID int, dn VARCHAR(200))

INSERT INTO blah

      SELECT TOP 10 name FROM sysusers

INSERT INTO blah

      SELECT TOP 10 name FROM sysusers

DELETE FROM blah

--incrementing to show different ID values.

DECLARE @insertedlist TABLE (id int, the_logonname VARCHAR(50))

INSERT INTO blah

output inserted.id, inserted.logon_name INTO @insertedlist

SELECT TOP 10 name

FROM sysusers ORDER BY createdate

INSERT INTO blah2 (blahid, dn)

SELECT insertedlist.id, sysusers.uid

FROM @insertedlist insertedlist INNER JOIN sysusers

ON insertedlist.the_logonname = sysusers.name

SELECT * FROM blah

SELECT * FROM blah2

[Free Space] finding what filegroups your data is saved onto.

Using this to tell me where my indexes are, but more importantly - where my data is. What filegroup/file. It produces multiple rows when there are multiple files for a filegroup; need to code a better way. Note that all the WHERE clauses are optional; we couldn't figure out where our data was until we dropped them. 10 gig in a heap table, and 20gb in a service broker table.

SELECT

o.name AS Table_Name ,

i.NAME AS Index_Name ,

CASE i.type

WHEN 0 THEN 'Heap'

WHEN 1 THEN 'C'

WHEN 2 THEN 'NC'

ELSE '?' END AS [Type],

p.rows AS [#Records] ,

a.total_pages * 8 / 1024 AS [Reserved(mb)] ,

a.used_pages * 8 / 1024 AS [Used(mb)] ,

s.user_seeks ,

s.user_scans ,

s.user_lookups,

fg.name,

f.name,

f.physical_name

FROM sys.indexes AS i

INNER JOIN sys.partitions AS p ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID

INNER JOIN sys.allocation_units AS a ON ( a.type = 2

AND p.partition_id = a.container_id

)

OR ( ( a.type = 1

OR a.type = 3

)

AND p.hobt_id = a.container_id

)

INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S ON S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = DB_ID(DB_NAME())

AND o.type_desc NOT IN ( 'SYSTEM_TABLE', 'INTERNAL_TABLE' ) -- No system tables!

LEFT OUTER JOIN sys.database_files f ON f.data_space_id = a.data_space_id

LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = a.data_space_id

--AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100

WHERE OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1

--AND i.TYPE_DESC <> 'HEAP'

AND i.type <> 1 -- clustered index

ORDER BY o.NAME ,

i.name

[Index] Size, usage, and location of your indexes

Updated my old code, since we were trying to figure out what indexes needed to get moved to the secondary (index) filegroup. You can even filter based off the usage (commented out below)
Standard disclaimer applies. Select the contents of the post, then copy/paste.

Note that there can be dupes - this is by design.  If a table spans multiple files, you'll see multiple rows returned with everything identical except for the filename.

SELECT
        o.name AS Table_Name ,
        i.NAME AS Index_Name ,
--        i.type_desc,
  CASE i.type
   WHEN 0 THEN 'Heap'
   WHEN 1 THEN 'C'
   WHEN 2 THEN 'NC'
   ELSE '?' END AS [Type],

   p.partition_number,

   p.rows AS [#Records] ,
        a.total_pages * 8 / 1024 AS [Reserved(mb)] ,
        a.used_pages * 8 / 1024 AS [Used(mb)] ,
        s.user_seeks ,
        s.user_scans ,
        s.user_lookups,
        fg.name, 
        f.name,
        f.physical_name
FROM    sys.indexes AS i
        INNER JOIN sys.partitions AS p 
   ON i.object_id = p.object_id
            AND i.index_id = p.index_id
        INNER JOIN SYS.OBJECTS O 
   ON I.OBJECT_ID = O.OBJECT_ID
        INNER JOIN sys.allocation_units AS a 
   ON ( a.type = 2
    AND p.partition_id = a.container_id
               )
   OR 
    ( ( a.type = 1 OR a.type = 3)
    AND p.hobt_id = a.container_id)
        INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S 
   ON S.OBJECT_ID = I.OBJECT_ID
   AND I.INDEX_ID = S.INDEX_ID
   AND DATABASE_ID = DB_ID(DB_NAME())
   AND o.type_desc NOT IN ( 'SYSTEM_TABLE', 'INTERNAL_TABLE' ) -- No system tables!
  LEFT OUTER JOIN sys.database_files f 
   ON f.data_space_id = a.data_space_id
  LEFT OUTER JOIN sys.filegroups fg 
   ON fg.data_space_id = a.data_space_id
WHERE   OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
        AND i.TYPE_DESC <> 'HEAP'
        AND i.type <> 1 -- clustered index
--AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100 

ORDER BY o.NAME ,
        i.name

[ETL] Importing UNIX files

Coworker had a problem importing a data file - one column per line, but was running into problems and had to invoke Code Page 65001 in his SSIS script. Took forever to fix, and forever to process. Looked at the file - UNIX.

So, the easy T-SQL way to do it:

CREATE TABLE deleteme (resultant VARCHAR(MAX))



BULK INSERT deleteme

   FROM '\\server\path\file.rpt'

   WITH 

      (

         ROWTERMINATOR = '0x0a'

      )

[Tuning] Disk usage over time, checking deltas

Not sure where this came from, if I wrote it or someone else did. Practically, it looks before and after to tell you which files are getting used. The commented out bit was because I was investigating WRITELOG delays.

--as always, drag-copy to get the full text.

DECLARE @compare TABLE (NAME sysname, type_desc varchar(10), physical_name VARCHAR(200), sample_ms BIGINT, 

num_of_bytes_written BIGINT, num_of_bytes_read BIGINT, size_in_gb INT)

DECLARE @compare2 TABLE (NAME sysname, type_desc varchar(10), physical_name VARCHAR(200), sample_ms BIGINT, 

num_of_bytes_written BIGINT, num_of_bytes_read BIGINT, size_in_gb INT)



INSERT INTO @compare 

SELECT 

  master_files.NAME, 

  master_files.type_desc,

  master_files.physical_name, 

  vfs.sample_ms,

  vfs.num_of_bytes_written, 

  num_of_bytes_read, 

  size_on_disk_bytes/1024/1024/1024 AS Size_in_GB

FROM sys.dm_io_virtual_file_stats(null, null) vfs

INNER JOIN MASTER.sys.master_files 

 ON vfs.[file_id] = master_files.[file_id] 

 AND vfs.database_id = master_files.database_id

 --WHERE type_desc = 'log'

ORDER BY (num_of_bytes_read + num_of_bytes_written) DESC



WAITFOR DELAY '00:00:15'



INSERT INTO @compare2

SELECT 

  master_files.NAME, 

  master_files.type_desc,

  master_files.physical_name, 

  vfs.sample_ms,

  vfs.num_of_bytes_written, 

  num_of_bytes_read, 

  size_on_disk_bytes/1024/1024/1024 AS Size_in_GB

FROM sys.dm_io_virtual_file_stats(null, null) vfs

INNER JOIN MASTER.sys.master_files 

 ON vfs.[file_id] = master_files.[file_id] 

 AND vfs.database_id = master_files.database_id

-- WHERE type_desc = 'log'

ORDER BY (num_of_bytes_read + num_of_bytes_written) DESC





SELECT old.NAME, old.physical_name, new.sample_ms - old.sample_ms AS time_elapsed,

new.num_of_bytes_written - old.num_of_bytes_written AS delta_num_bytes_written,

new.num_of_bytes_read - old.num_of_bytes_read AS delta_num_bytes_read, old.size_in_gb

FROM @compare old INNER JOIN @compare2 new ON old.physical_name = new.physical_name

ORDER BY (new.num_of_bytes_written - old.num_of_bytes_written) DESC

[Trick] get midnight fast

I've used this in the past, but technically it's sloppy.

select convert(char(8),getdate(),112)

So these days I'm using this:

select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)  

[Russian] Meat Perushky / Perushki / Piroshki / Piroshki

The classic Russian meat pie. Note similarities with Potato Perushki (she spells them piroshky), but definitely not the same recipes. This will take 3-4 hours total, but with some pauses. This version is meat, baked. You can fry them but they are very greasy. The potato variant excels when fried, so bake this and fry that.

Dough:
First, combine:
2 pk dry yeast (Active Dry is fine, Rapid Rise is not)
1.5-2.0 cups milk, at 95-100 degrees (too hot is BAD, you want roughly blood-temp)
1 tbl sugar
1 tbl flour
3/4 stick of butter or 1.5 stick margarine, room temp or melted.
Let sit 10-15 minutes, until bubbly or foamy or otherwise alive.

Beat together:
2 whole eggs
1 egg yolk (save the white for the egg wash)
1.5 - 2.0 teaspoon salt (sorry, last time I made it it was flat, and that was either 1 or 1.5 tsp salt)

Sift 2 lb flour.

Mix yeast mixture, eggs, and 1 lb 13 oz of flour in mixer. Use the bread hook, low speed. Once it all comes together, add flour until slightly sticky (I used 1 cup).

Let rise until doubled.


Meat:
Cut a 2-3 lb chuck roast into several hunks, trimming fat. Cover with water. Bring to boil then turn down to simmer for 60-90 minutes (this is to tenderize and break down). The time depends on the quality of the meat - better takes less time. To tell if done, poke with a knife: should fall off knife easily.

Once done, we grind it. On my kitchenaid stand mixer, I use the small grinding wheel, set the mixer on 3, and grind. Note that you can't just buy ground meat and cook it - the texture is totally different.

Onions:
Dice 2-3 medium onions. Simmer in 2-3 tbl oil. On our electric stove we cook on 5-6 (out of 10), covereed, then turn down to 3.5 - 4.0. Stir occasionally - you're trying to clarify and brown. Cook about 60 minutes.

Now, combine the meat and onions. Add 1-2 tsp salt (very little), 1-2 tsp black pepper, and 1/2 cup (varies) of the broth from the meat. You want it to stick together a bit - when you scoop it you want it to clump.

Making the perushky:
For each perushky, pull off 24-26 grams of dough.
Two ways to make:
1) stretch out by hand
2) roll and cut
My grandmother stretches it out to a 3.25" circle, thinner at the edges. This is a little heavy on the bread for my taste, so I roll out with a rolling pin until about 3.5 - 4.0 inches around, then use the top of a 3.25" restaurant tumbler (do a Google image search - about 2.75 inches at the bottom and straight sides) to cut out a circle. When I weight it, it comes out to about 18g.

Now take this circle of dough, stretch it out a little, and immediately drop in a dollop of meat. I use a #70 disher - Baba uses a heaping tablespoon. Put this in the middle, then bring the sides together, forming a half-circle. Pinch down repeatedly to seal, squeezing hard with your fingers. Now flatten - put perpendicular to table, round side facing down, and push. It'll wind up as a small round object. Check for seam splits and close them.

Now do this about 100 times (takes about an hour). I wound up with extra meat & onion - add rice to this and you've got the filling for cabbage rolls (see recipe on this blog).

Now take the egg white you saved, whisk for 10-20 seconds with a fork. Using a brush, put this egg wash on the top - enhances browning. Bake at 350 for 20-25 minutes. Let cool and enjoy!

Since I didn't have enough baking trays, I made a tray, put on egg wash (see below), put in the oven, and started the next tray. Overall it took about an hour to make them. Enjoy!

[Russian] Olga's Pork Stew

(yes, there's a lot of commentary in this recipe. You've never made this before, so you don't know how long or why things are done a certain way).

2 lb boneless country-style pork ribs
5-6 medium russet potatoes
1 large onion
2 medium carrots (can use baby carrots, though they take longer to cook than sliced carrots)
2-3 cups frozen/fresh green beans, cut or frenched. Or, one 1 can cut green beans
1-2 cups water

First, chop onion and simmer with 2-3 tablespoons butter. Let simmer until golden brown or mahogany. On my electric stove we put it on a 5 (out of 10) on a small burner and stir frequently. You're not trying to burn or blacken them. Let this cook while you prep everything else. Once brown turn off, though this usually happens about the time we're ready for them below.

Cut the meat into bite sized pieces, removing the big hunks of fat.

Fry the meat in a regular size pot (under 8 quarts) with 1 tablespoon oil, until light brown. You want color and browning to add flavor. We cook this on a 4-5 on the stove.

Add water so that the meat is just barely covered (let it at least hit the halfway point on the meat, better if it's just covered) and let simmer 30 minutes. If you're losing too much water, add some more - you're using the simmering to break down and tenderize the meat, as well as to put out flavor that'll go into the veggies.

While it's simmering, peel potatoes and cut into cubes (on a good-sized russet I usually quarter and slice about .5 inches thick). Peel & slice the carrots. Thaw the green beans.

After the 30 minutes, add the potatoes, onions, carrots, and fresh/frozen beans (if from a can, add when the potatoes have finished). Simmer and stir occasionally - the potatoes will break down, that's fine.

Once the potatoes are cooked, stir in 1 8-oz can of tomato sauce. At this point, cook 10-20 minutes further, stirring every 2-3 minutes. The potatoes will break down, and that's fine - that's our thickener that turns this into a stew.

After the 10-20 minutes, see if all the vegetables are cooked. Once they are, serve. If

[Cooking] Gumbo! Mama Saucier's Super Gumbo

My wife's family's recipe. And it's pronounced Sew-shay (sew as in needle and thread).

Yes, not baking. That's coming after this one.

Mama Saucier's Super Gumbo
Bring 8 boneless, skinless chicken breasts to a boil in a fair-sized (8-quart, fill halfway with water) pot. When done set aside.

Mix in a bowl:
1 Cup of Tony Chachere's Instant Roux Mix
2 Cans room temperature chicken broth (not from the above pot, just normal size cans of low-sodium chicken broth)

In a Very Large Stock Pot (3-4 gallons+)
Melt 2 sticks of butter
Saute:
1 "head" (stalk bunch) of Celery, sliced, just the main green part of the stalk. If you don't like the taste of celery, slice fine.
1.5 large yellow/1015 onions (diced)
2 Green Bell Pepper (diced)
3-4 minced garlic pods (aka toes or cloves - not the entire head!), can use from jar.

Once sauteed to softened, add & stir in:
Roux
Chicken broth from boiled chicken
3 cans of cut up/diced/mashed tomatoes (regular 14oz cans)

Bring to a boil then add:
Chicken breasts (don't bother cutting up)
1 tblsp Tony Chachere's Cajun Seasoning (add more if needed in 20-30 minutes. If too spicy-hot, add more canned tomatoes or tomato juice)
3 16-oz packages of frozen cut okra (3 pounds total, yes)

Let slowly simmer 3-4 hours (note below instructions). Stir constantly.
After the first 2 hours, add:
4-5 lbs smoked tube beef (sausage) or pork sausage cut into bite-sized pieces. These WILL swell, so slice more thinly than you would if panfrying or whatnot - maybe .25 inches, no more than .5 inches thick.

If things stick to the bottom, LEAVE THEM. It's burned by the time you notice and scraping them off the bottom will just ruin the gumbo - it'll be a burned-tasting mess. So leave it and you're fine (well, until cleanup).

10 minutes before serving (about the time to put the bread in the oven) bring to a boil (stir constantly at this point!) and put in 3 lbs shrimp. Turn down to slow boil, since you can easily scorch the entire pot at this point.

Serve over rice.

Note that if you want recognizable meat or veggies, add additional amounts along with the shrimp. Overall, the chicken will turn into little white strings, the veggies dissolve - basically, the only recognizable bits in the gumbo will be the sausage and shrimp, and if you added the sausage at the beginning, that's dissolved, too.

Any questions?

[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            

[Tools] Convert PDF to text

Courtesy of the interwebs:

"That depends. If it is an image in the PDF, you're out of luck.
Otherwise (if it is a PDF containing text) you can do the following, all in one line, assuming osx has the strings command and a perl interpreter:"

strings filename.pdf | perl -ne '$line=$_; $s=$line; $w=""; while ($s =~ m/(\w+)(.*)/){print $line if ($w eq $1); $w=$1; $s=$2;}'

[Tips] Create a comma-separated list in one query using COALESCE

Clever, clever. Wish I could take credit for it.

-----------------------------------
--Creating a comma-separated list--
-----------------------------------
 
DECLARE @EmployeeList varchar(100)
 
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') 
+ CAST(id AS varchar(15))
FROM temp_td_200704

SELECT @EmployeeList

[SSAS] Quick notes on logging queries run against the cube

There's a lot of details out there about OlapQueryLog, but here are the 3 things I ran into setting it up:


Tips and tricks from setting this up:

1. Do it on whatever server hosts the cube, since it will determine what version of the SQL driver it needs.
   
2. Event Viewer will tell you the errors you’re having, be they permissions, bad version of the SQL driver, etc.
   
3. The key settings:
   
   • Log\QueryLog\CreateQueryLogTable = true
     
   • QueryLogConnectionString (click and set it)
     
   • QueryLogSampling is "every X queries, save the query to table". So the default means every 10th query gets saved.
     

[Trick] Eliminating "arithmetic overflow error" that aren't in the result set.

Ran into a problem where the SP would always kick out the standard overflow message:

Arithmetic overflow error converting numeric to data type varchar

As it turns out, the problem was data that exists in the data set, but not in the results set - the WHERE clause eliminated it. What made it even harder to troubleshoot was that because that was the issue, we could duplicate it by running the SP - but not by copying and pasting the code. No matter what options you used - ARITHABORT, ANSI_WARNINGs, etc, it would run successfully, skipping over the bad record (since it was excluded via the WHERE clause).

As is pointed out in "Defensive Database Programming with SQL Server" by Alex Kuznetsov (WELL worth getting, and Red-gate offers a free PDF), you can't guarantee the order things get evaluated in.

So one way around it: take your query that is failing in the SELECT clause. Find a unique key that you can pull from your data set. Copy/paste the entirety of the FROM/WHERE, and SELECT only this key into a temp table. Now go to your full query and INNER JOIN this temp table.

Another potential way around it: remove everything from the where clause and keep it in the JOINs.

[Code] Stripping low-ascii out of a table, slow way

I'm working on a CLR to do this considerably faster, but here's what I have for now. The purpose of this is to remove dirty data from an upstream feed. We can't make XML with it - the code that creates the XML chokes on it. Note that this isn't terribly fast, because we're brute-forcing it and having to loop through (31 * number-of-fields) times. The CLR should do it in one pass per field.

--CREATE PROCEDURE [dbo].[strip_low_ascii]  
--as  
DECLARE @columns TABLE (id INT IDENTITY, column_name sysname)  
DECLARE @odd_ascii CHAR(1),
  @ascii_value int,
  @column_start smallint, 
  @column_end SMALLINT, 
  @sql NVARCHAR(4000), 
  @field_name sysname, --to allow us to loop through all fields  
  @table_schema sysname, 
  @table_name sysname
SET @ascii_value = 31   --31 aka 0x1F.  Space is 32/0x20.  0-30 is our "low-ascii" range
SET @table_schema = 'dbo'
SET @table_name = 'yourtablename'

CREATE TABLE #list_of_id (id bigint primary key)
  
INSERT INTO @columns  
      SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS   
      WHERE TABLE_SCHEMA = @table_schema 
  AND TABLE_NAME = @table_name
  AND DATA_TYPE IN ('char','varchar','nchar','nvarchar')  
  
SELECT @column_start = MIN(id), @column_end = MAX(id) FROM @columns  
  
WHILE @ascii_value >= 0 --look for all low ascii  
BEGIN  
      SET @odd_ascii = NULL  
      SET @column_start = 1  
      SELECT @odd_ascii = CHAR(@ascii_value)  
  
      WHILE @column_start <= @column_end  
      BEGIN  
   TRUNCATE TABLE #list_of_id
            SET @field_name = NULL  
            SET @sql = NULL   
            SELECT @field_name = column_name FROM @columns WHERE id = @column_start  
            SELECT @sql = 'insert into #list_of_id 
                  SELECT id FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + '
                  WHERE insert_datetime >=CONVERT(CHAR(8),GETDATE(),112)  
                  AND ' + @field_name + ' LIKE ''%'' +  @oddascii  + ''%''  
      IF @@rowcount >0   --( select count from @list_of_id )
            update ' + @table_name + ' set ' + @field_name + ' =  REPLACE(' + @field_name + ',  @oddascii, '''')   
            WHERE insert_datetime >=CONVERT(CHAR(8),GETDATE(),112)  
   and id in (select id from #list_of_id)
            AND ' + @field_name + ' LIKE ''%'' + @oddascii + ''%'''  
            
  
--PRINT @sql  
  
            EXECUTE sp_executesql @sql, N'@oddascii char(1)', @oddascii = @odd_ascii  
  
            SET @column_start = @column_start + 1  
      END  
  
      SET @ascii_value = @ascii_value-1  
END  
  
  
  

[Replication] Orphaned distrubution agent after SQL Agent crash

I need to figure out where THIS particular error is stored in the logs, since none of my normal processes or alerts flagged this...

"Error messages:
The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Di"

How'd I find it? The big red X in the Replication Monitor, for the server whose SQL Agent crashed earlier today. Nothing in the Agent Log or the SQL Server Log. So it's buried somewhere in sysjobhistory

I believe the extant process was running properly, but why chance it?

Login to server, find "qrdrsvc.exe" in Task Manager. Kill process. Run the Queue Reader job. On my server, it's [MYServerName].6 (?), and was Between Retries. Also, the job category was: "REPL-QueueReader"

And you didn't think you'd learn anything new today...

[SQL] BULK INSERT and adding an identity

So I have a file with two columns, and I need to import it into a table with an IDENTITY column. Here's the easy way:

CREATE TABLE blah (narf VARCHAR(4000), poit VARCHAR(4000))

BULK INSERT blah
from "c:\files\blah.txt"

ALTER TABLE blah
ADD ID int IDENTITY(1,1) NOT NULL

[Replication] Orphaned agents after Agent crash.

Had SQL Server agent crash on me, and then my home-grown replication monitoring gave me the following error via email (interestingly enough, the Alerts we created didn't fire):

Agent 'MYSERVERNAME-MYDatabase-MyPublication-Servername-525' is

retrying after an error. 14 retries attempted. See agent 

job history in the Jobs folder for more details.

Since that's the error you'd see in the Replication Monitor (start->run->"sqlmonitor"), I went and looked at the agent which showed our now-at-16 retries:

[...]

2011-01-27 22:46:17.596 Agent message code 21036. Another distribution agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent.

Interesting. My guess is that since the SQL Agent crashed, it left behind agents that are still running their original orders, blocking the new agents from starting. Well, let's look at what's connected.

SELECT * FROM master..sysprocesses 

WHERE program_name LIKE @@servername + '%' 

	AND login_time < CONVERT(CHAR(8),GETDATE(),112)--before today

	AND hostname = @@servername 

Bingo. Two SPIDs, both with program names like 'MYSERVERNAME-MYDatabase-MyPublication-Servername' - and which match my original email above.

Kill the two SPIDs, and the next go-round the distribution agent spins up successfully.

Rule 5

Rule 5: Software scales faster than hardware.

(Case in point: had a service broker issue. Several fixes were made, but changing the code increased performance substantially with an overall lower CPU)

CHAR() vs CHAR()

Another of the "I forget the tool so let's put it in the blog" posts.

Obviously there's the datatype. But you can also create high-ascii & low-ascii symbols that might show up as a tiny square, or (at least in SSMS) not show up at all.

A map:
http://web.cs.mun.ca/~michael/c/ascii-table.html
This also explains some of the other code you may have seen for BULK INSERT, such as 0x0a (which is "newline" or "linefeed", the Unix "return"; Windows uses CR/LF). It can also be used to clean up code. Instead of something like

+ ''' +

you could use

+ char(39) + 

The value in CHAR has to be a number. Which is, naturally, both good and bad.

more examples:
select char(20) --random low-ascii
select char(32) --space

The Rules: 0-4

Here are my current DBA rules. Yours will differ, but these hold me in good stead.

Rule 0: Verify your backups. Obviously I assume you are making backups - you _are_, right?
Rule 1: UPDATE STATISTICS. That will frequently save your hide.
Rule 2: Data/Log always on separate drives. Know what happens if your data and log are on the same drive, and it fills up? No? You don't want to.
Rule 3: Complexity is the enemy. Not quite KISS, but definitely related.
Rule 4: SQL Sentry's "Disable Until" is a godsend. Make sure to use that instead of simply disabling the job. And double-check the time it restarts, since SQL Sentry adds 1 day by default - not useful if you're trying to disable something for 2-3 hours.

Varchar(n) vs Varchar(Max)

Two questions:
1) You have a 1tb table with 1m rows, and need to change a field from varchar(5) to varchar(10). How long does it take, and how much log space is used?
2) You have a 1tb table with 1m rows, and need to change a field from varchar(5) to varchar(MAX). How long does it take, and how much log space is used?


The answer for (1) is simple: instantly, and none. Fortunately, it's a metadata operation, and since it knows everything already in the database must be the right size, no work needs be done.
For (2), I can't tell you - we rolled back after 90 minutes (it then took another 3+ hours to roll back), and it had consumed 200+gb of log space. On the plus side, it's table-partitioned, so we'll upgrade it that way.