Mars Connection Problem

I am trying to make a connection to an SQL database held on myserver I am able to connect through a machine data source using access using the credentials as below the attempt fails at con.open with following error:

System.Runtime.InteropServices.COMException was caught
ErrorCode=-2147467259
Message="Named Pipes Provider: Could not open a connection to SQL Server [53]. "
Source="Microsoft SQL Native Client"
StackTrace:
at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)
at Client_Access.JobRequest.AddJob_Click(Object sender, EventArgs e) in C:\Documents and Settings\Robert\My Documents\Visual Studio 2005\Projects\ Client Access\ Client Access\JobRequest.vb:line 392

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sPassword As String, sUserID As String
sPassword = "abcde"
sUserID = "cClient"
Try
con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=(myserver);" _
& "Database=transportRecs;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "UID=" & sUserID & ";" _
& "PWD=" & sPassword & ";" _
& "MARS Connection=True;"
Dim mySQL As String

mySQL = "SELECT * FROM dbo_jobitem " '& _
'" WHERE [Custid] ='" & strTag & "'"

con.Open()

rst = New ADODB.Recordset
With rst
.ActiveConnection = con
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.CursorType = ADODB.CursorTypeEnum.adOpenStatic
.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
.Open(mySQL)
.MoveLast()
.MoveFirst()
.MoveLast()
.MoveFirst()
Debug.Print(.RecordCount)
End With

Catch ex As Exception
MsgBox(ex.ToString)
Finally
If (con.State = ConnectionState.Open) Then con.Close()
End Try

Can anyone help.

Regards,
Joe

The connection string is pooched. Use a UDL to write the connection string for you.

1. Open notepad

2. Save the blank file as test.udl to your desktop

3. Open the UDL and create and test your connection string.

4. Close and open in notepad

5. Copy the generated connection string

Adamus

|||

Hi Adamus Turner

I have follwed the instructions supplied. The connection is good using the connection string below:

con.ConnectionString = "Provider=MSDASQL.1;Password=abcde;Persist Security Info=True;User ID=cClient;Mode=ReadWrite;Extended Properties= DSN=TransportDB_Comp1;Description= ;UID= cClient;PWD=abcde;APP=Microsoft? Windows? Operating System;WSID=MIDLAPTOP2; transportRecs;Network=DBMSSOCN" & _

"MARS Connection=True;"

Dim mySQL As String

mySQL = "SELECT dwjobitemid FROM dbo_jobitem"

'";" '" WHERE [Custid] ='" & strTag & "'"

con.Open()

rst = New ADODB.Recordset

With rst

.ActiveConnection = con

.CursorLocation = ADODB.CursorLocationEnum.adUseClient

.CursorType = ADODB.CursorTypeEnum.adOpenStatic

.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic

.Open(mySQL)

.MoveLast()

.MoveFirst()

.MoveLast()

.MoveFirst()

MsgBox(.RecordCount)

End With

When the connection attemps to open a record set (.open(mySQL) an error is called, Invalid object name? I know the table exists?

Regards

Joe

|||

Is the table name dbo_jobitem or dbo.jobitem?

It's a strange convention to use an underscore.

Adamus

|||

Also, do not post logins and passwords to databases.

Moderator, please remove login and password from connection string.

Adamus

|||

Hi Adamus

I have managed to sort out the problem, the issue was not with the connection string after I had used your advice the connection was good. The database tables when viewed on the sql server all had names starting “dbo_” I had attempted to look at the database schema.tables using “select * from information_schema.tables” out of vbnet but could not work out how you returned recordset!table_name in vb.net> I did managed to achieve this using vba. The table names returned were all shown minus the “dbo_” prefix I altered my sql to match and bingo.

Any user names, passwords etc used in my examples are all fictitious. And have been used for illustration only. Many thanks for your assistance with my postings

Regards

Joe

mapping Windows credentials to access linked server

Hi all,
I have linked SQL Server "SRV2" to SQL Server "SRV1" through
sp_addlinkedserver.
In my scenario, Windows user "U1" has access to "SRV1" while Windows
user "U2" has access to "SRV2".
Whenever I access "SRV1" as "U1" and execute a distributed query which
involves "SRV2", I would like "U1" to be mapped to "U2" for accessing
"SRV2".
Does anyone know whether this is possible and how?
I know that I can pass-through "U1" credentials to "SRV2" with
delegation and the default mapping, or map "U1" to a SQL User "sqlU2"
that can access "SRV2".
However what I would like to do is to map Windows user "U1" to Windows
user "U2".
I am using SQL Server 2005 which comes with Visual Studio Beta2.
Thanks in advance for any help,
-GianlucaHi
As fas as I know you will have to create a new login in "SRV2". with the
same permissions.
"Gianluca Torta" <giatorta@.hotmail.com> wrote in message
news:1121206171.989233.19240@.f14g2000cwb.googlegroups.com...
> Hi all,
> I have linked SQL Server "SRV2" to SQL Server "SRV1" through
> sp_addlinkedserver.
> In my scenario, Windows user "U1" has access to "SRV1" while Windows
> user "U2" has access to "SRV2".
> Whenever I access "SRV1" as "U1" and execute a distributed query which
> involves "SRV2", I would like "U1" to be mapped to "U2" for accessing
> "SRV2".
> Does anyone know whether this is possible and how?
> I know that I can pass-through "U1" credentials to "SRV2" with
> delegation and the default mapping, or map "U1" to a SQL User "sqlU2"
> that can access "SRV2".
> However what I would like to do is to map Windows user "U1" to Windows
> user "U2".
> I am using SQL Server 2005 which comes with Visual Studio Beta2.
> Thanks in advance for any help,
> -Gianluca
>

Mapping Package Variables to a SQL Query in an OLEDB Source Component

Learning how to use SSIS...

I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)

I wanted to add additional clauses to the where clause.

The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.

The package variable is called [User::Date_BeginningYesterday]

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.

http://msdn2.microsoft.com/en-us/library/ms139904.aspx

The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.

...cordell...

Not sure what your problem is; but the solution is to create a variable to hold your query, let's say [User::SQLStatement] and use as value your query. Then set EvaluateAsExpression property of the variable to true. In the expression property, create an expression that will be evaluate at run time where you concatenate your query with the [User::Date_BeginningYesterday] variable. Back in your OLE DB Component you need to choose 'SQL Statement from variable' and then choose [User::SQLStatement] from the list.

Notice that you need to cast the value of [User::Date_BeginningYesterday] to string in the expression builder before concatenating its value to the sql statement.

Rafael Salas

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

|||

The question that I have is: Can one embed a package variable into a sql statement while selecting "SQL Statement" from the data access mode. If so how would would go about that?

...cordell...

|||

The short answer is no. you cannot reference a SSIS variable directly in your sql statement. You need to use '?' and then use the parameter mapping in your OLE DB source OR, to concatenate it within a second varibale as I described in the previous post.

Rafael Salas

sql

Mapping Column Headers from Source to Rows in a spreadsheet

Hello,

I am trying to do the following:

I have been given an MS Access Database that has a table with columns

I have to create a spreadsheet that will have the data stored in the column header as a row (essentially we are creating a spreadsheet that records all of the different columns in all of the different tables in the MS Access DB).

Any suggestions?

Where the problem is?

Mapping Active Directory Group members to SQL Server Roles

My question is I have a SQL Server running on Web Server which is a member of a 2000 Active Directory, I only grant access to the database via Global Groups from the Active Directory. When I log onto the database via Windows Authentication the actual user shows up in the master.dbo.sysprocesses table, I can tell what database that process is going to but not how that user is being translated to the Global Group that was actually given access. I need the actual database user name which is the Global Group name that had permissions granted via user defined database roles so that I can do some pre-processing in an ASP.NET application so that I know what parts of a form are updatable or not.No, a Server Role container for users with a specific set of permission. The group has to be mapped to a principal in SQL Server to be included in a role.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.desql

many to many relationship inserts

Hi all

I have a SQL Server 2000 database that I converted from an access database. The interface is still existing in Access...

Basically I have 2 tables in a many to many relationship - Member and Nominators of members. But because the same nominator can nominate many members and a member can be nominated by multiple nominators, i've added a linking table call MemberNomLink that has the MemberId and the NominatorId.

So in my access interface I have a parent form for the Member, and a subform of all the nominators for that member. When i enter, if the nominator is brand new (never nominated before) then I'd just do an insert - which would:

a) insert into the nominator form (giving a new IDENTITY Primary key - Nominator Id) and
b) would insert into the MemberNomLink table - the new NominatorId and the parent form's Member Id.

When I was running this in access, this worked fine by simply using a left join query on my subform between Nominators and MemberNomLink. When I inserted into this query, access seemed to automatically insert nominator, then create the MemberNomLink fine populating it with the new id.

When I'm attached to the SQL Server 2000 db though, it will insert into the nominator table, however it won't insert into the MemberNomLink.

I don't think this is an unusual circumstance but if anyone has done anything like this, I'd like to know if I'm doing something obviously wrong...

thanks in advance
scott

Quote:

Originally Posted by smook

Hi all

I have a SQL Server 2000 database that I converted from an access database. The interface is still existing in Access...

Basically I have 2 tables in a many to many relationship - Member and Nominators of members. But because the same nominator can nominate many members and a member can be nominated by multiple nominators, i've added a linking table call MemberNomLink that has the MemberId and the NominatorId.

So in my access interface I have a parent form for the Member, and a subform of all the nominators for that member. When i enter, if the nominator is brand new (never nominated before) then I'd just do an insert - which would:

a) insert into the nominator form (giving a new IDENTITY Primary key - Nominator Id) and
b) would insert into the MemberNomLink table - the new NominatorId and the parent form's Member Id.

When I was running this in access, this worked fine by simply using a left join query on my subform between Nominators and MemberNomLink. When I inserted into this query, access seemed to automatically insert nominator, then create the MemberNomLink fine populating it with the new id.

When I'm attached to the SQL Server 2000 db though, it will insert into the nominator table, however it won't insert into the MemberNomLink.

I don't think this is an unusual circumstance but if anyone has done anything like this, I'd like to know if I'm doing something obviously wrong...

thanks in advance
scott

You can achieve the same functionality using a 'trigger' in SQL although I have to say the after_update event of the appropriate NominatorID field in your interface could be used primarily to determine if apropriate values exists in the MemberNomLink table and if not to then insert them immediately following an insertion of a new nominator. You would then simply requery to refresh the interface.

What format frontend are you using MDB or ADP? Have a good look at the ADP format in Access if you are upsizing for the first time. You can choose either of these of course whichever you arecomfortable with, however the ADP format handily exposes views and stored procedures to you in the interface and connects directly to SQL server using UDL Universal data link ie it doesnt use ODBC connectivity. If you need to create tables locally on the client then stick with the MDB format (mdb is back in favour for Access 2007 too)

Have a go at the after update event based on my simple response here and if you get stuck then post your table structure ie 'exact' table and field names and I'll replicate the tables and necessary SQL on my server, create MDB and an ADP solutions to show you the relevant differences and then mail you the files which should work on your system provided the db name is the same too. Don't post your email address though. (if this progresses to that) you would have to PM me with it and I,ll mail you

Regards

Jim :)

Many To Many

hi im implementing a database in ms access to migrate it later to SQL, its a project tracking/ employee tracking and im having trouble with some of the tables... the relationships are as follow

Employee : M
Employee_ID
Name
Phone
Supevirsor_Name
Supervisor_Email

Project : M
Project_ID
Project_ Name
Description
Project_Added

EmployeeProject
Employee_ID
Project_ID
AssignedBy

i made this third table called EmployeeProjects for the relationship, but when i go to collect the data everything is ballistic, when i go to capture employees in the employee table everything is fine, i go to the projects and everything is fine there is a "+" in the projects its lists every single employee that i captured in employees in the same project i go to the next record and the same deal, there is a possibility that this could be that many employees can be in a project and also working in another project, what is wrong with it? can anybody help me?I'd say nothing...

Why don't you post the DDL for the tables (CREATE TABLE myTable99(Col1 int, ect)

Some sample Data (INSERT INTO myTable99(Collist) SELECT Data UNION ALL SELECT ect)

The DML You've attempted (SELECT Col1, Col2 FROM myT INNER JOIN myt2, ect)

And the results you'd expect...

I'd say you'd get an answer in 15 minutes of that post...|||The designn looks OK. Id' change your Employ3ee table a little though:

Employee_ID
LastName
FirstName
MiddleName
FullName (calculated...not sure if access has that)
PhoneArea
PhonePrefix
PhoneSuffix
Phone (calculated...same as FullName)
EMail
Supervisor_ID (NULL or same if it's a supervisor)

The reason you may have the situation you describe is only because of the contents of EmployeeProject. Do this:

select Project_ID, count(*) from EmployeeProject group by Project_ID
union all
select 0, count(*) from Employee

This will get you started on finding out how many employees are assigned to each project and what's the total number of employees assigned vs. the total number of employees in the organization.|||I don't have any SQL procedures that can parse that last sentence of yours, so I'm not exactly sure what the problem is.

But I have to ask why you are developing this in MS Access if you are planning to upsize it to SQL Server anyway. Have you considered creating it in SQL Server and using an Access Data Project (.adp file) front-end? You'd get all the benefits of Access forms, reports, and modules for the interface, and you wouldn't have to upsize it later. Plus SQL Server's security is much better and easier to implement than MS Access security.|||the reason for me doing it in access first is my boss maily.... hes a manufacturing engeneer and he knows nothing about servers and all that... he asked me to do it first kinda like a prototype for collecting the data... i said prior to him that i could build it in SQL save us a hole lot of time.. but he wouldnt budge... (putz)... any way i explained that particular situation ( me using access as a front end) but he was like no no Y complicate it so much... just doit in access and then we will see what to leave or what not... any ways thanx for your help...|||i actually did that... in fact i did get the results that i wanted... but also... i dropped the tables and created them again... exactly and got them as i wanted... your solution was indeed good just got to it now... if only i'd gotten to it sooner... thanx for your help u really did help me|||Exactly what i was doing... but also i don't know what in the heck was wrong with the tables... so i'd dropped'm and built them again got what i wanted... what u posted... was what i did and it worked... thanx|||Your boss is a putz. Oh wait, you already said that. Well tell him I said so too.

Asking him why he bothers hiring competent people if he isn't going to trust their expert judgement. Duh.

many mistakes, one big mess

Hi there. I'm learning about MSDE, .adp and how to handle the files in between.
I have remote access to the server where the Database is, so this morning
I've detached the database, replace it with a newer version and re-attached...
I forgot to change the attribute and I re-attached as "read only". First big
mistake. Somebody tried to update a record and the project was "hanging" so
they press Ctr+Alt+Del and stop the Access project.
The database shows there is still one user connected
Trying to fix this, I've stopped the server... thinking this will disconnect
the user... nope.
Please help me to fix this mess and learn few big lessons (like never attach
a read only db)
How can I connect the Instance again? The service manager is not letting me
re-connect, everything is "disabled"
How can I disconnect the user so I can detach the read only database and
re-attache the good one?
Any help would be more than appreciated,
gaba
hi,
gaba wrote:
> Hi there. I'm learning about MSDE, .adp and how to handle the files
> in between. I have remote access to the server where the Database is,
> so this morning
> I've detached the database, replace it with a newer version and
> re-attached... I forgot to change the attribute and I re-attached as
> "read only". First big mistake. Somebody tried to update a record and
> the project was "hanging" so they press Ctr+Alt+Del and stop the
> Access project.
> The database shows there is still one user connected
> Trying to fix this, I've stopped the server... thinking this will
> disconnect the user... nope.
> Please help me to fix this mess and learn few big lessons (like never
> attach
> a read only db)
> How can I connect the Instance again? The service manager is not
> letting me re-connect, everything is "disabled"
> How can I disconnect the user so I can detach the read only database
> and re-attache the good one?
> Any help would be more than appreciated,
if your MSDE instance is running and you want to see/kill a certain user,
you can run, on oSql.exe the
EXEC sp_who
system stored procedure to identify who you want to eventually kill, and
then exeute the
KILL nr#
statement, where nr# states for the actual related spid of the "user" you
want to kill..
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Thanks for your response. The MSDE instance is not running, acually is
trying to stop. Under SQL Server Service Manager, SQL server for this
instance says "stopping". It shows it is still connected to one user. I've
tried to delete the db, no luck.
Please advise
gaba
"Andrea Montanari" wrote:

> hi,
> gaba wrote:
> if your MSDE instance is running and you want to see/kill a certain user,
> you can run, on oSql.exe the
> EXEC sp_who
> system stored procedure to identify who you want to eventually kill, and
> then exeute the
> KILL nr#
> statement, where nr# states for the actual related spid of the "user" you
> want to kill..
> --
> Andrea Montanari
> http://www.asql.biz/DbaMgr.shtm
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Andrea,
Is there any solution to this mess or should I uninstalled the instance and
re-install it? right now the server manager is greyed out and I can't do
anything. I'm using remote access to the server and have to let everybody
know if I'm going to re-start the server.
Thanks.
gaba
"Andrea Montanari" wrote:

> hi,
> gaba wrote:
> if your MSDE instance is running and you want to see/kill a certain user,
> you can run, on oSql.exe the
> EXEC sp_who
> system stored procedure to identify who you want to eventually kill, and
> then exeute the
> KILL nr#
> statement, where nr# states for the actual related spid of the "user" you
> want to kill..
> --
> Andrea Montanari
> http://www.asql.biz/DbaMgr.shtm
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
gaba wrote:
> Andrea,
> Is there any solution to this mess or should I uninstalled the
> instance and re-install it? right now the server manager is greyed
> out and I can't do anything. I'm using remote access to the server
> and have to let everybody know if I'm going to re-start the server.
> Thanks.
actually I'm trying to figure out what's happening...
you have the server "stopping" but not able to complete the process "becouse
some user's still connected"...
actually this should not prevent the SQL Server service to stop at all...
ok.. before uninstalling...
try managing the service applet to start MSDE manually and not automatic..
shut down the machine.. possibly "softly", and then physically if it does
not work :D
once restarted MSDE should not be running.. delete the db files you are
interested with...
start MSDE manually... the database should be marked as corrupted or the
like..
drop the database via
DROP DATABASE xxxx
statement... you should be then ok with that.. repristinate the automatic
startup of the service...
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
please forgive my ignarance,
what do you mean by "shut down the machine possibly "softly"?
Thanks so much for your help
gaba
"Andrea Montanari" wrote:

> hi,
> gaba wrote:
> actually I'm trying to figure out what's happening...
> you have the server "stopping" but not able to complete the process "becouse
> some user's still connected"...
> actually this should not prevent the SQL Server service to stop at all...
> ok.. before uninstalling...
> try managing the service applet to start MSDE manually and not automatic..
> shut down the machine.. possibly "softly", and then physically if it does
> not work :D
> once restarted MSDE should not be running.. delete the db files you are
> interested with...
> start MSDE manually... the database should be marked as corrupted or the
> like..
> drop the database via
> DROP DATABASE xxxx
> statement... you should be then ok with that.. repristinate the automatic
> startup of the service...
> --
> Andrea Montanari
> http://www.asql.biz/DbaMgr.shtm
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
gaba wrote:
> Andrea,
> please forgive my ignarance,
> what do you mean by "shut down the machine possibly "softly"?
> Thanks so much for your help
I just meant using the "standard" shut down Windows features and not
pressing the pc's interrupt :D:D
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
THANK YOU. I re-started the server and drop the database, attach the good
one and we are back in business.
I've recreated my mistakes... oh Boy
I work on my cpu, test and then burn a copy of the project and database to a
cd, paste into server.
1) Didn't stop the SQL Server on my computer BEFORE copying files: 1 user
still connected
2) When pasting files from CD, didn't make them ARCHIVES, they were read only
3) Instead of disconnecting the user (Kill nr#) and drop corrupted db, I've
tried to stop the SQL Server.
Thanks so much for you help. I know one way of learning and to get
experience is by making mistakes and fixing them... but Oh Boy I really had a
BAD day: I've made one after the other. Maybe somebody else can learn too
from them.
I want to learn SQL Server and do it right, can you recomend any material or
book for Beginners? Thanks
gaba
|||hi,
gaba wrote:
> 1) Didn't stop the SQL Server on my computer BEFORE copying files: 1
> user still connected
another lesson, if I can help... please always DETACH dbs before copying
them ... detaching a database for later re-attach is the only proper and
supported way for doing that kind of operations, and stopping the server is
not needed...
ok. both way, detaching and stopping the server, should provide a proper db
closing... but stay the documented way :D:D

> I want to learn SQL Server and do it right, can you recomend any
> material or book for Beginners? Thanks
I'd recommend the SQL bible, "Inside SQL Server 2000",
http://www.amazon.com/exec/obidos/tg...books&n=507846 ,
not about Transact-SQL but the actual engines and related architecture...,
by Notre Dame SQL Server Kalen Delaney.. :D
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks a lot for your help and advise.
I'm using the DbaMGR2k, actually I'm reading the help files first ;)
Thanks for a great product!
I'll get the book tomorrow.
I'm sure we'll "talk" again,
gaba
sql

Managing Concurrency

I want to centralize my previous standalone application. Previous application was using VB.NET and Access XP. Now I want to keep a centralized database (SQL Server 2005) and VB.NET 2005. At this point of time thousands of concurrent users will connect to the database at the same time.

In my application, when a ticket is being issued to a tourist, an SQL query finds the max(ticketno) for the current month from the main table and reserves this number for the current ticket. When the operator has finished entering information and clicks SAVE button, the record is saved to the main table with this ticket no. Previously there was no issue since the database was standalone.

I want to know how to block the new ticket no so that other concurrent users are not using the same number for saving a record. How to better tune the database for thousands of concurrent users at the same time? I also want that the other user must not get an error message when he attempts to save a record. It should be automatically handled by the database.

I would NOT try to lock a process that first gets a ticket number, then waits until someone clicks the 'SAVE' button, and then updates the master table and then unlocks the process.

This will not scale and will be a MAJOR headache.

I recommend that you use an IDENTITY field for the 'ticketno', and let the 'system' take care of the updating/incrementation.

|||Can you please illustrate?|||

Arnie's suggestion is a good one...there doesn't seem to be a strong justification to pull in the complexity of some kind of key management system and there doesn't appear to be a need for the client application to have any knowledge of the ticketno value prior to saving the information. If you need to maintain referential integrity between tables, you can use the @.@.IDENTITY system function to get the last identity value that was entered in the master table. For example:

CREATE TABLE Tickets (TicketID INT IDENTITY, PurchaseDate datetime, CustomerID int) <NOTE: I'm leaving out the key relationship to the identity column on a customer table>

DECLARE @.TicketID AS int

INSERT INTO Tickets (PurchaseDate, CustomerID) VALUES (GETDATE(), @.SomeValue)

SET @.TicketID = @.@.IDENTITY

INSERT INTO Table2 ( @.SomeInfo, @.SomeInfo2, @.TicketID)

|||

Which part, how to define a 'TicketNo' column in a table as INDENTITY?

Look in Books Online, Topics: IDENTITY, CREATE TABLE

OR, the problems with scaleability?

It if is the later, consider that you have a choke point that every user must wait in line to access. And if some user takes a bit more time before clicking the [SAVE] button, well everyone else just has to wait because the number incrementing process is locked in the scenario you described.

|||

I recommend using SCOPE_IDENTITY instead of @.@.IDENTITY.

Under some situations, @.@.IDENTITY may provide inaccurate data.

|||Ticket No in our case is not a sequence number but in this format:

000123-0507-0101

The first part is the max(TicketNo) of current month. The second part contains month and year and the third part contains the code of the place. After each month end, the ticket no starts with 1 again.

Here I also want to know whether taking a string (like above) a primary key affects performance as opposed to a numeric primary key.