Questions tagged [t-sql]

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

207 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
0
votes
0answers
52 views

Executing IF Exists condition

I have a sproc that scripts out permissions and in the process, I add a check to see if the object exists before dumping the results into a table. Then, I have another re-assign permissions sproc that ...
0
votes
0answers
68 views

Extracting Service name from stored procedure

I have the following procedure USE ServiceBrokerTest; GO CREATE PROCEDURE PublishNotification @NotificationData VARCHAR(1000) AS DECLARE @InitDlgHandle UNIQUEIDENTIFIER BEGIN ...
0
votes
0answers
86 views

How to execute a large script on each database in an instance?

How can I execute a large (>40k char) block of T-SQL on each database in a MSSQL instance, considering the following have character limits which would truncate the command passed in a variable, and I ...
0
votes
0answers
66 views

How to use SUM window function on a column that requires data conversion (and filtering away non convertible numbers)?

I am trying to aggregate a subset of values from colB (nvarchar) by filtering out all non-convertible values from this column first. I'm using a CTE along the lines of: ;with cte as ( select ...
0
votes
0answers
172 views

My most recent expensive query is sp_procedure_param_rowset

I'm stress testing our software running many transactions and reports at the same time on one same DB. analyzing the most recent expensive queries, I found that the top query is the ...
0
votes
0answers
103 views

Correlated Update with Join Order by

I'm trying to update a table with records in another table. My problem is that when there are duplicates returned the values of the first record get used to update. update table1 set ...
0
votes
0answers
526 views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. not consistent

We have seen this problem before and fixed it by looking at the KERBOS configuration program. But now I have something odd. Both I and another DBA have the same SysAdmin perms to both boxes. But when ...
0
votes
2answers
50 views

MS SQL 2012 - Need to split records from 4 different fields then query those

So I have a table called 'WORK_ORDER' and the ID is a unique text example '16-0833' next would be 0834, 0835, etc. In that table there are 4 user fields.. USER_1, USER_2, USER_3, USER_4.. This top ...
0
votes
0answers
30 views

UI freezes when at the particular task and only when accessing from terminal machine

For some reason User Interface freezes for 7-12 seconds when double click to open a PolicyNumber to display details. And its only happens when accessing the application from the terminal machine. ...
0
votes
0answers
29 views

Identify related fields inside one column based on position pattern

I have a table (imported from excel) similar to this In order to do subsequent manipulation of the data I would like to add a prefix like this Can it be done via some SQL trick, or do I have to use ...
0
votes
1answer
67 views

Modify SQL Query Text

How can I make below query work for virtual attached HDD. As of now this query return only C drive even though I have other drives also. SELECT DISTINCT dovs.volume_mount_point AS Drive, CONVERT(...
0
votes
0answers
1k views

Best way to avoid multiple joins

I have a view which determines the type of a transaction and whether it has been successful... ALTER VIEW [dbo].[vw_payment] AS SELECT t.*, (CASE WHEN (ht.Status = ...
0
votes
0answers
41 views

Ensure Unique Key from Multiple Tables

I have 2 tables that are used in a view. If the data does not exist in List tbl then use TaxRiskList instead. However, TaxRiskListID has a value of 1, which is also a value in VendorID in List tbl. ...
0
votes
1answer
189 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 ...
0
votes
1answer
109 views

How to use reference to another table in aggregate function

I have following easy-to-explain task: we have a table, and we should calculate total count of rows grouped by columns. Column number is fixed so we write: SELECT [GroupId] = cr.[GroupId], [...
0
votes
0answers
109 views

Filter hierarchical groups

Assume we have following table Id | ParentId | Timestamp | Status We also have another table, for each Status it shows if document can be deleted or not. This table produces several trees (parent ...
0
votes
0answers
122 views

Calculating a Running Value

I have a column (DividendReinvested) that is initially NULL but gets calculated based off the previous DividendReinvested column. The UniqueClassRN column is partitioned by ClassId. So how do I ...
0
votes
1answer
66 views

Query to Find Out How Many Days Until Database Is Full

Using my central MDW database I am trying to make a query that will be able to calculate the number of days until all my databases free space will be full. Currently the query I have is overly ...
0
votes
0answers
258 views

NEXT VALUE FOR SEQUENCE inside a CASE Statement

I want to use a new SEQUENCE each year (See here: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year ) However you can not use NEXT VALUE FOR inside a CASE statement. ...
0
votes
1answer
4k views

Adding a domain user in SQL Server Management Studio

Before I ask my question, let me mention that my SQL Server is installed on a member server of a domain (it belongs to the domain but is not an AD server). I am having trouble adding a network user ...
0
votes
0answers
163 views

The best way to raise error from mass insert query

I have a following stored procedure: CREATE TABLE Table1 (val INT) GO CREATE TABLE Table2 (val INT) GO ALTER TABLE Table1 ADD CONSTRAINT chk1 CHECK (val > 0 ) GO INSERT INTO Table2 SELECT 1 UNION ...
0
votes
0answers
62 views

How do you use Extended Events to find port numbers?

I'm migrating some TSQL code from one server to another. The server I'm moving it from is production, so I'm not easily able to adjust settings for obvious reasons. The code runs fine on the ...
0
votes
1answer
80 views

Finding keywords in SQL script

I have a list of 50 columns in a database that are likely to change or be removed within the next few months. I have a task to identify any SQL scripts that reference these tables. Up until now, I ...
0
votes
1answer
62 views

Sub Query vs Table Filter

I have have been assigned to resolve Performance issue of a Stored Procedure. Select TableTwo.Column1, DatabaseTwo..TableThree.Column2, Sum(TableTwo.Column3) As Column3 --- Section 1 from ...
0
votes
0answers
164 views

Recovered database files can't be attached

I have recovered my hard drive with my databases mdf and ldf files. When I want to attach them I get a 9004 error: Error: 9004, Severity: 21, State: 1. An error occurred while processing the log ...
0
votes
0answers
107 views

Rounding Casted Converting Error

The database I am working with has ntext datatypes which according to my understanding is evil. I used CAST to varchar to fix the issues in the below trimmed SQL Server query. Anyway, my question is ...
0
votes
0answers
889 views

Export a varbinary column to file system using existing authentication

I want to export a varbinary column to a file system. I can use BCP command to export data from a varbinary column, which requires username and password being sent along with BCP command. I can use ...
0
votes
0answers
1k views

Run a specific SQL Server job step from a stored procedure

I have a job with 5 steps that are scheduled to run after each other. I want to have a stored procedure where a user can execute the stored procedure and give the step number as a parameter. Only ...
0
votes
0answers
57 views

SSRS using select from (Select)

I want to report a script which contains a Select from (Select), when I just run the second select I have no problem but when I put the whole script it loads and nothing happens. Here's my code : ...
0
votes
0answers
60 views

select random tuples but prefer old dates

With this table: CREATE TABLE T_TEST ( id int identity, somevalue varchar(256), lastupdate smalldatetime ); i want to select n random tuples which age is older than x days but prefer old tuples (...
0
votes
0answers
549 views

SA Admin SQL-Server 2012 x64 keeps changing constantly

I know this is a newbie question, but really I can't find some precise info on this issue. I'm trying to figure out how to create a new user with admin server role on SQL Server 2012 x64. I mean, I ...
0
votes
0answers
28 views

Curious Regarding filtering behaviour and performance of where condition

So i have completed the clean up of SQL code that involves four set of queries that partially do the same filtering in where condition , while the rest of the part do their own task specific to each ...
0
votes
0answers
3k views

Row_Number() not working with OVER PARTITION BY

I have a query that I am "rolling up values". Lets say I have two rows that are the "same", save for one field: [CNumber] [PNumber] [date] [FullName] [Type] [P] [S] [...
0
votes
0answers
364 views

Getting Request Timeout from stored procedure?

Only sometimes, I get request timeout (means my stored procedure is not responding within a specified time-period which is 90 seconds). Here is my stored procedure: ALTER PROCEDURE [dbo].[...
0
votes
1answer
184 views

T-SQL Merge not updating in explicit transaction

We have the following code in a stored procedure which has a merge statement and an update statement to a different table in an explicit transaction. The merge update does not update any data in the ...
0
votes
1answer
180 views

List of queries run

I'm looking for a DMV query for SQL Server 2008 R2 which shows me all queries (full history) run by a specific user. I have this code for now: SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY ...
0
votes
1answer
575 views

Stored Procedure Resultset Row Count and Resultset Size in Bytes/KBs

Is there any way to get total row count and size of result-set generated by a stored procedure execution? @@ROWCOUNT is a good option if its already added in each stored procedure but I just want ...
0
votes
1answer
89 views

detect what columns to select depending on a column value of the same table

I am trying to build a query which returns different columns of the table depending on a column of the same table. Say, a column represents a type of item. This is my temporary solution, in which I ...
0
votes
1answer
101 views

databases cross references baselines

We have on several databases on same instances, some stored procedures have references to tables on other databases. We are in the middle of a big project to take our database under source control. ...
-1
votes
1answer
70 views

How to kill agent job if it does not finish after x min

I want to create an agent job and collect some data on my server, e.g. every 10 minutes. But I would like to kill the job if it does not finish within a specified time period, e.g. after 5 min. I have ...
-1
votes
1answer
110 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 ...
-1
votes
1answer
39 views

how do i reference a cursor defined in a database via another database's stored procedure

I have a cursor as part of a process that removes the files of a specified database file group. I want to code this in a stored procedure that will exist in one database and reference another ...
-1
votes
1answer
394 views

Lock differences between Insert Into vs Select Into while using Openquery

Using SQL Server 2014. My DBA said I should be using Insert Into instead of Select Into when using Openquery because Select Into will create a lock on the linked server table. Is this true? linked ...
-1
votes
1answer
927 views

How to change the default database for login in T-SQL

I was trying to change the default database for logins in the sql server instance to the database they are mapped to. Currently, the default database of all logins in master database. Alter login ...
-1
votes
1answer
137 views

Separating Numeric Values from string

I've looked at this link, but it does not really answer my question. Developing a Function for this would be ideal. I developed a trigger to insert the following into a table using one column: ...
-1
votes
1answer
103 views

Does Always Encrypted cause blocking queries or locking object?

I have perform some test with relatively small tables and the encryption took a very long time (maybe because there are many SQL objects that are referring the target table). I am wondering(worrying)...
-1
votes
2answers
2k views

sql query the historical data

I have designed an sql database to keep historical data of changes. I am tracking either full copy of my object or the change since the last version (very similar to incremental changes). I need to ...
-1
votes
1answer
308 views

T-Sql - Issue setting SQL Agent Database Mail

So, for SQL 2008 R2 or below, when looking in: SQL Server Agent Properties Alert System Mail System I see SQLMail (Urrggghh) and Database Mail (hurrah) When I use msdb.dbo....
-1
votes
1answer
331 views

Is there a way to create a stored procedure in a database when you don't have permissions?

From a security perspective, I'm curious to know if there is a way to create a stored procedure without permissions? For example, the following error is returned when tried "CREATE PROCEDURE ...
-1
votes
1answer
1k views

Impact of WHERE Clause on Table-Valued Function and query performance

I have taken over a legacy application that has had many performance issues. One I found is on a table valued function. The TVF runs fine as a basic SELECT. However, it is incredibly slow when it has ...