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

Archive for the ‘SQL Server’ 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: [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 »

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

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 »

sql server – I need a slow query on AdventureWorks (SQL 2005) – Stack Overflow

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.ProductID

Very 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:

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:

Read the rest of this entry »

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

windows – How do I limit MS SQL Server memory usage? – Server Fault

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.

  1. In Object Explorer, right-click a server and select Properties.
  2. Click the Memory node.
  3. 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 »

 
%d bloggers like this: