June 14, 2012 at 6:54 am
Hi all,
I am trying to read a Windows Directory and then have SQL update a table with the information found.
I came across this section of SQL code after a long search. It looks like something I am after, but I am getting the following errors:
Level 15, State 1, Procedure Dir, Line 131
Incorrect syntax near '0'.
Level 15, State 1, Procedure Dir, Line 144
Incorrect syntax near the keyword 'INSERT'.
Could someone please help me with this code below, or perhaps give me a better solution.
Thanks in advance!
**CODE**
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
[IsFileSystem] INT, --1 if it is part of the file system
[IsFolder] INT, --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
@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
--PRINT 'objFolder: ' + @objFolder
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,
'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)
SELECT @NAME, @path, @ModifyDate, @IsFileSystem, @IsFolder
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
June 14, 2012 at 8:01 am
Line:
IF @hr 0
misses "="
BTW, your code looks like pot of Singaporean noodles 🙂
Try to format you code so it's easier to read. When posting to forum you can use code="sql" tag to preserve formatting (you can find it on the left from the typing bog in the shortcuts list frame)
June 15, 2012 at 12:40 am
Thanks!
June 15, 2012 at 12:46 am
Can you perhaps see what the problem with my second error is:
Level 15, State 1, Procedure Dir, Line 144
Incorrect syntax near the keyword 'INSERT'.
It is in this part at the end:
INSERT INTO @MyDir(error) SELECT LEFT(@strErrorMessage,2000)
END
EXECUTE sp_OADestroy @objFolder
EXECUTE sp_OADestroy @objShellApplication
RETURN
END
June 15, 2012 at 1:37 am
It's in line before the INSERT. You have used wrong type of single quote. Copy this one in:
SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something')+ ',' + COALESCE(@Description, '')
You have used "`" instead of "'". Did you copy this code from web?
June 15, 2012 at 2:11 am
Thanks. Yes I copied it from the web but tried to fix it best possble.
I now get this error:
Msg 455, Level 16, State 2, Procedure Dir, Line 145
The last statement included within a function must be a return statement.
June 15, 2012 at 2:34 am
Change the last line to :
RETURN @MyDir
June 15, 2012 at 2:51 am
I have tried a view different options, but I keep getting errors.
Where in this code should I add your line of SQL:
--and insert the properties into a table
INSERT INTO @MyDir ([NAME], [path], ModifyDate, IsFileSystem, IsFolder)
SELECT @NAME, @path, @ModifyDate, @IsFileSystem, @IsFolder
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_OADestroy @objFolder
EXECUTE sp_OADestroy @objShellApplication
RETURN
END
SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something')+ ',' + COALESCE(@Description, '')
INSERT INTO @MyDir(error) SELECT LEFT(@strErrorMessage,2000)
END
June 15, 2012 at 5:32 am
every line where you have
RETURN
should return the table variable.
Make sure you have the same before function end.
June 15, 2012 at 5:41 am
Thanks for your patience and help!
I have changed RETURN to RETURN @MyDir, and have added RETURN @MyDir before END, but get this error:
Msg 178, Level 15, State 1, Procedure Dir, Line 141
A RETURN statement with a return value cannot be used in this context.
Msg 178, Level 15, State 1, Procedure Dir, Line 149
A RETURN statement with a return value cannot be used in this context.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply