Questions tagged [t-sql]

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

208 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
0answers
625 views

User-Defined Table Type script with constraints (SSMS 2016)

How can one script a User-Defined Table Type with default constraints in SQL Server Management Studio (SSMS) 2016? I can generate a script, but it does not include these constraints. One cannot ...
1
vote
0answers
76 views

How to check regex syntax on insert?

Problem: I have an Application (in C#) that uses a regex from a SQL Table Column to check certain things. The details of that don't really matter for the question. The thing is: "Key" users can make ...
1
vote
0answers
61 views

Are there any pitfalls to be aware of when using dynamic type-coercion in SQL Server?

I want to insert into a database with a query that looks something like the following: INSERT INTO <table name> (<set of column names>) VALUES (<set of corresponding values>); As I ...
1
vote
0answers
1k views

SSRS dataset query not ordering by properly

I have a dataset text query and I would like to have an ORDER BY in it. The issue is in SSRS, the ORDER BY is not working as desired. However, when I run the query in SSMS, the output is ordered as I ...
1
vote
2answers
45 views

Grouping data to move up and down in a Table depending on Month day

I am using TSQL. I have a set of data in a table. I have a stored procedure that runs every day that populates this table. The thing is I need the order of the data to change each day of the month. ...
1
vote
1answer
64 views

Combining multiple-row data into a single row

I have a table that has columns like following ID Number Value1 User1 Value2 User2 Value3 User3 -- ------ ------ ----- ------ ----- ------ ------ 1 123 0 Jack 1 123 ...
1
vote
2answers
346 views

Split databases into groups of roughly equal datasize

I am trying to split all databases from sys.databases into 7 roughly equally sized Groups based on data ROWS size. I want a table that contains every database and these databases are in 7 different ...
1
vote
0answers
771 views

Resolve "stuck" system process of dropping indexes

I am re-vamping some indexes, and have decided to drop 3 on a certain table. The process has been running for almost 2 hours now. The table does have quite a few million rows, but I still feel that is ...
1
vote
0answers
122 views

Matching table (with sub-tables) to another table(with sub-tables) efficiently

I have a problem in an ETL process. I need to integrate data into a data warehouse. The data is associated with a "variant". A variant is defined as having a specific set of parameters (identified by ...
1
vote
0answers
573 views

How to properly re-run a deadlocked trasaction in TSQL

In TSQL (SQL Server 2012), is this a proper way of re-running a transaction that was killed due to deadlocked - it was chosen as the deadlock victim. EX: "Transaction (Process ID 118) was deadlocked ...
1
vote
0answers
2k views

xp_delete_file not working

I am running a query window under my own account and the following command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\backup\sql', 'bak', '2015-11-24T23:30:02' ...
1
vote
0answers
456 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...
1
vote
0answers
71 views

Migration script fail and revert on first error

I have a db migration script about 4000 thousand lines. I would like to compose it when it reverts all changes on first error. What is the proper way to do that? Is wraping with try/catch is the ...
1
vote
0answers
89 views

Changing Action on Delete to Cascade

We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table. We now have ...
1
vote
0answers
39 views

Snapshot replication error two days in a row

We have a mature replication system to move data from OLTP to BI. It's been working for years with relatively few troubles. This week we've run into the same problem two days in a row with snapshot ...
1
vote
0answers
565 views

Unable to insert explicit value in table due to IDENTITY_INSERT is ON Error

I am trying to insert explicit values in one of my Real time Audit Production database. There are more than 80 columns, I have just taken here 5 columns. I am writing syntax is as below: use ...
1
vote
0answers
946 views

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= DATEADD(...
1
vote
0answers
64 views

Can I Parse and/or include "And" in my SQL Statement for a full text index?

So I have a query that looks at a full text index like this: SELECT * FROM Table1 q INNER JOIN CONTAINSTABLE(Table1,TableField, @Keywords) search ON q.Key = search.[KEY] Now @Keywords can ...
1
vote
2answers
77 views

Get time elapsed between log entries

At a company, fluids are prepared and held in tanks, following a preparation/recipe. The preparations move from tank to tank depending on needs. The tasks are logged in sequence into a table. What I ...
1
vote
0answers
661 views

Using loops inside Openrowset

I'm trying to execute a query across around 200 servers using openrowset function. This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in ...
1
vote
0answers
50 views

T-SQL stored procedure: granting general user access to specific stored procedure

I'm attempting to alter a stored procedure that needs the line added: grant exec on dbo.sproc_name to exec_proc I believe the admin setup the exec_proc as a group. I added this grant line following ...
1
vote
0answers
90 views

Nonclustered indexes

I have a question on index Our Application is throwing the following SQL error Index 'INDEXNAME' on table 'dbo.TABLENAME' (specified in the FROM clause) does not exist. Error: Unknown Error ...
1
vote
0answers
227 views

Export hierarchy tree in a specific way

I need a big help. I have two tables (SubCells and Codes) and Visually it looks like this (We can count an order) And i need to get this final result (with SQL or even a C#) How can i do that ? I ...
1
vote
0answers
1k views

Exporting to excel from OPENROWSET with cell formatting

I am trying to generate a report from SSMS to Excel, using OpenRowSet. All is working fine, still I have to export a specific column with a barcode font. I already have the column calculated from ...
1
vote
0answers
2k views

How to check if all the extendend stored procedures (xp_) are enabled or disabled?

These are the stored procedures that I want to check if they are enabled or disabled. xp_availablemedia xp_cmdshell xp_deletemail xp_dirtree xp_dropwebtask xp_enumerrorlogs xp_enumgroups ...
1
vote
0answers
5k views

incorrect value from SUM Function

Has anyone seen this before? When doing a simple sum on a table: select sum(column) from table where column is not null I'm receiving the data 1558.39999999999 However, investigation of the data ...
1
vote
0answers
856 views

SQL command in stored procedure continue on error

To perform Checkdb on all database of server i am using a stored procedure which loops over a view which contains list of databases in server and the stored procedure performs checkdb on all databases ...
1
vote
0answers
656 views

Sql Execution Plan - Hash Match

I'm currently working on a Sql Query that's having performance issues. I ran the execution plan and saw the Hash Match has 50% cost and also, doing a index scan. I'm starting to learning about the ...
1
vote
1answer
70 views

Seemingly equivalent queries take vastly different execution times

I am encountering an issue where two different ways of executing the same query are taking very different execution times. My guess is that in one way or another, the slow query is unable to utilize ...
0
votes
1answer
23 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
50 views

How to retrieve min/max values and corresponding dates in a table

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value. Example Data: CREATE ...
0
votes
0answers
32 views

If I rename a table with dependencies, and then create an synonym of the old name pointing to the new table name will the dependencies still work?

In an attempt to remove some downtime to my users, I want to redirect them to a back up table while I'm repopulating the production table. The tables in question have been in place for years so my ...
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
0answers
42 views

SQL Filtering on multiple properties

I have a Database with a table Books: Id IsVisible Slug UserId Name CreationDate Description IsAdult BirthDate City Siren 1 1 julien 56D1C3BE-7674-4CF0-6940-08D9D09EC5A8 Julien 2022-01-05 22:57:39....
0
votes
0answers
34 views

SQL Update with sum, inner join, and group condition

I'm trying to update with some condition: UPDATE a SET a.FLEX_ERROR = CASE WHEN ((a.NUM = 1 OR A.CODE = 'D') AND SUM(a.POINT) <> b.TOTAL_VALUE) ...
0
votes
1answer
45 views

Do Temporary Tables cause stored procedure Recompilations?

I have read certain articles online that creating temporary tables in stored procedures will cause stored procedure recompilations. Could someone please explain what happens when a temporary table is ...
0
votes
0answers
54 views

Calculate Start and End of Fiscal Period Weeks

I have a calendar table established (thank you, Aaron Bertrand): SET NOCOUNT ON IF OBJECT_ID (N'dbo.Dim_Calendar', N'U') IS NOT NULL DROP TABLE dbo.Dim_Calendar SET DATEFIRST 7, LANGUAGE ...
0
votes
0answers
63 views

Unable to get FileStream Length with After Insert trigger

I asked this question on StackOverlow, but it mostly received comments about the use of variables and no answer. I program mainly in VB.Net, so my T-SQL knowledge is limited. The reason I'm populating ...
0
votes
0answers
31 views

tsql do update queries that join other tables lock the joined tables also?

Azure SQL Managed Instance. If I am updating a temp table, and joining to another table or tables, do the joined tables also get locked, even though the update statement is only updating the temp ...
0
votes
0answers
28 views

How to get all the extended properties inheritance and regarding this heritage apply the values of extended properties?

Hope you are well I'm been a bit stuck trying to develop the code to get the inheritance of extended properties. I will explain the situation: The thing is have a query to list all extended properties ...
0
votes
0answers
42 views

Creating sql server custom database role that only allows reading some columns and nothing else

I want to create an sql server Custom Database Role which only allows reading some columns of some tables, and nothing else. this is my attempt. CREATE ROLE CustomDatabaseRole01 GO GRANT ...
0
votes
1answer
37 views

Calling function that takes user defined table type as input, in a select query, with data in the table type from the table selected from

Hopefully the title makes some amount of sense. Basically I have a scalar function that takes a user defined table as input, f_doCalc(@MyUserDefinedTableType). The table type has 2 columns, theValue ...
0
votes
0answers
179 views

Permissions to run SQL Server Agent Job

I am getting the following error message: Message Executed as user: fin\jdoe. The server principal "fin\jdoe" is not able to access the database "abc" under the current security ...
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
129 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 @...
0
votes
0answers
31 views

Report server (SSRS) email delivery delayed for 10 hours

Weird thing happened the other day. Around 2 am there are lots of subscriptions fires on report server that sends emails. Users received some of them, but not all. Then around noon, all of the sudden ...
0
votes
0answers
44 views

Dynamic SQL and AO AG - SQL Server

I have 3 servers with an SQL Server instance (S1\PROD, S1\PROD, S3\PROD) in my cluster and I have a couple of DBs hosted on these with AG AO. I have a lot of DBs and they have 2 replicas (1 primary ...
0
votes
1answer
36 views

How can I apply the same logic to multiple tables that have the same number of columns and data types?

I have multiple tables with identical number of fields (and data types). Here are two examples (DDL to create these tables at the bottom): Animal AnimalGroup Name ----------- ---------...
0
votes
0answers
39 views

Understanding CPU time to measure workload

I want to understand the workload of my stored procedures, which run on an instance with many other databases. Among other things I would like to know if I had my own server how many CPU core would I ...
0
votes
1answer
52 views

Getting data from Postgres through Linked server of SQL

I am trying to get the data from PostgreSql db through linked server of the SQL Server. But getting the following error on running this query select * from openquery(SALESFORCE,'select closeddate from ...