#temptable got order,fname and age. trying to find two high maxes per each order. query returns (1) but (2) is correct answer. supposedly, max(age2) < max(age1). **** select d.order,case d.t when 1 then s.fname else null end as fname, case d.t when 1 then s.age else null end as age1, case d....more >>

Hi, I want to be able to update a table when a record is inserted into another table where the record inserted meets certain criteria. I have the following which works for all insertions but I don't know how to limit it to only fire when the record inserted has an invoice no. > 300000 CR...more >>

I wrote a trigger and now I want to use "DROP TRIGGER" to delete it, but it returns me: "Cannot drop the trigger 'AlertaAl', because it does not exist in the system catalog." But if I execute "select * from sysobjects where type='TR'" It shows me my TRIGGER. Even if I want to create an...more >>

We have some our applications and they related to SQL through COM objects, DLLs and Web-pages. Sometimes(aprox.once a day(in different time, do not related to any scheduled job)) SQL server hangs on. It freezes and does not want to respond anything (except reboot). In log files(SQL-logs and...more >>

Hi, I have the following query but it keeps coming up with invalid column name ( the ones that are defined as 'AS' columnname when not a SUM) - I assume it is something to do with the group by clause - what am I doing wrong? SELECT DISTINCT TX.orderno AS [JobNo], CO.CustomerID AS Customer, ...more >>

Hello, I have SQL7 and need to convert the contents of a field to upper case. However, I do not want to simply issue the following statement due to the number of rows: UPDATE <table> SET <field> = UPPER(<field>) So, I tried the following in an effort to cause the minimal number of tra...more >>

'iif' is not a recognized function name. has anybody seen this before, strange as I'm sure this is a function available in sql. I'm running sql2000...more >>

What is the following missing T-SQL code, Please if exit .................... drop database Testing Go Create database Testing...more >>

Hi, I would like to know if there is a proper way to extract data from a table containing several items for one key to a columns structure : example : Field1 Field2 xxxx 1111 xxxx 456 yyyy 789 yyyy 123 I want to obtain the following : Field1 FieldXXX...more >>

Hi, i need to update a table which is having 19635785 number of rows is it advisable to write an update statement which updates these many rows at a time ? are there any limitations on update statement? this Update statement we will run every month, and size of the table my grow every...more >>

I use DTS to take data from multiple FoxPro tables and move them to our SQL 2000 Database. To do this I use a chain of DTS Packages. Each package takes the data from a FoxPro Table for example "Equipment" and creates a table in SQL called "EquipmentTemp". The "Equipment" Table in SQL is th...more >>

I have a db in SQL 2000 that I'd like to copy on another server running MS SQL 7. Can this be done? Thanks!...more >>

I have a field Date_Etd which contains timestamp (ie auto enters getdate()) of record when entered. I need to to calc the Shift according to the "Time" (not date) record was entered. Shifts are: 07:00:00 - 14:59:59 = Shift 1 15:00:00 - 22:29:59 = Shift 2 22:30:00 - 06:59:59 = Shift 3 ...more >>

have duplicate records in application using sql as db. No ability in application to remove duplicate data. Need a query to find all duplicate records, or records created within a date\time range will do as well, then how to delete\remove them from sql. many thanks,, ...more >>

Hi I have a Stored Procedure but it says 'Error 156: Incorrect syntax near the keyword 'end' What am I doing wrong? Can I use Case when in Group By queries Thanks A FYI here is the SP: CREATE PROCEDURE ProdByMachine @DateFrom smalldatetime, @DateTo smalldatetime AS SELECT dbo.W...more >>

When a string search is done on a non-unicode column in a MS SQL Server DB, then the index on that column is not used. Is it a known issue? thanks, G....more >>

Does anyone know how the SQL.LOG file gets generated. My sql.log file is up to 2 gig's and i am not able to delete it. I also do not know how it gets created on my sql server....more >>

Hello: Can anyone help me out : Suppouse I have one table name country : fields are countryID, countryName. Data like these : 1. Japan 2. Jakarta 3. Jardan 4. India 5. Italy 6. USA 7. UK 8. Pakistan I want to show the country name randomly (SQL Server). Thanks in advance ...more >>

How do I truncate the transaction log in SQL Server 2000? Thanks, Colin Skopinski...more >>

The other I was asked to send the amount of volume executed in facilitation, block, and combo orders for the month of July. Forget combos for the moment, also forget the month of July and concentrate on a single day (July 1 for example). I'm interested in the volume executed which means t...more >>

Hi, I have a field that is varchar which in the main contains numbers (occasionaly there will be an alpha character) When I sort I get the following: 1 11 111 2 22 222 etc is there a way of sorting the field as though it were a numeric field eg: 1 2 11 22 11 222 etc I have ...more >>

Hi, I have an SP that retrieves a batch of records based on a date range. I want to use the result of this query within another query but I keep getting invalid object - Is this not possible? if it is what do I need to do? This works in Access no problem Here is what I have so far: SELE...more >>

I have a 7.0 DB on box A and I have MS-SQL 2000 DB on Box B I would like to export my data from Box A to Box B. On both the box the db has the same name. I use DTS , but what is the best way to do the migration ? Thanks for help Did. ...more >>

Hi, I would like to have an answer for a general question. Well I am asked to write about E Technology. Could anyone out here let me know what does E Technology relate to. Does it relate to web programming languages or something else. I would like to have a brief information about this so that...more >>

What differences in the three product's version of SQL? What are some of the major differences? Is one superior over the others? If so, Why Thanks Larry...more >>

Hi, I was told that i need to convert the excel sheet to mssql database before using the DTS in Enterprise manager to export it to the MSSQL Server(host) from my PC. Can you pls. tell me how to convert the file? I am using MS SQL 2000 Client tolls to connect to MSSQL 7.0 (host). Will b...more >>

I am running SQL7 somehow one of the tables from the database I have created has been deleted, I don't have any back up of it at all, I am wondering if SQL itself create backup copies like (table.back) or is there any references to it in the master database Thank you....more >>

I have a customer database that stores such data as Customer Name Address Customer Type For the field Customer_Type, there are two possible values stored i.e. 'B' & 'P'. When i return a result set containing the field Customer_Type, I would like the values 'B' to be displayed as 'B...more >>

I'm having problems using the OPTION clause in a view. There doesnt seem to be any documentation stating that the OPTION clause cannot be used in a view, but I still receive syntax errors when trying to compile it. Here is a scaled down version of the a) the T-SQL that works b) the view ...more >>

How do you create a second table based on an existing table. I have tried create table X as select * from y and many other similar variations and get the same error: --> Incorrect syntax near the keyword 'as'. Is this action possible?? Many thanks Keltie...more >>

I have 2 databases within SQL Server 2000. What I want to do is create a view in database A that references tables in database B. Is that possible and how? Thanks in advance ...more >>

Simple question that I know is gonna have a simple answer, I just don't know it! I need to print off table structures from within Query Ananlyzer so I can dump 'em out to Excel format. Something like "select table, column, type, size, null from whereever order by table, etc." Sorta li...more >>

I want to create a 'view' but have a condition on a column based on another column. Basically, I want to do this: CREATE VIEW MyView AS SELECT [id] AS dept_id, [name] AS dept_name, url WHEN STATUS = 'ACTIVE' FROM department WHERE event_...more >>

I would like to zip up 10 Gig backup files after the backup is complete and then delete the backup file. Can this be done automatically using DTS or some other utility?...more >>

Is there a way to generate result files from SQL that have all character fields enclosed in quotes? Any help would be appreciated. Thanks...more >>

Hi, I might be posting this in the wrong section, but I really need help with this, so if this is the wrong section, please direct me to the right one. I am looking for a product/API/anything that shows me relationships between sub-objects. To illustrate, say we have Entity A, which ha...more >>

Hopefully, this is an easy question and I'm just having a case of temporary stupidity. I'm using the following SQL string -- Select ((convert(varchar(10),Year(GetDate())))-1) + (convert(varchar(10),Month(GetDate())))) as Date I'm trying to get a six-digit "number", consisting of the fo...more >>