tsql

How to get the month from a string in sql?

I have to get the month from a column which stores string in format - Column ---------- `Feb2007' 'Sep2008' So if the value was 'Feb2007' then I need to get back 2 or else if the value was 'Sep2009' then I should get back 9. Is there inbuilt function in SQL Server 2008 to achieve something like this? ...

Filtering unicode column in Sql server 2005 - to much data match

Hi, I have a simple table: CREATE TABLE [dbo].[Users]([Surname] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL) ON [PRIMARY] with two rows: Paweł Pawel Issuing following select statement: SELECT *, CAST(Surname AS VARBINARY(30)) AS Expr1, CAST(N'Paweł' AS VARBINARY(30)) AS Expr1 FROM Users WHERE Surname = N'Paweł' gives foll...

What is the SQL to delete a SQL User from a database?

What is the SQL to delete a SQL User from a database? ...

[SQL Server Procedure] Cannot user "Select TOP @Count ..."

I am creating a procedure something like below. it works fine when there is no "TOP @Count", or it works fine when i put a concrete vaule "TOP 100" . So why i cannot pass the value there??? how can i walk around it??? SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE MyProcedure @Count int = 100 AS BEGIN SELE...

How do I save xml exactly as is to a xml database field?

At the moment, if I save <element></element> to a SQL Server 2008 database in a field of type xml, it converts it to <element/>. How can I preserve the xml empty text as is when saving? In case this is a gotcha, I am utilising Linq to Sql as my ORM to communicate to the database in order to save it. ...

SQL to find runs of matching values

I have the following table called [Store_Sales] - Store Date Sales 1 23/04 10000 2 23/04 11000 1 22/04 10000 2 22/04 10500 1 21/04 10000 2 21/04 10550 I want a SQL that will return a "run" of similar values in the Sales column for a particular store. For exampl...

Will SQL Server roll back single statement after it was terminated?

I have a statement with a single UPDATE command. If I manually terminate it will all the results be rolled back? ...

SQL DateTime type issue

Hello everyone, I am using SQL Server 2008 Enterprise. I have a table called Foo and it has a DateTime type column called MyTime. I want to find all records (rows) whose MyTime column value has been elapsed for more than 3 days (elapsed from current time). How to implement this? thanks in advance, George ...

How to rewrite this query not to use union clause

How to rewrite this query NOT to use UNION (UNION ALL) clause: SELECT c FROM a UNION SELECT c FROM b expected result (recordset should be the same): SELECT c FROM .... ...

Sql query - how to get when a row first got a certain value

Hi, I have a table with rows like this: ID StatusId Date 1 1 2001-01-01 2 1 2001-01-02 3 2 2001-01-03 4 3 2001-01-04 5 1 2001-01-05 6 2 2001-01-06 7 2 2001-01-07 8 1 2001-01-08 9 1 2001-01-09 I need to g...

SQL Stored Proc: Help converting to trigger

Hi all, I'd like to convert a stored proc into a trigger. The stored proc is supposed to be run after inserts/updates, but users forget to execute it more often than not! The stored proc inserts recalculated values for some rows: --Delete rows where RowCode=111 DELETE FROM dbo.TableA WHERE [year]>=1998 AND RowCode=111 --Insert new va...

SQL query to get full hierarchy path

Hi, I have a table with three columns NodeId, ParentNodeId, NodeName. for each node I would like to get a full path like "lvl1/lvl2/lvl3..." where lvl1,lvl2 and lvl3 are node names. I found a function which does that at this link http://www.sql-server-helper.com/functions/get-tree-path.aspx. but I would like to use CTE OR any other techn...

column count based on a range

I have a student marks table with schema as given below: Student -------- SNO Marks I want to produce a result something as this: MarksRange Count ---------- ------ 0 10 10 2 20 43 : : 100 2 The above results shows that there are: 10 students who got a score zero, 2...

How to create a complex type from a stored procedure that uses exec()?

Hi... I want to create a complex type to use within an entity manager from a query constructed dynamically and executed with exec(). Is it possible?; since I'm writing a filter, what would you do instead if it is not possible? Also, I'm evaluating using linq, but the filter needs many tables and their registers, therefore efficiency is...

How do I have a check constraint that refers to another table?

I have the following tables in a SQL Server 2008 db: tblItem, which has an ItemID field; tblGoodItem, which also has an ItemID field, and has a foreign key pointing to tblItem; tblBadItem, which also has an ItemID field, and also has a foreign key pointing to tblItem. An item cannot be both a good item and a bad item; it must either ...

How to store TSQL query results for later display

I've never used TSQL before, but I decided I wanted to poke around in the SO data dump anyways. So this is probably a rookie question. I did try to search for an answer, but I don't know the technical term for what I'm trying to do, and search is only so good if you don't have the right keywords. My goal is to find out how many question...

Multiple WHERE clause inputs to T-SQL stored procedure

I have a multiple selection listbox on a web form which lists various internal activity codes, and users can filter the results of a query based on the codes selected. I can easily add a single code as a parameter to my stored procedure and filter the results with a WHERE clause like so: WHERE ActivityCode = @ActivityCode OR @ActivityC...

SQL query WHERE problem

I have this query that is optimized for speed (that's why it might look a bit odd - got some help a while back). Want the exact result as this BUT I ONLY want results from within the LAST minute not older. This query returns the last 100 no matter what and not only results from the last minute. SessionGuid is not unique - in fact it'...

How do I exploit "EXEC @sql"?

My co-worker is being unsafe with his code and is allowing a user to upload an SQL file to be run on the server. He strips out any key words in the file such as "EXEC", "DROP", "UPDATE", "INSERT", "TRUNC" I want to show him the error of his ways by exploiting his EXEC ( @sql ) My first attempt will be with 'EXEXECEC (N''SELECT ''You D...

Parse a string before the Last Index Of a character in SQL Server

I started with this but is it the best way to perform the task? select reverse( substring(reverse(some_field), charindex('-', reverse(some_field)) + 1, len(some_field) - charindex('-', reverse(some_field)))) from SomeTable How does SQL Server treat the multiple calls to reverse(some_field)? Besides a ...