John Zabroski

My feedback

  1. 3 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    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  · 
  2. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    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  · 
  3. 324 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  46 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.

  4. 54 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    4 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  · 
  5. 348 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    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  · 
  6. 8 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    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  · 
  7. 422 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  16 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

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

    We’ll send you updates on this idea

    21 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