Laden...
|
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Creating Aggregates | |
What types of code can I include in the CREATE AGGREGATE statement? | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Steve Jones - SSC Editor) |
An Incomplete Window Definition I have defined code as follows in SQL Server 2022: SELECT SUM(sod.OrderQty) OVER(ob_OrderID_ProductID partition BY sod.SalesOrderID) AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID)What is the default framing for a window definition such as this? Answer: RANGE UNBOUNDED PRECEDING AND CURRENT ROW if an ORDER BY is specified Explanation: The default framing is the same as if the OVER() clause were not in window definition. This is RANGE UNBOUNDED PRECEDING AND CURRENT ROW when an ORDER BY is included. Without an ORDER BY, then the entire partition is used. Ref: SELECT - OVER - https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16 SELECT - WINDOW - https://learn.microsoft.com/en-us/sql/t-sql/queries/select-window-transact-sql?view=sql-server-ver16 |
Database Pros Who Need Your Help |
Here's a few of the new posts today on the forums. To see more, visit the forums. |
SQL Server 2017 - Administration |
Semaphore Timeout Expired Issue When Communicating with Web Server - I've been encountering a persistent issue while attempting to read/write to a web server. The error message I'm receiving is A transport-level error has occurred (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) I've already ensured that the firewall is enabled and properly configured, and there don't seem to be any […] |
IMPLICIT TRANSACTIONS - Hi All, In our environment we have bunch of vendor specific db's where it is using SET IMPLICIT_TRANSACTIONS ON setting and it is causing a lot of BLOCKING. especially for long running adhoc batches with multiple statements and transactions,keeping the transactions open and holding locks for longer duration. So, the issue started when one of […] |
Elevated permissions to developers? - Hi All, Is it a good idea to give any elevated permissions to developers? In one of the sub-prod environments, Dev team is running some newly developed stored procedures from SSMS and Frontend UI. When they see the blocking they are reaching out to us and we are providing where it is getting blocked and […] |
Unsupported OS to update the driver - Hello, I have a client machine, which doesn't connect SQL Server. The driver seems unsupported but the operating system doesn't support to install the latest driver. Is there any workaround to connect to SQL using old version of driver? |
SQL Server 2017 - Development |
Commit transaction inside loops - Hello, our users are complaining about blocking during a delete loop so I'm thinking that adding begin tran; and commit tran; inside the loops would allow the other queries to squeeze through in between delete loops. Is this how people would achieve a reduction in impact while deleting data and is the below query pretty […] |
SQL Server 2016 - Development and T-SQL |
SQL runs slow in a union all statement - Could you please help? My Union all sql query consist of three individual queries that runs quickly on its own. However when they are in a union all statement it runs up to a point then kind of hangs and then resume after a while. It runs for a long time. The first two queries […] |
Reporting Services |
The subscription contains parameter values that are not valid - Hi, I've got an issue on SSRS where I have a daily subscription that works most of the time but sometimes the subscription fails with the message "The subscription contains parameter values that are not valid". This is without any changes to the subscription and when you manually run it, it goes through successfully. Checking […] |
Analysis Services |
Invalid location of measures when opening tabular model in excel - Hello, I built my first Analysis Services Tabular project, however the icon of measures and dimensions were not as expected. Also location of measures is under the dimension. (images attached) Any idea what could be wrong with the project setup ? Thank you. |
Working with Oracle |
Sequentially update a column with a decrementing value - I want to update the "QTY_REQ" column in the PLSQL table in the screen cap below sequentially row by row by dividing out a number (e.g. 7) among the rows. I want to start with the row with the highest value in QTY (QTY 4, LOC 10800B41) and enter 4 for QTY_REQ. Then go to […] |
Sequentially update a column with a decrementing value - I want to update the "QTY_REQ" column in the PLSQL table (in the screen cap attached) sequentially row by row by dividing out a number (e.g. 7) among the rows. I want to start with the row with the highest value in QTY (QTY 4, LOC 10800B41) and enter 4 for QTY_REQ. Then go to […] |
SQL Server 2022 - Development |
SQL Server Developer Edition on Windows VM - I'm currently facing a peculiar issue with SQL Server Developer Edition on my Windows VM, and I'm seeking your expertise to help me troubleshoot this problem. Has anyone faced this issue? |
Can you reference 'Inserted' or 'Deleted' Trigger Table in SQL Mail Query - Hello, I am trying to reference the results from the Inserted or Deleted tables from a SQL Trigger in teh @query param of sp_sendmail but doesn't seem to work. Getting the dreaded: Msg 22050, Level 16, State 1, Line 54 Failed to initialize sqlcmd library with error number -2147467259. This usually happens when you are […] |
Primary key column question - Hi, I'm new to sql server and successfully created a table of columns, however after creating the table I realised I forgot to add the primary key column. I used the ALTER command for and used a query to create a new column for the primary key. This was successful but it added the column […] |
Newby - Stuck with a trigger. - Hello Forum, Apologies for this post, I'm stuck with an update trigger that i need to amend. I need to capture the Date, OrderID and CustID, Surname and Firstname from a trigger which prevents an update on table. The Date, OrderID and CustID , Surname and Firstname need to go into a table. I can't […] |
Check Code Correction Current Period Prior Period Movement - Please assist me?I am trying to calculate Current Period Prior Period Movement from my data, the date columns are: Please check my code for any errors, code runs, i just want to make sure it does what it's supposed to do. Small data set is attached. -- Use IF EXISTS to check for the […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. [email protected] |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Laden...
Laden...