General Write

pixiSysUTRefreshViewsAndProcedures

Refreshes the metadata of all non-schema-bound views and marks all stored procedures of the Pixi database for recompilation.

Maintenance call without parameters. It works through the database objects in two passes:

Views: All views (type = 'V') in all schemas that are not schema-bound (IsSchemaBound = 0) are passed one by one to sp_refreshview. This updates their stored metadata, for example after columns of underlying tables have changed.
Stored procedures: All stored procedures (type = 'P') in all schemas are passed one by one to sp_recompile. Their cached execution plans are recompiled on their next execution.

The objects are addressed as schema.name.

If both passes finish, a single row with Code = '200' and Text = 'OK' is returned. If any step fails, processing stops at that point and a single row with Code = '500' and the SQL error message in Text is returned.

Notes

There is no transaction. Views refreshed before an error stay refreshed. If an error occurs during the view pass, no procedure is recompiled.
A single broken view stops the whole run. This can be a view that references a dropped table or column. The error is returned as a 500 row.
The cursors are not closed or deallocated in the error path. If they are global cursors (database default), they stay open for the connection. A later call on the same connection can then fail because a cursor named cursor_views or cursor_procs already exists.
sp_refreshview and sp_recompile require ALTER permission on each object. They also take schema locks. On a large database the call can run for a long time and block other sessions briefly.
Schema-bound views are skipped. Functions and triggers are not touched.
Recompiling all procedures causes extra compile load the next time each procedure runs.

Parameters

This call has no parameters.

Response

Result set 1 · Always: one row, either `200`/`OK` on success or `500` with the error message on failure.

Status row of the run.

Column	Type	Description
`Code`	varchar(3) string	`200` on success, `500` on error.
`Text` Nullable	nvarchar(4000) string	`OK` on success, otherwise the SQL error message (`ERROR_MESSAGE()`).

Response example

<SqlRowSet1>
  <row>
    <Code>200</Code>
    <Text>OK</Text>
  </row>
</SqlRowSet1>

Error messages

Status	Message	Cause
500	{ERROR_MESSAGE()}	An error occurred while refreshing a view or recompiling a procedure. Typical causes are a view that references a missing object or missing `ALTER` permission. Fix or drop the broken object, or grant the permissions, then call again on a new connection.

Examples

POST https://apigateway.descartes.com/tms/pixi/
Content-Type: text/xml; charset=utf-8
Authorization: Basic BASE64(USERNAME:PASSWORD)

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiSysUTRefreshViewsAndProcedures/>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

curl -X POST 'https://apigateway.descartes.com/tms/pixi/' \
  --user 'USERNAME:PASSWORD' \
  --header 'Content-Type: text/xml; charset=utf-8' \
  --data-binary @- <<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiSysUTRefreshViewsAndProcedures/>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
XML

<?php

$soapClient = new SoapClient(null, [
    'login' => 'USERNAME',
    'password' => 'PASSWORD',
    'location' => 'https://apigateway.descartes.com/tms/pixi/',
    'uri' => 'https://apigateway.descartes.com/tms/pixi/',
]);

$arguments = [
];

$soapArguments = [];
foreach ($arguments as $name => $value) {
    $soapArguments[] = new SoapVar($value, XSD_STRING, '', '', $name);
}

$response = $soapClient->__soapCall('pixiSysUTRefreshViewsAndProcedures', $soapArguments);

Tables used

objects · Read schemas · Read

SQL object:: dbo.pipiSysUTRefreshViewsAndProcedures