The best explanation and tool for troubleshooting problems with Kerberos and delegation in an IIS environment.
http://blogs.iis.net/brian-murphy-booth/archive/2007/03/09/the-biggest-mistake-serviceprincipalname-s.aspx
Monday, October 6, 2008
Tuesday, September 9, 2008
Nice Free Geocoding Web Site
I needed to be able to quickly geocode some addresses and this site did just that AND it was free.
http://www.batchgeocode.com/
http://www.batchgeocode.com/
Wednesday, August 6, 2008
Method to pass multiple "parameters" to a stored procedure using XML
I want to share with you a technique I have devised. I had a case where there was a stored procedure in a database that allowed you to look something up based on a parameter. The problem I had was that I needed to do this lookup anywhere between 1 – 200 times depending on the circumstances. I really didn’t like the idea of calling the stored procedure 200 times, so I looked into another way. Here is what I came up with, use XML.
To start off with, if you have never messed with XML or even XML in relation to SQL Server, then you typically don’t think of using XML when we are talking about SQL Server. But, with advances in SQL Server and its usage of XML data, you might find more ways to take advantage of it. And this method does just that.
To begin this exercise, we need to build a few things to setup a very simple scenario. Granted, the solution I’m going to illustrate is overkill for this scenario, but I’ll explain how you could use this method in other places.
Create some objects
CREATE TABLE [dbo].[tblDepartment](
[DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblEmployee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[DepartmentID] [int] NOT NULL
) ON [PRIMARY]
Insert some data
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Human Resources')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Sales')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Marketing')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Communications')
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('John', 'Smith', 2)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Nancy', 'Holder', 3)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Craig', 'Jones', 1)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Susan', 'Henderson', 1)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Robert', 'Craft', 4)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Jimmy', 'Foldgers', 2)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Max', 'Eilliot', 3)
Now that we have a couple of tables and some data, let’s talk about what we are wanting to do. Let’s say we have a stored procedure that will allow you to pass it a DepartmentID and it will return all of the employees who work in that department. But let’s say you need to get this information for all departments. In the example data above, you would have to call this stored procedure 4 times and then combine the 4 separate results into one result set. Seems inefficient to me. There has to be a better and easier way. This is where the XML part comes into play.
To start off, we need to first create a stored procedure that will give me what I want to look up. Consider the following.
CREATE PROCEDURE dbo.spGetDepartmentIDs
@RtnDeptID VARCHAR(MAX) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
WITH MyCTE (I) AS
(
SELECT DepartmentID
FROM tblDepartment
FOR XML RAW('item'), ROOT('items')
)
SELECT @RtnDeptID = MyCTE.[I] FROM MyCTE
SET NOCOUNT OFF;
END
So, what does spGetDepartmentIDs do? It returns all the department IDs as XML. So if I execute the following I will get some neat results.
DECLARE @xmlDeptID AS VARCHAR(max)
EXEC dbo.spGetDepartmentIDs @xmlDeptID OUTPUT
SELECT @xmlDeptID AS MyDeptIDs
Results (formatted for easy reading)
You are probably wondering why I’m using a CTE in this procedure. The reason is so that I can get back a defined column name. If you just issue the SELECT statement, the column name returned is nasty and you may not want to deal with it. So now you asking yourself, “So what, you can get back an XML string?” Now the cool part.
What if I pass this XML string onto a procedure that had a incoming parameter of an XML type? And then what if I could join to that XML data? It would be excellent, right? Here is how.
CREATE PROCEDURE dbo.spGetEmployeesForDeptByXML
@xmlDeptString XML
AS
BEGIN
SET NOCOUNT ON;
SELECT E.DepartmentID, E.EmployeeID, E.FirstName, E.LastName
FROM dbo.tblEmployee E
INNER JOIN @xmlDeptString.nodes('//items/item') AS x(item)
ON E.DepartmentID = x.item.value('@DepartmentID[1]', 'INT')
SET NOCOUNT OFF;
END
GO
Now, if I execute the following bit of code, I have my solution.
DECLARE @xmlDeptID AS VARCHAR(max)
EXEC dbo.spGetDepartmentIDs @xmlDeptID OUTPUT
EXEC spGetEmployeesForDeptByXML @xmlDeptID
Again, this is a very simple scenario and there is a much better way of doing this scenario. But, consider this technique if you were integrating with another system. Let’s say you needed to pass a list of some IDs to another system’s stored procedure and have it return the data back to you. Or say you need to insert data into another system. You could build an XML string that you could pass in one call. Again, you could call the other system’s stored procedure x times, or you would have a similar implementation as I have described above.
In terms of performance, I’m sure there is some gotchas with this. In my case, I was dealing with a small amount of data (200 records or less). So the XML string was small. I’m sure if you passed a string that had 1000 items, the join being done could be expensive. Therefore, use this method at your own risk and test it well. To learn more about the XML-type code used above, check out BOL or Google it. It is very powerful.
To start off with, if you have never messed with XML or even XML in relation to SQL Server, then you typically don’t think of using XML when we are talking about SQL Server. But, with advances in SQL Server and its usage of XML data, you might find more ways to take advantage of it. And this method does just that.
To begin this exercise, we need to build a few things to setup a very simple scenario. Granted, the solution I’m going to illustrate is overkill for this scenario, but I’ll explain how you could use this method in other places.
Create some objects
CREATE TABLE [dbo].[tblDepartment](
[DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblEmployee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[DepartmentID] [int] NOT NULL
) ON [PRIMARY]
Insert some data
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Human Resources')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Sales')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Marketing')
INSERT INTO [dbo].[tblDepartment] ([DepartmentName]) VALUES ('Communications')
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('John', 'Smith', 2)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Nancy', 'Holder', 3)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Craig', 'Jones', 1)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Susan', 'Henderson', 1)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Robert', 'Craft', 4)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Jimmy', 'Foldgers', 2)
INSERT INTO [dbo].[tblEmployee] ([FirstName],[LastName],[DepartmentID]) VALUES('Max', 'Eilliot', 3)
Now that we have a couple of tables and some data, let’s talk about what we are wanting to do. Let’s say we have a stored procedure that will allow you to pass it a DepartmentID and it will return all of the employees who work in that department. But let’s say you need to get this information for all departments. In the example data above, you would have to call this stored procedure 4 times and then combine the 4 separate results into one result set. Seems inefficient to me. There has to be a better and easier way. This is where the XML part comes into play.
To start off, we need to first create a stored procedure that will give me what I want to look up. Consider the following.
CREATE PROCEDURE dbo.spGetDepartmentIDs
@RtnDeptID VARCHAR(MAX) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
WITH MyCTE (I) AS
(
SELECT DepartmentID
FROM tblDepartment
FOR XML RAW('item'), ROOT('items')
)
SELECT @RtnDeptID = MyCTE.[I] FROM MyCTE
SET NOCOUNT OFF;
END
So, what does spGetDepartmentIDs do? It returns all the department IDs as XML. So if I execute the following I will get some neat results.
DECLARE @xmlDeptID AS VARCHAR(max)
EXEC dbo.spGetDepartmentIDs @xmlDeptID OUTPUT
SELECT @xmlDeptID AS MyDeptIDs
Results (formatted for easy reading)
You are probably wondering why I’m using a CTE in this procedure. The reason is so that I can get back a defined column name. If you just issue the SELECT statement, the column name returned is nasty and you may not want to deal with it. So now you asking yourself, “So what, you can get back an XML string?” Now the cool part.
What if I pass this XML string onto a procedure that had a incoming parameter of an XML type? And then what if I could join to that XML data? It would be excellent, right? Here is how.
CREATE PROCEDURE dbo.spGetEmployeesForDeptByXML
@xmlDeptString XML
AS
BEGIN
SET NOCOUNT ON;
SELECT E.DepartmentID, E.EmployeeID, E.FirstName, E.LastName
FROM dbo.tblEmployee E
INNER JOIN @xmlDeptString.nodes('//items/item') AS x(item)
ON E.DepartmentID = x.item.value('@DepartmentID[1]', 'INT')
SET NOCOUNT OFF;
END
GO
Now, if I execute the following bit of code, I have my solution.
DECLARE @xmlDeptID AS VARCHAR(max)
EXEC dbo.spGetDepartmentIDs @xmlDeptID OUTPUT
EXEC spGetEmployeesForDeptByXML @xmlDeptID
Again, this is a very simple scenario and there is a much better way of doing this scenario. But, consider this technique if you were integrating with another system. Let’s say you needed to pass a list of some IDs to another system’s stored procedure and have it return the data back to you. Or say you need to insert data into another system. You could build an XML string that you could pass in one call. Again, you could call the other system’s stored procedure x times, or you would have a similar implementation as I have described above.
In terms of performance, I’m sure there is some gotchas with this. In my case, I was dealing with a small amount of data (200 records or less). So the XML string was small. I’m sure if you passed a string that had 1000 items, the join being done could be expensive. Therefore, use this method at your own risk and test it well. To learn more about the XML-type code used above, check out BOL or Google it. It is very powerful.
Wednesday, July 16, 2008
Running SSIS Package gives: error failed validation and returned validation status "VS_NEEDSNEWMETADATA"
When running a SSIS package with a data pump task, if you receive an error similar to failed validation and returned validation status "VS_NEEDSNEWMETADATA" the problem could be that the security for the account running the package is not setup for the object being called within the data pump task source task. In the case this was found, the data pump was calling a stored proc in another database and that stored proc joined to data in another databases. The AD account for the system that was running the package needed to have rights to execute the store proc in the first database as well as the rights on the view the stored proc was joining to. The best way to troubleshoot this problem is to create a new package and have it execute a SQL task that calls the stored proc. Then run the package on the server and this will help pin point where the problem occurs.
Thursday, July 3, 2008
Get list of AD group members
Quick and dirty way to do this.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.DirectoryServices;
namespace ADTest
{
public partial class Form2 : Form
{
public const string adpath = "LDAP://domain.com/";
public Form2()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Boolean iresult;
iresult = GetGroupMembers(textBox2.Text);
}
public static DirectoryEntry GetDirectoryEntry()
{
DirectoryEntry de = new DirectoryEntry();
de.Path = adpath;
de.AuthenticationType = AuthenticationTypes.Secure;
return de;
}
public bool GetGroupMembers(string GroupName)
{
DirectoryEntry de = GetDirectoryEntry();
DirectorySearcher ds = new DirectorySearcher(de);
ds.Filter = "(&(objectClass=group)(cn=" + GroupName + "))";
SearchResult results = ds.FindOne();
if (results != null)
{
DirectoryEntry deGroup = new DirectoryEntry(results.Path);
System.DirectoryServices.PropertyCollection pcoll = deGroup.Properties;
int n = pcoll["member"].Count;
textBox1.Text = n.ToString();
for (int l = 0; l < n; l++)
{
DirectoryEntry deUser = new DirectoryEntry(adpath + "/" + pcoll["member"][l].ToString());
richTextBox1.Text = richTextBox1.Text + GetProperty(deUser,"givenName") + " " + GetProperty(deUser,"sn") + "\n";
deUser.Close();
}
deGroup.Close();
de.Close();
return true;
}
else
{
de.Close();
return false;
}
}
public static string GetProperty(DirectoryEntry oDE, string PropertyName)
{
if (oDE.Properties.Contains(PropertyName))
{
return oDE.Properties[PropertyName][0].ToString();
}
else
{
return string.Empty;
}
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.DirectoryServices;
namespace ADTest
{
public partial class Form2 : Form
{
public const string adpath = "LDAP://domain.com/";
public Form2()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Boolean iresult;
iresult = GetGroupMembers(textBox2.Text);
}
public static DirectoryEntry GetDirectoryEntry()
{
DirectoryEntry de = new DirectoryEntry();
de.Path = adpath;
de.AuthenticationType = AuthenticationTypes.Secure;
return de;
}
public bool GetGroupMembers(string GroupName)
{
DirectoryEntry de = GetDirectoryEntry();
DirectorySearcher ds = new DirectorySearcher(de);
ds.Filter = "(&(objectClass=group)(cn=" + GroupName + "))";
SearchResult results = ds.FindOne();
if (results != null)
{
DirectoryEntry deGroup = new DirectoryEntry(results.Path);
System.DirectoryServices.PropertyCollection pcoll = deGroup.Properties;
int n = pcoll["member"].Count;
textBox1.Text = n.ToString();
for (int l = 0; l < n; l++)
{
DirectoryEntry deUser = new DirectoryEntry(adpath + "/" + pcoll["member"][l].ToString());
richTextBox1.Text = richTextBox1.Text + GetProperty(deUser,"givenName") + " " + GetProperty(deUser,"sn") + "\n";
deUser.Close();
}
deGroup.Close();
de.Close();
return true;
}
else
{
de.Close();
return false;
}
}
public static string GetProperty(DirectoryEntry oDE, string PropertyName)
{
if (oDE.Properties.Contains(PropertyName))
{
return oDE.Properties[PropertyName][0].ToString();
}
else
{
return string.Empty;
}
}
}
}
Thursday, June 26, 2008
Problem Connecting to SQL 2005 Named Instance with Vista
If you are running Vista and you are using Management Studio or an application that is connecting to a SQL 2005 named instance server, you will receive the error.
Login timeout expiredAn error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. (Microsoft SQL Native Client)
This problem results in that in Vista, you must specify the port number of the instance you are connecting to. So rather than connecting to Server\Instance you need to connect using Server\Instance,port.
Login timeout expiredAn error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. (Microsoft SQL Native Client)
This problem results in that in Vista, you must specify the port number of the instance you are connecting to. So rather than connecting to Server\Instance you need to connect using Server\Instance,port.
Friday, June 20, 2008
Using Access 2007 as a conduit to SharePoint list data
I stumbled onto something today that I don’t know what to think of and thought I would kick it out to the world for comment. I was grappling with the idea of using “data” in lists that exist in a site collection. By using, I mean using in the since of reporting on it, integrating it with other data sources (SQL Server, web services, etc), etc. I knew that Access 2007 had native capability to treat SharePoint lists like a linked table. So, I thought to myself that if I can create a linked table to a SharePoint list and then I can use the Access 2007 database as a data source for applications other (ASP.Net, SSIS, SQL Server, SRS, etc) then would that work. Indeed it did! I first created two custom lists in SharePoint, each of them being in totally different web sites within the same site collection. Then I created an Access 2007 database and created linked tables to these lists. The first test was to see if I could join these lists together in an Access query. Yes. Then I thought, okay, can I create a SQL Server Integration Services (SSIS) package to work “directly” with the SharePoint data, but use the Access database as a conduit. So, I setup a connection to the Access database using the OLE DB Office 12 driver. Then I created a SQL Task to insert into the Access linked table that was linked to one of the lists. I ran the SSIS and by golly it inserted into the list just fine. I then wrote a transformation task that joined SQL Server data in a SQL database to the list data in SharePoint in order to insert data into another SQL Database. And that worked.
So, is this good or bad? I can’t see this being very scaleable, but using Access 2007 as a conduit into SharePoint data is a lot easier than working with the SharePoint API or web services and dealing with CAML. It seems to be that Microsoft should introduce a SharePoint data provider that can be used in SQL Server or .NET so that you can treat SharePoint lists just like tables without all of the headaches. What are your thoughts?
So, is this good or bad? I can’t see this being very scaleable, but using Access 2007 as a conduit into SharePoint data is a lot easier than working with the SharePoint API or web services and dealing with CAML. It seems to be that Microsoft should introduce a SharePoint data provider that can be used in SQL Server or .NET so that you can treat SharePoint lists just like tables without all of the headaches. What are your thoughts?
Subscribe to:
Posts (Atom)