Archive for August, 2009

26
Aug
09

Indexed Columns in SharePoint 2007

Here is a great in-depth article on what happens when you define an index on a SharePoint column.  You get to the Indexed Columns functionality by going into the settings of a list or library and looking at the links at the bottom of your column list.

Indexed Columns

It’s very simple to check a box next to a column you want to index, but be aware, you are NOT creating a true SQL Server index.  What you are doing is putting the values of the column in a name/value pair table and SharePoint is using these entries in a join to the UserData table in an attempt to increase performance when you have thousands or millions of items in a list or library.

My experience has shown that, in our situation where we have libraries with up to 40,000 items and custom SharePoint applications querying those libraries, we are not seeing any significant performance increase.  Your mileage may vary.

26
Aug
09

SharePoint View Not Returning Items Defined in Filter

Our team was testing a custom SharePoint app that had some recent modifications made to it and we were finding that it wasn’t returning results that we were expecting – and had in the past.  The programmer couldn’t find any unexpected changes in the CAML queries that were being used in the code.  We were all struggling trying to figure out what was going on.

I was doing some troubleshooting by creating views in SharePoint on the offending libraries to filter the items we were looking for and I found that some of these views weren’t functioning correctly.  One of the things I noticed when creating some views of the libraries that were giving us the problems was there were some SharePoint “indexes” defined on a couple of the fields.

For those not familiar with SharePoint indexes, they are not true relational indexes that you would create in a SQL database.  Instead, they involve some clever joins to supposedly increase the performance of list views. 

I found that, at some point in time, we had created some of these indexes.  When creating the view in SharePoint, you could see the fields that had indexes defined because they had the word (Indexed) next to them.  Trying to filter on these fields was giving us unexpected results.  In fact, in one of my test cases, the filter wasn’t working at all and no results were being returned.

To solve this problem all I did was turn off the indexing on the fields in SharePoint (under library settings).  After that, the filters in the views started working.  Plus, in our application, we began getting the results we expected.  Problem solved!

26
Aug
09

The web application at [URL] could not be found. Verify that you have typed the url correctly. If the url should be serving existing content, the system administrator may need to add a new request url mapping to the intended application.

I was recently working on a custom windows application whose job was to bulk load content into SharePoint document libraries.  I was testing it on the test server and then moved it over to the production server for the client to use.  When they logged in with their admin account (account #1) and fired up the app, it wouldn’t do its job.  Looking at the log file showed the error:

The web application at [URL] could not be found. Verify that you have typed the url correctly. If the url should be serving existing content, the system administrator may need to add a new request url mapping to the intended application.

This error is basically a really verbose SharePoint “file not found” type of error.  It’s saying that it can’t find the URL and suggesting that your SharePoint administrator might need to add an alternate access mapping to your SharePoint web application (extend the web app and add an alternate access mapping).  It was occurring when instantiating and SPWeb object.

The funny thing about this error was that, if I logged on as an administrator, it went away and the app worked fine.  That immediately suggested to me I had a permissions problem.  So I looked all around for where the differences might be.  The account was actually in the domain administrators group and had permissions that worked fine in SharePoint.  It was a farm administrator and a site collection administrator.  There was nothing it couldn’t access…except the SPWeb object. 

We had a couple of other accounts that were similarly configured, so I tried one of the other accounts after spending hours “googling” (or was it “bing-ing?) a potential solution.  While searching on the web, I found I had plenty of company with others who had similar problems.  Many of proposed solutions revolved around swapping out accounts in specific application pools being utilized by SharePoint.  However, most of these solutions do not take into account infrastructures that won’t allow these accounts to be changed to local system accounts.  Whatever…

Okay, so I tried one of our other similarly configured accounts and found that it worked just fine.  “What was the difference”, I pondered?  I searched all over and found that account #2 was NOT a site collection administrator.  In other words, the account that wasn’t working, account #1, WAS a site collection administrator and the account that WAS working was not.  So, I removed it from the site collection administrators.

Guess what?  My application suddenly started working when logged on as account #1.  So, I put it back in to the site collection administrators group and found that it STILL WORKED!  Just taking it out of the site collection administrators did something, somewhere in the vast security netherworld of SharePoint that flipped the appropriate “bit” and fixed whatever the original problem was.  Problem solved.  Only took eight hours.  😦

04
Aug
09

Creating a Selection Drop-down to a SQL Data Source in InfoPath

Here’s a method you can use to allow a SQL table in an InfoPath form to be the source of the data in a drop-down list.  This example uses the Adventure Works database.

The result of this example is a drop-down list of employees which, when one is selected, populates the First Name and Last Name fields on the form.

Thanks to Fidel for getting me to post this!

Create the Test Form

Create a new form template in InfoPath.  On the blank template, place three controls:  a drop-down list and two text boxes.  Set the name of the drop-down list to EmpId and the names of the text boxes to LastName and FirstName.

image

Create a Data Connection

Select Tools | Data Connections… from the InfoPath menu.  Click the Add button and add a new data connection to Receive data.

image

Select Database (Microsoft SQL Server or Microsoft Office Access only).

image

On the next dialog, click Select Database.

image

From here we will create a new data source.  Click the New Source button.

image

Select Microsoft SQL Server.

image

Enter your SQL Server name and the authentication type you want to use.  You’ll have to give this some thought, as you need to insure that the person filling out the form has the necessary security to access your database.

image

Select the database and table you want to use.  In this example, we are using the AdventureWorksDW database and the DimEmployee table.

image

Provide a name for the .odc file, a description and friendly name.

image

You should now have a connection to the DimEmployee table.  For this example, click on the Edit SQL button.

image

We’re going to create a concatenated field we can use in our drop-down list that contains the LastName and FirstName fields in the form LastName, FirstName.  After the last field in the select statement add:  LastName + ‘, ‘ + FirstName as ListBoxName and click Test SQL Statement to make sure you don’t have any errors.

image

image

Click OK to dismiss the Edit SQL dialog and you’ll receive the message “The SQL statement you entered cannot be represented as a tree view.  Do you want to continue?”  Click Yes, as this is simply informing you that you’ve created a SQL statement that won’t be displayed in the in the Data Connection Wizard dialog.

image

image

Continue to click Next and close the dialogs.  Be brave and and click Yes to all the warnings!

image

Provide a name for the data source.  How about DimEmployee SQL Data Source?

image

Click Finish and you should now have a new SQL data connection.  You can close the Data Connections dialog.

image

Bind the Fields on the Form to the Data Source

Double-click on the EmpId drop-down to display its properties and select Look up values from an external data source.  Make sure that your DimEmployee data source is selected if it isn’t automatically selected for you.

image

Click the Entries button and select the d:DimEmployee node and click OK.

image

For the Value, select the EmployeeKey field and click OK.

image

For the Display name, select the ListBoxName field and click OK.

image

Click OK on the Drop-Down List Box Properties dialog.

Test the First Part of the Form

You can now test the first part of the form by Previewing the form.  You should see the drop-down list is populated with the last and first names of all the employees.

image

Close your preview and move on to the next part of the form design.

Set the Values of FirstName and LastName Fields

Double-click on the FirstName field to display its properties.  Click the data binding button image on the Default Value field.  Click Insert Field or Group.

image

In the Select a Field or Group dialog, change the data source to be your SQL connection and then navigate to the FirstName field.  Don’t click OK yet!

image

Click on the Filter Data button and Add a new filter.  Select EmployeeKey as the first field and “is equal to” as the comparison.  For the comparison field click “Select a field or group” and change the data source to the Main data source.  Select EmpId as the field to compare against.  Click OK a bunch of times.  Here are the myriad of dialogs you’ll see.

image

image

image

image

Click OK to the final Text Box Properties dialog.

image

Perform the same binding routine for the LastName field.  Again, you are specifying a default value that comes from the SQL connection and is filtered by the EmployeeKey.

image

Test the Final Solution

Now you can do a preview of the form and check out how it works.  You should get the list of employees in the drop-down list and, when you select one, it should populate the FirstName and LastName fields.

image

You can, of course, make these fields read only so the end user can’t change them and then use them in whatever rules and other business logic you create for your form.




Asif Rehmani’s SharePoint Videos

SharePoint-Videos

Click to access a wealth of SharePoint videos

SharePoint Rx

SharePoint Rx Home

Categories

Posts by Date

August 2009
M T W T F S S
    Sep »
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Support Wikipedia