Solomon Rutzky

My feedback

  1. 5 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 » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

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

    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 :-)

    Solomon Rutzky commented  · 

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

    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  · 
  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 →
    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  · 
  3. 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 →
    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 ).
    _________________________________________________________________________

    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  · 
  4. 1 vote
    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 →
    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  · 
    Solomon Rutzky commented  · 
  5. 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  · 
  6. 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

    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;
    ------------------------------------------------

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

    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

  8. 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 » 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.

    Solomon Rutzky commented  · 
    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

    4 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 
    Solomon Rutzky supported this idea  · 
    Solomon Rutzky commented  · 

    One needs to also be careful not to remove square brackets from non-regular identifiers. That requires verifying both that a) all of the characters used are valid for use in regular identifiers, and b) the identifier being un-delimited is not a reserved word. I came up with an SSMS RegEx expression that is nearly 100% accurate (minor issue is that the Unicode classifications might have changed slightly between version 3.2--which is what SQL Server used to determine the list of valid characters years ago--and the version currently used by .NET). Please see DBA.StackExchange for the RegEx expression for use with Find/Replace in SSMS:

    Does this regex reliably remove square brackets from T-SQL? ( https://dba.stackexchange.com/a/198391/30859 )

  10. 4 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  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
    Solomon Rutzky commented  · 

    1) there is no such thing as "reserved characters" that require being delimited. But, there is a list of characters that do _not_ require being delimited (names using only these characters are known as "regular" identifiers, while names using characters not in the list are delimited identifiers since they require being delimited). The only known list of such characters (that I am aware of) is the list that I published here: Completely Complete List of Valid T-SQL Identifier Characters ( https://sqlquantumleap.com/reference/completely-complete-list-of-valid-t-sql-identifier-characters )

    2) Please see related item: "Remove Brackets from SSMS script generation" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32907346-remove-brackets-from-ssms-script-generation )

  11. 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 →
    Solomon Rutzky commented  · 

    So, I made the mistake of clicking on the "edit" button, and this pathetic, sad excuse of a website decided it would rather delete my comment. Nice. I guess it serves me right for thinking that progress was being made on a site that is clearly only being used by Microsoft (one of the largest, if not _the_ largest, software company on the planet) because someone at UserVoice knows someone at Microsoft.
    ________________________________________________________________________

    My comment was something along the lines of: I don't think this is a bug since it works for me on SQL Server 2017 CU15 using the following test script:
    ------------------------------------
    DECLARE @PythonScript NVARCHAR(MAX);

    SET @PythonScript = N'
    fileToSearch = "\\\\serverName\\shareName\\pathName\\fileName.txt"
    if os.path.exists(fileToSearch):
    print("File exists")
    else:
    print("File does not exist.")
    ';

    EXECUTE sp_execute_external_script
    @language = N'Python',
    @script = @PythonScript;
    ------------------------------------

    It could be a permissions issue (check the "\\server\share" permissions not the "F:\folder" permissions) as the "LaunchPad" service, by default, runs as the "NT SERVICE\MSSQLLaunchPad" account. And that account, even if it can access a local share, might not be able to access a networked share: you might need to add permissions for "computerName$" (or something like that).

    P.S. I only voted for this item because this horrible UserVoice system doesn't provide any other mechanism for notifications.

    Solomon Rutzky supported this idea  · 
  12. 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 » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported 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 →

    Upvotes: 1

    <=-=Aug 6 2017 9:14AM=-=>

    I also reproduced the same Problem against

    Microsoft SQL Server 2016 (SP1-GDR) (KB3210089) – 13.0.4202.2 (X64) Dec 13 2016 05:22:44 Copyright © Microsoft Corporation Enterprise Edition (64-bit) on Windows 8.1 Pro 6.3 <X64> (Build 9600: )
    which runs with Server collation
    SQL_Latin1_General_CP1_CI_AS

    That said, it seems to be an old issue and not related to SQL Server 2017 CTP.

    <=-=Sep 15 2017 9:48AM=-=>

    This appears to be a weighting issue that was fixed in the newer Latin1_General Collation available starting in SQL Server 2008:

    SELECT 1 WHERE N’?XXX’ COLLATE SQL_Latin1_General_CP1_CI_AS BETWEEN N’?X’ AND N’?X’;
    SELECT 2 WHERE N’?XXX’ COLLATE Latin1_General_CI_AS BETWEEN N’?X’ AND N’?X’;
    SELECT 3 WHERE N’?XXX’ COLLATE Latin1_General_100_CI_AS BETWEEN N’?X’ AND N’?X’;

    Only “3” will be returned from the queries above.

    Solomon Rutzky commented  · 

    This is a non-issue and can be closed. Comment from Sept 15th, 2017 explains that the newer collations added sort weight definitions for these characters (I believe that is actually my comment ;-).

    P.S. I only voted for this item to get notifications of other comments since this horrible UserVoice system doesn't provide any other mechanism :-( .

    Solomon Rutzky supported this idea  · 
  14. 8 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 » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    Even better would be to use something like:

    %USERPROFILE%\Microsoft SQL Server Local DB\{Instance Name}

    This would help avoid conflicts, and confusion, when multiple versions of LocalDB are installed and the same database name needs to be used across more than one of the versions. Having a single default directory for all versions is beyond annoying.

    Solomon Rutzky supported this idea  · 
  15. 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  · 
  16. 7 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 » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    I am experiencing the same problem using ODBC / SQLCMD version 17 and SQL Server 2019 CTP 2.5 LocalDB.

    Solomon Rutzky supported this idea  · 
  17. 1 vote
    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 →
    Solomon Rutzky commented  · 

    Here are two more instances where this feature would fix a bug in a system view:

    "Collation conflict … Could not use dbo.sysdac_instances" ( https://dba.stackexchange.com/a/133995/30859 )

    "Error: 468 trying to connect to SQL server with SQL Server management studio" ( https://dba.stackexchange.com/a/239450/30859 )

    Solomon Rutzky commented  · 

    Here is another example where this feature would fix a bug in a system stored procedure:

    xp_logininfo gets "Msg 468, Level 16, State 9: Cannot resolve the collation conflict..." when DB collation doesn't match instance collation ( https://feedback.azure.com/forums/908035-sql-server/suggestions/36324910-xp-logininfo-gets-msg-468-level-16-state-9-can )

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

    This ticket can be closed.

    Solomon Rutzky commented  · 

    Actually, the CHARINDEX function, when used with a Vietnamese collation, is returning the correct value. The example code given in the ticket description even shows the correct behavior happening. The problem is merely misunderstanding the linguistic rules of the Vietnamese collation.

    Even though the variables are VARCHAR, the comparison rules are still the Unicode rules due to using a Windows collation (and one of the reasons why everyone should be using Windows collations and not collations with names starting with "SQL_"). And, one of the awesome things that Unicode can do is recognize certain multi-character sequences as being a single unit that behaves differently than the individual characters that make it up. The example string of "thanh" has two instances of this: "th" and "nh". To get an individual "h", it either needs to be by itself, or you need to use a binary collation. Or, you can search for one of those combinations. The following demonstrates all of this:

    --------------------------------------------------------------------------
    SELECT CHARINDEX('h' COLLATE Vietnamese_100_CI_AS, 'thanh');
    -- 0

    SELECT CHARINDEX('th' COLLATE Vietnamese_100_CI_AS, 'thanh') AS [th],
    CHARINDEX('nh' COLLATE Vietnamese_100_CI_AS, 'thanh') AS [nh];
    -- 1 4

    -- "h" by itself:
    SELECT CHARINDEX('h' COLLATE Vietnamese_100_CI_AS, 'greyhound');
    -- 5

    -- no linguistic rules applied to binary collations:
    SELECT CHARINDEX('h' COLLATE Vietnamese_100_BIN2, 'thanh');
    -- 2
    --------------------------------------------------------------------------

    To be clear, all of this is expected behavior for Vietnamese collations. While the Unicode definition files are not the direct source of sorting data in Windows, it's still pretty close. So look at the following file, under the "traditional" section, and you can see the various characters and character combinations:

    https://github.com/unicode-org/cldr/blob/master/common/collation/vi.xml

    In there you can see a combination for "QU". If we try that out, we see the same behavior as we did with "th" and "nh":

    --------------------------------------------------------------------------
    SELECT CHARINDEX('q' COLLATE Vietnamese_100_CI_AS, 'racquet') AS [q],
    CHARINDEX('qu' COLLATE Vietnamese_100_CI_AS, 'racquet') AS [qu];
    -- 0 4
    --------------------------------------------------------------------------

    For more info on working with collations, Unicode, encodings, etc, please visit: https://Collations.Info/

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

    We’ll send you updates on this idea

    9 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    While I did up-vote this suggestion, there's no telling if/when it will be implemented. So, just to put this out there: I have included a SQLCLR aggregate function in my SQL# SQLCLR library ( https://SQLsharp.com/ ). It's called "Agg_JoinPlus", and is only available in the Full (i.e. paid) version, not in the Free version (which does include "Agg_Join" and "Agg_JoinDelim", but neither one handles ordering or duplicate removal). It works as follows:

    SELECT SQL#.Agg_JoinPlus(
    tab.[col], -- Value NVARCHAR(4000)
    N',', -- Delimiter NVARCHAR(4000)
    tab.[col], -- OrderBy NVARCHAR(4000)
    1, -- Ordering TINYINT: 0 = none, 1 = ASC, 2 = DESC
    NULL, -- InitialDelimiter NVARCHAR(4000)
    NULL, -- MinRecordsNeededForInitialDelimiter INT
    NULL, -- FinalDelimiter NVARCHAR(4000)
    NULL, -- MinRecordsNeededForFinalDelimiter INT
    NULL, -- NullReplacement NVARCHAR(4000)
    1, -- RemoveEmptyEntries BIT
    1, -- DuplicateHandling TINYINT: 0 = keep, 1 = remove (i.e. DISTINCT)
    1 -- Use Compression (to save on memory) BIT
    )
    FROM (VALUES ('a'), ('c'), ('b'), ('d'), (''), ('c')) tab(col);
    -- a,c,b,d

    Although it's not free, it does handle removing duplicates, and works with SQL Server as far back as 2008. The only platform is does not run on is Azure SQL Database since that no longer supports SQLCLR.

    Solomon Rutzky supported this idea  · 
  20. 2 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 » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    Actually, I ran the example code posted in that Stack Overflow question and found that the @Dummy method does not work. And it's basically the same issue that you reported here: NVARCHAR(MAX) has the problem, but NVARCHAR(1 - 4000) does not. Sad.

    Solomon Rutzky commented  · 

    Hi pbradshaw. It seems that "aggregate concatenation" (i.e. SELECT @x = @x + something) is not actually supported by SQL Server. The recommendation is to use one of the following:
    1) FOR XML
    2) SQLCLR
    3) Cursor

    Please see this answer on DBA.StackExchange for details: https://stackoverflow.com/a/15163136/577765

    That being said, I think there is a minor change that you could make to what you are doing that might "fix" the unexpected optimization issue: add a dummy variable containing an empty string.

    Given your example code:

    DECLARE @str varchar(max) = '';
    DECLARE @Dummy VARCHAR(1) = '';

    SELECT @str += [SomeColumn] + @Dummy + ','
    FROM SomeTable
    WHERE someCondition=true
    ORDER BY someColumn;

    The goal here is to force it to evaluate that expression each time. Anyway, something to try, at least.

    Also, just to put this out there, since SQLCLR is one of the official suggestions, and it works in versions starting at SQL Server 2005, I should mention that I included a SQLCLR aggregate function in my SQL# SQLCLR library ( https://SQLsharp.com/ ). It is called "Agg_JoinPlus", and is only available in the Full (i.e. paid) version, not in the Free version (which does include "Agg_Join" and "Agg_JoinDelim", but neither one handles ordering or duplicate removal). It works as follows:

    SELECT SQL#.Agg_JoinPlus(
    tab.[col], -- Value NVARCHAR(4000)
    N',', -- Delimiter NVARCHAR(4000)
    tab.[col], -- OrderBy NVARCHAR(4000)
    1, -- Ordering TINYINT: 0 = none, 1 = ASC, 2 = DESC
    NULL, -- InitialDelimiter NVARCHAR(4000)
    NULL, -- MinRecordsNeededForInitialDelimiter INT
    NULL, -- FinalDelimiter NVARCHAR(4000)
    NULL, -- MinRecordsNeededForFinalDelimiter INT
    NULL, -- NullReplacement NVARCHAR(4000)
    1, -- RemoveEmptyEntries BIT
    1, -- DuplicateHandling TINYINT: 0 = keep, 1 = remove (i.e. DISTINCT)
    1 -- Use Compression (to save on memory) BIT
    )
    FROM (VALUES ('a'), ('c'), ('b'), ('d'), (''), ('c')) tab(col);
    -- a,c,b,d

    Regarding STRING_AGG (mentioned by Steven in a previous comment):
    1) it started in SQL Server 2017, hence not available in SQL Server 2016 or older
    2) it DOES handle ordering
    3) it does NOT handle duplicate removal ( https://feedback.azure.com/forums/908035-sql-server/suggestions/35243533-support-distinct-for-string-agg )

    Take care, Solomon..

    https://SqlQuantumLeap.com/

    Solomon Rutzky supported this idea  · 
← Previous 1 3 4 5 6

Feedback and Knowledge Base