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,089 other followers

Archive for the ‘SQL Server’ Category

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 [Archive.is] XE5: Preprocessing Command Text (FireDAC) – RAD Studio documentation is not much different from the current state [Archive.is].

More background reading is at [WayBack] www.freepascal.org/~michael/articles/anydac2/anydac2.pdf 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              ,
{IF MSSQL}
       CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
          THEN CAST('Y' AS NVARCHAR) 
          ELSE CAST('N' AS NVARCHAR) 
       END HAS_CONFIGURATION      ,
{fi}
{IF FIREBIRD}
       CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
          THEN 'Y'  
          ELSE 'N'  
       END HAS_CONFIGURATION      ,
{fi}
       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.

–jeroen

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:

–jeroen

Posted in Development, Database Development, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | 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):

-- https://dev-doc.blogspot.com/2012/08/ms-sql-2008-client-ip-address-on-shared.html
-- https://blog.sqlauthority.com/2009/05/26/sql-server-find-hostname-and-current-logged-in-user-name/
-- https://blog.sqlauthority.com/2015/07/13/sql-server-how-to-change-server-name/
-- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6720817d-120f-4099-bf0e-e97fd2e26848/how-to-get-host-name-and-sql-instance-name-by-tsql?forum=transactsql#fc9e6b84-0264-424a-8aef-d03b0de6fade
select
  -- https://docs.microsoft.com/en-us/sql/t-sql/functions/connectionproperty-transact-sql?view=sql-server-2017
  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',
  -- https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017
  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',
  -- https://docs.microsoft.com/en-us/sql/t-sql/functions/version-transact-sql-configuration-functions?view=sql-server-2017
  @@Version as 'Full_Server_Version'
;

Based on parts from:

Features used:

–jeroen

Read the rest of this entry »

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

In SQL Server use `SET NOCOUNT ON` so tools taking the last modified record count won’t be confused by your trigger.

Posted by jpluimers on 2020/04/07

Interesting read: Time eating bug of the day… – Fabian S. Biehn – Google+.

TL;DR: in SQL Server use SET NOCOUNT ON so tools taking the last modified record count won’t be confused by your trigger.

Source: [WayBackTime eating bug of the day: I used a TADOQuery.ExecSQL (on Berlin) for an Up…

Related: [WayBack] sql server – ADODB affected rows return trigger’s affected rows – Stack Overflow

–jeroen

Posted in Database Development, Delphi, Development, Office VBA, Scripting, Software Development, SQL, SQL Server | Leave a Comment »

Use the Visual Studio Code mssql extension for SQL Server | Microsoft Docs

Posted by jpluimers on 2020/02/13

Since I really want to switch most of my SSMS usage to a tool being less resource intensive, as a truckload of my work is just running scripts, not browsing through data: [WayBackUse the Visual Studio Code mssql extension for SQL Server | Microsoft Docs

This tutorial shows how to use the mssql extension for VS Code. This extension allows you to edit and run Transact-SQL scripts in VS Code.

This will also make it a lot easier to run my code from a Mac.

–jeroen

Posted in .NET, Database Development, Development, Software Development, SQL Server, Visual Studio and tools, Visual Studio Code | Leave a Comment »

 
%d bloggers like this: