I understand that store procedures are the recommended way to handle SQL queries from an ASP application. However I am working on a small academic assignment and I just need to get this over with. I created the store procedure but now the teacher didn't accept that, as he is concerned he wou...more >>

I had created a test db below States: StateID(PK), StateName Fruits: FruitID(PK), FruitName, GroupId (FK) FruitState: FruitID (PK), StateID (PK) All columns is not null. This query works well with the test db :- SELECT * FROM Fruits f LEFT JOIN FruitState fs ON fs.fruitId = f.F...more >>

I got this sql statement SELECT ItemName, ItemId, ItemLocation, (SELECT COUNT(*) FROM Table2 t2 Where t1.ItemId = t2.ItemId ) AS ItemCount FROM ItemTable t1 Is it possible so that I can filter the resultset using ItemCount? (WHERE ...more >>

I have a stored procedure: CREATE PROCEDURE SearchHistoryClient @TextContain nvarchar(1000), @Email nvarchar(512), @Operator_URI nvarchar(512), @DateFrom DateTime, @DateTo DateTime, @Company_ID int AS DECLARE @SQL nvarchar(4000) SELECT @SQL = ' SELECT tblTemp.ChatTime,...more >>

Hello all, a first post here. This select gets the max value within 10 minute intervals: SELECT n_unixtime, MAX( f_value ) FROM ... GROUP BY SUBSTRING( FROM_UNIXTIME( n_unixtime ), 1, 15 ) But this will only work with substrings of a date - hour, day, etc. Is there a better way to grou...more >>

Hello, when I try to restore over an existing database, I get the following message: "Database is in use. The system administrator mus have exclusive use of the database to run the restore operation. Backup or restore operation terminating abnormally." And it "wasn't me", i.e. I wasn't con...more >>

I wanted to give user message from SQL Stored Procedure. I tried to use PRINT, but it does not go to my MS Access front end application I tried to use 'RAISERROR' to send out message, but the message has SQL server message to my MS Access. Are there any way to sent message to my front end...more >>

Hi all, CREATE TABLE Result ( ValueString char(8), ValueDate datetime ) Sample data from ValueDate column in british (103) format: 29/1/2005 1/12/2004 1/1/2003 How to convert to ValueString respectively 20050129 20041201 20030101 Thanks Zed ...more >>

I have an SQL Select statement strSQL = "Select Distinct FieldA, FieldB , 0 as FieldInt from Table1 Order By FieldA" I create a recordset rs in VB6 Set RS = New Recordset With RS .ActiveConnection = m_Conn .Source = strSQL .CursorType = adOpenStatic ...more >>

Please fix your clock. ...more >>

Query Help - Please I have a table: CREATE TABLE [dbo].[tblStudentOffers] ( [stud_no] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [crse_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [subj_offer_code] [varchar] (10) COLLATE SQL_Latin1_Gene...more >>

Hello - I am trying to construct a message board. I started out with three tables: tblLogin with UserID, some other columns, and ScreenName; tblPost with PostID, UserID, Subject, PostMsg; and tblReply with ReplyID, PostID, UserID, ReplyMsg. I know this is not the correct way to do it, s...more >>

I am trying to setup a system to let people type "secret phrases" in a webform (of course the below is not secret, this just for my testing now using pubs db). Upon matching the 3 phrases against the SQL db, the password can be reset in AD. My question is this, what would be the best way to h...more >>

Hi guys. I'm totally new at stored procedures and the world of SQL Server, not = even sure if this is the right forum! I want to write a stored procedure that will tell me simply if a record = exists with a particular value. In psuedo I'd write something like = this... "SELECT * FROM user...more >>

I am using SQL Server 2000. I have two sql statements like the below example. Declare @id int set @id = 21 select * from rule where corp_id = 123 and (rule_id = @id or rule_id is null) select * from rule where corp_id = 123 and rule_id = @id Both statements will return 1 result back...more >>

Sir, I am not very sure where to post this message, however since I am in this group some times, I ask it here. My apology if it bring any trouble for you. I am making a web client to microsoft newsgroup. Which can access to all group on news.microsoft.com, and will automatically all...more >>

I have a large nvarchar(2000) that need to be queried on often based on a subset of the data (first 50 characters). The application creating and using the data cannot be modified to capture a short and long column... I was wondering if creating a computed column with the formula being Left(l...more >>

I have a simple query that gives me the last updated record from a table 'casecomments', the field with the date is 'LastUpdated' SELECT TOP 1 * FROM casecomments Order BY LastUpdate DESC But what I need to do is kinda 'merge' that selection criteria to my full query, the left join to the ...more >>

Hi all, I have Date_Mmmyy column in my table that stores dates in MMMYY format for example; "OCT01","OCT04","AUG04". I need to update another column in this table, which is DateTime, with the last day of the Date_Mmmyy value. Here is an example... DECLARE @test table ( Date_Mmmyy v...more >>

If you have something like select au_fname from authors order by au_fname You get: au_fname -------------------- Abraham Akiko Albert Ann Anne Burt Charlene Cheryl Dean Dirk Heather Innes Johnson Livia Marjorie Meander Michael Michel Morningstar Reginald Sheryl Stear...more >>

I wonder if I could write a dynamic where statement in a store procedure. I have a web page that will pass following information to the store procedure. Lets say I have a parameter @typeid that can be 1 for rent, 2 for sale, 1 or 2 for either. My store procedure looks like this create select...more >>

Hi, We are experiencing serious performance issues in our production SQL server. It happens that from time to time that the CPU's jumps to 100% use and stays in average of 80 to 90% of CPU use for long periods of time. Configuration: 4 XEON Microprocessors 8 GB RAM MS - SQL 2000 ...more >>

Hi, Think of that there is a db which is used by many users. An there is a proc which is called frequently by all users. For concurrent callings of this proc by many differents users, Does there occur several execution time intervals as parallel or are the concurrent callers wait each other? A...more >>

I've been told that some tables in my SQL Database are corrupted and need to be repaired. In searching google I found examples such as "VERIFY TABLE TblName" but when I try that in QA it is not a valid command. How can I verify which tables are corrupted and then how do I repair them? Wayne ...more >>

Hi, I'm a beginner and so perhaps this is a dumb question, but here goes. here's two query statements: SELECT * FROM FewUniques WITH(index(0)) WHERE Status = 'Inactive' SELECT * FROM FewUniques WITH(index(inFewUniquesStatus)) --inFewUniquesStatus is a predefined INDEX WHE...more >>

I have the following statement: SELECT a.id, a.caseid, a.firmid, b.comid FROM cases a left join casecomments as b on a.id = b.caseid left join users as f on a.empid = f.userid inner join users as d on a.alienid = d.userid left join employment as l on d.userid=l.userid inner join processc...more >>

Hi, Sorry for asking about the same question... I would like to ask when I grant permission to users, all they need are users tables/view/sp... i should deny all permissions for sp with dt_xxx and tables with sysxxxxxx Thanks Ed...more >>

Hello All, I was trying to optimize a SP by replacing a Dynamic SQL with Ad-Hoc Sql. The code that I replaced it with seems to be worst than the original code. Would any one know why. The first batch of SQL statements (Dynamic Sql) is the original code and I tried to replace this code wit...more >>

I want to use a Select Top statement to filter out a portion of one field in a list of fields. I want to select the top 7 "rating"s (which is an integer datatype) to be part of this prospective recordset: select horsename, gateposition, rating FROM tblrace where trackname = '" & request.querys...more >>

Simple process, I know, but I don't recall how. I need to automatly update an "updated" column with txn datetime when a row is being inserted. ...more >>

Hi, I have a table A like: id -- 1 2 3 and a table B like: id type -- ----- 1 lion 1 tyger 2 donkey 3 lion 3 tiger 3 monkey I'd like to return a table C like: 1 lion 2 donkey 3 lion i.e., join A and B but only get the first row of multi rows. I don't want to ...more >>

Hello, I'am using the query below to get some information out of the table named uren in Crystal Reports. Because CR first needs all the information and then it sort it out (can't send parameters to a query) i have to get alle the needed information out of my table. The problem is, that i need...more >>

I am trying to delete a job, but keep getting the error 14274. It says thta I can not dlete the job because this job originated from an MSX Server. I would appreciate any help on this subject. Thanks ...more >>

I need to archive some rows from a production database. Any ideas on good principles to follow ? I would absolutlely want to ensure that each row is properly archived prior to deleting it from the production table. Anyone have ideas that they used and wish to share ? Thanks ...more >>

I have an application that, upon exiting, has to update upwards of 300+ records in a SQL Server database. There are issues with timeouts due to network traffic and server load. I was wondering if there was a better procedure or method of transferring a large group of simple sql tasks to t...more >>

Is there any resolution to this problem, When I connect to a Remote SQL server, over the Internet, with an opened port throgh the firewall. Using the "workstation id="ASPSERVERNAME";packet size=4096;user id=UUUU;data source="x.x.x.x,pp";persist security info=True;initial catalog=RBS;passw...more >>

How do I write a connection string in VB to use the local database. I have something like this, but it does not work. I do not want to use the local computer name. I want to make it generic so that the application can run on any computer. Thanks...more >>

I have the following sql: SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastNm, e.Processcatalog, b.I94Date, a.ExpDate,a.maincase,a.caserelationship, f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm, k.caseid, k.comments FROM Cases AS A ...more >>

Hi all, How do I customize table names returned from the stored procedure? For example, I have a stored procedure that is something like this: -------------------------------------- SELECT * FROM Employee SELECT * FROM Employer SELECT * FROM HealthInsurance -----------------------------------...more >>

Hi, I am trying to execute this very simple select statement: __________________________ select a.name from tp2.tp2.tpt_currency as a, oes.dbo.Customer as b where a.shortname = b.CurrencyCode ___________________________ I get the following error: ------------------------ Server: M...more >>

I have a bunch of data related to a property. On that property, we have several measurement requirements to perform several calculations. For instance, I may have data that currently am planning to set up as follows: PropertyInformation table: PropertyAddressID, ResidentialPropertyTypeID, ...more >>

Hi, Is there any third party tool that can allow us to look at what's inside the transaction_log? right now, i believe they are all binary data now... Thanks Ed...more >>

Can I pass parameters to a script or stored procedure that specifies table names to be used in an ALTER TABLE or CREATE TABLE statement within T-SQL? I would like to automate making changes to basic table structures. ...more >>

I am upgrading an Access database and need to convert some of the fields from text to a foreign key from another table. This is no problem and I already have the query batch to do this. I would like to know if it is possible to change a column type from nvarchar to int. Thanks, Drew Lain...more >>

I have a question about how the TOP n clause works on the SELECT statement. Assume I have a table called TheTable that contains 10,000,000 rows and no indexes. Assume I execute the following SQL... SELECT * TOP 100 FROM TheTable WHERE TheField = "12345" Will SQL Server first do a Tab...more >>

Hi, I have a system running in a production environment and a data anomaly bug comes up about once or twice a month that I can't figure out. Here is what is hapening. Within a sproc I have the following logic: begin tran insert into table1 (the pk for this table is an identity column) ...more >>

background: sql2k. what proper rights i can grant a user (developer) to create/modify DTS package and jobs but not granting the server admin rights? thank you. ...more >>

Hi All, I am wondering if someone can help me, I will try and provide as much information as possible, I have a function, most of the code listed below begin if @levelName = 'Sector' begin insert @levelTable SELECT DISTINCT tblSurveyDerivative.surveyID, tblMarke...more >>

Hello, I have a trigger on a table (after update) which keeps track of modified records / columns. I need to have a function wich can make updates to that table without firing the trigger (i.e. I don't want to track changes made by that function). So, I need is to make the following stat...more >>

hi, I am wondering in order for a user to use indexes in tables, I know he needs permission for those tables, how about the sysindexes table, do i also need to grant him sysindexes permission, too? Thanks Ed ...more >>

Hi All I am facing following problem while running a job from another job... I am writing following command to execute the another job. Exec sp_start_job @job_name = 'CSD_Phase2_Weekly_Job' In response of above I am receiving following Error Messege. Server: Msg 14262, Level 16, St...more >>

Hi I'm wondering if there is any possibility to make a backup of database protected in some way. Maybe is there a method to make a backup to a stream, not to a file, if yes, I would be able to cipher the backup by myself. I'd like to do it programmatically of course. Regards Miroo...more >>

Hi, If I join a table with a derived table, will the index be used? Thanks...more >>

Hi, I had a job interview yesterday and they gave me a small test to complete. One of the questions was the following... I was not sure what to answer... If you have the following table: CREATE TABLE [Customers] ( [FirstName] [varchar] (50) NOT NULL , [Las...more >>

Hi, After I grant permission for users only to the role of Public which I only grant Select right to them, they are still able to view the content of the Master and MSDB Database, is there anyway to block users from even looking at those two databases? Thanks Ed...more >>

Hi, I use the Enterprise Manager frequently and then have quite a few tables opened. I spend half my day (well, it feels like that anyway....) clicking away the messageboxes "Do you want to continue working with this resultset" and "the pane has been cleared to save resources". I understand...more >>

I am trying to do something that is not conceptually difficult but I can't seem to get my head around the correct SQL statement. What I am trying to do is something like this. UPDATE Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key1 = T2.Key2 SET T2.Field3 = T1.Field3 Of course, this i...more >>

I'm trying to find out what the fastest way is to query SQL Server for records from a table together with statistical information derived from those records. Currently i'm thinking of either: - Querying the table multiple times, once for the record data, and once for each statistical...more >>

Hi, is there any script (qucik way) to grant all Select, Update, Insert, Delete to a role which i call it "WebAccess" I would like not to spend too much time creating a role called "WebAccess" and go to that role and check on every Update, Select, Insert, and Delete for each object Th...more >>

I have a stored procedure that is semi complex, not too bad...but in its select statement it has something like: SUM(ordertotal) as TOTAL...and at the end it is ordered by TOTAL desc when ran, it takes about 1 minute to return data from 3 tables with about 20 million records. during the p...more >>

I have found 2 CASE statements in a couple of views and both seem to work. Is there any advantage of one over the other? Example below. Thanks Customer = CASE COALESCE(dbo.Customers.LastName, '') WHEN '' THEN dbo.Customers.CompanyName ELSE dbo.Customers.LastName + COALESCE(', ' + dbo.Custome...more >>

Dear all This problem really is driving me mad. I have written the query below... It works ok when I insert one row at a time. However if I insert, say, 4 rows at once (thus firing the trigger once) I keep getting this message: "Server: Msg 512, Level 16, State 1, Procedure tr_UpdateOrd...more >>

I am having a problem connecting to a SQL Server named [non-default] instance using the Server "alias" name. Connecting, through Enterprise Manager with the IP\instance name or server name\instance name works fine. Connecting to the default instance using the alias also works fine. I have ...more >>

I want all users in database to be able to call Set IDENTITY_INSERT [table] = ON However when a user who is not the dbo logs in to execute the stored procedure I get the following error message. prcIdentityFudgeSave, Line 8 The current user is not the database or object owner of table 'Ide...more >>

I am looking to find out when a database object has been created/modfied or deleted and by whom. Is there any way to get this information other than querying the log file. Thanks Tom...more >>

Hello All, Can I search into an image column without "full-text indexed it" ? Thanks in advance....more >>

Hi, Could anyone help me with the following complex problem: Suppose I have the following input: 'sig, ltw, onss' Than my output should give the following result set: sig ltw onss sig, ltw sig, onss ltw, onss sig, ltw, onss The solution should also work with any given number of names ...more >>

Hi, I'm having trouble finding a solution for the following problem: Suppose you have the following string as input 'sig, ltw, onss' I would like to generate the following result set: sig ltw onss sig, ltw sig, onss ltw, onns sig, ltw, onss This should also work for any number of name...more >>

I can find shrink method in object SQLDMO.database. but I don't how to use it to shrink my DB or shrink logfile. Is there anyone to have the experience on this case. thank for any help or suggestion! -- Developer QA Dashboard Microsoft China Development Centre...more >>

I did this in the past using .asp, but with ASP.NET 2.0 perhaps things have really changed, because I do response.write(TextBox1.Text) and doesn't work. Here is what I am trying to: Partial Class Default_aspx Sub btnDefault_Click(ByVal sender As Object, ByVal e As System.EventArgs) ...more >>

I am on MSSQL 2K. The field i am using to store chinese character is defiles as chinesename nvarchar(50) Using jsp file (meta tag set to UTF-8) to display form. User fill up the form and submit it. At backend i run servlet and insert into table. When i insert the value i use statements ...more >>

On ASP.NET 2.0, VB, I try to let user input data on TxtBox1.Text. Then I would like to go to SQL database named PUBS and compare the "TxtBox1.Text" against the table "job_id". If the results match, I return on the screen "Information matched. Your password will be reset". First, how can I ...more >>