How to use bcp utility as process in a CLR procedure ?
-
I have an CLR project where I use the next procedure :
public static int processCMD(string fileName, string arguments)
{
try
{
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo.FileName = fileName;
proc.StartInfo.Arguments = arguments;
proc.Start();
proc.WaitForExit();
}
catch (Exception ex)
{
return 1;
}
return 0;
}
}
And the call where I use this looks like (some lines from an stored procedure):
set @execcommand = 'cssprod.BankCardtrxGPEtemp in \\WYVSTDEVCSS01\ClearingReport\KM1700903.txt -c -S WYVSTDEVCSS01\CSS_SQL01 -r -T -C 1250 -e \\WYVSTDEVCSS01\ClearingReport\bcp.error -m 1000 >NUL'
exec @result = dbo.processCMD 'bcp.exe', @execcommand
What can be the problem, because this command doesn't execute what I expect to do ?
-
when you go outside of a SQL database, SQL uses an account that you would not intuitively expect it to use . even if you are sysadmin +domain admin for your AD account, SQL does not pass those permissions to CLR or xp_cmdshell. (thou shalt not pass! {credentials})
your command is using -T for trusted AD connection, and also access a network share \WYVSTDEVCSS01\... but it is not dong it as the caller who is logged into SQL
check the account the service runs under. if it's not a real domain account, with access to that specific share, your CLR will fail.
You will want to create an account , if you don't have one already, that will run the service, and hos permissions to various disk shares.
obviously don't get lazy and put a domain admin account there! go minimum permissions, with a new account, grant access to a couple of shares, and you'll be good to go.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I set permission to UNSAFE on assembly. So, not have now execute permission problems. I have another stored procedure where I use another commands, not bcp, and there I haven't any problem.
My question this time is that how can I use bcp call as a process ?
-
levaif (9/25/2015)
this command doesn't execute what I expect to dolevaif (9/25/2015)
I have another stored procedure where I use another commands, not bcp, and there I haven't any problem. My question this time is that how can I use bcp call as a process ?Hi there. First, and most importantly, you need to tell us what the exact problem is. Saying that it does not do what you expect doesn't tell us anything useful. Saying that it has "a problem" is also not useful. You need to help us help you by providing exact error messages AND more description of what is happening.
Next, why are you using SQLCLR to call "bcp.exe" in the first place? In most cases you would be much better off using the SqlBulkCopy class (you send batches by filling up a DataTable with X rows and sending it and repeating until done), or create a TVP so that you can stream the data in as it is read from disk (I posted an example of this here: how i can insert 10 million entry in shortest time possible?).
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR -
Solomon what about the other direction for a CLR?
if i pulled data into a Datatable and want to send them to disk, how would you do it?
I've done it like this, but always looking for peer review:
i have an export where i loop through a DataTable and keep appending data in a StringBuilder, and finally writing the StringBuilder.ToString in a file.
for big exports, that seems slow to me.
how might you tackle that?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (9/25/2015)
Solomon what about the other direction for a CLR?if i pulled data into a Datatable and want to send them to disk, how would you do it?
I've done it like this, but always looking for peer review:
i have an export where i loop through a DataTable and keep appending data in a StringBuilder, and finally writing the StringBuilder.ToString in a file. for big exports, that seems slow to me.
how might you tackle that?
Hey there. I agree that the method you described above would be slow for large exports. And another negative aspect of that approach is that you are filling up memory to do it as the entire result set needs to be copied into memory before being released to disk.
But funny that you should ask since that is the exact same topic of another thread that I answered just before this one: xp_cmdshell security risk: Any other option. Essentially you need to cycle through the SqlDataReader instead of filling a DataTable. And when you cycle through the SqlDataReader, you can still do it in batches to fill up 100 or so items in a string array and then do File.AppendAllLines(string[]), or you can open a FileStream and write out each row to the file as it is read from the SqlDataReader. The latter approach is what I did in the DB_BulkExport stored procedure that I mentioned in that other thread (part of the SQL# library which I wrote, and while there is a Free version, the DB_BulkExport stored procedure is only available in the Full version).
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR -
Lowell (9/25/2015)
when you go outside of a SQL database, SQL uses an account that you would not intuitively expect it to use . even if you are sysadmin +domain admin for your AD account, SQL does not pass those permissions to CLR or xp_cmdshell.Hey there. Just to clarify something. What you are describing here is the default behavior only. Unlike with x_cmdshell, you have the option in SQLCLR, for Windows logins (not SQL Server logins) to use Impersonation to change the security context to that of the Login executing the code from T-SQL. I have a walk-through of it in the following article that shows the code and how it works: Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies)[/url]. Of course, depending on what external resource is being accessed, the Windows Login might need to be configured for Delegation, which is an Active Directory setting.
You will want to create an account , if you don't have one already, that will run the service, and hos permissions to various disk shares.
obviously don't get lazy and put a domain admin account there! go minimum permissions, with a new account, grant access to a couple of shares, and you'll be good to go.
I definitely agree here. A Windows Login should be created to run both the SQL Server process as well as the SQL Agent process. And it should have minimal rights. It should also be noted that the account does not need to even be created as a Login in SQL Server unless it actually needs to log in, and even then does not need to be placed into the "sysadmin" server role.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR -
Hi, again!
Sorry if I not formulated clear my problem. First, I am new in using CLR. I thought that it will be easily for me to call bcp utility in a process, because I have over then 30 stored procedures where I can replace xp_cmdshell call with my CRL procedure call.
I saw somewhere that I can use any kind of commands in a process call. (del.exe, bcp.exe, mkdir.exe, and also unmanaged code like third party executable files). My problem is that when I try to use bcp with arguments described above, nothing happens. If I use xp_cmdshell the import is running well.
If you can see, I use also a redirection to an error file, if something goes wrong, but even this file is empty. No error messages on execution, it seems that all goes well, but nothing is imported from my cvs file.
Is something wromg with use processes in this way ?
Or the problme is argument parameter ?
Or I can use somewhere in this argument an escape ?
-
Sorry, it's a txt file, not cvs.
As you can see
filename = 'bcp.exe'
arguments = 'cssprod.BankCardtrxGPEtemp in \\WYVSTDEVCSS01\ClearingReport\KM1700903.txt -c -S WYVSTDEVCSS01\CSS_SQL01 -r -T -C 1250 -e \\WYVSTDEVCSS01\ClearingReport\bcp.error -m 1000 >NUL'
and the method is
public static int processCMD(string fileName, string arguments)
{
try
{
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo.FileName = fileName;
proc.StartInfo.Arguments = arguments;
proc.Start();
proc.WaitForExit();
}
catch (Exception ex)
{
return 1;
}
return 0;
}
}
And the call where I use this looks like (some lines from an stored procedure):
-
Regarding your question about why I use CLR for bcp.exe. I have to automate the usage of bcp, because I have 30 stored procedures where we use this. Till now we use with xp_cmdshell, but now I want to use this CLR for this. If I use bulkcopy, I understand that I should use different methods in CLR project for all of my stored procedures where I use bcp with xp_cmdshell now, because the data structures are different from one stored procedure to other. And I donát want this, I want only one method for all my stored procedures where I have to use bcp.
-
levaif (9/28/2015)
Sorry, it's a txt file, not cvs.and the method is
public static int processCMD(string fileName, string arguments)
{
try
{
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo.FileName = fileName;
proc.StartInfo.Arguments = arguments;
proc.Start();
proc.WaitForExit();
}
catch (Exception ex)
{
return 1;
}
return 0;
}
}
A "csv" file is a type of "txt" file, so there is no important difference here.
But, there is really no easy way to help since you are swallowing the errors. Your "catch" block needs, at least for the moment to help you debug, just "throw;". Then you will at least see the error message, and that is what we need in order to help (or it might be obvious enough for you to fix).
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR -
levaif (9/28/2015)
Regarding your question about why I use CLR for bcp.exe. I have to automate the usage of bcp, because I have 30 stored procedures where we use this. Till now we use with xp_cmdshell, but now I want to use this CLR for this. If I use bulkcopy, I understand that I should use different methods in CLR project for all of my stored procedures where I use bcp with xp_cmdshell now, because the data structures are different from one stored procedure to other. And I donát want this, I want only one method for all my stored procedures where I have to use bcp.and from another post:
filename = 'bcp.exe'arguments = 'cssprod.BankCardtrxGPEtemp in \\WYVSTDEVCSS01\ClearingReport\KM1700903.txt -c -S WYVSTDEVCSS01\CSS_SQL01 -r -T -C 1250 -e \\WYVSTDEVCSS01\ClearingReport\bcp.error -m 1000 >NUL'
Doing this correctly does not require multiple stored procedures. Just write it so that you pass in the configuration that you are currently hard-coding in the call to BCP.EXE. The file path, destination table name, field and row separators, code page, server name, etc can all be parameters sent into the SQLCLR stored procedure that is marked as PERMISSION_SET = UNSAFE.Take care,Solomon...
P.S. I had originally stated that the PERMISSIONS_SET should be EXTERNAL_ACCESS, but that is incorrect. It needs to be set to UNSAFE since this stored procedure is creating an external process / thread. For help with signing the Assembly, especially if using Visual Studio, and more especially if using SQL Server 2017 (or newer), please see the following post of mine:
SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
Take care,
Solomon..SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR -
I remove try-catch block :
proc.StartInfo.FileName = fileName;
proc.StartInfo.Arguments = arguments;
proc.Start();
proc.WaitForExit();
return 2;
Now, when I run my stored procedure, still doesn't import anything. I have no error message. When I print return code in my stored procedure , it will shown value 2.
- levaif - Tuesday, September 29, 2015 12:59 AM
I'm not sure why I didn't think of this 3 years ago, but it is quite possible that the reason your code is not showing an error is due to the .NET code itself not erroring. Meaning, sometimes when calling sub-processes, if what the sub-process executes gets an error but the sub-process itself does not error, then it will return a success. For example, if "bcp.exe" returns an error, then quite likely the "Process.Start()" will not error since it did what was requested: it executed "bcp.exe". But, if you execute "bc.exe" (assuming there in no"bc.exe" in your PATH), then "Process.Start()" should return an error because it could not do what was requested.
To check for errors, you can temporarily redirect stderr, similarly to how you are currently redirecting stdout to "NUL". Just add the following to the end of the "arguments" value, just after the ">NUL":
2> C:\TEMP\BcpErrors.txt
Make sure that C:\TEMP exists and that it is writable by the service account used for the SQL Server process. Execute it again and it should capture any errors not trapped by the "-e" switch.Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply