Kinetic - SQL Server Linked Server

Kinetic ERP
1

Hello,
my client will move to epicor kinetic cloud very soon.
my question is if it is possible to set up a linked server in the sql server to connect the cloud database.
our salesforce has this feature to setup a linked server and connect to the cloud database. I want to know if Epicor can do that too. if so, can you please show me how to do it.

thanks,

Eddy

1 Like
2

You can NOT link to your production server. Period. Itā€™s just not secure. You CAN have a replicated server that they give some access to. Your CAM will have details.

3 Likes
3

but i saw someone suggest using https://myserver.epicorsaas.com to connect as linked server. is it possible.

also, for the secure, it means linked server to azure sql database is not secure, right?

thanks,

Eddy

4

is app server for Kinetic, not database.
For SQL server port 1433 should be accessible from outside, it is not what is usually opened.

2 Likes
5

As Olga noted, this is the address of the Application Server, not the database server.

There is nothing inherently insecure about SQL Server on prem or SQL Server/Azure SQL in the cloud. Would you expose your on-prem SQL Server directly to the Internet? :thinking: If not, why would Epicor? Moreover, there are probably more than one customer databases in a single SQL Server instance.

Thatā€™s setting aside that Linked Server queries are not optimized by SQL Server (according to Brent Ozar).

So, the Business problem is ā€œHow do I interface with Sales Force in the cloudā€ and there are others doing that. The question Iā€™m hearing is, ā€œCan I keep my previous on-prem integration with the cloud?ā€ and I would suggest probably not. Iā€™m sure that most Sales Force integrators would prefer to work with a web service vs. directly with a database. APIs would provide one with finer security controls as well.

3 Likes
6

Epicor provides a robust REST interface.

2 Likes
7

thanks, i know i can call the rest api.

now, since you raise the rest api topic, my root of the problem is how to query the cloud epicor database properly.
give you an example, i have a stored procedure. it is list for each part, how many on hand qty, how many open quotes, how many open orders, how many qty shipped, how many qty in work order and how many qty in purchase not received yet.
now, I need convert this stored procedure using cloud database. I donā€™t know how to do it.
also, in my stored procedure, I have running total calculation because user choose the date, on that date, what is the on hand qty.

so, no idea how to do that. thatā€™s why I ask if I can use linked server to do that. now, Epicor force everyone move to cloud. it cause some headache to grab the data easily and fast.

regards,

Eddy

1 Like
8

Sounds like you could create a BAQ and get its output via REST

3 Likes
9

So many options.

You can query the business objects via odata, or via custom methods.

For more advanced stuff (simple stuff too), you could design a BAQ.

If you want fine grained control on the ā€œshapeā€ of your data, use an Epicor Function.

2 Likes
10

Look at us tackling the move to cloud one post at a time! Yes, OP, @eqiu , sounds like a BAQ is in order for you.

11

And since I just referenced this today, this might be of interest to you.

ODBC ODATA:

3 Likes
12

Hello,
thanks. thatā€™s the topic I saw too. CDATA ODBC driver. i think it is the solution i can try it.

thanks,

Eddy

1 Like
13

Man you had to dig for that one, you joined in 2021ā€¦

14

Or did I ? :smiling_imp:

1 Like
15

Thatā€™s why I am trying to understand how you are so plugged in and I havenā€™t connected with you before. Am I just losing my memory or my mind?

I apologize if we have connected, and I just donā€™t remember. But I read a ton of your posts and learn a ton, I feel like I would have connected with you a lot sooner.

16

No, I really did join in 2021 :rofl:

I just had a real trial by fire here at work to get 10-11 working the way we needed it, so
I read. A lot.

I also love this stuff and have a nice case of ADHD.

5 Likes
17

Pretty impressive haha

1 Like
18

Hi Eddy,
I have the same problems like you. I have stored procedures on my sql server and now that we transition to SaaS I am scratching my head how to convert the reports. Did you finaly try CDATA ODBC? Did it work?

Vassilis

19

Whatā€™s special about these stored procedures and what are you using to report?

We can probably get you guided in the right direction if you can provide some
more info.

2 Likes
20

To give you an example, we have multiple levels of subassemblies in methods master and we use crystal reports to explode the BOM down to the leaves with raw material part numbers and check availability for each part whatā€™s in stock, open POs, demand for other jobs and so on. Although crystal reports is currently the front end of the report, on the back end it is stored procedures using Transact SQL to loop through the BOM levels and prepare the data for the report. Kind of what you see below:

while (exists (select * from #bomtemp1 with (nolock) where lvl=@levelnum))
begin
insert into #bomtemp1
select partmtl.company, partmtl.partnum, partmtl.revisionnum, partmtl.pullasasm, partmtl.viewasasm, partmtl.mtlpartnum,
@levelnum + 1, cast(partmtl.mtlseq as varchar(8)), partmtl.qtyper, #bomtemp1.nodes + ā€˜-ā€™ + partmtl.mtlpartnum
from partmtl with (nolock)
inner join #tlastrev with (nolock)
on partmtl.company = #tlastrev.company and partmtl.partnum = #tlastrev.partnum and partmtl.revisionnum = #tlastrev.revisionnum
inner join #bomtemp1 with (nolock)
on partmtl.partnum = #bomtemp1.child and partmtl.company = #bomtemp1.company
where #bomtemp1.lvl = @levelnum and #bomtemp1.viewasasm = 1 and
partmtl.partnum in (select partnum from part with (nolock) where typecode=ā€˜Mā€™)

set @levelnum=@levelnum+1

end

I have no idea how to migrate this kind of reports in SaaS Kinetic.