I can't tell you how many countless times I loaded up a saved T-SQL cursor script to do some sort of looping over all the user tables in a database.  Well, APPARENTLY there is an undocumented system sproc named "sp_MSforeachtable" which does exactly what it says....it will do something for each table in the current database.  I stumbled upon this over at Joe Webb's blog

Here is an example of its use.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'SELECT COUNT(*) FROM ?';

There are obviously easier ways to get table counts but I just used this as an easy example.  The "?" is a special character that gets replaced by the actual table name.  Also, the T-SQL command in quotes must be something that can be executed.  Joe uses examples such as checking the amount of space each table is using.  This can really come in handy for doing those "maintenance" tasks on all your tables in a database quickly and easily.  You could write an SSIS package or a scheduled task to perform all this maintenance nightly using the sproc above.


Posted in:   Tags:
Tony Testa posted on December 30, 2007 04:47

I HIGHLY recommend that you download the following ASP.NET Dynamic Data Intro screencast and check it out.  I just watched it and it looks really easy/powerful.  In a nutshell it combines the power of ASP.NET,  LINQ, and new features in .NET 3.5 to create a web view of the database you define.

Looks like you could whip up some really cool views of your database in no time at all with this technology.  In addition it allows you to create custom data type templates so that when it maps a field to a data type you can control how it gets displayed.  It also has some nice custom validation that you can add onto it to make it a pretty powerful data driven site.

I'm not quite sure of its business value just yet.  I can easily see it being used to give some of your users easy views of your database and you have the ability to customize what they see.  I can also see it being used for e-commerce sites to quickly get product pages etc.  As for business systems the right now the only use I can see is for getting some prototypes up and ready for users in little to no time. 

Watch the video to see some of the other cool features.


Posted in:   Tags:
I spent a good part of the day today trying to figure out how to pragmatically add files to a document library.  I had to read in a directory of files, parse their names and add them to the document library.  In addition to adding the file, I needed to set a few fields, one of them being a Date and Time column/property.  Easy enough, right?  Well, I quickly found out it wasn't as easy as I originally thought.

My first attempt went as you would think.  I tried to set the Property to a DateTime object.

    1 //create the SP file to upload and set its initial properties

    2 SPFile fileToUpload = docLib.RootFolder.Files.Add(fl.Name, binFile, true);

    3 fileToUpload.Properties["Name"] = string.Format("{0} {1}", fileNameParts[1], fileNameParts[0]);

    4 fileToUpload.Properties["DateField"] = DateTime.Parse("12/18/2007");

    5 fileToUpload.Update();


As I thought, that worked fine.  The problem came when i hit a date that was prior to 1/1/1970.

    1 //create the SP file to upload and set its initial properties

    2 SPFile fileToUpload = docLib.RootFolder.Files.Add(fl.Name, binFile, true);

    3 fileToUpload.Properties["Name"] = string.Format("{0} {1}", fileNameParts[1], fileNameParts[0]);

    4 fileToUpload.Properties["DateField"] = DateTime.Parse("1/1/1968");

    5 fileToUpload.Update();


It throws the following error : "Microsoft.SharePoint.SPInvalidPropertyException: Date Times before 1/1/1970 are not supported.".  So anything after 1/1/1970 works fine, but anything prior to 1/1/1970 throws an error?!?!?!?!?

I searched high and low all over Google, the forums, etc.  Nothing, relating to anything with .NET, DateTime object, and a 1/1/1970 barrier.  So after doing a bunch of trial and error, I stumbled upon it. 
Sharepoint expects Date and Time columns in UTC format.  So what does that mean?

A UTC date should come in the form : yyyy-mm-ddThh:mm:ssZ    where T and Z are literals.
So here is the updated code that worked for me.

    1 //create the SP file to upload and set its initial properties

    2 SPFile fileToUpload = docLib.RootFolder.Files.Add(fl.Name, binFile, true);

    3 fileToUpload.Properties["Name"] = string.Format("{0} {1}", fileNameParts[1], fileNameParts[0]);

    4 //Date's are stored in SP in the format :    yyyy-mm-ddThh:mm:ssZ   T/Z are literals

    5 fileToUpload.Properties["DateField"] = "1950-01-01T00:00:00Z";

    6 fileToUpload.Update();


Posted in: Sharepoint  Tags: , ,
Well thanks to my dumb ass, I hit the wrong button on the blog editor and cleared out this post which I had about 95% done.  So I'm gonna make this quick and not rewrite the whole post, but long story short, avoiding XML and XSLT as long as I have came back to bite me in the ass.

At a current client I had to call a .NET web service by passing in a SOAP Envelope.  The return data was a .NET DataSet.  I then had to take that .NET DataSet and use an XSL Transform to parse out the data and return another, smaller XML chunk.  I had to do this to work with in the constraints of a 3rd party Sharepoint Workflow package known as Nintex Workflow.

I was able to get the DataSet back no problem, my problem lay in the XSL transform.  No matter what I tried or combination of XPath queries I tried, I was not able to parse out the data I needed from the DataSet.  I used Altova XMLSpy to take the results of the web service call and used that to give me the XPath queries.  Still, no luck, my XSL transform did not give me back the data, it didn't give me anything.  I asked around and luckily 1 person I asked knew exactly what my problem was.  The DataSet returned to me had an XML Namespace set in the properties.  He said to clear out that property of the DataSet and I should be fine.  Unfortunately i had no control over the web service or the DataSet it was returning to me.  So i had to figure this out.

I finally stumbled upon a combination of search results in Google that lead me in the right direction and after a few hours, and some good old trial and error, I was able to figure it out.  I had to put the XML namespace in the transform and then preface the XPath queries with the name that I gave to the XML namespace

Here are a few examples to illustrate my point.

Lets say you have a basic table of Person and you return an array of those as your DataSet.  Roughly speaking, here is what the basic XML should look like.

    1 <?xml version="1.0" encoding="utf-8"?>

    2 <PersonArray>

    3     <Person>

    4         <FirstName>John</FirstName>

    5         <MiddleName>H</MiddleName>

    6         <LastName>Doe</LastName>

    7     </Person>

    8     <Person>

    9         <FirstName>Jane</FirstName>

   10         <MiddleName>H</MiddleName>

   11         <LastName>Doe</LastName>

   12     </Person>

   13 </PersonArray>


Now, if you put the following XSLT onto the XML and open it in IE, you should see the Name of the first Person.


    1 <?xml version="1.0" encoding="iso-8859-1"?>

    2 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

    3     <xsl:output method="html" />

    4     <xsl:template match="/">

    5         <h3>First Name:

    6             <xsl:value-of select="PersonArray/Person/FirstName" />

    7         </h3>

    8         <br />

    9         <h3>Middle :

   10             <xsl:value-of select="PersonArray/Person/MiddleName" />

   11         </h3>

   12         <br />

   13         <h3>Last Name:

   14             <xsl:value-of select="PersonArray/Person/LastName" />

   15         </h3>

   16     </xsl:template>

   17 </xsl:stylesheet>


To see the results in IE, put the following in between line 1 and 3 of the XML above.
<?xml-stylesheet type="text/xsl" href="person_basic_transform.xsl"?>

That is a basic XML example and how to apply a basic XSLT to it to spit back some data.

For the sake of this post, I've modified the following XML a bit, but its basically what you would get back if you called a web service that returned back a .NET DataSet

    1 <?xml version="1.0" encoding="utf-8"?>

    2 <?xml-stylesheet type="text/xsl" href="person_namespace_transform.xsl"?>

    3 <PersonArray xmlns="http://www.somesite.com/Something">

    4     <Person>

    5         <FirstName>John</FirstName>

    6         <MiddleName>H</MiddleName>

    7         <LastName>Doe</LastName>

    8     </Person>

    9     <Person>

   10         <FirstName>Jane</FirstName>

   11         <MiddleName>H</MiddleName>

   12         <LastName>Doe</LastName>

   13     </Person>

   14 </PersonArray>


As you can see, the PersonArray has an XML namespace attribute added to it.  This is what was giving me so much trouble.  If you use the previous XSL Transform above and apply it to this XML, you won't get back an results.  If you instead use the XSLT below, you will get back results.

    1 <?xml version="1.0" encoding="iso-8859-1"?>

    2 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:test="http://www.somesite.com/Something">

    3     <xsl:output method="html" />

    4     <xsl:template match="/">

    5         <h3>First Name:

    6             <xsl:value-of select="test:PersonArray/test:Person/test:FirstName" />

    7         </h3>

    8         <br />

    9         <h3>Middle :

   10             <xsl:value-of select="PersonArray/Person/MiddleName" />

   11         </h3>

   12         <br />

   13         <h3>Last Name:

   14             <xsl:value-of select="PersonArray/Person/LastName" />

   15         </h3>

   16     </xsl:template>

   17 </xsl:stylesheet>


The difference with this XSLT and the one previously is i added the same XML Namespace as was in the DataSet and prefaced it with a name.  I then use the name I gave it in my XPath queries so that they know where to look.  I only did this for the First Name so that you can see the difference.  With out that XML Namespace, you won't get back the results you want.

I've packed these examples up in a zip file that you can download here.

Hopefully this helps someone that runs across this post and saves them some time.

Posted in:   Tags: , , ,
Tony Testa posted on December 9, 2007 00:00
Early last week I successfully passed the 70-541 : TS: Microsoft Windows SharePoint Services 3.0 - Application Development test!

I did extremely well on it so I am quite happy with my performance.

I am the first one to pass the test in my company, Perficient.  Perficient-Philiadelphia is trying to get its Information Worker Solutions Competency - Portals & Collaboration Specialization.  To get this, 4 people in our company have to pass the same test, so we can cross 1 off that list!

Contact me if you want to know more about the test and what it covers.  I'll help you as best I can prepare for the test.

Check out the Preparation Guide for Exam 70-541 on Microsoft's site.  They don't lie about what the test covers.  If you know content in the areas it specifies that it covers, you should do fine.

Now that I've passed the test, I'm hoping to get on some more Sharepoint projects and really expand my knowledge and experience with Sharepoint.

Posted in:   Tags:
Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017 Tony Testa's World