You are not logged in.

#1 2005-03-02 20:07:17

phrakture
Arch Overlord
From: behind you
Registered: 2003-10-29
Posts: 7,879
Website

yes, I know... it's a SQL Server question

Ok, I'm not too familiar with the internals of SQL Server, but can anyone explain this:

On a reload of some code, an initial stored procedure used to get user information (full name, blah blah blah) is called... just some simple selects.  When the code is reloaded, this stored proc hangs... takes forever... if I go and run the proc from the database once, it no longer hangs and everything works fine...

WTF?

Offline

#2 2005-03-02 20:28:24

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,615
Website

Re: yes, I know... it's a SQL Server question

I am not familiar with permissions on store procedures, but could it be a user issue with initilization of the stored procedure code (ie and you running the proc from the db with the same user as the app)? Does the stored procedure implementation in the db you are using do any type of caching?


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

#3 2005-03-02 20:38:02

phrakture
Arch Overlord
From: behind you
Registered: 2003-10-29
Posts: 7,879
Website

Re: yes, I know... it's a SQL Server question

cactus wrote:

I am not familiar with permissions on store procedures, but could it be a user issue with initilization of the stored procedure code (ie and you running the proc from the db with the same user as the app)? Does the stored procedure implementation in the db you are using do any type of caching?

well, it could be a user issue, but I suspect some sort of recompilation internally...

here's what happened:
1) application connects to db, runs procedure..... never returns... times out

2) open query analyzer, run procedure with dummy arguments to find out why it's hanging... runs fine
go back to app and try to run again, viola...

the procedure wasn't changed... I changed just the app... and some table indexes, but I had been running locally for some time hitting the same database....

Offline

#4 2005-03-02 21:56:15

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,615
Website

Re: yes, I know... it's a SQL Server question

so the procedure is being run in 1), just not returning? hmmm. is there any way to confirm whether or not the procedure finishes execution normally, or if it is being killed internally due to a detected cycle or something?

Are there any variables in the procedure that need initializing, whether passed to it or present internally?


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

#5 2005-03-02 22:05:39

lanrat
Member
From: Poland
Registered: 2003-10-28
Posts: 1,274

Re: yes, I know... it's a SQL Server question

phrakture wrote:

the procedure wasn't changed... I changed just the app... and some table indexes, but I had been running locally for some time hitting the same database....

I would say deadlock is coming but if you made some changes in your app maybe it just can't read anymore the output of this stored procedure?

Offline

#6 2005-03-02 22:16:46

phrakture
Arch Overlord
From: behind you
Registered: 2003-10-29
Posts: 7,879
Website

Re: yes, I know... it's a SQL Server question

I think there's some crossed wires here:

Chronologically:
changed the app, went to test... procedure hung..(this had been happening for days)
finally ran the procedure from the database itself, and it worked
now (for the past few hours) the procedure hasn't had a problem

Offline

#7 2005-03-02 22:26:36

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,615
Website

Re: yes, I know... it's a SQL Server question

ok..so it would appear to be a problem with the procedure then..maybe just a coincidental database inconsistency?
ps. what db vendor are you using?
*bites tongue*


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

Board footer

Powered by FluxBB