17,037 questions
2
votes
2
answers
157
views
Multiple rows aggregated to single row
Business:
I have one manager tied to multiple employees/single employees across different business and Manager and employees are in different rows. We have to map on ID and also on zip codes (both ...
- 640
0
votes
1
answer
150
views
Power function gives wrong result
Whether in SSMS or in SQLCMD, if I type
select power (9.0, 28)
I get the wrong result. If I break this down into multiple powers of sixteen, e.g.
select power (9.0, 16) * power (9.0,12)
I get the ...
user24503011
0
votes
0
answers
69
views
Get package depends object in SSIS package
I need to get list of objects depends in each SSIS package using SQL script, or any other approach autoprocess.
Example ssis solution have 2 packages.
package1.dtsx
package2.dtsx
Package1.dtsx has 5 ...
- 23
0
votes
2
answers
127
views
Stored procedure keeps locking itself
We trigger a stored procedure that inserts into table1, but then we want to remove extra rows, so there are no duplicates. The problem is when we try to remove these dups, it locks the table until it ...
- 9
0
votes
1
answer
71
views
SQL Group by date and payment total
I'm trying to pull data from a payment table. I need the total Payments received per day. So
The column names are "pmttotamt" and [date].
I've tried variations of this statement but am ...
- 76
2
votes
1
answer
201
views
SUM and Decimal precision
How does SQL Server handle decimal precision when the SUM() function is called? For precision and scale I have been using this guide to help me determine what is going on. The query goal is to ...
- 201
0
votes
0
answers
73
views
LimeSurvey upgrade to version 6.6.5 produces issues with export_responses API
I used LSRC2 JSON/RPC 2.0 API to connect to LimeSurvey and export the completed Surveys with LimeSurvey version 5.3.32 and C# as a programming language. I used the export_responses API method and ...
- 1
1
vote
1
answer
111
views
Powershell script works fine in Powershell Command line but throws error "ReportWrongProviderType" is invalid when run from Sql Agent job
My powershell script runs fine in powershell command line but throws the following error when run from Sql Agent :
Executed as user: DOMAIN\svcSQLDefaultPROD. A job step received
an error at line 21 ...
- 37
1
vote
1
answer
254
views
Export stored procedure results as CSV (UTF 8 BOM format) to external server from SQL Server 2012
I am able to export the stored procedure results as CSV to the local drive on SQL Server, but I am having difficulty exporting the results to an external server (or shared drive location).
I am able ...
- 37
0
votes
1
answer
178
views
SQL Server database copy utility fails - "Login failed for user"
This error seems quite self explanatory but I have no idea how to rectify it. I am trying to copy a group of databases from a SQL Server 2012 instance on one virtual server to a SQL Server 2019 ...
0
votes
0
answers
140
views
Changes in query duration for highly transactional table
I have recently noticed that one of the main queries that hits our busiest table fluctuates in terms of actual execution time by quite a lot and I'm struggling to understand why.
Below is the table:
...
- 57
0
votes
1
answer
129
views
How to join row values with Column names in SQL
I have three tables
Table1:
Employee
Designation
Table2:
ColumnA
ColumnB
Ravi
Developer
Swathi
HR
Table3:
Sourcecolumn
Destinationcolumn
Employee
ColumnA
Designation
ColumnB
Expected Output to be ...
- 7
0
votes
0
answers
83
views
XML path into temp table creates a nvarchar column, issues when merging it varchar column
I using SQL Server 2012 and need to use FOR XML PATH to get a comma-separated string for like projects. The query runs and works but when I try to alter #T2, I get an error
String or binary data ...
- 37
0
votes
2
answers
113
views
Use XML path in SQL Server 2012 with a temp table?
I am on version 2012 and need to use FOR XML PATH to get a comma separated string for like projects. I have a temp table that I have compiled and would like to combine people based on project numbers....
- 37
-2
votes
1
answer
141
views
Use of MERGE reserved keyword in different versions of SQL Server [closed]
I am getting runtime error while calling a stored procedure (as shown below) in SQL Server 2012 but not in SQL Server 2014, it looks very fundamental, but I would like to know exact reason and if ...
- 686
0
votes
1
answer
56
views
Stumped on a complex GROUP BY [duplicate]
Given the following query, how do I group by con_num and retrieve the lead_id with the MAX date_entered? I am using SQL Server 2012.
SELECT leads.id AS lead_id
, leads.date_entered
, so....
- 1,100
0
votes
2
answers
746
views
Object reference not set to an instance of an object: on Publish
When I click a publish button:
on the Database project in Visual Studio I cannot access the publish window and I just get a popup that says Object reference not set to an instance of an object.
...
- 1,036
0
votes
0
answers
142
views
Shared Memory Provider: No process is on the other end of the pipe. Unable to open a logical session
During the normal use of my program, I received the following error message:
"Shared Memory Provider: No process is on the other end of the pipe. **Unable to open a logical session**"
After ...
- 33
0
votes
1
answer
131
views
How can I optimize a SQL query to efficiently count non-blank column values without using a cursor?
I have written a T-SQL script to count non-blank values in each column across all tables in a database. The script currently uses a cursor to iterate through each column and dynamically generate and ...
- 413
0
votes
1
answer
78
views
Multiple repetitive tables
I'm querying results out of 2 tables, at a time, which has a repetitive pattern.
select
abc, xyz
from
2010_Q1_result
inner join
2010_Q1_run
(next comes 2010_Q2... inner join ...
- 53
-1
votes
3
answers
94
views
Remove duplicate nulls from a SQL Server table
Issue table:
col1 col2 col3 col4
----------------------
1 null a null
1 b null null
1 null null c
2 aa null null
2 null bb null
Output needed
col1 col2 ...
- 1
0
votes
1
answer
84
views
Take data from Lead Record when condition or filter criteria matches and ignore the records between current row and selected record (lead row)
How can I write a CTE in SQL Server to get the date from the preceding row for the person that has Type = NA.
If there are multiple preceding rows with NA then take the first one when sorted by dt_eff ...
- 110
-2
votes
1
answer
61
views
SQL Server 2012 trying to update fill missing bill no sequence continously and count? [closed]
I have this data in a SQL Server 2012 table:
bill_no
datetime
3125
2022-12-26 14:17:21.000
3125
2022-12-26 14:17:21.000
3126
2022-12-26 14:31:48.000
3126
2022-12-26 14:31:48.000
3127
2022-12-26 14:39:...
0
votes
1
answer
55
views
Calculate end date of partition rows using start date of following partition
How to write LEAD function to take value of next record when there is a change in Type.
Source Data
Person Type dt_eff
123 ABC 2018-10-23
123 DEF 2018-12-19
124 ABC ...
- 110
1
vote
2
answers
98
views
T-SQL Update statement with previous row
I have a SQL Server table with these 3 columns
Id, NetWeight, TransactionWeight
I need to update the current row's NetWeight value with the previous row's Netweight + current row's TransactionWeight.
...
- 57
0
votes
2
answers
79
views
Remove specific decimal from integer
Let's say we have value 2006 and now I want to remove 0 from it and the result is 26.
Value : 2006
After removing 0, the value is : 26
I have tried to replace directly using replace but in some case ...
- 21
0
votes
2
answers
163
views
Recreating the InitCap or Proper function with some restrictions
First of all I am using this SQL Server version according to @@version:
Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (X64) - Nov 1 2020 00:48:37
Standard Edition (64-bit) on ...
0
votes
1
answer
44
views
How to add a center for the next two months if there is an order the previous two months
My manager wants me count each center that has an order. If a center make one order within the beginning of the last two months, it is considered active for the next two months from the month that it ...
0
votes
1
answer
86
views
Turns rows into columns
I am using SQL Server 2012 and I am trying to "pivot" rows to columns per key.
Maximum rows per set is 4.
I have prepared a sample code in order to describe it better
DECLARE @resu TABLE
...
- 958
0
votes
1
answer
60
views
Fetching records from Text column and storing in separate column with comma separator
This is the sample data:
Create table #test (ID int, remark nvarchar(max))
Insert into #test
values (1,'ITEM:11119:QTY:24 LABEL:00008402279208913782,ITEM:16400:QTY:90 LABEL:00008402279248620756 ...
- 2,413
0
votes
1
answer
27
views
how to Retrieve Data from a Data Mining Model to display it via SSRS report?
I need to display prediction graphs using SSRS to make this accessible to some users who need it. To do this, here are the steps successfully completed using VS community 2019:
1- I created a ...
0
votes
1
answer
54
views
Query not returning expected output with Not exists
I wanted to display those events which is not having D1.
Here is the table schema
Create table #tmp (ID int,stopid int,Events nvarchar(10))
Insert into #tmp values (1,1,'AF')
Insert into #tmp ...
- 2,413
-3
votes
1
answer
71
views
Why does this date time format don't work?
How do I query this problem?
I want to insert a date time in this format in a column name called DateTime_Encoded using a where clause.
The format should be this one (mon dd yyyy hh:mi:ss:mmm AM (or ...
0
votes
1
answer
59
views
How table changes are automatically reflecting in table on another server?
I checked a couple of posts which advise a trigger but in my scenario I could not find any trigger.
I have a server DB. The client DB has Linked Server setup and points to server DB.
Any value change ...
- 14.2k
2
votes
1
answer
152
views
Grouping of data based on patterns or sets of other rows
I have a data set with two columns (Person and Item) and I want to generate a group column (Group) based on column1 but the group will expand to include rows which have the same values as column 2.
...
- 23
-2
votes
1
answer
94
views
Extract string from column in SQL Server 2012
I am trying to extract a certain amount of length in string if the specific value is available inside that column. The column however has a lot of data in it which where the value MODEL_NUMBER may be ...
- 79
-1
votes
3
answers
107
views
SQL Server Case Then Statement
Is it possible to have multiple expression in the Then for a Case statement
What i want will look like this
Select EmployeeId,
Case
When Dateofjoin > '1-1-2000' Then Select Column1 , Select ...
- 2,894
0
votes
0
answers
944
views
ASP.NET Core web application running slow & occasionally timing out while running large queries to database after updating to .NET 8
I recently updated my ASP.NET Core MVC web application from .NET 5 to .NET 8 and have since been having intermittent timeout issues with IIS while running larger queries against the database.
The ...
-2
votes
3
answers
157
views
SQL Substring from a column of strings
I have a table with around 100,000 articles.
Each article has a description.
short example:
ARTIKELNR
DESCRIPTION
104009400031900
S235JRG2C+C EN 10277/10 80 x 8 mm Blanker Flachstahl
104009800002950
...
- 35
-3
votes
1
answer
87
views
Sum time of consecutive rows with a condition of a field [closed]
In a table organised like this,
EventTime
Device
State
UpTime
2024-01-01 04:48:49.080
device_1
1000
1
2024-01-01 04:49:14.097
device_1
1000
0
2024-01-01 04:49:45.753
device_1
1000
1
2024-01-01 04:50:...
0
votes
0
answers
88
views
Trying to delete records from a table that has 20 million records
I have this stored procedure that it is not finishing the execution at all. The table TbARIAFLD3_ARIAFLD3Errors has 20 million rows, could you please help me how to execute it faster? The ...
- 561
0
votes
1
answer
89
views
CROSS JOIN and STDistance to find closest point
I have two tables with Walmart locations, and I want to know which Walmart from one table is closest to the Walmart of the other location.
This would be the result, if possible:
Source ...
- 3,346
1
vote
3
answers
135
views
Getting the Penultimate Record from a Table Sorted by Descending ID
We need to obtain the penultimate row of a table that is sorted by its ID in descending order, not by the date. Then, ensure that the table has unique date values. Finally, once the table is sorted ...
- 157
0
votes
1
answer
43
views
Make 1 row result from two result using SQL Server
Using SQL Server, is it possible to create a one row result from two rows? See the following:
Sample Data:
+-----+-----------+----------------+------------+---------------------+
| PC | DriveName | ...
-1
votes
2
answers
79
views
How can I reliably store an ID so that I can use it in another INSERT statement?
I have a Python program that executes a series of SQL statements via a loop. It first inserts data in one table, then it inserts data in another table.
This table doesn't use an identity seed, but it ...
- 7,216
0
votes
1
answer
64
views
How can I add a whole column of data as a row?
I am dealing with a library database today. The structure is kind of odd, and I am having trouble pulling the data how I want it to appear.
I have this query:
SELECT
lc.catalogID, hb_g.intro AS '...
- 7,216
0
votes
3
answers
209
views
SQL loop invalid because no scalar variable?
I'm trying to populate my database table with some dummy data and I wrote a loop for that. I'm using DBeaver to connect to my SQL Server db and I run the code against it.
DECLARE @counter INT = 1;
...
- 1,882
0
votes
4
answers
212
views
SQL Hierarchy Fill Down [closed]
I have an entity table that list row per entity, the rows are ordered (using id column) to show an implied hierarchy. Note for row 'B2', 'C1' does not get filled down because that is different second ...
- 154k
-1
votes
1
answer
1k
views
Row Number to give same value when same partition [duplicate]
How to get the row number equal when the name is the same in the SELECT statement?
When I try with following code:
SELECT
ROW_NUMBER() OVER (PARTITION BY BENFNAME ORDER BY BENFNAME DESC, BENFNAME ...
- 1,830
0
votes
1
answer
60
views
Need to update a column in a table from referencing from other table without hardcoding
I am currently working on a query which needs to update the column posting_ID in transaction table based on the information from Contract table for each client.
For example, the transaction table have ...
- 41