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. Cannot join database to AG using GUI if replica SQL Instance is not running on default port 1433

    I am running a cluster of 2 nodes where the SQL service is not running on the default port 1433. When I try to add a database to the AG using GUI I get stuck at �Connect to Existing Secondary Replicas� window. I click on Connect but the �Connect to Server� login form has the server name with no port specified and I cannot modify it as it is grayed out.

    Similar issue when the replica has multiple IPs and you specify the SQL instance to listen to a certain IP. When connecting to existing Secondary replicas you might get…

    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 →
  2. fn_hadr_backup_is_preferred_replica fails for Cluster Type = "NONE" Availability Groups

    The function fnhadrbackupispreferred_replica fails when executed for a database that is a member of a clusterless Availability Group. As this function is used by Maintenance Plans to determine replica preference, it is not possible in RC2 to use maintenance plans that do not ignore backup preferences.

    Can reproduce on SQL Server 14.0.900 with SSMS 17.2 and 17.3.

    For example, if wideworldimporters is a member of an Availability Group with Cluster Type = "NONE":

    SELECT [master].sys.fnhadrbackupispreferred_replica('wideworldimporters');

    Msg 41005, Level 16, State 1, Line 1
    Failed to obtain the Windows Server Failover Clustering (WSFC)…

    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 →

    Upvotes: 3

    <=-=Oct 4 2017 8:49PM=-=>

    Confirmed this is present in 14.0.1000.169

    <=-=Oct 31 2017 1:55AM=-=>

    The problem still continues with CU1,
    When I run the query “SELECT [master].sys.fn_hadr_backup_is_preferred_replica(‘dbname’)” it throws the exception “Could not process the operation. Always On Availability Groups does not have permissions to access the Windows Server Failover Clustering (WSFC) cluster. Disable and re-enable Always On Availability Groups by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable Always On Availability Groups, see SQL Server Books Online.”.
    There is no cluster, this is clusterless topology.

  3. "parameter name already exists" while trying to Process Database on Azure Analysis Services in SSMS

    If I try to Process Database in SQL Management Studio on either of my computers, I get the error:

    "parameter name already exists",
    "Parameter name: name (Microsoft.AnalysisServices.BackEnd).

    It seems to be an error returning from the AAS server itself (it happens in SSMS 2017 version 17.3 two different PCs (Windows 8.1 and Windows 10 1703)).

    Oddly, I can still successfully process the database if I run the refresh XMLA script above in SQL Management Studio or if I choose to process default or full under "Processing Option" in SSDT.

    I have tried deleting the database and redeploying, I have tried…

    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 →
  4. SQL Server 2016 SP1 CU1. Internal Query Processor Error: The query processor could not obtain access to a required interface.

    When i try to use partitioning function in a join's redicates over a columnstore table a get the error:

    Msg 8601, Level 17, State 46, Line 28
    Internal Query Processor Error: The query processor could not obtain access to a required interface.

    The bug can be reproduced at least on

    Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)   Jan  6 2017 14:24:37   
    
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 &lt;X64&gt; (Build 9600: )

    You can reproduce the behaviour. Just use the script from "Steps to Reproduce".

    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 →
  5. Type conversion in may affect CardinalityEstimate - Convert/cast on selected columns

    Type conversion in may affect CardinalityEstimate - Convert/cast on selected columns --- It is viable that this warning be cited when columns are filtered on and the filtering clause involves cast/convert function.

    But, it seems to be an overreach when converted/casted columns are simply cited in the selected / projected column list and not at all in filtering clause.

    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 →

    Upvotes: 1

    <=-=Dec 28 2017 10:38AM=-=>

    Defect #11427260 has been filed with our Product group on your behalf – Karly Shockley

    <=-=Dec 28 2017 11:34AM=-=>

    Shared here…

    Transact SQL � Warning � �Type conversion in expression may affect �CardinalityEstimate� in query plan choice�
    https://learningintheopen.org/2017/12/13/transactsqlwarningtypeconversion-in-expression-may-affect-cardinalityestimateinqueryplanchoice/

  6. CDC capture job stops after a while in SQL 2017

    I created a new simple database with one table in SQL 2017 (NOTE! An upgraded one from 2014). After a while and doing some CRUDs the capture job fails and stops. Here are details :

    Table definition :

    CREATE TABLE [dbo].[Table_1](

    [Id] [INT] NOT NULL,
    
    [name] [NVARCHAR](1000) NULL,
    [image] [VARBINARY](MAX) NULL,

    CONSTRAINT [PKTable1] PRIMARY KEY CLUSTERED
    (

    [Id] ASC
    

    )WITH (PADINDEX = OFF, STATISTICSNORECOMPUTE = OFF, IGNOREDUPKEY = OFF, ALLOWROWLOCKS = ON, ALLOWPAGELOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    Job Error (after 10 retries)

    Date 12/27/2017…

    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 →
  7. Schema not reported in the ERROR_PROCEDURE function

    When an error is raised by the error handler, the name of the trigger/procedure is reported by the ERROR_PROCEDURE() function. However, it does not include the schema.

    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 →

    Upvotes: 35

    <=-=May 1 2005 2:08PM=-=>

    Good catch, Louis! I will have to admit that I have not thought ot this, but you have certainly hit the nail on the head here.

    Then again, this is how the error message looks like without the catch handler:

    Msg 50000, Level 16, State 1, Procedure testErrorMessage, Line 4
    blech

    Note that only is schema missing – so is the database. And I am not really sure that it’s entirely good to add db.schema ahead of the name – there could be a compatibility issue.

    Maybe this is something for the next release.

    <=-=Nov 5 2007 9:50AM=-=>

    Hello Louis

    Thank you for your feedback. Please excuse the delayed response. I wanted to let you know that your feedback has been noted. As Erland pointed out, we would also need to consider whether to report the database name along with the schema. We will…

  8. Unable to deply Tabular model in 1500 compatibilty, but in 1400 works

    I can't deploy my model to SSAS 2019 RTM in compatibility level 1500. If I change to 1400, I can deploy it without problems to the very same instance of SSAS 2019.

    Error is from events log: The description for Event ID 22 from source MSOLAP$TABULAR cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    If the event originated on another computer, the display information had to be saved with the event.

    The following information was…

    6 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. Microsoft.ReportingServices.ReportViewerControl.WebForms 150.1358.0: Error Could not load file or assembly 'Microsoft.ReportViewer.Design'

    SUMMARY

    It seems version 150.1358.0 only fixed two of the three DLLs with version = 15.3.0.0.

    Version 150.1357.0 DLLs with version = 15.3.0.0:

    1) Microsoft.ReportViewer.DataVisualization
    2) Microsoft.ReportViewer.Design
    3) Microsoft.ReportViewer.ProcessingObjectModel

    Version 150.1358.0 DLLs with version = 15.3.0.0:

    1) Microsoft.ReportViewer.Design

    WORKAROUND

    Two options:

    1) Edit the Web.config to replace the Microsoft.ReportViewer.Design Version:

        &lt;!-- Workaround for v150.1358.0, which shipped 15.3.0.0, not 15.0.0.0 --&gt;
    
    &lt;add assembly=&quot;Microsoft.ReportViewer.Design, Version=15.3.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91&quot;/&gt;

    2) Downgrade to 150.900.148. This restores all files to Version = 15.0.0.0.

    ENVIRONMENT DETAILS

    Microsoft Visual Studio Professional 2017: v15.9.15 (latest)
    .NET Framework: 4.8
    Microsoft.ReportingServices.ReportViewerControl.WebForms: 150.1358.0 (latest)
    Microsoft.SqlServer.Types: 14.0.1016.290 (latest)
    Microsoft RDLC Report Designer: 15.3.1…

    6 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 →
  10. Avoid False-Positive reports of SQL Injection attacks from Azure Databricks

    We are receiving false-positive reports of SQL Injection attacks based on enclosing a candidate SQL statement into a sub-query and adding a "where 1=0" clause when using Azure Databricks. These reports cannot be ignored and Administrator time is wasted in reviewing these. If the connection was correctly identified, and not simply marked as "Microsoft JDBC Driver for SQL Server" or some filtering method was available, this could be avoided.

    6 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. Extended Event : "sp_cache_miss" event is not capturing data

    I have setup an extended event session for spcachemiss event. Somehow session is not capturing any data.

    If I setup profiler trace on the same event it works fine. Strange thing is if I keep both profiler trace and Extended Event session running at the same time, XE session captures data.

    I am stumped why XE session is not capturing data, please help.

    I have tested this against SQL2016 SP2-CU3 as well as on latest build Microsoft SQL Server 2016 (SP2-CU7-OD) (KB4508636) - 13.0.5343.1

    Here is the extended event session definition I am using.

    CREATE EVENT SESSION [CacheMisses]…

    6 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. Slow or non-operational Query Store under legacy cardinality estimator

    In many of our customers environment Query Store is completely non-operational or very slow. No report of Top Resource Consuming Queries is generated in timely fashion, no matter what configuration options is set. Other reports with some background data behave the same. So the tool is practically useless.

    If I try any SELECT statement that is generated by Query Store run in standalone SSMS windows, result is the same - did not finish.

    I found out it's because our databases are in legacy cardinality estimation mode. When I run appropriate SELECT with OPTION (QUERYTRACEON 2312) the result appear in acceptable…

    6 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. AG wizard does not run sp_control_dbmasterkey_password for databases with DMK.

    There is a bug with AG add database wizard for databases with DMK, it does not run spcontroldbmasterkey_password on replicas as expected.

    In the AG Add database wizard, if you select:
    'Full Database and Log Backup', it works as expected, and the credentials are created on the replicas.
    'Automatic Seeding' to add the database, it does not run spcontroldbmasterkey_password.

    If Automatic Seeding is selected, in the validation check "Checking password of the database master key", it says it has skipped it because: "Skipped Checking password of the database master key because you skipped initial data synchronization."

    6 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 →
  14. Accelerated Database Recovery - Incorrect Syntax Error

    I was recently working with some of the new features of SQL 2016 and 2019, found that a combination of two was causing me a lot of headaches. Not sure if you have come across this scenario but:

    While working with an Always Encrypted connection and to enable yourself to see the data you have to turn on one of the advanced setting in your connection by using - "Column Encryption Setting=Enabled".
    While this setting is used in your connection to a specific database, you will not be able to ALTER DATABASE to SET a setting like ACCELERATEDDATABASERECOVERY…

    6 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. Query Store "Log Memory Used" metric calculation is wrong

    I used XE to capture the query being executed when viewing the "Log Memory Used (KB)" metric, and noticed it performs this calculation:

    ROUND(CONVERT(float, SUM(rs.avglogbytesused*rs.countexecutions))*1024,2) totallogbytes_used

    As you can see, it's attempting to convert bytes to kilobytes by multiplying the bytes by 1024. It should be dividing the bytes by 1024. As it stands, the values displayed in the Query Store reports are off by about a factor of a million.

    There's a repro in this Database Administrators Stack Exchange post: https://dba.stackexchange.com/questions/231682/what-is-log-memory-in-query-store-2017

    I ran an insert that generated 346,796 avglogbytesused in

    6 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 →
  16. Bad cardinality estimate for subquery after patches / QUERYTRACEON 4199

    We run the latest version of SQL Server 2017(14.0.3048.4).
    When running the following query, it runs fine:

      SELECT
    
    (SELECT TOP 1 ROLEDEFINITION.NAME -- Rolle for ansvarlig dommer
    FROM AKTOER
    INNER JOIN PERSON ON PERSON.ID = AKTOER.PERSON_ID
    INNER JOIN ROLEDEFINITION ON AKTOER.ANSATTROLLEDEFSNAPSHOT = ROLEDEFINITION.ID
    WHERE AKTOER.SAK_ID = S.SAK_ID AND PERSON.ORGUNIT_ID = S.DOMMER_ORG_ID
    ORDER BY COALESCE(TODATE, &#39;9999-01-01&#39;) DESC)
    FROM SAK S
    INNER JOIN #AktuelleSaker AS AKTUELLE_SAKER ON AKTUELLE_SAKER.SAK_ID = S.SAK_ID option(recompile)

    However, If we add option(recompile, QUERYTRACEON 4199)
    it use a very wrong cardinality estimate. I included the good(no query optimizer fixes) and bad plan(query optimizer fixes).

    I have tried to run…

    6 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. MDS Deployment Error 400003 "The attribute reference is not valid. The attribute was not found."

    The deployment of a model containing a business rule causes error 400003 "The attribute reference is not valid. The attribute was not found.".
    The error occurs since the installation of one of the last cumulative updates and the migration of the MDS database through the MDS configuration Manager.
    We did reproduce the behaviour on several different machines.
    Deleting and re-creating the MDS database solves the problem.
    So it seems that the database migration scripts cause this problem.

    Steps to reproduce:
    1. Create new Model
    2. Create new Entity E with Attribute A
    3. Create Business Rule with Default Value x…

    6 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. 6 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 →
  19. filegroup to read_only prevents dbcc checkdb from running

    My database uses a combination of partitions and file groups in a sliding window configuration. I have implemented the VLDB CheckDB pattern and found that all file groups must be in a read-write state for this to work. By placing some older file groups into a read-only state I was hoping to utilize partial backups to reduce backup times. As a workaround we are temporarily placing the file groups back into a readwrite state to allow the checks to continue - however to make sure our FG backups can be used in a restore the FG backups have to be…

    6 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 →
  20. Fix merge bug on temporal history tables with indexes

    Fix bug in temporal tables where indexes on history tables result in the error: Attempting to set a non-NULL-able column's value to NULL when an MERGE is performed. Exact steps to replicate can be found here:
    https://social.msdn.microsoft.com/Forums/en-US/f4fbdfcc-c10e-4412-8011-2bbd3cdda5eb/merge-on-temporal-table-fails-with-attempting-to-set-a-nonnullable-columns-value-to-null?forum=sqldatabaseengine

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

SQL Server

Categories

Feedback and Knowledge Base