business intelligence
42 TopicsSQL Server issue but don't know what - please help!
Hi, I'm facing a SQL Server focused issue that I don't understand why it's occurring and would like your help to identify and resolve, please. I will provide an in-depth breakdown of the scenario. Two years ago, I created a Azure Data Factory (ADF) Pipeline to take data from Azure Synapse to Azure SQL Server, and two other Pipelines to take data from an On-Premises Sage server to the same Azure SQL Server. These Pipelines were working perfectly up until two days ago (11/03/26) when the Pipelines still always complete successfully but the duration have increased greatly. The below screenshot shows the log of these three Pipeline for the past week. Note how prior to the 12/03/26 the Synapse Pipeline took about 6 minutes to complete and the two Sage Pipelines took around 25 seconds. Also note how on the 12/03/26 the Synapse Pipeline took over 3 hours but the two Sage Pipelines continued with their normal 25 seconds. Notice today (13/03/26) the Synapse Pipeline was still slow but now the two Sage Pipelines are taking over an hour. I'll note here that the Pipelines still complete successfully (so the config must be correct as it has been for two years). Each Pipeline contains a single (for Sage) or several (for Synapse) 'Copy data' objects. The objects have their source configuration to simply extract from the source (so either Synapse or Sage) and then the sink configuration has a 'Pre-Copy script' which simply Truncates the target SQL Server table, before loading to the SQL Server table. The screenshot below is one example - each Pre-Copy script is populated the same but just with the tables being different. When I look at the log for the Pipelines, I see a common theme for each and that is the 'Pre-Copy script' (the Truncate) is consuming 99% of the time. The screenshot below shows this common theme. So at this point, I ask the question why, after two years of all working well and completing very quickly, are the Pipelines taking so long to complete? This also seems to be an intermittent problem as I have performed several manual executions which will take a long while, then revert back to the quick several minutes again (so good), then revert once more to taking a long while again. It is intermittent. See the screenshot below. Notice how the same Pipeline have different durations. It's intermittent. The reason why I think it is a SQL Server focused issue for the following reasons: 1) Synapse - Performing simple commands (Select Top) returns data within seconds. 2) SQL Server - Performing simple commands (Select Top) usually takes seconds but during the past two days is often taking half hour. 3) SQL Server - When amending a SQL View it would take a split second to complete. During the past two days, it goes over ten minutes and still doesn't complete (I was amending the View for testing purposes). 4) SQL Server - When looking at the Views node under a database, during the past two days, it intermittently doesn't show the View. Sometimes, it will work if I log out and then back into SQL Server. 5) Power BI - Refreshing a Power BI report, whether from the Power BI Service or Desktop, call SQL Server Views. These SQL Server Views read from several SQL Server tables. These report refreshes are failing due to an IDbCommand interface error. These Power BI report data refreshes simply read from a SQL Server table via a SQL View. They don't reference or consider at all the any ADF Pipelines. The Pipelines execute between 2am and 3am each morning. The Power BI report refresh their data around 12pm to 5pm. As SQL Server seems to be problematic at the point of the 'Pre-Copy script', which is one of the end to end process, and then problematic at the very other end where Power BI reports consumes SQL Views - leads me to believe the issue is with SQL Server. I am a report developer and not an ADF expert. I've built the ADF process on the side. I haven't changed any development/configuration/etc... between this all working and failing. Our I.T. department have advised they have made no changes to contribute to this issue. Please can someone advise on what's happening here and why this issue has arose when for two years all was fine? Thanks.35Views0likes0CommentsSQL Timestamp Incremental Load Issue
An incremental load is implemented based on timestamp, using a view to update a fact table. • The fact table contains multiple KPIs coming from different source tables. • Two main KPI tables are included directly in the view and drive the incremental logic (their timestamps change). • Additional KPIs come from other source tables, which are LEFT JOINed to the view. During an incremental run: • Main source tables are updated → timestamp changes • Other source tables are NOT updated → timestamp unchanged • Because of the LEFT JOIN, missing values from these tables are returned as NULL • These NULLs overwrite existing values in the fact table Example Fact table (before load) id app_number score 1 333 5 Source tables • source1, source2 → timestamps updated (drive incremental load) • source3 → timestamp unchanged Stored procedure logic MERGE fact_table tgt USING ( SELECT app_number, ISNULL(score, 0) AS score FROM vw_main_kpis v LEFT JOIN source3 s3 ON v.app_number = s3.app_number ) src ON tgt.app_number = src.app_number WHEN MATCHED THEN UPDATE SET tgt.score = src.score; Result (incorrect) id app_number score 1 333 0 Existing data is lost.206Views0likes2Comments“8152 String or binary data would be truncated” error while running select query on a view
I have a complex view (it’s organisational so i cannot paste it here) that joins multiple tables, uses CTEs, performs logical calculations and then provides for multiple columns over which we can select. This view is based on top of multiple master tables and a transactional table. It was performing fine until today morning. But then it started throwing 8152 error. I’m assuming it’s started happening only after certain values got written to the transactional table. The funny thing is, that the view is still executing fine if I remove just one column from the select query. If i include that one column in the select query, it throws 8152 error. I spent my entire day trying to troubleshoot, but couldn’t. Unable to understand how the view is running fine but the including a column in the select query causes it to malfunction. Any insights would be much appreciated.Solved241Views0likes1CommentSQL Server 2017 – CLR was loaded in an unsupported manner (All SSIS jobs failed)
Hi, We are facing a critical issue in our SQL Server 2017 instance. When trying to use a built-in CLR function or running SSIS-related jobs, we are getting the below error: The Common Language Runtime (CLR) was loaded in an unsupported manner. This can occur if an extended stored procedure or OLE Automation object running in SQL Server calls into managed code before the CLR integration runtime host loads the CLR. You need to restart SQL Server to use CLR integration features. Steps tried so far: Restarted SQL Server service Restarted the entire Windows Server Verified .NET Framework version (4.7.03062 installed) Confirmed CLR integration is enabled (sp_configure 'clr enabled', 1) All SSIS jobs are failing due to this issue. Any suggestions, please?158Views0likes0CommentsPet project on SQL Server 2022 platform
Hello, world! I would like to share my pet project on SQL Server 2022 platform. I have created a DWH solution that includes many MS's best practices and interesting features such us: ETL process with data cleansing and MDM that easy expand Documentation CI/CD Functional ETL test Ready analytical templates Time intelligence New & returning customers Cluster customers based on spending volume Product ABC classification Basket analysis Events in progress https://dev.azure.com/zinykov/NorthwindBI Unfortunately in SQL Server 2025 will be no DQS & MDS...88Views0likes0CommentsSQL Server Config Manager Error "MMC could not create the snap-in"
Hi, I have seen this error elsewhere online. I have gone to mmc to enable the snap in and I still have had no fix. My computer is running Windows Server 2022, SQL Server Express 2022, and SSMS. I have reinstalled, repaired, and all of the other tricks. Help!Solved5.1KViews0likes4CommentsSQL Query
Hi All, I have 2 tables ConfigurationTable and Data table. I require combine value like below output Source tables: Target Output: Sql Scripts: CREATE TABLE CONFIGTABLE(Productcode VARCHAR(10), Linkedvalue VARCHAR(10)) INSERT INTO CONFIGTABLE VALUES ('A', 'PEN') ,('C', 'PENCIL') ,('B', 'BOOK') ,('M', 'MOUSE') CREATE TABLE DATATABLE (FIELDVALUE VARCHAR(50), ORDERID INT,NAME VARCHAR(20)) INSERT INTO DATATABLE VALUES ('321', 9, 'COMPUTER') ,('THIS PEN IS', 1, 'A') ,('Country', 5, 'BOOK') ,('Great village in a', 4, 'B') ,('MINE', 2, 'PEN')Solved230Views0likes2CommentsOpen reportserver webservice URL with https leads to an ERR_HTTP2_PROTOCOL_ERROR
We have installed on a Windows Server 2022 the latest version of PowerBI Reporting Server. We have bound a wild card certificate to the PBRS webservice and PBRS portal site. We have also added the FQDN cname to the PBRS reportingserver config file. We have used to deploy any reports by URL's like https://reports.contoso.com/ReportServer/Pages/ReportViewer.aspx?/Invoice/InvoiceReport. Opening with Microsoft Edge we get the error: Hmmm… can't reach this page It looks like the webpage at https://reports.contoso.com/ReportServer/Pages/ReportViewer.aspx?/Invoice/InvoiceReport might be having issues or it may have moved permanently to a new web address. ERR_HTTP2_PROTOCOL_ERROR On the other hand when opening the URL https://reports.contoso.com/Reports/Invoice/InvoiceReport we will be asked to login with the user credentials. Afterwards calling the webservice runs fine. Does anybody had already the same error and how we can avoid it?7.8KViews3likes11Commentsduplicate 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!Solved175Views0likes2Comments