Using XML support, we could pass a large XML string to a stored procedure as a single parameter, which we could subsequently parse and translate into rows, updating data in multiple tables.
However, decomposing large XML strings can be time- and resource-intensive.
Second, when testing your code, start with and then validate the changes.
Fourth, make sure the key used in the WHERE clause is indexed on the remote SQL database: otherwise each UPDATE will cause a painfully slow table scan.
job that updates 10,000 rows in a large, remote Microsoft SQL table with 700,000 rows (1.4%).
A better alternative became available starting with SQL Server 2000, when XML support was introduced.It’s fine to use two conditions in the WHERE clause: in my real table, the unique identifier is not indexed, so I pass a second condition in the WHERE clause with a date that is indexed.Finally, it’s good practice to test the automatic macro variables SQLOBS and SQLRC to verify the PROC SQL executed correctly.This can be easily remedied by creating a connection manager.As you can see from the following image we have a validation error appear telling us that no connection manager has been assigned to the task.