GV

GeezerVison

The World Wide Web, restored to 800 pixels of glory.

Home|About|Gaming cabinet|Archives|How it works|Starter sites

On this site

  • Sign in
  • ActionsCI/CD and automation
  • CodespacesCloud development environments
  • All Features
  • Documentation
  • Training
  • Certifications
  • Build with Copilot
  • Customize Copilot
  • Integrations
  • Trending repos
  • GitHub Sponsors
  • Developer articles
  • Enterprises

This view

  • Original page
  • New address

GeezerVison is showing github.com — Inital commit · WCOMAB/SqlServerSlackAPI@417cf0e

File tree

Expand file treeCollapse file tree
Open diff view settings
Filter options
  • .gitignore
  • LICENSE.md
  • README.md
  • images
    • sql2slack.png
    • sqlserver.png
  • src
    • SqlServerSlackAPI.sln
    • SqlServerSlackAPI
      • Deploy.sql
      • Include_T4Include.cs
      • Include_T4Include.tt
      • Properties
        • AssemblyInfo.cs
      • SlackChatApi.cs
      • SlackChatMessageResult.cs
      • SqlServerSlackAPI.csproj
      • SqlServerSlackAPI.pfx
      • SqlServerSlackAPI.sln
      • SqlServerSlackAPI.sqlproj
      • SqlServerSlackAPI
        • SqlServerSlackAPI.sqlproj
        • T4Include_Include.tt
      • UserDefinedFunctions.cs
      • packages.config
Expand file treeCollapse file tree
Open diff view settings
Collapse file

‎.gitignore‎

Copy file name to clipboard
+25Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
_UpgradeReport_Files/
2+
bin/
3+
obj/
4+
ClientBin/
5+
App_Data/
6+
[Oo]utput/
7+
ipch/
8+
[Ii]ntermediate/
9+
[Pp]ackages/
10+
_Resharper*
11+
*.user
12+
*.suo
13+
*.opensdf
14+
*.sdf
15+
*.filters
16+
*.psess
17+
*.vspx
18+
*.exe
19+
*.dll
20+
*.xap
21+
*.zip
22+
*.dbmdl
23+
UpgradeLog.htm
24+
UpgradeLog.XML
25+
[Tt]ools/[Cc]ake/
Collapse file

‎LICENSE.md‎

Copy file name to clipboard
+33Lines changed: 33 additions & 0 deletions
  • Display the source diff
  • Display the rich diff
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
#Microsoft Public License (Ms-PL)
2+
3+
Copyright (c) 2014 WCOM AB
4+
5+
This license governs use of the accompanying software. If you use the software, you accept this license. If you do not accept the license, do not use the software.
6+
7+
##1. Definitions
8+
9+
The terms "reproduce," "reproduction," "derivative works," and "distribution" have the same meaning here as under U.S. copyright law.
10+
11+
A "contribution" is the original software, or any additions or changes to the software.
12+
13+
A "contributor" is any person that distributes its contribution under this license.
14+
15+
"Licensed patents" are a contributor's patent claims that read directly on its contribution.
16+
17+
##2. Grant of Rights
18+
19+
* (A) Copyright Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free copyright license to reproduce its contribution, prepare derivative works of its contribution, and distribute its contribution or any derivative works that you create.
20+
21+
* (B) Patent Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free license under its licensed patents to make, have made, use, sell, offer for sale, import, and/or otherwise dispose of its contribution in the software or derivative works of the contribution in the software.
22+
23+
##3. Conditions and Limitations
24+
25+
* (A) No Trademark License- This license does not grant you rights to use any contributors' name, logo, or trademarks.
26+
27+
* (B) If you bring a patent claim against any contributor over patents that you claim are infringed by the software, your patent license from such contributor to the software ends automatically.
28+
29+
* (C) If you distribute any portion of the software, you must retain all copyright, patent, trademark, and attribution notices that are present in the software.
30+
31+
* (D) If you distribute any portion of the software in source code form, you may do so only under this license by including a complete copy of this license with your distribution. If you distribute any portion of the software in compiled or object code form, you may only do so under a license that complies with this license.
32+
33+
* (E) The software is licensed "as-is." You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. You may have additional consumer rights under your local laws which this license cannot change. To the extent permitted under your local laws, the contributors exclude the implied warranties of merchantability, fitness for a particular purpose and non-infringement.
Collapse file

‎README.md‎

Copy file name to clipboard
+61Lines changed: 61 additions & 0 deletions
  • Display the source diff
  • Display the rich diff
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,61 @@
1+
# SqlServerSlackAPI
2+
3+
SqlServerSlackAPI is a SQL Server CLR assembly for direct communication with Slack via TSQL
4+
5+
![Slack message from SQL Server](https://raw.githubusercontent.com/WCOMAB/SqlServerSlackAPI/master/images/sql2slack.png)
6+
7+
8+
## Usage
9+
10+
### SlackChatPostMessage
11+
12+
Table value function that posts message via HTTP to Slack Channel API and parses json response.
13+
14+
#### Parameters
15+
16+
| Name | Type | Required | Description |
17+
|-------------|-----------------|----------|------------------------------------------------|
18+
| `@Token` | nvarchar(_max_) | yes | Slack auth token |
19+
| `@Channel` | nvarchar(_max_) | yes | Channel to send message to. Can be a public channel, private group or IM channel. Can be an encoded ID, or a name. |
20+
| `@Text` | nvarchar(_max_) | yes | Text of the message to send. For message formatting see https://api.slack.com/docs/formatting |
21+
| `@UserName` | nvarchar(_max_) | no | Name of bot. |
22+
| `@IconUrl` | nvarchar(_max_) | no | URL to an image to use as the icon for the bot |
23+
24+
#### Example
25+
26+
```sql
27+
SELECT Ok,
28+
Channel,
29+
TimeStamp,
30+
Error
31+
FROM dbo.SlackChatPostMessage(
32+
'<your slack token>',
33+
'@devlead',
34+
'Hello from SQL Server',
35+
null,
36+
null
37+
)
38+
```
39+
40+
#### Resultset
41+
42+
| Name | Type | Description |
43+
|-------------|-----------------|-----------------------------------------------------------|
44+
| `Ok` | bit | Indicating success/failure, see `Error` for error details |
45+
| `Channel` | nvarchar(_max_) | Encoded channel ID |
46+
| `TimeStamp` | nvarchar(_max_) | Timestamp of the message |
47+
| `Error` | nvarchar(_max_) | Error message on failure |
48+
49+
On success the table value funcion will return something like
50+
51+
| Ok | Channel | TimeStamp | Error |
52+
|----|-----------|-------------------|-------|
53+
| 1 | D03A9F0Q5 | 1421363019.000005 | NULL |
54+
55+
On failure the you could get an error / exception for unhandled errors, but for most errors you will get an resonse like below where `Error` column contains details about the issue.
56+
57+
| Ok | Channel | TimeStamp | Error |
58+
|----|-----------|-------------------|--------------|
59+
| 0 | NULL | NULL | invalid_auth |
60+
61+
Collapse file

‎images/sql2slack.png‎

Copy file name to clipboard
33.6 KB
  • Display the source diff
  • Display the rich diff
Loading
Collapse file

‎images/sqlserver.png‎

Copy file name to clipboard
5.85 KB
  • Display the source diff
  • Display the rich diff
Loading
Collapse file

‎src/SqlServerSlackAPI.sln‎

Copy file name to clipboard
+22Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
2+
Microsoft Visual Studio Solution File, Format Version 12.00
3+
# Visual Studio 2013
4+
VisualStudioVersion = 12.0.31101.0
5+
MinimumVisualStudioVersion = 10.0.40219.1
6+
Project("{00D1A9C2-B5F0-4AF3-8072-F6C62B433612}") = "SqlServerSlackAPI", "SqlServerSlackAPI\SqlServerSlackAPI.sqlproj", "{A6E64486-E12C-474E-BC5F-9B4D03CFB498}"
7+
EndProject
8+
Global
9+
GlobalSection(SolutionConfigurationPlatforms) = preSolution
10+
Debug|Any CPU = Debug|Any CPU
11+
Release|Any CPU = Release|Any CPU
12+
EndGlobalSection
13+
GlobalSection(ProjectConfigurationPlatforms) = postSolution
14+
{A6E64486-E12C-474E-BC5F-9B4D03CFB498}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
15+
{A6E64486-E12C-474E-BC5F-9B4D03CFB498}.Debug|Any CPU.Build.0 = Debug|Any CPU
16+
{A6E64486-E12C-474E-BC5F-9B4D03CFB498}.Release|Any CPU.ActiveCfg = Release|Any CPU
17+
{A6E64486-E12C-474E-BC5F-9B4D03CFB498}.Release|Any CPU.Build.0 = Release|Any CPU
18+
EndGlobalSection
19+
GlobalSection(SolutionProperties) = preSolution
20+
HideSolutionNode = FALSE
21+
EndGlobalSection
22+
EndGlobal
Collapse file

‎src/SqlServerSlackAPI/Deploy.sql‎

Copy file name to clipboard
+121Lines changed: 121 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,121 @@
1+
/*
2+
*
3+
* Enable CLR in master database
4+
*
5+
*/
6+
USE master
7+
GO
8+
sp_configure 'show advanced options', 1;
9+
GO
10+
RECONFIGURE;
11+
GO
12+
sp_configure 'clr enabled', 1;
13+
GO
14+
RECONFIGURE;
15+
GO
16+
17+
/*
18+
*
19+
* Enable CLR in target database
20+
*
21+
*/
22+
USE SlackTestDb
23+
GO
24+
sp_configure 'show advanced options', 1;
25+
GO
26+
RECONFIGURE;
27+
GO
28+
sp_configure 'clr enabled', 1;
29+
GO
30+
31+
/*
32+
*
33+
* Remove any previously installed functions/procedures
34+
*
35+
*/
36+
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SlackChatPostMessage]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
37+
DROP FUNCTION [dbo].SlackChatPostMessage
38+
GO
39+
40+
/*
41+
*
42+
* Remove any previously installed assemblies keys from master
43+
*
44+
*/
45+
USE master
46+
IF EXISTS(SELECT name FROM sys.server_principals WHERE name = 'SqlServerSlackAPILogin')
47+
DROP LOGIN SqlServerSlackAPILogin
48+
GO
49+
50+
IF EXISTS(SELECT name FROM sys.asymmetric_keys WHERE name = 'SqlServerSlackAPIKey')
51+
DROP ASYMMETRIC KEY SqlServerSlackAPIKey
52+
GO
53+
54+
/*
55+
*
56+
* Install new assembly keys in master
57+
*
58+
*/
59+
CREATE ASYMMETRIC KEY SqlServerSlackAPIKey FROM EXECUTABLE FILE = 'C:\temp\dev\github\SqlServerSlackAPI\src\SqlServerSlackAPI\bin\debug\SqlServerSlackAPI.dll'
60+
GO
61+
CREATE LOGIN SqlServerSlackAPILogin FROM ASYMMETRIC KEY SqlServerSlackAPIKey
62+
GO
63+
GRANT EXTERNAL ACCESS ASSEMBLY TO SqlServerSlackAPILogin
64+
GO
65+
66+
/*
67+
*
68+
* Remove any previously installed assembly from target database
69+
*
70+
*/
71+
USE SlackTestDb
72+
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlServerSlackAPI' and is_user_defined = 1)
73+
DROP ASSEMBLY SqlServerSlackAPI
74+
GO
75+
76+
/*
77+
*
78+
* Install new assembly to target database
79+
*
80+
*/
81+
CREATE ASSEMBLY SqlServerSlackAPI from 'C:\temp\dev\github\SqlServerSlackAPI\src\SqlServerSlackAPI\bin\debug\SqlServerSlackAPI.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
82+
GO
83+
84+
/*
85+
*
86+
* Install procedures and functions
87+
*
88+
*/
89+
CREATE FUNCTION SlackChatPostMessage(
90+
@Token nvarchar(max),
91+
@Channel nvarchar(max),
92+
@Text nvarchar(max),
93+
@UserName nvarchar(max),
94+
@IconUrl nvarchar(max)
95+
)
96+
RETURNS TABLE(
97+
Ok bit,
98+
Channel nvarchar(max),
99+
TimeStamp nvarchar(max),
100+
Error nvarchar(max)
101+
)
102+
AS EXTERNAL NAME SqlServerSlackAPI.UserDefinedFunctions.SlackChatPostMessage;
103+
GO
104+
105+
/*
106+
*
107+
* Test created functions and procedures
108+
*
109+
*/
110+
111+
SELECT Ok,
112+
Channel,
113+
TimeStamp,
114+
Error
115+
FROM dbo.SlackChatPostMessage(
116+
'<your slack token>',
117+
'@devlead',
118+
'Hello from SQL Server',
119+
null,
120+
null
121+
)

0 commit comments

Comments
0 (0)
You are visitor 01402520 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.