Minimum SQL Server permissions needed

I have some Access .adp VBA code (below) that deletes a SQL Server 2005 tabl
e
and recreates it.
DoCmd.DeleteObject acTable, "dbo.tblTemp"
DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
I would like to create a role that has the minimum permissions necessary to
do this. Any advice?
If I don't SELECT control to the two tables, Access doesn't seem to see
them. If I don't grant CONTROL, Access doesn't seem to be able to drop the
tables. Yet, after the tblTemp is recreated in the CopyObject line of code,
Access doesn't see it again as the role doesn't have that SELECT or CONTROL
permissions granted to it anymore.
Another problem which seems like overkill was that I had to grant CREATE
TABLE to the role.
Also, If I don't grant control to the role dbo, Access squawks about not
having permissions to dbo.
Your help is appreciated.Not sure about your case - as you don't mention how you are trying to access
these tables after their creations but did you look into the possibility of
creating your tables in the tempdb database?
(I don't even know if this will work from DoCmd.*; however, using a SP or
the ADO connection or the command objects would probably be a much better
idea than using DoCmd.* even if the DoCmd.* are working).
For example:
Set NoCount ON
create table Tempdb..Members (IdMember int Identity (1,1) primary key,
firstname
varchar(50) collate database_default)
insert into Tempdb..Members (firstname) values ('deny')
insert into Tempdb..Members (firstname) values ('ben')
select M.* from Tempdb..Members as M
drop table Tempdb..Members
(the collate database_default statement is there in case the default
collation for the tempdb database would be different from the default
collation of the current database. If this your case, don't forget the
collate database_default statement and don't use a use Tempdb
statement; otherwise the collation used will be the one defined for the
tempdb database. Of course, if both default collations are the same then
you don't have to fiddle with this.)
In my opinion, granting Control and Create table permission on an account is
pretty much giving away all security; so if possible, it would be a much
better idea to use the tempdb database; as this database has been created
exactly for that purpose.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>I have some Access .adp VBA code (below) that deletes a SQL Server 2005
>table
> and recreates it.
> DoCmd.DeleteObject acTable, "dbo.tblTemp"
> DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
> I would like to create a role that has the minimum permissions necessary
> to
> do this. Any advice?
> If I don't SELECT control to the two tables, Access doesn't seem to see
> them. If I don't grant CONTROL, Access doesn't seem to be able to drop
> the
> tables. Yet, after the tblTemp is recreated in the CopyObject line of
> code,
> Access doesn't see it again as the role doesn't have that SELECT or
> CONTROL
> permissions granted to it anymore.
> Another problem which seems like overkill was that I had to grant CREATE
> TABLE to the role.
> Also, If I don't grant control to the role dbo, Access squawks about not
> having permissions to dbo.
> Your help is appreciated.|||For the connection object, the most simple is to use the one who is already
available:
CurrentProject.Connection.Execute ("Your sql statement here")
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>|||Sylvain,
This is an older app that I've inherited. It currently runs with sa rights
(not good), so I was trying to create a new user & role with a limited set o
f
rights that could still allow the Access app to do what it needs. I was
hoping to avoid overly changing the MS Access code, but that may not be
possible. Your suggestion of ditching the DoCmd for executing a stored proc
though is a good one. I'll pursue this.
Thank you
"Sylvain Lafontaine" wrote:
[vbcol=seagreen]
> Not sure about your case - as you don't mention how you are trying to acce
ss
> these tables after their creations but did you look into the possibility o
f
> creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the ? collate database_default ? statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the ?
> collate database_default ? statement and don't use a ? use Tempdb ?
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
is
> pretty much giving away all security; so if possible, it would be a much
> better idea to use the tempdb database; as this database has been created
> exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...|||Rob (Rob@.discussions.microsoft.com) writes:
> This is an older app that I've inherited. It currently runs with sa
> rights (not good), so I was trying to create a new user & role with a
> limited set of rights that could still allow the Access app to do what
> it needs. I was hoping to avoid overly changing the MS Access code, but
> that may not be possible. Your suggestion of ditching the DoCmd for
> executing a stored proc though is a good one. I'll pursue this.
Yes, putting this in a stored proceedure is the only way out. But Sylvain
did not tell the full story. For this to work you need to sign the procedure
with a certificate, and create a user fot the certificate and grant that
user the required rights.
I describe this in detail in this article on my web site:
http://www.sommarskog.se/grantperm.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> ha
scritto nel messaggio news:#aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>

MIN/MAX issue

/*
I have posted DDL below for the issue i have. When you look at the output
from marc1, I would like to retrieve the minimum and maximum vehicle_id's
based on Vh_make_id
so as my output would look something like:
min max
-- --
3040501 3131887
3040443 3049312
How can I do this?
*/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[marc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[marc1]
GO
CREATE TABLE [dbo].[marc1] (
[Vehicle_id] [int] NULL ,
[Vehicle_desc] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_mfg_yr_id] [smallint] NULL ,
[Vh_mfg_yr_band_id] [tinyint] NULL ,
[Vh_mfg_yr_band_desc] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Vh_mfg_age_id] [tinyint] NULL ,
[Vh_group_id] [smallint] NULL ,
[Vh_group_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_type_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_category_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_category_desc] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_registration_id] [int] NULL ,
[Vh_registration_desc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Vh_reg_year_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_reg_area_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_rating_class_id] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vh_rating_class_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Vh_make_id] [smallint] NULL ,
[Vh_make_desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into marc1 values(3040501, '- QMVOG 02 VOL00101200539ST64268',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268, 05
,
05, '', '-', 'Not Defined', 312, 'VOLVO')
insert into marc1 values(3131887, '- QMVOG 03 VOL00101200539ST64268',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268, 05
,
05, '', '-', 'Not Defined', 312, 'VOLVO')
insert into marc1 values(3040443, '- QMVOG 01 MER00101200539ST766417',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
'05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
insert into marc1 values(3049312, '- QMVOG 02 MER00101200539ST766417',
2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
'05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
select * from marc1See if this helps
select min(Vehicle_id)as [max],max(Vehicle_id)as [min] from marc1
group by Vh_make_desc
order by [max] desc
BTW, your table has no primary key nor designed well in terms of
normalization.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:B90A0F66-77E3-49C5-99AE-9C58F27B41CB@.microsoft.com...
> /*
> I have posted DDL below for the issue i have. When you look at the output
> from marc1, I would like to retrieve the minimum and maximum vehicle_id's
> based on Vh_make_id
> so as my output would look something like:
> min max
> -- --
> 3040501 3131887
> 3040443 3049312
> How can I do this?
> */
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[marc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[marc1]
> GO
> CREATE TABLE [dbo].[marc1] (
> [Vehicle_id] [int] NULL ,
> [Vehicle_desc] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_mfg_yr_id] [smallint] NULL ,
> [Vh_mfg_yr_band_id] [tinyint] NULL ,
> [Vh_mfg_yr_band_desc] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_mfg_age_id] [tinyint] NULL ,
> [Vh_group_id] [smallint] NULL ,
> [Vh_group_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_type_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_desc] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_registration_id] [int] NULL ,
> [Vh_registration_desc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_reg_year_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_reg_area_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_rating_class_id] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Vh_rating_class_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_make_id] [smallint] NULL ,
> [Vh_make_desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into marc1 values(3040501, '- QMVOG 02
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3131887, '- QMVOG 03
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3040443, '- QMVOG 01
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> insert into marc1 values(3049312, '- QMVOG 02
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> select * from marc1
>|||sorry im having a sleepy day
select min(vehicle_id), max(vehicle_id), Vh_make_id from marc1 group by
Vh_make_id|||SELECT MIN(Vehicle_Id), MAX(Vehicle_Id)
FROM marc1
GROUP BY Vh_make_id
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:B90A0F66-77E3-49C5-99AE-9C58F27B41CB@.microsoft.com...
> /*
> I have posted DDL below for the issue i have. When you look at the output
> from marc1, I would like to retrieve the minimum and maximum vehicle_id's
> based on Vh_make_id
> so as my output would look something like:
> min max
> -- --
> 3040501 3131887
> 3040443 3049312
> How can I do this?
> */
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[marc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[marc1]
> GO
> CREATE TABLE [dbo].[marc1] (
> [Vehicle_id] [int] NULL ,
> [Vehicle_desc] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_mfg_yr_id] [smallint] NULL ,
> [Vh_mfg_yr_band_id] [tinyint] NULL ,
> [Vh_mfg_yr_band_desc] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_mfg_age_id] [tinyint] NULL ,
> [Vh_group_id] [smallint] NULL ,
> [Vh_group_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_type_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_category_desc] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_registration_id] [int] NULL ,
> [Vh_registration_desc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_reg_year_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_reg_area_id] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Vh_rating_class_id] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Vh_rating_class_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Vh_make_id] [smallint] NULL ,
> [Vh_make_desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into marc1 values(3040501, '- QMVOG 02
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3131887, '- QMVOG 03
> VOL00101200539ST64268',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 64268,
> 05,
> 05, '', '-', 'Not Defined', 312, 'VOLVO')
> insert into marc1 values(3040443, '- QMVOG 01
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> insert into marc1 values(3049312, '- QMVOG 02
> MER00101200539ST766417',
> 2005, 7, 2000-2009, 0, 1, 'Not Defined', 'h-', '-', 'Not Defined', 766417,
> '05 LH 325', 05, 'LH', '-', 'Not Defined', 267, 'MERCEDES')
> select * from marc1
>|||You mean
select min(Vehicle_id)as [min],max(Vehicle_id)as [max] from marc1
group by Vh_make_desc
order by [max] desc
--
Regards
R.D
--Knowledge gets doubled when shared
"Uri Dimant" wrote:

> See if this helps
> select min(Vehicle_id)as [max],max(Vehicle_id)as [min] from marc1
> group by Vh_make_desc
> order by [max] desc
> BTW, your table has no primary key nor designed well in terms of
> normalization.
>
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:B90A0F66-77E3-49C5-99AE-9C58F27B41CB@.microsoft.com...
>
>|||Hi Marc,
try this:
Select Vh_make_id, MIN(Vehicle_id) as minvalue, MAX(Vehicle_id) as
maxvalue
>From marc1
GROUP BY Vh_make_id
HTH, jens Suessmeyer.|||Yep, sorry
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:42FF4DAE-810D-47B7-908B-CE2EC6D70B85@.microsoft.com...
> You mean
> select min(Vehicle_id)as [min],max(Vehicle_id)as [max] from marc1
> group by Vh_make_desc
> order by [max] desc
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Uri Dimant" wrote:
>

Migration to Yukon

Hi,

Anyone kindly let me know more and clear details about the below 2 statements

1.Remove reference to undocumented system tables/stored procs

2. Remove statements modifying system objects

as are not supported in SQL Server 2005. while migrating from sql server 2000 to YUkon (sql server 2005) the above 2 points need to be taken care. but no much clarity on the above.

kindly help me

Regards

Crish

Hi,

not sure above the first one, which procedure are deprecated but the latter one had some major chnages. The system objects are now in the sys schema,e.g. changing the sysobjects to sys.objects. if you used INFORMATION_SCHEMA views before you don′t need to change anything, because the views were also modified to reflect the changes, if you use direct access to the system tables, you have to change your code.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Thanks much for the information Jens.

Migration Millions of Records

Hi

Please guide me for the below given problem.

While doing migration by using cursors for the below given sample data its taking more hours to complete the process. Therefore want to know is there any way I can do it in simple query.

ACNo Amount Balance CalType
A001 10 10 +
A001 10 20 -
A001 40 40 +
A001 10 30 -
A002 90 90 +
A002 20 110 +
A002 40 150 +
A003 10 30 +
A003 10 40 +
A003 10 30 -
A004 40 40 +
A004 10 30 -

Iam having Amount value alone and Balance has to be calculated value based on CalType. At the same time the Balance has to be reset as 0 when AcNo has changed.

Please guide me for the faster approach.

Regards,
Mohanraj

It is not clear what you want help with.

Your data does not appear to be consistant. (It appears that the second row for [A001] should have a Balance of [0], and it appears that the first two rows of [A003] have incorrect Balance amounts.

Also, there is no column that can be used to determine sequencing, i.e., a datetime column or a IDENTITY field.

Do you wish ONLY a summary row with the correct Balance?

OR

Do you wish to 're-calculate' the Balance Column?

This can be very efficiently accomplished without using a CURSOR, #Temp tables, or table variables. It is really just a SET based operation.

|||

The data doesn't seem right.&nbsp; That said:&nbsp; there are ways to calculate running balances without using a cursor, but the methods (using COALESCE or cross-joins) are actually slower than using a cursor.&nbsp; (See <a href="http://www.sqlteam.com/article/calculating-running-totals">this article by Garth Wells</a> for a discussion of the various methods.)

What you don't want to do, though, is use a cursor to update your production table. Create a table variable to hold the running balances. Use a read-only cursor to read through your production table and populate the table variable. Then update the production table with the table variable when you're done.

|||

Hi Arnie,

Thanks for your reply.

ACNo Amount Balance CalType Ident_Column
A001 10 10 + 1
A001 10 20 - 2
A001 40 40 + 3
A001 10 30 - 4
A002 90 90 + 5
A002 20 110 + 6
A002 40 150 + 7
A003 30 30 + 8
A003 10 40 + 9
A003 10 30 - 10
A004 40 40 + 11
A004 10 30 - 12

Yeah, you are correct earlier data was wrongly posted, now it has highlighed as yellow in color.

Now I have added Identity column.

When AccountNumber has changed the balance need to recalculate.

Please provide me some faster approach.

Regards,

Mohanraj