arabic characters are not saved (was "A serious problem ...- plz. help"

Dear all ..
i have a serious problem & all ur comments will be appreciated..

i have bought an ASP .NET publishing tool which i receieved an sql script with it to execute on either Ms sql server or MSDE .

i executed it on MSDE as i don't have Ms sql server on my windows dedicated server .

I wanted the tool for publishing (Arabic Language)content for a highly traffic soccer website..

After executing the sql script i tested the tool but i found arabic characters are not saved when i add articles .. they were saved as question marks (??).

so i re-executed the sql script on a new db after modifying every code containg (varchar) to (nvarchar) to support unicode & thus arabic.

it worked & i succeeded in saving arabic articles

BUT >>>>>>>>>>>>>

i found that only short arabic articles r saved fine while any article that reaches around (1 microsoft word page) is not saved well with arabic characters but saved as question marks ( ? ) .. !!

=======

so i checked the db tables using ASP.NET Enterprise manager & i found that

the field of article has (ntext) & infront of it number 16 ..

it seems that the (ntext) has a limit to wt it can save ..

so i believe there's a way which i don't know to make the (ntext) accepts long articles entry .

========

Here's the code in the original sql script i received with the tool & i hope u can guide me in details to any modification to do so that the ntext limit is raise to save any long arabic article.

========

code :

CREATE TABLE [dbo].[xlaANMarticles] (
[articleid] [int] IDENTITY (1, 1) NOT NULL ,
[posted] [nvarchar] (50) NOT NULL ,
[lastupdate] [nvarchar] (50) NOT NULL ,
[headline] [nvarchar] (350) NOT NULL ,
[headlinedate] [nvarchar] (255) NOT NULL ,
[startdate] [nvarchar] (50) NOT NULL ,
[enddate] [nvarchar] (50) NOT NULL ,
[source] [nvarchar] (255) NOT NULL ,
[summary] [nvarchar] (3000) NOT NULL ,
[articleurl] [nvarchar] (1000) NOT NULL ,
[article] [ntext] NOT NULL ,
[status] [tinyint] NOT NULL ,
[autoformat] [nvarchar] (50) NOT NULL ,
[publisherid] [int] NOT NULL ,
[clicks] [int] NOT NULL ,
[editor] [int] NOT NULL ,
[relatedid] [nvarchar] (2000) NOT NULL ,
[isfeatured] [nvarchar] (10) NULL ,
[keywords] [nvarchar] (255) NULL ,
[description] [nvarchar] (255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

i highlighted the specific column for the article field with red color ..

i tried making it (nvarchar) but the sql manager i use saud it can't be done coz. there has to be a field for TEXTIMAGE coz. it's set to be (on) in the code.

waiting for ur help plz. ... i am desperate .. :confused:Masry-

I maximum length of NTEXT is 1,073,741,823 characters. I can't imagine a page of MS word document holding more than that limit. In addition, BOL says prefix the unicode character strings with N. You might want to try with N. Not sure what 16 means.|||The problem is that saving Arabic characters (like any unicode or 16 bit characters) requires a full 16 bit data path from begining to end. If any part of the path reverts back to 8 bit characters, then any character that isn't supported by the 16 bit to 8 bit translation will appear as a question mark.

Apparently something in the data path that handles the translation from Word documents larger than a given (roughly one page) threshold to a database column causes the data to pass through as 8 bit characters. The problem lies in isolating whatever that weak point is!

-PatP|||Masry,
BLOB values are not saved in row, but elsewhere in data file, unless you use sp_tableoption. The 16 in front of ntext column is only a pointer to where the actual ntext value is located.
But about your problem, I suggest to check the path that your data is traversing to be saved to DB. There might some variables or ... be used that cause the Unicode information be lost!
Regards,
Leila

Apply XSLT to an xml field in SQL Server 2005

Hi all,
Is it possible to apply an xslt template to an xml field in SQL Server 2005
by means of T-SQL? Or should I create a .NET assembly to perform this task?
Thanks in advance,
Alberto.Hello Alberto,

> Is it possible to apply an xslt template to an xml field in SQL Server
> 2005 by means of T-SQL? Or should I create a .NET assembly to perform
> this task?
Here you go: http://www.sqljunkies.com/WebLog/kt...l
t.aspx
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent.
Not only you answer my question, but also you give a good example.

Application services database in sql server 2000

I need some opinion about coping data tables from auto-generated ASP.NET database into SQL 2000 database.

I'm not sure I understand your question but, if you mean you want to create the application services database in sql server 2000 then you should read this article:

Creating the Application Services Database for SQL Server

Please let me know if that's what you needed.

|||

Thank you!

application rolls and ADO.Net

Hi.
Is it possible to get the benefit of application rolls with
the new ADO.Net drivers?
Do i have to call "sp_setapprole" for every connection?
best regards,
JanThe answer to both questions is yes.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Application roles have the same limitations in ADO.NET that they did
in ADO classic. See:
PRB: SQL Application Role Errors with OLE DB Resource Pooling
(Q229564)
http://support.microsoft.com/defaul...B;EN-US;Q229564
--Mary
On Mon, 14 Jun 2004 10:05:40 +0200, Jan Weingrtner <jweingart@.gmx.de>
wrote:

>Hi.
>Is it possible to get the benefit of application rolls with
>the new ADO.Net drivers?
>Do i have to call "sp_setapprole" for every connection?
>best regards,
>Jan|||Hi.
Thanks, for the infos.
I have a further question in relation to application rolls.
Does ADO.Net support the encryption of the password for the
application roll?
best regards,
Jan|||Take a look at using DPAPI for that:
d12.asp" target="_blank">http://msdn.microsoft.com/library/d...
d12.asp
- Stephen
"Jan Weing?rtner" wrote:

> Hi.
> Thanks, for the infos.
> I have a further question in relation to application rolls.
> Does ADO.Net support the encryption of the password for the
> application roll?
> best regards,
> Jan
>

Application Role

Hi there
I have a VB.NET application, that is accessing a SQL DB. I would like to
set it up to start using Application Roles, but am getting some strange
results, and would appreciate advice regarding Best Practices.
My VB.NET application is using SQL Helper class, which basically seems
to be using Connection Pooling, and creating a new connection for each
transaction (if none in the pool). I dont want to set the APplication
Role to be activated in every single call to the DB, as this will slow
down the application completely, but I would like to somehow implement
an Application Role.
I know that Application Roles dont use Connection Pooling, and I dont
mind the preformance hit there, as the application is mostly
single-user.
Also, does anyone know - if I set up an Application Role, can Any
application that has the ApplicationRole password get in and Activate
the Role, or is there a way to limit it somehow?
Thanks in advance
David Crone
*** Sent via Developersdex http://www.codecomments.com ***On Wed, 08 Jun 2005 04:56:51 -0700, David Crone wrote:

> My VB.NET application is using SQL Helper class, which basically seems
> to be using Connection Pooling, and creating a new connection for each
> transaction (if none in the pool). I dont want to set the APplication
> Role to be activated in every single call to the DB, as this will slow
> down the application completely, but I would like to somehow implement
> an Application Role.
I think you're going to have to give up the SQLHelper class (the Data
Access Application Block), or at least extend it / replace it with your own
class. Frankly I found the whole Patterns & Practices library to be bloated
and mostly useless ... come on, every connection I want to use has to store
its connection string in App.config? And I should only use app.config
through the classes? Ridiculous.
It sounds like your app would be happy with one SQLConnection. Try this:
Public Class SQLHelp
Public shared cxn as SQLConnection
Public shared ConnectionString as String = ...
Public shared Sub Init()
cxn = New SQLConnection(ConnectionString)
cxn.Open
dim pass as String = ...
dim cmd as String
sqlcmd = String.Format( _
"set_approle @.rolename='{0}', @.password='{1}'" , _
"MyRole", pass
cxn.Execute cmd
End Sub
Public shared Function ExecDataset(sqlcmd as String) as Dataset
dim ds as new Dataset
dim da as new SQLDataAdapter(sqlcmd, cxn)
da.Fill(ds)
return ds
End Function
End Module
That should get you started. It provides SQLHelp.Init() to open the
connection and set the app role, and SQLHelp.ExecDataset to run a SQL
command and return a dataset. Steal more code from the MS SQLHelper class
if you need it.

> Also, does anyone know - if I set up an Application Role, can Any
> application that has the ApplicationRole password get in and Activate
> the Role, or is there a way to limit it somehow?
The only limitation is that the application must first gain access to the
Database before it can call set_approle. In other words, a valid user in
the database has to open the connection - if Ross Presser isn't defined as
a user (or isin't a member of a windows group that is defined as a user,
etc.) then he can't run approle.
Best practice is usually to store the password encrypted in a file or
registry setting rather than hardcode it into the app, and to change it
periodically.

Application Login SQL Error

I created aASP.net 2.0 application usingC# on VS2005
The application access several database on a remoteSQL Server 2005

I recently added Login functionalties to the application, this created a MDF in the app_data folder.

Everything works fine on my local desk top...

I can access my remote SQL Server 2005

and the local MDF file works fine, I can create account, login and all that fun stuff

So I Published the site to my target server:

Which is the same server running the SQL Server 2005

The parts of the application that does not require login works fine, I can access the SQL server 2005 with ease..see data, update, everything

However when ever I try to login or create an account from the application(MDF file) I get this:

An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I can only assume that the problem is with the MDF file.

Can anyone point me in the right direction?

Thank you
Andre

This is often fixed by ensuring the Initial Catalog entry is in the connectionstring.

.....Initial Catalog=yourFileWithoutTheExt....

I just reread the error...actually, I think it's because you are trying to administer the DB from your PC, and it's not setup that way.

|||

Thank you for responding,

On my target server I do not have SQL Express but SQL server 2005 and the remote setting for that is correct.

I changed my application from using the local SQL Express MDF file to use the SQL server 2005 for membership and roles by running aspnet_sql.exe to set up the database.

And all is working.

Thanks again

Andre

Application Login and Integrated Security

Hello,

We're having a bit of a problem getting Integrated Security to work with a .Net 2.0 application and SQL 2005. While we're tweaking permissions on the SQL-side, we came across an account "Application Login" and wondered what its role is. First, our problem:

Currently, the users in the AD group get a connection error. This group is defined as follows at the instance level:

role: public

user mapping: to the database without any default schema

securables: none

status: grant and enabled

At the database security level:

general: none

securables: execute on all (100+) stored procedures

And we gave them "Execute" on the database itself.

A little background: we had detached and copied this database from one server to another. So we suspect that the Application Login may have been modified/corrupted, even though it appears to be identical between the original and the copied databases. So we redefined it on the copied DB to match the original.

Another group, which is defined as dbo on the database, has no problem at all connecting and running the application.

The Application Login has Execute permissions on all stored procedures and Delete, Insert, Select, Update, and View Definition on the ChangeLog table. It also has db_DataReader, db_DataWriter, and db_ddlAdmin roles associated with it.

Is there another SQL login required for initial connection to the database even though Integrated Security=SSPI is used in the connection string?

Does anyone see where we may be missing a security setting for the non-dbo user group to connect to the database?

Thanks very much for any suggestions, ideas ....

Cheers,

Tess

Okay, it looks like granting Execute to the database itself has resolved the connection issue.

Have a good day everyone!

application failover error

We currently have a system that uses sql server 2005 mirroring. In testing the application, we fail over the database in the middle of a 2.0 .NET web application. The failover partner is specified in the connection string.

The next request to the web server results in an error page popping up with the message "A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

This message only occurs for the first request to the web server, all subsequent requests sucessfully access the new failed over database.

Thanks for any suggestions.

I'm having the same problem and haven't yet found anything. I know that if you leave the app alone for at 1.5 minutes and then try to execute it again it will connect just fine. Some timeout is occurring that is then allowing the app to connect and I'm trying to find a way to force that timeout after a failover condition.

Let me know if you have any luck. :-)

|||This confused me also. From what I can tell, this is by

design. If you want the user to see no errors, the application

needs to trap the error and re-open the connection. The

documentation says

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Assume a client is successfully connected to the principal, and a database

mirroring failover (automatic, manual, or forced) occurs. The next time

the application attempts to use the connection, the ADO.NET or SQL Native

Client driver will detect that the connection to the old principal has failed,

and will automatically retry connecting to the new principal as specified in

the failover partner name.

This message talks more about it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=752968&SiteId=1|||

Could I get some feedback from a Microsoft person if this is supposed to be a "feature" or a "bug" so I could go back to the customer with an answer?

Thanks.

|||

Yes, this is expected behavior. Your application needs to handle these errors and retry the connection.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||

I'm not sure if that's true.

I'm catching the error using a TRY CATCH statement and once I try to connect again after catching the error I still am unable to connect. The only way I'll be able to connect is if I leave my app alone for about 60 seconds and then try to connect. At that point it will work just fine.

This is a sample of my code:

Dim constring As String = "Server=SQL01.eqa\Fantabet;Failover Partner=SQL02.eqa\Fantabet;" _
& "Database=test;" _
& "UID=FailTest2;PWD=test;Network=dbmssocn"

Using con As New SqlConnection(constring)

Using cmd As New SqlCommand
Try 'First TRY statement to catch errors when opening the connection
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter','" & con.DataSource & "')"
Try 'Second Try statement only for executing the SQL Command
cmd.ExecuteNonQuery()
Catch ex As SqlException
lblresult.Text = "Error"

Catch ex As SqlException
con.Close()
lblresult.Text = lblresult.Text & "<BR>Con.open error<BR>Error code = " & ex.ErrorCode & "<BR>Error Description = " & ex.Message
End Try

lblprimary.Text = "Current Primary Database = " & con.DataSource

con.Close()
con.Dispose()
End Using
End Using

So what my code does is that it tries to open the connection and if everything goes smooth a SQL command is executed but if an error is encountered while opening the connection object then the sub procedure finishes and changes the text of a label to "Error". The SQLConnection object is closed manually using a con.close but also by design since I'm using a "Using" statement.

Yet when I try to execute the procedure afterwards it still cannot connect. I must wait about 60 seconds to the procedure will work again.

|||

Matt,

Thanks for your response. My understanding based on Microsoft’s documentation, was that there were no modification required in the application code.

In the event of a fail-over, the sqlclient would try the partner database. In Explicit mode you can even provide the partner in the connection string.

Can you point me to MS documentation that supports your response?

Thank you

application failover error

We currently have a system that uses sql server 2005 mirroring. In testing the application, we fail over the database in the middle of a 2.0 .NET web application. The failover partner is specified in the connection string.

The next request to the web server results in an error page popping up with the message "A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

This message only occurs for the first request to the web server, all subsequent requests sucessfully access the new failed over database.

Thanks for any suggestions.

I'm having the same problem and haven't yet found anything. I know that if you leave the app alone for at 1.5 minutes and then try to execute it again it will connect just fine. Some timeout is occurring that is then allowing the app to connect and I'm trying to find a way to force that timeout after a failover condition.

Let me know if you have any luck. :-)

|||This confused me also. From what I can tell, this is by

design. If you want the user to see no errors, the application

needs to trap the error and re-open the connection. The

documentation says

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Assume a client is successfully connected to the principal, and a database

mirroring failover (automatic, manual, or forced) occurs. The next time

the application attempts to use the connection, the ADO.NET or SQL Native

Client driver will detect that the connection to the old principal has failed,

and will automatically retry connecting to the new principal as specified in

the failover partner name.

This message talks more about it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=752968&SiteId=1|||

Could I get some feedback from a Microsoft person if this is supposed to be a "feature" or a "bug" so I could go back to the customer with an answer?

Thanks.

|||

Yes, this is expected behavior. Your application needs to handle these errors and retry the connection.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||

I'm not sure if that's true.

I'm catching the error using a TRY CATCH statement and once I try to connect again after catching the error I still am unable to connect. The only way I'll be able to connect is if I leave my app alone for about 60 seconds and then try to connect. At that point it will work just fine.

This is a sample of my code:

Dim constring As String = "Server=SQL01.eqa\Fantabet;Failover Partner=SQL02.eqa\Fantabet;" _
& "Database=test;" _
& "UID=FailTest2;PWD=test;Network=dbmssocn"

Using con As New SqlConnection(constring)

Using cmd As New SqlCommand
Try 'First TRY statement to catch errors when opening the connection
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter','" & con.DataSource & "')"
Try 'Second Try statement only for executing the SQL Command
cmd.ExecuteNonQuery()
Catch ex As SqlException
lblresult.Text = "Error"

Catch ex As SqlException
con.Close()
lblresult.Text = lblresult.Text & "<BR>Con.open error<BR>Error code = " & ex.ErrorCode & "<BR>Error Description = " & ex.Message
End Try

lblprimary.Text = "Current Primary Database = " & con.DataSource

con.Close()
con.Dispose()
End Using
End Using

So what my code does is that it tries to open the connection and if everything goes smooth a SQL command is executed but if an error is encountered while opening the connection object then the sub procedure finishes and changes the text of a label to "Error". The SQLConnection object is closed manually using a con.close but also by design since I'm using a "Using" statement.

Yet when I try to execute the procedure afterwards it still cannot connect. I must wait about 60 seconds to the procedure will work again.

|||

Matt,

Thanks for your response. My understanding based on Microsoft’s documentation, was that there were no modification required in the application code.

In the event of a fail-over, the sqlclient would try the partner database. In Explicit mode you can even provide the partner in the connection string.

Can you point me to MS documentation that supports your response?

Thank you

Application - document locking

Hi !
I have problem with locking concept in my application (ADO.NET , C#).
Application has several documents which looks like Order.
My question is what and how should I lock those documents.
1.Lock document as a whole (Order + OrderLines) so another user can't work
on this document (pessimistic lock)?
1.1. How to implement this on SQL server ?
2.Should I use optimistic locking on Order and OrderLines ?
3.If I use optimistic locking on OrderLines should I each time update
timestamp of Order
and by each insert, update and read of OrderLine check whether somebody
has changed this Order and/or OrderLine which is both possible ?
Any thoughts are appreciated!"Marek" <marek@.home.puton.cz> wrote in message
news:%23OCPpamIGHA.3192@.TK2MSFTNGP10.phx.gbl...
> Hi !
>
> I have problem with locking concept in my application (ADO.NET , C#).
> Application has several documents which looks like Order.
> My question is what and how should I lock those documents.
>
> 1.Lock document as a whole (Order + OrderLines) so another user can't work
> on this document (pessimistic lock)?
> 1.1. How to implement this on SQL server ?
my advice: forget about #1. what #1 really means? either selecting the data
with (updlock,holdlock) or something similar, and keeping the transaction
open for indefinite amount of time (no, no, no, and no), or setting some
flag, marking that the document is being edited by a client, which is
somewhat better, but still leaves you with the housekeeping problems that
have to be dealt with from the application (and relying on application to
take care of things is generally not a good idea).

> 2.Should I use optimistic locking on Order and OrderLines ?
i would. and i do.

> 3.If I use optimistic locking on OrderLines should I each time update
> timestamp of Order
> and by each insert, update and read of OrderLine check whether somebody
> has changed this Order and/or OrderLine which is both possible ?
a timestamp column will be updated automatically each time data in the row
is updated, you just have to check whether it's value is same as what it was
when the row was read.
it's just my opinion, of course..
dean|||
> a timestamp column will be updated automatically each time data in the row
> is updated, you just have to check whether it's value is same as what it
> was when the row was read.
> it's just my opinion, of course..
> dean
Tnx Dean, but I believe that you answer is over simplified
and does not address all the issues ...
What should happened when orderline is updated ?
should order also be touched (so timestamp can change)
Is this sequence right when updating Orderline ?
1.Get TimeStamp of Order
2. Get TimeStamp of OrderLine
3. User changes orderline
4. Check if Order was changed meanwhile
4. 1 If yes there is a conflict since somebody changed order while I was
updating OrderLine
5. Check if OrderLine was changed
5.1 If yes there is a conflict since somebody changed OrderLine line while I
was updating OrderLine
6. Update OrderLine
7. Touch Order
or there is not need to check for Order Timestamp
when updating OrderLine in this case|||If you acquire locks in the correct order and hold them until the end of the
transaction, no other user will be able to change any rows that you have
locked until the transaction is either committed or rolled back.
ML
http://milambda.blogspot.com/|||hi, marek
yes, it was somewhat simplified, i agree. the actual implementation depends
on the actual business requirements (eg, is it ok that two people work with
different lineitems of the same document?), so this sequence could be
right - provided that everything from (and including) #4 is isolated inside
a transaction.
dean
"Marek" <marek@.home.puton.cz> wrote in message
news:uQlLHnnIGHA.3060@.TK2MSFTNGP10.phx.gbl...
>
> Tnx Dean, but I believe that you answer is over simplified
> and does not address all the issues ...
> What should happened when orderline is updated ?
> should order also be touched (so timestamp can change)
> Is this sequence right when updating Orderline ?
> 1.Get TimeStamp of Order
> 2. Get TimeStamp of OrderLine
> 3. User changes orderline
> 4. Check if Order was changed meanwhile
> 4. 1 If yes there is a conflict since somebody changed order while I was
> updating OrderLine
> 5. Check if OrderLine was changed
> 5.1 If yes there is a conflict since somebody changed OrderLine line while
> I was updating OrderLine
> 6. Update OrderLine
> 7. Touch Order
> or there is not need to check for Order Timestamp
> when updating OrderLine in this case
>
>|||Well it's interesting how would you solve the problem
when two people must not work with 2 different Orderlines
of the same Order ? Should we use pessimistic locking than ?
Another question that arises when considering scenario below is
how many roundtrips to SQL requires such a solution ?
Steps 1,2 - one roundtrip
Steps 4,5 - one roundtrip
Steps 6,7 - one roundtrip
Can this be smaller number than 3 ?
> yes, it was somewhat simplified, i agree. the actual implementation
> depends on the actual business requirements (eg, is it ok that two people
> work with different lineitems of the same document?), so this sequence
> could be right - provided that everything from (and including) #4 is
> isolated inside a transaction.
>|||not more than 2 roundtrips - 1&2 is one, 4-7 the other one, inside a stored
procedure, inside a transaction.
dean
"Marek" <marek@.home.puton.cz> wrote in message
news:OIqObNoIGHA.524@.TK2MSFTNGP09.phx.gbl...
> Well it's interesting how would you solve the problem
> when two people must not work with 2 different Orderlines
> of the same Order ? Should we use pessimistic locking than ?
> Another question that arises when considering scenario below is
> how many roundtrips to SQL requires such a solution ?
> Steps 1,2 - one roundtrip
> Steps 4,5 - one roundtrip
> Steps 6,7 - one roundtrip
> Can this be smaller number than 3 ?
>
>
>|||Tnx. Dean
That looks like a reasonable solution
with minimum overhead on SQL server side.
And what would be theoretical scenario:
1. when two people must not work with 2 different Orderlines of the same
order
2. two people must work not with the same Order at all ?
Does this require pessimistic lock and an open connection during
order/orderline update or is there better simpler solution ?
I have two solutions on my mind:
1. Lock the whole Order and child Orderlines with hold lock so nobody
can update those records.
2. Create LockingTable (RecordId, UserId) and hold connection open
on this record in LockingTable so if program crashes
SQL server will release lock. Before reading record With 'SELECT (NOLOCK)'
read
wheather record is locked and if it is tell the user who locks the record.
But both solutions require open connection ?
I'm I missing something ?
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:uxNdmVoIGHA.1836@.TK2MSFTNGP11.phx.gbl...
> not more than 2 roundtrips - 1&2 is one, 4-7 the other one, inside a
> stored procedure, inside a transaction.
> dean
> "Marek" <marek@.home.puton.cz> wrote in message
> news:OIqObNoIGHA.524@.TK2MSFTNGP09.phx.gbl...
>|||hi again,
as i see it, this whole optimistic vs pessimistic thing is really a business
problem. in most cases, unless you're dealing with some very chaotic
organization, it is well known who can do what with what data. it is not
unusual to have an owner for the document, and only that person is permitted
to make updates to the specific document. this type of scenario (involving
update conflicts) is rather unlikely in the real world, imo. sometimes it's
practically impossible, sometimes it's desirable, even required (why not let
several people do the data entry on an inventory list?). to answer the
question 'how to deal with it?' is really up to your customers. ask them, is
it possible at all? how often could it happen? how severe will the
consequences be? most of them, in my experience, could live with an update
conflict here and there - as long as you can tell them what happened, and
who was competing with them. auditing is here much more important than
locking.
technically speaking, would you really want to let the user opet a
transaction, select the data and place and hold update lock on the data,
then keep the data locked for nobody knows how long (he might go to lunch,
or go home or whatever - you have no control over it), and eventually at
some point in time decide to end the transaction, with or without any
change? first of all, transactions should be short-lived - started as late
as possible, and ended as soon as possible. they eat up resorces on server,
and keep others from accessing data. i can't think of a real-world situation
that would justify such a scenario.
you have come to a rather good practical solution yourself (in your previous
post) - why not use it?
dean
"Marek" <marek@.home.puton.cz> wrote in message
news:eXoRwooIGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Tnx. Dean
> That looks like a reasonable solution
> with minimum overhead on SQL server side.
> And what would be theoretical scenario:
> 1. when two people must not work with 2 different Orderlines of the same
> order
> 2. two people must work not with the same Order at all ?
> Does this require pessimistic lock and an open connection during
> order/orderline update or is there better simpler solution ?
> I have two solutions on my mind:
> 1. Lock the whole Order and child Orderlines with hold lock so nobody
> can update those records.
> 2. Create LockingTable (RecordId, UserId) and hold connection open
> on this record in LockingTable so if program crashes
> SQL server will release lock. Before reading record With 'SELECT (NOLOCK)'
> read
> wheather record is locked and if it is tell the user who locks the record.
> But both solutions require open connection ?
> I'm I missing something ?
>
>
> "Dean" <dvitner@.nospam.gmail.com> wrote in message
> news:uxNdmVoIGHA.1836@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Dean !
I don't have such situation , but I was just thinking aloud.
Currently in my firm there is an old proprietary system which uses
pessimistic
locking and it works fine, so I was thinking how this can be implemented on
SQL server.
Since the whole architecture of ADO.NET does not encourage pessimistic
locking
and there is no business need to support it I will definitely use solution
to that we came up in previous posts. Thanky you again for a thoughtful
posts
that have broadened my "data access" sights !
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:unvH%23hqIGHA.2708@.tk2msftngp13.phx.gbl...
> hi again,
> as i see it, this whole optimistic vs pessimistic thing is really a
> business problem. in most cases, unless you're dealing with some very
> chaotic organization, it is well known who can do what with what data. it
> is not unusual to have an owner for the document, and only that person is
> permitted to make updates to the specific document. this type of scenario
> (involving update conflicts) is rather unlikely in the real world, imo.
> sometimes it's practically impossible, sometimes it's desirable, even
> required (why not let several people do the data entry on an inventory
> list?). to answer the question 'how to deal with it?' is really up to your
> customers. ask them, is it possible at all? how often could it happen? how
> severe will the consequences be? most of them, in my experience, could
> live with an update conflict here and there - as long as you can tell them
> what happened, and who was competing with them. auditing is here much more
> important than locking.
> technically speaking, would you really want to let the user opet a
> transaction, select the data and place and hold update lock on the data,
> then keep the data locked for nobody knows how long (he might go to lunch,
> or go home or whatever - you have no control over it), and eventually at
> some point in time decide to end the transaction, with or without any
> change? first of all, transactions should be short-lived - started as late
> as possible, and ended as soon as possible. they eat up resorces on
> server, and keep others from accessing data. i can't think of a real-world
> situation that would justify such a scenario.
> you have come to a rather good practical solution yourself (in your
> previous post) - why not use it?
> dean
> "Marek" <marek@.home.puton.cz> wrote in message
> news:eXoRwooIGHA.3144@.TK2MSFTNGP11.phx.gbl...
>

Anyone Using 2005?

Since "The Release" 4 ws ago I've been monitoring the various Visual
Studio/.NET NGs to get a sampling of the issues people are running into. In
this SQL Server NG I don't see many questions at all about SQL Server 2005 -
issues, migration, etc... very little of anything 2005-specific... while
other groups (e.g., ASP.NET) have a fairly large percentage of 2005-specific
questions.
So my question... are any of you switching to SQL Server 2005? Or is the DBA
community, from your points of view, taking a wait and see approach? Or are
you all happily migrated with no issues? Or?
Thanks!Well it comes down to $$$.
My current client would like to upgrade to Visual Studio 2005 team suite and
SQL Server 2005 but MSDN Universal is no longer "universal" as Microsoft
wants an additional $1200 to obtain the Visual Studio team suite.
If the company is going to upgrade at all, it has to be team suite because
that has the integrated developer tools, testing, etc. An upgrade to Visual
Studio 2005 professional wouldn't provide enough of an upgrade to justify th
e
upgrade, retraining, retesting the entire Windows application (over 150
DLL's), upgrading all our clients to .NET Framework 2.0, upgrading
documentation, etc.
At this point, developers continue to request the upgrade but it falls on
deaf ears. Without team suite, we don't have enough ROI for a Windows
application.
Just my experience,
Joe
"Jeff" wrote:

> Since "The Release" 4 ws ago I've been monitoring the various Visual
> Studio/.NET NGs to get a sampling of the issues people are running into. I
n
> this SQL Server NG I don't see many questions at all about SQL Server 2005
-
> issues, migration, etc... very little of anything 2005-specific... while
> other groups (e.g., ASP.NET) have a fairly large percentage of 2005-specif
ic
> questions.
> So my question... are any of you switching to SQL Server 2005? Or is the D
BA
> community, from your points of view, taking a wait and see approach? Or ar
e
> you all happily migrated with no issues? Or?
> Thanks!
>
>|||Most of the questions here revolve around SQL queries, data modeling, etc.
and are not 2000 vs. 2005 specific.
"Jeff" <Jeff@.NoSpam.com> wrote in message
news:%23Urq4Wu%23FHA.3568@.TK2MSFTNGP09.phx.gbl...
> Since "The Release" 4 ws ago I've been monitoring the various Visual
> Studio/.NET NGs to get a sampling of the issues people are running into.
> In this SQL Server NG I don't see many questions at all about SQL Server
> 2005 - issues, migration, etc... very little of anything 2005-specific...
> while other groups (e.g., ASP.NET) have a fairly large percentage of
> 2005-specific questions.
> So my question... are any of you switching to SQL Server 2005? Or is the
> DBA community, from your points of view, taking a wait and see approach?
> Or are you all happily migrated with no issues? Or?
> Thanks!
>|||Yes - and that's why I asked! ... couldn't figure it out just from the
questions posted...
Anyone else using 2005?
"JT" <someone@.microsoft.com> wrote in message
news:uGHR1Lz%23FHA.984@.tk2msftngp13.phx.gbl...
> Most of the questions here revolve around SQL queries, data modeling, etc.
> and are not 2000 vs. 2005 specific.
> "Jeff" <Jeff@.NoSpam.com> wrote in message
> news:%23Urq4Wu%23FHA.3568@.TK2MSFTNGP09.phx.gbl...
>|||Yes, we have some serious Data Warehouses running on SQL Server 2005.
I know of 1 airline web reservation system that is running it already too.
There are 1'000's of installations out there running in production already.
Microsoft did a lot of pre-release training so the number of questions are
expected to be low.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff" <Jeff@.NoSpam.com> wrote in message
news:OCdgU65%23FHA.4088@.TK2MSFTNGP09.phx.gbl...
> Yes - and that's why I asked! ... couldn't figure it out just from the
> questions posted...
> Anyone else using 2005?
>
> "JT" <someone@.microsoft.com> wrote in message
> news:uGHR1Lz%23FHA.984@.tk2msftngp13.phx.gbl...
>|||Here you can find case studies of companies that have deployed SQL Server
2005.
http://www.microsoft.com/sql/prodin...es/default.mspx
"Jeff" <Jeff@.NoSpam.com> wrote in message
news:%23Urq4Wu%23FHA.3568@.TK2MSFTNGP09.phx.gbl...
> Since "The Release" 4 ws ago I've been monitoring the various Visual
> Studio/.NET NGs to get a sampling of the issues people are running into.
> In this SQL Server NG I don't see many questions at all about SQL Server
> 2005 - issues, migration, etc... very little of anything 2005-specific...
> while other groups (e.g., ASP.NET) have a fairly large percentage of
> 2005-specific questions.
> So my question... are any of you switching to SQL Server 2005? Or is the
> DBA community, from your points of view, taking a wait and see approach?
> Or are you all happily migrated with no issues? Or?
> Thanks!
>