Use a domain account on MSSQLSERVER

We had some trouble running the full backup (both from CA and stsadm), we got error messages in CA that said “Error: Object SharePoint_Config failed in event OnBackup. For more information, see the error log located in the backup directory. SqlException: Cannot open backup device…” and in EventViewer it complained that we used a Bad username or password. Started to investigate that the app pools ran with the right userid = ok, services on the sharepoint server = ok, user account that initiated the backup = ok, network share had correct permissions and that it actually was a share = ok, the app pool account/service accounts/backup account had right permissions on the SQL server = ok, and then I thought everything was checked. But not. Turns out that the MSSQLSERVER service on the SQL server was running on a local account and that account tries to access the file share where you want your backup files and that share is of course not on the same machine so – access denied for that local account! It’s all so clear when you find the error, isn’t it? 🙂 So, we changed the MSSQLSERVER to a domain account which has all rights to the network share and the databases, and now our backup is up and running!

CQWP that displays date

I got a request from a customer that wanted the Content Query Web Part to display Created (or Modified, whatevah) date along with the title. Why isn’t there a function for this already, and why why can’t you select any column you want to display in the web part? I’m not so impressed by this web part as you can tell 🙂 Well nevermind, moving on! I googled and found this nice description:

http://www.microsoft.com/belux/msdn/nl/community/columns/stevenvandecraen/contentquerywebpart.mspx
But instead of editing the itemstyle.xsl in Sharepoint Designer I copied the file and renamed it to itemstyle_lise.xsl and did all the customization in that file. Just to be sure that those customizations wont be lost in case of an upgrade or some other catastrophic scenario were all your work is lost! I am sure my Lise-file wont be overwritten 🙂

I added the CQWP to a page, exported it, opened it in Notepad and added some extra lines in it:

The above line points to my customized xsl stylesheet

The above line declares which default item style that should be displayed in the web part and I named my xsl template “Datum” inside the xsl file.

Saved the file as something else like “Datum.webpart” and imported it to the Sharepoint page, and now the webpart displayed my list with Date and Title.

Now you have your own customized web part and it wont effect the “default” web part:

Now to the deployment, and it’s here where my coding skills sucks – or are non existent to be honest – and therefore I always look for the quick & dirty solution at first and then maybe… I’ll ask my MVP collegue (yeah, he sits right next to me at the office – pure luxury) but that’s not the first thing I’ll do since I want to learn by doing. Trial and error, lovely. So my first thought is to upload it to the Web Part gallery under Site Actions, and put it in a group called “Lises web parts” or something.

The next, and real, step is to deploy it as a feature (I’ve been told by the dev pro’s here) and I will give it a shot!

This is so so sad…

Read the chocking news that Patrick Tisseghem has passed away! I have met him a couple of times, and was really impressed by this guy. Extremely competent and funny with his belgian beer lists always ready 🙂 He made coding look so easy! We in the sharepoint world have lost a great source of competence….

New updates may solve the BDC issue?

The new infrastructure update to MOSS fixes the issue I got in Event Viewer, regarding the BDC. It said that anonymous users are not allowed to connect. It seems like this new update fixes that:

“When users are authenticated anonymously, they cannot use a Business Data Catalog (BDC). This update enables the BDC to explicitly let anonymously authenticated users access the BDC”

Read more here, I will install this update ASAP.

It’s a dirty job but someone’s gotta do it….

Got this error in the Backup history:

Object SSO failed in event OnBackup. For more information, see the error log located in the backup directory. SqlException: Could not locate entry in sysdatabases for database ‘SSO’. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally.

Found out that in the Backup list there was a database called SSO that was listed for backup. But, it does not exist on the SQL server:

So, how do you edit the list of databases? I gave up searching cause I didn’t find any good answers. I wanted to be able to edit the xml file that builds the list of databases to be backed up, (like the spsbackup.xml) but that is created when you run the backup…

I did a really quick and dirty solution to this: I ended up creating an empty database called SSO on my SQL server and now, the backup runs without any errors! Sometimes you just have to be a little dirty to get things done 🙂

BDC part 2

The struggle with the BDC (Business data catalog) continues and so far I am able to return a search result inside the Editor, but still not in Sharepoint. I think it has to do with the authentication. I use PassThrough, since WindowsCredentials needs SSO (which I have not enabled on the server). Here are some printscreens of what I have done this far. I have installed the Business Data Catalog Editor that comes with the SDK (read here). After the connection string is set, I am able to select the table that I want to get data from:

Uncheck the fields that you want to exclude from the query and press OK. Press OK again to confirm the connection. Now your entity is created:

Make sure that the “PassThrough” authentication is selected. Test the bdc file by opening the “Parameter” and “Instance” and select the instance. Rightclick and select “Execute”. Enter a value, for example a customer number and make sure that a value is returned:

Export the xml file. And then, import the xml file to Sharepoint (application definition file):

The import:

You will get an error message about the profile page, but nevermind. The message is “Could not create profile page for Entity NaB Solutions AB$Customer. The error is: Cannot create a new connection to closed Web Part g_c01d1f2b_0382_45a2_ad6e_07ce86936b3a”. But, this is obviously not an error to care about. It is just a warning! Press OK to continue.

Now, go to your website and add the webpart for Business Data. Lets start with a list, enter a new column and select “Business data” as type. Enter the instance and select the fields you wish to display:

In my list, if I try to enter a character, it says that no exact match was found. If I click on the “more items” then it is empty as well:

And the “picker” where you can browse, is not selectable and there is no data in the column:

So I skip the list and select a webpart instead. The first web part is the Business Data Items. And that gives me the following error (Business data catalog object not found). I have tried them all Business Data List, Business Data Item etc but noone gives me any data:

None of the webparts displays any data. So I figured that something was changed in the xml file while I imported it. So I exported the file from Sharepoint and imported it back into the Editor. Tried the Execute, it works.

This error turns up in Event Viewer but I don’t know how to correct it – yet:

A new fresh attempt with the BDC

I have tried this before, but with no result, to actually get data from Dynamics NAV straight into Sharepoint using the BDC catalog. It seemed so easy, just create the ADF file and import it in Central Administration and youre ready to read from your business system. Not quite.. So today I did a new attempt using the BDC application from Microsoft (downloaded it here). I have tried the BDC Metaman before, but today I used this app which comes with the SDK:

Quite easy to install and setup and even enter the connection string:

“Server=myServerNametheInstanceName;Database=myDataBase;Trusted_Connection=True;”

I added the table I wanted data from and exported the xml file and imported it to Sharepoint. I got an error about the profile page:

Could not create profile page for Entity NaB Solutions AB$Contact. The error is: Cannot create a new connection to closed Web Part “g_f9c81dbd_bdf8_4935_a3f3_6558836696df”.

Went into my Sharepoint site and created a column that is of type Business Data and it was empty. Access? No, I’m the admin. I probably need to configure some more, and look into the error I got.

To be continued…

Display updated items

The Content Query Web Part is really nice but leaves some wanted functions like:

* Display updated items for a specific time period
* Be able to display items from several lists and libraries (why not all?)

* It would be nice if you could filter your latest updates so that it does not show an item from the year 2007 just because that is the latest updated item. You should be able to set a filter that tells the webpart to display only items that are [Modified]+7 or something similar. As it is now, you have to create such a field in your list and display that certain field (I did this, look further down in this post).

* I would like to pick any field from my lists and display them and sort them in the webpart. Only fields that are created as Site Column are in the list now.

* You may group your latest updates and that is nice, I usually group them after Site. BUT, if you only want to show the latest 10-15 updated items – and you have like 5 updated items on one site – then it only shows 15 rows and that mean that I can’t see the other sites updates because of the row limit. It would have been nice to display the ONE latest update from all the sites.

One solution I have used to display only the latest weeks updates is to do this:

Create a new column. Important: you must create it as a Site Column! Or else you will not find it in the Content Query Web Part!
Enter a name that tells the user that this is a “system” field with a name like “ShowUntil” and select “Date and Time” as the type of column.

Date and Time Format is set to “Date Only”.

Then set the default value to a calculated value. Add “=Today + 7”.

Clear the Add to default view check box.

Save the new column.

Add your new column to the document library or list you want to display updates from.

Now in your Content Query Web Part, select the column you created and set the filter to display it if it’s “Greater than or Equal to” [Today] to your column:

Now when the list is updated on a site, it will be displayed in your web part for seven days from the created date.