Forum Widgets
Latest Discussions
Filestream error - File location cannot be opened
Trying to solve this error: The File location cannot be opened. Either access is not enabled or you do not have permissions for the same. when trying to open a File Table using "Explore FileTable Directory" This is a client's computer and these are the configurations I have checked: SQL Server v 2019 (installed cumulative update today) Filestream enabled in configuration manager. All 3 check boxes are checked. In SSMS, instance has: Filestream Access Level = "Full access enabled", Filestream Share Name: MSSQLSERVER DB properties Filestream Directory Name is specified (and exists), Filestream Non-Transacted Access = "Full" Logged into computer as an admin and into SSMS as sa. I can execute a select statement against a filetable but am unable to access via the "Explore FileTable Directory" option. What am I missing?jvalleeFeb 14, 2025Copper Contributor4Views0likes0CommentsUnable to installing the SQL server 2022 reporting services on the Windows 10 Pro and Windows 11Pro
I am writing to you because I am encountering an issue while attempting to install SQL Server 2022 Reporting Services on Windows 10 Pro and Windows 11 Pro machines. While I have been able to start the installation process, I am unable to run the SQL Server 2022 Reporting Services .exe file. I have included the error message log details below for your reference. Application: SQLServerReportingServices.exe Framework Version: v4.0.30319 Description: The process was terminated due to an internal error in the .NET Runtime at IP 6CD5D612 (6CA70000) with exit code 80131506. I would greatly appreciate any assistance you can provide in resolving this issue. Thank you for your time and attention to this matter.jalotiaFeb 13, 2025Copper Contributor21Views0likes0CommentsNeed help to figure the solution for recent vulnerabilities report in my DB host server.
Dear Experts, During recent scans, below vulnerability was reported in my Stage database server, this is my current DB version Microsoft SQL Server 2019 (RTM-CU23) (KB5030333) - 15.0.4335.1 (X64). This scan item we keep coming on every scan item and need to a proper solution to fix. we have applied the patch- KB5030333 but still issue was reported. Even though planning to apply the Jan 2024 patch SQL db patch but i'm not confident if the patch will fix this scan or not as this scan item is coming from 2023 July onwards and after that we have applied JNS and OND patches as well. but again in jan scans this item came in. Need your expertise to understand the real cause of this scan and how to fix the same. Note: I'm very new to SQL DB and started learning from last 4-5 months. Vulnerability Title Vulnerability Description Vulnerability Proof Vulnerability Solution Microsoft SQL Server Obsolete Version: Remote An obsolete version of the Microsoft SQL database server is running. Note: When the support period ends for a Microsoft SQL Server product, no further patches will be provided even for serious security problems. * Running TDS service * Product SQL Server 2008 found in fingerprint is not SQL Server 2000 * Product SQL Server 2008 found in fingerprint is not SQL Server 2005 * Product SQL Server 2008 exists -- Microsoft SQL Server 2008 10.0.2531 Upgrade to the latest version of Microsoft SQL Server Download and apply the upgrade from: http://technet.microsoft.com/sqlserverMaheshKSFeb 12, 2025Copper Contributor4.6KViews0likes13CommentsSQL 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 12, 2025Copper Contributor38Views0likes1CommentUsing 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 11, 2025Copper Contributor105Views0likes9CommentsHow 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;amrragab23Feb 11, 2025Copper Contributor47Views0likes2CommentsUnable 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 Contributor27Views0likes0CommentsNeed 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 Contributor23Views0likes1CommentSQL 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 Contributor65Views0likes2CommentsNeed 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 Contributor22Views0likes1Comment
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