SQL CLR UDF

  • Thread starter Thread starter songstre
  • Start date Start date
S

songstre

am currently trying to write a very simple UDF that checks for the
existence of a file. If the file does not exist it should return
null.
It doesn't seem to see the file even when it exists, however. I've
checked and rechecked and the file is there. HELP!
I tried using both UNC and mapped drive.

[Microsoft.SqlServer.Server.SqlFunction(DataAccess =
DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]


[System.Security.Permissions.PrincipalPermission(System.Security.Permission­
s.SecurityAction.Demand)]
public static SqlString GetPictureUrl(string accountName)
{
try
{
if (accountName == null || accountName == string.Empty)
return SqlString.Null;
else if (System.IO.File.Exists(@"Y:\Hr\OurPeople\WhosWho
\photos\big\" + aname + ".jpg"))
return new SqlString("http://mh000xsiis01/hr/
ourpeople/
whoswho/photos/big/" + aname + ".jpg");
else
return SqlString.Null;


}
catch (Exception exc)
{


throw exc;
}
}
 
What is the account that this is running for? Are you using SQL Server
accounts, or windows impersonation? This is important, as you have to make
sure that the mapping for the Y drive exists for the user that this is
running for.

Regardless, it's a bad idea to access the Y drive (assuming it is mapped
to a network drive) with a mapping, as mappings are user-specific, and I
don't think you know who the user is that is accessing the stored procedure
(is it the account the server is set up to use, or are you using trusted
connections and that's the user the stored procedure is running for?).

On top of that, you have to make sure that whomever the user is, that
user has access to the file, otherwise, I don't believe Exists will return
true.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

am currently trying to write a very simple UDF that checks for the
existence of a file. If the file does not exist it should return
null.
It doesn't seem to see the file even when it exists, however. I've
checked and rechecked and the file is there. HELP!
I tried using both UNC and mapped drive.

[Microsoft.SqlServer.Server.SqlFunction(DataAccess =
DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]


[System.Security.Permissions.PrincipalPermission(System.Security.Permission­
s.SecurityAction.Demand)]
public static SqlString GetPictureUrl(string accountName)
{
try
{
if (accountName == null || accountName == string.Empty)
return SqlString.Null;
else if (System.IO.File.Exists(@"Y:\Hr\OurPeople\WhosWho
\photos\big\" + aname + ".jpg"))
return new SqlString("http://mh000xsiis01/hr/
ourpeople/
whoswho/photos/big/" + aname + ".jpg");
else
return SqlString.Null;


}
catch (Exception exc)
{


throw exc;
}
}
 
    What is the account that this is running for?  Are you using SQLServer
accounts, or windows impersonation?  This is important, as you have to make
sure that the mapping for the Y drive exists for the user that this is
running for.

    Regardless, it's a bad idea to access the Y drive (assuming it is mapped
to a network drive) with a mapping, as mappings are user-specific, and I
don't think you know who the user is that is accessing the stored procedure
(is it the account the server is set up to use, or are you using trusted
connections and that's the user the stored procedure is running for?).

    On top of that, you have to make sure that whomever the user is, that
user has access to the file, otherwise, I don't believe Exists will return
true.

--
          - Nicholas Paldino [.NET/C# MVP]
          - (e-mail address removed)


am currently trying to write a very simple UDF that checks for the
existence of a file. If the file does not exist it should return
null.
It doesn't seem to see the file even when it exists, however. I've
checked and rechecked and the file is there. HELP!
I tried using both UNC and mapped drive.

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess =
DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]

[System.Security.Permissions.PrincipalPermission(System.Security.Permission­­
s.SecurityAction.Demand)]
    public static SqlString GetPictureUrl(string accountName)
    {
        try
        {
            if (accountName == null || accountName == string.Empty)
                return SqlString.Null;
            else if (System.IO.File.Exists(@"Y:\Hr\OurPeople\WhosWho
\photos\big\" + aname + ".jpg"))
                return new SqlString("http://mh000xsiis01/hr/
ourpeople/
whoswho/photos/big/" + aname + ".jpg");
            else
                return SqlString.Null;

        }
        catch (Exception exc)
        {

            throw exc;
        }
    }
Click to expand...

Thanks Nicholas.

I am running it with Windows Auth on a dev box where I am an
administrator. I have access to the file as well. I'm thinking I need
to grant some kind of access to the function (I may have said Stored
Proc before, not true....SQL function). I did try it with a UNC as
well. Just wanted to try with a mapped drive to see if it made a diff.

Thanks.
 
Back
Top