I am try to convert a list of intervals in a column to two columns with intervals from and too. eg: Hole_ID :Depth hh001 :0 hh001 :3 hh001 :7 hh002 :0 hh002 :6 hh002 :7 to Hole_id :Depth_From :Depth_to hh001 :0 :3 hh001 :3 :7 hh002 :0 :6 hh002 :6 :7 Creating...more >>

Hello all, I need some help. create table contract_submission (id char(10) , contract_line int, condition1 int, condition2 int, condition3 int, condition4 int) id contract_line con1 con2 con3 con4 guid1 1 1 2 3 4 guid1 2 1 2 0 0 guid1 3 1 2 3 0 guid1 4 1 2 7 9...more >>

Dear friends, I have a table Item(Item_No, Loc). Data: 111, sa 111, la 222, sa 333, sa 444, sa Now I run the following sql script: SELECT COUNT(*) FROM ITEM I LEFT JOIN ITEM I2 ON (I2.ITEM_NO <= I.ITEM_NO) GROUP BY I.ITEM_NO, I.LOC I get the following: 2, 2, 3, 4, 5 How do ...more >>

Crystal Report/Sql: Got: select A,B,C,D,count(*) cnt from AA,AB,AC,AD where C.Res <> 0 group by A,B,c,D order By B How to modify the above to return only the top 3 count(*) where output looks like?. B D1 6 D2 5 D3 4 -- Sent by msg_2222 from yahoo part from com Thi...more >>

I need to include a computed column in the result set of a query. Because I am using a linked-table to get the computed value, I must create a dynamic OPENQUERY statement. I succeeded in doing that, but now I am unable set a variable equal to that value. Here is the query: declare @StartDate v...more >>

Hi, I have a solution that I've been developing that allows me to apply paging to just about any allowed query. It appears to be just as efficient as other methods I have discussed, however I have a need to know the record count, which tends to slow things down. I use it as follows: DECLAR...more >>

Hi all, Does anyone know how to extract the 'ROWDATA' column in DBCC log resultset? Highly appreciate all your reply. Dinesh ...more >>

If I try to run: CREATE FUNCTION fn_MyFunc (@AreaPar nvarchar(30)) RETURNS table AS RETURN ( SELECT Revenue AS Revenue_for_ + @AreaPar FROM MyTab WHERE Area = @AreaPar ) GO I get a syntax error, because AS Revenue_for_ + @AreaP...more >>

Hello; I'm trying to make and execute DTS Packages in C++, and I find it to be pretty hard... There are a lot of samples in VB or Delphi, but nothing in C++... Can someone post a C++ sample, please ? Thanks a lot. ...more >>

hi, I want to implement a row-locking or row-write-protect mechnism, that prevents certain rows of a table (e.g. that ones with lock (bit)=1) to be modified or deleted. I think the best way is to this by triggers, but how ? this one doesn´t work create trigger xy on table test for upda...more >>

Hi, I have a query select Assign_id,Ass_date from Assignment where Assign_id not in (select Assign_id from Assignment_info) This query takes long time because both tables have millions of rows. have index on assign_id. is there any other way to remove not in? Please reply. ...more >>

How do you build a date in SQL Server sp? I need to perform a Month-to-date calculation so given a date, I need to build a date which is the first day of that month: select datepart(year,'8/12/2003') + datepart(month,'8/12/2003') + '01' I have tried to do this several ways, but sql always A...more >>

Hi all, I have the following theoretical table. [ID] [Sector] [ReturnID] [Date] [Price] [Rank within sector] 1 001 55 02/03 1.12 2 001 55 03/03 4.234 3 001 55 04/03 ...more >>

Hi, I need to do rounding on numbers but while doing so I shouldn't get more/less of the total value. I am running on SQL Server 2000. Can someone provide me some help/hints on how to achieve this. Thanks Suresh Please find the script attached below. USE PUBS GO DROP TABLE SOP_Ro...more >>

QA grid internally is using CHAR(1) to separate column values but does not escape it if it comes from the query results. columns shifting is demonstarted with this query: SELECT 'z' + CHAR(1) + 'z' + CHAR(1) + 'z' AS A , 'B' AS B , 'C' AS C , 'D' AS D...more >>

I want to select all items that where recorded today for instance Todays Date = Now() in this case 8/18/2003 [Key] [int] [Date] [datetime] Key Date 1 2003-08-12 18:12:11.230 2 2003-08-12 13:43:15.530 3 2003-08-13 10:45:01.230 4 2003-08-18 13:26:26.520 ...more >>

CREATE PROCEDURE GetAvailSales ( @theSearchVal nvarchar (100), @theSearchBy nvarchar (20), @theCompanyList nvarchar (50), @theRecordsCount int, @theSalesDateFrom SmallDateTime, @theSalesDateTo SmallDateTime, @SelSort nvarchar(50), @SelSortDir ...more >>

Hi! I've noticed there's a new short for something around here :P Everyone posts a DDT which I believe is the scripts for the relevant tables.. But what the **** does DDT stand for? :P Lars-Erik ...more >>

I have a table with Title, Effective and Expiry fields. I want to be able to group this table based on the title And dates Eg. Title is - Test effective 01/01/70 expiry 12/31/71 Test effective 01/01/72 expiry 9/1/73 Test ...more >>

I need to use the truncate sentence in my stored procedure, but i not be dbo over the database, this is possible ? the table is to big and delete sentence is to slowly. Thanks and Regards...more >>

I want to pad a number with leading zeroes to append it with other characters. I am using sequence numbers. so if the sequence number is 17, it should display as C00017 instead of C17. How can I do so? Thank you, Alice...more >>

is this possible with only sql (not writing any code)? i have a table (seqid, spid, ts) with 10k rows and 50 spid's.... 33 56 8/8/2003 11:00:01 34 57 8/8/2003 11:00:02 35 59 8/8/2003 11:00:02 36 56 8/8/2003 11:00:02 37 62 8/8/2003 11:00:03 38 57 8/8/2003 11:00:05 39 56...more >>

I have a Visual C++ program using ADO to load data into a SQL Server 7.0 database. I'm getting an error after loading about 5,000-10,000 records. The error code is 80004005. The message is "Unspecified error" and the description is "[DBNETLIB][ConnectionRead (recv()).] General network err...more >>

Hello, I get the following warning when I run an sp in query analyzer which returns some null values. Warning: Null value is eliminated by an aggregate or other SET operation. The sp is supposed to list a count of 6 items whether or not the items exist in the count. If some items are ...more >>

To All, Having a problem: t-sql: select appt_date,unit,appt_time, sum(case when schedule.logno is null then 0 else 1 end) count from schedule where appt_date between '2/15/03' and '8/15/03' group by appt_date,unit,appt_time order by unit,appt_date,appt_time results: appt_date un...more >>

Hi Everybody, The Help provides information about the valid range of data for specific data type. What if I want to get the maximum and minimum value that can be assigned to a variable of specific data type at run time, is there a way to do that? ...more >>

Hello! I have a little problem, maybe someone can help me out? I want to use the following table: CREATE TABLE [dbo].[Study] ( [study_id] [int] NOT NULL , .... .... [study_date_start_registration] [datetime] NULL , [study_date_end_registration] [datetime] NULL ) ON [PRIMARY] I use...more >>

I have a MS Access 2000 front end application accessing a MS SQL 7 database via ODBC. The application calls many stored procedures in the database. I have a new report and new stored procedure as the source data for the report. Some users accessing the report via a Windows 2000 Terminal S...more >>

Can someone tell me if this schema design is good or what I need to change in your opinion. I will respond back to any replies, so please check. Thanks everyone. CREATE TABLE [dbo].[Storages] ( [StorageID] [int] IDENTITY (100, 1) NOT NULL PRIMARY KEY, [EmpID] [int] NOT NULL , [Stora...more >>

Hi all, I have a strange problem that I have never seen before. I imported a database called PPGCOPY from ServerA to ServerB. I can use this database in my application. I can see this database in Enterprise manager but I can not see it in Query Analyzer's Database dropdown. I made sure ...more >>

I am doing a search for record between certain dates... It will not bring back the recond if the salesdate has a time in it like...8/14/2003 8:03:00 PM Unless I change the search date to 8/15/2003 SELECT tempSales.tempSalesID, Contacts.FIRST_NAME + ' ' + Contacts.LAST_NAME AS theName, comp...more >>

Hi, I have to transfer data from Oracle 8.1 to SQL Server 2000. I tried to get the Oracle data thru Linked Server. It is working but very slow when the data is huge and when I use the Where clause it is very very slow. I am using the following syntax: insert into SSS select * from LS..s...more >>

How can i know how much columns have specific table ? Thanks ...more >>

This has to do with my previous question with a twist: If we are looking at the first row for someone, I want a NULL value for the Amount_Previous column. I have the following table: tbl1 ID (int-identity) Name (varchar (30)) Amount (money) 1 Dave 40 2 ...more >>

Hi All, Why SQL Server restricts, to have a Full Text Index on a table which has a composite primary key..? what will be the issues if it has so..? Regards, Selva Balaji B. ...more >>

What is your opinion on having a DateCreated and Username field in your table structure?...more >>

I need to convert a string to currency. Is it best to use money or decimal (18,0)? The field in the table is currently decimal (18, 0) but I get an error trying to directly cast nvarchar(20) to decimal (18, 0). Suggestions?...more >>

I've written a function to pull the datetime out of a memo field (migrating from Notes to SQL Server) <below>. The time element isn't needed, just the date. It's working, but the date is being changed. If you run the function (select dbo.FN_GETDATETIME('') --No need to pass anything in, hardco...more >>

I was wondering if anyone could help me with an SQL Trigger problem i'm experiencing? The problem is this: I have 3 tables Profile, Asgnmnt, CallLog When CallLog.CallStatus changes to 'closed' i would like to look up the Asgnmnt table for the total time spent on that callid and sum the h...more >>

This is the current query that I am running: SELECT DISTINCT VE.DocumentId, DL.Truck_Num, DL.Trailer_Num, DL.Trailer2_Num, DL.Trailer3_Num, UDL.Image_Name, UDL.Company_Driver, UDL.Company_Co_Driver FROM ValidationError VE (NOLOCK) INNER JOIN DL (NOLOCK) ON DL.DL_ID = VE.DocumentId INNER JOIN...more >>

StudentID Gender Age 1 Male 12 2 Male 10 3 Femaile 16 4 Male 09 5 Male 14 6 Female 12 7 Female 13 8 Male 14 SQL: how to return 3 highest age with its correspon...more >>

Hi, I have some simple Stored Procedures. These just do a select of one table or an insert into one table. I have forced myself to put Error Logic into even these simple Sprocs. Below is the format of my Sprocs. Can someone let me know if this seems OK. ############################ ...more >>

Hi, we are building a system which stores huge number of transactions for several hundred clients. We store aprox. 50.000 records every day for each customer .. all together aprox. 25.000.000 records a day. We need to keep 30 day history, so the table size will be aprox. 750 million records. ...more >>

Hi, Sorry for the crappy Subject, it's kinda hard for me to "catch" my problem in a snappy "Subject". I have a database containing several versions and languages of pdfs for several products. I want to make a "select" which delivers a table of products with only _1_ pdf per product. It...more >>

I have this structure table (sorry I omitted column names for brevity) ======================= int 4 uniqueidentifier 16 int 4 binary 15 binary 125 binary 250 binary 125 1 binary 125 1 varchar 10 1 numeric 9 1 bit 1 1 bit 1 1 int 4 1 nvarchar 70 1 nvarchar 60 1 nvarchar 10 1 nv...more >>