 | Lars Fosdal on Security alarm provider Woonve… |
 | Thomas Mueller on Question got closed in May 202… |
 | Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
Posted by jpluimers on 2020/09/29
Cool, this works in a Mac and Linux too: mssql extension for VS Code.
Links:
–jeroen
Posted in .NET, Database Development, Development, Software Development, SQL Server, Visual Studio and tools, vscode Visual Studio Code | Leave a Comment »
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:
select
-- part names via https://wiert.me/2012/11/07/netc-sqlclient-connectionstring-keys/
-- prefer SSPI over True via https://wiert.me/2010/10/19/solution-for-ole-db-provider-connecting-to-sql-server-giving-error-multiple-step-ole-db-operation-generated-errors-check-each-ole-db-status-value-if-available-no-work-was-done/
'Data Source=''' + @@servername + '''' +
';Initial Catalog=''' + db_name() + '''' +
case type_desc
when 'WINDOWS_LOGIN'
then ';Integrated Security=SSPI'
else ';User ID=''' + suser_name() + ''';Password='''''
end
as DotNetConnectionString,
-- note the below need to be URI-encoded later on:
'sqlserver://' + suser_name() + ':password@' + @@servername + '/' + db_name() + '?param1=value¶m2=value'
as GoLangConnectionString
-- sqlserver://username:password@host/instance?param1=value¶m2=value
-- https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn
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.
Related:
–jeroen
Posted in Database Development, Development, Software Development, SQL, SQL Server, SSMS SQL Server Management Studio | Leave a Comment »
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 [WayBack] I 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 »
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 Database Development, Development, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Leave a Comment »
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
Posted in Database Development, Development, SQL Server | Leave a Comment »
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: [WayBack] Time 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 »
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: [WayBack] Use 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, vscode Visual Studio Code | Leave a Comment »
Posted by jpluimers on 2020/01/29
When migrating to a new machine, or syncing settings between machines, I hoped these are useful steps (in reverse order: export, then import, just like to reset a machine you turn it off first, then on): [WayBack] Import and Export Registered SQL Servers To Other Machines.
TL;DR: don’t bother exporting the SQL Server login passwords over: doing so will break your registered servers list.
Trying this myself between co-workers, exporting from one person, then importing for another can cause a very unstable SSMS environment giving errors like these during startup even using SSMS 2017:
TITLE: Registered Servers ------------------------------ Sleutel is niet geldig voor gebruik in opgegeven status. (System.Security) ------------------------------ BUTTONS: OK ------------------------------ =================================== Sleutel is niet geldig voor gebruik in opgegeven status. (System.Security) ------------------------------ Program Location: at System.Security.Cryptography.ProtectedData.Unprotect(Byte[] encryptedData, Byte[] optionalEntropy, DataProtectionScope scope) at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.ProtectData(String input, Boolean encrypt) at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.get_SecureConnectionString() at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.get_ConnectionString() at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.get_ServerName() at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServerTree.AddRegisteredServerNode(RegisteredServer regSrv, TreeNodeCollection nodes)
and errors like these when exporting information:
TITLE: Export Registered Servers ------------------------------ The operation 'Export' failed. (Microsoft.SqlServer.Management.RegisteredServers) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: Serialization operation on RegisteredServer[@Name='servername'] has failed. (Microsoft.SqlServer.Management.Sdk.Sfc) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&LinkId=20476 ------------------------------ Sleutel is niet geldig voor gebruik in opgegeven status. (System.Security) ------------------------------ BUTTONS: OK ------------------------------ =================================== The operation 'Export' failed. (Microsoft.SqlServer.Management.RegisteredServers) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&LinkId=20476 ------------------------------ Program Location: at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore.Export(SfcInstance obj, String file, CredentialPersistenceType cpt) at Microsoft.SqlServer.Management.RegisteredServers.Utils.Export(Object obj, String filename, Boolean includeUserNames, ServerType serverType) at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersContextMenuManager.ContextExport(Object sender, EventArgs e) =================================== Serialization operation on RegisteredServer[@Name='SQLServerName'] has failed. (Microsoft.SqlServer.Management.Sdk.Sfc) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&LinkId=20476 ------------------------------ Program Location: at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.Write(XmlWriter instanceWriter, Object instance, Dictionary`2 namespaces) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.WriteAllInstances() at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.Write(XmlWriter xmlWriter) at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore.Export(SfcInstance obj, String file, CredentialPersistenceType cpt) =================================== Sleutel is niet geldig voor gebruik in opgegeven status. (System.Security) ------------------------------ Program Location: at System.Security.Cryptography.ProtectedData.Unprotect(Byte[] encryptedData, Byte[] optionalEntropy, DataProtectionScope scope) at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.ProtectData(String input, Boolean encrypt) at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.get_SecureConnectionString() at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer.GetConnectionStringWithEncryptedPassword(CredentialPersistenceType cpt) at Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore.FilterProperty(SfcSerializer serializer, FilterPropertyEventArgs propertyArgs) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.WriteInternal(XmlWriter instanceWriter, Object instance, Dictionary`2 namespaces) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.Write(XmlWriter instanceWriter, Object instance, Dictionary`2 namespaces)
%APPDATA%\Microsoft\Microsoft SQL Server\140\Tools\Shell\RegSrvr.xml,You cannot import SQL Server credentials exported on another machine. It will break your server registrations list, and you cannot repair them.
See:
More background:
–jeroen
Posted in Database Development, Development, SQL Server | Leave a Comment »
Posted by jpluimers on 2019/11/28
[WayBack] sql server – I need a slow query on AdventureWorks (SQL 2005) – Stack Overflow:
Medium slow:
SELECT * FROM Sales.SalesOrderDetail s INNER JOIN Production.Product p ON s.ProductID = p.ProductIDVery slow:
SELECT * FROM Production.TransactionHistory th INNER JOIN Production.TransactionHistoryArchive tha ON th.Quantity = tha.Quantity
It is based on the output of [WayBack] sql server – Query to list number of records in each table in a database – Stack Overflow, which originally ommited tables starting with dt (as those were be used for the “Database Diagram” in the SQL Server 7/2000 era using tables like dtProperties), but which I adopted using:
object_id <= 255 as a criterion for SQL Server System Objects.The query below does not support SQL Server 2000, where you would have to use things like objectproperty, but since by now even the [WayBack] documentation has been retired on the Microsoft site, you need to read [WayBack] Get list of tables but not include system tables (SQL Server 2K)? – Stack Overflow
In the end, it filters out tables like dbo.sysdiagrams that are generated by SQL Server Management Studio (SSMS), which are SSMS System Tables, but technically not SQL Server System Tables.
select
s.name as SchemaName,
t.name as TableName,
i.name as indexName,
p.rows,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
from
sys.tables t
inner join
sys.schemas s on t.schema_id = s.schema_id
inner join
sys.indexes i on t.object_id = i.object_id
inner join
sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
inner join
sys.allocation_units a on p.partition_id = a.container_id
where
t.is_ms_shipped = 0 -- not internal to SQL Server
and
(not exists (
select ep.major_id
from sys.extended_properties ep
where
ep.major_id = t.object_id and
ep.minor_id = 0 and
ep.class = 1 and
ep.name = N'microsoft_database_tools_support'
)
) -- not internal to SQL Server Management Studio
and
i.index_id <= 1
group by
s.name, t.name, i.object_id, i.index_id, i.name, p.rows
order by
s.name, t.name
The counts for the [WayBack] GitHub version of AdventureWorks is this:
Posted in Database Development, Development, Software Development, SQL Server | Leave a Comment »
Posted by jpluimers on 2019/11/26
Sometimes you need this so it easier to run other services on the same system: [WayBack] windows – How do I limit MS SQL Server memory usage? – Server Fault.
Thanks wfaulk.
From How to configure memory options using SQL Server Management Studio:
Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an instance of SQL Server.
- In Object Explorer, right-click a server and select Properties.
- Click the Memory node.
- Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.
You can also do it in T-SQL using the following commands (example):
exec sp_configure 'max server memory', 1024 reconfigure
Related:
–jeroen
Posted in Database Development, Development, SQL Server | Leave a Comment »
The Wiert Corner - irregular stream of stuff 