March 31, 2006 at 3:46 am
Heres an extract of a stored procedure creating a column.
Path = FileLen([CacheServers].[CachePath]+ (left([DOCUMENT].[PHYSICAL_DOC_GUID],6))+''\''+[DOCUMENT].[PHYSICAL_DOC_GUID]+[DOCUMENT].[FileType])
Reult:
\\comp-ap-70c\Imxxs$\data\docs\70393C\70393CE0EC6D11D8BB64000D568A4637.tif
The above is a file path of an image stored on the SAN server. The code above works perfectly fine in MS Access but not in SQL server 2K
It should return the size in bytes of the file.
Books online state the VBA function fileLen() works in SQL Analyser, however, when I execute the stored procedure I receive an error message
"FileLen is not a recognised function name".
I cant find a thing on the Microsoft Tech communities the specifically relates to calling VBA functions in SQL.
Any ideas?
March 31, 2006 at 4:15 am
VBA functions do not work directly within chunks of T-SQL, as far as I know. Please post the quote from BOL that says they do.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 31, 2006 at 4:33 am
its in SQL server TSQL help itself.
Type in "Visual Basic for Applications functions" I cnat find the link to the Books Online via the web
March 31, 2006 at 4:34 am
Heres the quote.......
Microsoft® SQL Server™ 2000 Analysis Services supports many functions in the Microsoft Visual Basic® for Applications Expression Services library. This library is included with Analysis Services and automatically registered. Functions not supported in this release are marked by an asterisk in this table.
March 31, 2006 at 5:11 am
OK - that's Analysis Services, not straight T-SQL. Analysis Services is one of Microsoft's business intelligence tools (design of OLAP cubes etc). It is complementary to SQL.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 31, 2006 at 6:12 am
I simply need to obtain the size of a files sotred on at a location.
If theirs a VBA method it Im sure theirs a related SQL server method similer to FileLen()???
March 31, 2006 at 6:25 am
1. Use sp_OAxxxxx OLE interfaces to utilise FileSyatemObject
2. Create a extended stored procedure to get details
p.s. there is an undocumented procedure xp_getfiledetails but may not be in future editions beyond SQL 2000
CREATE TABLE #temp (alternate_name char(12), int, creation_date char(10), creation_time char(6), last_written_date char(10), last_written_time char(6), last_accessed_date char(10), last_accessed_time char(6), attributes int)
INSERT INTO #temp
EXEC master.dbo.xp_getfiledetails 'filename'
DECLARE @size int
SELECT @size = FROM #temp
DROP TABLE #temp
SELECT @size
Far away is close at hand in the images of elsewhere.
Anon.
March 31, 2006 at 6:33 am
I cannot find an sp called xp_getfiledetails in the sp list or the tsql help file.
Besides I would like to run this method against of colum of data returning the files size per row in a record set.
just like:
fileSzie = fileLen((co1+'\'+col2)) - this method works in MS Access VAB against the sql server that proivdes access to the stored image file on a SAN server
Its strange that MS Access would have a more complicated set of library functions than SQL, even though SQL is meant to be its Daddy?
March 31, 2006 at 7:12 am
Not strange really. MS Access is a database, a development environment, an end-user environment ... it tries to do pretty much everything.
SQL Server is a pure database environment - it stores stuff, edits stuff and deletes stuff. You are expected to use tools other than T-SQL for jobs outside of the db.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 31, 2006 at 7:17 am
CREATE FUNCTION udf_GetFileSize (@filename varchar(255))
RETURNS int
AS
BEGIN
DECLARE @fso int
DECLARE @res int
DECLARE @file int
DECLARE @size int
EXECUTE @res = sp_OACreate 'Scripting.FileSystemObject', @fso OUT
IF @res <> 0 RETURN -1
EXECUTE @res = sp_OAMethod @fso, 'GetFile', @file OUT, @filename
IF @res <> 0 RETURN -1
EXECUTE @res = sp_OAGetProperty @file, 'size', @size OUTPUT
IF @res <> 0 RETURN -1
EXECUTE @res = sp_OADestroy @file
EXECUTE @res = sp_OADestroy @fso
RETURN @size
END
SELECT [filename],dbo.udf_GetFileSize([filename]) FROM
Far away is close at hand in the images of elsewhere.
Anon.
March 31, 2006 at 7:35 am
I have created the stored proc dbo.udf_GetFileSize, but I cant see it, where would it be stored.
I have also inserted the following code into my original stored pr
select ........,
path = dbo.udf_GetFileSize(([CacheServers].[CachePath]+ (left([DOCUMENT].[PHYSICAL_DOC_GUID],6))+''\''
+[DOCUMENT].[PHYSICAL_DOC_GUID]+[DOCUMENT].[FileType]))
from table
where.....
But I recevi an error message "statements could not be prepared....."
Any ideas?
March 31, 2006 at 7:56 am
I get an error with the quotes around the \
other than that it works OK for me
try changing to this
dbo.udf_GetFileSize([CacheServers].[CachePath] + LEFT([DOCUMENT].[PHYSICAL_DOC_GUID],6) + '\' + [DOCUMENT].[PHYSICAL_DOC_GUID] + [DOCUMENT].[FileType])
p.s.
dbo.udf_GetFileSize is a user defined function not a procedure and can be found in User Defined Functions under the database in EM
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply