cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
1064
Views
0
Helpful
12
Replies

CUDLI - QUERYBUILDER

David Corbeil
Level 1
Level 1

How I can execute a SQL Update with this tool?

12 Replies 12

lindborg
Cisco Employee
Cisco Employee

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.

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

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.

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

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)?

Yes it's for the primary MWI-1

Version 7.1.3ES11.21900-11 I guest it's 7.1.3b SU1

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).

ok thanks I will try this and come back to you.

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

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.

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

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.

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: