July 29, 2014 at 6:42 am
Can anyone help me. I use a script which run in 2008 without error.
But in 2012 i got this error:
Msg 102, Level 15, State 1, Procedure sp_SDMS_getfiledetails, Line 34
Incorrect syntax near '22004'.
Thanks.
ALTERprocedure [dbo].[sp_SDMS_getfiledetails]
(
@filenamenvarchar(255) = null
)
as
declare@fileobjint,
@fsobjint,
@existsint,
@errorint,
@srcvarchar(255),
@descvarchar(255)
begin
-- create FileSystem Object
exec@error = sp_OACreate 'Scripting.FileSystemObject', @fsobj out
if@error <> 0
begin
execsp_OAGetErrorInfo @fsobj, @src out, @desc out
selectconvert(varbinary(4),@error) as [Error],
@src as [Source],
@desc as [Desription]
return1
end
-- check if specified file exists
exec@error = sp_OAMethod @fsobj, 'FileExists', @exists out, @filename
if@exists = 0
begin
raiserror 22004 'The system cannot find the file specified.'
return2
end
-- create file object that points to specified file
exec@error = sp_OAMethod @fsobj, 'GetFile', @fileobj output, @filename
if@error <> 0
begin
execsp_OAGetErrorInfo @fsobj
return3
end
-- declare variables holding properties of file
declare@Attributestinyint,
@DateCreateddatetime,
@DateLastAccessed datetime,
@DateLastModified datetime,
@drivevarchar(1),
@Namenvarchar(255),
@ParentFoldernvarchar(255),
@Pathnvarchar(255),
@ShortPathnvarchar(255),
@Sizeint,
@Typenvarchar(255)
-- get properties of fileobject
execsp_OAGetProperty @fileobj, 'Attributes', @Attributes out
execsp_OAGetProperty @fileobj, 'DateCreated', @DateCreated out
execsp_OAGetProperty @fileobj, 'DateLastAccessed', @DateLastAccessed out
execsp_OAGetProperty @fileobj, 'DateLastModified', @DateLastModified out
execsp_OAGetProperty @fileobj, 'Drive', @drive out
execsp_OAGetProperty @fileobj, 'Name', @Name out
execsp_OAGetProperty @fileobj, 'ParentFolder', @ParentFolder out
execsp_OAGetProperty @fileobj, 'Path', @Path out
execsp_OAGetProperty @fileobj, 'ShortPath', @ShortPath out
execsp_OAGetProperty @fileobj, 'Size', @Size out
execsp_OAGetProperty @fileobj, 'Type', @Type out
-- destroy File Object
exec@error = sp_OADestroy @fileobj
if@error <> 0
begin
execsp_OAGetErrorInfo @fileobj
return4
end
-- destroy FileSystem Object
exec@error = sp_OADestroy @fsobj
if@error <> 0
begin
execsp_OAGetErrorInfo @fsobj
return5
end
-- return results
selectnull as [Alternate Name],
@Size as [Size],
convert(varchar, @DateCreated, 112) as [Creation Date],
replace(convert(varchar, @DateCreated, 108), ':', '') as [Creation Time],
convert(varchar, @DateLastModified, 112) as [Last Written Date],
replace(convert(varchar, @DateLastModified, 108), ':', '') as [Last Written Time],
convert(varchar, @DateLastAccessed, 112) as [Last Accessed Date],
replace(convert(varchar, @DateLastAccessed, 108), ':', '') as [Last Accessed Time],
@Attributes as [Attributes]
end
July 29, 2014 at 7:08 am
The RAISERROR parameters need to be provided within brackets and seperated by a comma. See http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(RAISERROR_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT)&rd=true
July 29, 2014 at 7:54 am
After changing to
if@exists = 0
begin
raiserror (22004, 'The system cannot find the file specified.')
return2
end
I got the following error:
Msg 102, Level 15, State 1, Procedure sp_SDMS_getfiledetails, Line 34
Incorrect syntax near 'The system cannot find the file specified.'.
July 29, 2014 at 8:25 am
Take a good look at the URL in my previous post about the meaning and position of the parameters. Here is some sample code to help you:
declare @exists int
set @exists = 0
if@exists = 0
begin
/*
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
*/
-- specify ad-hoc message with severity 10:
raiserror ('The system cannot find the file specified.', 10, 1)
-- creating, using and removing a custom defined message:
exec sp_addmessage @msgnum = 50004, @severity = 10, @msgtext = N'The system cannot find file ''%s'' specified.';
raiserror (50004,10,1,'FILE')
exec sp_dropmessage @msgnum = 50004;
end
July 29, 2014 at 8:45 am
I don't understand why this Statemant runs in SQLServer 2008
raiserror 22004 'The system cannot find the file specified.'
I have change it in SQLServer 2012 to
raiserror (N'22004 The system cannot find the file specified.', 22004, 1 )
and it runs.
But I dont't know why. :unsure:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply