Reporting Services Scripting for fun and profit

After spending too long on Google, and finally getting an appropriate answer from a colleague, I thought it wise to record this information in case someone finds themselves in a similar situation.

This stackoverflow sums up my issue nicely: “I need to create a repeatable process for deploying SQL Server Reporting Services reports. I am not in favor of using Visual Studio and or Business Development Studio to do this. The rs.exe method of scripting deployments also seems rather clunky. Does anyone have a very elegant way that they have been able to deploy reports. The key here is that I want the process to be completely automated.”

The current accepted answer is nice, but it goes a little too far for my taste. However, the real gem is in this link for Reporting Service Scripter. It’s a delightfully simple utility that does as advertised. I can then take the code it generates and check it into our source control system. Our deployment software just needed to be pointed at the Windows Command Script file generated, and we were off and running.

Hopefully this will help out anyone else in a similar situation.

Posted in SQL Toolbox | Leave a comment

WordPress 3.0.1 upgrade hung!

Just in case anyone’s site gets hung on an upgrade, this is a great tip to get out of maintenance mode.

Stuck WordPress Fix

I have no idea what caused the issue – I have two WordPress sites with identical plugins. One worked, the other one didn’t.

Posted in Uncategorized | Leave a comment

Thought of the day

I was doing some research for a project and stumbled across this gem. I did a quick search for a source, but only found variations on a theme.

People don’t want to buy a quarter-inch drill. They want a quarter-inch hole.

Remember to give people what they really want.

Posted in Uncategorized | Leave a comment

Outside the Box Thinking

I had a problem to solve recently, and essentially it boiled down to a need to do multiple pivots in one statement. After playing with it for a little while and not quite getting it the way I wanted, I turned to Google which led me (seemingly as always) to stackoverflow.com. There I stumbled upon this gem of a Q&A exchange that (almost) perfectly described my problem, and offered an awesome response that I wouldn’t have considered otherwise.

SELECT
field1
, [1] = MAX(CASE WHEN RowID = 1 THEN field2 END)
, [2] = MAX(CASE WHEN RowID = 2 THEN field2 END)
, [3] = MAX(CASE WHEN RowID = 3 THEN field2 END)
, [4] = MAX(CASE WHEN RowID = 4 THEN field2 END)
FROM (
SELECT
field1
, field2
, RowID = ROW_NUMBER() OVER (partition BY field1 ORDER BY field2)
FROM tblname
) SourceTable
GROUP BY
field1

Thanks Peter – I can’t seem to contact you directly for the contribution.

Posted in Uncategorized | Leave a comment

Increasing the size of a vmware virtual disk

I use VMWare Workstation on my personal laptop to run my work environments. The brief reasoning behind this is to separate work and home on my machine.

My base system is windows 7, but I have a VM with windows xp sp3 with all of my development tools installed. The environment started running out of space, and I needed to expand the virtual disk. I thought (incorrectly) that this would be a simple fix, and I was wrong.

First, it should be noted that it is simple to add a virtual hard drive to your virtual machine. However, I wanted to expand the partition that the machine was on – which is a much trickier operation.

Here were the steps I followed:

— I exported the current version of my work VM to a new VM. This was to remove any snapshots that are currently in my machine. This was to avoid any issues with the old snapshots I have saved – just in case.

— Next, I went to the installation directly of my VMWare Workstation and used vmware-vdiskmanager.exe /? to get a grasp of the syntax and then expanded the hard drive to the new size.

— Finally, I used the free tool gparted – located here – to boot the VM, and used the tools included to re-size the partition.

— The machine booted up and required a reboot, but other than that I’ve had no issues.

Hopefully this will help a few folks out, as searching for a solution online was surprisingly difficult.

Posted in Uncategorized | Tagged , | Leave a comment

Hashbytes – VARCHAR vs. VARBINARY Performance

A recent post on sqlservercentral.com got me thinking about Hashbytes recently. I was ignoring the issue of getting a false positive using a hashbyte solution to check for errors, and focused on the performance of the routine.

My initial pass on my testing was simply this.

SELECT
REPLICATE('x', 8000)
, HASHBYTES( 'sha1', REPLICATE('x', 8000) )

After reading into the BoL a little more, I noticed that HASHBYTES can accept varchar, nvarchar and varbinary. It was at this point that I wondered about how the performance would or wouldn’t change based on the input, here are my methods and my result.

--Variables
DECLARE
@TimeStart DATETIME
, @TimeEnd DATETIME
, @Counter INT
, @HashHolder VARBINARY  -- Simply a container, overwritten many times.
, @StringToHash VARCHAR(MAX) -- string to test speed of hashing
, @VarbinaryToHash VARBINARY(MAX) -- varbinary representation of @StringToHash to test speed of hashing

--Variable Setup
SELECT @TimeStart = GETDATE()
, @Counter = 0
, @StringToHash = REPLICATE('x', 8000 )

SELECT @VarbinaryToHash = CONVERT( VARBINARY, @StringToHash )

--While loop set to 10k
WHILE @Counter < 10000
BEGIN
--swap these statements as needed to test both situations
--SET @HashHolder = HASHBYTES( 'sha1', @StringToHash )
SET @HashHolder = HASHBYTES( 'sha1', @VarbinaryToHash )

SET @Counter = @Counter + 1
END

SELECT @TimeEnd = GETDATE()

--Results
SELECT @TimeStart
, @TimeEnd
, @TimeEnd - @TimeStart
, @Counter

Here’s what I found:

Type – Records – Time

String – 100,000 – 00:00:04.030
String – 1,000,000 – 00:00:40.077
String – 10,000,000 – N/A
Varbinary – 100,000 – 00:00:00.737
Varbinary – 1,000,000 – 00:00:07.017
Varbinary – 10,000,000 – 00:01:04.720

As you can see – at least in this limited test – the Varbinary hashing far out performs the string method. The reason for this test was I’m looking at using a combination of methods to detect change in large data sets, and I was testing if it would be worth my time to convert everything to varbinary and then hash, or convert to varchar(max) and then hash.

I still need to investigate the overhead of converting everything to varbinary of course!

Posted in SQL Server Performance | Tagged , , , , | Leave a comment

Windows Server 2008 & SQL Server 2005

It seems that Windows Server 2008 and/or SQL Server 2005 doesn’t automatically add a firewall exception to the OS for access to the database.

As a result, an exception needs to be added, by following instructions found here.

Posted in SQL Toolbox | Tagged , | Leave a comment

First Day of Current Month

SELECT DATEADD( mm, DATEDIFF( mm, 0, GETDATE() ), 0 ) AS FirstOfMonth

Since I always manage to get the syntax wrong from memory, above is a SELECT statement that will return the first day of the current month.

Edit: As usual, Joshua has a good point. Also, it just goes to show that re-inventing the wheel every time, instead of re-using code, isn’t the way to go.

Posted in SQL Toolbox | Tagged , , | 1 Comment

XML Modification

As a followup to this, I needed to shred the xml (passed into the process) to the values denoted.

See this sample. Note the error that’s commented out from the first select statement.

--Params we need
DECLARE @XMLBlob XML
DECLARE @FirstInt INT
DECLARE @SecondInt INT

--XML to play with
SET @XMLBlob =
'<Parameters>
    <Parameter Name="FirstInt">NULL</Parameter>
    <Parameter Name="SecondInt">2</Parameter>
</Parameters>'


--This will error: Conversion failed when converting the nvarchar value 'NULL' to data type int.
SELECT @FirstInt = @XMLBlob.VALUE('(//Parameter[@Name="FirstInt"])[1]', 'INT' )
--This will work!
SELECT @SecondInt = @XMLBlob.VALUE('(//Parameter[@Name="SecondInt"])[1]', 'INT' )

So, I needed to try to solve this, so I came up with the following T-SQL at first…

SELECT @FirstInt =
    CASE
        WHEN @XMLBlob.VALUE('(//Parameter[@Name="FirstInt"])[1]', 'VARCHAR(100)' ) = 'NULL' THEN NULL
        ELSE @XMLBlob.VALUE('(//Parameter[@Name="FirstInt"])[1]', 'INT' )
    END

The above code works, but is far from ideal. Especially considering that this is a simplified example; the real one has many, many more params. After completing the case statement, I took a step back to look at what I had done and laughed at myself.

Here’s the real solution…

SET @XMLBlob.MODIFY( 'delete (//Parameter[text()="NULL"])' )

All the statement does is look for matches on //Parameter where the text is the string “null” as compared to the value null. Then, it deletes these rows.

Now, here is the whole thing from beginning to end.

--Params we need
DECLARE @XMLBlob XML
DECLARE @FirstInt INT
DECLARE @SecondInt INT

--XML to play with
SET @XMLBlob =
'<Parameters>
    <Parameter Name="FirstInt">NULL</Parameter>
    <Parameter Name="SecondInt">2</Parameter>
</Parameters>'


SET @XMLBlob.MODIFY( 'delete (//Parameter[text()="NULL"])' )

--This works now!
SELECT @FirstInt = @XMLBlob.VALUE('(//Parameter[@Name="FirstInt"])[1]', 'INT' )
--This will still work!
SELECT @SecondInt = @XMLBlob.VALUE('(//Parameter[@Name="SecondInt"])[1]', 'INT' )

This won’t work in all applications – for example ones where you needed to know if something was passed in – but it works when all you’re doing is checking for the existence of a value.

Posted in SQL Server Development | Tagged , , | Leave a comment

XML Nodes Query

Something’s that easy to forget due to the case sensitive nature of the syntax.

XML that looks like this…

<Parameters>
<Parameter Name="FirstInt">NULL</Parameter>
<Parameter Name="SecondInt">2</Parameter>
</Parameters>

and has this t-sql run against it…

SELECT
    T.c.VALUE( '@Name', 'VARCHAR(36)' ) AS Name
    , T.c.VALUE( '.', 'VARCHAR(36)' ) AS VALUE
FROM @ExecutionParameters.nodes('//Parameter') AS T(c)
WHERE T.c.VALUE( '.', 'VARCHAR(36)' ) = 'NULL'

returns a rowset that looks like…

Name      Value
FirstInt  NULL
SecondInt 2
Posted in SQL Server Development, SQL Toolbox | Tagged , | Leave a comment