Friday, April 25, 2008
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
FETCH NEXT FROM viewcur INTO @vtr
WHILE @@FETCH_STATUS <> -1
EXEC sp_refreshview @vtr
PRINT 'Errors found in view:' + @vtr + ', Error received: ' + ERROR_MESSAGE();
FETCH NEXT FROM viewcur INTO @vtr
Wednesday, April 23, 2008
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
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.
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.
Easy actually, follow the instructions below...
Open Computer: C:\Program Files\Microsoft Office\Office12
Find and Delete (putting it in recycle bin) this dll: OWSSUPP.DLL
Run Office Diagnostics by: Start -> All Programs -> Microsoft Office -> Office Tools -> Microsoft Office Diagnostics -> Run the Diagnostics
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.
Monday, April 14, 2008
Thursday, April 10, 2008
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).
- 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
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.