The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,091 other followers

Archive for the ‘Database Development’ Category

Simple query to get .NET connection string from current SSMS connection – via: Database Administrators Stack Exchange

Posted by jpluimers on 2020/09/24

I’m connected to database. I use db by Management Studio 2012 Express. Can I check connection string by click something in Management Studio?

[WayBack] sql – How to check connection string in SSMS2012? – Database Administrators Stack Exchange

I adopted the SQL statement in the answer to the above question to:

  • use more common parameter names and values
  • embed strings in quotes
    -- part names via
    -- prefer SSPI over True via
    'Data Source=''' + @@servername + '''' +
    ';Initial Catalog=''' + db_name() + '''' +
    case type_desc
        when 'WINDOWS_LOGIN' 
        then ';Integrated Security=SSPI'
        else ';User ID=''' + suser_name() + ''';Password='''''
    as DotNetConnectionString,
    -- note the below need to be URI-encoded later on:
    'sqlserver://' + suser_name() + ':password@' + @@servername + '/' + db_name() + '?param1=value&param2=value'
    as GoLangConnectionString
    -- sqlserver://username:password@host/instance?param1=value&param2=value
from sys.server_principals
where name = suser_name()

You can use this to generate connection strings for use in .NET, OLE DB, Visual Studio Code, go lang and likely many other tools.



Read the rest of this entry »

Posted in Database Development, Development, Software Development, SQL, SQL Server, SSMS SQL Server Management Studio | Leave a Comment »

FireDAC can do DBMS back-end conditional SQL via Conditional Substitution

Posted by jpluimers on 2020/09/17

Though the field-types mentioned in the problem and solution are equal (so either is wrong), the solution in [WayBackI have a little problem with FireDAC and the TStringField and TWideStringField design time generation… – Juan C. Cilleruelo – Google+ pointed out by Jeff Weir is interesting: FireDAC supports conditionals that depend on the DBMS back-end, so you can differentiate between them.

The feature is called Conditional Substitution and has been present ever since AnyDAC (which got bought by Embarcadero, transformed into FireDAC, then after Idera bought Embarcadero, the main developer got pink-slipped).

The AnyDAC documentation is in the wayback machine, though you have to disable the onload event in order to read it.

The [] XE5: Preprocessing Command Text (FireDAC) – RAD Studio documentation is not much different from the current state [].

More background reading is at [WayBack] and Cary Jensen covered it in his 2017 course on FireDAC of which you can see the free ToC.

Example from that thread:

SELECT ART.CD_ITEM                ,
       ART.CD_FAMILY              ,
       ART.CD_CATALOGUE           ,
       CAT.DS_CATALOGUE           ,
       FAM.DS_FAMILY              ,
          THEN 'Y'  
          ELSE 'N'  
       ART.DS_ITEM                ,
       ART.CD_TAX                 ,
       TAX.DS_TAX                 ,
       TAX.PRC_TAX               ,

Given the problem statement, the casts likely should have been VARCHAR instead of NVARCHAR, but the construct can be very powerful.


Posted in Database Development, Delphi, Development, Firebird, InterBase, Software Development, SQL, SQL Server | Leave a Comment »

Interesting: SQL Server FileStream and FileTable

Posted by jpluimers on 2020/09/03

Some links for my research (when writing this a few years back, I had not done a lot of SQL Server BLOB work for a while, so I missed a lot of interesting progress).


As of SQL Server 2008, one can use FileStream, which SQL Server installs on an NTFS file system local to the SQL Server instance, but is accessible over the SQL Server API. It benefits from a lot of SQL Server features (including transactional and backup related).

As of SQL Server 2012, FileTable has extended FileStream, which makes it even easier to access the files from Windows applications.

Over the years, FileStream and FileTable has improved a lot.

Some links to get a feel:


Posted in Development, Database Development, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »

Joins explained.

Posted by jpluimers on 2020/08/26

Turn your head 90 degrees counter-clockwise around the longitudinal axis [WayBack] Joins explained. – Kristian Köhntopp – Google+

Wait, let me help you:

Read the rest of this entry »

Posted in Database Development, Development, SQL | Leave a Comment »

Small query for some SQL Server client and server information

Posted by jpluimers on 2020/08/24

Sometimes in a less paved SQL Server environment you need a quick way to gather information on both the client and server. I assembled this query from various sources to help with that. It runs with few privileges (hence the use of the various *property functions):

  CONNECTIONPROPERTY('net_transport') AS net_transport,
  CONNECTIONPROPERTY('protocol_type') AS protocol_type,
  CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
  CONNECTIONPROPERTY('local_net_address') AS local_net_address,
  CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
  CONNECTIONPROPERTY('client_net_address') AS client_net_address,
  HOST_NAME() AS client_hostname,
  SUSER_NAME() LoggedInUser,
  @@servername AS 'ServerName\InstanceName',
  SERVERPROPERTY('ServerName') AS 'ServerName',
  SERVERPROPERTY('MachineName') AS 'Windows_MachineName',
  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',
  SERVERPROPERTY('instanceName') AS 'InstanceName',
  SERVERPROPERTY('IsClustered') AS 'IsClustered',
  SERVERPROPERTY('Edition') AS 'Edition',
  @@Version as 'Full_Server_Version'

Based on parts from:

Features used:


Read the rest of this entry »

Posted in Database Development, Development, SQL Server | Leave a Comment »

%d bloggers like this: