4,719 questions
0
votes
0
answers
102
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
0
votes
1
answer
275
views
How to extract ETL (Jobs) from SQL Server Management Studio
I want to extract the code from my Jobs (ETL) from my SQL Server 2014, which are in SQL Server Agent -> Jobs. I currently have more than 400, is there a faster way than entering each one and saving ...
- 405
1
vote
0
answers
64
views
What separator to use when sending SQL Query results in .CSV via email?
so I could get this query to do what I need:
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@profile_name = 'my profiles',
@subject='subject',
@body='body',
@query= 'SELECT top 10 * ...
- 217
0
votes
1
answer
57
views
Error attempting to use dynamic columns in SQL Server stored procedure
I saw several posts on Stack Overflow describing how to use dynamic columns in a SQL Server stored procedure, but my attempts result in an error message regarding the dynamic column name I am trying ...
- 51
-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
0
answers
98
views
SQL Connection Timeout Even though there are less than 100 connections in the POOL
I am using the rest service connecting to the SQL server for data read and write operations. I have this SQL job running to kill sleeping connections when I execute sp_who or sp_who2. I still get into ...
- 1
0
votes
0
answers
25
views
How to return a specific date range in a filtered table in sql server 2014
I am using sql server 2014 report builder.
I have a system that can schedule activity in different date, and I want to return activities that are scheduled in wed and fri only in the report. Then I ...
- 3
0
votes
0
answers
65
views
Dynamic name of column with Year and Month
I need to complete the column names based on the Usage.Doc_WarehouseApprovedDate in such a way that the column name is in the format [Year_month]
I had a lot of tries but I can't figure it out how I ...
- 11
1
vote
3
answers
557
views
Why CURRENT_DATE in SSIS works but not in SQL?
I have a fully functional legacy SSIS system which I have inherited. I am using Visual Studio 2015 to view the packages. I also have a connection to Microsoft SQL using Microsoft SQL Server ...
- 6,028
1
vote
1
answer
85
views
Retriving data row that matches all conditions
"Great Indian Sale Discount" would be applied if all the conditions are satisfied.
context can have multiple context_subCategory values.
Negative grouping means mandatory conditions.
...
- 23
-5
votes
2
answers
144
views
What is the most efficient way of extracting these integers from a string using SQL?
I have a table (in a SQL server 2014 database, accessed via SSMS) of engineering data, and I need to report the bolt lengths that are mentioned in a description field, marked in bold:
STUD BOLT SET, ...
- 24
2
votes
2
answers
114
views
GROUP BY works but I don't think it should
From https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16 it says "However, each table or view column in any nonaggregate expression in the list ...
- 620
0
votes
3
answers
207
views
Get total minutes between 2 date time
I need to get a total minutes between 2 date time with the requirement where the time fall to another date should not count the minutes for the StartDate. Eg data as below
ID Start ...
- 701
1
vote
1
answer
66
views
Connect together different values with splitter that have been grouped by [duplicate]
In picture result is what result of that UNION ALL inner select looks like.
I'm having troubles connecting together values assigned to same ID.
In inner select I can get multiple same ID results, but ...
- 23
1
vote
0
answers
1k
views
The incoming TDS protocol stream is incorrect' error requiring IIS restart
I'm working on an ASP.NET Core 2.0.8 application, and it has been running smoothly. However, I'm facing an intermittent issue with all of my APIs. Occasionally, when I call this API, it returns an ...
0
votes
1
answer
61
views
How to get sum of amount from multiple tables?
I have three (3) tables which l want to get the sum total of amount from it.
OrderDetail
Product
Amount
OrderDate
Apple
10
2023-09-01
Orange
5
2023-10-01
LearnerDetail
Name
Amount
OrderDate
Tutor
30
...
- 65
0
votes
2
answers
122
views
Pivoting/Unpivoting multiple columns using dynamic SQL
I have the below requirement.
CREATE TABLE #TempData
(
Columnname1 NVARCHAR(50),
Value1 NVARCHAR(50),
Columnname2 NVARCHAR(50),
Value2 NVARCHAR(50),
Columnname3 NVARCHAR(50),
...
0
votes
1
answer
398
views
Count rows by monthend - year to date falling between two dates
I'm trying to create a query that can return counts of rows that fall between two dates as of the last day of the month prior and a count for the current month (MTD) at the time the query is run. For ...
- 1
2
votes
3
answers
200
views
How to optimise a query with multiple subqueries for string aggregation?
This is my query:
SELECT
wp.WorkplanID,
STUFF((SELECT ', ' + ISNULL(ul.FirstName + ' ', '') + ISNULL(ul.LastName, '')
FROM UserLogin ul
INNER JOIN Vendors v ON ul....
1
vote
1
answer
87
views
Group by MIN value in SQL Server pivot table query
I have looked through some similar questions but still can't figure this out.
My query:
SELECT *
FROM
(SELECT
Sites.ID AS SiteID,
MIN(DateInspectionDue) AS DateDue,
...
- 394
-4
votes
3
answers
710
views
Update column with leading zeros
Hope someone can help. I am trying to pad a zero infront of any result that has under 10 digits in column GPF4.
The column GPF4 is designed to be Numeric(18,0) as later on I will be converting this ...
- 1
0
votes
1
answer
83
views
Join most recent event into daily splits
I currently have a table of events with a time stamp and a "state" description of that event. The distribution of these events are rather random--I may have a hundred events in a day, ...
-4
votes
1
answer
81
views
In SQL how to take rows of data and collate into a new row
I have a table in SQL Server 2014 which stores all the process steps users have done on handheld scanners. The use of the scanners is to pick stock for sales orders.
The table has the following ...
- 111
1
vote
0
answers
70
views
SSRS fails to connect SQL Server DB Hidden instance
We had to set SQL Server hide instance flag as "Yes" in production to fix the violation like ADHOCdistQueries_RunValue, hideinstance etc.
But once we hide the instance, SSRS data source ...
- 55
0
votes
1
answer
53
views
How to setup to run a query against 2 databases
I am trying to find out which staff records are in the Production system and not in development with this query: If the id_number is not in the dev system then we need to update. They both have ...
0
votes
0
answers
857
views
SQL Server Always-on AG: database property TRUSTWORTHY on secondary replica different from primary replica
In an environment with SQL Server 2014 (there are practically even this version still in use), I routinely checked for databases that had the TRUSTWORTHY property enabled.
I stumbled across a database ...
- 83
0
votes
0
answers
183
views
SSRS report returning error at XML position that is higher than max position in the file
I have a report in SSRS that is not playing nicely on a single installation instance. All other installations the report runs as expected. All other reports on the offending server run as expected as ...
- 2,537
1
vote
1
answer
132
views
How to get all records from previous year
My code is producing current year's rows instead of previous year's rows in the last year column
DECLARE @prevYear int
DECLARE @prevMonth int
SELECT @prevYear = DATEPART(year, GETDATE() - 1)
SELECT @...
- 65
1
vote
0
answers
58
views
User types get "strange" names in sys_objects? [duplicate]
I've never used a user type in SQLS before, and I've been handed some sql code that creates one. Unfortunately the code assumes it doesn't exist, so I added a IF...DROP. But that does not happen...
IF ...
- 9,344
0
votes
1
answer
91
views
Calculate the percentage of a column base on year
I want to get the percentage of column Quantity based on the same year.
ID
Model
Year
Qty
1
M1
2020-01-01
10
2
M2
2020-02-07
12
3
M3
2020-11-20
7
4
M1
2021-01-04
8
5
M2
2021-05-19
5
6
M3
2021-09-11
11
...
- 65
0
votes
1
answer
98
views
SQL Server using IIF in Where Clause Syntax Error
I'm using SQL Server 2014. I keep getting a red squiggly lines under the last = sign and last parenthesis:
DECLARE @I_sYear char(4);
SET @I_sYear = '2021'
SELECT
PriceType,
SUM(PriceAmount) ...
- 1
0
votes
0
answers
97
views
DISTINCT results of CTE Outer Apply return empty set
Summary:
Results of CTE using OUTER APPLY return duplicates due to dirty nature of data and results of RegexFind to split out commingled key=value pairs.
When using DISTINCT on that CTE, the result is ...
- 155
0
votes
0
answers
718
views
SQL Full Backup Timeout
I've to do a backup on SQL2014 of a database of size 400GB.
When I do a the backup after 15min SQL give me an error : Time out. The available time expired before the operation was completed or the ...
1
vote
1
answer
564
views
What Roles and/or Permissions would I need to Assign to Users
I would like to have certain users of an Sql Server database to be able to:
Create Logins
Create Users in databases
Assign insert, update, select and delete permissions on certain tables within that ...
0
votes
2
answers
4k
views
How to Get Latest Updated TABLES in SQL?
I use Microsoft SQL Server to maintain the data which received from other software (Smartplant Instrumentation). I want to know the last table which was updated and inserted by the data from the other ...
1
vote
1
answer
2k
views
Hibernate "Invalid object name 'TABLENAME_SEQ'. " when inserting
I am writing a spring-boot application with hibernate. The application connects to a MSSQL 2014 database. I have used this tutorial for the basic architecture:
https://www.baeldung.com/spring-boot-...
- 11
0
votes
0
answers
27
views
SQL Server 2014 views differences in code
when I see the code in the view designer it's different from the code showed when I script the view using "ALTER TO" new query editor. Just an example
In design mode:
SELECT * FROM products ...
0
votes
2
answers
112
views
Dynamically add columns to query results
I am looking for a query to summarized data representing manufacturing job operations in one line. For example, a job can have multiple operations and not all jobs have the same operations. Then ...
- 29
0
votes
0
answers
318
views
Could not load file or assembly 'Microsoft.SqlServer.DTSRuntimeWrap on ASP.NET WEB API
I am trying to build an ASP.NET Web API application that will run dtsx files expected to be on the same server, but I am getting error below:
System.IO.FileNotFoundException: Could not load file or ...
- 11
1
vote
0
answers
362
views
Database email job succeed but no email with attachment received
I have set a databasemail profile on SQL2014, created a job, run it and the job succeed but I don't receive any email with my attachment.
Here is my query:
declare @clientname as varchar(50)
set @...
0
votes
1
answer
138
views
SQL rebuild Index Maintenance Plan is missing the Index Stat Option to set fragmentation percentages?
My maintenance plan is missing the section in the config window that allows you to set the fragmentation > a percentage. Does anyone know how to get this option or is it maybe I'm on too old a ...
0
votes
0
answers
69
views
Extract firstname and lastname, but firstname max length
I need to get the first and last name, but the maximum in the first name is 30 characters
declare @name nvarchar(250)='David Judith and Holofernes Equestrian of Gattamelata'
SELECT LEFT(rtrim(@name), ...
-1
votes
2
answers
134
views
Passing variable precision to TIMEFROMPARTS function
I'm trying to build a function that takes a string as input representing a time variable with no separators. The fraction part could have variable precision.
This is the code I came up with :
CREATE ...
- 406
1
vote
1
answer
858
views
SQL Query Dynamically get columns depending on mapping
I am stuck on an issue. I am trying to create a select which returns information depending on the Rules Mapping table. The Rules table tells us where (TableName.ColumnName) value should come from. I ...
- 93
0
votes
1
answer
882
views
Convert DateTimeOffset to DateTime in a where between 23:00 previous day an 07:00 Am next day for past 2 weeks
I have to make an query on a table where the datetime field is of type datetimeoffset.
The query itself is already difficult, but now working with conversions makes it even more complicated. What I ...
- 1,169
0
votes
2
answers
107
views
SQL to pull only the first 10 records of all fields in table, grouped by only 2 columns
My SQL skills are too weak to solve this problem, but I am pretty certain it is possible to solve.
To simplify - I have a small table with 5 columns, let's label them A, B, C, D, E. It's 1000 rows.
I ...
- 734
0
votes
2
answers
3k
views
SQL remove characters from string and leave number only
I have string(nvarchar) from db data and I would like to transfer it to numbers only. I was searching on Google for solution but I didnt find anything. I found something similiar here on StackOverflow ...
- 35
0
votes
1
answer
3k
views
Using a WITH clause inside SELECT * INTO [closed]
I have the following SQL for which I want to backup the results in to a new table (using SELECT * INTO backup_table)...
WITH LIST AS
(SELECT column1
FROM table1
UNION
SELECT column1
FROM table2
...
- 383
-2
votes
3
answers
711
views
Create a pseudo column to display Yes/No if one column value is present in another for a particular id
I have a table with the following structure :
create table test_18Nov ( account_id nvarchar(12)
, account_name nvarchar(25)
, zip_legacy_file nvarchar(5)...
- 123
0
votes
1
answer
58
views
I can't delete empty containers in an xml document on SQL Server, such as "<Item />"
I started working for the first time and I don't know much yet, I've been suffering with this problem for two days.
I have a document of this type:
<Tables>
<dbo.ES_Connection_Modes>
...
