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

learn more… | top users | synonyms (1)

0
votes
1answer
20 views

EXEC master.dbo.xp_cmdshell 'The user name or password is incorrect'

I am trying to move a file from a network drive to another server and when I execute this statement. I get an output of The user name or password is incorrect DECLARE @SourceFile AS ...
1
vote
0answers
34 views

What is the correct way to rename/swap a table in production

I have two tables, tblCode and tblCodeAlternative tblCode has about 4100 rows and is used in production. tblCodeAlternative has about 4200 rows and is in the production database but not used ...
3
votes
1answer
70 views

Aggregate data from multiple rows into single row

In my table I have rows, and each row has some data and its version. When column is not-null, it overrides an old value otherwise nothing happens. Pseudocode (in C#) var resultTable = new ...
1
vote
1answer
35 views

Compare Timestamp Difference Between Consecutive Records

How do I compare Timestamp difference between 2 consecutive records? This is my example: DECLARE @x TABLE(ProductID INT, SingleRunFailure bit, SampleDate date); INSERT @x VALUES (101, 1, ...
3
votes
1answer
59 views

Select data divided in groups evenly distributed by value

I would like to select into 4 groups the data from a table having the sum of a values in the groups as evenly distributed possible. I am sure that I am not explaining it clear enough so I will try to ...
3
votes
1answer
67 views

Where clause from OUTER APPLY using LEFT function

What would be a good alternative to improve my where clause that currently uses the LEFT function? Should I create a persisted computed column and an index to it? SELECT --columns-- FROM ...
2
votes
0answers
34 views

Cross database - Table valued Stored Procedure

I have the same table type in two databases (DB1 and DB2), DB1 has an stored proc (sp1) which has the above type as an input parameter. Now, I want invoke this SP from DB2. But, I am getting this ...
2
votes
1answer
32 views

String parsing: Extracting the filename from a URL

This is a link I have in a column of a table: http://earthexplorer.usgs.gov/browse/etm/1/11/2000/LE70010112000130KIS00.jpg There are like 1000's of rows like this. Is it possible to extract the ...
6
votes
3answers
207 views

Updating a view on multiple joined tables

Since MSDN does not say much, what happens exactly if I execute the following query? update claims set status='Awaiting Auth.' where status = 'Approved' Can I use the ClaimStatusName column of the ...
1
vote
2answers
165 views

How to use SET operation in CASE clause in SQL?

I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error: Incorrect syntax near SET Query: DECLARE @tbl_Result TABLE ( RowID INT ...
2
votes
2answers
183 views

Use Distinct With Sum?

I want to sum a few values using case statements. My problem is compiler complains if I do not add a group by to the end of the query, which then that negates my Distinct, hah. For example, in my ...
2
votes
2answers
49 views

DBCC Usage on production system

We have a system running SQL Server 2008 R2 on a Hyper-V VM (64GB RAM and 16 virtual cores). Another VM (the application server) within the system accesses the db server quite intensively for both ...
-2
votes
3answers
54 views

Extract Portion From A Decimal [closed]

I need to extract any value > .5 from a decimal. Here is sample ddl Create Table #b ( fv decimal(16,4) fv1 decimal(16,4) ) Insert Into #b (fv) Values ('.8032') How can I capture the .3032 and ...
3
votes
2answers
89 views

Convert int to RGB or hex color

I'm working with a database where colors are stored as integers. For some unknown reason, they are negative integers actually. Now, I need to use them in the CSS background-color property, which ...
0
votes
0answers
42 views

Query Freezes and SSMS Crashes

I feel like I have the syntax set on my query, but anytime I try to run this SSMS freezes and I have to reboot and once I get my PC back up and running no records have been updated. The table only ...
1
vote
1answer
28 views

How to combine 2 SQL statements into one?

I have these 2 SQL statements. Each year, I need to add the new one for the new DB. How can I write it so it will just work for the new years instead of me manually editing this each year? Is there a ...
2
votes
2answers
45 views

Finding and listing all columns in a SQL Server database with identity [duplicate]

I wanted to RESEED all the identity columns in that database, so I need to create a script so I can find out which column in which table has the identity property.
1
vote
2answers
61 views

SQL Server: Query Efficiency Having vs Subquery

I have the following query where I used a sub query since I can't use aggregates in the where clause. SSMS suggested I use the HAVING clause but then I would need to group by all the other columns ...
2
votes
2answers
37 views

How do I create a SQL Agent Alert on a custom error message in SQL Server 2012?

I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an ...
1
vote
2answers
100 views

Transforming multiple rows into a single row

In my company, I need to join table a to table b. Table a is the sources table and table b is the empty table. I want to know that how can I did it and the result like table b? table a: ID date ...
1
vote
0answers
58 views

reset identity of a column on a new primary key record

I have the following table CREATE TAbLE aTEST_person ( person_id int default (NEXT VALUE FOR [person_id_TEST]) , version_num int, name varchar(255) CONSTRAINT ...
0
votes
0answers
11 views

Why is Echo statement in SQL query not writing in text file? [duplicate]

Following code is used for writing a number in the text file but instead it is writing "Echo is ON" into the text file. SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck Print @CMD EXEC xp_cmdshell ...
1
vote
3answers
352 views

Using Echo statement writes Echo is ON in the text file

Following code is used for write a number in the text file but instead it is writing "Echo is ON" into the text file. SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck Print @CMD EXEC xp_cmdshell ...
3
votes
3answers
59 views

Sum values from child tables and ignore equal rows

I have two child tables with a value that must be summed separately and at the end the totals must be subtracted. The problem is that Child Table A and Child Table B can have 0 or more rows associated ...
3
votes
1answer
27 views

Split Field Every 1K Characters

I have a field that can hold up to 3000 characters. I need to split this field into 3 individual fields split1, split2, split3, I have successfully been able to figure out how to get the beginning, ...
3
votes
0answers
63 views

Query with sp_executesql returns nothing while query without it returns the result [closed]

I'm using MS SQL Server 2008 R2 through ORM in .NET application. This ORM uses ADO.NET, so any query will be wrapped in exec sp_executesql. One of the generated queries returns nothing, but it ...
0
votes
2answers
50 views

Format Phone Numbers In SQL Server

I am using SQL Server 2008 and I found this solution which is only for SQL Server 2012 - what is the equivelant to format a phone number in SQL Server 2008? FORMAT([PhoneField],'###-###-####')
2
votes
1answer
48 views

Check 1 Table If No Data Check Second Table

I am passing into a stored procedure two variables @begindate & @enddate. I need to check firsttabletocheck and get a count of where csdate is between @begindate and @enddate. I then need to ...
-1
votes
1answer
32 views

How can I automate the creation of SQL Server Agent Notifications on job failure?

There are no alerts setup on any of my SQL Server Agent jobs ( for failure,severity code etc.). Is there is a script to help or do I have to create an alert manually for every single job?
2
votes
2answers
50 views

Most Recent order from multi join query

Here's the CTE version of the query, but it is returning multiple rows for the same customer. Not just the row containing the most recent open order date: with LatestOPenOrderDate_CTE AS ( Select ...
2
votes
2answers
51 views

RIGHT() works in a SELECT but not in an UPDATE? [closed]

I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use ...
4
votes
2answers
89 views

Replace special characters in a column with space

I'm trying to write a query which replaces the special characters with space. Below code helps to identify the rows. (alpha-numeric characters, comma and space is valid): SELECT columnA FROM tableA ...
1
vote
1answer
73 views

How to increase performance of UPDATE of ~ 50.000.000 entries

We have a query that uses a temporary table to update certain columns in the database: CREATE TABLE #NEW_OLD ( NEW nvarchar(20), OLD nvarchar(20) ); INSERT INTO #NEW_OLD VALUES ...
-3
votes
1answer
33 views

Problem with INSERT INTO [closed]

I can't make INSERT INTO Booking work. My code: INSERT INTO Gaest (pasNr ,fuldNavn , adresse, tlf , email ) VALUES ('1234', 'Simon jensen', 'Heimdalsgade 6, 9000','6691612' , ...
1
vote
1answer
35 views

Update Int Field [closed]

Why is my table not getting updated? For example, if I run this statement alone, I have a value of 6 returned Select COUNT(*) from #madeupland where SOapproved IS NULL however, when it is run ...
0
votes
1answer
36 views

Copy database to a remote server on daily basis

I need to replace the copy of my PROD db in a DEV system on a daily basis. The problem is that I haven't enough space in my DEV system to restore the PROD db. Is there any way to create a shrinked ...
0
votes
1answer
44 views

How can copy one column data of one table1 into other table2 column and other clumns table2 empty?

Here is my problem.I have two tables Stdd and result. stdd columns: id fname lname gender phonenum city result columns: physics chem math english urdu bio total id I want that in result table ...
2
votes
2answers
41 views

DayOfYear as per Indian fiscal year

I want to get sequence number according to the Indian fiscal year. I tried to write the function using SQL built-in function "DATEPART(DAYOFYEAR,'2015-04-01')" but at some point I am facing challenge ...
6
votes
4answers
183 views

Multiple column concatenation

How to concatenate multiple columns in to a single row? For example: id name car 1 sam dodge 1 ram maserati 1 john benz 1 NULL mazda 2 kirk lexus 2 Jim ...
6
votes
1answer
97 views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...
1
vote
1answer
49 views

Two If's And An Else [closed]

I am trying to run this t-sql query but I keep getting an error of Incorrect syntax near the keyword 'END' How should this be re-written in order to execute properly? What I want to achieve is ...
2
votes
1answer
30 views

Find Difference In Time

I am attempting to write a stored procedure to determine the difference in seconds between two times. If I were to hardcode the values I know I can use CAST(datediff(second, [start], [end])/3600.0 ...
1
vote
3answers
66 views

How to retrieve the latest guid value after you have inserted a new row in realtime

Goal: Retrieve the latest guid value in real time after you have inserted the the value in the table Problem: Don't know how to do it Info: *You can only add a new vale that is address and zip code ...
0
votes
3answers
88 views

How to read and increment a number in text file using TSQL

I have a text file C:\abc.txt with a number 5 in the first line. I need to increment it into 6. Please help me with the T_SQL syntax. I have tried the following query to read the value but not able ...
12
votes
2answers
1k views

What does the position of the ON clause actually mean?

The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, ...
2
votes
2answers
80 views

T-SQL SELECT use multiple indexes for no reason

I have a question about indexer and execution plans in T-SQL. My database is SQL Server 2008. I have a simple three-table db schema: The InverterData table is very large and partitioned ...
1
vote
1answer
52 views

update statements shows update count but not updating the table

I am facing a strange issue which I have never faced before. I have a update query like below Update A Set A.Col10 = E.Col10, A.Col11 = E.Col11 FROM TableA A INNER JOIN TableB B on A.Col1 = B.Col1 ...
4
votes
1answer
86 views

Validate XML using XSD inside TSQL function in SQL Server 2012?

I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function. To make validation I have to set value to XML and if it is not valid I will ...
0
votes
1answer
49 views

Verify this TSQL to find the indexes fragmented over 50%

select OBJECT_SCHEMA_NAME(IPS.object_id) 'schema', OBJECT_NAME(IPS.object_id) as table_name,index_type_desc, round(avg_fragmentation_in_percent,2) 'avg_fragmentation_in_percent', si.name ...
0
votes
2answers
52 views

Insert Data in seconds between 30 minutes time interval [duplicate]

I want to divide my time interval from the start time to end time. The elapsed time should be divided across 30 minute time intervals so that for this input 08:05:13 12:09:49 I should get ...