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. Remote harden of transaction '...' (ID ...) started at ... in database '' at LSN (...) failed.

    On SQL Server 2016, now even on two versions, the same error message can be seen while AG failovers either fail, or the after failover secondary replica has their databases in REVERTING / IN RECOVERY state.

    Example of these error messages
    Remote harden of transaction 'IFTSAutoNested' (ID 0x00000000141b98bf 0000:1d20f419) started at Feb 19 2019 11:16AM in database 'DB4' at LSN (93341:183352:3) failed.

    Remote harden of transaction 'INSERT' (ID 0x00000000141b989c 0000:1d20f418) started at Feb 19 2019 11:16AM in database 'DB4' at LSN (93341:183346:17) failed.

    Versions:
    13.0.5026.0
    &
    13.0.5237

    Two of such occurences have now appeared on dba.stackexhange.

    https://dba.stackexchange.com/questions/230128/always-on-availability-groups-resolving-state-after-failover-remote-harden-of

    https://dba.stackexchange.com/questions/230663/availability-group-database-stays-in-reverting-for-too-long-after-a-failover?noredirect=1#comment455408_230663

    Kind regards,

    8 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 →
  2. SQL Server 2017 Express LocalDB Shared Instance Connections Fail

    Using SQL Server 2016 Express LocalDB I can create a shared instance and connect to it by name, e.g. "(localdb).\SharedTestingInstance". After upgrading to SQL Server 2017 and applying the latest CU (puts me at version 14.0.3048.4) the connection now times out. Here are the steps to recreate:

    SqlLocalDB versions
    SqlLocalDB create TestingInstance
    SqlLocalDB share TestingInstance SharedTestingInstance
    SqlLocalDB info
    SQLCMD -S "(localdb)\TestingInstance" -Q "SELECT 'hello'"
    SQLCMD -S "(localdb).\SharedTestingInstance" -Q "SELECT 'hello'"
    SqlLocalDB unshare TestingInstance
    SqlLocalDB stop TestingInstance
    SqlLocalDB delete TestingInstance

    And here is the output:

    PS C:\WINDOWS\system32> SqlLocalDB versions
    Microsoft SQL Server 2017 (14.0.3048.4)
    PS C:\WINDOWS\system32> SqlLocalDB create TestingInstance
    LocalDB instance…

    8 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 →
  3. SSMS graphical execution plan doesn't show IsNotNull seek predicate

    The IsNotNull node under the SeekKeys node of execution plan XML is not being displayed at all in the graphical execution plan for an Index Seek. This makes it look like the IS NOT NULL portion of the predicate is being ignored unless you look at the XML.

    SentryOne Plan Explorer correctly parses and displays the IsNotNull node - see the attached screenshot pe-vs-ssms.PNG. That's hovering over the Index Seek operator for the same execution plan in both tools.

    The problematic XML looks like this in my example query:

    <SeekKeys>
    
    <Prefix ScanType="EQ">
    <RangeColumns>
    <ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Comments]" Alias="[c]" Column="PostId" />
    8 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. -Database parameter on Invoke-SqlCmd powershell cmdlet should accept the output from Get-SqlDatabase cmdlet

    Following on from a conversation on https://www.powershellgallery.com/packages/Sqlserver/21.0.17279
    The -Database parameter on the Invoke-SqlCmd cmdlet takes a [string] as its input, but ideally should be able to cope with receiving the output object from the Get-SqlDatabase cmdlet.
    As the -Instance parameter does successfully take the output of the Get-SqlInstance cmdlet, I assume this is a bug.
    Issue can be reproduced like so:

    $SQLCred = Get-Credential -UserName "dbUser"

    $instance = Get-SqlInstance 192.168.1.100 -Cred $SQLCred #Works fine

    $db = Get-SqlDatabase -Name "test_db" -ServerInstance $instance #Works fine

    $results = Invoke-SqlCmd -Database $db -ServerInstance $Instance -Query "Select column FROM view" -Cred $SqlCred # Throws that…

    8 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. SQL Server 2016 SP2 CU1 deletes Database Mail file

    Installing the above mentioned CU deletes this file...

    C:\Program Files\Microsoft SQL Server<instance>\MSSQL\Binn\DatabaseMail.exe.config

    It results in Database Mail being queued but never sent.
    This appears to be a return to an issue caused by SP1 CU1, as described here...

    https://support.microsoft.com/en-us/help/3186435/fix-sql-server-2016-database-mail-does-not-work-on-a-computer-that-doe

    To fix, follow the workarounds in the article. Hopefully this latest CU hasn't broken anything else!

    8 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 →
  6. SSRS 2016 SP2 no longer respects text/html MIMEType

    An HTML file uploaded to SSRS 2016 SP1 would be identified as mimetype text/html. From the Report Manager portal you could click the link and it would be rendered in a browser window.

    After installing 2016 sp2 the mime type for the exact same file is now application/octet-stream. When clicking the link for this file from report manager, it prompts for download instead of opening in a browser window.

    This occurs in I.E, Edge, FF and Chrome.

    The Use case is uploading an HTML file to include a link/redirect to our data dictionary website to keep our users inside of…

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

    We’ll send you updates on this idea

    under review  ·  4 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  7. Unable to script out ExternalDataSource of type BLOB_STORAGE.

    It appears SSMS v17.7 does not know how to script out the external data source. I am getting this error when attempting to script out an existing data source.

    TITLE: Microsoft SQL Server Management Studio

    Unexpected value for enumeration type 5. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMSRel17_4).180502-0908)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.WrongPropertyValueExceptionText&LinkId=20476

    Attempting to look at Properties in the right click context menu also produces an error

    TITLE: Microsoft SQL Server Management Studio

    Cannot show requested dialog.


    ADDITIONAL INFORMATION:

    Invalid DataSourceType (SqlManagerUI)

    Here is the version information:
    Microsoft SQL Server Management Studio 14.0.17254.0
    Microsoft Analysis Services Client Tools 14.0.1016.251
    Microsoft Data Access Components (MDAC)…

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

    We’ll send you updates on this idea

    started  ·  4 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  8. SQL Server won't install on Linux Ubuntu 18.04 due to Openssl version issue

    Doing an Ubuntu OS upgrade to VERSION="18.04 LTS (Bionic Beaver)", my SQL Server had to be removed, and can't be reinstalled. The following message shows during the apt-get installation step:

    "
    maxt@mars:~$ sudo apt install -y mssql-server
    Reading package lists... Done
    Building dependency tree

    Reading state information... Done
    Some packages could not be installed. This may mean that you have
    requested an impossible situation or if you are using the unstable
    distribution that some required packages have not yet been created
    or been moved out of Incoming.
    The following information may help to resolve the situation:

    The following packages have…

    8 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 →
  9. Opening Schema Compare Options crashes SSDT

    I open a saved schema comparison and press the arrow in front of "Application Scoped" on the "Object Types" tab. This makes SSDT crash immediately.

    Microsoft SQL Server Data Tools for Visual Studio 2017 (SSDT)
    Version 15.6.6
    VisualStudio.15.Release/15.6.6+27428.2037
    Microsoft .NET Framework
    Version 4.7.02556
    SQL Server Data Tools 15.1.61801.210
    Microsoft SQL Server Data Tools

    8 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 →
  10. Error with Detail Rows Expression when used with Excel drillthrough

    With a SSAS Tabular model on compatibility Level 1400, I have defined a Default Rows Expression to allow the client to obtain specific columns when connecting with an Excel pivot table but in Excel 2016 the following error is returned when the drillthrough is executed:

    "RETURN clause cannot be used with the DRILLTHROUGH statement when a DetailRows expression is already defined on the measure 'Transaction Count'"

    I have defined the Default Detail Rows Expression at the table level and have not defined a Detail Rows expression on any measures individually. My understanding is that if no expression is defined on…

    8 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 →
  11. SSRS 2016 Report Descriptions missing in report manager tiles

    In the new report manager which came SQL Server 2016 i'm missing the report description in the tiles.

    In our environment we used this field as a user friendly report name and the report name property as just a code. This way we could easily link to existing reports and never have to worry about changing report names, but without the descriptions the users won't know which report they must open.

    8 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: 8

    <=-=Oct 19 2016 4:13PM=-=>

    Thanks for your feedback Mark. A change for this is coming with SQL server 2016 SP1.

    -SSRS team

    <=-=Oct 19 2016 4:13PM=-=>

    Thanks for your feedback Mark. A change for this is coming with SQL server 2016 SP1.

    -SSRS team

    <=-=Jan 24 2017 3:17PM=-=>

    We have an SSRS 2016 SP1 Developer Edition instance running in native mode, and we still do not see descriptions on the report tiles.

    <=-=Aug 1 2017 1:39AM=-=>

    We are using SQL server 2016 SP CU3 and I don’t see any description on my reports, do you know if the change have been done ?

  12. 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)"

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

    We’ll send you updates on this idea

    11 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

  13. Debugging Stored Procedure in SQL Management Studio

    While debugging the yellow cursor-line will become misplaced (see attached image)

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

    We’ll send you updates on this idea

    unplanned  ·  6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  14. Plain Text Password in Maintenance Plans

    The user and password of the user that create a Maintenance Plans using the MSSMS is save in plain text, you can use the following query to access this informaci�n.

    SELECT CAST(CAST(packagedata AS VARBINARY(max)) AS VARCHAR(max)) FROM sysssispackages
    WHERE name LIKE 'MaintenancePlansName'

    This will show the xml of Maintenance Plans including the user and password.

    8 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 →
  15. LocalDb Data Defaults Data Directory to User Profile Root. Location cannot be changed via SSMS

    LocalDb for some reasons sets the default data directory to the root of the user profile directory. This means all database files, logs and backups clutter the root of this directory. There does not seem to be a way to change the default data locations. Trying to change the option in SSMS Server Properties -> Database Settings results in a RegCreateKeyEx() returned error 5, "Access is denied" error (Microsoft Sql Server, Error: 22002). Files by default should go into a sub directory of the user profile e.g Databases and this should be able to be changed via SSMS.

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

    7 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 →
  17. Cannot script external table in SSMS 18.2

    I receive the error message attached when trying to script an external table in SQL Azure in SSMS 18.2.

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

    We’ll send you updates on this idea

    5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  18. SSMS 18 Remembers password for user, not specific to database

    I have a number of SQL Server connections which use the same login but each has a different password. Before SSMS 18 the remember password function worked correctly, storing the password for individual connections.

    Under SSMS 18 it appears to only be storing the last used password for the login regardless of the server connection.

    Can this be restored to the previous functionality.

    7 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 →
  19. Hybrid Buffer Pool using all conventional memory on SQL Server 2019 CTP 3.0 when trace flag 834 is enabled

    In SQL Server 2019 CTP 3:


    1. Set your max server memory so that SQL Server doesn't use all the memory on the box - in my case, I'm using a 16GB VM, and I set max memory to 10GB


    2. Enable Locked Pages in Memory and trace flag 834 (large pages)


    3. Restart the SQL Server. SQL Server uses the appropriate amount of memory, say 10GB.


    4. Enable Hybrid Buffer Pool:


    5. Enable hybrid buffer pool:


    EXEC spconfigure 'show advanced', 1;
    RECONFIGURE
    EXEC sp
    configure 'hybrid', 0
    RECONFIGURE

    And restart the SQL Server. SQL Server instantly uses all of the memory on the…

    7 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 →
  20. SSIS Server Maintenance job fails

    For most recent CUs of 2017 SQL the native SSIS Server Maintenance job Fails (for AlwaysOn at least). See notes here.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ce94c74-d552-48ec-8621-8be3124c53bc/ssisdb-2017-cu8-running-in-always-on-group-maintenance-procedure-bug?forum=sqlintegrationservices

    Manually modifying the proc if executes will fix the error but that isn't something end users should be changing.

    Resolution: The cursor deallocate statement on line 175 of proc "ssisdb.internal.cleanupserverretention_window" needs to be moved two lines down.

    http://answers.flyppdevportal.com/MVC/Post/Thread/238eb212-f757-4ce8-acea-e4c579628bd2?category=sqlintegrationservices

    7 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