Recent content by spaliwal

Hi Gurus, ADODB 2.8 SQL server 2000 We are calling sql server 2000 stored procedure from ADODB 2.8 command object, while using print command in sp its setting the command object's state as completed but stored procedure is still executing, we are using "AdStateExecuting" constant for...

Hi Gurus, I am using following code segment in stored procedure - DECLARE @hdoc int DECLARE @doc nvarchar(4000) select @doc = detailXML from Tab1 where col1 = val1 exec sp_xml_preparedocument @hdoc OUTPUT,@doc select * from OPENXML(@hdoc,'Root/Customer',1) with (CustomerID...

Hi Gurus, While executing a large query (400 k) thru application i am getting a error - java.sql.SQLException: The server encountered a stack overflow during compile time. Severity 18, State 1, Procedure 'OCI_IL_BA_18NT null', Line 1 I dont want to resize the query but increase the stack...

Hi Gurus, I am using Profiler for the deadlock and deadlock chain detection, I have SP3 installed on sql server 2000, To pinpoint a deadlock i am leisint events - 1.Lock : deadlock 2. Lock : deadlock chain 3. SQL : StmtStarting But after many tries i conclude that 1,2 is mutually...

Hi Gurus, In my production database with 40 users load, Many of the resources are left locked even with noload in night time. Type of Lock is 'DB' and mode is 'S'.All the locks are initiated from report queries. It unnecessary increase load on server, caused sometimes exclusive locks on the...

Hi Gurus, I have Three tables - TableA 6 lac recs TableB 2 Lac recs TableC 1 Lac recs Table A is is in frequent is in Insert / Update/ Delete , Pl. anybody who worked in large data volume in sql server 2000 can suggest me will it create any problem while accessing Join of TableA ...

Hi, For next no. generation i am using sp in sql server. sometimes it returning same no again , and voilating PK constraints while insertion in application.Below is the code, i am using default autocommit behaviour of sqlserver2000. Here Location.LastDeliveryTicketNo is treated as next no...

Dear RT, Thanks a lot, Yeah i am very much agree with u. But instead of keeping time difference from mine time zone , I would like to to keep time zone differece from GMT. Since i can get GMT time directly by using GETUTCDATE() and from table mst_timezones , i will get the desired Time zone...

Hi, I am using sql server 2000. I need date/time with time zone like "MM/DD/YYYY HH:MI:SS ZZZ" where ZZZ may be PST , IST etc, but while seeing datetime or smalldatetime datatype i could not get any style code to get time zone as a part of it.Pl. help me to get it by anyway. Thanks...

Hi, I am using a table of single row for next number, Table Name - SequenceOID column name - NextOID varchar(32) this nextOID number is used for PK of system wide tables. Thru a stored procedure(GetNextOID) i am taking the currect value of that single row column NextOID and table...

Hi, I have to retrive text/ntext field in a stored procedure , it contains XML patterns.I have to parse that xml in a tabular form. But problem is it is not possible to retrive text/ntext field in a local variable.Can anybody can suggest how to retrive text/ntext datatype field in a local...

Another way of using a table variable in a user defined function as given below (sql server 2000) CREATE FUNCTION MyFuncTab () RETURNS @ProdInfo TABLE (Column1 <datatype>, Column2 <datatype>, . . . ColumnN <datatype>) AS BEGIN <Function body> INSERT @ProdInfo SELECT * FROM Tab . . . END...

Hi Experts, I have to replace sql server default error messages by my own defined messages .In sysmessage table i have created some user defined messages having nos > 50000 thru. &quot;sp_addmessage&quot; command. Also i used RAISERROR to raise these user defined messages . But I am unable to...

Hi, I have to replace sql server default error/ message by my defined message in sysmessage table which is above msg no. 50000 .I have used sp_addmessage command to add message in sysmessage table, also RAISERROR to raise this user defined message . But I am unable to replace sql server default...

Hi tlbroadbent, Thanks again, But dont you feel it will increase the chances of database fregmentation, see for a paricular table we know the max size will go say 50MB in next six month , as sql server can not provide contigious space allocation for it. It will be a cause of row chaning and row...