Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error Msg 102, Level 15, State 1, Procedure Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 6:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:35 AM
Points: 3, Visits: 7
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.

ALTER	procedure [dbo].[sp_SDMS_getfiledetails]
(
@filename nvarchar(255) = null
)
as

declare @fileobj int,
@fsobj int,
@exists int,
@error int,
@src varchar(255),
@desc varchar(255)
begin
-- create FileSystem Object
exec @error = sp_OACreate 'Scripting.FileSystemObject', @fsobj out

if @error <> 0
begin

exec sp_OAGetErrorInfo @fsobj, @src out, @desc out
select convert(varbinary(4),@error) as [Error],
@src as [Source],
@desc as [Desription]
return 1

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.'
return 2

end

-- create file object that points to specified file
exec @error = sp_OAMethod @fsobj, 'GetFile', @fileobj output, @filename

if @error <> 0
begin

exec sp_OAGetErrorInfo @fsobj
return 3

end

-- declare variables holding properties of file
declare @Attributes tinyint,
@DateCreated datetime,
@DateLastAccessed datetime,
@DateLastModified datetime,
@Drive varchar(1),
@Name nvarchar(255),
@ParentFolder nvarchar(255),
@Path nvarchar(255),
@ShortPath nvarchar(255),
@Size int,
@Type nvarchar(255)

-- get properties of fileobject
exec sp_OAGetProperty @fileobj, 'Attributes', @Attributes out
exec sp_OAGetProperty @fileobj, 'DateCreated', @DateCreated out
exec sp_OAGetProperty @fileobj, 'DateLastAccessed', @DateLastAccessed out
exec sp_OAGetProperty @fileobj, 'DateLastModified', @DateLastModified out
exec sp_OAGetProperty @fileobj, 'Drive', @Drive out
exec sp_OAGetProperty @fileobj, 'Name', @Name out
exec sp_OAGetProperty @fileobj, 'ParentFolder', @ParentFolder out
exec sp_OAGetProperty @fileobj, 'Path', @Path out
exec sp_OAGetProperty @fileobj, 'ShortPath', @ShortPath out
exec sp_OAGetProperty @fileobj, 'Size', @Size out
exec sp_OAGetProperty @fileobj, 'Type', @Type out

-- destroy File Object
exec @error = sp_OADestroy @fileobj

if @error <> 0
begin

exec sp_OAGetErrorInfo @fileobj
return 4

end

-- destroy FileSystem Object
exec @error = sp_OADestroy @fsobj

if @error <> 0
begin

exec sp_OAGetErrorInfo @fsobj
return 5

end

-- return results
select null 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

Post #1597261
Posted Tuesday, July 29, 2014 7:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 2,290, Visits: 2,749
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’! **
Post #1597272
Posted Tuesday, July 29, 2014 7:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:35 AM
Points: 3, Visits: 7
After changing to

if	@exists = 0
begin

raiserror (22004, 'The system cannot find the file specified.')
return 2

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.'.
Post #1597300
Posted Tuesday, July 29, 2014 8:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 2,290, Visits: 2,749
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’! **
Post #1597317
Posted Tuesday, July 29, 2014 8:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:35 AM
Points: 3, Visits: 7
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.
Post #1597331
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse