Hello I am using stored procedure with sql 2005 (with Visual studio 2005) I have two tables .. TABLE1 And TABLE2 >From TABLE1 i need to retrive the OrderID's of the 4 most top rows. so i did: SELECT TOP 4 OrderID FROM TABLE1 order by OrderID desc Now what i am trying to do is take the...more >>

We have a problem with a table giving us deadlock issues and we can't figure out why. It's a table we write to fairly often perhaps 50 times a minute. And also do a select of 200 rows at a time from 4 servers every 5 minutes or so. We are only keeping 48 hours worth of rows in the table wh...more >>

Hello to all SQL Server junkies who work with non-English characters: For people running scripts from the command line using ANSI files with special characters, it is very important to use isql and disable "Automatic ANSI to OEM conversion": - This only affects isql from the command line, an...more >>

Hello, I've been searching the web for quite some time to resolve the problem of "1/1/1900" returning in a datetime field in SQL that resulted from a blank (not NULL) value being passed to it through an ASP page. The solution is that a NULL value needs to passed to SQL from ASP. Thats fine....more >>

I have an "Issues" table for my technicians. An issue can be on "hold" or "assigned". I want to get a count for each tech with a column showing number of issues on hold and a column for number of issues assigned. It would look like this -- Tech Num_Assigned Num_On_Hold Fred 3 ...more >>

I have SQL records as follows REGION PRODUCT Amount Week IMDS A 100 Week1 IMDS A 250 Week2 IMDS A 150 Week3 IMDS B 525 Week1 IMDS B 400 Week2 IMDS C 500 Week2 IMDS C 40 Week3 IHYD A 50 Week1 IHYD A 52 Week2 IHYD A 100 Week3 IHYD D 20 Week2 ...more >>

Lately I did a mass update on all our scripts and added dbo. in front of all tables and other objects. There is a function that returns a table and the function is I think 300 lines. There are a lot of UNIONs, EXISTS, NOT EXISTS, etc... Anyhow there's a single place where if i add dbo. in f...more >>

I have the time of an event stored on each record as a datetime field.It includes the year,month,day, etc. Suppose my user wants to search the table for all events over the lunch hour, say between11am and 1pm. How do I construct the SELECT query to peek into each datetime field and return only t...more >>

I have a customer who wants to SELECT records based on a partial match in a text field. For example, in a list of telephone numbers they want to search for all records that contain the digits '777' in any part of the string. How do I formulate such a query? Many thanks. ...more >>

Hello guys, I'm not an sql expert but I need to pull out some data from SQL database. I hope you can help me here. I need to find out how many time a product was ordered. Some product have the same ProductName but different Option and for me that's a different product. Here is the database...more >>

Hi, This is my basic sql shape query: ------------------------------------------------------------ SHAPE {select * from tbl1} APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO field) ------------------------------------------------------------ With this query i get a Recor...more >>

Hello Everyone: I am loading a text file onto a table. One field is a text and the field in my table is a numeric. Here's a sample of my code....i get (type mismatch) error....can someone help me. sample data to be loaded (" ^876504 ") text sample date loaded (87654) numeric Function...more >>

Hello, I need to be able to select only the numeric data from a string that is in the form of iFuturePriceID=N'4194582' I have the following code working to remove all the non-numeric text from before the numbers, but it is still leaving the single quote after the numbers, i.e. 4194582' ...more >>

Hello, I only have remote access to the db via textual interface. Now I try to find out what an existing stored procedure is doing. I tried like 'desc sp_sale_pro' and 'show sp_sale_pro' - but that is not working. Can anybody help? Thanks!! Paul ...more >>

Hello, I only have remote access to the db via textual interface. Now I try to find out what an existing stored procedure is doing. I tried like 'desc sp_sale_pro' and 'show sp_sale_pro' - but that is not working. Can anybody help? Thanks!! Paul ...more >>

I 'm using SQL Server 2000 Enterprise Edition in Windows 2000 Sever box. Now , message "SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [D:\DATABASE\xxxxxx.MDF] in database [aaaaaaa] (7). The OS file handle is 0x00000354. The off...more >>

I am trying to write a query in access that will pull results that are in the database in a text field and convert to where I can get a average including decimals. Any Ideas? -----------------------Start SQL-------------------------------------- SELECT AVG(CAST(dbo_sur_response_answer.answe...more >>

Hi: Does anyone know if IBM (or any other IT Software company) offers store procedure classes anywhere in the country? Thanks Marc ...more >>

There a stored procedure or query that will count all the records in all my tables in a database. One of these two - exec sp_MSforeachtable N'SELECT "table" = ''?'', cnt = COUNT(*) FROM ?' select object_name(id), rows from sysindexes where indid in (1, 0) Is there a way to add columns t...more >>

I am trying to query a table of items There is a field for the name of the item, price, quantity etc. When I query I would like to return a "total price" which would be price * quantity and alias it out as "Total". So something along the lines of: SELECT vchrItem, monPric...more >>

I created a new user (Joe_Reader) and gave this user the db_datareader Role. I then went and specified what tables and queries would this user see. This works fine. I however keep on creating tables and views on the database and automatically these objects are viewable by the user. I have to m...more >>

I'm planning to establish a recovery plan for my SQLServer 2000 databases. How to change home and data directories? ...more >>

My problem is that the 'INSERT INTO' query that sends the records to the table is dynamically compiled in VBA and and the target table has a two column primary key. I have made a number of attempts at getting 'WHERE NOT EXISTS' to cure the problem but so far without success and previous posting...more >>

installation of their softwares? If there has NEVER been a backup of the Database (.mdf), Recovery Model Set to Full, and there has never been a backup of the Log (.ldf) files? Using the trial software for both, this was unclear. Thank you for your time, bd ...more >>

Hi, I have a view that looks something like this - CREATE VIEW myview AS SELECT myudf(col1) as col1, col2, col3 FROM mytable The view has an 'INSTEAD OF' trigger on it to do the correct thing on insert. When I run the bcp, it runs successfully, but the value inserted into col1 is al...more >>

Hi, Does SQL support update to multiple rows where values coming from a sub-query? e.g insert into TABLE1 select column1, column2, column3 from TABLE2 This is perfectly valid, assumes TABLE1 has only three columns, column1, column2, column3. My question: Is there any way to UPDAT...more >>

Hi How is SQL Server 2000 better than MySQL? I need to convince my boss as we are contemplating doing a website with either SQL Server 2000 or MySQL as backend. Thanks Regards ...more >>

I have created an SSIS (think that is what DTS is called now) package to run on a SQL 2005 server. It's job is to connect to a SQL 2000 server, execute a stored procedure, and insert the returned data into a table of the '05 box. In both tables (source and destination), the string columns are ...more >>

Hey all. Ive got a big problem with an sql statement Im working on. There are 2 tables with a master/detail relationship. The Header Table is the master, the Line Table is the detail. So for each Header, there are many Lines, but a Line can only reference one Header. There is a Line Total and ...more >>

Hi all, I get this message when trying to update a tabel i have which has nested hierarchies. The current hierarchies beginning from root = 1 are up to the level 5. Before going into details and sample data with all the sql queries and procedures, this limitation from Microsoft for nested leve...more >>

Hi! I'm trying to build an app. for my parents' office. They have a small office (5 PCs), and they currently work with a very old program that's based on DOS. The program doesn't work well under XP, so they still use Win 98. They had their network upgraded about a year ago. They had Microsoft...more >>

Hi, I'm having problems constructing a nested join. It's quite complex, so here's a simplfied example of the problem. Any thoughts on what I'm doig wrong - or if I've got the whole approach wrong are welcome. I've two tables :- one is a contact table contacting name, addresses etc. Three ...more >>