topic Re: How can I interact with a Function Statement in SQL Server in ArcGIS? in Geodatabase Questions

How can I interact with a Function Statement in SQL Server in ArcGIS?

JacquelinePursell — Mon, 21 Aug 2017 23:58:40 GMT

I previously have only used Views to do complex queries but recently one of our database guys made me a couple of functions instead because he is pulling data from multiple databases and doing some complex things. These are not showing up in the table list like a view does so I can't plot the XY manually in ArcMap. To remedy this, he made a View out of the Function but in SQL Server Management Studio it takes 12 seconds to select the top 1000 records and in ArcGIS it never loads.

Is there a way to interact with SQL Function Statements in ArcGIS?

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

JoshuaBixby — Tue, 22 Aug 2017 00:46:47 GMT

How do you expect to interact with the functions any differently than the view that has been created? Unless the view is a complete hack job, the functions have some issues if they are taking 12 seconds for the top 1000 records. I would focus more time on understanding why the SQL view, or really the functions, are performing so poorly and less about directly interacting with the functions from ArcGIS.

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

JacquelinePursell — Tue, 22 Aug 2017 15:39:35 GMT

I am not asking why it takes forever, I'm asking if there is a better way to interact with a function in ArcGIS because I am not finding it. The function itself takes only a second or 2 to load. The issue is making a view out of the function in order for me to interact with it in GIS. Because the view is made from a function, the view is essentially running the query twice, hence the 12 seconds. I was able to create a feature class out of the view in ArcCatalog but I cannot view the records until after the feature class is created.

Views show up in the table list when I connect in ArcGIS but Functions do not. I want to know how to use a function in ArcGIS without shoving it through a view and making it extremely slow.

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

JoshuaBixby — Tue, 22 Aug 2017 17:51:05 GMT

Assuming it was possible, which I am not saying it is, how would you interact with the function once it is accessible from within ArcGIS? Ostensibly, the functions have parameters, how would you expect to pass the parameters into the function? What is the output of the function?

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

George_Thompson — Tue, 22 Aug 2017 18:24:17 GMT

I would also add that if the ArcGIS client showed any "function" in the database it would cause a ton of clutter in the connection properties. It would show all the functions that are added as part of the enterprise geodatabase.

The best way to interact with a function is using SQL Server Management Studio or other management platform.

I would venture to say that it is not possible with the ArcGIS client at this time. I have not seen anyone access them. I also know that passing SQL functions/binding information via query layers have been know to fail in the past.

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

JacquelinePursell — Wed, 23 Aug 2017 15:27:37 GMT

The function is acting the same as a view which is a permanent query that runs every time the table is viewed or loaded.

I gather data from many non spatial databases in Oracle and SQL Server and plot them and truncate and append my SDE on a regular basis. Making a query such as this function or a view makes it possible for me to do this quickly and easily.

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

JacquelinePursell — Wed, 23 Aug 2017 15:38:30 GMT

Just to be clear, that I am not referring to a SQL Function as in using a Count, Max, or Square Root. I am referring to a script of sorts called a Function that can perform a query on multiple tables and databases to produce a table of joined results just like any spatial or non spatial view or query layer.

I was unable to even call the function in a query layer so that was not possible.

Casting the function as a View in the database did allow me to blindly make query layers and feature classes out of them, but I was not able to load the view 'as is' because the load time was so long that the table just remained empty in ArcGIS, hence why I had to do it blind.

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

JoshuaBixby — Wed, 23 Aug 2017 15:45:38 GMT

If you are running ArcGIS 10.5.x, have you tried registering the view with the geodatabase to see if that addresses the long load times?

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

JacquelinePursell — Wed, 23 Aug 2017 15:49:39 GMT

I tried registering a view in my SDE I made (that was not a function) and it was not an option available to me. Registering these views I am referring to in this post is definitely not an option because they are in non-SDE databases that I do not own or manage.

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

Asrujit_SenGupta — Wed, 23 Aug 2017 16:20:04 GMT

I can suggest a workaround, but you will need to decide whether using it is feasible for you or not.

You mentioned that your Database guy has made you a View based on that function. Now that View is slow to load, but does load in SQL Server Management Studio eventually. The workaround would be to create a separate Table out of this View. This Table would be static and thus will load normally in ArcGIS or SQL Server.

Now, obviously the whole point of using a View is because the data will not be static. So, to see the new data, you will need to create a SQL Server job, which can run at any decided intervals, truncating the Static table and then updating it again with records from the View.

The factors to consider:

1) How frequently will the data in the View change? If it hardly changes, then running the Job at the start of every day, should suffice. If it changes frequently, then you can decide upon the interval accordingly.

2) How much data are you dealing with in the View. Truncating the Table will not take much time, but updating the static table with the data from the View will take time based on the amount of data involved.

Example for reference:

Lets assume that the View that the Database guy gave you is View1, owned by GIS user. So it is dbname.gis.View1

SELECT * INTO dbname.schema.newtable_name FROM dbname.gis.View1

The above will create the new static table. Then use the below two SQL in the SQL Server Job:

Truncate table dbname.schema.newtable_name

Insert INTO dbname.schema.newtable_name SELECT * from dbname.gis.View1

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

AlbertoAloe — Tue, 29 Aug 2017 13:33:34 GMT

Using Query Layer in ArcGIS/ArcGIS Pro should solve the problem.

I imagine you have table valued functions with parameters.....just enter the sql as you would write it in SSMS and it should work even if your function does not appear in the object list.

For example, in my case i have a table valued function called fnCatchDownStream that takes an input parameter called HydroID.

I use it this way in SSMS as well as in Query layer....

SELECT * FROM fnCatchDownStream(356987)

Alberto

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

JacquelinePursell — Tue, 29 Aug 2017 15:53:02 GMT

I actually did try that but I got an error saying that it couldn't find the table. I can do this in FME and SSMS but not ArcGIS.

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

JacquelinePursell — Tue, 29 Aug 2017 15:58:19 GMT

currently part of the data is dynamic and part of it is only nightly because they are migrating from an Access database and only parts have fully moved over. So assuming that soon it will be pretty dynamic, having it "live" is preferable. I have published several temporary event layers in map services from views built in Oracle and SQL Server databases and it has worked out well, a second or 2 delay, but it is well worth it and my users are ecstatic.

Also, I do truncate and append my SDE with this data and all my other data sources nightly using FME so that my GIS users can have data to work with in their maps without needing to connect to 10 databases and sifting through tables like I have to.

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

JacquelinePursell — Tue, 29 Aug 2017 16:05:27 GMT

That is a great idea, however, I will keep it open as an option if I have to.

Re: How can I interact with a Function Statement in SQL Server in ArcGIS?

GayleNeufeld — Thu, 07 Nov 2019 01:07:57 GMT

Did you ever figure out if you could use the Function (a sql script that pulls data from many different tables/databases and also performs calculations) in ArcGIS Online? I find myself in this same situation. Our data is way too complicated to pull together in anything other than a function.