Error Msg 102, Level 15, State 1, Procedure

  • 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

  • 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

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • 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.'.

  • 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

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • 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