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. SSDT 17.2 for VS 2015 - SSIS 2016 packages can't open Analysis Services Processing tasks

    I upgraded an SSIS project from 2014 to 2016 using SSDT 14.0.61707.300 in Visual Studio 2015. Upon doing so, whenever I open a Analysis Services Processing Task, I get the following error:

    TITLE: Analysis Services Processing Task

    Method not found: 'Microsoft.AnalysisServices.ErrorConfiguration Microsoft.AnalysisServices.Commands.BatchCommand.get_ErrorConfiguration()'.

    If I revert back down to 2014, I can open it just fine. If I create a new AS Processing Task in the same package or new package in the same solution, I get the same error. If I upgrade the SSIS project to SQL2017, it magically works. The issue is only when the target version is 2016.…

    16 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 →

    Upvotes: 13

    <=-=Dec 4 2017 3:53AM=-=>

    Hi Gabe,
    I’m having a similar problem, but not only with upgraded packages.
    I’m having problems also if I create a completely new package
    with Integration Services 2016 in Visual Studio 2015 (SDDT version installed is 17.2).
    It works if I use Integration Services 2014 or 2017.

    I’ve not yet found solution to that issue.

    Please let me know if you identify a solution to your issue

  2. 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 "

    15 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 →
  3. 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

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  4. service broker target not functional on extended events

    I filed a question on StackOverflow, if SQL Server 2017 supports a service broker service as a target for extended events. It seems to be configurable, but no events are written to the service broker queue.

    Any ideas?

    https://dba.stackexchange.com/questions/196571/is-there-a-service-broker-target-for-an-extended-event-session

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

    We’ll send you updates on this idea

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

    15 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 →
  6. 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.

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

    We’ll send you updates on this idea

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

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

  7. Databases not accessible by default on auto-unlocked BitLocker drive

    Take the following BitLocker set up:

    C:\ OS Drive (encrypted) - Windows and SQL Server installed here
    D:\ Database Drive (encryped + auto-unlock)

    When Windows boots, it seems that the MS SQL Server service starts before BitLocker auto-unlocks the drives. Therefore, when SSMS is started, the databases are inaccessible (labelled as Recovery Pending). Also, any attempt to access the database data in SSMS results in strange read/write error dialogs:


    Msg 823, Level 24, State 2, Line 1

    The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000005f4000 in file 'D:\Databases\MyDb_Data.mdf'. Additional

    15 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 →
  8. Enable SSIS Double Hop authentication to work with Windows Defender Credential Guard

    Windows Defender Credential Guard enabled machines only allow Kerberos Constrained delegation. SSIS double hop authentication will not work with either full delegation or constrained delegation once Credential Guard is enabled. Connecting to an Integration Services catalog server and executing a package from a Credential Guard enabled client will fail with “NT AUTHORITY\ANONYMOUS LOGON”.

    Enabling SSIS to work with constrained delegation is imperative for us since the Credential Guard security feature is enabled in our environment and I am certain that many out there are dealing with the same issue.

    14 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 →
  9. SSMS 17.2 (and 17.1) Mirror configuration fails because mirror database is restoring

    Attempting to configure database mirroring through the SSMS 17.2 (and 17.1) GUI between two SQL 2016 servers fails with "Database 'Test' cannot be opened. It is in the middle of a restore."

    14 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 →
  10. 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 PK
    Id ON dbo.Test REBUILD WITH (ONLINE = OFF);
    GO

    Then 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…

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  11. Self referencing scalar function nesting level exceeded when adding a select

    Bug report on Self referencing scalar functions following this question, answered by Paul White here:

    https://dba.stackexchange.com/questions/239324/self-referencing-scalar-function-nesting-level-exceeded-when-adding-a-select

    In short a self referencing scalar function's references are evaluated when it should not be, resulting in an infinite loop in certain circumstances.

    Reproducing the issue:

    CREATE FUNCTION dbo.test6(@i int)
    RETURNS INT
    AS
    BEGIN
    RETURN(
    SELECT TOP 1
    CASE
    WHEN @i = 1 THEN 1
    WHEN @i = 2 THEN 2
    WHEN @i = 3 THEN (SELECT dbo.test6(1) + dbo.test6(2))
    END
    )END;

    SELECT dbo.test6(3);

    Resulting in the error:
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    Part of the answer…

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  12. SSMS 18.0 unable to export Extended Events XEL to XEL

    SSMS 18.0 is unable to export an Extended Events XEL file to another XEL file.

    When viewing an XEL file in SSMS, using the Extended Events menu command "Export to" > "XEL file..." results in this error:

    (Error Summary)

    TITLE: Microsoft SQL Server Management Studio

    Method not found: 'Void System.Object.SerializeEvent(Microsoft.SqlServer.XEvent.Linq.IEventSerializer, Microsoft.SqlServer.XEvent.IMetadataGeneration)'. (Microsoft.SqlServer.XEvent.Linq)

    BUTTONS:

    OK

    (Error Detail)

    Method not found: 'Void System.Object.SerializeEvent(Microsoft.SqlServer.XEvent.Linq.IEventSerializer, Microsoft.SqlServer.XEvent.IMetadataGeneration)'. (Microsoft.SqlServer.XEvent.Linq)

    Program Location:
    at Microsoft.SqlServer.XEvent.Linq.XEventFileProvider`1.SerializeEvent(IEventSerializer serializationContext, TEvent serializableEvent)
    at Microsoft.SqlServer.XEventStorage.ComputedGridStorage.SaveData(Object param)

    I reported this same issue for a previous version of SSMS (17.?) via connect item 3136373 on 2017-06-22 and it was fixed in a later minor update…

    13 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 →
  13. save passwords v18

    SSMS v18.0 doesn't save password properly for each server. Save the password from one server, then attempt to connect to another server with a save password and it will fail. Using SQL authentication and version 15.0.18118.0 and migrated my settings from SSMS v17.9.1.

    13 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 →
  14. Don't show diagram in Microsoft SQL Server management studio 18

    Hello.

    I have error. I didn't see diagram in database. You can help me show diagram. I use Microsoft SQL Server management studio 18

    13 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 →
  15. dm_db_stats_histogram missing row when column/histogram step contains NULL

    When statistics on a nullable column contain a NULL sys.dmdbstatshistogram will not return a row for the corresponding histogram step (when RANGEHIKEY is a NULL). DBCC SHOWSTATISTICS returns all steps including the aforementioned missing one. Without all the histogram levels dmdbstats_histogram is not usable.

    I can confirm this behaviour with INT and VARCHAR columns, I haven't tested other data types). I have attached the reproduction code and a screenshot. Any questions please let me know. Thank you.

    SQL Version: 14.0.3025

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

    We’ll send you updates on this idea

    under review  ·  3 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  16. MDS Explorer should show Attribute description of Name and Code attributes.

    It's possible to set Attribute description on system attributes Name and Code in the same way as user defined attributes on an entity in MDS. But Description only shows in MDS Explorer for user defined attributes not for Name and Code. I expected this to work the same way for system and user defined attributes.

    SQL Server version 13.0.4466.4
    MDS Schema version 13.1.0.3

    13 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 →
  17. DBCC CHECKDB needs to check the checksums on In-Memory OLTP (Hekaton) tables

    Today (SQL 2014 & 2016), CHECKDB skips in-memory OLTP tables. It needs to check the checksums on those tables so that we can find corruption before we find it the hard way.

    Scenario: weekly full backups on Saturday, and log backups every X minutes. Corruption strikes on Monday, and we find out when we have an accidental reboot on Friday. The database is now completely down, and we have to wait until we restore the database from full + all transaction logs since.

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  18. SSDT (14.0.61709.290) Unable execute SSIS package on server that contains a script component.

    When using Visual Studio 2015 (14.0.25431.01) and the latest version of SSDT (14.0.61709.290) on my client machine we are no longer able to execute a SSIS packages from the SSISDB in SQL Server 2016 (13.0.4451.0) on the server machine when it contains a script component and the single package is deployed. I have made sure project TargetServerVersion is SQL Server 2016.

    When we try to execute the package from the server we get the following errors.

    The component is missing, not registered, not upgradeable, or missing required interfaces.

    Script failed validation and returned error code 0xC0048021

    The component metadata for…

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  19. SSMS 17.1 Add New Firewall Rule Error Code 401: Add IP when logging into Azure DB for the first time.

    The title says it all.
    New SSMS install on new PC, to new DB on new Azure SQL Server.� Prompted to Log in to Azure, OK.
    'New Firewall Rule' dialog Prompts to add firewall rule to Azure, OK. Click OK and get error
    "An error occurred while creating a new firewall rults (HTTP Status Code 401)) (ConnectionDlg)"

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

    We’ll send you updates on this idea

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

    Sorry folks, but we are not able to repro this issue (either using SSMS 17.9.1 or SSMS 18 Preview 7).

    Since multiple people out there are reporting this issue, it is obvious that we are missing something that is specific to your environment or configuration.

    Could you help us out and try to describe a little better your subscription/environment/account/permissions?

    For example, does the following mainstream scenario works for you all:
    1) Connect to Azure Portal
    2) Create SQL server+database
    3) Go to SSMS and connect to
    4) Add firewall rule

    If it does, then can you identify what is different between the case where it works and the case where it does not?

    Thanks,
    -Matteo

  20. Database diagram kills SSMS

    When i want to open an existing diagram in SSMS 18.2 on a database which is on compativity level 100 SSMS closes and restarts immediately. I also tried it with a new diagram - this seems to work.

    12 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 →
  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base