[SQL] Why doesn't my procedure work properly?

Question

0.00/5 (No votes)

See more:

I have this extremely simple procedure, and for some reason it works when I'm activating it using the Visual Studio's Database thingy, but fails when I'm using it from my code (the extracted value is 0 instead of 1 when there's such a user).

SQL

CREATE PROCEDURE Client_Authenticate
 (@ID int,@Password varchar(32),@OutAuthenticated tinyint OUTPUT) AS
 BEGIN
 	SELECT * FROM _Clients WHERE ClientID = @ID AND MD5Hash = @Password
	SET @OutAuthenticated = @@ROWCOUNT
 END

Also the following procedure seem to do nothing when executed from code.

SQL

CREATE PROCEDURE Client_ConnectionChanged
 (@ID int,@Value tinyint) AS
 BEGIN
 UPDATE _Clients SET ClientConnected = @Value WHERE ClientID = @ID
 END

Any reasons why it should fail?

Posted 29-Oct-11 9:59am

ShacharK

Updated 29-Oct-11 10:14am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Not Active · Answer 1 · 2011-10-29T10:25:00

Solution 1

Visual Studio Database thingy? I'm unfamiliar with that tool :)

There would be no reason a stored proc would work in one environment and fail in another. Make sure you are connecting to the same database and you are handling any exceptions. You can also SQL Sever profile to monitor what is happening

Posted 29-Oct-11 10:25am

Not Active

Comments

ShacharK 29-Oct-11 16:35pm

How can I debug the Procedure code?

By the way, how would you recommend handling multi-threaded database access? Should I be holding a connection for every thread?

[no name] 29-Oct-11 16:48pm

You can debug a stored proc either through SSMS or using Visual Studio Database Edition, or thingy if you prefer.

Multi-threading and connections depends on your usage but generally connections should opened as late as possible and closed as soon as possible. SQL Server will pool connections so there will be some optimization as far as opening connections

ShacharK 29-Oct-11 17:01pm

Well, I'm executing my queries in very specific functions, so its not a problem to pool.
I have several main threads, only two of them are accessing the database. The one is actually a timer, used to load data from a specific database table (once in a while... haven't decided what would be the interval left, and I assume it would be for a few seconds once in real time).

The data is loaded into a queue of messages, which are sent by another thread to other instances of my program, and are received by the second database-accessing thread (in other instances), and it can do operations on the database.

So I have actually one reader, and one writer. The reader is used once in a few minutes, The writer runs whenever it gets data from the sockets...