Hello All, We have a long running application running an SQL Server backen and VB Front End. We are looking into creating an online reporting system which queries the database. We are just not sure about the requirements to make this happnen. I assume that the customer would need a window...more >>

I have a web application coded in dotnet and ms sql server. It is a multiuser system that lets members search for other members via their profile fields. It gets complicated in that it lets users search based on distance from a zip code, as well as degree of separation. However every time I ...more >>

Hi I have 2 tables, which consist of: - 1 header table - 1 detail table The key is in both tables, but: Table 1, looks like Key fld,data, data, data Table 2, is: Key Fld, SubKey1, data Key Fld, SubKey2, data Key Fld, SubKey3, data I am looking for a quick way to flatten this structu...more >>

I have an application where my html pages are using XMLHttpRequest to call SQL templates. The templates simply call various stored procedures. I have been using template commands like this: EXEC ('dbo.MyStoredProc ' + @param1 + ', ' + @param2) This works, but I recently read that using sp_...more >>

Dear NG, I am having problems constructing Bills of Materials (BOM) using examples from Joe Celko's book: Trees and Hierarchies in SQL for Smarties. I would like to use what Celko calls the "nested set model." My application works fine if I have but one product with a BOM, but I might hav...more >>

Dear NG, I need to be able to process trees in SQL Server. I bought Joe Celko's book at the recomendation of the C# newsgroup. His book is mostly based on SQL-92. I am new to SQL (of any flavor) and I can not seem to get some very basic procedures to execute. In the following code I am ...more >>

We are using SQL 2000 Enterprise, and the database was originally set up as Case Sensitive, which sometimes causes us problems. Can this be changed after the inital creation of the database, or am I stuck? John ...more >>

I'm having trouble with a query to come up with the max continous date from a table. By continous i mean that there shouldn't be more than one month of a gap create table testdates(id int,d datetime) insert into testdates(id,d) values(1,'1/31/2004'); insert into testdates(id,d) values(5,'2...more >>

I have a 1 : M for Assets : Repairs when I do; Select a.Name, a.OwnerID, Sum(r.RepairCost) as Repairs from Assets A left join Repairs R on a.AID = r.AID group by a.Name, a.OwnerID I can get NULL's in my Repair column. Fine, that is correct, but how can I show a 0 in numeric format to ...more >>

This may be a silly question, but I am having problems with SQL Server 2000 inserting duplicate records. We are using 4 different dup checks in our C# code to prevent dups from entering our database and most of the time this works fine, but occasionally when the volume is high SQL Server will i...more >>

Ok, we've got invoices... Invnum, date_start,_date_end,amount. Most invoices are for 1 month, so the data would be 100, 1/1/05, 1/1/05, 500 101, 2/1/05, 2/1/05, 700 But we can also have an invoice that covers 1 year... 102, 1/1/05, 12/1/05, 3600 The $3600 is the total invoice for the...more >>

Hi everyone, I have a stored procedure that takes 2 values (UserID and UserGroup) and it checks a table to see if they exist if not it adds them. How can I provide a list (maybe .txt) and do a batch job? Ex. 1324 2 4545 2 4324 5 4123 6 Thank you Maz. ...more >>

Hi everyone, I use the DTS (Data Transformation Services) to grab a copy of a remote database to my local (copy table + objects). How do I make that a job? So I can schedule it to do it automatically? Thank you Maz. ...more >>

What should I do with a numeric (9,0) field that has exceed nine digits? (Server uses five bytes to hold the nine digits, and to go to ten digits means I need to expand the storage to nine bytes). Is my understanding correct, and what should I do to get around this problem? Any advice is greatly...more >>

Hi, Can someone show me what the syntax is to query one table for all IDs in that table and updating 2 other tables based on IDs found in the first table? For example, I have table1 -> table1.tid Now I want to go through all ids found in table1 and for each issue 2 updates on 2 oth...more >>

How would I lock a record or a set of records for an UPDATE query set FIELD1 to "UNAVAILABLE". I want to insure users cannot modify or read the record while I am updating the record. Once I UPDATE the record, it should not show up in clients SELECT * WHERE FIELD1="AVAILABLE" query and I am wo...more >>

Hi everyone, I'm relative new to store procedures and want to know how I can query a table for a record, get the UserID and use it in my INSERT statement? ALTER PROCEDURE AddUserToGroup ( @UserName [varchar](50), @UserGroupID [numeric] ) AS declare @UserID int @UserID = SEL...more >>

Hi everyone, Can someone please tell me how I would be able to the following? Need an assignment here? SELECT UserID FROM Users WHERE (UserName=@UserName) INSERT INTO UsersandGroups (UserID, UserGroupID) VALUES ( @UserID, @UserGroupID_1); First I want to find the UserID for my UserNam...more >>

Hi all, One of our companies is having problems with a query on a linked server. They have two servers, serverA and serverB. On serverA they have set up a linked server to serverB. Query: select * from oas_company where code = 'TEST' If I look on serverB via Profiler the query is exec...more >>

I have a bunch of tables that contain certain pieces of data that relate to a master table. I want to be able to do one query to relate all of these various pieces of data back to the master table and am not sure the best approach. To better illustrate what I'm talking about let me give a li...more >>

I'm thinking this should be simple to do (but I'm still in the learning phase with SQL) and hope someone can help. I have two tables I'll call T1 and T2. The T1 to T2 relationship is a 1 to many. If I'm saying this right 1 row from T1 can match 1 or more records within T2. I want a query ...more >>

Good afternoon all, I am currently working on an installation for a software package we are developing. The software is database aware and can use either an access database for small installations or a client\server based installation for larger installation. I am working on the SQL serve...more >>

Hi, I am building a db install program. Can somebody help me with a problem please. I need to know how can I check if a column 'aColName' exists in a table programatically. Any help will be appreciated, Dino...more >>

I've got an instance of SQL server running on my website. I have port 1433 open and I can connect to it via enterprise manager across the internet just by using the domain name in the server name box. I recently installed SQL 2K5 beta 2 on the same box and gave it a different name. I should ...more >>

The data file is a simple Unicode file with lines of text. BCP apparently doesn't guarantee this ordering, and neither does the import tool. I want to be able to load the data either sequentially or add line numbering to large Unicode file (1 million lines). I don't want to deal with another p...more >>

I have 2 questions which I can't seem to get answered anywhere. #1: Does SQL Server 2000 Developer (8.0) or SQL Server 2000 Standard (7.0) run on Windows Small Business Server 2003 STANDARD edition? I know it come with Professional edition, but will my copy even run on STANDARD edition? For e...more >>

And I mean real dummies! Can anyone explain, in 25 (small) words or less, what SQL is and if / why I should care. An icon has recently shown up on two of my computers (start bar tray) that says that SQL is not connected. I have no idea what I did to cause that. Thanks for any guidance here....more >>

Hi, I need to do the following through Enterprise Manager (EM). I need to copy the structure of all the tables along with their PK, FK etc and need to copy the data of only select set of tables (not all of them). I tried doing this in EM but lost the relationship between tables in the target ...more >>

Ok. Call me dumb, but why is this occurring. Table firstv has 303,000 records, table lla has 10,000 records. When I run this query though: SELECT IsNull(lla.voy, '') as voy, IsNull(lla.poe, '') as poe, IsNull(firstv.ocean, '') as ocean, IsNull(firstv.car, '') as car, IsNull(firstv.equipment,...more >>

Hi, I'm hoping this is something basic and I'm being obtuse. When restoring a database it must not be in use. Is there a way to close connections and prevent new ones being made? My problem was that users were constantly trying to access the system even though we'd alerted everyone that the...more >>

My development system hard disk crashed and I have lost the Operating system and the SQL executables and the system cannot be salvaged. However at the time of the crash the server was not running and the log files and the data files of the database were on another hard disk. I have managed to ...more >>

Hi: I'm using the following code to set Corp_AvgTalkTime to null if there is a division by zero (see nullif below). I might want to set it to zero, or in some cases I might want to replace it with '00:00:00'. Right now I go back over the data and set it using another update, but I would like ...more >>

Hi, Does anyone know the expiration date on Yukon Beta 3? Thanks, Cyndi ...more >>

Hello all, Despite having a good trawl I am finding it difficult to establish whether this product exists or not - I realise that sounds foolish but I'm genuinely confused. I can find dozens of articles discussing it from before 2003 and various announcements about Yukon being postponed/delay...more >>

Iam doing an installer for my .net application. The app uses an SQL Server database. During the installation process, I have to check if SQL Server 2000 is installed in the target machine. For this, I wish to check in the registry (with code) to find out if SqlServer is installed. What entry...more >>

Hi, I can't seem to solve this problem. I hope someone can help. I have a table which is owned by dbo called xxx. I also have a user define function also called xxx but owned by a different user. Everything works fine on the local database. From query analyzer if I type in select * fr...more >>

Hi Guys, I'm trying to write a stored procedure which gets a result set back and stores it to a temporary table. Once this is done, there are some extra columns for each row that will need to be added from another relating result set which is generated from a under-defined function. The p...more >>

Hi: I have a script below, when I run it on its own, it is great, but when I run it as an "Execute SQL" task as part of a DTS package, the step fails with a "division by zero" error. Shouldn't it ignore this error due to setting ANSI_WARNINGS and ARITHABORT both to OFF? I get the error in Quer...more >>

Hi, We have a SQL database that is constantly being written to, that is every other seconds a few rows are added to a couple of main tables. The database currently has one data file and one log file. I set the initial data file size to 2Gb, about that size I anticipate the database to be...more >>

Hi there: I have a table that has several different fields with time values in seconds (datatype int). Beside every such column is another varchar column that is meant to represent the same number in HHMMSS. I want to avoid a cursor, but doing a straight UPDATE to me doesn't seem possible beca...more >>

Hello, I am writing a stored procedure that should generate a list of customers that have a birthday three days before today and 14 days after. The way the sproc is currently written has some limitations I would like to change. First, it doesn't accurately capture the window near the change...more >>

Hi, I'm semi-new to crystal reports & sql server and what i want is to extract data from a sql server file. I've been able to connect to sql server directly and get the info. i need but is there a way to just open the data file (in crystal reports) without making a server connection? ...more >>