Now that we have collected mountains of data via my online Form, we need to edit some data fields. (Those who completed the forms cannot spell well, so we want to clean up some plain text fields.) Found that this cannot be done in DataSheet View in Sharepoint. The Data Sheet View is always opened as Read-Only. Found the explanation as to why this is here.
"There are three types of views available. A Standard View shows a list of your forms. A Datasheet View looks like a spreadsheet. With normal lists, you can edit the items directly in the Datasheet View, but data promoted from InfoPath forms cannot be pushed back into the forms by SharePoint, so the fields will be marked read-only. A Calendar View displays a calendar control with the forms displayed at a particular date or range of dates. You can use promoted values (such as the Start Date in the Meeting Agenda) as the date used to display the form." (My emphasis added.)
So... rather than easily edit all incorrect data from a single page, we have to manually open edit and save each Form.
Wednesday, July 15, 2009
Friday, June 26, 2009
SP2 fix now available from Microsoft
When Microsoft released SP2 for MOSS 2007, it contained a bug. Applying SP2 would cause the software to expire after 180 days. There were a couple workarounds, but Microsoft has issued an additional update that can be applied before or after applying SP2 that will resolve this problem. More information here from the Microsoft Sharepoint Team Blog.
Convert single-line text columns to multi-line text columns in Sharepoint Lists
Had an issue with my form that is now in production. Users completing the "Additional Comments" field in my form were writing entire novels. Once the form was submitted, the Sharepoint List would only display truncated text, as the column type in my Sharepoint list had been defaulted to single-text. I had to find a way for all the text to appear in the Sharepoint List (so it could be exported to excel and delivered to the client intact), without risking any loss of data on the 200+ forms that had already been completed.
I found a solution via Experts Exchange. The solution is to go into the Sharepoint List > Settings > Document Library Settings, and choose to Create a new Column. I created a column called "Addl Comments Long" and specified that as a Multi-Line text type. I then went to my original form and told it to re-publish. In the publishing wizard window where one chooses which fields to promote, I highlighted my AddlComments field, and hit Modify. From that window, I choose to map my AddlComments field to my new multi-line text AddlCommentsLong column.
I then went back to my Sharepoint List. Bummer, nothing changed. I had to open and close (not save, or edit or change in any way) the completed forms one by one (yes, tedious), but after doing so, it refreshed the data in the Sharepoint list, and suddenly the entire text that users had entered in the Comments field, was now all intact. No data lost, and the client gets what they expect.
Whew!!
I found a solution via Experts Exchange. The solution is to go into the Sharepoint List > Settings > Document Library Settings, and choose to Create a new Column. I created a column called "Addl Comments Long" and specified that as a Multi-Line text type. I then went to my original form and told it to re-publish. In the publishing wizard window where one chooses which fields to promote, I highlighted my AddlComments field, and hit Modify. From that window, I choose to map my AddlComments field to my new multi-line text AddlCommentsLong column.
I then went back to my Sharepoint List. Bummer, nothing changed. I had to open and close (not save, or edit or change in any way) the completed forms one by one (yes, tedious), but after doing so, it refreshed the data in the Sharepoint list, and suddenly the entire text that users had entered in the Comments field, was now all intact. No data lost, and the client gets what they expect.
Whew!!
Monday, June 22, 2009
Preventing InfoPath forms from timing out in Sharepoint
Had this question posed to me today. Users needed more time to complete infopath forms that were web-enabled on sharepoint. Found this link that explains one can set this timeout value in the Central Admin at: Central Administration > Application Management > Configure Session State
Saturday, June 6, 2009
InfoPath: redirect user to URL on Submit
Was testing my extranet. After a user submitted a Form, the user would be taken to the Sharepoint Document Library showing all completed forms. I didnt want this. OnSubmit I wanted users directed to a URL. There is no such option from within InfoPath.
The solution is to edit the initial link they launch from by editing the &Source portion of that URL. I replaced the &Source portion of the URL on my homepage with the &Source URL I wanted users returned to, and now once the user hits Submit, the user ends up back at the homepage.
The solution is to edit the initial link they launch from by editing the &Source portion of that URL. I replaced the &Source portion of the URL on my homepage with the &Source URL I wanted users returned to, and now once the user hits Submit, the user ends up back at the homepage.
Correcting a mistake: Moving a sharepoint site to a new web port.
I created an Extranet site in a fury the other day to meet a client's need. When I went to Central Admin > Application Management > Create or Extend Web Application, I wanted to create my site as: newhostname.mysharepointsite.com. But, the GUI forced me to append a port number to it: newhostname.mysharepointsite.com:47994. In my hurry, I let that go. I needed to get something up quickly and start posting.
Before making it live to the client, however, I wanted the site on port 80. I found that in the original deployment of my single sharepoint server farm, there was a site in IIS that answered all port 80 requests for mysharepointsite.com. In IIS, when I viewed properties on mysharepointsite.com and reviewed the host headers (Website tab > Advanced button), the host-header field was blank. Altho its 'wrong' I removed that blank entry. I then went into my new client extranet site settings in IIS and edited the port number from 47994 to 80, and made sure the host heading was newhostname.mysharepointsite.com. Again, this is wrong, but... there are more steps.
From there, I logged into the Central Admin and went to Operations > Alternate Access Mappings > Edit Public Zones URLs, selected my Web Application, and removed the port number from my extranet's URL. Then I restarted IIS.
And that worked...
Before making it live to the client, however, I wanted the site on port 80. I found that in the original deployment of my single sharepoint server farm, there was a site in IIS that answered all port 80 requests for mysharepointsite.com. In IIS, when I viewed properties on mysharepointsite.com and reviewed the host headers (Website tab > Advanced button), the host-header field was blank. Altho its 'wrong' I removed that blank entry. I then went into my new client extranet site settings in IIS and edited the port number from 47994 to 80, and made sure the host heading was newhostname.mysharepointsite.com. Again, this is wrong, but... there are more steps.
From there, I logged into the Central Admin and went to Operations > Alternate Access Mappings > Edit Public Zones URLs, selected my Web Application, and removed the port number from my extranet's URL. Then I restarted IIS.
And that worked...
InfoPath filters. Where's my 'Where' clause...
I have a copy of an old database in the form of an Excel dump. Ive uploaded the Excel file as a separate list in sharepoint.
GOAL:
Create an online Asset Inventory form in sharepoint that prepopulates field data from my sharepoint list. IE. When user enters an AssetTag that already exists in my sharepoint list, then prepopulate my form's serial#, model#, etc fields, with the values from the sharepoint list. Prepopulating these fields reduces likelihood of user error when completing machine details in my form.
PROBLEM:
I created a Rule on my myAssetTag field in my infopath form.
It says if myAssetTag = DataSourceAssetTag, then fire more rules:
a) set myModelNum to DataSourceModel,
b) set mySerialNum to DataSourceSerialNum, etc.
(Nested rules, all attached to my myAssetTag field.)
When I ran the form it did prepopulate information in the corresponding fields, but it picked the first row of data from the existing sharepoint list, not the data of the specific AssetTag that was entered by the user.
What I really wanted to say was:
myModelNum field should equal DataSourceModel where DataSrouceAssetTag equals myAssetTag.
SOLUTION:
Create a filter in InfoPath. I find this 'feature' somewhat awkward in InfoPath. One has to pay close attention to each window while completing this.
When creating the rule "myModelNum isequalto DataSourceModel", then selecting the Value of 'DataSourceModel', you have to first highlight the field name, THEN select FILTER. This filter button doesnt exist anywhere else, except, and only after a field is highlighted. Once I had the filter box open, I said: 'DataSourceAssetTag is equalto myAssetTag."
This filter is essentially the 'Where' clause I was trying to find. In infopath, the final rule looked like this:
Set a field's value
Field: myModelNum
Value: @DataSourceModel[@DataSourceAssetTag = myAssetTag]
Meaning: myModelNum = DataSourceModel where DataSourceAssetTag = myAssetTag.
Certainly not difficult, but it actually took me awhile to find the right filter, and to make sure I was in the correct window to even be offered the Filter option. Instead I often kept ending up in a New Rule window, which did not meet my needs.
I guess the lesson here was to tread lightly. Its easy to go through next/next/finish and then realize youve completely missed the key step in the process.
GOAL:
Create an online Asset Inventory form in sharepoint that prepopulates field data from my sharepoint list. IE. When user enters an AssetTag that already exists in my sharepoint list, then prepopulate my form's serial#, model#, etc fields, with the values from the sharepoint list. Prepopulating these fields reduces likelihood of user error when completing machine details in my form.
PROBLEM:
I created a Rule on my myAssetTag field in my infopath form.
It says if myAssetTag = DataSourceAssetTag, then fire more rules:
a) set myModelNum to DataSourceModel,
b) set mySerialNum to DataSourceSerialNum, etc.
(Nested rules, all attached to my myAssetTag field.)
When I ran the form it did prepopulate information in the corresponding fields, but it picked the first row of data from the existing sharepoint list, not the data of the specific AssetTag that was entered by the user.
What I really wanted to say was:
myModelNum field should equal DataSourceModel where DataSrouceAssetTag equals myAssetTag.
SOLUTION:
Create a filter in InfoPath. I find this 'feature' somewhat awkward in InfoPath. One has to pay close attention to each window while completing this.
When creating the rule "myModelNum isequalto DataSourceModel", then selecting the Value of 'DataSourceModel', you have to first highlight the field name, THEN select FILTER. This filter button doesnt exist anywhere else, except, and only after a field is highlighted. Once I had the filter box open, I said: 'DataSourceAssetTag is equalto myAssetTag."
This filter is essentially the 'Where' clause I was trying to find. In infopath, the final rule looked like this:
Set a field's value
Field: myModelNum
Value: @DataSourceModel[@DataSourceAssetTag = myAssetTag]
Meaning: myModelNum = DataSourceModel where DataSourceAssetTag = myAssetTag.
Certainly not difficult, but it actually took me awhile to find the right filter, and to make sure I was in the correct window to even be offered the Filter option. Instead I often kept ending up in a New Rule window, which did not meet my needs.
I guess the lesson here was to tread lightly. Its easy to go through next/next/finish and then realize youve completely missed the key step in the process.
Wednesday, June 3, 2009
InfoPath Forms and Sharepoint and annoying Visual Studio
I was asked to put a form on sharepoint. Great. Have a copy of the doc they all wanted posted. I edited it and published it and all was well. Went back to tweak the document and suddenly InfoPath warns me that only an administrator can approve this template. I could not figure out why.
The error I was getting was:
"An administrator must approve this form template before it can be filled out by using a browser. The template may contain form code, require full trust, be enabled to render on a mobile device, or use a data connection managed by a server administrator."
Turns out I made the stupid mistake of accidentally clicking on Programming > Validating Event..., from within a field in my Form. That action opened Visual Studio for me. I closed Visual Studio and didnt think about it.
Opening Visual Studio was apparently my mistake. I finally found this doc: http://support.microsoft.com/kb/922125
Only the "Administrator-approved form template (advanced)" option is available when you publish a browser-compatible form template by using Visual Studio 2005."
Great. >Accidentally< clicking on something should not render one's document incompatible.
The fix is to go into InfoView and click on Tools > Form Options > Programming, and disable the programming.
ARGH!!!!!!!!!!!!!!
The error I was getting was:
"An administrator must approve this form template before it can be filled out by using a browser. The template may contain form code, require full trust, be enabled to render on a mobile device, or use a data connection managed by a server administrator."
Turns out I made the stupid mistake of accidentally clicking on Programming > Validating Event..., from within a field in my Form. That action opened Visual Studio for me. I closed Visual Studio and didnt think about it.
Opening Visual Studio was apparently my mistake. I finally found this doc: http://support.microsoft.com/kb/922125
Only the "Administrator-approved form template (advanced)" option is available when you publish a browser-compatible form template by using Visual Studio 2005."
Great. >Accidentally< clicking on something should not render one's document incompatible.
The fix is to go into InfoView and click on Tools > Form Options > Programming, and disable the programming.
ARGH!!!!!!!!!!!!!!
Tuesday, April 28, 2009
More error message battles
I left things alone, and, well, that was a mistake. Errors 6398 and 6481 came back. This time with an Error 6482 as well.
To resolve 6482:
(I had same symptoms of no longer being able to launch IIS manager - this began after applying windows updates last week.) I installed hotfix from KB946517 and that cleared up 6482.
Once 6482 was resolved, I was still getting 6398 and 6481 and found that I also could not edit Search settings in Central Admin. If I clicked on Search Administration, I would get the dreaded: Exception from HRESULT: 0x80040D1B.
To get the Central Admin pages working again, I:
Went to Central Administation > Application Management > Manage this Farm's Shared Services. Clicked the drop down on the default SSP and clicked edit properties. I re-entered my credentials and specified my local server as the index server.
Since doing that, I can now get to the Shared Services Administration > Search Administration pages. A crawl is running now. Currently I do NOT have 6398 and 6481 errors occurring, but I know from experience, that they will likely re-appear tomorrow.
This is a great article, and I tried all the options previously except adding my service account to Backup Operators group on my sharepoint server. Ive just done that. Let's wait and see.
To resolve 6482:
(I had same symptoms of no longer being able to launch IIS manager - this began after applying windows updates last week.) I installed hotfix from KB946517 and that cleared up 6482.
Once 6482 was resolved, I was still getting 6398 and 6481 and found that I also could not edit Search settings in Central Admin. If I clicked on Search Administration, I would get the dreaded: Exception from HRESULT: 0x80040D1B.
To get the Central Admin pages working again, I:
Went to Central Administation > Application Management > Manage this Farm's Shared Services. Clicked the drop down on the default SSP and clicked edit properties. I re-entered my credentials and specified my local server as the index server.
Since doing that, I can now get to the Shared Services Administration > Search Administration pages. A crawl is running now. Currently I do NOT have 6398 and 6481 errors occurring, but I know from experience, that they will likely re-appear tomorrow.
This is a great article, and I tried all the options previously except adding my service account to Backup Operators group on my sharepoint server. Ive just done that. Let's wait and see.
Monday, April 13, 2009
Event log errors: 6481 and 6398
Constant errors of this nature (1400+ per day):
Event Type: Error
Event Source: Office SharePoint Server
Event Category: Office Server Shared Services
Event ID: 6481
Date: 4/13/2009
Time: 1:46:43 PM
User: N/A
Computer:
Description:
Application Server job failed for service instance Microsoft.Office.Server.Search.Administration.SearchServiceInstance (8c711cc4-5b7f-4b74-aaec-c6d268a36cd3).
Reason: Exception from HRESULT: 0x80040D1B
Techinal Support Details:
System.Runtime.InteropServices.COMException (0x80040D1B): Exception from HRESULT: 0x80040D1B
at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.SynchronizeDefaultContentSource(IDictionary applications)
at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.Synchronize()
at Microsoft.Office.Server.Administration.ApplicationServerJob.ProvisionLocalSharedServiceInstances(Boolean isAdministrationServiceJob)
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Event Type: Error
Event Source: Windows SharePoint Services 3
Event Category: Timer
Event ID: 6398
Date: 4/13/2009
Time: 1:59:03 PM
User: N/A
Computer:
Description:
The Execute method of job definition Microsoft.Office.Server.Search.Administration.IndexingScheduleJobDefinition (ID ff276992-79c2-4aea-9aee-c1c6663ba9da) threw an exception. More information is included below.
Exception from HRESULT: 0x80040D1B
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
These instructions worked for me:
That stopped the annoying event log errors, but I noticed of course that Search now no longer worked. I got a few different error messages, one was:
Your search cannot be completed because of a service error. Try your search again or contact your administrator for more information.
To resolve, I did:
1. Opened Central Admin
2. In the Application Tab went to Create or Configure this farm's shared services
3. Clicked the drop down for my SSP, and clicked edit
4. Re-specified the Index server.
The index server pointer apparently got whacked. After I re-entered the Index Server, searches worked fine again - were even speedy. I did not need to force a full crawl, although that may be a good idea.
M
Update:
Noticed today that the next day my MOSS started throwing more errors of the same ilk:
Event Type: Error
Event Source: Office SharePoint Server
Event Category: Office Server Shared Services
Event ID: 6481
Date: 4/15/2009
Time: 4:10:11 PM
User: N/A
Computer: SMARTCENTER
Description:
Application Server job failed for service instance Microsoft.Office.Server.Search.Administration.SearchServiceInstance (8c711cc4-5b7f-4b74-aaec-c6d268a36cd3).
Reason: Exception from HRESULT: 0x80040D1B
Techinal Support Details:
System.Runtime.InteropServices.COMException (0x80040D1B): Exception from HRESULT: 0x80040D1B
at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.SynchronizeDefaultContentSource(IDictionary applications)
at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.Synchronize()
at Microsoft.Office.Server.Administration.ApplicationServerJob.ProvisionLocalSharedServiceInstances(Boolean isAdministrationServiceJob)
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Looked all over and couldnt find a solution related to MOSS 2007. So, I re-ran the stsadm commands that I had as above. Once that completed I logged into Central Admin. Had all kinds of Search related errors. Event log had this:
Event Type: Error
Event Source: Office Server Search
Event Category: Gatherer
Event ID: 10032
Date: 4/15/2009
Time: 4:31:22 PM
User: N/A
Computer: SMARTCENTER
Description:
Could not create a database session.
Context: Application 'd28dc583-1602-4488-8f5e-8f7c28900afe'
Details:
The database connection string is not available. (0xc0041228)
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I then went to Central Admin > Operations > Services on Server > Office Sharepoint Server Search and checked the "Use this server for serving Search Queries." (I have single server farm.)
Once I did that, all seems back to normal and event log is clean.
Im going to reboot tonite to make sure all remains well.
Event Type: Error
Event Source: Office SharePoint Server
Event Category: Office Server Shared Services
Event ID: 6481
Date: 4/13/2009
Time: 1:46:43 PM
User: N/A
Computer:
Description:
Application Server job failed for service instance Microsoft.Office.Server.Search.Administration.SearchServiceInstance (8c711cc4-5b7f-4b74-aaec-c6d268a36cd3).
Reason: Exception from HRESULT: 0x80040D1B
Techinal Support Details:
System.Runtime.InteropServices.COMException (0x80040D1B): Exception from HRESULT: 0x80040D1B
at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.SynchronizeDefaultContentSource(IDictionary applications)
at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.Synchronize()
at Microsoft.Office.Server.Administration.ApplicationServerJob.ProvisionLocalSharedServiceInstances(Boolean isAdministrationServiceJob)
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Event Type: Error
Event Source: Windows SharePoint Services 3
Event Category: Timer
Event ID: 6398
Date: 4/13/2009
Time: 1:59:03 PM
User: N/A
Computer:
Description:
The Execute method of job definition Microsoft.Office.Server.Search.Administration.IndexingScheduleJobDefinition (ID ff276992-79c2-4aea-9aee-c1c6663ba9da) threw an exception. More information is included below.
Exception from HRESULT: 0x80040D1B
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
These instructions worked for me:
- stsadm -o osearch -action stop
- Warning: The Office SharePoint Server Search service that was enabled on this server will uninstall and all index files for all SSPs will be deleted. Are you sure you want to permanently delete all data in the index for all SSPs? [y/n]
- NOTE: Only do this if you have not crawled / indexed content that cannot be re-indexed. If this is the case, then you open a support ticket with MSFT and work through it. Otherwise, hit Y
- Now issue: stsadm -o osearch -action start -role index
- You may receive the errror 'start' action failed. Additional information: the handle is invalid. Wait 30-60 secs and re-issue the command. It should go through fine.
- Open Central Admin - select your SSP
- Check / validate your search service configuration.
That stopped the annoying event log errors, but I noticed of course that Search now no longer worked. I got a few different error messages, one was:
Your search cannot be completed because of a service error. Try your search again or contact your administrator for more information.
To resolve, I did:
1. Opened Central Admin
2. In the Application Tab went to Create or Configure this farm's shared services
3. Clicked the drop down for my SSP, and clicked edit
4. Re-specified the Index server.
The index server pointer apparently got whacked. After I re-entered the Index Server, searches worked fine again - were even speedy. I did not need to force a full crawl, although that may be a good idea.
M
Update:
Noticed today that the next day my MOSS started throwing more errors of the same ilk:
Event Type: Error
Event Source: Office SharePoint Server
Event Category: Office Server Shared Services
Event ID: 6481
Date: 4/15/2009
Time: 4:10:11 PM
User: N/A
Computer: SMARTCENTER
Description:
Application Server job failed for service instance Microsoft.Office.Server.Search.Administration.SearchServiceInstance (8c711cc4-5b7f-4b74-aaec-c6d268a36cd3).
Reason: Exception from HRESULT: 0x80040D1B
Techinal Support Details:
System.Runtime.InteropServices.COMException (0x80040D1B): Exception from HRESULT: 0x80040D1B
at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.SynchronizeDefaultContentSource(IDictionary applications)
at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.Synchronize()
at Microsoft.Office.Server.Administration.ApplicationServerJob.ProvisionLocalSharedServiceInstances(Boolean isAdministrationServiceJob)
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Looked all over and couldnt find a solution related to MOSS 2007. So, I re-ran the stsadm commands that I had as above. Once that completed I logged into Central Admin. Had all kinds of Search related errors. Event log had this:
Event Type: Error
Event Source: Office Server Search
Event Category: Gatherer
Event ID: 10032
Date: 4/15/2009
Time: 4:31:22 PM
User: N/A
Computer: SMARTCENTER
Description:
Could not create a database session.
Context: Application 'd28dc583-1602-4488-8f5e-8f7c28900afe'
Details:
The database connection string is not available. (0xc0041228)
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I then went to Central Admin > Operations > Services on Server > Office Sharepoint Server Search and checked the "Use this server for serving Search Queries." (I have single server farm.)
Once I did that, all seems back to normal and event log is clean.
Im going to reboot tonite to make sure all remains well.
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
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...
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.
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.
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.
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...
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.
Subscribe to:
Posts (Atom)