Forum Widgets
Latest Discussions
Using XQUERY on complicated XML
Hi All, and thanks in advance for any help you can provide. I'm currently working on SSMS v20 and am trying to parse XML in SQL Server using XQUERY. I've looked at dozens of examples, tutorials, etc., but every one has such simplistic XML that I can't figure out how to do it on my more complicated (more levels) XML. Here is a sample of data in an XML column: <dcjis:DataExchange xmlns:dcjis="http://www.xfact.com/schemas/eopss/dcjis/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dcjis-ext="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:dot-ec="http://www.xfact.com/schemas/eopss/dot-ec/1.0/extension" xmlns:nc="http://niem.gov/niem/niem-core/2.0" xmlns:j="http://niem.gov/niem/domains/jxdm/4.0" xmlns:s="http://niem.gov/niem/structures/2.0"> <dot-ec:Vehicle s:id="vehicle1"> <nc:VehicleColorPrimaryCode>BLU</nc:VehicleColorPrimaryCode> <nc:ItemMakeName>VOLK</nc:ItemMakeName> <nc:ItemModelName>JETTA</nc:ItemModelName> <nc:ItemModelYearDate>2011</nc:ItemModelYearDate> <nc:VehicleCMVIndicator>false</nc:VehicleCMVIndicator> <nc:ConveyanceRegistration> <nc:ConveyanceRegistrationPlateIdentification> <nc:IdentificationID>194JM4</nc:IdentificationID> <nc:IdentificationExpirationDate> <nc:Date>2020-04-30</nc:Date> </nc:IdentificationExpirationDate> <j:DrivingJurisdictionAuthorityANSID20Code>MA</j:DrivingJurisdictionAuthorityANSID20Code> <nc:IdentificationJurisdictionFIPS10-4Code>US</nc:IdentificationJurisdictionFIPS10-4Code> </nc:ConveyanceRegistrationPlateIdentification> <dot-ec:VehiclePlateTypeCode>PAN</dot-ec:VehiclePlateTypeCode> <dot-ec:VehiclePlateTypeText>Passenger Normal</dot-ec:VehiclePlateTypeText> </nc:ConveyanceRegistration> <dot-ec:VehiclePassenger16PlusIndicator>false</dot-ec:VehiclePassenger16PlusIndicator> <dot-ec:HazMatPlacardDisplayedIndicator>false</dot-ec:HazMatPlacardDisplayedIndicator> <dot-ec:VehicleRegistrationUnknownIndicator>false</dot-ec:VehicleRegistrationUnknownIndicator> <nc:InsuranceCarrierName>GOVT EMPLOYEE INS</nc:InsuranceCarrierName> </dot-ec:Vehicle> </dcjis:DataExchange> Thanks.Solved0105Feb 10, 2025Copper Contributor83Views0likes8CommentsUnable to verify Authenticode signatures due to error code 1627
Hi all, our SQL Server (which has been running just fine the past couple of years) suffered from a faulty (ECC) memory module last week, which is in the process of being replaced (currently the SQL Server runs on 96GB instead of 128GB. The SQL Server doubles as a Power BI Gateway btw, which might or might not be relevant. When rebooting the server (scheduled server reboot during the weekends), SQL Errorlog comes up with this error below, which I've never encountered before: "Unable to verify Authenticode signatures due to error code 1627. Signature verification of SQL Server DLLs will be skipped. Genuine copies of SQL Server are signed. Failure to verify the Authenticode signature might indicate that this is not an authentic release of SQL Server. Install a genuine copy of SQL Server or contact customer support." I've tried to search for related problems and answers, however, so far I came up with nothing. I've read somewhere that Error code 1627 seems to suggest a system error, advising to reinstall the OS, but I'm not really convinced this is the same issue. Due to there having been a faulty memory module, I'm not entirely willing to write off the possibility, however, I do find this unlikely since the module was an ECC module. Does anyone know what causes this error or how to go about pinpointing the root cause? Any thoughts are appreciated. Cheers, NielsC4YNelisFeb 10, 2025Copper Contributor9Views0likes0CommentsNeed help with an SQL query without using a cursor
Hi there to all SQL gurus So, here is the scenario. I have a #temp table in one of my SQL stored procedures which has only 2 columns, say Customer ID and Profile ID, and it has the below data Customer ID Profile ID 100001 ABCD001 100001 ABCD002 100002 ABCD001 100002 ABCD002 100003 ABCD001 I need to write a query which selects only the Profile ID which is mapped to all the Customer IDs. In this case Customer ID 100001 and 100002 have both ABCD001 and ABCD002, but Customer ID 100003 has only Profile ID ABCD001, so, the output of the SQL should have only ABCD001. How do I do this without using a CURSOR? Would a CTE help? I am not very familiar with CTE, so if the solution is using a CTE, please give your suggestions in more detail. Thanks in advanceKrishnaGcebFeb 10, 2025Copper Contributor12Views0likes1CommentHow can I optimize this query for better performance
Hi , I have this query and it is taking a long time. If there are more than 10k rows, it takes more than 5 minutes. Is there another way to speed up the process? SELECT ROW_NUMBER() OVER (ORDER BY CreationDate DESC) AS RowId, Id [Id], transactionsId [TransactionsId], amount [Amount], AccountId [AccountId], dbo.Account_FirstBalance(CreationDate, AccountId, 161, CompanyId) [FirstBalance] FROM p_Ledger WHERE CreationDate >= '2024-11-01' AND CreationDate <= '2025-02-11' AND CompanyId = 117 AND branchId = 161 ALTER FUNCTION [dbo].[Account_FirstBalance]( @TransactionsDate DATETIME, @AccountId BIGINT, @BranchId INT, @CompanyId BIGINT ) RETURNS FLOAT AS BEGIN DECLARE @credit FLOAT; SELECT @credit = SUM(CASE WHEN T.transactionStatusId = 1 THEN T.amount ELSE -T.amount END) FROM dbo.Transactions T WHERE T.Approval = 1 AND T.CompanyId = @CompanyId AND T.AccountsId = @AccountId AND T.IsDeleted = 0 AND T.transactionsDate < @TransactionsDate AND (@BranchId = 0 OR T.branchId = @BranchId); RETURN ROUND(COALESCE(@credit, 0), 2); END;29Views0likes1CommentSQL Server Distributed AG's Forwarder Is Not Syncing After Primary AG's Internal Failover
I have set up a SQL Server Distributed Availability Group (DAG) in Kubernetes using SQL Server on Ubuntu images. The setup consists of two availability groups (AGs) across two separate clusters: Setup Details: Primary Cluster (AG1) Pods: ag1-0 (Primary), ag1-1, ag1-2. The Primary is Exposed via the LoadBalancer service. Remote Cluster (AG2): Pods: ag2-0 (The Primary of AG2, Acting as a forwarder of DAG), ag2-1, ag2-2. The Forwarder (ag2-0) is Exposed via the LoadBalancer service. Distributed AG Configuration: AG1 and AG2 are part of the DAG. Each AG’s primary is dynamically selected using the pod's label role=primary. LISTENER_URL in the DAG configuration points to the LoadBalancer service of each AG. Issue: DAG Not Syncing After AG1 Failover For testing, I triggered a failover in AG1 using: `ALTER AVAILABILITY GROUP [AG1] FORCE_FAILOVER_ALLOW_DATA_LOSS;` The global primary changed from ag1-0 to ag1-1, and I updated the role=primary label accordingly (removed from ag1-0, added to ag1-1. However, AG2 (the forwarder and its replicas) stopped syncing and became unhealthy. From ag2-0 (forwarder) logs, I only see connection timeouts and disconnections from the global primary. AG2 is not automatically reconnecting to the new primary (ag1-1), even though the LoadBalancer service in LISTENER_URL now points to ag1-1. Logs from ag2-0 (Forwarder) Shows Like A connection timeout has occurred while attempting to establish a connection to GLOBAL PRIMARY. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance Steps I Tried: - Checked DAG Configuration – The LISTENER_URL is correctly set to the LoadBalancer of AG1, which now points to ag1-1. - Ran the Resume Command: `ALTER DATABASE [agtestdb] SET HADR RESUME;` This did not resolve the issue. - Verified Network Connectivity Questions: - What steps are required to ensure AG2 correctly syncs with the new global primary (ag1-1) after an AG1's internal failover? - Is there a specific command that needs to be run on the forwarder (ag2-0) or the new global primary (ag1-1) to reestablish synchronization? - Why isn’t AG2 automatically reconnecting, even though the LoadBalancer service points to the correct primary? - Are there any best practices for handling SQL Server DAG failovers in Kubernetes? Any insights would be greatly appreciated!neajmorshadFeb 10, 2025Copper Contributor12Views0likes0CommentsSQL Server Collation
Hi Experts, I am doing cross database migration. source database characterset is ISO_1 (ISO 8859-1) and binary sorting. While checking equivalent collation in MS SQL, I found Latin1_General_BIN. Later, I checked that all 4 collations are equivalent to the source database characterset and sorting. Latin1_General_BIN Latin1_General_BIN2 Latin1_General_100_BIN2 Latin1_General_100_BIN2_UTF8 Could you share your expert advice on the difference between the above collations and which one is best?Solvedsljesra27Feb 09, 2025Copper Contributor55Views0likes2CommentsNeed a solution for a problem without using CURSOR
Hi there to all SQL gurus So, here is the scenario. I have a #temp table in one of my SQL stored procedures which has only 2 columns, say Customer ID and Profile ID, and it has the below data Customer ID Profile ID 100001 ABCD001 100001 ABCD002 100002 ABCD001 100002 ABCD002 100003 ABCD001 I need to write a query which selects only the Profile ID which is mapped to all the Customer IDs. In this case Customer ID 100001 and 100002 have both ABCD001 and ABCD002, but Customer ID 100003 has only Profile ID ABCD001, so, the output of the SQL should have only ABCD001. How do I do this without using a CURSOR? Would a CTE help? I am not very familiar with CTE, so if the solution is using a CTE, please give your suggestions in more detail. Thanks in advanceKrishnaGcebFeb 09, 2025Copper Contributor17Views0likes1CommentQuestions about converting date
I used a function to convert character to date and then do a calculation using datediff. ,CONVERT(DATETIME,CONVERT(CHAR,cs.LastCourseActivityDate)) AS Last_Course_Activity_Datetime ,case when m.CompletionStatusYear = 2023 then '2023-12-31 00:00:00.000' when m.CompletionStatusYear = 2024 then '2024-21-31 00:00:00.000 else null end as 'Census' WHEN CompletionStatusGroup <> 'Complete - Reported' AND DATEDIFF(DAY ,last_course_activity_datetime,census)< 90 then 'In_Training' However, for some reason, when I run the script, it gives me an error "Conversion failed when converting date and/or time". How can I fix this?kpan8Feb 07, 2025Copper Contributor46Views0likes3CommentsSQL Server 2019 End of Support
Hi all I tried contacting a few people about this including our Microsoft supplier but with little information. Can any give me an idea of costs for support of general bug fixes i.e. If we encounter a platform issue during this period, potentially after patching/upgrading application software will there be support from Microsoft (without charge and possible delay)?LpienaarFeb 06, 2025Copper Contributor18Views0likes0CommentsStore Procedure to capture changes to tables/column
I need help to develop a stored procedure that can capture changes to all tables/columns in a CDC-enabled SQL database (all the tables are also CDC-enabled). The database is on SQL 2016. Store these operational changes (INSERTS, UPDATES, DELETES) into another table. The procedure should also capture old value, new value, and the date/time the changes took place.Technical_Architect5Feb 04, 2025Copper Contributor73Views0likes5Comments
Resources
Tags
- Data Warehouse65 Topics
- Integration Services58 Topics
- sql server46 Topics
- Reporting Services43 Topics
- SQL39 Topics
- Business Intelligence35 Topics
- Analysis Services30 Topics
- Business Apps22 Topics
- Analytics19 Topics
- ssms13 Topics