Forum Widgets
Latest Discussions
Counting distinct values
I’m trying to create a view but I’m struggling to find a solution to this issue. I need a method that counts multiple visits to the same location as one if they occur within 14 days of each other. If there are multiple visits to the same location and the gap between them is more than 14 days, then each should be counted separately. For example, in the attached screenshot: Brussels had visits on 08/05 and 15/05, which are less than 14 days apart, so this should be counted as one visit. Dublin had visits more than a month apart, so these should be counted as two separate visits. Could someone please guide me on how to achieve this? Thanks.
Switch Azure SQL Provisioned to Serverless and back to Provisioned
Hi Can we switch Azure SQL from "Provisioned" to "Serverless" and back to "Provisioned" without any interruptions? Wanted to observe how serverless works for a week. Data should never be lost while switching the mode (NOT a transactional data). Its more of nightly refresh and nothing much happening during day time. Its a retail dataWH. Currently im connecting to the below Azure SQL which is a provisioned instance. We are scaling up or down per our needs / work load and paying $X. sql-xxxx-xxx-xxx-dev.database.windows.net Nightly process runs at higher cores and then scales back to the lowest 2 core. When someone queries tables, joins during day time, it takes 10-30 mins or so depending on the table size. I'm wondering if i switch to serverless i would save money even if billed by seconds since nightly process runs 1hr and not much of querying every day during day time. i would like to test that.
Azure SQL server rollback itself?
We have an Azure SQL server. It is a datasource of a Power App canvas app. Today I connected to it with SSMS v19. First, I ran 'Begin tran' twice (is it a mistake?). Then 'Delete From dbo.table1 where ID=30' and another row with ID=31. Then I verified these 2 rows are deleted by 'Select * from dbo.table1' Finally, I ran 'Commit tran' I verified again above 2 rows are deleted by 'Select * from dbo.table1' However, there is no change in the Power App. So I reopen the SSMS and connect to the DB again. This time when I ran 'Select * from dbo.table1', the 2 rows are showing up. What could be the problem? Is it a bug in old version SSMS?
Azure SQL Firewall / Locks
Hi there, I have 2 environments. I'm more of admin on Azure environment (recently made as subscription admin) after which Dev issue - Azure SQL I'm having difficulty to remove IP from Azure SQL Firewall. (Earlier i was able to) today my manager granted me subscription admin and as SQL Security Manager and it still not able to remove grayed out IPs.Better way to check database for dups
I have vba in Excel that incorporates SQL to iterate through all the records in a sheet and checks the databsase for that record using a combination of two fields to make a unique id. If it's in the database then it's deleted from the sheet. Otherwise it goes to the next one. Easy enough. I've had this code for years and it's always been fine but now there are too many records to do it this way. It takes too long. What would be a better way to query the SQL database to return those records that are dups and then delete them from the sheet. Here's an idea of the snippet of code I currently use: 'i is the record number from bottom to top For i = DataSheet.Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1 CountyName = DataSheet.Cells(i, 1).Value CaseNumber = DataSheet.Cells(i, 3).Value strSQL = "SELECT County, Case_Number FROM tblAdLetter " & _ "WHERE County = '" & CountyName & "' AND Case_Number = '" & CaseNumber & "'" rs.Open strSQL, cn If Not rs.EOF Then DataSheet.Rows(i).Delete End If rs.Close Next i Is there a better way or faster way? todAzure SQL DB Index Recommendations
For the SQL Azure Databases in multiple regions and multiple subscriptions, we keep getting recommendations/warning regarding the addition of indexes is required, but those recommendations are implemented already and those indexes exist. We understand that manually applied recommendations will remain active and shown in the list of recommendations for 24-48 hrs. before the system automatically withdraws them but it's a recurring issue for them and is causing lot of churn. Basically we want to understand if the recommendation has already been applied why is the base issue not resolved? Will they be there forever or is there a way to force the recommendation to be evaluated again. Thanks.
Query from Excel in Synapse Serverless
I'm trying to use the following code to read from an Excel file using Synapse Serverless. Is this even possible? SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;Database=https://container.blob.core.windows.net/datalake/Bronze/Manual/Testing 20230106.xlsx', 'SELECT * FROM [ProductList$]') AS ROWS; I'm getting an error near Openrowset which is less than helpful from the Synapse Serverless SQL engine
