Update: Microsoft will be moving away from UserVoice sites on a product-by-product basis throughout the 2021 calendar year. We will leverage 1st party solutions for customer feedback. Learn more here.

John Zabroski

My feedback

  1. 0 votes

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 15

    <=-=Apr 5 2011 10:28AM=-=>

    Thanks for taking the time to file this issue.
    It may be an occurrence of an issue that was fixed in SP1 CU7.

    That update is described and can be requested here:
    http://support.microsoft.com/kb/979065

    The item is third from the bottom:
    409769 981037 (http://support.microsoft.com/kb/981037/ ) FIX: “Attempting to set a non-NULL-able column’s value to NULL” error message when you run an INSERTSELECT statement that uses the ISNULL() function on a temp table in SQL Server 2008

    Use of ISNULL.

    Campbell, SQL Development

    <=-=Apr 6 2011 6:40AM=-=>

    Thanks for the reply.

    We are running SP2, which by definition includes the fix from SP1 CU7.

    Since this has been reproduced in SP2 there is a scenario in the fix for SQL 2008’s T-SQL “merge” statement which still fails.

    <=-=Apr 7 2011 4:29PM=-=>

    Oops yes, you said SP2.…

    An error occurred while saving the comment
    John Zabroski commented  · 

    I think I hit this today on SQL Server 2017.

    It appears to ONLY happen on a newly created table with an identity insert clause and temporal table. -- I am not sure which specific combination triggers it. Also, it ONLY happens when the size of the MERGE is above a certain number of rows. If I had to guess, it happens due to ValidFromInUtc/ValidToInUtc columns.

  2. 3 votes

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    John Zabroski commented  · 

    I am able to reproduce the problem, and validated I have an identical problem (but on SQL Server 2019 instead of Azure SQL), AND have a workaround you can potentially use!

    When you rename the parent table column, and then also rename the temporal table column, the second rename fails. I also verified I can reproduce this problem even if I split out the two consecutive renames into separate transactions in separate connections, run sequentially. Ergo, there is no shared state conflicts where one DDL transaction has yet to commit.

    sp_rename N'[dbo].[MyTable].[Is_Active]', N'IsActive'
    sp_rename N'[dbo].[MyTableHistory].[Is_Active]', N'IsActive'
    The error was No item by the name of '[dbo].[MyTableHistory].[Is_Active]' could be found in the current database 'PortfolioMG_UnitTest', given that @itemtype was input as '(null)'.

    Below is my server information - this is not happening for me on Azure SQL but rather Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

    sqlcmd -S localhost -d master -Q "SELECT @@VERSION"

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
    Sep 24 2019 13:48:23
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

    (1 rows affected)

    Worth noting this problem does not happen on SQL Server 13.0.4206 or SQL Server 14.0.3048.4

    WORKAROUND
    Disable system versioning, rename the columns on both the parent and temporal table, and re-enable system versioning.

    ALTER TABLE [dbo].[MyTable] SET (SYSTEM_VERSIONING = OFF)
    sp_rename N'[dbo].[MyTable].[Is_Active]', N'IsActive'
    sp_rename N'[dbo].[MyTableHistory].[Is_Active]', N'IsActive'
    ALTER TABLE [dbo].[MyTable] SET(SYSTEM_VERSIONING = ON(HISTORY_TABLE = [dbo].[MyTableHistory], DATA_CONSISTENCY_CHECK = ON))

    This forces the sys.sp_rename to follow a different path through the system. What is UNKNOWN to me is WHY SQL Server has this hidden dependency on column ids. I am worried that this part of the system may be under-tested in this scenario, and could lead to data corruption issues. However, I've been using temporal tables in a production system with 400GB of data for 2+ years now with no CHECKDB failures.

    John Zabroski supported this idea  · 
  3. 2 votes

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    John Zabroski supported this idea  · 
  4. 383 votes

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  50 comments  ·  SQL Server » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    John Zabroski commented  · 

    Hi,

    I believe I have discovered an alternative path forward today: Docker containers.

    The following article explains how to use Docker containers to create multiple SSRS instances on the same machine: http://www.sqlservercentral.com/articles/containers/178448/ - In theory, all you need to do is bind the exposed port for SSRS to a unique port for each container instance.

    I had stumbled upon this answer today while trying to figure out how to create repeatable test environment for Microsoft's Powershell Module ReportingServicesTools, and it occurred to me this would be a cheap way to also do multiple instances of SSRS on the same machine.

    NOTE: I have not yet tested this, just sharing my clever idea with everyone.

    John Zabroski supported this idea  · 
    An error occurred while saving the comment
    John Zabroski commented  · 

    I think I'll just push us to us AWS bi tools if this is how Microsoft wants to treat customers.

  5. 62 votes

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    7 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 29

    <=-=Jul 27 2016 5:17PM=-=>

    This feature vastly simplifies development of certain types of applications. Would be interesting at least know if microsoft has some intention to implement it.

    <=-=Jul 27 2016 5:19PM=-=>

    A fully compliant implementation to ansi sql 2011 would nice too.

    <=-=Jul 28 2016 1:01AM=-=>

    I would also like to see bitemporal support added, it would be most useful!

    <=-=Mar 7 2017 11:54AM=-=>

    This feature would really help people trying to migrate their DBs from DB2 or Oracle to SQL Server that much easier.

    John Zabroski supported this idea  · 
  6. 379 votes

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    6 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 249

    <=-=Oct 4 2016 2:15PM=-=>

    This is similar to my feedback located here: https://connect.microsoft.com/SQLServer/feedback/details/2769130/sql-2016-temporal-tables-with-triggers. The solution I suggest is similar to this, and could be used in many different cases developing using SQL server.

    <=-=Jan 10 2017 9:43AM=-=>

    I would love this too. The issue is that many/most applications don’t use Windows Auth in the connection to SQL Server (connection pooling issues, etc), so SQL can’t get the User information

    <=-=Jan 10 2017 9:51AM=-=>

    @Sanford

    Just to clarify, I am not asking for an “automatically store user name” feature. I’m asking for “automatically store whatever I want, based on whatever expression I provide.” So the fact that the user name may or may not be available is not really relevant.

    That said, there are plenty of workarounds for various situations. I imagine that if you’re using a shared connection, you can get some notion of “user” some other…

    John Zabroski supported this idea  · 
  7. 12 votes

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    John Zabroski supported this idea  · 
  8. 473 votes

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  18 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    John Zabroski commented  · 

    @Erland Sommarskog - quote is definitely needed, as per the RFC for CSV

    Quoting: https://tools.ietf.org/html/rfc4180

    5. Each field may or may not be enclosed in double quotes (however
    some programs, such as Microsoft Excel, do not use double quotes
    at all). If fields are not enclosed with double quotes, then
    double quotes may not appear inside the fields. For example:

    "aaa","bbb","ccc" CRLF
    zzz,yyy,***

    For interpretation of "may not", please see this RFC: https://www.ietf.org/rfc/rfc2119.txt

  9. 617 votes

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    28 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    John Zabroski supported this idea  · 
    An error occurred while saving the comment
    John Zabroski commented  · 

    This would significantly aid Microsoft SQL Server competition with Amazon Aurora.

Feedback and Knowledge Base