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. Security error connecting to SQL Server Express LocalDB in SQLCLR using EXTERNAL_ACCESS and "(localdb)\Instance" connection string

    I am using:


    • Windows 8.0 (though that shouldn't matter)

    • SQL Server 2012 Developer Edition (64 bit) SP2 (11.0.5058.0)

    • SQL Server 2014 Express Edition (64 bit) SP1 (12.0.4100.1)

    • .NET Framework 4.5.2 installed

    When connecting to SQL Server Express LocalDB via the "trusted_connection=true;server=(localdb)\InstanceName" syntax that became available in either the .NET Framework 4.0.2 Update or .NET Framework 4.5 (depending on what blog or documentation you go by), I can connect successfully to:


    • an automatic instance: (localdb)\v11.0

    • a named instance: (localdb)\Projects

    • a shared named instance: (localdb).\SharedProjects

    However, when doing that same simple test via SQLCLR in either SQL Server 2012 SP2 or SQL…

    9 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 →
  2. Task List seems incomplete

    I did a test for Task List in SSMS according to a question in the forum:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a160d5f-b390-4583-bab4-eb7596729088/not-able-to-create-tasklist-in-sql-server-management-studio?forum=sqlgetstarted#f8369eb5-4ae7-4188-945d-13875649caed

    It works as expected with version 13.0.700.242 and I checked it with some older versions of SSMS, no problem there. But after some version (for instance 13.0.16000.28), Task List is incomplete. By that, I mean there's no "New Task" button around, no dropdownbox at the left of the button, no nothing.

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

    9 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

  4. Trusted Assemblies are more problematic yet less functional than Certificates - Please Remove

    The new "Trusted Assemblies" feature, introduced in SQL Server 2017 RC1, is entirely unnecessary, and pushes people farther away from good security practices (i.e. using a signature-based Login).

    The main problem it is attempting to solve -- allowing SQLCLR code in unsigned, SAFE Assemblies to work with "clr strict security" enabled and TRUSTWORTHY disabled -- was solvable back when SQLCLR was first introduced in SQL Server 2005, but in a more elegant, more functional, and more secure way.

    All that needs to be done is to sign the Assembly using ADD SIGNATURE (well, you still need the signature-based Login having…

    9 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. Debugging Stored Procedure in SQL Management Studio

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

    9 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 →
  6. Use More Than Density to Cost a Scan on the Inner Side of a Nested Loop with TOP

    Consider a simple query of the following form:

    SELECT o.ID
    FROM dbo.OUTER o
    WHERE NOT EXISTS
    (

    SELECT 1
    
    FROM dbo.INNER i
    WHERE o.ID = i.ID

    );

    Sometimes the query optimizer will implement the join as a left anti semi nested loop join
    with a TOP operator applied to the scan on the dbo.INNER table. The row goal introduced
    by TOP aggressively reduces the cost of the repeated scan on the dbo.INNER table.
    The density of the table is used in the calculation to reduce the cost of the scan.
    Using the density means that the query optimizer assumes that…

    9 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 →
  7. Partition Table using min/max functions and Top N - Index selection and performance

    Partitioned Tables performance issues - For select statements using Min and Max functions and Top N with ordering over an index.

    Poor performance is being detected for Queries on partitioned tables utilising the min and max functions and select Top N clause with "order by" matching columns of the index.

    The candidate index is either not being used or is being used to scan or seek all rows rather than a subset.

    9 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: 125

    <=-=Aug 10 2007 9:17PM=-=>

    Hi Microsoft, please confirm this optimizer problem by running the scripts, it is self evident and can be optimised significantly better.

    Eg. For a minimum, the optimal plan is to obtain the minimum for each partition using the index and then taking the minimum of all the (partition) results. The same principle can apply to maximum, and for Top N over an index …

    The plan should stand out as optimal, because the number of reads over the index is very small, and there will be one set of page reads (usually only one page) for each partition…. you can use the assumption that the number of partitions is significantly smaller than the number of rows (or even count the number of partitions), to estimate the cost (page reads) – which comes out at about N pages where N is the number of partitions.…

  8. CREATE OR ALTER is not fully supported in Transactional Replication DDL commands

    We've been having trouble with some objects not replicating correctly after a DDL change.

    When a published function or proc is altered, the following appears to happen:
    A database trigger is called: trMStranalterschemaonly
    Which calls a proc called: sys.spMStranddlrepl - This proc will output debug IF MSrepldebugDDL exists (create anything called this!)
    which calls a function: master.sys.fn_replgetparsedddlcmd, which is designed to return the ddl after the object's name, to be used for generating the command to be applied 
    at the subscribers.

    SQL Server BOL states that ALTER is supported (https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-schema-changes?view=sql-server-2016)

    However, this doesn't…

    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 →
  9. 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…

    8 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 →
  10. 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 →
  11. 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 →
  12. 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:

    &lt;SeekKeys&gt;
    
    &lt;Prefix ScanType=&quot;EQ&quot;&gt;
    &lt;RangeColumns&gt;
    &lt;ColumnReference Database=&quot;[StackOverflow2010]&quot; Schema=&quot;[dbo]&quot; Table=&quot;[Comments]&quot; Alias=&quot;[c]&quot; Column=&quot;PostId&quot; /&gt;
    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 →
  13. -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 →
  14. SQL Server 2016 SP2 CU1 deletes Database Mail file

    Installing the above mentioned CU deletes this file...

    C:\Program Files\Microsoft SQL Server&lt;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 →
  15. 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 →
  16. 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  ·  5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  17. 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 →
  18. 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 →
  19. SQL Server should not raise illogical errors

    You already have tons of these bug reports, and I bet that you have closed them all "by design" and told people to use the CASE statement. But, no, you are wrong. This is a bug, and should be fixed. Although the fix is to add new functionality, hang on.

    Consider this silly repro:

    create table albert(a int NOT NULL,

                        b varchar(23) NOT NULL)
    

    create table stina (a int NOT NULL)

    go
    insert albert (a, b)

       values  (1, &#39;99&#39;), 
    
    (2, &#39;Gurka&#39;),
    (3, &#39;89&#39;)

    insert stina (a) values(1), (3), (9), (12)
    go
    SELECT a.a, a.b + 100

    FROM albert a
    JOIN…

    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 →

    Upvotes: 82

    <=-=Mar 3 2010 10:44AM=-=>

    Hi Erland,
    Thanks for your feedback. We are aware of this request and hope to address it in a future version of SQL Server.


    Umachandar, SQL Programmability Team

    <=-=Jul 21 2010 5:36PM=-=>

    This bug cost me and others about 8 hours of troubleshooting this week. And the query was working until statistics were updated. That’s the spooky part, that a new join order can cause an error in a previously-working query. This was compounded by the fact that the query was in a UDF and 1) error messages don’t list the line in the UDF but rather the calling SP and 2) I know of no way to see the execution plan of a UDF, which would have helped because I could have seen in the plan the conversion to float before the JOIN to the filtering table.

    <=-=Mar 9 2011 12:08PM=-=>

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

    2 comments  ·  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 ?

  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base