Transact-SQL (t-sql) is a dialect of SQL used by Micrsoft's [SQL Server](http://www.microsoft.com/sqlserver/en/us/default.aspx) and SAP's [Sybase](http://www.sybase.com/).
2
votes
2answers
160 views
Defrag a HEAP by creating clustered index and immediately dropping it
I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
1
vote
2answers
715 views
Create job to send alert using sp_send_dbmail when table value greater than x
PROBLEM
The SMScounter.Counter is used to accumulate usage from a service. The service has a programmatic cap of 1000. I would like to alert users when this cap is breached.
TABLE CREATE
CREATE ...
0
votes
1answer
69 views
modify T-SQL to get results
I am an amateur.
I am trying the following
select * from [dbo].[AccessFeeDetail]
where LastLoginDate >= '10/15/2012 00:00:00.000', jobname not like 'Uptest_%'
I am making a mistake on where ...
1
vote
1answer
228 views
Stuff function in T-SQL
select STUFF(
STUFF(RIGHT('000000' + CAST([run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)]
from sysjobhistory
The query I have ...
1
vote
2answers
948 views
how to calculate difference of first row and last row fields value in each group
I have table with structure like this:
+-------+------------------+
| Value | Date |
+-------+------------------+
| 10 | 10/10/2010 10:00 |
| 11 | 10/10/2010 10:15 |
| 15 | ...
1
vote
0answers
91 views
TSQL Trigger Incorrect Syntax
Im a SQL noob but here goes - I have a trigger in SQL which is giving me an incorrect syntax error E.G:
INSERT INTO aTable
(
tTypeId,
opId,
inserted.Id
)
SELECT
3,
0,
inserted.Id
...
5
votes
2answers
291 views
T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs
I am trying to write a function which will split a varbinary(max) field into a table with 16 byte GUIDs, but am struggling. I haven't been able to find any examples or walkthroughs on how to do this ...
0
votes
3answers
134 views
Pivoting row contents to produce one dimensional result
I have a table with a varchar column containing pipe-delimited values
For example:
Row 1 Column 1 = a|b|e|gg|foo
Row 2 Column 1 = oV|foo|do
Row 3 Column 1 = boop
How can I query this to return ...
5
votes
4answers
423 views
Create a plan guide to cache (lazy spool) CTE result
I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the ...
2
votes
2answers
476 views
Merge two records in the same table, keeping foreign key relationships intact for both
I have a table, called SITES, that has three columns, lets say it looks like this:
ID Name Path
1 Google http://www.google.com/
2 Microsoft http://www.microsoft.com/
I also have ...
2
votes
1answer
135 views
How are foreign keys resolved when creating tables in batch?
I am creating a database version management tool with the goal of being able to recreate a database from scratch using text/sql files contained in source control. As part of establishing a baseline ...
4
votes
1answer
187 views
Calculating Median Value within a Group By
I have a SPROC which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do next is ...
2
votes
2answers
359 views
How to map another server through SQL Server Management Studio
I am trying to map another server by giving the command
EXEC xp_cmdshell 'NET USE H:\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes'
I got an error with this network path not ...
0
votes
1answer
238 views
Query performance and join hints, plan cost, and duration
Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases.
Background
I have a reporting database using ...
3
votes
2answers
610 views
Linked Server Query Results: OPENQUERY vs Distributed Transactions
I have two databases. They are on different servers. One is named REPORTDB and the other is named TRANSDB. TRANSDB is referenced as a linked server on REPORTDB named TRANS.
I execute a query like ...
3
votes
1answer
756 views
How can I create a stored procedure using xp_fixeddrives?
I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using sql server 2008
ALTER PROCEDURE freespace
AS
...
1
vote
0answers
213 views
Query tuning help needed (Hash Match and Table Scans)
I need help optimizing the following query (returning ~8k rows):
SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name)
FROM (
...
2
votes
2answers
169 views
T-SQL Naming Standard
Is there a recommended (official or not) style from Microsoft regarding naming and casing in T-SQL?
If there isn't one from Microsoft then is there one with broad acceptance?
1
vote
1answer
297 views
bulk insert from file
I am often requested to upload data from file (let's say csv) into a Sql Server 2008 db.
I would like to use BULK INSERT, but I have no access to the db server file system.
Questions
is there a ...
0
votes
2answers
88 views
Print variable of same datatype
I am looking for to print two variables of same datatype I have something like this
declare @one int ,@two int
set @one=1
set @two=2
print @one+@two
obviously it will give a result 3 but
I need ...
1
vote
1answer
511 views
To use or not use ISNULL(NULLIF(Field1, ''))?
In the past I've seen use of something such as
SELECT ISNULL(NULLIF(Field1, ''), 'NewValue')
to tersely get a fallback value.
However, since the advent of CASE in TSQL, we've favored something ...
5
votes
1answer
93 views
Can We Perform an “Up-To-The-Minute Restore” After Taking Transaction Log Backups?
The Setup:
Say we have a Database in Full Recovery Mode that does a nightly Database Backup, Transaction Log Backup, and Transaction Log Shrink.
Now the Transaction Log grows so quickly during the ...
4
votes
3answers
259 views
Can I change table structure in a transaction and then roll it back if there is an error?
I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements ...
4
votes
2answers
300 views
Restore all applicable files in a backup device in T-SQL
I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a ...
4
votes
3answers
1k 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 ...
5
votes
1answer
271 views
Query performance while using sqlcmd [duplicate]
Possible Duplicate:
Running queries with SQLCMD vs. Running queries with SSMS
I have a number of scripts that query the data from different tables and consequently update them. I have ...
1
vote
1answer
184 views
Using sys.dm_os_performance_counters and DIFF to display logins per second
PROBLEM
Running my script below i can see the number of logins as an accumulated number to my instance:
SELECT cntr_value AS [LoginsPerSec]
FROM sys.dm_os_performance_counters
WHERE
...
1
vote
2answers
868 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 ...
1
vote
1answer
375 views
Trying to Build TreeView Query in T-SQL
I have parent records in two tables, Table1 and Table2. Some records in Table3 are children of Table1, and some are children of Table2.
I would like to build a query that I can use to populate a ...
4
votes
2answers
387 views
Query two SQL Server databases in same function but on two different servers
Suppose I have two database servers (DB-SERVER-1 & DB-SERVER-2), and there is a single database on each server (DB1 & DB2). Both are SQL Server databases. Is it possible to query both ...
4
votes
2answers
215 views
T-SQL Syntax error copying views dynamically
I'm creating a stored procedure to copy all views from one database to another using the following code.
ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB ...
6
votes
1answer
273 views
CATCH block is triggered when it should not
On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of ...
5
votes
3answers
430 views
Total of orders open on a given date for each date in a date range
This question is similar to Running total with count?, but please allow me to explain some further twists on the issue.
I'm using SQL Server 2008, so the cursor option described by Aaron Bertrand ...
2
votes
2answers
1k views
SQL Server : how to drop all constraints on a table in T-SQL
I´d like to drop all constraints on all tables in my database, because I need to change the relationships of many of the tables.
Is there any way to do that with T-SQL? I cannot find any solution ...
2
votes
1answer
736 views
How to remove xmlns from child elements with FOR XML
I have a TSQL query that I use to generate a SOAP document. I am using two namespaces. Namely:
- http://schemas.xmlsoap.org/soap/envelope/ for "packaging" the soap document
- A custom one that ...
3
votes
2answers
6k views
Writing select result to a csv file
We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option.
I ...
5
votes
3answers
2k views
SQL Server 2008 merge statement deadlocking itself
I have the following procedure (SQL Server 2008r2):
create procedure usp_SaveCompanyUserData
@companyId bigint,
@userId bigint,
@dataTable tt_CoUserdata readonly
as
begin
set ...
5
votes
3answers
1k views
RETURN statement before DEALLOCATE CURSOR
Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure
Consider this example:
CREATE PROCEDURE [dbo].[test]
@ReturnEarly BIT = 0
AS
BEGIN
SET NOCOUNT ...
2
votes
1answer
342 views
Rules in SQL Server 2005
The syntax for rules in SQL Server 2005 is given:
CREATE RULE [ schema_name . ] rule_name
AS condition_expression
Whenever using rule, for example:
CREATE RULE list_rule
AS
@list IN ('1389', ...
4
votes
1answer
674 views
T-SQL: Convert XML to string without self-closing tags
We have a weirdo system we're integrating with which consumes XML, but it does not understand self-closing tags, so
<BOOKING>
<BOOKINGID>56812</BOOKINGID>
<PERSONCODE />
...
1
vote
1answer
179 views
SQL Server 2008 R2 - Weekly / Monthly Statistics
I'm new working with SQL Server 2008 R2 Express, and I´d like to gather some statistics every week / month for users who are connecting to this server. For instance, how many times they were ...
2
votes
2answers
120 views
SQL SELECT Kill FROM problem
$sql = "SELECT Kill FROM tbl_pvporderview";
Problem is that il lend up with : Incorrect syntax near the keyword 'Kill'.
Cuz kill is a TSQL command... any way to bypass it?
I cant change the column ...
1
vote
1answer
101 views
EXISTS and sub-query cost
i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns?
1)
SELECT id
FROM Parent p
...
3
votes
1answer
100 views
Selecting 'edge' records in a sequence of data
I have a table of items which run sequentially by date with both a start and end for each entry:
ID | Start | End
----------------------------------------
1 | 2012-08-20 00:00 | ...
8
votes
2answers
149 views
Non-Clustered Indexes - keys and nonkeys
I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.
Here's my theory from the query I've just optimised, through a process of trial and ...
4
votes
4answers
408 views
T-SQL command to log in as a different user?
Is there a T-sql command, like the Oracle "Connect" which lets me change the user that I am logged in as?
4
votes
4answers
496 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 ...
2
votes
2answers
249 views
Make sure two columns are sorted in the same order
I have a table like this:
CREATE TABLE T (
ID INT NOT NULL IDENTITY PRIMARY KEY,
CreateDateTime datetime2 NOT NULL,
--other columns
)
How can I make sure that ID and CreateDateTime are both ...
0
votes
6answers
326 views
Equivalent of CHARINDEX OR IN for ints
I want the equivalent of this for ints.
--Valid SQL
DECLARE @users AS nvarchar(50)
SET @users = 'Joe ,Bob,Fred,Tim' --1,2,8,23
select * FROM Person AS p
WHERE CHARINDEX(p.sFullName,@users) > 0
...
7
votes
1answer
61 views
sysschedules datatype choices
I am Curious.
Why does TSQL save the values in msdb.dbo.sysschedules for date and times as ints instead of using datetime. I assume the reason dates back to something in SQL Server 2000.
Was it a ...
