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

Hi all, Its great to see that so many ppl helpout each other. I hope I can do the same on day :), anyway Can any one please guide me that how can i make a runing column in result set e.g. i have a query select au_id,au_lname,au_fname from authors which returns au_id a...more >>

Hello, How do I set the column of a table so that where it equals null it gets populated with a certain value. What I have tried is: update HAIRCUT set HAIRCUTCD = '001' where HAIRCUTCD = NULL and update pipe_pipeNo set ContractId = '001' where ContractId = 'NULL' and upda...more >>

Hello, I've an issue with converting varBinary data into other data types. The column definition is varBinary(2498). How do I pull the data out for a user? Here's what I've tried thus far: DECLARE @myval decimal (5, 2) SET @myval = 193.57 Insert INTO Test_Tbl1(varBintst) ...more >>

When I look at the execution plan of a query all steps are 0%. Now when I change my local settings from Dutch to United States it comes up correctly (same query of course). See here for Dutch: http://www.entrysoft.com/be.jpg Here for US: http://www.entrysoft.com/us.jpg Is this a known bug?...more >>

Hi, I know how to allocate a table to a particular user defined filegroups, while creating it. But how can one allocate other data objects like Stored procedure, functions, views to a particular filegroup Thanks in Advanc GYK...more >>

Sorry for reposting but I have removed all fields and tables that weren't necessary for this problem so it will be much easier now :) DLL: CREATE TABLE [dbo].[CodeLink] ( [CL_ID] [int] IDENTITY (1, 1) NOT NULL , [CL_COID] [smallint] NOT NULL , [CL_SNID] [int] NOT NULL , [CL_Begin] [sm...more >>

search word: ok if i use SELECT * FROM sometable WHERE someColumn like %searchword% it will return results that include these characters ex: look I would like to search the word "ok" in the db as a whole word. YES: dsfd dfdf ok dfdf NO: dfdsf look dsfsdf...more >>

Hello, Table 1: MainID Qty Message 1 3 "Apple" 2 5 "Orange" 3 5 "Banana" Table 2: MainID SubID 1 "A" 1 "B" 1 "B" 1 "C" 2 "F" 2 "F" 2 "H" 3 "A" 3 ...more >>

I have table with columns of start and end date. startDate endDate ID 2003-11-24 23:00:00 2003-11-24 23:10:00 1 2003-11-24 23:15:00 2003-11-24 23:20:00 1 2003-11-24 23:25:00 2003-11-24 23:35:00 ...more >>

Hi I know it's possible to do the following, but not sure of the SQL required. I've two tables, table A and table B Table A has amongst others, two columns 'person' and 'address' Similarly, Table B has amongst others, two columns 'person' and 'address' What I want to do is to get a sing...more >>

Hi, Does anyone know how I can reset an auto-increment field in a table (Microsoft SQL Server). So that it starts counting from 0... again? Thank you Maz. ...more >>

Is there a way within a curor to wait for a command to complete before fetching the next row? declare victim_cursor cursor for select spid from mast.dbo.sysprocesses where spid >=@spidlow and spid <= @spidhigh and db_name(dbid)='OnePoint' open victim_cursor fetch next from victim_cursor ...more >>

Hi, I tried to use table variable in sp_executesql statement, it failed. So I assumed that table variable can't work with sp_executesql because sp_executesql doesn't treat table variable as "variable" . Can you confirm this? thanks Joe...more >>

My db table looks like this FILENAME FILE_CREATED FILE_MODIFIED test 9/12 test2 9/13 10/13 .. .. all FILE_CREATED field have value, only some FILE_MODIFIED have value. I need to get the value of FILENAME column and this is what i have SELECT FILENAME FROM REW_FILES ...more >>

Is there anything in SQL Server 2000 that allows me to pull the greater of 2 columns in a query? Example. I have a row with 3 cols. In col1 is a generated key. Col2 has a date and Col3 has a date. In my query I want to return only the generated key and the column that has the greatest...more >>

I have a sql server table with a unique id for an individual and 11 languages, if they speak the language the value if 1 if they don't it is 0. How do I just query to get the individual and the first 5 languages they speak? I do not code well, and this is really baffeling to me. THANKS!!...more >>

I have three different stored procedures that have been performing very poorly. We finally tracked the problem down to SQL Server choosing a sub-optimal index in the query plan. For some reason it is insisting on choosing a clustered index scan instead of utilizing another index. We are havin...more >>

Our users have potentially dirty legacy data that they need to get into our application. We provide a set of staging tables, which map to our source tables, that the users do their ETL into. Every row in the source tables has a generated integer id. Every row in both the source and stag...more >>

Hello I have a function written in C that gets a string as input and returns another string that is manipulated by the function according some rules. I need to make an index on a field in a table in sql server 2000 based on the output of this function. How can I make it? I also need to write ...more >>

Looks like my last post didn't take. I received the following code from Pat (thank you!) CREATE TRIGER tiMyTable ON MyTable FOR INSERT AS UPDATE myTable SET strata = Rand() WHERE EXISTS (SELECT * FROM updated WHERE updated.SID = myTable.SID) EXECUTE sp_sendmail ...more >>

sp_depends doesn't return objects outside of current db how do I write a query that returns all objects names inside a procedure? for example, sp_find_objects @proc='get_employee_info' ...more >>

Hi All Wondered if you could help. Basically I have 2 tables that contain all the data I want for my report, but I need to put it in a particular way and I need to display it in an ASP page so my queries got to be manual rather than an MS Excel/Query 'munge'. To be honest, the report itse...more >>

Dynamic Query for Searching data I have a stored procedure which takes three parameters. The parameters would be null or would have values. Currently I am doing like the below listed code and it takes a long time to execute because of the Varchar conversions. Is there an easier and fast...more >>