User Profile
SivertSolem
Iron Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Re: Current SQL Server Certifications
This short-URL leads to the most up to date Certification Poster published by Microsoft. https://aka.ms/TrainCertPoster The Azure Database Administrator certification, Microsoft Certified: Azure Database Administrator Associate - Certifications | Microsoft Learn, is the only certification I'm aware of that relates to SQL Server itself, and contains both SQL Server on-premises and Azure SQL Services.7Views0likes0CommentsRe: Two missing record from this query
Despite the fact that you're technically in the wrong forum, this is a logic error. Because of the "MAX" statement, you'll only get one match with that t1.LMDTM1 = (...) comparison. Of course, returning more than one row on that subquery would lead to other errors. I would attempt to replace your equality and subquery with a join, see if that helps. SELECT '20'||SUBSTR(CAST(T1.LMDTM1 as VarCHar(15)), 2, 6) as Date, T1.LMFSTM as Status, T1.LMREF1 as CustPO, TRIM(T1.LMREF1) ||'-'||TRIM(T1.LMREF2) as Key, T1.LMREF2 as Order, T1.LMREF3 as GUID FROM EXTSYSFCC.EXLLMQLD T1 JOIN EXTSYSFCC.EXLLMQLD T2 ON t2.LMREF2 = t1.LMREF2 WHERE t1.LMTPC1 = 'ABC' AND t1.LMMSID = '123' AND t1.LMDTM1 = t2.LMDTM1; In the future, Oracle PL SQL related questions should be asked at their community forum, in order to improve your chances for relevant replies. SQL & PL/SQL - Oracle Forums3Views0likes0CommentsRe: Query Help to show record in single row
This should work then. Note that in your sample table, you've inserted 'LED Start' twice. I changed the second to 'LED End', as that is in line with your expected output. DECLARE @tblData TABLE (BName varchar(50), StartDate varchar(10), StartTime varchar(10), EndDate varchar(10), EndTime varchar(10)) INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('STAT Start','02/20/2025','00:34:02') INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('STAT End','02/20/2025','00:40:36') INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('CDS Start','02/20/2025','00:40:38') INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('CDS End','02/20/2025','00:47:26') INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('LED Start','02/20/2025','00:40:42') INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('LED End','02/20/2025','00:54:28') /* Do a self join on the unique part of the Bname */ SELECT st.BName, st.StartDate, st.StartTime, en.StartDate as EndDate, en.StartTime as EndTime FROM @tblData st JOIN @tblData en /* Compares the substring before ' Start' with the substring before ' End', joining STAT Start with STAT End, CDS Start with CDS End etc. */ on SUBSTRING (st.BName, 0, CHARINDEX(' Start', st.Bname, 0)) = SUBSTRING (en.BName, 0, CHARINDEX(' End', en.Bname, 0)) where st.BName like '%start' It's also possible to use a substring statement in the select row to convert the column to just STAT, CDS, LED. /* Do a self join on the unique part of the Bname */ SELECT SUBSTRING (st.BName, 0, CHARINDEX(' Start', st.Bname, 0)) as Name, st.StartDate, st.StartTime, en.StartDate as EndDate, en.StartTime as EndTime FROM @tblData st JOIN @tblData en /* Compares the substring before ' Start' with the substring before ' End', joining STAT Start with STAT End, CDS Start with CDS End etc. */ on SUBSTRING (st.BName, 0, CHARINDEX(' Start', st.Bname, 0)) = SUBSTRING (en.BName, 0, CHARINDEX(' End', en.Bname, 0)) where st.BName like '%start' I will not claim that this is the most efficient way to solve your select, but it works assuming the BName column is unique, i.e. there's a single start and end pr. prefix. If there's duplicates, this will return the cross product of the dupes, matching each possible start row with each possible end row, as shown in this result where CDS has been renamed STAT as well.2Views0likes0CommentsRe: unwanted folder on desktop
Do you actually use your desktop icons? If not, right click the desktop, view, unselect "Show desktop icons". Since I first found that setting, I've always hidden the desktop icons. This may not be an acceptable solution for all users. I am unable to test this following solution at this moment, as I do not have a removable device at hand, but you could also try curating your system desktop icons. Right click desktop Choose "Personalise" Go to "theme" settings Choose "Desktop Icon Settings" under related settings De-select unwanted system icons. I suspect this removable device folder is part of "User's files" Click "OK" Verify if this has removed the offending folder from your desktop, and that you're happy about the current desktop. Alternatively, you could partly follow Nobel_Baynes's solution. Remove your connected removable storage device(s) right click the desktop choose "refresh" This will of course make your removable storage devices unavailable, and may not be the solution for you. Personally, I'd go for hiding desktop icons. All items on your desktop will be available in File Explorer (Win+E), in the quick access "Desktop" folder in the left sidebar.9Views0likes0CommentsRe: Query Help to show record in single row
Your insert statements confound me. Do you have a table that's already formatted that way, and you're having trouble extracting the data the way you want it, or are you struggling with inserting and updating rows to your table? Changing your "End" inserts to updates will at least leave the table in the format you requested. DECLARE @tblData TABLE (BName varchar(50), StartDate varchar(10), StartTime varchar(10), EndDate varchar(10), EndTime varchar(10)) INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('STAT','02/20/2025','00:34:02') UPDATE @tblData set EndDate = '02/20/2025', EndTime= '00:40:36' where BName = 'STAT' INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('CDS','02/20/2025','00:40:38') UPDATE @tblData set EndDate = '02/20/2025', EndTime = '00:47:26' where BName = 'CDS' INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('LED','02/20/2025','00:40:42') UPDATE @tblData set EndDate = '02/20/2025', EndTime = '00:54:28' where BName = 'LED' Select * from @tblData34Views0likes3CommentsRe: SQL Server not supported Windows 11 Home Version
Have you looked at the SQL Server errorlog as suggested? A default SQL Server 2022 installation will have it's error log on the following path: C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG The errorlog is a text file without an extension, and can be opened in notepad.28Views0likes0CommentsRe: SQL Server replication to read only secondary?
I second using read only routing for Always on Availability Groups (AGs), but would mention two caveats from the start: Readable secondary replicas are only supported with "full" AGs, i.e. Enterprise edition. In order to connect to the read only replica, the application needs to include it's read only "application intent" in the connection string, which is an optional parameter defaulting to read/write.35Views1like0CommentsRe: SQL Server account locked out and password did not match?
It's difficult to impossible to tell from SQL Server itself why something external to SQL Server attempted to log in, with the wrong password even. Often, there's a hint in the account name what service is attempting the login, but that is dependant on whether the account was named in such a way when created.38Views0likes0CommentsRe: SQL Server Distributed AG's Forwarder Is Not Syncing After Primary AG's Internal Failover
`ALTER AVAILABILITY GROUP [AG1] FORCE_FAILOVER_ALLOW_DATA_LOSS;` There's your starting issue. Forces failover of the availability group, with possible data loss, to the failover target. The failover target will take over the primary role and recover its copy of each database and bring them online as the new primary databases. On any remaining secondary replicas, every secondary database is suspended until manually resumed. When the former primary replica becomes available, it will switch to the secondary role, and its databases will become suspended secondary databases. ALTER AVAILABILITY GROUP (Transact-SQL) - SQL Server | Microsoft Learn What you most likely wanted to do, was the `ALTER AVAILABILITY GROUP [AG1] FAILOVER;` command. FORCE_FAILOVER_ALLOW_DATA_LOSS is only for situations where the primary is unavailable, automatic failover has not occurred, and the regular FAILOVER command does not work. As for why resuming doesn't work, I'll have to admit I don't personally have sufficient experience with DAGs. I would suggest verifying whether the databases are suspended on the primary as well, and running a MODIFY on your Global primary resetting your modes. Resetting SEEDING_MODE is how you'd restart (or cancel) automatic seeding attempts, for example. ALTER AVAILABILITY GROUP [DAG1] MODIFY AVAILABILITY GROUP ON 'AG1' WITH ( AVAILABILITY_MODE = YourCurrentMode, FAILOVER_MODE = YourCurrentMode, SEEDING_MODE = YourCurrentMode ), 'AG2' WITH ( AVAILABILITY_MODE = YourCurrentMode, FAILOVER_MODE = YourCurrentMode, SEEDING_MODE = YourCurrentMode ); GO11Views0likes1CommentRe: Upgrading from 2012 (SP4) to 2019 including SSRS
Upgrade and migrate Reporting Services - SQL Server Reporting Services (SSRS) | Microsoft Learn Upgrade to Reporting Services 2017 and later from Reporting Services 2016 and older: This upgrade scenario isn't the same as previous versions. When upgrading to Reporting Services 2016 and older versions, you could follow an in-place upgrade process using SQL Server installation media. When upgrading to Reporting Services 2017 and later from Reporting Services 2016 and older, you can't follow the same steps because the new Reporting Services installation is a standalone product. It's no longer part of the SQL Server installation media. To upgrade from Reporting Services 2016 and older versions to Reporting Services 2017 and later, follow the "Migrate a Reporting Services Installation (Native Mode)" article, with Reporting Services 2017 or later as your destination instance. As you can see from the above, there's no in-place upgrade path available for you SSRS installation. Link to Migrate a Reporting Services Installation (Native Mode) - SQL Server Reporting Services (SSRS) | Microsoft Learn19Views0likes0CommentsRe: CDC- change data capture
The security update from September 10. 2024 locked down changes to cdc tables. KB5042215 - Description of the security update for SQL Server 2017 CU31: September 10, 2024 - Microsoft Support Traceflag 15006 seems to disable this lock, but is not documented for this purpose. Using it may leave your instance out of support. Pr. the documentation, CDC metadata objects should not be modified. Known Issues, Limitations, and Errors With CDC - SQL Server | Microsoft Learn Thanks to Paul White on StackExchange. https://dba.stackexchange.com/a/34369046Views0likes0CommentsRe: SQL Server not supported Windows 11 Home Version
If you're intent on installing SQL Server on a client OS such as Win11 Home (or pro for that matter), I'm going to assume you're outfitting a development machine. SQL Server 2022 developer is supported on Win11 Home. As is Express and standard edition if you're running production workloads on your client. SQL Server 2022: Hardware & software requirements - SQL Server | Microsoft Learn153Views1like3CommentsRe: Assistance Required for Downloading SQL Server 2014 ESU Patch for Standalone Installation
Extended security updates are available for your instances running in Azure that are connected to Azure ARC. Outside of that scenario, they're a paid feature. Additionally, there are no ESU's released yet for 2014. See the following article. What are Extended Security Updates? - SQL Server | Microsoft Learn19Views1like1CommentRe: Connect Remote SQL server with SSMS issue
The named pipes thing is generally a misleading part of this error. Remote connections attempt TCP first, then named pipes. If both fail, only Named Pipes show up in the error. You are unable to connect to you SQL Server instance because of a networking issue. Assuming your SQL Server installation is using the default port configuration, you can run Test-NetConnection in powershell. You want "TcpTestSucceded: True" to be part of the output. Test-NetConnection -ComputerName SQLServerHostName -Port 1433 At this point you can get one of several failure modes: Host name resolution failed - You have the wrong name or there's a DNS issue. Talk to your IT department. TcpTestSucceded: False - SQL Server is not listening on TCP 1433 or you've been blocked in a firewall. Talk to your IT department. TcpTestSucceded: True - Try SSMS again, this particular issue should have been resolved. As dalek suggested, you may have to trust the server certificate.68Views0likes0Comments
Groups
Recent Blog Articles
Re: Announcing GA of enhanced patching for SQL Server on Azure VM with Azure Update Manager
TarynPratt wrote: At this point, patching SQL Server on Azure VMs through Azure Update Manager or Automated Patching via the SQL IaaS extension is not aware if the SQL Server is a part of an Alwa...2likes0Comments