Returning the Nth weekday in SQL…and in InfoPath

Ok, after much research I found a forum that had a posting that demonstrated how to get the Nth chosen weekday of a month using T-SQL. Here’s the code:

DECLARE @date datetime
DECLARE @weekday int
DECLARE @day datetime
DECLARE @number int
DECLARE @WhichOne int

SELECT @WhichOne = 2 -- Set this to the week you want to grab
SELECT @number = 1
SELECT @weekday = 0
SELECT @date = getDate() -- Change to dateadd(month, 1, getDate()) for next month, etc.

WHILE @weekday  4 -- Change this value to be the day of the week you're looking for
BEGIN
    SELECT @day = (CAST(STR(MONTH(@date)) + '/' + STR(@number) + '/'+ STR(YEAR(@date)) AS DATETIME))
    SELECT @weekday = DATEPART(weekday, @day)
    SELECT @number = @number + 1
END

SELECT DATEADD(d, (@WhichOne - 1) * 7, @day)

Many MANY thanks to Tara for this posting! The link to the forum post is here.

Now, how did I get InfoPath to see this? In the InfoPath form, I added a DatePicker control. Next I had to create a Data Source, point to a server and database on our network somewhere (doesn’t matter where). Click “Edit SQL” and add this code to that box. Click through the wizard and right click on the control, choosing Properties. Click the Function design button next to Default Value, Click Insert Field or Group, and under Data Source, choose your newly created data source. Expand out the tree until you see your result set field and choose it. Don’t forget to export your data connection to a Data Connection library on your SharePoint server and site, or the form won’t work.

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Calculating Nth weekday of next month in Excel

I won’t normally post Excel stuff on here, but while researching an XSLT/Javascript function I was determined to get it right in Excel first, so I understood it better, and definitely wanted to document it so I could pull it back up later!

In this example, I need to know the second Wednesday of next month, because that’s when server maintenance is every month. But what day is it? Here’s the Excel formula:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*2)-WEEKDAY(DATE(YEAR(TODAY(),MONTH(TODAY())+1,8-4))

Notice the DAY argument of the first DATE function in the formula says “1+7*2”. The “2” represents the week of the month, so if you wanted the third week change it to “3”, etc.

Next, notice the DAY argument of the second DATE function in the formula says “8-4”. The “4” represents the day of the week, where 1 is Sunday, 2 is Monday, etc. So if you wanted Fridays only, change it to 6.

This worked in Excel, now to figure out how to do this in JavaScript and/or XSLT. Ultimately I need it in XSLT for InfoPath, and the generic formula functions in InfoPath don’t come close to this. Once I figure it out I’ll post it here.

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

InfoPath data validation using regular expressions

I found it interesting yesterday, while building an InfoPath template for SharePoint 2007, that you can use regular expression code to perform data validation the same way it’s used in an ASP.Net form, and the same way it’s done in C#. Here’s an example:

In my form, I wanted to make sure the data entered conformed to a specific email address format. Normally, an email address pattern looks like this:

w+([-+.’]w+)*@w+([-.]w+)*.w+([-.]w+)*

This won’t work in InfoPath, there are too many unrecognizable characters. However, very similarly you can force a pattern match in InfoPath by doing something like this:

p{L}+@p{L}+.p{L}+

Unlike C# and ASP.Net, InfoPath data validation has some explanation of the code behind their form of regular expression validation. C# regular expression code is all over the internet, a Google search will provide a wealth of help on it, and I’ve blogged about it before. But I found it interesting that you can use regular expression for data input validation, not to mention to promote security, preventing SQL injection and launching of unsafe code. If you’re well versed in InfoPath you already knew this. If not, give it a try by right-clicking on the TextBox control, for example, and choose Data Validation. Under the “If condition is true” section, the third drop down will say “Select a Pattern”. Choose this, and in the Data Entry Pattern window, under Standard Patterns choose Custom Pattern. Then use the Insert Special Character drop down below to help you build your expression. Like regular expressions, you can force specific words, too. So something like:

p{L}+@gmail.com

Would be the same as saying “allow any word before @gmail.com”. The p{L} allows any letter, the “+” immediately after that says “multiple”. As you can see, there’s a lot of possibilities with the way to construct expressions. Give it a try the next time you’re in InfoPath!

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Complete TFS 2008 Install Guide

I wanted to use Team Foundation Server 2008 as our development solution for our company, and I had never used it before, so I thought I’d give it a whirl on a VM dev box and see how it goes. I tried opening the Installation Guide, and it didn’t work. I tried opening the one online, and it forced me to download a copy that also didn’t work. So, flying blind I gave it my best shot. After over a week of trial and error, I managed to get a good install. Here is my step by step guide for installing and troubleshooting an install of TFS 2008, assuming a MOSS 2007 farm exists and using a local instance of SQL 2005:

There are some preliminary steps that need to take place before you can just slap the CD in the drive and go. Here are those steps:

Preliminary Steps

1. Ensure you have reporting services, analysis services, integration services and notification services all installed with that local instance of SQL 2005. If anything’s missing the TFS install will break.
2. Open up the Services MMC snap-in and disable anything that has to do with SQL.
3. Install SQL 2005 SP1 on the TFS 2008 machine. If you already have SP1 on there, skip this step.
4. Re-enable all of those SQL services disabled in step 2 above.
5. Choose an AD service account that you won’t mind granting sysadmin role access on the DB, and local admin access on the box. You will also need to grant elevated permissions on a folder path once TFS is done installing. We’ll get there later.

Ok, with that out of the way, it’s now safe to install TFS 2008. Follow these steps to do so:

Installation

1. Run the setup app, click through the welcome screen and EULA.
2. Choose the destination path
3. If it’s not pre-populated already, type the local instance of SQL from the preliminary steps above.
4. A System Health Check will run and determine if TFS 2008 will have any problems installing. Here’s where the preliminary steps above come in handy. Any errors here and you should check to make sure everything was completed above.
5. Specify the service account to run web services. This should be the service account you previously identified. Although you can mix service accounts in these next few steps, don’t. I had nothing but trouble doing it that way. Keep it consistent and it’ll work. DO NOT USE A SYSTEM ACCOUNT!!! This will require you to grant sysadmin role access to the system account, as well as make the system account a local admin, etc. Not the safest solution IMHO.
6. Specify an SSRS service account. Again, make this the same account as step 5.
7. Specify Windows SharePoint Services settings. Enter the Central Admin path, including port number (http://server:port) and the default site URL in the appropriate spaces. Note that the default site MUST be a path to http://server/sites. After much research it seems the wizard forces new sites to be created in this path. There is a way to update the connections afterwards by using tfsadminutil configureconnections /sharepointsitesuri:new_site /sharepointunc:new_unc_path at the command line, but this did nothing for me. The default site in the Team Project creation wizard was still http://server/sites. If anyone knows how to get around this please feel free to chime in.
8. Specify alert settings. Here you can set up the SMTP server and reply-to address for alerts.
9. The installer will proceed by itself
10. Once finished, install TFS 2008 SP1 (unless it was part of your original app)
11. When that’s finished, install Team Explorer on each client machine that will be connecting to TFS 2008. Note there’s a 5 client limit for TFS 2008 workgroup edition. Team Explorer is found on the TFS 2008 workgroup edition CD. When you run the CD on the client, the main menu will have the Team Explorer option under Software. This will install the plug-in to allow connections to the TFS server.
12. After that, you need to ensure the TFS service account you selected is in the sysadmin role on all TFS databases on the TFS server. Open up SSMS and open the TFS server database instance. All TFS databases start with “TFS”. Go to Security->Server Roles, double-click on sysadmin and make sure your service account is in there.
13. Next, we need to make sure the service account has full control over the MachineKeys directory on the TFS server. Just dropping them in the local administrators group isn’t good enough, trust me, although that should be done as well. In Windows Explorer, navigate to \servernamedrive$documents and settingsall usersapplication datamicrosoftcryptorsa and right-click on MachineKeys, selecting Properties. There should be just Administrators and Everyone in the Security tab. Make sure both have Full Control, then click Advanced. Check the “Replace permission entries on all child objects” box and click OK. Click OK at the warning also.
14. Next, the two main templates for TFS 2008 need to be uploaded to MOSS 2007. Log onto the MOSS 2007 box as an administrator, open up a command prompt and type the following commands:

cd c:program filescommon filesmicrosoft sharedweb server extensions12bin

stsadm -o addtemplate -filename “\tfsservernamedrive$program filesmicrosoft visual studio 2008 team foundation servertoolstemplatesMSFAgile30.stp” -title “VSTS_MSFAgile”

stsadm -o addtemplate -filename “\tfsservernamedrive$program filesmicrosoft visual studio 2008 team foundation servertoolstemplatesMSFormal30.stp” -title “VSTS_MSF_CMMI”

The “title” argument of each of these commands is very important, TFS 2008 cannot create a new website if the title of each of these templates is not spelled exactly this way.
15. Restart IIS on the MOSS 2007 box.

That’s it for installing and configuring the TFS 2008 server. Now let’s configure the Visual Studio 2008 instance on each box. Don’t worry, it’s quick:

Configure VS2008

1. Open Visual Studio 2008 on the client machine.
2. Click on Tools->Options
3. Expand the Source Control item on the left and click on the Plug-in Selection option.
4. Select Visual Studio Team Foundation Server from the list and click OK.

That’s it, and that’s enough. Holy installation, Batman! Now, to create a new Team Project try this:

Create a Team Project

1. In Visual Studio 2008, click on File->New->Team Project
2. Select the TFS server from the list and click OK
3. Provide a name for the team project. Note this name will become part of the URL to the website, so to avoid HTML encoding use underscores and avoid symbols and spaces here.
4. Select the process template from the list.
5. Project portal settings are next. The title will be the website title, so spaces and symbols are ok here. The description will go under the title and above the “announcements” web part on the website, and the template will append info about the template to the end of your description without your consent. It’s ok, you can modify it later in MOSS. Note you cannot modify the project portal address.
6. Specify your source control settings, either create a new empty repository, branch onto an existing repository, or skip source control altogether.
7. Click Finish and you’re done! FINALLY! The wizard will create your website, reporting, documentation, source control repository, and project.

There’s one final step that needs to happen if you want to add items to the source control of the team project, and that’s creating a mapped workspace. The workspace by default is your machine, but the source control needs to be able to map it’s repository to a folder in the workspace. To do this, with Visual Studio 2008 open, click on View->Team Explorer, then in the Team Explorer window, double-click on Source Control for the project in question. This will open up Source Control Explorer. Locate the Workspace drop down list, open it up and select “workspaces”. Select your machine and click Edit. In the Working Folders section at the bottom of the window, choose the “Source Control Folder”, which will be the current working repository, and also choose the Local Folder to the right. Then click OK to map them.

So, let’s say you’re having trouble creating a project site. You’re getting errors and the description is not a very good one. You’re not alone, the forums are packed with this. Here are some steps you can take first before hunting for your needle in a haystack:

Troubleshooting

  • Make sure the Instance for all of the TFS databases matches. You can find this value by right-clicking on the VersionControl database and choosing Properties. Then click Extended Properties. Note the TFS_INSTANCE value. It should be the same for all databases. If not, make sure the other databases match the value in VersionControl. The article is detailed here
  • Double check your permissions on the MachineKeys directory listed above.
  • Try recreating the InstanceID on TFS and SQL. The details are found here

Whew! That’s all I have, and I think it’s enough. Like I said, if anyone has an easier method of accomplishing this feel free to mention it. Otherwise this guide is the result of numerous trial and error steps that were documented as they happened. A final install from start to finish was conducted and it worked. I hope this helps whoever needs it!

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

WSS 2.0 error when adding new users to a site

Yeah, I know, they’re still running WSS 2.0. I’m just here to help, not judge. = )

So I get the request (and I got it before and forgot how I did it) from a site admin that they can’t add a user to their new website they created. It says they already exist when clearly they do not in the Manage Users screen. This is generally because the SID of the existing user account doesn’t match the SID of the new account you’re trying to add. The solution? Well documented in this Microsoft KB article. The quick solution? If it’s a WSS site that is hosted on the portal site, go here:

http://ServerName/Sites/SiteName/_layouts/1033/Siteusrs.aspx

Otherwise go here:

http://ServerName/_layouts/1033/Siteusrs.aspx

Obviously replacing “servername” with your server and “sitename” with the parent site you’re trying to add the user to. Locate the user in the list, check the box and at the top click the “Remove Selected Users” link. Once gone you can now safely add this person to the parent and/or child sites without issue.

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube