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

learn more… | top users | synonyms (1)

1
vote
0answers
19 views

Performance issue with larger resultsets SQL Server

I currently have a stored procedure in MSSQL where I execute a SELECT statement multiple times based on the variables I pass to the stored procedure. The stored procedure counts how many records are ...
0
votes
1answer
19 views

differences between SPID and session_id

I'm configuring a job to get the SPID of an offending process and kill it. I'm using a mix between the great sp_Whoiscative from @AdamMachanic and joining with sysprocesses on login name and SPID and ...
0
votes
0answers
17 views

login failure for sa to unspecified DB

I have a pair of SQL Server 2008 R2 DB mirrored and I'm seeing a login failure approximately every one minute. [417593] MSSQLSERVER Type: FAILURE AUDIT Computer: EVS-DBSERVER-2 Time: ...
1
vote
2answers
31 views

SSMS clean output window with sql instruction

How can i clean output window in SSMS using t-sql syntax? Is it possible ? Do you know any workarounds ?
0
votes
1answer
36 views

OVER and PARTITION BY for db size change

I have the following column below. I am not too familiar with the OVER function. Question is, will the column usage_change_mb be the daily change (from daily snapshots) or an average change of all the ...
0
votes
0answers
15 views

t-sql errors during OPENROWSET(BULK… possibly related to terminators

I have a CSV file and I am trying to use OPENROWSET(BULK... on it with a non-xml format file. I am receiving errors which I have been unable to overcome. They lead me to believe that my field or row ...
1
vote
2answers
26 views

How can I perform a full-text CONTAINS call across a list of phrases?

I have a list of words/phrases in a table phrases. I have another table with a full-text catalog indexing the description column. I want to populate a table results with a record for each phrase, and ...
0
votes
1answer
26 views

Querying SQL Server extended properties for tables/views and its columns

I need to get metadata information about tables/views and its columns in SQL Server. When I run the query separatelly for tables/views (sys.tables) or columns (sys.columns) I can get the extended ...
3
votes
1answer
35 views

How to migrate a huge application to CONCAT_NULL_YIELDS_NULL ON

Finally, none of our customers are using SQL Server 2005 anymore, so we can drop support for it and start using all those awesome "new" SQL Server 2008 features (indexed views, filtered indexes, query ...
2
votes
2answers
145 views
+50

Why the same execution plan causes LOB reads in some cases?

I have a spatial index and the following query: SELECT DS2.[ID] ,DS2.[InstanceID] ,DS2.[Location].STDistance(@Location) FROM [dbo].[DataSource01] DS1 INNER JOIN [dbo].[DataSource02] DS2 ...
1
vote
2answers
49 views

Сopy rows from one table to another and replace duplicates in the process

I have an SSIS job that loads daily data from CSV files into a raw staging table in a SQL Server database (technically is an Azure SQL database). Then after that, a store procedure is executed to copy ...
2
votes
1answer
47 views

Make index work on join to table variable

I a table with several columns, but the most importants are: DocumentId(bigint), StatusId(int) and ReceiveDateTime(datetimeoffset(3)). CREATE TABLE [DocumentInfo]( [DocumentId] [bigint] NOT NULL, ...
0
votes
2answers
34 views

Need to create a “summary grid” query?

I have multiple transaction records in table A like this: TXN# DATE CUSTOMER 1001 10/05/2015 Cust 1 1002 02/03/2016 Cust 2 1003 02/06/2016 Cust 1 1004 03/25/2016 Cust 3 1005 03/26/...
0
votes
1answer
30 views

Select 1 Row Per Day For Each Database

I have a table with over 60 million rows. Snapshots taken at multiple times a day for all databases across all instances. I am trying to reduce the rows in my select by only returning 1 snapshot per ...
0
votes
0answers
29 views

concatenate/using string doesn't work while query for clearing table [closed]

My boss required me to write a query that clear tables... and i searched and found some code, put them together, at the end, for simplify and remove any mistake, put the code in variables... but it ...
0
votes
0answers
48 views

Cannot execute code connecting to SQL Server 2008 R2 in SQL Server 2014 SSMS

I am not able to connect and execute a code in SQL Server 2008 R2 while connected to SQL Server 2014 SSMS. SQL Server 2008 R2 is on Windows 2008 R2 64 Bit SP2 (Stand Alone) SQL Server 2014 is on ...
0
votes
1answer
45 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 ...
1
vote
2answers
143 views

PERCENT_RANK does not distribute over 100

I'm trying to find out the position of each given record of a query in 0-100 scale. I use PERCENT_RANK ranking function this way: select Term, Frequency, percent_rank() over (order by Frequency desc) ...
1
vote
6answers
434 views

Combining (joining) tables with no key

I have table B and I have Table A. Table B contains records with dates I need to pull. However, I only want to pull them if: They do not exist at all in table A They exist in table A, but only when ...
0
votes
2answers
47 views

Need to fetch latest record always and ignore if there is no latest data [closed]

Basically i want to send mail whenever there is a new record in this table throughout the day with list of all records got created. I have a table with one date column. i have to always fetch the ...
5
votes
6answers
131 views

Alternative to xp_cmdshell for emailing a report as a CSV file

I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible. I know that xp_cmdshell itself poses risks, even with a proxy ...
0
votes
1answer
25 views

sp_recompile does not recompile inline TVF. What alternatives are there other than using an ALTER statement?

In the following, I would expect the first SELECT of the new column [Bit01] to be successful however it is not. Is there any way of avoiding the use of the ALTER statement to get it to work? I have ...
3
votes
1answer
102 views

Object_name(object_id) returning incorrect results

We are trying to exclude stored procedures which have name like '%backup%' or %WI%'.. Here is the total query i used.. ;WITH CTE AS (select object_name(object_id) as procname from sys.sql_modules ...
0
votes
0answers
26 views

Using a Window Function to Replace a Subquery

I am having an issue with a query that I am trying to change to take advantage of the Windows Functions - mainly as a learning tool. The working query is below: SELECT A.county_name AS [CountyName] ...
1
vote
1answer
57 views

How to set rank per group with a SQL UPDATE statement?

Please first refer to the table structure: Now you could see there is one unique constraint including manager_code, archive_year and archive_day_of_year. I need to rank the managers for every ...
0
votes
2answers
47 views

SQL Script to Exclude Weekends and Holidays

I have a query where I need to add 2 days to the date variable based on a condition. However, this date variable needs to skip weekends and holidays. I have a table that stores holidays. How do I ...
0
votes
1answer
125 views

How to Insert all PDF files from a folder into a varbinary(max) column in SQL using a Query

I am trying to create a query script for SQL Server that will convert pdf file into varbinary data and store it into the SQL Table column which is varbinary(max). I also have to make sure it loops so ...
3
votes
2answers
69 views

How to escape XML characters in TSQL before converting to XML?

I have XML structure which is input param for my stored procedure. It contains element with & (which is escaped in XML). When I extract that element to VARCHAR I receive & which is not valid ...
1
vote
1answer
46 views

sp_help with different object types with the same name

I have a table with the same name as a user-defined data type (UDDT), and when I use sp_help, only the table definition is shown. Is there a way to use sp_help or an equivalent to show the ...
0
votes
0answers
44 views

Simplifying this NULL check [duplicate]

Is there a way to simplify this check? I would like to compare if two columns are equal and that includes if one is NULL and the other has a value (which would mean they're considered different). (A.[...
0
votes
1answer
78 views

Dynamic length obfuscation method in T-SQL

I would like to obfuscate (scramble) sensitive data from a SQL Server database, but in the way which will provide: irreversibility (the plaintext can't be derived from the obfuscated data), ...
2
votes
1answer
803 views

CLR SQL vs T-SQL for queries

I am aware that CLR stored-procedures is more efficient in procedural code and T-SQL is better for queries - however I am having a hard time deciding what to use for my specific needs, and I would ...
4
votes
2answers
90 views

SQL Server- Limit to Concurrent table queries?

In SQL Server, or RDBMS systems in general, is there a limit to the number of concurrent queries that can be made against a table? How is it that N number of users can access the same physical data ...
1
vote
1answer
56 views

Update all tables containing column name

In SQL Server, I can find all tables containing a COLUMN_NAME: SELECT * FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'COLUMN_NAME' How can I update all of these columns like: UPDATE <...
-1
votes
1answer
28 views

SELECT rows according to another table

Table A (Id, Value), Table B(AId, Value). I need to select only rows from A, for which the pair (Id, Value) is not in table B. Solution with "JOIN" would probably better, because of performance.
19
votes
2answers
2k views

If positive, sum all items. If negative, return each one

I am needing to find a way to SUM() all of the positive values for num and return the SUM() of all positive numbers and an individual row for each negative number. Below is a sample DDL: Create ...
3
votes
2answers
43 views

Insert if something changed other than specific column

I have a table with many columns but my snippet is simplified. I have the trigger shown below but how can I have it so that nothing is inserted if only the created_at column has changed? ALTER ...
0
votes
0answers
32 views

ERROR: Arithmetic overflow error converting float to data type numeric

I unfortunately have some bad data and need to work around it. I have a Parcel column in a spreadsheet with some parcels that have inadvertently been converted to floating point numbers. I posted a ...
1
vote
1answer
58 views

How to search by text in SSIS packages? [duplicate]

Is there a way to search by text in SQL Server Integration Services packages using T-SQL statement (or other language)? I want to check if certain table is used in any package as I am going to drop ...
1
vote
1answer
55 views

Contradiction in book related to variable scope in Stored Procedures

I was reading the 70-461 TSQL training book and the part about stored procedures said this: Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by ...
3
votes
1answer
21 views

System Database Migration and Security - Log Shipping or DB Mirroring

I've setup log shipping between two servers (they used to be mirrored). What is the simplest or most efficient way to mirror the system databases between the primary and secondary? Is it just a ...
-1
votes
1answer
44 views

Using SQL MERGE to UPDATE a CTE

I have the following SQL... DECLARE @FromDate DATETIME ,@toDate DATETIME IF @toDate IS NULL SET @toDate = GETDATE() IF @FromDate IS NULL SET @FromDate = DATEADD(MONTH, -13, @toDate) SET @...
8
votes
2answers
396 views

Is there a way to make this select retrieve the same results with a single seek?

Is it possible to retrieve the same data as the following with a single seek or scan, either by modifying the query or influencing the optimizer's strategy? Code and schema similar to this are ...
0
votes
1answer
31 views

Linkserver update not working in Transaction

I am trying to do operation on my linked server. I have created one linked server and everything is ok. I just observed one thing which I am not able to understand. if I run: update SERVER.testdb.[...
1
vote
2answers
70 views

Applying single quotes across multiple lines using ssms code snippet

further to Applying Quotes Across Multiple Lines Is it possible to create a "surround with" ssms code snippet to turn a list of values across multiple lines into an IN clause, so I could: select the ...
0
votes
0answers
46 views

Is it possible to inspect data in NORECOVERY mode [duplicate]

We are trying to restore a dataset to our online database that was inadvertently deleted. However we aren't quite sure as to when. Is it possible to restore a test database using NORECOVERY, but still ...
0
votes
1answer
35 views

Bulk Update Based Off Values In A Table

In Sql Server 2014 I need to run an Update statement that will update NULL values with values found previously in the table. To better illustrate, here is a very small subset of my data Create Table ...
3
votes
1answer
310 views

SQL query failing because of commented part of T-SQL query

I am executing below query but I am facing issue. Note :SQL 2008 R2 and SQL 2014 running with two diff serv accounts on Win 2008 R2 and Win 2012 R2. TCP Ports, Firewall ports are all enabled along ...
0
votes
1answer
24 views

How to display name on a column chart itself in SSRS, not on a legends

How can I display legends name on a column chart itself in SSRS?
5
votes
1answer
264 views

Microsoft SQL Server creating an Inline function

Hey guys I'm completely new to SQL where I have to write a user-defined function (UDF) that calculates a student's GPA for a given time frame. The Inputs are StudentId int, ClassStartDateStart ...