Apologies if I get the terminology wrong. How do I define a constraint in MSSQL Server 2005 to enforce uniqueness in one column depending on another column? E.g. considering the last two columns: 1 A 1 2 A 2 3 A 2 <- Disallow because '2' has already appeared alongside 'A' 4 B 1 5 B 2 ...
Hi, If I write a sproc in this format, will sql server do this effeciently or should the sql 'weaving' be done on the server side (.net)? Note: this is just a rough idea of what my query looks like, there are more 'if' clauses the build up my query that I execute using 'exec' declare @sql nvarchar(4000) declare @order nvarchar(4000) ...
I am trying to retrieve a list of date strings ordered by date like this... SELECT DISTINCT CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDate FROM dbo.ProviderProcessGeneratedDate ORDER BY GeneratedDate This orders by the varchar that I converted the dates to. example... 02/01/2008 02/15/2008 02/21/2007 02/23/2007 02/29/2008 ...
I'm wondering how others handle this situation... and how to apply the Don't Repeat Yourself (DRY) principle to this situation. I find myself constantly PIVOTing or writing CASE statements in T-SQL to present Months as columns. I generally have some fields that will include (1) a date field and (2) a value field. When I present this bac...
I have a temp table I am creating a query off of in the following format. That contains a record for every CustomerID, Year, and Month for several years. #T Customer | CustomerID | Year | Month ex. Foo | 12345 | 2008 | 12 Foo | 12345 | 2008 | 11 Bar | 11224 | 2007 | 7 When I join this temp table to another table of the followin...
I have a table that contains a number and a range value. For instance, one column has the value of 40 and the other column has a value of 100 meaning that starting 40 the range has 100 values ending in 139 inclusive of the number 40. I want to write a tsql statement that expands my data into individual rows. I think I need a cte for th...
I want to construct a transact sql script that will stop specified people from running certain commands against all databases: drop database, drop table or preferbly drop * delete update Is this possible? The user will already have access to the server. Note : I am not trying to develop a security model for a server, or to prevent...
I was wondering what you would use to scrub a database of all test data (leaving the structure intact) prior to going into production? I use something like: -- disable referential integrity EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable ' IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRe...
What is the best way to append to a text field using t-sql in Sql Server 2005? With a varchar I would do this. update tablename set fieldname = fieldname + 'appended string' But this doesn't work with a text field. ...
Question Hello All, I've had some confusion for quite some time with essentially flooring a DateTime SQL type using T-SQL. Essentially, I want to take a DateTime value of say 2008-12-1 14:30:12 and make it 2008-12-1 00:00:00. Alot of the queries we run for reports use a date value in the WHERE clause, but I either have a start and en...
Main problem is about changing the index of rows to 1,2,3.. where contact-id and type is the same. but all columns can contain exactly the same data because of some ex-employee messed up and update all rows by contact-id and type. somehow there are rows that aren't messed but index rows are same. It is total chaos. I tried to use an in...
Is it possible to update more than one local variable in a single select? Something like: set @variableOne = avg(someColumn), @variableTwo = avg(otherColumn) from tblTable It seems a bit wasteful to make two separate select operations for something as trivial as this task: set @variableOne = ( select avg(someColumn) from...
I was wondering what is the best way to parse a DateTime object to your SQL server. Where you are generating the SQL in code. I have always used something like DateTime.Now.TolongDateString() and had good results, apart from today where i got a error, and it made me think. System.Data.SqlClient.SqlException: Conversion failed when ...
Hello. I need to wrap a few strings in single quotes for a dynamic TSQL statement in a stored procedure. I am absolutely certain that no single quote values will be passed(these fields are not "editable" at the application level, only selectable) hence my requirements are pretty mild in this aspect. The solution I came up with is simpl...
I have a table of values like this 978412, 400 978813, 20 978834, 50 981001, 20 As you can see the second number when added to the first is 1 number before the next in the sequence. The last number is not in the range (doesnt follow a direct sequence, as in the next value). What I need is a CTE (yes, ideally) that will output this 97...
a friend of me is asking if i know any reasons why he should not included Temporal or variables tables inside a View in SQL-Server 2000. i could only think that a new table will be created everytime the view is accessed but i am wondering is there are any other concerns. Thanks, EDIT: This is not Possible ...
How do I do a Case WHEN in Linq to SQL (vb.net please). In SQL it would be like this: SELECT CASE WHEN condition THEN trueresult [...n] [ELSE elseresult] END How would I do this in Linq to SQL? ...
I would like to execute a stored procedure over each row in a set without using a cursor with something like this: SELECT EXEC dbo.Sproc @Param1 = Table1.id FROM Table1 I am using T-SQL in SQL Server 2005. I think this might be possible using a function, but I'd like to use a stored procedure if possible (company standards) ...
I can't seem to find a relevant example out there. I'm trying to return a sub-set of a table, and for each row in that table, I want to check how many children it has, and return that number as part of the result set. Parent Table Columns: PK_ID, Column1, Column2, FK1 For each FK1 in result set, select count(*) from child_table. Fina...
Assume I have two data tables and a linking table as such: A B A_B_Link ----- ----- ----- ID ID A_ID Name Name B_ID 2 Questions: I would like to write a query so that I have all of A's columns and a count of how many B's are linked to A, what is the best way to do this? Is...