# Error while inserting data to MSSQL

**URL:** <https://forum.inductiveautomation.com/t/error-while-inserting-data-to-mssql/78327>\
**Category:** Ignition\
**Tags:** database, vision, ignition81\
**Created:** [August 24, 2023, 11:19am UTC](https://forum.inductiveautomation.com/t/error-while-inserting-data-to-mssql/78327 "2023-08-24T11:19:41Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Henrol\_Jeo\_Gutierrez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/henrol_jeo_gutierrez/32/50993_2.png) [@Henrol\_Jeo\_Gutierrez](https://forum.inductiveautomation.com/u/Henrol_Jeo_Gutierrez)\
**Post date:** [August 24, 2023, 11:19am UTC](https://forum.inductiveautomation.com/t/error-while-inserting-data-to-mssql/78327/1 "2023-08-24T11:19:41Z")

</div>

Hi,

I am trying to insert data using an script in Vision, using preview mode I can successfully run the script and I can insert data to my MSSQL server.

Here is my code:

```auto
check_exist_query = "SELECT 1 FROM Users WHERE First_Name = ? AND Last_Name = ?"
	check_exist_args = [firstName, lastName]
	
	#Check if record exist
	record_exist = system.db.runScalarPrepQuery(check_exist_query, check_exist_args)
	
	if record_exist == None:
		insert_query = "INSERT INTO Users (First_Name, Last_Name, Age, Gender, Address) VALUES (?,?,?,?,?)"
		insert_args = [firstName, lastName, age, gender, address]
		print insert_args
		system.db.runPrepUpdate(insert_query, insert_args)
		
		system.gui.messageBox("Succesully Added!", "Success!")
	else:
		system.gui.messageBox("The name already exist!", "Failed!")

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/3/13a9fc0de5be48020924fb4a174af9f322a3f04c.png)

As seen above on my screenshot, I can successfully insert data to my MSSQL, however using the "Launch Project (F10 or F11)" I get an error:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/8/e841379f464ea1736b76fab826d02f58c9be6ee5.png)

Seems like the error is related to "Roles", but I already logged-in as administrator user, It's the same user when I logged-on my designer, so I don't know if there are still limitations as an administrator.

Hope you can help me.

Regards,

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [August 24, 2023, 11:25am UTC](https://forum.inductiveautomation.com/t/error-while-inserting-data-to-mssql/78327/2 "2023-08-24T11:25:09Z")

</div>

Go to your project properties and look at Vision-\>Permissions and see what Legacy Database Access is set to -  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/e/6e4107901ef15e49806461c7aba886ad67eed95b.png)

---

<div class="post-metadata">

**Author:** ![Henrol\_Jeo\_Gutierrez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/henrol_jeo_gutierrez/32/50993_2.png) [@Henrol\_Jeo\_Gutierrez](https://forum.inductiveautomation.com/u/Henrol_Jeo_Gutierrez)\
**Post date:** [August 24, 2023, 11:25am UTC](https://forum.inductiveautomation.com/t/error-while-inserting-data-to-mssql/78327/3 "2023-08-24T11:25:45Z")

</div>

I get it now from this link:

> [@ERROR(500): Error executing function 'RunQuery': The current user does not have the required roles for this operation](https://forum.inductiveautomation.com/t/error-500-error-executing-function-runquery-the-current-user-does-not-have-the-required-roles-for-this-operation/15252/6):
>
> Thanks, I turned on all the permissions and it works.

Designer authentication settings is different from client/vision.

Thanks and regards

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [August 24, 2023, 11:29am UTC](https://forum.inductiveautomation.com/t/error-while-inserting-data-to-mssql/78327/4 "2023-08-24T11:29:52Z")

</div>

FWIW Legacy DB Access can be a security hole. Your queries in your script don't look too complicated and it's good to use the `prep` version, but you can convert them to named queries and then turn off this permission to minimize risk. Not to mention named queries provide one source for all your CRUD (Create, Read, Update, Delete) operations which helps you not have to re-write the same queries in multiple places throughout your application.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [August 24, 2023, 12:09pm UTC](https://forum.inductiveautomation.com/t/error-while-inserting-data-to-mssql/78327/5 "2023-08-24T12:09:10Z")

</div>

What Brian said.

Don't turn on Legacy Database permissions for new projects. It is called "Legacy" because it exists to accommodate upgrades from Ignition versions before Named Queries existed.

Learn to use Named Queries. If you have an odd circumstance that an NQ cannot handle, put the "Prep" query in a project library script, call it from a gateway message handler, and use `system.util.sendRequest()` to call it from client scope. If you do this latter, and any dynamic SQL is constructed, be particular about excluding client-supplied strings, or the security hole opens back up.

---

<div class="post-metadata">

**Author:** ![Henrol\_Jeo\_Gutierrez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/henrol_jeo_gutierrez/32/50993_2.png) [@Henrol\_Jeo\_Gutierrez](https://forum.inductiveautomation.com/u/Henrol_Jeo_Gutierrez)\
**Post date:** [August 24, 2023, 12:40pm UTC](https://forum.inductiveautomation.com/t/error-while-inserting-data-to-mssql/78327/6 "2023-08-24T12:40:10Z")

</div>

Thanks for the tips! I have a long way to go to learnrn the basics of Ignition platform.

Regards,

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [August 24, 2023, 12:43pm UTC](https://forum.inductiveautomation.com/t/error-while-inserting-data-to-mssql/78327/7 "2023-08-24T12:43:04Z")

</div>

You got it. There is [https://inductiveuniversity.com/](https://inductiveuniversity.com/) if you haven't checked it out yet to learn all about it. I would also say pay this forum a visit when you get stuck or just randomly throughout the week during downtime (if you have any) and you can learn a lot regarding best practices and techniques from the very knowledgeable users here.