tsql

Exit and rollback everything in script on error

Hey guys ! I'm in a bit of a pickle here. I have a TSQL script that does a lot of database structure adjustments but it's not really safe to just let it go through when something fails. to make things clear: using MS SQL 2005 it's NOT a stored procedure, just a script file (.sql) what I have is something in the following order BEG...

SQL Server default values: why with one or two parentheses?

Hi, if you run this script to retrieve all default value definations in a database: select c.name as columnname, t.name as tablename, d.definition as value, d.name as constraintname from sys.default_constraints d join sys.columns c on d.parent_column_id = c.column_id and d.parent_object_id = c.object_id...

SQL Server: get top xx blog record from Umbraco by parameter...

Following SQL get what I need: SELECT TOP (50) [nodeId] FROM [dbo].[cmsContentXml] WHERE [xml] like '%creatorID="29"%' AND [xml] like '%nodeType="1086"%' ORDER BY [nodeId] DESC I need to pass in the numbers as parameters, so I have follows: exec sp_executesql N'SELECT TOP (@max) [nodeId] FROM [dbo].[cmsContentXml] WHERE...

how to join with group by statement

Im having two tables TableA Custid prodid Amt1 1 10 200 2 20 300 3 10 400 4 30 300 5 20 400 6 30 200 TableB prodid Amt2 10 100 20 200 30 300 and i want sum of all amount group by prodid. can any one give me both ansi and Tsql quries. ...

ASP.NET Try.Parse for Guids ?

hi, my application generates an uniqueidentifier in mssql which then used to generate a querystring for link for user to click. Page user lands after that click do something with user but first validates if such uniqueidentifier exists to avoid heavy stored procedure with small check. I want to improve it by checking whether querystring...

SQL Server Distinct Question

I need to be able to select only the first row for each name that has the greatest value. I have a table with the following: id name value 0 JOHN 123 1 STEVE 125 2 JOHN 127 3 JOHN 126 So I am looking to return: id name value 1 STEVE 125 2 JOHN 127 Any idea on the MSSQL Syntax on how to perform this operation?...

Insert consecutive numbers

Hi. I have a table A (Acons, A1, A2, A3) in which I should insert information from another table B with columns (B1, B2, B3). The Acons is a column in which should contain some consecutive numbers (it is not an identity and I cannot make it identity). I know xmin - starting the from number the sequence has to be computed. How can I ins...

SQL2k8 T-SQL: Output into XML file

I have two tables Table Name: Graph UID1 UID2 ----------- 12 23 12 32 41 51 32 41 Table Name: Profiles NodeID UID Name ----------------- 1 12 Robs 2 23 Jones 3 32 Lim 4 41 Teo 5 51 Zacks I want to get an xml file like this: <graph directed="0"> <node id="1"> <att nam...

T-SQL Dynamic SQL and Temp Tables

It looks like #temptables created using dynamic SQL via the EXECUTE string method have a different scope and can't be referenced by "fixed" SQLs in the same stored procedure. However, I can reference a temp table created by a dynamic SQL statement in a subsequence dynamic SQL but it seems that a stored procedure does not return a query r...

How to check dates don't overlap in a table using TSQL.

I have a table with start and finish datetimes that I need to determine if any overlap and not quite sure the best way to go. Initially I was thinking of using a nested cursor as shown below which does work, however I'm checking the same records against each other twice and I'm sure it is not very efficient. eg: this table would result...

In SQL Server, how can I separate a large number of tsql statement into batches?

Hi guys, In SQL Server, how can I separate a large number of tsql statement into batches? Should I use the GO statement in stored procedures or functions? Should I use the GO statement in explicit transaction management situation(between BEGIN TRANSACTION and ROLLBACK TRANSACTION or COMMIT TRANSACTION)? Are there some best practice abou...

In SQL Server, how do I know what transaction mode I'm currently using?

Hi guys, In SQL Server, how do I know what transaction mode I'm currently using? Such as autocommit, explicit, or implicit. And how can I change one mode to another using tsql? Great thanks. ...

Slow T-SQL query, convert to LINQ to Object

I have a T-SQL query which populates a DataSet from an MSSQL database. string qry = "SELECT * FROM EvnLog AS E WHERE TimeDate = (SELECT MAX(TimeDate) From EvnLog WHERE Code = E.Code) AND (Event = 8) AND (TimeDate BETWEEN '" + Start + "' AND '" + Finish + "')" The database is...

check if a value is NULL or Less than 0 in one TSQL statement

ISNULL(SUM(MyTable.Total), 0) AS Total how can i modify the above statement to also check if Total is less than 0 (zero), such that if Total is NULL or less than 0 (negative), i assign 0 to Total ...

Dynamically set the result of a TSQL query using CASE WHEN

SELECT MyTable.Name, ( SELECT CASE WHEN ISNULL(SUM(TotalDays), 0) <= 0 THEN 0 ELSE SUM(TotalDays) END AS Total FROM Application AS Applications WHERE (ID = MyTable.id) ) - MIN(Assignments) AS Excesses FROM MyTable The above TSQL statement is a subq...

At what point should a Table Variable be abandoned for a Temp Table?

Is there a row count that makes table variable's inefficient or what? I understand the differences between the two and I've seen some different figures on when that point is reached, but I'm curious if anyone knows. ...

How do I Change SQL Server column names in T-SQL?

I have a table with 600+ columns imported from a csv with special chars % _ - in the column names, is there a way to change the column names to remove these special chars ? The code can be in T-SQL. ...

SQL CLR and nullable datetime parameter

I'm trying to write a SQLCLR function that takes a DateTime2 as input and returns another DateTime2. Based on this post I altered the parameter to be the C# type DateTime giving me the level of precision I require. However because the input can be null I would like it to be DateTime?; the return type as well. using System; using Micros...

T-SQL Pivot? Possibility of creating table columns from row values

Is it actually possible to rotate a T-SQL (2005) so that (for the sake of argument) the values of the first column's rows become the titles of the output table's columns? I realise this is not really what PIVOT is for, but it's what I need - the ability to request a table where the columns are not known before-hand because they have bee...

How to create view from more than one table in MS Sql Server database?

I have to create a view from more than one table in MS Sql Server database, but i am not able to get the correct syntax for the same. need help. thanks. ...