A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Friday, March 26, 2010

Yes Virgina, there is a .NET path builder

In the .NET world, if you are gluing paths together with string concatenation, or some reasonable facsimile thereof, STOP.  I know I'm not the only one who didn't know this method existed in the framework as I see plenty of code with "System.IO.File.Open(basePath + fileName, System.IO.FileMode.Open);"  In the python world, it was easy open(os.path.join(basePath, fileName)).

What's the problem?  Directory separators.  In the Windows world, it's a slash \.  In the C#/C++/C world, \ is also the beginnings of an escape sequence.  If I wanted a string that held a newline (return/enter), a tab, a bell, etc, I would have to encode that with an escaped sequence like \n, \t, \a  Despite two characters there, it's a 1 character size.  What does escaping have to do with building directory paths?  The true problem is consistency.  Do you store your path with a trailing slash or not?  The coding above assumes basePath has a trailing slash but what about the people using your method?  Perhaps they aren't versed in using the raw/literal string (the at sign @ in path1) so they are going to save themselves the two extra keystrokes to add the trailing slahes as shown in path2 below.  Over time, that simple string concatenation will grow to look for trailing slashes, adding where it's required, doing some monkey patching when there are quotes etc.  

If you have your own library and you love using it, by all means, keep it up.  As for me and the code I review, we're going with System.IO.Path.Combine  If you click through to the Combine method on that link, they have a much larger example of using Combine but this snippet should suffice to show the proper way to build the path.

string fileName = @"Zapotec.bmp";
string path1 = @"C:\windows";
string path2 = "C:\\windows\\";
string path3 = @"C:\windows\";
Console.WriteLine(System.IO.Path.Combine(path1, fileName));
Console.WriteLine(System.IO.Path.Combine(path2, fileName));
Console.WriteLine(System.IO.Path.Combine(path3, fileName));

Tuesday, March 23, 2010

SSIS Lookup task with CTE SQL Query and Enable Memory Restriction checked causes Error

Isn't that a mouthful? Common Table Expressions are valid for use against a SQL Server database version 90 and above (2005 & 2008). SSIS works also works for version 90 and above. CTEs are perfectly valid data sources in SSIS. They can be perfectly valid sources in the Lookup component as well, as long as you are using Full Caching. Once you attempt to set the caching to Partial or None, then your package will fail to validate.

The reason for the failure has to do with some of the internal voodoo the Lookup component does to accomplish this. It's readily apparent if you click into the advanced tab and check the box for Custom Query. SSIS takes the most brain dead approach to ensuring it can build out the query in Partial/No cache by wrapping your query with "select * FROM (YOUR QUERY HERE) [RefTable] where [refTable].[critera] = ?" Given the title of this post, the astute reader can see the problem. From BOL: "When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon."

2005 Error message
Validation error. MyDataFlowName: MyLookupTask [84]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

2008 Error message
Error at Data Flow Task [Lookup [52]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
Error at Data Flow Task [Lookup [52]]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties.
Error at Data Flow Task [SSIS.Pipeline]: "component "Lookup" (52)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)

So, what to do? Until they fix it, my money is on "Closed, won't fix" as a resolution, the query will have to be rewritten in a non-CTE fashion. Heaven help you if it was a recursive CTE

References:
http://msdn.microsoft.com/en-us/library/ms175972.aspx
https://connect.microsoft.com/SQLServer/feedback/details/531823/ssis-lookup-task-with-cte-sql-query-and-enable-memory-restriction-checked-causes-error

Friday, March 19, 2010

sysname is not just an alias for nvarchar(128)

I had noticed the datatype sysname in sys.types before but never thought much about it.  It seemed obvious that it would be used for the internals of SQL Server to put a limit on object lengths.  A default behaviour that I had not noticed from sys.tables is the is_nullable column has a value of 0 for this type.  Fast forward to today, I had built some table variables to capture roles and permissions and used sysname for all of my fields, even the nullable ones.  I'm lazy and didn't want my fingers to stray into the number row.  I ran my script and it exploded everywhere

Cannot insert the value NULL into column 'server_role', table '@PEOPLE'; column does not allow nulls. INSERT fails.

That didn't make sense, my table declaration approximated "DECLARE @PEOPLE(server_role sysname) TABLE"  By default, a column is nullable unless you explicitly state it's not nullable.  

CREATE TABLE really (blah sysname)
EXECUTE sp_help really

Column_nameTypeComputedLengthPrecScaleNullable
blahsysnameno256 no

Huh, by golly it's automatically not null because of the way the datatype is defined in sys.types. That's kind of cool.  Next logical thought of course is, what happens if I explicitly declare it as nullable, who wins?

CREATE TABLE really_really (blah sysname NULL)
EXECUTE sp_help really_really

Column_nameTypeComputedLengthPrecScaleNullable
blahsysnameno256 yes

Key take away:  Don't be lazy.  Explicitly declare your sizes and nullability lest it bite you in the britches.


References

Thursday, March 18, 2010

Twitter warm fuzzy

The internet overlords at work really need to remove Twitter from the list of banned sites.  Sure, there's plenty of noise on there but I absolutely love it as an option for interacting with my peers.  Case in point, I had a question about SQL Server Reporting Services as I wasn't sure if mine was installed correctly and @SeanBoon, the program manager for SSRS responded.  How awesome is that?  It's not an isolated event either, at least for SQL tweeps.  Questions and answers on SSIS, backups, recovery, optimization, best practices, etc all fly through the twittersphere.  As much as I'd love to attribute this phenomenon to the sheer awesomeness of our community, I think there are other groups out there organized and sharing information in real-time. Maybe I'm just an information hippie but I think that's far out, man.

Wednesday, March 17, 2010

Disk Partition Alignment

I started here nearly 3 years ago.  Around that time, I also began to attend the Kansas City SQL Pass meetings.  The president at that time had informal chit-chat sessions starting those meetings and asked the question "Who knows if their organization has disk partition alignment?"  A few meetings later, they had an in depth presentation on it and while I've never touched SAN configuration, my key take aways were the default partitioning for Windows 2003 and earlier was most likely incorrect for a given SAN and that misalignment could result in a 30% decrease in read/writes as well as increase disk usage.  A hallway conversation with a DBA indicated that was not an issue in our organization.  

Fast-forward to yesterday's twitter-ings.  Someone had linked Paul Randal's post about how to check partition alignment.  Diagnosing the problem is so easy, even a developer could do it!  And this developer did and the results were unexpected.

H:\>diskpart

Microsoft DiskPart version 5.2.3790.3959
Copyright (C) 1999-2001 Microsoft Corporation.
On computer: DEVSQL0

DISKPART> list disk

  Disk ###  Status      Size     Free     Dyn  Gpt
  --------  ----------  -------  -------  ---  ---
  Disk 0    Online        15 GB  8033 KB
  Disk 1    Online        25 GB   736 KB   *
  Disk 2    Online       150 GB  1023 KB   *

DISKPART> select disk 2

Disk 2 is now the selected disk.

DISKPART> list partition

  Partition ###  Type              Size     Offset
  -------------  ----------------  -------  -------
  Partition 1    Dynamic Data       150 GB    32 KB

DISKPART> select disk 1

Disk 1 is now the selected disk.

DISKPART> list partition

  Partition ###  Type              Size     Offset
  -------------  ----------------  -------  -------
  Partition 1    Dynamic Data        25 GB    32 KB

DISKPART> exit

Leaving DiskPart...

H:\>wmic partition get BlockSize, StartingOffset, Name, Index
BlockSize  Index  Name                   StartingOffset
512        0      Disk #0, Partition #0  32256
512        0      Disk #1, Partition #0  32256
512        0      Disk #2, Partition #0  32256

That's not so good.  Now, admittedly those are Dynamic disks so the second article by Jimmy May comes into play but my crude divination of that report tells me that server is not going to be aligned.  I didn't have RDC access to the non-production servers but I do have the ability to deploy and run arbitrary SSIS packages so choice usage of "Execute Process Task" and a package later, I had a report on non-production servers.  Verdict:  they all have the same block size.

Lesson learned?  Trust, but verify.  Cleanup on this, while straight forward according to the articles, will not be pleasant.

Reference articles 

Sunday, March 14, 2010

Visual Studio Team System 2008 Database Edition install CF

I pulled the trigger and finally bought my own MSDN license.  I wanted to take advantage of the 2010 upgrade deal and invest in myself as a software professional so "Visual Studio Team System 2008 Database Edition" is now mine.

After a 4GB download, I double clicked on the install in my new Windows 7 VM and *boom*  "Windows cannot access the specified device, path, or file. You may not have the appropriate permissions to access the item"  If you look at the pricing for VSTS, you can understand how vexing that might be.  A few unfruitful days of "run as admin", resetting permissions, various compatibility modes, etc led me nowhere.  It was maddening, I could rename the file, set various flags on it but to no avail. I wasn't looking forward to downloading the file again but was set to do so when I took one last shot at google.  I searched for "en_visual_studio_team_system_2008_database_x86_dvd_x14-26465" which is the name of the EXE.  3 hits and http://thom.pantazi.com/AppListTool/view_InstallSet_list.asp was the winner.  As part of their install sequence, I see they mount that file. For whatever reason, when I downloaded the file, it was tagged as an EXE but hey, it's an ISO.  Why Windows 7 didn't say "this is an invalid binary, you tool" is beyond but hey, who cares?  I have Datadude installing!

Wednesday, March 10, 2010

Post-build event

I was looking for an article I had seen that discussed how to use the installer project to simplify my SSIS component deployment and I ran across Todd McDermid's article The Post-Build Command Line for SSIS Custom Objects (Updated)

That's pretty.  Way more elegant and reusable than what we are doing here http://billfellows.blogspot.com/2009/09/visual-studio-build-macros.html We just copy the DLL into PipelineComponents and Tasks, regardless of what it is.  However, the batch script failed on a default machine because gacutil is not part of the standard path.  Sure, you can add it in there. Or you can add some guessing logic into those scripts.  I went with the later.

Using the files on Todd's Skydrive, I added my sauce.  I suspect using $(FrameworkSDKDir) will provide the most consistent reference for gacutil which is why it's listed last

SET GACUtil=unset

REM Not sure why one would choose windir vs SystemRoot
REM both seem to evaluate to same value on available systems
SET GACUTIL_PATH_win1="%windir%\system32\dllcache\gacutil.exe"
SET GACUTIL_PATH_win2="%windir%\gacutil.exe"
SET GACUTIL_PATH_v1="%windir%\Microsoft.NET\Framework\v1.1.4322\gacutil.exe"
SET GACUTIL_PATH_v2005="%ProgramFiles%\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe"
SET GACUTIL_PATH_v2008="%ProgramFiles%\Microsoft SDKs\Windows\v6.0A\Bin\gacutil.exe"
SET GACUTIL_PATH_v2008a="%ProgramFiles%\Microsoft SDKs\Windows\v6.0A\Bin\gacutil.exe"
SET GACUTIL_PATH_framework="$(FrameworkSDKDir)bin\gacutil.exe"

IF EXIST %GACUTIL_PATH_win1% SET GACUtil=%GACUTIL_PATH_win1%
IF EXIST %GACUTIL_PATH_win2% SET GACUtil=%GACUTIL_PATH_win2%
IF EXIST %GACUTIL_PATH_v1% SET GACUtil=%GACUTIL_PATH_v1%
IF EXIST %GACUTIL_PATH_v2005% SET GACUtil=%GACUTIL_PATH_v2005%
IF EXIST %GACUTIL_PATH_v2008% SET GACUtil=%GACUTIL_PATH_v2008%
IF EXIST %GACUTIL_PATH_v2008a% SET GACUtil=%GACUTIL_PATH_v2008a%
IF EXIST %GACUTIL_PATH_framework% SET GACUtil=%GACUTIL_PATH_framework%

I then replaced all mentions of GACUtil with %GACUtil%  and voilĂ !

Friday, March 5, 2010

GPO Group Policy Object

This GPO not that GPO 

Ultra brief post but this will serve as a reminder for myself.  When I'm looking for the group policy editor/group policy viewer on a Windows XP machine, the program is C:\WINDOWS\system32\gpedit.msc

Tuesday, March 2, 2010

SSRS pages displayed count does not match pages printed

Coworker came to me with an interesting problem.  She designed a report for display in SSRS Report Manager.  QA had reported a defect where the built-in pagination was not matching the physical pages kicked out by the printer.  The display said X of 31 pages but when it printed, it consumed 37 pages.  I was at a loss.  If it was doubled, then I'd say something was up with a margin bleeding over but nope, they looked clean and it was just a few pages difference.  I said this may be some ultra-queer defect in SSRS and wished her well with Google searches.  Creative thinking/research not being her strong suit, she persisted in talking about it.  "No, see a printed page is this many rows, a displayed page goes this much further" and showed me page 2.
*click*  Light bulb went on.  I laid the two printed sheets together (landscaped) and then set a third page on top of them rotated 90 degrees (portrait).   Whaddya know, the edge of the portrait page bumped right up against the final row that was displayed.  Not sure how she managed to do it, but the printed page was landscaped but displayed/rendered in portrait mode.  

Nifty SSRS pages I wish I had more time to read

Resources