Another Hack's SharePoint Experiences

Tip o' the day: Getting the Content database information of a specific site

andrewjvelez — Tue, 11 Jul 2006 06:06:00 GMT

Here's a way to get the server and database name of the content database that a site belongs to using C#:

using Microsoft.SharePoint;

using Microsoft.SharePoint.Administrator;

public class Test

{

private static SPGlobalAdmin oGlobAdmin = new SPGlobalAdmin();

public static SPVirtualServer GetVirtualServerBySite(SPSite oSite)
  {
   foreach(SPVirtualServer iServer in oGlobAdmin.VirtualServers)
   {
    foreach(SPSite iSite in iServer.Sites)
    {
     if(iSite.ID == oSite.ID)
     {
      return iServer;
     }
    }
   }
   throw new IndexOutOfRangeException("Unable to find site in configuration database: " + oSite.Url);
  }

public static void Main(String[] Args)

{

SPSite oSite = new SPSite([Your site's URL here]);

SPVirtualServer oVS = GetVirtualServerBySite(oSite);

string SPContentDBServer = oVS.ContentDatabases[0].Server;
string SPContentDB = oVS.ContentDatabases[0].Name;

Console.WriteLine(SPContentDBServer);

Console.WriteLine(SPContentDB);

}

Hopefully someone finds this the least bit interesting...Personally I'm trying to recreate the connection string for the SharePoint DB, but shhhhh, don't tell anyone. Mucking w/ the SharePoint databases is not supported by MS.

Posted on SharePoint Blogs

Alerts

andrewjvelez — Fri, 23 Jun 2006 23:15:00 GMT

Anyone ever notice a common practice with Microsoft: how they make something that is ALMOST useful, but stop right at the place where they are juuust at the cusp of something being great and just make it OK? It's something that we notice here at my work...

Anyways, for just about the past 2 years I've been trying on-and-off to find a way to customize and automatically trigger alerts. I've been told that is a bad idea, but unfortunately we need it. Last month I attended SharePoint Experts SharePoint programming class, and since I've been tinkering a bit w/ Web Part coding, etc. (Aside: It was a good class...you probably got more out of it if a) you are experienced with SharePoint and b) you go having an idea of what you want SharePoint to do, rather than an expectation of learning what SharePoint CAN do. Imagination is helpful when it comes to programming web parts).

Since the class I've gotten a requirement to find a way to automatically create an alert for every creator of an item in a specific task list. Evidently the people in my company are too stupid to actually check on the requests they make, and instead pepper others with status update questions. One could make a point that this is really a training issue rather than a coding issue, but if every item does need an alert going to the creator, it would save time to programmatically do that rather than have everyone create an alert every time they make an item.

I've been playing with the SPAlert class, and let me tell you, it's a pain in the butt. First of all, I'd like a function to find all the alerts on a given list or item. Here's where that first rant plays in: Microsoft evidently thought that the only time someone might want to query the alerts is on a per user basis only. Uh huh... So I had to build a procedure to get the creator of the list item, which isn't as easy as you'd think. I would have thought that since every item has a creator, there might be, oh I don't know, a Creator property on the SPItem class. Nope. You need to do this:

SPSite oSite = new SPSite("Your site URL");
SPList oList = oSite.OpenWeb().Lists["Your list name"];
SPListItem oItem = oList.GetItemById("Item ID");
string userValue = oItem["Created By"].ToString();
int index = userValue.Index Of(';');
int id = Int32.Parse(userValue.Substring(0, index));
SPUser itemCreator = oSite.OpenWeb().SiteUsers.GetByID(id);

Yeah...that's great. This was actually in the SDK, if you looked under SPListItem and scrolled waaaaaaaaaaay to the bottom. So MS knew you might want to do this, but didn't put the functionality in to do it for you. Oh yeah, and you can't inherit from SPListItem to put this in yourself, b/c there's no default constructor. Sweet!

So you get the user, and then you have to loop through all the alerts that the user has. You need to compare if the AlertType, the List, and (if you are doing an Item alert like me) the Item ID are all the same on each alert. If someone has a better way to do this, let me know. Once you determine that the user doesn't already have an alert set up (b/c the user might also have a list level alert already set up), you can add an alert. I haven't gotten that far yet.

I'm considering just writing a stored procedure to do that check for me. I've looked into the DB a bit, and there are various values depending on the alert type/frequency you are searching. The immediate alerts are in the ImmedSubscriptions Table, and the daily/weekly alerts are in the SchedSubscriptions table. Here's what the values are:

Change Type	EventType (DB)	Frequency	Notify Freq (Sched)
All	-1	Immed
Add	1	Immed
Changed	2	Immed
Deleted	4	Immed
All	-1	daily	1
Add	1	daily	1
Changed	2	daily	1
Deleted	4	Daily	1
All	-1	weekly	2
Add	1	weekly	2
Changed	2	weekly	2
Deleted	4	weekly	2

When I finish my code (including SPs) I'll post it here if someones interested. What I have yet to overcome is how exactly do I trigger the alert creation. I'm considering writing a web control I can plug into create item page, but I don't know if I can both call the Submit form routine and the create alert stuff I'm making. I'm also considering creating a service to check on new list items and adding the alerts on the back end. I'm honestly not sure. If someone has ideas on that, I'd love to hear them.

Anyways, it's good to be back after 2 years. Hopefully I'll post w/ more frequency.

Tips I learned:
if you ever end up with a really long list [mine had 10000+ items] don't get an item by doing SPList[ItemID]. I ended up getting memory leaks and things died. Use SPList.GetItemById(ItemID). You'll be a happier person for it

Posted on SharePoint Blogs

SharePoint Database/List Best Practices???

andrewjvelez — Sat, 22 May 2004 01:44:00 GMT

Anyone out there have a reference to what the best practices for programming SharePoint are? Specifically, how much normalization one should use in their database/lists? Here's my scenario:

I'd like to create a Time Off Request and Reporting application. It would have the following operation:

Employees would go to a form to request a period or day as PTO
Managers would be alerted to the request and either approve or deny it (which would then update the employee
When the scheduled day came, the manager would confirm that the employee did or didn't take the day off
Payroll would be able to run a report of the data to see who took what time off. Managers could run the same report for their departments. Employees could not.

With a normal database application, I'd have 4 related tables: Employees, Departments, Administrators (but actually, this could be a boolean field for the employee table), and the PTOData table. However, I'm not sure if you can write all of these within the context of SharePoint lists. Anyone have any ideas? Should I just create separate tables, and only use SharePoint as a wrapper? Let me know what you think!

BTW - Dustin - I asked my manager if they'd send me to the dev training. If I can go, this is something I'd like to cover. For those of you who also would like to go, but are afraid of asking your boss for a few grand, I can send you my request document (the sucker was 2 1/2 pages long!) as a template.

Posted on SharePoint Blogs

SMigrate Bug

andrewjvelez — Fri, 14 May 2004 00:27:00 GMT

I think I found a bug in using SMigrate to move a site - it's only a minor one. I'm putting it here because I'm not sure where to submit bugs to Microsoft anymore, and I'm not willing to spend an hour to figure it out.

Here's the scenario: I used SMigrate to copy our production WSS site collection to some development servers. It worked great, actually: the prod server was mis-configured to use a MSDE database rather than a Web Farm w/ SQL, but when I moved it to a server w/ that configuration, it moved the data into SQL just fine. However, when I went to add a Web Part to the development server, it tried to connect to the old server's web part library. I know this because it prompted me to log into the old server.

I'm not sure how to fix it quite yet, but I'm going to hack into the database in the next few days to figure out where it's storing the old server's name. If I find anything, I'll update you.

Posted on SharePoint Blogs

Starting to feel constricted...

andrewjvelez — Thu, 13 May 2004 00:58:00 GMT

I'm starting to feel pretty constricted by SharePoint. There's a lot of little things that I'd like to customize that you just can't with SharePoint right out of the box. For instance, I've gotten requests to make attachments open in a new window when you click on them. In fact, most of my limitations seem to come from the inherent inflexibility of the out of the box web parts. Yes, you can change some of the attributes. However, in order to customize the display of the data, you need to convert the views from web parts to data views. Of course, in a data view, you can't edit the data. It's all very frustrating...I wish you could edit the default web parts...If you know a way, please let me know.

I'd like to be able to automatically create alerts for the people who actually submit an item to issue lists, rather than only send out an email only to the person the problem is assigned to.

Also, there aren't any books on using only SharePoint, at least none I could find. The documentation is pretty sparse. It seems like the primary method for learning SharePoint remains seminars like those put on by SharePoint Experts. They are good classes, don't get me wrong, but they are few and far between. Not only that, but the price for a book would be around $50 - $70, while classes are around $2000 w/o room and board. You do the math...

It looks like in order to get SharePoint to do what we want it to do, we're going to have to write custom web parts and web pages. That isn't too big of a problem, as that the SDK does in deed seem to have a lot of information in it. I'm just ignorant of how to code specifically for SharePoint, and I have a pretty high learning curve to overcome to learn it. Dustin, when's the next coding class?!

Enough ranting for me. If you have any ideas how to correct or work around my problems, let me know, please! ;)

Posted on SharePoint Blogs

Upgrading from Windows 2000 to Windows 2003 and Sharepoint

andrewjvelez — Tue, 27 Apr 2004 20:16:00 GMT

This is actually a problem with the Windows upgrade, but nonetheless the problem manifested itself while trying to install the sharepoint central database.

I had a development computer that was running Windows 2000 Server. I wanted to run SharePoint and use Visual Studio.Net 2003 to create some web parts. However, to do so, I had to upgrade to Windows 2003 Server. Normally I don't upgrade, since problems occur. However, I had heard some good things with the upgrade wizard. Note to self: never believe them. The upgrade itself went smoothly, however, I started having problems once I tried installing SharePoint.

The installation itself was smooth, but once I had to configure/create the central database, I started having problems. I'd fill out all the information, but every time I'd click submit I would be forwarded to a generic error message screen. I then tried creating the DB from the command line, by running: “stsadm -o setconfigdb -databaseserver [name] -databasename [db name]” Then I got a decent error: GetTextExtentPointI could not be located in msdart.dll. I found some good information here, and it fixed my problem for now. Basically, you need to copy over the oledb32.dll from another server that got a fresh install of Windows Server 2003 to the upgrade server.

I hope that helps someone else out.

Posted on SharePoint Blogs

Microsoft and the CSS demons

andrewjvelez — Fri, 23 Apr 2004 19:01:00 GMT

Like I've mentioned before, I've customized a lot of my sharepoint site by changing the style sheet and backend standard graphics. Unfortunately, the limitation is that you have to rely on Microsoft to properly assign stylesheet classes properly to all the elements. For reference, here's a link to a screen shot of my page, since the gallery still won't work.

My latest issue was that a user suggested the colors in the list view nav bar change from the default blue to white. I didn't have a problem with this one bit - I viewed the source of the list page, found the link, and saw that it belonged to class ms-toolbar (fairly intuitive, huh?). So I opened the style sheet (Program Files\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\LAYOUTS\1033\STYLES - default location) and changed the sheet to have color: #FFFFFF;. Problem solved.

However, someone coding the web part had a brain fart, because any links within the web part are assigned the same class. However, the background at this point is also white, so you get invisible text. I didn't even realize that until I got another complaint not 5 minutes later. Unfortunately, this wasn't a case of a higher level tag being assigned the ms-toolbar class: it was the a tag itself. Now, will someone please explain why your class would be called ms-toolbar, and you'd have something in the main content window be assigned to the ms-toolbar class? It didn't and still doesn't compute for me.

So, if anyone out there has any ideas for how to work around this, I'd love to hear them. Right now, I just changed the color of ms-toolbar to #990000 so that you can see it in both places. It's still hard to see against my orange background, though. Shoot me a line if you have any ideas.

Posted on SharePoint Blogs

Alright! Got the listeners to work!

andrewjvelez — Fri, 09 Apr 2004 11:49:00 GMT

Addendum: People have been asking me why I wanted to do this. I explained it in a previous blog (I think) but here's the short version:

I have an Issues List for internal system operations. If a phone goes down, or a computer dies, a user could enter a ticket into the system. However, most of our users are ignorant as to who would fix each type of issue. So I have two drop down boxes: a Category and Assigned to. When you select a certain category, the assigned to box automatically changes to the person who handles that type of issue. The “listener“ therefore is used to listen to the onChange event for the category.

------------------------------------------------------------------------------

W00t! I got the listeners to work. Here is the JavaScript, and then I'll explain it below:

window.onload = startup;
function startup()
{
setAssignment();
document.forms[0].elements[getElementIndex("OWS:Category")].onchange = setAssignment;
}

function setAssignment()
{
var getCategoryTagIndex, getAssignToTagIndex, getCategoryText;
getAssignedToTagIndex = getElementIndex("AssignedTo");
getCategoryTagIndex = getElementIndex("OWS:Category");
getCategoryText = getSelectedElement(document.forms[0].elements[getCategoryTagIndex]);
setAssignedTo(document.forms[0].elements[getCategoryTagIndex], document.forms[0].elements[getAssignedToTagIndex]);
}

function setAssignedTo(CategoryObj,AssignedToObj)
{
switch(CategoryObj.options[CategoryObj.selectedIndex].text)
{
  case "LoopNet.com website error":
   searchAssignedTo("[User Name]", AssignedToObj);
   break;
  case "InsideLoop website error/suggestion":
   searchAssignedTo("[User Name", AssignedToObj);
   break;
  case "E-mail":
   searchAssignedTo("[User Name]", AssignedToObj);
   break;
  case "Network file access":
   searchAssignedTo("[User Name]", AssignedToObj);
   break;
  case "Phone problem":
   searchAssignedTo("[User Name]", AssignedToObj);
   break;
  case "Local workstation problem":
   searchAssignedTo("[User Name]", AssignedToObj);
   break;
  case "Other":
   searchAssignedTo("[User Name]", AssignedToObj);
   break;
  default:
   searchAssignedTo("[User Name]", AssignedToObj);
   break;
}
}
function searchAssignedTo(userName, AssignedToObj)
{
for(i = 0; i < AssignedToObj.options.length; i++)
{
  if (userName.toLowerCase() == AssignedToObj.options.text.toLowerCase())
  {
   AssignedToObj.options.selected = true;
   return;
  }
}
}
function getSelectedElement(selectObj)
{
return selectObj.options[selectObj.selectedIndex].text;
}
function getElementIndex(elementName)
{
var e;
e = -1;
for (i = 1; i
{
  var temp;
  temp = document.forms[0].elements.name;
  if(temp.indexOf(elementName)>= 0)
   {
   e = i;
   break;
   }
}
return e;
}

//-->

I hope this shows up, since it's javascript. Basically I put this at the end of my issues form, right after the end table tag. This is so that the page loads before the script runs, allowing time for the web part to load. The first thing I do is set the window.onload event handler to be my startup function. That way the values are set on every page load. Within that I run two other commands. First, I run the setAssignment function, to do the initial setting of the assigned task, just in case the user doesn't change it. Then I set the onchange event for my Category to be the setAssignment again. You'll notice a couple of things. First of all, we aren't sure of the names of the form or the drop down list. So instead of using the name, we use the forms[0] element and then search for the element index for the category drop down list. So, here's a description of the functions:

startup: is the window.onload event handler

setAssignment: the event handler for the Category select drop down box. It calls the functions to find the indexes of the Assigned To box and the Category box, then passes those objects to the actual function that changes the assignment.

setAssignedTo: the function that actually changes the assignment. It takes two parameters: the assigned to box object and the category box object. It reads the category, then loops through a switch. The switch reads the category, and then sets the proper assignment. If none of the options are found, then it sets the assignment to the administrator (me).

searchAssignedTo: basically just a loop that checks the the name given to the name in the assignment box. If they are the same, it sets that selectedIndex to true. It could be included in the setAssignedTo function, but this saves having to code it a grip of times

getSelectedElement: gets the name of the element passed to it. I had it here for debugging purposes; it can be removed.

getElementIndex: gets the index of the element that matches the string you pass into it. Be careful here. You want ot make sure that you pass the right name in. I thought I could search for an element with the word Category in it, but you can't. You need to look for OWS:Category. So watch yourself. You can find these names by opening the form and then browsing through the source.

So that's basically it. Let me know if you have any questions on this, and I'll be happy to explain. It took a team effort here to come up with the right script. Hopefully this will save you some time.

Posted on SharePoint Blogs

Setting up JavaScript listeners

andrewjvelez — Wed, 07 Apr 2004 21:29:00 GMT

UPDATE: I got this to work - see the next post!

So I'm trying to set up a listener on my Issues list. It's not going well. Microsoft looks to be using some kind of custom drop down box, rather than a stardard select. It's killing me. When I find out how to fix it, I'll post it here. Hopefully it won't take more than a day.

Posted on SharePoint Blogs

SQL Frustrations

andrewjvelez — Thu, 01 Apr 2004 03:19:00 GMT

Well, I blew it already on my WSS server. I installed WSS on it before I installed SQL Server, so it installed the MSDE database instead. Well, I tried to upgrade it to SQL Server 2000, SP 3, but I couldn't get it to upgrade correctly. Instead of upgrading the MSDE database, it installed SQL along side it, and I don't know if it transfered the data. I couldn't get the SQL Server service started, and I couldn't get into MSDE via Enterprise Manager. I'm glad 99% of my customization at this point had been to the .CSS and XML files rather than the database. I've backed that up.

I tried uninstalling, well, everything, but there was a problem with the MSSearch service. For whatever reason, it decided it wanted to install itself on my network home directory (H: drive). I couldn't get it off. I removed all the registry entries I could find for SQL, I removed the registry entries under HKLM\Service for MSSearch, and I tried simply reinstalling SQL. Nothing worked. SQL would install OK for me, but when I tried applying SP3, it would fail on MSSearch.

So, I'm doing the only thing left to do now: reinstall Windows 2003 from scratch. Oh well...previously I had used Windows Server 2003 Standard when I wanted to use the Web Server Edition. I couldn't find the disks at the time, but I found them now, so I get to have that right, at least.

A word from the newly experienced: if you plan on using SQL Server, select Web Farm configuration during WSS installation, and more importantly, install SQL Server BEFORE you install WSS. In writing that last sentence, it all seems pretty obvious, but I guess when you feel you know what you're doing, you get careless.

Good luck to you!

Andrew

Posted on SharePoint Blogs

So why the heck are you blogging?

andrewjvelez — Wed, 31 Mar 2004 20:26:00 GMT

You know, that's probably the first question that's on your mind. It would be for me, too. Well, this post will hopefully explain my purpose in setting up this blog.

I'm pretty much new to SharePoint - WSS 2003 is the only one I've used - so I'd like to share my experiences with you other newbies out there and hopefully save you some time and heartbreak. I've been charged to use SharePoint to create our company's intranet site. There's some fairly intense stuff I need to do to make it work, so hopefully there will be a little something in it if your bother reading this stuff. One of the things I'll be doing in the next few weeks is migrating a stand-alone WSS server that is currently using an MSDE database to the web-farm model of WSS that talks to a SQL 2000 database on a remote server. Then I need to copy the data from that server to another server, and then sync both servers to have the same data and pages. Fun stuff coming down the pipes. So stay tuned.

BTW: I recently attended the SharePoint BootCamp in Anaheim, Cali. I'll post a longer review in a later blog, but for now know that it was a lot of fun and very informative. Oh, yeah, and if you made it this far into my blog, have some mints (inside joke).

One more thing, if you ever have a comment, correction, question, or answer about a blog I write PLEASE post a comment or e-mail me. This is meant to be a discussion that anyone can add to.

Later!

Andrew

Posted on SharePoint Blogs