CLR will not return MyDocuments Directory

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CLR will not return MyDocuments Directory

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-17 : 20:04:33

CLR Stored Procedure
.NET 3.5
Database set to External Permission
ALTER DATABASE VC SET TRUSTWORTHY ON
CLR Enabled

Problem: Returns strCurrentTime and pathdir1 OK but will not return pathdir or pathdir2 when Exec dbo.clrsptest from within SQL Server Management Studio?

Result:

Current System DateTime is: 18/10/2011 10:12:23 AM

C:\Program Files (x86)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Linq;
using System.Net;
using System.Security;
using System.IO;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CLRSPTest()
{
SqlPipe sp;
sp = SqlContext.Pipe;
String strCurrentTime = "Current System DateTime is: " + System.DateTime.Now.ToString();
String pathdir = Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments);
String pathdir2 = Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
String pathdir1 = Environment.GetFolderPath(System.Environment.SpecialFolder.ProgramFiles);
sp.Send(strCurrentTime);
sp.Send(pathdir);
sp.Send(pathdir1);
sp.Send(pathdir2);
}
}

C# project returns MyDocuments (pathdir) folder OK.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MyComputerTest
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();

String pathdir = System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments);
String pathdir2 = System.Environment.SystemDirectory;
int a = 0;
}
}
}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-17 : 22:37:37

This is probably because the SQL Server service account doesn't have a personal folder setup.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-18 : 00:03:04

Thanks, any idea through sql server 2008 how to get the current Windows User MyDocuments path? C:\Users\sharlington for example.

Also see;

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=166688

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-18 : 00:16:46

You won't be able to get that info from SQL as SQL can't see out. You need to use a front-end application to get to this information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-18 : 00:34:23

This is for reporting so I don't have a front end app. My only tools are SQL Server Management Studio, what ever sql code can be written (T-SQL or CLR) and BIDS for creating templates. I was so close with my previous post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=166688. Just could not work out why the idiom returned differently from DOS CMD prompt and xp_cmdshell.

I did get something working in the end but what a mess.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-18 : 12:46:56

The reason why the two return different output is due to the context of who is running it. With xp_cmdshell, it'll be the SQL Server service account. Within a cmd window, it'll be the logged in user.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-10-18 : 23:30:41

While this works under a c# project I could not get this to work under a sql server clr database project either.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Linq;
using System.Net;
using System.Security;
using System.IO;
using System.Security.Principal;
using Microsoft.Win32;
using System.Security.Permissions;

using System.Collections;
using System.Diagnostics;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ReadRegKey()
{
SqlPipe sp;
sp = SqlContext.Pipe;
string InstallPath = (string)Registry.GetValue("HKEY_CURRENT_USER\\Software\\Microsoft\\Windows\\CurrentVersion\\Explorer\\Shell Folders", "Personal", null);
sp.Send(InstallPath);
}
}

Procedure and error. It is probably failing security and returning null?

Use [vc]

Create Table #t (date nvarchar (255))
Insert Into #t
Exec dbo.ReadRegKey

Drop Table #t

Msg 6522, Level 16, State 1, Procedure ReadRegKey, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "ReadRegKey":
System.ArgumentNullException: Value cannot be null.
Parameter name: message
System.ArgumentNullException:
at Microsoft.SqlServer.Server.SqlPipe.Send(String message)
at StoredProcedures.ReadRegKey()

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-18 : 23:35:28

Like I said, I seriously doubt the service account has a personal folder, hence the error. You won't be able to get the user's personal folder using SQL, SQL can't see it as it runs under the context of the SQL Server service.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources