SQL Server

Microsoft SQL Server 2017 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 2017 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. SQL2012 Could not locate Statistics on secondary replica

    When querying a table thru a linked server, got the following error message.

    Msg 2767, Level 16, State 1, Procedure sp_table_statistics2_rowset, Line 105
    Could not locate statistics '_WA_Sys_00000007_47DBAE45' in the system catalogs.

    The database was a secondary replica, primary replica was fine. Ran dbcc show statistics on secondary replica, that failed to find statistics distribution. There was an entry in sys.stats on both. Drop statistics on primary, to resolve.

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

    Upvotes: 48

    <=-=Jul 9 2014 11:04PM=-=>

    This is happening to us, at least 3 times a week, on different tables in the database. The drop statistics on primary works, but the processes that are meant to run on the secondary via, linked servers are failing! A real pain!

    <=-=Apr 2 2015 1:38AM=-=>

    >A real pain!
    +1

    <=-=May 4 2015 3:39AM=-=>

    Microsoft folks – Can we please have some kind of explanation on why this happens for dynamic stats and on the asynchronous node only? It has been a real pain for us to manually drop the statistic on the primary node to clean up the corrupt statistic on the asynchronous secondary node.

    <=-=May 19 2015 11:48PM=-=>

    Clearing the userstore_dbmetadata related pools on ASYNC commit resolves the issue.
    DBCC FREESYSTEMCACHE (‘dbname’)

    Or create a stored procedure to check if the instance is an ASYNC commit, then clear the cache using an…

  2. Tempdb spill columns in sys.dm_exec_query_stats should account for batch mode operator spills

    I am testing against SQL Server 2017 RTM-CU4. As far as I can tell, only tempdb spills associated with row mode operators are tracked in the new columns for tempdb spills in sys.dm_exec_query_stats: last_spills, total_spills, max_spills, and min_spills. Spills for batch mode operators are not included. None of the documentation that I can find mentions this as a limitation, so I assume that this is a bug.

    Batch mode execution brings additional challenges around memory grant management and tempdb spills are common for the workloads that I look at. Adaptive query memory feedback is not always sufficient to resolve all…

    34 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 →
  3. Microsoft Command Line Utilities 15 for SQL Server fails to recognize pre-requisite ODBC 17

    SQLCMD docs state that Command Line Utilities 15 requires ODBC 17 (https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017). However, the actual Command Line Utilities 15 installer does not recognize a previously installed ODBC 17.

    Installing ODBC 13 will allow the Command Line Utilities 15 installer to complete successfully, but we end up with errors later presumably due to the Command Line Utilities 15 actually requiring OBDC 17 functionality. Installing both versions 13 and 17 seems to get around this, but obviously requires installation of both packages.

    Our server for testing this is a 64 bit Windows 2008 R2 Enterprise with SP1.

    Repro steps:
    -…

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

    We’ll send you updates on this idea

    10 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  4. Update produces impossible result

    The following produces an illogical result:

    DECLARE @Target table
    (
    c1 integer PRIMARY KEY,
    c2 integer NOT NULL,
    c3 integer NOT NULL
    );

    DECLARE @Source table
    (
    c1 integer NULL,
    c2 integer NULL,
    c3 integer NULL,

    INDEX c CLUSTERED (c1)
    );

    INSERT @Target
    (c1, c2, c3)
    VALUES
    (1, 0, 0);

    INSERT @Source
    (c1, c2, c3)
    VALUES
    (1, 2, NULL),
    (1, NULL, 3),
    (1, 4, 4);

    UPDATE T
    SET T.c2 = S.c2,
    T.c3 = S.c3
    FROM @Target AS T
    JOIN @Source AS S
    ON S.c1 = T.c1;

    SELECT * FROM @Target AS T;

    Result:

    c1 c2 c3
    1 2 3

    The…

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

    We’ll send you updates on this idea

    4 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  5. SQL 2017 CU7 - using In-Memory OLTP table variable inside MSTF causes error

    The code from attached file causes error as:
    Msg 3628, Level 16, State 1, Line 39
    The Database Engine received a floating point exception from the operating system while processing a user request. Try the transaction again. If the problem persists, contact your system administrator.

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

    We’ll send you updates on this idea

    under review  ·  6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  6. Identical symmetric keys do not work between SQL Server 2017 and other SQL Server version

    hi everybody!

    I believe, there is a general product issue in SQL Server 2017. The problem concerns encrypting & decrypting data using the same symmetric key on different servers.
    I have an issue in SQL Server 2017 CU3 (version 14.0.3015.40). I need to create identical symmetric keys on two servers of different versions, as it is described in MS article:

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-identical-symmetric-keys-on-two-servers

    These steps work well within and between different versions of SQL Servers (2012, 2014, 2016), but not between SQL Server 2017 and any other server.
    I can create identical symmetric keys on SQL Server 2012, 2014 and 2016. So…

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

    We’ll send you updates on this idea

    under review  ·  12 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  7. Schema files have blank lines appended after schema compare

    After doing a schema compare from the DB to the project, most schema files have a blank line appended. This causes the files to be checked out of source control and then show as changes when they shouldn't

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

    Upvotes: 19

    <=-=Mar 13 2015 2:44PM=-=>

    This is happening for me too; it’s been a long time issue in the SSDT. It creates noise when doing compares, merges, and checkins where these don’t pertain to the real changes of objects.

    Microsoft Visual Studio Ultimate 2013
    Version 12.0.31101.00 Update 4
    Microsoft .NET Framework
    Version 4.5.51209

    Installed Version: Ultimate

    Scaffolding: A framework for building and running code generators
    Server Explorer extensions for Microsoft Azure Websites

    NuGet Package Manager 2.8.50926.663
    NuGet Package Manager in Visual Studio. For more information about NuGet, visit http://docs.nuget.org/.
    SQL Server Data Tools 12.0.50226.0
    Microsoft SQL Server Data Tools

    <=-=Apr 1 2015 10:31AM=-=>

    Thank you for submitting this feedback. We are investigating this issue and will update when we have more information.

    Steven Green
    SQL Server Data Tools team

    <=-=Feb 1 2016 11:30AM=-=>

    This is one of those issues that convinces me that nobody at MSFT

  8. Filtered index not used when IS NULL and key lookup with no output

    Filtered index not used when IS NULL is used. IS NOT NULL works fine, but IS NULL does a key lookup. Same as this issue but on different version of SQL Server
    https://connect.microsoft.com/SQLServer/feedback/details/454744/filtered-index-not-used-and-key-lookup-with-no-output

    27 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. SEQUENCE gets reset on Publish from SSDT DB Project

    When you add a sequence to a database project and have it start at 1 and then deploy the project to a database, the first time it gets created correctly.
    When you get more values and the sequence next value is 100 and then publish the project again, the publish generates an ALTER SEQUENCE dbo.SequencObject RESTART WITH 1; statement and that should not happen when the sequence object already exists.

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

    We’ll send you updates on this idea

    planned  ·  5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  10. SSMS 18.0 tabs don't display proper contents

    I'm getting this CONSTANTLY on my new install of SSMS 18.0, and it's DANGEROUS. I'll open several tabs, and when I switch from one tab to another, the contents of the window don't change... I'm still looking at the old tab's contents with the new tab selected. It doesn't ALWAYS happen, but it happens at least half the time... FREQUENTLY. Further, I can close a tab, and the contents of the closed tab will still display under the new "top most" tab. This can lead to making changes to the wrong file, or being confused, and losing code. This is…

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

    We’ll send you updates on this idea

    14 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  11. SSMS: Setting for declining Intellisense not respected in SSMS 18.

    The first thing I do when I get a new install is to go do settings and change a few things to my liking. On of these things is to turn off Intellisense.

    However, this is not respected in SSMS 18, but when I open a new query window, Intellisense is still enabled. Sometimes the icon is lit in the toolbar, but it does not really seem to be active. But far too often this is. This is extremely irritating.

    24 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 →
  12. Add setting so LEN counts trailing whitespace

    Maybe there is a historic reason why LEN('a') and LEN('a ') return the same number.

    But that's stupid.

    The internet is awash with people like me who were stunned to discover this counter intuitive quirk (Google it).

    None of the solutions to this problem are particularly elegant or efficient:
    https://stackoverflow.com/questions/2025585/len-function-not-including-trailing-spaces-in-sql-server/2025587

    I'm assuming since this has been baked in for ages, we can't change default behavior.

    So how about:
    - Add a parameter to the LEN function to count white space
    - Add an SQL Option that more generally keeps SQL Server's hands off my white space - everywhere

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

    We’ll send you updates on this idea

    under review  ·  6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  13. Traceflag 460 causing truncation errors on code paths that are not followed

    This is an odd one. I have tested this on both SQL Server 2017 CU12 and CU13 (in a container and on my production boxes).

    If you have an IF/ELSE condition, and you are inserting into a table variable in the ELSE, and that INSERT would cause a truncation issue, it will throw an error, EVEN IF THE CODE IS NOT EXECUTED.

    I have attached a reprop script that creates a database called "Repro" and runs the code. When TF460 is enabled, the ELSE block with throw an error when inserting into a table variable, even though it is impossible…

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

    We’ll send you updates on this idea

    under review  ·  5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  14. Fix the Problem of Converting Hijri Dates to Gregorian Dates

    Currently Microsoft tsql function called Convert() is not able to convert the Hijri date 'yyyy-02-30' to its corresponding Gregorian Date, here are the details of this issue

    https://social.msdn.microsoft.com/Forums/en-US/b86b0cf6-280d-436e-ba92-ff168794c420/error-in-converting-hijri-date-to-gregorian-date?forum=sqlnetfx&prof=required

    23 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 →
  15. 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 COMPATIBILITY_LEVEL=140. But it works as expected using COMPATIBILITY_LEVEL=130. As I didn't find any word on this breaking change in the docs, I consider this a bug.

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

    We’ll send you updates on this idea

    under review  ·  5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  16. SSIS package fails to start - application lock timeout in SSISDB.catalog.create_execution

    Details are described here:
    https://connect.microsoft.com/SQLServer/feedback/details/783291/ssis-package-fails-to-start-application-lock-timeout-in-ssisdb-catalog-create-execution

    In short: when a scheduled job tries to execute an SSIS package , there's a time-out in the communication between the job and the SSISDB database. The package is never executed. The original bug reports was closed as duplicate (no duplicate to be found though).

    I encountered this bug in SQL Server 2017, patched to the latest CU.

    Error message: Failed to execute IS server package because of error 0x80131904. Description: The operation failed because the execution timed out. Source: .Net SqlClient Data

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

    We’ll send you updates on this idea

    under review  ·  6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  17. CDW from 2016 to 2017 ends with "Property HasMemoryOptimizedObjects is not available for Database"

    Hi
    I'm trying to copy a couple of databases from a SQL2016 to SQL2017 using the Copy Database Wizard.
    I'm using SQL Management Object Method in SSMS 17.3
    SSIS Proxy account is sysadmin on both instances and local admin i Windows on both servers.
    When running the job I get the error "Property HasMemoryOptimizedObjects is not available for Database '[DATABASENAME]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
    The databases has no Memory Optimization configured.

    If I try to use detach/attache methods I can't select any of the databases witch…

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

    We’ll send you updates on this idea

    20 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  18. Columnstore REORGANIZE command increases modification_counter on table statistics

    Reorganizing a columnstore index can, in some cases, cause the modification_counter on the table's statistics to increase. This is unexpected because while the underlying structure of the table is being changed, the data itself isn't being modified. This can lead to issues where SQL is automatically updating statistics the next time a query is executed against the table in cases where the modification_counter increases a significant amount because of the REORGANIZE. This has been particularly problematic for our large fact table with 150+ billion rows and full scan statistics across 40+ columns.

    Attached is a reproduction script with three examples:

    21 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 →
  19. SSDT 17.x - Unable to preview SSIS OLE DB Data Source when using Oracle Connector

    I have an SSIS packages that queries an Oracle data source. When trying to preview the query in the data source configuration window, it errors out.

    Technical Details:
    Visual Studio 2015, Update 3, Version 14.0.25431.01
    SSDT 17.4 (Build 14.0.61712.050)
    Oracle Client 12.1.0
    OLE DB Connector: Oracle Provider for OLE DB
    Error Details:
    ===================================

    There was an error displaying the preview. (Microsoft Visual Studio)

    ===================================

    The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)

    ------------------------------
    Program Location:

    at Microsoft.DataTransformationServices.Design.UnsafeNativeMethods.ICommandWithParameters.GetParameterInfo(IntPtr& pcParams, IntPtr& prgParamInfo, IntPtr& ppNamesBuffer)
    at Microsoft.DataTransformationServices.Design.DesignUtils.GetQueryParameters(ConnectionManager connectionManager, String sqlStatement)
    at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, QueryParameter[]…

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

    We’ll send you updates on this idea

    under review  ·  6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  20. VS 2017 SSDT SSIS Dark Theme fail

    VS 2017 SSDT SSIS Dark Theme: Annotations and dataflow row numbers are in white text, therefore invisible.

    20 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?

  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base