Friday, March 27, 2009

Hoping this will help.


Taking a break on sharepoint for the rest of the week. Waiting for the Sharepoint Best Practices book to arrive. Im having problems determining the best information architecture for our site overhaul and more importantly, how to structure permissions for the organization. Hoping this will help.

Tuesday, March 24, 2009

SQL connection string made easy

I came across this a few times while fighting with attempts to access the ACT! database. I wanted to try a few different connection strings in Sharepoint Designer. Found an easy way to make one:

1. On your desktop, right-click and choose New > Text Document.
2. Give it a name, and save it with an extension of .UDL. It will now have a fancy icon.
3. Right-click properties the doc you just created.
4. Enter the type of connection. I chose OLE DB for SQL. Enter server name (with \instancename, if required). Enter username/pw info, etc. Click Apply. Click Ok.
5. Open notepad.
6. Drag the UDL icon document you created onto notepad. It will expose the full connection string.

Here is what mine looked like:

[oledb]
Provider=SQLOLEDB.1;Password=ChangeMe;Persist Security Info=True;User ID=ActReader;Initial Catalog=ACT2009TestDB;Data Source=mydatabasesevername\ACT7

Fighting with ACT! integration with Sharepoint (part deux)

I decided to try option B:
Use the ACTReader component to make a connection to the ACT! database.

ActReader.exe is a utility that logs into the database and creates a new user in the database. We can use that user to build ODBC connections. The utility is recommended for those who want to build custom Crystal reports. If you are using ACT! then I recommend you open Help while in ACT! and search for 'reader'. The info you need for using ActReader.exe is there.

To test the utility, I backed up my production database, and copied it and renamed it on my local machine. I then upgraded my local ACT! client to ACT! 2009 Premium. (ActReader is free with ACT!2009, not with ACT!2008. So, I had to upgrade or pay the $500.) After install, I told it to run all updates, and to update the database as well. That went fine.

I then tried to find the ACTReader.exe file. It did not exist. I had to shutdown and restart my machine, then re-launch ACT! 2009, and then it indicated there were (even) (more) (newer) updates. I had to again go through backing up the database, downloading the update, running the update, and then updating the database again. Now, ACT! on my machine is at version: 11.1.183.0. (Apparently the actreader.exe does not come with 11.0 out of the box. You must be at version 11.1.x.) Once I was at the latest build, ActReader.exe was in my Program Files\ACT folder.

I launched the ActReader and set a password for the ActReader user. I then built an ODBC connection on my local machine pointing to the database I renamed as Test. I had to specify the ACT instance in the server field of my ODBC connection. Because the test database is on my local machine, I used localhost\ACT7. I then used my ActReader credentials, and voila... I can FINALLY get a successful SQL test connection.

From there, I opened SQL 2005 Management Studio and logged in with same user credentials, again remembering to specify the ACT instance with "\ACT7". Successful.

To make sure all was truly well, I also created a Database Connection in Sharepoint Designer. I used both the wizard, and also Custom connections. Both were successful. I created a rough data view webpart, and... successful.

Finally, I can poke around the ACT! database til my heart's content. Now if I could only script better in SQL...

Monday, March 23, 2009

Fighting with ACT! integration with Sharepoint

There are three ways to extract data form an ACT! database into Sharepoint.
a) Create an OLE DB file (.odc.)
b) Use the ACTReader utility.
c) Use the SA admin utility and break the SQL password, for direct SQL access.

My business is using ACT! 2008 Premium.

My first attempt was to use an OLE DB file. This option is free. The ACTReader comes free in the ACT!2009 Premium version, but we are on 2008. The SA admin utility is $500.

So... I first tried the OLE DB file option.
Here are the instructions from Sage themselves:
http://kb.sagesoftwareonline.com/cgi-bin/sagesoftwareonline.cfg/php/enduser/std_adp.php?p_faqid=14023

This did not work for me. I had my ACT!Server running Excel 2003. Not until I applied every single Microsoft update I could find, could I get that to work. Until then I was getting "Cannot CoCreateInstance" dll errors.

However, Sharepoint designer only likes Office 2007. From my workstation running Office 2007, I again could not build the OLE DB file. To do so, I had to install the full ACT! 2008 client on my machine. I wanted to avoid this, as I do not need ACT! on my machine, but... just copying the ACTOLEDB.dll over to my machine and manually registering it was not enough to make it function. After installing the full ACT! client on my machine running Office 2007, I could then build the ODC file. That worked great. I can view my ACT! data table in Excel.

Now that I knew I could get the data into Excel 2007, I wanted to see if Sharepoint Designer would be able to do the same. I copied the Connection String created in Excel (Data > Connections > Properties > Definitions). It looks something like this:
Provider=ACTOLEDB.1;Data Source=\\MyActServer\actdb\ProductionACTDb.pad;User ID=User Name;Persist Security Info=True

No matter how I tried, this attempt failed. I toyed with NTFS share permissions and the like, and still had no success. I could not get the table to open in Sharepoint designer via OLE DB at all.

So... Consider the OLE DB attempt a failure.

See next post.

Friday, March 20, 2009

SQL queries and sharepoint designer data view web parts

As an exercise, I wanted to create a data view web part that showed the number of certifications earned per employee in the year 2008. I used a data connection to our sql database and found that the best place to start such a thing, was to get a SQL query put together that performed what I needed.

To achieve what I wanted, I had to create two custom views in my existing SQL database. Below is the example of my code and what it does:

CREATE view [Techcert_view] AS
Select Tech.Name,Techskil.Type_code, Techskil.Auth_date from Tech, Techskil
where
Tech.Technician = Techskil.Technician
and Auth_date >= '2008-01-01'


The block above creates a new custom view called Techcert_view.It selects 3 columns from two separate tables (Tech, Techskil) in the main database.The Where clause was necessary because one table showed the technician's name, and the technician's ID, whereas the other table only contained the technician ID. I wanted my output to contain the full technician's name, so I had to correlate the two values. The final statement indicates I want values only since 2008.The output looked like this:

Name Type_code Auth_date
-------------------------------- --------- ---------------------------
SMITH, JOHN VCP 2008-02-18 00:00:00.000
SMITH, JOHN FIRETIDE 2008-05-06 00:00:00.000
FRANK, JANICE 272 2008-05-23 00:00:00.000
FRANK, JANICE DELL-OD 2008-07-20 00:00:00.000

...

This is great, but what I really wanted was a count of certs per technician during this period. Right or wrong (my sql skills are lacking), I created another custom view:

CREATE VIEW [TechcertCount_view] AS
select Name, count(Distinct Type_code) AS Certification_count from [Techcert_view]
group by Name

That now gives an output of:
Name Certification_count
-------------------------------- -------------------
SMITH, JOHN 2
FRANK, JANICE 8
HUBER, MARK 5
BRANDT, SEAN 15

I then made my data view web connection in Sharepoint Designer, and I selected the TechcertCount_View (you can add tables and/or views via SPDesigner), and sorted by the Certification_count, so that it shows who received the most certifications.

Cannot open page in sharepoint designer. SOAP error.

In playing with web parts this week, I managed to destroy a page I was working on. Resetting that page to Site Definition did not do the job.

In sharepoint designer, I was getting the error:
soap:ServerServer was unable to process request. ---> An unexpected error occurred while opening this page. For assistance, contact your site administrator.

To fix, in IE, I appended ?contents=1 to my broken page url.
http://testingsite.copm/sites/Shared%20Documents/statuspage.aspx?contents=1

I was then able to delete the data view web parts I believed were causing the problem.

Now I can open the page again in Sharepoint Designer with no SOAP error.

Tuesday, March 17, 2009

Migration from Sharepoint 2003 to MOSS 2007

I had a hell of a time getting this to work. I tried initially an in-place upgrade. I have a single server farm, with the database hosted on a separate machine running SQL 2005 SP2.

I am running both my sharepoint server and database server in vmware virtual machines. Using snapshots on both the database server and sharepoint server, I tried unsuccessfully many times to try the in-place upgrade.

In the end I had to drop the in-place method (altho it was supposed to have worked) and used the Database Migration method instead.

These instructions are good for that:
http://www.sharepointblogs.com/johnwpowell/archive/2007/07/12/migrating-sharepoint-portal-2003-database-to-sharepoint-2007-using-content-database-migration.aspx

When I was finished, my sharepoint server had two websites in IIS - the old 2003 and the new 2007. I disabled the old 2003 site. I then had to go into moss2007 central admin > operations > alternate access mappings. I put my central admin and my public site in the Default zone.

I then went through every link in the Central Admin Operations and Application Management tabs and configured/set everything. Everything in my environment is using the same sharepointadmin credentials, as I had read of conflicts with database permissions, application pool services, etc. So... to simplify, in my simplified environment, all services run using the same credentials, and that user is also a site collection administrator.


More on this later...

Brute Force Sharepoint

Welcome to my Blog. Im starting this to document my trials and tribulations with MOSS2007. I am a sr. network engineer responsible for everything in my small business, but most recently, Ive been tasked with building out a sharepoint environment that actually provides some business value. Im at a loss. I dont know anything about sharepoint. Its a large evil monster that makes my life more difficult than it should. Hopefully, the quirks I have uncovered and fought through will do others some good.