Cloud SQL stored procedures

MySQL | PostgreSQL | SQL Server

This section describes stored procedures for Cloud SQL instances.

A stored procedure contains SQL code that you can reuse.

To execute a stored procedure, you use the EXEC command and replace the following variables:

procedure_name is the name of the stored procedure.
database_name is the name of the database where you want to run the procedure.
schema_name is the name of the schema where you want to run the procedure.

EXEC database_name.schema_name.procedure_name @param1, @param2;

For more information, see the EXECUTE (Transact-SQL) reference page.

To create your own stored procedure, see Create a Stored Procedure.

Note: You cannot run some stored procedures because they require a sysadmin role, which isn't available in Cloud SQL.

Cloud SQL for SQL Server stored procedures

Stored procedure for using bulk insert

For information about bulk insert, see Use bulk insert for importing data.

msdb.dbo.gcloudsql_bulk_insert

Syntax

EXEC msdb.dbo.gcloudsql_bulk_insert @database @schema @object @file ...

Description

This stored procedure has similar parameters and behavior to the BULK INSERT command.

The stored procedure imports data to a Cloud SQL instance from a file stored in a Cloud Storage bucket. It uses Cloud Storage interoperable API and HMAC keys to authenticate access to the Cloud Storage bucket.

This stored procedure has the following parameters:

Parameter	Type	Description
`@database`	`SYSNAME`	Specifies the name of the target database to which the data is to be imported.
`@schema`	`SYSNAME`	Specifies the name of the schema to which the table belongs.
`@object`	`NVARCHAR`	Specifies the name of the table where the data is to be inserted.
`@file`	`NVARCHAR`	Specifies the path to the import file in the Cloud Storage bucket. The path must have the following format: s3://storage.googleapis.com/`BUCKET_NAME`/`FILE_PATH` Replace the following: `BUCKET_NAME`: specifies the Cloud Storage bucket name. `FILE_PATH`: specifies the path to the file stored in the Cloud Storage bucket.
`@batchsize`	`INT`	Specifies the number of rows in a batch.
`@checkconstraints`	`BIT`	Specifies that all constraints on the target table must be checked.
`@codepage`	`NVARCHAR`	Specifies the code page of the data in the file. `RAW` is the default and only option.
`@datafiletype`	`NVARCHAR`	Specifies the bulk insert file type.
`@datasource`	`NVARCHAR`	Specifies the name of the external data source from which you want to import the data.
`@errorfile`	`NVARCHAR`	Specifies the path to the file used to collect rows that have formatting errors. The path must have the following format: s3://storage.googleapis.com/`BUCKET_NAME`/`FILE_PATH` Replace the following: `BUCKET_NAME`: specifies the Cloud Storage bucket name. `FILE_PATH`: specifies the path to the file stored in the Cloud Storage bucket. Additionally, Cloud SQL creates a file with the extension `.ERROR.txt`. This file contains references to each row in the error file and provides error diagnostics.
`@errorfiledatasource`	`NVARCHAR`	Specifies the name of the external data source in which you want to create the error file.
`@firstrow`	`INT`	Specifies the numeric identifier of the first row to load.
`@firetriggers`	`BIT`	Indicates that any insert triggers defined on the target table would execute during the bulk insert operations.
`@formatfiledatasource`	`NVARCHAR`	Specifies the name of the external data source from which you should load the format file.
`@keepidentity`	`BIT`	Specifies the use of identity data from the import file for the identity column. The values are `0`, which means false, and `1`, which means true.
`@keepnulls`	`BIT`	Specifies whether empty columns should retain a null value during the bulk import operation, instead of having any default values for the columns inserted. The values are `0`, which means false, and `1`, which means true.
`@kilobytesperbatch`	`INT`	Specifies the amount of data per batch, in KB.
`@lastrow`	`INT`	Specifies the numeric identifier of the last row to load.
`@maxerrors`	`INT`	Specifies the number of errors allowed, before Cloud SQL cancels the operation.
`@ordercolumnsjson`	`NVARCHAR`	Specifies the sort order and columns, in JSON format. For example: [{"name": "`COLUMN_NAME`","order": "`ORDER`"},{"name": "`COLUMN_NAME`","order": "`ORDER`"}] Replace the following: `COLUMN_NAME`: the name of the column. `ORDER`: the sort order. The value of this parameter can be `asc` for ascending or `desc` for descending.
`@rowsperbatch`	`INT`	Specifies the number of rows per batch. For more information about selecting a batch size, see Performance considerations
`@tablock`	`BIT`	Specifies that a table lock is taken for the duration of the bulk insert operation.
`@format`	`NVARCHAR`	Specifies the format of the file. Use `CSV` as the value of this parameter.
`@fieldquote`	`NVARCHAR`	Specifies the character to be used as the quote character in the CSV file. If you don't specify a value, then Cloud SQL uses `"` as the default value.
`@formatfile`	`NVARCHAR`	Specifies the path of the file in Cloud Storage describing the format of the data to be imported. The path should have the following format: s3://storage.googleapis.com/`BUCKET_NAME`/`FILE_PATH` Replace the following: `BUCKET_NAME`: specifies the Cloud Storage bucket name. `FILE_PATH`: specifies the path to the file stored in the Cloud Storage bucket.
`@fieldterminator`	`NVARCHAR`	Specifies the field terminator for char and widechar data files.
`@rowterminator`	`NVARCHAR`	Specifies the row terminator for char and widechar data files.

Stored procedures for SQL Server Audit functionality

For information about using the functionality of SQL Server Audit, see SQL Server database auditing.

msdb.dbo.gcloudsql_fn_get_audit_file

Syntax

msdb.dbo.gcloudsql_fn_get_audit_file

Description

Retrieves the data from an audit file that was created by SQL Server Audit functionality.

This stored procedure accepts the same parameters as the sys.fn_get_audit_file function. See the documentation for that function for more information related to msdb.dbo.gcloudsql_fn_get_audit_file.

Stored procedures for change data capture (CDC)

For more information about CDC, see Enable change data capture.

msdb.dbo.gcloudsql_cdc_enable_db

Syntax

exec msdb.dbo.gcloudsql_cdc_enable_db databaseName

Description

Turns change data capture on for a database.

databaseName - Name of the database to run this stored procedure on.

msdb.dbo.gcloudsql_cdc_disable_db

Syntax

exec msdb.dbo.gcloudsql_cdc_disable_db databaseName

Description

Turns CDC off for a database.

databaseName - Name of the database to run this stored procedure on.

Stored procedures for external replication with Cloud SQL as a publisher

For more information about publishing to an external subscriber, or to another Cloud SQL instance, see Configure external replicas.

msdb.dbo.gcloudsql_transrepl_setup_distribution

Syntax

    exec msdb.dbo.gcloudsql_transrepl_setup_distribution @login,@password

Description

A wrapper stored procedure that sets up a distribution database. The wrapper calls the following: sp_adddistributor, sp_adddistributiondb, and sp_adddistpublisher.

login - An existing login used to connect and create the distribution database.
password - The password used when connecting to the distributor.

msdb.dbo.gcloudsql_transrepl_replicationdboption

Syntax

    exec msdb.dbo.gcloudsql_transrepl_replicationdboption @db,@value

Description

Enables or disables the publishing option of a database for the publisher that uses sp_replicationdboption.

db - The database for which the replication option is being set.
value - Allows you to specify True to enable the publishing option, or False to disable the publishing option.

msdb.dbo.gcloudsql_transrepl_addlogreader_agent

Syntax

    exec msdb.dbo.gcloudsql_transrepl_addlogreader_agent @db,@login,@password

Description

Sets up the log reader agent for a database that uses sp_addlogreader_agent.

db - Database to be published.
login - Login used when connecting to the publisher.
password - The password used when connecting.

msdb.dbo.gcloudsql_transrepl_addpublication

Syntax

    exec msdb.dbo.gcloudsql_transrepl_addpublication @db,@publication

Description

Creates the transactional publication, and acts as a wrapper stored procedure for sp_addpublication.

db - Database being published.
publication - Name of the new publication created.

msdb.dbo.gcloudsql_transrepl_droppublication

Syntax

    exec msdb.dbo.gcloudsql_transrepl_droppublication @db,@publication

Description

Drops the transactional publication, and acts as a wrapper stored procedure for sp_droppublication.

db - Database for which publication is dropped.
publication - Name of the publication dropped.

msdb.dbo.gcloudsql_transrepl_addpublication_snapshot

Syntax

    exec msdb.dbo.gcloudsql_transrepl_addpublication_snapshot  @db,@publication,@login,@password

Description

Creates a snapshot agent for the database that is being published, acting as a wrapper stored procedure for sp_addpublication_snapshot.

db - Database for which publication is dropped.
publication - Name of the publication dropped.
login - Login used when connecting to the publisher.
password - The password used when connecting.

msdb.dbo.gcloudsql_transrepl_addpushsubscription_agent

Syntax

    exec msdb.dbo.gcloudsql_transrepl_addpushsubscription_agent
      @db,@publication,@subscriber_db,@subscriber_login,
        @subscriber_password,@subscriber

Description

Creates a new scheduled agent job to synchronize the push subscription, acting as a wrapper stored procedure for sp_addpushsubscription_agent.

db - Database that is published.
publication - Name of the publication to which to add a push subscription agent.
subscriber_db - The database on the subscriber.
subscriber_login - The login used when connecting to the subscriber.
subscriber_password - The password used when connecting to the subscriber.
subscriber - The IP name of the subscriber instance. This value can specified as: <Hostname>,<PortNumber>

msdb.dbo.gcloudsql_transrepl_addmonitoraccess

Syntax

    exec msdb.dbo.gcloudsql_transrepl_addmonitoraccess @login

Description

Provides access to the Replication Monitor and the SELECT statement on replication-related tables on the distribution database.

login - The login used to access the replication monitor.

msdb.dbo.gcloudsql_transrepl_changedistributor_property

Syntax

    exec msdb.dbo.gcloudsql_transrepl_changedistributor_property @property,@value

Description

This stored procedure changes the heartbeat_interval, and wraps sp_changedistributor_property. For more information, see the documentation for sp_changedistributor_property. Also see that documentation for more information about the heartbeat_interval value.

property - The property for a distribution database.
value - The value to provide for the specified property.

msdb.dbo.gcloudsql_transrepl_dropsubscriber

Syntax

    exec msdb.dbo.gcloudsql_transrepl_dropsubscriber @subscriber

Description

Removes the subscriber, acting as a wrapper stored procedure for sp_dropsubscriber.

subscriber - The IP name of the subscriber to be dropped. This value can specified as: <Hostname>,<PortNumber>

msdb.dbo.gcloudsql_transrepl_remove_distribution

Syntax

     exec msdb.dbo.gcloudsql_transrepl_remove_distribution

Description

Removes the distribution setup, acting as a wrapper stored procedure for the following: sp_dropdistpublisher, sp_dropdistributiondb, and sp_dropdistributor.

What's next

Learn how to enable change data capture (CDC).
Learn how to configure external replicas.