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:

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
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:

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
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:   Tags: ,

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
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: , , ,

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
I just got word earlier today that I got in to do a talk at the PhillyDotNet CodeCamp 2008.1.  It is going to be @ DeVry university instead of the standard Microsoft Malvern office.  This should will allow more people to attend as well as more talks!

Right now my talk is going to be on Incorporating AJAX into Sharepoint.  In my last talk I briefly demo'd a small AJAX enabled webpart within Sharepoint to show how easy it was to incorporate into Sharepoint.  I'm planning on expanding this out and showing more useful examples that people can use in existing sites.

I'll post more as I prepare for the event.

If your in the Delaware Valley area and want to share some knowledge I encourage you to sign up and do a talk!  Check out the PhillyDotNet site and contact them if your interested!

Posted in:   Tags: , ,

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Search Blog

Blog Roll

    OPMLDownload OPML file

    Authors

    Recent Comments

    Banners

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

    © Copyright 2008 Tony Testa's World