Is there any other way apart from Database connector so I can retive records from Database ?
You can retrieve records from below components using db connector profile.
> Database Connector
>Set Properties - Create variable based on parameter value in the statement
>SQL-Lookup (Map Functions) - Execute a static or dynamic SELECT query against a database. Typically used to perform cross-reference lookups or obtain supplemental data.
>Program Command - Execute a database SQL action (INSERT, UPDATE, DELETE) Queries are not supported
>Decision - True of false output based on parameter value in the statement
SELECT COUNT(1) FROM SOME_TABLE WHERE ID=?
>Route - Filtering data based on parameter value in the statement.
I'm getting a permissions error on the invoke of the SQL. Do you know how to fix that?
Please try to run the SQL statements using the same credentials thru a SQL client tool first. If that works, it should also work from Boomi as well. A permission error could indicate you miss certain access rights to the database objects or operations.
SQL login = success
AD login = failure
In Boomi there are now 2 connectors under #Connections.
The SQL servers are on non-standard ports.
SQL Dev Connector (Axdsql01) is on port 62587
SQL PROD connector (Axpsql01) is on port 50533
When I use the non-Active Directory ID (SQL login) of SSRS, the connection is successful (both Dev and Prod).
When I use the active directory login of boomiprod or the domain qualified serv-u-success\boomiprod OR lwestcott
I see this error.
Unable to connect to database; Caused by: Unable to open database connection. Login failed for user 'boomiprod'. ClientConnectionId:2cf7202a-3e3d-4d31-b431-85085de7168d; Caused by: Login failed for user 'boomiprod'. ClientConnectionId:2cf7202a-3e3d-4d31-b431-85085de7168d
Unable to connect to database; Caused by: Unable to open database connection. Login failed for user 'lwestcott'. ClientConnectionId:a7cfa880-b1d4-4d37-b0d3-b0b60ce7476b; Caused by: Login failed for user 'lwestcott'. ClientConnectionId:a7cfa880-b1d4-4d37-b0d3-b0b60ce7476b
I don’t think the firewall port is an issue.
I’m thinking that with all my privileges on SQL server; if my ID can’t get through there’s another issue. But I don’t see it as yet.
Retrieving data ...