Created
July 20, 2023 15:33
-
-
Save JerryNixon/7868aa3f8233b3c2181d59a5a111c8b1 to your computer and use it in GitHub Desktop.
Build a connection string
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP FUNCTION IF EXISTS ConnectionString; | |
GO | |
CREATE FUNCTION ConnectionString(@includeProperties bit = 1) | |
RETURNS nvarchar(1000) | |
AS | |
BEGIN | |
-- Retrieve server name | |
DECLARE @hostName nvarchar(128); | |
SET @hostName = CAST(host_name() AS nvarchar(128)); | |
DECLARE @machineName nvarchar(128); | |
SET @machineName = CAST(SERVERPROPERTY('MachineName') AS nvarchar(128)); | |
SET @machineName = CASE WHEN @hostName = @machineName THEN '(local)' ELSE @machineName END; | |
DECLARE @instanceName nvarchar(128); | |
SET @instanceName = CAST(SERVERPROPERTY('InstanceName') AS nvarchar(128)); | |
SET @instanceName = LEFT(@instanceName, CHARINDEX('#', @instanceName + '#') - 1); | |
SET @instanceName = CASE WHEN @instanceName = 'LOCALDB' THEN 'MSSQLLocalDB' ELSE @instanceName END; | |
-- Build server name | |
DECLARE @serverName nvarchar(128); | |
SET @serverName = CONCAT(@machineName, '\', @instanceName); | |
-- Retrieve database name | |
DECLARE @databaseName nvarchar(128); | |
SET @databaseName = DB_NAME(); | |
-- Retrieve current login | |
DECLARE @currentLogin nvarchar(128); | |
SET @currentLogin = CAST(SUSER_SNAME() AS nvarchar(128)); | |
-- Check if MARS is enabled | |
DECLARE @isMarsEnabled bit; | |
SET @isMarsEnabled = CASE WHEN CONNECTIONPROPERTY('IsMARS') = 1 THEN 1 ELSE 0 END; | |
-- Check if connection is encrypted | |
DECLARE @isEncrypted bit; | |
SET @isEncrypted = CASE WHEN CONNECTIONPROPERTY('Encrypt') = 1 THEN 1 ELSE 0 END; | |
-- Check if integrated security is used | |
DECLARE @isIntegratedSecurity bit; | |
SET @isIntegratedSecurity = CASE WHEN CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS bit) = 1 THEN 1 ELSE 0 END; | |
-- Check application name | |
DECLARE @applicationName nvarchar(128); | |
SET @applicationName = APP_NAME(); | |
-- Retrieve language | |
DECLARE @currentLanguage nvarchar(128); | |
SET @currentLanguage = CAST(@@language AS nvarchar(128)); | |
-- Retrieve collation | |
DECLARE @collation nvarchar(128); | |
SET @collation = CAST(DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS nvarchar(128)); | |
-- Retrieve compatibility level | |
DECLARE @compatibilityLevel tinyint; | |
SET @compatibilityLevel = CONVERT(tinyint, DATABASEPROPERTYEX(DB_NAME(), 'CompatibilityLevel')); | |
-- Build connection string | |
DECLARE @connectionString nvarchar(1000); | |
SET @connectionString = N'Data Source=' + @serverName + | |
N';Initial Catalog=' + @databaseName + | |
N';User Id=' + @databaseName + | |
N';Password=' + @databaseName + | |
CASE WHEN @includeProperties = 1 THEN | |
CONCAT(N';Integrated Security=', CASE WHEN @isIntegratedSecurity = 1 THEN N'True' ELSE N'False' END, | |
N';MultipleActiveResultSets=', CASE WHEN @isMarsEnabled = 1 THEN N'True' ELSE N'False' END, | |
N';Encrypt=', CASE WHEN @isEncrypted = 1 THEN N'True' ELSE N'False' END, | |
N';Application Name=', @applicationName, | |
N';Language=', @currentLanguage, | |
N';Collation=', @collation, | |
N';CompatibilityLevel=', CAST(@compatibilityLevel AS nvarchar(10))) | |
ELSE N'' END; | |
RETURN @connectionString; | |
END; | |
GO | |
SELECT dbo.ConnectionString(0) AS AdoConnStr; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment