Hi all, I have an mssql server and sqlserver agent running=20 with a local administrator id 'GCBAPU427\sqlrep01' instead=20 of local system account. The id is not a domain id. It is=20 only avaiable in the server. I try executing the below command EXEC master.dbo.xp_sqlagent_proxy_account N...more >>

Hello, I have a table with a primary key on two fields. When I run sp_helpindex 'tablename', I get a record with the index with the two fields. So correct information. When I run sp_indexes_rowset 'tablename', I get only one of the two columns. So incorrect information. If I do the sam...more >>

I'm looking to do as the topic says, I currently use temporary real tables (not #temp tables) that i keep in existence for just long enough for an access adp frontend to issue an output to excel command, and then it's dropped. The parameters are usually 2 dates to form a range or a few char...more >>

Hi, In a SQL table I have a field named departure and a date in inserted in it. What is the exact syntax to get only records with a future date (in departure field) please. The SQL data type for departure is 'datetime' I have tried SELECT * FROM dbo.table WHERE Departure_date> ' & dat...more >>

Greetings, I am trying to do send a mail message when a certain field gets updated in my table (trying to track when this is happening and by who) . Tho when i look at the inserted table i get there are no records there. Table and trigger below also the query I run from query analyzer to t...more >>

Hello I'm ambiguous between two types of query. :) I want to show you two differen written query. Both of them do same job and both of them I can use without problem. But I want to choose the right one of them (I mean the performance issues, etc.). Than...more >>

Hi, When considering transactions should you: 1) - Put a begin and end tran inside every SP that alters data? 2) - Use the ADO Transaction features 3) - Mix 1 and 2 4) - Do something different I appreciate it depends on the exact situation, but, as a general guide for a small system (30...more >>

Hi, Sorry, for the lack of info in the title. Just don't know how to simplify it. I have previously been asked to post my questions in a certain way. Unfortunately for a novice, this is not always possible. Please excuse my way of asking this, I think, at least for laymen, this is the simples...more >>

I have a table Revision with 3 fields JobID, RevisionDate and RevisionNumber. 1. I'd like to get all records for a particular JobID. I can do it by this SP: CREATE PROCEDURE CoordinatorRevisions @CurrentJob int AS SELECT TOP 100 PERCENT RevisionDate, RevisionNumber FROM Rev...more >>

hi folks, I have accidentally UPDATEed huge amount of data that is in a table. I need to rollback it....is there any way to do it using log file without executing another UPDATE statement. Thanks in advance. ...more >>

I've got a problem I need to solve where I have an item that can have one or more prices, but the item can be bought from one or more distributors and the price we pay is dependent on the distributor. How can I model this sort of relationship where the price for an item is dependent on the distr...more >>

Hi all, I appreciate if anyone could tell me what the standard TCP port number to make an ODBC connection to SQL Database from web server?? Thanks.. ...more >>

How can i performe a cascade delete in SQL2k? I have 2 child table for a master table and i want, when delete from the master, automatically delete all record related in the child. Now i delete first the record in the child DELETE FROM child1 WHERE IdDoc = @IdDoc Delete from c...more >>

Hi Freinds, I have a <order> table columns: <orderno><salesman><weekend><sp1><sp2><orderamount> also have 2 other tables <tblsp1> and <tblsp2> which keep the values for <sp1> and <sp2> for <order> table. Like: ORDER <orderno><salesman><weekend><sp1><sp2><orderamount> --------------------...more >>

Within a procedure i create two connection RConnection for readonly operation WConnection for write operation on a DB. Associated with the WConnection i create a Transaction with isolationlevel ReadUncommitted; All three object are stored in Session variables. I use a class to perfor...more >>

Hi, I have a view with the following columns: member_rating_id ,rating_table_id ,member_id ,screen_name ,rating_value ,number_won ,number_drawn ,number_lost ,points_for ,points_against ,rating_date ,iso_country_code I need to build a resultset of the top 20 ratings (bas...more >>

I have read a statement that recommends to use the new 'table' data type instead of temporary tables, as it can improve performance. What are your experiences with this? The documentation says it cannot be used for two cases, INSERT INTO's and SELECT INTO's. Any good resources on the web? T...more >>

Hi I use SQL Server 2000 and I try to trace and keep its result activities(by SQL Profiler and by T-SQL), now there's some question with me: how can I automatically start a trace everyday? I made a script on my trace information by SQl profiler but there was no topic related to the name of ta...more >>

hi guys, I have this stored procedure: CREATE PROCEDURE newClient @StartDate datetime, @EndDate datetime, @cManager nvarchar(20) AS Select 'Total ' as [NEW CLIENT], count (ClientID) as total FROM [Client Intake] where Manager=@cManager AND Date>=@StartDate AND Date<=@EndDate I want it to...more >>

I have 2 tables: FILES and ACTIONS I would like all the Files to get listed with all their Actions when the actions are on a certain date. So I have something like: select * from files left outer join actions on AC_FIID = FI_ID where ((AC_Stamp >= '2003-06-30') OR (AC_Stamp IS NULL)) A...more >>

Hi. I came across following stored procedure. CREATE PROCEDURE [dbo].[pr_tblUser_Delete] @iId int, @iErrorCode int OUTPUT AS SET NOCOUNT ON DELETE FROM [dbo].[tblUser] WHERE [Id] = @iId -- Get the Error Code for the statement just executed. SELECT @iErrorCode=@@ERROR My...more >>

Hi there, because of some inabilities of SQL Server 2000, I have to use a trigger to enforce referential integrity. If an INSERT statement is not allowed to be executed (because of a virtual constraint -- the referenced foreign key is not available), I want to raise an error. First I was u...more >>

I am trying to get weekly and monthly rolling totals in a query that I am doing. Is there something similar to compute and compute by from 2000 in 7.0...more >>

i am using sql 2k. as far as i know, in previous versions doing a select... into could cause blocks in sysobjects and maybe on syscomments, sysindexes for the duration of the whole select statement and then the insert statement. besides you had an extra overhead because sql server has to dete...more >>

Hi, I had my Windows 2000 crashed and after that I reinstalled windows, sql server etc. after taking backup of all mdf ( and no ldfs by mistake). Now when I try to restore it, it says, -------- Could not open new database 'kothari1'. CREATE DATABASE is aborted. Device activation error. The...more >>

I can connect fine via query analyzer, but it takes several minutes to expand the databases on one particular server. Other servers, database expand fine. Any hints? pcwik@triwest.com...more >>

Hello all, I have a table with an id column and an image column. I need to write a stored proc that copies data from 1 row (given the id) into a new row. Any ideas where I could find some sample code? Image column could be more than 1 MB so writetext wouldnt work. Basically I would need to use...more >>

I don't know the best way to go about selecting ranges of data. I want to be able to have the user select data from the table in 20% ranges of the data (0-20, 20-40, etc) and don't know how, or the best way, to accomplish this. The table is dropped every week and loaded with fresh data,...more >>

I am looking forward suggestions and solutions. I have a table and would like to add a "computed column" for reporting performance reason. The value of the column is calculated based on the columns on other tables. For example: I have tables A, B, C. A inclues columns a_id, b_id, a_name ...more >>

I have coded the following in a stored procedure. --------------------------------------------------------------START CREATE PROCEDURE dbo.sp_ChangeOEReqShipDate @ordno CHAR(5), @newdate INT AS BEGIN TRAN SELECT * FROM OEORDHDR_SQL WHERE ord_no = '000' + @ordno IF @@ROWCOUNT = 0 ...more >>

Is it possible to use an ATL CTable to insert rows into a SQL Server table with an identity column? I can get there with a CCommand, but I would like to use CTable. This works: CCommand<CNoAccessor, CNoRowset> cmd; hr = cmd.Open(session, "SET IDENTITY_INSERT theTable ON"); cm...more >>

Hi, we have a problem with case sensitive column names in a database application running on a Turkish client. The database is an MSDE on the client machine, with server collation SQL_Latin1_General_CP1_CI_AS. The client program is in VB6, using ADO version 2.7. The problem occurs when ac...more >>

Hello, I've successfully implemented a hierarchy strategy in my database using the method Itzik Ben-Gan writes about... but I'm having a problem searching over multiple criteria. I need to be able to search for all those records on say level 1 that have a certain property and all those records...more >>

hi guys, I have a query that will return 4 record with 1 column. I want to add another "title" column for the four record. The query is design so that it will only return 4 record. any help is really appreciated. thanks, Jun ...more >>

I have a Tagdataminutely table with columns TagKey, datetime, Value, Quality and Rev. TagKey is not a primary key in this table, in fact this table has no primary key at all. When an Update statement is issued on Value or Quality or Rev fields, the new value specified in update statement r...more >>

My question is: which one of these will run quicker? If not (I'm not expecting a definitive answer), what would determine what works best in what situation? What would the speed be dependent on? Where Date between 6/1/03 and 6/15/03 Where Date <= 6/1/03 and date >= 6/15/03...more >>

I am trying to do the backup of my bank using IN and I receive the following message. micorsoft SQL-DMO (ODBC SQLSTATE: 42000) Write on 'c:\tst\bakteste' failed, status = 112. See the sql server error log for more details Backup database is terminating abnormaly. ...more >>

In ss. 7.0 Is there a function available that will return the differences between two strings? e.g. The difference between 'This is a test' and 'This is Test' would return 'a'. TIA, Jordan...more >>

I have a query created to pull results based on a date range the user keys in. Right now, I am using the BETWEEN function, however, it seems to not return the complete result set. Problem appears to be with records that are equal to the dates keyed in. Example: Select * from test where da...more >>

Hello, friends: If I have a SP with TSQL code as EXEC sp1 EXEC sp2 EXEC sp3 .... where sp1, sp2, sp3 are several stored procedures, am I guaranteed they will be executed sequentially? Or is there some possibility that their execution overlap? Is there some way to control this circumst...more >>

Dear Sir or Madam, Refer to the title above, I was encountered the following error message when I was trying to create a huge size of table using SQL Server 2000. (Server: Msg 1701, Level 16, State 2, Line 2 Creation of table 'xtcwt_InvtSAPOview' failed because the row size would be 13...more >>

We often use code in stored procedures for premature exit, such as SET @returnerror = @@ERROR IF ( @returnerror <> 0 ) BEGIN RAISERROR( 'Whoopsie in sub-module 3 - error %d', 16, 1, @returnerror) RETURN @returnerror END Another program detects t...more >>

Dear all, Two short question for SQL2K: 1) Can table variables be passed into / out of stored procedure as parameters, just in the same way of other generic data type? ( I can't get it done, but SQL2K books online says "All data types, ..., can be used as a parameter for a stored procedure....more >>