configure IIS in SQLXML support

Hi,
I have to create a B2B application in c#.Net and use SQLXML and use
Annotated schemas to get output as XML.
I have no background in any of these. First of all i have to set up
the environment. So I installed win XP pro ,SQL Server 2000 and tried
to configure IIS in SQLXML but when I give the physical path to the
virtual directory say Northwind, as C:\Inetpub\Wwwroot\Northwind,
it says the physical path is not valid. what is the problem i am
having? What should i do? when I navigate in browse and look for the
folders Inetpub and Wwwroot I see nothing there other than some
picture Icons. Can someone help me please...
thanks,
-shubha
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/XML-configur...ict235374.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=816596
Did you create the Northwind directory under wwwroot?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"vanilla" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_816596_2a8025c990fe19afdddaea35d5531d23@.dbf orumz.com...
> Hi,
> I have to create a B2B application in c#.Net and use SQLXML and use
> Annotated schemas to get output as XML.
> I have no background in any of these. First of all i have to set up
> the environment. So I installed win XP pro ,SQL Server 2000 and tried
> to configure IIS in SQLXML but when I give the physical path to the
> virtual directory say Northwind, as C:\Inetpub\Wwwroot\Northwind,
> it says the physical path is not valid. what is the problem i am
> having? What should i do? when I navigate in browse and look for the
> folders Inetpub and Wwwroot I see nothing there other than some
> picture Icons. Can someone help me please...
> thanks,
> -shubha
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/XML-configur...ict235374.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=816596
sqlsql

Configuration of RSreporthost

Hi,
having used the most of my day trying to do this - i have to ask.
Is there no way to config this tool to get the same permissions as the vs.net preview?
I have a strong name assembly who read the System.Environment.username - and it won't run in the rsreporthost.
Please help me out here..
I've got it right now...
I had a restart (mentally) and now it works.
For the record, I've added a permissionset and a codegroup in the policy config file - and added permission asserts to the code and it works perfect.
sqlsql

Configuration Help

I am running 2005 reporting services and 2005 visual studio. I have a
c# .net web application located in the default web directory in IIS
that uses forms authentication, but that authentication is to active
directory using an ldap call inside the code. We set it up that way so
the user can log in to a normal page instead of the windows pop-up
login. I have reporting services running inside the web site directory.
It is setup to use windows authentication. I have the user login into
the web site and then when they go to run a report a windows login box
appears and they have to login again. Does anyone know how I can
authenticate to reporting services in the background so the user does
not see the windows pop-up login.
Thanks in advance,
JasonWrite a FormsAuthentication extension to MSRS.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
When you app hits LDAP, you can also authenticate against MSRS and grab
the auth cookies for MSRS. Your other options is to hijacks the
ASP.NET pipeline when a user requests an MSRS report and perform the
auth on demand.
Andy Pottersqlsql

Configuration for SQL 2000

Hi,

What configuration steps should I do to enable my .NET web application to access my SQL 2000 database? I have SQL Server 2000 SP3, SQL 2005 Express, SQL 2005 and VS 2005 installed on the same machine. Is it anything to do with the NT Authority\Network Service or ASPNET account that I need to allow access to or what? Kinda lost here... been banging my server for the past week.

Cheers!

There are several options. Do you want to run the SQL Server in Mixed Mode ? DO you want to use impersonation ? DO you want to impersoante the user at the SQL Server ?

-Jens Suessmeyer.

|||

That is also my next question. What I would like is for annonymous access to view the data taken from the database. Only authorized personnel can log in through Forms Authentication to Insert, Update or Delete the database.

I believe this is a simple implementation but I can't seem to get the configuration right. The database works if I put it as a local database but that defeats the purpose of the implementation.

I got this error which I believe is very common

What can I set in IIS 6.0, Compiter Management and SQL 2000?:

Server Error in '/' Application.

---

Runtime Error

Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".

<!-- Web.Config Configuration File --> <configuration> <system.web> <customErrors mode="Off"/> </system.web> </configuration>

Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.

<!-- Web.Config Configuration File --> <configuration> <system.web> <customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/> </system.web> </configuration>

|||

Anyway after some tweaking here and there, I managed to get it to work properly. Added NT Authority\Local Service account to SQL 2000 User Login, which will categorize it as the public grp. After that checking the tables this group can operate Select instruction on.

This is probably not the conventional way, so I would like to know what the generic workable implementation should be (otherwise called the industrial standard).

Cheers!

Configuration Error in roleManager in security VSS

We have a project that is in a server with windows 2000 (SQL Server 2005 and .Net 2005) And the other developer has a windows XP machine ( .Net 2005) , We are using the VSSC for the project , when the other developer run the application , the following error is show :

Server Error in '/XXX' Application.

---

Configuration Error

Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load type 'XXXSecurity.RoleSql'.

Source Error:

Line 27: <roleManager enabled="true" defaultProvider="SqlRoleManager" cacheRolesInCookie="true" cookieTimeout="30" cookieRequireSSL="false" createPersistentCookie="true"> Line 28: <providers> Line 29: <add name="SqlRoleManager" type="XXXSecurity.RoleSql" connectionStringName="MySqlConnection" applicationName="XXX"/> Line 30: </providers> Line 31: </roleManager>

Source File: C:\Documents and Settings\YYYY\My Documents\Visual Studio 2005\Projects\XXX\RRRR\KKKKK\web.config Line: 29

Where XXX is the name of application.

SomeOne Know How Help Me Please?

This error has nothing to do with SourceSafe. Moving to SQL groups, perhaps they can help...

Alin

|||

The error seems to come from the VS XML parser. I don't see anything related to SQL Server.

Thanks
Laurentiu

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.
Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>
|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas

"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.
sqlsql

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.

Conditional join?

I have a dropdown box in a .net app that I am populating from a couple
tables.
One is a salesrep table, with the sales rep code and a name in it.
The other table is a table with a sold to sales rep code and a ship to
sales rep code - which may not always be the same in both these columns.
One of those two columns will be in the salesrep table.
Is there a way to do a join on the first table from the second?
Basically, I want to pick out a statement like:
select repname from salesrep_table
inner join sourcetable on
sourcetable.soldtorepcode = salesrep_table.repcode or
sourcetable.shiptorepcode = salesrep_table.repcode
In other words, I want to show this repname in a box when the repcode
that is selected is in either the soldto repcode column, or the shipto
repcode table.
Wiil this work? If not, how would you go about doing this?
Any help would be appreciated.
BC>> I have a dropdown box in a .net app that I am populating from a couple ta
bles. <<
This is a database group and we do not care about the front end. The
basic principle of a tiered architecture is that display and input are
done in the front end and never in the back end. This a more basic
programming principle than just SQL and RDBMS.
This is a major screw up. There is no LOGICAL difference in these
guys. A sales rep is a sales rep; if you want to flag with a role,
then that goes into a column, not a separate table.
Look up "attribute splitting" as a design error.|||Hi Blasting Cap,
This help?
select repname
from salesrep_table as st
inner join sourcetable t on st.repcode = coalesce( t.soldtorepcode,
t.shiptorepcode )
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Blasting Cap" <goober@.christian.net> wrote in message
news:OiYTzAWmGHA.1568@.TK2MSFTNGP05.phx.gbl...
>I have a dropdown box in a .net app that I am populating from a couple
>tables.
> One is a salesrep table, with the sales rep code and a name in it.
> The other table is a table with a sold to sales rep code and a ship to
> sales rep code - which may not always be the same in both these columns.
> One of those two columns will be in the salesrep table.
> Is there a way to do a join on the first table from the second?
> Basically, I want to pick out a statement like:
> select repname from salesrep_table
> inner join sourcetable on
> sourcetable.soldtorepcode = salesrep_table.repcode or
> sourcetable.shiptorepcode = salesrep_table.repcode
> In other words, I want to show this repname in a box when the repcode that
> is selected is in either the soldto repcode column, or the shipto repcode
> table.
> Wiil this work? If not, how would you go about doing this?
> Any help would be appreciated.
> BC|||> This is a database group and we do not care about the front end. The
> basic principle of a tiered architecture is that display and input are
> done in the front end and never in the back end. This a more basic
> programming principle than just SQL and RDBMS.
Check the group title AGAIN -> MICROSOFT.SQLSERVER.PROGRAMMING
SQL Server is not just a database rather RDBMS - its a data processing
engine with many different features other than just SQL and store/retrieve.
If you don't like the posts then keep your gob shut and don't answer them!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151366508.537061.16560@.m73g2000cwd.googlegroups.com...
> This is a database group and we do not care about the front end. The
> basic principle of a tiered architecture is that display and input are
> done in the front end and never in the back end. This a more basic
> programming principle than just SQL and RDBMS.
>
> This is a major screw up. There is no LOGICAL difference in these
> guys. A sales rep is a sales rep; if you want to flag with a role,
> then that goes into a column, not a separate table.
> Look up "attribute splitting" as a design error.
>|||On 26 Jun 2006 17:01:48 -0700, "--CELKO--" <jcelko212@.earthlink.net>
wrote:

>This is a database group and we do not care about the front end. The
>basic principle of a tiered architecture is that display and input are
>done in the front end and never in the back end. This a more basic
>programming principle than just SQL and RDBMS.
The questions was not about how to program the front end, it was
simply about database retrieval. Mentioning the front end when
describing the query requirement and asking for help with the query is
not a problem.

>This is a major screw up. There is no LOGICAL difference in these
>guys. A sales rep is a sales rep; if you want to flag with a role,
>then that goes into a column, not a separate table.
>Look up "attribute splitting" as a design error.
Nonsense. This exact configuration occured where I worked, and was an
important BUSINESS REQUIREMENT. Surely you remember that the database
must model the business? The salesman who walked into the customer's
headquarters and made a sale that resulted in orders going to hundreds
of stores received credit for the sale. However, each store (shipto)
was also associated with a local salesman. Sales reporting required
tracking BOTH associations, the appropriate one chosen for any given
report.
Roy Harvey
Beacon Falls, CT|||Tony:
Thanks for the help..
It does what it is supposed to do - but not exactly what I want.
The table structure is sort of like this:
Cust No SoldtoRep ShiptoRep SoldToGrp ShiptoGrp
Cust1 Rep1 Rep1 Grp1 Grp1
Cust2 Rep1 Rep2 Grp1 Grp2
Cust3 Rep1 Rep3 Grp1 Grp3
Cust4 Rep4 Rep4 Grp2 Grp2
Because Coalesce returns the first non-null value, it returns Rep1 in
the query when I actually want Reps 1, 2 & 3 to show up. I am going to
use this same thing when determining which groups I want to show up, too.
I believe if I reverse the columns (shipto first, soldto second), it may
do exactly what I wanted.
Many many thanks for your succinct help.
BC

> Hi Blasting Cap,
> This help?
> select repname
> from salesrep_table as st
> inner join sourcetable t on st.repcode = coalesce( t.soldtorepcode,
> t.shiptorepcode )
> Tony.
>|||Hi BC,
If you get stuck just post another thread - good luck.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Blasting Cap" <goober@.christian.net> wrote in message
news:O3esDoemGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Tony:
> Thanks for the help..
> It does what it is supposed to do - but not exactly what I want.
> The table structure is sort of like this:
> Cust No SoldtoRep ShiptoRep SoldToGrp ShiptoGrp
> Cust1 Rep1 Rep1 Grp1 Grp1
> Cust2 Rep1 Rep2 Grp1 Grp2
> Cust3 Rep1 Rep3 Grp1 Grp3
> Cust4 Rep4 Rep4 Grp2 Grp2
> Because Coalesce returns the first non-null value, it returns Rep1 in the
> query when I actually want Reps 1, 2 & 3 to show up. I am going to use
> this same thing when determining which groups I want to show up, too.
> I believe if I reverse the columns (shipto first, soldto second), it may
> do exactly what I wanted.
> Many many thanks for your succinct help.
> BC
>
>

Conditional Join

Hello,
I have the following problem...
I am musing ADO.NET with SQL Server. I have a table that stores events
that are linked to different tables.. for example, lets suppose that I
have the following tables : Airplanes, Cars, Trains. Each table is
different, however, events produced by any of these are stored on a
single Event Table with the following fields:
EventID, OwnerTable, OwnerID, EventTime, Description.
Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
unique ID within the given table. I now want to create a SELECT
statement that shows all events and joins the particular row to the
correct record based on the OwnerTable and OwnerID to show common fields
among all. For example, assume that all three tables have a COLOR
field, the resulting query should produce the following:
Event Table
Event ID: 1
OwnerTable: Airplanes
OwnerID: 1
Color : Red ( This is the COLOR field on the Airplanes table with
AirplaneID = 1)
Event Table
Event ID: 2
OwnerTable: Trains
OwnerID: 1
Color : Blue ( This is the COLOR field on the Trains table with
TrainID = 1)
Thanks,
Jeronimo BertranHi J,
As per the info you have porvided I have created tables and inserted the
data.
The query you require is at the end.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
go
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000),
OwnerID Int, EventTime DateTime, Description Varchar(1000))
go
Insert Into plane values ('Blue')
go
Insert Into plane values ('Blue1')
go
Insert Into plane values ('Blue2')
go
Insert Into car values ('Red')
go
Insert Into car values ('Red1')
go
Insert Into car values ('Red2')
go
Insert Into train values ('Yellow')
go
Insert Into train values ('Yellow1')
go
Insert Into train values ('Yellow2')
go
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
go
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
go
--This is your query
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem
Thanks,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:

> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Hi Jeronimo Bertran,
As per the info you have provided, I have created table and populated them
with data. The query you are looking for is at end. I am giving you all the
scripts so that there is no misinterpertation.
Create table plane (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table car (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table train (objectID Int Primary Key Identity,Color Varchar(1000))
GO
Create table Events (EventID Int Primary Key Identity, OwnerTable
Varchar(1000), OwnerID Int, EventTime DateTime, Description Varchar(1000))
GO
Insert Into plane values ('Blue')
GO
Insert Into plane values ('Blue1')
GO
Insert Into plane values ('Blue2')
GO
Insert Into car values ('Red')
GO
Insert Into car values ('Red1')
GO
Insert Into car values ('Red2')
GO
Insert Into train values ('Yellow')
GO
Insert Into train values ('Yellow1')
GO
Insert Into train values ('Yellow2')
GO
Insert into Events Select 'Plane',ObjectID,GetDate(),'Planes has added this'
from plane
GO
Insert into Events
Select 'Train',ObjectID,GetDate(),'Trains has added this' from train
Union
Select 'Car',ObjectID,GetDate(),'Cars has added this' from Car
GO
--This is the query you are lookin for
Select Events.* , Case Events.OwnerTable
When 'Plane' Then Plane.Color
When 'Train' Then Train.Color
When 'Car' Then Car.Color
Else '{Blank}'
End
From Events Left Outer Join Plane on Events.OwnerId = Plane.ObjectID And
Events.OwnerTable = 'Plane'
Left Outer Join Train on Events.OwnerId = Train.ObjectID And
Events.OwnerTable = 'Train'
Left Outer Join Car on Events.OwnerId = Car.ObjectID And Events.OwnerTable
= 'Car'
Please respond if it solves your problem.
Regard ,
Vishal Khajuria
Sungard SCT India
"Jeronimo Bertran" wrote:

> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran
>|||Jeronimo
SELECT <column lists> FROM Airplanes A JOIN Events E
ON A.EventId=E.EventId AND A.OwnerID=E.OwnerID
If it does not help please post DDL+ sample data + expected result
"Jeronimo Bertran" <jeronimo.bertran@.newsgroup.nospam> wrote in message
news:eexdtw7FFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the following problem...
> I am musing ADO.NET with SQL Server. I have a table that stores events
> that are linked to different tables.. for example, lets suppose that I
> have the following tables : Airplanes, Cars, Trains. Each table is
> different, however, events produced by any of these are stored on a
> single Event Table with the following fields:
> EventID, OwnerTable, OwnerID, EventTime, Description.
> Where OwnerTable might be Airplanes, Cars or Trains and OwnerID is the
> unique ID within the given table. I now want to create a SELECT
> statement that shows all events and joins the particular row to the
> correct record based on the OwnerTable and OwnerID to show common fields
> among all. For example, assume that all three tables have a COLOR
> field, the resulting query should produce the following:
> Event Table
> Event ID: 1
> OwnerTable: Airplanes
> OwnerID: 1
> Color : Red ( This is the COLOR field on the Airplanes table with
> AirplaneID = 1)
> Event Table
> Event ID: 2
> OwnerTable: Trains
> OwnerID: 1
> Color : Blue ( This is the COLOR field on the Trains table with
> TrainID = 1)
> Thanks,
> Jeronimo Bertran|||Try this:
SELECT E.eventid, E.ownertable, E.ownerid,
COALESCE(A.color, T.color) AS color
FROM Events AS E
LEFT JOIN AirPlanes AS A
ON E.ownerid = A.airplaneid
AND E.ownertable = 'Airplanes'
LEFT JOIN Trains AS T
ON E.ownerid = T.trainid
AND E.ownertable = 'Trains'
Alternatively, why not put those common columns in a single table
across all the types of transport and in the separate tables just have
columns that are specific to that subtype - that would greatly simplify
this type of query.
If Event is some kind of system-maintained audit trail then its maybe
reasonable to hold the applicable table name in there but otherwise I
would suggest that the table name is probably a poor way to identify
the entity. Don't you have identifying codes for the types "Airplanes",
"Trains", etc? In general its best not to mix data and metadata in a
table - doing so suggests that there may be something missing from the
data model.
David Portas
SQL Server MVP
--|||Thanks David,
The COALESCE will do the job. Yes I do have identifying codes for the
different types whcih I can use instead of the table name.|||Thanks Vishal,
Especially for being so thorough. Yor implementation solved my problem.

conditional formatting - noob question

Hello,

I do not know vb. How can I turn the below idea into VB.NET and embed in a table object in RS?

IF Fields!someField.Value LIKE "%string%" THEN Fields!someField.Value

.. the goal is to only include values that contain the string, and exclude all other values from the table.

TYIA...You can try some filtering on the Table - right click on table - filters - add the field with a Like filter.|||

Yes, you should filter on the table.
Note: Like filters work with * instead of %. E.g. ="abc*"
-- Robert

Conditional compile the connection string in datasets

I need to find a way to useconditional compilation option to change the connection string in ado.net datasets 2.0.

Debug > connection 1 : test server
Release > connection 2 : real server

How is it possible ?This is often accomplished by storing the connection string in the web.config file, and using different web.config files on your test and real servers.|||

This is what I do in global.asax.vb:

Sub Application_Start(ByVal senderAsObject,ByVal eAs EventArgs)' Fires when the application is startedIf System.Environment.MachineName ="{Your production machine name here}"Then

Application(

"IsProduction") =TrueElse

Application(

"IsProduction") =FalseEndIfDim configAs Configuration

config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(

"~")Dim cssAs ConnectionStringsSection

css = config.ConnectionStrings

Dim settingAs ConnectionStringSettingsDim fUpdateAsBoolean =FalseDim cAsNew ConnectionStringSettingsCollectionForEach settingIn css.ConnectionStrings

c.Add(setting)

NextForEach settingIn cIf (Left(setting.Name, 5).ToUpper ="TEST-"AndNot Application("IsProduction"))Or _

(Left(setting.Name, 5).ToUpper =

"PROD-"And Application("IsProduction"))ThenIf css.ConnectionStrings(setting.Name.Substring(5))IsNothingThen

css.ConnectionStrings.Add(

New ConnectionStringSettings(setting.Name.Substring(5), setting.ConnectionString, setting.ProviderName))

fUpdate =

TrueElseIf css.ConnectionStrings(setting.Name.Substring(5)).ConnectionString <> setting.ConnectionStringThen

css.ConnectionStrings(setting.Name.Substring(5)).ConnectionString = setting.ConnectionString

fUpdate =

TrueEndIfEndIfEndIfNext' Make sure debug is turned off when running on the production serverDim xAs System.Web.Configuration.CompilationSection = config.GetSection("system.web/compilation")If x.DebugAnd Application("IsProduction")Then

x.Debug =

False

fUpdate =

TrueEndIfIf fUpdateThen

config.Save()

EndIfEndSub

Then I'll create connection strings like "TEST-MyConnection" and "PROD-MyConnection", etc. In code (or from data controls), I'll reference them as "MyConnection". When the application starts up, it'll update the web.config with the correct connection string to use on the machine it's running (and possibly restarting the application).

|||thanks a lot

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of the
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
Todd
Can you post the trigger?
AMB
"pralnwuf" wrote:

> I have a table that I am auditing by having a trigger insert the Deleted row
> to an audit table. .Net is calling the same stored procedure to update the
> same row in the base table 4 times. The stored procedure subtracts a passed
> in value from a column in the base table. After the code runs, the value in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger to
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd
|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of th
e
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
ToddCan you post the trigger?
AMB
"pralnwuf" wrote:

> I have a table that I am auditing by having a trigger insert the Deleted r
ow
> to an audit table. .Net is calling the same stored procedure to update th
e
> same row in the base table 4 times. The stored procedure subtracts a pass
ed
> in value from a column in the base table. After the code runs, the value
in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of
the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger t
o
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],
[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[Tota
lAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:
>

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of the
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
ToddCan you post the trigger?
AMB
"pralnwuf" wrote:
> I have a table that I am auditing by having a trigger insert the Deleted row
> to an audit table. .Net is calling the same stored procedure to update the
> same row in the base table 4 times. The stored procedure subtracts a passed
> in value from a column in the base table. After the code runs, the value in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger to
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:
> > I have a table that I am auditing by having a trigger insert the Deleted row
> > to an audit table. .Net is calling the same stored procedure to update the
> > same row in the base table 4 times. The stored procedure subtracts a passed
> > in value from a column in the base table. After the code runs, the value in
> > the base table is correct, but the audit rows appear to show the the first
> > update subtracted first two amounts. The other weird thing is the time
> > stamp, which is generated from a getdate() is exactly the same for two of the
> > rows.
> >
> > How tightly is the trigger code tied to the code that causes the trigger to
> > fire? We have tried playing with isolation levels on the .Net transaction
> > and this hasn't helped. Have a tripped on a bug, or am I doing something
> > wrong. Thanks for the help.
> >
> > Todd

concurrent connection limits

I'm new using SQL 2005 Express

I'm planning to use SQL Server Express 2005 for a asp net web application for a video rental store.

Is there a concurrent user conection limit that I should care?

TIA

Gerardo

You can find more information here.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Concurrency in asp.net

Hi everybody,

I need to understand how concurrency excatlywork in asp.net. For example, I'm confused what happens if two users atthe same time try to access the same record in a table or even the samevariable. Do ASP.NET handle this , I mean by locking one user andletting the other to have access OR it's up to the programmer to writesome code to lock shared resources such as database , objects andvariables?

If it's up to the programmer to do this task, I appreciate if you can show me an example that clarifies that.

Thank you

ADO.NET uses a disconnected data model, meaning that in theory, nothings knows 2 users are accessing the same record at the same time. When you read a record, and the record is displayed, there is no connection left on the database. Therefore there is no "concurrency", so to speak, because for all general purpose, the users only see the record's -content-, not the record itself. This is required because the web is a stateless environment...you cannot keep a live connection to the server.

Thus, the only really effective way of handling this (there are other ways, but this is the all purpose one), is called Optimistic Concurrency. The record is read, and when you attempt to update it, ADO.NET (only IF you ask it to do so), will check if the record changed (by storing the original values) since it was read by the user. If it did, it will complain and tell you that someone else changed the record between the last time you read it and your update. Otherwise, it will write it normally.

If 2 people attempt to write the record at the exact same time ( I mean, there are 2 open connections on the same record at the exact same instant), then it is up to the DBMS to handle this, ADO.NET has no control over it. Usualy what will happen is one of the users will be given the go (first come first serve), and the second update will trigger the Optimistic Concurrency mechanism I just talked about.

|||

This article tell you how to Handling Concurrency Issues in .NET

Concurrency Control

Hi! I'm building a web application with ASP.NET, and using MS SQL 2000 for my database server.

How should I do to guarantee the integrity of the data in spite of the concurrent access? Meaning... how can I make sure that more than 1 user can update 1 table at the same time, while no error will occur? Do I need to add some codes at my aspx file? Or do I need to do something to my database? Or do I not have to worry about it?

Thank you.

Well, from a database standpoint -- SQL server already has the abilityto deal with concurrent users, no modifications are necessary. Locksand other mechanisms make sure data updates don't interfere with oneanother. There are cirumstances where concurrent data access can causeproblems (deadlocks for instance), but these circumstances are rare.
So I guess the short answer is don't worry about it.
Jason

Concurrency Control

Hi! I'm building a web application with ASP.NET, and using MS SQL 2000 for m
y
database server.
How should I do to guarantee the integrity of the data in spite of the
concurrent access? Meaning... how can I make sure that more than 1 user can
update 1 table at the same time, while no error will occur? Do I need to add
some codes at my aspx file? Or do I need to do something to my database? Or
do I not have to worry about it?
Thank you.The easiest way is to use a rowversion (timestamp) type column. Look it up
in the books online for most of the details, but simply put, it is a
automatically maintained value that will change everytime your rows in your
database change. Then, as part of every update and delete of rows you have
cached, you can compare these values in the where clause and if they don't
match you knwo someone else has modified the row.
Then you just fetch the row back to the client and let them know what
changed.
----
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
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:12D9DA82-8D0B-451E-881F-6AAEE852DC14@.microsoft.com...
> Hi! I'm building a web application with ASP.NET, and using MS SQL 2000 for
> my
> database server.
> How should I do to guarantee the integrity of the data in spite of the
> concurrent access? Meaning... how can I make sure that more than 1 user
> can
> update 1 table at the same time, while no error will occur? Do I need to
> add
> some codes at my aspx file? Or do I need to do something to my database?
> Or
> do I not have to worry about it?
> Thank you.|||That all depends on what is actually needed. You have two viable choices in
my opinion:
1. Last write wins
2. First write wins
In the first scenario, the data from the person that makes the last write
call is the data that sticks. This is by far the easiest, but it has the
obvious downside of not informing the first user who just wrote their data
that their write was superceded.
In the second scenario, the data from the first person that writes to a row
sticks and an error is thrown for all other users. This is far more
complicated as you have to decide how to handle concurrency errors. Do you
let users choose whether to overwrite the data? Do you simply overwrite it?
Do you throw an error and force the user to re-enter their information? If
on a multi-row write, only of subset of writes fails, do you rollback all
writes?
As you can see, the second scenario is far more complicated to handle.
However, if you want to use the second scenario, then you need a way of
determining whether the row has changed since you last read the data. That
can be done in a couple of ways:
1. Timestamp. You add a timestamp column for every table that participates
in the First Write Wins scenario. For every write, you ask whether the
existing timestamp on the record is the same as the one you have when you
read the data. If they are different, you have a concurrency error,
otherwise write the data.
2. Other row version data type. Same solution as #1, except you use
something other than an actual Timestamp field. I have used Guids in the
past successfully.
3. You compare all fields you are trying to update with the values you had
on initial read. In order to really do this, you need to use dynamic SQL. It
is simply too cumbersome to try to handle with stored procs. The .NET
SqlDataAdapter does a passable job at this. There are also Object Relation
Mappers that will do this work for you or you can roll your own. Beyond not
being able to use stored procs, the other downside to this approach is that
the SQL statements can very long very fast, especially if some of the fields
are Text or Image.
4. Checksum. SQL has a checksum function that XOR the data in all the
columns to produce a quasi-unique number representing the contents of the
row. The catch is that SQL's checksum function does not account for Text or
Image columns.
IMO, you really need a very strong justification to going with First Write
Wins. The additional complexity added is significant and must be justified
by a high expected rate of collision and the absolute need for
user-consistency on all writes.
HTH,
Thomas
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:12D9DA82-8D0B-451E-881F-6AAEE852DC14@.microsoft.com...
> Hi! I'm building a web application with ASP.NET, and using MS SQL 2000 for
> my
> database server.
> How should I do to guarantee the integrity of the data in spite of the
> concurrent access? Meaning... how can I make sure that more than 1 user
> can
> update 1 table at the same time, while no error will occur? Do I need to
> add
> some codes at my aspx file? Or do I need to do something to my database?
> Or
> do I not have to worry about it?
> Thank you.|||in ado.net the concept of pessimistic locking is a bit difficult.
basically, ado.net only directly supports optimistic locking.
you'll need to read up on that in a good ado.net book.
sql server has concept of isolation level that you will likely want to read
up on. You can modify the Isolation level via ADO.NET via the sqlConnection
object.
Not to be taken lightly as High concurrency will yield low Consistency and
High consistency will yield low concurrency.
need to know what your application is doing and how tolerant it can be in
regards to dirty reads, etc.
study up. Also Load Test and Performance test Early and Often.
Greg Jackson
PDX, Oregon|||Thank you all of you. I've also read this article
(http://msdn.microsoft.com/library/d...ncychecking.asp) and learned a lot from it.
Then say if by default, I select the Optimistic Concurrency option in the
DataAdapter Configuration Wizard of the Visual Studio to handle Concurrency
Control. Do I still need to add a datetimestamp column to the database table
and compare the value of that column every time I update the table?
And must I also include the comparison of this columns for insert and delete
command as well? I guess the answer is no?
Anyway, someone told me that using SQL 2000 already provides the concurrency
control by default, such that even without ADO.NET, the amount of research o
n
concurrency control in SQL 2000 gurantees data integrity. I don't really kno
w
how true it is.
"pdxJaxon" wrote:

> in ado.net the concept of pessimistic locking is a bit difficult.
> basically, ado.net only directly supports optimistic locking.
> you'll need to read up on that in a good ado.net book.
> sql server has concept of isolation level that you will likely want to rea
d
> up on. You can modify the Isolation level via ADO.NET via the sqlConnectio
n
> object.
> Not to be taken lightly as High concurrency will yield low Consistency and
> High consistency will yield low concurrency.
> need to know what your application is doing and how tolerant it can be in
> regards to dirty reads, etc.
> study up. Also Load Test and Performance test Early and Often.
>
> Greg Jackson
> PDX, Oregon
>
>|||> Do I still need to add a datetimestamp column to the database table
> and compare the value of that column every time I update the table?
No, ADO.NET caches the values of *all* rows and compares the values against
*all* these rows. As far
as I can understand, you can customize this behavior.

> And must I also include the comparison of this columns for insert and dele
te
> command as well? I guess the answer is no?
No.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:00E5B1E6-97E5-4997-8F7A-BF17E1641332@.microsoft.com...
> Thank you all of you. I've also read this article
> (http://msdn.microsoft.com/library/d...kin
g.asp)
> and learned a lot from it.
> Then say if by default, I select the Optimistic Concurrency option in the
> DataAdapter Configuration Wizard of the Visual Studio to handle Concurrenc
y
> Control. Do I still need to add a datetimestamp column to the database tab
le
> and compare the value of that column every time I update the table?
> And must I also include the comparison of this columns for insert and dele
te
> command as well? I guess the answer is no?
> Anyway, someone told me that using SQL 2000 already provides the concurren
cy
> control by default, such that even without ADO.NET, the amount of research
on
> concurrency control in SQL 2000 gurantees data integrity. I don't really k
now
> how true it is.
> "pdxJaxon" wrote:
>|||On Wed, 6 Apr 2005 20:23:15 -0700, Thomas wrote:
(snip)
>2. Other row version data type. Same solution as #1, except you use
>something other than an actual Timestamp field. I have used Guids in the
>past successfully.
Hi Thomas,
Why on earth would you do that?
A timestamp (aka rowversion) column takes 8 bytes, a uniqueidentifier
takes 16 bytes.
A timestamp column is changed automatically each time a row is inserted
or updated, a uniqueidentifier column is changed only when you
explicitly assign it a new value - so if you forget to, you'll miss the
change.
The timestamp datatype is designed explicitly for this goal, the
uniqueidentifier datatype is designed to be used as a surrogate key.
What is the advantage of using uniqueidentifier for concurrency control?

>4. Checksum. SQL has a checksum function that XOR the data in all the
>columns to produce a quasi-unique number representing the contents of the
>row. The catch is that SQL's checksum function does not account for Text or
>Image columns.
And another catch is that not all changes in the data result in a
different checksum. A different checksum guarantees changes, but an
unchanged checksum doesn't guarantee that the data is unchanged.
SELECT CHECKSUM('a'), CHECKSUM ('aaaaaaaaaaaaaaaaa')

>IMO, you really need a very strong justification to going with First Write
>Wins. The additional complexity added is significant and must be justified
>by a high expected rate of collision and the absolute need for
>user-consistency on all writes.
I couldn't disagree more. The risk of updates being overwritten without
anyone ever noticing is enough justification to take whatever steps are
necessary to prevent this in any serious business.
If you don't want the extra complexity, you should consider pessimistic
locking: get an exclusive rowlock when reading the data, keep the lock
as long as the data is displayed on the users' screen and only release
the lock after either the update is made or the user has decided to quit
the screen without saving changes. This will impact concurrency, but if
you expect very few collisions, it won't be a big deal. If you expect
lots of collisions, go for optimistic locking.
Never use Last Write Wins in any serious multiuser business application.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 6 Apr 2005 23:01:01 -0700, wrytat wrote:

>And must I also include the comparison of this columns for insert and delet
e
>command as well? I guess the answer is no?
Hi wrytat,
I have to disagree with Tibor's answer. You don't need it for inserts
(it's new data, so it can't be already locked), but you probably do need
it for deletes.
Example scenario: I start the application and show customer #123 on my
screen. You start the application and show the same customer. You see
that the information is outdated, find some new information in your
files and you make all changes to reflect the new status.Then you save
your data. Meanwhile, I see that this customre is outdated and I decide
that this old information might just as well be deleted. I hit delete.
The program will then happily delete the new information you just
entered for customer #123.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> (snip)
<snip>
> What is the advantage of using uniqueidentifier for
> concurrency control?
The primary reason is that the calling code can generate the
new Id. It doesn't require an extra round trip to get the
updated Timestamp. The second reason is that mickeysoft has
already admitted that they are changing the Timestamp column
to be more inline with ANSI standards. Translation: It's
changing and I haven't the foggiest idea how compatitbility
will work. Lastly, and this was far more true in the past,
various development technologies did not always play nice
with Timestamps.

> And another catch is that not all changes in the data
> result in a
> different checksum. A different checksum guarantees
> changes, but an
> unchanged checksum doesn't guarantee that the data is
> unchanged.
> SELECT CHECKSUM('a'), CHECKSUM ('aaaaaaaaaaaaaaaaa')
Yep. It's far more problematic. You can of course roll your
own checksum to get around these problems, but it is a
option depending on how accurate the concurrency must be.

> I couldn't disagree more. The risk of updates being
> overwritten without
> anyone ever noticing is enough justification to take
> whatever steps are
> necessary to prevent this in any serious business.
It's a cost-benefit analysis. Does the substantial
additional development time for all development projects
justify the benefit of handling collisions? If there are few
collisions and the data involved is not that critical, then
it is not justified IMO. Finding other means of preventing
the collisions from happening in the first place is far
better choice if available.
Don't overlook the cost of handling first-write-wins
concurrency. Every system that touches the database has to
know how to deal with it. Every system has to potentially
present users with options about what to do in the case of a
collision. Situations like multiple row deletes (e.g. Delete
From Table Where ForeignKey = Value and thus multiple rows
would be deleted) are much more difficult if not impossible
because someone could have changed a row during the middle
of a delete. Isolation levels become much more of an issue
as some writes must be serialized which adversly affects
scalability. The list goes on. It basically becomes a
problem that has to be dealt with at all tiers of the
application.
It is a massive cost that, IMO, must be justified. Sometimes
it is. Most times it isn't.

> If you don't want the extra complexity, you should
> consider pessimistic
> locking: get an exclusive rowlock when reading the data,
> keep the lock
> as long as the data is displayed on the users' screen and
> only release
> the lock after either the update is made or the user has
> decided to quit
> the screen without saving changes. This will impact
> concurrency, but if
> you expect very few collisions, it won't be a big deal. If
> you expect
> lots of collisions, go for optimistic locking.
Pessimistic locking is even worse and should require an even
*more* extensive justification. There are places where it
makes sense, but they are extraordinarily rare IMO (SCMs are
a good example). Its biggest killer is scalability. Someone
locks a records and walks away from their desk. Now you have
to code timeouts and leases. It requires much more work and
hurts scalability.

> Never use Last Write Wins in any serious multiuser
> business application.
Nonsense. I have seen, and built myself, numerous
successful, large multiuser systems built with
Last-Write -Wins. They were designed such that collisions
could not happen or if, in the oft-chance they did, the
impact was minimal. The benefit of significantly shorter
development time more than justified the cost of an
occasional oddity due to collision.
Claiming that Last-Writes-Wins should never be used is
simply ignorant. It shows a blindness to other possiblities
that make First-Write-Wins unnecessary.
Thomas|||On Thu, 7 Apr 2005 13:20:45 -0700, Thomas wrote:
(snip major part of the post)
Hi Thomas,
Thanks for your post! I won't go into a debate. I think it's good that
both cases have now been defended. The OP can read both our posts,
weight the arguments and counter-arguments, decide how they apply in his
situation and then decide what works best for him. That's what I tried
to accomplish with my post.

>Pessimistic locking is even worse and should require an even
>*more* extensive justification. There are places where it
>makes sense, but they are extraordinarily rare IMO (SCMs are
>a good example). Its biggest killer is scalability. Someone
>locks a records and walks away from their desk. Now you have
>to code timeouts and leases. It requires much more work and
>hurts scalability.
I agree that pessimistic locking is worse than optimistic locking. I
would only consider using it for tables with extremely low update
frequency.
But it won't come as a surprise that I still think that pessimistic
locking is better (or rather: less bad) than Last-Write-Wins :-P
(snip)
>Claiming that Last-Writes-Wins should never be used is
>simply ignorant. It shows a blindness to other possiblities
>that make First-Write-Wins unnecessary.
Okay, I admit that this statement was too bold. I probably should have
written something along the lines of:
IMO, you really need a very strong justification to go with Last Write
Wins. The risk of losing relevant changes without any warning *and* the
consequences of such data should be weighed against the benefit of
reduced complexity and development costs. Because of the possible impact
of unnoticed data loss, this should ideally be a management decision.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Concerning .net and SQL Procedures

Recently i had to write a script in sql to compare multiple tables to get a result of items that do not conform to certain business logic. In doing so i wrote all of this information into a sql parameter which branches out to a few other parameters within the parameter.

Now if you need the code just let me ask, but this is a general question to see if it has occured for anyone else.

The problem i am recieving is when i access the code from a .net windows application it tells me:

Error Message:
Insert Error: Column name or number of supplied values does not match table definition.
Insert Error: Column name or number of supplied values does not match table definition.

Procedure Errored On: val_GetDuplicateItemsFromAssignment
Line Number: 16

However when i run the sql parameter within SQL it accesses it just find. This is using the same parameter values.

Does anyone know why this could be happening?

Please do show the code used to insert the values.