336,750 questions
1
vote
1
answer
119
views
Ranking dataset based upon two conditions
In SQL Server the following query needs to rank records with the same ID by the latest 'date completed' output column (derived from a coalesce from date columns from two tables specified in the join).
...
- 103
0
votes
0
answers
103
views
Is DTExec.exe and SSIS backwards compatible?
We have hundreds of SSIS packages created with Visual Studio 2015 and currently they all run perfectly fine through batch commands that execute the following:
C:\Program Files (x86)\Microsoft SQL ...
- 314
0
votes
1
answer
162
views
The given key '170' was not present in the dictionary
I am using Azure Data Studio on Ubuntu 24. I was able to set up MS SQL Server using terminal and a connection in Azure data studio. Now the problem is that whenever I try to create a database, I get ...
0
votes
2
answers
182
views
For ERROR_NUMBER 515, how to get the column name from ERROR_MESSAGE
In many SQL error messages, SQL Server does not mention column name. But in messages such as the one shown below, the error message does include the column name.
Question: How can we extract the ...
- 24.2k
2
votes
3
answers
99
views
2 date attributes stored as text strings need to be compared with earliest date selected
I have 2 date attributes stored as text strings that need to be compared with earliest date selected.
In the output shown here, you can see for ID 1 the earliest date has not been correctly identified ...
- 103
0
votes
1
answer
143
views
PowerShell Invoke-Sqlcmd Error: "The certificate chain was issued by an authority that is not trusted"
I'm using a PowerShell script to execute multiple SQL scripts. The script works perfectly on my local machine. However, when I run the exact same script on a remote desktop (RDP) server, I get an ...
0
votes
3
answers
215
views
Partial matching on strings
In SQL Server I have a table of bank transactions, and a table of payees. I want to link the transaction to the correct payee. I have used CHARINDEX (PayeeName, TxnDescription,1) > 0 to find the ...
- 11
-1
votes
1
answer
105
views
Grouping data using the LEAD() function
I've got this query:
SELECT TOP (1000) [TRANS_ID]
,[VSN_VENDR_EFF_DT]
,LEAD([VSN_VENDR_EFF_DT], 1, '9999-12-31') OVER (ORDER BY [ACCT_NUM],[VSN_VENDR_EFF_DT]) AS NEXT_EFF_DT
,[...
- 8,492
1
vote
3
answers
153
views
SQL Calculate Running Total Against Positive Transactions
I have an instance where I need to be calculating the running total by positive transactions. This exercise is to determine which positive entries in our warehouse entries is still open as we need to ...
- 21
1
vote
1
answer
89
views
How to ensure rollback consistency and scalability when inserting into multiple SQL Server tables using JPA and JDBC in Spring Boot?
I'm working on a system where I need to store processed data across approximately 30 tables in a SQL Server database. For frequently used tables, I'm using JPA, while the majority of the insertions ...
- 27
0
votes
1
answer
107
views
Update Bicep File to Fix Azure SQL Database 2014-04-01 APIs will be retired?
My IT department is getting an e-mail "Azure SQL Database 2014-04-01 APIs will be retired on 31 October 2025". I use bicep and have never used any API's that are that old, they're all ...
- 8,935
2
votes
1
answer
155
views
SqlQuery result : Microsoft.Data.SqlClient.SqlException: 'Invalid column name : 'Value'.'
I have this EF Core query on a SQL Server database to get the database collation (code from Copilot):
var collation = dbContext.Database.SqlQuery<string>($"SELECT collation_name FROM sys....
- 12k
-4
votes
1
answer
197
views
Populate a DataTable row with the primary key after an Insert into a SQL Server table
I have a DataTable in C# with a primary key column that is using negative numbers to auto increment. When I update the back end, I cannot figure out how to get the primary key from the database into ...
- 11
4
votes
2
answers
232
views
SQL query to return roles and groups a user has that are not related to a toplevel role
I have a set of tables in SQL Server that all join to each other and I'm trying to find a way to find a negative relationship. My tables also have recursion.
The data is here: https://sqlfiddle.com/...
- 63
-1
votes
1
answer
122
views
Counting unique records across two tables [closed]
I have two tables
The first table is my INVOICEHEADER which contains one record per invoice, but multiple records per order. InvoiceNumber is the unique key on this table (there is no table for ...
- 388
4
votes
1
answer
100
views
Strip numbers from string after certain characters into a recursive table
I have unstructured, free-form data, which requires cleansing to produce a clean list of all PO numbers in a column. Unfortunately, the column can accommodate multiple PO numbers in various formats (...
- 575
0
votes
2
answers
144
views
Case expression to set a variable that is then used in another case expression [duplicate]
I have the following SQL query (VERY simplified):
SELECT
Column1,
Column2,
Column3,
Column4,
CASE
WHEN [Field1] = Condition1 THEN Value1
WHEN [Field1] = Condition2 ...
- 25
2
votes
3
answers
185
views
How can I get a list of referenced tables from a query?
I have stored queries in a table that run when I want to validate the model. I want to get a list of every table that query references so I can check if a query needs to run only if a certain table is ...
- 103
0
votes
0
answers
127
views
Dotnet Aspire > Failed to debug this project because no SQL Server has been installed on the local machine
I'm trying to setup program.cs in AppHost project with all resources (SQL, API) but unable to spin up SQL project
I want my SQL project to run without any dependency of my SQL credentials. Instead it ...
- 81
0
votes
0
answers
101
views
SQL Server 2014 full text search can't handle short strings
I have an instance of SQL Server 2014.
I have FTS index on my Customerstable. Everything works fine but I found that it can't handle short strings. For example:
SELECT *
FROM [dbo].[Customers]
WHERE ...
- 5,327
-3
votes
1
answer
103
views
SQL query gives error Invalid object name [duplicate]
I have three tables:
Table1: Account (UserID, FirstName, LastName)
Table2: Luncheon (LuncheonID, Restaurant, Date, Address)
Table3: SignUp (UserID, LuncheonID)
I am trying to populate a GridView ...
- 1,074
0
votes
0
answers
121
views
SQL Server BULK INSERT Delimited Text File with over 500 columns
I'm trying to bulk load an ascii pipe-delimited text file with 537 varchar columns into SQL Server 2019 (v15.0.4390.2) where I only need 276 of those columns. Regardless of the methods I have tried, I ...
- 13
1
vote
1
answer
79
views
Query pivot with encryption
I need to make a query with a large dataset. The data is spread over multiple rows in a table, the goal is to have them in a single column.
Some columns are encrypted (Always Encrypted, in this ...
- 47
2
votes
2
answers
96
views
Select only one in case of multiple occurrences
When looking at an SZERZODES_ID, there are records where PARTNER_ID is specified and there are records where it is not. In the result, I would like to get back only those records where the PARTNER_ID ...
- 51
0
votes
2
answers
327
views
Deployment pipeline for SQL Server 2022 DACPAC file throws an error Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider is not valid
One of my clients has an on-premises SQL Server 2022 database. I'm building a deployment pipeline in Azure DevOps that builds a DACPAC file based on a VS2022 project and deploys this to the server. We ...
- 143