tsql

How to prevent a stored proc error from throwing exception in SqlClient?

Say i have a stored proc that inserts some data into a table. If data is duplicate it will cause a sql server error (because i have unique index on some columns) and this error causes an exception in SqlClient. What can i do inside the stored proc to handle the error so no exception is being generated in my data access code? ...

How to select a column in SQL Server with a special character in the column name?

I have a table contain the '%' in the column title, and this cause problem when I do the select statement on that column (Find below for more details). Does anyone know how can I select that column by not keeping the original column title? Example: Table1 name ref_no tot_sales %Phone ------------------------------- Alan 1 1 ...

Get the time of a datetime in TSQL?

Hello, how could I do this? I have a datetime in DB like this: 2010-09-06 17:07:28.170 What I want is only 17:07:28.170. Is there a function for that or something? Thanks :-) ...

Difference between '<>' and 'Is NOT' in TSQL?

I don't get it. I had this expression and it didn't work. IF @LocationName <> NULL AND @Latitude <> NULL AND @Longitude <> NULL AND @Zoom <> NULL AND @MapTypeId <> NULL BEGIN ... END I changed it to this expression. IF @LocationName Is NOT NULL AND @Latitude Is NOT NULL AND @Longitude Is NOT NULL AND @Zoom Is NOT NULL AND @MapType...

How to manually expire a SQL login account?

Hi. I am writing vb.net code to log a user into my application using SQL to authenticate the user. Each app user is an actual Microsoft SQL Server 2005 user. Now I need to test the login mechanism and for this I need to: 1) test against a SQL account that is locked. 2) test against a SQL account who's password has expired. Is there...

Sql Query for multiple tables

I Used SQL 2000 and database have following tables LandParcel (Table Name) BlockID ParcelNo NameofOnwe 11001 1056 Chandana 11001 1078 Sisil 11001 1158 Kumara 11078 105 SK 11078 245 Shantha Actions (Table) Blockid ParcelNo ActionTaken 11001 1056 Received 11001 1078 Received 11001 ...

SQL Server dynamic ORDER BY with mixed datatypes: is this a good idea?

I have stored procedure that returns results sorted dynamically. The parent folder (this is for content management) has a RankTypeID field that allows sorting by Rank (0), Start Date in ascending order (1), Start Date in descending order (2), and document title (3) Rank is an integer, date is smalldatetime, and title is a nvarchar. ......

Create a table from CSV columns in SQL Server without using a cursor

Given a table: |Name | Hobbies | ----------------------------------- |Joe | Eating,Running,Golf | |Dafydd | Swimming,Coding,Gaming | I would like to split these rows out to get: |Name | Hobby | ---------------------- |Joe | Eating | |Joe | Running | |Joe | Golf | |Dafydd | Swimm...

Write stored procedure parameters to an XML Column

I have a need to take the parameters passed in to a Stored Procedure (SQL 2005) and write those values into an XML column as one xml document. Looking for an idea on how to start it. ...

TSQL Comparing two Sets

When two sets are given s1 ={ a,b,c,d} s2={b,c,d,a} (i.e) TableA Item a b c d TableB Item b c d a How to write Sql query to display "Elements in tableA and tableB are equal". [Without using SP or UDF] Output Elements in TableA and TableB contains identical sets ...

SQL only want to return a single row for each note

I have the following tables: Orders, Notes, Permits The following columns are in each table: Orders = ID Notes = ID, RelatedID, Note, Timestamp Permits = ID, OrderId I have the following query SELECT o.id , op.id , n.timestamp FROM [tblOrders] o INNER JOIN [tblNotes] n ON n.[RelatedID] = o.[ID] INNER JOIN [tblPermits] op...

How do I get the position of the match in a FORMSOF INFLECTIONAL full-text search?

I'm using a CONTAINSTABLE query with MS SQL Server's full-text indexing engine to search within a textual column; e.g.: SELECT * FROM MyTable INNER MERGE JOIN CONTAINSTABLE(MyTable, sDescription, 'FORMSOF(INFLECTIONAL, "brains")') AS TBL1 ON TBL1.[key]=MyTable.ixKey This does a great job of finding rows with a description includi...

How do I NOT use a t-sql cursor when printing rows is necessary?

A colleague and I wrote this stored proc that documents a database table in wiki markup, for a ScrewTurn wiki system. Originally, I wrote it without a cursor, because until today I never even knew how to use one! I started with what is essentially a combination of what you see below. I would select one column for each row, where that co...

SQL Server - How to lock a table until a stored procedure finishes

I want to do this: create procedure A as lock table a -- do some stuff unrelated to a to prepare to update a -- update a unlock table a return table b Is something like that possible? Ultimately I want my SQL server reporting services report to call procedure A, and then only show table a after the procedure has finished....

Select multiple columns as a result of a case expression

This is what I would like to do: case p.residency_status when 0 then dbo.fn_formatAddress(r1.prm_name, p.py_hospital, p.py_address1, p.py_city, p.pyear_state) when 1 then dbo.fn_formatAddress(r1.prm_name, p.respgm_hospital, p.respgm_address1, p.respgm_city, p.respgm_state) when 2 then dbo.fn_formatAdd...

SQL comparing sets, part II: How to join sets of sets

This question reminded me of a couple related problems with whole-set comparison. Given: a collection of sets, and a probe set Three questions: How do you find all sets in collection that match probe, element for element? How do you find all sets in collection that match a collection of probes, without the use of explicit looping ...

t-sql query : retrieve latest row approaches

Based on the following table Table_A ID Rev Description ----------------------------------- 1 1 Some text. 1 2 Some text. Adding more. 1 3 Some text. Ading more & more. The above will keep adding a new row when user updates the description. I want to take the row with MAX(Rev) [i.e. the latest description]. To...

len of varbinary 2

hello, this is a followup question to http://stackoverflow.com/questions/3661641/len-of-varbinary len(0x0a0b0c0d) returns 4 because len counts bytes. does this behaviour depend on the current server collation? if i chose utf-16 for example, would it return 2? in other words, does len treat its argument as string or does it distinguish ...

Set operation on TSQL (SQL 2005/2008)

When a set is given say {1,2,3,4,5,6} The task is to separe pair of subsets {1,2}, {1,3}, {1,4}, {1,5}, {1,6}, {2,3}, {2,4}, {2,5}, {2,6}, {3,4}, {3,5}, {3,6}, {4,5}, {5,6} So when i have a table Table Element 1 2 3 4 5 6 What is the way to list out all possible pair of comma separated subset ? (Duplicates can be ignored (i.e) {1,...

How to COUNT column values differently in a VIEW?

I have a column in Datablase Table, Suppose its Observation which contains three types of values Positive Negative NULL Now I want to count the Total no of rows , Total Positive and Total Negative and some other columns. I can not use Where clause here. And its a view So result should be like Total Positive Negative SomeOthe...