how to manage concurrency between multiple, disconnected clients

I have a system use MS SQL 2005 & .NET 2.0, my tables don't have rowversion, but I heard SQL 2005 manage a rowversion by itself, can I use this to do a "ConflictDetection".

All I try to do is I want to get a error when I try to update a row which been modified by someone else after I read row.

Thanks.

I have never used SQL Server 2005 timestamp field which is what you are referring to as the row version. This is an autoincrementing field when a change is detected and is useful in preventing dirty writes.

I have used this practice in managing phantom data back with SQL Server 2000 just by adding a column of type INT and incrementing it each time the record is changed. Basically this is how it works, when I retrieve the record, I get a version of 1, at the same time another person gets the same record with version 1 as well. This person then updates the record causing it to increment to 2. In the meantime, I decide to update the record as well, your application or database should compare the version number you have which is 1 with the current version number at the database, if it is the same then allow update as this mean no changes were detected. In this case the version is at 2 which means the copy of the data at version 1 when i retrieved it is stale and you would alert the user that either they cannot save until they do a refresh or inform them if the choose to proceed, data will be lost.

Using row versions is optimistic locking as many people can read the same record.

You can also use pessimistic looking by locking the record once you have it. You can do this by using Transactions in .NET (System.Data.SqlClient.Transaction) and specify the Isolation levels.

how to make this report?

on a timesheet system, if I want a sum of a user on each project over a given period, how can I do this without creating a view? I mean creating a view will just do the sum for that given period, but not prompting the user for the period.

the table looks like this:

empid, projid, hours,closingDate

now I want the sum of the hours on two closing dates, can crystal report prompt the user for two closing dates then do the sum for each employee on each project?You want to sum the hours on 2 closing dates.
Create 2 parameters for each closing date (in 8.0: Insert | Field Object ... on the menu, then create a new parameter value for each date that the user can enter. Be sure to select Date for data type.)

You want the report to prompt the user, so use the parameters in the report and in the select statement or Select Expert. If you named your parameters Date1 and Date2: ({closingDate} = {?Date1}) or ({closingDate} = {?Date2}).

Insert a Group on the ID.
Insert a Group under that on the projid.
Sum the hours for all groups and you will get a total for each projid and a total for the id.

Format your report to make it look like you want it to.

I hope this helps!
Kymberlie|||thx kstefanski

how to make report parameter value change dynamic when run time?

Dear all,

i have 2 parameter [startDate] [endDate].
startDate will basic on the day run report to get the start date from system(follow the store procedure) as below.

CREATE PROCEDURE [dbo].[CFSRep_spGetLastWeekMonDate]
AS
BEGIN
DECLARE @.dtStart DATETIME
-- SET @.dtStart = GetDate() --Set Date to Todays Date
--Set Start Date to 6 days before. If its a Sunday it will get this weeks Monday Date, else any day before will get the past weeks Monday Date
SELECT @.dtStart = dateadd(dd, -6, Getdate())
WHILE (datepart(dw, @.dtStart) != 2) --While its not Day 2 (Monday)
BEGIN
SELECT @.dtStart = dateadd(dd, -1, @.dtStart) --Set the Start Date to a day before
END

SET @.dtStart = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @.dtStart))) --Set the time to 00:00:00 AM

SELECT @.dtStart as LastWeekFirstDay
--

END
GO


endDate will get the date after the startDate is process. let say the answer as below.
SELECT DATEADD(dd, 4, @.startDate) as endDate

when i get the startDate = 30th Jan 2006, then the endDate = 3rd Feb 2006

then when i preview report, and i change the parameter of the date on start date to 23rd Jan 2006 but the endDate still the same 3rd Feb 2006.

anyway for me to refesh the endDate to 27th Jan 2006?

Hi Terence,

first of all you can use visual basic functions like Today etc. to get the startdate. In case of that you must not go to the database.

then you can try the following steps:

1. define 2 parameters (startdate + enddate)

2. in the enddate-dataset define a parameter @.startDate and set it to =Parameters!StartDate.Value.

3. make your 'SELECT DATEADD(dd, 4, @.startDate) as endDate' statement.

Now the reportservice should set in the endate-parameter the startdate-param +4 days.

hope this will work for you

sql

how to loop through my workflow by looking at the file system?

Hi, not sure my subject title makes it clear what I want but here it is.

I have a workflow which basically looks at an excel file in a folder on the local drive and then does loads of stuff to it. Everytime I want to process a different excel file that is in the same location al I have to do is change the value of a single local variable, which is just the name of the excel file.

Is there a way to make this automatic? For exmaple....could I somehow put my whole workflow inside a loop that looks inside that local folder and one by one, get the name of the file, assigns the name to that global variable, and then runs the flow...and continues to do that until it gets to the end?

any help would be greatly appreciated...thanks!!!!

andy

Have you look at the ForEachLoop container in the control flow? you can use it to loop through each excel file in a specific folder.

|||awesome, thanks!

How to link to ACT7 with SQLexpress,both on same Notebook.

How to link to ACT7 with SQLExpress,both on same Notebook.

I tried to make ODBC via settings Adm.Tools for Sql Server, and I made System DNS,

From Msaccess 2003 I try to male "get external data" and via ODBC databases,.... but it did not work. I don't see

and popping up if listing/prgs/database or whatever, it remains dead.

I can however via Start,Programs work with Sql express studio and with a demo database it is working,but I need the ACT7 tables to work with.

What my main task is, is to connect via Msaccess/ODBC to my ACT7 ( Sql) in order to do some update/import etc etc.

because standard Import features very poor for Additional Contacts etc.

Thanks for any help.

William

Hi William,

ACT! does not support access to their database outside of their defined API, which I'm not 100% familiar with. My understanding is that they do have an OLEDB provider for use with reporting engines such as Crystral reports, but I'm not sure if they support ODBC access to their tables.

I recommend you check out the ACT! support site at http://www.act.com/support/index.cfm to find your answer. The have both a knowledge base and customer forums that should help you figure out the correct way to do this.

Mike

|||

Thanks Mike sofar, I am trying further somewhere somehow.

William

How to link informix data with MS SQL 2000?

I have a unix system with an informix data,
How many options there are to link this informix data with MS SQL?
Is there a "best way"?
Different methods of data transfer include DTS for a "one time" transfer -
by way of scheduled jobs, etc. For a constant movement of data (queries,
etc) you could use linked servers.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

How to link informix data with MS SQL 2000?

I have a unix system with an informix data,
How many options there are to link this informix data with MS SQL?
Is there a "best way"?Different methods of data transfer include DTS for a "one time" transfer -
by way of scheduled jobs, etc. For a constant movement of data (queries,
etc) you could use linked servers.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

How to link informix data with MS SQL 2000?

I have a unix system with an informix data,
How many options there are to link this informix data with MS SQL?
Is there a "best way"?Different methods of data transfer include DTS for a "one time" transfer -
by way of scheduled jobs, etc. For a constant movement of data (queries,
etc) you could use linked servers.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

how to know which filegroup a table is using with a SQL query

there are several filegroups
I want to know which filegroup a table is using with a SQL query
(i think there may be a SYSTEM TABLE I can query to know it)
Benny
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('tablename'))
and i.indid < 2
and i.groupid = s.groupid
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1178688427.945823.281780@.e65g2000hsc.googlegr oups.com...
> there are several filegroups
> I want to know which filegroup a table is using with a SQL query
> (i think there may be a SYSTEM TABLE I can query to know it)
>
|||On May 9, 1:54 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Benny
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('tablename'))
> and i.indid < 2
> and i.groupid = s.groupid
> "Benny" <wuyueb...@.gmail.com> wrote in message
> news:1178688427.945823.281780@.e65g2000hsc.googlegr oups.com...
>
from your query, I found it is based on index's filegroup,
so how about if some table do not have index?
and how about .indid >= 2 ?

how to know which filegroup a table is using with a SQL query

there are several filegroups
I want to know which filegroup a table is using with a SQL query
(i think there may be a SYSTEM TABLE I can query to know it)Benny
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('tablename'))
and i.indid < 2
and i.groupid = s.groupid
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1178688427.945823.281780@.e65g2000hsc.googlegroups.com...
> there are several filegroups
> I want to know which filegroup a table is using with a SQL query
> (i think there may be a SYSTEM TABLE I can query to know it)
>|||On May 9, 1:54 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Benny
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('tablename'))
> and i.indid < 2
> and i.groupid = s.groupid
> "Benny" <wuyueb...@.gmail.com> wrote in message
> news:1178688427.945823.281780@.e65g2000hsc.googlegroups.com...
> > there are several filegroups
> > I want to know whichfilegroupa table is using with a SQL query
> > (i think there may be a SYSTEM TABLE I can query to know it)
from your query, I found it is based on index's filegroup,
so how about if some table do not have index?
and how about .indid >= 2 ?

how to know which filegroup a table is using with a SQL query

there are several filegroups
I want to know which filegroup a table is using with a SQL query
(i think there may be a SYSTEM TABLE I can query to know it)Benny
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('tablename'))
and i.indid < 2
and i.groupid = s.groupid
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1178688427.945823.281780@.e65g2000hsc.googlegroups.com...
> there are several filegroups
> I want to know which filegroup a table is using with a SQL query
> (i think there may be a SYSTEM TABLE I can query to know it)
>|||On May 9, 1:54 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Benny
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('tablename'))
> and i.indid < 2
> and i.groupid = s.groupid
> "Benny" <wuyueb...@.gmail.com> wrote in message
> news:1178688427.945823.281780@.e65g2000hsc.googlegroups.com...
>
>
from your query, I found it is based on index's filegroup,
so how about if some table do not have index?
and how about .indid >= 2 ?

How to know pages flushed at checkpoint?

The checkpoints at our system take way too long.
I'd like to figure out the number of dirty pages
flushed at a checkpoint and ideally which tables and indexes
they belong to.
Is there any way to do it?
Thanks a lotHi,
Use SQL Server performance monitor counters specifically SQL Server Buffer
Mgr: Checkpoint Pages/Sec and SQL Server Buffer Mgr: Lazy Writes/Sec
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
<news1_skh@.yahoo.com> wrote in message
news:61be40e2.0308150733.56398e08@.posting.google.com...
> The checkpoints at our system take way too long.
> I'd like to figure out the number of dirty pages
> flushed at a checkpoint and ideally which tables and indexes
> they belong to.
> Is there any way to do it?
> Thanks a lot|||Thanks - these counters are very helpful.
They, however, do not give an exact info on the number
of flushed pages - just an estimate.
The reason I'd like to have an exact info is this:
checkpoints on our Production database take very long (~20 sec).
The majority of the deta from the Production DB is replicated
on another server where checkpoints take ~0.2 sec.
I'd like to compare what exactly is flushed on both servers
to see if the difference is due to the hardware problems
or there is something I can do with the DB settings
to improve performance.
Any help is greatly appreciated...

How to know latest update date of each stored procedure ?

on SQL Server 2000

They show only Create date

but I need know update date

because I install my system on customer's site and solve problem on customer site

and I can't bring all stored procedure back to my office and restore all stored

because of my database have two projects.

Please Help me....

You may need:

select * from [Information_Schema].routines where ROUTINE_TYPE='PROCEDURE'

|||

Oh

It's cool

THANK YOU VERY MUCH......

:D

How to know if a job is running...

Howdy folks... just a quick question. Is there a system table that holds a variable that states if a job is currently running or not? Something like a status column for jobs. If so, where is it? and if not, any suggestions aside from setting a flag in a table when it runs and stops?
TIAha! Found my own answer... sp_help_job