Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,519
questions
0
votes
1answer
36 views
SQL Server Automate refresh of one table only from one environment to another
I'm looking for a best practice/option to refresh one table only from Prod to Staging environment.
Technical Details:
The table in Source is loading daily with new data.
The table in destination ...
-3
votes
1answer
34 views
Store procedure Incorrect syntax else [closed]
Could I be missing a condition inside my stored procedure? I'm getting this error:
Incorrect syntax near keyword else line 41.
use eNtsaRegistrationDB;
create proc[dbo].[...
-3
votes
0answers
37 views
Incorrect syntax near end [closed]
Does anyone knows how to solve this issue 'Incorrect syntax near end'. Here is my store procedure.
use eNtsaRegistrationDB;
CREATE proc [dbo].[Usp_CheckUserRoles]
@UserId int
as
begin
if((select ...
0
votes
1answer
19 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 '...
8
votes
2answers
3k views
Dodgy T-SQL Query Execution driving me crazy
SQL Server 2019. Here's a link to a gist with the xml plan.
Hello, I am having a heck of a time understanding why this query is taking 0.02 seconds to execute when it can't find a record with one of ...
0
votes
1answer
25 views
SQL stored procedure to produce custom XML text
I have around 30 tables from which I need to generate a specific XML format, which is consistent through the tables.
With my current solution I have a stored procedure for each 30 tables where I do: ...
6
votes
3answers
280 views
Finding Missing Inverse Relationships
I'm struggling with a query which I feel should be straightforward but I just can't get my head around it.
We have a single table that holds relationships between different parties. The table SHOULD ...
0
votes
0answers
52 views
What is wrong with the stored procedure? [closed]
Trying to run such query and getting such errors.
USE [CompeatSampleDev];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROC [dbo].[exeAuditLogJson]
(
@AuditionLogRecords ...
0
votes
1answer
40 views
Duplicate record inserted - why trigger is not preventing it?
I have two tables: DebitIUVs and RPTs. I have record inside DebitIUVs which is with state 118001. My business logic must insert new line inside RPTs table. Inside RPTs table there is additional ...
0
votes
0answers
22 views
How to update from one table to another [closed]
Whats the best way to update 150 rows into one table from a temp table?
Temp table includes
Col1 col2 col3 col4
Table1 includes
Col1 col2 col3 col4
Both have values in them. Table1 has 1700 rows ...
0
votes
1answer
53 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
39 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
votes
1answer
26 views
Help with joining to date table
There is an existing report that takes two parameters (BeginDate, EndDate) from the user. When a user inputs two date ranges, it produces a result in the LeadTime column of the difference between the ...
0
votes
1answer
29 views
How to return JSON objects in tabular format using T-SQL?
I am storing student data by group (or class) and week in the following format:
where GroupData is an array of students (relevant fields are PersonId, StudentName, and GradePoints). GradePoints can ...
0
votes
1answer
47 views
Distribute values equally
Each ship docks at a port. If too much ships arrive at a single port the crew gets overwhelmed.
How to return a result set so a port does not get overloaded?
One possible solution and set based ...
1
vote
1answer
29 views
Querying XML Nodes with SQL
I think I'm nearly there with this but have reached an impasse!
I've a relatively straightforward XML structure that I need to parse out as a SQL resultset but can't seem to get it to work.
Here is ...
1
vote
1answer
32 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
41 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
...
-1
votes
1answer
20 views
Using Sum With Query
How can I get the Sum of data from a secondary table w/o using a join?
Select emp.ID, emp.fname, emp.lname, emp.address1,
emp.address2, emp.city, emp.state, emp.zip, emp.phone,
Sales = SUM(Select ...
0
votes
3answers
94 views
How did you split strings before string_split()
I have some strings I need to split on a SQL Server 2014 instance.
I would normally do it something like this:
declare @bk nvarchar(1000)
set @bk = 'SIS5.0~person_id~696969'
select value from ...
0
votes
1answer
31 views
Copy last modified files from network path into SQL server box and rename them using Agent job
I have to copy latest backup from a network path and bring that to my SQL server physical drive for restore, I have to do that using agent job.
This is what i am doing.
Created a SQL server agent ...
0
votes
1answer
42 views
How to create a master key on the master database?What are the roles required to GRANT to the login and user of the master db for the same?
I am trying to create a master key on a master database of my azure sql datawarehouse. But my user does not have required permissions to do so. What are the roles need to be assigned/granted to the ...
0
votes
0answers
17 views
Is NOLOCK Select able to create incomplete rows [duplicate]
I'm hunting a bug in our software and wonder if a Select using NOLOCK could give incomplete rows.
Let's say my table has a row like
A A A A A A ...
and while selecting with NOLOCK an insert with
B ...
2
votes
2answers
69 views
How to check AUTO_CREATE_STATISTICS/AUTO_UPDATE_STATISTICS is enabled for database
I have a project database that maintain different databases from different environnement (dev, test, prod, predprod...), I need to check if AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICSis set to ...
0
votes
1answer
31 views
current_timestamp at time zone 'UTC' SQL Server equivalent
What is the equivalent of the Postgres "current_timestamp at time zone 'UTC'" in SQL Server?
How do I select the current timestamp in UTC in SQL Server?
-1
votes
1answer
42 views
Is there a good SSMS T-SQL code clean up / formatter plugin? [closed]
I'm looking for a good plugin that will automatically do things like upper case all keywords, indent lines appropriately, maybe brackets around reserved words being used as names / aliases, etc.
2
votes
1answer
399 views
How to force a sysadmin to store data in schema other than dbo? [duplicate]
I am using a GUI tool to upload data in my SQL Server 2016 database.
The user being used for above task has been granted sysadmin permission
I created a dedicated schema to store above data. This ...
-2
votes
1answer
76 views
Three updates in order in the same script won't work as intended, why?
I have a table with a field called gender. It's an int. 1 = male, 2 = female.
Here's what was done, in the same script
UPDATE table
SET GENDER = 9
WHERE GENDER = 1
AND (other criteria);
UPDATE ...
2
votes
2answers
58 views
Is it possible through only T-SQL to physically move the data and log files of a database from one drive to another drive (on the same server)?
I know you can alter the database to update their data and log file paths in the database properties, but is there also a way to script out the actual physical move of the files themselves?...maybe ...
0
votes
1answer
62 views
Where to put single quotes?
I am trying to execute the below statement which creates a database called service2019:
DECLARE @dbname VARCHAR(50);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));
EXEC (
'CREATE ...
1
vote
3answers
69 views
REPLICATE() return type for VARBINARY input
REPLICATE ( string_expression ,integer_expression )
As documentation says, it
returns the same type as string_expression.
I see that return type is always VARCHAR.
declare @vb varbinary(10) = ...
6
votes
1answer
197 views
Sometimes the updated rows are not locked within instead of update trigger
Here is as small repro:
create table dbo.t (id int primary key, v int);
insert into dbo.t values (1, 1), (2, 2);
create table dbo.s (id int primary key, v int);
insert into dbo.s values (1, 10);
go
...
3
votes
1answer
64 views
Is @@ROWCOUNT preserved by EXECUTE or not?
The docs for @@ROWCOUNT say:
EXECUTE statements preserve the previous @@ROWCOUNT.
I take this to mean that @@ROWCOUNT should have the same value after an EXECUTE statement that it had before.
...
0
votes
1answer
33 views
SQL Server Linked Server - Timeout Error
I am trying to create a linked server between two SQL Servers (ServerA and ServerB). ServerA is hosted by Windows Server and ServerB is hosted on a local Windows desktop. I want to create a linked ...
2
votes
2answers
85 views
SQL Server - Select where using split - without declaring function
SQL Server. Compatibility level 120.
I have a table where one VARCHAR column contains semicolon-separated integral values. I would like to SELECT against the values in this column as if they existed ...
-1
votes
2answers
74 views
GETDATE( ) in columns in T-SQL
Is there a way to create a table in which the DATE column updated every date with the current date?
I want a column that every time I select gives me the current date.
GETDATE() function gives the ...
1
vote
2answers
82 views
Multiple instance of Update Query is causing very high Lck_M_U wait thus high CPU
My investigation found the following query is causing high Lck_M_U wait. I noticed there are multiple instance of this query running obviously competing for the same resource. I wonder if there is ...
2
votes
2answers
131 views
Moving SQL Server Database Files to New Location - Why permission error?
I was trying to move a database (.mdf and .ldf files) to a different directory on the same server hosting the SQL server. I followed these steps found in this Q&A:
ALTER DATABASE MyDB SET ...
2
votes
2answers
130 views
Bulk insert from a complex text file into a table
I want to use bulk insert to load a text file into a table, but my coding is only able to load half of the text file because my text file has two parts:
(ID, CompanyName and Date) as one part; and
(...
2
votes
3answers
245 views
Operator used tempdb to spill data . The estimated row are less than real rows
I have a query that takes around 32 seconds, I tried many ways to fix the performance problem.
The first thing I tried is adding a new column that contain date and time as an integer. After I added ...
3
votes
2answers
229 views
Rolling back truncate table
I have a stored procedure that does the following:
BEGIN TRANSACTION
-- Code to delete updated records from production (dbo) table
DELETE FROM [dbo].[factMyTable]
WHERE exists (SELECT *
...
1
vote
1answer
44 views
Union from Multiple Tables in many databases and group results in batches of n
First post in dba, so please be gentle.
I have a project I am working through and have 4+ SQL Server databases to work with.
These DBs contain 2 (or more tables) I wish to UNION the results from.
...
-1
votes
1answer
28 views
Create a CASE in WHERE which depends on a parameter
DECLARE @MyParam bit = 1
SELECT * FROM MyTable mt
WHERE
CASE WHEN @MyParam = 1 THEN mt.MyFK IS NULL
CASE WHEN @MyParam = 0 THEN mt.MyFK IS NOT NULL
END
How to write a query to filter where ...
3
votes
3answers
163 views
Only return recordset(s) from Stored Procedure if it has rows
I have a stored procedure that returns multiple recordsets for use in an application. Sometimes some of those recordsets are empty.
I'd like to reduce overhead and only return those which have 1 or ...
0
votes
1answer
79 views
Invalid length parameter passed to the LEFT or SUBSTRING function when inserting data into a table that has computed columns
I have a process that needs to split a string of key-value pairs. To do so, I'm using a combination of a temp table that has computed columns to calculate the key and the value from each key-value ...
-2
votes
1answer
46 views
Modification in the SQL script
I want sysadmin logins which are not listed in the exceptionssysadmin table.
I'm receiving empty columns with the below script.
can someone suggest where am i going wrong ?
The below is the script
...
0
votes
0answers
38 views
Visualization Of Graph Data With Spital Drawing SSMS
I want to create a visualization of my graph database utilizing Spatial Drawing within SSMS. How do we connect the various polygons with node names and to from pointers? The layout should preferably ...
1
vote
0answers
28 views
Capture trigger update() with inserted & Deleted changes only
I have price table that has over 100 columns.
I'm in the process of fine tunning triggers and reducing amounts of records inserted, to audit changes when updates are applied.
The audit table ...
-2
votes
1answer
60 views
Calculate nth business day of the current month
Looking for a simple inline statement (MSSQL) that can return the current workday number of the month (number of days elapsed minus Saturday and Sunday) E.g. NOV 11 2019 would be the 7th working day.
-1
votes
1answer
124 views
How to find difference between two columns in one table across the rows (T-SQL)?
I have one table called table1. This table has three columns column1 and column2 and dates_tart. Columns column1,column2 has same date type (int) and date_start has date type (date). For example, I'm ...
