SP errors out when attempting to pass in var to a Console app that receives as an argument

I have the following SP below that I'm attempting to use to call a Console app and pass a variable that contains a list of workstations. The console app works like this --> "ProofOfConcept -f c:data.txt". So, currently it takes a Filename, that gets passed into it, stored in a variable in the Console app, stores workstations in array to loop through, Ping and store Status's in DB. Someone else on our team will pass all the workstations to a variable in the SP called RunPingUtil. Please see code below. I've also copied error output that may help. I've been working on this one all day and could really use another set of eyes. :)
Thanks Experts,
Wally
------------------------------------ SP RunPingUtil -----------------------------------------------
USE [DMS]
GO
/****** Object:  StoredProcedure [dbo].[RunPingUtil]    Script Date: 04/03/2009 09:34:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER Procedure [dbo].[RunPingUtil]
AS
BEGIN
	SET NOCOUNT ON;
 
DECLARE @Workstations varchar(max)
SET @Workstations = 'B001617259BA7,B001E371E21B2,B001422C95811'
 
DECLARE @CommandLine sysname;
SET @CommandLine = '\\B001C231FC4CC\c$\Test\ProofOfConcept.exe "' + @Workstations + '"';
 
EXEC xp_cmdshell @CommandLine;
 
END
---------------------------- ERRORS When executing SP -------------------------------------
NULL
Unhandled Exception: System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
   at System.Security.CodeAccessPermission.Demand()
   at System.Environment.GetCommandLineArgs()
   at ProofOfConcept.Program.Main()
The action that failed was:
Demand
The type of the first permission that failed was:
System.Security.Permissions.EnvironmentPermission
The first permission that failed was:
<IPermission class="System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Read="Path"/>
NULL
The demand was for:
<IPermission class="System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Read="Path"/>
NULL
The granted set of the failing assembly was:
<PermissionSet class="System.Security.PermissionSet"
version="1">
<IPermission class="System.Security.Permissions.FileDialogPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Access="Open"/>
<IPermission class="System.Security.Permissions.IsolatedStorageFilePermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Allowed="ApplicationIsolationByUser"
UserQuota="512000"/>
<IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Flags="Execution"/>
<IPermission class="System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Window="SafeTopLevelWindows"
Clipboard="OwnClipboard"/>
<IPermission class="System.Security.Permissions.UrlIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Url="file://B001C231FC4CC/c$/Test/ProofOfConcept.exe"/>
<IPermission class="System.Security.Permissions.ZoneIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Zone="Internet"/>
<IPermission class="System.Drawing.Printing.PrintingPermission, System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
version="1"
Level="SafePrinting"/>
</PermissionSet>
NULL
The assembly or AppDomain that failed was:
ProofOfConcept, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
The method that caused the failure was:
Void Main()
The Zone of the assembly that failed was:
Internet
The Url of the assembly that failed was:
file://B001C231FC4CC/c$/Test/ProofOfConcept.exe
NULL

Open in new window

wally_davisAsked:
Who is Participating?
 
wally_davisConnect With a Mentor Author Commented:
Gentleman, I finally got it figured out.
1. Added ProofOfConcept.exe file to local SQL Server for local processing.
2. Added the data.txt file from which it would get passed to the EXE file
3. I had to use local Drive path vs UNC Path
and that pretty much did the trick. I do appreciate your help guys.
0
 
ralmadaCommented:
It doesn't seem to be  a problem with the stored procedure. Please check this link:
http://social.technet.microsoft.com/Forums/en-US/sqlnetfx/thread/1505d7e6-59dc-4997-b707-9e9736a1f978/ 
0
 
ZberteocCommented:
Why are you using sysname as type for the @CommandLine variable in the sp? Use varchar(max) instead.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
wally_davisAuthor Commented:
1. I couldn't change the @CommandLine to use the varchar(max) variable. I used the sysname because that appeared to be the recommendation when passing in a Path to an executable plus the variable being passed to it to get a proper execution. I did try it but received the following error: "Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'". So I left the type at sysname.
2. I made some changes to my code to accept an input of a variable versus a text file. I then attempted to run the SP again and this time I got the error " Output = Access is Denied" and Null. Return value was 0". So I'm one step closer.
3. As a last resort, I enabled "ClickOnce Security Settings" to run the Console app as a full trust application but that didn't help either.
Anyone else have any suggestions on why I can't pass the contents of the variable and why I'm  getting "Access is Denied".
0
 
wally_davisAuthor Commented:
FYI, I tried varchar(255) in place of "sysname" and I got the same error (Access is Denied). for whatever reason, I can't pass the @CommandLine to the xp_cmdshell with type varchar(max) and varchar(255) is not going to be enough for the amount of workstation names I want to pass in to be pinged. Somewhere between the neighborhood of 20,000 to 40,000 machines will need to be processed.
0
 
wally_davisAuthor Commented:
I've also verified that the xp_cmdshell is enabled on the SQL Server in question when I execute the xp_cmdshell within the SP.
0
 
ZberteocCommented:
<<"Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'". So I left the type at sysname.>>

I don't understand, the error states clearly "Procedure expects parameter 'command_string' of type 'varchar'" but you go on with "So I left the type at sysname" !!!

Change the type to varchar(max) or insetad of (max) you can use a speciffic value like (8000). In SQL 2000 the maximum size for varchar was 8000 in 2005 you should be able to use bigger that than or (max) without problems:

DECLARE @CommandLine varchar(max);
0
 
ZberteocCommented:
On the other hand you have to understand that the comand line in dos/windows itself cannot be of any length as it is limmited to 255 most likely.  Adding a parameter,@Workstations, of bigger length won't help.
0
 
ralmadaCommented:
It is not a problem of parameter type. either varchar or sysname will work. (I've tested and double tested this in my environment). It is a problem with this program "ProofOfConcept.exe". I'm just wondering if you were able to take a look at the link I've mentioned in my previous comment.
0
 
ralmadaCommented:
Also, do you have xp_cmdshell enabled?. By default this sp is disabled in SQL 2005. Run the attached script and then try you code.
 


-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Open in new window

0
 
wally_davisAuthor Commented:
I've checked everything you guys have provided and tested it several times over but to no avail.
ProofOfConcept.exe works great. When I add a list of workstations within the Properties/Debug/Command line arguments textbox, the code works. I've also passed in a text file to this EXE and it works. However, I do see where when I run the SP after a couple changes per your suggestion, i.e. varchar(255) , I get "Output = NULL'.
Ralmada, the link you sent me was for changes to an ASP.Net application. I have a console application. My question is, where within VS 2008 to I make the neccessary changes to the code to see if those changes via that link might help?
0
 
wally_davisAuthor Commented:
FYI, yes, xp_cmdshell is enabled. I used the Surface Config Features util to verify.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.