With the help of others on SO I've knocked up a couple of Tables and Stored Procedures, this morning, as I'm far from a DB programmer. Would someone mind casting an eye over this and telling me if it's thread-safe? I guess that's probably not the term DBAs/DB developers use but I hope you get the idea: basically, what happens if this s...
Using SQL Server 2000: SELECT PERSONID, CARDEVENTDATE, INTIME, CASE WHEN OUTTIME = INTIME THEN 'No PunchOut' ELSE OUTTIME END AS OUTTIME, CONVERT(char(8), CASE WHEN DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) > '18:00:0...
I'm using a Microsoft SQL Server 2005 database with isolation level READ_COMMITTED and READ_COMMITTED_SNAPSHOT=ON. Now I want to use: SELECT * FROM <tablename> FOR UPDATE ...so that other database connections block when trying to access the same row "FOR UPDATE". I tried: SELECT * FROM <tablename> WITH (updlock) WHERE id=1 ...bu...
Inspired by this question where there are differing views on SET NOCOUNT... General accepted best practice (I thought until this question) is to use SET NOCOUNT ON in triggers and stored procedures in SQL Server. We use it everywhere and a quick google shows plenty of SQL Server MVPs agreeing too. MSDN says this can break a .net SQLDat...
Alright SQL Server Gurus, fire up your analyzers. I have a list of titles in application memory (250 or so). I have a database table "books" with greater than a million records, one of the columns of the table is "title" and is of type nvarchar. the "books" table has another column called "ISBN" books.title is not a primary key, is not...
Hi guys, Would any of you know how to get the list of computed columns in a SQL Server database table? I found sys.sp_help tablename does return this information, but only in the secord resultset. I am trying to find out if there is a better way of doing this. Something which only returns a single result set. Any help is very appre...
I'm writing a user-defined function in SQL Server 2008. I know that functions cannot raise errors in the usual way - if you try to include the RAISERROR statement SQL returns: Msg 443, Level 16, State 14, Procedure ..., Line ... Invalid use of a side-effecting operator 'RAISERROR' within a function. But the fact is, the function takes...
We found out that we have a bunch of french strings with incorrect characters. We know that the hexadecimal representation of the character is 0xFDFF. Is there an easy way to SELECT the rows in a table where the NVarchar field contain this character? Thanks ...
Hi, I have two tables, 1 indexing the details of an email campaign. The second table details the recipients of this campaign, and whether they have responded to the email etc. I need to create a stored procedure that can update the status of the 'master record' in TBL1 when all the references(recipients) in TBL2 have a status >1. The ...
Is there a T-SQL statement to auto fill an empty column in a table with incremented values starting at one specific value? E.g. UPDATE A SET A.NO = ROW_NUMBER() OVER (ORDER BY A.NO) + @max WHERE A.NO is NULL This statement doen't work and I don't know how to make it run... Any help is appreciated! ...
Hi, This could easily be done using code, but I wondered if it could be done at the database level using SQL Server (2008). I have a table similar to below: CROP_ID YEAR_ PRODUCTION 1 1 0 1 2 300 1 3 500 2 1 100 2 2 700 I want to be able to run a que...
I have a problem creating a database schema for the following scenario: (I’m not creating a dating site but just using this as an example) A user logs on to a dating site and is given a multiple selection for the hair colour they’d like their date to have: This is easy enough to model with the three tables below: Tables: User {key}...
UPDATE Evidently I didn't include enough data, sorry! What I need to do is set 'campaign_Status' = 6 when 'campaign_Date' is more than 90 days old. Hi, I have a column (campaign_Date) which stores a DATETIME. Using a Stored Procedure I need to check if the stored date is 90 days old (or more). Any help would be great. Thanks. ...
I have a bit of a puzzle (at least for me) which I am hoping is mostly because I am not yet an SQL master of the universe. Basically I have three tables: Table A, Table B, and Table C. Table C has a FK (Foriegn Key) to Table B, which has FK to Table A. (Each of these is many to one) I need to remove an entry from Table A and of cours...
What's the most memory effective way to read an SQL 2005 image field using C# 3.5? Right now I have a (byte[])cm.ExecuteScalar("..."). If I could not read all field content into memory, that would be nice. ...
How do you say the following in Microsoft SQL Server 2005: IF EXISTS (SELECT * FROM Table WHERE FieldValue='') THEN SELECT TableID FROM Table WHERE FieldValue='' ELSE INSERT INTO TABLE(FieldValue) VALUES('') SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY() END IF What I'm trying to do is to see if there is a blank fie...
If there are no begin and end statements in sql, the next statement is the only one that gets executed if the if condition is true...in the case below, is there anyway the insert statement will also get executed if the if condition is true? IF (a > 1) SET @b = 1 + 2 INSERT INTO #F (a, b, c) VALUES (1, 2, 3) ...
How can I create two temporary tables with the same structure without write twice? Something like that: DECLARE @TEST_TABLE1, @TEST_TABLE2 TABLE ( FIELD1 INT, FIELD2 INT ) and NO: DECLARE @TEST_TABLE1 TABLE ( FIELD1 INT, FIELD2 INT ) DECLARE @TEST_TABLE2 TABLE ( FIELD1 INT, FIELD2 INT ) ...
We can see schema for all tables and views by: SELECT * FROM INFORMATION_SCHEMA.TABLES SELECT * FROM INFORMATION_SCHEMA.VIEWS Can we view schema for stored procedures or functions through tsql? ...
If I have the following table: CREATE TABLE #temp ( id int, num int, question varchar(50), qversion int ); INSERT INTO #temp VALUES(1, 1, 'Question 1 v1', 1); INSERT INTO #temp VALUES(2, 1, 'Question 1 v2', 2); INSERT INTO #temp VALUES(3, 2, 'Question 2 v1', 1); INSERT INTO #temp VALUES(4, 2, 'Question 2 v2', 2); INSERT...