Problem

How do I run script sql server side


Solution

You can not run sql scripts for two main reasons:

1) Check permissions and log (using the framework classes, you always have control of who is doing what, and then allow or not to make the transactions)
2) abstraction layer (Different database engines have different syntax and functions sql)

However, you often need to execute native sql script, basically to make massive changes to data in the db, also because native sql script to run faster is to use the framework classes.
To grant a user permission to run sql script you have to go to Administration/Users and then Permissions panel add the following line:

system.NativeDb

Then in the properties below (Key/Value) add:

Key: operation
Value: all

To run the SQL script you have to go to Administration / Scripting, then select SQL type.
From here you must use the syntax of the database engine related to the instance (HSQLDB, PostgreSQL, etc..), the names of the tables are thus formed: "[packagename] [tablename]" instead of the name of the columns are "[columnname]".

Remember that once you change the permissions if the user is connected, must logout and login to take effect.

Published in Mon, 03 January 2011 18:05