Here's a few of the new posts today on the forums. To see more, visit the forums.
|
| SQL Server 2017 - Development |
| How to extract text after a given string to look for. - I am a forever learning new DBA. I have been handed a project I have no idea how to complete. I have a table that has a column called settings. In that column there is a massive string of data. Within that string of data I must extract out a single setting. The text I […] |
| SQL Server 2016 - Development and T-SQL |
| Adding new column to GROUP BY vs adding MIN or MAX of that column - I have a straightforward report, which performs some calculation, e.g. SELECT myCustomerId, customerProductId, SUM(totalSpentByCustomerForProduct) AS totalPerProductPerCustomer FROM customersTable JOIN ordersTable ON ... JOIN productsTable ON ... GROUP BY myCustomerId, customerProductId Now I need to extend it, providing both customer and product names. Normally I would implement it by adding MIN (customer_name), MIN (product_name) to the […] |
| Python call of a Stored Procedure with send_dbmail does not work - Hello, I have a python script (version 3.8) which then calls the Stored procedure in a database (with all the required params: databasename, user,pwd) The SP has an update within Begin/End trans. If the Update fails then I have an EXECUTE [msdb].[dbo].[sp_send_dbmail] to send me an alert email. That doesn't work from the Python […] |
| Administration - SQL Server 2014 |
| How to find lock scope from DMVs? - Hi all, I am testing my data purging (deleting) procedure, and while it's running I monitor sys.dm_tran_locks. It shows everything for every lock, but not a scope (like row or page). Or maybe I should check in another DMV? Thanks |
| Trying to create key - Hi I had to drop a constraint to run an update. The drop was fine but when I run the create statement: ALTER TABLE [dbo].[PEC_Claim_Supp] WITH CHECK ADD CONSTRAINT [FK_claim_PEC_Claim_Supp] FOREIGN KEY([claim_id]) REFERENCES [dbo].[claim] ([claim_id]) GO ALTER TABLE [dbo].[PEC_Claim_Supp] CHECK CONSTRAINT [FK_claim_PEC_Claim_Supp] GO I get the error: Msg 1776, Level 16, State 0, Line […] |
| Development - SQL Server 2014 |
| How to delete in small chunks - Hi All, We have a large log table which consists of last 10 year data. When we are running a delete on such big table which has some LOB columns as well. So as a result, we are facing 9002 log full error. So, want to come up a purge script which can delete in […] |
| Query is not giving me expected output. - Below is query with data,which is not giving me expected output. DECLARE @StartDate date = '03-06-2020'; DECLARE @enddate date = '06-06-2020'; Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),Packsize varchar(50)) Create table #Bigbalprd (B_ID int,Codeitem int,Bpqty int,Bweight int,Entrydate date,delid int) Create table #DispatchBM (DID int,Name varchar(50),Date date,Del int) Create table #Dispatch_BD (ID int ,BID int,DID int,Codeitem int,QTY […] |
| SQL Server 2019 - Administration |
| What TLS version is used: how? - Hello, Is there a way to find the TLS-version used in connections on SQL Server 2019? I'm getting The event name, "sqlsni.trace", is invalid, or the object could not be found when I try to add extended event sqlsni.trace CREATE EVENT SESSION [tls] ON SERVER ADD EVENT sqlsni.trace( WHERE ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%'))) https://www.sqltact.com/2018/01/sql-server-on-tls-12-xevent-session-to.html |
| SQL Server 2019 - Development |
| function output - Hi Expert, I am troubleshooting this function and unable to understand the actual output from it . could you please explain it SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION Create funciton dbo.test1 ( @List nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(100) ) AS BEGIN While (Charindex(@SplitOn,@List)>0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End […] |
| T-SQL (SS2K8) |
| Count consecutive number of years of orders - Hi I like to get the number of consecutive years a customer have placed ordres starting from current year or last year Input 2015 2016 2019 2020 2021 Result = 3 I hope someone can help with this. |
| SQL Azure - Administration |
| Looking for info on azure active directory authentication - We have an Azure DB in place and now need to make it accessible to a number of on prem AD users. I'm told there is a way to do this via azure active directory authentication. Any recommendations on articles on how to do this? Everything I've found is about creating the azure active directory […] |
| Integration Services |
| OLE DB Source Dynamic Source Name - Hi All, Using VS 2017 I have a for each loop using a variable @currentfile to pass the file name of the excel file which changes every time it runs and the excel headers start in row 3 and column B to BO. I've tried using a excel source with the SQL command SELECT * […] |
| OLE DB Source Dynamic Source Name - Hi All, Using VS 2017 I have a for each loop using a variable @currentfile to pass the file name of the excel file which changes every time it runs and the excel headers start in row 3 and column B to BO. I've tried using a excel source with the SQL command SELECT * […] |
| Flat File Connection Manager issue - All, Firstly apologises as I think I've missed the obvious. I've been looking at this for a while and also tried searching but not found an answer. This is the XML for a Flat File Connection Manager: @[$Package::FilePath] + "\\Location.csv" |
| Disaster Recovery |
| SQL Server Failover Cluster - First sorry for my English, I am French speaking I've searched and did not found any information on how to do this. Let me explain (see file attached) I know how to setup an SQL failover cluster, did it and it works perfectly. Here is my situation : Node1 and Storage A is in […] |
