Dear All: I can reference old value and new value in a trigger using Oracle as belews: CREATE OR REPLACE TRIGGER trigger_1 AFTER INSERT OR UPDATE ON table1 REFERENCING NEW AS n OLD AS o FOR EACH ROW WHEN (n.organization_id IN (82, 83)) DECLARE v_neworgzid VARCHA...more >>

I want to create an 8 char string for today's date in the format 'YYYYMMDD' using T-SQL <not VB>. Also, is there functionality like DATEADD in VB for TSQL. How might you get a date three days from now? TIA and Happy New Year. George. ...more >>

I have a process that can potentiall affect alot of records. I was testing this out and it seems that when I let it go on a large number of records it takes along time. The time it takes seem to start of linear until you get to a point and then it becomes an exponential increase in time. I was...more >>

hi all, I have two columns ProductType text and and Countries varchar 250 (both types can still be changed). The columns contain similar structure of data: strings of coma delimited lists for example: 12,13,14,15,53,77,87,98 I would like to find all rows where ProductType contains the num...more >>

Hi Is it possible to perform two Replacements in the same statement? For example I have a value c:\test.do I want to replace 'c:\' with '' where 'c:\' and replace '.doc' with '' where '.doc' exists. The directory name and file extension may or may not be part of the column contents for any...more >>

Has anyone experienced sp_updatestats drastically slowing down complex = queries that create a large Dynamic SQL? =20 If I rebuild the indexes on the table, this query returns in 2 seconds. = If sp_updatestats is run, the same query, same parameters, etc. takes = between 17 and 27 minutes. ...more >>

A table with a primary key on a datetime column does not order the resultset when I perform the following query. SELECT pkColumn FROM myTable WHERE otherColumn = '123' Here is the 27 row resultset as shown in query analyzer. While I did not sepecify an ORDER BY, I have assumed that the exis...more >>

i am working with the following table create table stockTick( tickID int identity(1,1), securityID int, timestmp datetime, price money, volume int ) this table stores intraday price and volume data for common stocks. there are 11 million rows in this table. th...more >>

First, happy new years to all :) Got a problem with a SP. I have been researching this issue most of the day but have found no answer yet. My application is calling a stored procedure through ADO. It is done in C++ and the call of the SP happens inside a COM object called from the GUI. The ...more >>

All, I have a VB 6.0 process scheduled on SQL server 2000. It does not pop up the program Window. What's missing? Thanks, Fred Esmaeili...more >>

I think this is a relatively easy question: I have a table that contains duplicate records. I confirmed this by running query: select recid, count(*) from table group by recid I'd like to remove the duplicate records and leave the original using an SQL query but I don't know how to app...more >>

I have function which returns the first free start time and first free end time in limits, you enter. It works OK just in this case it returns: start_date='2003-11-24 09:41:00.000' and end_date='2003-11-24 09:41:00.000' what is wrong, it should be: start_date='2003-11-24 11:55:00.000' and end_da...more >>

I have a stored procedure (SP1) that is erroring out, but not returning the error. SP1 calls SP2 inside itself and in that one it calls SP3. I purposfully put in SP2 an error to try to trap it in SP1. Well I have included the code after SP2 is called: IF @@ERROR <> 0 RAISERROR('Test Er...more >>

Hi all, I have been given the task of writing a notification application. I work in one of the primary care trusts in the NHS (UK), the current process for notifying staff of urgent matters is to spam all staff in the trust (2500) with an email - obviously a LOT of people receive these that d...more >>

2 questions: 1. ALTER TABLE statements cannot be issued for temporary tables. True of False? 2. ALTER TABLE statements cannot be issued for table variables. True of False? I'm pretty sure 2 is False. I get an error when I try 1 so I'm thinking it's false too but would like a confirmation....more >>

I was thrown into admin for the SQL boxes here and had to move a database from onw box to another. Now I thought I had everything moved but I missed a DTS. I have the source and destination created, but what I need is the syntax for updating. What I am trying to do is create a DTS that ...more >>

Hoping somebody can help me out here... I've got a couple of column in a table that I would like to constrain their values. The first column is nvarchar(4) and can contain A, B, C or D in any combination. Each letter can appear once or not at all, and in any order. For example, A, ABD and D...more >>

I know that I can put all sorts of programming constructs in Transact SQL but is it possible to put an if-then-else into a normal sql statement? I want to put a conditional into a view creation. I have done this sort of thing before with ISNULL and COALESCE. I want to say something like: If ...more >>

I have a table with line feeds in some of the fields. I need to export this data but drop the extra line feeds in those fields. I've tried to use REPLACE but apparently don't quite understand the syntax. Can anyone offer a suggestion Thanks...more >>

I have a stored procedure where if my cursor returns 0 I want to run a specific UPDATE and if it is not 0 then run a different UPDATE statement. How do I do the if statement in a cursor SET @MyCursor = CURSO FOR SELECT dbo.tblTagPrint02.RETAIL_SKU, dbo.cp_sku_qty_retail_price.effective_time...more >>

I want to retrieve filename and file modified datetime from a directory. how can i accomplish this. i tried dir \b but i don't get modified datetime thanks ...more >>

Is there a recommended place to get database design standards for an industry? I am specifically looking for information on standard data collected for insurance type databases. Any advice appreciated -- Ray Higdon MCSE, MCDBA, CCNA -- ...more >>

sql2k So the lead developer has just informed me that he wants our users to be able to specify the criteria they will need in reports on the fly. (the where clause) What he wanted to do was make our procs totally dynamic sql. My suggestion instead was to use a view + a proc with dynamic ...more >>

I created SQL statement listed below to list all object permissions on the public role except the select permission (193). I would like to add to the SQL statement listed below to eliminate the system object permissions. Please help me with this task. Thank You, Dean selec...more >>

I am using the following line to export data from SQL Server to a text file. This has been running without any problem for a couple of months. We had to reset the server the other day and since then the BCP fails with the error below any idea Command Used: SET @Qry = 'SELECT LineValue FROM myD...more >>

Hi All, I created my own replication that brings 20 different SQL2000 SP3 databases into one huge 20gig database. I have customer names in a varchar(50) field. Each of my 20 branches entered names in differently. Ex. Wawa store 0001.. Wawa 0002..wawa 987 Anheuser Busch Incorporated.Anhe...more >>

In ORACLE you can declare datatypes implicit like PROCEDURE myProc (myParam myTable.myColumn%TYPE) Is there any way to do the same in SQL Server ? The problem is, if I declare datatypes explicit e.g. myProc( @myParam VARCHAR(50) ) and the datatype of the retated table-column is modified, ...more >>