The Baking DBA - select * from flour

[Table Partitioning] what is your lock escalation?

2012-01-30T13:59:00.002-06:00

Bare bones query. This will give you the lock escalation for your partitioned tables. In our experience we've had good luck with AUTO (which is _not_ the default!), but the reason it's not the default is because some MS customers had deadlock issues with it enabled. For us, it prevents issues with queries blocking inserts.

Also, let me put a suggestion in your ear: don't go with just one table for inserts & queries. Create a partitioned table to catch live data, keep a week's worth of data. Create a mechanism to copy that into your history (what gets queried) table. Make the history table AUTO, and experiment with your live table. Compress the history. Make a view between the two if you need. Like much on this blog, let my pain be your gain.

SELECT distinct tables.name,
lock_escalation_desc
FROM sys.partitions
inner join sys.tables
on tables.object_id = partitions.object_id
and partition_number >1
order by name

[Code] Using EXECUTE AS with a trigger to capture changes when a user doesn't have permissions.

2012-01-25T15:03:00.000-06:00

Had to build this to keep track of changes on certain tables in our environment. Due to limited permissions, we wound up with people hitting the table but the trigger not working right, because they hadn't been granted permissions. So the obvious solution is to grant permissions to both tables to a group, then add people into that group. Not an option for this particular scenario. Hence, my code.

Note that THIS WILL NOT WORK across databases, unless it’smarked as TRUSTWORTHY. Plannedfunctionality, not a bug. Within a database it works.

New code bolded. Icreated a user Change_User, a change-tracking table named change_mytable and gave Change_User select/insert permissions ONLY into change_mytable.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

alter trigger [dbo].[mytrigger]

on [dbo].[mytable]with execute as 'change_user'

after update

BEGIN

SET NOCOUNT ON

declare @current_user nvarchar(128) --suser_sname()is nvarchar(128)

execute as caller --caller is the original person running code

set @current_user = SUSER_SNAME() --get the nameof the user

--select @current_user --returnsmy name when I run it

revert --go back to the EXECUTE AS user, ch_user

--Audit capture

if exists (select name from sysobjects where name = 'change_mytable')

begin

--print suser_sname() --returnschange_user

insert into change_mytable(

ID, name, updated_date,change_login, dml_action)

SELECT

ID,NAME, getdate(), @current_user, 'Update'

FROMDeleted

end

END

Adding locations to SSMS "Open File"

2012-01-20T14:51:00.004-06:00

Been looking like this for years. Who knew, the right google and there it is. I take no credit for this, simply sticking it in my blog so that I can find it next time.

http://www.sqlservercentral.com/Forums/Topic907188-391-1.aspx
Todd Engen

Here's where you'd find that for SSMS with 2005.

\HKCU\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\Open Find\Places\UserDefinedPlaces

Add subkeys Place0,Place1,Place(n)

Under each subkey add two REG_SZ values

Name = "Shortcut Name"
Path = "Folder location"

[Network] Specified network name is no longer available

2012-01-17T12:45:00.001-06:00

We've seen this in our environment for a while. Tried moving backup times around, that helped some but not in every case. TCP Chimney Offload didn't seem to be our issue either, though it has been for others.

Got this nugget courtesy of kgerde, which seems to be a near-magic-bullet for us.
http://www.sqlservercentral.com/Forums/Topic768429-391-1.aspx

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters
Create a New DWORD value with the name: SessTimeout
set the value: 360 keep it Hexadecimal
(This value might not work for your backup but it was high enough for mine. If this doesn't work increase the value and try again.)

Use RAISERROR to return results in SSMS immediately

2011-11-14T14:21:00.001-06:00

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

2011-09-27T15:23:00.004-05:00

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 foreignkey 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 10name FROM sysusers

INSERT INTO blah

SELECT TOP 10name 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_nameINTO @insertedlist

SELECT TOP 10 name

FROM sysusers ORDER BY createdate

INSERT INTO blah2 (blahid, dn)

SELECT insertedlist.id, sysusers.uid

FROM @insertedlist insertedlist INNERJOIN sysusers

ON insertedlist.the_logonname= sysusers.name

SELECT * FROM blah

SELECT* FROM blah2

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

2011-09-08T16:44:00.002-05:00

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

2011-09-01T14:01:00.006-05:00

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.

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

[ETL] Importing UNIX files

2011-08-16T14:41:00.002-05:00

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

2011-08-15T09:07:00.003-05:00

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

2011-08-05T09:43:00.003-05:00

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

2011-07-31T00:16:00.004-05:00

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

2011-07-30T23:56:00.002-05:00

(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

2011-07-30T23:30:00.005-05:00

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

2011-06-23T10:52:00.002-05:00

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

2011-05-31T15:50:00.002-05:00

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

2011-05-26T16:34:00.004-05:00

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)

2011-04-20T13:48:00.003-05:00

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.

2011-04-13T15:16:00.002-05:00

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

2011-04-07T11:30:00.004-05:00


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

2011-04-06T16:30:00.005-05:00

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

2011-04-06T10:32:00.002-05:00

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

2011-03-22T15:25:00.002-05:00

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

2011-03-10T15:22:00.002-06:00

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

2011-03-03T10:24:00.001-06:00

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

2011-02-23T17:01:00.001-06:00

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

2011-02-18T15:16:00.002-06:00

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:

Do it on whatever server hosts the cube, since it will determine what version of the SQL driver it needs.
Event Viewer will tell you the errors you’re having, be they permissions, bad version of the SQL driver, etc.
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.

2011-02-17T15:05:00.004-06:00

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

2011-02-17T10:53:00.005-06:00

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

2011-01-27T18:31:00.006-06:00

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

2011-01-27T18:09:00.002-06:00

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.

2011-01-27T16:56:00.003-06:00

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

2011-01-11T10:54:00.001-06:00

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()

2011-01-11T10:05:00.002-06:00

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

2011-01-03T10:51:00.002-06:00

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)

2011-01-03T10:13:00.002-06:00

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.

Texas Hill Country BBQ Brisket (yes, that's redundant)

2010-12-07T15:55:00.004-06:00

Bare bones, basic Texas BBQ. Have done it twice so far with surprisingly good results.

One whole brisket, in cryo-vac packaging (aka a "packer-cut brisket"). Weighs about 10 pounts.
One smoker. I cheat and use an electic smoker (Brinkman, $70 at Home Depot).
Wired-probe thermometer. You'll put the probe in the brisket, and the actual temperature will be shown on the other part which will be NEAR the smoker (not on).
One cup yellow mustard
Half-cup kosher salt
Half-cup black pepper
1-8 pounds Oak chunks (not chips). Can use Hickory, Mesquite, Apple, Cherry.

Steps:

Soak 1 pound wood in water (I put it in a ziplock-style bag). You want it to soak between 15 minutes and one hour.
Pull brisket out of bag, drain off juices, slice the "fat cap" (one side will have a substantial amount of fat) with a knife, but not all the way to the actual meat.
Cut brisket in half - a whole one won't fit on my smoker. Make sure the two pieces are roughly of equal weight, since they're both going to cook the same amount of time. As you cut it in half you'll see that there are two muscles. And, just to make it fun, the grain on one is perpendicular to the other.
Coat each piece in mustard. A quarter-cup is probably plenty for each, but you want everything coated.
Mix salt+pepper together. "Dalmatian Dust" or "Dalmatian Rub". Apply liberally to both pieces of brisket.
Drain wood chunks, put in smoker. Fill water bowl with water. Put in smoker. Put brisket on both top & bottom grates. Put thermometer in bottom piece. Put lid on. Plug in. Apply roughly one pound of moist wood per hour until the meat is very dark in color. Remove once temperature hits 185. Wait 20 minutes. Separate the two cuts of meat. Determine where the grain is (the way the long muscle strands run), then slice perpendicular to grain. Eat.

[semantics] Why I hate ISNUMERIC

2010-11-04T14:37:00.003-05:00

So, ISNUMERIC is simple, right? Put in numbers, and it tells you whether it is.
Except it has very specific exceptions you may not know about.

Any of these will come back with ISNUMERIC = 1:

0D123
123D50
123E50
$,,1,,.1

Currency doesn't count (and that's ALL currency symbols), D and E don't count in certain circumstances, commas and periods don't count.

Instead, use something like this:


if (select PATINDEX('%[^0-9.]%','$00.01')) = 0  print 'numeric'

[Replication] more replication trouble tracking

2010-11-04T14:28:00.000-05:00


--get list of the possible databases by querying the publisher

SELECT * FROM distribution.dbo.MSpublisher_databases

--Now figure out what the article number is
--the database name there is the database that the publication is in.
Replication_Master..sp_helparticle @publication = 'User_Profiles'


--Now that you have the publisher database ID (step 1) and the article id (step 2)
--get the list of commands.
EXEC distribution..sp_browsereplcmds @publisher_database_id = 2, @article_id = 369


--And if you're really lucky, in sqlmonitor you'll get the following:
--(Transaction sequence number: 0x00018A0500009072002A00000000, Command ID: 1)
--in which case...

EXEC distribution..sp_browsereplcmds @publisher_database_id = 2, @article_id = 369, 
@xact_seqno_start = '0x00018A0500009072002A00000000', @xact_seqno_end = '0x00018A0500009072002A00000000'
--(set both start and end to the same value, the one in the error message)

[Code] IF EXISTS for procedures using INFORMATION_SCHEMA

2010-10-28T13:11:00.002-05:00

Adam Machanic's code does two things that I love, but don't think to do.




IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'yourprocname')

      EXEC ('CREATE PROC dbo.yourprocname AS SELECT ''stub version, to be replaced''')

GO

It uses the INFORMATION_SCHEMA tables. These are like the system tables, but are more portable (every database vendor has them and they all look the same), and are meant to be human-readable. The downside is that they don't necessarily have all the details you need. I use INFORMATION_SCHEMA.TABLES AND INFORMATION_SCHEMA.COLUMNS all the time, but forget about ROUTINES, which includes functions and stored procedures.
By creating a stub entry, it ensures permissions remain, as well as other things like the original creation date. If you have an automated environment, you might do a DROP/CREATE, which would break all your explicit permissions (and reset the create_date). By doing a stub entry then an ALTER, you ensure those remain.

Highly recommended code.

[Replication] Better alternative to Replication Monitor

2010-10-08T18:49:00.003-05:00

(changed on 10/12 - whoever woulda thunk a UNION wouldn't work right?)

I hate Replication Monitor. Here are a couple of scripts that, embedded in a job, will better help you. I need to look into adding special alerts for replication. The main change is the addition of snapshot detection - we have missed issues because it somehow falls offline and doesn't notify that the replication needs to be snapshotted.

IF OBJECT_ID('tempdb.dbo.##replication_command_count') IS NOT NULL

DROP TABLE ##replication_command_count

SELECT SUM(UndelivCmdsInDistDB) AS UndelivCmdsInDistDB,

MSdistribution_agents.NAME,

MSdistribution_agents.publication,

subscriber_id,

subscriber_db

INTO ##replication_command_count

FROM MSDistribution_Status

INNER JOIN MSdistribution_agents

ON MSDistribution_Status.agent_id = MSdistribution_agents.id

WHERE UndelivCmdsInDistDB > 0 --show only those that are backed up

AND subscriber_id > 0 --negative subscriber IDs are for those that

--always have a snapshot ready

AND MSdistribution_agents.NAME NOT LIKE '%someserverthatthrowserrors%'

GROUP BY MSdistribution_agents.NAME, MSdistribution_agents.publication,

subscriber_id, subscriber_db

ORDER BY MSdistribution_agents.NAME, MSdistribution_agents.publication,

subscriber_id, subscriber_db

--delete from stuff we don't care about; refinement of the IF below.

delete from ##replication_command_count

where publication = 'a_busy_publication'

and UndelivCmdsInDistDB <>

IF (SELECT MAX(UndelivCmdsInDistDB) FROM ##replication_command_count WHERE publication NOT IN ('a_busy_publication', 'a_busy_publication')) > 20

OR (SELECT MAX(UndelivCmdsInDistDB) FROM ##replication_command_count WHERE publication = 'a_busy_publication') > 20

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBA',

@recipients = 'dev@null.com',

@subject = 'Replication is backed up on REPL server'

,@query = 'select left(publication,20), convert(varchar(9),UndelivCmdsInDistDB), left(name,90) from ##replication_command_count where UndelivCmdsInDistDB >20'

,@query_result_header = 0

----------------------------

Script 2 -- looking for errors.

IF OBJECT_ID('tempdb.dbo.##replication_errors') IS NOT NULL

DROP TABLE ##replication_errors

SELECT

errors.agent_id,

errors.last_time,

agentinfo.name,

agentinfo.publication,

agentinfo.subscriber_db,

error_messages.comments AS ERROR

INTO ##replication_errors

FROM

--find our errors; note that a runstatus 3 can be the last message, even if it's actually idle and good

(SELECT agent_id, MAX(TIME) AS last_time FROM distribution.dbo.MSdistribution_history with (nolock)

WHERE (runstatus IN (3,5,6) AND comments NOT LIKE '%were delivered.' AND comments NOT LIKE '

or (runstatus = 4 and comments like 'The initial snapshot%is not yet available.') GROUP BY agent_id) errors

FULL outer JOIN

(SELECT agent_id, MAX(TIME) AS last_time FROM distribution.dbo.MSdistribution_history with (nolock)

WHERE (runstatus IN (1,2,4) and comments not like 'The initial snapshot %is not yet available.')

OR comments LIKE '%were delivered.' GROUP BY agent_id

) clean

ON errors.agent_id = clean.agent_id

--grab the agent information

LEFT OUTER JOIN distribution.dbo.MSdistribution_agents agentinfo

ON agentinfo.id = errors.agent_id

--and the actual message we'd see in the monitor

LEFT OUTER JOIN distribution.dbo.MSdistribution_history error_messages

ON error_messages.agent_id = errors.agent_id AND error_messages.time = errors.last_time

where errors.last_TIME > ISNULL(clean.last_time,'20100101')

AND comments NOT LIKE '%TCP Provider%'

AND comments NOT LIKE '%Delivering replicated transactions%'

AND name NOT LIKE '%suckyservername%'

IF (SELECT COUNT(*) FROM ##replication_errors) > 0

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'dba',

@recipients = 'dev@null.com',

@subject = 'Replication errors on REPL server'

,@query = 'select * from ##replication_errors'

,@query_result_header = 0

DROP TABLE ##replication_errors

[Replication] Simple replication to find an issue

2010-10-08T16:58:00.002-05:00

Say you get a message like this:

Cannot insert duplicate key row in object 'dbo.yourtable' with unique index 'yourtable_Index'

Run this:

--run from wherever the table exists

DECLARE @publisher_database_id INT, @article_id int

SELECT @publisher_database_id = id FROM distribution.dbo.MSpublisher_databases where publisher_db = DB_NAME()

SELECT @article_id = artid FROM dbo.sysarticles WHERE dest_table = 'yourtable'

EXEC distribution..sp_browsereplcmds @publisher_database_id = @publisher_database_id, @article_id = @article_id

[QA] Linked server for QA - faking a prod server

2010-08-17T15:19:00.003-05:00

In case you use linked server a lot. This creates a linked server named ServerB that actually connects to the local server. I needed it for testing, as a lot of code references linked servers (no comments on _that_, please)

EXEC sp_addlinkedserver

@server = 'ServerB',

@srvproduct = '',

@provider = 'MSDASQL',

@provstr = 'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=True;'

[Tricks] Single-user-mode with a twist

2010-08-13T10:25:00.003-05:00

Had to restart a SQL Server in single-user mode. Two problems. One, the configuration was preventing it from starting properly, and there were automatic processes connecting to that single user.

The normal way to do it is:
sqlservr -m
(that's single user mode)
but for this we needed the configurations to not screw us up.
sqlservr -f
and we needed to make sure that it was only us connecting.
sqlservr -f"sqlcmd"
(Yes, you could also do sql server management studio)

[Code] Xtype definitions

2010-06-28T08:38:00.001-05:00

'C' =  CHECK constraint 
'D' =  Default or DEFAULT constraint  
'F' =  FOREIGN KEY constraint  
'L' =  Log  
'FN' =  Scalar function  
'IF' =  In-lined table-function  
'P' =  Stored procedure  
'PK' =  PRIMARY KEY constraint (type is K)  
'RF' =  Replication filter stored procedure 
'S' =  System table  
'TF' =  Table function  
'TR' =  Trigger  
'U' =  User table  
'UQ' =  UNIQUE constraint (type is K)  
'V' =  View  
'X' =  Extended stored procedure

[Ugly] Drop a view using Dynamic SQL

2010-06-07T14:30:00.004-05:00

There's a time and a place for Dynamic SQL. This is, as far as I know, one of them.

I need to ensure a view (standard_view) is dropped in a particular database (My2008DB). That database is named for the year, so it's different each year. I can't use sp_msforeachdb for a particular (REDACTED) reason. You probably don't have that problem, I do.

So, best I can tell, the way to do it is to call sp_executesql from within the context of that other database. And since I have to execute the code to verify the drop before doing it, we wind up with this particularly ugly piece of code. Yes, we're 3 layers deep. sp_executesql calls My2008DB.dbo.sp_executesql, which calls the actual drop code. And yes, it works, at least on SQL Server 2005.

DECLARE @date SMALLDATETIME, @drop_view VARCHAR(8000) ,@SQLString nvarchar(500), @ParmDefinition nvarchar(500)
select @date = '5/15/2008'

--DROP VIEW.  Need to look in the DB, determined dynamically via the @date
SELECT @drop_view = 'if object_id(''My' + CONVERT(CHAR(4),@date, 112) + 'DB..standard_view'') is not null
    drop view standard_view'

--now we create a "wrapper" to run in the correct database context.
SELECT @sqlstring = N'execute My' + CONVERT(CHAR(4),@date,112) + 'DB.dbo.sp_executesql @mysql', @ParmDefinition = N'@mysql nvarchar(max)'

--execute in our dynamic db
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@mysql = @drop_view

[Tips] HOW TO find page splits by reading the transaction log

2010-05-25T16:46:00.001-05:00

http://strictlysql.blogspot.com/2009/10/identifying-page-splits.html

There are two tricks in here. The first is using ::fn_dblog (undocumented function) to read the transaction log for a database.
The second is pulling out the delete messages involved in a page split and using that to determine where the splits are occurring.


select AllocUnitName, count([AllocUnitName]) [Splits]
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
    and parsename(AllocUnitName,3) <> 'sys'
group by AllocUnitName

[Tuning] Getting only the DATE from a datetime

2010-05-25T16:29:00.004-05:00

Many ways to handle dates, and most time I don't think about it - then came across a gigantic thread over two years that discussed it.

Comparing dates. Traditionally, for simplicity, to mark particular dates, I've used convert(char(8),a_date_field,12), but it’s non-SARGable, and not terribly efficient.

Looking online I found a couple alternatives, though they’re mostly useful when having to deal with large tables.

http://blog.sqlauthority.com/2008/10/18/sql-server-retrieve-select-only-date-part-from-datetime-best-practice-part-2/

For SQL Server 2008 it's pretty simple:


SELECT  cast(GETDATE() as date)
or
SELECT CONVERT(date,GETDATE())

(date is a new datatype that only stores the date, and is 3 bytes in size)

For SQL Server 2005, there seem to be two options. Naturally, neither is SARGable from what I’ve seen.


SELECT CONVERT (datetime, FLOOR(CONVERT (float, a_field_name)))
and
SELECT DATEADD ( DAY, DATEDIFF(DAY, 0, a_field_name), 0)

The first one performs math on the internally-stored FLOAT value of the datetime - right now, for instance, is 40315.37003125, where the whole number is the day and the fraction is the time. (Note that you can’t cheat and use INT; it can round up during the conversion).

The second one does date manipulation - there's a whole article online about using datediff and dateadd and the like to get the first day of the month, the first day of last month, etc, etc.

From what I’ve seen online, the FLOOR option can be slightly faster than the DATEADD, but either is considerably faster than CONVERT(char(8)).

[Tuning] Disk usage, redux

2010-05-24T15:11:00.002-05:00

Here's a slightly different version of an earlier query, that tells you how much IO each database is getting.

This one is by database, not by file - did this since it makes dealing with TEMPDB easier. So you can't use it for physical file IO - that's the other version. And it's in GB, since that's our issue.

SELECT

sysdb.name AS name,

master_files.type_desc,

vfs.sample_ms,

SUM(vfs.num_of_bytes_written)/1024/1024/1024 AS GB_written,

SUM(num_of_bytes_read)/1024/1024/1024 AS GB_read,

SUM((num_of_bytes_read + num_of_bytes_written))/1024/1024/1024 AS GB_total_IO,

SUM(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

INNER JOIN master..sysdatabases sysdb ON vfs.database_id = sysdb.dbid

GROUP BY

sysdb.name, master_files.type_desc,

-- master_files.physical_name,

vfs.sample_ms

ORDER BY SUM((num_of_bytes_read + num_of_bytes_written))/1024/1024/1024 desc

[Tuning] Filtered indexes...and filtered statistics.

2010-05-13T16:29:00.003-05:00

So, SQL Server (starting with 2008) now has Filtered Indexes. Which are great - since an index is basically just a copy of the data, why copy particular fields for the entire table when you only query particular subsets of data? Add a WHERE clause to your index, and now you're querying subsets of data.

However, while reading up on it I came across this:
http://msdn.microsoft.com/en-us/library/ms190397.aspx
which introduces filtered statistics(!).

Instead of


CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)

which would give you stats for the entire range, do this:


CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3)

which will only compute statistics for that subset of data.

[Files] Tricking Excel with SP_SEND_DBMAIL

2010-05-07T15:03:00.004-05:00

My pain = your gain. I needed to automate a process to send a file to an end user. They would just double-click it and have it open in Excel. The problem was due to a field with a leading zero, which Excel will simply lop off. One way around is to create an XML file. I've done that before, but this is simpler and ideal for this situation. The syntax and parameters are important.


EXEC msdb.dbo.sp_send_dbmail @profile_name = 'youremailprofile', 
    @recipients = 'dev@null.com', 
    @subject = 'Here is your file pull done',
    @query = 'SELECT a, b, convert(varchar(12),c) as C, char(61) + char(34) + leadingzerossuck + char(34) as leadingzerossuck FROM mytemptable', 
    @attach_query_result_as_file = 1, 
    @query_attachment_filename = 'yourfilename.csv', 
    @query_result_separator = ' ', -- tab
    @exclude_query_output = 1, 
    @append_query_error = 1

Table Partitioning

2010-04-28T10:58:00.004-05:00

Working on learning partitioned tables. We've done it, and it works really well for us, but it's time to start getting clever.

Was reading this, which is an excellent primer:
http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/

A few rules I learned the hard way. I deliberately did it from scratch in hopes that I'd remember it better - what to do, what not to do. In particular, working on switching tables into partitions (our current stuff switches out, not in)

* clustered key of the table you're switching in has to be on the same filegroup as the partitioned table.
* indexes have to match. ALL of them.
* indexes are NOT on the partition scheme
* only one constraint (below)
* Add a constraint to make sure the partitioned key is within the partition range (order_date >='20100428' and order_date <'20100429' and order_date is not null)
* Then, SPLIT the range (code below), and SWITCH the table in.


    ALTER PARTITION SCHEME ps_daily NEXT USED your_partitioned_fg
    
    ALTER PARTITION FUNCTION pf_daily() SPLIT RANGE ( @Day) --@day is one day larger than current max date

    ALTER TABLE orders_stg SWITCH TO dbo.orders PARTITION 200

Now, what if you need to add an old day in? You have up to 5/4/2010, but need 5/3?

ALTER PARTITION SCHEME [ps_daily] NEXT USED your_partitioned_fg
ALTER PARTITION FUNCTION [pf_daily] () SPLIT RANGE (N'2010-05-03')

[Offtopic] How to print to a networked Laserwriter (XP)

2010-04-21T21:03:00.003-05:00

Needed this desperately, so posting it here. Worked like a charm for me. Thanks to this Anthony Duplessis chap, whomever he is. Thanks!

Note that once you get to the test page print the first time, you're NOT DONE, so uncheck it. Follow the directions, step by step.

(found some screenshots at: http://blog.eppesconsulting.com/2009/05/05/AppleLaserWriter16600PSOnWindowsVista.aspx)

Printing to an Apple Laserwriter 16/600 using IP from a Win 2K and Win XP
client.

The following procedure explains how to configure desktop clients,
running Windows 2000 or Windows XP, without installing the appletalk
protocol.

Select Start/Settings/Printers.
Double-click 'Add Printer' icon. Click the 'Next' button.
Select 'Local printer' bullet.
Uncheck 'Automatically detect and install my Plug and Play printer' box.
Click the 'Next' button.
Select 'Create a new port' bullet.
Select 'Standard TCP/IP Port' from the drop-down arrow list.
Click the 'Next' button. Click the 'Next' button again.
Enter the appropriate IP address of the printer. Click the 'Next' button.
Select 'Apple Network Printer' from the drop-down arrow list in the 'device
type' field.
Click the 'Next' button. Click the 'Finish' button.
Select the appropriate printer driver. Click the 'Next' button.
Accept or modifiy the suggested printer name. Click the 'Next' button.
Select the 'Yes' bullet if you want to make this printer the default, or
Select the 'No' bullet if you do not want to make this printer the default.
Click the 'Next' button.
Select the 'Do not share this printer' bullet. Click the 'Next' button.
Select the 'No' bullet so that a test page is not printed.
Click the 'Finish' button.
Open the Printers folder.
Select and Right-click the apple printer. Select 'properties' from the
context menu.
Select the ‘Ports’ tab and then the ‘Configure Ports’ button.
Verify that the 'LPR' bullet is selected in the Protocol section.
Enter ‘lp’ in the 'Queue Name' field. [Note: that's LP, lowercase, not IP]
Click to select the 'LPR Byte Count Enabled' box.
Click the 'OK' button.
Select the ‘General’ tab and then Click the 'Print Test Page' button.

Anthony Duplessis.
LIFE is the toughest teacher of all.
First, you get the test.
Then, you're taught the lesson.

[Powershell] What a piece of $#!+

2010-04-21T10:18:00.003-05:00

Wow. So, Powershell is Microsoft's idea of UNIX: a command-line shell. Unfortunately, more than a bit half-assed, even with version 2.0.

I grabbed a module to allow UI scripting. Here's what I have to do in order to run a simple Hello World with it:

download Powershell
download PowerShellPack
install both
start Powershell
Set-ExecutionPolicy Unrestricted (so I can run scripts)
Import-Module PowerShellPack (needs to be done each time)
Hit ctrl+c because it's going to ask me once per script, and there are about 50. And, each time I run powershell.
Set-ExecutionPolicy Bypass (hello, usefulness, goodbye protection. Grrrr)
Import-Module PowerShellPack
Import-Module WPK
New-Label "Hello, World" -Show
get odd error: Exception calling ".ctor" with "0" argument(s): "The calling thread must be STA, because many UI components require this."
read blog
change shortcut to Powershell to add -STA
restart Powershell
Import-Module PowerShellPack
Import-Module WPK
New-Label "Hello, World" -Show

Success!

WTF. Am I a bad person if I wonder why the hell I have to jump through all these hoops?

[Partitioned Tables] Does the clustered index take up space if referenced?

2010-04-19T17:03:00.004-05:00

Setting up a new partitioned table with associated indexes. I was curious whether the adding the partitioning key to any index would cause the index to grow - I expected not, but you never know.

Our clustered index:

create unique clustered index clustind_pk on ourtable (id, partitionedkey)

Our test indexes:


create nonclustered index A on ourtable (partitionedkey, fielda)
on ps_daily (partitionedkey)

create nonclustered index B on ourtable (fielda)
on ps_daily (partitionedkey)

create nonclustered index C on ourtable (fielda) include (partitionedkey)
on ps_daily (partitionedkey)

Then used this query to check a particular partition for all 3 indexes (thanks to Simon Sabin). Each was within 1 page of the others.


select OBJECT_NAME(p.object_id ), i.name,p.*
from sys.dm_db_partition_stats p
join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
WHERE p.object_id = 2071234567
AND partition_number = 28

[Install] Restarting your SQL service within SQL

2010-04-16T16:49:00.006-05:00

DOES NOT WORK. Not deleting the post below in case someone comes across this. We wound up using powershell instead. WMI-process.

We're building a Powershell script to automatically build out our machines. Not just the install, but the full config: backup jobs, maintenance, tempdb files, model, etc, etc.

So, we need to bounce the service so that all the tempdb files get created, the agent knows the database config, etc.

The trick isn't stopping the server: "net stop mssqlserver" will do that. The real trick is starting it back up, once you've shut down the SQL server. Since our solution is all done via SQLCMD we needed a way, within SQL itself, to start back up.

Our secret is the ampersand; when the command line interpreter catches it, it views it as you hitting the "Enter" key. So, even though the SQL service is off, the job continues.


declare @sql varchar(8000)
select @sql = 'net stop SQLSERVERAGENT & ping -n 15 127.0.0.1 & '
+ 'net stop MSSQLSERVER & ping -n 15 127.0.0.1 & '
+ 'net start MSSQLSERVER & ping -n 15 127.0.0.1 & '
+ 'net start SQLSERVERAGENT'
EXEC xp_cmdshell (@sql)

[Replication] Finding the commands that are breaking

2010-04-13T14:46:00.004-05:00

Here's a simple one I had to do today. Documenting since I hate having to dig through the help files.

On your distributor:


SELECT * FROM distribution.dbo.MSpublisher_databases

That gives you a list of the source databases from the publishers. You want the ID field.

Now,


use distribution
go
sp_browsereplcmds @publisher_database_id = 13

will list all the waiting commands. The command field is the actual command, while the article_id can be gotten from running the following on the publisher:


sp_helparticle @publication = 'your_publication_name'

[Sp3] Another reason not to use DDL Triggers

2010-04-12T08:48:00.003-05:00

So, I've already posted about my issues using DDL triggers with Replication (http://thebakingdba.blogspot.com/2009/12/replication-and-ddl-triggers-do-not-mix.html). Well, it looks like it may have contributed to issues with an active/active cluster upgrade (2005 SP3).

Installing the patch on the first active node, we got a failure on the upgrade. The error message? "Target string size is too small to represent the XML instance". But, oddly enough, the passive (which is upgraded first) worked and was on SP3.

So we deleted the trigger (which, interestingly enough, showed that replication uses DDL triggers - which makes sense but I hadn't thought about), and ran it again.

It failed again, but this time because the passive node wasn't upgraded. Check both - and yes, SP3. Roll back and forth, everything looks good. A sucky upgrade, but we got through it.

[Tuning] SPARSE varchar calculation

2010-04-09T04:10:00.004-05:00

Since it doesn't appear that anybody has done this before, here you go. I've been comparing SPARSE to COMPRESSION, and for my particular tables, I got 25% space savings via parse. However, I got 40% savings from ROW compression, and 50% savings from PAGE.


Standard
=(Number_Of_Rows*(Average_Varchar_Length+2)
*((100-Percent_Null)/100))
+(Number_Of_Rows*(Percent_Null/100*2))

Sparse:
=(Number_Of_Rows*(Average_Varchar_Length+4))*((100-Percent_Null)/100)

Next up is comparing the CPU for each option. Nobody's really talked about whether the compression is symmetric or asymmetric, though I'd hope it's asymmetric (aka easier to decompress than compress, in this case)

[Jobs] Quick & Dirty - is the job running?

2010-03-29T17:20:00.003-05:00

Needed a simple piece of code to tell me if a job was running or not. Several ways to do it, but this is the simplest and probably dirtiest.


EXECUTE sp_get_composite_job_info @job_id = 'B74856BF-3326-4B9F-B3A6-B1D182E1F300', @execution_status = 1

IF @@rowcount = 1
PRINT 'job running'
else print 'nope, not'

sp_MSforeachDB - skipping databases

2010-03-26T14:04:00.001-05:00

SP_MSforeachDB is awesome - an easy way to walk through every database and run some code.

But what about databases you don't _want_ to touch?


sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'',''model'',''a'',''b'',''c'') 
--sp_updatestats required after reorg, but not after rebuild
begin 
use ?
exec sp_updatestats 
end'

Kulich - Russian Easter Bread

2010-03-26T13:54:00.004-05:00

FINALLY, something baking related! It's been too long, I've been resting on my laurels.

I'll update this once I've made it myself. One other way to make it is inside a coffee can - I need to get details on that, since that's how I always had it - bullet-shaped, funnily enough, with a sprinkle-laden glaze on top.


6 cups sifted flour
3 egg yolks
1 whole egg
3/4 cups sugar
1 teaspoon salt
1 and a 1/2 packages yeast (come in a pack of 3)
1 and 1/4 cups WARM (not hot) milk
1 stick (1/4 pound) melted butter. 
 (Let cool off so it's not hot.)


Put yeast and milk in bowl and mix.
put eggs, sugar and salt in another bowl and mix on low speed.
Put milk and yeast together with the flour.
Add the eggs,sugar, salt mixture in.
Add melted butter.
Mix well with a wooden spoon.
Knead the dough mixture about 10 minutes (maybe more).
Dough needs to be soft.
Put a cover over the bowl so it can rise.
Dough needs to rise for an hour or more.
Needs to become twice its size.
Separate into 2 loaves.
Put 1 cup raisens for each loaf
Preheat oven to 350.
Use loaf pan sprayed with PAM or greased with butter.
Let dough rise again for 40-60 minutes.
If you want a shiny crust, use the whites from the 3 eggs.
Beat the whites for just a minute with a fork and brush the mixture
over the crust before you put in oven.
Bake 45-60 minutes.
Check dough with toothpick at 45 minutes to see if ready.
Pull the bread out and put on a towel.

[Code] IF EXISTS table check

2010-03-05T14:38:00.002-06:00

Because I keep coming over here to find the snippet...


if object_id('tempdb..##temptablename') is not null
    drop table ##temptablename
CREATE TABLE ##temptablename (id INT IDENTITY, filelist VARCHAR(255))


 SELECT * FROM tablename.dbo.sysobjects 
 WHERE id = OBJECT_ID(N'tablename_goes_here') 
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1

[Replication] Alternative to SQLMonitor (Replication Monitor)

2010-02-01T16:00:00.004-06:00

SQL Server's Replication Monitor is great except for a few major issues
1) It has to be running all the time, and it consumes a large number of window resources.
2) Due to the way SQL reports issues in replication, it can show a publication as good, even if it's been erroring for 2 days.
3) It's active, not passive - I have to go look at it. It can't warn me via email or whatnot.

So, half a day digging through code and error messages, and I've come up with this. This is not fullproof by any stretch - I don't deal with Merge Replication, for instance. But in MY environment, it seems to work pretty well.

I'll see about adding a couple more fields, but I want to get this on paper.
(And yes, this is a request: if there's a better way than this, let me know)


--Courtesy of The Baking DBA
--Replication query that shows what's currently in error.
--1.01 added "delivering" exclusion, email, fixed 
--   line-too-long-breaks-blogspot formatting
IF OBJECT_ID('tempdb.dbo.##replication_errors') IS NOT NULL 
    DROP TABLE ##replication_errors

SELECT 
 errors.agent_id, 
 errors.last_time, 
 agentinfo.name, 
 agentinfo.publication, 
 agentinfo.subscriber_db,
 error_messages.comments AS ERROR
INTO ##replication_errors
FROM 
--find our errors; note that a runstatus 3 
--can be the last message, even if it's actually idle and good
(SELECT agent_id, MAX(TIME) AS last_time 
 FROM distribution.dbo.MSdistribution_history with (nolock)
 WHERE runstatus IN (3,5,6) 
 AND comments NOT LIKE '%were delivered.' 
 AND comments NOT LIKE ' GROUP BY agent_id) errors
INNER JOIN 
(SELECT agent_id, MAX(TIME) AS last_time 
 FROM distribution.dbo.MSdistribution_history with (nolock)
 WHERE runstatus IN (1,2,4) 
 OR comments LIKE '%were delivered.'
 GROUP BY agent_id) clean
 ON errors.agent_id = clean.agent_id 
 AND errors.last_TIME > clean.last_time
--grab the agent information
INNER JOIN distribution.dbo.MSdistribution_agents agentinfo
ON agentinfo.id = errors.agent_id
--and the actual message we'd see in the monitor
inner JOIN distribution.dbo.MSdistribution_history error_messages
ON error_messages.agent_id = errors.agent_id 
AND error_messages.time = errors.last_time
AND comments NOT LIKE '%TCP Provider%'
AND comments NOT LIKE '%Delivering replicated transactions%'

IF (SELECT COUNT(*) FROM ##replication_errors) > 0 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'email_profile',
    @recipients = 'blah@tbd.com',
    @subject = 'Replication errors'
   ,@query = 'select * from ##replication_errors'
   ,@query_result_header = 0

DROP TABLE ##replication_errors

IF EXISTS for tables

2010-01-29T13:40:00.001-06:00

I know - I should have this memorized. And it doesn't deal with schemas.

IF EXISTS
(
SELECT * FROM databasename.dbo.sysobjects
WHERE id = OBJECT_ID(N'tablename')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
DROP TABLE databasename.dbo.tablename

[Setup] Making sure your disks are optimized

2010-01-25T16:32:00.002-06:00

At this point, probably everyone knows that you need to make sure to format your drives properly to take full advantage of them. There's two different issues: the cluster size, and the offset. Practically, you want for each of them to be 64k.

Here's how to make sure.

Block size.

> fsutil fsinfo ntfsinfo d:

That will give you a bunch of info. What you care about is the Bytes Per Cluster, which should be 65536 (aka 64k)

Cluster offset:
Using DISKPART:

> diskpart

> list disk

> select disk 1 (or whichever disk you want to look at)

> list partition

Look for the "offset" column

Powershell: See
http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!291.entry
for a powershell script.

To format a drive properly:
diskpart
list disk
select disk 2
create partition primary align=1024
format fs=ntfs unit=64K label="yourdrivenamehere" nowait
exit

[Text] Counts of a word within a file. Not by line, total.

2010-01-05T10:05:00.003-06:00

Came across this (courtesy of Franklin52 in the unix.com forums).
http://www.unix.com/shell-programming-scripting/63576-how-find-count-word-within-file.html

Say you need a count of a particular word in a file. For example, in an XML file where the word can repeat within a line. Can't use WC or GREP or FIND, but AWK will do the job. (You do have these tools on your Windows box, right? [if you have a UNIX box, it's assumed you do])


awk 'BEGIN{RS=" "}/WORDTOCOUNT/{h++}END{print h}' blah.txt

One note: this assumes that there will be a space somewhere between the occurrence of the words. TESTTEST and
TEST
TEST
would each only count as 1, since there's no space to "reset" the find. (It's a stream function - search for the word. If you find it, increment by one and skip forward to the next space. When you hit a space, start searching again.) For our XML, there are spaces after the tag we searched for, so the counts work.

Replication and DDL Triggers - DO NOT MIX

2009-12-04T15:13:00.002-06:00

So, we had started rolling out DDL Triggers, and then today replication broke.

How? A weird ARITHABORT error trying to add a table via the GUI. Weird. So I disable the trigger and add it - at which point replication itself starts throwing the error :

Target string size is too small to represent the XML instance (Source: MSSQLServer, Error number: 6354)
Get help: http://help/6354

Well, it turns out I have an XML trigger on the TARGET database, and it can't deal with the large commands involved in transactions.

How to diagnose and find the exact commands causing problems?


use [replicated_table]
go
sp_helparticle @publication = N'publication_name', @article = 'article_name'
go
use distribution
go
sp_browsereplcmds @article_id = 77 --where 77 is the article_id from above

DDL Triggers

2009-11-18T17:26:00.003-06:00

We're slowly starting to roll this out, based on the below code. A very well written article; our concern is on performance.

http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx

Piecemeal (aka Partial) filegroup restores from litespeed (sorta)

2009-10-28T10:18:00.002-05:00

Okay, I wanted to write this down while I'm thinking about it.

I use filegroups and do filegroup backups. Extensively. Gives you more control and power, since you can backup each one individually, put it in its own location, give it its own schedule, etc, etc. Litespeed helps with this, obviously, since it'll shrink each file.

The downside is that restoring with Litespeed is an all-or-nothing ordeal. You can't restore just the primary, or just one of the filegroups, or just a table (no, object-level restore doesn't work on filegroup backups). Fortunately, SQL 2005 has a new "PARTIAL" option that allows you to restore just the primary filegroup, and then restore others in addition, so I'm using that.

I've already requested this feature for Litespeed, as well as the PARTIAL option, but here's what I had to do to restore some tables from my filegroup backup

0. Take filegroup backups. Take a TLOG backup if needed.

1. Decompress the files I need. Litespeed _does_ offer an "extractor" utility. This will turn your Litespeed BKP into an uncompressed SQL backup. One note: it creates 7 files, and the total will be the same as the uncompressed backup. So make sure you have room. I barely did - thank god for filegroups (or maybe not, since the whole purpose of this post is working around it). So, extract the primary and whichever filegroup you need.
Here's the code to restore a file:

extractor.exe -Fc:\temp\Northwind.bak -Ec:\temp\NorthwindNative.bak

On my system it converts to native at about .75gb per minute (uncompressed size). It's extremely disk-bound. You need to extract the log file too, if you're compressing it (you might not be! Check and see....).

2. Restore the primary filegroup WITH PARTIAL. Make sure to keep your Primary filegroup small. Mine isn't - I need to fix that. Here's the command I used:
RESTORE DATABASE main
FILE = 'main_system_01'
FROM DISK = 'K:\primary.bak0',
DISK = 'K:\primary.bak1',
DISK = 'K:\primary.bak2',
DISK = 'K:\primary.bak3',
DISK = 'K:\primary.bak4',
DISK = 'K:\primary.bak5',
DISK = 'K:\primary.bak6'
WITH partial, move 'main_system_01' TO 'E:\MSSQL_Data\main.mdf' ,
move 'main_log_01' to 'F:\MSSQL_Log\main_log.ldf', --without this, it will fail
FILE = 1, norecovery, nounload, stats = 10

3. Wait a couple hours for the primary to restore. (See? This is why you have a small primary filegroup.)

4. Restore the additional filegroup. You do _not_ use the PARTIAL keyword here. Why? Don't know - using PARTIAL caused it to fail, though my lab notes say to use it. My bet is that you don't need PARTIAL if you're restoring the only filegroup within a backup.
RESTORE DATABASE Main
FILE = 'Main_Secondary_01'
FROM DISK = 'K:\FG_Main_Secondary.bak0',
DISK = 'K:\FG_Main_Secondary.bak1',
DISK = 'K:\FG_Main_Secondary.bak2',
DISK = 'K:\FG_Main_Secondary.bak3',
DISK = 'K:\FG_Main_Secondary.bak4',
DISK = 'K:\FG_Main_Secondary.bak5',
DISK = 'K:\FG_Main_Secondary.bak6'
WITH --partial,
move 'Main_Secondary_01' TO 'E:\MSSQL_Data\Secondary.ndf' ,
FILE = 1, norecovery, nounload, stats = 10

5. Restore the log. I'd say to use use STOPAT so you don't have to keep restoring TLOGs, but I can't find anywhere how to get that to work with Litespeed's restore - I tried adding it to the @with, but then it just failed. Odd. Also, if I extract and restore a log, I get error messages about having to roll the LSN forward to a certain point, whereas I don't get that when running the below command.

EXEC master.dbo.xp_restore_log
@database = 'Main'
, @filename = '\\myserver\SQL_Backups\Main\Main_tlog_200802041030.TRN'
, @filenumber = 1
,@WITH ='RECOVERY'

6. Keep running restores until you get current. The way I did it, I kept running TLOG restores until I got an error message telling me the database is offline. It does this once you hit the last log file prior to the last database backup finishing.

7. Bring database ONLINE. I do it via the GUI.

8. Bring database from SINGLE-USER to MULTI-USER.

[Tuning] Getting file disk usage

2009-10-28T09:57:00.006-05:00

Short and simple. Uses the DMV to tell you how much IO each file is using. What you think may be the case is not necessarily the case. And remember that backups count. I'd recommend doing 2+ samples during the day, and comparing. That'll help remove the backups from the end totals. Sample_MS is the number of milliseconds the computer has been up.


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
ORDER BY (num_of_bytes_read + num_of_bytes_written) desc

[Compression] Seeing how much savings you'll get

2009-08-20T16:22:00.003-05:00

If you meet all sorts of criteria (SQL Server 2008, Enterprise Edition) then you can use row or page level compression on your tables. But, you ask: will it make a difference in space used?

Fortunately, and a bit surprisingly, Microsoft came up with a way to do so.
sp_estimate_data_compression_savings

Sample usage:


sp_estimate_data_compression_savings 
       @schema_name =  'dbo'  
     , @object_name =  '20090820__abc' 
     , @index_id = NULL             --NULL does all.
     , @partition_number = null     --if you use partitioned tables
     , @data_compression = 'PAGE'   --can also use ROW or NONE
go

It looks like it takes roughly 40mb of data, copies it to TEMPDB, and compresses that. It then returns the results, including comparing the size to what the current compression is.

For us, mixed results. One set of EDI data, which uses certain characters to split out values, gets roughly 25% compression. A different set of EDI data that uses XML (with huge swathes of repeating data) get a whopping 1% savings.

I love the idea. I want to use it everywhere - since most systems are IO bound (not CPU bound) it seems a home run. But the requirement of Enterprise Edition lessens its usefulness by a _lot_.

[Jobs] Search job history for a particular date range

2009-07-27T10:04:00.001-05:00

Pieces cribbed off several people. Enjoy.


select job_name, run_datetime, run_duration from (
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
WHERE run_datetime between '2009/07/24 01:00' AND '2009/07/24 01:59'
order by job_name, run_datetime

[Servers] No linked server? Need inserts? No problem!

2009-07-27T09:50:00.003-05:00

Linked servers can be handy. But, also occasionally annoying, and not necessarily where you need them. Fortunately, OPENQUERY to the rescue. Yes, you can use it to do an adhoc query from another server. But, you can also use it to do an insert into another server.

Code:


INSERT INTO OPENROWSET('SQLNCLI', 'Server=yourserversname;Trusted_Connection=yes',
 'SELECT * FROM northwind.dbo.targettable') SELECT FieldA, FieldB, FieldC
FROM sourcetable

[Tip] getting local context from sp_ in master

2009-06-29T16:30:00.002-05:00

In SQL 2005, if you create an SP in master that queries from system tables (or the INFORMATION_SCHEMA views), it'll return the details from the Master database - regardless of where you're running it. In SQL 2000, it worked.

So, an unsupported workaround, pointed out to me by Erland Sommarskog, SQL MVP & SQL God:

EXEC sp_MS_marksystemobject [your_sp_name]

[Cruft] New DMV in 2008 - SP details

2009-06-12T16:01:00.002-05:00

One of the things I've been hoping/waiting/praying for, in SQL 2008, is the ability to see when an SP was last run. In 2000 and 2005 you can do it, so long as it's still in the cache. Which means that things only run sporadically won't occur.

Imagine my happiness:

SELECT * FROM sys.dm_exec_procedure_stats

Extra credit: join to sys.procedures (also new) to get the object.

So, now you can see, since the server was started, what runs. And since your server stays up for several months at a time, you have a good "hit list" of procs that can be scripted out and removed.

I'll update this post when we get a good working version.
(Thanks to Hammer for his help)

[Cruft] Fixing dependencies

2009-06-11T10:54:00.005-05:00

EXEC sys.sp_refreshsqlmodule 'dbo.MyProcFnOrView'

That will update your dependencies table, allowing you to use sp_depends even if the objects weren't inserted in order. New to 2005, thankfully someone at MS realized and fixed sp_depends' weakness.

When objects are added, rows are added to the internal dependency table. However, it's very easy to get out of sync - say if you add SP A that calls SP B, but add them in the reverse order. Which _never_ happens. Sure.

So, anyhow. Use that, then use one of the handy pieces of code online that assume that the dependency table always works.

[Code] Powershell - a basic script for all servers

2009-04-23T08:43:00.003-05:00

Just acquainting myself with Powershell - I've known about it, but have preferred using Cygwin's shell. However, I wanted to run some code against all servers, and this seemed a good time to try with Powershell.

I found this original script, which would run the same code against all servers in a list. (http://www.quicksqlserver.com/2009/01/powershell-sqlcmd-and-invoke-expression.html)


foreach ($svr in get-content "C:\MyInstances.txt"){
    $svr
    invoke-expression "SQLCMD -E -S $svr -i createMyuser.sql"
   
}

So then I adapted it - using SQLCMD /Lc to get a list of servers on the network, print the server name, then run SQLCMD against each server, running the c:\simplemodel.sql script (while you can run SQL inline, it kept parsing the @@ and choking on it). Not foolproof, mind you, but a good starting point.


foreach ($svr in (sqlcmd /Lc))
{
        $svr
        invoke-expression "SQLCMD -E -S $svr -i c:\simplemodel.sql"
}

[Maint] Quick and Dirty maintenance

2009-04-21T17:02:00.003-05:00

Is this suitable most places? No. Is it fast and easy? Yes.

Reindex all tables in a database (2000 & 2005)

exec sp_MSforeachtable "DBCC DBREINDEX ('?')"

EXEC sp_MSforeachtable "print '?' DBCC DBREINDEX ('?', ' ', 85)"

Update statistics in a database
For 2000 (since sp_updatestats can break certain things)

EXEC sp_MSforeachtable "update STATISTICS ?"

For 2005:

sp_updatestats

[Cooking] Marathon weekend

2009-04-13T09:32:00.003-05:00

So, overall a good weekend. If ever there was a post of mine that qualified as "useless twitter feed", this would be it.

I made the following this Sunday:
Latkes for breakfast - a bit salty when I doubled the recipe. Maybe I subbed Tbsp for Tsp?

Dinner:
Ham (prefab from Sam's), Mixed veggies (prefab steamer bag), Prefab bread (I know, I know - just didn't have time or forethought to do something special), and I made:
Glazed Carrots - worked well, but for some reason I needed to cook them for longer than stated in the recipe. It took a while, and while the glaze wasn't perfect (next time try 1.5 cups of Ginger Ale), it was passable.
Bag Lady's Favorite Chocolate Pound Cake. Don't make this in a Bundt pan - it'll overflow. I used an Angel food pan, and it worked perfectly. Pulled it out after 85 minutes and it was done and came out moist. I used the Hershey's Perfectly Chocolate Icing (recipe below) and that worked wonderfully with it.

I do need to see if there's some sort of liquid-center bundt-cake recipe, where when it bakes enough it's cake, and where it's not it's pudding-like. That would be yummy.

Here's the recipe - I didn't make the cake, just the icing.
http://www.hersheys.com/recipes/recipes/detail.asp?id=184
"PERFECTLY CHOCOLATE" CHOCOLATE FROSTING

1/2 cup (1 stick) butter or margarine
2/3 cup HERSHEY'S Cocoa
3 cups powdered sugar
1/3 cup milk
1 teaspoon vanilla extract

Melt butter. Stir in cocoa. Alternately add powdered sugar and milk, beating to spreading consistency. Add small amount additional milk, if needed. Stir in vanilla. About 2 cups frosting.

[Backups] Restoring Litespeed archival backups to secondary server

2009-04-10T14:58:00.002-05:00

After a year or so, databases on my primary server get moved to a secondary server. I wrote the below script to look in a particular for 1 backup, then it determines where to restore it based off the database name. Hopefully someone else can use this.


DECLARE @backup_directory VARCHAR(500), @Verified VARCHAR(100), @full_backup_name VARCHAR(600),
  @database_name sysname, @restore_directory VARCHAR(500), 
  @logical_name_log  VARCHAR(50), @physical_name_log  VARCHAR(50),
  @logical_name_data VARCHAR(50), @physical_name_data VARCHAR(50)
  ,@with_data VARCHAR(200), @with_log VARCHAR(200)


---------------------------------------------------------------------
--put the name of the folder with the backup you want restored here--
---------------------------------------------------------------------
SET @backup_directory = '\\servername\share\databasefolderbackup'


IF RIGHT(@backup_directory, 1) <> '\'
 SET @backup_directory = @backup_directory + '\'

IF EXISTS 
 (
 SELECT * FROM dbo.sysobjects 
 WHERE id = OBJECT_ID(N'tempdb.[dbo].[#listing]') 
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1
 )
DROP TABLE #Listing
create table #Listing
(resultant nvarchar (255))

--Find the backups in the given folder
declare @dirlist varchar(500)
select @dirlist = 'exec master..xp_cmdshell ''dir /b "'+  @backup_directory + '*"'''
insert #Listing exec (@dirlist)

--get the most recent 
select @Verified = MAX(resultant) from #listing WHERE resultant LIKE '%.bak'
DROP TABLE #Listing

--get a list of all logical files within the backup so we can restore with the right data
SELECT @full_backup_name = @backup_directory + @Verified

if object_id('tempdb..#database_details') is not null
    DROP TABLE #database_details

CREATE TABLE #database_details 
 (
  LogicalName sysname, 
  PhysicalName varchar(500), 
  [TYPE] VARCHAR(2),
  FileGroupName varchar(50),
  [Size] VARCHAR(50),
  [MaxSize] VARCHAR(50))
INSERT INTO #database_details
 EXEC MASTER..xp_restore_filelistonly @filename = @full_backup_name
 
--get the names of everything.
SELECT @logical_name_data = LogicalName, @physical_name_data = PhysicalName
 FROM #database_details WHERE [TYPE] = 'D'
SELECT @logical_name_log = LogicalName, @physical_name_log = PhysicalName 
 FROM #database_details WHERE [TYPE] = 'L'

SELECT @database_name = @logical_name_data

--set up the folders where the restore will automatically go. 
SELECT @physical_name_data = CASE 
 WHEN @logical_name_data LIKE '2009%' THEN 'L:\2009\'
 WHEN @logical_name_data LIKE '2008%' THEN 'M:\2008\' 
 ELSE 'not known'
 END 
 + right(@physical_name_data, CHARINDEX('\', reverse(@physical_name_data))-1)

SELECT @physical_name_log = CASE 
 WHEN @logical_name_data LIKE '2009%' THEN 'L:\2009_Logs\'
 WHEN @logical_name_data LIKE '2008%' THEN 'M:\2008_Logs\' 
 ELSE 'not known'
 END 
 + right(@physical_name_log, CHARINDEX('\', reverse(@physical_name_log))-1)

--need to set these separately since you can't call within the SP
SELECT @with_data = 'MOVE "' + @logical_name_data + '" TO "' + @physical_name_data + '"'
SELECT @with_log = 'MOVE "' + @logical_name_log  + '" TO "' + @physical_name_log  + '"'

--Now do a restore with MOVE.  
exec master..xp_restore_database @database=@database_name
  , @filename= @full_backup_name
  , @with = @with_data
  , @with = @with_log

[Objects] dropping objects via code

2009-04-10T09:10:00.003-05:00

The following code works in both SQL 2000 and SQL 2005.

Tables


if object_id('tempdb..#database_details') is not null
    DROP TABLE #database_details

Stored Procedures


IF EXISTS 
 (
 SELECT * FROM dbo.sysobjects 
 WHERE id = OBJECT_ID(N'[dbo].[the_procedure_name]') 
 AND OBJECTPROPERTY(id, N'IsProcedure') = 1
 )
DROP PROCEDURE the_procedure_name

Alternatively, the following code will CREATE a dummy SP if it doesn't exist, then ALTER it. This way you will only CREATE, not DROP, which can come in handy in certain circumstances, since it will save permissions. Note that this uses INFORMATION_SCHEMA, which is more portable than sysobjects.


IF NOT EXISTS 
 (
 SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
 WHERE ROUTINE_NAME = 'the_routine_name' 
 and routine_type = 'PROCEDURE' --could also be 'FUNCTION' for a function
 )
        EXEC ('CREATE PROC dbo.the_procedure_name AS SELECT 1')
GO
ALTER PROCEDURE dbo.the_procedure_name
AS
SELECT  *
FROM    myTable

[Backups] Determine your database growth via backup history

2009-04-08T09:30:00.004-05:00

One thing about backups in SQL Server is that the history is kept forever, unless you clean it up using a maintenance plan, or one of the not-very-well-documented SPs.

But we can have it work for us.
Case in point - database growth estimations. This is a basic view that will show what your database growth has been like for the past 60 days. Turn it into a chart with reporting services, and you can see what's growing, at what rate, and what you need to be concerned with.

Yes, this is pretty basic code, but I hadn't seen anybody do this before.
And obviously, if you're cleaning up your backup history this won't necessarily do much.


CREATE VIEW [dbo].[backup_history]
as
SELECT 
 server_name,
 DATABASE_name,
-- catalog_family_number,  --not sure what this does; unclear in BOL
 backup_size/1000000 AS backup_size,
 CONVERT(CHAR(12),backup_start_date,101) AS backup_date--, 
-- in case you want to look at a particular type of backup
-- CASE [type]
--  WHEN 'D' then 'Database'
--  WHEN 'I' then 'Differential database'
--  WHEN 'L' then 'Log'
--  WHEN 'F' then 'File or filegroup'
--  WHEN 'G' then 'Differential file'
--  WHEN 'P' then 'Partial'
--  WHEN 'Q' then 'Differential partial'
--  END AS Backup_Type,
-- [NAME], 
-- [description] 
FROM msdb.dbo.backupset 
WHERE [TYPE] IN ('D','F') --full backups, though tlogs could be interesting
AND server_name = @@SERVERNAME
AND database_name NOT IN ('msdb', 'MASTER', 'model')
AND backup_start_date > GETDATE()-60
--ORDER BY SERVER_name, DATABASE_name, backup_start_date, catalog_family_number

[Entree] Gordon Ramsey Cookalong Lasagna, Take 2

2009-03-04T18:37:00.004-06:00

I've been following Gordon Ramsey's "Cookalong Live", albeit a bit delayed. However, I have to share and show off my attempt at his Lasagna Al Forno. The first time I did full mise en place, and it took about 75 minutes to put together. The second time it took about 45 minutes, and is utterly fantastic. Only takes a few ingredients that you might not have, nothing really too special (except for the pre-cooked lasagna sheets), and comes together VERY easily.

Highly recommended. There's even videos on how to make it.
Two hints
1) Tomato paste, not puree - the brits apparently name things differently.
2) Mince - ground beef, about 2/3 - 3/4 of a pound. I use 85/15 ground beef and it comes out wonderfully.

http://www.channel4.com/food/recipes/chefs/gordon-ramsay/gordon-s-lasagne-recipe_p_1.html

And since they can't be arsed to keep the recipe in one place (or even searchable on their own site...)

2 tbsp olive oil
½ large onion, peeled
1 large carrot, peeled
2 cloves garlic, peeled
2 pinches dried oregano
300g minced beef
1 tbsp tomato purée
1 tbsp Worcestershire sauce
1 bay leaf
30ml (2 tbsp) red wine
1 x 400g tin chopped tomatoes
50ml milk
Salt and freshly ground black pepper
For the sauce
25g butter
25g flour
300ml milk
Pinch of ground nutmeg
60g Cheddar cheese, grated
30g Parmesan cheese, grated
6 sheets of 'non-cook' lasagne sheets
For the salad
1 tsp Dijon mustard
1 tbsp white wine vinegar
2-3 tbsp olive oil
Salt and pepper
1 x round lettuce head, rinsed and dried
METHOD

How to make Gordon's classic lasagne al forno
1. Pre-heat the oven to 220C/ gas mark 7.

2. Heat the olive oil in a hot pan. Grate the onion and carrot and crush the garlic before frying together. Season with the bay leaf, a pinch of oregano, Worcestershire sauce and a little salt and pepper. Allow the onion to soften before making a well in the centre of the pan. Place the mince in the middle of the pan and stir to break it up. Add the tomato puree and allow to cook out for 30 seconds. Continue until all the meat has browned nicely. Add the wine and cook off the alcohol before adding the tomatoes. Leave to simmer for a further 2-3 minutes. Finally add the milk, turn off the heat and set aside (watch Gordon's video on how to prepare bolognaise).

3. To make the cheese sauce, first melt the butter in a saucepan. Add the flour and using a wooden spoon, stir to form a paste. Over a gentle heat add a third of the milk, whisking to prevent any lumps forming. Add the rest of the milk a third at a time, whisking as you go. Season with salt and pepper and a pinch of ground nutmeg. Allow the sauce to cook out for another minute before adding the Cheddar cheese. Stir and remove from the heat (watch Gordon's video on how to make the best white sauce).

4. Spoon half of the meat sauce into the bottom of the baking dish and place pasta sheets on top (break the sheets if necessary to avoid any overlapping). Next, pour in just under half of the cheese sauce, and spread evenly using a spatula before spooning the remaining meat on top. Add the final layer of pasta and use the spatula to pour over the remaining cheese sauce.

5. Finish with the grated Parmesan and sprinkle with another pinch of oregano. Add a light seasoning of salt and pepper before cleaning the edges of the dish and placing in the oven to bake for 20-25 minutes, or until golden brown.

6. In the bottom of your salad bowl use a fork to whisk together the mustard, vinegar and olive oil. Season with salt and pepper. Carefully open the head of lettuce, season inside with salt and pepper. Upend the lettuce headfirst into the salad bowl. Holding the root, wipe the leaves around the bowl to coat in the vinaigrette. Twist the root and pull it out. Turn the dressed head of lettuce onto a large plate and gently open out.

7. Portion out the lasagne and serve alongside the salad.

[Russian] Olga's Haluski

2009-03-02T17:26:00.002-06:00

No baking on this one, but a really good Russian version of dumplings. This recipe includes all the stuff we serve it with; potatoes, kielbasa, onions. It's very simple, tastes really good, is very filling, and cheap. Russian poverty food, with some meat added.

Ingredients:

Heluski

4 Cups AP flour
1.25 cup warm water
1 teaspoon salt
1 tablespoon oil

3 medium potatoes
Vinegar (serve with food)
1-2 pounds of kielbasa/sausage
3 medium onions
- 1 stick of butter

Onions:
First, we need to cook the onions down, since this takes longer than the other steps. Peel and slice 3 medium onions into rings. Put in a pan, add one stick butter, cover, and cook on medium until clarified. Once this occurs, turn it to medium-high (not high but close to it) and let them fry and caramellize. You want to fry the onions. Once you get them nice and brown, turn to low. Yes, there will be an oil slick of butter; that's fine

Meat:
In another pan, cook kielbasa or a sausage. Cut into whatever size you desire (4-5 inch-long pieces work best). Cook and brown to taste, and once cooked set on low. This step can be done at the same time as the boiling, but doing it ahead of time will make it easier if you're not a whiz in the kitchen.

Haluski:
To make the Haluski itself, mix all 4 ingredients together and knead on a floured board. When I did it, it initially was very dry, but as it was kneaded it came together and absorbed all the flour. The consistency when done was not sticky, and texture was almost plasticine. Now, take a hunk of the dough and roll it with your hand into what looks like a snake - a round, long strip of dough about 1 inch thick. Once you have that, take a butter knife or regular non-sharp knife and cut off slices about 1/4-1/2 inch thick. It will deform as you do it; that's fine. The end result looks like this:

Potatoes:
Peel 2-3 Medium potatoes. Add to pot of water, with about 2tbl salt, and boil until about mostly done. At this point, add haluski, all at once (yes, this'll drop the temperature) and boil approximately 4-5 minutes. The haluski will float to the top when done; you can either fish out the potatoes & haluski, or drain.

Serving:
Get a large serving bowl. Pour the haluski and potatoes into it, pour the onions & butter over them, then take the sausage and place it on top.

Eating:
In our family, we use a serving spoon to grab mass quantities of potatoes/heluski, and a piece or two of kielbasa. Pour a splash of vinegar over the potatoes/onions/heluski - even my wife the Southerner agrees on that. Eat.

[Index] Find duplicate indexes

2009-03-02T14:23:00.002-06:00

I've been working on indexes lately, if it's not been obvious. I went looking for code to detect identical indexes, and this one seems the best.

http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx

Here's the top one, which looks for identical indexes.


-- exact duplicates
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

[Replication] List your subscriptions, including articles, from the subscriber machine

2009-02-12T15:05:00.008-06:00

Sometimes you need to know what articles you've received via a subscription. From Computer B (which is a subscriber), you can run this against Computer a (the publisher or distributor) and get a list of articles (tables) that are being replicated. A rare thing to need, but durn handy. You will need a linked server on Computer A that can read from Computer B, and you need to run it against each replicated database.

(remember that are you need to copy even though you can't see the whole line
DECLARE @sqlstatement VARCHAR(1000)
SELECT @sqlstatement = 'SELECT identity(int, 1,1) as ID,
* INTO ##Publications FROM OPENQUERY([computera],
''SET FMTONLY OFF {call replicated_db..sp_helpsubscription}'')'

EXEC(@sqlstatement)
SELECT * FROM ##publications where subscriber = @@servername
DROP TABLE ##publications

[Query] Case-sensitive query on a case-insensitive server

2009-02-11T09:39:00.003-06:00

SQL Collations play an important role in queries - if you're lucky enough to have a case-insensitive server (which many are; it's the default), then you are saved from having to make sure EveryThing Is Capitalized just Right.

However, there are times when you need it.
http://www.mssqltips.com/tip.asp?tip=1032


SELECT *
FROM dbo.CaseSensitiveTest
WHERE Value1 LIKE '%Test%' Collate SQL_Latin1_General_CP1_CI_AS
GO

That will give you only the values that are capitalized like "Test" is.

[SQL] BULK INSERT and BCP -n

2009-02-10T12:38:00.002-06:00

I came across this while rereading the man pages for BCP and BULK INSERT:

http://msdn.microsoft.com/en-us/library/ms188365.aspx

DATAFILETYPE value	All data represented in:

native	Native (database) data types. Create the native data file by bulk importing data from SQL Server using the bcp utility.

I like BULK INSERT more than BCP, but you need BCP to export data. I was unaware that you could BCP a file out in Native format, and use BULK INSERT to import it. Doh!

Latkes!

2009-02-04T23:02:00.002-06:00

This picture does not do them justice. They were awesome. Took about 20 minutes to make, another 20 to cook. Came out really well, considering I've never made latkes (potato pancakes) before. Only change I made was to add more oil in the pan - 2 tbl of oil isn't enough. And since I hadn't made them before, I decided to skip the ricer - layer paper towels and potatoes in a bowl, then push down with all your might and get as much water out as possible.

The below link is safe, but the page with the recipe does have some... interesting pictures.

http://www.boingboing.net/2008/12/29/susie-bright-the-lea.html

Why is this baking? Easy - store them in a warm oven while you make them. And this way you're not worrying about the others getting cold.

[Sweets] Nutella Biscotti

2009-02-04T22:49:00.003-06:00

And now for something actually baked.

Recipe from here: http://bakingbites.com/2005/06/nutella-biscotti/ Note that my picture doesn't look as good as their picture. But I will say, it came out quite well following the recipe as written.

[Profiler] Found: "Error: 208, Severity: 16, State: 0"

2009-01-29T13:29:00.003-06:00

So, while running some code, trying to figure out why it's failing, I see this:


Error: 208, Severity: 16, State: 0

What is it, you ask? Some sort of weird bug, maybe, involving temp tables.

Run this:


exec ('exec master..xp_cmdshell ''dir c:\''')

No problems.

Run this:


create table #Listing (results varchar (255))
insert #Listing (results) 
exec ('exec master..xp_cmdshell ''dir c:\''')

Bingo.

Why is this the case? I wish I knew.

[Tuning] Getting rid of Dynamic SQL

2009-01-23T08:41:00.004-06:00

We've all done it. There's times when you just need to write something quick and dirty that deals with a simple problem. And there doesn't seem to be an easy way to make it proper SQL, so you write some dynamic SQL instead.


CREATE PROCEDURE cases_select_dynamic
@sortBy varchar(100),
  @status Int
AS
BEGIN
IF len(@sortBy)>0
BEGIN
 DECLARE @sql varchar(max)
 SET @sql='SELECT * FROM cases WHERE 
    status = ''' + CONVERT(VARCHAR(100),@status) + '''
 order by ' + @sortBy
 EXEC (@sql)
END
ELSE
BEGIN
 SELECT * FROM cases WHERE 
    status = @status
END
END

Fortunately, there are people out there who don't particularly like dynamic SQL. Fortunately for us, because they've found ways around it. Instead of the above, do something like


CREATE PROCEDURE cases_select_nondynamic
  @sortBy varchar(100),
  @status Int
as 
BEGIN
 SELECT * 
 FROM cases
 WHERE  status = @status
 order by CASE @sortBy WHEN 'finished' THEN finished ELSE NULL END, 
   CASE @sortBy WHEN 'name' THEN name ELSE NULL END, 
   CASE @sortBy WHEN 'lead' THEN lead ELSE NULL END
END

Which does the same thing. A little more work, but you gain several benefits.

Dynamic SQL runs under the users context, so they need perms to the tables. Nondynamic means you just grant normal SP rights.
Cached plans. 2000/2005 are good about reusing the cached plan - if it's IDENTICAL. Unless people are always running the exact same query, parameters and all, it won't cache. Nondynamic gets cached once and used for all parameters.
Faster. In my case, running the exact same parameters for both versions resulted in the dynamic version having a 89% Query cost (relative to the batch), while the nondynamic version was a mere 11%.

So go out there and convert 1 SP today to be nondynamic. Read Erland's page, he's got a lot of examples and a lot of thought in there.

http://www.sommarskog.se/dynamic_sql.html . Erland is a SQL god.

[SQL] A very easy way to convert tinyint to hex

2009-01-15T17:41:00.003-06:00


SELECT CONVERT(VARBINARY(1), 254)
0xFE

If you need more leading zeroes, raise the size of the Varbinary.


SELECT CONVERT(VARBINARY(8), 254)
0x000000FE

[SQL] Running code on most databases on a server

2009-01-12T11:06:00.003-06:00

Everybody should already be using sp_MSforeachDB to do things in all databases.

This is a really cheap way of only doing it to some databases; just add the list in two single-quotes (since you're running it within sp_MSforeachDB)


sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'')
begin 
 use ?
 exec sp_updatestats 
end'

[Notification] and a version to watch a SPID

2009-01-08T15:15:00.002-06:00


WHILE (SELECT    COUNT(*)
FROM     sysprocesses WHERE spid = 230 AND cmd IN ('ALTER table','CREATE index')
)>=1
BEGIN 
  PRINT 'waiting'
        WAITFOR DELAY '00:01:00'
END         

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBA',
    @recipients = 'dba@thebakingdba.com',
    @subject = 'index creation done'

[Jobs] Code to email when job finishes

2009-01-07T16:44:00.003-06:00

If you have to run a job outside its normal time, and didn't remember to change the notification before you ran it, fret not. By hitting the system tables, you can have it notify you when the job finishes.


WHILE (SELECT    COUNT(*) 
FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
           ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_date >= '20090107' and --today's date
         j.NAME = 'your_job_name' AND 
--max step for the job, so you don't get emailed when step 1 of 5 finishes
         h.step_id = 3 
         )=0
BEGIN 
  PRINT 'waiting'
        WAITFOR DELAY '00:01:00'
END         

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBAs',
    @recipients = 'thebakingdba@yourcompanyname.com',
    @query = 'SELECT    j.[name],
         s.step_name,
         h.step_id,
         h.step_name,
         h.run_date,
         h.run_time,
         h.sql_severity,
         h.SERVER,
         h.run_status
FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
           ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_date >= ''20090107'' and 
         j.NAME = ''your_job_name'' AND 
         h.step_id = 3
' ,
    @subject = 'Your Job Finished',
    @query_result_separator =  ' ',
    @query_result_width = 750,
    @attach_query_result_as_file = 0 ;

[Russian] Olga's Cabbage Rolls (Holubtsi/Helutsi)

2008-12-30T13:54:00.002-06:00

(apparently my original posting showed up with the original date I started - reposting so that it's at the top)

Let's see, we've done my Baba's borscht (which, honestly, you should make - calling it "beet soup" does it a disservice, especially when the beet slivers are white when done), but we still have several left to do:

Piroshki (meat pies, baked)
Piroshki (potato pies, fried)
Vareniki
Haluski (helushki?)
Pork Stew (Goulash)

...and Cabbage Rolls (aka "pigs in a blanket", aka "heh-lute-see", which according to Wikipedia is probably spelled Holubtsi but I'm not positive of.)

Basically it's just beef, rice & onions wrapped in a cabbage leaf, fried in a tiny bit of oil, then baked with a sour cream & tomato sauce. So, it does count as baking! *laugh* I spent the better half of a day working with Baba on this, taking her recipe and adding footnotes.

And yes, it tastes much better than that description. Think spiced meat served with a pink sauce, and you're not far off.

Ingredients for the Cabbage Rolls themselves:
3 lb chuck meat (lean), OR 2 lb chuck meat (lean) and 1 pound boneless country ribs
1 1/2 cup rice, cooked as it directs
3 medium onions, diced and chopped
6+ tbl corn oil
5 tbl dried parsley
salt & pepper to taste
2-3 heads cabbage

Ingredients for the sauce:
15 oz can tomato sauce
8 oz can tomato sauce
10.5 oz beef buillion (we use the Campbell's double strength, but you can also use regular beef broth)
8 oz sour cream

Equipment needed:
Grinder (coarse blade)

Prepping the filling:
Take your meat and boil it in water, at least enough to cover. Save 1/2 cup broth. You can do either all beef, or beef and pork - she normally does beef & pork, but not everybody likes pork.
Take the cooked meat and grind on the coarse setting. Yes, you need to cook it then grind it - the texture is totally different if you do it the other way around.
Prepare the 1.5 cups rice as per package directions.
Take the 3 onions, dice and simmer over medium heat with 2-3 tbl oil until clarified.
Now, mix all together - the ground-up & cooked meat, rice, 1/2 cup broth, dried parsley, onions, and salt & pepper to taste. You definitely want this to taste good on its own, so don't be stingy with the pepper.

Prepping the rolls:
Take head of cabbage, score the base so that the leaves will be able to come off. Put in a pot bigger than needed to boil the cabbage. Add water, then heat to boiling. Turn off heat after it boils 30 seconds - we are trying to wilt the leaves and make them pliable. Don't use the whole cabbage - the inner leaves are just too small to hold enough meat.

Making the rolls:
Pull off the outer-most leaves and throw away.
Remove one leaf at a time. Cut away as much of the "vein" as possible on the leaves, so that they can be easily folded. Put 1-2 heaping serving spoon (1.25-2 oz total) of meat in the middle, then fold the sides in, overlapping, then fold over the "top" of the leaf, then fold over the base of the leaf. You now should have a small package. Cook this in 2-3 tbl oil on both sides until GBD (golden-brown and delicious), then set aside. At this point you can freeze them.

Making the sauce:
In a pot, add the tomato sauce, sour cream, beef bullion, 1-2 tsp black pepper and a SMALL pinch of salt (the beef bullion is a salt lick; if you go the low sodium route, you'll wind up having to add salt at the end. Sorry, this isn't healthy.) Heat on medium high heat, stirring frequently, until it comes to a boil. Boil for 30 seconds then take off heat.

Putting it all together:
Take a casserole dish (the deeper the better - we use an old corningware dish about 8 inches deep), and layer the rolls inside, open-side up. You want room between them for the sauce - don't just cram them together. I say open-side up because it's more forgiving when you're pulling them out, but be careful when pouring the sauce lest they open. Pour the sauce over until they're almost completely covered. Bake in a 350-degree oven for 30 minutes - you want it to be bubbly. Use a cooking spoon to pull out 2-3 rolls per person, pouring over sauce as desired. You can either cut it up and eat it, or (for those that don't like cabbage) remove the leaves and pour the sauce over the meat. Enjoy!

[Indexes] Size of unused indexes

2008-12-30T10:25:00.006-06:00

Was reading an old post of mine, and wanted to revisit it and add the ability to check the sizes of unused indexes. Pretty easy.

Updated this from a subquery to an inner join, which gives you the usage stats as well.


select

-- i.[object_id],

-- i.index_id,

 o.name as Table_Name,

 i.NAME AS Index_Name,

 i.type_desc,

-- p.partition_number,

 p.rows as [#Records],

 a.total_pages * 8 as [Reserved(kb)],

 a.used_pages * 8 as [Used(kb)],

 s.user_seeks,

 s.user_scans,

 s.user_lookups

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!

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

GO

[Unix] File management

2008-12-08T11:11:00.003-06:00

Yes, I know - it's neither Baking nor DBA. But still relevant.

I had a set of files named .Z.aa that I needed to decompress. GUNZIP doesn't like those extensions, so it ignores it. Here's a basic hack using Cygwin. (You are using Cygwin, right?)


for i in *.Z.aa;
do
    j=`basename $i .aa`;
   echo mv $i $j;
    mv $i $j;
   echo gunzip $j;
    gunzip $j;
done

The Baking DBA - select * from flour

[Table Partitioning] what is your lock escalation?

[Code] Using EXECUTE AS with a trigger to capture changes when a user doesn't have permissions.

Adding locations to SSMS "Open File"

[Network] Specified network name is no longer available

Use RAISERROR to return results in SSMS immediately

Using OUTPUT to set up foreign keys' data

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

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

[ETL] Importing UNIX files

[Tuning] Disk usage over time, checking deltas

[Trick] get midnight fast

[Russian] Meat Perushky / Perushki / Piroshki / Piroshki

[Russian] Olga's Pork Stew

[Cooking] Gumbo! Mama Saucier's Super Gumbo

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

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

[Excel] Converting float and dealing with leading zeros

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

[Code] Using INFORMATION_SCHEMA with temp tables.

[Powershell] Basic SQL query exported to CSV

[Powershell] Basics to run a SQL query

[Maintenance] Checking age of statistics

[Replication] Nasty bug and reinitializing without a snapshot

[Backups] Verify your backups physically exist

[Tools] Convert PDF to text

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

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

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

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

[Replication] Orphaned distrubution agent after SQL Agent crash

[SQL] BULK INSERT and adding an identity

[Replication] Orphaned agents after Agent crash.

Rule 5

CHAR() vs CHAR()

The Rules: 0-4

Varchar(n) vs Varchar(Max)

Texas Hill Country BBQ Brisket (yes, that's redundant)

[semantics] Why I hate ISNUMERIC

[Replication] more replication trouble tracking

[Code] IF EXISTS for procedures using INFORMATION_SCHEMA

[Replication] Better alternative to Replication Monitor

[Replication] Simple replication to find an issue

[QA] Linked server for QA - faking a prod server

[Tricks] Single-user-mode with a twist

[Code] Xtype definitions

[Ugly] Drop a view using Dynamic SQL

[Tips] HOW TO find page splits by reading the transaction log

[Tuning] Getting only the DATE from a datetime

[Tuning] Disk usage, redux

[Tuning] Filtered indexes...and filtered statistics.

[Files] Tricking Excel with SP_SEND_DBMAIL

Table Partitioning

[Offtopic] How to print to a networked Laserwriter (XP)

(found some screenshots at: http://blog.eppesconsulting.com/2009/05/05/AppleLaserWriter16600PSOnWindowsVista.aspx)

Printing to an Apple Laserwriter 16/600 using IP from a Win 2K and Win XPclient.

[Powershell] What a piece of $#!+

[Partitioned Tables] Does the clustered index take up space if referenced?

[Install] Restarting your SQL service within SQL

[Replication] Finding the commands that are breaking

[Sp3] Another reason not to use DDL Triggers

[Tuning] SPARSE varchar calculation

[Jobs] Quick & Dirty - is the job running?

sp_MSforeachDB - skipping databases

Kulich - Russian Easter Bread

[Code] IF EXISTS table check

[Replication] Alternative to SQLMonitor (Replication Monitor)

IF EXISTS for tables

[Setup] Making sure your disks are optimized

[Text] Counts of a word within a file. Not by line, total.

Replication and DDL Triggers - DO NOT MIX

DDL Triggers

Piecemeal (aka Partial) filegroup restores from litespeed (sorta)

[Tuning] Getting file disk usage

[Compression] Seeing how much savings you'll get

[Jobs] Search job history for a particular date range

[Servers] No linked server? Need inserts? No problem!

[Tip] getting local context from sp_ in master

[Cruft] New DMV in 2008 - SP details

[Cruft] Fixing dependencies

Printing to an Apple Laserwriter 16/600 using IP from a Win 2K and Win XP
client.