Solomon Rutzky

My feedback

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

    We’ll send you updates on this idea

    5 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 3

    <=-=Dec 28 2016 7:28PM=-=>

    Thank you for submitting this feedback. We have reproduced the behavior for certificate objects that you’ve described and are now investigating the appropriate resolution. We will update this Connect item as additional information is available.

    Thanks again!
    Steven Green
    SQL Server tools team

    <=-=Feb 21 2017 12:04PM=-=>

    I have this issue as well. It makes creating a homogenous test environment amongst our development team more difficult.

    <=-=Feb 21 2017 12:09PM=-=>

    Ref: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/04dd415c-047e-421d-97fe-038328b54a9a/create-certificate-from-binary?forum=ssdt

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Please see (and vote for) related issue regarding Asymmetric Keys:

    "Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32896978-allow-asymmetric-key-to-be-created-from-binary-hex?tracking_code=59a149c86262ef9e840b5b17bcee2c89 )

    Take care,
    Solomon...
    https://SqlQuantumLift.com/
    https://SqlQuantumLeap.com/
    https://SQLsharp.com/

    Solomon Rutzky 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 →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Hi there. I believe this is just an issue with very old versions of collations. You are using the version 80 collations that came with SQL Server 2000. Unless needed for backwards compatibility, anyone using SQL Server 2008 and newer should be using _at least_ the version 100 collations, and for Japanese, the version 140 collations if using SQL Server 2017 or newer.

    The query below, adapted from your original query, shows that the issue was fixed in the version 100 collations:

    -----------------------------------------------------------------
    SELECT
        REPLACE(N'Chachach' COLLATE Czech_CS_AS, N'ጠ', N'#') AS [Czech_CS_AS],
        REPLACE(N'Chachach' COLLATE Czech_100_CI_AI, N'ጠ', N'#') AS [Czech_100_CI_AI],
        REPLACE(N'Chachach' COLLATE Slovak_CS_AS, N'ጠ', N'#') AS [Slovak_CS_AS],
        REPLACE(N'Chachach' COLLATE Slovak_100_CI_AI, N'ጠ', N'#') AS [Slovak_100_CI_AI];
    /*
    Czech_CS_AS  |  Czech_100_CI_AI  |  Slovak_CS_AS  |  Slovak_100_CI_AI
    #hachach       |  Chachach             |  #hachach         |  Chachach
    */
    -----------------------------------------------------------------

    No need to show the binary collation as that will always match code points to only themselves. Also, it is best to use the "_BIN2" collations instead of the older and not entirely correct for sorting "_BIN" collations.

    Please see the following post of mine as it has info relevant to this issue that might help explain some things, especially sections 2 (Different Versions) and 3 (Different Binary Comparison Types):

    "Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2)" ( https://sqlquantumleap.com/2019/03/13/differences-between-the-various-binary-collations-cultures-versions-and-bin-vs-bin2/ )

    This bug report can probably be closed.

    Take care,
    Solomon...
    https://SqlQuantumLift.com/
    https://SqlQuantumLeap.com/
    https://SQLsharp.com/

    P.S. I'm only voting for this suggestion to get notifications because it's the only mechanism that this horrible UserHasNoVoice platform provides.

    Solomon Rutzky supported this idea  · 
  3. 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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Hi Jassim. What exactly is the request here? Unicode isn't a singular thing. Yes, it is a single character set, but it has 3 different encodings: UTF-8, UTF-16, and UTF-32. The "N" prefixed strings as well as the NCHAR, NVARCHAR, and NTEXT (deprecated, don't use) datatypes are always UTF-16. SQL Server 2019 introduced the "_UTF8" collations, which are also Unicode, but only affect non-"N"-prefixed strings, as well as the VARCHAR and CHAR datatypes (but not the deprecated TEXT datatype).

    So, if you are using SQL Server 2019 AND are using the "_UTF8" collations, then you can use Unicode without prefixing string literals with "N" or using NVARCHAR / NCHAR. Of course, UTF-8, while convenient in some cases, can be bad for other cases.

    And since you can do Unicode in non-"N"-prefixed / VARCHAR now, why is there a request to remove the "N" prefix? You can simply use UTF-8 and not use "N"-prefixed strings or NVARCHAR / NCHAR.

    Or, are you wanting to do UTF-16 in non-"N"-prefixed / VARCHAR? That would make SQL Server like several other RDBMSs, and that could be interesting, but currently that concept doesn't even make sense as there would be no way to indicate that non-"N"-prefixed / VARCHAR data should be UTF-16. Maybe they could add a new set of collations (not an exciting idea as there are already 5500 collations). Maybe a database-scoped configuration could be added to indicate that VARCHAR is actually UTF-16?

    However, what exactly are you requesting? It isn't very clear.

    If it helps, please see the following post of mine that explains how encodings work across the various string datatypes:

    How Many Bytes Per Character in SQL Server: a Completely Complete Guide ( https://sqlquantumleap.com/2019/11/22/how-many-bytes-per-character-in-sql-server-a-completely-complete-guide/ )

    Take care,
    Solomon...
    https://SqlQuantumLift.com/
    https://SqlQuantumLeap.com/
    https://SQLsharp.com/

    P.S. I'm only voting for this suggestion to get notifications because it's the only mechanism that this horrible UserHasNoVoice platform provides.

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

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This suggestion was actually fulfilled back in SQL Server 7.0 (when the NVARCHAR, NCHAR, and NTEXT datatypes were introduced), before any emojis (or any other Supplementary Characters) even existed. There is nothing truly special about emojis. They are just one or more Unicode code points that are handled mostly by fonts and the display / UI subsystem of the OS. Some are "regular" BMP characters (e.g. U+267B    ♻    recycling symbol), some are Supplementary Characters (e.g. U+1F4B0    💰    money bag), and some are combinations are one or both of those (e.g. U+1F939 U+200D U+2640 U+FE0F    🤹‍♀️    woman juggling). All of these are valid NVARCHAR data:

    SELECT N'  ♻    💰    🤹‍♀️  ';

    If the "woman juggling" appears as two characters, just copy and paste the result cell from the grid in SSMS into a text area of a form on a web page and it should appear as a single emoji. If you don't see anything but square boxes, then that is just a font issue, in which case copying the cell in the results grid and pasting into a text area in a browser should again display it correctly. Or, try the following (which should work on all versions of SQL Server starting with 7.0, as long as you are using a client tool and OS that supports emojis and has at least one font loaded that has those glyphs):

    --------------------------------------------------------
    DECLARE @Emoji TABLE
    (
         [name] VARCHAR(50) NOT NULL,
         [character] NVARCHAR(20) NOT NULL
    );

    INSERT INTO @Emoji ([name], [character])
         VALUES ('recycling symbol', NCHAR(0x267B));
    INSERT INTO @Emoji ([name], [character])
         VALUES ('money bag', NCHAR(0xD83D) + NCHAR(0xDCB0));
    INSERT INTO @Emoji ([name], [character])
         VALUES ('woman juggling', NCHAR(0xD83E) + NCHAR(0xDD39) + NCHAR(0x200D)
                                            + NCHAR(0x2640) + NCHAR(0xFE0F));

    SELECT * FROM @Emoji;
    --------------------------------------------------------

    Please see the following answer of mine on DBA.StackExchange where I show how to do this:

    How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character? ( https://dba.stackexchange.com/a/139568/30859 )

    Please see the following post of mine where I explain this in detail:

    How Many Bytes Per Character in SQL Server: a Completely Complete Guide ( https://sqlquantumleap.com/2019/11/22/how-many-bytes-per-character-in-sql-server-a-completely-complete-guide/ )

    This suggestion can be closed.

    Take care,
    Solomon...
    https://SqlQuantumLift.com/
    https://SqlQuantumLeap.com/
    https://SQLsharp.com/

    Solomon Rutzky supported this idea  · 
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    (note: I am only voting for this suggestion to get notifications since this barely-functional UserVoice platform doesn't provide any other mechanism for subscribing)

  5. 6 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Also, due to not being able to update this suggestion on this horrible UserVoice platform, I should add:

    If it isn't clear, I don't see any conflict, even conceptually, between a database using a collation that is not Supplementary Character-Aware and the 𝙽𝙲𝙷𝙰𝚁() function returning Supplementary Characters in that database. The 𝙽𝙲𝙷𝙰𝚁() function isn't interpreting characters in any way; it's merely doing a simple algorithm to return 2 or 4 bytes based on the supplied value. This, like the 𝙳𝙰𝚃𝙰𝙻𝙴𝙽𝙶𝚃𝙷() function, has nothing to do with collations / is not collation-sensitive. In fact, this request is consistent with the non-"_SC" version 90 and 100 collations having sort weights added to many of the surrogate code points so that Supplementary Characters could at least be distinguished from each other, even if the collations aren't fully Supplementary Character-Aware.

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Oops, typo in title: starting range should be 0x10000, not 0x1000 (since 0x1000 - 0xFFFF are BMP characters and already work). Unfortunately, no way for me to update the title now.

    Solomon Rutzky shared this idea  · 
  6. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky shared this idea  · 
  7. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 23

    <=-=Aug 6 2008 10:00AM=-=>

    Hello,

    Thank you for sharing your idea with us. As I understand from your description the problem is database collation rather than SQL Server instance collation. E.g. would having new databases collate to system local by default solve your problem as well?

    Thanks,
    Krzysztof Kozielczyk

    <=-=Aug 6 2008 12:04PM=-=>

    I’m afraid that I don’t really understand your comment. While it’s great to have the ability to have multiple collations in the same server instance, for very many users this of little interest. They are best served by having the same collation in the entire server, or else they will have to fight battles with collation conflict.

    If I understand you correctly, you suggest that rather than inhering the server collation as the default, the default for the database collation would be derived from the system locale. I am afraid that this would only aggrevate…

    Solomon Rutzky supported this idea  · 
  8. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
  9. 10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    22 comments  ·  SQL Server » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
  10. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
  11. 4 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 →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    As far as I can tell, "Feature Restrictions" has been disabled in the official, RTM release of SQL Server 2019.

    Thank you, Microsoft, for preventing customers from using this feature until it can be improved, or maybe even replaced with new, named permissions that can be managed via the existing GRANT / REVOKE / DENY framework 😺 .

    I have updated my post with this info, including a link to a live demo showing that it reports being disabled if you attempt to add a new restriction:

    https://sqlquantumleap.com/2019/08/05/feature-restrictions-in-sql-server-2019-are-worse-than-useless-a-false-sense-of-security-and-wasted-opportunity/#update_20191028

    Take care,
    Solomon...
    https://SQLsharp.com/

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    I updated my post (linked in the main content above) to add the following to the "Conclusion", to provide more explanation of why I feel that spending any more time trying to fix / expand this feature is futile, and provide some suggestions that I feel would do a better job of accomplishing this goal (which I do agree with):

    _________________________________________________________________________
    This feature is intended to improve security when using Dynamic SQL. When using Dynamic SQL, it is common to run into permissions problems resulting from broken Ownership Chaining. While it is best to use Module Signing ( https://ModuleSigning.Info/ ) in order to resolve the permissions errors, many people still use the `WITH EXECUTE AS` clause with either `'dbo'` or `OWNER` as the User to "Execute As" (and if the Schema is owned by `dbo`, then that is the same as specifying `'dbo'`). If "Feature Restrictions" does not work with the `dbo` User, and yet it is common for stored procedures using Dynamic SQL to be executing as `dbo`, then how often will "Feature Restrictions" actually be restricting anything?

    If anything is to be restricted, why not expand the existing permissions hierarchy by making more commands their own named permissions, commands such as `WAITFOR`, `RAISERROR`, `TRUNCATE TABLE`, `PRINT`, etc. Doing this fits naturally into the `GRANT` / `DENY` / `REVOKE` system that people are familiar with and would allow for `DENY`ing something such as `WAITFOR`, which can be a Database-level permission (along with `PRINT` and `RAISERROR`, while `TRUNCATE TABLE` can be Schema-level and/or object-level).

    If the desire is to make Dynamic SQL more secure _without_ requiring code changes (or at least a lot of them), then a far better approach would be to attempt preventing end-user manipulation of the query, rather than what the query can do once it has been manipulated. There are only a few methods that someone can use to inject their own SQL, but if they can do even one of them, then there is a lot that they can do. Rather than focus on cleaning up a mess, focus on _preventing_ the mess (i.e. "an ounce of prevention is worth a pound of cure"). If the problems all begin with User input, provide one or more mechanisms to filter one or more input parameters. For example, implementing the following suggestion would not only work for this scenario, but it also handles other scenarios, thereby increasing the ROI for the time / energy put into developing it: "Intercept query batch from client to rewrite it or cancel it" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32896183-intercept-query-batch-from-client-to-rewrite-it-or ).
    _________________________________________________________________________

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    I found two more problems:

    1. If the `EXECUTE AS` clause specifies a User that is associated with Login that is “sysadmin”, _and_ the DB is set to `TRUSTWORTHY ON`, _and_ the DB is owned by a Login that has a certain level of permissions, that will prevent the restrictions from working as expected.

    2. No permissions are required to `SELECT` from `sys.sql_feature_restrictions` system catalog view, even though it should require at least having the `CONTROL` DB-level permission.

    I have updated my blog post (linked at the bottom of the main ticket body above) to include these new issues.

    Solomon Rutzky shared this idea  · 
  12. 5 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 » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Just FYI: this is still broken in SSMS 18.4 (15.0.18206.0).

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Just FYI: this is still broken in SSMS 18.3.1 (15.0.18183.0).

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Just FYI: this is still broken in SSMS 18.2 (15.0.18142.0).

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    I realize that the release notes for the RTM / GA release of SSMS 18.0 states that this bug was fixed, but technically speaking, it is not 100% fixed. It takes a few additional steps now, but I am still able to inject my own T-SQL, thanks to another bug that I found while testing this :-)

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This feature, er, bug ;-) still exists in SSMS 18.0 Preview 7.

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Please see related suggestion:

    SSMS: Allow forcing case-insensitive matching in Object Explorer filters ( https://feedback.azure.com/forums/908035-sql-server/suggestions/36679522-ssms-allow-forcing-case-insensitive-matching-in-o )

    Solomon Rutzky shared this idea  · 
  13. 1 vote
    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
    Solomon Rutzky commented  · 

    Just FYI: this is still broken in SSMS 18.4 (15.0.18206.0).

    Solomon Rutzky shared this idea  · 
  14. 3 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky shared this idea  · 
  15. 3 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    I was able to reproduce this using the following T-SQL, which also shows that DBCC INPUTBUFFER does work correctly in the same context:

    ---------------------------------------------------
    USE [tempdb];
    CREATE LOGIN [InpBufTest] WITH PASSWORD = 'Show Me The Buffer!';
    CREATE USER [InpBufTest] FOR LOGIN [InpBufTest];

    EXECUTE AS LOGIN = N'InpBufTest';
    SELECT USER, SYSTEM_USER;

    DBCC INPUTBUFFER(@@SPID); -- works
    SELECT * FROM sys.dm_exec_input_buffer(@@SPID, CURRENT_REQUEST_ID()); -- error:
    /*
    Msg 300, Level 14, State 1, Line XXXXX
    VIEW SERVER STATE permission was denied on object 'server', database 'master'.
    Msg 297, Level 16, State 1, Line XXXXX
    The user does not have permission to perform this action.
    */
    REVERT;

    DROP USER [InpBufTest];
    DROP LOGIN [InpBufTest];
    ---------------------------------------------------

    Please note that there is a related documentation issue for this bug:

    Errors occur when the user does not have VIEW SERVER STATE permission. #2846 ( https://github.com/MicrosoftDocs/sql-docs/issues/2846 ).

    Take care,
    Solomon....
    https://SqlQuantumLeap.com/

    Solomon Rutzky supported this idea  · 
  16. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    A new use for this feature has come up: intercepting queries from application users / Dynamic SQL to filter on patterns that indicate SQL Injection.

    Perhaps this feature could:

    1) be configurable per each Login and/or User.
    2) filter on total number of statements in the batch (additional statements would indicate end-user manipulation)
    3) allow for filtering on patterns via Regular Expressions (RegEx)
    4) might need some ability to add conditional logic if the goal is to work with absolutely NO code changes (is it possible to pass in a "filter_profile" or some new connection string keyword?)

    I think this would be a much better approach to preventing SQL Injection as opposed to the new "Feature Restrictions" feature that was introduced in SQL Server 2019 CTP 3.2:

    "URGENT: "Feature Restrictions" actually increases chances of SQL Injection and must be removed immediately (before RTM)" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/38319796-urgent-feature-restrictions-actually-increases )

    Solomon Rutzky supported this idea  · 
    An error occurred while saving the comment
    Solomon Rutzky commented  · 
  17. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky shared this idea  · 
  18. 0 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 » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Jan 18 2010 4:09PM=-=>

    Hi,
    Thanks for reporting the issue. These type of errors are however specific to your environment / database. It will be good if you can provide us with a sample database & script that demonstrates the issue. We will also look at the code based on the error message to see what the pontential problem might be. So if you can provide us with a repro that will be great.


    Umachandar, SQL Programmability Team

    <=-=Nov 12 2010 2:08PM=-=>

    Closed the feedback item.


    Umachandar

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    I assume that this was reported for SQL Server 2005 or 2008, which I have not tested. But on SQL Server 2017, the following works just fine:

    ------------------------------------------------
    DECLARE @IssueHistory TABLE ([EventText] NVARCHAR(MAX));
    INSERT INTO @IssueHistory ([EventText]) VALUES (N'dfdfgdfg' + NCHAR(0x0D) + NCHAR(0x0A) + N'sdfs');

    declare @LFCR char(2)
    declare @CRLF char(2)
    declare @CR char(1)
    declare @LF char(1)
    set @LFCR=char(10)+char(13)
    set @CRLF=char(13)+char(10)
    set @CR=char(13)
    set @LF=char(10)

    UPDATE hist
    SET hist.EventText = replace(replace(replace(replace(hist.[EventText], @CRLF, '<br>'), @LFCR, '<br>'), @LF, '<br>'), @CR, '<br>')
    FROM @IssueHistory hist

    SELECT * FROM @IssueHistory;
    ------------------------------------------------

  19. 0 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 →

    Upvotes: 1

    <=-=Jun 24 2014 4:29PM=-=>

    Hello,
    After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. �The reasons for closing these bugs are following:
    1.������ The fix is risky to implement in the current version of the product (service packs)
    2.������ Scenarios reported in the bug are not common enough
    3.������ A viable workaround is available

    Thanks again for reporting the product issue and continued support in improving our product

    Jos de Bruijn – SQL Server PM

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    There is an easy workaround / fix for this: use a binary collation in the REPLACE() function. For example, the following query:

    DECLARE @NullTerminated NVARCHAR(500);
    SET @NullTerminated = N'one' + NCHAR(0) + N'two';

    SELECT @NullTerminated,
    REPLACE(@NullTerminated COLLATE Latin1_General_100_CI_AS, NCHAR(0), N''),
    REPLACE(@NullTerminated COLLATE Latin1_General_100_BIN2, NCHAR(0), N'');

    returns:

    one ... one ... onetwo

  20. 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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Nov 17 2016 6:36AM=-=>

    Solomon, such function already exist, NEXT VALUE FOR , see https://msdn.microsoft.com/en-CA/library/ff878370.aspx?f=255&MSPPError=-2147217396, and gets value before insert, not after. Knowing it before is a lot more advantageous than after as with Scope_Identity IMHO

    <=-=Nov 18 2016 10:50AM=-=>

    Hi Solomon,

    Thank you for reaching out to Microsoft.

    The current value of a sequence is exposed by the sys.sequences catalog view (https://technet.microsoft.com/en-us/library/ff877934(v=sql.110).aspx)
    Would this work in your scenario?

    Thanks,
    Panagiotis Antonopoulos

    <=-=Dec 11 2016 3:00PM=-=>

    Hi @Panagiotis. Thanks for the reply. To be honest, I don’t have a current scenario that I need this for. I was just doing some testing with Sequences and thought that it might be nice to have. It would have made what I was doing at the time easier to validate, so I figured others might have run into this as well, especially since SCOPE_IDENTITY() is used so frequently.

    An error occurred while saving the comment
    Solomon Rutzky commented  · 
    Solomon Rutzky supported this idea  · 

Feedback and Knowledge Base