SQL Server
Microsoft SQL Server powers your entire data estate by supporting structured and unstructured data sources. It builds on previous versions of SQL Server, which have been industry leading for four years in a row and a leader in TPC-E. It scales to petabytes of data and allows customers to process big data through PolyBase using T-SQL over any data. SQL Server has also been the least vulnerable database during the last seven years. SQL Server brings data insights with business intelligence capabilities that provide analytics at a fraction of the cost on any device along with advanced analytics with support for R and Python.
-
SQL 2017: "SET FMTONLY ON" doesn't return any resultset on UDF w/ COMPATIBILITY_LEVEL=140
Our application uses at various places the SchemaOnly attribute (see https://msdn.microsoft.com/en-us/library/system.data.commandbehavior(v=vs.110).aspx). Lately, we noticed some strange errors when the application runs against a DB on SQL Server 2017. I boiled the issue down to the attached repro script in T-SQL, that shows clearly that SQL Server doesn' return any metadata, if a table-valued function is called using SET FMTONLY ON with COMPATIBILITYLEVEL=140. But it works as expected using COMPATIBILITYLEVEL=130. As I didn't find any word on this breaking change in the docs, I consider this a bug.
23 votes -
SQL Server Assertion: File: <lobss.cpp>, line = 725 Failed Assertion = '0' Should never happen.
DBCC CHECKDB reports no errors, I rebuilt the indexes just to be sure. It happens when running this query:
WITH t1 AS
(
SELECT [Endpoints].[Name] AS [f1],[MatchLocations].[Location] AS [f2],ISNULL(SUM([Matches].[LastCount]), 0) AS [f3],ROWNUMBER() OVER (ORDER BY [Endpoints].[Name] ASC) AS [f4]
FROM MatchLocations
INNER JOIN [Matches] ON ([MatchLocations].[Id] = [Matches].[MatchLocationId])
INNER JOIN [Endpoints] ON ([Endpoints].[Id] = [MatchLocations].[EndpointId])
GROUP BY [Endpoints].[Name],[MatchLocations].[Location]
)
SELECT [t1].[f1] AS [t1f1],[t1].[f2] AS [t1f2],[t1].[f3] AS [t1f3]
FROM t1
WHERE ([t1].[f4] BETWEEN 1 AND 100)
ORDER BY [t1].[f4] ASCWhen the SUM and GROUP BY are removed, it runs just fine. It happens from time to…
22 votes -
VS 2017 SSDT SSIS Dark Theme fail
VS 2017 SSDT SSIS Dark Theme: Annotations and dataflow row numbers are in white text, therefore invisible.
22 votesUpvotes: 4
<=-=Oct 12 2017 11:54AM=-=>Agreed – it’s pretty much unusable with the dark theme. See attachment of screenshot.
<=-=Oct 12 2017 11:55AM=-=>Apparently I can’t add an attachment so for a screenshot look here: https://i.imgur.com/X2PfTp7.png
<=-=Oct 25 2017 9:23AM=-=>I am also getting this. I thought I was going crazy! When I was using VS 2015 my canvas color was black, so the dark theme worked. But now that I have installed windows 10 and VS 2017 fresh, and the white text on cream background is totally unreadable. Is there any way to change the canvas color?
-
Query Store SELECT Performance
Running a query against a large Query Store repository (10 GB) can be really slow if filtering on runtime stats intervals.
It looks like an index is missing on sys.querystoreruntimestats (runtimestatsintervalid).
22 votesUpvotes: 3
<=-=May 27 2017 6:38AM=-=>Thank you for taking the time to post this issue! We understand that this could be an important performance improvement.
Performance tuning is of such scenarios is not a trivial task. Change of such feature as Query Store can speed up one scenario and lead to degradation in others. We�ll look more into this particular use case and see what we can do. -
MSOLEDBSQL
When trying to use the new MSOELDBSQL driver as a linked server, any attempt to run a query as simple as
SELECT * FROM SERVER.master.sys.databases
results in the error message "Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.", even in the trivial case where you set up a loopback server to the same instance.
There is a workaround: if you first say BEGIN DISTRIBUTED TRANSACTION, you can access the linked server.
21 votes -
Can't SHRINK data files due to off-row LOB in large tables
There are times where I believe it's very important to be able to SHRINK (EMPTYFILE) database files that are on obsolete storage technology in an online manner. An example is moving an secondary data file from older HDD technology to newer SSD technology in an online manner.
However, when shrinking a database ROWS data file that has LOB data where the table has many rows it requires a table scan for each piece of off-row LOB data encountered. While this table scan occurs a LCKMSCH_S lock occurs on the table preventing operational queries from accessing the data, resulting…
20 votesUpvotes: 1
-
ssdt build does support 'CREATE OR ALTER'
'CREATE OR ALTER' in StoredProcedure .sql files in any Database project show errors in Visual Studio 2015 SSDT
Build database, schema compare.20 votesUpvotes: 23
<=-=Aug 1 2017 1:08PM=-=>Would be great to get this added.
<=-=Nov 6 2017 1:42AM=-=>This is supported for “non build” scripts, not sure the use case for having for “build” files
<=-=Nov 7 2017 7:49PM=-=>The scenario for supporting the CREATE OR ALTER in a build .sql for a stored procedure, is as follows:
Since the [Test, modify, fail, modify Stored Proc, modify Test -> pass] cycle is very labour intensive currently in the VS DB project with the DB SQL Test feature, I have been experimenting with how to make writing the SQL for the test in a quicker manner.
I have written a SQL file that has [Arrange, Act, Assert] set of SQL statements, and after finished developing the final SQL ‘Assert’ statements they are then added to the DB Test file type’s formal Assertions.
The fact that running the Build of a DB and…
-
GROUPING SETS returns incorrect results
The Query
SELECT Surname,
LEFT(Surname,1) AS SurnameInitial,
COUNT(*) AS Count
FROM Queen
GROUP BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) )Does NOT return the same results as the expanded version
SELECT Surname,
NULL AS SurnameInitial,
COUNT(*) AS Count
FROM Queen
GROUP BY Surname
UNION ALL
SELECT NULL AS Surname,
LEFT(Surname,1) AS SurnameInitial,
COUNT(*) AS Count
FROM Queen
GROUP BY LEFT(Surname,1)Details here https://dba.stackexchange.com/a/267196/3690
19 votes -
SSMS 18.4 SQLServer 2019 CU2 Object explorer fails for SQLAgent
After applicting CU2 on SQLServer 2019, SSMS18.4 fails to show SQLAgent jobs via object browser.
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.4013&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476
BUTTONS:
OK
19 votesThe bug introduced in CU2 with xp_sqlagent_enum_jobs is now understood and a fix will ship in CU3. Meanwhile, if you’re hitting this issue, revert back to CU1. We apologize for any inconvenience we may have caused.
-
sql 2017 "cube processing task" ssis adomd client error
A SQL Server 2017 Cumulative Update (somewhere between RTM and CU5) has broken 'Cube Process Tasks' in Integration Services (SSIS). Packages that run fine on SQL 2017 RTM (containing a Cube process Task) FAIL on CU5 and also CU6 with an error related to ADOMDClientUI. The specific error reported in the SSIS log is "Process Cube:Error: Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClientUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 or one of its components. The system cannot find the file specified."
19 votes -
SQL Server 2017 Express LocalDB Shared Instance Connections Fail
Using SQL Server 2016 Express LocalDB I can create a shared instance and connect to it by name, e.g. "(localdb).\SharedTestingInstance". After upgrading to SQL Server 2017 and applying the latest CU (puts me at version 14.0.3048.4) the connection now times out. Here are the steps to recreate:
SqlLocalDB versions
SqlLocalDB create TestingInstance
SqlLocalDB share TestingInstance SharedTestingInstance
SqlLocalDB info
SQLCMD -S "(localdb)\TestingInstance" -Q "SELECT 'hello'"
SQLCMD -S "(localdb).\SharedTestingInstance" -Q "SELECT 'hello'"
SqlLocalDB unshare TestingInstance
SqlLocalDB stop TestingInstance
SqlLocalDB delete TestingInstanceAnd here is the output:
PS C:\WINDOWS\system32> SqlLocalDB versions
Microsoft SQL Server 2017 (14.0.3048.4)
PS C:\WINDOWS\system32> SqlLocalDB create TestingInstance
LocalDB instance…18 votes -
STRING_AGG - Multiple STRING_AGG columns defer to first delimiter
If you have multiple STRINGAGG columns with different delimiters, the delimiter specified in the first STRINGAGG column will be used for all other columns. For example:
;WITH personFruits
AS
(
…SELECT 'John' AS [Person], 'Apple' AS [Fruit] UNION
SELECT 'John' AS [Person], 'Banana' AS [Fruit] UNION
SELECT 'John' AS [Person], 'Kiwi' AS [Fruit] UNION
SELECT 'Jane' AS [Person], 'Apple' AS [Fruit] UNION
SELECT 'Jane' AS [Person], 'Strawberry' AS [Fruit] UNION
SELECT 'Jane' AS [Person], 'Watermellon' AS [Fruit] UNION
SELECT 'Jane' AS [Person], 'Grape' AS [Fruit] UNION
SELECT 'Dale' AS [Person], 'Kiwi' AS [Fruit] UNION
SELECT 'Dale' AS [Person],18 votes -
DROP TABLE IF EXISTS fails
The DROP TABLE IF EXISTS does appear to be contextual, so it checks if the objects exists within sys.objects rather then the limited sys.tables, so the follow example would fail on the second run -
https://gist.github.com/matt40k/f1793ecc7e20738ebf5c0d7425e2a4ca
-- code start ---
DROP TABLE IF EXISTS dbo.ObjectFormerlyATable;
CREATE TABLE dbo.ObjectFormerlyATable
(ID INT IDENTITY(1,1) NOT NULL
CONSTRAINT PK_dbo_ObjectFormerlyATable PRIMARY KEY CLUSTERED (ID ASC));
INSERT INTO dbo.ObjectFormerlyATable DEFAULT VALUES;
SELECT ID FROM dbo.ObjectFormerlyATable;
DROP TABLE IF EXISTS dbo.ObjectFormerlyATable;
GOCREATE OR ALTER VIEW dbo.ObjectFormerlyATable
AS
SELECTID=1;
GOSELECT ID FROM dbo.ObjectFormerlyATable;
--- code end ---
Other examples could be DACPAC…
18 votes -
BULK INSERT does not work Utf-8 support enabled for system locale
In Windows 10 (build 1803) you can go the Control Panel and select the Region applet. Here you can go to the Administrative tab and in the second half of that tab, you can change the System Locale. In this dialog, there is a checkbox "Beta: utf8 support". If you check this, reboot Windows, you find that any attempt to use BULK INSERT fails with
Msg 2775, Level 17, State 12, Line 3
The code page 65001 is not supported by the server.For instance
create table ttt(namn nvarchar(24) NOT NULL, col2 char(3), col3 char(3) NOT NULL)
go
BULK INSERT…18 votes -
SSAS Supported Compatibility level is actually showing default Compatibility in SSMS 17.3
In SSMS 17.3 with Analysis Services Client tools 14.0.1008.227 when you click on server properties for an SSAS 2017 instance it shows the "Suppported Compatibility level" as 1200 when it should show 1100,1103,1200,1400.
If you run a DISCOVERXMLMETADATA command against the SSAS 2017 instance you can see the correct data returned for the default and supported compatibility modes, it appears that the SSMS UI has just mapped this incorrectly.
<ddl400:DefaultCompatibilityLevel>1200</ddl400:DefaultCompatibilityLevel>
<ddl600:SupportedCompatibilityLevels>1100,1103,1200,1400</ddl600:SupportedCompatibilityLevels>18 votesUpvotes: 5
-
System.Data can't referenced in SQLCLR at runtime.
SQLCLR now causes a System.Drawing.dll reference error when using System.Data.dll.
The reason for this is that System.Data has been changed to refer to System.Drawing , but the library supported by SQL Server does not include System.Drawing.dll.
Information on the changes can be found at https://portal.msrc.microsoft.com/en-us/security-guidance/advisory/CVE-2020-1147 and https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/supported-net-framework-libraries
A temporary workaround is to CREATE ASSEMBLY the System.Drawing.dll.
But PERMISSION_SET = UNSAFE is required.The type initializer for 'Scope' threw an exception. ---> System.IO.FileNotFoundException: Could not load file or assembly 'System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at System.Data.TypeLimiter.Scope..cctor()
System.TypeInitializationException:
at System.Data.TypeLimiter.Scope.IsTypeUnconditionallyAllowed(Type type) …17 votes -
Bug in sys.identity_columns: does not honor read uncommitted, causes blocking
There's a bug in sys.identity_columns: it ignores requests for read uncommitted. This means if someone's rebuilding a large index on an identity column, queries get blocked.
To reproduce it, create a table with a clustered index on an identity column, and rebuild it:
CREATE TABLE dbo.Test (Id INT IDENTITY(1,1), CONSTRAINT PKId PRIMARY KEY CLUSTERED (Id));
GO
BEGIN TRAN
ALTER INDEX PKId ON dbo.Test REBUILD WITH (ONLINE = OFF);
GOThen in another window, try to query sys.identity_columns with dirty reads:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM sys.identity_columns WITH (NOLOCK);(I know, those two isolation level…
17 votes -
Full text search host restarts repeatedly under Linux (SQL Server 2019 rc 1)
Under Linux, after enabling full text search in a table and during the population of the index, one sees frequent messages in the log
Error '0x80004005' occurred during full-text index population for table or indexed view 'mytable' (table or indexed view ID '629173587', database ID '5'), full-text key value '5567999'. Attempt will be made to reindex it.
In the errorlog there are many repeated messages like
The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during…17 votes -
SQL 2016/17 Assertion Check Bug
Running the code below will result in a severe error and stack dump, full error message below. Had others test in both 2016 and 2017 with same results. Looks like it's partially an implicit conversion issue, if you explicitly cast to DATE inside the EOMONTH no errors. It also causes the same error if you insert into a table with a datatype of DATE. It will crash even if no data is returned.
Location: e:\b\s3\sources\sql\ntdbms\storeng\dfs\access\rowbucketprocessor.inl:658
Expression: compsetoptypes(pValueInfo, pParamInfo) <= 0
SPID: 82
Process ID: 3000
Msg 3624, Level 20, State 1, Line 38
A system assertion check…17 votes -
bug - string_agg on external table results in "An error occurred while executing GlobalQuery operation: 100001;"
string_agg on external table results in "An error occurred while executing GlobalQuery operation: 100001;Failed to generate query plan."
declare @colList varchar(max) = ''
select @colList=stringagg(quotename(colname), ',
') from dbo.externaltable --external tableon "Microsoft SQL Azure (RTM) - 12.0.2000.8 May 15 2020 00:47:08 Copyright (C) 2019 Microsoft Corporation "
16 votes
- Don't see your idea?