Product:

Microsoft SQL server 2016

Microsoft Windows 2016

Problem:

How add data from one table to other table?

Suggested solution:

For the example we have downloaded the StackOverflow2013 database from here

https://www.brentozar.com/archive/2021/03/download-the-current-stack-overflow-database-for-free-2021-02/

Create a new table;

CREATE TABLE [dbo].[Staff](
[Id] [int] NOT NULL,
[CreationDate] [datetime] NOT NULL,
[DisplayName] [nvarchar](40) NOT NULL,
[DownVotes] [int] NOT NULL,
[LastAccessDate] [datetime] NOT NULL,
[Location] [nvarchar](100) NULL,
[Reputation] [int] NOT NULL,
[UpVotes] [int] NOT NULL,
[Views] [int] NOT NULL,
[AccountId] [int] NULL,
CONSTRAINT [PK_Staff_Id] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

To be able to copy IDENTITY values between tables you need to use IDENTITY_INSERT and specify the columns in the INSERT INTO statement (not used in our example) .

SET IDENTITY_INSERT dbo.Employee ON

To copy all rows for users from Denmark to the new table;

INSERT INTO dbo.Staff
SELECT
    [Id],
    [CreationDate] ,
    [DisplayName] ,
    [DownVotes] ,
    [LastAccessDate],
    [Location] ,
    [Reputation] ,
    [UpVotes] ,
    [Views] ,
    [AccountId]
FROM windows2016.stackoverflow2013.dbo.users
WHERE location = 'Denmark'

To set the value to zero for column DownVotes when View is less than 100;

UPDATE [StackOverflow2013].[dbo].[Staff]
  SET DownVotes = 0
  WHERE views < 100

Select only one row by use of ID column;

SELECT *
FROM StackOverflow2013.dbo.staff
WHERE id = 19 AND location = 'denmark'

UPDATE StackOverflow2013.dbo.staff
SET DownVotes = 1
WHERE id = 19 AND location = 'denmark'

SELECT 
       [Id]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[LastAccessDate]
      ,[Location]
      ,[Reputation]
      ,[UpVotes]
      ,[Views]
      ,[AccountId]
  FROM [StackOverflow2013].[dbo].[Staff]
  ORDER BY views DESC

More information:

http://www.sql-server-helper.com/error-messages/msg-8101.aspx

https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/

https://www.sqlshack.com/overview-of-the-sql-insert-statement/

https://www.tutorialspoint.com/sql/sql-order-by.htm

Product:

Microsoft SQL server 2016

Issue:

How do I give access only to a table?

Solution:

Ensure the user exist in the database. Enter below SQL query to create a role (RESTRICTED_SELECT), and set the SELECT to a table (TABLE_NAME) for that role. Last add the user (USER_NAME) to the role.  Replace values with your user and table names.

Or better add a AD group to the role, then the IT department can add people to that AD group to get access to the table.

CREATE ROLE RESTRICTED_SELECT
GO
GRANT SELECT ON TABLE_NAME TO RESTRICTED_SELECT
GO
SP_ADDROLEMEMBER RESTRICTED_SELECT , USER_NAME

If i want give access to a function, for instance the user should be able to see Active Monitor from SSMS enter below SQL query.

USE master;
CREATE SERVER ROLE SQLMonitor ;
GRANT VIEW SERVER STATE TO SQLMonitor ;
GO

To view the Data File I/O pane, besides VIEW SERVER STATE, the login must be granted CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION permissions.

To kill a process, it’s necessary to be a member of the sysadmin role.

To add the user to a server role enter:

sp_addsrvrolemember USER_NAME, SQLMonitor

More Information:

https://www.sqlshack.com/sql-server-activity-monitor/

https://www.codeproject.com/Tips/1103206/SQL-Server-Grant-Permission-to-Particular-Table

https://www.guru99.com/sql-server-create-user.html

https://sqlserverplanet.com/dba/using-sp_addrolemember

Product:

Microsoft SQL server 2016

Issue:

How log start and stop times of SP in your database?

Suggested solution:

Create a table to store time values, and create a SP that you call from other SP to update the table.

Create the log table:

CREATE TABLE [dbo].[_log_logged](
[LogID] [BIGINT] NULL,
[starttime] [DATETIME] NULL,
[endtime] [DATETIME] NULL,
[iscomplete] [INT] NULL,
[runstatus] [INT] NULL,
[durationInSeconds] [INT] NULL,
[whatProcess] [VARCHAR](100) NULL
) ON [PRIMARY]
GO

Create the SP to call to create log values:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[_logger] @LogID [INT], @StartTime [DATETIME],@EndTime [DATETIME], @WhatProcess [VARCHAR](100), @NewLogID [INT] OUT 
AS BEGIN 
SET NOCOUNT ON;
DECLARE @RunTimeSeconds INT,
@RunStatus INT,
@IsComplete INT,
@NewLogRowID INT,
@myDuration NUMERIC(20,10)

IF @LogID IS NULL
SET @LogID = 0

IF @LogID = 0
BEGIN 
SET @RunStatus = 1
SET @IsComplete = 0

SET @LogID = (SELECT ISNULL((MAX(LogID)),0) + 1 AS maxID FROM _log_logged)
INSERT INTO _log_logged(LogID
,StartTime
,runStatus
,IsComplete
,WhatProcess
)
SELECT @LogID,
@StartTime, 
@RunStatus,
@IsComplete,
@WhatProcess

END

ELSE IF @LogID != 0
BEGIN
SET @RunStatus = 0
SET @IsComplete = 1
UPDATE a
SET EndTime = @EndTime,
IsComplete = @IsComplete,
RunStatus = @RunStatus,
whatProcess = @WhatProcess,
DurationInSeconds= DATEDIFF(SECOND,@StartTime, @EndTime) 
FROM _log_logged a
WHERE a.LogID = @LogID

END

SET @NewLogID = @LogID
END
GO

Add below code to your SP, to enable them to log values to the above table.

-- =============================================
-- Author: 
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE [dbo].[USP_ThisIsTheTest]
AS
BEGIN

SET NOCOUNT ON;
declare @StartTime datetime = GETDATE();
declare @EndTime datetime 
declare @logid int
DECLARE @NewLogID INT
DECLARE @whatProcessText VARCHAR(100) = 'Your name of the sp to be logged';


EXEC [dbo].[_logger] @LogID = 0
,@StartTime = @StartTime
,@EndTime = NULL
,@whatProcess = @whatProcessText
,@NewLogID = @NewLogID OUTPUT

-- =============================================
-- Here should your code be for the process to execute


WAITFOR DELAY '00:00:10.000'


-- =============================================
-- Log the end of the sp process with this call:

SET @EndTime = GETDATE()
EXEC [dbo].[_logger] @LogID = @NewLogID, @StartTime = @StartTime ,@EndTime = @EndTime ,@whatProcess = @whatProcessText ,@NewLogID = @NewLogID OUTPUT


END

Run the SP to test the log table.

The result can look like this:

More information:

SLEEP Command in Sql Server

https://www.mssqltips.com/sqlservertip/2003/simple-process-to-track-and-log-sql-server-stored-procedure-use/

https://www.sqlservercentral.com/articles/logging-and-error-handling-for-sql-stored-procedures

https://www.tech-recipes.com/database/sql-server-error-logging-and-reporting-within-a-stored-procedure/ 

Product:

Microsoft SQL server 2016

Problem:

Why should collation be an issue?

Solution:

Ensure that the SQL server and the database all have the same collation setting from the beginning – and try to not change it.

But some products demand a specific collation setting (like cognos) – that is equal to the SQL server.

To get around differences, between databases, in each select statement use collate;

More information:

https://kimconnect.com/latin1_general_ci_ai-vs-sql_latin1_general_cp1_ci_as/

The Windows collation can use an index while comparing unicode and non-unicode, such as nvarchar to varchar, with a slight performance cost. The SQL collation cannot use the index while comparing data in such scenarios.

Some characters that are treated as independent letters. For example, operator LIKE ‘%ß%’ will return that exact match in SQL collation, while Windows collation will also return LIKE ‘%ss%’ as the expanded character of ß to ss.

Mixing collations within the database can cause errors such as this: ‘Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the equal to operation.’

Collation can be specified in a statement to instruct the SQL engine to use such collation ad-hoc (e.g. SELECT * FROM SomeTable WHERE SomeField COLLATE SQL_Latin1_General_CP1_CI_AS = N’ßeta’)

https://docs.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-ver16

https://www.sqlshack.com/sql-server-collation-introduction-with-collate-sql-casting/

https://www.sqlshack.com/the-collate-sql-command-overview/

Product:

Microsoft SQL server 2016

Issue:

When you try to script out the database schema, to be able to create a empty database on other server, we get a error when we select to much. If you have more than 42000 views in a database you will get issues with the wizard.

The wizard – Generate Scripts – from task menu on the database, will give error if the database contain to many objects.

Can also be a an issue if you use an older versions of SSMS or that you have encrypted SP in the database.

SSMS can crash when you try to generate the script with to many objects selected.

Solution:

Manually script out all the views in a table.

select schema_name(v.schema_id) as schema_name,

       v.name as view_name,

       v.create_date as created,

       v.modify_date as last_modified,

       m.definition

from sys.views v

join sys.sql_modules m 

     on m.object_id = v.object_id

 order by schema_name,

          view_name;

Then copy the result from the table column to a new query window for the new database.

Change that each line (command) end with ;

Then run the new SQL statements, to recreate the views in the new database.

More information:

https://www.gethynellis.com/2020/07/ssms-error-generating-scripts.html

https://dataedo.com/kb/query/sql-server/list-views-with-their-scripts

https://dwhanalytics.wordpress.com/tag/generate-create-script-for-all-views-in-sql-server-database/

https://sqlblog.org/2011/11/03/the-case-against-information_schema-views

Product:

Microsoft SQL server

Problem:

What version of SQL server is it?

The different tools to SQL server are often installed in folder C:\Program Files\Microsoft SQL Server\150, where the number represent the version of SQL server.

The Database default folder is C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA, where the number 15 represent the SQL version – in this example that is SQL server 2019.

Solution:

This page show the version numbers:

https://sqlserverbuilds.blogspot.com/2014/01/sql-server-internal-database-versions.html

Legend: ? = still investigating, RTM = Release to manufacturing, SPn = Service Pack n, CTP = Community Technology Preview (beta release).

To see the compatibility level on the database enter:

EXEC sp_helpdb;

Product:

Microsoft SQL server 2016

Visual Studio developing

Issue:

When deploy/publish a database change to a SQL server 2016 we get a error;

The remote script failed with exit code 1

The action Publish DacPac on server failed

Could not find the file: Microsoft.SqlServer.Dac.dll

Possible solution:

Check on the target SQL server if the needed files are installed in correct folder.

Depending on version of SQL server, the files are in folders like 120,130,140.

Folder 150 is for SQL server 2019.

For SQL server version 2016 the files should be in folder 130

C:\Program Files\Microsoft SQL Server\130\dac\bin

If the folder is missing, check what version of SQL server that is installed.

Can be that SQL server is installed, but the supporting tools like Microsoft SQL Server Data Tools and Microsoft SQL Server Data-Tier Applications Framework (x64) is installed, but at an different version.

Go to control panel – program and features – and check what is installed.

Download and install the correct version you need.

More information:

https://sqlserverbuilds.blogspot.com/2014/01/sql-server-internal-database-versions.html

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/be484b63-a6cc-4dac-a2c2-78a56ff5b502/where-is-the-microsoftsqlserverdacdll-that-includes-support-for-sql-server-2014?forum=ssdt

Product:

Microsoft SQL server 2016

Issue:

A rollback of a deadlock is hung on itself. You have try to kill a process that was hung, but not it is stuck in suspended state.

Suggested solution:

Go to SSMS – Activity Monitor – sort on the Command column to find the rollback processes. Note down the ID

Check if this process is doing anything with this command:

select percent_complete, * from sys.dm_exec_requests where session_id = 69  -- change to your id

If the values does not change from 0%, then the process is most likely not doing anything.

From the result of above statement, you can of wait_resource column find out what table is creating the lock.

wait_resource = “KEY: 40:844424931901440 (7210abc) ” =  Database_Id, HOBT_Id

The first number is the database – use this SQL query to find what:

SELECT     name    FROM    sys.databases    WHERE      database_id=40;

The second number is the table that gives the issue – use this SQL query in the database to find where:

SELECT 

    sc.name as schema_name, 

    so.name as object_name, 

    si.name as index_name

FROM sys.partitions AS p

JOIN sys.objects as so on 

    p.object_id=so.object_id

JOIN sys.indexes as si on 

    p.index_id=si.index_id and 

    p.object_id=si.object_id

JOIN sys.schemas AS sc on 

    so.schema_id=sc.schema_id

WHERE hobt_id = 844424931901440;

If you can not find the other blocking process and stop it, and a recreate of a index does not help, then your option is to restart the SQL server service in Microsoft Windows.

Obtain some downtime on the SQL server, and restart the service in hope it will solve the deadlock.

https://www.mssqltips.com/sqlservertip/6307/how-to-stop-and-start-sql-server-services/

More information:

LCK_M_S

https://www.brentozar.com/archive/2014/03/happens-issue-kill/

https://linuxhint.com/sql-server-kill-spid/

https://littlekendra.com/2016/10/17/decoding-key-and-page-waitresource-for-deadlocks-and-blocking/

Product:
Microsoft SQL server 2016

Issue:

Get a warning when you create a table with a primary key.

Warning! The maximum key length for a clustered index is 900 bytes. The index ‘xyz’ has maximum length of 8024 bytes. For some combination of large values, the insert/update operation will fail.

CREATE TABLE [fun_table]

([the_name] [nvarchar](100) NULL,

[the_value] [sql_variant] NOT NULL,  -- will give the error

 CONSTRAINT [PK_thevalue] PRIMARY KEY CLUSTERED 

([the_value] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Suggested solution:

Change the sql_variant to a nvarchar() instead if you are going to use it in a index.  This is a warning that if you store more than 900 bytes in the column the_value, it will be truncated at the index/primary key.

More information:

From the internet – but this facts change with what version of Microsoft SQL server you are using.

As a general rule, you should avoid using SQL Server’s sql_variant data type. sql_variant is limited:

• Variants can’t be part of a primary or foreign key in SQL server before 2005.
  
• Variants can’t be part of a computed column.
• Variants won’t work with LIKE in a WHERE clause.
• OLE DB and ODBC providers automatically convert variants to nvarchar(4000)

To avoid problems, always explicitly convert sql_variant data types as you use them.

But some people claim that SQL_VARIANT is a fair bit faster than going trough VARCHAR conversions for your columns.

Warning! The maximum key length is 900 bytes. The index has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.

https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver16

https://www.visualbasicplanet.info/sql-server-2000/sqlvariant.html

https://stackoverflow.com/questions/13823130/are-there-any-benefits-to-using-sql-variant-over-varchar-in-sql-server

https://stackoverflow.com/questions/9039455/should-i-use-sql-variant-data-type

Product:

Microsoft Windows 2019 server

Issue:

When setting up a new server, where you want to create the same shared folders as on a existing server. How do you easy transfer the folder setup?

Solution:

You can check the existing file shares on your old server by go to control panel – administrative tools – Computer Management.

Under Shared folders – Shares, can you se the existing file shares on the server. By dubbleclick on a share, you can see permissions and other settings.

Most of this are stored in the Windows Registry. At [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Shares] branch.

To move the shares to other server, start REGEDIT program from start menu, on the old server.

Go to branch HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Shares.

Right click shares and select Export.

Save it in a file, called shares.reg.

Copy the file shares.reg over to the new Windows server where you want the shares to be created.

Login to the new server as local administrator.

Double click on the shares.reg file.

Click YES to the question if it should update the server with this values.

Click OK.

Then you must restart the server or the server service. Go to a command prompt as administrator.

Enter this to restart the server service;

Net Stop Server

Net Start Server

Then you should have the file share. On the new Windows server, go to the folder that you want to share, and right click and select properties.

Under Sharing tab and Advanced Sharing button, you should now see the same setup as the old other server.

Please use $ after share name when you create them, then the file share is hidden and will not show up when someone browse the network. You need to know the share name and have access to it to be able to connect to it from other computer.

More Information:

https://www.tgrmn.com/web/kb/item133.htm