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.
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_PATHReplace the following:
|
@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_PATHReplace the following:
.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:
|
@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_PATHReplace the following:
|
@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, orFalse
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.