Hi All: Being a bit of a nube to MSSQL I could use a little advice. This is the situation. A client's HDD became full so I backed up, deleted then recreated a blank database (they said they didn't need the stored data). They have now deceided that they want the data again. My plan is to ...more >>

I have column data that looks like this: id | ColumnA ---+----------- 1 | 12,35,123 2 | 1,23 3 | 233,34,35 4 | 34 And I want to be able to make a WHERE clause where I can match up a value to one of the values between the commas. I.e. each of the numbers between the commas are seper...more >>

We are developing with SQL 7 Developer Edition and planning to install MSDE with the VB application. We received the following question from the IT dept. of our client: "Does your version of SQLServer database work well across a frame relay wide area network?" Should there be any proble...more >>

Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can create a parameter query in a stored procedure, but how do I use the result set of a parameter query...more >>

Hello, does anyone know what could be wrong? There's a lot of similar entries in the SQL Server current log: 2004-01-21 17:54:00.01 spid57 Starting up database 'DB_NAME' 2004-01-21 17:54:06.45 spid57 Starting up database 'DB_NAME' 2004-01-21 17:54:59.13 spid56 Starting up database 'DB_NAME...more >>

Hi, can someone tell how to write a Trigger; I am familiar with Sybase Sql Anywhere trigger syntax. Actually I have three tables MEMBER, CONTRACT and PAYMENT I need to update the MEMBER.BALANCE once the PAYMENT.AMOUNT is INSERTED where PAYEMENT.CONTRAC_ID = CONTRACT.CONTRAC_ID and CONTRAT.ME...more >>

In oracle you rarely need to rebuild indexes no matter whether you do massive inserts or deletes. Does the same hold true in sql server? ...more >>

Our disk drive died, and we were able to backup most of the files (all the reports, the shuttled, pending, etc) But when we open the administrator, we get a blank. none of our catalogues are listed in there. I don't want to recreate all the catalogues and reports- does anyone know how we...more >>

I have scoured the user groups for an answer to this UDF-MSDE question and see a lot of conflicting info. I am using MSDE Version 7.00.623 to develop an MS Access2K application that is being used with a SQL Server 2000 backend. I need to create some User Defined Functions. Can it be done using ...more >>

Hi all, Often when I post a query problem the replies ask that a create table script is provided. Is there an easy way to do this on SQL 2000? What if I don't want to include a whole table only certain fields? Any pointers would be gratefully received. M *** Sent via Developers...more >>

We use a number of similar databases and frequently create a new database using a backup restore of another similar database. We try to keep changes between databases in _Additional tables - like Account Additional, Sale_Additional so most tables stay the same. The latest restored database (I'...more >>

Are there any basic rules of thumb for how much disk free space is needed to even attempt a reorg on a SQL Server 2000 database? I have a database which is about 35 GB, and the drive it is on has about 19 GB free. Another drive on the same server has about 25 GB free. Do I need 35 GB of free ...more >>

Is SQLServer Yukon available for download? I assume the developer edition is free (like oracle, for example)....more >>

Hi all Just coming to grips with triggers and t-sql. I am trying to build a trigger to calculate a sorting code in a table so far I have gotten :- ================================================================ CREATE TRIGGER [MakeSortCode] ON dbo.tblPeoplesNames FOR UPDATE AS --...more >>

OK, Here is my challenge. If I have a query that produces the following Item Sold_On A 01-10-2004 8:03 A 01-11-2004 10:05 A 01-12-2004 1:37 A 01-14-2004 7:16 B 01-10-2004 9:37 B 01-12-2004 11:42 B 01-13-2004 9:37 But I need it to produce this instead Item Sold_On ...more >>

How can I transfer the schema of a database from one SQL Server app to another? I've used MySQL in the past and all I had to do is create a ..sql file with the DDL statements for the database, then import that file in the other db. Can I do the same kind of thing with SQL Server 2000? If so, ...more >>

We are trying to load text tab delimited files into SQL server using informatica 5.1 version. The text files are FTPed to a local server and then files are moved to applicable folder before loading. The problem is when we open the files after FTP the characters are already converted to somethi...more >>

Hi, I have a table with the following columns: ID INTEGEDR, Name VARCHAR(32), Surname VARCHAR(32), GroupID INTEGER, SubGroupOneID INTEGER, SubGroupTwoID INTEGER How can I create a rule/default/check which update SubGroupOneID & SubGroupTwoID columns when ...more >>

How efficient is ti to use join views in a database? I'm developing an e-commerce system and using join views to join the product, product category and product review tables and wondering if this would have any adverse effect on performance. Thanks in advance ...more >>

Oracle has a 'wait interface' for monitoring bottlenecks, etc... does SQL Server have utilities like this? ...more >>

Using prepared statements like Oracle does? This way in a high transaction system you do not have to recompile queries every time? ...more >>

Hi everyone, I am new in the MS SQL server world in cluster. I am in the process of implementing a cluster of SQL server 2000. Some of the IT people in my company asked me if an ADO connection will see the virtual server name as a normal SQL server? I believe so but my lack of experience in...more >>

Reset the Identity Increment Hello: I have a table with a bigint type column (field) that has an identity seed of 1 and an identity increment of 1. The column is the primary key for the table. After I backup and clean out the database (delete all of the data in the DB) I need to have th...more >>

First of all I don't normally use SQL, so please excuse my ignorance The following query Selects data based on another table. SELECT A.DSTRCT_CODE, A.WORK_ORDER, A.WO_TASK_NO, A.WO_TASK_DESC, A.TASK_STATUS, A.WORK_GROUP, A.CREW,A.WORK_CENTRE, A.ASSIGN_PERSON, A.JOB_DESC_CODE, A.SAFETY_INSTR,...more >>

This seems so simple yet I can't figure it out. I have a table that has two important columns for this query. Column A has varchar Column B is datetime All I want to do is select any record that is between 5:00 am and 10:00 am regardless of what date it falls under. In my brain I see ...more >>

We are running an ASP application. That is fairly simple. It selects from one table (15.000 records), and updates into another table. But when we execute the ASP script, it reports the following error, after completing between 70%-80%. This varies each time it runs "Microsoft OLE DB Pr...more >>

Hi all, I'm getting the above error in Delphi 4 when attempting to connect a TDatabase component to an MS Access table. I am following the below instructions: Database1.DatabaseName:='tryDB'; Database1.DriverName:=MSACCESS; Database1.LoginPromt:=false; Database1.Params.Clear; Database1....more >>

I'm thinking of writing a sp to loop through the broken users and fix them, before I re-invent the wheel, has anyone done this?...more >>

I'm trying to run this query and I think I know where the problem is: ------------ in select (SUM(C.Score * C.Weight) / SUM(C.Weight * C.MaxScore)*100) AS SC ...... HAVING ((SUM(C.Score * C.Weight) / SUM(C.Weight * C.MaxScore) * 100) > 30) AND ((SUM(C.Score * C.Weight) / SUM(C.Weight * C.Max...more >>

Help, I have a vendor file with the vendors name. I need to add a field that has alternate captions eg. : In my vendor file I have records for AT&T with three different names/spellings for AT&T. AT&T Lucent Technologies A T & T I want to add a new field that would have AT&T for the abov...more >>

We have Sqlsvr 6.5 under NT4. We want to upgrade the database to Sqlsvr 2000 under windows 2K. Is there any way we can do this by copying the database to the win2K computer?? At any rate would someone explain how to go about updating this. I know nothing about sqlsvr anyversion but do need t...more >>

Hi, This is the first time I use SOAP. Dont really know the exact picture yet. Hope you can shed some lights on me. May I know where should I start troubleshooting if I get the followin error message when running SOAP on Windows 2003, SQL Server 2000 ? Error Code: 0 Error Source= Micro...more >>

Hy Never use/practice SQL a lot, (vb... more, have free msde 2000) . 2 questions A)is it simple to write a T-SQL query for having 2) at result starting from 1) . B)how to test dynamically sql with parmaeter ( using vb ADO) 1) before query columA columB d e <-same e ...more >>

Suppose that I have a table that contains a lot of records that are identical except for an id field and a date-time-stamp field. For example Id Unit Price DTS 1 A 1.00 Date 1 2 A 1.00 Date 2 3 A 1.00 Date 3 4 B 1.25 Date 4 5 B 1.50 Date 5 6 ...more >>

Can SQL server handle High Availability or does it have to go down occasionally for a cold backup? Does it support incremental backups? ...more >>

Hi, In SQL Books Online in the section on @@Error it gives the following example: -- Execute the INSERT statement. INSERT INTO authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values (@au_id,@au_lname,@au_fname,@phone,@address, @city,@state,@zip,@contra...more >>

Hi All, Here's a challenge. If there is a 'one word' answer to this, i.e. the name of a built in SQL Server function I can use, that would be great! However... I have a standard 1:m relationship, e.g. tblHeader and tblDetail. I want to create a view of records from 'tblHeader' and with...more >>

I have a very complex view which we've been working with for some time. As we have progressed with this view, we have reached the stage where the data is generated into a table which is then used in our reporting application. This has improved reporting performance considerably. There is a st...more >>

I have a table tblCustomers in a one-to-many relationship with table tblProducts. What I want to do is to create a stored procudure that returns a list of each customer in tblCustomers but also creates a field showing a string (separated by commas)of each matching record in tblProducts. So th...more >>

Hi guys, I've been playing with this query for about a week now, and I still can't find ways to speed it up. It runs for about 5 minutes. The only table (among the 41) with significant amount of data is dbo.S_EVT_ACT, which contains about 900,000 records. The query is automatically generated...more >>

Hi all, i was wondering if for the use of logshipping two Enterprise Edition versions of SQL Server are necessay or logshipping can also work between a Enterprise Edition and a Standard Edition. Does anyone know an url for me where i can read more about this. I've found quite a lot of arti...more >>

Hi all, Given: f1 f2 -- -- 1 a 2 b 3 b 4 b 5 c 6 c 7 d How to get the highest count for field f2, output as: f2 f2count -- ------- b 3...more >>

I have a client running SQL server 2000 on Windows 2003. Yesterday data just started disappearing. It is being accessed by asp via his website and when the data is being inserted it seems to all of a sudden drop all data in the table. Earlier in the day the table disappeared all together. An...more >>

Im designing a database where a user enters the date and the number of hours and minutes he worked for the day..now i can do this Workdate small date Hours integer Minutes integer But then would I have to have the front end know that when adding up the hours and minutes for the week tha...more >>

First of all I don't normally use SQL, so please excuse my ignorance. 1st Query We have a table with a column with data that looks like this: AMTL03256636 What I need to do is change the AMTL portion of the column to another set of charachters i.e TEST Currently i'm extracting the data, ...more >>

I'm simply trying to divide each close1 column by 20, then insert that value into the twentydayMA field. The query keeps on running without finishing, even if I'm only using a single page of data. What could be making this hang? To execute, I'm using exec tad778. I thought about using another cu...more >>

Hi, Does anyone know of any documentation/web page that details SQL SERVER 7 backup format? Thanks. Jon. ...more >>

I want to find if a data string is somewhere in a table but i dont know the table of the database or the field. Who can i search anywhere? Thanks Brainjk...more >>

I'm getting: Server: Msg 107, Level 16, State 3, Line 1 The column prefix 'tMembersClone' does not match with a table name or alias name used in the query. Help appreciated. Thanks. INSERT INTO ResultSet (CustomerNumber, CustomerName, AddressLine1, AddressLine2, AddressLine3,City, State, Zipco...more >>

I'm going to write an application for a friend's business using VS .NET and SQL Server 2000. I'm confused though as to how I can write everything on my laptop, then copy to their server. I have an MSDN subscription and did not realize that I cannot install the Enterprise Edition of SQL Server o...more >>

I need to create a table that would be the result set of a comparison between table a and table b? Table a and b first 2 fields will always be the same (CustomerName and CustomerNumber). But if the Address1 field changes in table a, I would like to throw that whole row into my comparison table. ...more >>

Dear Sir, Given the table structure: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Module_Tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Module_Tbl] GO CREATE TABLE [dbo].[Module_Tbl] ( [ModuleID] [varchar] (20) COLLATE SQL_Latin1_General_...more >>

Has anyone here heard or come across an article or write up about Grid Computing in SQL Server 2000? Bharathi http://www.vkinfotek.com...more >>

Hello: I have a stored procedure for generating our invoices in Crystal Reports. I have added a new field to the SP, but when I try to add the field to my Crystal Report invoice, the field isn't available in the list. However, if I create a new, blank report using the same stored procedure as...more >>

I have an Insert, Update and delete trigger on a table that works fine. It records the changes into another table called tblTracking. tblTracking records the changes so they can be uploaded to another server off site. The problem is that it fires each time my DTS package fires (every 15 minute...more >>

Howdy People, If there is a FAQ for this, could someone point me to it, otherwise, here is my problem. I'm running an extremely simple (or so I though) vbscript which uses ADO to update an SQL Server table from a text file. The script reads the file line by line and for each line it perform...more >>

I ended up writing the folling code to get a query to work: create table #PI (prot_inv_id int, inv_type int, protocol_ID int) insert into #PI (prot_inv_id, inv_type, protocol_id) (select Prot_inv_ID, inv_type, protocol_ID from Protocol_investigator where inv_type in (1,2)) SELECT distinct P...more >>

Hi, I have an sql database that has the primary key set to three fields, but has not been set as unique(I didn't create the table). I have 1 record that has 2 duplicates and I am unable to delete the duplicate entries. If I try to delete any of the three records(they are identical) I get the m...more >>

Hello. I have an information question. In my work I often have to compare databases between releases in order to upgrade them to needed version of software we developing. And I am looking for software that could do that. It doesn't have to be free, but have to be good. I need to be able to comp...more >>

I have a db with three tables - books, sections, and a joining table. The normal way of getting a many to many relationship (i.e. one book may belong to many sections, and one section may contain many books) I want to extract the data with a single row for each book so that I only retrieve the...more >>

Guys, what I need is a tool which gives details on the choice of an execution plan by the SQL Server. For example, the cost for a hash join might be 200 and 100 for a nested loop, and therefore a nested loop is used. Same thing for the access paths for each table/view involved. In Oracle, we...more >>

Hi I've created a stored procedure (see below) which accesses the Active Directory and SQL server to get "real names" back. When I run the stored procedure in Query Analyzer it returns the expected results, however when I try to create a Web Assistant job based upon the procedure I get the SQ...more >>

Hi, I have a View that groups and sums up totals. This View is then nested within in another View and used (it needs to be done like this). What i need to do is to be able to vary the records in the nested query by specifying dates. These would somehow need to be passed to the nested query....more >>

create table guidtest(a int primary key clustered, b uniqueidentifier, c varchar(200)) insert into guidtest values (1, newid(), 'ashakhfoagfoahfafdsa') insert into guidtest values (45, newid(), 'EWRYEYYTURJ') insert into guidtest values (231, newid(), 'WYEW') insert into guidtest values (7357...more >>

It seems like im missing something, what i want to do is select the last row from a table that relates to a specific person. My table and select statement follow, the select statement works fine, works great as a matter of fact, but i feel as if i am missing something (well something in addition...more >>

Hai 1. Is it possiable to delete a record from the parent table.It is even ok to me , if it leads to the deletion of all the child tables . 2.I 've come across a situiation where the name of the table is to be supplied by the variable in my sp ,like @t = 'table1' select * from @...more >>

I am programing an asp web page using ado/sql. My query is timing out. I was doing just fine when I wasnt doing an inner join. I test an instance of the query (Using the inner join) in sql query and it ran fine, but when I did the query in the web page it times out. Any Suggestions thanks ...more >>

Hi gurus, I just started to look at a very slow-running SQL statement generated by an application (Siebel). I spooled the SQL from the application, replaced the bind variables by their values, and tuned from the Query Analyser. But after awhile, I realized that the statement using bind varia...more >>

Hi I have a Table with Automatic ID numbers... In access I delete All records by hand When I add new record with delphi (SQL) the number ID of record count begin with last record+1 and not with 0 Someone know the statement to reset that? thanx for any help ...more >>

Please let me know if there is a more suitable group to post in. In query analyzer I do : alter table mytable add mycolumn varchar default 50 But when I check in Enterprise manager the column has a length of 1 rather than 50. What am I doing wrong? Thanks. Cheers - Tom. "Do ...more >>

I'm having problems using an update query. I get the error: Server: Msg 8152, Level 16, State 9, Procedure SP_MemberUpdate, Line 3 String or binary data would be truncated. The statement has been terminated. I have 2 databases that I DTS data out of and create a view for each. I'm trying to u...more >>

Hi, I have a tricky sql statment I have to write (tricky for me) and I am stuck. I'm having trouble with the following problem. Table1 (Column a, Column b, Column c) Table2 (Column a, Column b, Column c) Table3 (Column a, Column b, Column c) Table1 contains a row of value (1, 2, 3) Tab...more >>

I have been searching the net and haven't found anything that has worked so far for resolving this issue, hopefully someone on here can provide some insight? I did a fresh install of a Windows 2003 Standard Edition server (as part of a workgroup, not a domain..the server is only temporary for ...more >>

We have a system at work that copies (using DTS) over databases from one SQL Server box to another every night. The copying process drops each target object and then recreates them. On the 2nd SQL Server box I have an account setup that is supposed to only have access to run queries on the dat...more >>

I have a SQL Server 2000 application that send's e-mail to users using the xp_smtp_sendmail procedure. That works fine. What I would like to do is RECIEVE e-mail with SQL Server. Example: I text message an alert to a user. They reply with some keyword in the subject of their reply e-mail,...more >>

Hi Chaps Can someone point me in the right direction for a very in depth practical treatment of this for sql2k. Seen a number of books that skirt around it, read some articles that touch on it (including some of erlands) but I need more... Thanks for any tips!...more >>

This one's really got me. I have a VB.NET (version 1.1.4322) project that provides an easy way to execute stored procedures on a generic level. When I run the code on computer A (running SQL Server 2000 version 08.00.0194) the code works great. However, computer B (running SQL Server 2000 ver...more >>

Hey, I've got an Classic ASP app pulling some records out of a DB, which worked fine when it was Access. When we upped to SQL Server, all of a sudden, when the Recordsets came back, all the dates were empty, even though the data was showing up in the DB. About a year ago (only code asp...more >>

I have a full sql statement which was generated dynamicly, and need to execute that string and then take the output and generate a spreadsheet document based on the output. I'm new to sql and the book I have doesn't really explain much. Anyone with an example of their work would be appreaciated...more >>

DECLARE @varDate datetime DECLARE @varMonthDate datetime DECLARE @varYear datetime DECLARE @varFOM varchar(10) DECLARE @NumDaysInMonth as int select @varDate = '2/1/2004' select @varMonthDate = MONTH(@varDate) select @varYear = YEAR(@varDate) select @varFOM = CAST(CAST(@varMonthDate as i...more >>

The code below returns the number of Service Visits logged to Each Property. What I'd like to do is summarise this data by displaying the number of Properties with 1,2,3,4,n Visits. I assume I need to nest the first sproc ? Ideas please ????? --------------------------------------...more >>

Hi All, I have a question about lock hint for you : If the first user currently run a select command with share lock and hold it. What kind of lock (lock hint) should be used by the second user in the select command (from the same table) so that this command will wait until the first user...more >>