Called from SQL

A dynamic link library (SQLWSCall.dll) provided by Third Wave Business Systems is required for this functionality to work. The file is installed in C:\Program Files\Bizweaver\BWPortal\BWSQLSP when Bizweaver is installed.

Execute the following SQL statements against the Master database

sp_configure 'show advanced options', 1 
GO  
RECONFIGURE 
GO  
sp_configure 'clr enabled', 1
GO  
RECONFIGURE 
GO
 
-- Create Asymmetric Key using DLL.
CREATE ASYMMETRIC KEY [bwws]
AUTHORIZATION [dbo]
-- Source DLL file must be located in C:\Program Files\Bizweaver\BWPortal\BWSQLSP, or path changed below
FROM EXECUTABLE FILE = 'C:\Program Files\Bizweaver\BWPortal\BWSQLSP\SQLWSCall.dll'
-- Create login
CREATE LOGIN TWBS
FROM ASYMMETRIC KEY [bwws]
-- Modify login
GRANT UNSAFE ASSEMBLY TO TWBS

Create an Assembly in the calling database

• In MS SQL Management Studio, expand the folder tree for the database.
• Expand the “Programmability” folder.
• Right-click on “Assemblies”; select “New Assembly”.

• Browse to folder where the SQLWSCall.dll is located and select it.
• The Assembly Name and Assembly Owner are set automatically.
• Select “External Access” from the drop-down for “Permission Set”.
• Click [OK] to register the assembly.

Create the following stored procedure in the calling database.

CREATE PROCEDURE [dbo].[spCallBizweaverWS]
@wsMethod NVARCHAR (MAX), 
@wsUrl NVARCHAR (MAX),
@wsPort NVARCHAR (MAX),
@wsContent NVARCHAR (MAX),
@wsAuthToken NVARCHAR (MAX),
@wsTaskId INT,
@wsStartAfter INT,
@wsParameters NVARCHAR (MAX)
AS EXTERNAL NAME [SQLWSCall].[StoredProcedures].[spCallBizweaverWS]

• @wsMethod: POST
• @wsUrl: https://<yourURL>/ (Be sure to use http or https as appropriate)
• @wsPort: <yourPort> (Typically 443 for https or 8080 for http)
• @wsContent: 'application/json'
• @wsAuthToken: token generated in Bizweaver's Settings window
• @wsTaskId: workflow id
• @wsStartAfter: 0
• @wsParameters: arguments must be passed using label:value pairs enclosed in double quotes and brackets
  • Example: the variable name in the Bizweaver workflow is vgo_BatchID (case sensitive)

    SET @wsParameters = '{"vgo_BatchID":' + '"' + @vgo_BatchID + '"' + '}'
 

DECLARE @Arguments NVARCHAR(MAX)
SET @Arguments = '{"YourTargetVariable":' + '"' + @list_of_cols_val_tab_del + '"' + '}'
 
EXECUTE [dbo].[spCallBizweaverWS]
    @wsMethod='POST'
    ,@wsURL='https://YourURL'
    ,@wsPort='443'
    ,@wsContent='application/json'
    ,@wsAuthToken='xyxyxyxyxyxyxyxyx'
    ,@wsTaskId='999'
    ,@wsStartAfter='0'
    ,@wsParameters= @Arguments
 
END

Home