Multi-Step Transaction, fails but reports success

I have for example the following SQL:
BEGIN TRAN
Delete from users where userid = 102
Update mainSettings set userCnt = UserCnt -1
COMMIT TRAN
If for example the "update" statement fails, but the "delete" statement
is successful - the overall transaction reports no error, and thus, my
appliction thinks everything was a success.
Any ideas?
VB 6.0
MDAC ADO 2.7
JasonAnswered in microsoft.public.sqlserver.programming
Please do not multi-post.
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/
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||You have to check for @.@.error after every statement that insert / delete or
update
the db. Also, you have to check the return value of the sp.
create procedure proc1
@.userid
as
set nocount on
declare @.error int
BEGIN TRAN
Delete from users where userid = @.userid
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('Error deleting from table users.', 16, 1)
return 1
end
Update mainSettings set userCnt = UserCnt -1
if @.error != 0
begin
rollback transaction
raiserror('Error updating from table [mainSettings].', 16, 1)
return 1
end
COMMIT TRAN
return @.@.error
go
declare @.rv int
declare @.error int
declare @.tc int
set @.tc = @.@.trancount
exec @.rv = proc1 102
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.error != 0
begin
print 'there was an error.'
end
if @.@.trancount != @.tc
rollback transaction
go
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"jroozee@.gmail.com" wrote:
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
server return a error?
Jason|||Read the articles. There you will find the answer.
AMB
"jroozee@.gmail.com" wrote:
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Same reason, last statement in the batch was successful.
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/
<jroozee@.gmail.com> wrote in message
news:1112043813.998362.180420@.g14g2000cwa.googlegroups.com...
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Additionally, the reason you must check for the error, is that the SQL
programmers can not make the decision for you as to whether or not the error
is sufficient to roll back the transaction. That is for you to decide, so
you much catch the errors and rollback the transaction when necessary.. (
There are types of errors which will automatically roll back the transaction
without your intervention. These are generally more severe errors which
cause batch abort, or you have been chosen as a deadlock victim.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
>I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>

Multiple/optional parameters

Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.

Thanks.

Hello John,

Yes it can be done in a single report but you would have to create an extra parameter and do all the logic in the Stored procedure.

1.) Create an extra parameter in the Report Parameters called ParamYear as Datatype string.

2.) In the available values select Non-Queried and enter FisacalYear in Label and Value and also the CalenderYear.

3.) In the stored Procedure the logic would be like this:

If @.Parm = fisicalyear

Begin

End

Else

Begin

End

Hope this helps..

|||

I'm running all my queries against the cube but I guess the logic would be similar.

I should include another parameter and the first thing they select is Fiscal or Calender and then the proper hirarchies are offered to pick from?

|||It depends on the hirarchies. You can set the default value which one you want first and follow on. In the SP it gets the value for the one it selected. so hirarchies doesn't matter I believe.|||Can you hide a parameter list and then display one parameter or the other depending on the data from the first parameter?|||

Yes , it is possible. I personally did not do it but this link might be helpful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087731&SiteID=1

John, I have this saved in my Code-Sample database and this was answered in the same forum.

This is possible. I'll give an example using the AdventureWorks db. Suppose you want to have a report with all the contacts. The first filter on your report will filter on the Title column, the second will filter on LastName. When a user selected a Title in the dropdownlist, the dropdownlist will be filled with all the LastName's that have the same Title as the one selected.

Here is how to do this:

1.) create a dataset 'TITLES' with the following sql statement:
SELECT DISTINCT Title FROM Person.Contact ORDER BY Title

2.) create a second dataset 'LASTNAMES' with the following sql statement:
SELECT DISTINCT LastName FROM Person.Contact WHERE Title = @.Title

3.)create a third dataset 'CONTACTDETAILS' with the following sql statement:
SELECT * FROM Person.Contact WHERE Title = @.Title AND LastName = @.LastName

4.) Go to the Layout tab and open the \Report\Report Parameters menu

Normally you will see two parameters: Title and LastName. Make sure that Title is the first parameter.

Select the Title parameter and set the following settings:
Available values\From Query\DataSet = TITLES
Available values\From Query\Value Field = Title
Available values\From Query\Label Field = Title

Select the LastName parameter and set the following settings:
Available values\From Query\DataSet = LASTNAMES
Available values\From Query\Value Field = LastName
Available values\From Query\Label Field = LastName

Click OK to leave the Report Parameters menu.

5.)Drag a table on your report and add fields from the CONTACTDETAILS dataset to it.

If everything went well and you run your report, you will first need to select a value for the Title filter. After that, the LastName filter will be filled based on the value selected from the Title filter. When selecting a name in there, the report will be shown.

|||

I'm not quite doing the same thing.

I want a report that will drop down a list of fiscal year/months or calender year/months depending upon user input. I've thought about creating a report with 2 subreports, one a fiscal year report and one a calender year report. The subreports will be toggled from the main report. I can default to one and let the user toggle between reports. No parameters will be passed, the user will make appropriate selection in the subreport.

multiple where statement in a SP

Hello, a small question. I have a search page on the site where the user can search for other users, they can fill in age, gender etc

The question is, how should i extract all that information from the database, i am using SP in MS SQL 2005, C#.

For each of the options is there a possibility to press "not specified", how do i build up a query in the sql server?

Should i just

IF (@.Age1 <> '')
BEGIN
SET @.SearchString = @.SearchString + 'AND (profile_publicinfo.age = ' + @.Age1 + ')'
END

and then continue like this?

so it would look something like this:

SELECT username, gender, signupdate .... FROM profile_publicinfo FULL OUTER JOIN ..... WHERE (@.SearchString)

any ideas?

I highly recomomend Erland Sommarskog's article:Dynamic Search Conditions in T-SQL.|||Thanks, Erland Sommarskog's article solved my problems.sql

Multiple Values for single row

hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..

now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...

so far i created a table 'UserAccount' with column names as
UserName as varchar(50)
Password as varchar(50)
EmailID as varchar(100)
DateOfJoining as datetime
UserID as int --> this is unique for user..i enabled automatic increment..and this is primary key..

so now every user must have a list of other userid's.. as contact list..

Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..
THANK YOU !

if it helps..iam using sql server express edition..and iam accessing using asp.net/C#Hi,

Create a contact list table which includes columns,

UserId,
ContactId

Eralper

Multiple value on y axis in chart?

Friends, I would like to put employee name and type of leave on the y-axis and on the x-axis the date, so that users can select the date start and end and employee/employees and see the chart or leaves.

But how to do that?

My fields are: Name, LeaveDescription and Date.

The data with me is for each employee, for each date, leave codes are there.

You would drag and drop name and leave description to the category fields (y-axis) and date to data fields (x-axis).|||

Dear ??€?§Q?,

It gives me this error:

More than one data set, data region, or grouping in the report has the name 'chart1_CategoryGroup1'. Data set, data region, and grouping names must be unique within a report.

Please help me.

Thanks,

|||Are you trying to drag and drop the same field twice to the same axis? Find where chart1_CategoryGroup1 is being added multiple times to a dataset, data region or grouping...|||

Dear ??€?§Q?,

Yeah that worked. Thanks.

But the thing is that, It gives me description on x axis and not on y.

Multiple value on y axis in chart?

Friends, I would like to put employee name and type of leave on the y-axis and on the x-axis the date, so that users can select the date start and end and employee/employees and see the chart or leaves.

But how to do that?

My fields are: Name, LeaveDescription and Date.

The data with me is for each employee, for each date, leave codes are there.

You would drag and drop name and leave description to the category fields (y-axis) and date to data fields (x-axis).|||

Dear ??€?§Q?,

It gives me this error:

More than one data set, data region, or grouping in the report has the name 'chart1_CategoryGroup1'. Data set, data region, and grouping names must be unique within a report.

Please help me.

Thanks,

|||Are you trying to drag and drop the same field twice to the same axis? Find where chart1_CategoryGroup1 is being added multiple times to a dataset, data region or grouping...|||

Dear ??€?§Q?,

Yeah that worked. Thanks.

But the thing is that, It gives me description on x axis and not on y.

Multiple value on y axis in chart?

Friends, I would like to put employee name and type of leave on the y-axis and on the x-axis the date, so that users can select the date start and end and employee/employees and see the chart or leaves.

But how to do that?

My fields are: Name, LeaveDescription and Date.

The data with me is for each employee, for each date, leave codes are there.

You would drag and drop name and leave description to the category fields (y-axis) and date to data fields (x-axis).|||

Dear ??€?§Q?,

It gives me this error:

More than one data set, data region, or grouping in the report has the name 'chart1_CategoryGroup1'. Data set, data region, and grouping names must be unique within a report.

Please help me.

Thanks,

|||Are you trying to drag and drop the same field twice to the same axis? Find where chart1_CategoryGroup1 is being added multiple times to a dataset, data region or grouping...|||

Dear ??€?§Q?,

Yeah that worked. Thanks.

But the thing is that, It gives me description on x axis and not on y.

Multiple users, SourceSafe, and Reporting Services

I am new to the .NET world. I have created a Solution with some Projects in
them for Reporting Services. I have successfually integrated SourceSafe with
my environment and Added the solutions to Sourcesafe.
Now I have a co-worker who needs to begin sharing my solution and Projects.
I have him set up in SourceSafe but can not seem to find a way to get his
environment to "check out" my solution and bind his environment into
SourceSafe like I have mine bound.In the VS IDE, try File>Source Control>Open From Source Control
HTH
"mlapoint" wrote:
> I am new to the .NET world. I have created a Solution with some Projects in
> them for Reporting Services. I have successfually integrated SourceSafe with
> my environment and Added the solutions to Sourcesafe.
> Now I have a co-worker who needs to begin sharing my solution and Projects.
> I have him set up in SourceSafe but can not seem to find a way to get his
> environment to "check out" my solution and bind his environment into
> SourceSafe like I have mine bound.|||Worked like a charm. Thanks!
"Alien2_51" wrote:
> In the VS IDE, try File>Source Control>Open From Source Control
> HTH
> "mlapoint" wrote:
> > I am new to the .NET world. I have created a Solution with some Projects in
> > them for Reporting Services. I have successfually integrated SourceSafe with
> > my environment and Added the solutions to Sourcesafe.
> >
> > Now I have a co-worker who needs to begin sharing my solution and Projects.
> > I have him set up in SourceSafe but can not seem to find a way to get his
> > environment to "check out" my solution and bind his environment into
> > SourceSafe like I have mine bound.

Multiple users with the same default "schema"

Probably quite a simple one, have patience...I'm just learning the
ropes with SQL Server...
I want to grant new users access to an existing database, owned by
user X. So far this works, but user Y has to include X's username
before any object names. For example
select * from cd_collection;
does not work, Y has to use
select * from X.cd_collection;
instead. Any advice on how to add new users (with varying permissions)
to an existing "schema" so that they would have the schema as default
one? SQL Server version is 7.0.Hi
In SQL Server 7 or 2000 you can not change this.
Kalen Delaney wrote an article in the April SQL Server magazine " "Object
Ownership and Security" (InstantDoc ID 41773), I talked about the confusions
and limitations surrounding SQL Server 2000's model, which doesn't separate
the concepts of user and schema." This also talks about the changes in SQL
Server 2005 (Yukon).
http://www.winnetmag.com/SQLServer/...1773/41773.html
John
"janne" <janne_1976@.hotmail.com> wrote in message
news:6539de6f.0406142332.6de2004d@.posting.google.com...
> Probably quite a simple one, have patience...I'm just learning the
> ropes with SQL Server...
> I want to grant new users access to an existing database, owned by
> user X. So far this works, but user Y has to include X's username
> before any object names. For example
> select * from cd_collection;
> does not work, Y has to use
> select * from X.cd_collection;
> instead. Any advice on how to add new users (with varying permissions)
> to an existing "schema" so that they would have the schema as default
> one? SQL Server version is 7.0.

Multiple users problem

Hi, I am building a web application now that will be used by multiple users. Users import data from a CSV into table 'temp' which is later converted to the right data type and then moved to another table, but I was wondering what would happen if more than 1 user was importing different files at the same time.

Will this create a problem, if so how can I fix this?

Thanks for the help.

From the perspective of the SQL database, it won't care if multiple people are uploading at the same time. You might however.

The simple solution (especially if this is a multi-step process) is to ensure that in your shared temp table you insert the UserID into each row. Then as you step through your processes you just make sure that the same UserID is in each row, that way users cannot trip over/ corrupt each others' data at import time.

This assumes that the CSV file names are different. If you require all people to name their file 'Import.csv' then of course you will either have a file lock issue (can't upload the second csv if the first is in use) or you will have an over-writing issue (your code doesn't check if the file is in use and just over-writes it).

I hope that helps.

|||

Or simply use session-scoped temporary tables like:

CREATE TABLE #Table ...

Of course it'll disappear as soon as you close the connection, and won't interfere with any other connections #Table. (The real name is something god aweful like #Table_________________________________AB9F3E2578A2C) where the last part is random-ish for each different connection, but you call it #Table, and it knows which one you mean.

|||Hey guys,
I realize its been quite sometime. But thanks to both of you for your answers. I have used the 2nd method because I don't have to change my table definition. But thank you for your ideas.

Multiple users of a website using aspnetdb.mdf database

I don't know whether this question belong here, but...

In the past I had many customers using the same website each with their own domain name and their own database. To accomplish that, I used MS Access and placed each customers database in separate directories. I steered the customer to the correct directory using their domain name. I am now trying to accomplish the same thing using aspnetdb.mdf and other .mdf database files. I am using the aspnetdb.mdf database for membership, etc. I want the aspnetdb.mdf database file to be unique for each customer. Does anyone know how to accomplish what I was doing with MS Access using aspnetdb.mdf? Is there any way to have multiple web.config files each of which would be dedicated to a particular customer?

I am not sure if you can keep the same website, but the obvious way is to create duplicated websites & for each one you set (in web.config) a different configuration database. something like:

<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=aspnetdb1;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

where you have createdaspnetdb1before and configured it usingaspnet_regsql.exe

Multiple users logging into a database

I'm developing an application in ASP.Net that uses SQLServer. The database person in the team wants to have each user have a seperate login to the database so he can tell who has done what changes in the database. Is this a good practice? Or are their performance\security issues with this model?For system performance it is better to use one account (to use one connection string) and connection pooling. My solution is as follows. Create your own users and log-in system. You can save current username or id to Session. Then create a table system_events to your database. Everytime user makes something you can insert a new row that describes user action. Also, and it is maybe even better, you can use plain text logs for this.|||We implemented this feature and what we did was write a stored procedure to check the old value against the new values being updated in the database and then it writes only the changes to a seperate table designed for tracking changes. We then wrote a page to display change history by date range in a D-Grid. It works very well and there has been no noticable performance hit since implementation.

CREATE PROCEDURE AddChangeHistory
(
@.propertyID INT,
@.userName VARCHAR(75), --Assigned in session when a user logins
@.tableName VARCHAR(75),
@.fieldName VARCHAR(80),
@.fieldChangeVal varchar(200)
)
AS
DECLARE @.fieldInitVal varchar(200)
BEGIN TRAN
CREATE TABLE #tempVal
(
fieldInitVal VARCHAR(200)
)
INSERT INTO #tempVal exec('SELECT TOP 1 ' + @.fieldName + ' FROM ' + @.tableName + ' WHERE propertyID=' + @.propertyID + '')
SELECT @.fieldInitVal = (SELECT top 1 * FROM #tempVal)
DROP TABLE #tempVal
IF(LOWER(@.fieldInitVal) <> LOWER(@.fieldChangeVal))
BEGIN
INSERT INTO ChangeHistory(propertyID, userName, tableName, fieldName, fieldInitVal, fieldChangeVal)
VALUES (@.propertyID, @.userName, @.tableName, @.fieldName, @.fieldInitVal, @.fieldChangeVal)
END
IF(@.@.ERROR <> 0)
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
GO

SPROC to update my database

CREATE PROCEDURE dbo.oPK_UpdateAdminHours
(
@.Role AS varchar(100),
@.UserName AS varchar(100),
@.vcManager AS varchar(50),
@.vcAssistant1 AS varchar(50),
@.vcAssistant2 AS varchar(50),
@.vcLeasingManager AS varchar(50),
@.vcMarketingDirector AS varchar(50),
@.vcLeasing1 AS varchar(50),
@.vcLeasing2 AS varchar(50),
@.vcLeasing3 AS varchar(50),
@.vcLeasing4 AS varchar(50),
@.vcLeasing5 AS varchar(50),
@.bMon_Fri AS bit,
@.cDisplaySummer_Winter AS char(1),
@.vcOfficeHoursSummerMon AS varchar(25),
@.vcOfficeHoursWinterMon AS varchar(25),
@.vcOfficeHoursSummerTue AS varchar(25),
@.vcOfficeHoursWinterTue AS varchar(25),
@.vcOfficeHoursSummerWed AS varchar(25),
@.vcOfficeHoursWinterWed AS varchar(25),
@.vcOfficeHoursSummerThur AS varchar(25),
@.vcOfficeHoursWinterThur AS varchar(25),
@.vcOfficeHoursSummerFri AS varchar(25),
@.vcOfficeHoursWinterFri AS varchar(25),
@.vcOfficeHoursSummerSat AS varchar(25),
@.vcOfficeHoursWinterSat AS varchar(25),
@.vcOfficeHoursSummerSun AS varchar(25),
@.vcOfficeHoursWinterSun AS varchar(25),
@.vcTimeZone AS varchar(50),
@.PropertyID AS INT
)
AS
--Calling the AddChangeHistory SPROC to check the values before updating the Database
exec AddChangeHistory @.propertyID, @.userName, 'vcManager',@.vcManager
exec AddChangeHistory @.propertyID, @.userName, 'vcAssistant1',@.vcAssistant1
exec AddChangeHistory @.propertyID, @.userName, 'vcAssistant2',@.vcAssistant2
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasingManager',@.vcLeasingManager
exec AddChangeHistory @.propertyID, @.userName, 'vcMarketingDirector',@.vcMarketingDirector
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing1',@.vcLeasing1
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing2',@.vcLeasing2
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing3',@.vcLeasing3
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing4',@.vcLeasing4
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing5',@.vcLeasing5
exec AddChangeHistory @.propertyID, @.userName, 'bMon_Fri',@.bMon_Fri
exec AddChangeHistory @.propertyID, @.userName, 'cDisplaySummer_Winter',@.cDisplaySummer_Winter
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerMon',@.vcOfficeHoursSummerMon
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterMon',@.vcOfficeHoursWinterMon
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerTue',@.vcOfficeHoursSummerTue
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterTue',@.vcOfficeHoursWinterTue
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerWed',@.vcOfficeHoursSummerWed
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterWed',@.vcOfficeHoursWinterWed
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerThur',@.vcOfficeHoursSummerThur
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterThur',@.vcOfficeHoursWinterThur
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerFri',@.vcOfficeHoursSummerFri
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterFri',@.vcOfficeHoursWinterFri
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerSat',@.vcOfficeHoursSummerSat
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterSat',@.vcOfficeHoursWinterSat
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerSun',@.vcOfficeHoursSummerSun
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterSun',@.vcOfficeHoursWinterSun
exec AddChangeHistory @.propertyID, @.userName, 'vcTimeZone ',@.vcTimeZone
--Put your Update code here (removed for space)

Hope this helps.|||Thank you so much. That is what I needed.

multiple stored procedures executed simultaneously

hi,
i have an application that uses a table to track the last invoice number used. there are around 50 users simultaneously trying to retrieve the invoice number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from the workstation, then send it back to the server via an UPDATE, there is the remote possibility of some users retrieving the same number before it being updated by the first user. (the UPDATE issued by the first user can sometimes be delayed due to slow network)
can i use stored procedures to solve this problem? i figured that if i make the SELECT and UPDATE into a single stored procedure, even if i have 50 users calling it all at the same time, each call (SELECT and UPDATE) must execute completely before the next user's call can be executed. there by making sure that the invoice number is incremented before the next user can retrieve it from the table. is this a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...
> hi,
> i have an application that uses a table to track the last invoice number
used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.
> the problem is when i issue a SELECT to get the number, increment it from
the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)
> can i use stored procedures to solve this problem? i figured that if i
make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?
> thanks in advance.
> bob
>

multiple stored procedures executed simultaneously

hi,
i have an application that uses a table to track the last invoice number use
d. there are around 50 users simultaneously trying to retrieve the invoice
number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from th
e workstation, then send it back to the server via an UPDATE, there is the r
emote possibility of some users retrieving the same number before it being u
pdated by the first user. (
the UPDATE issued by the first user can sometimes be delayed due to slow net
work)
can i use stored procedures to solve this problem? i figured that if i make
the SELECT and UPDATE into a single stored procedure, even if i have 50 use
rs calling it all at the same time, each call (SELECT and UPDATE) must execu
te completely before the ne
xt user's call can be executed. there by making sure that the invoice numbe
r is incremented before the next user can retrieve it from the table. is th
is a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...

quote:

---

> hi,
> i have an application that uses a table to track the last invoice number

---

used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.

quote:

---

> the problem is when i issue a SELECT to get the number, increment it from

---

the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)

quote:

---

> can i use stored procedures to solve this problem? i figured that if i

---

make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?

quote:

---

> thanks in advance.
> bob
>

---

Multiple Step OLE DB error from VB

One of our users is getting this error message.

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21) Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was done.

This is the only user that is currently getting the message. Last week another user got this one day and then it stopped.

Inside VB6 code, we're sending some filters to an ASP that in turn sends a SELECT based on those parameters the SQL database and we get data back to the program.

Stepped through the code and found the error returns after this line

oSqlServConn.Open "Provider=MSDAOSP;Data Source=MSXML2.DSOControl.2.6"

Any thoughts out in ForumLand? Please take it easy on me since I didn't write most of this code. Just trying to solve the problem. :)Check if this (http://support.microsoft.com/default.aspx?scid=kb;en-us;312288) pertains to your situation.|||Nope. Unfortunately not. Only sending a handful of filters and the user has Service Pack 4 already. Wouldn't be as concerned (since this is a user that rarely needs the data and can get it via other means) except this person has the newest computer on the block.|||Still having this problem. Any thoughts out there?|||Check tables names and columns names in SQL. I think there may be issue in deploying columns referred by the page.

Check the following :
Use client-side cursors (by setting the ADO recordset Cursor Location property to adUseClient).
Remove the insert trigger from the source table.
Put SET NOCOUNT ON line at the beginning of the trigger code.
After calling Update the first time to insert the new row in the table, scroll the recordset using methods like MoveNext and MovePrevious before calling Update again.

HTH|||Setting cursor location may affect the situation, but the rest simply does not apply, - the error occurs at the time of initializing the connection, not at the time of execution of an action query that fires a trigger.|||Tried setting cursor location on either side of the equation and still no go. I can recreate the problem on my computer now as long as I'm pointing to a test SQL server. When I switch over and point to our Production SQL server, I get no error.

Multiple Sorting

Hello All!
My report has columns Date, Year, Model, Cost, Color
I have users that will require the abililty to sort by multiple criteria.
Is it possible to configure a primary AND secondary AND maybe a THIRD sort
in Reporting Services?
For example: Sort the above columns as: Date, Model and Cost at the same
time?
This sorting criteri will change based on the user and the info sought. So
another user may want to
sort on Cost and Year, while another person wants Color,Cost, Year etc..
Currently, as far as I know, Reporting Services limits one to one column
sort at a time in a report.
Thanks in advance !
ScottOn Nov 16, 9:53 am, iamscott <iamsc...@.discussions.microsoft.com>
wrote:
> Currently, as far as I know, Reporting Services limits one to one column
> sort at a time in a report.
By default, all of your objects will be populated in the order of the
data in the DataSet. You can define this with a ORDER BY clause in
your SQL.
But, you can create Sort rules by object in your Report. Go to your
Table Properties, then the Sort tab, and you can add multiple sorting
rules to your table. Each rule is applied in sequence, with an
Ascending or Descending. Note that the sorting value is actually an
Expression, which means you can make it dynamic with a little code.
For your problem, what I would do is create a Parameter called
SortRules, make it a Integer, then define Available values as:
"Date, Model, Cost", 1
"Cost, Year", 2
"Color, Cost, Year", 3
etc
Then, in your table, add three Sort rules, each Ascending
Rule 1:
= IIF( Parameters!SortRules.Value = 1, Fields!Date.Value,
IIF( Parameters!SortRules.Value = 2, Fields!Cost.Value,
IIF( Parameters!SortRules.Value = 3, Fields!Color.Value,
Nothing ) ) )
Rule 2:
= IIF( Parameters!SortRules.Value = 1, Fields!Model.Value,
IIF( Parameters!SortRules.Value = 2, Fields!Year.Value,
IIF( Parameters!SortRules.Value = 3, Fields!Cost.Value,
Nothing ) ) )
Rule 3:
= IIF( Parameters!SortRules.Value = 1, Fields!Cost.Value,
IIF( Parameters!SortRules.Value = 2, Nothing,
IIF( Parameters!SortRules.Value = 3, Fields!Year.Value,
Nothing ) ) )
Now, when the user views the report, they will get a Parameter Prompt
at the top to select a sort method, then after choosing one and
hitting the View Report, the table will sort based on that method.
-- Scott|||Thanks so much for your idea! have a great weekend!
Scott
"Orne" wrote:
> On Nov 16, 9:53 am, iamscott <iamsc...@.discussions.microsoft.com>
> wrote:
> > Currently, as far as I know, Reporting Services limits one to one column
> > sort at a time in a report.
> By default, all of your objects will be populated in the order of the
> data in the DataSet. You can define this with a ORDER BY clause in
> your SQL.
> But, you can create Sort rules by object in your Report. Go to your
> Table Properties, then the Sort tab, and you can add multiple sorting
> rules to your table. Each rule is applied in sequence, with an
> Ascending or Descending. Note that the sorting value is actually an
> Expression, which means you can make it dynamic with a little code.
> For your problem, what I would do is create a Parameter called
> SortRules, make it a Integer, then define Available values as:
> "Date, Model, Cost", 1
> "Cost, Year", 2
> "Color, Cost, Year", 3
> etc
> Then, in your table, add three Sort rules, each Ascending
> Rule 1:
> = IIF( Parameters!SortRules.Value = 1, Fields!Date.Value,
> IIF( Parameters!SortRules.Value = 2, Fields!Cost.Value,
> IIF( Parameters!SortRules.Value = 3, Fields!Color.Value,
> Nothing ) ) )
> Rule 2:
> = IIF( Parameters!SortRules.Value = 1, Fields!Model.Value,
> IIF( Parameters!SortRules.Value = 2, Fields!Year.Value,
> IIF( Parameters!SortRules.Value = 3, Fields!Cost.Value,
> Nothing ) ) )
> Rule 3:
> = IIF( Parameters!SortRules.Value = 1, Fields!Cost.Value,
> IIF( Parameters!SortRules.Value = 2, Nothing,
> IIF( Parameters!SortRules.Value = 3, Fields!Year.Value,
> Nothing ) ) )
> Now, when the user views the report, they will get a Parameter Prompt
> at the top to select a sort method, then after choosing one and
> hitting the View Report, the table will sort based on that method.
> -- Scott
>

Multiple server connections with XP client to SQL server using Great Plains

We have 2 XP clients using Great Plains client connecting
to a Small Business Server running SQL 2000. When these
users start up Great Plains they each are using multiple
server connections/sessions which is eating up our
limited licenses. Our Windows 2000 users only use one
connection but the two XP users are taking up 4 and 7
licenses each. I'm not sure if it is an XP, Great
Plains, SQL or Small Business Server issue.
.SQL server cals are machine based, a single machine with
sql cal can open multiple connections to a sql server and
it can also connect to multiple sql servers at the same
time.
Are you getting an error for exceeding licenses ?
Use sp_who command in sql query analyzer to find out how
many connections are open.
>--Original Message--
>We have 2 XP clients using Great Plains client connecting
>to a Small Business Server running SQL 2000. When these
>users start up Great Plains they each are using multiple
>server connections/sessions which is eating up our
>limited licenses. Our Windows 2000 users only use one
>connection but the two XP users are taking up 4 and 7
>licenses each. I'm not sure if it is an XP, Great
>Plains, SQL or Small Business Server issue.
>..
>
>.
>|||We've got a 25 user Small Business Server CAL. As soon
as we get 25 connections (as seen through a NETSTAT
command) the server refuses to allow anymore. We
discovered that the two machines that are creating
multiple connections are XP and it appears to be ODBC
related.
>--Original Message--
>SQL server cals are machine based, a single machine with
>sql cal can open multiple connections to a sql server
and
>it can also connect to multiple sql servers at the same
>time.
>Are you getting an error for exceeding licenses ?
>Use sp_who command in sql query analyzer to find out how
>many connections are open.
>>--Original Message--
>>We have 2 XP clients using Great Plains client
connecting
>>to a Small Business Server running SQL 2000. When
these
>>users start up Great Plains they each are using
multiple
>>server connections/sessions which is eating up our
>>limited licenses. Our Windows 2000 users only use one
>>connection but the two XP users are taking up 4 and 7
>>licenses each. I'm not sure if it is an XP, Great
>>Plains, SQL or Small Business Server issue.
>>..
>>
>>.
>.
>