Compare documents in folder to items in a table

Question

Compare documents in folder to items in a table

Marius Davidsen

SSC Journeyman

Points: 92
More actions
December 17, 2009 at 6:04 am

#213614

Hi, After a migration of a Mamut Business installation to a new server, there was some issues with some non-US characters, so we decided to compare the content of the document folders with a SQL table containing relative links to all the documents.
What I want, is to get notifications on those files that does exists in one, but not the other. Since i am not very experienced with SQL, i turn to you for some guidance. I have searched the web, but i can't for some reason find what im looking for.
Is there a way to do this only SQL based? or with VBscript or batch?
Best regards,
Marius D.
Should be posted in the newbie section, sorry.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 1

The only tools I'm aware of compare Folders to Folders and Databases to Databases, not Databases to Folders.

The best thing you can do is set up dual monitors on your box, point one to the Folder and open SSMS on the other. Run a SELECT statement to get all your links. Then do a side by side manual comparison between the links and the documents.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Marius Davidsen SSC Journeyman Points: 92 More actions · Answer 2

Hi, and thanks for reply.

The problem here, is that its not 100 rows, but 5000. Would it be possible to compare them in any other way? -> Manually is not a option.

Best regards,

Marius D.

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 3

You can import folders/files into a table

Maybe this, haven't tried it: http://www.sqlusa.com/articles2005/launchcelebration/ or http://www.sqlservercentral.com/articles/SQLCLR/65656/

The method I have used is xp_cmdshell and dir. Search xp_cmdshell and dir in the scripts section of this site.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

The method Emily is describing has worked for me in the past.

Using xp_cmdshell, run a dir of the directory. Output the results of that directly to a table.

Once in the table you can use t-sql to do your comparisons.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 5

Just remember that xp_cmdshell is off by default and you'll have to turn it on before you can use it in this manner.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

dan77eu SSC Journeyman Points: 86 More actions · Answer 6

I have used something like the second one with success.

CLR Procedures help if you don't want to program the logic in T-SQL.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Step 1 to a high speed T-SQL solution that does NOT require xp_CmdShell... try the following command and see if you understand the output....

EXEC Master.dbo.xp_Dirtree 'C:\',1,1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Nice one. Thanks for jogging the memory. I use sql so rarely to query the file-system that I forgot about this one.

It would work well with xp_getfiledetails.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

I agree... the problem with that is (IIRC) that xp_getfiledetails is no longer available in SQL Server 2008 (although xp_DirTree is) so any upgrade plans would make some pretty loud flushing sounds. Although it hasn't stopped me from doing so, that's why the "don't use undocumented features folks" say not to use undocumented features. Of course, even documented features can change or go away at the drop of a hat like the required privs for xp_MakeWebTask did in 2k sp4 or like some uses of ORDER BY did in 2k5.

I guess I'm going to have to write up a wish list for MS on CONNECT... I understand the marketing draw of such things like SSIS but, holy cow... there should be no reason why you can't do a full gambit of ETL related things from T-SQL (especially on a non-public facing machine dedicated to ETL processes) without having to dink around with someone's idea of what a "simple to use" GUI is.

Makes me want to write a CLR or something... 😉 What a pain.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marius Davidsen SSC Journeyman Points: 92 More actions · Answer 10

Thanks for all the replys guys! I will digg into this again early next week when im at the office again, and then let you know how it works out.

Best regards,

Marius D.

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 11

Now, see, I've never heard of xp_DirTree before now. And for a moment, I actually considered changing the code I had to use it.

And then you spoiled it with your talk of disappearing undocumented features... @sigh. Jeff, you ruin all my fun. @=Pbt.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

I'll save the CLR for another time. And, absolutely agree on the undocumented v. documented changing at the whim of MS.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

Brandie Tarvin (12/21/2009)
Now, see, I've never heard of xp_DirTree before now. And for a moment, I actually considered changing the code I had to use it.
And then you spoiled it with your talk of disappearing undocumented features... @sigh. Jeff, you ruin all my fun. @=Pbt.

Heh... guess what? If you have SQL Server 2000, 2005, or 2008, use it. How often do you migrate applications from one version of SQL Server to another? Almost never would be my bet. If the undocumented feature continues in 2k11, then no worries. If it doesn't, deal with it at migration time. Chances are they'll deprecate some documented feature between now and then and you'll need to do a rewrite anyway. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

I wish xp_dirtree would give enough information to be able to traverse a directory hierarchy. Anyone know how to do that?

d:\sqlexp\112233

d:\sqlexp\112233\20091211

d:\sqlexp\112233\20091212

d:\sqlexp\112233\20091213

d:\sqlexp\112233\20091214

d:\sqlexp\112233\20091215

d:\sqlexp\112233\20091216

d:\sqlexp\112233\20091217

d:\sqlexp\112244

d:\sqlexp\112244\20091211

d:\sqlexp\112244\20091212

d:\sqlexp\112244\20091213

d:\sqlexp\112244\20091214

d:\sqlexp\112244\20091215

d:\sqlexp\112244\20091216

d:\sqlexp\112244\20091217

I seem to get the following output from xp_dirtree, and this doesn't enable me to do stuff on subfolders (such as rmdir):

112233 1

20091211 2

20091212 2

20091213 2

20091214 2

20091215 2

20091216 2

20091217 2

112244 1

20091211 2

20091212 2

20091213 2

20091214 2

20091215 2

20091216 2

20091217 2

Of course given that I need to do the rmdir, I need xp_cmdshell anyway, or CLR with unsafe ... 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service