Could someone tell where I can find out if it's true that during UPDFATE SQL Serve deletes data from table, and then inserts new one. Thanks -A ...more >>

Hi i have a table with a date & week field the week number is 1,2,3 or 4 so as you scroll down the date there is 7 1's then 7 2's then 7 3's then 7 4's then 7 1's & so on. This process starts from a specific date. is there any way to do an update statement on this ? Thank you...more >>

Hi All, I am just wondering if what I am doing would be considered the correct way to insert data into multiple tables when a forigen key is in place between the tables primary keys. Here is a simple DB structure Table 1 ID int (auto incriment) Primary Key FirstName varchar(100) LastNa...more >>

I am using MS SQLServer 2000. One column in a table is Account No. for an Accounting Application is a VARCHAR(50) and values are like: 1120 1 4 2340 345 85 557548609121 By using the following command SELECT SUBSTRING(A.AccountNO,1,4) + CASE SUBSTRING(A.AccountNO,5,4) WHEN '' THEN ...more >>

I have a field that is PK field. the field is varchar (6) not null. the field should accept up to 6 characters and I am trying to guard against some one keying a space in the field. how can I make sure that the code entered in the field must be 6 characters, no spaces? thanks Al...more >>

I need to crate a query to get the 3 most recent sales dates from a table. The table would include CustomerID OrderID SalesDate SlaesAmount If a customerID has less than 3 last sales, corresponding columns can be null Cust1 Date1 Date2 Date3 Cust2 Date1 Date2 Null Cust3 Dat...more >>

I'm having problems using UPDATETEXT for the first time. Trying to follow BOL. I want to add the text from #PSIT.SI to the text in PSIT.SI starting at 0 position. Both are TEXT fields. Basically, I just don't get it... What am I doing wrong? I get this error: Line 6: Incorrect syntax near '...more >>

Good afternoon, everyone. I'm trying to speed up a function, and I could use an expert opinion. I have a table that stores searches, with one column for each criteria, holding either a value to search for or NULL if that criteria doesn't apply. I also have a table function to retu...more >>

hi, I have a select statment wlll return a table data, how can I email it out using sql? Thanks. ...more >>

I have a table that contains the following tbl.incident, tbl.timestarted, tbltimeended, tbl.crew Nearly everytime I have more than one crew involved on an account and so when I query for an account number I get at least 2, sometimes as many as 5 records. I need a way to return only the incid...more >>

I'm trying to collect the database status of about 150 linked SQL Servers (2000 & 2005) for centralized monitoring. My central server is SQL Server 2005. I'm unable to enable allow network DTC access on many of the remote servers. I tried sp_dboption and DATABASEPROPERTYEX, but unable to exec...more >>

I have a table with fields as follows: ID Value Value1 123 10 123 10 567 35 567 35 What I want to end up with is the following: ID Value Value1 123 10 10 123 10 0 567 ...more >>

Outside of security concerns (Example: Employee table has a SSN column, and I don't want to give permissions to the Employee table to users and I create a view that has all the columns except SSN on it, and grant permissions there) are there any good reasons to use them? I've been usi...more >>

Compare the following two queries: select * from noteMeta where noteMeta.noteTypeEnumID = 89 select * from noteMeta left join notes on noteMeta.noteMetaID = notes.noteMetaID where noteMeta.noteTypeEnumID = 89 and isnull(notes.fundsID, 60) = 60 The first query returns 15 row...more >>

If it put everything in a HAVING section of my query even if it parts could or should go in the WHERE section, is there a significant performance hit? Or does SQL Server optimize for this? Thanks. ...more >>

Hi, what's the diff between OPENROWSET and OPENROWsource to select records from a remote database table. Thanks ...more >>

TBL_1: K_ID, U1_ID, U2_ID, U3_ID TBL_2: U_ID, UserName I need to replace the Ux_ID in tbl_1 with the actual UserName from tbl_2. In stead of writing nested select and inner joins between these two tables. Can I have one join to complish it? K_ID, U1_UserName, U2_UserName, U3_Username....more >>

got varchar field, need to strip all characters to the left of an &. May be 5 characters, may be 10. I tried PARSENAME(REPLACE(myfield, '&', '.'), - ?) - but can't get it to work. I can get the charas to the right , but no left. Also tried REVERSE. but alwasys comes up null. ...more >>

please if any know how can I do, our problem is , we have 2 servers, on each server we have SQL and different databases but we want get info from one and make some querys and combine tables from those servers and honesty I have not to much experience working with two diferent servers , is anyb...more >>

Hello, My company has a trigger on one of our tables which is used to send update messages to a 3rd party application. We have found that on one of our customers system, this trigger disables it's self randomly. Is this a known issue for 2005? Any idea's what might cause this? Carlo ...more >>

Hello there I have a problem with a giant 'for xml explicit' query. The query contains 47 union all's in order to generate the required xml which is consumed using ..net. The query is in a stored procedure. The first time it is run it can take anywhere between 30 to 90 seconds to compl...more >>

Hi i am facing probelms while executing following stored procedure. I tried to print what exactly is error i am building update statement when i execute by passing projectid as 1 and area as 'a' i am getting result as Update dbo.LEAK_PROJECT Set where Project_Id = 1 (which is wrong) but right...more >>

Hi All, I have a following update statement that returns an error: update job_schedule_rep set tlbkup = (select Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), tl.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7) as 'Transactio...more >>

We're getting CSV files in from disparate sources. The level of inconsistency is disheartening. The same source will sometimes send the file with column headers, sometimes not. Is there some way to check for the existence of the headers - all alpha characters - versus data - mix of alpha ...more >>

I believe that "Inner Join" in the default for "Join". My question is if it is more correct to use "Inner Join" in a select statement (with joins) instead of just "Join", or is it just a matter of one or the other? Thanks, Rich...more >>

How can I BULK INSERT a fixed width file so that rows are in order? I currently do this without a format file and a single column table. But, does it need a format file for row order? Column order is a no-brainer: 1 column: the entire row. I require row order because multiple lines of va...more >>

Our web site performs numerous updates, inserts, deletes and the site is quite heavily used. From time to time users are receiving an unusual error along the lines of "can not perform update when object is closed" when an I/O occurs. We're concerned that we're not managing our cursors correctl...more >>

I am trying to put together a recurring charge system that looks at a transaction file to find out who owes money based on the date of their creation date and last transaction date. I am trying to get a couple of different results The Data is: CREATE TABLE [dbo].[TestCompany] ( [CompanyI...more >>

I came across this query at work that uses the "with" keyword which I have never seen in t-sql and I was just wondering why it would be used this way - or any way, because my first instinct would be to use a temp table - however I am still learning best practices and would like to know if this...more >>

New SQL programmer, please assist me. In the code below, I am attempting to replace a particular string with another within a text field...however, when I run the code, I receive the error message: Could not execute statement. Incorrect syntax near updatetext. set nocount on declare @textpo...more >>

We have an application that runs on both SQL Server 2000 and SQL Server 2005. On SQL Server 2000 the following queries produce exactly the same execution plan: Query 1: SELECT * FROM dbo.lfn_getactivegroups(23001) Query 2: DECLARE @iduser int SELECT @iduser = 23001 SELECT * FROM dbo.lfn_...more >>

Hallo, the following statement does not work. create procedure usp_testtemptable as begin select * into #tmptable from sysobjects select * from #tmptable drop table #tmptable select name, id into #tmptable from sysobjects select * from #tmptable end I'll get the ...more >>

Important performance differences with record numbers in SQL Server 2005. I have a stored procedure which executes the CLR in the SQL Server 2005, this procedure executes a local loop of consultations as per the parameters sent, the consultations are executed over 4 tables, after a few tests...more >>

-- Copy paste it will work -- DECLARE @HouseTypeCollectionXML XML SET @HouseTypeCollectionXML = ' <ArrayOfCType> <CType> <Key>1</Key> <Description>Site Built type 1</Description> <ResTypes> <RType> <Key>1</Key> </RType> <RType> ...more >>

Hi I want to implement the following functionallity into SQL Server 2005. A stored procedure sp_PostEvent(EventNameString) that can be called several times within a transaction. When, and only when, this transaction is commited one TCP-telegram per used EventName is sent, containing informatio...more >>