hi experts, can anyone have a look my simple questions, thxxx a lot!

Hi guys,

got another simple question (I am very new to sqlserver...), I have a c# desktop program, it connected to a sqlexpress 2005 server, and I need my program to connect to sql server reomotely , so I turn on TCP/IP protocol, my details are in below:

inside the server's tcp/ip on the ip tab, IP1 is set to active, enabled, the ip address is 192.168.0.3 , there is no port number for IP1, should there be a port number? dynamic port is 0

IP2 is set to active, enable with address 127.0.0.1 and also does not have a port number, dynamic port is 0

IPAll is using dynamic port 1921 but also has no port number.

for the client TCP/IP setting the default port is 1433 and it is also enabled.

I then set up a port forwarding service in my router with port (1921, I think that's the port number ?)when I run my application and sqlexpress server on the same machine, both of the following connectionstrings are working well for the program:

server= 62.31.81.210.\SQLEXPRESS,1921; user id='sa'; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True

server= 192.168.0.3.\SQLEXPRESS,1921; user id='sa'; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True

(I use dynamic port 1921 as the port number, don't know if it's right, but it works, but what do we need for TCP/IP setting the default port 1433? )

62.31.81.210 is the router's IP address, I attach sqlexpress's machine's(local ip address 192.168.0.3) on port forwarding service with port 1921, so above connection string is working right as I want to test wheter I can connect to sqlexpress server via real Ip address not just local Ip address.

then there is the problem: I try to run the same program on the other computer, but within the same local network (both sqlexpress server machine and application machine are connected to the same router ), I got a error message:

Login failed for user 'BRISTOL-1\Guest'

after some researching, I did the following:

I open the sql server management studio express(also free), go to 'Database' ->'EvoHealthSQLex'-> 'properties' ->'permissions'

I add a user called 'BRISTOL-1\Guest' and granted it with 'connect' and 'control' permissions, however, I still got the same error.

In 'Database' ->'EvoHealthSQLex'-> 'properties' ->'general'

I saw the owner part said 'BRISTOL-1\Ray'

the full computer name of machine running sqlexpress is 'bristol-1', and I login as user 'ray', I guess that's why the database owner is 'BRISTOL-1\Ray', on the other computer (which run the application), the full computer name is 'ray' and I login as 'rui', I don't know whether those info is useful.

Questions:

I wonder why the same error occured after I created the user 'BRISTOL-1\Guest', and I don't know why I am recoginzed as 'BRISTOL-1\Guest' on the other machine?

I wonder if I created a user, for example,say 'BRISTOL-1\ross' (does it have to use the same prefix BRISTOL-1?), when I use the program in other computer how do I specified myself as user 'BRISTOL-1\ross' or whatever ? does it have anything to do with connectionstring? as in the connection string, user id is 'sa', i don't think I should change the connectionstring to:

server= 192.168.0.3.\SQLEXPRESS,1921; user id='BRISTOL-1\ross' ; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True - if this case is right, what the pw suppose to be?

I know those suppose to be very simple for you experts, I am just very new to sqlserver, and I can't find any related articles online...(is it because the solution is too obvious?)

I don't have anyone around me can help me so I post it here, any help will be very appreciate!!!

Ps: I just turn on the 'Guest' account in 'User Account' of 'Control Panel', but same error.

"user id" and "password" is used for SQL Authentication, while Integrated Security" is used for windows authentication. You needs to only use one of them. If both is specified, the result is not determinstics. I know some driver ignores SQL authentication in this case.

How to use configure these two authentication is a long story, I am sure you can find plenty of articles on the web or use bookonline.

In you case, if you use sa password, make sure you don't specify Integrated Security. If you want to use windows authentication, make sure that you client account has access privilieges to the SQL Server account.

|||

Hi, thanks so much for your rapid reply, I realized that I asked many stupid questions...

I just make a small progress now, after I downloaded BOL (just now), I fint out I need to greate a login rather than just create a user! :

the whole command:

CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> } <sources> ::= WINDOWS [ WITH <windows_options> [ ,... ] ] | CERTIFICATE certname | ASYMMETRIC KEY asym_key_name <option_list1> ::= PASSWORD = 'password' [ HASHED ] [ MUST_CHANGE ] [ , <option_list2> [ ,... ] ] <option_list2> ::= SID = sid | DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language | CHECK_EXPIRATION = { ON | OFF} | CHECK_POLICY = { ON | OFF} | CREDENTIAL = credential_name <windows_options> ::= DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = languageIn my case I did:CREATE LOGIN [BRISTOL-1\Guest] FROM WINDOWS; GOthen it works, I wonder is it anyother computer try to connect with the same sqlexpress server will be BRISTOL-1\Guest ?now I need to figure out how to connect from outside rather than local network, any suggestions :)thanks a lot Nan Tu!

Help: Table Lock Confirmation

I need confirmation from you SQL Server experts out there. Please let me know if the following works. Thanks!

This stored procedure gets a value and increments by 1, but while it does this, I want to lock the table so no other processes can read the same value between the UPDATE and SELECT (of course, this may only happen in a fraction of a second, but I anticipate that we will have thousands of concurrent users). I need to manually increment this column because an identity column is not appropriate in this case.

BEGIN TRANSACTION

UPDATE forum WITH (TABLOCKX)
SET forum_last_used_msg_id = forum_last_used_msg_id + 1
WHERE forum_id = @.forum_id

SELECT @.new_id = forum_last_used_msg_id
FROM forum
WHERE forum_id = @.forum_id

COMMIT TRANSACTIONI would go for a different solution; a transaction is used to be able to rollback data in case of a failure, and may help to solve a concurrent-user issue. But not like this. I would think there could be another update between the update and the select..|||Originally posted by Kaiowas
I would go for a different solution; a transaction is used to be able to rollback data in case of a failure, and may help to solve a concurrent-user issue. But not like this. I would think there could be another update between the update and the select..

Thanks for your response, but I am using the transaction to lock the table initiated by the UPDATE forum WITH (TABLOCKX). I understand this table should stay locked until the COMMIT TRANS.|||The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Thank you very much Pat!|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Thank you very much Pat!|||Originally posted by stevenpath
Thanks for your response, but I am using the transaction to lock the table initiated by the UPDATE forum WITH (TABLOCKX). I understand this table should stay locked until the COMMIT TRANS.

You're right! Sorry I missed that, but why not stick with it?|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Pat why the extra BEGIN TRAN?

Is that a type o?

Geez what a way to hit 2000|||Originally posted by Brett Kaiser
Pat why the extra BEGIN TRAN? It is a nifty little trick that I dreamed up one night in a haze...

When using nested stored procedures, things got really, really complicated if the transaction level got puckered up, and things just went to heck in a handcart. I had to find some way that I could rollback without blowing the whole tamale out of the water. Necessity being a mother (as you so recently pointed out), I came up with a deviant solution.

The code is two transactions when life is good, with an empty one being rolled back, which has no impact on the database. When life is hard, it is only one transaction, which is also rolled back so it has no impact on the transaction count either...

The net result is that it is an odd bit of code, but it works nicely in all of the peculiar ways that we need code to function. Someday I'll have to post a little diatribe about the bad old days, when Sybase wanted considerably more dollars for each replicated database (per year) than they wanted for the license for the database! That drove us to some peculiar work arounds, this being one of them.

-PatP|||OK, I see it now...but why do it that way?

Why not handle it like the code in this thread?

http://www.dbforums.com/showthread.php?threadid=988019&perpage=15&pagenumber=1

What's the difference...you're using Goto's anyway...|||As I said in the previous post, this was a side effect of the cost of using early (like 1994) versions of SQL replication. Our work around required each procedure have one entry point, and one exit point from a code execution perspective so we could effectively "bottle" the procedure with calls to other procedures that our work around required.

It wasn't necessarily pretty, but it saved us more than a million dollars per year in licensing fees.

-PatP