Hello Can anybody tell me how to make "single join". I have two tables. Relation is one to many. Firs table has n records, secons has m records. n < m ! I need single inner join. After joining I wish to have only n records. For example: Table1: taActors (100 records) Table2: ta...more >>

/* Any help creating a view that would do the following... If OptionSetting = "Y" then return a 1 column table with column name Trick and show all values associated with Trick (i.e., A,B,C) If OptionSetting = "N" then return a 1 column table with column name Trick with no results Thank...more >>

Hi All, I have a table "COLLECTIONS" with the folowing DATA. ID COLLECTION_DT AMOUNT -------------------------------------------- 1 01/15/2002 100 2 01/16/2002 75 3 02/20/2002 50 4 02/21/2002 ...more >>

All, I am looking for a book, white paper, etc. that will help me with my confusion about this instruction. I have read the books on-line and have purchased "The Guru's Guide to Transact-SQL" by Ken Henderson, but still often get confused when attempting to utilize this very powerful statement An...more >>

hello, i have sql server 2000 on my local machine (with enterprise manager etc). i want to host a website (on a dedicated server) which talks to sql server, but i dont want to buy another license for the server machine that i am going to host on (too much money!). my question is: is ther...more >>

I need some fields to contain huge amounts of text. How long can a text field be in an SQL Server 2K table? Thanks, Trint .Net programmer trintsmith@hotmail.com *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

what's the difference between VT_NULL and VT_EMPTY...more >>

Hello folks, is there a simple way to include row numbers in views or functions or does it require a Stored procedure RowNum Data 1 data1 2 data2 3 data3 ..... thank you Anton ...more >>

Hi, I would like to have a stored procedure that increments an integer field value in a database table (containing a single record) once every 5 minutes for as long as the server runs. The table is defined as: MyField int And the "pseudo-code" for the stored procedure should be: :Loo...more >>

The sp_spaceused @tablename proc gives us name, rows,reserved,data,index_size,unused info for each table. I need to add the database name to that list and was trying to use the sp_MSforeachDB but am having trouble with the syntax. Here's my code create proc getalltables as set ...more >>

I need to find out what orders have specific amount of products. Eg. I need to know which orders have following products in it: Product 1 / 10pcs Product 424 / 3pcs Product 23 / 2pcs So that then the query result be like: order 2 order 421 order 635 ....and those orders contains only ...more >>

CREATE PROCEDURE update_phodept ( @Dept char(8), @DDept char(8) ) As declare @count int Select @count = 1 Set NoCOUNt On Begin Select '@DDept' set rowcount 100 update phodept set fo_dept = @Ddept where fo_dept = @Dept While @count <> 0 Begin begin tran update phone set fo_d...more >>

Here goes: (good luck understanding all this) I have data in SQL Server that needs to be inserted into DB2. I have installed the IBM DB2 Client Configuration Assistant on the SQL Server and created a DSN. I can use the IBM Command Center to execute commands (both reads and writes) successfully...more >>

Hi, How can I do this on one select statement. There is the business rule and the result want show below. Any suggestions would greatly appreciate. Thank you very much in advance. drop table #Address go CREATE TABLE #Address ( Agent_id INT NULL, Person_id ...more >>

I want to display search results that works like ebay's with: | photo | title | description(hyperlink) | price ... ... ... Does anyone know if there is an easy way to do this with Visual Studio .Net 2003 and SQL Server 2000? And is a datagrid the best way? Thanks, Trint .Net programm...more >>

Hi, I need a constraint that allows only unique values, unless the value is null. I though the UNIQUE constraint already did this in reading the BOL. Any Idea? Thx in advance Gary ...more >>

Hello, all. I have to convert several columns of data in a table so that the terminal character in each field (which currently represents a digit that is positive or negative, depending on the code) becomes a human readable number in decimal notation. much like this: 00000000A 0000000...more >>

I am looking for two queries that will give me the following result tables: table1:Customer, date-of-first-order table2:list of customers who orderd this month or this year who have not ordered the product they ordered before particular product before. (the first time this customer ordered t...more >>

hi, i have a table in database which has a structure like cols ---> A B C D WK QTY rows 1 a1 b1 c1 d1 wk1 10 2 a2 b2 c2 d2 wk2 10 ... now i want the data to appear as like this cols ---> A B C D WK1 rows 1 a1 b1 c1 d1 10 after i execute a 'select' statement on the table where my c...more >>

Hi there! I have made the following query : select o.name from sysobjects o where o.parent_obj in ( select o2.id from sysobjects o2 where o2.name = 'Commande') and o.xtype = 'PK' I'd like to add the actual column name in the select list. How can I do it? I not been a...more >>

In UPDATING a table, I need to use the most immediate previous row's calculated YTD % performance field and multiply it by the current row's daily %change field to calculate a new YTD % performance for each successive current row's YTD field. Is there a way to do this without using a loop to ...more >>

Hi All, I am having some trouble using a datetime field in an update query. Field Information: fnum char(6) fqtdate datetime(8) fstatus char(20) The query below works correctly: SELECT fnum,CONVERT(varchar(10),fqtdate,101) as fqtdate,fstatus FROM TBLQT WHERE fqtdate < '11/19/2002' AN...more >>

Hi, I have a table that looks like this: ColA ColB Darrin a Darrin a Darrin a Marc b Marc b I would like to assign a number to each record and have the numbers reset for each grouping in a select statement to populate another table. What I'd like to return and popu...more >>

Hi I need to change the length of a field in many tables. However, it is part of the primary key of the main table (clustered index) and is referenced as a foreign key in many other tables. In the related tables I also have indexes defined on this column. It looks like sql server force...more >>

Hi, I'll use Northwind as example of something similiar I need to do in my own project If I wanted to find all ORDERS that had TWO specific products. For example I want all customers who ordered BOTH product #31 and #49. --Returns 51 Row SELECT FROM [Order Details WHERE ProductId = 31 --...more >>

Hi All, I was trying to find the dollar amount from a record. DDLs: CREATE TABLE [dbo].[Status] ( [SARNo] [varchar] (25) NOT NULL , [DealerNo] [varchar] (10) NULL , [Status] [varchar] (30) NULL , [Status_Date] [smalldatetime] NULL , [Reason] [varchare] (250) NULL ) ...more >>

I have the following query that groups parts into hourly buckets which is no big deal. SELECT PlantArea, Linename, COUNT(*) AS [Total Parts] FROM tblPartToPartTimes PTP WHERE RecordTime >= '2/13/2004 12:00 AM' AND RecordTime <= '2/13/2004 11:59 PM' GROUP BY PlantAr...more >>

Do views speed up performance in any way? -- -- Regards, Troy...more >>

hi all I am running an sql script. Inside that script, I need to call another script, and so I am using xp_cmdshell. Everything is running PERFECT. But my question is, whenever I execute xp_cmdshell from that primary script, I always recieve a column called 'output' in my query analyzer, and the...more >>

Value for column with default NEWID *can* be specified. The BOL page: Adding Rows with INSERT says: INSERT statements do not specify values for the following types of columns because Microsoft® SQL ServerT generates the values for columns of these types: *Columns with an IDENTITY proper...more >>

Hallo, do you think there is a way to represent distances between localities stored in a table? I mean: id-----locality 1 milan 2 rome 3 venice the first thing I should think to do is to do an other table so: idloc1----idloc2---distance 1 2 ...more >>

Hi: Does sql 7 maintains its own serial (like system auto number) that keep track of records inserted by user. I am not talking about any column of table. Table may have its auto number field. But I am concern with chronological number within system. I look for this because then I can write sql...more >>

I recently added a trigger which fires when a table is updated (AFTER UPDATE) . This trigger checks to see if a value has been changed in a particular field, and if the changed value meets the right conditions, writes an entry to another table The problem is, there is another process that updates ...more >>

Hi, I have a view written that grabs the customer, item, price, and TranDate from Sales Order tables. Of course, this view can have multiple items per customer, multiple prices per item, and multiple TranDates per item (but not multiple trandates per unit price). What I'm trying to do is get t...more >>

Hi, When system procedure returns one resultset (like "sp_pkeys table") there is an easy way to put the result into table: INSERT INTO #MY_TMP_TABLE EXEC SP_KEYS ANY_TABLE But what to do if a procedure returns several resultsets (like sp_help table)? How to put for example a second resu...more >>

Hello there. My problem is that I call a SP from the query analyzer and it takes 14 minutes to finnish, but when I call the same SP from a job it takes about 3 hours. I understand that can be some differences between the two methods, but there's a gigantic diference between them. Is that normal T...more >>

I have an Order table that is used heavily. About 90% of queries is looking for 1 particular sales order and is joining with other tables via the primary key to get relevant data (select * from Order inner join OtherTable on Order.OrderKey = OtherTable.OrderKey where Order.OrderKey = @OrderKey). ...more >>

I have a stored procedure that has 'in-line' SQL (or whatever this is called technically), but I need to have a 'dynamic' WHERE clause. While I could build the entire statement as a string and use execsql, I was hoping that there was a more elegant solution. Could I build a custom function (o...more >>

Hello All I have a large amount of data in my SQL Server tables (About 100000 records)- I want to export these to Comma Separated Value Text files and each file should have only 65000 records. Is there a way to do this in SQL? Is there a way to do this in DOS? Please advise Thanks ...more >>

Hi I have an Sql Server 2000 Developer where iam trying to get data from an Oracle server. The only way i have found to get data from the Oracle database is to link the server and to query data from oracle and sql server in the same query is to set a view of each of the oracle tables in the sql...more >>

I have 20 servers in 20 different locations. they all have the same databases (diffeent data and names) I create SPs that they all need to run locally. Is there an easier way then connecting to each to compile the SPs in one script as opposed to connecting to each individually then compilin...more >>

Ovako, Table with integer as PK, clustered index in QA execution plan shows sort operation when I use ORDER BY on PK When I use the same query but without ORDER BY, there's no sort in execution plan Why is it using sort, isn't clustered index already sorted??!!!! tia Zarko ...more >>

here's the ddl: Create Table #OrderByTest (UserID int, Color varchar(15), ItemSize varchar(12), Item int) Insert into #OrderByTest SELECT 1,'blue','big', 2 union SELECT 1,'blue','medium', 2 union SELECT 1,'red','small', 2 union SELECT 1,'yellow','big', 2 union SELECT 1,'pink','big...more >>

How can I set default database to open QA with? ...more >>

I want to read a SQL Server table into Excel and then make changes to it and then update the Excel range back to SQL Server. Also, I want to be able to add new rows or delete rows in a range and reflect back the same into SQL Server. In simple words I want to do the same thing what Access Link t...more >>

I am trying to create a new SQL Server user. I will be using tht login to access all my SQL data. After I created the new user I am testing it running Query Analyzer and using SQL Server Authentication to connect. But I am getting an error that login is failed for my new user. Reason: Not assot...more >>

Are there any tools which can verify DB normalization? Thanks ...more >>

When I have an INSTEAD OF INSERT Trigger on a Table with an identity column, the inserted table returns 0 for the identity column values. SQL BOL says something about needing to have SET IDENTITY_INSERT Accstatuss ON for the table. I can't retrieve the values from the identity column! --...more >>

Hi, is there a way to cast or convert Hex number to int ? I didn't find any about it. Thanks, Ori...more >>

Hi there, I got a problem with a quite simple statement: (SQL Server 2000) Table is (int, char(20),varchar(2000)) When I do a "Select * from MyTable" in the Enterprise Manager, I get the full string back(approx 400 bytes). If I do the same in the Query analyzer, or another application(using ms...more >>

Hi, I experience some weird behavior with the INFORMATION_SCHEMA views on sqlserver 2000. I want to retrieve information about all unique constraints in a given catalog. I use the following query to do that: SELECT CCU.* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN IN...more >>

Hi all, I am trying to improve performance wherever I can in my web application. I am doing so by converting standard sql queries ( I think this is called dynamic sql) throughout my asp.net code into stored procedures. The question I have is as follows... in one area of my applic...more >>

hi guys, m having a problem with the stored procedure copied below. when i try to execute this stored procedure from query analyzer then i get the following error: Error: ---------------------------- Server: Msg 203, Level 16, State 2, Procedure EJ_GetEvents, Line 24 The name 'select sEve...more >>