SQL 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.SSRS subscription
Hi all, I hv a question about SSRS subscription under mssql 2022 STD, I saw when i subscript to a report, it will create a job under SSMS jobs. I would like to manage the report subscription not run on the specified date store in some table in the database. Is it possible to run another job to use sp_update_job to disable the job when date is find in the table, and enable the job when the date is not find in the table. Because the SSRS's job contain a remarks that "modify this job will make database incompatible", im not sure is it work. thanks in advance.
SSRS 2022 not able to connect SQL Server 2022
Hi, on SQL Reporting Service Configuration Manager 2022, when i try to connect SQL 2022 instance, its enforcing encryption. However, during the SQL 2022 i just installed by default and haven't configured any SSL certificate or activated encryption. Moreover, MSSQLSERVER service account also default account which comes with installer. How can i disable or bypass this issue in order to configure SQL Reporting Service ? Regards,
Pet 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...
Open 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?Upgrading from 2012 (SP4) to 2019 including SSRS
Hi, I have done a fair bit of googling/reading and found conflicting paths/advice so am looking for a definitive guide. Step by step would be ideal if possible. One of our servers is currently running SQL 2012 and we need to upgrade to 2019. The instance also includes SSRS and the report server is on the same server. With approx. 100+ reports in use. I've read that SSRS does not upgrade when you upgrade the SQL version as it is now a stand alone program (true?) So basically I need to upgrade (in place) SQL Server (easy) but then do what ever I need to do to get SSRS to 2019 and obviously have the report definitions (RDLs) recognise their new environment and upgrade themselves in the process. I imagine the schema path at the top of the RDL would need changing at the very least. We also have scheduled subscriptions that we need to retain.... and of course datasources that point to the relevant DBs (not sure if these would be affected as part of any upgrade/install of SSRS). Would be grateful for any legitimate sources that deal with this scenario and I thank you in advance.Moving SQL Reporting Services Native Mode
I am trying to move a SQL Reporting Services database from a 2016 server to 2022. No problem, done this many times before. But on this one the source server in Reporting Services Configuration manager shows as Native, but once restored on the new server. When I run Reporting Services Configuration manager it pops up as Sharepoint. Anyway to set it back to native mode? Please advise. Thanks! JGERROR 4819
SQL Server: 2019 ERROR (4819) -" Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violates a uniqueness constraint imposed by the target table. Sort order incorrect for the follow" We tested the exact same stored procedures on SQL2019 System A and everything worked as expected. But it gives errors when we test on the SQL SERVER 2019 System B. SQL Server A and SQL Server B are the same version, 2019 but something is causing these jobs to fail. I’m not sure what to do next. Any thoughts/suggestions would be greatly appreciated. Thanks.
