You are currently browsing the category archive for the 'SQL' category.
Issue: Using repeating tables in InfoPath is a great approach to capturing repetitive data on a form. However when you want to display the information contained in the form on a SharePoint list or web page, a few caveats come into play.
Let’s say you want to display information contained in the repeating table on a web page. When you design the InfoPath form you only have the options to display each field as either the first, last, count or merge (see image below).
This is not an issue if you are content to simply display the merged contents of the repeating table. But let’s say that you want to use the web page for something more than just displaying static data. For example; you might want to edit the page in SharePoint Designer and insert some conditional formatting. Having data merged into one cell/line might not work for your display requirements. In this instance you will need a unique line for each item in the table displayed on the list.
So how do you submit the rows of a repeating table in InfoPath to a SharePoint list? There is no easy method of doing this (specifically crafted my MS). I have found a few alternatives but most require code and are not for the beginner.
The Visual Basic code and the UpdateListItems method is one of the best methods I’ve come across so far. I have followed these instructions and got this to work. However a lack of time and knowledge of VB prevented me from getting more than basic fields to work.
For my requirements I tried another approach. This might seem long winded but it worked for me. I created a SQL database with all the fields I needed in the InfoPath form. I then designed the InfoPath form from the SQL database and published it to a SharePoint site. This form had 2 data connections – 1 to the SQL database and 1 to the SharePoint library.
Users go to the SharePoint library to open, complete and submit the form. The form (for record purposes) is saved in the library. A copy of the data is also submitted to the SQL database. Each item/line in the repeating table is stored as a unique line in the database.
On a new web page on the SharePoint site I inserted a dataview web part and connected it to the SQL db. I then ordered the fields as appropriate and applied some conditional formatting so that the page could be used as an overview or management page.
The immediate downside of this approach is you cannot edit the information in the SQL db directly by opening up the form again and re-submitting the data. I have not found out how to do this yet!
To be able to edit the data, open the web page with the dataview web part on it in SharePoint Designer and go into the web part properties section. Make sure to tick both the ‘show edit items links’ and ‘synchronize commands’ boxes (see below).
This will provide an ‘edit’ link on the dataview web part (see below). You can then edit the information in the list and have the changes synched in the SQL db.
Hope this info helps…
I recently inhereted a MOSS 2007 system where the main MOSS content database had a SQL 2005 transaction log file of approx 150Gb, whereas the database was only 7Gb. Although full backups where scheduled (including shrinking of the db’s) as part of the daily maintance plan, this did not include a transaction log backup.
Inital attempts to shrink the log file failed. I was contemplating using the DETACH/ATTACH method and then deleting the log file as a last resort due to space issues on the server. However after reading up on the procedure, I decided to persue a way to shrink the log file the ‘correct‘ way.
After reading several posts and articles I found (like many others) that shrinking the transaction log after backing it up did not shrink the overall size of the file (albeit it had 99% available free space!). Quite by acident, I shrank it again with a nominal ’shrink file to’ and hey presto! At last the file size went back to roughly the same size as the nominal value, giving me back my server disk space. I repeated this on the other MOSS db’s and had to do the same procedure – *2 shrinking of the log files.
The next step was to figure out why the log file had grown so large. This answered itself when I went to set the autogrowth file size. For some reason the Max file size ‘Restricted file growth’ setting was set to around 2,700,00 MB.
MOSS Created SQL Databases
Further investigation on the other MOSS SQL databases revealed that when MOSS automatically created it’s SQL databases, it set the max growth file size on the transaction log files to some arbitarily large figure (way bigger than the size of the hard drive). As the previous administrator had not checked these settings the log’s grew out of all proportion.
I had 2 AAC servers connected with the farm information on a SQL 2000 server. Due to end of life I had to migrate the database to a new SQL 2005 server. This is how I did it;
1. I backed up the database (usually called by default the name of your AAC farm) on the SQL 2000 server and restored it onto SQL 2005.
2. Open the AAC Server configuration utility. Go to ‘Services’ and stop all services.
3. Go to ‘Server Farm Information’ and change the name of the SQL server.
4. Restart the services.
5. Check the log/event logs for any unusual behaviour.
6. I bounced the server(s) to be sure.
I had a Presentation 4.0 farm using an old SQL 7 server for it’s datastore which I wanted to migrate onto a new SQL 2005 server. This is how I did it beating in mind the article found here;
1. Backed up the datastore database on SQL 7 and restored it directly on the SQL 2005 server.
2. Shutdown all Citrix Pres servers apart from licence/data collector server.
3. Stopped the IMA service on this server
4. Copied the MF20.dns file located by default in c:\Program Files\Citrix\Independent Management Architecture
5. Edited the original MF20.dns and changed the SERVER= setting to the new server.
6. Shutdown the old SLQ 7 server.
7. Re-started the IMS service.
8. Check logs for any sign of datastore errors.
9. Repeated steps 3 – 8 on all other Citrix servers.
10. Set up maintenance task on SQL 2005 to backup the datastore DB. You might have to make the DB version 80 or upwards compatible (options setting on the properties of the DB) for it to appear in the list of Db’s available.
