Stephen Songer

Querying Active Directory from SQL Server 2005

2009-06-01T16:12:00Z

Querying Active Directory from .NET code is pretty easy and straightforward. Doing the same from SQL Server is not too difficult but there is one glaring issue in my opinion - your result set is limited to 1,000 records. For a small company this may be fine but for larger corporations this creates a problem. In .NET code, you can set the page size for your DirectorySearcher object. Unfortunately, there is no way to do that in SQL Server. At least none that I was able to find. So while you can query AD directly when you know you'll have a small recordset, there are times when you will need more than 1,000 records. To get around this issue, I did the next best thing: I created a table to store the AD data and then created a job to refresh that data about 6 times a day. So while not real-time, it provides timely-enough data for most purposes.

Step 1: You need to create a linked server that points to AD. Use the following the SQL to do that. In addition, AD will require authentication to query it so in my case I had our network admin create a domain account that has read-only rights to AD.

EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject', @datasrc=N'your.fully.qualified.ldap.server;'

Step 2: I created a table to store the data and then used the following SQL (thanks Google) to populate the table. After confirming that the stored procedure worked, I simply setup a SQL job to run this stored procedure several times a day.

CREATE PROCEDURE [dbo].[uspCreateActiveDirectoryTable]

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

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

-- Declare Variables

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

DECLARE @chvAlphaChars VARCHAR(60)

,@chvSearch VARCHAR(10)

,@chvSearchLevel1 VARCHAR(1)

,@chvSearchLevel2 VARCHAR(1)

,@chvSearchLevel3 VARCHAR(1)

,@intcountLevel1 INT

,@intcountLevel2 INT

,@intcountLevel3 INT

,@intRowCount INT

,@strSQL NVARCHAR(4000) ,@strADSISQL NVARCHAR(4000)

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

-- Delete existing data from table

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

DELETE dbo.tblActiveDirectoryUsers

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

-- Search letters to cycle through

-- any chars, but the first char must be a space

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

SET @chvAlphaChars = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'

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

-- Start on non space char

SET @intcountLevel1 = 2

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

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

-- First level loop

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

WHILE @intcountLevel1 <= LEN(@chvAlphaChars) BEGIN

-- Get first level char

SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)

-- Reset start on space

SET @intcountLevel2 = 1

-- Second level loop

WHILE @intcountLevel2 <= LEN(@chvAlphaChars) BEGIN

-- Reset start on space

SET @intcountLevel3 = 1

-- Third level loop

WHILE @intcountLevel3 <= LEN(@chvAlphaChars) BEGIN

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

-- Setup the string to search for. By using the trim function we can form each level depending on no records

-- eg A 99, B 1000 > BA 9, BB 20 etc

-- Trim the spaces forming just A, B, C ; AA, AB for search etc

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

SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)

SET @chvSearchLevel2 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel2,1))

SET @chvSearchLevel3 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel3,1))

SET @chvSearch = @chvSearchLevel1 + @chvSearchLevel2 + @chvSearchLevel3

SET @strADSISQL = 'SELECT samaccountname,sn,givenname,mail,mobile,telephonenumber,homephone,facsimiletelephonenumber,physicaldeliveryofficename '

+ CHAR(13) + 'FROM ''''LDAP://your.fully.qualified.ldap.server/DC=aaaaa,DC=bbbbb,DC=ccccc'''' '

+ CHAR(13) + 'WHERE objectCategory = ''''Person'''' '

+ CHAR(13) + 'AND objectClass = ''''user'''' '

+ CHAR(13) + 'AND sAMAccountName = ''''' + @chvSearch + '*'''' '

SET @strSQL = 'INSERT INTO dbo.tblActiveDirectoryUsers (user_name,last_name,first_name,email_address,mobile_phone,work_phone,home_phone,fax_number,office_location) '

+ CHAR(13) + 'SELECT samaccountname AS user_name,sn AS last_name,givenname AS first_name,mail AS email_address,mobile AS mobile_phone,telephonenumber AS work_phone,homephone AS home_phone,facsimiletelephonenumber AS fax_number,physicaldeliveryofficename AS office_location '

+ CHAR(13) + 'FROM OPENQUERY(ADSI,''' + @strADSISQL + ''' ) '

EXEC SP_EXECUTESQL @strSQL

SET @intRowCount = @@ROWCOUNT

-- Prints what string is being searched for : no of inserts. DEBUG CODE

-- PRINT @chvSearch + ' : ' + CONVERT(VARCHAR,@intRowCount)

-- If searched on @chvSearchLevel1 and under 1000 then everything is fine so skip search2 to next search1 eg A > B

IF @intRowCount < 1000

AND @chvSearchLevel2 = ''

SET @intcountLevel2 = @intcountLevel2 + 100

-- If searched on @chvSearchLevel2 and under 1000 then everything is fine so skip to next search2 eg AA > AB

IF @intRowCount < 1000

AND @chvSearchLevel3 = ''

SET @intcountLevel3 = @intcountLevel3 + 100

-- Else over 1000 so increment third level

SET @intcountLevel3 = @intcountLevel3 + 1 END

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

-- Increment next second level char

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

SET @intcountLevel2 = @intcountLevel2 + 1 END

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

-- Increment next first level char

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

SET @intcountLevel1 = @intcountLevel1 + 1

END

Keeping ASP.NET sessions alive

2009-04-22T21:01:00Z

I had an application where users would routinely start working, then stop to do something else and then keep the web page open for hours on end. This would inevitably lead to some ASP.NET Session has expired messages. To combat this, there is a simple solution. Somewhere on your application's page add the following code:

The Blank.aspx page should just be a blank page with the following code-behind code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

'********************************************************

'* Refresh this page 120 seconds before session timeout *

'********************************************************

Response.AddHeader("Refresh", Convert.ToString((Session.Timeout * 60) - 120))

End Sub

Some would argue (correctly in my opinion) that it's a user training issue but the reality is that all users won't always use the application as intended and this simple fix is a better alternative to setting some ridiculously high session timeout setting.

Separate gridview column alignment with CSS Friendly Control Adapter

2009-04-08T16:57:00Z

When using the CSS Friendly Control Adapters, you get an easy to modify the look and feel of all your controls through one style sheet. With the gridview style, you can set the text alignment for your columns:

.PrettyGridView .AspNet-GridView table tbody tr td

{

color: #333333;

background: White;

padding: 2px 8px 3px 2px;

border-bottom: solid 1px #CCCCCC;

border-right: solid 1px #CCCCCC;

text-align: left;

vertical-align: top;

}

However, there are cases when you don't want all columns to be aligned the same way. While left alignment works for most items, if you're displaying numbers chances are you'll want to right-align them. However, if you modify the above CSS to right alignment, ALL columns will be right aligned. Fortunately there is an easy solution: add another class and then specify that class for the specific column you want to right align. For example, a simple solution is to add the following to your gridivew style sheet:

/* Use this ItemStyle-CssClass to align numeric columns to the right */

.PrettyGridView .AspNet-GridView table tbody tr td.number

{

text-align: right;

}

Then you need to specify this style for you numeric columns. For bound fields, you can use as follows:

<asp:BoundField HeaderText="Total Amount" DataField="totalLineAmount" SortExpression="totalLineAmount" DataFormatString="{0:#,##0.00}" ItemStyle-CssClass="number" />

For template fields, use as follows:

<asp:Label ID="Label1" runat="server" Text='<%# Bind("remainingAmount", "{0:#,##0.00}") %>' ForeColor='<%# IIF(Eval("remainingAmount")>=0,System.Drawing.Color.Black,System.Drawing.Color.Red) %>'></asp:Label>

</ItemTemplate>

Using a linked server in a stored proc job

2009-04-03T19:58:00Z

Working on a new stored proc, I encountered an error when trying to run a job that called the stored proc. The error message was:

Executed as user: NT AUTHORITY\SYSTEM. The OLE DB provider "SQLNCLI" for linked server "costpoint_rw" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "costpoint_rw". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI" for linked server "costpoint_rw" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412). The step failed.

The solution I found was to modify the modify the security context for the linked server to map the NT AUTHORITY\SYSTEM account to the SQL login on the remote server that had the rights I needed. I'm sure there are other ways to do it. I'm guessing that using a Proxy would be another way to solve it. But in the case, I chose to go this route.

On the linked server's properties, go to the section on Security. In this case my setting was "Be made without using a security context." Then I mapped the local server login (NT AUTHORITY\SYSTEM in this case) to a local account on the remote server by entering the remote login's user and password information.

Doing the above solved the first error but then I received errors that "Server 'myserver' is not configured for RPC". In SQL Server 2000 this was enabled by default but in SQL Server 2005 it is not. Go to the linked server properties and then check the tab for Server Options. Make sure that "RPC" and "RPC Out" are both set to True.

Convert SQLDataSource to DataTable

2009-04-02T20:46:00Z

Sometimes I find the need to convert a SQLDataSource to a DataTable. Here's how to do it:

Dim dv As DataView = New DataView()

Dim dt As New DataTable

dv = Me.sqldsData.Select(DataSourceSelectArguments.Empty)

dt = dv.ToTable()

Padding in SQL (Replicate Command)

2009-04-01T20:37:00Z

In .NET there are nice functions for padding like PadLeft and PadRight. Found out SQL has its own: the replicate command. You pass a string (the value you want to pad) and then an integer value for how many occurences you want. Combine that command with the RIGHT or LEFT commands to pad any value. For example:

RIGHT(REPLICATE('0', 4) + CAST(@rmaNo AS VARCHAR(4)),4)

In this example I have a variable "@rmaNo" that I want to left pad with zeros to make it 4 characters. To accomplish this I convert the variable to a varchar value and then add the padded zeros to the left. Then I take the rightmost 4 characters to get the value I want.

Open menu item in new window

2009-03-26T16:31:00Z

By default when you open a menu item based on your application's sitemap, it opens in the current window. To have items open in a new window, it's just a simple two step process. First add the "target" property to the sitemapnode you want to be opened in a new window:

Next add the following code to your master page (or whatever page your menu resides on):

Protected Sub mnDefault_MenuItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.MenuEventArgs) Handles mnDefault.MenuItemDataBound

e.Item.Target = e.Item.DataItem("target")

End Sub

Disable/Enable export format in SSRS and ASP.NET

2009-02-20T17:33:00Z

When using the SSRS ReportViewer control in ASP.NET, by default you have the option to export to Excel or PDF. Recently I had an application where the users wanted to limit the export options to PDF only. Thanks to this blog post, I found it was very easy to accomplish. First, call the procedure from your report's PreRender event:

Protected Sub ReportViewer1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles ReportViewer1.PreRender

' Disable EXCEL format

Call DisableFormat(Me.ReportViewer1, "Excel")

End Sub

The code for the disabling an export format is as follows:

Protected Sub DisableFormat(ByRef viewer As ReportViewer, ByVal formatName As String)

Const Flags As System.Reflection.BindingFlags = System.Reflection.BindingFlags.NonPublic + System.Reflection.BindingFlags.Public + System.Reflection.BindingFlags.Instance

Dim m_previewService As System.Reflection.FieldInfo = viewer.LocalReport.GetType().GetField("m_previewService", Flags)

Dim ListRenderingExtensions As System.Reflection.MethodInfo = m_previewService.FieldType.GetMethod("ListRenderingExtensions", Flags)

Dim previewServiceInstance As Object = m_previewService.GetValue(viewer.LocalReport)

Dim extensions As IList = ListRenderingExtensions.Invoke(previewServiceInstance, Nothing)

Dim name As System.Reflection.PropertyInfo = extensions(0).GetType().GetProperty("Name", Flags)

Dim extension As Object

For Each extension In extensions

If (String.Compare(name.GetValue(extension, Nothing).ToString(), formatName, True) = 0) Then

Dim m_isVisible As System.Reflection.FieldInfo = extension.GetType().GetField("m_isVisible", System.Reflection.BindingFlags.NonPublic + System.Reflection.BindingFlags.Instance)

Dim m_isExposedExternally As System.Reflection.FieldInfo = extension.GetType().GetField("m_isExposedExternally", System.Reflection.BindingFlags.NonPublic + System.Reflection.BindingFlags.Instance)

m_isVisible.SetValue(extension, False) m_isExposedExternally.SetValue(extension, False)

Exit For

End If

Next extension

End Sub

The above code shows how to disable a format. To enable a specific format, simply change False in the the below two lines to True.

m_isVisible.SetValue(extension, False)

m_isExposedExternally.SetValue(extension, False)

You could easily take the above procedure and add a boolean value so that the procedure could be used to enable or disable a given export format.

Malformed URL when sitemap node points to SSRS folder

2009-02-06T19:31:00Z

Needed to create a menu item linked to a folder on our SQL Server Reporting Services site. After putting the URL in my sitemap, I got error messages about a malformed URL even though I new the URL was good (I could copy and paste it into a web browser and get to the site). My URL was this:

http://gemini/Reports/Pages/Folder.aspx?ItemPath=%2fIT

Turns out the solution was simple. Just needed to replace the "%2f" with "/" and all was good.

http://gemini/Reports/Pages/Folder.aspx?ItemPath=/IT

Parameters for SSIS package when running as a job

2009-02-03T18:20:00Z

I was playing around with SQL Server Integration Services to create a parameterized package to copy the latest production backup file to a dev server and then restore the backup to the dev server. It's not that hard to do manually but thought it would be a simple example to try out SSIS. Got my package designed and working fine when executed directly from SQL Server. I was passing in three variables (source directory, destination directory and database name). When I took the next step and created a job to run my package I ran into an error. Kept getting error messages about invalid characters in the path. Since I was setting the variable values the exact same way as I did when manually executing the package, I couldn't figure out. After adding some logging to write out the variable values from my script task which copied the backup file from the production server to the dev server I found my answer. The "path" variables I was setting ended in a backslash. When the SQL job executed, the trailing backslash was acting as an escape character and thus my variable values were getting all messed up. The solution was simple. Instead of "C:\Temp\" for example, I just changed the value in the Set Values option to "C:\Temp\\" and all was well.

Multiple file download from a single ASP.NET page

2009-01-21T17:57:00Z

Had an application that needed to generate two CSV files to send to corporate. One file was a simple one line header file and the other file had the data details. I really didn't want to have two buttons on my page and make the user create each file independently. While not that big of a deal, wouldn't it be better from the user's perspective if they could create both files from a single click? I thought so. I found some articles detailing multi-part MIME messages that would allow you to stream multiple files out. The premise sounded good, stream each file and include a boundary delimiter to separate files. While the concept was good, the implementation turned out to be impossible for me so I turned to other avenues and stumbled upon a fairly simple way to get it done. Not the most elegant solution but good enough for the controlled environment of a simple internal web application.

On the page that solicits the user input required in order to create the two download files, I have the following javascript function:

function fnOpen(fl) { window.open('TBFile.aspx?myId=' + fl);

}

</script>

On the click event of the "create files" button, I validate the form input and if all is good, do the following to call the javascript function twice (two files so two calls):

Dim myId As String

myId = "HEADER"

ClientScript.RegisterStartupScript(Me.GetType(), myId, "<script language='javascript'>fnOpen('" & myId & "');</script>")

myId = "DATA"

ClientScript.RegisterStartupScript(Me.GetType(), myId, "<script language='javascript'>fnOpen('" & myId & "');</script>")

On the page (TBFile.aspx in this example) that the javascript function calls, I simply check the request query string value and write out the proper contents (either Header or Data):

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

'**********************************

'* Verify query string was passed *

'**********************************

If IsNothing(Request.QueryString("myId")) = False Then

"If Request.QueryString("myId") = "HEADER" Then

'***************

'* HEADER file *

'***************

ElseIf Request.QueryString("myId") = "DATA" Then

'*************

'* DATA file *

'*************

End If

End Sub

Simple and easy. Browser pop-up blockers may prevent the download windows from opening so that is one gotcha you need to be aware of.

Common page titles for child pages in ASP.NET app

2008-12-08T19:08:00Z

In almost all my application I utilize master pages and in most cases my master page has two content place holders: one for the child page content and one for the child page heading content. This helps me give the application a consistent look and feel. I like having a naming convention for the pages in my app. Usually "[App Name]" for the default page and then "[App Name] - [Function Name]" for the other pages. For example, if I have "Production Forecast Tool" as the name of my app, then the admin menu page would have a name of "Production Forecast Tool - Admin Menu". I had been setting the page title separately for each page but then had one application where I needed to change the name and it was a pain to edit all the entries. I realized my page heading content had a label with the function name I wanted to included in my page title so I just added a few lines of code to my master page's load even to take of the page titles for me.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

Dim lblHeading As Label = Me.SubHeading.FindControl("lblHeading")

If Not (lblHeading Is Nothing) Then

Me.Page.Title = "Production Forecast Tool (PFT) - " & lblHeading.Text

Else

Me.Page.Title = "Production Forecast Tool (PFT)"

End If

End Sub

The caveat is that all the child pages must have the same control name for the heading label. Now if the application name needs to change I need only change it once in my master page. Simple but effective.

Opening SSRS Report in PDF format via ASP.NET

2008-12-05T16:55:00Z

I had to create a report where the report data was displayed over several columns. Just a simple report for admins with everyone's name and phone extension. SSRS allows you to do this by specifying the number of columns in your report but this view is not available in HTML mode. So if you are showing your report within an ASP.NET application then the user only sees one column of data. If they export the report to PDF format then the report looks as you intended it but obviously that's not the best approach from a user's viewpoint. So I wanted my report to come in PDF format right away so the user could see the report as it was designed. Doing this is pretty easy.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

'Get report data

Dim dsReport As New dataset

Dim obj As New Insitu.Data.PhoneBook

dsReport = obj.GetPeople("")

obj = Nothing

'Create report datasource

Dim rdsReport As New Microsoft.Reporting.WebForms.ReportDataSource

rdsReport.Name = "ReportData"

rdsReport.Value = dsReport.Tables(0)

'Initialize report viewer

Me.ReportViewer1.LocalReport.ReportPath = "reports\ExtLN.rdlc"

Me.ReportViewer1.LocalReport.DataSources.Clear()

Me.ReportViewer1.LocalReport.DataSources.Add(rdsReport)

Me.ReportViewer1.LocalReport.Refresh()

Dim result As Byte()

Dim warnings As Microsoft.Reporting.WebForms.Warning() = Nothing

Dim DeviceInfo As String = "<DeviceInfo>" _

& " <OutputFormat>PDF</OutputFormat>" _

& " <PageWidth>11in</PageWidth>" _

& " <PageHeight>8.5in</PageHeight>" _

& " <MarginTop>0.1in</MarginTop>" _

& " <MarginLeft>0.1in</MarginLeft>" _

& " <MarginRight>0.1in</MarginRight>" _

& " <MarginBottom>0.1in</MarginBottom>" _

& "</DeviceInfo>"

Dim streamIDs As String() = Nothing

Dim mimeType As String = "application/pdf"

Dim encoding As String = Nothing

Dim extension As String = Nothing

'Render report in PDF format

result = Me.ReportViewer1.LocalReport.Render("PDF", DeviceInfo, mimeType, encoding, extension, streamIDs, warnings)

Response.ClearContent()

Response.ContentType = mimeType

Response.AddHeader("content-disposition", "attachment; filename=Report." & extension)

Response.BinaryWrite(result)

Response.End()

End Sub

Recursive search for SharePoint Tag Cloud 1.2

2008-10-27T18:16:00Z

A while back I found a nifty webpart for our SharePoint WSS site called Tag Cloud which lets you tag documents in your SharePoint site and then display a list of all tags with those that occur more often being rendered in larger text. We decided to use for our application support/help desk sites because it makes finding documents easier. Yes, SharePoint has a search feature and of course we could structure our documentation to make it easier to find stuff but this just seems to be a nice option to have. After installing it though I came across a bug/feature limitation. The "tag cloud" webpart would show tags from documents that may be buried several levels deep in a document library but when you clicked on a tag you would get no results. The solution is modify the source code to include the following:

SPQuery query = new SPQuery();

query.ViewAttributes = "Scope=\"Recursive\"";

Add the second line above after the first line in the SPUrlBrowser.cs and SPTagBrowser.cs files. Recompile the solution and deploy the update SharePoint solution file. Once that is done the "cloud browser" web part will correctly find all items, no matter what level they may reside in. Note that you'll need to make the code modifications from a machine where SharePoint is installed since the solution references the SharePoint dlls.

11-21-2008 Update: I've uploaded my wsp file.

Corrupt Web Parts on SharePoint

2008-10-22T19:11:00Z

Ran into a problem after modifying a web part. Could not open the default page for my SharePoint site. All other pages worked fine. Found a gem online that saved me. Add "?contents=1" to the end of the URL. For example, http://mysite/default.aspx?contents=1 . This will give you a list of webparts on that page and allow you to delete the one you suspect is causing the problem. Worked great for me.