This is mostly a bug fix release, but with one new SQL feature implemented which could be very useful for testing and ALM usage scenarios.
This SQL statement pauses your script for a period of time. This can be useful if you need to wait for some async operation to happen in the background after you run an
DELETE command. You could also leave a script running in a loop to see how your data changes over time. For example, while you’re running a data migration:
DECLARE @count AS INT; WHILE 1 = 1 BEGIN SET @count = (SELECT count(*) FROM account); PRINT CONVERT (VARCHAR, CURRENT_TIMESTAMP, 120) + ' - ' + CONVERT (VARCHAR, @count); WAITFOR DELAY '00:10'; END
This produces a log in the Messages pane that updates every 10 minutes with the number of accounts in your system so you can monitor the progress of your data import process. Just hit the Stop button in the toolbar when you’re done.
WAITFOR statement you can either specify a
DELAY or a
TIME. Both should be in the format
WAITFOR DELAY '12:00' will pause your script for 12 hours, while
WAITFOR TIME '12:00' will wait until 12pm.
This release fixes a few bugs with how the
null literal is handled in different situations, particularly with
One other useful enhancement is with clearing out polymorphic lookup fields. Normally if you want to set the value of one of these lookups that can refer to more than one other table (like
<anything>.ownerid) you also have to set the corresponding
___type field. For example, if you write:
UPDATE contact SET parentcustomerid = 'BBD2BDBF-2E1B-4225-93E2-A17FC8F55761' WHERE firstname = 'Mark'
it won’t know if that ID relates to a contact or an account. Instead you have to write:
UPDATE contact SET parentcustomerid = 'BBD2BDBF-2E1B-4225-93E2-A17FC8F55761', parentcustomeridtype = 'account' WHERE firstname = 'Mark'
null is a special case, so you can now remove any existing values from a polymorphic lookup field without having to specify the type:
UPDATE contact SET parentcustomerid = null WHERE firstname = 'Mark'
If you query the metadata using a SELECT statement like:
SELECT displayname FROM metadata.entity WHERE logicalname = 'Account'
this would previously not produce any results due to the incorrect case of the logical name (should be all lower case). These filters are now translated to the correct case so you get the expected results.
Because filters have to be supplied in the correct case when SQL 4 CDS issues the metadata query to the server, filters on other columns where SQL 4 CDS doesn’t know what case the value will be in can’t be sent to the server. In these cases SQL 4 CDS will instead retrieve all the possible values and perform the filtering itself. This means you’ll get the correct results but the queries may take a little longer to run.