Archive for July, 2009

Helpful SQL Server Date Functions

One of my common duties is to create and/or update reports in SSRS (SQL Server Reporting Services). A large number of these reports ran from very unclean data. To compensate for this, the original developers handled the data in a variety of inconsistent manners, mostly around dates. Although this may have gotten the job done originally, it does nothing for the quality of the report.

As these reports were updated, we began using standard date functions to improve development time and report quality.


/* First Day of the Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

/* Last Day of the Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,GETDATE())),0))

/* First Day of Next Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,GETDATE())),0)

/* Last Day of Next Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,2,GETDATE())),0))

/* First Day of Last Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())),0)

/* Last Day of Last Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

/* First Day of the Year */
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

/* Last Day of the Year */
SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,DATEADD(yy,1,GETDATE())),0))

/* First Day of the Quarter */
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)

/* Last Day of the Quarter */
SELECT DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,DATEADD(qq,1,GETDATE())),0))

/* First Monday of the Month */
SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,6 - DATEPART(DAY,GETDATE()),GETDATE())),0)

~Ron

Advertisements

SharePoint Farm Expansions

When I started my current position, I inherited a SharePoint mess. Before I arrived a tech was tasked with installing SharePoint development environment in preparation for a deployment. Due to hardware constraints or some such issue, the development installation and hardware soon became the production environment.

The first hurdle was actually licensing the software. The installation was done with a demo version. We purchased the requisite licenses under out volume license agreement, which of course means that activation failed. Remember, this is a production environment. A couple of weeks later we acquired the proper key and we were on our way.

Our production environment had less than 250 total users and less than 50 regular users for the last year and has humming along quietly. Then came the expansion project.

Now, the current installation was the default installation, which means that it was running off of SQL Server 2005 Express. The service accounts were all NETWORK SERVICE. (Again, and I cannot say this enough, this was done before I arrived!)

We had to expand (finally) our SharePoint Farm. OK, first things first, lets get those service accounts configured properly and put into production. Best practices aside, this is necessary as we will be standing up an additional SQL Server and the SharePoint service accounts are the credentials used to access the databases.

Set the service principle name for each SharePoint service account.

setspn -A HTTP/ServerFQDN DOMAIN\Account
setspn -A HTTP/ServerFQDN:80 DOMAIN\Account
setspn -A HTTP/ServerNetBIOSName DOMAIN\Account
setspn -A HTTP/ServerNetBIOSName :80 DOMAIN\Account

Now do it for each SQL service account.

setspn -A MSSqlSvc/ServerFQDN DOMAIN\Account
setspn -A MSSqlSvc/ServerFQDN:1433 DOMAIN\Account
setspn -A MSSqlSvc/ServerNetBIOSName DOMAIN\Account
setspn -A MSSqlSvc/ServerNetBIOSName :1433 DOMAIN\Account

Now that the accounts are created we need to swap out the credentials. Simple enough task, but it failed miserably. The error referenced that an update was currently in progress. I was a bit confused, as I was the only one in the system, but after a bit of research I located a fix.

Stop the “Windows SharePoint Services Timer” service. Navigate to C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\{GUID}. Backup the contents of this directory. After the backup completes delete everything except cache.ini. Open cache.ini and replace the contents with “1” and save the file. Start the “Windows SharePoint Services Timer” service.

Now to redirect the SQL Server. Open C:\windows\system32\cliconfg.exe. Added an alias on the application server redirecting the old instance to the new one. Stop the SQL services on the old server and copy the data and log files to the new server. Attach the databases on the new server.

SharePoint was still throwing a fit, so for good measure I rebooted both servers.

SUCCESS!!!

~Ron

Scrolling to a given row in WinForms DataGridView

We needed to feature to take the user directly to the row which they search by an ID. Even though we have the Row.Selected property for the DataGridView it selected to the row but did not scroll to it. So I used a little hack to scroll to the DataGridView:

I wrote a loop to get the specific data the user is searching for in the grid (dgvDisabilityInformation) and I used the underlying binding source (bsDisability in my case) to set the current position as given below.

for (int iCounter = 0; iCounter < dgvDisabilityInformation.Rows.Count; iCounter++) { objRow = dgvDisabilityInformation.Rows[iCounter]; objCell = objRow.Cells[0]; if (objCell.Value.ToString() == txtGotoRowID.Text.Trim()) { objRow.Selected = true; bsDisability.CurrencyManager.Position = objRow.Index; Found = true; break; } } [/sourcecode] Works like a charm 🙂 Hope this helps. Happy Programming!!! Cheers, Raja