[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

CLR Stored Procedure

Posted on 2010-01-04
9
Medium Priority
?
1,208 Views
Last Modified: 2012-05-08
the .net sql stored procedure tries to create a file on the server:
error:
Msg 6522, Level 16, State 1, Procedure ZIP, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "ZIP":
System.ArgumentException: exception: {0}
Parameter name: System.UnauthorizedAccessException: Access to the path 'C:\' is denied.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

Code:
EXEC sp_configure @configname = 'Show Advanced Options', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO
drop assembly ZipStorer
ALTER DATABASE UTIL SET TRUSTWORTHY ON
create ASSEMBLY ZipStorer from 'C:\ZipStorer.dll'
WITH PERMISSION_SET = UNSAFE

DROP ASSEMBLY PMA
ALTER DATABASE UTIL SET TRUSTWORTHY ON
create ASSEMBLY PMA from 'C:\StoredProcedureZip.dll'
WITH PERMISSION_SET = UNSAFE

ALTER DATABASE UTIL SET TRUSTWORTHY ON
GO
Drop PRocedure zip
go
CREATE PROCEDURE ZIP
@SourcefilePath nvarchar,
@DestinationFilePath nvarchar,
@EveryFile bit
WITH EXECUTE AS OWNER
AS
EXTERNAL NAME PMA.[StoredProcedureZip.PMA.Zip].Compress

exec UTIL.dbo.ZIP 'C:\test zip\','c:\test.zip',1
0
Comment
Question by:cstratton
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 2

Accepted Solution

by:
swapneel_d14 earned 668 total points
ID: 26175418
Check that account under which you are running your SP has access to C:\
0
 
LVL 11

Assisted Solution

by:lenordiste
lenordiste earned 668 total points
ID: 26176379
as swapneel says, it's a permission issue.

=> you'll also need WRITE permissions it seems
=> one more thing, C: is the C drive where sqlserver is running not the one from which you are calling the store procedure
0
 

Author Comment

by:cstratton
ID: 26179920
Ive looked in services and added every account with full permissions. where do I find the account that SP is running under?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 664 total points
ID: 26180189
Look for the MSSQLSERVER service.  It is either:
1. Running as a Local System account or
2. An account with not sufficient permissions to write the file or
3. You have not enabled CLR Integration.
0
 

Author Comment

by:cstratton
ID: 26180746
ok great, I have a different error now:

I gave permission to the whole c:\ folder and I'm not looking/running anything in that folder?

The process cannot access the file 'C:\WINDOWS\system32\C' because it is being used by another process.
0
 
LVL 2

Expert Comment

by:swapneel_d14
ID: 26182564
what is thwe file name/path?
0
 

Author Comment

by:cstratton
ID: 26183256
c:\test zip\ saving a file to this directory
0
 

Author Comment

by:cstratton
ID: 26183261
also moved it to c:\program files\test zip\    same security error
0
 
LVL 11

Expert Comment

by:lenordiste
ID: 26192962
i think there must be a problem with how you pass the name of your file:
The process cannot access the file 'C:\WINDOWS\system32\C' because it is being used by another process.

it looks as if it's trying to run from c:\windows\system32 and appending C (the drive letter) to the path...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Loops Section Overview

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question