# Changing from SQL server to ignition historian **URL:** \ **Category:** Ignition\ **Created:** [July 13, 2022, 3:36pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856 "2022-07-13T15:36:23Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Conn\_Mccurdy](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@Conn\_Mccurdy](https://forum.inductiveautomation.com/u/Conn_Mccurdy)\ **Post date:** [July 13, 2022, 3:36pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/1 "2022-07-13T15:36:23Z")
So I have a database in SQL that only shows data for the moth that we are currently in. I am looking for a way to either have the name of the database update automatically every month or just use ignition itself to store and pull the data from. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/c/7ce63a808beffd99c50aa70136b2875845cff660.png) This is the name of the database. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/f/ffaddc7ab42521eeafa8d95c690ef5cd16dc5b7d.png) The problem is that next month i will have to go into SQL and change the name of the database to ‘sqlt\_data\_1\_2022\_08’ or else the table will not record any data because it is still looking for the data form the previous month. Any help would be great. ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [July 13, 2022, 3:40pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/2 "2022-07-13T15:40:00Z")
What is pulling the data from the SQL Database? What you are seeing is the normal table partitioning for the Ignition historian. Typically you wouldn’t pull directly from the tables. You would use the internal system.tag.queryHistory function: [system.tag.queryTagHistory - Ignition User Manual 8.1 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory) Or if you have a 3rd party system that is reading data, then you would store that data specifically for that system using the Transaction Manager and generating your own specific tables for that system. ---
**Author:** ![Conn\_Mccurdy](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@Conn\_Mccurdy](https://forum.inductiveautomation.com/u/Conn_Mccurdy)\ **Post date:** [July 14, 2022, 3:44pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/3 "2022-07-14T15:44:11Z")
So i am using a SQL query to pull the data from the database and then am coping that query into a named query in Ignition so that i can link it to tables in Ignition. I tried using transaction groups but they didnt work for my task ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 14, 2022, 4:45pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/4 "2022-07-14T16:45:07Z")
The point is you shouldn’t need to pull from the historian tables manually like this at all. If you need to just query the historical data then you should use `system.tag.queryTagHistory()` it will take care pulling data from the correct table and joining across partitions if needed. ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [July 14, 2022, 4:54pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/5 "2022-07-14T16:54:13Z")
Could you use a historian binding on the table instead of a query? That would take care of the table issue. Like @lrose is saying… if you are querying the historian tables directly, especially in Ignition, then something is amiss in the approach. ---
**Author:** ![Conn\_Mccurdy](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@Conn\_Mccurdy](https://forum.inductiveautomation.com/u/Conn_Mccurdy)\ **Post date:** [July 15, 2022, 12:40pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/6 "2022-07-15T12:40:10Z")
So the reason I am pulling the data manually is because I have multiple markers on a map. When I click on the marker it brings up a popup of the table that shows the data for the marker I have clicked on. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/9/390cd4596521f5f184212dfde78c849071e05931.jpeg) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/b/0bbc871a6137a8feab12d8631a3c339b065e3d26.jpeg) So in the query we are looking for the selected\_tag\_path because I made a custom property with that name to be used in the query. So the query is being used to find the specific data for a specific marker so when the marker is clicked on it will bring up the data on the popup for that marker. here is the script for the map: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/9/199532e7c5610ef6e1d46979e289a9c54f6eca1e.png) I am only using one table but the table will only show the data of the marker that has been clicked even though all the tags are connected to it. It just looks for the correct tagpath Is there another way of doing this while still achieving the same thing? Is it possible to do what @bschroeder said and use an expression to find the correct tagpath so I can achieve the same thing where the marker clicked brings up the data relating to the marker? ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 15, 2022, 12:47pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/7 "2022-07-15T12:47:17Z")
I’m not sure I completely understand what you’re doing, what I think is that you’re selecting a marker on the map which sets a custom property to the tagPath for that marker, and then you’re wanting to use that tag path to display historical data. Is that correct? ---
**Author:** ![Conn\_Mccurdy](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@Conn\_Mccurdy](https://forum.inductiveautomation.com/u/Conn_Mccurdy)\ **Post date:** [July 15, 2022, 12:48pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/8 "2022-07-15T12:48:08Z")
yes that is correct ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [July 15, 2022, 12:56pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/9 "2022-07-15T12:56:53Z")
And you are displaying the historical data in a table? I would then have a custom property on the popup/view that is using a Tag History binding that uses that tagPath parameter to generate a dataset you can use in other parts of the view. Something like this? ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/c/9c279a9e2247438fff27b05d23bfd322dc74ffe5.png) ---
**Author:** ![Conn\_Mccurdy](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@Conn\_Mccurdy](https://forum.inductiveautomation.com/u/Conn_Mccurdy)\ **Post date:** [July 15, 2022, 2:34pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/10 "2022-07-15T14:34:40Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/d/bdf47fdb77e810999c6ebf2251b24eddb32a1454.png) so something like this? I am just confused on how to use an expression to do what the query was doing where the tagpath = :tagselected. ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [July 15, 2022, 2:37pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/11 "2022-07-15T14:37:55Z")
You don’t need to add the where statement… the expression takes care of getting the data from the historian based on the tag you pass. what happens if you have a valid path to a tag in the selected\_tag\_path custom property? IE just keep everything between the {} curly braces and see what happens. ---
**Author:** ![Conn\_Mccurdy](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@Conn\_Mccurdy](https://forum.inductiveautomation.com/u/Conn_Mccurdy)\ **Post date:** [July 15, 2022, 2:49pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/12 "2022-07-15T14:49:48Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/b/9b73618bb1b76a2789fa6fcd99a0b7be7e187b28.png) so this is the valid path to a tag in the selected\_tag\_path ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/3/a3b16e0b66d214ae55338c1889791bc0aea6f62e.png) This is what i get from doing that. ---
**Author:** ![Conn\_Mccurdy](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@Conn\_Mccurdy](https://forum.inductiveautomation.com/u/Conn_Mccurdy)\ **Post date:** [July 15, 2022, 2:52pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/13 "2022-07-15T14:52:11Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/e/be1b16c654305f1cf79323906ddaaf03eb17db6a.png) this is the error I am getting if that helps ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [July 15, 2022, 2:59pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/14 "2022-07-15T14:59:38Z")
OK take a look at this: [Tag History Bindings in Perspective - Ignition User Manual 8.1 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC81/Tag+History+Bindings+in+Perspective) Sorry it’s been a hot minute since I’ve done this in Perspective. So you will need to do one of two things. 1. Change your selected\_tag\_path property to be an array and have a single array member under it as an object that has an aggregate, alias and path member. Then when you click on the map update the path member. 2. Have a custom property that is bound to the current selected\_tag\_path property and then use a script transform to generate the array of dictionaries to generate the list of tags to send to the history binding. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 15, 2022, 3:44pm UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/15 "2022-07-15T15:44:05Z")
Here is a similar example, that shows how to set up the property for use with the expression. > [@System.tag.querytaghistory return error](https://forum.inductiveautomation.com/t/system-tag-querytaghistory-return-error/61383/10): > > I think that the binding without the transform is the correct way to do this. You have to set it up first though. I will assume that you already have properties for Parameter\_1 and Parameter\_2. Add a custom prop to the view or coponent that this binding will be on. It must be an Array type. Add an array element of object type for each tag that you want to collect history for. For this example I’m assuming only 1 tag. Add three values to each array element they must be named ‘aggregate’, `ali… ---
**Author:** ![Conn\_Mccurdy](https://avatars.discourse-cdn.com/v4/letter/c/e495f1/32.png) [@Conn\_Mccurdy](https://forum.inductiveautomation.com/u/Conn_Mccurdy)\ **Post date:** [July 22, 2022, 8:51am UTC](https://forum.inductiveautomation.com/t/changing-from-sql-server-to-ignition-historian/61856/16 "2022-07-22T08:51:54Z")
Thanks a million guys for helping me with this. Works great!!