03-19-2010 08:32 AM - edited 03-19-2019 12:38 AM
How I can execute a SQL Update with this tool?
03-19-2010 08:36 AM
The proper way to change data in Connection's very heavily constained database (FK constraints hang on just about eveyrthing) is to use stored procedures. You can, of course, construct those in the query builder itself but it's much easier to use the stored procedure interface it exposes seprately under the view menu.
Each input value has a data dictionary value, range defined, etc... for all stored procs and a simple execute capability - you can even review the code behind the proc if you feel the need (though you cannot edit it).
Remember that you can right click on any cell on any grid and copy that cell's value - so snagging OBjectID's for, say, users and handlers when calling stored procs is reasonably easy.
03-19-2010 08:39 AM
My problem is I want to change something for all Voicemail box and the store procedure is just one time shot, so I need to copy paste every objectId and execute the store procedure 1000 times if I have 1000 VM.
Thanks
03-19-2010 08:42 AM
Noting that direct database edits are not (and probably never will be) TAC supported - for good reason - can you tell me what, exactly you're changing on all these users?
It's possible, certainly - but there may be a supported way to go about doing what you're attempting first before you do potential damage to your system.
03-19-2010 08:47 AM
I make a mistake with Bulk Edit, to fix a problem with MWI I push for all VM "Disable MWI" but Bulk Edit doesn't offer me to push "Enable MWI"
Because it's a bug : it seems your hitting the bug CSCsi24009 which is still in “enhancement” stage.
So I want to execute a batch file to change the "active" setting in vw_notificationmwi to 1 instead of 0.
I don't want to pass every VM to check the box or execute 1000 times the store procedure.
Thanks
03-19-2010 08:51 AM
so you want to do this for every user's MWI (each user can have up to 10)?
Or is this for the primary MWI only? (usually the alias would be MWI-1 in this case).
If it's all user's primary MWI it's doable - I can test a query for you real quick locally - which version of Connection are you using (including ES if installed)?
03-19-2010 08:54 AM
Yes it's for the primary MWI-1
Version 7.1.3ES11.21900-11 I guest it's 7.1.3b SU1
03-19-2010 09:02 AM
Ok – the query builder isn’t designed to do updates (it expects a recordset back on any successful query – even an empty one) – however you can issue the update query no problem. You’ll just get an error back (it’ll still tell you how many records were updated). I should probably rework that to be smarter…
Anyway – you have to make edits on tables, not views (which are derived). So the query would look like this:
Update tbl_notificationMWI SET Active= 1 WHERE displaynamelowercase='mwi-1'
This activates ALL primary mwi devices in the system regardless of which subscriber they’re associated with – if you need to filter by COS assignment or the like it’s doable but trickier (INNER JOINS involved).
03-19-2010 09:09 AM
ok thanks I will try this and come back to you.
03-19-2010 10:34 AM
it worked, but I guess it doesn't push a reset on each VM.
but you help me to check this checkbox for every VM. Thanks
03-19-2010 10:37 AM
No - the notifier doesn't trigger events based on DB changes - but you can force a reset of all MWIs from the switch pages in the SA.
06-09-2016 04:07 PM
I'm running this command to update device notification.
Update tbl_notificationdevice SET Active=0
from tbl_notificationdevicesmtp where smtpaddress='User ID@xxx.com'
We have over 8k users with additional smtp address that's incorrect. Need to update them to disable. I know updating tables is 'evil' how do I update it through stored proc?
However when I execute command I'm getting 'Error executing query:ERROR[42000][Informix.NET prover][iNFORMIX]A syntax error has occurred
06-10-2016 04:54 PM
in CUDLI you can view stored procs - go look at csp_NotificationDeviceSMTPModify.
CUDLI even lets you run stored procs and lets you fill in properties as part of the process and shows you what the query looks like when it's done - this can be run in the query editor.
Again - and I know everyone ignores this - updates via direct table edits are not just dangerous (they are), not just unsupported (they aren't) but they flat out won't work much of the time (by design). Stored procs are designed from the start as the _only_ proper way to create, delete and update data in the Unity Connection database.
If you want to take it to the next level you can look at the Python Scripting Host I provided for folks that need to write simple scripts that loop over many users/objects and/or pulll data from CSV and call stored procs against the Unity Connection Database: http://www.ciscounitytools.com/Applications/CxN/PythonScriptingHost/PythonScriptingHost.html
It comes with lots of example scripts and training material to help you along if you're interested.
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide