Ok, I need to start reading from the beginning of a table. 2 conditions: While reading, if an endingdate column is less than todays date and another columns value is "y" I need to copy data from one field to another. This must all be done from within vb.net. Any help is appreciated. Thanks, T...more >>

Hi I have a system where each member can only have 3 records in a table. When a new row is created for a member, I need to delete the oldest record first so that there are never more than 3 records for a member. How do I do this in TSQL? Thank, S ...more >>

Hiya, I have an error in this function It is supposed to remove any non-numeric characters. a value of 3v345 would come back as 3345. But why does it return a NULL if the first character is non-numeric? i.e. v3345 comes back NULL rather than 3345 thanks!! CREATE FUNCTION [dbo].[S...more >>

Is there a query I can run to display the contents/code of a certain stored procedure? Regards, Alex -- ...more >>

Hello All: I have been tasked to move a moderate sized application from oracle to sql server. Oracle has a sequence mechanism for generating unique numbers in sequence for record keys. Is there a mechanism like this for SQL SERVER? The identity column will not help me because i need the key a...more >>

Do I have a problem, a lock must occur and it isn't happening. Please, lets with the war against my marketing department ;). I have a problem with locking on sql server 2000 sp3. I select one record from a table using the primary key on the where clause, inside a transaction with serializabl...more >>

I need to get the current date as a varchar in the following format yymmddhhmmss What's is the quickest way to do this? Thanks S ...more >>

I have a Stored Procedure where I need to concatenate in order to build a Query, but it gets soo long... Up to 255 bytes, so, I need to know how to store a string like these. I have tried to use TEXT and NText but I can not use them on Stored Procedures. Please help me Carlos Lee ...more >>

Hello: I am trying to store C# decimal datatype values in SQL Server and seem to be having some difficulty. For example, if I set a a C# variable to 11.75M and attempt to persist that value to a decimal database field with a precision of 19 and scale of 2, the value is stored as 12 instead ...more >>

I have a problem with locking on sql server 2000 sp3. I select one record from a table using the primary key on the where clause, inside a transaction with serializable level. In another connection, I try to do a simple select * from sametable (also using serializable transaction level) and this ...more >>

Hello, I am having problems with the following trigger; CREATE TRIGGER [tr_Test] ON [dbo].[Test] AFTER UPDATE AS EXEC master..xp_CmdShell "C:\DB-2.vbs" What I am hoping to achieve here is executing DB-2.vbs after an update to the Test table. Currently this throws back an "invalid syn...more >>

Hello, I have two table and foreign key relation. Users to Equipment... I want to add Equipment users column null value.Bur because of the relation I cant. How can I add null value thanks ...more >>

Hello SQL Gurus, I am getting a very strange error message as follows in the Production. Cannot Start Transaction While in Firehose Mode Please give me some inputs about these. Thanks in Advance, PVRAJ...more >>

Hello All I have a stored procedure which runs on a sql server table after the data has been imported and creates 2 indexes on the records. There are about 2.3 million records in the table. It takes about 10 minutes to index these up. What should an optimal index time frame be - is there any s...more >>

I want to run a query to find the dependencies, preferably variable names and types, of user created stored procedures. I know that I can filter the stored procedures out of the sysobjects table, but I do not know where to find the dependancies. Any help would be greatly appreciated. Thanks, ...more >>

How to write a query to filter Just the date of field that has a Date/Time Data Type? For Example: Select * from TableName Where HireDate = 02/27/2004 The above does not work. However this does Select * from TableName Where HireDate > 02/27/2004 ...more >>

What is the best way to store $ value in SQL Server. I've looked in several DBs and they all use something different. Money, float, numeric, decimal. The $ values I'm storing goes into Fact tables and this values will be used for aggregation. Any suggesting on which datatype would be best...more >>

I have the following triggers that work on one server, but are not working on my present server. They just appear to be ignored. Any thoughts? thanks! ------------------------------------ CREATE TRIGGER tr_ExpYear ON Orders FOR INSERT AS BEGIN UPDATE Orders SET ExpYear = Right(ExpYear,2) E...more >>

Hi, I'm still very much a novice but hope someone will bare with me. I'm trying to do an update on a single returned row if it exists here is a sample that does not work UPDATE gnr_activity_signup SET status = 1 WHERE Exists (SELECT ALL TOP 1 gnr_id, wit_no, activity_id, date_time_re...more >>

I have an ISA server logging to a SQL server. I noticed that it also writes space (for example after desthost). So when a record is: www.msn.be it puts spaces behind the name til it get to 255 chars. I'm gonna make a trigger who chops of those chars but I just want to make sure: these spac...more >>

Hello everyone, I have a problem to place a order by .. Here an example in the northwind database select CustomerID, ShipName, OrderDate from dbo.Orders ORDER BY CUSTOMERID give the result : ALFKI Alfreds Futterkiste 1997-08-25 00:00:00.000 ALFKI Alfred's Futterkiste 1997-10-03 0...more >>

I have a TSQL function that does some processing of a comma delimited string. The function takes a string with a set of options for a vehicle and abbreviates some of them, removes others, etc. Here is a portion of the function as it is today. CREATE FUNCTION FixOptions (@OptionValue varchar(3...more >>

hi, i write a SP as below and use a transaction. but i found if failed to insert into tb2, then the calling function will got the error message, and can't rollback tb1's inserted record? Pls advice. thanks... SP: CREATE Procedure CreateNewUser .... BEGIN TRAN TranStart INSERT INTO tb...more >>

When I try to insert a value into my database like INSERT INTO OrderDetails (Description) VALUES ('John's computer order' I get an error Server: Msg 170, Level 15, State 1, Line Line 1: Incorrect syntax near 's' Server: Msg 105, Level 15, State 1, Line Unclosed quotation mark before the ...more >>

i have the following function FUNCTION JRFN_GetSearchIDBaseMatch ( @industry numeric(18,0), @location numeric(18,0), @worktype numeric(18,0) ) RETURNS TABLE AS RETURN ( select s.search_id=20 from jobseekersearch s where s.work_type_id =3D @worktype and s.industr...more >>

I'm working on an application that uses a series of dictionaries that we will provide to the end user, and will update on a quarterly basis. The dictionaries are also customizable to be more specific to the particular site using them. So my first problem is how to show the custom content inste...more >>

I am trying to create a script that will produce the following: (from authors in pubs) INSERT INTO Authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) SELECT '172-32-1176', 'White', 'Johnson', '408 496-7223', ',10932 Bigge Rd.', 'Menlo Park', 'CA', '94025', '1' ....more >>

How do I search for these any one of these characters ( #$%^/ ) in a varchar or char column, in one SQL line All I can come up with is using CHARINDEX, however this fn only appears to accept 1 character at a time. I DO NOT want the following SELECT * FROM MyTabl WHERE CHARINDEX('#', LastName) ...more >>

Hello All, Curious to know if it's possible to create a join between the inserted table in a trigger to a database in a linked server? I haven't been able to get this to work. example: IF Exists (SELECT distinct B.CustNumb FROM LinkedServerB.Database.dbo.Customers B ON B.custid = ins...more >>

is there a function for getting current in sql programming? i m new to sql so m not aware of all the function names. i have a database software that can produce reports using sql statements. i have a table in which each row will have a date. so i want to query only those which has current ...more >>

Hi I was wondering if anyone can point me in the right direction for creating my own extended stored procedures for SQL Server 2000? Languages I wish to develop it in is either VB 6 or VB.NET. Can anyone point me in the right direction? Cheers ...more >>

Hi, I have a question about CLUSTERED INDEX. When we use ORDER BY in a clustered index column , we found out that optimizer skips the sorting since it get the records in sorted manner. My question is that on multi-processor server does the SELECT return the rows in the CLUSTERED INDEX order, ...more >>

I need to load a string date data (from a text file) into a datetime type field. CONVERT(VARCHAR, COL1, 101) -- These one doesn't work because my destination field is a datatype and the load fails. No matter what style I use (100, 101, 102, etc) CONVERT(DATETIME, COL1, 101) -- This one, ...more >>

Is there a straightforward way to do this: create proc GetTopN @limit int as select top @limit from myTable where ... Thanks! ...more >>

Hello, Asume I have two tables, Employess and Payroll. Employes: IdEmp Fname Lname PayRoll idEmp PayDate PayAmount In Employess IdEmp is key, where as in PayRoll, IdEmp and PayDate are key (you cannot get paid more than once in one day). I need to Perform a query where I bring al...more >>

Hello Is there a way to use the Inserted and Deleted tables to tell what column was updated. I would like to create a table that records column updates, user, table, date and any other information. I have written audit trails using VB, but I would like to have them reside on the server Thank you...more >>

I have two tables that I'm trying to do a LEFT OUTER JOIN on, but I'm getting results that don't make sense. For example: Table 1 orgID, orgName 213, 'The First Organization' ....... Table 2 TshowID, orgID, boothNum, contact, active 1, 213, '', '', '', 0 2, 213, '', '', '', 0 3, 213, ...more >>

does there have any different between select * and select [field_name] which one is better in query speed?? thx~ ...more >>

Hi All, I'm totally confused. I have a SPROC that runs perfectly from SQL Query Analyzer: - CREATE PROCEDURE SP_CALLCENTRE_CUSTOMER_SELECT /* Name: SP_CALLCENTRE_CUSTOMER_SELECT By: Graham P. Blandford Date: February 25 2004 Purpose: Selects records from the CUSTOMER table ...more >>

Hi, How to configure that so when using trusted connection, the (windows account) users only can read (have read only permission) the table? thanks... ...more >>

Hello Is there a query or other way to see how much space my transaction logs has left before they fail?...more >>

If I've got: While drSQL.Read() functioncall() End While And the read comes accross with certain conditions that need to be changed, can I do this: strSQL = "UPDATE TBL_Items SET" During the read without sql.close and then continue with the read? Thanks, Trint ..Ne...more >>

I've been into SQL programming for about 6 months and learning more and more all the time. However, I need to get more organized. How do you pros catalog your code samples for easy referencing? I thought about text files, but that could produce a bunch of files. How do you guys keep everything ha...more >>

I am wanting to be able to get results from a date table for queries such as: - Every Nth week; monday, tuesday & thursday; between @Date1 and @Date2 - Every Nth month; saturday & sunday; between @Date1 and @Date2 - Every Nth year; wednesday & friday; between @Date1 and @Date2 etc... I h...more >>

I need a query to get non exisits i Exampl TABLE B (contains column name ID I Table A (contains TOW columns name ID,ORGID ID ORGI 1 2 3 4 5 Table A id contains all ORGID values (noexce...more >>

Dear All I have the following code Declare @FileName as char(200) set @FileName = 'D:\Backup ' + cast(getdate() as varchar (50)) + '.bak' BACKUP DATABASE TestDatabase TO DISK = @FileName It should create a new backup using the date time. However what actually happens is 1) Only ...more >>

Please help me with this Querry CREATE TABLE #T1 (CID uniqueidentifier,COL2 VARCHAR (2),COL3 VARCHAR(10)) INSERT INTO #T1 SELECT 'D12E530D-0D13-4D8D-9B40-85712BED44FA','A','A1' UNION SELECT 'D12E530D-0D13-4D8D-9B40-85712BED44FA','B','B1' UNION SELECT '8DA42BA4-548A-4E0A-8971-04FE311E9A...more >>

I want to check for rows in two tables before proceeding. I can't get the if exists construct to work with an "or" as follows if exists (select * from table 1 where col1 = @var1) or (select * from table 2 where col1 = @var1 begi do somethin en Help!...more >>

I am trying to call a com dll using TSQL and am having problems. I us sp_OACreate to crate an object then sp_OAMethod to call a method on the object. I get an error from sp_OAMethod saying "Unkown Name". I think the problem is that the method that I am calling is not on the public int...more >>

Hi All, I wonder if any of you guys can help me. I am trying to EXECute a stored procedure (dynamic SQL) from my VB app. using something like; sSQL = "EXEC SP_CUSTOMER_CALLBACK_RECYCLE ...." Set rsList = clsQuery.Do_Select(sSQL) The procedure does what it needs to do, but I am trying to r...more >>

Consider the following query: Select a.equipment_id, a.carriage_no, a.start_time, a.end_time From active_carriage a, panels p, tracking t Where a.equipment_id = p.equipment_id and a.start_time >= p.start_time and a.end_time <= p.end_time and p.equipment_id = t.equipment_id and a.slot ...more >>

Hello, I am currently doing a data migration between two databases. The source databases stores the date in dd/mm/yy. The desination database stores the date dd/mm/yyyy. The dates span both centuries it 1999, 2000. Can anyone tell me then why this works:- declare @Fred as datetime ...more >>

i am trying to run a query for a report , which selects records/ rows where the datetime field = last day of the previous mont ie if date today = 31/01/2004 then i need to select all records that have a date of 31/12/2003 recordid In recieveDate smalldatetim thanks...more >>