|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Crazy Code I | |
I have this data in my Customer table:
CustomerID CustomerName 1 Steve 2 Andy 3 Brian 4 Allen 5 DevinI run this code: SELECT t.CustomerID , c.value FROM ( SELECT CustomerID , STRING_AGG (CustomerName, ',') AS me FROM customer GROUP BY CustomerID) t CROSS APPLY STRING_SPLIT(me, ',') c;What is returned? | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Alessandro Mortola) |
STRING_AGG's behavior Executing the following script (Sql Server 2022), you get the table t0 with 10 rows: CREATE TABLE t0 ( id INT PRIMARY KEY , field1 VARCHAR(1000) , field2 VARCHAR(MAX)); INSERT INTO t0 SELECT gs.value , REPLICATE ('X', 1000) , REPLICATE ('Y', 1000) FROM generate_series(1, 10, 1) gs; GO What happens if you execute the following statements?
Answer: The query (1) gives the error message "9829 STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation". The query (2) ends successfully Explanation: According to the official documentation, the data type returned by the STRING_AGG function, depends on first argument (expression).
For this reason the first statement returns a varchar(8000) and it gives the error because the size of the resulting string is greater than 8000 characters. The second statement aggregates data from a varchar(max) field, therefore there is no problem with the size of the result. |
Database Pros Who Need Your Help |
Here's a few of the new posts today on the forums. To see more, visit the forums. |
SQL Server 2017 - Administration |
Linked server and trouble between manager and agent - Hello, I have a problem and i don´t understand it. After to link a server, i execute delete top(1) from LinkedServer.MyDB.dbo.MyTable From mananger works fine but from a agent's job fails with error 18452 "The login failed. The login is from an untrusted domain and cannot be used with Integrated Authentication." I don't know why […] |
SQL Server 2019 - Administration |
Changetracking disabled in database snapshot, new bug in ms sql 2019 ? - Hi, We are using database snapshot together with changetracking to keep a datawarehouse up to date. The database snapshot is used to have transactional consistency for the duration of the warehouse update, without having any locking issues. CREATE DATABASE [DB_SS] ON (NAME = 'DB', FILENAME = 'DBFile') AS SNAPSHOT OF [DB] The changetracking functionality is […] |
Impact of adding the opening of the Master Key in a SQL job for SSIS packages - Bonjour à tous, J'ai un problème d'exécution de mes packages SSIS. A chaque opération de basculement, les packages ne s'exécutent pas correctement et je suis obligé d'ouvrir manuellement la Master Key à chaque fois. La base de données SSISDB est en disponibilité active permanente (AG). J'ai vérifié les connexions et les configurations, mais je ne […] |
Switch from MSSQL SVC to MSA - Unable to start services - Using MSSQL 2019 standard edition It was installed using SVC Account. But now I want to switch from SVC to Managed Service Account. When I granted folder level permissions for NT SERVICE\MSSQL$SQLSERVER and NT SERVICE\SQLAgent, and tried to switch, it is throwing below error after starting agent. it shows like Services are started aling with […] |
SQL Server 2019 - Development |
Need to group the same valued ID fields to run an aggregate average over them - I have data that contains poll results, with the poll_id consistuting a single row of data for each candidate in said poll, so a poll_id will have a minimum of two different rows (not my design, parsed from ABC polling results). Sometimes there are more than 2 rows for each distinct poll_id as the survey […] |
SSIS Question - Hi everyone. I am re-writing a portion of my SSIS logic. My SSIS package downloads daily stock market data and then performs calculations for each trading day. Once a particular day's calculations have been done and stored there is no need to re-do this work when new data is added. Only new data should be […] |
SQL Azure - Development |
Azure SQL DB CLR - Hi, Azure SQL DB doesn't support CLR, if we have an application with CLR and assemblies, is there a workaround for it? SQL Managed Instance is an expensive option and Migrating to VMs will have a lot of management overhead. I would like to know if anyone has managed to migrate to SQL DB without […] |
ETL data load DML SP slowness suggestion - Hi All, We are using Microsoft SQL Azure SQL 2022 (12.0.2000.8). Some of our ETL SPs are running very longer more than 30 minutes. The reason is it is doing a lot of DML - almost 15 (Sometime 100) million Insert, update and delete. Table size is (196113141 rows and size 120 805 792 KB) […] |
SSDT |
SSIS package Excel source hanging in validation or prepare for execute phase - Hi everyone, For several weeks now we have issues with an SSIS package connected to an Azure SQL server which sometimes (at least every few days) hangs in the Validation phase of an Excel source task which reads an Excel file from a shared network drive, or lately, also failed with error in the Prepare […] |
Integration Services |
An error occurred while skipping data rows - I am trying to build an ETL for an Existing Table. The ETL (SSIS) Takes data from Excel, Convert it into CSV. The Data in Excel Source is: INSERT INTO YourTableName ( [Fiscal year/period], [Company Code], [Contra Company Code], [Account], [Level 1], [Level 2], [Level 3], [Level 4], [Generic Material], [Metric], [Unit], [Value] ) VALUES […] |
SQL Server 2022 - Administration |
Steps to restore database which is part of Trans replication and Allwayson db - We have a Setup where replication and AG is configured as below. Servers:Node1,Node2 ==>Node1 Instances: SQLNode1/distributor, SQLnode1/Pub, ==>Node2 Instances: SQLNode2/distributor, SQLnode2/Pub, --> Distributor database is configured as remote server and configured in AG between SQLNode1/distributor and SQLNode2/distributor -->Both Publisher(DB-A) and Subscriber(DB-A-reporting) database are in same instance SQLnode1/Pub and SQLnode2/Pub which are on configured on Same […] |
What is causing this error? - (This is probably the wrong forum to be posting this question, so please forgive me. I did try to figure out the correct one, but none of the other forums looked right to me.) Every time I get into SSMS 20.2 I get this error: Does anyone have any idea what's wrong and how I […] |
SQL Server 2022 - Development |
case statement with and like operators - Hello, I'm trying to remove results from this sql statement where the custid like '%abc%' and company = 'abc', but think I'm getting the syntax wrong. Not including the entire thing, hopefully it's enough. the case statement begins at t2.custid. Please help if you can, thank you! t1.Company, t2.CustID (CASE WHEN custid like '%abc%' and […] |
update error. Subquery returned more than 1 value - Hi friends. how can i fix that? ;with cte as (select distinct(e.Id ) from SecurityKmsAbfa..EnterprisePositions e join SecurityKmsAbfa..Import_UsersTeh i on e.Name=i.vahedeSazmani join NewKmsAbfa..Members m on m.SSN=i.kodeMelli ) update SecurityKmsAbfa..users set EnterprisePositionId = (select id from cte ) from SecurityKmsAbfa..Import_UsersTeh i join NewKmsAbfa..Members m on m.SSN=i.kodeMelli join SecurityKmsAbfa..users u on u.Id=m.id where u.EnterprisePositionId is null |
THE CSV PROBLEM - Hello everyone, First of all, thank you very much for this great community. I hope you can help me. I have been struggling for days with importing CSV files. The background is that I am currently doing an SQL course and would like to work with the CSV files provided. I have already tried all […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |