Connection ignition to mssql valid but cannot select or insert

Ignition
1

hi all
i have an issue where ignition 8.1 shows a valid connection to mssql and ignition sql browser can see the tables list but cannot use select (error that objects are not valid). Adding a schema before table name when using select did not help.What is a little strange is that select works fine when using mssql sql browser.
I belive the issue is related to permissions.
Does anybody understands the nature of
this issue and fix?

2

Hi hewking32,
What error specifically are you seeing? Where and what are you doing that is generating this error?

3

hi
Thank you for reaching out. I was trying to run a simple insert query using sql query browser with 1 record to the remote database. The query works perpectly fine when executed to local database.
Remote database does not have dbo. schema so i had to modify my insert to include that:

INSERT INTO [external_schema].[TableNAME]...

i get message when error message saying: gatewayexception: sql error for insert.....
a result set was generated for update.

Wrapper file error says:
(<type 'java.lang.Exception'>, java.lang.Exception: Error executing system.db.runPrepUpdate(

I am confident it is permission issue but how to troubleshoot it in well planner manner ? what other diagnostic to use? what role permissions do i need?
Any pointers for me would be worth a lot :slight_smile:

4

SQL Server Management Studio is the program that lets you adjust database security.
If you don't have admin access to the database you are connecting to, you'll need to ask IT to add a user (I use Ignition as the user name) and have them grant that user datareader, datawriter, and if you turn on the historian, Ignition will require ddladmin access.

Also, this assumes that SQL is setup with SQL Server and Windows Authentication modes.

1 Like
5

Does your INSERT statement have a RETURNS clause? If so, you will have to run it as if a query, not an update.

6

Also, this assumes that SQL is setup with SQL Server and Windows Authentication modes

I am logging using SQL server authentication mode - do you think it might make a difference ?

Regards

7

hi
there is no return clause just simple insert of 1 record with 3 values
1 insert to remote db works fine in sql managment studio using the same account and code
2 insert to remote db using ignition sql browser inserts data but generates error below in the wrapper

(<type 'java.lang.Exception'>, java.lang.Exception: Error executing system.db.runPrepUpdate

3 insert to remote db using actual code does not work at all (when tags are triggered)
4 insert to local db which is exact copy of the remote table structure works without any issues
5 local copy of remote table does not include triggers which i am pretty sure are there for some validation

Could it as simple as permission issue?
Regards

8

Not sure. I'll have to let the SQL Server fans around here delve deeper.

1 Like