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.
-
SSMS - Right-click menu option to "Design" tables configured as Temporal Tables is missing
I am using the new Temporal Table feature on certain tables in my database.
When I navigate to such tables via SSMS Object Explorer, I have found that the "Design" right-click menu option is not present for Temporal Tables. (Or as SSMS labels them, "System-Versioned" tables.)
The "Design" option is, however, still available for NON-temporal tables.
22 votesUpvotes: 0
<=-=Mar 7 2017 2:10AM=-=>Thank you for taking time to post this issue!
I can confirm that Design option in SSMS Object Explorer is not available for temporal (system-versioned) tables.
Unfortunately, we do not plan to include a fix for this issue in the upcoming release.
We might include it as an improvement in a future release, though.
How important for you is to have this option available for temporal tables? -
SQLLocalDB Utility 2016 returns "FormatMessageW failed. Error code returned: 15100"
A recent update to SQL Server Express LocalDB 2016 causes errors when attempting to display output for several options while using the sqllocaldb.exe command-line utility.
I current have Microsoft SQL Server 2016 (version 13.0.5026.0) installed (only LocalDB). "SELECT @@VERSION;" displays:
Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64)
When executing the following program:
"C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe"
the following options get the stated errors:
-? as well as any invalid option receives:
FormatMessageW failed. Error code returned: 15100i {invalidinstancename} receives:
FormatMessageW failed. Error code returned: 15100
FormatMessageW failed. Error code returned: 15105i {validinstancename}…
21 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.
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 -
Fix Export/Import of Registered Servers WITH Passwords
I just did an Export of Registered Servers from one machine, running SSMS 17.5, followed by an IMPORT of that file in a machine running SSMS 17.6. Upon exporting, I left the check box blank for "Do not include user names and passwords in the export file". Yes, I am aware that it is a security risk, but I don't want to have to re-enter all my passwords again.
Upon import, I got about a dozen errors stating "Key not valid for use in specified state." (I'm in Connecticut. Does it only work in Washington state?)
Then it says, "Import…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.
-
SSMS 18.2 crash on Extended Events properties form
Opening a "New session…." (not the wizard) of extended events (or a previously existing one) and browsing the pages (Events, Data Storage, Advanced) hangs the windows and/or directly crashes/terminates SSMS with no error message, specially on the Events page.
Application logs shows the crash as an unhandled exception:
Log Name: Application
Source: Application Error
Event ID: 1000
Task Category: (100)
Level: Error
Description:
Faulting application name: Ssms.exe, version: 2019.150.18142.0, time stamp: 0x5d3573be
Faulting module name: System.Windows.Forms.ni.dll, version: 4.8.3752.0, time stamp: 0x5c7a2429
Exception code: 0xc0000005
Fault offset: 0x00c412df
Faulting process id: 0x24a4
Faulting application start time: 0x01d5433d9e072f9d
Faulting application path: C:\Program…19 votes -
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).
18 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 -
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
-
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 -
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 -
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],16 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…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?