ShellExecute the SQLServer BCP Utility does not work with large lpPar

[JoseNADP]

ShellExecute the SQLServer BCP Utility does not work with large lpParams

Hi all,

What I'm trying to do is the following:
(BCP Utility is a SQLserver program to import and export data to/from a SQLServer database).

ShellExecute(0, 'Open', PChar('<path>\bcp.exe'), PChar(<some big querry string>), PChar(''), SW_Hide);

The value of <some big querry string> is something like this:
"SELECT A.DVB_CODE as 'personeelsnummer', B.L12_K_VERKVAKURE as 'Urenverkocht', B.L12_K_AANKOOPVAK as 'Urengekocht', CONVERT(char(10), B.L12_begindatum, 120) as 'datum'
FROM
SOMEDATABASE_MS..PII_DIENSTVERBND A, SOMEDATABASE_MS..LCE_GROEP012 B
WHERE B.DVB_OBJECTID = A.DVB_OBJECTID_ORG AND A.DVB_BEGINDATUM < getdate() AND A.DVB_EINDDATUMTOT >= getdate() AND B. L12_EINDDATUMTOT = (SELECT MAX(L.L12_EINDDATUMTOT) FROM SOMEDATABASE_MS..LCE_GROEP012 L WHERE L.DVB_OBJECTID = B.DVB_OBJECTID GROUP BY L.DVB_OBJECTID) ORDER BY 1"
queryout "C:\Cafetaria.CSV" -c -t; -S<SOMASERVER> -U<SOMEUSER> -P<SOMEPASSWORD> -o"C:\Cafetaria.LOG"

So the Params are:
1 the query itself, inclosed by "
2 queryout with path and file
3 -c -t;
4 -S -U -P
5 -o path and name log file

First I make sure all linebrakes are removed.
When I run this in Delphi it wors fine, but when I change the Query part so it gets bigger I get error 5 (Access is denied). One of the queries I have to use is bigger.
As a test I copy pasted the where part in the above query and got the error.

Well you guess it. Has anyone any idea? The TShellExecute params are AnsiString and allowed to be up to 2 GB in size according to Delphi. My queries are not even close to that size (2.14 Kb).
Any help would be appreciated.

Bom dia/Greatings,

Jose

 

[DjangMan]

Could you put your query into a temporary text file and reference the path to that text file with a command line argument?

 

[JoseNADP]

No thats not possible in this case with the BCP Utility... if I wanted to import then yes.

 

[JoseNADP]

BCP - Take a look at:

http://msdn.microsoft.com/en-us/library/ms162802.aspx

 

[smays]

Perhaps I am inadvertently echoing DjangMan,... Can the BCP Utility be run from a batch file (*.bat)? If so, you could generate a text file at run-time, give it a *.bat extension, and run the batch file using the ShellExecute function. You could experiment using a batch file without using Delphi and after you settle on the structure of the batch file, you could wrap code around the process of building the batch file.

Steve.

 

[JoseNADP]

Well the thing is that you can run the BCP from a command window. I did this by opening a command window adding BCP after the prompt and then copy and paste the big query with all of its params after the BCP and pressing Enter. It works like a charm then. So the problem seems to be TShellExecute in Delphi 2005 or in the Windows XP Shell API that cant handle big params. Strange since its AnsiString as I stated before.
If this can be resolved or workaround whitout me changing to much of my application I then would prefer that. Your solution with a batch file would probably work but is not my first choice.

 

[JoseNADP]

Well guys and girls, I did the batchfile option and offcorse that works. I had made objects anyways so it was not that much work changing things so it made and used batch files.

This thread is solved but the TShellExecute not working with large params I guess is not.

Thanks for your input.

Bom dia/Greatings,

Jose