I need help with making a conclusion in SQL Server based on some column’s values, like status aggregation sort of. As an example, below is a table containing server tasks and their status. If I want to return the aggregated status of each server here are the rules: if all the server tasks are at status …
Tag: sql-server
The difference between OUT and OUTPUT
In an application that uses SQL Server I came across a code that uses both OUT and OUTPUT parameters. What is the difference between OUT and OUTPUT parameters. Answer As noted in the comments, the 2 are synonyms. From the documentation the syntax for the clause is listed as below: So this denotes that either …
Return value from dynamic linked server stored procedure
I want to return a value from a dynamic linked server stored procedure. On the local server, how can I retrieve the value of @DocNumOut? I have trial many combination using EXEC, openquery, EXEC sp_executesql, but none can return the value into my local variable, @DocNumberOUT. Answer Use OUTPUT parameter on …
Returning a value before committing transaction and (UPDLOCK, HOLDLOCK)
I need to check if a record exists – if yes: then returns its id, if not: creates a new record and returns its id. I am using WITH (UPDLOCK, HOLDLOCK) in SELECT to prevent from duplicates (it creates lock). I wonder if I should commit the transaction if a record exists in a database for realising the lo…
Divide the results of two select queries
I have two SQL count queries that on their own work fine, they are: Now how to do I add to the query to get the percentage of 26/130?? I have a new query to go along with how to get percentages. Here it is: this brings back results below: If I remove the Distinct from my query there are
Retrieve the correct disk space usage of a columnstore index in MSSQL
I was trying to retrieve the exact amount of bytes required by a clustered columnstore index in MSSQL. Unfortunately I get different result from sp_spaceused compared to a simple query on sys.allocation_units. Here the simple script: Here the sp_spaceused result: Then I try to get the amount of occupied pages…
How to count from first date till date?
I have a table like below in SQL server Date Store ID Sales Amount 01-01-2021 1001 1000$ 01-01-2021 1002 1000$ 01-01-2021 1003 0$ 02-01-2021 1001 1000$ 02-01-2021 1002 1000$ 02-01-2021 1003 1000$ 03-01-2021 1001 1000$ 03-01-2021 1002 1000$ 03-01-2021 1003 1000$ how to calculate count of previous days if Sales…
Insert repeating tag xml data to multiple sql table columns
I’m having following XML and query. header tags are there and Item tag can be repeated up to 10 tags. I want to insert those item no and qty in separate columns. Here I mentioned only 2 tags for Item but in real situation there are 5 tags in XML. So in worst case scenario it will be 50 columns.
Divide by zero exception in SQL Server
I have an update statement which has division operation and one row throws exception Divide by zero exception and I see that none of the rows got updated . How to continue on exception so that rows which doesn’t encounter 0 gets updated and row which has 0 fails? Its just a normal update statement witho…
Unable to Run Update Query in SQL Azure *Invalid object name ‘DataSync.provision_marker_dss’.*
I was syncing my Azure SQL Database with On-Premises SQL Database which created a lot of tables in the Online database (As Usual). Anyway, It was working fine. Now, I had to re-create the Azure SQL Database. So, I deleted the Sync Group & Sync Agent as well. Everything’s working absolutely fine unle…