Getting Rows of data as a single column in a SQL query

Recently, I've been working on a Sharepoint Based document workflow solution that uses a custom SQL database. I ran into an issue where I needed to join a database table and get many rows of data back as one column in a query.

For example:
Table A:
ID, Name,

Table B:
childName, ParentID

where parent ID is table A.ID


I needed my results to be in the format:
A.ID, name, (childName1, ChildName2,ChildName3,....)


I found a few options,
One: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Provided about a dozen different ways to do it. All of them looked overly complex.

Then i stumbled upon:
http://johnnycoder.com/blog/2006/09/05/concatenate-multiple-rows-into-a-single-string/

which provided a simple Scalar function to return the results. I took that method and put together a SQL Scalar function to do it:

CREATE FUNCTION GetChildrenFromParent
(


@ParentID varchar(15)
)
RETURNS varchar(5000)
AS
BEGIN

DECLARE @ChildList varchar(5000)

SET @ChildList = ''

select @ChildList = coalesce(@ChildList + ', ', '') + childName from B where ParentID=@ParentID



-- Return the result of the function
RETURN @ChildList
END
GO


Then my SQL to get the data looks something like:
select *,GetChildrenFromParent(id) as ChildNames from A

This returns results like:

1|Jeff| andy,mark,shannon

so, the one column is a comma separated list of rows of data from another table.

How to make a film strip the default view for a Picture Library

Recently, I was trying to make a picture library for a customer to store their pictures in. I would prefer to use the film strip view as the default view because I think it is nicer looking.

The problem is that the details/filmstrip/thumbnails views are controlled via javascript, they do not change the URL when you visit the page and they cannot be set as the default view.

I found:

http://jyhuh.com/blog/2009/11/how-to-make-a-filmstrip-a-default-view-for-a-picture-library/

which has a pretty good description of how to fix this issue. Only problem is that the most important part, the javascript, is not visible!

I've decided to post it here so that it's easy to find.

<script>SwitchViewStyle('filmstrip');</script >

You'll need to add a content editor to the page, and add this javascript to it.

Make sure you add the content editor web part after the Picture Library View Web part. Otherwise, as the page loads, it runs the javascript before the Picture library exists and does nothing.

What happened to my SharePoint Usage Analysis Logs?

Recently, I went to check my usage analysis logs and noticed that they were practically empty. No new data in weeks. I know that it was working before, but what changed? What's going on here?

I double checked that everything was enabled in CA, the SSP and in the site settings, and everything was still good. I also noticed that my Log location was D:\UsageLogs. I remember setting that up, so i went and looked at the logs. There was data, but only for the CA site. After some checking, I realized what was going on.

Make sure that all your App Pool accounts have Access to write to that location. For me, only CA had the correct rights. After giving the WSS_WPG and WSS_RESTRICTED_WPG and WSS_ADMIN_WPG groups Write permission to that folder, the logs immediately started filling up. No errors in the event logs were being created for me, or else I would have noticed this a long time ago.


Conclusion: Just give all your SharePoint accounts Admin access on your servers, it'll save you a LOT of headaches. Or, even better, just run everything under one account! HAHA, no, don't actually do this, but always check permissions when things aren't working as you expect.

Oh NO! I deleted a site that I shouldn't have!

There is no recycle bin for sites. You probably know this, or you or your users found this out the hard way. This has happened to me more than once, and is the topic of a previous post about removing the 'delete this site' link.

Let's stay that you haven't done that, and you don't have a nice SharePoint backup solution that will let you easily restore deleted content. A user has just deleted a site and has come to you to get it back.

Possible solutions:
Solution 1:
Copy your backups(you have those right? :P ) to another SharePoint server and create a new Web Application using that DB. That site should now have the content that you lost. Migrate it back over to your production portal.
This is a solution, but migration can be a pain the butt. Also, it assumes you've got backups. You'll lose all your version history, and information about created and last modified. If your site is small enough you might be able to make a template out of it and rebuild a new site from there. You might even be able to use stsadm to backup and restore. Both of those options will run into trouble if your site is too big or complex.

Solution 2:
Roll back the database to a time before this disaster happened. This solution will work if you don't have backups(I think) :) and everything will seem as if nothing happened. The down side is that you will have lost everything that has happened before the roll-back point. Hopefully you were alerted quickly to the problem.
First, make a backup of your transaction logs. if you already have backups, this should be enough. If you don't, you'll want to backup the site as well.
Next, Restore a Database. Give it a new name, and pick a time that you know to be before the problem happened(but not too far back or else you'll lose more data). Choose your live database as the place to restore from. You should see it list your backup's. If you didn't back up your transaction log, you'll only be able to restore to the last backup. That is why it is important to backup your transaction log before you start.
Once the restore is complete, you'll have a copy of the DB from before the problem happened. detach your production DB, and rename this restored copy to the production name. You should be good to go.
You may want to shut down the site when you do this so that no new data is uploaded, since it will be lost. If done correctly, you should be able to restore the site very quickly, but it depends on how big your content DB is and how much traffic you get.

Good luck! And now go delete the "delete this site" link from your _layouts/settings.aspx page so that users can't do this again in the future :P

Add webpart to newform, DispForm and EditForm pages

Lists and Doc libraries both have Edit and DispForm pages. These pages have webpart zones on them, and adding your own webpart is possible. For some reason, sharepoint doesn't want to let you do it(the Edit Form Site action is grey'd out).

to get around this, add:
&PageView=Shared&ToolPaneView=2

to the end of the URL. It will put the page into the browse to add a webpart view. You should be good to go from there.

Error Code Gotchas due to Policy Settings

I run into a few problems on almost every SharePoint install.
David Szabo has put together a great post on some of the common ones:
http://blogs.msdn.com/dszabo/archive/2008/01/02/some-more-moss-gotchas.aspx

I've also run into issues with search not being able to search itself. This is due to a registry setting to prevent the server from DOS'ing itself. Make sure you add all the possibly URL's that your site is running to the registry setting.
http://support.microsoft.com/kb/896861



One more thing that always seems to come up a few months after the SharePoint portal has been up and running causes IIS to lock up. You'll get lots of errors in the event log for 6398, 7076, and 6482. You also won't be able to administer IIS. This has to do with two processes trying to access IIS admin service at the same time.

Install IIS Hotfix KB946517 and restart. This should fix the issue.


Problems with the timer service can also be caused by the scheduled task service being disabled. You'll re-enable it and it will turn off again. This is due to a group policy setting shutting it down. Have whoever manages the policy settings disable that setting on your sharepoint machines.

Errors with DCOM settings:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID - Error 10016
Check out: http://geekswithblogs.net/mhamilton/archive/2006/12/19/101568.aspx

http://geekswithblogs.net/mhamilton/archive/2006/12/19/101568.aspx



I'm sure there will be more to come as I remember them.

Add Webparts Popup missing links and buttons

After a few days of modifying the CSS and master pages for SharePoint and thinking everything was good I found the Add Webparts popup had lost it's buttons and the Advanced Link!

Tracking down what's going wrong can be tricky. This page uses the same CSS as the rest of your site, so good luck tracking down what you did. Also, this page doesn't work in Firefox, so using FireBug isn't an option(more on that later). And worst, IE won't let me right click and get Page Source!

Fortunately, someone else has hit this problem:
http://sharethelearning.blogspot.com/2009/01/missing-buttons-and-link-on-add-web.html

This person did the work to track down which page is used: /_layouts/webpartgallerypickerpage.aspx

Going directly to the link will let you us FireFox and Firebug to track down the problem. For me, I was setting the height of the page to Auto, not 100%. This caused the buttons and advanced link to appear near the top of the page, and behind the box that lets you pick webparts. I simply added:

#mainTable{
height:100%;
}

to my CSS file, and everything was fixed. :)