Forum Widgets
Latest Discussions
SQL Server failing to execute extremely complex queries
A query is failing with this error message on a customer environment: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. Sorry I cannot post the actual SQL query, which must be massive, but basically it is a union of a lot of tables. Maybe around 600 tables, maybe several thousands, I don't know exactly how many. For technical reasons, we cannot really simplify this query. In the documentation: https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16 It is specified that the maximum number of tables in a select is limited only by available resources. Is it possible to increase the size of available internal resources somehow?sql server 2019 how to reverse engineer a View using VS or Visio
Hi, I am trying to trace back, and document the lineage of a series of Views that have been created in SQL Server 2019 over many years. Many of the views are quite complex and are Views built or several other Views, Tables and functions. I need to unpick all of the dependencies and logic that has been used in creating these views. I tried to use both Visio and Visual Studios 'Reverse Engineer' tools to do this, but this is not supported for SQL Server 2019 or later. When I connect my database to Visio and select the server and have the connection authenticated, the dialogue box greys out the Views checkbox. I have been told that Visio does not support reverse engineering for SQL Server 2019 or newer. The correct ODBC driver is installed, and I am working with a supported version of Visio (Visio Plan 2) and Visio 2505. Is anyone aware of a workaround to this, and how I might use either Visio and VS to reverse engineer my 50+ views and find all their dependencies and calculations, outputting these in a diagrams that I can give to the engineers to easily understand and unpick? Otherwise, this will take me weeks to do. My company is not keen on using any Third party tools that we would need to install on the server, as these could cause a security issue, but any suggestions of anything that would be light touch would be most welcome. Any help would be much appreciated. Thanks!SQL server 2016 with Integration services on Ubuntu
Hi everyone, I need to setup SQL server 2016 on Ubuntu with Integration Services. Is it feasible to install SSIS with SQL server 2016 on Ubuntu for Prod use? If i go to Microsoft documentation,they only showing SQL 2017 and 2019 for Linux. Can anyone help with any related resources for clarification w.r.t SQL 2016? Thanks in advance Prakash
Sql Server Credential Manager
I am trying to add a new domain login to Credential Manager in Windows 11. I have no problem adding a Sql Server credential such as myserver.internal.company.com But if I try adding a named server such as myserver\instancename.internal.company.com I cannot add it. I have also tried 192.168.0.1\instancename, still cannot add. How do I add a named instance int Credential Manager? I cannot seem to find any help on this via a web search. I can use runas netonly, no problem, but I would like to use Credential Manager. Thank you for any help.
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.
Reading a non-rounded value from Excel cells using OPENDATASOURCE
Reading a non-rounded value from Excel cells using OPENDATASOURCE I have the following situation: I have an Excel document that contains in a cell a value 10,45. Format of the cell is “# ##0,0;-# ##0,0”. Therefore, the value 10,45 is displayed in Excel as rounded value “10,5” – because of the format allows only one digit in the decimal part. I have the following T-SQL code that reads the Excel: CREATE TABLE #from_excel ( rate decimal(20, 10) ) INSERT into #from_excel (rate) SELECT REPLACE(F1, N',', N'.') FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=TheExcelPathIsHere ;Extended Properties="EXCEL 12.0;HDR=No;IMEX=1"')...['NameOfTheSheetInTheExcelFile$'] This way the value 10,45 from the file will be put into #from_excel.rate as value 10,5. Is it possible somehow (not changing the format of the cell, this is important) have a query that reads the non-rounded value; i.e. to have 10,45 in the #fromexcel.rate after the reading.
Trigger is hanging up the system.
Hi, I need to send out a database email when the status field is <> '0' for a newly inserted record. I have a trigger that works elsewhere, but is hanging up the system at this location. When I say "hanging up the system" I am referring to the database not populating. The database will populate after the trigger is disabled and another insert occurs. Below is the code for the trigger. I have checked and the test database email went out and was received. I have also successfully sent out and received an email from a query using just the Line EXEC msdb.dbo.sp-send-dbmail and the lines that follow from below. It is something with the trigger is all I can come up with. I would greatly appreciate any input. USE [AK_Mid_TV] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[TV Front Image Alarm Alerts] ON [AK_Mid_TV].[dbo].[TV Data] FOR INSERT AS SET NOCOUNT ON; DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<h1>TV FRONT ALARM ALERT</H1>' + N'<table border = "1">' + N'<tr><th>Car ID</th><th>Image Time</th>' + N'<th>Front Alarm Level</th><th>Front Alarm Temp</th><th>Direction</th>' + CAST ( ( SELECT td = dbo.[TV Data].[Car ID], ' ', td = dbo.[TV Data].[Image Time], ' ', [td/@align] = 'center', td = dbo.[TV Data].[Front Image Alarm Status], ' ', [td/@align] = 'center', td = format(dbo.[TV Data].[Front Temp F], '#,#'), ' ', [td/@align] = 'center', td = dbo.[TV Data].[Direction Label] FROM dbo.[TV Data] where [Image Time] in (SELECT MAX([Image Time]) from dbo.[TV Data]) and [Front Image Alarm Status] <> '0' FOR XML PATH ('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>'; If @tableHTML <> ' ' EXEC msdb.dbo.sp_send_dbmail profile_name = 'Alarm emails', @recipients ='email address removed for privacy reasons', @copy_recipients = 'email address removed for privacy reasons', @subject = 'TV Alarm Alert', Body = @tableHtml, Body_format = 'HTML';Unable to drop a user on SSISDB
Hi, I am unable to drop the user from the SSISDB, I am getting the below error, has anyone experienced the same issue? "The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped. The transaction ended in the trigger. The batch has been aborted. (Framework Microsoft SqlClient Data Provider)" Regards, Noma
