Forum Widgets
Latest Discussions
duplicate nodes for XML Auto
Hi, I have this xml in SQL Server 2022. select cust.cust_Id, id, timestamp, uid, [user], visible, [version], changeset, [action] , (SELECT distinct ref FROM [nd] as nd where nd.cust_Id = cust.cust_Id and tag.cust_Id = ND.cust_Id FOR XML raw('nd'), type ), k , v from cust as cust join [dbo].[nd] as nd on ND.cust_Id = cust.cust_Id join cust_tags as tag on tag.cust_Id = cust.cust_Id where cust.cust_Id=23165 for xml auto and it gives me this result. As you can see it get duplicates. Apart from the dups in <tag> it''s fine. <cust cust_Id="23165" id="1132454103" timestamp="2023-01-16T14:25:54" uid="5612131" user="k" visible="1" version="1" changeset="131338301"> <nd ref="10239832277" /> <nd ref="10559433751" /> <nd ref="10559433752" /> <nd ref="10559433753" /> <tag k="highcust" v="residential" /> <tag k="highcust" v="residential" /> <tag k="highcust" v="residential" /> <tag k="highcust" v="residential" /> <tag k="name" v="Artemis" /> <tag k="name" v="Artemis" /> <tag k="name" v="Artemis" /> <tag k="name" v="Artemis" /> <tag k="source" v="maxar" /> <tag k="source" v="maxar" /> <tag k="source" v="maxar" /> <tag k="source" v="maxar" /> </cust> Now I have modified it like this: select cust.cust_Id, id, timestamp, uid, [user], visible, [version], changeset, [action], (SELECT ref FROM [nd] as nd where nd.cust_Id = cust.cust_Id FOR XML raw('nd'), type ) , (SELECT k , v FROM cust_tags as tag where tag.cust_Id = cust.cust_Id FOR XML raw('tag'), TYPE ) from cust as cust join cust_tags as tag on tag.cust_Id = cust.cust_Id join [GIS].[dbo].[nd] as nd on ND.cust_Id = cust.cust_Id for xml auto This gives me the structure I want but the node appears multiple times as a duplicate , 5+ times due to the joins <cust cust_Id="23165" id="1132454103" timestamp="2023-01-16T14:25:54" uid="5612131" user="k" visible="1" version="1" changeset="131338301"> <nd ref="10239832277" /> <nd ref="10559433751" /> <nd ref="10559433752" /> <nd ref="10559433753" /> <tag k="highcust" v="residential" /> <tag k="name" v="Artemis" /> <tag k="source" v="maxar" /> </cust> <cust cust_Id="23165" id="1132454103" timestamp="2023-01-16T14:25:54" uid="5612131" user="k" visible="1" version="1" changeset="131338301"> <nd ref="10239832277" /> <nd ref="10559433751" /> <nd ref="10559433752" /> <nd ref="10559433753" /> <tag k="highcust" v="residential" /> <tag k="name" v="Artemis" /> <tag k="source" v="maxar" /> </cust> <cust cust_Id="23165" id="1132454103" timestamp="2023-01-16T14:25:54" uid="5612131" user="k" visible="1" version="1" changeset="131338301"> <nd ref="10239832277" /> <nd ref="10559433751" /> <nd ref="10559433752" /> <nd ref="10559433753" /> <tag k="highcust" v="residential" /> <tag k="name" v="Artemis" /> <tag k="source" v="maxar" /> </cust> Any idea how i can get rid of the duplicates. Either in the fist sql or the second. Thank you!panos2024bNov 07, 2024Copper Contributor15Views0likes1Commenthow to upgrade Browser for SQL Server 2022 16.0.1000.6 and Microsoft VSS Writer for SQL Server 2022
We need to upgrade ‘Browser for SQL Server 2022 16.0.1000.6’ and ‘Microsoft VSS Writer for SQL Server 2022 16.0.1000.6’ in order to be SOC2 compliant, we use SentinelOne application vulnerability scan reports as SOC2 evidence and SentinelOne is reporting these as vulnerable and so we are forced to remediate. We have Microsoft SQL Server 2022 (RTM-CU14-GDR) (KB5042578) - 16.0.4140.3 (X64) installed on top of Windows Server 2022 Standard We bought the server(s) from Dell with Microsoft SQL Server 2022 (RTM-CU14-GDR) (KB5042578) - 16.0.4140.3 (X64) installed on top of Windows Server 2022 Standard and we have access to the installation files for both Windows Server 2022 Standard and Microsoft SQL Server 2022 however they are both the same versions as what we currently have. We installed the cumulative update(s) SQLServer2022-KB5038325-x64.exe but this did not upgrade Browser for SQL Server 2022 or Microsoft VSS Writer for SQL Server, after some research it seems that the way it is done is buy upgrading to the latest version of Microsoft SQL Server 2022, have I missed something? … we are surely not the first to be in this situation and would appreciate some guidance. Am I correct in thinking that the only way is to get ahold of a current installation ISO of Microsoft SQL Server 2022? Any input is appreciatedGlenD1945Nov 07, 2024Copper Contributor810Views0likes12CommentsBrowser for SQL Server 2017 and 2019
Hello, my question is the following. Despite having the latest updates released for SQL Server 2019 and 2017, I notice that SQL Server Browser does not change versions, remaining in the versions mentioned below. I would like to know if they are in fact the latest versions of SQL Server Browser released in the 2017 and 2019 packages. If the versions below are correct, if they really have not been updated. Browser for SQL Server 2017 14.0.1000.169 Browser for SQL Server 2019 15.0.2000.5rafaelsalgado92Nov 06, 2024Occasional Reader10Views0likes0CommentsSQL Server failing to execute extremely complex queries
A query is failing with this error message on a customer environment: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. Sorry I cannot post the actual SQL query, which must be massive, but basically it is a union of a lot of tables. Maybe around 600 tables, maybe several thousands, I don't know exactly how many. For technical reasons, we cannot really simplify this query. In the documentation: https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16 It is specified that the maximum number of tables in a select is limited only by available resources. Is it possible to increase the size of available internal resources somehow?bboissardNov 06, 2024Copper Contributor208Views0likes6CommentsHas the SQL Server Bulk Insert Assertion Error (KB 2700641) Been Resolved in SQL Server 2019?
Hello SQL Community, I’m reaching out to confirm whether the specific issue described in KB 2700641 has been resolved in SQL Server 2019. The issue in question, which occurred in SQL Server 2008 and 2008 R2, involved an assertion error during BULK INSERT or BCP operations without the CHECK_CONSTRAINTS option enabled. Although our tables do not have constraints to check during insert operations, we had to use the CHECK_CONSTRAINTS option as a workaround in SQL Server 2008. I have reviewed the release notes for SQL Server 2012 - 2019 but couldn’t find an explicit mention of this particular fix. Before we remove the workaround from our code, could anyone confirm if this issue has indeed been addressed in SQL Server 2019? Or is there any documentation available that confirms this bug was fixed in a specific SQL Server version? Thank you for any insights or official references you could provide!auz405Nov 04, 2024Copper Contributor50Views0likes0CommentsDBCC CLONEDATABASE isn't a clone in 2022
We use DBCC CLONEDATABASE currently in SQL Server 2019 and have done in previous versions too. Having recently begun a project to migrate some servers up to SQL 2022 I've found that the use of CLONEDATABASE is no longer giving a "clone" and instead is making some assumptions about my data and implementing (badly) the LEDGER functionality. Example: Say my database has 4 tables, (Table_A,Table_B,Table_C and Table_D) all have the attributes USER_TABLE,NON_TEMPORAL_TABLE andNON_LEDGER_TABLE in sys.tables. Running DBCC CLONEDATABASE creates two of these tables as 'HISTORY_TABLE' types (instead of NON_LEDGER_TABLE), with one being paired to another one of the tables. You can see this relationship as the object_id of say Table_B is present in the 'history_table_id' column of Table_A when looking at sys.tables. The same is true of Table_C and Table_D in my scenario. It is also apparent that on some of the columns in Table_A and Table_C (the ones still created asNON_LEDGER_TABLE types) that the clone has chosen to add 'GENERATED ALWAYS AS ROW END' to some of the fields. I can't runSYSTEM_VERSIONING = OFF as I'm presented with the error that "SYSTEM_VERSIONING is not turned ON" for those tables. I can drop and manually re-create the Table_A & Table_C which removes the'history_table_id' values, but I still can't drop Table_B and Table_D as they are still seen asa ledger history table. I have no idea why SQL 2022 has made these choices on my behalf and I can't see to get around them, so for now at least I am having to script out the schema of my database and create it that way which leads to its own challenges and isn't as easy as simply specifying 'DBCC CLONEDATABASE' in code.JordanBentleyNov 01, 2024Copper Contributor74Views0likes0Commentswhat is the correct process to deploy SQL server developer script as DBA?
I am DBA currently deploying sql scripts from developers but is not clear to me what is the correct way or process to do it, my concern is about how can I reverse the changes or guarantee data integrity before or after deployment in case of something wrong happen. Should I make a backup every time I deploy? (Some times a day) is there any way to lock specific database to avoid deployment in production by mistake? any recomended tool to make it? Should the developers use always rollback in their scripts? as additional information we are implementing GIT, I am not sure if this tool can handle these thasks better and safely than SQL SSMS Can someone provide feedback please? any comment can help. Thank you all I hope to find the best and safest way to make this taskFer_MicOct 30, 2024Copper Contributor80Views0likes0CommentsSQL server query - Update the json attribute in a column query updating double quotes and back slash
Hi, i have written an update query to update a specific attribute(orderstatus) with value ({ "Packing":{"status":"completed}, "Shipping":{"status":"Completed"}) if its null Example: ["purchaseorders": { "orders":[{ "ordernum": "A1", "orderstatus": null }, { "ordernum": "A2", "orderstatus": { "Packing":{"status":"completed"}, "Shipping":{"status":"Completed"} }} ] }] Written below query to process. But after update, it inserts backslash and double quotes that encloses the whole string {\"ordernum\": \"A1\",\"orderstatus\": "{ \"Packing\":{\"status\":\"completed\"}, \"Shipping\":{\"status\":\"Completed\"}\"} Query used: update purchaseorder set details = JSON_MODIFY(details, CONCAT('$.purchaseorders.orders[',@i,'].orderstatus), statusText); any help to get this updated properly for the orderstatus is null. thanks in advancesooriyathoppanOct 30, 2024Copper Contributor110Views0likes1CommentPHP PDOStatement::rowCount problem
Hello everyone, I have a problem with the rowCount() in this code: $sqlLoc= "DECLARE @Data2 AS DATE; SET @Data2 = CONVERT(DATE,CONVERT(date, '$dataIncasso'), 102); DECLARE @Data1 AS DATE; SET @Data1 = DATEADD(DAY, $interval, @Data2) SELECT noteincassi.CodLocale,Insegna,Citta FROM [Edera].[dbo].[NoteIncassi],edera.dbo.AnagraficaLocali where DataIncasso=@Data2 AND tipoincasso='6' and AnagraficaLocali.CodLocale=NoteIncassi.CodLocale AND sospeso=0 GROUP BY noteincassi.CodLocale,insegna,Citta ORDER BY Insegna"; $queryLoc=$conn->prepare($sqlLoc,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); //$queryLoc->execute(); if($queryLoc->execute()){ echo $numero=$queryLoc->rowCount(); } $id=0; while($resultLoc=$queryLoc->fetch()){ It will print -1 but the fecth is working and row are returned,so the rest of the code is working fine, it's only the rowcount and I don't understand why. Thank you
Resources
Tags
- Data Warehouse63 Topics
- Integration Services55 Topics
- SQL Server41 Topics
- Reporting Services40 Topics
- Business Intelligence35 Topics
- SQL34 Topics
- Analysis Services28 Topics
- Business Apps22 Topics
- Analytics19 Topics
- Big Data10 Topics