For those that are interested, I have a zip file that contains the presentation and project files from my SSIS tutorial presentation.

SSISPresent_NovaCodeCampSouthMar2008.zip (1.77 MB)

I was shocked....SHOCKED...to realize that an hour and 15 minutes is not enough time to cover this material. Seriously, a minimum of 2 hours is required to get a good understanding of the basics of SSIS. A good understanding of SQL Server 2005 and Visual Studio 2005 is recommended.

Learning Tree uses an entire week to cover the material, but the end result of their hands on training is that you walk out being able to practically apply SSIS immediately on the job.

 

Working with Microsoft MVP, Jeff Schoolcraft, we have organized a wonderful developer learning event on Saturday, March 29th at Strayer University in Woodbridge, Va.

CodeCamp SOUTH

Speakers include:

• Brian Noyes, Microsoft MVP
• Jonathan Cogley, Microsoft MVP
• Sahil Malik, Microsoft MVP
• Frank LaVigne, Microsoft MVP
• Antonio Chagoury, DotNetNuke Team Lead

and many more.

Topics to be covered include a host of new technologies along with some introductory courses. Here is a sample (you can see the rest at the NovaCodeCamp site).

• Visual Studio 2008
• SQL Server 2008
• SharePoint 2007
• DotNetNuke Module Development
• Office 2007 Development
• Service Oriented Architecture (SOA) Development
• Ajax
• Silverlight

For you developers, this is a golden opportunity to see presentations that would typically be presented at high priced conferences like Microsoft TechEd and VSLive. And it is FREE. However, registration is limited to the first 100 that sing up - so make your reservation today!

 

 

 

Thanks to Greg Robidoux and Jeremy Kadlec, I have now published my first MSSqlTips Tip.

Dynamically build connection objects for Microsoft Access databases in SQL Server Integration Services

There have been numerous posts on this issue, but in case you are looking for a solution I have compiled it here.

 

Problem:

You are working in the SSIS (SQL Server 2005 Integration Services) Business Intelligence Development Studio (BIDS) Control Flow. You are working on a solution that requires that you loop through a list of files. Therefore you add a Foreach Loop to the design surface (below).

 

 

You select the "Collection" attribute (left side) and find that the File Enumerator entry is missing (and so is the Item Enumerator).

 

The problem is associated with an incorrectly set permission on a registry subkey. I found mine associated with a Lexmark key. This issue can be very confusing, particularly when you are operating your SSIS and BIDS as an administrator (as you might on your development computer).  The Component Categories Manager is the application that is experiencing this issue.

 

Solution:

Warning: You will need to reboot your computer after completing these steps. Because you will be modifying the registry, you are advised to back up your registry before proceeding.

 

1. You will need a program to tell you which registry subkeys are effected. Please follow the steps in the following Microsoft knowledge base article to create this program (written in C#). Compile the program. It will run as a console application.

The title is a little misleading for our particular problem.

Microsoft KB: You receive an error message when you use Business Intelligence Development Studio to build a SQL Server 2005 Integration Services package

 

2. You must run the program as a user with a "limited account", if running under XP. Do the following steps.

 

A. Select Start|Programs|Accessories|Command Prompt

B. Change directory to the bin/debug folder of your compiled program.

C. Use the "Runas" command to run the application as a user with a limited account. Here is an example:

 

 

When you run the program as this low level user, the following window will appear with a listing of the problem subkeys:

 

D. Copy the GUID for the subkey.

E. Select Start | Run and in the Open text entry type "regedt32.exe".

F. In the registry editor, locate the HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID folder. From here, search for the GUID(s) you found from running the previous program.

G. When you find the subkey, right-click on it, and select "Permissions...". Add "users" to the list for each subkey that was reported in the previous program.

H. Reboot your computer.

 

When you open your SSIS project in the BIDS, you should now be able to see and select the File Enumerator.

Microsoft reaches standardization with the W3C XQuery recommendation with the addition of the LET clause in the next release of SQL Server, SQL Server 2008.

Here is an example of using the LET clause as part of the XQuery syntax.

CREATE TABLE customerData(        ID int IDENTITY(1,1) NOT NULL,        customerDocs xml NOT NULL,        updated datetime NOT NULL DEFAULT (getdate()),  CONSTRAINT [PK_customerData] PRIMARY KEY CLUSTERED (        [ID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO     INSERT INTO customerData(customerDocs)        VALUES(N'<?xml version="1.0"?>        <customers>               <customer FirstName="Bob" LastName="Hayes" Zipcode="91126" status="current">                      <order ID="12221" Date="July 1, 2006">Laptop</order>               </customer>               <customer FirstName="Judy" LastName="Amelia" Zipcode="23235" status="current">                      <order ID="12221" Date="April 6, 2006">Workstation</order>               </customer>               <customer FirstName="Howard" LastName="Golf" Zipcode="20009" status="past due">                      <order ID="3331122" Date="December 8, 2005">Laptop</order>               </customer>               <customer FirstName="Mary" LastName="Smith" Zipcode="12345" status="current">                      <order ID="555555" Date="February 22, 2007">Server</order>               </customer>        </customers>') GO   -- FLWOR with LET operator SELECT customerDocs.query('        <CustomerOrders> {        for $i in //customer        let $name := concat($i/@FirstName, " ", $i/@LastName)        order by $i/@LastName        return               <Customer Name="{$name}">               {               $i/order               }               </Customer>        }        </CustomerOrders> ') FROM customerData

It would be great to create a library of XQuery strings maintained in the database that developers could use and DBAs could manage. Unfortunately, you are going to have to jump through some hoops to make that happen in SQL Server 2005. The problem originates from the fact that the XML datatype "query" method can only take a literal string value.

 

This also causes problems if you want to use dynamic queries. You can create a SQL statement involving a dynamic query, but what you have to do is piece together the entire query as a string that is called in the dynamic SQL stored procedure "sp_executesql". But, you cannot pass the XQuery as a variable in dynamic queries either.

 

Below are some examples of what works and what does not.

 

The first example shows a typical XQuery call, then one that will not work that uses a variable passed to the query method.

 

-- This works! DECLARE @myResult xml;   SELECT @myResult = Study.query(  '   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ' ) FROM JustXML WHERE ID = 1;   SELECT @myResult; GO   -- This does not (PASSING VARIABLE INSTEAD OF STRING LITERAL)   DECLARE @myResult xml; DECLARE @myXquery nvarchar(500);   SET @myXquery =  '   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ';     SELECT @myResult = Study.query(@myXquery) FROM JustXML WHERE ID = 1;   SELECT @myResult; GO

 

Here are two examples, one that works and one that does not, using dynamic SQL queries.

 

-- Using Dynamic Queries with XQuery -- --------------------------------- -- THIS WORKS (dynamic sql) DECLARE @myXquery nvarchar(255);   SET @myXquery =  N'   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ' DECLARE @myDynamicSql nvarchar(500);   SET @myDynamicSql = N'SELECT Study.query(''' + @myXquery +        ''')  AS MYRESULT FROM JustXML WHERE ID = 1'   EXECUTE sp_executesql  @myDynamicSql;   -- But this does not!!! DECLARE @myXquery nvarchar(255);   SET @myXquery =  N'   <Root> {   for $f in //Form   where $f/@Status eq "COMPLETE"   return        <Forms> {               $f               }        </Forms> } </Root>  ' DECLARE @myDynamicSql nvarchar(500); DECLARE @ParmDefinition NVARCHAR(500);     SET @myDynamicSql = N'SELECT Study.query(@thisXquery)  AS MYRESULT FROM JustXML WHERE ID = 1' SET @ParmDefinition = N'@thisXquery nvarchar(255)';     EXECUTE sp_executesql  @myDynamicSql, @ParmDefinition,                        @thisXquery = @myXquery;

For those who attended my recent presentation, "XQuery for DBAs and Developers", at NovaSQL, you can find the files here.

 

NovaSQLNov2007.zip (958.63 KB)

 

NovaSQL is the Northern Virginia SQL Server User Group. I would like to thank Jim Rotan and Jeremy Kadlec for the invitation to speak.

OK, I'm a little slow. But, here are the two presentations I made at the Richmond Code Camp two weeks ago. These are in Microsoft Powerpoint.

XQuery for DBAs and Developers (Powerpoint)

Richmond Code Camp XQuery for DBAs and Developers.pptx (980.39 KB)

XQuery SQL Demonstration File (.SQL)

XQuery Presentation.sql (42.59 KB)

Dr. Jekyll and Mr. Hyde; SQLCLR for DBAs and Developers (Powerpoint)

SQLCLR Richmond Code Camp Oct 6.pptx (620.63 KB)

I have worked on some very large DotNetNuke websites (100k+ users), and while performance is typically fairly good, there are times when the websites are very slow, and in some cases the pages "timeout".

Many times the performance of the website is directly attributable to the performance of the database. Often this is due to the volatile nature of your database resulting in the table indexes becoming fragmented. This means that look up queries could run slower over time.

If you are experiencing performance issues coming from your database, check the fragmentation on your indexes on your DNN tables (particularly those associated with your user accounts). Below is the SQL Server 2005 interface for viewing index fragmentation (on an AdventureWorks table).

While this interface (above) is useful for a looking at a few tables, one at a time, there are quite a few DNN tables in a typical installation, and this can get tedious. Here is an excellent article, "Queries Draffing? Try Defragging",  in SQL Server Magazine (InstantDoc 96059), by Eric Peterson, with a downloadable stored procedure that you can use to analyze and tune the fragmentation of your table indexes. The stored procedure will save you a lot of time in analyzing and defragging tables.

 

Located below are the SQL, data and VB project files from my presentation at the Richmond Code Camp

RichmondCodeCampXquery.zip (12.6 KB)

Here is my powerpoint presention. It is more than a little light because the focus of CodeCamp is the CODE!

Richmond Code Camp XQuery Tips and Tricks.ppt (2.34 MB)

I would like to thank Andy Leonard, Susan Lennon, and the rest of the Richmond crew for a well run operation.

When working on code developing a SQLCLR stored procedure for my upcoming presentation at the Richmond Code Camp, I ran into this problem with the SqlPipe output.

Here is my code:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub procExtractSubjectDetail ()
'Return a string of Subject IDs/XML seperated by a ";"
Try
'Connect to database, notice the context connection
Dim Command As SqlCommand = New SqlCommand
Command.Connection = New SqlConnection("Context connection=true")
Command.Connection.Open()

'Generate the SQL query
Dim strSQL As String = "SELECT ID, Study FROM StudyDetail"

'Attach query to command object
Command.CommandText = strSQL

'Execute command object and get results back in a SQL reader object
Dim reader As SqlDataReader = Command.ExecuteReader()

'Create an efficient stringbuilder to hold the results
Dim Subjects As StringBuilder = New StringBuilder

'Add an emtpy string
Subjects.Append("")

'Collect the results
While reader.Read
'String output size limit is 4kb
'so let us restrict our output for this demo
Subjects.Append(reader.Item(0).ToString() + "/")
Dim sXml As SqlXml = reader.GetSqlXml(1)

Subjects.Append(sXml.Value.ToString)
End While

'Close and release the reader
reader.Close()
reader = Nothing

Dim strResult As String = Subjects.ToString()

'Is it too big?
Dim iSize As Integer = strResult.Length

'Send the results
SqlContext.Pipe.Send(strResult)

Command.Connection.Close()

Catch ex As Exception
'Catch the error and resend it. You could add more error code handling here.
Throw New ApplicationException("An error occurred. " + ex.ToString())
End Try

End Sub

--

This was failing when I ran the code. The issue? SqlContext.Pipe.Send(STRING) only allows a string size of 4k bytes. My output was over 7k.

OK. I'm a slacker.

I promised to get this up back a month ago. Here is my powerpoint presentation on developing user defined aggregates in SQL Server 2005 using .NET CLR. I gave this presentation at the NovaSQL user group. The group is managed superbly by Jeremy Kadlec, who also does MS SQL Tips.

novasqlMarch2007.zip (2.2 MB)

I had the pleasure of presenting at Monday's NovaSQL user group meeting on developing User-Defined Aggregates using Visual Studio 2005.

I will post my presentation and code later today for anyone who is interested.

 

Getting an error message after installing your CLR assembly in SQL Server 2005 and running the binding operations (CREATE TYPE or AGGREGATE or FUNCTION) to register the function name with the assembly method? Perhaps the reason is...

XQuery has now reached standardization by the W3C. The XML Team at Microsoft is now asking for input on whether there should be a standalone XQuery implementation in the .Net Framework in their blog entitled Standalone XQuery Implementation in .NET?.

 

My posted response is below:

I, also, would like to see an XQuery implementation within the .Net Framework.

If available, I would be more inclinded to use XQuery over XSLT, particularly in a more dynamic setting to extract data, and then to shape the output before consuming within an application.

While the XQuery implementation in SQL Server 2005 is excellent, it is still only a subset of the standard, for example, the ability to query against multiple documents. And, I would prefer to see the implementation as native to the .Net Framework, rather than as a commercial add-on.

 

The differences between what could be and what is available now are two fold. First, the current implementation resides on server-side, within the SQL Server itself. A .Net Framework (System.XML.XQuery) implementation would allow the developer to choose where the query would be executed. Additionally, you would be able to query across multiple documents, which you cannot do in the SQL Server 2005 version.

I have found the XQuery syntax to be easier to craft to extract XML content and shape the output than using XSLT. I think there is a strong incentive to have the XQuery implementation because it can provide some features that may either be missing or are easier to use than SQL Server XQuery, LINQ or XSLT.

I vote "Yes" to XQuery in the .Net Framework.

 

 

Often, in development, we will take a backup of a database from one SQL Server to another. Perhaps we are moving a database to our development environment to do some local debugging.

Invariably, if you use a backup/restore process, you have an orphaned database "user" account that you need to map to a login. I have done this several times, but I keep forgetting the command, so I thought I would blog it so that I won't forget it, and perhaps others would benefit from this information.

Assume your database that you restored has a user, called "appUser", that is used by some application that accesses that databasde. Use the following steps to match a login account to the database user.

1. Create a login account, if there is not one, in this case create a SQL Server login "appLogin" (In this case we are creating a database login account, not a Windows account. But it is just as easy to do the same thing with a Windows account).
2. Execute the following command:

EXEC sp_change_users_login 'Update_One', 'appUser', 'appLogin';

GO

That's it. Pretty easy. Make sure to check the login and user to ensure that this login/user can access the database.

You can check the details out at the Microsoft SQL Server Books-On-Line for sp_change_users_login.

I gave a presentation on XQuery at the NovaSQL user group that meets in Vienna, Virginia.

Jeremy Kadlec and Brian Moran run a great program. The turnout was very good, too. Lots of SQL experts.

Do you know how you can tell if you are giving a presentation to people that are SQL experts? You can tell that they are when you try to do a SELECT statement, and you get the dreaded Invalid object name error. Before you can blink you hear twenty-thirty voices saying, "You're in the Master database!"

I had a great deal of fun talking about XQuery with a very savvy crowd.

Attached to this post are my PowerPoint slides from the presentation.

 

XQuery-NovaSQL.ppt (106 KB)

Looking for some basics on XQuery expresssions in SQL Server 2005? Here is a great guide that is right in the books-on-line (BOL).

http://msdn2.microsoft.com/en-us/library/ms189919.aspx

This guide, for example, is useful if you want to find out what the different types of comparison operators there are. According to the guide, there are four.

• General comparison operators
  
• Value comparison operators
  
• Node comparison operators
  
• Node order comparison operators
  

Note that the Value operators ('eq', 'ne', 'lt', etc.) work on singleton atomic values. You can use the General operators instead of just the Value operators for comparison.

Sahil Malik's book is on the shelves, and well worth the investment if you are building or upgrading your applications to .NET Framework 2.0.

Sahil Malik speaks at the Capital Area .NET User Group in Washington, D.C.