RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from http://cloud.google.com/sql/docs/sqlserver/stored-procedures below:

Cloud SQL stored procedures | Cloud SQL for SQL Server

This section describes stored procedures for Cloud SQL instances.

A stored procedure contains SQL code that you can reuse.

You cannot drop or alter system stored procedures.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4