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

Hello .
I am using SQL Server 2000 in order to create a multi user program that accesses data.
The problem is that multiple users will update and select data at the same time at the same table.

Is there a way to avoid deadlocks ?
I heard about two ways: using a temporary table to store data and then write the data only when the user finished the update.
and the other is using xml to write the database to a xml file that is stored locally. do the updates on the file and then after completion insert the xml file into the database.

does anybody know much about these ways? do you know where i can find code for this ?

is there a better way?

thanks !
and happy new year !yeah, according to the textbook of database, there should be 2 other ways instead of write the information to other place from the database.

1. lock all what u will lock in the transaction when u begin the transaction.

2. all kinds of resources should be locked at the same order.

hehe.

happy new year.|||Those two ways you described will help but truely it all depends on how you write your sql script. Are all the updates and deletes using cursors? Do you have begin trans and commit on all your transactions? There is no one way you can completely avoid deadlocks, but there are ways to manage it. The best way is to let us know what sql script in your environment that creates deadlock so we can better help with your situations.|||Are we talking about deadlocks or blocking here? It surely sounds that the poster is concerned about object availability when more than one connection attempts to access its data.|||There is no silver bullet to avoid deadlocking other than fully analysing the transactions you intend to commit. Using a temporary table may work, as long as your transaction does not depend upon data previously read from the permanent tables remaining unchanged during the accretion of the temporary data. XML in this regard is a complete red herring.

Essentially you control locking policy using the SET TRANSACTION ISOLATION LEVEL command and wrapping all calls with an outer transaction until all components of the unit of work are available for committing.

You need to consider whether the integrity of data reads is essential to the integrity of the intended data writes. If youre booking seats on an aircraft you must ensure no other user books your particular seat between you reading that its free and you writing that youre booking it. To make reads fully transactional with writes you use isolation level SERIALIZABLE everything you read is locked against being updated or inserted against for the duration of the transaction. Isolation level REPEATABLE READ ensures that data you have read cannot be updated by another user, but allows background inserts.

If it is not possible to extend the duration of the transaction between critical reads (i.e. you can keep your option on the flight seat open for twenty minutes) you must implement soft locking of the seat row in the permanent tables using something like a timestamp or a flag.

Isolation level READ COMMITTED ensures you can only read data that has had full transactional commit to the database. You use this read mode to ensure you never read over another users half finished work, but you have no locks of the data you have read for the purposes of subsequent writes. READ UNCOMMITTED means you read through any locks applied during any other users writes. You cannot write through any other users locks.

Particularly useful in ensuring transactional integrity is SET XACT_ABORT ON, which will cause any error anywhere in your SQL to Rollback the entire transaction.

Also be aware that if a deadlock does occur SQL will terminate one or other transaction, at random, unless you explicitly set a deadlock priority on the thread whose death you would prefer to occur.|||Yeah, use strored procedures and stroe the data locally...

don't do dynamic sql

don't open recordsets...

Read data store

manipulate data in the app

when an action is to occur...update or delete, check the records timestamp to see if someone else alread modified the record and act accordingly

if ok, exec (through a sproc) your transaction...

keep'em short

Help: JDBC 1.22 and SQL 2000

HI, all

I need to write a program, which using VM 1.1.8.

So I need to find the JDBC driver version 1.22, Can anyone help me?

Please Reply, a link for download jdbc 1.22 or any other solutions .

Thank you!

You can take a look at the following links for Microsoft SQL Server 2005 JDBC Driver (recommended)

http://msdn.microsoft.com/data/ref/jdbc/

http://www.microsoft.com/downloads/details.aspx?FamilyId=6D483869-816A-44CB-9787-A866235EFC7C&displaylang=en

For SQL Server 2000 Driver for JDBC Service Pack 1

http://www.microsoft.com/downloads/details.aspx?FamilyID=4F8F2F01-1ED7-4C4D-8F7B-3D47969E66AE&displaylang=en

HELP: Crystal Report 8 with Acces 2003

Am I missing something?

I am trying to run a crystal 8 report, wich is feeded from a access 2003 database, this is done trough a vb 5 - program. It works all right on my computer (win 98) where Crystal Report 8 is installed.

But it doesn't work on my Win XP - computer (Here i don't want to install Crystal Report 8, because it has to run on other machines too), i am trying to install the needed files to make this work. So far I installed the following files:

[Files]
File1=1,,CRVIEWER.DL_,CRVIEWER.DLL,$(WinSysPath),$(DLLSelfRegister),$(Shared),1/28/2000 16:19:24,509328,8.0.0.371,"","",""
File2=1,,CRAXDRT.DL_,CRAXDRT.DLL,$(WinSysPath),$(DLLSelfRegister),$(Shared),1/28/2000 15:16:48,5550080,8.0.0.371,"","",""
File3=1,,IMPLODE.DL_,IMPLODE.DLL,$(WinSysPath),,$(Shared),11/18/1996 1:00:00,18944,1.0.0.1,"","",""
File4=1,,CRPAIG80.DL_,CRPAIG80.DLL,$(WinSysPath),,$(Shared),1/11/2000 7:09:56,618496,8.0.0.11,"","",""
File5=1,,sscsdk80.dl_,sscsdk80.dll,$(WinSysPath),,$(Shared),12/10/1999 15:24:30,974848,2.2.0.2,"","",""
File6=1,,P2SMON.DL_,P2SMON.DLL,$(WinSysPath),,$(Shared),12/15/1999 7:17:52,147456,8.0.0.15,"","",""

The strange thing is that it works allright when i use a report based , on a MS access 97 database. But when I use a report, based on a MS Access 2003 database, i get an error: "Unrecognized database format" , also i'm prompted for a password.

Does anybody know this problem?you need to redesign the report with Access 2003|||Dear Madhi

thanks for your reply, but i don't know what you exactly mean.

the report is designed with Crystal Report 8, in it there is a database connection with an MS Access 2003 mdb. As I said , this works on my win89 machine (with full crystal report on it), but not on my WinXp machine (with only the runtime files from crystal report on it.

You say that I have to redesign my report, but i'm sorry to say that I can't quit follow you there, could you be more specific?

thanks, paul|||Did you use Setup to install the application there?
If so, you need to add all the required dlls|||I think that i did that at first, but to be absolutely shure, i wil do that again.|||Mahdi

That was the problem, i didn't install all the required dll's. Now i have to sort out, wich dll was missing precisely . I wil post it here, as soon as i find out,

thanks