I don't get it... 'System.Security.SecurityException' occurred...

[okiiyama]

I'm new to C#, Visual Studio, and the whole .NET Framework.
I'm trying to create a stored procedure class(?) that will run a query and output the results to a text file located on a shared network drive. So far the class is empty, and when I compile/debug it I'm getting:
A first chance exception of type 'System.Security.SecurityException' occurred in mscorlib.dll

After reading a few things here and there, I've set All_code permission to Full trust on the server. I'm still getting the same error.

Here is my code so far:

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ExportRecordsToTextFile()
    {
        

    }
};

What do I need to do to get around this exception? It would be nice if I could just add something to the code, but if not what do I need to modify/configure on the server?

Any help, advice, direction would be great... I feel as though I'm at my wits end.


Thanks.

 

[dseaver]

Are you running the program from the remote drive? It doesnt like it when you do

 

[jmeckley]

why are you including using Microsoft.SqlServer.Server;
and the [Microsoft.SqlServer.Server.SqlProcedure] attribute

executing a sp looks like this

SqlCommand cmd = new SqlCommand("MyStoredProc", new SqlConnection());
cmd.Parameters.Add("Parameter1", SqlDataType.VarChar).Value = "my value";
//repeat for other parmeters


IDataReader reader = cmd.ExecuteReader();
DataTable table = new DataTable();
//configure columns
while(reader.Read())
{
   table.Rows.Add(new object[] {reader[0], reader[1], reader[n] });
}
return table;

you'll need to catch exceptions and dispose of resources as well.

for writing data to a file use the System.IO namespace to read/write files. the user running the operation will need access to the remote directory/file. I prefer to use absolute paths rather than drive letters. [tt]\\server name\drive\drictory\file.ext[/tt]

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[okiiyama]

Starting Over... I would like to create a c# stored procedure, and if possible I would like to write the code on my local machine, and also test/debug from my local computer. The stored procedure, once completed will be executed within a Job.

What the SP needs to do is:
1. Run a query
2. Write query results to a text file on a different server/mapped drive and in some sort of delimited format.

So what is (maybe arguably) my best course of action? Is there something code wise I need to do to "secure" my code, or is there something server-wise I need to do? I really dont know.

Thanks again.

 

[jmeckley]

What the SP needs to do is:
1. Run a query
2. Write query results to a text file on a different server/mapped drive and in some sort of delimited format.

I design all my stored procs from query analyzer (client tools for sql server). I know it can be done from VS, but I'm not sure how exactly.
my guess is: create a connection to the db. then right click the connection and select add query/stored proc

I would seperate fetching the data and exporting the data into 2 seperate routines. I haven't used sql server 2k5 yet, but with 2k doing anything other than CRUD seems to be difficult compared with OOP.


Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[obislavu]

In order your program to write on another machine you have to go in the .NET cOnfiguration and trust that directory e.g. \\server name\drive\drictory.
You can do it manually, executing the caspol.exe utility or in the installer when you install the assembly.
obislavu