Locked out again?

I have SQL Server 2005 Developer edition installed. I use Windows
Authentication and the user is an admin on this box. For whatever
reason, I was recently locked out of SQL Server. So I uninstalled and
reinstalled then I could get back in. Then once again, I became locked
out. Same procedure and I can get back in. I downloaded and installed
SP1 for SQL Server 2005 last night. Now I'm locked out again. "sa"
never works with a blank or any other password.
Any ideas why this keeps happening and how I can gain access?
Thanks,
BrettI'm running this on Vista. I always turn off account access control.
After installing SP1 for SQL Server and rebooting, access control was
reenabled. This prevented me from logging into SQL Server. I've once
again turned access control. Now I can log in.
Brett|||Make sure that you have configured it for mixed authentication if you want
to use sa. Also, make sure that if you are using Windows authentication
with SSMS that you have that option selected when you login.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"brett" <account@.cygen.com> wrote in message
news:1167499761.263497.179960@.h40g2000cwb.googlegroups.com...
I have SQL Server 2005 Developer edition installed. I use Windows
Authentication and the user is an admin on this box. For whatever
reason, I was recently locked out of SQL Server. So I uninstalled and
reinstalled then I could get back in. Then once again, I became locked
out. Same procedure and I can get back in. I downloaded and installed
SP1 for SQL Server 2005 last night. Now I'm locked out again. "sa"
never works with a blank or any other password.
Any ideas why this keeps happening and how I can gain access?
Thanks,
Brett|||Well did you give the sa acoount a password?
You sure that youre windows account is a member of the local administrator
group?
Greetz,
HDD
"brett" wrote:

> I have SQL Server 2005 Developer edition installed. I use Windows
> Authentication and the user is an admin on this box. For whatever
> reason, I was recently locked out of SQL Server. So I uninstalled and
> reinstalled then I could get back in. Then once again, I became locked
> out. Same procedure and I can get back in. I downloaded and installed
> SP1 for SQL Server 2005 last night. Now I'm locked out again. "sa"
> never works with a blank or any other password.
> Any ideas why this keeps happening and how I can gain access?
> Thanks,
> Brett
>|||Hate_orphaned_users wrote:
> Well did you give the sa acoount a password?
This was never an option during set.

> You sure that youre windows account is a member of the local administrato
r
> group?
Yep|||Tom Moreau wrote:
> Make sure that you have configured it for mixed authentication if you want
> to use sa.
Can it be set to mixed after installation?
Thanks,
Brett|||In Vista by default you are not an admin and as such not part of the
sysadmins group. You need to make yourself an admin, create an account for
yourself in SQL Server and then you can log in without being an admin. This
wasn't an issue with XP because you were generally always an admin on the
box when you logged in. Vista changed this default because running as an
admin made viruses much more nasty. I think that SP2 is supposed to create
this user for you automatically but I don't know if that part of it works in
the current CTP.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"brett" <account@.cygen.com> wrote in message
news:1167501829.564730.89250@.n51g2000cwc.googlegroups.com...
> I'm running this on Vista. I always turn off account access control.
> After installing SP1 for SQL Server and rebooting, access control was
> reenabled. This prevented me from logging into SQL Server. I've once
> again turned access control. Now I can log in.
> Brett
>|||Yes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"brett" <account@.cygen.com> wrote in message
news:1167505428.307381.19420@.i12g2000cwa.googlegroups.com...
Tom Moreau wrote:
> Make sure that you have configured it for mixed authentication if you want
> to use sa.
Can it be set to mixed after installation?
Thanks,
Brett

Lock/block retained after commit

I am using SQL server 2000 with Uniface (4GL).

I am running a process which commits every 30 seconds or so on one
machine and another user is trying to perform a small update at the
same time on the one of the tables whose data is modified by the first
process.

I accept that the two second process may get blocked at times by the
first but expect that to only happen rarely due to the likelihood of
both processes trying to update the very same row in a large table.
Other users are also on line at the same time performing updates
across the database.

What I have witnessed though was the second process being blocked by
the first (confirmed by looking in enterprise manager) and REMAINING
BLOCKED EVEN AFTER A COMMIT in the first process!!

My big problem is that I have only seen this happen once on a customer
site and have not been able to reproduce it myself to provide any
further information.

The application is set to use row level locking and is also in read
uncommited mode.

Any help or suggestions would be greatly appreciated.

Bob."Bob Jones" <uniface_list@.hotmail.com> wrote in message
news:1ba5e227.0410120559.52e0f339@.posting.google.c om...
>I am using SQL server 2000 with Uniface (4GL).
> I am running a process which commits every 30 seconds or so on one
> machine and another user is trying to perform a small update at the
> same time on the one of the tables whose data is modified by the first
> process.
> I accept that the two second process may get blocked at times by the
> first but expect that to only happen rarely due to the likelihood of
> both processes trying to update the very same row in a large table.
> Other users are also on line at the same time performing updates
> across the database.
> What I have witnessed though was the second process being blocked by
> the first (confirmed by looking in enterprise manager) and REMAINING
> BLOCKED EVEN AFTER A COMMIT in the first process!!
> My big problem is that I have only seen this happen once on a customer
> site and have not been able to reproduce it myself to provide any
> further information.
> The application is set to use row level locking and is also in read
> uncommited mode.
> Any help or suggestions would be greatly appreciated.
> Bob.

I would start by trying to identify what the lock is and on which object -
you can use a combination of sp_lock and sp_who2, but you might find
Erland's tool to be more flexible:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

You could also use a Profiler trace to gather more information about the
processes which are active when the problem occurs. Hopefully that will give
you enough information to narrow down the problem to a specific component or
piece of code. Finally, you should probably make sure that the transaction
really is COMMITted, especially if it's started in a middle-tier or client
component as opposed to a stored procedure - Profiler should be able to help
there, and of course DBCC OPENTRAN.

Simon|||I am pretty sure the cause for the blocking is that the transaction in the
first process did NOT really commit. If this shows up again, you can use
the following cmd to find out who is blocking the second process and whether
it has any active transaction associated with.

select spid, blocked, open_tran from sysprocesses

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Jones" <uniface_list@.hotmail.com> wrote in message
news:1ba5e227.0410120559.52e0f339@.posting.google.c om...
> I am using SQL server 2000 with Uniface (4GL).
> I am running a process which commits every 30 seconds or so on one
> machine and another user is trying to perform a small update at the
> same time on the one of the tables whose data is modified by the first
> process.
> I accept that the two second process may get blocked at times by the
> first but expect that to only happen rarely due to the likelihood of
> both processes trying to update the very same row in a large table.
> Other users are also on line at the same time performing updates
> across the database.
> What I have witnessed though was the second process being blocked by
> the first (confirmed by looking in enterprise manager) and REMAINING
> BLOCKED EVEN AFTER A COMMIT in the first process!!
> My big problem is that I have only seen this happen once on a customer
> site and have not been able to reproduce it myself to provide any
> further information.
> The application is set to use row level locking and is also in read
> uncommited mode.
> Any help or suggestions would be greatly appreciated.
> Bob.|||The most likely cause is that either the COMMIT is not being executed as
expected or more than one BEGIN TRAN was executed before the COMMIT. Since
this occurs with only one of your customers, it may be that data differences
cause a different code path to be executed.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Bob Jones" <uniface_list@.hotmail.com> wrote in message
news:1ba5e227.0410120559.52e0f339@.posting.google.c om...
>I am using SQL server 2000 with Uniface (4GL).
> I am running a process which commits every 30 seconds or so on one
> machine and another user is trying to perform a small update at the
> same time on the one of the tables whose data is modified by the first
> process.
> I accept that the two second process may get blocked at times by the
> first but expect that to only happen rarely due to the likelihood of
> both processes trying to update the very same row in a large table.
> Other users are also on line at the same time performing updates
> across the database.
> What I have witnessed though was the second process being blocked by
> the first (confirmed by looking in enterprise manager) and REMAINING
> BLOCKED EVEN AFTER A COMMIT in the first process!!
> My big problem is that I have only seen this happen once on a customer
> site and have not been able to reproduce it myself to provide any
> further information.
> The application is set to use row level locking and is also in read
> uncommited mode.
> Any help or suggestions would be greatly appreciated.
> Bob.

Lock row.

Hello everyone,
I have a web project where users access a aspx page to view information stored in an SQL database.
My client want that one user can access a row of information and see it, allother users shouldn't be able to view or update thesame row?
it means whenever a row of data is displayed by some user, this row should be locked even for beeing viewed by all other users, when this user close this page, this row will be available. ?I should do this in code behind or something in sql...
How can I do that?

It is not easy to do but the link below will take you in the right direction. Hope this helps.

http://www.sql-server-performance.com/lock_contention_tamed_article.asp

|||

Thanks for your reply,
All I want to do is: if a userA access a row, all other users can't access this row even for reading.
I read the article but I didn't understand how can I do this.
Any help...

|||

No, not really. You'd have to roll your own method of locking out views when someone is viewing the same record.

Really, it sounds like a bad design.

|||

Goodway:

Thanks for your reply,
All I want to do is: if a userA access a row, all other users can't access this row even for reading.
I read the article but I didn't understand how can I do this.
Any help...

(UPDATE Users WITH (ROWLOCK)
SET Username = 'fred' WHERE Username = 'foobar'

By specifically requesting row-level locks, these problems are avoided.)

I got this from that link but I have told you it is not easy to lock access because as relational algebra expert Chris Date puts it a SELECT returns a table so you could be locking a table instead of one row through lock escalation managed by SQL Server. Hope this helps.

|||

That Update doesn't do what he asked for.

A) The WITH ROWLOCK really doesn't help. Sure, it (might) help with reducing contention by holding less granular locks, but... It doesn't hold the lock for any longer than the update statement takes to complete. That is unless you wrap it in a highly isolated transaction.

B) Transactions complete when the execution of the transaction variable is destroyed or the connection object is closed. This normally happens when the execution of the page completes. In order to avoid that (If it's even possible), you would need to stuff the transaction into either a session or application object so it doesn't go out of scope with the page finishes executing.

The workaround for B causes problems C,D and E.

C) Because the transaction (and locks) are now being held for LONG periods of time, you'll start having all kinds of performance and timeout problems within the database.

D) Memory usage within the webserver will skyrocket because of all the transaction/connection objects being held across postbacks. This will lead to additional scaling issues. Possibly consuming enough memory to trigger the .NET framework to recycle the application. Make sure to add plenty of memory to the webserver, and set the recycling threshold very high to avoid the locks being lost randomly when the system recycles the process.

E) Abandoned sessions will cause the record to be locked indefinately. What if the user loses power, or closes the browser? The server will continue to lock that record forever (Or until the session dies after a very long period of activity if you've stored the information in session, or until you recycle the application if you stored them in application). Sure the user can then log back in to the website, and he'll have to wait for the record to unlock itself before even he can do anything with the record.

The idea is flawed. Don't lock the record. Remember the original values, and when you go to update the record make sure the record still looks exactly the same prior to actually doing the update. The sql wizard will do this for you if you tell it to compare all values.

|||

(My client want that one user can access a row of information and see it, all other users shouldn't be able to view or update the same row?)

I was replying his original post and I understand he is trying to lock the viewing of scalar value well that is not something you could do without problems with RDBMS(relational database management systems).

Lock Query

How do you find what user is locking what row in what table in what database?On Thu, 27 Oct 2005 13:17:01 -0700, CLM
<CLM@.discussions.microsoft.com> wrote:
>How do you find what user is locking what row in what table in what database?
exec sp_who2 is a start.
I don't know of any way to map to individual rows, though it would be
nice!
What do you need it for?
J.|||CLM wrote:
> How do you find what user is locking what row in what table in what
> database?
Might not be a row. Could be a page or the entire table.
You can run sp_lock to a get a list of locks on the server. Turn the
dboid into a database name using db_name(dbid). Turn the ObjId into a
name using object_name(ObjId) from the correct database. Turn the spid
into a user by selecting the spid from master..sysprocesses.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I had some informix guys ask me. They can find out exactly what row is being
locked which I know has been useful on the production informix side of the
world. I figured it would be even easier on the Sql Server side. You can
find a lot from sp_lock but not which row...
"jxstern" wrote:
> On Thu, 27 Oct 2005 13:17:01 -0700, CLM
> <CLM@.discussions.microsoft.com> wrote:
> >How do you find what user is locking what row in what table in what database?
> exec sp_who2 is a start.
> I don't know of any way to map to individual rows, though it would be
> nice!
> What do you need it for?
> J.
>|||David Gugick wrote:
> CLM wrote:
>> How do you find what user is locking what row in what table in what
>> database?
> Might not be a row. Could be a page or the entire table.
> You can run sp_lock to a get a list of locks on the server. Turn the
> dboid into a database name using db_name(dbid). Turn the ObjId into a
> name using object_name(ObjId) from the correct database. Turn the spid
> into a user by selecting the spid from master..sysprocesses.
You can also use DBCC OPENTRAN and fn_get_sql to get information about
what is running for the spid that is blocking.
See if this page helps:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224453
--
David Gugick
Quest Software
www.imceda.com
www.quest.comsql

Lock Query

How do you find what user is locking what row in what table in what database
?On Thu, 27 Oct 2005 13:17:01 -0700, CLM
<CLM@.discussions.microsoft.com> wrote:[vbcol=seagreen]
>How do you find what user is locking what row in what table in what database?[/vbco
l]
exec sp_who2 is a start.
I don't know of any way to map to individual rows, though it would be
nice!
What do you need it for?
J.|||CLM wrote:
> How do you find what user is locking what row in what table in what
> database?
Might not be a row. Could be a page or the entire table.
You can run sp_lock to a get a list of locks on the server. Turn the
dboid into a database name using db_name(dbid). Turn the ObjId into a
name using object_name(ObjId) from the correct database. Turn the spid
into a user by selecting the spid from master..sysprocesses.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I had some informix guys ask me. They can find out exactly what row is bein
g
locked which I know has been useful on the production informix side of the
world. I figured it would be even easier on the Sql Server side. You can
find a lot from sp_lock but not which row...
"jxstern" wrote:

> On Thu, 27 Oct 2005 13:17:01 -0700, CLM
> <CLM@.discussions.microsoft.com> wrote:
> exec sp_who2 is a start.
> I don't know of any way to map to individual rows, though it would be
> nice!
> What do you need it for?
> J.
>|||David Gugick wrote:
> CLM wrote:
> Might not be a row. Could be a page or the entire table.
> You can run sp_lock to a get a list of locks on the server. Turn the
> dboid into a database name using db_name(dbid). Turn the ObjId into a
> name using object_name(ObjId) from the correct database. Turn the spid
> into a user by selecting the spid from master..sysprocesses.
You can also use DBCC OPENTRAN and fn_get_sql to get information about
what is running for the spid that is blocking.
See if this page helps:
http://support.microsoft.com/defaul...kb;en-us;224453
David Gugick
Quest Software
www.imceda.com
www.quest.com

Lock Query

How do you find what user is locking what row in what table in what database?
On Thu, 27 Oct 2005 13:17:01 -0700, CLM
<CLM@.discussions.microsoft.com> wrote:
>How do you find what user is locking what row in what table in what database?
exec sp_who2 is a start.
I don't know of any way to map to individual rows, though it would be
nice!
What do you need it for?
J.
|||CLM wrote:
> How do you find what user is locking what row in what table in what
> database?
Might not be a row. Could be a page or the entire table.
You can run sp_lock to a get a list of locks on the server. Turn the
dboid into a database name using db_name(dbid). Turn the ObjId into a
name using object_name(ObjId) from the correct database. Turn the spid
into a user by selecting the spid from master..sysprocesses.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I had some informix guys ask me. They can find out exactly what row is being
locked which I know has been useful on the production informix side of the
world. I figured it would be even easier on the Sql Server side. You can
find a lot from sp_lock but not which row...
"jxstern" wrote:

> On Thu, 27 Oct 2005 13:17:01 -0700, CLM
> <CLM@.discussions.microsoft.com> wrote:
> exec sp_who2 is a start.
> I don't know of any way to map to individual rows, though it would be
> nice!
> What do you need it for?
> J.
>
|||David Gugick wrote:
> CLM wrote:
> Might not be a row. Could be a page or the entire table.
> You can run sp_lock to a get a list of locks on the server. Turn the
> dboid into a database name using db_name(dbid). Turn the ObjId into a
> name using object_name(ObjId) from the correct database. Turn the spid
> into a user by selecting the spid from master..sysprocesses.
You can also use DBCC OPENTRAN and fn_get_sql to get information about
what is running for the spid that is blocking.
See if this page helps:
http://support.microsoft.com/default...b;en-us;224453
David Gugick
Quest Software
www.imceda.com
www.quest.com

lock pdf export

Hi
i just create a report in c#. The user can export this report in .pdf format. Now i want to lock the content of this .pdf files. How can i do this in crystal report 10?
Thank'sSet the EnableExportButton to False
Add a command button to the Viewer and customize the allowed export options...
But remember, that the user could have a PDFPrinter

Lock out user for database structure update

Is there any way to disconnect active users from a live database and lock them out till the database strucure has been updated?

Something like this should work for you.

use <db>

go

--remove all active users from the database

Alter database <db> set SINGLE_USER with ROLLBACK IMMEDIATE

go

--make changes to the database structure on this connection

go

--let users back in

Alter database <db> set MULTI_USER

|||Thank you

Lock out 1 user within Enterprise Manager

I have one rogue user who constantly creates, deletes,
and just does things in the system that are not
authorized causing tons of administrative work on my
end. Is there a way to allow this user ONLY to READ
everything from within SQL-EM, vs. writing or creating
anything. All this person does is Crystal reports, so I
might just remove the software altogether. But I'd like
to restrict rights because they'll definitely come tell
me that they couldn't do something due to restriction, at
which point I can bust them. Thanks!Don't give the user sysadmin privileges on the database.
Make this person a member of the datareader role in the database they need
access to and no other role membership other than public and no permissions
explicitly.
Bill
"CPS" <anonymous@.discussions.microsoft.com> wrote in message
news:14e2f01c3fa26$40d04c80$a001280a@.phx
.gbl...
> I have one rogue user who constantly creates, deletes,
> and just does things in the system that are not
> authorized causing tons of administrative work on my
> end. Is there a way to allow this user ONLY to READ
> everything from within SQL-EM, vs. writing or creating
> anything. All this person does is Crystal reports, so I
> might just remove the software altogether. But I'd like
> to restrict rights because they'll definitely come tell
> me that they couldn't do something due to restriction, at
> which point I can bust them. Thanks!|||Thanks!! How do I make sure they don't go into EM and
give themselves the rights back again?
>--Original Message--
>Don't give the user sysadmin privileges on the database.
>Make this person a member of the datareader role in the
database they need
>access to and no other role membership other than public
and no permissions
>explicitly.
>Bill
>"CPS" <anonymous@.discussions.microsoft.com> wrote in
message
> news:14e2f01c3fa26$40d04c80$a001280a@.phx
.gbl...
I
like
at
>
>.
>|||If they don't have sa or dbo rights they wont be able to change permissions.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:1567901c3fa5b$c5789d90$a501280a@.phx
.gbl...
> Thanks!! How do I make sure they don't go into EM and
> give themselves the rights back again?
> database they need
> and no permissions
> message
> I
> like
> atsql

Lock hint question

Hi All,

I have a question about lock hint for you :

If the first user currently run a select command
with share lock and hold it. What kind of
lock (lock hint) should be used by the second user
in the select command (from the same table) so that
this command will wait until the first user releases
the lock ?

I have tried using tablock, tablockx, xlock and
updlock hint in the select command for the second
user, but it is not successful. Below is my
unsuccessful test :

The first user :
Begin Transaction
select fprefix from ut1 where fprefix = '000'

The second user :
select * from ut1 with (tablockx)

Please help me

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!> The first user :
> Begin Transaction
> select fprefix from ut1 where fprefix = '000'
> The second user :
> select * from ut1 with (tablockx)

With the default READ COMMITTED transaction isolation level, any locks
acquired during the select will be released when the select completes. The
second user will then be able to acquire any type of lock on the table.

The example below will override the default isolation level:

The first user :
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT fprefix FROM ut1 WHERE fprefix = '000'

The second user :
SELECT * FROM ut1 WITH (TABLOCKX)

Of course, this approach reduces concurrency. See the Books Online
<tsqlref.chm::/ts_set-set_74bw.htm> for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:4005dee1$0$70304$75868355@.news.frii.net...
> Hi All,
> I have a question about lock hint for you :
> If the first user currently run a select command
> with share lock and hold it. What kind of
> lock (lock hint) should be used by the second user
> in the select command (from the same table) so that
> this command will wait until the first user releases
> the lock ?
> I have tried using tablock, tablockx, xlock and
> updlock hint in the select command for the second
> user, but it is not successful. Below is my
> unsuccessful test :
> The first user :
> Begin Transaction
> select fprefix from ut1 where fprefix = '000'
> The second user :
> select * from ut1 with (tablockx)
> Please help me
> Thanks in advance
> Anita Hery
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dan Guzman,

Yes, now it is successful with SERIALIZABLE hint.
Many thanks for your reply

Regards,

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Lock a row

I wanted to locked to hide a record to other user if the record is viewing
by one user.
Are there any way to do this?
Does lock the record can hide this record to other user?
Any information is great appreciated.
SourisYou can use query hints with a transaction but you're looking at scalability
issues. Why do you want to hide a row while someone is looking at it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"souris" <soukkris@.viddotron.com> wrote in message
news:eJ3lg3kBFHA.2916@.TK2MSFTNGP10.phx.gbl...
I wanted to locked to hide a record to other user if the record is viewing
by one user.
Are there any way to do this?
Does lock the record can hide this record to other user?
Any information is great appreciated.
Souris|||On Sat, 29 Jan 2005 17:00:05 -0500, souris wrote:

>I wanted to locked to hide a record to other user if the record is viewing
>by one user.
>Are there any way to do this?
>Does lock the record can hide this record to other user?
>Any information is great appreciated.
Hi Souris,
You could use the READPAST locking hint. Keep in mind that this is only
usefull in some very specific circumstances. If you use this locking hint
in a statistical or reporting query, you'll miss some data without having
any indication if and how much data you miss.
A typical situation where the READPAST hint is useful is a queueing
system: each application / connection / agent / whatever reads the first
row from a wrok-queue table and acquire an exclusive lock; other agents
will read past this row and get the next non-locked one. The exclusive
lock ensures that the connection reading the row will be able to change or
even delete is after the data is processed.
BEGIN TRANSACTION
SELECT TOP 1 ....
FROM WorkQueue WITH (READPAST, XLOCK)
ORDER BY Priority
-- Do some stuff
IF (...) -- data changed
UPDATE WorkQueue
SET ... = ...
WHERE ....
IF (...) -- processing completely done
DELETE WorkQueue
WHERE ....
IF (...) -- check for unhandled errors
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the message.
This app is for a call center.
Because I do not want the customer get more than one call for the same issue
Souris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZJxXhlBFHA.1296@.TK2MSFTNGP10.phx.gbl...
> You can use query hints with a transaction but you're looking at
> scalability
> issues. Why do you want to hide a row while someone is looking at it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "souris" <soukkris@.viddotron.com> wrote in message
> news:eJ3lg3kBFHA.2916@.TK2MSFTNGP10.phx.gbl...
> I wanted to locked to hide a record to other user if the record is viewing
> by one user.
> Are there any way to do this?
> Does lock the record can hide this record to other user?
> Any information is great appreciated.
> Souris
>|||Thanks for the information,
It is what I am looking for.
Souris
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:4b6ov0dsbu5e3gmhsne0omtr2ul2ac95ae@.
4ax.com...
> On Sat, 29 Jan 2005 17:00:05 -0500, souris wrote:
>
> Hi Souris,
> You could use the READPAST locking hint. Keep in mind that this is only
> usefull in some very specific circumstances. If you use this locking hint
> in a statistical or reporting query, you'll miss some data without having
> any indication if and how much data you miss.
> A typical situation where the READPAST hint is useful is a queueing
> system: each application / connection / agent / whatever reads the first
> row from a wrok-queue table and acquire an exclusive lock; other agents
> will read past this row and get the next non-locked one. The exclusive
> lock ensures that the connection reading the row will be able to change or
> even delete is after the data is processed.
> BEGIN TRANSACTION
> SELECT TOP 1 ....
> FROM WorkQueue WITH (READPAST, XLOCK)
> ORDER BY Priority
> -- Do some stuff
> IF (...) -- data changed
> UPDATE WorkQueue
> SET ... = ...
> WHERE ....
> IF (...) -- processing completely done
> DELETE WorkQueue
> WHERE ....
> IF (...) -- check for unhandled errors
> ROLLBACK TRANSACTION
> ELSE
> COMMIT TRANSACTION
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||I would probably suggest that this is not a great idea. Holding locks from
the client is troublesome, and if the connection dies you are not even going
to see that there was a call in progress. In this case, why not use a
status column (or table, if you want a complete history of status)
Then when the user gets the row, you set the status to in-progress, and set
the user that has the row in use. If they don't complete the call properly,
it will stay in an open state. When the user logs back in, you can check to
see if they have any open sessions and make them deal with them.
All of your other clients will look for rows that are not in-progress or
completed. Use the readpast hint as mentioned by Hugo to keep any clients
from having to wait. This way your management applications can see which
calls are in progress, what their status is, and it will still be "locked"
from the users that it needs to be locked from.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"souris" <soukkris@.viddotron.com> wrote in message
news:%23Fbdm4oBFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks for the information,
> It is what I am looking for.
> Souris
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:4b6ov0dsbu5e3gmhsne0omtr2ul2ac95ae@.
4ax.com...
>

lock

Hi,
i have a big problem , i work with sql 2000 on windows 2000.
When user do a select against my database sqlserver lock all the table and nonoe can work.
Haw can i change the isolation level for a ropw and for all the db.
Thanks.That's quite a heavy lock. What client/operation are you using?
Too me, sounds someone restricted the database: a db can be restricted to allow one single user at a time. Did you check that? It's in the database properties, Options tab.|||have you isolated the problematic code using profiler? somebody using nasty table hint or big long transaction? got code?|||Sounds like your user's query is doing a table scan. Get his query, and see if any indexes will help it. An index is a pre-requisite for row locking.

Location of query results (sql 2000)

I have a multi user application running with a query to fill GUI components
(Delphi)
I get a 'share violation' for 'del1.mb' (according to a user). All users run
with the same userid/password against the SQL server. Now I wonder where MS
SQL put the resultset from the query and if access rights to this folder is
the cause of this problem.
Can anyone help?
LanCan you tell what is del1.mb. I feel share violation is because of some
concurrency and locking and not to do with file access.
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Lan" wrote:

> I have a multi user application running with a query to fill GUI component
s
> (Delphi)
> I get a 'share violation' for 'del1.mb' (according to a user). All users r
un
> with the same userid/password against the SQL server. Now I wonder where M
S
> SQL put the resultset from the query and if access rights to this folder i
s
> the cause of this problem.
> Can anyone help?
> Lan|||SQL Server doesn't store the results in a file. It sends the result to the c
lient application. So,
you have to ask this in a Delphi forum.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lan" <Lan@.discussions.microsoft.com> wrote in message
news:4A9D1C6F-7866-4BC8-81E3-C8F88C96FC90@.microsoft.com...
>I have a multi user application running with a query to fill GUI components
> (Delphi)
> I get a 'share violation' for 'del1.mb' (according to a user). All users r
un
> with the same userid/password against the SQL server. Now I wonder where M
S
> SQL put the resultset from the query and if access rights to this folder i
s
> the cause of this problem.
> Can anyone help?
> Lansql

Locating recently edited tables

Hi
Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer
application. This application keeps its data in a SQL 2000 database.
What would be the easiest way for me to figure out which table got updated
with the "Pennsylvania Av." string?`
I don't care whether the solution is to change some view settings in my SQL
GUI or if I can write some t-sql code and output the solution for me, I just
need to figure out where the application stores the darn addresses.
Thanks in advance for any input
IbUse Profiler. You can catch the SQL statements submitted by the app. You can even catch the
execution plan and from that determine what tables were hit (if that approach is better than working
with the SQL submitted).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ib Schrader" <ibschrader@.gmail.com> wrote in message news:ez2gcoqHHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi
> Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer application. This
> application keeps its data in a SQL 2000 database.
> What would be the easiest way for me to figure out which table got updated with the "Pennsylvania
> Av." string?`
> I don't care whether the solution is to change some view settings in my SQL GUI or if I can write
> some t-sql code and output the solution for me, I just need to figure out where the application
> stores the darn addresses.
> Thanks in advance for any input
> Ib
>|||Ib
Do you store "Pennsylvania Av." value in many tables?
"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:ez2gcoqHHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi
> Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer
> application. This application keeps its data in a SQL 2000 database.
> What would be the easiest way for me to figure out which table got updated
> with the "Pennsylvania Av." string?`
> I don't care whether the solution is to change some view settings in my
> SQL GUI or if I can write some t-sql code and output the solution for me,
> I just need to figure out where the application stores the darn addresses.
> Thanks in advance for any input
> Ib
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVUvutqHHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Ib
> Do you store "Pennsylvania Av." value in many tables?
I don't know that. The exercise is to locate which tables got updated by the
application. I don't know how many that is, but I suspect it's just one
table that stores the data.
Thanks for the profiler idea..I'll look into that.
Ib|||The profiler helped me catch which table got updated.
Thank you very much.
Ib

Locating recently edited tables

Hi
Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer
application. This application keeps its data in a SQL 2000 database.
What would be the easiest way for me to figure out which table got updated
with the "Pennsylvania Av." string?`
I don't care whether the solution is to change some view settings in my SQL
GUI or if I can write some t-sql code and output the solution for me, I just
need to figure out where the application stores the darn addresses.
Thanks in advance for any input
IbUse Profiler. You can catch the SQL statements submitted by the app. You can
even catch the
execution plan and from that determine what tables were hit (if that approac
h is better than working
with the SQL submitted).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ib Schrader" <ibschrader@.gmail.com> wrote in message news:ez2gcoqHHHA.1248@.TK2MSFTNGP02.phx
.gbl...
> Hi
> Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer app
lication. This
> application keeps its data in a SQL 2000 database.
> What would be the easiest way for me to figure out which table got updated
with the "Pennsylvania
> Av." string?`
> I don't care whether the solution is to change some view settings in my SQ
L GUI or if I can write
> some t-sql code and output the solution for me, I just need to figure out
where the application
> stores the darn addresses.
> Thanks in advance for any input
> Ib
>|||Ib
Do you store "Pennsylvania Av." value in many tables?
"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:ez2gcoqHHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi
> Pop quiz: Let's say a user types in "Pennsylvania Av." in his customer
> application. This application keeps its data in a SQL 2000 database.
> What would be the easiest way for me to figure out which table got updated
> with the "Pennsylvania Av." string?`
> I don't care whether the solution is to change some view settings in my
> SQL GUI or if I can write some t-sql code and output the solution for me,
> I just need to figure out where the application stores the darn addresses.
> Thanks in advance for any input
> Ib
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVUvutqHHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Ib
> Do you store "Pennsylvania Av." value in many tables?
I don't know that. The exercise is to locate which tables got updated by the
application. I don't know how many that is, but I suspect it's just one
table that stores the data.
Thanks for the profiler idea..I'll look into that.
Ib|||The profiler helped me catch which table got updated.
Thank you very much.
Ib

Locate records that meet ALL the requirements using IN or EXIST? Help?

Using SQL Server 2000...
I have a front end that provides the user with a 'search engine' to
pass search parameters. The stored procedure it calls joins multiple
tables/views to return the proper result.
I am running into a problem that I solved in the interface using code
(that takes WAY too long), but I know there must be a way to have SQL
Server do the work using T-SQL... as this seems a very simple issue.
The following is just a snippet of the pertinent information:
Assume I have a main Customer table with a unique CustID field.
I have another table of descriptive Flags with a unique FlagID field.
A third table Customer_Flag_Link has a unique ID field and contains 2
columns, the CustID and the FlagID.
Obvioulsy, the role of this 3rd table is to be able to assign multiple
Flags to each Customer.
Now assume I have the following data in the Customer_Flag_Link table:
UniqueID CustID FlagID
===============================
1 123 333
2 123 444
3 123 222
4 987 444
5 987 222
6 567 111
7 567 222
My issue is that I want to be able to locate Customers who have ALL of
the passed Flags associated with them, I do not know how many Flags
will be passed (and there are other search parameters passed as well -
but this is the piece that is giving me trouble, though I am sure it
is simple!)
For example, I want to return CustID where exists FlagID 222 AND 444.
Based on the above data it should return CustID 123 and 987, but not
567.
Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
this does not give me the results I want.
Any ideas would be appreciated!
Thanks,
AK
This might help.
SELECT COUNT(distinct FlagID)
FROM Customer_Flag_Link
WHERE FlagID IN (222,444)
AND Customer = 'Ralph'
HAVING COUNT(distinct FlagID) = 2
The number of items in the IN clause is what is used in the HAVING
test. Of course this could be written as an EXISTS subquery,
correlated on Customer.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Jun 2007 10:50:45 -0700, aklein <abklein@.optonline.net>
wrote:

>Using SQL Server 2000...
>I have a front end that provides the user with a 'search engine' to
>pass search parameters. The stored procedure it calls joins multiple
>tables/views to return the proper result.
>I am running into a problem that I solved in the interface using code
>(that takes WAY too long), but I know there must be a way to have SQL
>Server do the work using T-SQL... as this seems a very simple issue.
>The following is just a snippet of the pertinent information:
>Assume I have a main Customer table with a unique CustID field.
>I have another table of descriptive Flags with a unique FlagID field.
>A third table Customer_Flag_Link has a unique ID field and contains 2
>columns, the CustID and the FlagID.
>Obvioulsy, the role of this 3rd table is to be able to assign multiple
>Flags to each Customer.
>Now assume I have the following data in the Customer_Flag_Link table:
>
>UniqueID CustID FlagID
>===============================
>1 123 333
>2 123 444
>3 123 222
>4 987 444
>5 987 222
>6 567 111
>7 567 222
>My issue is that I want to be able to locate Customers who have ALL of
>the passed Flags associated with them, I do not know how many Flags
>will be passed (and there are other search parameters passed as well -
>but this is the piece that is giving me trouble, though I am sure it
>is simple!)
>For example, I want to return CustID where exists FlagID 222 AND 444.
>Based on the above data it should return CustID 123 and 987, but not
>567.
>Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
>this does not give me the results I want.
>Any ideas would be appreciated!
>Thanks,
>AK
|||Hmmmm...
I have to see how to stick that idea into the larger procedure... but
it gives me a starting point.
Thanks for the idea!
AK

Locate records that meet ALL the requirements using IN or EXIST? Help?

Using SQL Server 2000...
I have a front end that provides the user with a 'search engine' to
pass search parameters. The stored procedure it calls joins multiple
tables/views to return the proper result.
I am running into a problem that I solved in the interface using code
(that takes WAY too long), but I know there must be a way to have SQL
Server do the work using T-SQL... as this seems a very simple issue.
The following is just a snippet of the pertinent information:
Assume I have a main Customer table with a unique CustID field.
I have another table of descriptive Flags with a unique FlagID field.
A third table Customer_Flag_Link has a unique ID field and contains 2
columns, the CustID and the FlagID.
Obvioulsy, the role of this 3rd table is to be able to assign multiple
Flags to each Customer.
Now assume I have the following data in the Customer_Flag_Link table:
UniqueID CustID FlagID
=============================== 1 123 333
2 123 444
3 123 222
4 987 444
5 987 222
6 567 111
7 567 222
My issue is that I want to be able to locate Customers who have ALL of
the passed Flags associated with them, I do not know how many Flags
will be passed (and there are other search parameters passed as well -
but this is the piece that is giving me trouble, though I am sure it
is simple!)
For example, I want to return CustID where exists FlagID 222 AND 444.
Based on the above data it should return CustID 123 and 987, but not
567.
Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
this does not give me the results I want.
Any ideas would be appreciated!
Thanks,
AKThis might help.
SELECT COUNT(distinct FlagID)
FROM Customer_Flag_Link
WHERE FlagID IN (222,444)
AND Customer = 'Ralph'
HAVING COUNT(distinct FlagID) = 2
The number of items in the IN clause is what is used in the HAVING
test. Of course this could be written as an EXISTS subquery,
correlated on Customer.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Jun 2007 10:50:45 -0700, aklein <abklein@.optonline.net>
wrote:
>Using SQL Server 2000...
>I have a front end that provides the user with a 'search engine' to
>pass search parameters. The stored procedure it calls joins multiple
>tables/views to return the proper result.
>I am running into a problem that I solved in the interface using code
>(that takes WAY too long), but I know there must be a way to have SQL
>Server do the work using T-SQL... as this seems a very simple issue.
>The following is just a snippet of the pertinent information:
>Assume I have a main Customer table with a unique CustID field.
>I have another table of descriptive Flags with a unique FlagID field.
>A third table Customer_Flag_Link has a unique ID field and contains 2
>columns, the CustID and the FlagID.
>Obvioulsy, the role of this 3rd table is to be able to assign multiple
>Flags to each Customer.
>Now assume I have the following data in the Customer_Flag_Link table:
>
>UniqueID CustID FlagID
>===============================>1 123 333
>2 123 444
>3 123 222
>4 987 444
>5 987 222
>6 567 111
>7 567 222
>My issue is that I want to be able to locate Customers who have ALL of
>the passed Flags associated with them, I do not know how many Flags
>will be passed (and there are other search parameters passed as well -
>but this is the piece that is giving me trouble, though I am sure it
>is simple!)
>For example, I want to return CustID where exists FlagID 222 AND 444.
>Based on the above data it should return CustID 123 and 987, but not
>567.
>Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
>this does not give me the results I want.
>Any ideas would be appreciated!
>Thanks,
>AK|||Hmmmm...
I have to see how to stick that idea into the larger procedure... but
it gives me a starting point.
Thanks for the idea!
AK

Locate records that meet ALL the requirements using IN or EXIST? Help?

Using SQL Server 2000...
I have a front end that provides the user with a 'search engine' to
pass search parameters. The stored procedure it calls joins multiple
tables/views to return the proper result.
I am running into a problem that I solved in the interface using code
(that takes WAY too long), but I know there must be a way to have SQL
Server do the work using T-SQL... as this seems a very simple issue.
The following is just a snippet of the pertinent information:
Assume I have a main Customer table with a unique CustID field.
I have another table of descriptive Flags with a unique FlagID field.
A third table Customer_Flag_Link has a unique ID field and contains 2
columns, the CustID and the FlagID.
Obvioulsy, the role of this 3rd table is to be able to assign multiple
Flags to each Customer.
Now assume I have the following data in the Customer_Flag_Link table:
UniqueID CustID FlagID
===============================
1 123 333
2 123 444
3 123 222
4 987 444
5 987 222
6 567 111
7 567 222
My issue is that I want to be able to locate Customers who have ALL of
the passed Flags associated with them, I do not know how many Flags
will be passed (and there are other search parameters passed as well -
but this is the piece that is giving me trouble, though I am sure it
is simple!)
For example, I want to return CustID where exists FlagID 222 AND 444.
Based on the above data it should return CustID 123 and 987, but not
567.
Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
this does not give me the results I want.
Any ideas would be appreciated!
Thanks,
AKThis might help.
SELECT COUNT(distinct FlagID)
FROM Customer_Flag_Link
WHERE FlagID IN (222,444)
AND Customer = 'Ralph'
HAVING COUNT(distinct FlagID) = 2
The number of items in the IN clause is what is used in the HAVING
test. Of course this could be written as an EXISTS subquery,
correlated on Customer.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Jun 2007 10:50:45 -0700, aklein <abklein@.optonline.net>
wrote:

>Using SQL Server 2000...
>I have a front end that provides the user with a 'search engine' to
>pass search parameters. The stored procedure it calls joins multiple
>tables/views to return the proper result.
>I am running into a problem that I solved in the interface using code
>(that takes WAY too long), but I know there must be a way to have SQL
>Server do the work using T-SQL... as this seems a very simple issue.
>The following is just a snippet of the pertinent information:
>Assume I have a main Customer table with a unique CustID field.
>I have another table of descriptive Flags with a unique FlagID field.
>A third table Customer_Flag_Link has a unique ID field and contains 2
>columns, the CustID and the FlagID.
>Obvioulsy, the role of this 3rd table is to be able to assign multiple
>Flags to each Customer.
>Now assume I have the following data in the Customer_Flag_Link table:
>
>UniqueID CustID FlagID
>===============================
>1 123 333
>2 123 444
>3 123 222
>4 987 444
>5 987 222
>6 567 111
>7 567 222
>My issue is that I want to be able to locate Customers who have ALL of
>the passed Flags associated with them, I do not know how many Flags
>will be passed (and there are other search parameters passed as well -
>but this is the piece that is giving me trouble, though I am sure it
>is simple!)
>For example, I want to return CustID where exists FlagID 222 AND 444.
>Based on the above data it should return CustID 123 and 987, but not
>567.
>Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
>this does not give me the results I want.
>Any ideas would be appreciated!
>Thanks,
>AK|||Hmmmm...
I have to see how to stick that idea into the larger procedure... but
it gives me a starting point.
Thanks for the idea!
AK

localization and sqlserver

Hi,

I have a simple 'user' table and an ASP form that connects to it. I
want users of all languages to be able to type in their registration
info, in their own language, store it that way, then have another page
that displays it.

Are there any specific settings I need in SQL Server to handle this?

ThanksSorry, I forgot to mention I'm using SQL Server 2000, Windows 2000
server and ASP Classic.

On Tue, 10 Aug 2004 19:25:34 GMT, Justin
<ng@.NO_SPAMmaritimeNO_SPAMsource.ca> wrote:

>Hi,
>I have a simple 'user' table and an ASP form that connects to it. I
>want users of all languages to be able to type in their registration
>info, in their own language, store it that way, then have another page
>that displays it.
>Are there any specific settings I need in SQL Server to handle this?
>Thanks|||Justin (ng@.NO_SPAMmaritimeNO_SPAMsource.ca) writes:
> I have a simple 'user' table and an ASP form that connects to it. I
> want users of all languages to be able to type in their registration
> info, in their own language, store it that way, then have another page
> that displays it.
> Are there any specific settings I need in SQL Server to handle this?

There are ways to set user languages in SQL Server, yes, but I am not
sure that this is what you are after. What happens when you set the language
in SQL Server is that it controls how date literals are interpreted, the
output from the function datename(), and it may also affect the text of
error messages. There are probably a few more things, but nothing terribly
exciting. And the format of dates is best handled client-side anyway.

Maybe you could clarify what you are looking for?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 10 Aug 2004 21:45:58 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Justin (ng@.NO_SPAMmaritimeNO_SPAMsource.ca) writes:
>> I have a simple 'user' table and an ASP form that connects to it. I
>> want users of all languages to be able to type in their registration
>> info, in their own language, store it that way, then have another page
>> that displays it.
>>
>> Are there any specific settings I need in SQL Server to handle this?
>There are ways to set user languages in SQL Server, yes, but I am not
>sure that this is what you are after. What happens when you set the language
>in SQL Server is that it controls how date literals are interpreted, the
>output from the function datename(), and it may also affect the text of
>error messages. There are probably a few more things, but nothing terribly
>exciting. And the format of dates is best handled client-side anyway.
>Maybe you could clarify what you are looking for?

I'm investigating strategies for internationalization of an asp-based
website.

So basically as long as the sqlserver supports unicode I can stuff any
kind of characters in there I want to, but the only difference is when
using SQL Servers functions it has to know what locale it's dealing
with?|||Justin (ng@.NO_SPAMmaritimeNO_SPAMsource.ca) writes:
> I'm investigating strategies for internationalization of an asp-based
> website.
> So basically as long as the sqlserver supports unicode I can stuff any
> kind of characters in there I want to, but the only difference is when
> using SQL Servers functions it has to know what locale it's dealing
> with?

Of course the full story is not that simple. Generally, I would to as much
of the localization client-side, but there are of course situations when
the DB engine gets involved. And you may find these too complex to actually
resolve.

All character columns in SQL Server has a collation, but obviously one
collation that fits one user, does not fit another.

The most obvious case is sorting. While you can sort client side, it's
probably more performant to do it on the server. If you are sending down
SQL statements from the server, you can tack on a COLLATE clause:

SELECT *
FROM tbl
ORDER BY textcol COLLATE Finnish_Swedish_CI_AI

If you use stored procedures, you will have to resort to dynamic SQL, in
which case the point with the procedures are lost to a great deal.

When it comes to searching it becomes more delicate. Say that a user
is looking for a person named Wallenberg, and enters Vallenberg. In
most collations you would not get a hit, but in Finnish_Swedish_CI_AI
you would, since W is just a variant of V in Swedish. You can address
this with the COLLATE clause, but this will lead to indexes being
useless, and can have serious performance consequences. I would say
that in this case you will have to settle with Latin1_General_CI_AI
or what you choose.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Local Variables in User Defined Functions

I'm having a problem declaring variables in UDFs. Are they allowed? Can someone send me some syntax to see what I am doing wrong?You can only declare local variables in scalar and table-valued functions. To understand the difference go to Templates tab of Object Browser in QA and select Functions folder.|||I am using a table function with the following syntax:

create function dbo.TestFunction (@.InputVariable int)
returns table
with encryption
as

declare @.LocalVariable smalldatetime

select @.LocalVariable = ABCDate from ABCTable where rowid = @.InputVariable

return(
select * from XYZTable where XYZDate < @.LocalVariable
)

Any thoughts?

Originally posted by rdjabarov
You can only declare local variables in scalar and table-valued functions. To understand the difference go to Templates tab of Object Browser in QA and select Functions folder.|||It appears you're confusing the syntax of inline function with table-valued function. If you want "returns table" then you can't have any other statement except for "return (select...)", while if you want to have local variables then you need "returns @.tbl table (<structure>) with encryption as begin...<other statements>...end" Review the templates in QA and make a decision.|||gtocha...thanks

Originally posted by rdjabarov
It appears you're confusing the syntax of inline function with table-valued function. If you want "returns table" then you can't have any other statement except for "return (select...)", while if you want to have local variables then you need "returns @.tbl table (<structure>) with encryption as begin...<other statements>...end" Review the templates in QA and make a decision.