Questions tagged [t-sql]

Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

Filter by
Sorted by
Tagged with
2
votes
2answers
38 views

Recursively get ids of tree

I have a table foo. So we have a hiearchical object structure where on foo has one parent (or none) and a list of children. What I want is a sql statement that lists all id:s that can be found either ...
4
votes
2answers
439 views

Fulltext search limiting users possibilities

We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure. CREATE PROCEDURE [dbo].[SearchPages] ...
0
votes
1answer
99 views

Conditional field compare based on the value of another field (Case statement?)

I have two tables Alerts(iscomplete, threshold, isup, coinid) Coins(coinid, LastPriceBtc) Coins table is updated regularly with prices (going up n down). I want to trigger alerts that meet the ...
-1
votes
1answer
59 views

SQL AVG() Returning the wrong result

I have a function and a stored procedure that used this function, when i run the function seratelty with the stored procedure i get the correct result but when i use this function with the stored ...
-1
votes
1answer
32 views

Complex SQL Agent Job

I would like to tun the Stored Procedures on the following dates. 1st sept 2020 1st Nov 2020 1st Dec 2020 1st Feb 2021 1st March 2021 1st May 2021 1st june 2021 1st august ...
0
votes
2answers
195 views

How to avoid conditional join in order to write a tsql view

I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables Roles table RoleName --------- Admin Viewer User Set table Id Name --- ---- 1 Employee Data 2 Help ...
-3
votes
0answers
33 views

Correct SQL query to compute customer support response time [closed]

Consider the following table: TABLE TicketMessage COLUMN TicketId INT COLUMN Sender VARCHAR (either 'customer' or 'staff') COLUMN SentAt DATETIME The goal of the query is to compute the average ...
2
votes
1answer
43 views

Space Calculation for SQL Server Table: What's wrong with my query?

I'm using the following query in order to calculate some space related measurements on a particular table in my SQL Server database: SELECT t.name AS TableName, p.rows, (sum(a....
2
votes
0answers
19 views

Validating SQL Server Change Tracking Synchronization

I have some tables that I need to synchronize from one SQL Server database to another. I'm using SQL Server's "Change Tracking" feature. My program uses Change Tracking to synchronize the ...
0
votes
1answer
30 views

SQL Server :: how to move country code column to another table?

I have 2 tables: tbl_Countries: 250 values, with columns like CountryName (Afghanistan), TwoCharCountryCode (AF), ThreeCharCountryCode (AFG). Like this: CountryName TwoCharCountryCode ...
0
votes
1answer
31 views

Joining Additional Table Column

I need to add another column from another table i.e. CusSorMaster+.SalesOrder.The join is on where dbo.SorMaster.SalesOrder = dbo.CusSorMaster+.SalesOrder. This wasn't written by me and I need to ...
2
votes
1answer
129 views

Assign a student to a Classroom based on course and scheduled hour

Each year we have to manual assign students to classes that were not included in original assignment either they are new or the class was cancelled etc, we are talking 100s of students where we have ...
-1
votes
1answer
27 views

TSQL query searching for multiple keywords across multiple fields

I am looking for assistance in improving the search capability of my webstore. I have tried multiple methods without success. I sell diecast cars, and am missing out opportunities of products being ...
0
votes
1answer
240 views

Security user audit

This has probably been asked a million times already and for this i apologise, but is there a way or any T-SQL I can run that will tell me, the server, the database, users login name, what permissions ...
0
votes
1answer
13 views

OPENROWSET Bulk insert Text File NULL Columns

I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the ...
2
votes
3answers
49 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?
2
votes
1answer
51 views

Populating bridge table with conditions [closed]

I have a bridge table trajectory_detail that I am supposed to populate with millions of records from the corresponding parent tables. The problem is that every value from parent table trajectory_id is ...
1
vote
1answer
30 views

Execute SSIS (package) in SSMS using T-SQL and return a value

Using VS (2019) I created an Integration Services Project to code an SSIS package. The goal was to call a Web API from an SSIS package using input variables like username/password/url and so on. I ...
0
votes
3answers
46 views

Simultaneous SQL full backups of newly created databases

I would like to take a backup of newly created databases but within a minute if the user creates three databases the script can able to take only one database but for one new database which was ...
15
votes
4answers
30k views

Can I change table structure in a transaction and then roll it back if there is an error?

I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements ...
0
votes
1answer
164 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
23 views

Query specific key fields from x-number of tables?

I'm building an insurance database and I need some help with best practice on fast quering data. Let's say I have three insurance types ... Car, Home and Travel. Common for all three types, I have the ...
1
vote
1answer
86 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 '...
-1
votes
3answers
21 views

Set The Order Query Results Display

I need to display my emailType in DESC order. Now it is a varchar field so I need it to be reverse alphabet order Z - A. When I run my query it is displayed in ASC order, and since I am using a ...
-3
votes
0answers
48 views

I wish to create a table using SQL that provides counts at multiple levels that include zeroes

I wish to create a table using SQL that would look somewhat like the following: County | Sex | Age Group | Ethnicity| Count -------------------------------------------------- County1 | Male | ...
3
votes
2answers
529 views

Numbered stored procedures: introduction and deprecation?

SQL Server and Sybase ASE both support the highly dubious feature of grouping stored procedures by appending a number: CREATE PROCEDURE Foo;1 AS ... CREATE PROCEDURE Foo;2 AS ... The only tangible ...
2
votes
2answers
75 views

Optimizing my query

Here's my query: DECLARE @monthStartDate date; DECLARE @monthEndDate date; SET @monthStartDate = DATEFROMPARTS(2020,6,1); SET @monthEndDate = EOMONTH(@monthStartDate); SELECT @...
0
votes
0answers
37 views

Index usage for database: where are the sys tables located

I have seen a couple of scripts that query some sys.someName tables to find usage of indexes in a database. My guess is that these tables are tied to a database e.g. there is a sys.indexes table in ...
12
votes
5answers
16k views

SQL Server 2012 is putting [brackets] around Table and Column Names

When you drag the Columns folder to editor Window, the full list of Columns are added without Brackets. If you drag columns individually they have Brackets. Is there anyway to turn that off? I'm ...
1
vote
2answers
387 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-...
0
votes
0answers
9 views

No Data for RING_BUFFER_SCHEDULER_MONITOR

I am attempting to add a query for monitor recent CPU usage for our SQL servers into an SSRS dashboard. I have encountered numerous examples online recommending querying the sys.dm_os_ring_buffers DMV,...
0
votes
1answer
23 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....
0
votes
1answer
84 views

Query distinct emails not logged in within a period of time

I need help in writing a query to return distinct emails that have not logged into ABC database in the past 5 months. The exact task I have been asked is as following: Create a table in ABC ...
3
votes
2answers
489 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
vote
2answers
547 views

Unique key constraint violation not escalated to parent stored procedure

I have a stored procedure which calls 2 nested sub procedures. For Example, MyProc BEGIN BEGIN TRY BEGIN TRAN EXEC UpdateProc EXEC InsertProc COMMIT TRAN END TRY ...
-1
votes
1answer
45 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] ...
1
vote
1answer
40 views

Group by every N records but remove incomplete group that has records less than N

I need to query a table to fetch data in groups of some size say N=5 but if the size is less than 5 then the last group should be ignored i.e every 5 records should be grouped ==> i.e first 5 ...
0
votes
1answer
38 views

“To many rows being processed at one. Reduce and try again.” error in SQL Server Management Studio

I get this error when trying to update some rows from SQL Server Management Studio v18.5: Msg 50000, Level 16, State 1, Procedure UPDATElimit, Line 7 [Batch Start Line 0] To many rows being processed ...
24
votes
6answers
34k views

Does SQL Server support GREATEST and LEAST, if not what is the common workaround?

Reviewing this question it seems like that's a lot of work that shouldn't be needed. They're trying to extend a range with a date. In other databases, you would just use greatest and least.. least(...
1
vote
1answer
273 views

Error passing variable table type to a stored procedure

I'm having a noob problem passing a data table type to a stored procedure. Here is the implementation : DECLARE @T TVARIABLE INSERT INTO @T VALUES ( '#var1','1') INSERT INTO @T VALUES ...
0
votes
1answer
66 views

Single user mode looses connection

So just a quick background, we are trying to update the database design, in a production environment. But we want to be sure, no users try to login during that time. So we started looking into single ...
1
vote
1answer
21 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 ...
9
votes
1answer
269 views

function to receive a character input and return date format (with incorrect input)

I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect ...
1
vote
1answer
30 views

Combine Rows with indirect relation

I am trying to create a report from a cloud based EHR so I cannot share real data and some of these tables are fairly massive. I will try to minimize and share the bare minimum and expand if someone ...
1
vote
1answer
56 views

Compatibility level of databases based on sql server version

We need to check for databases that are not in the correct compatibility level on a particular instance. For example, on a SQL Server 2016 instance, if there are databases that are not in ...
0
votes
1answer
64 views

TSQL SELECT statement to list a column calculated based on the value of a field

I have a table named Table1, with three fields Project ID, EmployeeID, Starting Date. Need a select statement to list all columns of the table, and an additional column to show the number of project ...
0
votes
1answer
39 views

SP is locking due to Begin Transaction

Please read the following code SET IMPLICIT_TRANSACTIONS ON BEGIN TRANSACTION DELETE FROM xxxx SELECT * FROM xxxxxxxx WHERE EXISTS ( SELECT * FROM xxxxx WHERE CONCAT(xxxxxxx) = ...
0
votes
1answer
174 views

Limit the number of results but show how much would be returned

I have a big problem in T-SQL with large amount of data where I need to limit number of returned rows to 200K but also to show the number of rows that would be returned. It is needed to be done under ...
1
vote
1answer
572 views

Lock a table for a CTAS upsert in Azure SQL DataWarehouse

I have a type 2 dimension in Azure SQL Data Warehouse. Essentially I am creating an interim table with CREATE TABLE myDimension_temp AS SELECT ... FROM myStagingTable; etc After the CTAS is ...
3
votes
1answer
264 views

User-defined function returns first character only although RETURN VARCHAR(10) is defined?

Everyone, I cannot figure out what is missing in the below user-defined function: CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money) RETURNS VARCHAR(10) AS BEGIN IF (@Salary < 30000) ...

1
2 3 4 5
53