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.

More details about SQL Server are available in the SQL Server documentation.
If you have a technical issue, please open a post on the developer forums through Stack Overflow or MSDN.


  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. 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…

    22 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  10 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  2. 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 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 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?

  3. 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 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 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.

  4. 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 [t1
    f1],[t1].[f2] AS [t1f2],[t1].[f3] AS [t1f3]
    FROM t1
    WHERE ([t1].[f4] BETWEEN 1 AND 100)
    ORDER BY [t1].[f4] ASC

    When the SUM and GROUP BY are removed, it runs just fine. It happens from time to…

    20 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  5. 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
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    planned  ·  0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  6. 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 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    8 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  7. 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
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    8 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  8. 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


    1. Create table in SQL Server 2017 database with named system versioned temporal/history table.

    2. Create non-clustered index on the temporal/history table.

    3. Insert 400+ records in table.

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


    1. Drop the index from…
    19 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  9. 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
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    13 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  10. 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
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  17 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  11. 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 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    36 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Hello 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

  12. 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 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    7 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  13. 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 &#39;John&#39; AS [Person], &#39;Apple&#39;       AS [Fruit] UNION
    
    SELECT &#39;John&#39; AS [Person], &#39;Banana&#39; AS [Fruit] UNION
    SELECT &#39;John&#39; AS [Person], &#39;Kiwi&#39; AS [Fruit] UNION
    SELECT &#39;Jane&#39; AS [Person], &#39;Apple&#39; AS [Fruit] UNION
    SELECT &#39;Jane&#39; AS [Person], &#39;Strawberry&#39; AS [Fruit] UNION
    SELECT &#39;Jane&#39; AS [Person], &#39;Watermellon&#39; AS [Fruit] UNION
    SELECT &#39;Jane&#39; AS [Person], &#39;Grape&#39; AS [Fruit] UNION
    SELECT &#39;Dale&#39; AS [Person], &#39;Kiwi&#39; AS [Fruit] UNION
    SELECT &#39;Dale&#39; AS [Person],
    17 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  14. 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
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  15. 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.external
    table --external table

    on "Microsoft SQL Azure (RTM) - 12.0.2000.8 May 15 2020 00:47:08 Copyright (C) 2019 Microsoft Corporation "

    16 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  16. 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
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  17. 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
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  18. 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
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  19. 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.xp
    instanceregread
    @rootkey = 'HKEY
    LOCALMACHINE',

    @key = 'Software\Microsoft\MSSQLServer\MSSQLServer',

    @value
    name = 'BackupDirectory',
    @BackupDirectory = @BACKUPDIR OUTPUT;
    SELECT @BACKUP
    DIR

    EXEC master.sys.xpdeletefile 0, @BACKUP_DIR, N'bak', N'21111111';
    GO

    Error message :
    Msg 22049, Niveau 15, État 0, Ligne 67
    Error executing extended stored procedure: Invalid Parameter

    16 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  20. 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…

    16 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base