Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Dunners73  
#1 Posted : Thursday, September 10, 2015 8:29:49 AM(UTC)
Dunners73

Rank: Member

Groups: Registered
Joined: 8/28/2015(UTC)
Posts: 10
United Kingdom
Location: London

Thanks: 1 times
Hi There,

Is there a reference guide or more information available about how inaport uses SQL and what syntax / keywords are or are not allowed & which connectors they might work with.

I havent been able to use aliases or joins so far - when querying a CRM source.

Thanks In advance

Russell / Non Profit / IT Dept / UK Based


Russell / Non Profit / IT Dept / UK Based
ipadmin  
#2 Posted : Thursday, September 10, 2015 2:29:26 PM(UTC)
ipadmin

Rank: Administration

Groups: Administrators
Joined: 8/17/2015(UTC)
Posts: 22

Was thanked: 2 time(s) in 2 post(s)
Hi Russell

The answer depends on which connector type you are using.

In general, Inaport does not actually interpret the SQL at all - it passes the SQL through to the underlying database. The means, for example, if you are querying SQL Server, you can do any SQL that SQL server supports.

With Excel, the SQL does have some unusual requirements: in particular, a worksheet is surrounded by the backward quote character '`' and ends with a '$', so you have:

Code:
select field1, field2 from `worksheet$`


One enhancement Inaport does give you is that #fieldname in a SQL select will be replaced with the value of the field. So if you have a field called 'myfield' with a value of 'abc', you can do this:

Code:
dbselect("T", "select field1 from table where field2 = '#myfield' ")


The #myfield will be replaced with the value of the field, so the SQL that gets passed to the target is:

Code:
select fiedl1 from table where field2 = 'abc'


Microsoft CRM

The MSCRM web service API does not actually support SQL. Under the hood, Inaport translates what you enter as a SQL statement into a web service call.

The implication is that you can only use relatively simple SQL SELECT statements: single table (no joins), no aliases.

The where clause supports =, <>, like, not like, null, not null, and parentheses.

So some sample SQL statements would be:

Code:
select field1, field2 from table where field3 = 'aaa'

select * from table where (field1 = 'aaa') and (field2 not like 'bb%')



HTH. Let me know if you have any other questions.
Dunners73  
#3 Posted : Friday, September 11, 2015 8:16:43 AM(UTC)
Dunners73

Rank: Member

Groups: Registered
Joined: 8/28/2015(UTC)
Posts: 10
United Kingdom
Location: London

Thanks: 1 times
Originally Posted by: ipadmin Go to Quoted Post
Hi Russell

The answer depends on which connector type you are using.

In general, Inaport does not actually interpret the SQL at all - it passes the SQL through to the underlying database. The means, for example, if you are querying SQL Server, you can do any SQL that SQL server supports.

....

The MSCRM web service API does not actually support SQL. Under the hood, Inaport translates what you enter as a SQL statement into a web service call.

The implication is that you can only use relatively simple SQL SELECT statements: single table (no joins), no aliases.

The where clause supports =, <>, like, not like, null, not null, and parentheses.

...

HTH. Let me know if you have any other questions.


Ok

So I think, if the query was complex enough, it could be worth using the "Microsoft SQL Server" connector type and reading from the "filtered views" to extract the data instead of using a CRM source connector. If writing back into CRM isnt a concern, is there any notable downside to that approach ? ie. anything you cannot do with a sql connector source that you would normally be able to with a crm datasource. ? (i know this would require an on premise install of crm)

Russell / Non Profit / IT Dept / UK Based
ipadmin  
#4 Posted : Friday, September 11, 2015 8:46:17 AM(UTC)
ipadmin

Rank: Administration

Groups: Administrators
Joined: 8/17/2015(UTC)
Posts: 22

Was thanked: 2 time(s) in 2 post(s)
Hi Russell

Reading from MSCRM using SQL is fine, and using the filtered views makes it a good approach.

Absolutely *not* recommended to write to MSCRM using a SQL connector.

I believe Microsoft will void support if they find out you have done it.

Regards

Support
jerrythomasnyk  
#5 Posted : Monday, October 19, 2015 11:16:53 PM(UTC)
Guest

Rank: Guest

Groups: Guests
Joined: 8/17/2015(UTC)
Posts: 2

Originally Posted by: Dunners73 Go to Quoted Post
Hi There,

Is there a reference guide or more information available about how inaport uses SQL and what syntax / keywords are or are not allowed & which connectors they might work with.

I havent been able to use aliases or joins so far - when querying a CRM source.

Thanks In advance

Russell / Non Profit / IT Dept / UK Based



Hi Russell,

I am also searching the answer of this question from many times.



ipadmin  
#6 Posted : Tuesday, October 20, 2015 7:50:28 AM(UTC)
ipadmin

Rank: Administration

Groups: Administrators
Joined: 8/17/2015(UTC)
Posts: 22

Was thanked: 2 time(s) in 2 post(s)
Hi Jerry

Did the above information help, or do you have further questions?

Regards

InaPlex
davidsmith  
#7 Posted : Thursday, November 10, 2016 3:06:34 AM(UTC)
davidsmith

Rank: Newbie

Groups: Registered
Joined: 11/10/2016(UTC)
Posts: 1
United Kingdom

Thanks its worked.
dissertationtutors  
#8 Posted : Tuesday, February 21, 2017 10:49:49 PM(UTC)
Guest

Rank: Guest

Groups: Guests
Joined: 8/17/2015(UTC)
Posts: 2

Thanks ipadmin for giving us useful information.
rjames90  
#9 Posted : Friday, February 24, 2017 3:32:27 AM(UTC)
rjames90

Rank: Newbie

Groups: Registered
Joined: 2/24/2017(UTC)
Posts: 1
United Kingdom
Location: Leicester

Thanks a lot. it work for me
Users browsing this topic
Guest
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Powered by YAF | YAF © 2003-2017, Yet Another Forum.NET
This page was generated in 0.407 seconds.

Notification

Icon
Error