2 Replies Latest reply: Apr 25, 2017 3:35 AM by Christian Wunder RSS

    Execute Stored Procedure with Parameters via Makro

    Christian Wunder

      Hi,

       

      I want to run a SQL Stored Procedure via Makro and fill the 3 Parameters for the stored Procedure.

      It works when i fill the parametes directly, like:

       

      SUB TEST

      dim dbconn, ConnectionString, Name1, Vorname1, Alter1

       

      ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xxx;Data Source="xxx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;;Use Encryption for Data=False;Tag with column collation when possible=False"

      Set dbconn = CreateObject("ADODB.Connection")

      dbconn.Open ConnectionString

      dbconn.Execute("DWHBI.dbo.update_from_qv @Name1='Huber', @Vorname1='Hans', @Alter1=50")

       

      END SUB

       

      But how can i fill the parameter with variables from the qvw??

       

      Like this:

       

      sub TEST

       

      dim dbconn, ConnectionString, Name1, Vorname1, Alter1

       

      Name1 = ActiveDocument.Variables("vName1").GetContent.String

      Vorname1 = ActiveDocument.Variables("vVorname1").GetContent.String

      Alter1 = ActiveDocument.Variables("vAlter1").GetContent.String

       

      ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DWHBI;Data Source=segdwh02;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;;Use Encryption for Data=False;Tag with column collation when possible=False"

      Set dbconn = CreateObject("ADODB.Connection")

      dbconn.Open ConnectionString

      dbconn.Execute("DWHBI.dbo.update_from_qv @Name1=Name1, @Vorname1=Vorname1, @Alter1=Alter1")

       

      END SUB

       

      Any Ideas??

       

      Thanks for your regards

       

      Christian