SQL Server 2005 - Refresh All Views in All Schemas

The textbook reason to run sp_refreshview: Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

You could turn this into a stored procedure, accepting a parameter for schema (@filterschema varchar(255)) and adding the where clause: where table_schema=@filterschema. Just some thoughts.

T-SQL with error catching for SQL Server 2005 (for example a column was removed from a table but is still referenced in a view):

DECLARE @vtr VARCHAR(1000)
DECLARE viewcur CURSOR FOR
SELECT table_schema + '.' + table_name FROM information_schema.views
order by table_schema, table_name

OPEN viewcur
FETCH NEXT FROM viewcur INTO @vtr
WHILE @@FETCH_STATUS <> -1
BEGIN
BEGIN TRY
EXEC sp_refreshview @vtr
END TRY
BEGIN CATCH
PRINT 'Errors found in view:' + @vtr + ', Error received: ' + ERROR_MESSAGE();
END CATCH;
FETCH NEXT FROM viewcur INTO @vtr
END

CLOSE viewcur
DEALLOCATE viewcur

SharePoint + Active Directory + Bogus Accounts = Confusion

SharePoint pulls active directory accounts, easy right? But for my corporation there are tons of accounts that are not actual user accounts, but necessary for normal operation (conference room accounts, guest accounts, etc.) I had to dig through the SharePoint tables to create a solution to display not only the actual user accounts, but parsed into individual divisions for display on separate portal pages (division only names).

Deconstructing SharePoint AD pull...

I created a table, called SP_ADUsers to push data into it in a more understandable structure so I matched fields with what I needed.

The main tables in SharePoint (sharedservices database, named whatever you configured it to be named) are UserProfile and UserProfileValue. I first created a view so I could limit the userprofile ids in which I need to push into my more user-friendly table.

The sql query started like so:
--sameple view sql
select * from [sharedservicesdbname].dbo.UserProfile

This returned over 2,500 records, and after scrubbing out the bogus accounts, I am left with a little over 1,950 valid user accounts. I eliminated accounts by simply adding where clauses, both eliminating actual preferrednames or like ('%tester%') preferred names which would eliminate all tester accounts we utilize.

Next I joined (multiple times) the userprofile table to the userprofilevalue tables in a statement like so:

select Convert(VarChar,upv.PropertyVal), --firstname
Convert(VarChar,upv2.PropertyVal), --lastname
Convert(VarChar,upv3.PropertyVal), --workphone
Convert(VarChar,upv4.PropertyVal), --workemail
Convert(VarChar,upv5.PropertyVal), --office
Convert(VarChar,upv6.PropertyVal), --title
Convert(VarChar,upv7.PropertyVal), --department
Convert(VarChar,up2.PreferredName), --managername
Convert(VarChar,upv8.PropertyVal), --fax
Convert(VarChar,upv9.PropertyVal), --cellphone
Convert(VarChar,upv11.PropertyVal), --company
Convert(VarChar,upv12.PropertyVal), --city
Convert(VarChar,upv13.PropertyVal), --state
Convert(VarChar,upv14.PropertyVal), --country
Convert(VarChar,up.NTName) --login
from [sharedservicesdbname].dbo.UserProfile up
left join [sharedservicesdbname].dbo.UserProfileValue upv
on up.recordid=upv.recordid and upv.propertyid=4 --firstname
left join [sharedservicesdbname].dbo.UserProfileValue upv2
on up.recordid=upv2.recordid and upv2.propertyid=5 --lastname
left join [sharedservicesdbname].dbo.UserProfileValue upv3
on up.recordid=upv3.recordid and upv3.propertyid=8 --workphone
left join [sharedservicesdbname].dbo.UserProfileValue upv4
on up.recordid=upv4.recordid and upv4.propertyid=9 --workemail
left join [sharedservicesdbname].dbo.UserProfileValue upv5
on up.recordid=upv5.recordid and upv5.propertyid=11 --office
left join [sharedservicesdbname].dbo.UserProfileValue upv6
on up.recordid=upv6.recordid and upv6.propertyid=13 --title
left join [sharedservicesdbname].dbo.UserProfileValue upv7
on up.recordid=upv7.recordid and upv7.propertyid=14 --department
left join [sharedservicesdbname].dbo.UserProfileValue upv8
on up.recordid=upv8.recordid and upv8.propertyid=20 --fax
left join [sharedservicesdbname].dbo.UserProfileValue upv9
on up.recordid=upv9.recordid and upv9.propertyid=19 --cellphone
left join [sharedservicesdbname].dbo.UserProfileValue upv10
on up.recordid=upv10.recordid and upv10.propertyid=6
left join [sharedservicesdbname].dbo.UserProfileValue upv11
on up.recordid=upv11.recordid and upv11.propertyid=10001 --company
left join [sharedservicesdbname].dbo.UserProfileValue upv12
on up.recordid=upv12.recordid and upv12.propertyid=10002 --city
left join [sharedservicesdbname].dbo.UserProfileValue upv13
on up.recordid=upv13.recordid and upv13.propertyid=10003 --state
left join [sharedservicesdbname].dbo.UserProfileValue upv14
on up.recordid=upv14.recordid and upv14.propertyid=10004 --country
left join [sharedservicesdbname].dbo.UserProfile up2
on upv10.PropertyVal=up2.NTName and upv10.PropertyID=6
where up.recordid in (select recordid from [viewcreatedabove])

And inside a stored procedure that is run once a day (after the profile import on SharePoint) does the following:

1. Truncates the more userfriendly table I created for dataviews within SharePoint (SP_ADUsers);
2. Fills the table with the values returned from the select statement above.

Now I have a nightly updated, scrubbed active directory user list which doubles as our phone directory.

IE Crashes when accessing a file from SharePoint

Scenario:
One day a user can click on a hyperlink to a document (word, excel, one-note) in SharePoint and everything is great.

Next day the user gets an Internet Explorer error message or simply crashes trying to access the same document.

Cause (or at least my opinion on the cause):
The user computer has two versions of office documents, most likely 2003 and 2007. When office updates are applied (sometimes automatically if you have automatic updates on, or by SUS server pushes) the dll (OWSSUPP.DLL) is overwritten with incorrect information or is corrupted.

Fix:
Easy actually, follow the instructions below...

1. Open Computer: C:\Program Files\Microsoft Office\Office12

2. Find and Delete (putting it in recycle bin) this dll: OWSSUPP.DLL

3. Run Office Diagnostics by: Start -> All Programs -> Microsoft Office -> Office Tools -> Microsoft Office Diagnostics -> Run the Diagnostics

4. When complete, close Diagnostics and attempt to access a task list or open a document within SharePoint Portal.

This should fix the issue. Good luck.

SQL 2005: Change object schema

So simple that I usually forget the syntax...

USE [WHATEVERDATABSE]
GO
ALTER SCHEMA [NewSchemaName] --change to schema
TRANSFER [OldSchemaName].[ObjectName]
GO

SharePoint - Default Value for connected DataView webpart

Scenario (MOSS 2007):

I have a listview (standard format) connected to a dataview. Connection is source web part and provides row to target web part, which gets filter values from.

In essence, if you click on an item in the list view, I want to see a few particular fields in the dataview.

In this particular case, the listview was a list of open projects or tasks for members of our network group, and the comments were too long to display - it made the rows gy-normous and basically unreadable - also making the page stretch so long you had to scroll to eternity to get to the bottom of the list.

The culprit field was "comments" so we removed it from showing on the list. Now the title of the project, along with the comments, could be shown in the top webpart which was the dataview getting filter value from the listview.

Problem: When a user first opened the list, the first item (ID=1) would show the comments. It was as if the default value for the page was the first record. We did not want this behavior.

Solution: After some research on the web, I found musings about sending parameters in querystring. I also noticed that when I selected an item, the page was refreshed and a querystring parameter was being sent as: &selecteditem=... (number ID of task I clicked).

Steps:

1. Add a parameter:
• Under Common Data View Tasks (click the little boxed arrow on the right of the dataview when hovering over the dataview), then click on Parameters... link.
• Select New Parameter, I named it RecID, set Parameter Source to Query String, Query String Variable to SelectedID, Default Value: 0
• Click out of the Parameter menu and select Filter: in the Common Data View Task pop-up.
• Click in the field name, choose field (for my list I choose ID), left the comparison as Equals, then Value my new parameter of [RecID] was in the pull-down list

Now the default filter value for my dataview webpart was set to nothing (no 0 record existed in my list).

Sharepoint - Listview and enabling connections to other webparts

A colleague came to me and asked me to help her with more advanced functionality in SharePoint. She created a list and it contained too much data to display easily. It was suggested to her to make a small text box somewhere that, once a user clicks on an item in a list, the box or area is filled in with the list field, in this case a comment field.

She already tried creating a webpart and using the "Connections" ability without success. It was grayed out.

After some researching and testing, I started to believe her list (which appeared as a datasheet view and had the Access Web Datasheet icon in the upper left hand corner) format may be the culprit.

When I chose to display the list in Standard View, the connections menu item was enabled. I imagine, because I don't have time to do the research, that the Access Web Datasheet may not allow interaction to other webparts, but this is just a theory. I found no such information in a search through the web.

To see how I enabled the dataview webpart to default to an empty record upon the original page loading, see my next blog.