All Questions

Tagged with
Filter by
Sorted by
Tagged with
0
votes
1answer
22 views

Importing CSV Flat File - how can we change defaults of all columns to n characters

I have to import a CSV file into a table that has 75 columns. The Flat File connection defaults all the columns to 50 character strings. Some of the fields are much smaller and some are 255. The only ...
0
votes
0answers
38 views

Multiple Date rows into One single Date row(date range)

An employee was sick during different period of time, when there is not a single day difference between the multiple dates range, or the date is continuous then it should be shown in one row covering ...
0
votes
1answer
24 views

Populate new rank field with custom order

I'm trying to populate a new Rank field in my table. Here is an example of how it should look like. +-----------+------------+------+ | CountryId | CreateDate | Rank | +-----------+------------+------+...
3
votes
1answer
147 views

How to reverse only English words?

I've an nvarchar column with English and Hebrew letters in some fields. In the fields where the English and Hebrew are together, the English letters are in reverse order (this is how the ERP program ...
8
votes
3answers
2k views

Improper use of logical operators has led to poor query performance

I have a table with huge amount of data (Nearly 15 million) and below structure. create table test (a int,--> /* There is a normal index on this column */ b int, <other columns>) There is a ...
0
votes
1answer
27 views

SSIS debuging tool in SSMS 2016

I want to trace the execution and see the changes in variables in the package during the runtime. the Microsoft report tool for the SSIS package is not sufficient. I want more details and don't have ...
12
votes
1answer
978 views

Seemingly identical WHERE clauses returning different results

I have a table named [User]. Below is a simple query to find one user from the table: SELECT [Username] ,[FirstName] ,[LastName] ,[EmailAddress] ,[IsStaff] ,[ExternalID] ...
4
votes
1answer
157 views

SQL Server 2016 suddenly produces Error:402,State:1,Class:16 updating a sproc with DATETIME/DATE/TIME arithmetic, that was fine before

In one of stored procs I need to capture the duration of a live event. It is guaranteed to never exceed 24 hours, so the datatype for the result column is chosen as TIME(3) (mapping to TimeSpan on ...
1
vote
1answer
55 views

Query to divide each column value to two categories: [Common , Not_Common]

I have a table with below structure and data: create table PTEST ( col_name VARCHAR(50), col_value VARCHAR(50) ) COL_NAME COL_VALUE ----------------------- first apple ...
0
votes
1answer
29 views

Help in querying hash PK and FK

I was trying to get the name of the PK and FK. However, this is the output that I get: PK: PK__adm_lib___3214EC067E4F7CCA FK: FK__adm_lib_S__fk_Co__7093AB15
0
votes
0answers
29 views

TSQL - Take a single row query and transpose based on field value

I'm trying to create a fact table with data I was given. We have a monthly goal number, but we want to be able to figure out how many weeks are in that month and divide the goal number by the weeks in ...
0
votes
1answer
125 views

Comma separated value string and compare against other values

Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything. Here is my query: DECLARE @ATID AS VARCHAR(3) = 12 DECLARE @...
1
vote
3answers
526 views

Tsql help - doing a large update from CSV file to a table in ssms

I have been given a csv file with just ids in there and those ids are in a table in ssms with columns that require updating. Out of 90,000 ids in that table, only 83,000 of them require updating from ...
1
vote
3answers
239 views

How can parse column names using ordinal position (not name) from XML data in SQL Server?

I have several hundred tables in a database that have the same structure: SomeId, Pos, Varying Number of Other Fields So, for example, one table may look like this: PersonId, Pos, Hobby, Degree 12345, ...
4
votes
4answers
912 views

Why changing the column in the ORDER BY section of window function "MAX() OVER()" affects the final result?

I have a table with below structure and it's data : create table test_table ( Item_index int, Item_name varchar(50) ) insert into test_table (Item_index,Item_name) values (0,'A') insert into ...
1
vote
1answer
506 views

In SQL Server, what's the best way to split multiple delimited strings into rows without a function?

I know there are numerous examples of how to split strings in SQL Server. However, they are often for a single delimited string. What's the best way to split multiple delimited strings without using a ...
-1
votes
1answer
55 views

TSQL throw weird error when commenting a line of text [duplicate]

Here is a strange situation. Following code works perfectly if I run it as is, but if I comment the first CHARINDEX line, it throws an error. Msg 536, Level 16, State 4, Line 1 Invalid length ...
0
votes
1answer
9 views

Query To Show All Users Who Have Multiple Managers

I have a database that holds user information. I need a way to find out what users have multiple managers. Below is a subset of data to illustrate the data structure. Using MS SQL Server 2016 what ...
5
votes
1answer
105 views

Merge with multiple records at once

There is a piece of code, from a previous dev, that is doing a merge statement with variables, and processing one record at time. I'm sure the merge can deal with multiple records at one. create table ...
-4
votes
1answer
71 views

Check If Today Is First Of Month [closed]

On my search engine I have seen multiple results to get the first day of a month, but I want to do something slightly different. In SQL 2016, I want to check if Today GetDate() is the first day of ...
-4
votes
1answer
29 views

Append String To Int [closed]

I need to add a % sign to a calculation I'm doing in MS SQL Server. This is the syntax I am using Cast(Round(Coalesce(totalsaleamt,0) * 100,0) As Int) + '%' but I get the error Conversion failed ...
1
vote
1answer
255 views

Accessing Memory Optimised Tables Under Snapshot Isolation

We have a high traffic database, that I am looking to convert to using IM-OLTP for performance gains. Due to the large size of the table in question, I'm looking to do a hot/cold solution, with a view ...
-1
votes
2answers
96 views

Index usage across databases [closed]

I would like counseling as to what is the best way to get performance in the following scenario. This is on a SQL2016 server. I have a newly created central database where data is replicated to from ...
1
vote
1answer
67 views

How to know a backup command failed or worked in a script [duplicate]

I have a command in a script to do a 'backup database [name] to [file] and all the other parameters. Now, we have a problem where we run out of disk space, the script keeps on trucking, just issuing ...
2
votes
2answers
76 views

DBCC CloneDatabase - sys.partitions rows value wrong

I'm using DBCC CLONEDATABASE to create a shell of a schema. One of the side-effects of the new shell clone database is that I left with wrong values at sys.partitions. as a matter of fact, the values ...
0
votes
1answer
35 views

Unusual behaviour of a query when using case expression in group by, some records are being missed

I have 4 table with below structure: Table "Customer_Info" create table Customer_Info ( Customer_Number int, Customer_Issued_Branch int, <Other Columns related to customer&...
2
votes
3answers
256 views

Query to update one column of a table based on a column of a different table

I have two tables with below structure : Create table Customer_info (Customer_num varchar(50), Customer_Branch int); Create table Customer_Transaction_Info (Customer_num varchar(50), ...
0
votes
2answers
125 views

Parent/Child summary rollup

I'm struggling to wrap my head around this one. I'm sure someone will simplify it. We have a parent/child relationship that I'm needing to roll-up based on sold quantities and sales dollar amounts. We ...
0
votes
3answers
34 views

Query to fine "Order_num" that has no two equal "product_name"

I have a table with below structure: create table A_TEST ( order_num int, product_name varchar(50), product_amount int, product_price int ); Sample data : insert into a_test (...
-1
votes
2answers
33 views

Concatenated string gets truncated

I have a query that i am writing to convert some heap tables with primary keys into clustered tables, but the string that i'm generating truncates on objects past a certain length it seems. SELECT ...
-1
votes
2answers
81 views

TSQL - Sql Injection

DECLARE @TSQL NVARCHAR(MAX) = N'SELECT 1, ''One'''; CREATE TABLE #ThisIsATable (Id INT, VAL NVARCHAR(10)); INSERT INTO #ThisIsATable EXEC [dbo].[sp_executesql] @TSQL ; Is it safe to have input ...
3
votes
1answer
273 views

What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`?

My company logs DDL on one database using a Database Trigger, looking for DDL including: ALTER_TABLE CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER When I disable or enable triggers using ALTER TABLE x ...
1
vote
0answers
283 views

Debugging T-Sql code using Visual Studio 2019. Error message: frame not in module

DBAs, Has anyone had success using Visual Studio 2019 for debugging T-SQL code, the way SSMS allows Step-into & Step-over? When I hit debug (Alt-F5), gives me this error, after making some ...
2
votes
3answers
160 views

Query to find sql server services currently running [duplicate]

Is there any query to find all SQL services currently running on the server including SSRS, SSIS, SSAS?
0
votes
1answer
83 views

After Insert Trigger WIth MS SQL Server

I think I have all my bases covered but wanted to ask someone who knows better than I. This is my syntax. UPDATE SI SET autoInsert = 0 FROM inserted WHERE SI.autoInsert = SI.autoInsert OR SI....
-1
votes
1answer
117 views

How do you convert a cursor to CTE? T-SQL

In an effort to clarify I have added more information and removed the "simplified" code. I have table with datetime punch information. CREATE TABLE [dbo].[AT_Punches]( [PunchID] [int] ...
2
votes
1answer
169 views

Add Total Row To PIVOT Query

I want to add a TOTAL monthly row to my query, below is my DDL, how can I have an additional row, under the last employeename that is labeled TOTAL and it shows the SUM() of the sales for ALL ...
0
votes
2answers
168 views

DBCC Shrinkfile not working on SQL 2016

Executed the DBCC Shrinkfile('file1',emptyfile) successfully on first file in the filegroup and removed the file that I shrank. Executed the DBCC ShrinkFile('file2',emptyfile) unsuccessfully on file ...
1
vote
1answer
46 views

Will this trigger always complete before returning results of Insert to client?

I have the following trigger for Auditing. CREATE OR ALTER TRIGGER dbo.TR_MyTrigger ON dbo.Cust AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; DECLARE @EntityState AS SMALLINT; ...
10
votes
1answer
739 views

Simplifying Function with Recursive CTE and/or Window Function

I'm trying to come up with a Recursive CTE and/or Window Function to create a function. After days, I've boiled the function down to (pseudocode) where I have N and B, and need to generate E: En =...
0
votes
2answers
89 views

T-SQL Query or DB Engine Optimization? [closed]

This query takes 100 ms on avarage but when you have hundreds or thousends of calls of them every second this would cost a lot. The dataset can not be cached as it may be changed at any second. Any ...
0
votes
1answer
61 views

Determine the source of user permissions in a database

I have a developer that just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment. How can I track down what security ...
1
vote
1answer
2k views

Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON

Bear with me - the example code has bit going on and I'll do my best to explain it, below. with ENTITIES as ( select [name] as entityName from (values ('A'), ('B'), ('C')) X([name]) ), PROPERTIES as ( ...
0
votes
2answers
36 views

Insert Records That Do Not Exist

We are upgrading from one database to another. Both databases have the same tables, and for examples sake let's say we have dbOld and dbNew. I need a way of for each table on dbNew check the ...
0
votes
2answers
54 views

MSSQL NULL's Between specific dates

I have a TSQL script that counts transactions for companies based on all approved, declined and failed trans in the previous month(s). I also show companies that have 0 approved, declined and failed ...
1
vote
1answer
297 views

Error during sp_refreshsqlmodule after altering table type

I am trying to add a column to a user-defined table type. I have done some research and here is what I have now: -- First, rename existing table type to something else EXEC sp_rename '...
0
votes
1answer
60 views

Can't help the engine to choose the correct execution plan

The stuff are pretty complex to share the original code (a lot of routines, a lot of tables), so I will try to summarize. Environment: SQL Server 2016 standard edition Objects: wide table with ...
1
vote
2answers
86 views

How to sort values starting with a letter followed up by a number?

I have a series of values, possibilities are : A1, A2, B1, B2, A100, A101, B100, B200, 1, 2, 3, 4 and so on. How can I sort it so that first the letter is sorted, and after that the number with the ...
1
vote
1answer
129 views

Subtracting With GetDate()

I am trying to convert a postgresql query to ms sql query. This is what I have in postgresql round(cast((Current_Date - emp.hiredate) As Decimal)/365,1) now I thought it would be a simple ...
0
votes
2answers
266 views

Performing Sum in Case Statement

I am trying to get a sum inside a case statement, but I am getting the error Cannot perform an aggregate function on an expression containing an aggregate or subquery This is my query syntax ...