BCP export file to folder on another server

[rbross]

Hello,

Using AwareIM v8.7 and SQL server.
Currently, we have AwareIM and SQL Express on the same server.
We are running several BCP processes to export data to CSV files and have a symbolic link pointing to a folder on the server.
All is working fine.

Getting ready to transfer to a new server where things are configured a bit differently.

AwareIM is on one VM (let's call it VM01)
SQL Server is on another VM (let's call it VMSQL01)

When a BCP process runs now, we get Error #2, The system cannot find the file specified.

We have a share for VMSQL01 to the folder on VM01 where the file should be saved.
If I run the complete BCP string from a CMD prompt on VMSQL01 it does work and the file is saved in the folder on VM01.
When I run it from AwareIM on VM01 that's when I am getting the error.
I tried adding -S and -d to the string for the server name and database but that did not change anything.

These are the rules for the process.

1.Call sproc to build the file that will be exported

EXEC_SP 'sproc_Rpt_DeferredIncomePivot' WITH '@LIRUID'=LoggedInRegularUser.ID, '@BatchNo'=AppSessionMem.BatchNo
COMMIT TRANSACTION

2. Create TempBCP string

CREATE TempBCP WITH TempBCP.LIRUID= LoggedInRegularUser.ID, TempBCP.LinkToCSV=SystemSettings.BusinessSpaceName+'/'+'DeferredIncomePivotRpt_'+AS_STRING(AppSessionMem.BatchNo)+'_'+AS_STRING(AppSessionMem.BatchDate)+'.CSV',
TempBCP.BCPCommand=`bcp `+`~Select t.YearNo, t.BatchDate, t.BatchNo, t.PaymentMethod, t.January, t.Febuary, t.March, t.April, t.May, t.June, t.July, t.August, t.September, t.October, t.November, t.December, t.Total FROM ` +SystemSettings.DBName+`.dbo.TMP_DEFERREDINCOMEPIVOT t` +`~`+` queryout `+ SystemSettings.ClientDirectory+`DeferredIncomePivotRpt_`+AS_STRING(AppSessionMem.BatchNo)+'_'+AS_STRING(AppSessionMem.BatchDate)+`.CSV`+` -c -t, -T`

3. Clean string and replace '~' with double quotes

TempBCP.BCPCommand=REPLACE_PATTERN(TempBCP.BCPCommand,'~',SystemSettings.DQ)

4. EXEC PROGRAM

EXECUTE PROGRAM TempBCP.BCPCommand NO WAIT

5. Display Query to download file

DISPLAY LinkToCSV_DeferredIncomePivot_Rpt


../Tomcat/webapps/AwareIM/TOMA/ is the path set in an attribute in SystemSettings.ClientDirectory BO that points to the symbolic Link folder on VM01

all the rules in the process execute fine, the error occurs in the rule, EXECUTE PROGRAM TempBCP.BCPCommand NO WAIT

Any idea's???

[Jaymer]

no idea, but I esp. like this part:
TempBCP.BCPCommand=REPLACE_PATTERN(TempBCP.BCPCommand,'~',SystemSettings.DQ)

I had to use a lot of " (double quotes) when making Emails for GMAIL API.
I didn't want to pollute the code with SystemSettings.DQ

I used a ~ symbol in exact place for "

Then used a REPLACE_PATTERN just before I did the REST call to switch all them to "