Search results for query: *

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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