Recent Discussions
Table
Hello everyone, I have a problem. Our products are dimension-dependent (width / height) and the prices are stored in a table [Costinformation]. The grid table is in the Pricetable column. Rows are separated with CRLF and the individual columns with Tab. Table [Costinformation] Mandant Code Text Category Pricetable 1 4711 Product Table 1 Window 1 4712 Product Table 2 Door Example Pricetable-Column W / H 100 200 300 400 100 10,00 20,00 30,00 40,00 200 20,00 30,00 40,00 50,00 300 30,00 40,00 50,00 60,00 400 40,00 50,00 60,00 70,00 Now it is so that there are always price adjustments. In 100% of cases, this is in percentage terms. This means that the prices in the grid table have to be extrapolated. I need a solution so that I no longer have to do this manually. Can it be done directly via an SQL statement? Example: Product Table 1 --> price + 5% Produkt Table 2 --> price + 3 % I hope it is understandable. My English is not so good. I can also provide an example table. Thank you very much for you help. Greetings from Germany CharlyStella
Parameterize OLE DB Destination?
I have a large number of tables to backfill and I was hoping to automate this process with a package parameter instead of creating a dataflow for each table (see screenshot below). Package parameter like 'tbl_a, tbl_b, tbl_c' Followed by an Execute Script that splits this string into an Array. The array would then be enumated in a Foreach loop container. 3a. SQL command from enumerated variable would be fed into an OLE DB Source SQL Command 'Select * from tbl_a', 'Select * from tbl_b', etc. 3b. Table name or view name variable in OLE Destination would be given different table names 'tbl_a' , 'tbl_bl', tbl_c' through the enumerated variables Where it fails is when tbl_a, tbl_b is enumarated into OLE DB Destination Error: 0xC020201B at Data Flow Task, OLE DB Destination [2]: The number of input columns for OLE DB Destination.Inputs[OLE DB Destination Input] cannot be zero. Error: 0xC004706B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination" failed validation and returned validation status "VS_ISBROKEN". Is there a way to parameterize OLE DB Destination? Is there a way to avoid need column mappings or somehow query all the columns from the OLE DB Source to feed into OLE DB Destination? Each table is different and mapping the columns for one backfill would completely blow up the next backfill in the batch. Is there another Task I can use in place of OLE DB Destination that can be preceded by OLE DB Source? Thanks in advance
Current SQL Server Certifications
Hello, I had a couple Microsoft certifications many years ago and am interested in getting re-certified in SQL Server. I looked on learning.microsoft.com but couldn't find any certification options. Am I looking in the right place? Does Microsoft still offer SQL Server certifications? Thanks, Brett
Creating LinkedServer between On-Prem SQL to Azure SQL MI
Hi, is it possible to use security option (Be made using the login's current security context )with lnikedserver from on-prem SQL to MI ? Our MI is enabled for windows auth and we are successfully connect MI in SSMS using windows auth from on-prem client. when I use this option in linkedserver and try to access linked server with windows auth I get following error. Login failed for user '<token-identified principal>'. Reason: Could not find a user matching the name providedTwo missing record from this query
I have the following query: 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 WHERE t1.LMDTM1 = ( SELECT MAX(t2.LMDTM1) FROM EXTSYSFCC.EXLLMQLD t2 WHERE t2.LMREF2 = t1.LMREF2 ) AND LMTPC1 = 'ABC' AND LMMSID = '123' For some unknown reason to me I detected at least two records that are not included, the date field is 20250217 and the part where it skips those two records, there is at least one record with the same date, is this: t1.LMDTM1 = ( SELECT MAX(t2.LMDTM1) FROM EXTSYSFCC.EXLLMQLD t2 WHERE t2.LMREF2 = t1.LMREF2 ) If I delete that part I can see those two records. Any help will be deeply appreciated. Thanks!
How do I unpivot so that the value column comes at the end?
hello all, I have learning the unpivot function, according to the documentation, the clause before the FOR keyword comes at the end. But when I do it, the column before the FOR keyboard comes before the column after the FOR keyboard. please see this example I did How can I make the value column to be the final column like the documentation? Thanks you.Error converting data type varchar to numeric when running insert query
Hi , I am getting below error when trying to run insert statement even though data types are same in target table and source view Error details : Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric. Insert Query INSERT INTO [Data].[Noti_Window] SELECT CONVERT(VARCHAR(16), SYSDATETIME()) AS 'RunTime', EDW.* --INTO [Data].[Noti_Window] FROM ( SELECT * FROM [Data].[Noti_EDW_PAN_TempViewIII] UNION SELECT * FROM [Data].[Noti_EDW_DOD_TempViewIII] ) AS EDW INNER JOIN [Data].[Noti_Dim_PageID] AS PgID ON EDW.PageID = PgID.PageID ; Noti Window table structure. PAN and DOD View Structure. Could you please let me know how to find out which column is causing this issue bco'z from last 1 year working fine without any issues. Thanks in Advance for your great support. Regards, BhaskarQuery Help to show record in single row
Hi All, need help with an SQL query. Currently we are getting Start and End BNames on separate rows. 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 Start','02/20/2025','00:54:28') Current Output: EXPECTED OUTPUT: The EndDate and EndTime I want it displayed in the same row where BName like '% Start' I tried to do this through cursor by updating the EndDate and EndTime, but that is not working. Any help will be appreciated. Thanks!
Having difficulties to paste data to excel ?
Hi, I need to provide data in excel format. One of the field is having varchar - I am using CAST(AP.IDINVC AS VARCHAR) as [INVOICE_ID] But I am missing the leading zero's when I copy the data from MS SQL Server Result to excel file. No of rows is 147474. How to keep the leading zeros when copying the Invoice ID from MS SQL Server result to Excel.SQL 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!
SQL Server not supported Windows 11 Home Version
I bought a new computer that came with Windows 11 Home. I am unable to install SQL Server 22 on W11. I would like to install Windows 10 that does support SQL server but I will void my support warranty doing so. I suspect I can upgrade to Windows 11 Pro but I am not sure that OS supports SQL Server. Any thoughts or advice would be helpful.
SQL Server account locked out and password did not match?
Hi We have this situation that a SQL Server account has been having two different login failures recently. The errors appear in the SQL Server log without much details. Wonder if any one had encountered the same situation and if it had been resolved? The two different error messages are: 1. Login failed for user 'sql_account_name'.Reason: The account is currently locked out. The system administrator can unlock it. [CLIENT: ip_of_another_computer] 2. Login failed for user 'sql_account_name'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>] Note: I replaced the actual account name and ip with sql_account_name and ip_of_another_computer respectively. I found a query that displays the user account SQL agent jobs run under, but I did not see this SQL account. Also, I checked linked servers, but did not see this account. Furthermore, I checked Windows services of the two computers in the error messages, but I did not see that SQL account. I did look Windows task scheduler, but do not think we can use SQL account as run under for a task. Any idea or insight? ThanksSQL Server replication to read only secondary?
Hi, I currently have a SQL 2019 instance with a database that some application writes data to, this data is later read (only read) by other applications. The plan is to split the readers from the writers, the goal is to have a solution where I have the editable master database where applications can actually change the data more secured on one instance and a replica/copy of the same database on another instance where other applications can read the data but not change anything. Networkwise its preffered that any communication is initiated from the R/W instance to the R/O instance but not the other way around. So even if the "read only" copy somehow is compromised or lost I always have the master r/w database available to just re-sync. I need suggestions on the best way or method to accomplish this. Both db's have to be accessible at the same time.Need Help To Get Last Record Based On Column Value
Hi , Below is the sample data, and I need to get the latest record based on last_modified_date, and [active/inactive] columns. ID, Name are the key columns and based on last modified date i need to get the last record If Any of these key columns have 'Active' status, irrespective of last modified date, pull that record else take the latest record of inactive record. the Ideal output should be: 111, AAA, Active,2024-04-21 (Since it has status of active on earlier dates)Need help SQL query performance issue
I am looking to help optimize the below select statement I have created supporting indexes and updated statistics , the same query is taking 2sec in one database ( note that same table structure and same data on both tables ) but another database it's taking 95sec on the same server. I compared the execution plan for both 2sec vs 95sec it's exactly matching . Any clue on why it's running faster on one database but taking lot of time on other database. These tables not heavily fragmented and stats are up to date on both database. These tables contains 200million rows. Another important note is that indexes is not playing any role here , with or with out indexes query is taking 95sec declare @v_vch_cut_off_date date =cast( getdate()-10 as date) Select ztr.work_day,tlms.work_day, ztr.from_zone,zl.travel_zone_name, ztr.from_business_unit,tlms.business_unit, ztr.from_process,tlms.process, ztr.from_item_category,tlms.item_category FROM dbo.t_zone_travel_report(nolock) ztr LEFT JOIN dbo.t_lms_process_time (NOLOCK) tlms ON ztr.wh_id=tlms.wh_id AND ztr.employee_id=tlms.employee_id AND ztr.start_tran_datetime >= tlms.start_tran_datetime AND ztr.start_tran_datetime < tlms.next_tran_startdatetime -- AND ztr.log_id >= tlms.log_id AND (ztr.log_id < tlms.next_log_id OR tlms.next_log_id=999999999) AND tlms.work_day >= @v_vch_cut_off_date LEFT JOIN dbo.t_travel_zones_locations (NOLOCK) zl ON ztr.wh_id=zl.wh_id AND ztr.from_location=zl.location_id WHERE (ztr.work_day >= @v_vch_cut_off_date OR ISNULL(ztr.work_day,'') = '' ) AND ( ISNULL(ztr.from_zone,'') <> ISNULL(zl.travel_zone_name,'') OR ISNULL(ztr.from_business_unit,'') <> ISNULL(tlms.business_unit,'') OR ISNULL(ztr.from_process,'') <> ISNULL(tlms.process,'') );
Recent Blogs
- Happy almost-Spring (for any Southern Hemisphere dwellers: happy almost-Fall)! We are excited to announce the latest preview of SSMS 21: Preview 5. If you already have SSMS 21 Preview installed, you ...Mar 11, 2025
- 2 MIN READWe’re thrilled to announce the alpha release of our new open-source Python driver for Microsoft SQL Server and the Azure SQL family, now available on GitHub: mssql-python. Built from the ground up,...Mar 11, 2025
