I'm not even sure how to even phrase this as it sounds weird conceptually, but I'll give it a try. Basically I'm looking for a way to create a query that is essentially a WHERE IN LIKE SELECT statement. As an example, if I wanted to find all user records with a hotmail.com email address, I could do something like: SELECT UserEmail FR...
Hello, I have a table which has a column called Direct of type char(1). It's values are either 'Y' or 'N' or NULL. I am creating a view and I want the value to be transformed to either 0 or 1 of type bit. Right now it's of type INT. How do I go about doing this? Following is the code: CASE WHEN Direct = 'Y' THEN (SELECT 1) WHEN...
Hiya, I don't know if this can be done but I thought I'd ask. What I want to do is have a case statement query and if a 1 begin another action. if 0 don't do anything. For Example select CASE WHEN client.deathofdeath = yes THEN 1 do another select in here (which is another table) Else 0 End AS DeathDate From Client client...
Hi http://msdn.microsoft.com/en-us/library/ms189797.aspx In this link they are committing a transaction within catch clause IF (XACT_STATE()) = 1, I don't get it, if there is an error why they are committing it? even if the problem in select statement and there is no big deal committing it, why don't just roll it back. Thanks ...
Which approach is better to use if I need a member (sp or func) returning 2 parameters: CREATE PROCEDURE Test @in INT, @outID INT OUT, @amount DECIMAL OUT AS BEGIN ... END or CREATE FUNCTION Test ( @in INT ) RETURNS @ret TABLE (outID INT, amount DECIMAL) AS BEGIN ... END What are pros and cons of each approach con...
Using SQL Server 2008, is there a way to allow inserts to a table only via a stored procedure, and if so how? EDIT: The best way to go is probably Martin Smith's suggestion to use an INSTEAD OF INSERT trigger. The direct answer to this question is marc_s' one with GRANT and DENY, though it won't limit some user accounts. ...
Which is faster SELECT * FROM X INNER JOIN Y ON x.Record_ID = y.ForignKey_NotIndexed_NotUnique or SELECT * FROM X INNER JOIN Y ON y.ForignKey_NotIndexed_NotUnique = x.Record_ID ...
I'm using SQL Server Express 2008 and I'm doing a bulk insert of data. I'd like to have more verbose error messages, ideally printing the data that failed to be inserted. Is that possible? ...
We have a strange problem in our team. When a developer is using Eclipse in Debug mode, SQL Server 2005 blocks other developers from accessing a stored procedure. Debug session typically involves opening Hibernate session to persist an entity which could be accessing a stored procedure used for Primary key generation. Debugging is done i...
While renaming the column name, the square bracket is included in the column name, which I think is a bug, Here is a sample code snippet, create table [TestTable] (TestColumnName nvarchar(30)) select TestColumnName from TestTable sp_rename '[TestTable].[TestColumnName]', '[RenamedColumnName]', 'Column' select [RenamedColumnName] ...
How can I see all the records that appear more than once per day? I have this table: ID Name Date 1 John 27.03.2010 18:17:00 2 Mike 27.03.2010 16:38:00 3 Sonny 28.03.2010 20:23:00 4 Anna 29.03.2010 13:51:00 5 Maria 29.03.2010 21:59:00 6 Penny 29.03.2010 17:25:00 7 Alba 30.03.2010 09:3...
How do you calculate the number of weeks between two dates? Declare @StartDate as DateTime = "01 Jan 2009"; Declare @EndDate as DateTime = "01 June 2009"; @StartDate and @EndDate ...
I've got the following table: patients id name diagnosis_id What I need to do is get all the patients with N most popular diagnosis. And I'm getting nothing using this query: SELECT name FROM patients WHERE diagnosis_id IN (SELECT TOP(5) COUNT(diagnosis_id) FROM patients GROUP BY diagnosis_id ORDER BY diagnosis_id) How to fix it? ...
I have a large database and am putting together a report of the data. I have aggregated and summed the data from many tables to get two tables that look like the following. id | code | value id | code | value 13 | AA | 0.5 13 | AC | 2.0 13 | AB | 1.0 14 | AB | 1.5 14 | AA | 2.0 13 | A...
Hi, lets say I have a script like that: if(some condition) begin select somecolumn from sometable end Lets say, that "somecolumn" does not exist and the condition is not true, which means the select is NOT executed. Even though the select would not be executed, the script is not valid, Management Studio complains about the missing ...
I need to get a list of all tables that are published for replication from MS-SQL 2005/2008 databases. Is there a system stored procedure or a query I could run to generate such a list? Thank you, ...
Take a simple table like below: Column Headings: || Agent's Name || Time Logged In || Center || Row 1: Andrew || 12:30 PM || Home Base Row 2: Jeff || 7:00 AM || Virtual Base Row 3: Ryan || 6:30 PM || Test Base Now lets say that a single cell is deleted so the table now looks like this: Column Headings: || Agent's Name || Time ...
I have a populated SqlCommand object containing the command text and parameters of various database types along with their values. What I need is a T-SQL script that I could simply execute that would have the same effect as calling the ExecuteNonQuery method on the command object. Is there an easy way to do such "script dump" or do I ha...
I create backups using the same .bak file. I've made a script to automagically restore this backup. RESTORE DATABASE [xxx] FROM DISK = N'xxx.bak' WITH FILE = 10, NOUNLOAD, REPLACE, STATS = 10 GO Here, the backup set to restore is explicitly specified. However, I always want to restore the latest set available. By default, it us...
I want to do some SQL Server performance testing and I want to establish a good baseline. I want to flush all the caches each time so that I know between each test it hasn't cached something so it performs better in between runs. Which DBCC commands should I use to make my SQL server as clean as possible? ...