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.
-
VS 2017 SSDT SSIS Dark Theme fail
VS 2017 SSDT SSIS Dark Theme: Annotations and dataflow row numbers are in white text, therefore invisible.
21 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).
21 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. -
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…
20 votes -
Performance Dashboard / Expensive Queries / copy query text
When using the reports in the SSMS Performance Dashboard, you are unable to copy the query text from any of the "expensive queries" reports. Make it so you can either click to view the query in a new query window, or right-click the query to copy the text for pasting into a new window.
20 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.
-
MERGE not working when index created on temporal table.
Following are the steps to reproduce the MERGE statement issue in any SQL 2017 database.
Script is attached for all the steps.--Steps to reproduce fail Scenario
- Create table in SQL Server 2017 database with named system versioned temporal/history table.
- Create non-clustered index on the temporal/history table.
- Insert 400+ records in table.
- Execute the merge statement (source table should have data for both Insert and Update scenario), and it will fail with following error
Msg 681, Level 16, State 3, Line 453
Attempting to set a non-NULL-able column's value to NULL.--Steps to see success scenario
- Drop the index from…
19 votes -
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.
19 votes -
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 -
Attempting to create a database diagram causes error "Index was outside the bounds of the array"
When attempting to create a new database diagram on an on premises SQL 2014 database using SQL Server Management Studio 16.5, an error window is shown with error "Index was outside the bounds of the array". Additionally, SSMS 2014 is able to create a database diagram just fine. When back in SSMS 2016, I can view the new diagram, but it is in a window on another monitor, with no ability to resize the window (window chrome is missing).
19 votesHello all
Database Diagrams is being deprecated for SSMS 18. As such, we have not planned any work on this feature for a while. Please use Visio or any of the 3rd party ER modeling tools for this functionality. I understand some of you may have dependency on this feature. You are welcome to continue using 17.9.1 if it works for you.
thanks,
Dinakar -
Reordering an XML column
Reordering an XML column in SQL server management studio's results grid affects the "View XML" feature.
Clicking on a cell of an XML column shows wrong data.19 votesUpvotes: 0
<=-=May 2 2011 8:50AM=-=>Hi DamiLaufer
Thanks for taking the time to report this issue. We have investigated and found this is a known bug that has not made it to the top of our list yet. I’m resolving this item for now, but do have a work item tracking it.Thanks,
-Sam Hughes -
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
-
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],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 -
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…17 votes -
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) …16 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 -
Error when connecting to SSAS via msmdpump.dll in SSMS 18.5
Hi,
We have an SSAS instance with https access configured via msmdpump.dll. In 18.4 connection works fine, but in 18.5 it gives following error:
Cannot connect to https://myserver/OLAP/msmdpump.dll.
ADDITIONAL INFORMATION:
String reference not set to an instance of a String.
Parameter name: s (mscorlib)
Could you check what is the reason?
16 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…16 votes -
Query store create database
If you enable Query Store on model, then attempt to create a database, Query Store will be enabled on the new database.
However, if you attempt to create a database using a non-default collation, the CREATE database fails with a deadlock on sys.sysschobj, in the middle of a DQS transaction. (I'm using 2017 CU4)
I should be able to specify a non-default collation when creating a database, and not have DQS cause a failure.
--Enable Query Store on model
ALTER DATABASE model SET QUERYSTORE (OPERATIONMODE = READWRITE);
GO
--Create a database using any collation other than your…16 votes -
xp_delete_file on SQL Server 2017/Linux
The xpdeletefile system stored procedure for deleting bakup file does not work on Linux client OS.
Wee tested on Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64) Enterprise Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)Script we used :
DECLARE @BACKUPDIR NVARCHAR(256);
EXEC master.dbo.xpinstanceregread
@rootkey = 'HKEYLOCALMACHINE',
@key = 'Software\Microsoft\MSSQLServer\MSSQLServer',
@valuename = 'BackupDirectory',
@BackupDirectory = @BACKUPDIR OUTPUT;
SELECT @BACKUPDIR
EXEC master.sys.xpdeletefile 0, @BACKUP_DIR, N'bak', N'21111111';
GOError message :
Msg 22049, Niveau 15, État 0, Ligne 67
Error executing extended stored procedure: Invalid Parameter16 votes
- Don't see your idea?