locked
Can we create MASTER KEY,DATABASE SCOPED CREDENTIAL ,external data source and external data table dynamically ? RRS feed

  • Question

  • Hi,

        Is that possible to create MASTER KEY,DATABASE SCOPED CREDENTIAL ,external data source and external data table dynamically in stored procedure.please find the below stored procedure which i create to create external table but i got error

    like 

    Msg 102, Level 15, State 1, Procedure CreateExternaltable, Line 9
    Incorrect syntax near '@DBPassword'.
    Msg 102, Level 15, State 1, Procedure CreateExternaltable, Line 11
    Incorrect syntax near '@DBUserName'.
    Msg 102, Level 15, State 1, Procedure CreateExternaltable, Line 16
    Incorrect syntax near '@DBServerName'.


    Create PROCEDURE [dbo].[CreateExternaltable]    
                      @DBServerName as nvarchar(255),          
          @DatabaseName as nvarchar(255),    
          @DBUserName as nvarchar(20),    
          @DBPassword as nvarchar(50) 
    AS     
    BEGIN      

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = @DBPassword; 
     CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred 
     WITH IDENTITY = @DBUserName, 
     SECRET = @DBPassword;

    CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH 
        (TYPE = RDBMS, 
        LOCATION = @DBServerName, 
        DATABASE_NAME = @DatabaseName, 
        CREDENTIAL = ElasticDBQueryCred, 
    ) ;

    CREATE EXTERNAL TABLE [dbo].[CustomerInformation] 
    ( [CustomerID] [int] NOT NULL, 
      [CustomerName] [varchar](50) NOT NULL, 
      [Company] [varchar](50) NOT NULL) 
    WITH 
    ( DATA_SOURCE = MyElasticDBQueryDataSrc)


    END

    Thanks in Advance

    Wednesday, July 26, 2017 6:45 AM

Answers

  • Hello,

    DDL statements do not support parameters. Try using dynamic SQL instead.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, July 26, 2017 1:58 PM
  • Hello,

    You need to use dynamic SQL to assign the variable values.

    Direct assigning values like this will not work.

    Regards

    Kapil


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Wednesday, July 26, 2017 2:13 PM

All replies

  • Hello,

    DDL statements do not support parameters. Try using dynamic SQL instead.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, July 26, 2017 1:58 PM
  • Hello,

    You need to use dynamic SQL to assign the variable values.

    Direct assigning values like this will not work.

    Regards

    Kapil


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Wednesday, July 26, 2017 2:13 PM
  • Thanks Alberto Morillo,

                               It is working perfectly in dynamic SQL.

    Monday, August 7, 2017 8:07 AM
  • Thanks kapil,

                               It is working perfectly in dynamic SQL.

    Monday, August 7, 2017 8:07 AM