Latest Contributions by CraigSwadner

This will give you the typical esri permission by user / object (leave out the where clause if you want everything) SELECT dp.NAME AS principal_name, dp.TYPE_DESC AS principal_type_desc, o.NAME AS object_name, p.PERMISSION_NAME, p.STATE_DESC AS permission_state_desc FROM sys.database_permissions p LEFT OUTER JOIN sys.all_objects o ON p.MAJOR_ID = o.OBJECT_ID INNER JOIN sys.database_principals dp ON p.GRANTEE_PRINCIPAL_ID = dp.PRINCIPAL_ID where permission_name in ('select', 'delete', 'update', 'insert') order by principal_name ... View more

is this a versioned or non-versioned database ?   ... View more

Not sure if this is what you are looking for however I have done this in the past. ALTER VIEW .[MOBILE_GPS_LOCATIONS_LAT_LON] AS SELECT gis.GPS_LOCATION_DATA.OBJECTID, gis.GPS_LOCATION_DATA.SERNO, gis.GPS_LOCATION_DATA.SEQNO, (SELECT Description FROM htetables.dbo.Dart2_Reason_Codes WHERE (R_Val = gis.GPS_LOCATION_DATA.REASON)) AS Reason, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, gis.GPS_LOCATION_DATA.DATEUTC), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS CC_Date, CAST(REPLACE(CAST(CONVERT(time, SWITCHOFFSET(CONVERT(datetimeoffset, gis.GPS_LOCATION_DATA.DATEUTC), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS Varchar(5)), ':', '') AS int) AS CC_Time, gis.GPS_LOCATION_DATA.DATEUTC, gis.GPS_LOCATION_DATA.FIELDS_LAT, gis.GPS_LOCATION_DATA.FIELDS_LONG, CONVERT(int, gis.GPS_LOCATION_DATA.FIELDS_ALT * 3.28084) AS FIELDS_ALT, CONVERT(int, gis.GPS_LOCATION_DATA.FIELDS_SPD * 0.0223694) AS FIELDS_SPD, gis.GPS_LOCATION_DATA.FIELDS_SPDACC, gis.GPS_LOCATION_DATA.FIELDS_DEVSTAT, htetables.dbo.Faster_Assets.DepartmentDesc, htetables.dbo.Faster_Assets.Model, htetables.dbo.Faster_Assets.Make, htetables.dbo.Faster_Assets.AssetNumber, htetables.dbo.Faster_Assets.AssetComment, htetables.dbo.Faster_Assets.AssetDepartment, htetables.dbo.Faster_Assets.AssetStatus, htetables.dbo.Faster_Assets.License, htetables.dbo.Faster_Assets.Location, htetables.dbo.Faster_Assets.SerialNumber, htetables.dbo.Faster_Assets.Year, htetables.dbo.Faster_Assets.FleetNumber, gis.GPS_LOCATION_DATA.UNITCODE, gis.GPS_LOCATION_DATA.PSDEPT, CASE WHEN PSDEPT = 'LAW' THEN 'Police' WHEN PSDEPT = 'FIRE' THEN 'Fire' WHEN DepartmentDesc LIKE '%Utilities%' AND departmentdesc NOT IN ('40100 Utilities Field Service') THEN 'Utilities' WHEN DepartmentDesc LIKE '%Public Works%' THEN 'Public Works' WHEN DepartmentDesc LIKE '%ITS%' THEN 'Information Technology' WHEN DepartmentDesc LIKE '%parks%' THEN 'Parks' WHEN DepartmentDesc LIKE '%DCD%' THEN 'DCD' WHEN DepartmentDesc LIKE '%police%' THEN 'Police' WHEN departmentdesc LIKE '%city clerk%' THEN 'City Clerk' WHEN departmentdesc LIKE '%EOC%' THEN 'Fire' WHEN departmentdesc LIKE '% PW %' THEN 'Public Works' WHEN departmentdesc LIKE '%city manager%' THEN 'City Manager' WHEN departmentdesc LIKE '%finance%' THEN 'Finance' WHEN departmentdesc LIKE '%Utilities Field Service%' THEN 'Utilities Customer Service' END AS Department, CASE WHEN reason IN (1, 3, 4, 5, 6, 7, 12, 13, 14, 15, 16) THEN 'In Motion' WHEN REASON IN (11) THEN 'Ignition Off Vehicle Dormant' WHEN REASON IN (2, 8, 9, 10) THEN 'Stationary' WHEN reason IN (20) THEN 'Towing Alert' END AS Vehicle_Status, geometry::STGeomFromText('POINT(' + CAST(CAST(gis.GPS_LOCATION_DATA.FIELDS_LONG AS decimal(15, 9)) AS varchar) + ' ' + CAST(CAST(gis.GPS_LOCATION_DATA.FIELDS_LAT AS decimal(15, 9)) AS varchar) + ')', 4326) AS shape FROM gis.GPS_LOCATION_DATA WITH (nolock) LEFT OUTER JOIN htetables.dbo.Faster_Assets ON gis.GPS_LOCATION_DATA.SERNO = htetables.dbo.Faster_Assets.GPS_Serno ... View more

Jay, Here is an example of a spatial view we use, the crw_land view is a join between the city land files and the county appraiser’s data joined to the parcel layer. One view/table needs to own the objectids, or you can always do a row over sql statement (however those don’t perform all that well). All that said before you start publishing data the sql view/table stuff should be worked out first. SELECT gis.crw_gisland.OBJECTID, gis.FABRICPARCELS_V.Name AS Strap, gis.crw_gisland.Strap AS Owner_Strap, gis.crw_gisland.Block, gis.crw_gisland.Lot, gis.crw_gisland.Street_Number, gis.crw_gisland.Site_Address, gis.crw_gisland.SITE_UNIT_NO AS Site_Unit_Number, gis.crw_gisland.SiteZip, gis.crw_gisland.Owner_Name, gis.crw_gisland.OwnerOthers, gis.crw_gisland.OwnerCareOf, gis.crw_gisland.Mailing_Addr1, gis.crw_gisland.Mailing_Addr2, gis.crw_gisland.Mailing_City, gis.crw_gisland.Mailing_State, gis.crw_gisland.Mailing_Zip, gis.crw_gisland.Improved, gis.crw_gisland.Foreign_Address, gis.crw_gisland.State_Use_Code, gis.crw_gisland.Future_Land_Use, gis.crw_gisland.Zoning, gis.crw_gisland.Legal, gis.crw_gisland.Council_District, gis.crw_gisland.CO_Date, gis.crw_gisland.Assessed, gis.crw_gisland.Building, gis.crw_gisland.Land, gis.crw_gisland.WATER, gis.crw_gisland.SEWER, gis.crw_gisland.IRRIGATION, gis.crw_gisland.GACUNT, gis.crw_gisland.Building_Impervious, gis.crw_gisland.Open_Air_Impervious, gis.crw_gisland.Coastal_Hazard, gis.crw_gisland.FolioID, gis.crw_gisland.Flood_Zone, gis.crw_gisland.FIRM_Panel_No, gis.crw_gisland.SEWERDISTRICT AS Sewer_Impact_Dist, gis.crw_gisland.PARKING_ASSESS_DATE, gis.crw_gisland.SEAWALL_ASSESS_DATE, gis.crw_gisland.SEAWALLASSESSMENT, gis.crw_gisland.PARKGLOTASSESSMENT, gis.crw_gisland.Confidential, gis.crw_gisland.GEOTYPE, gis.FABRICPARCELS_V.OBJECTID AS parcelobj, gis.FABRICPARCELS_V.Shape FROM gis.crw_gisland INNER JOIN gis.FABRICPARCELS_V ON gis.crw_gisland.FolioID = gis.FABRICPARCELS_V.FolioID WHERE (gis.crw_gisland.Confidential IS NULL) Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more

Check the database / schema , and permissions to table / layer (SELECT MAX(GDP2005) FROM gisdata.dbo.countries) Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more

If I have troubles, I usually register the spatial table first, after registering alter the view, and reset permissions with arccatalog. The spatial table needs to have a field objectid as int and geometry field as shape. The objectid field should be unique. Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more

For us we kept everything the same, however it was a new server install. Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more

We have dozens of queries / views, 10.7.1 Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more

We are running Microsoft sql 2017 database, and we really don’t have a dataset that large however just a couple of questions / suggestions. Are you running a versioned database ? have you tried without versioning? Also if versioning how often is the db gone to full compress? Also you may want to do a trace on the database to see if indexing is necessary. i reindex a lot of our tables / layers daily for performance … Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more

Select * into tablename from view Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more

I haven’t however any gis application that uses versioning should work fine, ArcMap, Pro and our webapps work just fine. I don’t think survey123 works with any kind of sql db to my knowledge, probably why we don’t use it much at all. Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more

This doesn’t effect survey123, however we have multiple software apps that touch our sde database and we created a db trigger to maintain our ids. Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more

What does your compress log say for that database ? Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more

We have done this, its just a bit painful ,…. This can get you started https://desktop.arcgis.com/en/arcmap/10.3/manage-data/using-sql-with-gdbs/edit-versioned-data-using-sql-sqlserver.htm Craig Swadner (GIS Coordinator) City of Cape Coral 1015 Cultural Park Blvd. Cape Coral, Fl 33990 Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net ... View more