I need to create a union query. When I do I receive an error message that says "The Query Designer does not support the UNION SQL construct.". How can I get around this. Thank you in advance for any help that anyone can give me. Marty...more >>

I have Access 2000 database. In its table, there is a field, called Case and field type is number. Usually its data is in sequence. But sometimes, it skips one number. I want to list the beginning of records, and end of sequence. For example, data is 1, 2, 3, 5, 6, 7, 8, 10, 11. Rep...more >>

Hi, I've two columns in table importDate(char) and Import_Date (Datetime). I want to import the data of char field in to datetime field. when I'm trying to insert tha data is getting messed up. Please let me know if I've to use any function to convert Char field to Datetime. Regards,...more >>

Below is my WHERE clause: WHERE SOP10200.SOPTYPE = 2 and SOP10200.QUANTITY > 0 AND SOP10100.DOCID NOT IN ('SP','DF','TS') AND SOP10100.VOIDSTTS = 0 AND SOP10100.DOCDATE >= @StartDate and SOP10100.DOCDATE <= Left(@EndDate,12) AND (CASE @SelectBy WHEN 1 THEN (IV00101.USCATVLS_1 BETW...more >>

Hi, I am trying to work out the actual table names for temp "#" table names created in a session. For e.g if i have a seesion which has created a temp table #temp1 what will be the actual table name in sysobjects? I can see an entry in tempdb..sysobjects saying "#temp1___________________...more >>

Using DBCC commands or any other commands is there a way i can check for the registry entries made on that machine??? Santhosh.C. ...more >>

I have 3 tables AUTHORS BOOKS SW_BOOK_AUTHOR_JUNCTION this is a junction table joining authors to books as this is a many to many relationship what i want to do is list my authors and a count of how many books they have each authored. author ! count of books --------!---------------...more >>

I have the following join FROM TrnDetail LEFT OUTER JOIN FormData ON TrnDetail.Customer = FormData.KeyField This returns all records from TrnDetail whether there be an entry in the FormData table for the given customer. - This is what I want . . .however If I add a where c...more >>

Hi Everybody Can I override a function in SQL Server e.g, I want to use IsNull function in a different manner i.e., it should return True or False depending on whether the supplied argument is null or not by writing a UDF. Very similar to what Jet does. Any help would be appreciated...more >>

Hi, I'm getting the following error: cannot perform aggregate function on an expression containing aggregate function or subquery. In my query I am using sum() on a subquery. I wanna ask if is there an option in ODBC or in SQL Server to let me use an aggregate function on a subquery or I h...more >>

Hi, I have a problem checking the content of a table before inserting a new row. What I would like is a SQL statement for a web form which does the following: IF (SELECT aField FROM aTable WHERE aField="astring") is empty then INSERT INTO aTable (aField ) VALUES ('astring') END IF What is the best...more >>

What am I missing here? Declare @OldValue varchar(50), @FieldName varchar(50) select @FieldName = 'email' Select @OldValue = (Select @FieldName from Contacts where contactid = 32) print @OldValue OldValue always returs as "email" instead of "emaaddress@somwhere.com" I am writing a ...more >>

got slightly similar table. #Temp Table: name,name1,name2,exam,score A,A1,A21, A,A1,A21,math100,88 A,A1,A21,math101,56 A,A1,A21, A,A1,A21,math102,67 A,A1,A21, A,A1,A21,math104,45 A,A1,A21,Chm100,55 A,A1,A21, A,A1,A21,chm101,86 A,A1,A21,chm102,44 A,A1,A21, (skipping) A,A1,A22,math100,...more >>

I have an INSERT trigger set up on a table and when i manually run an INSERT from Query Analyzer, it works as designed, but when running from the application (peoplesoft), the error generated is: SQL Status = 22022, SQL Error = [Microsoft][ODBC SQL Server Driver][SQL Server]SQLServerAge...more >>

I have a query that returns data values ordered by Asc where the data is between 2 dates. Can I select The TOP 3 items in each group? I think that the TOP command selects all data in a single field irrespective of grouping. ...more >>

hi just wante to know the reason why is the ORDER BY clause INVALID in views ?? thanx. dotan...more >>

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 >>

Is it possible to change the following query so that the @classification can either be null - to return all records 'DB' to only return records where classification = 'DB' and ' ' to only return records where classification like ' ' here is what I have so far but it doesn't cater for the lik...more >>

New to SQl and would appreciate some help in executing a stored procedure where the criteria in a WHERE clause is more than one value for a given column. For example I use this when I pass a single value as UserID, but how could I filter the table if I have multiple values for the Where c...more >>

I really hope someone out there can help me out. When running a query, I want to fill an empty field with the "name" field minus the middle initial or suffix (JR, SR, etc). I know how to pull these off in Excel, Access, but I can't seem to do it in SQL query. PLEASE HELP - i would be fo...more >>

Hi have a the following snippet from a query where QotStat = coalesce(@Status, QotStat) If I don't give a value for @Status then all records are returned however what I want to achieve is that when I don't give a value for @Status I want records returned to be limited to where QotStat = 0 or...more >>

I just upgraded a system to SQL Server 2000. I have an access datadase that run's SQL queries. Now after the upgrade the queries no longer run. I know it is the access query but am unsure of how to rewrite then query. This is an example of one of many queries I use. SELECT datetime, meltr...more >>

Hi, I would like to merge two different tables running an SQL script thus I can export it to excel and then creat a flat file (.txt). Does someone know how the SQL should read? Thanks in advance Alberto -- Posted via http://dbforums.com...more >>

Does anyone know about to generate in Query Analyzer the Space Allocated Statistics that are graphically available in Enterprise Manager? What would be the correct query to run to generate this information? Thanks! Jarrad Wilkinson...more >>

hi All, As i am developing an application in Vb.Net. I want to take backup of SQL database. My application is Client server application. SQL server is on remote Machine. While taking back up i have to give the access to SQL server Machine only. if give the Path of Local machine it give th...more >>

Hello Sir I am facing a problem while using Cursors in Procedure Actualy the fetch is working properly when i Execute first time in query analyser.2nd time when i execute again the cursor is not fetching any data.The @@Fetch_status showing -1. Please help me how can i initialize @@fetch_statu...more >>

Hi, All I' Have 8Gb of transaction Log, is very big. It almost hang the server. Any way to decrease it or truncate it? Thanks. ...more >>

How do you rename a datafile on Sql Server?...more >>

MS Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to maintain t...more >>