Executing an external process() in SQLCLR Project

I can create an external text file from within the SQLCLR project, but I cannot run an external executable. Just in case you are asking, I need to do this to push data into a legacy application using a different DB format. I have found it best to simply use my old language (Clipper) for data validation, etc. - and especially since I require multiple indices to be open. So, if you could just take my word on this.

The following code:

Process newProcess = new Process();

string path = @."C:\TEST.BAT";

newProcess.StartInfo.FileName = path;

newProcess.Start();

Executes without error, but does not actually run the external.

Now, I can have a DOS (Clipper) application poll a directory for text files, but I am trying to get away from all these "mini" data transformation applications. If an exception is caused in the DOS app, a remote user on the other side of the country has no idea it is broke and his data (coming from a SQL Mobile Device) never gets to our legacy database structure.

So, am I out of luck?

Is your assembly deployed at the UNSAFE permission level? What happens if you try to run the same code in a console app run under the same user account (SQL Server service account or impersonated account, as appropriate)?|||

Hi Nicole - thx for responding.

I am able to execute a console app - the issue is that SQL Server will not allow touching any network drive or network resource period (from a SQLCLR Project). So my cmd app can update a DB on the C drive, but not one of my network drives.

As well, I can establish an OLEDB connection (Visual Foxpro) to a local directory, but not a network directory. I'm surprised that CLR will not even allow an external app to touch a network resource. Wild, eh? Although I understand why there is such security, there must be a way for me to execute an external console app that can update a DB on a network resource.

Oh - and yes, I am running the assembly at the UNSAFE permission level.

|||Might the problem be the user context rather than anything having to do with SQLCLR? Unless you deliberately impersonate another user (e.g.: via SqlContext.WindowsIdentity.Impersonate), your SQLCLR code will run under the user context of the SQL Server service account, which is highly unlikely to have any permissions on any network resources.|||

I think you're correct - it has to do with attempting to authenticate a local user (IUSR_Computername) on a network resource. I will look into impersonation (something my wife says I'm horrible at...)

You know, Nicole, you are the first person to assist me in these forums. I had actually thought of using a female handle - seems they get a pretty quick response... :)

I'll post the results of my efforts... and thx again.

|||

After impersonation, I can do something simple like use a streamwriter to create a text file on a network share. But it still throws an exception when attempting to execute an executable on the same network share. The impersonation rights are those of administrator (only for testing!). Oh, in case you're wondering - I simply change the Process StartInfo from @."C:\Mobile.exe" to @."F:\Mobile.exe", and you will have to take my word for it that the Mobile exists in both places. It runs without exception on the C drive.

What's really wild, is that the actual Mobile.exe code (clipper) will update a dbf on the C drive, but not on the F drive. What does the CLR do? Freeze all resources when in process? geez!

I'm also using:

[PermissionSetAttribute(SecurityAction.Demand, Name = "FullTrust")]

So... I really thought this would allow my CLR code to access a network share. I can't understand why the answer to this wouldn't be just a little more simple, even for an old programmer like me.

ExecuteXmlReader error code 0x80040E21

Hi,
I can't seem to do anything with sqlxml from the .net managed classes
without getting this error. This a simple test case I've been running
from a console app...
string err;
try
{
string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
SqlXmlCommand cmd = new SqlXmlCommand(connstr);
cmd.CommandText = "select product_id, name_display from tc_products
FOR XML AUTO";
XmlReader xr = cmd.ExecuteXmlReader();
catch (SqlXmlException e)
{
e.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(e.ErrorStream);
err = errreader.ReadToEnd();
errreader.Close();
}
It enters into the catch block but the error is always blank.
I don't ever see anything in sql profiler. Both columns in the select
are char types. The sql runs ok in query analyzer.
I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
versions I have...
sqlxml (sp3) - 3.2.2917.0
vs.net - 7.0.9466
..net framework - 1.0.3705
Thanks,
Scott
Hi Scott,
Is there some information in e.Message? There should be details about the
error either in the exception message or in the error stream.
Thank you,
Amar
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running
> from a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products
> FOR XML AUTO";
> XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select
> are char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
> versions I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>
|||e.Message = "Exception from HRESULT: 0x80040E21."
The e.ErrorStream length is 0. The message is blank.
I also checked my mdac version. It's 2.8 sp1.
Scott
Amar Nalla [MS] wrote:
> Hi Scott,
> Is there some information in e.Message? There should be details about the
> error either in the exception message or in the error stream.
> Thank you,
> Amar
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
>
|||Can you try the below:
ComException ce;
ce = (COMException) ex.InnerException();
ce.ErrorCode; //This will give you the error code.
ce.Message; //This will give you the error message
You will need to include
using System.Runtime.InteropServices
I am not sure why you don't see more details in the error stream and the
outer exception. If you see the actual error using this method then please
let me know as I would like to investigate why this error is not visible
otherwise.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:OF$SY8m7EHA.2032@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> e.Message = "Exception from HRESULT: 0x80040E21."
> The e.ErrorStream length is 0. The message is blank.
> I also checked my mdac version. It's 2.8 sp1.
> Scott
>
> Amar Nalla [MS] wrote:
the[vbcol=seagreen]
|||Root is missing in your SQL. Try,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO, elements, root('a')";
instead of,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO";
Pohwan Han. Seoul. Have a nice day.
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running from
> a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO"; XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select are
> char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other versions
> I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>
|||root() in FOR XML does not work in SQL 2000 (only 2005).
However, there should be a root property on the provider or you need to fake
it using a select '<a>' select '</a>' before and after the command...
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Root is missing in your SQL. Try,
>
> instead of,
>
> --
> Pohwan Han. Seoul. Have a nice day.
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
|||Really. By the way, that reminds me of dogs trying to bite their own tails.
IIRC, multiple selects with ExecuteXmlReader, not legacy ADO stream, doesn't
work. Tomorrow in my office I will check that again.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eEgIJkd8EHA.2900@.TK2MSFTNGP09.phx.gbl...
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to
> fake it using a select '<a>' select '</a>' before and after the
> command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
|||Drilling down on the com exception in the debugger, I also noticed a
com+ exception code and a stack trace. I tried error lookup on the code
but it wasn't found.
_COMPlusExceptionCode-532459699int
StackTrace" at
Microsoft.Data.SqlXml.Common.ISQLXMLCommandManaged Interface.ExecuteToOutputStream()
at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(S tream strm)"string
Scott Walters wrote:[vbcol=seagreen]
> I tried this...here are the results.
> ErrorCode -2147217887 int
> ce.Message "Exception from HRESULT: 0x80040E21." string
>
> Amar Nalla [MS] wrote:
|||I tried setting the RootTag prop on the command object. That didn't
make any difference. I also attempted to try what you suggested but
wasn't sure I really understood it. I tried it with the sql cmds below.
Is that what you meant?
cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
tc_products FOR XML AUTO";
and...
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '<a>';";
Michael Rys [MSFT] wrote:
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to fake
> it using a select '<a>' select '</a>' before and after the command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>
|||I meant the second one
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '</a>';";
But as Han observed, it may be that the ExecuteXMLReader does not allow such
streamed statements (the OLEDB and ADO command streams allowed it).
If you set the RootTag prop, are you getting the correctly formatted XML
back (single root node)?
Best regards
Michael
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23TFNyIo8EHA.1228@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
>I tried setting the RootTag prop on the command object. That didn't make
>any difference. I also attempted to try what you suggested but wasn't sure
>I really understood it. I tried it with the sql cmds below. Is that what
>you meant?
>
> cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
> tc_products FOR XML AUTO";
> and...
>
> cmd.CommandText = "select '<a>'; select product_id, name_display from
> tc_products FOR XML AUTO; select '<a>';";
> Michael Rys [MSFT] wrote:
sql

ExecuteXmlReader error code 0x80040E21

Hi,
I can't seem to do anything with sqlxml from the .net managed classes
without getting this error. This a simple test case I've been running
from a console app...
string err;
try
{
string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
SqlXmlCommand cmd = new SqlXmlCommand(connstr);
cmd.CommandText = "select product_id, name_display from tc_products
FOR XML AUTO";
XmlReader xr = cmd.ExecuteXmlReader();
catch (SqlXmlException e)
{
e.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(e.ErrorStream);
err = errreader.ReadToEnd();
errreader.Close();
}
It enters into the catch block but the error is always blank.
I don't ever see anything in sql profiler. Both columns in the select
are char types. The sql runs ok in query analyzer.
I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
versions I have...
sqlxml (sp3) - 3.2.2917.0
vs.net - 7.0.9466
.net framework - 1.0.3705
Thanks,
ScottHi Scott,
Is there some information in e.Message? There should be details about the
error either in the exception message or in the error stream.
Thank you,
Amar
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running
> from a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products
> FOR XML AUTO";
> XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select
> are char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
> versions I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>|||e.Message = "Exception from HRESULT: 0x80040E21."
The e.ErrorStream length is 0. The message is blank.
I also checked my mdac version. It's 2.8 sp1.
Scott
Amar Nalla [MS] wrote:
> Hi Scott,
> Is there some information in e.Message? There should be details about t
he
> error either in the exception message or in the error stream.
> Thank you,
> Amar
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
>
>|||Can you try the below:
ComException ce;
ce = (COMException) ex.InnerException();
ce.ErrorCode; //This will give you the error code.
ce.Message; //This will give you the error message
You will need to include
using System.Runtime.InteropServices
I am not sure why you don't see more details in the error stream and the
outer exception. If you see the actual error using this method then please
let me know as I would like to investigate why this error is not visible
otherwise.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:OF$SY8m7EHA.2032@.tk2msftngp13.phx.gbl...
> e.Message = "Exception from HRESULT: 0x80040E21."
> The e.ErrorStream length is 0. The message is blank.
> I also checked my mdac version. It's 2.8 sp1.
> Scott
>
> Amar Nalla [MS] wrote:
the|||Root is missing in your SQL. Try,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO, elements, root('a')";
instead of,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO";
Pohwan Han. Seoul. Have a nice day.
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running from
> a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO"; XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select are
> char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other versions
> I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>|||root() in FOR XML does not work in SQL 2000 (only 2005).
However, there should be a root property on the provider or you need to fake
it using a select '<a>' select '</a>' before and after the command...
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Root is missing in your SQL. Try,
>
> instead of,
>
> --
> Pohwan Han. Seoul. Have a nice day.
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>|||Really. By the way, that reminds me of dogs trying to bite their own tails.
IIRC, multiple selects with ExecuteXmlReader, not legacy ADO stream, doesn't
work. Tomorrow in my office I will check that again.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eEgIJkd8EHA.2900@.TK2MSFTNGP09.phx.gbl...
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to
> fake it using a select '<a>' select '</a>' before and after the
> command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>|||Drilling down on the com exception in the debugger, I also noticed a
com+ exception code and a stack trace. I tried error lookup on the code
but it wasn't found.
_COMPlusExceptionCode -532459699 int
StackTrace " at
Microsoft.Data.SqlXml.Common.ISQLXMLCommandManagedInterface.ExecuteToOutputS
tream()
at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(Stream strm)" string
Scott Walters wrote:
> I tried this...here are the results.
> ErrorCode -2147217887 int
> ce.Message "Exception from HRESULT: 0x80040E21." string
>
> Amar Nalla [MS] wrote:
>|||I tried setting the RootTag prop on the command object. That didn't
make any difference. I also attempted to try what you suggested but
wasn't sure I really understood it. I tried it with the sql cmds below.
Is that what you meant?
cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
tc_products FOR XML AUTO";
and...
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '<a>';";
Michael Rys [MSFT] wrote:
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to fa
ke
> it using a select '<a>' select '</a>' before and after the command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>|||I meant the second one
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '</a>';";
But as Han observed, it may be that the ExecuteXMLReader does not allow such
streamed statements (the OLEDB and ADO command streams allowed it).
If you set the RootTag prop, are you getting the correctly formatted XML
back (single root node)?
Best regards
Michael
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23TFNyIo8EHA.1228@.tk2msftngp13.phx.gbl...
>I tried setting the RootTag prop on the command object. That didn't make
>any difference. I also attempted to try what you suggested but wasn't sure
>I really understood it. I tried it with the sql cmds below. Is that what
>you meant?
>
> cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
> tc_products FOR XML AUTO";
> and...
>
> cmd.CommandText = "select '<a>'; select product_id, name_display from
> tc_products FOR XML AUTO; select '<a>';";
> Michael Rys [MSFT] wrote:

Execute SSIS Package in C# (ErrorMessage ?)

Hi all

I am executing a SSIS Package in C# (2005).
Does anybody know how to find out, in case of an error, the
error message?

I am doing the follwoing:
Microsoft.SqlServer.Dts.Runtime.Package local_Package = new Microsoft.SqlServer.Dts.Runtime.Package();
Microsoft.SqlServer.Dts.Runtime.DTSExecResult local_DTSExecResult = new Microsoft.SqlServer.Dts.Runtime.DTSExecResult();
Microsoft.SqlServer.Dts.Runtime.Application local_Application = new Microsoft.SqlServer.Dts.Runtime.Application();

local_Package = local_Application.LoadPackage("c:\temp\ssis_package.dtsx", null);
local_DTSExecResult = local_Package.Execute();

if (local_DTSExecResult == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
Display MessageBox of the error message ...
}

Thanks for any comment.

Best regards
Frank Uray

Try reading the SSIS system variable like

local_Package.Variables["ErrorDescription"].value.ToString()

inside the IF condition.

Thanks,
Loonysan

|||

Hi all

I have found out the solution ... :-)

if (local_DTSExecResult == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in local_Package.Errors)
{MessageBox.Show(local_DtsError.Description);}
}

Best regards
Frank Uray