Using Data from Access in SharePoint via SQL

This is a reoccurring issue I have; I don’t know C# beyond one program I wrote 3 years ago when my career path took me away from development and toward other areas. But now, working with SharePoint, this has become a handicap – I want to accomplish a task that is more complicated than straight SharePoint Designer can handle. Unfortunately I don’t have time to defer the task while I learn the C# that would solve this task in a more efficient manner. OK, so it is imperative that I get around to learning C#. Until then what can I do? I do know SQL. This leads me to my current workaround.

Task: Create a site for an engineering project that will store drawings, pieces of equipment (known by their tags), and all specs and requisitions related to those tags. The drawings are created with a program called CADworks. CADworks stores information about the drawings (such as the tag numbers) in an Access database. Pull in the information from the Access database into a SharePoint Equipment list that can be used to tie together drawings (stored as PDF’s in a SharePoint library), Specs (information can be in lists or libraries), Requisitions (lists), and Purchase Orders (library.)

I hoped first to use a ShareVis workflow to take the drawing number as it’s uploaded into the library and pull the data from the Access database and manipulate the list then. However, that functionality does not currently exist with ShareVis – the plug-ins that looked like they might be able to do what I wanted are for forms only. (They did however pass on that request to their development department. I have hope that it will be possible in the future.)

Issue: Can’t insert into SharePoint List via SQL Insert. So can I pull data from Access AND SharePoint Libraries to create a cross-linking data view?

1. Create a linked server to access an Access database. See Configuring a Linked Microsoft Access Server on SQL 2005 Server . Since the CADworks database is stored on the shared drive, I’ll need to create a batch file to copy it to the SQL server nightly.
2. Create store procedure to query access database:

   set ANSI_NULLS ON
   

   set QUOTED_IDENTIFIER ON
   

   GO
   

   — =============================================
   

   — Author:        SharePoint Diva
   

   — Create date: 3/10/2009
   

   — Description:    Pull data from Linked Access Database
   

   — to display in a data view on SharePoint
   

   — =============================================
   

   ALTER PROCEDURE [dbo].[csp_GetComponentsMechanical]
   

       — Search by PID if provided, otherwise use wild card
   

       @pid varchar(40) = ‘%’
   

   AS
   

   BEGIN
   

       — SET NOCOUNT ON added to prevent extra result sets from
   

       — interfering with SELECT statements.
   

       SET NOCOUNT ON;
   

    

       — Inside declare for debugging
   

       — DECLARE @pid varchar(40)
   

       — SET @pid = ‘%’
   

    

   — Insert statements for procedure here
   

       SELECT DISTINCT REPLACE(DWG_NAME_,’.DWG’,”) AS [P&ID], REPLACE(TAG_,’-‘,”) AS [Equip. Tag]
   

       FROM AccessDatabase…[AccessTable]
   

       WHERE DWG_NAME_ like @pid
   

   END
   

    

3. Create Database Connection in SharePoint Designer:

   
   

    

   
   

    

4. Create a data view in SharePoint Designer:

   

    

5. Modify the DataForm field to create a hyperlink to the drawings library based on the value of the field:

   

   

   Address example: http://DevActiveProject/DrawingsDesign/Forms/AllItems.aspx?&FilterField1=P%5Fx0026%5FID&FilterValue1={@P_x0026_ID}

    

   So now I have an equipment page containing data from Access that links to my document library, and because of the query string parameter, I can create a link in my document library that will redirect to a filtered view of my equipment page.

    

   

    

   I’ll cover in a following post the workflow I created for my library to create the hyperlink back to this list.

 

 

From SharePoint List thru Excel to Web Part

How do you take information from your SharePoint list and display it in a pivot table on a SharePoint page? This can be achieved by using Excel’s Data Connections and Excel Services. This article will walk through the steps of going from a SharePoint list to an Excel Pivot Table and then to displaying the pivot table on a SharePoint page.

 

Export the list to a pivot table

 

Change the view of the list to “Edit in Datasheet”.

On the right side of the list is a subtle arrow. This is the hidden task pane. Click the arrow to open the task pane.   Select “Create Excel Pivot Table Report”

Choose the fields to put on the pivot table.   For more information on creating Pivot Tables with Excel 2007 see the Excel 2007 Courses site.

Change the name of your Pivot Table to something meaningful. This is how you’ll identify the table to the web part.

Publish to Excel Services

 

When you’re ready to publish your table, select the windows button > Publish > Excel Services.

Select the SharePoint document library you’d like to publish to.   If you need to add a network place, use the “new folder” button to launch the wizard.

Open “Excel Services Options…”

Select “Items in the Workbook” from the Show selection dropdown.

Select the pivot table created and click “OK”.   After naming the file, click “Save”. This will publish the workbook to SharePoint and Excel Services.

Excel Services will open with a preview of the report created.   Close the tab when you’ve finished reviewing the output.

 

 

Create the Web Part Page

 

Back on the SharePoint site, create a new page.

Enter in a Title, the URL Name (without spaces, using CamelCase), and “Blank Web Part Page” for the layout.   Create.

Add a web part

Add an Excel Web Access web part.

In the web part, you’ll see “Click here to open the tool pane.”   Inside the tool pane, the ellipsis next to the Workbook will open a browse menu to libraries/lists in the site. Locate the document library the workbook was published to.   You may need to use the paging option at the top to retrieve the next set of options.

Select the workbook that was published earlier.   Alternatively, if you have the URL, you can just enter that into the Workbook field.

In the Named Item, enter the name of the pivot table (exactly as it was named earlier in the workbook.)   Click “Apply”.   Click “Save and Stop Editing” to view the page normally.

The page will be saved into the “Pages” document library, a library that is common to all SharePoint sites.   Copy the URL to add to a link list.

This link can then be added to another page more accessible to others.

Refreshing the list

 

When the originating SharePoint list has changed, for example new items have been added to the list, the excel pivot table will need to be opened and refreshed. (Instructions on having the data refresh automatically will be added in another post.)

 

Open the document library housing the spreadsheet, and “Edit in Microsoft Office Excel.”

On the “Data” tab, select “Connection” and “Refresh”, then “Close”.   You should see the new totals reflected on the pivot table in the workbook.   Click “Save”. Since the workbook was opened from SharePoint it will save back to SharePoint. You do not need to republish it.   Close the workbook and refresh the page with the web part to view new data.

GroupBoard Workspace: Bad

I’ve encountered two problems since installing the Microsoft GroupBoard Workspace. One was causing the week-view of the calendar to give an error. The second was anytime I tried to add a content database, I’d get the error:

The specified SPContentDatabase Name=SharePoint_Content Parent=SPDatabaseServiceInstance has been upgraded to a newer version of SharePoint. Please upgrade this SharePoint application server before attempting to access this object.

There was nothing in SharePoint to upgrade, we’ve already installed the most up-to-date service packs. I found that the problem was the GroupBoard Workspace. Once I un-installed it, as suggested by GPK2005 on the Microsoft TechNet forum, I was able to add my content database without issues.

Thank you GPK2005.

Attaching this database requires upgrade…

I’m trying to add a content database to an existing Web Application.  I go into Central Administration > Application Management > Content Databases and Add a content database.  I then get the error: 

Attaching this database requires upgrade, which could time out the browser session.  You must use the STSADM command ‘addcontentdb’ to attach this database.   

The content database gets created in the separate SQL Server (2005).  Running the STSADM command gives the following error in the error log: 

The specified SPContentDatabase Name=blah_Content Parent=SPDatabaseServiceInstance has been upgraded to a newer version of SharePoint. Please upgrade this SharePoint application server before attempting to access this object. 

Nothing requires an upgrade, by following the steps in this article, I was able to create the content database and attach it to the web application. 

Reproduced Content from Article (just in case link disappears): 

Step a: Use SQL Query Analyzer to create the database

Use SQL Query Analyzer to create a new content database for use with the virtual server in Windows SharePoint Services. You cannot use the Create Database Wizard in the SQL Server Enterprise Manager tool to create the new database. Instead, run the following two queries in SQL Query Analyzer. When you use the following queries, make sure that you do the following:

·         Replace each instance of WSS_Content in the queries with the name that you want to use for the database.

·         Replace each instance of AdminVsAppPoolIdentity in the query with the account that is the SharePoint Central Administration virtual server application pool identity. This account is also the database owner (dbo) of the database.

·         Replace each instance of ContentVsAppPoolIdentity in the query with the account that is the content virtual server application pool identity.

·         Replace each instance of Domain in the query with the domain name.

Use the following query to create the database: 

— Windows SharePoint Services Content Database Creation Script

— TODO: Change the database name from WSS_Content to the name of the database that you want to use
CREATE DATABASE [WSS_Content] COLLATE Latin1_General_CI_AS_KS_WS
exec sp_dboption [WSS_Content], autoclose, false
GO

 

Use the following query to configure permissions for the database that you created: 

— Windows SharePoint Services Content Database Creation Script
use WSS_Content

— TODO: Change AdminVsAppPoolIdentity to the SharePoint Central Administration virtual server application pool account and change ContentVsAppPoolIdentity to the content virtual server application pool account.

DECLARE @AdminVsAppPoolIdentity nvarchar(128 )
DECLARE @ContentVsAppPoolIdentity nvarchar(128 )
SET @AdminVsAppPoolIdentity = N’Domain\AdminVsAppPoolIdentity’
SET @ContentVsAppPoolIdentity = N’Domain\ContentVsAppPoolIdentity’

EXEC sp_grantlogin @ContentVsAppPoolIdentity;
EXEC sp_grantlogin @AdminVsAppPoolIdentity;

— The following line will fail with error 15007 if @AdminVsAppPoolIdentity is the Network Service account.  This is expected.
EXEC sp_changedbowner @AdminVsAppPoolIdentity;

— The following line will fail with error 15063 if the account is not the Network Service account.  This is expected.
IF NOT EXISTS (SELECT * FROM sysusers WHERE name=@ContentVsAppPoolIdentity) EXEC sp_grantdbaccess @ContentVsAppPoolIdentity;

— The following line will fail with error 15410 if the account is not the Network Service account.  This is expected.
EXEC sp_addrolemember ‘db_owner’, @ContentVsAppPoolIdentity;

Step b: Add the content database to the virtual server

Use Windows SharePoint Services Central Administration to add the content database to the virtual server. To do this, follow these steps.

1. Click Start, point to All Programs, point to Administrative Tools, and then click SharePoint 3.0 Central Administration.
2. Click Application Management, and then click Content databases under SharePoint Web Application Management.
3. On the Manage Content Databases page, click Add a content database.
4. In the Web Application area, click the name of the Web application to which you want to add the content database.
5. In the Database Name and Authentication area, specify the name of the database, the name of the database server, and the authentication method.
6. In the Database Capacity Settings area, type the number that you want in the Number of sites before a warning event is generated box and in the Maximum number of sites that can be created in this database box, and then click OK.

Note If you configured permissions for the database with the NT Authority\Network Service account, you have to add the Network Service account to the System Administrators role in SQL Server before you can follow this step. You cannot make the Network Service account the database owner (dbo) of the database. You have to add the Network Service account to the System Administrators role before you can add the database to the virtual server. After you add the database to the virtual server, remove the Network Service account from the System Administrators role in SQL Server.

 

 

 

 

 

 

Office SharePoint Server Search Service stuck

How did I get here? Sometime this morning I was trying to fix an issue with the people search feature not working. While trying to correct that, I got stuck in the limbo of Office SharePoint Server Search remaining in the status of “Stopping” without ever getting to the status of “Stopped”. Apparently this is a common issue, but the recommended fix for it, running “stsadm -o osearch -action stop”, isn’t working for me. 

Changed the “Log On As” to Local System, disabled the service and rebooted. Once back up, re-enabled the service, started it, stopped it. Still showed as Stopping in the Central Administration. Re-ran the command “stsadm -o osearch -action stop”. YAY!

Tried to start: An unhandled exception occurred in the user interface.Exception Information: OSearch (DOMAIN\ShareAdmin)

Change the “Log On As” back to the (DOMAIN\ShareAdmin) and start the service from Central Administration > Operations > Services on Server. Everything was back up. 

Below is a link to one of the options I tried. While it didn’t fixe this particular issue, I’m sure this will come in handy in the future.  

SharePoint Services Timer Service File System Cache

Resetting the file system cache have resolved many issues I had in my SharePoint farm. Microsoft tech support gave me this resolution to fix some issues I had with Usage Analysis. I perform this operation first if I get stuck with any issues related to timer jobs. Good times!

Site Directory is Petarded

I’m using trying to use the Site Directory Template. Out of the box, it creates “categories” for Division and Region. Of course I want to use my own categories and I remove those for my own. Being of a database driven mentality, I of course make my columns lookup columns to be driven from list values that I’ll be using over and over throughout my site; Department and Location. So why can’t I get these new columns to show up in the “Categories” tab?

Because only “choice” columns are made into Categories. And those choices are “hard coded” inside the column definition.

Grrrrr.

Trackback

I’ll be spending a great deal of my Monday learning how to customize the themes and master pages on SharePoint.

CSS Hacks for SharePoint Design

Lately, I am doing lot of design work for a client redesigning their MOSS Intranet solution. I found my self fiddling around with Master Pages, Page Layouts and CSS files more often. During this journey I came across lot of the handy resources, which helped me along the way!

The coolest thing I’ve done today thanks to this blog:

Media Player Web Part

This webpart allows you to play avi, mpg and wmv files with in your MOSS site.

But then in the comments, Daniel A (unfortunately with no link for me to trackback to) wrote:

Rather than going through this heart ache, just add a content editor web part and use the below HTML

<OBJECT id=”VIDEO” CLASSID=”CLSID:6BF52A52-394A-11d3-B153-00C04F79FAA6″ type=”application/x-oleobject”>

       <PARAM NAME=”URL” VALUE=”/Docs/[Your Document Library]/[folder]/[your file.wmv]”>

       <PARAM NAME=”animationatStart” VALUE=”true”>

       <PARAM NAME=”transparentatStart” VALUE=”true”>

       <PARAM NAME=”SendPlayStateChangeEvents” VALUE=”True”>

       <PARAM NAME=”AutoStart” VALUE=”false”>

       <PARAM name=”uiMode” value=”full”>

</OBJECT>

Which I did as well, and it was sweet! I love it when things work exactly as described. Especially on a rainy brain-deadened Friday.

Jumping the Gun

“Divas” are rarely as talented or knowledgeable as they’d like to be. Call it a statement of intent. An “if you build it they will come…” idea. Or call me a complete poser. Whatever… I’m attempting to share the knowledge of my success and failures. Like every good diva-poser-scientist-wannabe, I imagine I’ll be encountering far more failures than successes.

So far, trying to use SQL Server 2008 with Microsoft Office SharePoint Server 2007 on a Windows Server 2008 isn’t working for me. Actually I’d not even reached the MOSS installation part yet. I was just trying to do the step before installing the Windows SharePoint Services 3.0 x64 with Service Pack 1. The part where I go into SQL Server Surface Area Configuration and allow Remote Connections.

And I find there is no such animal in 2008. So if anyone knows how to allow remote connections on SQL Server 2008, please let me know. My google-fu has yet to find any answers. For now, we’re going back to 2005 in this installation.

To continue, provide valid credentials for the SQL Server Agent Service

This was driving me crazy. We’re performing a fresh installation on a fresh server; Microsoft Office SharePoint Server 2007 on a Windows Server 2008. I’ve reached the part of the installation where I’m installing SQL Server 2008. No matter what I put in for the UserName, I’d get the error “Invalid or missing username or password. To continue, provide valid credentials for the SQL Server Agent service.”

Looking at the forums, it wasn’t just me. Several people provided answers or reporting not having any issue, but it was obvious, from the continuous pleas for help, that the responses weren’t providing the help asked for. Maybe they’re like me; I need pictures. So here’s my error:

Peter Saddow from MSFT listed the answer, but it took me a few tries to understand it.

 invalid or missing username or password. To continue, provide valid credentials for the SQL Server Agent Service – MSDN Forums

I suppose won’t know until later if this was completely the correct action to take, but it did get me beyond this screen, which after umpteen tries is all I really wanted at this point: