This section describes how to execute queries to the Veeva Vault linked server from any tool that can connect to SQL Server.
When executing queries from a linked server, you must use a four-part name format, as shown below.
[<Linked Server Name>].[<DSN Name>].[<Schema Name (normally but not always the service name)>].[<Table Name>]
- Linked Server Name is the name of the linked server that you created.
- DSN Name is the name of the data source.
- Schema Name is the name of the system schema or user-defined schema.
- Table Name is the name of the table you are copying data from.
To create new records:
SELECT * FROM [Linked Server Name].[CData VeevaVault Sys].[VeevaVault].[Documents] WHERE Name = 'Test'
Execute Stored Procedures
To execute stored procedures, you must first enable RPC and RPC Out in the settings for the linked server. Then, you can use the following set of commands to run the stored procedure:
DECLARE @RunStoredProcSQL VARCHAR(1000);
SET @RunStoredProcSQL = 'EXEC SampleProcedure Id=''7''';
EXEC (@RunStoredProcSQL) AT [Linked Server Name];