Forum Widgets
Latest Discussions
Data Archival in Merge replication
We are working on purging data in 3.5 TB database which is acting as a publisher in Merge replication with 2 subscribers which are hosted on EC2 machines in Europe and US regions respectively. We are looking for a Data Archival Strategy to optimize the data sync between publisher and subscribers in the best possible way as the volume of records that are going to be purged in publisher will be in millions. This huge data purge in publisher will choke the subscribers and might delay the data sync from days to weeks. Hence we are looking for some recommendations on the archival strategy.sunderbNov 09, 2024Copper Contributor94Views0likes1CommentAuthentication issues when trying to Migrate Data using VS Code from SQL server
Hi, I am trying to migrate data from our SQL database which is on one virtual machine to another virtual machine on the same network using VS code and Node JS. I keep getting authentication errors. On the SQL server the error is "Login failed for user 'xxxx'. Reason: Could not find a login matching the name provided. [CLIENT: xxx.xxx.xx.xx]". The user details entered are correct and I am admin on the SQL Server. I have checked all permissions. Event ID: 18456Joe_C96Nov 09, 2024Copper Contributor140Views0likes2Commentsduplicate 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!Solvedpanos2024bNov 08, 2024Copper Contributor28Views0likes2Commentshow 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 Contributor821Views0likes12CommentsBrowser 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, 2024Copper Contributor13Views0likes0CommentsSQL 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 Contributor216Views0likes6CommentsHas 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 Contributor57Views0likes0CommentsDBCC 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 Contributor78Views0likes0Comments
Resources
Tags
- Data Warehouse63 Topics
- Integration Services55 Topics
- SQL Server41 Topics
- Reporting Services41 Topics
- Business Intelligence35 Topics
- SQL34 Topics
- Analysis Services28 Topics
- Business Apps22 Topics
- Analytics19 Topics
- ssms10 Topics