Console
    Contact Us Start free

    Cloud SQL stored procedures

    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_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
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: