Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,455
questions
0
votes
0answers
3 views
How Can I Evaluate a SQL Server Policy using T-SQL?
I have created a policy in SQL Server 2008 R2 and need it to be evaluated on demand
- whenever a new database is created.
In order to achieve that, I created a DDL trigger at server level, which ...
0
votes
0answers
17 views
Get Row Value by Column Name of Name at runtime
I currently have a stored procedure that obtains rows from an import table and joins another table which has a row for each column header of the import table.
I am able to query this but in order to ...
2
votes
0answers
22 views
Advantage of querying data outside a filtered index
Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index?
My playground:
Table with billion rows and 100 columns
Unique clustered ...
0
votes
1answer
21 views
Cross Database with Application Role
I have a requirement to access (read) a table/view in database B from database A. The application that needs to execute the query however uses an Application Role. From what I've read and tried, doing ...
1
vote
2answers
38 views
I need to reference the same two columns multiple times in where clause
I've got a table that stores survey question information and I'm looking to find out a count based on gender, race, etc My issue is that I have to reference the same columns multiple times in the ...
3
votes
2answers
53 views
Way to obtain full list of column dependencies for stored procedure?
I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a ...
-1
votes
2answers
50 views
Stored procedure result in HTML format
I require the changes in the below script so that we have to receive the results of the stored procedure sp_validatelogins as mail in HTML format.
The below is the script .
USE [test]
GO
ALTER PROC ...
0
votes
1answer
31 views
Excluding records from SQL Query
I have a table which I am trying to return only records of rendition_type Interactive and no other associated rendition_type. If a records has more than one rendition_type, I need to exclude it from ...
0
votes
1answer
15 views
Empty Strings, NULLs and For XML Path
I have no control over the source table.
There is a mixture of NULLs and Empty strings.
What I need is to get all users on 1 line and semicolon delimit a list of all their entitlements. I can strip ...
1
vote
2answers
38 views
Select All Records From Table That Do Not Exist In Secondary Table [duplicate]
This should be straight forward, but I am getting my tail kicked with the proper syntax.
I have this syntax
Select DISTINCT
bm.Location
,bm.[Beer (Upcoming Menu)]
FROM
...
2
votes
1answer
67 views
Query extremely slow when joining 2 views
I will preface this by saying i am not a DBA, just a dev that is trying to figure out performance problems with the following query we have.
SELECT
OCCoverID as StaffID, FullName, SUM(...
2
votes
2answers
103 views
Getting Msg 15151, Cannot alter the user 'xxxx', because it does not exist or you do not have permission
I have the following script and I keep getting errors when it is executed.
USE [master]
GO
If Not EXISTS (Select loginname from [master].[dbo].[syslogins]
Where name = 'xxxx' and dbname = 'xxxx-...
-1
votes
2answers
40 views
remove a step from sql agent jobs
I copied a bunch of jobs from an HA server to a regular server and I need to delete only the first step( checks for the primary) on these jobs not all jobs. Any suggestion?
this is the query I used to ...
1
vote
3answers
76 views
How to return the value from a query with SP_EXECUTESQL in a stored procedure
I have a stored procedure that generated a query string and then executes it with
EXECUTE SP_EXECUTESQL @Query
The query, e.g., is: Select Name, Age from tableA, so very simple.
But how to get the ...
0
votes
2answers
66 views
Help error message inside dynamic T-SQL
I have a requirement to come up with reporting for indexes for our whole production environment:
I found the below script online and modified it according to my requirement and trying to execute it ...
1
vote
1answer
70 views
Get max, min, avg value within each 1 day range in sql server [duplicate]
I want to select max, min, avg value within each 1 day range.
My Table looks like:
+-------+---------------------+
| Value | TimeStamp |
+-------+---------------------+
| 31.20 | 2019-04-...
0
votes
1answer
43 views
Mysql query to find last entry for each of the last 7 days
I have a table that contains a timestamp and a number for each row. This data is logged every 15mins on the quarter hour, 24 hours a day, but it does sometimes get missed or is delayed. I'd like to be ...
1
vote
2answers
40 views
Trying to trace error in Stored Procedure using XEvents
Here is the situation:
I have a SQL server ETL application with a stored procedure that is inserting/updating some data into a table. The table has 1 INT column (it is an identity column and there ...
2
votes
3answers
165 views
T-SQL Stored Procedure Returns Before Completing
We are running into a very curious error. We have two production SQL Servers. We call a stored procedure on one (call it Server_A) that calls a stored procedure on the other (Server_B).
The stored ...
1
vote
1answer
54 views
Should a DBA be able to write basic T-SQL? [closed]
I am a SQL Server database developer (not a DBA) and I find myself frustrated working with my company's new DBA. I'm trying to figure out whether my expectations are unreasonable.
For the sake of ...
43
votes
9answers
5k views
Asked to Not Use Transactions and to Use A Workaround to Simulate One
I've been developing T-SQL for several years and am always digging in further, continuing to learn all I can about all aspects of the language. I recently started working at a new company and have ...
1
vote
1answer
94 views
Why is my auto-restore procedure not dropping the databases when I expect it to?
I wrote a stored procedure to loop through my databases in a cursor, restore the most recent backup, run checkdb, drop the restored database, then move on to the next one. I have found that during the ...
0
votes
0answers
63 views
Variable Records
This question keeps coming up at work & collectively we can't think of an easier way to do it than a SQL pivot & huge case statement. So I'm hoping someone might have an idea that could help.
...
5
votes
2answers
710 views
Selecting 2 column in an Inner join
Below are my two tables :
I am trying to write a query that joins these 2 tables AND returns the Source_Title and Destination_Title for the given Source ID of Result Map table.
I have tried written ...
1
vote
0answers
17 views
Read all Captured Extended Events for a Given Extended Event Session Name
I want to be able to read all XE files for a dynamic XE session name. My XE session stores files to disk.
I have the query below
DECLARE @XeName SYSNAME = 'MyXE'
DECLARE @Path NVARCHAR(255)
SELECT @...
1
vote
1answer
95 views
using a join to find data in a second table? [closed]
I'm very new to SQL and don't really understand JOINS as much as I would like, or even if this would be a good case for a join.
I have two tables:
Table1
Headings:
"folder","OwnerNumber"
Data
"...
1
vote
1answer
73 views
filter a table based on a second table matching all criteria
Having trouble writing a query to return rows from one table matching rows in a second table. Simple joins I can pull in combinations, but how do I get only those rows that match ALL of the criteria?
...
1
vote
2answers
105 views
How to pass a table-valued parameter to sp_execute_external_script?
I have a stored procedure that calls an external script via a SQL Server language extension. I would like for my stored procedure to use a caller supplied table valued parameter (TVP) to then submit ...
3
votes
1answer
126 views
SUM Case Logic with Distinct
This is my first post, so please be gentle :)
I have a couple of queries:
SELECT COUNT(DISTINCT(h.USERID))
FROM MHGROUP.USERHISTORY h with (NOLOCK)
JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID ...
1
vote
1answer
28 views
Always Encrypted Data Validation [closed]
Just a question in how could i validate duplicate data's in my database if columns is set to be encrypted(deterministic) in db?
I've tried running the following code, but receive the error below.
...
0
votes
1answer
71 views
Deadlock on partitioned table
we see deadlocks when loading a partitioned table. I wonder if someone can confirm what I think is the reason, or if I am looking at it wrong and we need to fix another thing.
Our setup is like this: ...
-4
votes
1answer
91 views
Earliest version of SQL Server with OPENJSON
What is the earliest Microsoft SQL-Server version having the T-SQL OPENJSON function?
As far as I could see before asking this question, the Microsoft manual shows the function's documentation for ...
7
votes
1answer
154 views
Inserting with implicit type conversion causes warning for cardinality estimates
I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:
Type ...
0
votes
0answers
52 views
Get the scope role for each employee
I have the following query, for each employee I get the list of his different roles :
SELECT e.EmployeeId,r.EntityRoleId, r.Label,er.ValueId
FROM Employee e
LEFT JOIN Employee_Role er ON e.EmployeeId ...
-1
votes
1answer
47 views
Creating database with only ldf file created 2 .ldf file
When I run this query without specifying the ldf file it automatically creates the ldf file.
Declare @DBname nvarchar(50) = 'Test'
Declare @path nvarchar(50) = 'D:\DB'
Declare @query nvarchar(max)
...
4
votes
1answer
68 views
How to recreate an Index with the same settings?
I'm in a situation where I want to add another included column to an existing index, but keep the rest of the settings (eg. FILLFACTOR, ONLINE, SORT_IN_TEMPDB, etc.) the same.
I do not have the ...
1
vote
2answers
44 views
Microsoft SQL Joins and Where Clause
I am sure a very simple question for people out there but my brain has shutdown.
I have joined two tables with a left join which has worked as hoped as I do need to bring back the NULLs in one of ...
1
vote
2answers
51 views
Joining unrelated data
I have two tables. Each table has a single column with 2 records. How would I go about merging the columns from both tables into another?
For instance, the first table has records 1 and 2, and the ...
0
votes
2answers
61 views
TSQL script returning wrong result
I'm trying to find out if a particular index name "Test" exists in all databases using below code. The result I'm getting is always "NOT EXIST" for all databases in an instance. However, the index ...
0
votes
2answers
36 views
How can I update the roles for all logins using T-SQL?
I need to update the roles for all database logins, but without using SQL Server Management Studio. I can update a single user with the following example SQL.
EXECUTE sys.sp_addsrvrolemember
@...
0
votes
1answer
36 views
Command runs fine in elevated command prompt, but fails when run in a Windows Task Scheduler task
Here's my predicament:
The version on the server is SQL Server Express. And no, we cannot use any other version. (Long story, but: vendor will not allow without $ome...)
From 1, you can deduct that ...
0
votes
1answer
30 views
declare table variable inside function
In my stored procedure, I have table variable with a lot of columns. Because I want to simplify the stored procedure I thought i could declare table variable in function like
CREATE FUNCTION [dbo].[...
6
votes
2answers
780 views
Infinite loop in CURSOR
I'm trying to use a cursor to clean up temp tables when they are no longer needed. I have a small table which has the names of the temp tables along with an identifier. The cursor is stuck in an ...
4
votes
1answer
114 views
Get the full text of a long request
How to get the full text of the request from the following request:
SELECT t.[text]
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
Here, with a sufficiently long ...
1
vote
3answers
137 views
Slow query in production, bad execution plan or bad index?
I've just fixed a production performance issue by dropping an index and recreate it.
I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad.
...
0
votes
1answer
64 views
Two conversions of practically the same number giving two different results
To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle...
This
Select convert(numeric(19,2),convert(float,9....
1
vote
4answers
145 views
remove leading and trailing quotes
I have a field that may start and end with quotes. I only want to remove the quotes if the string starts and ends with quotes. If quotes exists inside the string then I wish to retain them.
I have ...
1
vote
2answers
47 views
Equivalent of Oracle RR date format in SQL Server
Having a bunch of SQL insert scripts generated in Oracle, I have the requirement to adjust them to be usable with T-SQL in Microsoft SQL Server.
The Oracle export has date formats like this:
to_date(...
1
vote
2answers
40 views
SQL Server Update Statement joining on index column
Using SQL Server 2012, I have a stored procedure that updates data by doing a delete and insert similar to this:
DELETE FROM MyTable
WHERE EXISTS ( SELECT 1
FROM ...
0
votes
1answer
49 views
Casting fraction number to decimal throws Arithmetic overflow error
I have a table called AssignmentMarks that stores Student's assignment marks for different subjects.
This table has a column called Marks varchar(10) which stores the marks. The marks can be like 1, ...
