How can I update the query timeout value in SQL 2K? I have a batch of stored procedures that occasionally time out. How can I update the timeout value for a particular stored procedure Thanks....more >>

Please help me with complicated query: eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where value in "a" field is dublicate, but leave one of each. in simple sentence I need to delete invert distinct of field "a". Please help me with this query thx ...more >>

Hello, can SQL Server 2000 survive a join of a table on itself 400 times ? Something like SELECT * FROM myTable AS t1 INNER JOIN myTable AS t2 ON t1.field2 = t2.field1 INNER JOIN myTable AS t3 ON t3.field2 = t2.field1 // and so on... The table myTable would have only 10 colum...more >>

I'm trying to return the TOP n records of a SQL Server table as follows SELECT TOP 5 SalesTransactionID as [Trans ID] FROM SalesTransactions WHERE SalesTransactionID > 101 I get Trans ID ----------- 101 101 101 101 101 Ok So far If I use an ORDER BY clause, I get unexpected result...more >>

I am trying to set up security for my intranet at work and want to find out how to do a stored procedure to check Logons, usernames, passwords and groups a person belongs to. We are going to set up a special group to access some of the areas of the site and I don't want to set up another se...more >>

I am really confused and hope someone can help me out. I have a script that will be distributed to our customer base in an upgrade of our software. This script is ALTERing many tables to ADD a column with a DEFAULT. Some of these tables have hundreds of thousands records. When I am running t...more >>

Hi everyone, I'm having this strange problem in and asp page that makes an update call in sql but don't have the same problem in the enterprise manager. userQuery = "update MonthlyReport set weight = 16 * weight;" MainConn.Execute(userQuery) Initial Values 5 10 20 Result: Weight ...more >>

I'm learning How DBMS processes concurrency transactions. Could you introduce me some material about this problem? It seems better to have examples Thanks a lot ...more >>

Hello, I have a DataTable containing an Image-Field (DataType Image). If I = change the Image in the DataField and try to update the DataSource (SQL = Server 2000 sp3) through the DataSet.Update() Call, I receive the = following error: 'SqlException': The query processor could not produce a...more >>

In the classic self-join example that we've all seen, an "employees" table is joined to itself so that the immediate supervisor of each employee can be returned. (to make sure we're on the same page, please refer to http://www.onlamp.com/pub/a/onlamp/2001/07/12/aboutSQL.html for sample SQL artic...more >>

I have a column that contains phone numbers. I need to strip out off the blanks, parentheses, minuses etc and leave myself with just the 10 digits. Is there any easy way to do this? -- Jonathan Blitz AnyKey Limited Israel "When things seem bad Don't worry and shout Just count up...more >>

My son is starting his own business and mentioned SQL 7.5. However, I can't find it. I've only found 7.0. Is this 7.5 obsolete or am I doing something wrong? Thanks in advance....more >>

Hi, I have a big problem on precision, when I try to multiply 2 decimal datatypes or divide 2 decimal datatypes, The results get rounded to 2 decimal places. But I need atleast 12 dp. Eg., declare @d1 decimal(38,12), @d2 decimal(38,12), @d3 decimal(38,12) set @d1 = 1245879954685....more >>

Thanks, Darko ...more >>

I currently have a stored proc that uses a cursor that I would like to replace with a different (and hopefully faster solution). The problem is that what I need seems to be impossible to come up with without a cursor. What I really need is for one of the fields in an initial data put to contain th...more >>

Hello all, I hope someone can point me in the right direction. One of the database tables we use was poorly planned out. Initially, all data was deleted each morning and the table was repopulated. Now, however, we are under tighter time constraints and want to drop the table and recreate it...more >>

Sorry for asking too many questions today. the question is, if I truncate a table, then those data are not going to transaction log. So, is it still possible to rollback to get all my data back? I know you can do restore database but assume we don't have any database full/differential backup b...more >>

I'm trying to wrap my brain around this whole concept of the integrity provided by cursor and transaction isolation levels, and I'm still unclear on some points. My question a few weeks ago was regarding the default (read committed) isolation that a cursor automatically has. If I understand...more >>

Hi, How can you explain Hash Join in a few sentences, I know it's in book-online, but it's like a whole page of explaination. ...more >>

Hi all, I'm a rank beginner at Transact-SQL programming and I would like some suggestions. What I need to do is calculate the moving range average for a column in an efficient way. This will be part of a stored procedure that works on SQL Server 7.0 and 2000. For those unfamiliar with moving r...more >>

I have function in a stored procedure that works fine by itself, but when I try to use it in the select statment it will not compile. The query is made up of many different columns in a four table join. I pass in a column from a joined query into the function as a parameter and I get all...more >>

Hello Sir/Madam I am pretty sure this is simple. But I guess I am having a temporary brain failure. I am trying to do a query on a table that has two identical entries on 2 separate dates. This is the case for thousands of records. I need to get only the latest date in my resultset. The only probl...more >>

Will SQL Server 7.0 work with ASP.NET/VB.NET? Also, will it work with Office 2002? I to import data from tables to insert into word templates. Thanks, Sam MCP...more >>

I need create one index, but i need this index only have a one part of a fild, example: if the fild have 20 char's... I need to create indice in the first 10 characters it is that possible one? pd... sorry by my badly english ...more >>

let's say I have one C: drive, is it possible to install 2 instances on C drive or I need to add an additional drive D:? ...more >>

Hi, how can I find a list of tables which do not have any indexes on them in a database? I have too many tables in the database so i can't check them one by one. ...more >>

Hi. Any suggestions for this would be great!!!! It used to work fine so I'm not sure what I did wrong. I have a view that shown below (sorry for the length) that counts the number of days from when an account was trained to when it ordered, and the time between orders. The problem is that ...more >>

I am running a stored procedure to retriever a for-xml auto recordset. I understood from somewhere that "for xml auto" would replace the "special characters" (&, ', ", <, >). The only one being replaced is the "&". If I try to "manually" replace the others with replace()s, all the ampersands get ...more >>

Hello, Is it possible to have Foreign Key between databases? If not, how could you provide data integrity if you have to deal with two different databases? Thanks, Jim. ...more >>

SQL 2K We have a job to run a DTS which loads and computes data from a database in the same server.. This jobs worked till yesterday, failed today with error message Executed as user: MyDomain\Myuser. ...: DTSStep_DTSExecuteSQLTask_11 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_11 DTSRun...more >>

Hi, maybe off-topic but I've found this group to be full of very smart people so I'll give it a try! We currently have a lot of Crystal reports and we are considering changing our philosophy on how we use it. We are thinking that instead of linking tables, creating complicated sub-reports etc...more >>

I need to create a sorted view and then join it to another table to make a combined view for a report. How do I do this if I can't use Order By in views Any help would be appreciated Thanks Ted...more >>

Just to elaborate on this theme, is there anyway that I could adapt the query so that if there isn't a price available in the STOCKPRICES table for the currencyid specified then I can change my query to take the exchange rate from a 4th table (or session var) and convert the default currency pri...more >>

Hi there! :O) I've been trying to make a query, that seemed stupid in the first place, but now i'm wondering what's the most efficient way to actually do it. here's the create statements and explanations that goes with my problem.. watch for line wrap. : --------------------------------------...more >>

We have a database that needs to keep track of how many reservations are made. Each transaction will update Reservation table and ReservationCount table. As you can imagine when you have several concurrent connections attempting this transaction for different reservations it produces deadlocks. ...more >>

Please fix your system clock. "Be Learning" <coldman@hotpop.com> wrote in message news:OztyGlbMEHA.4036@TK2MSFTNGP12.phx.gbl... > I'm learning How DBMS processes concurrency transactions. > > Could you introduce me some material about this problem? It seems better to > have exampl...more >>

Hi in a custom action (VB.NET installer class) I need to create a database, initialze table values and so on. I realized this task in VB.NET (1.1) by calling an sql script (via shell and osql). Everything works fine until SQL Server service is not started or user has not sufficient rights for SQL ...more >>

Can somebody explain what is meant by first normal form thanks, Paul....more >>

If I have a login name starting with a number then I am unable to pass it to the sp_who stored procedure e.g. sp_who '1A' as it returns an error that it can't convert this to a number. Apart from taking the necessary code out of the sp_who stored procedure and using that, is there any way to conv...more >>

When I do this select * from mytable where name IN ('Joe', 'Matt' It will return all the rows with name 'Joe' or 'Matt' regardless the case What if I want case sensitive ...more >>

HI, Is there a way to query the DB using a sp where I can have multiple parameters (i.e an arraylist of parameters): For example: Based on a list that the user has displayed (OrderID, Address, etc.), I want to query the DB to return the exact list that the user is seeing, after he modif...more >>

When I run this query SELECT DISTINCT BranchName, PersonID FROM MembershipPersonsHistory WHERE (CONVERT(varchar(10), '04/20/2004', 101) BETWEEN EffectiveDate AND ExpirationDate) Group By BranchName, PersonID i get 2 rows, which is correct but when I run this query SELECT DISTINCT Bra...more >>

I have a trigger that inserts 100 row into a related table when a new recorecd is inserted into the parent table. Currently I have 100 lines to insert each row and was wondering if a loop could be written to replace the many lines of inserts. Something similar to a for i < 100 loop?...more >>

I need a function that will grab values for both a regular table ( charge ) and a archive table (archived) given a social sercurity number, customer id, start date, and end date. However the start date and end date needs to be optional. The code runs fine outside of a function but when I try...more >>

Hello, Is there any decent book/white paper etc explains the strategies for deploying and updating/maintaining the database applications in subsequent releases using MSDE/SQL Server and VB.Net.. thanks *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in ...more >>

Hello Experts, Shouldn't the following query run? select top 2 * from syscomments order by number desc OPTION (ROBUST PLAN) Thanks in advance!...more >>

Dear Sir, I am a VB programmer and now working on SQL Server7.0. In SQL Server I want to create a stored procedure named sp_autoNumber which will giv me the maximum value of specify field in specify database. So I write down this statement in SQL Query Analyzer but it gives me an error "...more >>

I have a table following create table t int primary key not null t2 int foreign key references t(t I only want to select records to which have no record refer (more clearly : I only want to get records have no child I write below select * from t where t.t not in (select distinct t2 fr...more >>

Hi - I have 3 tables, tblUser, tblRoles, and tblLevels - each user can have access to one or more levels, connected through tblRoles. What I want to do is select all users where there is a maximum levelid of (example) 2. My select is: Select tblUser.uid, tblLevels.leveltxt, tblLevels.leve...more >>

Hi all, what is a short cut key provided by Microsoft in Query analyzer 8 for jumping on result pane and source pane Please let me know if anyone knows....more >>

I've heard the term 'relationship cardinality' used from time to time and sometimes I think it is referring to 1-to-1, 1-to-many or many-to-many relationships. Which one is it? Or is it all? It would be good to find out once and for all. Thanks in advance. Bob ...more >>

Ok, I have to enter up to 3,000 records (56 bytes each)into a table in one shot. It takes forever doing the standard dataadapter/update. I changed to a stored procedure to save each record, but the time saved was negligible. Increased connection packet size, no improvement. The only thing left...more >>

Salve Ragazzi!!! ho un problema che non riesco a risolvere e spero di riuscirci con il vostro aiuto... dunque.. ho un file csv tipo questo Date,Aircarrier,CallSign,ComFltNbr,AcReg,AIPTDep,AIPTDst,STD,STA,AOBT,ATA,CancellationDate 01/2004,AZ,,0735,IDATD ,SKG,MXP,01/01/04 04:20,01/01/04 06:45,...more >>

My index is non clustered because i need to insert often in this table. Read past is useful only for some situations.. Why locked rows are not included in my output? Shouldn't be in the output without change (in the state the rows was before the transaction start) Ther's an option in select claus...more >>