I have a problem I am trying to currently solve. I want to return the row with the Max Cycle Number for each order. In the below example there are 3 rows for one order and 2 rows for another order. I want to return 2 rows: OrdId = 19852, Cycle = 52 and OrdId 20155, Cycle = 51. Can anyone h...more >>

Hi I have a parameter @Status in a stored procedure How can I achieve the following a). return all records or b) return only records that are = 'N' or null or blank I thought about where compflag = coalesce(@status,compflag) but this doesn't cater for both 'N' and null records wh...more >>

I'm trying to delete a user but SQL is given me the following message "You cannot drop selected login ID because that login ID owns object in one or more databases" any ideas on how to get around this issue. Again thanks for any advice that you can provide me.....more >>

Hi. Didn't see anything in my book for this. Is it possible to use a wildcard ( * ) in the criteria field when querying for data in a table? I've tried using * followed by a few characters but that does returns no data. Any help is appreciated. Thank you,...more >>

Hi All, I wanted to know how to verify a encrypted password in the table. I have encrypted the admin password while inserting. Now, how I can verify that the user entered is correct or not. Insert Query is as Follows : DECLARE @strUserEmail NVARCHAR(50), DECLARE @strPassword VARB...more >>

Below I included the portion of the verbose log file I created to try and see why the sp3a when burned to a disk, stops and asks for a disk insert at the end of the installation. It appears that that the orginal install was created from drive c and the sp3a is from in this case drive E: t...more >>

hi, I am using mssql7.0, Database gone into suspet mode., what could be the problem, along with that, would like to know what should be setting for transcation log and database file size increment and space allocation. Presenly db Size incremant set to 1mb and space allocated is 1.8gb whe...more >>

Hi I have the following SQL string that I pass to SQL 2000 using a command but I get the error -2147217900 - The column prefix 'A' does not match with a table name or alias name used in the query. Here is the string Select StockCode From InvMaster A Where WarehouseToUse = 'P' AND StockCode = ...more >>

I have been beating my head over this query. I would appreciate some help. I am using ordnum (order number) and orlink (orders that link to another) Main Order Link To Order 76213 72193 76213 85613 76213 42910 76213 76...more >>

Hello, I am realy struggling with this. I need to update a feild in one table based on the value of a feild in another table I have SQL server 2000 Example: goes something like this. IF F4101.IMSTKT = 'U' SET F4102.IBSTKT = 'U' JOIN ON F4101.imitm = f4102.ibitm Thank you, In adv...more >>

I have a working Access SQL statement (below) that is not working on an SQLServer and I can't work out why DELETE [table1].*, [table2].* FROM [table1] INNER JOIN [table2] ON [table1].[table1ID]=[table2].[table2ID] WHERE (([table1].[table1ID]) = ?) If anyone could point out to me what...more >>

I want to use rownum(oracle) like function to return a running value like serial no. in Sql Server Query. Any help please. Thanks in advance....more >>

Hi I want to be able to return a set of records from one table that don't appear in the resultset of another query. Eg InvMove table has details of all stock movements InvMaster has details ALL stockcodes Select Stockcode from InvMove M inner join InvMaster I On M.Stockcode = I.Stockcode...more >>

Hi I want to be able to return a set of records from one table that don't appear in the resultset of another query. Eg InvMove table has details of all stock movements InvMaster has details ALL stockcodes Select Stockcode from InvMove M inner join InvMaster I On M.Stockcode = I.Stockcode...more >>

I have a database containing several tables, two of the tables have an identical column I need to use, in Access I simply prefix the table name before the sql query, For example Tables![TStock]![StockCode]=Tables![TOldStock]![Stockcode] how do I do this in sql server 7 in a Select query...more >>

Hello, I am currently changing the Access queries in my database to SQL Server Pass-through queries with view to speeding up my application, by-passing the Jet engine. I have been creating pass-through queries in Access and linking this to my SQL Server. I now need to convert some of th...more >>

Hi there, just one easy question, hope you can help. I need to ALTER a TABLE with simple SQL-Statement. But how can I format a DATETIME column within my query. Example: ALTER TABLE table_name ADD Datum DATETIME; Now I have a column Datum in DATETIME Format, but the additional Paramet...more >>

I need to know how to use an alternative E-Mail solutions other than SQL Mail? The article on the knowledge base cannot be found any more. Thanks Brian...more >>

I have several insert statements like following. But the rows in tblRptQcDocIndex table are not in the order they are supposed to be, e.g. "A" - "Z". In other words, they are randomly in a order like "M" - "S", "I" - "L", "P" - "R". Any idea? INSERT tblRptQcDocIndex SELECT * FROM tblRpt...more >>

Hello. I will like create a query that will combine two or more fields into one. I good example for this is when you have the first, last and MI in different field, but when displaying in an application (ASP) you want them to show like it was inputed together. Like: John W Smith or combine it s...more >>

Hi, Does anyone know if I can create Materialized views in MS SQL 7.0? If possible whta will be the syntax. I have searched all over the place and cant find anything about it. Thanks, Pedro...more >>

Hello, I am changing the syntax of my access queries to be SQL Server compliant. I have got partway, and would now like to place the result of my datediff into a case statement, here I experience a few problems. '************* ORIGINAL ACCESS QUERY ************** SELECT eta, [Book Tim...more >>