November 4, 2013 at 7:07 am
Hi,
I found the below code that will bring back "some" of the attributes of any file in the selected folder... however, I also need the Created Date of the file... I get the modify date, I need both.
In the code, I added "CreateDate", it runs, but brings back less information than when I have it commented out and the created date is null...
Does anyone have any ideas as to what I should do???
Thanks,
John
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
alter FUNCTION [dbo].[Dir](@Wildcard VARCHAR(8000))
/* returns a table representing all the items in a folder. It takes as parameter the path to the folder. It does not take wildcards in the same way as a DIR command. Instead, you would be expected to filter the results of the function using SQL commands
Notice that the size of the item (e.g. file) is not returned by this function.
This function uses the Windows Shell COM object via OLE automation. It opens a folder and iterates though the items listing their relevant properties. You can use the SHELL object to do all manner of things such as printing, copying, and moving filesystem objects, accessing the registry and so on. Powerful medicine.
--e.g.
--list all subdirectories directories beginning with M from "c:\program files"
SELECT [path] FROM dbo.dir('c:\program files')
WHERE name LIKE 'm%' AND IsFolder =1
SELECT * FROM dbo.dir('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG')
*/
RETURNS @MyDir TABLE
(
-- columns returned by the function
[name] VARCHAR(2000), --the name of the filesystem object
[path] VARCHAR(2000), --Contains the item's full path and name.
[ModifyDate] DATETIME, --the time it was last modified
--[CreatedDate] DATETIME,
[IsFileSystem] INT, --1 if it is part of the file system
[IsFolder] INT, --1 if it is a folsdder otherwise 0
[error] VARCHAR(2000) --if an error occured, gives the error otherwise null
)
AS
-- body of the function
BEGIN
DECLARE
--all the objects used
@objShellApplication INT,
@objFolder INT,
@objItem INT,
@objErrorObject INT,
@objFolderItems INT,
--potential error message shows where error occurred.
@strErrorMessage VARCHAR(1000),
--command sent to OLE automation
@Command VARCHAR(1000),
@hr INT, --OLE result (0 if OK)
@count INT,@ii INT,
@name VARCHAR(2000),--the name of the current item
@path VARCHAR(2000),--the path of the current item
@ModifyDate DATETIME,--the date the current item last modified
--@CreatedDate DATETIME,
@IsFileSystem INT, --1 if the current item is part of the file system
@IsFolder INT --1 if the current item is a file
IF LEN(COALESCE(@Wildcard,''))<2
RETURN
SELECT @strErrorMessage = 'opening the Shell Application Object'
EXECUTE @hr = sp_OACreate 'Shell.Application',
@objShellApplication OUT
--now we get the folder.
IF @HR = 0
SELECT @objErrorObject = @objShellApplication,
@strErrorMessage = 'Getting Folder"' + @wildcard + '"',
@command = 'NameSpace("'+@wildcard+'")'
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objShellApplication, @command,
@objFolder OUT
IF @objFolder IS NULL RETURN --nothing there. Sod the error message
--and then the number of objects in the folder
SELECT @objErrorObject = @objFolder,
@strErrorMessage = 'Getting count of Folder items in "' + @wildcard + '"',
@command = 'Items.Count'
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objfolder, @command,
@count OUT
IF @HR = 0 --now get the FolderItems collection
SELECT @objErrorObject = @objFolder,
@strErrorMessage = ' getting folderitems',
@command='items()'
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objFolder,
@command, @objFolderItems OUTPUT
SELECT @ii = 0
WHILE @hr = 0 AND @ii< @count --iterate through the FolderItems collection
BEGIN
IF @HR = 0
SELECT @objErrorObject = @objFolderItems,
@strErrorMessage = ' getting folder item '
+ CAST(@ii AS VARCHAR(5)),
@command='item(' + CAST(@ii AS VARCHAR(5))+')'
--@Command='GetDetailsOf('+ cast(@ii as varchar(5))+',1)'
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objFolderItems,
@command, @objItem OUTPUT
IF @HR = 0
SELECT @objErrorObject = @objItem,
@strErrorMessage = ' getting folder item properties'
+ CAST(@ii AS VARCHAR(5))
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objItem,
'path', @path OUTPUT
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objItem,
'name', @name OUTPUT
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objItem,
'ModifyDate', @ModifyDate OUTPUT
--IF @HR = 0
-- EXECUTE @hr = sp_OAMethod @objItem,
-- 'CreateDate', @CreatedDate OUTPUT
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objItem,
'IsFileSystem', @IsFileSystem OUTPUT
IF @HR = 0
EXECUTE @hr = sp_OAMethod @objItem,
'IsFolder', @IsFolder OUTPUT
--and insert the properties into a table
INSERT INTO @MyDir ([NAME], [path], ModifyDate, IsFileSystem, IsFolder) --CreatedDate,
SELECT @NAME, @path, @ModifyDate, @IsFileSystem, @IsFolder --@CreatedDate,
IF @HR = 0 EXECUTE sp_OADestroy @objItem
SELECT @ii=@ii+1
END
IF @hr <> 0
BEGIN
DECLARE @Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
@Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
SELECT @strErrorMessage = 'Error whilst '
+ COALESCE(@strErrorMessage, 'doing something') + ', '
+ COALESCE(@Description, '')
INSERT INTO @MyDir(error) SELECT LEFT(@strErrorMessage,2000)
END
EXECUTE sp_OADestroy @objFolder
EXECUTE sp_OADestroy @objShellApplication
RETURN
END
GO
November 4, 2013 at 7:18 am
Is it possible to create an CLR stored procedure to do what you need it to do? CLR has more security safeguards then sp_OA. sp_OA used to have some serious side-effects in previous versions (haven't tired them in 2012).
November 4, 2013 at 7:23 am
I haven't heard of CLR, do you have any examples I can borrow???
November 4, 2013 at 7:37 am
Ah, the Table-Valued Functions... yes, I can do that...
November 4, 2013 at 8:05 am
jsteinbeck-618119 (11/4/2013)
Ah, the Table-Valued Functions... yes, I can do that...
Table Valued Functions aren't the same as CLR. CLR, specifically SQLCLR, is code written in another language (such as C#) and compiled to a DLL and then consumed by SQL Server.
As for SP_OA*, Keith is correct. They seemed to have some "connection leaks" in the old days but rumor has it that hasn't been true since SQL Server 2005 hit the streets. Of course, you can still end up with connection leaks if you forget to close any connections that were formed by your SP_OA* code.
If you're still interested in using SP_OA* for this (maybe because you don't want to implement SQLCLR), let me know. I have some SP_OA* code that will return all of the things you ask and more. As a bit of a sidebar, though, SP_OA* is quite slow for what it does AND it requires "SA" privs. If you don't want to go the CLR route, an excursion to xp_CmdShell would be a whole lot faster.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2013 at 12:35 pm
Well, hello!!!!
Big fan of yours...
I'll explain what I'm doing, or why... I inherted a commercial database, that isn't so great... lot's of redundant data, seems no real normilization...
So, the system recieves a fax and puts it into a folder (not yet entered into the DB, yet I have the files Created Date!), when a user access this file from within the DB, they are creating an Order, and the DB will rename the file appropriatley, and move the whole file renamed to a new location... then it writes to the table, the file name, type of file, and the date entered into the table...
Work wants to know when that file came in, and beign that it's modified, I know when they started working on that order... so having the created date and the modified date, I can derive metrics... I don't want to create something that is manual to have to consinously run by someone, to go and read all the folders and find the files and store the information in a table...
i'd rather, place a trigger on the table the DB writes to, and use my trigger (inspired by you (tally and COLUMNS_UPDATED)); code for that is below for fun, and for you to see)... then I am hands free to get the information from the new folder and write to the table...
I love to learn, so I am a very eager student and willing to do what is best!!!!
Does that make sense???
Thanks again...
INSERT INTO mytable
(MRN, ORDER_NO, BILL_NO, DRUG_NO, RX_NO, RECORD_STAGE, RECORD_ACTION, [RECORD_TYPE], STATUS_TYPE, ACTUAL_DATET, [STATUS_DATE], [RPT_DD], [RPT_WW], DSOURCE, PHASE, OT_STATUS, SITE_NO)
SELECT
I.LINKAS MRN
, I.ORDERNOAS ORDER_NO
, NULLAS BILL_NO
, I.NOAS DRUG_NO
, I.SCRIPTEXTAS RX_NO
, 1AS RECORD_STAGE
, 3AS RECORD_ACTION
, I.[RECORD_TYPE]
--CASE
--WHEN I.NOTES = 'Animal'THEN 0 --
--WHEN I.NOTES = 'New patient'THEN 1 -- New Patient -- New Therapy
--WHEN I.NOTES = 'Existing patient - new therapy'THEN 2 -- Existing Patient — New Therapy
--WHEN I.NOTES = 'Existing patient - same order'THEN 3 -- Existing Patient – Existing Therapy (Refill)
--WHEN I.NOTES = 'Existing patient – brand to generic'THEN 3 -- Existing Patient – Existing Therapy (Refill)
--WHEN I.NOTES = 'Existing patient - decrease in dose'THEN 4 -- Existing Patient – Existing Therapy (Renewal)
--WHEN I.NOTES = 'Existing patient - increase in dose'THEN 4 -- Existing Patient – Existing Therapy (Renewal)
--WHEN I.NOTES = 'Existing patient - same dose, cycle change'THEN 4 -- Existing Patient – Existing Therapy (Renewal)
--ELSE 9
--ENDAS [RECORD_TYPE]
, 63AS STATUS_TYPE
, I.[TOUCHDATE]AS ACTUAL_DATET
, I.STATUS_DATE
, I.RPT_DD
, I.RPT_WW
, 'NN'AS DSOURCE
, 'NN'AS PHASE
, I.OT_STATUS
--CASE
--WHEN I.NOTES = 'Animal'THEN 'Animal'
--WHEN I.NOTES = 'New patient'THEN 'New Patient -- New Therapy'
--WHEN I.NOTES = 'Existing patient - new therapy'THEN 'Existing Patient — New Therapy'
--WHEN I.NOTES = 'Existing patient - same order'THEN 'Existing Patient – Existing Therapy (Refill)'
--WHEN I.NOTES = 'Existing patient – brand to generic'THEN 'Existing Patient – Existing Therapy (Refill)'
--WHEN I.NOTES = 'Existing patient - decrease in dose'THEN 'Existing Patient – Existing Therapy (Renewal)'
--WHEN I.NOTES = 'Existing patient - increase in dose'THEN 'Existing Patient – Existing Therapy (Renewal)'
--WHEN I.NOTES = 'Existing patient - same dose, cycle change'THEN 'Existing Patient – Existing Therapy (Renewal)'
--ELSE 'Unkown'
--ENDAS OT_STATUS
, I.SITENOAS SITE_NO
--, COLS.N
FROM
(
SELECT
D.LINK
, D.ORDERNO
, D.SCRIPTEXT
, D.SITENO
, D.NO
, D.NOTES
,
CASE
WHEN L.ID IS NULL THEN 'Unkown'
ELSE L.NOTES_VALUE
ENDAS OT_STATUS
, D.[TOUCHDATE]
,
CASE
WHEN L.ID IS NULL THEN 9
ELSE N.ID
ENDAS [RECORD_TYPE]
, CAL.STATUS_DATE
, CAL.RPT_DD
, CAL.RPT_WW
FROM
INSERTED D
INNER JOIN
[lstRPT_CALENDAR] CAL WITH (INDEX(IDX_ACTIONDATE_1))
ON
CAST((CONVERT(VARCHAR(8), D.[TOUCHDATE] ,112)) AS INT) = CAL.F_ACTION_DATE
LEFT JOIN
[CRP_Support_DF_lstNEWNEW_LABELS] L
ON
D.NOTES = L.NOTES_VALUE
LEFT JOIN
[CRP_Support_DF_lstNEWLBL_BRIDGE] B
ON
L.ID = B.CPR_ID
LEFT JOIN
[CRP_Support_DF_lstNEWNEW] N
ON
B.NEW_ID = N.ID
) I
CROSS JOIN
(
SELECT
S.NAME
, T.N AS N
FROM
[TALLY] T
INNER JOIN
SYSCOLUMNS S
ON
T.N = S.COLID
WHERE
(
(
N IN (71)
) -- THIS IS YOUR NEW LOOP OF COLUMNS YOU WANT MONITORED
AND
ID = object_id(N'DB_TABLE_NAME')--@TableName)
)
AND
(
CASE
WHEN (SUBSTRING(COLUMNS_UPDATED(), 1 + ROUND(((T.N - 1)) / 8, 0), 1) & POWER (2, ((T.N - 1)) % 8) <> 0 )
THEN 1
ELSE 0
END
) = 1
) COLS
November 5, 2013 at 12:58 am
I guess I'm a little confused about your process. What do you mean by "when a user access this file from within the DB"?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2013 at 2:36 am
I guess you want to automate the (FAX Document) file reading and order generation process rather that it is happening manually(as of now).
I think you take SSIS in to consideration and automate it using jobs. i dont know much about SSIS, experts can help you on that.
Regards
Durai Nagarajan
November 5, 2013 at 7:34 am
Jeff,
The user is in the DB via GUI, they click a button on the form the basically says, "Lets look at new faxes that came in", and this form reads from the folder where all the fax documents are... So all the faxes they are looking at in this form, is "like" a table, when they double click on the line/row it will open the fax document, where they can then enter the fax infromation into the DB, thus inserts into the DB happens...
Does that help?
November 5, 2013 at 7:41 am
jsteinbeck-618119 (11/5/2013)
Jeff,The user is in the DB via GUI, they click a button on the form the basically says, "Lets look at new faxes that came in", and this form reads from the folder where all the fax documents are... So all the faxes they are looking at in this form, is "like" a table, when they double click on the line/row it will open the fax document, where they can then enter the fax infromation into the DB, thus inserts into the DB happens...
Does that help?
It does but it raises another question. If the user is able to get to the file(s) from ther GUI, why not just do a similar bit of programming in the GUI as you're done with the sp_OA* functionality?
Or do I have this backwards? Are you saying that you already have the filepath/name/CreationDate in a table and THAT's what these users are looking at? And that the GUI modifies that table when someone takes ownership of a given file?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2013 at 8:50 am
This DB is proprietary, so we can't make any changes to the code behind the GUI!.. I can place triggers on the table...
So the form, is showing what files are in the inbound folder virtually, no table... even if the form showed the create and modified date, I couldn't grab from anywhere because i can't change the code in the form, nor is it in any table as of yet, until they double click the line to enter into the database... Once they have, there is a documents table that is inserted into with the path of the file... (the table stores the date stamp of when the line was entered, not when the file was created and moved to it's new location)... so, having an insert trigger on this table, having the path, I need code to go and read the file attributes and then insert the data into a different table... (like the trigger I posted earlier)
Did I do better this time...lol 🙂
November 5, 2013 at 10:37 am
If you're looking for a SQL procedure to read the contents of a directory, here's one approach. This does require xp_cmdshell, so you're going to need permissions to run it. Please don't give the user that connects from the application permission to run it, as that would create a security hole you don't want to open. You could use this basic logic to create a procedure that would populate your table with any new files created using either date or filename (where not exists). It could be done as a database job or whatever other event you want, but wouldn't suffer from the performance hit of having a trigger on the table.
declare @strCmd varchar(255);
set @strCmd = 'dir c:\temp /a-d';
--create a temp table to receiving the directory listing
if object_id('tempdb.dbo.#dir', 'u') is not null drop table #dir;
create table #dir (
raw varchar(500),
filedate datetime,
filename varchar(255));
--shell out to dos and populate our table
insert into #dir(raw) execute xp_cmdshell @strCmd;
--assuming that all files begin with a date, eliminate the junk
delete from #dir
where substring(raw, 1, 3) not like '[0-9][0-9]/'
or raw is null;
--parse the strings we have to get the date and filename
update #dir
set filedate = CONVERT(datetime, substring(raw, 1, 20)),
filename = REVERSE(LEFT(REVERSE(raw), CHARINDEX(' ', REVERSE(raw)) - 1));
--select our result set and clean up after ourselves
select filedate, filename
from #dir;
drop table #dir;
HTH
November 5, 2013 at 11:09 am
Hi,
Your code would need to co-exist in my trigger l posted earlier...
I could use your to create and inline table function and pass in the location and file name... I assume I'd have to parse out the file name?
Is that possible? Does your code retrieve the create date and modified date of the file...
This is a production db, what security holes would we face?
Thanks
November 5, 2013 at 2:14 pm
jsteinbeck-618119 (11/5/2013)
Hi,Your code would need to co-exist in my trigger l posted earlier...
I could use your to create and inline table function and pass in the location and file name... I assume I'd have to parse out the file name?
Is that possible? Does your code retrieve the create date and modified date of the file...
This is a production db, what security holes would we face?
Thanks
This will likely start an argument but you were using sp_OA and that requires SA privs. xp_CmdShell also requires SA privs BUT... we could build a stored procedure that has the privs instead of giving individuals or apps the privs.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2013 at 3:00 pm
I like your idea of building the stored procedure to have the privs...
Can the trigger have the privs?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply