MOSS Forum

Ask Question   UnAnswered
Home » Forum » MOSS       RSS Feeds

Mass Edit to a Single Column of Library using SQL

  Date: Nov 01    Category: MOSS    Views: 734

I am wondering if anyone has found an easy way to use SQL and SharePoint
together. I want to create a selection of documents in a SP library that meet
certain criteria in the "Modified Date", then change all of the information in
the "Approved Date" column that I created using one script in SQL. I know if
this were an Access DB I could make thousands of edits in a matter of minutes,
but as of now I can't seem to get into SP using SQL. If there is another way of
doing mass edits in SP I would like to hear people's ideas.



10 Answers Found

Answer #1    Answered On: Nov 01    

Which version of SharePoint? You might consider using LINQ (I think
there's a codeplex project for 2007).


Answer #2    Answered On: Nov 01    

Making editing changes directly to the SharePoint content databases is very
dangerous and unsupported. Creating your own databases in the same SQL
instance is possible (although I don't recommend it), but you should never
make changes in SQL to SharePoint databases. Doing so voids your warranty
with Microsoft and usually leads to trouble in the long run. It will
definitely break any chance you have to update to the next version and will
probably cause errors the next time you apply a service pack or cumulative
update. In short, "Don't go there".

Answer #3    Answered On: Nov 01    

You could probably do this pretty simply using the datasheet view.

If we were doing this in my team, we would probably use a .Net/C# solution.

Answer #4    Answered On: Nov 01    

Very dangerous? Swimming in a bay full of sharks is very dangerous. I have
made numerous modifications to the alluserdata table. Please make sure you
understand exactly what you are doing (whatch for duplicate rows/deleted
rows). Backup your database before you try anything. Save the backup for
some days so you'd be sure you have a recover scenario. Make sure you know
which rows are to be updated. Do every update/delete in a transaction and if
possible out of office hours. Use the with nolock hint

Here is the link to the unsupported story:
http://support.microsoft.com/kb/841057/en-us. Did you ever try to get
support? I have, many times and none were very pleasant.

So if you have budget/c# knowledge, build a nice console app that loops
through the right splistitems and alter the metadata. If you have only SQL
knowledge/little time, do as I do and tell no-one ;-)

Answer #5    Answered On: Nov 01    

In this case its worse than dangerous, its Russian roulette. He's not just
talking about editing record values. He's talking about making a direct
change to the Schema of the database. ".the "Approved Date" column that I
created using one script in SQL." You might get away with editing some
records in the database if you know exactly what you are doing. But making
changes to the schema will eventually make it impossible to patch or upgrade
your system. That's more than just dangerous and should NEVER be attempted.

BTW the link you provided is broken. Here's a working one.


Answer #6    Answered On: Nov 01    

I do indeed agree changing schema is definitely a no-go; but I interpreted
Shane's wish to be able to edit values in a column based on values in
another column. His mail is a bit difficult to read.

Sorry for the broken link; thanx for fixing.

Answer #7    Answered On: Nov 01    

I wasn't arguing. Simply stating my opinion based upon your comments. "I've made
numerous changes", "do as I do and tell no-one" and such, yes. I would not want
someone like that working on a SharePoint deployment I had anything to do with.
But then I believe in following the rules and not going places I shouldn't.

Answer #8    Answered On: Nov 01    

Please remember that a little trimming of text goes a long way. Or maybe i just
need a new reader...

Answer #9    Answered On: Nov 01    

Thanks for the info. I am not sure but I think a majority think this is not
the best practice? LOL, I will NOT be suggesting the use of SQL to my people. I
will find a way that works for everyone, mostly the Datasheet view, until I find
something else.

You must all know how much acceptance you got when implementing SP to the
company, not a lot here. It is getting more and more accepted as time goes on
and as they realize that in the long run we will all be better off. I try to
show them ways of doing things with the least amount of steps. Moving 100's of
documents from a place they knew, file share, and asking them to touch every
document does not make for a happy user. I digress.

Answer #10    Answered On: Nov 01    

,there are several ways to do this, is the Approved Date column your own
custom column or the one created by the publishing infrastrcture approval
process or a sharepoint approval workflow?
If is is your own, you can change the view to datasheet, and the manually update
the field just like you do in Excel.
Since you are familiar with Access, you can create an Access file, then create
an external link to the SharePoint Document library, SP lists and libraries can
be used in Access as external tables, a very powerful option. Just create an
Update query in Access with your logic and let it rip, however, if you have
thousands of rows it will take a little while.

Didn't find what you were looking for? Find more on Mass Edit to a Single Column of Library using SQL Or get search suggestion and latest updates.