question

MPham-2003 avatar image
0 Votes"
MPham-2003 asked AlonZentner-9660 published

extract database name from database connection string

Hi all,

I'm trying to get the database name from connection string. It only works some of the time. Just wonder if I miss anything in my SQL script:

declare @dbconn varchar (500)

set @dbconn = 'Data Source=test-sql01d\NEW_NIDEV01;Initial Catalog=Restricted;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=false;'

select substring(@dbconn,(CHARINDEX('g=',@dbconn) + 2),CHARINDEX('T',right(@dbconn,79)))


if my database name is 'tested' then it worked but if my database name is something else like 'Restricted' then it came back with just Restr



sql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is your SQL Server version?

 SELECT @@VERSION;



0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Please try the following:

SQL

 DECLARE @dbconn varchar (500)
  , @init_catalog VARCHAR(100)
  , @db_name VARCHAR(100)
  , @separator CHAR(1) = ';'
  , @separator2 CHAR(1) = '=';
    
 SET @dbconn = 'Data Source=test-sql01d\NEW_NIDEV01;Initial Catalog=Restricted;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=false;';
    
 SET @init_catalog =  TRY_CAST('<root><r>' + 
             REPLACE(@dbconn, @separator, '</r><r>') + 
             '</r></root>' AS XML).value('(/root/r[contains(./text()[1], "Initial Catalog")])[1]','VARCHAR(100)');
 SELECT @init_catalog AS initial_catalog;
    
 SET @db_name =  TRY_CAST('<root><r>' + 
             REPLACE(@init_catalog, @separator2, '</r><r>') + 
             '</r></root>' AS XML).value('(/root/r/text())[2]','VARCHAR(100)');
 SELECT @db_name AS [db_name];

Output

 +------------+
 |  db_name   |
 +------------+
 | Restricted |
 +------------+
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MPham-2003 avatar image
0 Votes"
MPham-2003 answered

thank you so much YitzhakKhabinsky-0887. It worked!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

The database name is not required in the connection string. So that is not a universal solution to your question.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlonZentner-9660 avatar image
0 Votes"
AlonZentner-9660 answered AlonZentner-9660 published



CREATE FUNCTION [dbo].[fn_GetDbNameFromConnectionString] (@ConnectionString VARCHAR(MAX) )

 RETURNS sysname
 AS
 BEGIN
   
  DECLARE @Result sysname
  DECLARE @DBNameStart INT
  DECLARE @DBNameEnd INT
  DECLARE @DBNameLength INT

  SET @DBNameStart = (SELECT CHARINDEX('Initial Catalog=',@ConnectionString,0)+LEN('Initial Catalog='));
  SET @DBNameEnd = (SELECT CHARINDEX(';',@ConnectionString,@DBNameStart));
  SET @DBNameLength = (SELECT @DBNameEnd-@DBNameStart);
    
  SELECT @Result = SUBSTRING(@ConnectionString,@DBNameStart,@DBNameLength); 
    

  RETURN @Result
    
 END
 GO


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.