Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  • Users: agar
  • Order by date
  1. agar

    RAND() function

    What the h*** is going on with the RAND() function? The way RNG's are supposed to work is you seed it first, then make subsequent calls to retrieve the subsequent numbers in the random sequence. The RAND() function has an optional argument -- the seed. From this you might guess that you're...
  2. agar

    Deleting tables with FK constraints

    Hi all, This may be old news to some but I thought I would post this code in case it would be helpful to someone. Basically the problem I had to deal with was this: we are developing a database application iteratively and every so often need to install a new version at the client site. As you...
  3. agar

    question: row locking w/ triggers

    Hi, Thanks for your help. When the problem occurred, I did a few selects on the database to confirm that, in fact, widgetid was unique in the widget table, and it was. The problem (as I just figured out) is that the first time through the loop, the variable @widgetid gets set to some number...
  4. agar

    question: row locking w/ triggers

    ARRGH! I just figured out the problem. Of course, when you do this: set @x = 4 select @x = x from my_abilities where name = 'programming SQL' if there is no such record in the my_abilities table, then @x will remain at 4 and will not get set to NULL. I deserve a few lashes for that.
  5. agar

    select top N with order by, N is dynamic

    Hi all, Does anybody know of an easy way to select the top N records of a table, where the select uses an order by clause, and N is dynamic (for example, is passed as a parameter to the stored procedure)? The "SELECT TOP" syntax must be hardcoded, and "SET ROWCOUNT&quot...
  6. agar

    question: row locking w/ triggers

    here are the "create table" statements create table widget (widgetid int, widgetcolor int, ... blahblah ...) create table widgetbin (widgetid int PRIMARY KEY, ... blahblah ...) here's the problem code: declare @i int, @n int, @widgetid int ... set variables ... set @i = 0...
  7. agar

    question: row locking w/ triggers

    Question: how do transactions and locking work w/ triggers? Let's say I have a table with an update trigger defined on it. I execute a query that updates row N in that table. Which, if any, of the following occur, and if none than what does occur? Scenario A 1. begin transaction 2. the job...
  8. agar

    RIDDLE

    ? In the set (3, 5, 4, 6) the number 6 is the highest value, the number 5 is the next highest value, and the number 4 is the third highest value -- right? So you order by desc, like this: 6 5 4 3 3 is the lowest value, 4 is the next lowest value, and 5 is the third lowest value, right? So...
  9. agar

    RIDDLE

    Don't take away the chocolate yet :-) I tested the code and it works. Try it!
  10. agar

    RIDDLE

    p.s. the advantage to this is that if this is in a stored procedure, you can pass N as an argument -- whereas with the "select top" solution, N must be hardcoded. At least, this is true in 7.0, maybe not 2000. J
  11. agar

    RIDDLE

    You use a variation on a technique taught to me by foxdev (thanks again!!! :-) set nocount on create table WHATEVER (COL int) insert into WHATEVER (COL) values (3) insert into WHATEVER (COL) values (5) insert into WHATEVER (COL) values (87) insert into WHATEVER (COL) values (255) insert into...
  12. agar

    undocumented feature in SQL Server 7.0

    I just discovered that if you are using sp_executesql, in addition to passing parameters INTO the statement you are dynamically building, you can also use output parameters. This is undocumented in SQL Server 7.0 but it works. I'm probably not the first person to figure this out, but I thought...
  13. agar

    I need something similar to "Sum()" but for strings.

    Thanks very much... I started a thread last night, then I figured it out anyway on the way home. Basically I had two transactions selecting from a row and getting shared locks on it, then both trying to update the row and deadlocking trying to get the exclusive lock for the update. I used the...
  14. agar

    Deadlock problem

    Okay I think I figured it out. I thought that setting the isolation level to "serializable" would mean that all locks are acquired at the beginning of the transaction, but looks like that's not the case. So, we still get the deadlock where process A gets a shared lock on the...
  15. agar

    Deadlock problem

    Yes, I'm sure. I've been reading the links on your FAQ and they have been somewhat helpful but no answer yet. The deadlock is happening in a stored procedure. I've got a somewhat complex system of nested stored procedures that all originates in a single entry-point SP. This entry-point SP is...
  16. agar

    Deadlock problem

    I've been stuck on this for over a day and I want to move on. Why do I keep getting deadlocks when the concurrent batches that are deadlocking should only be contending for one object to begin with (a single row in a single table)? I thought a deadlock happened only when process A has a lock...
  17. agar

    I need something similar to "Sum()" but for strings.

    foxdev, wow, you're totally right. I didn't believe you at first but I just tried it and it works. I guess that's how you got to be a "Sr. DBA" at "some big company" :) I'll just shut up now. BTW, since you're so smart :) -- can you answer this one for me (I've been stuck...
  18. agar

    I need something similar to "Sum()" but for strings.

    Yes you can do it without cursors, but it's a little more complicated than that. You still have to have a loop, and you have to have a primary key column in the table, which will give each row a unique ID. The ID's don't have to be integers and they don't have to be contiguous, but they do...
  19. agar

    I need something similar to "Sum()" but for strings.

    You can also use a cursor, in case the ID's aren't contiguous. Here's an example (this is all in TSQL): ----------------------------------------- declare @t varchar(...) declare @tdesc varchar(...) declare tcursor cursor local for select description from inventory where ... order...

Part and Inventory Search

Back
Top