Service Connect - select and sub select statements from from db and csv files respectively

Epicor ERP 10
1

I am try to build a dataset that has about 15,000 records, which is then compared with over 25,000 records; a further update to parts using .net adapters is then done.

Workflows and subworkflows are putting server on halt for hours without success.

I am wondering if any one know how I can accomplish the folowing.

select t1.pratnum, t1.customer, t1.rev (select t2.rev from test.csv t2 where t2.partno = t1.partnum) from part t1

The following does not work for me (not from SQL Server nor Service Connect)

SELECT * FROM OPENQUERY
(
[My-SqlDBserver],
‘Select * FROM OPENROWSET
(
‘‘MSDASQL’’,
‘‘Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=\My-fileLocation\z$\ESC\Pilot;’’,
‘‘SELECT * FROM test.csv’’
)’
)
+++++++++++++++++++++++++
My errors are as follows:

Msg 7411, Level 16, State 1, Line 1
Server ‘My-SqlDBserver’ is not configured for DATA ACCESS.
++++++++++++++++++++++++++++

This does not work for me either (not from SQL Server nor Service Connect)

select * from openrowset(‘MSDASQL’ ,‘Driver={Microsoft Access Text Driver (*.txt, *.csv)}; DBQ=\My-FileLocation\z$\ESC\Pilot’
,‘select * from “test.csv”’)

+++++++++++++++++++++++++++++
my errors are as follows:

OLE DB provider “MSDASQL” for linked server “(null)” returned message “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “(null)”.
++++++++++++++++++++++++++++++++++++++

***** Please note: actual database and file location data scrambles for security, if you wish to test the above solution in you own environment do please edit to suit.

2

Hello,
Perhaps the solution here is to go a bit old school and insert the csv records into a ud table and then perform a the tsql.

Cheers
Simon

1 Like
3

Hello Hally,

Thanks for the suggestion, I was able to do that yesterday on sql server directly, but not tried it from within service connect.

the file I need is generated daily, so that reading from the .csv will be my best option; but I should be able to make do with the old school way until a solution is found.

in case it helps any one this is the step I took.

– table with matching column count (to those of .csv) already created.

delete from dbo.myTable;

BULK INSERT dbo.myTable
FROM ‘\myPath\myCSV.csv’
WITH
(
FIRSTROW = 3,
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
) ;

select Company, PartNum, myCol1_c, myCol2_c, myCol-rel_c, myCol-rel_c,
(select rev from dbo.myTable where ItemID = t1.myCol1_c) as myCustomCol2,
(select rev from dbo.myTable where ItemID = myCol2_c) as myCustomCol3
from dbo.part t1
where t1.myCol1_c in (select ItemID from dbo.myTable) or t1.myCol2_c in (select ItemID from dbo.myTable);

I had to conclude my research into a more concise way of achieving my desired result and introduce SQL Server Schedule to auto populate table with CSV records and handle the other processes with SC DbOperation, etc.

4

The following works for me (in SSMS), pulling records out of .csv without the need to “bulk insert” into db table. But file not found/location not accessible from Service Connect “DBOperation select statement”.

– Note: myTable already created in database as a template for bcp to create format file.

bcp myTable format nul -c -x -f targetFormat.xml -t, -F2 -T ---- this command in command prompt

SELECT a.* FROM OPENROWSET( BULK ‘fileLocation\myCsvRecs.csv’,
FORMATFILE = ‘fileLocation\targetFormat.xml’) as a;

Please help with Service connect DBOperation, I need my select statement to be able to read from file system or any useful way of accomplishing my task.

5

Ended up doing the following for efficiency.

select * from
OPENDATASOURCE(‘SQLNCLI’, ‘Data Source=myDB;Initial Catalog=mySchema;User ID=myUser;Password=myPwd’).mySchema.dbo.myTable

The previous post will be the best if you do not have access to the other Database.

1 Like