sql-server

SELECT FOR UPDATE with SQL Server

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...

SET NOCOUNT ON usage

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...

error while deleting from database

I have 2 tables MachineGroups and Machines. Machine Groups has values MachinegroupID MachineGroupName MAchineGroupDesc And Machines has values MachineGroupID (FK) MachineID MachineName Machinedesc Now i want to delete am machinegroup but it gives me an error because there are already values in it. So i want to de...

CTE vs. IN clause performance

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...

Get List of Computed Columns in Database Table (SQL Server)

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...

Deleting an entity from a table which is dependent on another table

I have 2 tables MachineGroups and Machines. MachineGroups has columns: MachinegroupID MachineGroupName MachineGroupDesc And Machines has columns: MachineGroupID (FK) MachineID MachineName Machinedesc Now I want to delete a machinegroup but not the ones that have machines in it. So if there are machines it should give an error mes...

SQL Server / JDBC Connectivity Issues

I am experiencing some strange behaviour in my Java server application whereby database operations that usually take a few milliseconds are sporadically taking much longer (30s - 170s) to complete. This isn't isolated to a specific query as I've seen the delays occurring for both SQL update and select statements. Also, all of my select...

How do I load text files into database that are greater than the 64 kb buffersize limit?

I'm trying to load text files (.aspx, .cs, html, etc) into a sql server 2008 database. I'm able to load all files that are less than 64 kb so far. I have two questions; How do I get around the 64 kb limit, and is the method I'm using the best way to do this? Thanks for the help. Database: file_length int, file_path varchar(250...

How to report an error from a SQL Server user-defined function

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...

how to add current date time in bigint field...

I need to add the current date time in a bigint field in a database... and then display from that only the date in format: october 1, 2009. I am currently thinking of storing the value in string variable and then converting it to int... String s = DateTime.Now.ToString(); i dont know what to do next.. please help ...

deleting row from gridview

I have 2 tables MachineGroups and Machines. MachineGroups has columns: MachinegroupID MachineGroupName MachineGroupDesc And Machines has columns: MachineGroupID (FK) MachineID MachineName Machinedesc Now I want to delete a machinegroup but not the ones that have machines in it. DELETE FROM MachineGroups WHERE MachineGroupID NOT ...

Find specific byte combination inside NVARCHAR

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 ...

Implementing a database application for multilevel marketing

I'm doing a project in the field of multilevel marketing on .Net and SQL server. In the database it should save like a b-tree. How should i design a database? ...

Where to put the SQL logic

I have an existing SQL Server database whose structure I can't really change, although I can add stored procedures or new tables if I want. I have to write a stand-alone program to access the DB, process the data and produce some reports. I've chosen C# and Visual Studio as we're pretty much an MS shop. I've made a start at exploring us...

Linked server with space in table name

I have added an linked server from an SQL 2000 server to an SQL 2005 server which is a named instance. This has worked well until I have got to a table with a space in the name. It seems to not be able to resolve the object, and causes an error on the query. Is this a known issue with linked servers, I can't find anything mentioning it...

Update 1 record in TBL1 when all references in TBL2 are equal to a given value

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 ...

Incrementing int column in MS SQL Server table via SP

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! ...

Repeating Record Sequence using SQL

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...

Database design problem

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}...

Check if DateTime in DB is more than 90 days old via Stored Procedure

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. ...