Dynamic WHERE statement - SQL server 2005
I need to create a SQL statement that uses the LIKE keyword to filter the result set. Sometimes this value is NULL. What is the best way to implement in T-SQL? I am using SQL server 2005 ...
tsql
How to change mm/dd/yyyy to desired format
hi guys, I have date in format mm/dd/yy.For eg:4/1/2009 means April 1 2009.I want to get format as April 1,2009 in sql.Can anybody help? ...
What to do when I want to use database constraints but only mark as deleted instead of deleting?
I am working in a project where database items are not deleted, but only marked as deleted. Something like this: id name deleted --- ------- -------- 1 Thingy1 0 2 Thingy2 0 3 Thingy3 0 I would like to be able to define something like a UNIQUE constraint on the name column. Seems easy, right? Let's imagine a scena...
Aggregate SQL Function to grab only the first from each group
I have 2 tables - an Account table and a Users table. Each account can have multiple users. I have a scenario where I want to execute a single query/join against these two tables, but I want all the Account data (Account.*) and only the first set of user data (specifically their name). Instead of doing a "min" or "max" on my aggregate...
How to check if I'm currently the database owner for SQL 2000/2005/2008
I can't remember how to do this in a TSQL query. It was a one-liner, good for testing before running DML. The query was something similar to SELECT IS_DBO() or SELECT IS(DBO). ...
What is the best practice for storing boolean values in SQL?
Are there reasons for not storing boolean values in SQL as bit data types without NULL? I see them often stored as integers without constraints to limit values to 0 and 1, and as strings with things like T/F, True/False, yes/no, etc., again without constraints. Isn't it better to store them as bits and not have to worry about additiona...
Set based calculation for events table, exceptuating an event
Hello, I have a table which contains events for change the state of a maintenance service. This is a sample of data: id date event 234 2009-04-22 10:00:00 Service Request 234 2009-04-22 10:02:00 Service Approbation 234 2009-04-22 10:03:00 Service Asignation 234 2009-04-22 10:40:00 Service Fulfilled ... In a report, i need to show tim...
Determine Active Node in SQL Failover Cluster
Does anyone know how to determine the active node of a SQL Active-Passive Failover Cluster programmatically from T-SQL? @@SERVERNAME only returns the virtual server name, which is identical from both nodes. I don't plan to make any decisions based on the data - I trust the failover to do its thing - but I would like to include the info...
How to enclose square brackets to a variable which has been set as a value of one variable in T-SQL?
I got a script from the Net that computes the usage of datafiles and transaction log files from a certain SQL Server instance. The script works fine if there is not database name with whitespace or is not too long. However, if the database name has whitespace or is too long, I get the error message "Msg 911, Level 16, State 1, Line 1 Cou...
Checking for time range overlap, the watchman problem [SQL]
I am running into a road block on a larger problem. As part of a large query I need to solve a "night watchman" problem. I have a table with schedule shifts as such: ID | Start | End 1 | 2009-1-1 06:00 | 2009-1-1 14:00 2 | 2009-1-1 10:00 | 2009-1-1 18:00 3 | 2009-2-1 20:00 | 2009-2-2 04:00 4 | 2009-2-2 06:00 | 2009-2-2 14:...
How do I delete from multiple tables using INNER JOIN in SQL server
In MySQL you can use the syntax DELETE t1,t2 FROM table1 AS t1 INNER JOIN table2 t2 ... INNER JOIN table3 t3 ... How do I do the same thing in SQL Server? ...
SQL function question
Hi, having trouble coming up with search parameters for this issue, so I can't find an answer on my own. Column X | Message (info 1) | Message (info 2) (info 1) | Above is the contents of one column I need to handle. The result of the query should be the part INSIDE the parentheses only. Problem is,...
Using table just after creating it: object does not exist
I have a script in T-SQL that goes like this: create table TableName (...) SET IDENTITY INSERT TableName ON And on second line I get error: Cannot find the object "TableName" because it does not exist or you do not have permissions. I execute it from Management Studio 2005. When I put "GO" between these two lines, it's working. But ...
Changing SQL CONSTRAINT Based On Other Fields
I have an SQL table written in MSSQL: create table [action] ( action_id bigint identity not null, -- PK action_action char(1) not null, -- 'C' Call, 'R' Raise, 'F' Fold, 'P' Post action_size decimal(9,2) not null, -- zero if fold, > zero otherwise constraint pk_action primary key clustered (action_id), constraint...
SQL server write once column
Is there any way to configure a SQL server instance to not permit updates to values inserted in the database? e.g. once inserted, the value is fixed, other columns in that same row may be changed, but that value is only written once. ...
t-sql select question
Hi, I have a table in a sql server 2008 database that contains bunch of records as well as a date column. The date is inserted automatically when a new entry to the table occurs. So, it contains the date of the record that has been created. I am trying to run a query that would return me the earliest date and the latest date in this t...
TSQL Return Guid that occurs the most in a column
If I have a table with two columns, e.g. Name, and ID, where ID is GUID's, how do I return the Guid value from Column 2 that occurs the most? ...
How to join two tables together with same number of rows by their order
I am using SQL2000 and I would like to join two table together based on their positions For example consider the following 2 tables: table1 ------- name ------- 'cat' 'dog' 'mouse' table2 ------ cost ------ 23 13 25 I would now like to blindly join the two table together as follows based on their order not on a matching columns (I ...
SQL storing strings
I need to saving strings in database. Each string has different size from 1 to N. I think that limit will be about 10000, maybe more. nvarchar(MAX) will be best way to resolve this ? ...
All stored procedures listed by create date?
Anyone know a simple query to grab a list of all stored procedures in a SQL 2005 database ordered by createdate? ...