Hi, I need to design a table that stores products prices and can be subclassed. Each product has it's own unique ID and a default price. Those prices can be changed for specific customers, however for other customers without a specific price, the default price is to be user. It gets more comp...more >>

I'm using a uniqueidentifier column as the "identity" primary key column for a table. How can I retrieve the value of that column after an insert (using NEWID())? I tried something like: "INSERT INTO TABLE........... SELECT @@Identity" The @@Identity does not work in this case. It returns NUL...more >>

I am trying to write a SP which will accomplish the following: Return a row for every entry in the "Regionals" table where the field "CG" is 1 and for those rows whose ID ("RegionalID") appears in the RegRegistration table for a selected value of UnitID, certain fields will have values (instea...more >>

Hi, In my upsize program I have to first delete record(s) in each SQL table and then insert data. If table has 'Enforced relationship for INSERTs and UPDATEs' checked, then I cannot delete records before deleting in reference table(s). How can I extract table(s) list (by SQL queries) of su...more >>

Hello : I have a table called invmstr, and my WEB-SYSTEM written in VISUAL.NET and ASP.NET and SQLSERVER, is giving me a lot of deadlocks because all my users are trying to update, write or delete this table. What should I set in the database or in my stored procedures to avoid this deadl...more >>

Hi, I have the foll query SELECT number,sum(po_quantity_sold) from po_data_file join {call PO..P_GetSale} on number = number and po_file_date >= 2004-01-01 group by po_quantity_sold The stored proc called is USE OP GO IF EXISTS(SELECT name FROM sysobjects WHERE na...more >>

Hi all, I am trying to assing the value of a field (stored in a variable) to another variable. I have tried various approaches with no success :( here is the code I am working with... --SELECT@CurrFieldValue = CustomerID FROM[Inserted] SET @MySQL = 'SELECT @CurrFieldValue = ' + @CurrFiel...more >>

I have this set of data number dateEntered 1 2004-06-15 1 2004-06-18 2 2004-06-15 3 2004-04-15 3 2004-05-15 I want a query that allow me to get the MAX (dateentered) of each set. So I would get this: number dateEntered 1 2004-06-18 2 ...more >>

I am attempting to run a CASE statement. Unfortunatley, I am having problems. The following statement works great: SELECT MESSAGE_CONTENT = CASE WHEN (TRANS_ID = 1089486738 AND MESSAGE_CONTENT='ID_FLAG="YES" INTRODUCTION_TYPE="PRODUCTION"') THEN Success ELSE 'Z' END FROM T_TABLE ...more >>

Hi, I am trying to load data from table to another. My source table contains smalldatetime values stored with datatype char(19) and my destination table has the datatype smalldatetime. Ideally when i insert data from char to datetime the convertion shall take place implicitly however it does...more >>

Hello, i have this SQL code in my dataSet (I am working in ASP.Net / VB): SELECT Author, COUNT(Author) AS totalDocuments FROM documents GROUP BY Author ORDER BY COUNT(Author) DESC What i want to do is this: > When there are 2 Authors which published the same number of documents, i wa...more >>

Hi All, I just have a simple question and I hope I posted this message in the right forum; if not, please forgive me. I have a table from someone that contains employee names, the table has only one column which holds lastname, firstname, and middle initial. TABLE EMLOYEE: Name Miller, ...more >>

Hi! I have written a stored proc that supports a MS access front-end app which allows to pass 4 or a combo of any four parameters. Please see the code: CREATE proc up_FTr @contract varchar(20) = '%' , @CCAN varchar(40) = '%' , @customer varchar(40) = '%' , @legacy varchar(40) ...more >>

Hi All, I have a table with 3 columns. Product, Location and Value. The data looks like this: NULL NULL 100 Atlanta NULL 50 Atlanta Cookie1 30 Atlanta Cookie2 20 Dallas NULL 120 Dallas Cookie1 80 Dallas ...more >>

Hi friends I am trying to create a backup job on sql server 2000 server. I want backup files to be on different machine.I created a mapped drive thr windows explorer. when i open sql server (destination path on sql server back wizard screen) i dont see the mapped drive.it only displays current ma...more >>

datetime Datatype displays both time and date. I want to display only date and not time. Is there any such datatype? Panks ...more >>

SELECT a.* FROM OPENROWSET('SQLOLEDB','seattle1';'manager';'MyPass', 'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a how can I replace the provider name with a parameter ? Why doesnt that work : DECLARE @prov varchar(50) select @prov = 'SQLOLEDB' SELECT a.* FROM O...more >>

Sounds like your application is trying to commit the transaction even though your trigger did a ROLLBACK. See if the application is missing the error handling code to detect that the transaction has been aborted. Bob ...more >>

I need an easy way to round a value up or down to the nearest x dollars. E.g. $11.50 - I want to round up to the nearest 5 dollars to return $15. Is is easily possible to achieve this in SQL, maybe using a function? Thanks, Chris ...more >>

The SQL statement below works for other but not for me. Anyone know why? RESTORE LOG testdb FROM DISK=N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\test-Backup.BAK' WITH RECOVERY,STOPAT='07/12/2004' The error I get is: "The preceding restore operation did not specify WITH NORECOVERY ...more >>

Hi everyone, is it true that everyone who has access to a sqlserver will have full right to tempdb? if this is true, then following steps are not neccessary. please give your commands. thanks exec tempdb..sp_grantdbaccess 'joe' exec tempdb..sp_addrolemember 'db_owner','joe' ...more >>

Hi All, I have a table with 3 fields in it. drinkName varchar(25),Ingredient(varchar(50),Per(float) I need to create either a check constraint or a trigger to limit the sum(Per) to a max of 100% I have been banging my head against the wall trying to solve this. Can anyone shed a lit...more >>

I know this topic has been brought up a whole bunch, but after searching the newsgroups, I haven't found a fix. I have a small table with 5 fields, ApptName, ApptDoctor, ApptDate, ApptTime, ApptNotes. I need to retrieve the data from the database that is between 2 dates. I have tried this ...more >>

I want to update a column in a table with a value. The value would be determined by the amount in another column in that table. Basically, I have a po_text1 column and a total column. I want the po_text1 column to be updated with 'Expense' if the total column is <= 500 or be updated with 'Capital' i...more >>

What's the word on when it will be released? All data I find says first half of 2004 (which just ended). I have a MSDN Universal subscription....more >>

I have a following structure table A userid ReferedBy B A C B Table B Userid compID A Alpha B self C self now the scenario is : the user A is from the company "Alpha" he introduces user B, who registers in the system his company bcomes "self", now B inturn refers u...more >>

For a database called "test" with two tables "table1" and "table2" what is the SQL command to EXPORT all of the records in each table to a csv file. Can it be done where the first record is the field names? ...more >>

My OS just crashed on my test server and I have a bunch of procedures in the DB's. I can piggy back the drive to grab any data I need from the hard drive. I need to restore the DB's that were on the test servers drive. If I get the .mdb and .ldf files from the disk can I attach them to anothe...more >>

This should be an easy one, but I am drawing a blank... I have CreditAccounts with a Credit limit. Say Account#1 has $4000 credit limit. Account#2 had a $10000 credit limit. CREATE TABLE Accounts(AccountID int, CreditLimit int) Then I have a charge table: Account#1, spend $100 on July-2 ...more >>

I want to search for any field with the character % in it. Of course this is the wildcard, so how can I find fields with the actual charater in it? select name from product where name like ?? ...more >>

I am trying to use a variable with the TOP function, but I noticed it doesn't work. Can anyone tell me how to dynamically change the number that the TOP function returns. I am trying to use for a paging app we have on the web. Thanks. SELECT TOP @pagesize * FROM ProviderSearch WHERE Pr...more >>

I need to do something like the following. I need to delete records in one table using a query from another table. This looks like it would work but it returns more than one record which is not allowed. Is there a way to use a join and specify which table to delete?? How is this done? ...more >>

WHat is the proper SQL Command for osql.exe to show all the databases and all the tables on the local ms sql server?...more >>

I have a column that has a substring with values that are supposed to be numeric, but sometimes there's garbage in the field. If I use "IsNumeric" to test the field, it returns as numeric even when there's letters. I first thought there were hex, but that doesn't seem to be the reason. See this: ...more >>

Hi, I have a tblTable1 containing approximately 1.5 million rows. Looks like this ================== create table tblTable ( [Col1] [numeric](10, 0) NOT NULL , [Col2] [numeric](10, 0) NOT NULL , [Col3] [varchar] (150) NOT NULL ) ================== I have a clustered unique index on...more >>

Hi, I want to integrate a subquery returning a boolean value into a query, but I seem to have a synatx problem: Simplified it is select x from (if (exists (select 1)) 1 as x) as y (x) I am always confused by the lack of a 'then' so I think thats probably where my mistake lies, but I ca...more >>

In our system we have triggers of several tables. Last day when a worked whith system, I detected that the trigger was not found. I executed sp_help trigger and the objects not found. It is possible to monitoring who user has dropped it ?? I was probeing to create a trigger for deleted in s...more >>

How I can set a type of locks for a temporary table ?? I need to set a page or table lock. TIA...more >>

Hey there, I am trying to create an index on a computed column and getting nowhere. For the life of me I can't get ISDATE to be deterministic. To simplify the problem if I define the column as: [some_date] AS ISDATE(CONVERT(datetime, [action_date], 112)) I get an error: Cannot create...more >>

Hello, I saved a DTS packaged down to a VB6 module. If I compile this module in VB6 it runs fine. But I would like to incorporate the code into a VB.net app. I am having a lot of problems with this. I have turned Option Strict Off (for now) and even with that I still get 3 types of er...more >>

I'm rewriting VB6 application working with DAO Access 2000 into SQL Server 2000 version. Access version used to use BeginTrans, CommitTrans and Rollback method of DAO.Workspace object to handle transactions. If I replace those calls with BeginTrans, CommitTrans and RollbackTrans methods of ADO....more >>

Does the work done by DBCC SHRINKDATABASE include what DBCC CLEANTABLE dose? TIA ...more >>

Hi, I have a simple SELECT statement, SELECT * FROM table1 which returns 10 records, is it possible have back in the dataset also the record number as a field. I mean instead of A B C D .... .... have back 1 A 2 B 3 C 4 D 5 ... 6 ... Thanks FILIPPO ...more >>

I have the following function that is supposed to save a file to a SQL = server. It works fine when the file is under about 5mb, but bigger than = that and after I hit submit (which eventually fires off this function) = it get a 400 page error. private void btnUpload_Click(object sender, Syst...more >>

I have two tables. One which details the amount a well was dug for that day, and another which shows the well's depth. Here is the schema CREATE TABLE DigLog (WellID int, DigTime datetime, DigDepthMeters int) CREATE TABLE WellDepthHistory(WellID int, DepthDate datetime, DepthMeters int) E...more >>

Hi everyone, I want to clear a table down and reset the IDENTITY field. Without dropping and recreating the table is there and easy way? Thanks Paul...more >>

Hi Everyone, I tried to bcp in the data file to a table in sqlserver 2000. The data file has got few blanks in certain columns. My requirement is all those blanks has to be converted null while bcp in the data. 1. I have tried giving the option -k - Didn't work 2. I have tried creating a ...more >>

Hello, I'm currently searching the ability to create a stored procedure with a table parameter. I thought it was possible to create such procedures as of SQL 2000. I'm having trouble with finding the right syntax. Has someone done this before and give me the syntax please? Kind regards...more >>

SUMMARY I have a transaction table requiring a major batch conversion. For each row in 'MeterEvent', I need to look up its recorded Amp value in a static table of nonintersecting ranges (one set of ranges per Meter). There are 1-3000 ranges >>>per Meter<<<, and about 100K meters. The naive qu...more >>

Hi Would appreciate ideas on how to optimise a query which needs to compare 2 text fields in a table (obviously these cols are not indexed). As SQL doesn't allow TEXT column comparison, am currently doing a compare on the first 800 chars of to eachother, like so: if not exists (select .....more >>

i have a table containing an money field for every day over a three day period. Currently the table runs from 01/06 to 31/08, for each of our clients ie Client 01/06 02/06 03/06 ...... 01/07 02/07 etc 111111 0.00 125.00 0.00 99.00 0.00 ...more >>

hi all i have a table with one field (number-primary key) ,contains data like below. number 1 3 7 8 9 10 now. i need to query the above table to get the following result(in ranges) 1-1 3-3 7-10 how z it possible.any help on this ...more >>

I'm trying to do inserts on a table and am exceeding the maximum allowable size of 8060 for a row. My problem is that 2 of the columns need to hold the xml from a dataset (one dataset per column) and each dataset has a length exceeding 4000. What are my options to get around this problem? B...more >>

Hello, I'm used since several years to create tables, stored procs and inner joins... So, I can tell that I know SQL syntax a little bit, still I would need help on this one. The client gave me a CSV file of subject: PARENT ID Name_E Name_F ------ ----- ------ ------ 1024 1 Groceries ....more >>

Hello @all ive a daily generated MS-Access Database with round about 250.000 Records. With them i must update a sql-Server DB-Table with three fields. Actualy i connect to the ms-access DB with an oledb.datareader (.NET), read thru the whole Access db, test for every Record existing in the SQ...more >>