Displaying Document Libraries in other sites

A client recently asked for a modification to Business Portal - Requisitions Management. They wanted to post a library for supporting documentation (quotes, product spec sheets, etc.). The problem is that they wanted it visible in both the Employee Purchase Request entry screen and the Purchasing Requisitions management screen and these pages are in two entirely separate sites.

As you may know, SharePoint is great at showing content from all the libraries you set up....as long as they are within the same site. Site A has no clue that Site B even exists.

The key for this solution came down to using the Page Viewer web part. This web part allows you to display another website within a window on another page.

The next challange is that SharePoint pages all include navigational borders and headers. When you display a SharePoint page within another page, all this flotsam is included, making a really confusing page. Here's how I worked around this issue.

Step 1: Setup your document library. Because I wanted to make sure that all users could get to the library, I set it up in the main Business Portal site. You can add permissions to the library if you need to in order to keep the right people in and the wrong ones out. Add any custom fields you might need to help find your documents more quickly. In this case, I added a mandatory Requisition ID field and an optional description field.

Step 2: Create a view for the document library that only shows the columns you'll want displayed on the other page.

Step 3: Create a new blank page that we'll use to display the document library in a web part. We'll be adding code to hide all of the navigational elements, so we don't want to do this on a page that's needed for something else. In this case, I went to the Business Portal Common Pages library and added the new page there, calling it RequisitionDocs.aspx

Step 4: Add the document library web part to the page.

Modify the web part to your liking. For me, I like to remove the ability of the user to do anything bad, like deleting the web part, moving it around, etc. I put it there for a reason, now you leave it alone! :) You'll also want to either have no title showing or show the title only. Select the view for the document library that you created above.

Step 5: Add another web part - the Content Editor web part. This is where we'll hide everything else on the page like the borders, the header...all that junk. In the content editor page, paste the code found on this page: http://vspug.com/spfromscratch/2007/12/18/blank-page/

NOTE: Take the "*" characters out of the word "java*script and don't forget the colon after the work "java*script"

As soon as you save the content, the page should change dramatically. If you're brave, you can remove the link at the bottom. Just remember though that you need a way to get back into edit mode so DON'T hide all your titlebars!

Step 6: Now that we have the page built that we want to display, we can link to it from our source pages. Edit the page where you'd like to display the library and add the Page Viewer web part. Modify the web part and add the url to your document library to the Link field. In this case, the URL would be http://dynamics-web/BP/Business%20Portal%20Common%20Pages/RequisitionDocs.aspx

Adjust the web part to be the way you want it on the page and Viola!

To take it further, the list view that I created had title that you could click on which then loaded the document library in my web part...bringing back all the navigational nastiness. To get around this, change the titleurl field in the document library on the display page (requisitiondocs.aspx). Instead of pointing to the document library, use the hash character instead (#).

Another problem is that in the default views for the document library, there's an "Add new document link" that again loads a full page for uploading documents. Sheesh, I try to get out and they keep pulling me back in! To get around this, edit the document library web part on your display page and change the toolbar type to "No Toolbar".

I have quite a few sources to thank for this, most of all is the code for hiding the nav elements. I found it posted on this page - http://vspug.com/spfromscratch/2007/12/18/blank-page/

The tip for hiding the Title URL was found here - http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/ac235b36-b516-40d3-bd2d-ea7dccd8eef1 and the tip on hiding the "Add new document" link was found here - http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/9561ff37-bd33-44ec-be45-82f0fcd868db

Sharepoint or Sharepoint Central Admin Returns Error “The File Exists (Exception from HRESULT: 0x80070050)

One of my clients is a financial institution. As such, they have pretty stringent controls on user accounts for vendors that may need to work on their system. Several months ago, I was brought in to do an install of Business Portal, which requires SharePoint.

I was given a temporary admin level userid to use during the setup, which I used to install and configure SharePoint and Business Portal.

Today I was called back to resolve some issues with Business Portal. When I tried to log back into SharePoint, my access was denied. I tried to get to Central Admin and received the error – The file exists. (Exception from HRESULT: 0x80070050).

Basically, what has happened is that the user account I was given initially was deleted after the initial install was completed and then recreated when I was asked to come back and repair Business Portal. They also never created another Admin login for the Central Admin and SharePoint sites. When the new account was created, AD issued a new SID (Security ID) which is the string of characters that REALLY defines your account. Your username is just a descriptive field. SharePoint provides access based on the SID, not just the username field. So, my username is the same, but the SID is different, preventing me from getting to SharePoint. The problem is that my account is the ONLY one that has rights – not a good situation for the client to be in.

Fixing this problem….well, it’s not a simple procedure. I’ve spent nearly two hours now researching and testing ways of getting the SID updated in the SharePoint database.

Here’s the procedure to go through to get the SID changed for a user in SharePoint. You’ll definitely earn your Geek badge for this one.

WARNING: This is a low-level solution, requiring hackery and other risky behaviors. USE AT YOUR OWN RISK ONLY AFTER YOU HAVE A BACKUP.

Procedure:

• Get the SID listed in SharePoint
• Get your current SID from Active Directory
• Convert your current SID from binary SID to HEX
• Update the database with the new HEX value

Simple sounding, not so much in real life. After much searching and trial and error, here are the source files you’ll need to accomplish this.

1) Get the SID listed in Sharepoint

Connect to your SQL Server and run the following query on the AdminContent database for your SharePoint install:

SELECT Sites.Id, Webs.FullUrl FROM Sites inner join Webs on Sites.RootWebId = Webs.Id

This will give you a list of the sites and associates URL’s that SharePoint knows about. Copy the Site ID to your clipboard.

You’ll now run this script that will get you the other information you need – including the user’s SID which is in the tp_SystemID field. Paste your Site ID from above into the XXXX space below.

SELECT * FROM UserInfo WHERE tp_SiteID='XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'

If you don’t mind filtering through a long list manually, you can just skip the 1st step and run this query -

SELECT * FROM UserInfo

This will get you a list of the usernames for that site.

2) Get your current SID from AD and convert to HEX

This is the part that took a long time to figure out. Fortunately, I came across a Visual Basic Script that will accomplish the task in one fell swoop.

You’ll want to copy the following text into NotePad and then save it as getSID.vbs. Note the portion in yellow below, you’ll need to replace it with the user’s AD context information. If you don’t know what the context is, use the dsquery command listed below the script to retrieve it.

GETSID.VBS

Option Explicit
Dim objUser, arrSid, strSidHex, objTrans, strUserDN, strSidDec
' Constants for the NameTranslate object.
Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_1779 = 1
Const ADS_NAME_TYPE_SID_OR_SID_HISTORY_NAME = 12
' Bind to object. REPLACE WITH YOUR AD INFO 
Set objUser = GetObject("LDAP://cn=Test,ou=Sales,dc=MyDomain,dc=com")
' Retrieve SID and convert to hex string, then to decimal string.
arrSid = objUser.objectSid
strSidHex = OctetToHexStr(arrSid)
Wscript.Echo strSidHex
strSidDec = HexStrToDecStr(strSidHex)
Wscript.Echo strSidDec
' Use the NameTranslate object to convert objectSid to
' Distinguished Name.
Set objTrans = CreateObject("NameTranslate")
' Initialize NameTranslate by locating the Global Catalog.
objTrans.Init ADS_NAME_INITTYPE_GC, ""
' Use the Set method to specify the SID format of the object name.
objTrans.Set ADS_NAME_TYPE_SID_OR_SID_HISTORY_NAME, strSidDec
' Use the Get method to retrieve the Distinguished Name of the user object.
strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)
Wscript.Echo strUserDN
Wscript.Quit
Function OctetToHexStr(arrbytOctet)
' Function to convert OctetString (byte array) to Hex string.
Dim k
OctetToHexStr = ""
For k = 1 To Lenb(arrbytOctet)
OctetToHexStr = OctetToHexStr _
& Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
Next
End Function
Function HexStrToDecStr(strSid)
' Function to convert hex Sid to decimal (SDDL) Sid.
Dim arrbytSid, lngTemp, j
ReDim arrbytSid(Len(strSid)/2 - 1)
For j = 0 To UBound(arrbytSid)
arrbytSid(j) = CInt("&H" & Mid(strSid, 2*j + 1, 2))
Next
HexStrToDecStr = "S-" & arrbytSid(0) & "-" _
& arrbytSid(1) & "-" & arrbytSid(8)
lngTemp = arrbytSid(15)
lngTemp = lngTemp * 256 + arrbytSid(14)
lngTemp = lngTemp * 256 + arrbytSid(13)
lngTemp = lngTemp * 256 + arrbytSid(12)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
lngTemp = arrbytSid(19)
lngTemp = lngTemp * 256 + arrbytSid(18)
lngTemp = lngTemp * 256 + arrbytSid(17)
lngTemp = lngTemp * 256 + arrbytSid(16)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
lngTemp = arrbytSid(23)
lngTemp = lngTemp * 256 + arrbytSid(22)
lngTemp = lngTemp * 256 + arrbytSid(21)
lngTemp = lngTemp * 256 + arrbytSid(20)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
lngTemp = arrbytSid(25)
lngTemp = lngTemp * 256 + arrbytSid(24)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
End Function

This script will give you pop-up messages with the HEX value of the userid you’ve searched for. Unfortunately, you can’t copy and paste. So, VERY CAREFULLY transcribe it into notepad and then copy to your clipboard.

Future upgrade: I’ll try and see if I can revise the script to export to a text file instead.

DSQuery:

This command line will have to be run from a server that is AD aware and has the DSQuery utility. Easiest option is to run it from your AD Controller. In this example, we return any userid that starts with the letters JE. So for me, where my userid is RYoder, I’d use RY*

dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User)(sAMAccountName=je*))" -attr sAMAccountName distinguishedName ObjectSid -Limit 0

 

3) Update the database with the new HEX value

Run the following script against the Content database. NOTE: You’ll need to append the 0x to the front of the HEX string you found above.

Replace the X with the tp_ID of the user you are trying to update.

UPDATE UserInfo SET tp_SystemID = 0x010500000000000000000986BD9EA976E44036C3F5D3F04040000 WHERE tp_ID = 'X'

You should now have access to the content database. Note,  you’ll also want to run this update against any other content databases that are out there – usually one exists for each web application, otherwise you’ll get an Access Denied error when trying to hit the site.

 

That’s it! Enough for now, my brain hurts just typing this!

Troubleshooting SSRS With Dynamics GP

Installing and getting SQL Reporting Services to work with Dynamics GP is a fairly straightforward process, but there can be some tricks involved. The following link provides a good bit of information about configuring and troubleshooting the SQL Reporting Services Deployment Wizard.

SSRS with Dynamics GP FAQ

SQL Maintenance Plans – Rebuild or Reorganize?

Over time, customers may begin to notice a slowdown in the responsiveness of their Dynamics GP or CRM systems. One of the culprits can be SQL indexes that are no longer valid. It’s like telling your SQL Server to drive to California and then handing it a 20 year old road map – it’s going to take a little longer than it should!

This is where maintenance plans come in. In a perfect world, each of our customers would either hire a SQL DBA to constantly monitor the server, tweaking and optimizing or else pay us to do this. Yeah…right. This is where Maintenance Plans come in – ways of automating and scheduling those cleanup tasks so that the system doesn’t deteriorate over time.

Paul Thurrott of SuperSite for Windows posits that the brain is like a stack. Put a new memory in at the top and an old memory has to drop out the bottom. As I get older, I’m starting to believe this more and more! Memorize that esoteric SQL command and out drops that memory of Skippy, my pet turtle. (Did I have a turtle? I seem to remember something like that…)

That’s where this blog post comes in. I need to document those common tasks that I use every time I setup a new maintenance plan and there’s no reason you shouldn’t benefit as well.

Nightly sub plan:

• Full backup of all user databases
• Delete any existing backups older than 1 week (depending on disk space)
• Reorganize Indexes
• Update Statistics

Daily sub plan (every hour):

• Transaction log backup of all user databases
• Delete any existing backups older than 1 week (depending on disk space)

Weekly sub plan:

• DB Integrity Check
• Full backup of all databases (including system)
• Rebuild Indexes
• Update Statistics
• Delete history older than 8 weeks
• Shrink Databases

According to Pinalkumar Dave, indexes should be rebuilt when greater than 40% fragmented. Between 10% and 40%, use reorganize.

Setup Bing Wallpaper Desktop Slideshow

For those running Windows 7, you may have played around with the desktop slideshow. Did you know you can have those images piped in from an RSS feed. I’m thinking Flickr or maybe even those cool images Bing uses on it’s front page.

Mahendra Palsule at makeuseof.com has posted an article on how to do this. If you want to use another source, you could edit the file she provides and throw in your own source!

Find the article here.

Creating Report Subscriptions in Microsoft Dynamics CRM 4

Something that gets asked a lot is “Why can’t the data come to me? Why do I have to go somewhere and get it?”

There are ways to make that happen. CRM MVP Donna Edwards has posted an article on the Dynamics CRM Team Blog about exactly that topic. Find the article here - http://tinyurl.com/n5dsy7

Plus, it utilizes one of my new favorite tools – SQL Reporting Services.

Vista Defrag: Nice Utility - No Feedback

The defrag utility in Windows XP was a useful utility that provided a text report on fragmentation statistics as well as a graphical representation of the hard drive showing fragmentation.

Vista has none of that. For some reason, the developers removed all the reporting and set defrag as a background process. Admittedly, it does a good job of keeping the system relatively cleaned up.

However, sometimes as an admin, I just gotta know what's up with the volume. Command line to the rescue!

Open a command prompt in administrative mode (right click and choose Run as administrator and then type defrag /?

Description:  Locates and consolidates fragmented files on local volumes to improve system performance.

Syntax:  defrag.exe <volume> -a [-v]
defrag.exe <volume> [{-r | -w}] [-f] [-v]
defrag.exe       -c [{-r | -w}] [-f] [-v]

Parameters:

Value                 Description

<volume>              Specifies the drive letter or mount point path of the volume to  be defragmented or analyzed.

-c                        De fragments all volumes on this computer.

-a                        Performs fragmentation analysis only.

-r                        Performs partial defragmentation (default). Attempts to consolidate only fragments smaller than 64MB.

-w                       Performs full defragmentation. Attempts to consolidate all file fragments, regardless of their size.

-f                        Forces defragmentation of the volume when free space is low.

-v                        Specifies verbose mode. The defragmentation and analysis output is more detailed.

-?                        Displays this help information.

Below are defrag.exe examples:

defrag.exe d: (defrag the D: drive)

defrag.exe d:\vol\mountpoint -w -f (defrag the mountpoint while consolidation all file fragments regardless of size and if volume space is low, forces it to defrag)

defrag.exe d: -a -v (run analysis on the D: drive and set verbose mode for detailed output)

defrag.exe -c -v (defrag all volumes on your Computer and set verbose mode for detailed output

Opportunities don't display Pipeline Phase in CRM

I've run into some cases where Dynamics CRM will not correctly display the pipeline phase in the status area of the Opportunity record. Most often, the Opportunity simply display "None" even though the Opportunity may show up correctly on Reports like the Sales Pipeline Report.

There is a quick work-around. It appears that if you add the Pipeline Phase record as a read-only record on the Opportunity form, it will correctly display in the status area.

So, dig into Setting / Customization / Customize Entities / Opportunity / Form and add the record. Make sure the check the box that sets the field as Read-Only.

Update: If you need to develop a workflow that sets the pipeline phase, you'll need to remove it from the Opportunity form or it won't show up under Additional Fields.