Forum Widgets
Latest Discussions
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.
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.
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
Change SQL Login to AD service account for Link server
Hi Everyone, I tried to change account used to impersonate from SQL account to AD service account for link server however im getting below error. The AD service account has sys admin rights on the SQL servers. Access to the remote server is denied because no login-mapping exists. (Framework Microsoft SqlClient Data Provider) Any ideas how to resolve? Regards
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,
problem restoring a backup
I have a backup file (xxxx.bak) in my c:\trmp directory. When I launch SSMS signed on as "sa" and try to restore this database, i get this window: I am not able to enter anything for "Database" and the dropdown is empty. Then if I switch it to "Device" I get this window: Now, under "Destination" I am able to enter a database name, i.e. the name I want the database to be referred to after I restore it. However, note the message at the top of the window: No backupset selected to be restored. How do I resolve this issue? Murray
