Recent Discussions
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 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.
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';
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? RegardsCSV file size from "Save Results As..." versus exported via SSIS Package / Export Wizard
When exporting query results from the grid using "Save Results As...", I'm consistently creating CSV files that are half the size of CSV files created by exporting via the Import/Export Wizard (and the resulting SSIS Package). Exact same query used for both, same results, same number of records etc. I believe this has to do with the file encoding used by each export process. I know this is not a precise science, but based on how Notepad interprets the resulting files, the encoding of the "Save Results As..." files is "UTF-8 with BOM", while the SSIS Package generates a "UTF-16 LE" file. I've tried a variety of approaches to try to get the Import / Export Wizard to mimic the "Save Results As" encoding without success. Changing the Code Page field on the "Choose a Destination" screen to "65001 (UTF-8)" results in the dreaded "DT_NTEXT not supported with ANSI files" error upon export due to nvarchar(max) data types in the source table. That can be resolved by checking the Unicode box on the "Choose a Destination" page, but checking Unicode also disables the Code Page dropdown. Even if you select "65001 (UTF-8)" in Code Page and then check Unicode, it still produces a UTF-16 encoded file. Two questions at this point: 1) How does the "Save Results As..." function in the query grid avoid the DT_NTEXT error and produce a UTF-8 encoded file? 2) If I tried to edit the package in SSIS Designer, would I have more control over the encoding and be able to mimic the "Save Results As..." file sizes? This would take a bit of effort (installing Visual Studio data tools, learning how to use it, etc) so if that's a dead end I'd rather not pursue it.
SQL connector for Azure Key Vault on Linux
Hi everybody, after having successfully configured EKM (to access Azure Key Vault) and encrypted a database with a asymmetric key on a SQL running on a windows VM, i am trying to replicate the same configuration in SQL (2022, CU19) running in a linux container. I cannot find the CRYPTOGRAPHIC PROVIDER (from the documentation it should be built-in), nor create it from file (tried in many ways using the windows dll, i get an error 33027 without additional informations). The guide bellow (see last link) suggest to skip step 3 and 4, and proceed with step 5. But the next steps (create credential, create asymmetric key) requires to refer the cryptographic provider. Can someone supply more information for this step? Thks in adv. Stefano Links: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault?view=sql-server-ver16&tabs=portal) https://techcommunity.microsoft.com/blog/sqlserver/enabling-azure-key-vault-for-sql-server-on-linux/4091248Unable 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, NomaTransaction was deadlocked on lock resources with another process ??
Hi, We have a cognos datastage job that updates a table. It fails with an error that the table is locked., see below ini_PerformanceManagement_tblEmployees,0: ODBC function "SQLExecute" reported: SQLSTATE = 40001: Native Error Code = 1,205: Msg = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (CC_OdbcDBStatement::executeUpdate, file CC_OdbcDBStatement.cpp, line 1,058) Anyone any ideas about how to resolve this? Thanks for your time, OllieSSMS - Display time query was executed
Would you like to see the time that you started a query in SSMS? I do because I normally run multiple queries and need to know when I started the first query, and second, etc. Please upvote my suggestion to display the query start time in SSMS. It's under consideration given the community's demand. Thanks!Sql Server complexity
For someone who has worked in PostgreSQL env for a very long time, learning the ropes of Sql server looks like some task. I practice with SSMS everyday, creating DB objects - schemas, tables, views, indexes, triggers, sequences, users, roles. And have noticed that Sql server code is more verbose, complex and not as straightforward as PostgreSQL. The use of 'stored procedures' for modifying column/table name and 'severity level' and 'state number' parameters for triggers was really topsy-turvy. I guess, it will take some time before I get used to the 'proprietary' way of doing things.Sql Query
Hi All, I have a requirement. want output data like below screen print CREATE TABLE [dbo].[Source_Table]( [Product] [varchar](10) NULL, [Trans_type] [varchar](10) NULL, [SalesCode] [varchar](10) NULL, [Profitamount] [int] NULL ) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product1', N'Type1', N'A', 7) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product2', N'Type1', N'B', 3) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product1', N'Type2', N'C', 4) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product2', N'Type2', N'A', 5) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product1', N'Type1', N'D', 9) GO Thanks in Advance...
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...Stored Procedure runs long the first run, then runs faster
I have a Stored Procedure that does the following: Creates 2 Temp Tables Inserts into first Temp Table Inserts into second Temp Table from 20 Tables, plus joining to the first Temp Table and has 10 subqueries. Inserts into second Temp Table from 18 Tables. Inserts into second Temp Table from 14 Tables. The first time the SP is run for the day, or after it has not run for a few hours, it sure 40+ seconds. The runs after that come in between 7 – 20 seconds. The SP takes three parameters which can change, but for testing we are using the same three parameters. The SP is in cache before the first run. I have checked all the Indexes are in place and not overly fragmented. I do not see any CPU, Memory, or Drive issues. I have gone through the Execution Plan more times that I can count. I do not understand why the first run takes long, but the following runs are always short. I did see this in another post: "A result set is never cached in SQL Server, however, the underlying data pages containing the result rows are. All this means is that the response time for subsequent queries may be lower since the underlying data is re-read from memory, with only any variations read from storage." If that is the issue, is there a way to "fix it"? Any ideas?
Recent Blogs
- We are happy to announce that, you can now use Managed Identity to authenticate to Azure Key Vault from SQL Server running on Azure VM (Linux) available from SQL Server 2022 CU18 onwards. This blo...Jul 28, 2025
- The 20th cumulative update release for SQL Server 2022 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative up...Jul 10, 2025
