xp_getfiledetails, xp_cmdshell, xp_dirtree??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

xp_getfiledetails, xp_cmdshell, xp_dirtree???

Author

Topic

tornaritech
Starting Member

1 Post

Posted - 2004-08-27 : 22:49:42

Hello all,

SQL Server Database
VB FrontEnd App.
App loads XML file data into sql backend.

Changes are often made to the XML files. When changes are made, the files
must be reloaded into the database via the application. Changes have been
missed causing loss of production and jobs being processed incorrectly.

I am looking for a script that will check the filedate of the xml file or
the filedate tag in the xml against a 'DesignFileDate' field in the
database. If a discrepancy in the filedates is identified, I want to load
the XML data to a temporary table [ExchangeTable] into the database.

I have been experimenting with xp_getfiledetails and the xp_cmd shell
'dir..' procedures, however
I can not seem to get the process to execute in the manner needed. I
believe that I can run a sp that will import the file attributes, (name,
date, size) to a temp table, compare the date in the temp to table to the
date of [orderid, scheduledate] which is located in another table
[dbo.shoporder]. If a date is different then I need to copy the data for
that row from .shoporder to another table [exchange].

The developer will provide a process to either load the data into the
production database or delete the data. This will be processed from the
Exchange table.

Having no background in programming languages and fairly new to SQL this is
quite a challenge.

Any assistance is greatly appreciated..

Regards

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-28 : 00:32:54

[code]
-- Something like this should get you close

DECLARE @filedate datetime

Create table ##filedetails(
alternatename char(20),
size char(20),
creation_date char(20),
creation_time char(20),
last_written_date char(20),
last_written_time char(20),
last_accessed_date char(20),
last_accessed_time char(20),
attributes char(20)
)

--Get file info
INSERT ##filedetails EXEC master.dbo.xp_getfiledetails '\\server\path\file.xml'

--If file is not found return
IF @@ROWCOUNT = 0
RETURN

--Last update time of file
SELECT @filedate = convert(datetime, rtrim(last_written_date) + ' ' + substring(last_written_time,1,2) +
':' + substring(last_written_time,3,2) + ':' + substring(last_written_time,5,2))
FROM ##filedetails

--Load file if date is latter than date in DesignFileDate in MyHistoryTable. Add WHERE clause as needed
IF (
SELECT DateDiff(ss,@filedate,DesignFileDate )
FROM MyHistoryTable
) > 0
BEGIN
-- Execute proc to load updated XML file
EXEC sp_load_file_proc

-- Store filetime in History Table
UPDATE MyHistoryTable
SET DesignFileDate = @filedate
END
[/code]

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources