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 1,888 other followers

Archive for the ‘Database Development’ Category

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 »

ODCB settings in the Windows registry on 32 and 64 bit windows

Posted by jpluimers on 2020/03/27

  • While 32-bit applications can run on 64-bit machines, they cannot use 64-bit ODBC drivers. A 64-bit application must use a 64-bit ODBC driver, and a 32-bit application must use a 32-bit ODBC driver.
  • ODBC.INI registry paths for the various permutations:
    • User DSNs for for 64-bit and 32-bit applications
      • HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
    • System DSNs for 64-bit applications on 64-bit Windows and 32-bit applications on 32-bit Windows
      • HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI 
    • System DSNs for 32-bit applications on 64-bit Windows
      • HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI

Via: [WayBack] Windows registry on 32 and 64 bit windows, which also explains ODBCINST.INI to define drivers.

–jeroen

Posted in Database Development, Development, ODBC, Power User, Windows | 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 »

Import and Export Registered SQL Servers To Other Machines: don’t bother.

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): [WayBackImport 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)

Solution

  1. Stop SSMS
  2. Make a back-up, then delete %APPDATA%\Microsoft\Microsoft SQL Server\140\Tools\Shell\RegSrvr.xml,
  3. Start SSMS.

Cause

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 »

Exporting / importing ODBC DSNs (both System and User)

Posted by jpluimers on 2020/01/28

ODBC stores the DSN information in an ODBC.INI tree in the registry.

These posts explain how to export/import individual DSN entries:

–jeroen

Posted in Database Development, Development, ODBC, Power User | Leave a Comment »

 
%d bloggers like this: