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

learn more… | top users | synonyms (1)

-4
votes
1answer
45 views

Why all ranking is 1 for this query [closed]

Declare @ranking_term char set @ranking_term ='Amount' select case (@ranking_term)when'Amount' then ( select Dense_rank() over (order by Amount desc) ) else( select ...
7
votes
2answers
81 views

DBCC CHECKIDENT ('table name', RESEED, 20000) not working

When I run the script DBCC CHECKIDENT ('table name', RESEED, 20000) the output of the message is Checking identity information: current identity value '200000', current column value ...
6
votes
0answers
85 views

How to reload a linked server?

I am using Microsoft SQL Server 2014 - Enterprise Edition, a problem occurs with the linked servers where it is necessary to restart the server or to stop the MSSQLSERVER services. When the server is ...
0
votes
1answer
34 views

Why the result is ranked by both amount and date instead of amount only? [duplicate]

Declare @ranking_term char set @ranking_term ='Amount' select Dense_rank() over (order by case(@ranking_term) when 'Amount' then 1 else 0 end, amount, case(@ranking_term) when ...
5
votes
4answers
2k views

Speeding up COUNT(*) - WHERE clause slowing query

The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on ...
4
votes
1answer
40 views

Is is possible to combine local variables in a case statement?

DECLARE @CalcQuery varchar(340) SET @CalcQuery = (SELECT sum(T0.[LineTotal]) FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN [@SES_SSNSBRG] T2 ON ...
1
vote
2answers
291 views

how to create trigger in SQL server using two tables to find stock quantity

I have two table sales and purchase. I want to create a trigger which stores final quantity in stock table by subtracting sales table sold quantity from purchase table. There is kikkat as a name n ...
3
votes
2answers
86 views

Data extraction with multiple delimiters?

I have a legacy data source column that is delimitted by semicolons and commas. The first semicolon indicates the last name, the second indicates the first and middle name (or initials), and the last ...
0
votes
1answer
54 views

Update if not exists statement

If I have a simple table with 2 columns and a composite primary key consisting of both columns, how do I rename one of the columns but only if it doesn't cause a primary conflict? Here's a simplified ...
5
votes
3answers
125 views

What are the SQL Server 2012 versions of FIRST() and LAST()?

I have a table with a value column. I want to calculate the last row minus the first row, as shown here: id value 1 10 2 45 3 65 4 95 . . . . ...
1
vote
2answers
2k views

Options for keeping two SQL Server Compact databases in sync?

Developer here. I have a system where there is a front machine creating rows in a moderately complex database, and a back machine that must sync with the front machine to run reports. Both run SQL ...
2
votes
3answers
71 views

Explicit transaction left open after try/catch

I recently wrote a T-SQL script to perform some updates and inserts on 3 different tables. I wanted it done in a single transaction, so I read up on the Microsoft documentation on how to use explicit ...
5
votes
2answers
201 views

How to name table-variable function unique constraint?

I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as ...
7
votes
2answers
103 views

Enforce Data integrity on an Email column in a database

I want to put a constraint on an email column like so: xxxx@xxxx.yyy Obviously "x" is of various lengths and x is some piece of string data and .yyy is a domain type of .com, .gov etc
0
votes
1answer
28 views

Trigger to drop table only if there are no records

How do I write a DDL DROP_TABLE trigger that will only drop the table if there are no records?
0
votes
0answers
26 views

The best way to raise error from mass insert query

I have a following sproc: 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 ALL SELECT ...
2
votes
1answer
64 views

Updating table based on conditions (overlapping dates)

Say I have a table name CustomerBooking which contains Customers, checkInDate and checkOutDate as follows. I would like to achieve the outcome as shown in condition 1 and condition 2 with update ...
2
votes
1answer
29 views

Unpivot with rows converted to columns as well as columns converted to rows

I've tried to understand unpivot examples on the web but they don't quite result in what I'm trying to do, which is to have specific rows turned into columns and columns turned into rows. To try and ...
1
vote
0answers
61 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' ...
4
votes
2answers
214 views

Executing the same SQL statement for different tables

I have a stored procedure that deletes data from multiple tables. The SQL is all the same, except for the name of the table. UPDATE desttable SET desttable.reportarea = esz2reportarea.reportarea ...
-7
votes
1answer
47 views

Need Immediate Help To Develope T-SQL

Hi DBAs, I want to Develop a T-SQL For Fetch data from following Table. But Condition is : I want to select only those data which status has all three status. suppose for id 1 : It has Only two ...
1
vote
1answer
1k views

Joining multiple tables results in duplicate rows

I am running SQL 2005. I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. Here is my query: There are multiple tables that have ...
3
votes
1answer
24 views

Crosstab Pivot or not?

I am have difficulty wrapping my head around the following issue. I currently have a set of data in one table as shown below: As you can see, there is an invoice ref number along with a date, and ...
1
vote
0answers
45 views

Powershell To Run SQL Case Update [closed]

This is my syntax which throws an error of incorrect syntax near abcdefg. Can someone who is more experienced in the world of powershell take a gander and let me know how to remedy my issue? ...
1
vote
1answer
311 views

Is it worth time converting Access query to T-SQL query

Let me just say, before anything else, that I am a enormous noob when it comes to databases in general, so please forgive me for my rather stupid thoughts and actions. I had a database built in ...
3
votes
2answers
545 views

How to separate name string by the spaces in the name in T-SQL

I have a list of names, such as: Doe John James, Doe Jane and Doe Henry Albert Sr I need to split those out into the four component parts of the name (if the name has four parts) to put each of them ...
0
votes
0answers
16 views
1
vote
0answers
89 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
2answers
117 views

Format column length in output - SSMS

SQL Server 2012. Sample query at bottom. I'm trying to create a simple report for when a given database was last backed up. When executing the query at bottom with output to text in SSMS, the ...
1
vote
3answers
94 views

Copy table structure excluding nullability into local temporary table

How can I copy the structure of a table into a temp table (or table variable) excluding the nullability of each column? Mainly I need to copy the column names and data types, but not nullability, ...
10
votes
3answers
10k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
2
votes
3answers
136 views

Show all stored procedure definitions (content) for editing

Apologies if this has been asked before, the suggested 'related' questions weren't relevant and my own search turned up very little. I need to list the definitions (code) of all my stored procedures, ...
16
votes
4answers
89k views

What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

Many times I need to write something like the following when dealing with SQL Server. create table #table_name ( column1 int, column2 varchar(200) ... ) insert into #table_name execute ...
1
vote
1answer
68 views

SQL Server database has corruption, but displays no errors

We have an extensive database of tables. I moved over a table from our dev db to our production db using a program called SQL Compare. I just copy paste the SQL it produces into Management Studio and ...
1
vote
1answer
48 views

T-SQL Check another table rows if match delete them

I am creating a script in SSIS where I want table A to check if rows exist in table B. If they exist, they must be deleted. Table A is an archive table named Customer_archive Table B is a table that ...
3
votes
2answers
301 views

Rebuilding indexes on a list of tables

I have a large number of tables that may change, may have infrequent changes in index, etc. I may well hand this off to somebody else and I don't want indexes to become overly fragmented, slowing the ...
2
votes
1answer
75 views

Deterministic function for getting today's date

I am trying to create an indexed view using the following code (so that I can publish it to replication it as a table): CREATE VIEW lc.vw_dates WITH SCHEMABINDING AS SELECT DATEADD(day, ...
1
vote
0answers
63 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
42 views

Window function vs group by method: find value for MAX ID

I'm trying to understand the performance issue with Window function when i select value for MAX ID --Old group By method SELECT tj.MainProcedure , tj.StartTime , tj.EndTime , ...
1
vote
1answer
30 views

Stored Procedures execution permissions

So what I am trying to do is to limit the access to a stored procedure so that only another stored procedure may call it. Here is an example: create procedure Database_Deployment_Proc -- This ...
0
votes
0answers
14 views

Updating column as the value selected by user in multivalued parameter

I am creating a report in which I have data from 11 different databases... I have created multi-valued parameter... user selects the state... I want to add a column to my result which should display ...
5
votes
2answers
39 views

SQL Server 2008R2 - MAX Result and Hour per Day

I have a table full of network interface statistics. InterfaceID TimeStamp RCV_bps XMT_bps ----------- ------------------------------ ------------- ------------- 275 ...
1
vote
1answer
46 views

Forbid user to query internal SQL server information

I'm trying to forbid database user to get SQL server internal information. Script that creates DB user: CREATE USER [testuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo] DENY SELECT, INSERT, UPDATE, ...
0
votes
0answers
37 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
31 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 ...
1
vote
3answers
137 views

Need to get identify all tables and columns in a SQL Query

I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or ...
1
vote
1answer
43 views

Procedure completes prematurely after converting comma-delimited value strings to XML

I have a procedure which executes beautifully within SSMS environment yet, when I hook it up to a web page, instead of seeing a list of packageIDs returned, the return is 'null'. I've checked ...
4
votes
1answer
64 views

Minimum distance of a location; one table of points compared to a set of points SQL 2012

I am trying to find the quickest way to find the minimum distance of one point to a table of points. The only caveat is that the table of the points I'm trying to find the minimum distance of is 150K ...
0
votes
1answer
39 views

How to pivot this unpivot?

This is the pivot I made: SELECT * FROM ( SELECT *-- codigo,ID,Grupo,convert(int,CodOpcao) as CodOpcao FROM ( SELECT * FROM ( SELECT 39 as codigo , ...
0
votes
1answer
34 views

How to exporting a databse structure (or many tables) for import

In TSQL: When right click a table you have the option to Script table as > Create which creates the table script including the constraints If you want to port the database or large portion of the ...