I would like to retrieve distinct customer IDs where the customer has at least one record that meets the condition of FieldX <> "Y". I have this: "SELECT distinct CustID FROM Orders order by CustID" but this just gets the list of all customer IDs. (There is no need for an actual customer table)....more >>

I am trying to use a stored procedure to look for all the DTS packages stored on the system of sql server7 and sql server 2000, Is there a system table where the names are stored? Thanks, Tom ...more >>

Whice Date function would I use If I wanted to take a dateTime field in my table and produce this output. 06/14/04 to 061404 ---> and I need to kept both postions of MMDD Thanks. ...more >>

I hope this is an easy one... I have a CSV input file like: id,name,address,phone 1,bob,12 anywhere road, 502-555-1212 2,tom,13 anywhere place, 502-999-9999 How do I setup my format file to not use the first line at all? I've tried /r and /n with no luck. Oh, this is on MS Sql 2000. A...more >>

SQL 80 SP3 MS Server 2003 Database has AutoUpdate Statistics On, AutoCreateStatistics Off Why would a query return a different number results? Running the query one minute gets 18 records, waiting a few minutes and running it again gets only 1 record. Waiting again, gets 16, then again,...more >>

We have an application where a user session is run using point-in-time dates for users that are logged in, serving as sort of a CURRENT_TIMESTAMP replacement value for that session. Transactions and queries are performed based on this point-in-time. We have a table that has user-specific param...more >>

Hi, I've a table with numerous of fields. Whenever a user edits some field, I want to store it into history table with the information about the edited field. For e.g. if 'Date of Birth' was edited, then i want to store old DOB and New DOB into history table. Can anyone tell me if there is a g...more >>

Hi, I am new to reporting service. But it seem to have to do a lot of work on this .... Right now i have a problem I need a button for users to click to directly print a report generated without having to export the report.I don't want the user to go this extra step. The users that will be usin...more >>

I'm having trouble using the following: DECLARE @topNum INT SELECT @topNum = 5 SELECT TOP @topNum colOne, colTwo FROM #tempTable I get: Incorrect syntax near '@topNum' Any idea what's wrong with this? ...more >>

Hello, Here is a example of my table and its setup and how it works. I have a record in a table, when a person changes one or more fields for that record what happens is that a new records is created with the same info as the first records and the changes made. the second record or shall i ...more >>

is there a published doc or anyone who cares to comment about the relative processing speeds of the various version of SQLServer 2000? we are testing and noticed that SQL developer edition can do simple inserts around 10X faster than Standard edition! is the standard edition crippled? are we for...more >>

I have the following table: game_dates: id (PK) name (CHAR) date_order (NUMBER) I'm am trying to determine how to select 2 rows starting with a given ID, ordered by date_order. Here's my futile attempt: SELECT id,name,date_order FROM game_dates WHERE id IN ( ...more >>

Does anyone know how to change the owner of a user-defined data type? Would like to changed it to reference 'dbo' as the owner. I cannot drop the user-defined data type and re-create it because they are currently being referenced in the database tables. Looked into a couple of pre-defin...more >>

INSERT INTO INVENTORYTRANS SELECT EdpNo,TransactionNo,TransactionDate,TransactionTime, UserID,FromRef,'80BULK',Qty,TransactionCost,CartonCapacity, ' ',Status,AddlProdCst,OldAddlCost,FIFONo FROM GTSUploadData The INVENTORYTRANS table has a sixteenth column SLOTID which is an ide...more >>

Hi All, I am having problem running bulk insert statements at a client's site. We use tempdb to do bulk insertion. Thus the code involves creating a table on tempdb and then 'bulk insert'. The connecting login is bulk insert administrator, dbo of both the production database and the tem...more >>

I am getting this error : [OLE/DB provider returned message: Cannot start more transactions on this session.] I am trying to update some data on SQL 2000 server from SQL 7.0 server through link server. Update query is written in Exec statement. What could be the work around for this proble...more >>

Hello, Can i change the active (current)database in a cursor? Here is my TSQL, and i need to to cycle through all of my DBs. i error out at the "Use " statement below. running SQL7SP4 .... any ideas? THANKS!!! set nocount on DECLARE db_info CURSOR FOR select distinct name fro...more >>

Hi all, I have two tables, on holds info on sales personnel, the other holds info on sales. Sales people work in teams of two or three, where each sales person may change teams at any time. Sales are broken down into three types of sales: "product line", "accessory" and "misc" Each rec...more >>

Hi all, What's the best way to combine data from SQL-Server2000 and DB2 Could anybody please be so kind and give me a hint about that? I already tried some approaches: - linked servers (failed due to connection problems - I could not get the thing up running) - DTS served import -table...more >>

Hi. Does anyone perhaps know of a way to re-write this query to perhaps make it a bit faster. It used to be a single select statement but then i had to break it up to cater for specific exclusions. I have added indexes and so on. Any ideas? Thanks N SELECT qrt.QuarterlyClientID, qrt.Client...more >>

Here is the situation. I want to prevent the developers to CREATE TABLE. But if I do that the developer won't be able to create temporary tables, which should be granted. How do I solve this problem? ...more >>

We are getting an error when we try to use NT authentication for domain users. When we try to login through the query analyzer using NT authentication we are getting the error "[Microsoft][ODBC SQL Driver][Shared memory] General network error, check your documentation." We added the domain us...more >>

Hello I have a fuction that sends email that looks like this: ALTER FUNCTION dbo.SendEmail(@Email varchar(256),@SenderEmail varchar(256), @Subject varchar(400), @Mailbody text, @Priority tinyint) RETURNS tinyint AS BEGIN DECLARE @hr int, @Object int, @Message int, @Return...more >>

Hi all. Help for a newbie would be greatly appreciated. I'm trying to update existing data that came from an Access database, and when I'm finished, none of the columns will be allowed to have null values. But, the existing data does contain nulls. So, within the same table, I need to swap ...more >>

Hello, I have to support a non-Microsoft/non-rdbms application that contains some sets of data with 180+ columns (I have no say-so in the design of this app). This includes reading the data into sql Server tables (daily - DTS not an option because too many faults with the data - and the ...more >>

I created a class that uses the same _CommandPtr and _RecordsetPtr. Since I will be make recurring function calls, I didn't want delete the objects and recreate them using CreateInstance(). I call my first stored procedure, it works fine but when I call my second stored procedure, it fails and re...more >>

Hi! I have a stored procedure which returns some hundreds of records. Now I need to select several records from this SP by some criteria,in other words, I need to make subselection. In InterBase it was so easy, like this: SELECT * FROM MyStoredProcedure(<someParams>) WHERE myField=... So ...more >>

I need to retrieve records from several linked tables using several criterias such as SalesmanID, InvoceDate, ShipDate and some others. In one case SalesmanID will not be passed as a parameter. If so then WHERE clause will not include SalesmanID and SP will return records for all Salesmen. In ca...more >>

Hi, we are upsizing from Access to SQL Server 2000 I have set up constraints for a table in SQL Server 2000 Enterprise Manager, The consraints are only simple ie: ([Surname] is not null) we are using Access as the front end and i have noticed you are able to specify the validation text if the ...more >>

Hi all, i have a job which basically executes a SP that does 2 things a). backup a db b) call SP on linked server with parameters such as path to .bak file etc which restores the db. This works fine for databases lets say under 100MB. For any DB over that size I get an "OLE DB TIMEOUT" error. I h...more >>

Hey everyone! I need the syntax for MS SQL Server 2000 for a constraint. Here's what I'm trying to do, in plain english: If [Terminal]='Wilmington, DE' then [Wilmington_Data] is not null Basically, if the Terminal field is equal to Wilmington, DE, then I want the user to be required to pu...more >>

I want to set a variable to a field's contents given a field name. e.g I have a loop which gets each column name that has been updated into a variable. I now want to store the contents of that field in another variable and concatenate them together into an audit string. I've tried the following: ...more >>

Group, I am hoping someone can help me. I have the following stored Procedure that I am trying to sort by the Year and then by the month, but it will not work. Can anyone tell me how to do this correctly? Thanks. Tx, Virgil Carroll ALTER PROCEDURE fosterparent.sp_FPNReports_elearningCou...more >>

Hi all! This is making me nuts. Using SQL 2000, I am trying to add a UNIQUE constraint to an existing table (already populated with data). I have "scrubbed" the data so that the only repeated values are NULL. According to all the documentation I can find, UNIQUE constraints are allowed where...more >>

Hi, I think this a real newbie question. My client wants certain users to have the ability to insert data into fields that were left null, but not be able to change fields that were entered. Is this possible? Thanks, Denis...more >>

Hi, We are using OSQL utility to extract data from database and making one txt file. It has limitation of 1000 chars per line. But we want to print more than 1000 chars per line. Is there any other way to print more than 1000 chars per line. Thanks, San...more >>

I am trying to create Insert/Update/Delete triggers on my tables but I want a generic trigger that will work for all tables. Basically I want to write a string back to an audit table which will contain the updated field name + the old and new field value. I have managed to get the updated field na...more >>

Hi! When I execute a stored procedure using ms sql server job engine, the job always fails. Stored procedure reads data from a text file and writes data into sql server database table. The job has only one step, 'exec @spname', when I evoke the execution of the SP from query analyzer (...more >>

I want to examine the contents of log files and data files. How these files are structured , what is their file format and other detail. Any idea about API's or some resource through which we can explore the transaction log. Many tools like Log Explorer by Lumigent and SQL Apex etc are capabl...more >>