Archive & Restore design question

Hi,

I have a design question.

So, I am designing an web application that will store lots of data.

Because of that we need to allow administrator users to archive some of the data.

However, the user needs the ability to view the archive data (just to know that it is there)

The administrator user can restore the data by using the web application.

My design includes 2 databases, one for the actual data, and the second for the archive data, and I manipulate it by moving data from one database to the other. What do you think about that?

Another thing is the fact that every month administrator will want to empty the archive database and save it on a disk. What is the best way of doing so? and lets say i want to take one of these backup file and restore several records from it into the archive database, what method should I be using?

Finally is there any best practices articles for this kind of issues?

Thanks,

Are you using SQL Server 2005? If you are, you can take advantage of table partitioning. But if not, then you will have to create an application that uses distributed transactions on the two databases you mentioned. Do you have to do all of this in your web application?|||

10x for the response,

All of that should be achived via the web application!

I am using sql 2005.

I am trying to figure out what are the alternatives and what is the best practice for it.

My idea, if using the current design of 2 databases, is to add 3rd database that will handle the restore from file.

Another option is to create a design that will actually to Import/Export to file.

|||

I too have a similar requirement as you have. I wonder if you have found a good design.. Please let me know how are going about diong this task.

sql

Archive & Restore design question

Hi,

I have a design question.

So, I am designing an web application that will store lots of data.

Because of that we need to allow administrator users to archive some of the data.

However, the user needs the ability to view the archive data (just to know that it is there)

The administrator user can restore the data by using the web application.

My design includes 2 databases, one for the actual data, and the second for the archive data, and I manipulate it by moving data from one database to the other. What do you think about that?

Another thing is the fact that every month administrator will want to empty the archive database and save it on a disk. What is the best way of doing so? and lets say i want to take one of these backup file and restore several records from it into the archive database, what method should I be using?

Finally is there any best practices articles for this kind of issues?

Thanks,

Are you using SQL Server 2005? If you are, you can take advantage of table partitioning. But if not, then you will have to create an application that uses distributed transactions on the two databases you mentioned. Do you have to do all of this in your web application?|||

10x for the response,

All of that should be achived via the web application!

I am using sql 2005.

I am trying to figure out what are the alternatives and what is the best practice for it.

My idea, if using the current design of 2 databases, is to add 3rd database that will handle the restore from file.

Another option is to create a design that will actually to Import/Export to file.

|||

I too have a similar requirement as you have. I wonder if you have found a good design.. Please let me know how are going about diong this task.

Architecture question

I have a couple of dozen users running about 20 reports. The reports are
running from SQL Server stored procedures on a server named CORP_DB. The
reports render quickly, even running on my development box, I've never had
anyone complain about performance. My question is this: can I use a beefy
workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as my server
to host IIS and Reporting Services? If I do it this way should I put the two
RS databases on CORP_DB or should I put them on CORP_RS? I may be scaling up
to about 100 users and 50 to 80 reports.
Thanks,
DougSMy personal feeling is to have the RS databases local, even if the data is
on another server. The RS databases are used as an object store (all the
report definitions are stored there, as well as everything else need by RS).
SQL Server is good at how it uses memory. I am currently using a workstation
with 2 gigs of ram and 2 processors. My suggestion is to add a gig of ram if
you can (RS is pretty ram intensive) and add a processor. You might be fine
with 1 processor and 1 gig but it up if at all possible beef it up. Also,
install Windows 2003 Server Standard edition on it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DougS" <doug@.nospam.com> wrote in message
news:OWP73$EmFHA.3144@.TK2MSFTNGP12.phx.gbl...
>I have a couple of dozen users running about 20 reports. The reports are
>running from SQL Server stored procedures on a server named CORP_DB. The
>reports render quickly, even running on my development box, I've never had
>anyone complain about performance. My question is this: can I use a beefy
>workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as my server
>to host IIS and Reporting Services? If I do it this way should I put the
>two RS databases on CORP_DB or should I put them on CORP_RS? I may be
>scaling up to about 100 users and 50 to 80 reports.
> Thanks,
> DougS
>|||I have to have a sql server license for the machine that is rendering the
reports dont I? That's MS's gotcha isnt it? RS is 'free' with SQL Server but
it has to run on a machine by itself so you end up buying another license.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uxTlqRFmFHA.3552@.TK2MSFTNGP10.phx.gbl...
> My personal feeling is to have the RS databases local, even if the data is
> on another server. The RS databases are used as an object store (all the
> report definitions are stored there, as well as everything else need by
> RS). SQL Server is good at how it uses memory. I am currently using a
> workstation with 2 gigs of ram and 2 processors. My suggestion is to add a
> gig of ram if you can (RS is pretty ram intensive) and add a processor.
> You might be fine with 1 processor and 1 gig but it up if at all possible
> beef it up. Also, install Windows 2003 Server Standard edition on it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
>
> "DougS" <doug@.nospam.com> wrote in message
> news:OWP73$EmFHA.3144@.TK2MSFTNGP12.phx.gbl...
>>I have a couple of dozen users running about 20 reports. The reports are
>>running from SQL Server stored procedures on a server named CORP_DB. The
>>reports render quickly, even running on my development box, I've never had
>>anyone complain about performance. My question is this: can I use a beefy
>>workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as my
>>server to host IIS and Reporting Services? If I do it this way should I
>>put the two RS databases on CORP_DB or should I put them on CORP_RS? I may
>>be scaling up to about 100 users and 50 to 80 reports.
>> Thanks,
>> DougS
>|||No, you do not have to have it run on a machine by itself. I currently have
it running on the same machine as my datamart. Now, some IT departments
freak at the idea of having anything but a database run on the machine.
Especially IIS but IIS with an asp.net application really disturbs them. My
feeling is having a web server running on the database machine is the
direction all vendors are going (Oracle does this too) so the dba's need to
get with the program. Anyway, it is your own decision whether to have it on
the same box or not. Now, people that are doing a web farm by definition are
running on other boxes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DougS" <doug@.nospam.com> wrote in message
news:epbro4bmFHA.3120@.TK2MSFTNGP09.phx.gbl...
>I have to have a sql server license for the machine that is rendering the
>reports dont I? That's MS's gotcha isnt it? RS is 'free' with SQL Server
>but it has to run on a machine by itself so you end up buying another
>license.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uxTlqRFmFHA.3552@.TK2MSFTNGP10.phx.gbl...
>> My personal feeling is to have the RS databases local, even if the data
>> is on another server. The RS databases are used as an object store (all
>> the report definitions are stored there, as well as everything else need
>> by RS). SQL Server is good at how it uses memory. I am currently using a
>> workstation with 2 gigs of ram and 2 processors. My suggestion is to add
>> a gig of ram if you can (RS is pretty ram intensive) and add a processor.
>> You might be fine with 1 processor and 1 gig but it up if at all possible
>> beef it up. Also, install Windows 2003 Server Standard edition on it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>>
>> "DougS" <doug@.nospam.com> wrote in message
>> news:OWP73$EmFHA.3144@.TK2MSFTNGP12.phx.gbl...
>>I have a couple of dozen users running about 20 reports. The reports are
>>running from SQL Server stored procedures on a server named CORP_DB. The
>>reports render quickly, even running on my development box, I've never
>>had anyone complain about performance. My question is this: can I use a
>>beefy workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as
>>my server to host IIS and Reporting Services? If I do it this way should
>>I put the two RS databases on CORP_DB or should I put them on CORP_RS? I
>>may be scaling up to about 100 users and 50 to 80 reports.
>> Thanks,
>> DougS
>>
>

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same Db
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? This based on the application used. maybe via the connect string ?
any suggestion would be welcome.
PeterThere is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
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
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:
> There is no trigger or event under which you can place code when a user logs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If you
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> 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
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> > Situation :
> > - via an application "xApp" a user has role "xRole" on a Db
> > - Via an application "yApp" the same user has an other "yRole" on the same
> Db
> > - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> > - The Application we can't change ( not owned by us )
> > Question :
> > Can we in any way assign a role ( change a role ) when the user connects ?
> This based on the application used. maybe via the connect string ?
> > any suggestion would be welcome.
> > Peter
> >
>
>|||I do not know, perhaps one of the others has more information...
--
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
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.
>
> "Wayne Snyder" wrote:
> > There is no trigger or event under which you can place code when a user
logs
> > in or changes databases, which is what you would need...
> >
> > Since roles are fixed, you'll have to grant both roles to the user... If
you
> > chould change the apps, you could use an application role, but since you
> > can't change the application it is not an option...
> >
> >
> >
> > --
> > 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
> >
> > "Peter" <Peter@.discussions.microsoft.com> wrote in message
> > news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> > > Situation :
> > > - via an application "xApp" a user has role "xRole" on a Db
> > > - Via an application "yApp" the same user has an other "yRole" on the
same
> > Db
> > > - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> > > - The Application we can't change ( not owned by us )
> > > Question :
> > > Can we in any way assign a role ( change a role ) when the user
connects ?
> > This based on the application used. maybe via the connect string ?
> > > any suggestion would be welcome.
> > > Peter
> > >
> >
> >
> >

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same Db
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? This based on the application used. maybe via the connect string ?
any suggestion would be welcome.
Peter
There is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
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
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>
|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:

> There is no trigger or event under which you can place code when a user logs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If you
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> 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
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Db
> This based on the application used. maybe via the connect string ?
>
>
|||I do not know, perhaps one of the others has more information...
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
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.[vbcol=seagreen]
>
> "Wayne Snyder" wrote:
logs[vbcol=seagreen]
you[vbcol=seagreen]
same[vbcol=seagreen]
connects ?[vbcol=seagreen]

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same D
b
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? T
his based on the application used. maybe via the connect string ?
any suggestion would be welcome.
PeterThere is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
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
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:

> There is no trigger or event under which you can place code when a user lo
gs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If y
ou
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> 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
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Db
> This based on the application used. maybe via the connect string ?
>
>|||I do not know, perhaps one of the others has more information...
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
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.[vbcol=seagreen]
>
> "Wayne Snyder" wrote:
>
logs[vbcol=seagreen]
you[vbcol=seagreen]
same[vbcol=seagreen]
connects ?[vbcol=seagreen]

application_role

I am using SQL Server 2005 Enterprise Edition. I am a little confused on
setting up a Application role. Where do I make users a member of the role?
Is there a good explaination of this somewhere."Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23$WW46i%23HHA.600@.TK2MSFTNGP05.phx.gbl...
>I am using SQL Server 2005 Enterprise Edition. I am a little confused on
>setting up a Application role. Where do I make users a member of the role?
>Is there a good explaination of this somewhere.
>
You don't add users to app roles. App roles are intended as a way of
identifying an application rather than a user.
http://msdn2.microsoft.com/en-us/library/ms190998.aspx
--
David Portas|||Users don't belong to application roles. The application connects to the
database then executes the sp_setapprole procedure to get permissions.
Check this link out...
http://msdn2.microsoft.com/en-us/library/ms190998.aspx
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23$WW46i%23HHA.600@.TK2MSFTNGP05.phx.gbl...
>I am using SQL Server 2005 Enterprise Edition. I am a little confused on
>setting up a Application role. Where do I make users a member of the role?
>Is there a good explaination of this somewhere.
>|||In SQLS 2005 there are better alternatives, which are explained in
Erland Sommarskog's excellent paper, Giving Permissions through Stored
Procedures, http://www.sommarskog.se/grantperm.html.
-mary
On Tue, 18 Sep 2007 14:59:18 -0500, "Tom Reis" <reistom@.cdnet.cod.edu>
wrote:
>I am using SQL Server 2005 Enterprise Edition. I am a little confused on
>setting up a Application role. Where do I make users a member of the role?
>Is there a good explaination of this somewhere.
>

Application Roles and Bulk Insert

Is it possible to grant a user access to Bulk Insert via an Application Role
?
I have a situation where I dont want to grant users direct access to any
tables, but there is one table that they will need to do a bulk insert into.
I cant see anywhere that you can assign an approle as part of bulkadmin.
Any ideas of how to do this would be appreciated. Thanks.I don't believe you can use "Application roles" for this, but you can assign
"Bulk Insert Administrator" Server Role to any individual Server Login...
Under SQL Server Login Properties, Server Roles, one of the Server Roles is
Bulk insert Administrator.
"Jace" wrote:

> Is it possible to grant a user access to Bulk Insert via an Application Ro
le?
> I have a situation where I dont want to grant users direct access to any
> tables, but there is one table that they will need to do a bulk insert int
o.
> I cant see anywhere that you can assign an approle as part of bulkadmin.
> Any ideas of how to do this would be appreciated. Thanks.

Application Roles across databases in SQL Server 2000

Hello
I have 2 databases that run application role security
(different role names and passwords), users access these
databases only from within different Visual Basic
applications.
I require to be able to request data from both
databases. I have read in SQL Server help that if you
enable the guest user account and then give it the
relevant permissions the system will only allow the other
database to get to these objects.
I have created a stored procedure on one of the databases
that calls a table in the database with the guest account
enabled. I have not given the guest account access to
this table but I can still get to the data in the table.
Please can someone explain why this is and what I need to
do to prevent this.
Thank you
Caroline> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
This is due to ownership chaining behavior. As long as all objects are
owned by the same login, permissions are not checked on indirectly
referenced objects. Additionally, you need to enable cross database
chaining for ownership chains to apply to cross-database access. This
appears to be the case in your environment.
As long as you access data only via views and procedures, you don't need to
grant any permissions to guest. This allows you to leverage ownership
chains as a security mechanism. See Ownership Chains in the Books Online
for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Caroline" <anonymous@.discussions.microsoft.com> wrote in message
news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
> Hello
> I have 2 databases that run application role security
> (different role names and passwords), users access these
> databases only from within different Visual Basic
> applications.
> I require to be able to request data from both
> databases. I have read in SQL Server help that if you
> enable the guest user account and then give it the
> relevant permissions the system will only allow the other
> database to get to these objects.
> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
> Thank you
> Caroline|||> and what I need to do to prevent this.
Only grant execute permissions on the procedure to those users/roles whom
you want to access the underlying data.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23fcOSPDYEHA.3596@.tk2msftngp13.phx.gbl...
> This is due to ownership chaining behavior. As long as all objects are
> owned by the same login, permissions are not checked on indirectly
> referenced objects. Additionally, you need to enable cross database
> chaining for ownership chains to apply to cross-database access. This
> appears to be the case in your environment.
> As long as you access data only via views and procedures, you don't need
to
> grant any permissions to guest. This allows you to leverage ownership
> chains as a security mechanism. See Ownership Chains in the Books Online
> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Caroline" <anonymous@.discussions.microsoft.com> wrote in message
> news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
>

Application Roles

Hi
I'm using an application role to restrict acess to the users to a Database
The Application role is always activated without any problem.
Nevertheless I'm receiving permission error messages ("XXXX permission denie
d
on object 'TABLE', database...") when inserting, updating or deleting
records if I open a recordset before this operations, otherwise everything
works fine.
Below is an example of this,
If I call the following code before I trie to INSERT a record in TABLE2 I
receive the permission error message
..
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
..
If I don't call the previous code the INSERT works ...
(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 and
TABLE2)
Do someone have any idea why this behavior ?
I'll apreciate any help.
Many Thanks
Daniel
EXAMPLE:
--
Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Set cnnConn = New ADODB.Connection
With cnnConn
.Open _
"Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=DBx;Data Source=SERVERx"
End With
'The AppRole is activated without problems--
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password} ,
'odbc'"
.CommandType = adCmdText
.Execute
End With
'IF this code is not called the INSERT bellow works fine, otherwise not--
--
--
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
'INSERT record on TABLE2--
With cmdCommand
.CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
('A', 'XXX', 'P')"
.CommandType = adCmdText
.Execute
End With
..
ErrorHandler:
' clean up
End SubDid you turn pooling off in your connection string? If not, then
another connection is being opened under the covers and in that
connection the approle is not active. There's more information at
http://support.microsoft.com/defaul...;en-us;Q229564.
--Mary
On Tue, 05 Sep 2006 13:42:54 GMT, "Daniel Rodrigues" <u26179@.uwe>
wrote:

>Hi
>I'm using an application role to restrict acess to the users to a Database
>The Application role is always activated without any problem.
>Nevertheless I'm receiving permission error messages ("XXXX permission deni
ed
>on object 'TABLE', database...") when inserting, updating or deleting
>records if I open a recordset before this operations, otherwise everything
>works fine.
>Below is an example of this,
>If I call the following code before I trie to INSERT a record in TABLE2 I
>receive the permission error message
>..
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>..
>If I don't call the previous code the INSERT works ...
>(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 an
d
>TABLE2)
>Do someone have any idea why this behavior ?
>I'll apreciate any help.
>Many Thanks
>Daniel
>EXAMPLE:
>--
>Private Sub CommandButton1_Click()
>On Error GoTo ErrorHandler
>Dim cnnConn As ADODB.Connection
>Dim rstRecordset As ADODB.Recordset
>Dim cmdCommand As ADODB.Command
>Set cnnConn = New ADODB.Connection
>With cnnConn
> .Open _
> "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
> "Persist Security Info=False;" & _
> "Initial Catalog=DBx;Data Source=SERVERx"
>End With
>'The AppRole is activated without problems--
>Set cmdCommand = New ADODB.Command
>Set cmdCommand.ActiveConnection = cnnConn
>With cmdCommand
> .CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password}
,
>'odbc'"
> .CommandType = adCmdText
> .Execute
>End With
>'IF this code is not called the INSERT bellow works fine, otherwise not--
--
>--
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>'INSERT record on TABLE2--
>With cmdCommand
> .CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
>('A', 'XXX', 'P')"
> .CommandType = adCmdText
> .Execute
>End With
>..
>ErrorHandler:
> ' clean up
>End Sub|||Hello Mary
Yes.
I used in connection string "OLE DB Services = -2" and I also tried with
"Pooling=’False " (despite I think this is only for .Net and I'm tried wit
h
VB and Delphi6 with the same results.
I already read the KB article you mentioned.
Is there any other way to deactivate pooling ?
The only way I found to solve the problem was with one connection for the
selects and another one only for INSERT, UPDATE and DELETE statments.
By the way, i did't mentioned in my previous mail, I'm using SQLServer 2K
with SP4 and acessing with ADO using Delphi6 aplications.
Thanks for your answer
Best regards
Daniel
Mary Chipman [MSFT] wrote:[vbcol=seagreen]
>Did you turn pooling off in your connection string? If not, then
>another connection is being opened under the covers and in that
>connection the approle is not active. There's more information at
>http://support.microsoft.com/defaul...;en-us;Q229564.
>--Mary
>
>[quoted text clipped - 68 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1

Application Roles

Hi
I'm using an application role to restrict acess to the users to a Database
The Application role is always activated without any problem.
Nevertheless I'm receiving permission error messages ("XXXX permission denied
on object 'TABLE', database...") when inserting, updating or deleting
records if I open a recordset before this operations, otherwise everything
works fine.
Below is an example of this,
If I call the following code before I trie to INSERT a record in TABLE2 I
receive the permission error message
..
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
..
If I don't call the previous code the INSERT works ...
(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 and
TABLE2)
Do someone have any idea why this behavior ?
I'll apreciate any help.
Many Thanks
Daniel
EXAMPLE:
--
Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Set cnnConn = New ADODB.Connection
With cnnConn
.Open _
"Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=DBx;Data Source=SERVERx"
End With
'The AppRole is activated without problems--
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password} ,
'odbc'"
.CommandType = adCmdText
.Execute
End With
'IF this code is not called the INSERT bellow works fine, otherwise not--
--
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
'INSERT record on TABLE2--
With cmdCommand
.CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
('A', 'XXX', 'P')"
.CommandType = adCmdText
.Execute
End With
..
ErrorHandler:
' clean up
End SubDid you turn pooling off in your connection string? If not, then
another connection is being opened under the covers and in that
connection the approle is not active. There's more information at
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q229564.
--Mary
On Tue, 05 Sep 2006 13:42:54 GMT, "Daniel Rodrigues" <u26179@.uwe>
wrote:
>Hi
>I'm using an application role to restrict acess to the users to a Database
>The Application role is always activated without any problem.
>Nevertheless I'm receiving permission error messages ("XXXX permission denied
>on object 'TABLE', database...") when inserting, updating or deleting
>records if I open a recordset before this operations, otherwise everything
>works fine.
>Below is an example of this,
>If I call the following code before I trie to INSERT a record in TABLE2 I
>receive the permission error message
>..
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>..
>If I don't call the previous code the INSERT works ...
>(The AppRole have SELECT, INSET, UPDATE and DELETE permissions on TABLE1 and
>TABLE2)
>Do someone have any idea why this behavior ?
>I'll apreciate any help.
>Many Thanks
>Daniel
>EXAMPLE:
>--
>Private Sub CommandButton1_Click()
>On Error GoTo ErrorHandler
>Dim cnnConn As ADODB.Connection
>Dim rstRecordset As ADODB.Recordset
>Dim cmdCommand As ADODB.Command
>Set cnnConn = New ADODB.Connection
>With cnnConn
> .Open _
> "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
> "Persist Security Info=False;" & _
> "Initial Catalog=DBx;Data Source=SERVERx"
>End With
>'The AppRole is activated without problems--
>Set cmdCommand = New ADODB.Command
>Set cmdCommand.ActiveConnection = cnnConn
>With cmdCommand
> .CommandText = "Exec sp_setapprole AppRole, { Encrypt N Password} ,
>'odbc'"
> .CommandType = adCmdText
> .Execute
>End With
>'IF this code is not called the INSERT bellow works fine, otherwise not--
>--
>Set rstRecordset = New ADODB.Recordset
>rstRecordset.Open "SELECT * FROM TABLE1", cnnConn
>'INSERT record on TABLE2--
>With cmdCommand
> .CommandText = "INSERT INTO TABLE2 (tipo, departamento, estado) VALUES
>('A', 'XXX', 'P')"
> .CommandType = adCmdText
> .Execute
>End With
>..
>ErrorHandler:
> ' clean up
>End Sub|||Hello Mary
Yes.
I used in connection string "OLE DB Services = -2" and I also tried with
"Pooling=â'False " (despite I think this is only for .Net and I'm tried with
VB and Delphi6 with the same results.
I already read the KB article you mentioned.
Is there any other way to deactivate pooling ?
The only way I found to solve the problem was with one connection for the
selects and another one only for INSERT, UPDATE and DELETE statments.
By the way, i did't mentioned in my previous mail, I'm using SQLServer 2K
with SP4 and acessing with ADO using Delphi6 aplications.
Thanks for your answer
Best regards
Daniel
Mary Chipman [MSFT] wrote:
>Did you turn pooling off in your connection string? If not, then
>another connection is being opened under the covers and in that
>connection the approle is not active. There's more information at
>http://support.microsoft.com/default.aspx?scid=kb;en-us;Q229564.
>--Mary
>>Hi
>[quoted text clipped - 68 lines]
>> ' clean up
>>End Sub
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1

Application Role and Securityadmin

Helo,
I've got an application wich uses application roles... The problem is that
some of this users must add and remove users from the SQL Server. Since the
application role overrides the user settings I need to find a way for the
user to abandon the application role in order to gran or deny database
access, as well as adding or removing user logins from the SQL Server.
I have not found a way to abandon the application role in order to execute
this commands... or a way wich I could execute this commands without leaving
the application role.
Any one has a solution for this "problem"?
Thank you in advance.Juan
Just a guess
Perhaps you need to create a second app role with an appropriate permissions
and within the appliaction to check out to which of app role to set up.
"Juan" <ssccrriipptteerr@.tteerrrraa.eess> wrote in message
news:erFNliNwEHA.1976@.TK2MSFTNGP09.phx.gbl...
> Helo,
> I've got an application wich uses application roles... The problem is that
> some of this users must add and remove users from the SQL Server. Since
the
> application role overrides the user settings I need to find a way for the
> user to abandon the application role in order to gran or deny database
> access, as well as adding or removing user logins from the SQL Server.
> I have not found a way to abandon the application role in order to execute
> this commands... or a way wich I could execute this commands without
leaving
> the application role.
> Any one has a solution for this "problem"?
> Thank you in advance.
>|||I've been thinking about this a couple of days while reimplementing the
application...
If I use an application role I loose all the user privileges, therefore I'm
not part of the securityadministrators, therefore I can't add logins to my
server, neither I can grant database access. I need this for some of my
users (Finally I made this users a user role, and left all others as
Application roles).
As well, you can't grant the application Role security admin privileges,
since its not a session login on the server, and it's specific to a
database...
I guess I'll have to use my changes in the application (Application roles
for everyone except those who need the ability to add users)...
"Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
news:#lmA68YwEHA.1524@.TK2MSFTNGP09.phx.gbl...
> Juan
> Just a guess
> Perhaps you need to create a second app role with an appropriate
permissions
> and within the appliaction to check out to which of app role to set up.
>
>
> "Juan" <ssccrriipptteerr@.tteerrrraa.eess> wrote in message
> news:erFNliNwEHA.1976@.TK2MSFTNGP09.phx.gbl...
that[vbcol=seagreen]
> the
the[vbcol=seagreen]
execute[vbcol=seagreen]
> leaving
>|||Hi Juan,
Since the application role is only actived via application, you may still
let your users using the application role when they are using the
application, but make separate SQL connections using their own SQL login
accounts to add users/grant DB access.
Thanks,
Lan Lewis-Bevan
MS SQL support
This posting is provided "AS IS" with no warranties, and confers no rights.

application process don't continue after run trigger

Hi,
There is an application about cashier application process. I'd
wrote a trigger for send information messages to users. Send an e-mail
message when TransactType equal to db. But there is a problem, e-mail
message is sending but other application process is not continue and
record is not write to MSSQL database when TransactType equal to db.
Have any suggestion? Why is not application process continue after run
the Trigger proccess.
MSSQL version 7.0
Thanks.
CREATE TRIGGER sendmail_trigger ON [Transact]
FOR INSERT
AS
DECLARE
@.ptype varchar (100),
@.psubject varchar (100),
@.pacctid varchar (100),
@.ptransactamount varchar (100),
@.premail varchar (100),
@.pmessage varchar (900),
@.pname varchar (100)
set @.ptype = (select TransactType from Inserted)
if (@.ptype = 'db')
begin
set @.pacctid = (select AcctID from Inserted)
set @.ptransactamount = (select TransactAmount/-50 from Inserted)
set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
set @.psubject = 'Information Message'
set @.pmessage =
'Dear '+ @.pname+',
Debit '+ @.ptransactamount + ' your account.
For your information.'
EXEC master..xp_sendmail @.recipients = @.premail,
@.blind_copy_recipients = 'xxx@.yyy.zzz',
@.subject = @.psubject, @.message = @.pmessage
end
It is possible that you are getting an error, which may cause a scope
abort... Scope aborts do NOT return control back to the calling Sp etc, they
merely exit...
Comment out lines of code one at a time and see if you can find the line
which is hurting you... I suspect the email send may be causing the
problem...
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
"U A" <umuta@.sabanciuniv.edu> wrote in message
news:u1YkANWSEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> There is an application about cashier application process. I'd
> wrote a trigger for send information messages to users. Send an e-mail
> message when TransactType equal to db. But there is a problem, e-mail
> message is sending but other application process is not continue and
> record is not write to MSSQL database when TransactType equal to db.
> Have any suggestion? Why is not application process continue after run
> the Trigger proccess.
> MSSQL version 7.0
> Thanks.
> --
> CREATE TRIGGER sendmail_trigger ON [Transact]
> FOR INSERT
> AS
> DECLARE
> @.ptype varchar (100),
> @.psubject varchar (100),
> @.pacctid varchar (100),
> @.ptransactamount varchar (100),
> @.premail varchar (100),
> @.pmessage varchar (900),
> @.pname varchar (100)
> set @.ptype = (select TransactType from Inserted)
> if (@.ptype = 'db')
> begin
> set @.pacctid = (select AcctID from Inserted)
> set @.ptransactamount = (select TransactAmount/-50 from Inserted)
> set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
> set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
> set @.psubject = 'Information Message'
> set @.pmessage =
> 'Dear '+ @.pname+',
> Debit '+ @.ptransactamount + ' your account.
> For your information.'
> EXEC master..xp_sendmail @.recipients = @.premail,
> @.blind_copy_recipients = 'xxx@.yyy.zzz',
> @.subject = @.psubject, @.message = @.pmessage
> end
> --
>
|||Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
successfuly finished. But e-mail didn't send. What can I do for send
e-mail automatically this trigger method?
thanks.
On 03-06-2004 15:30, Wayne Snyder wrote:
> It is possible that you are getting an error, which may cause a scope
> abort... Scope aborts do NOT return control back to the calling Sp etc, they
> merely exit...
> Comment out lines of code one at a time and see if you can find the line
> which is hurting you... I suspect the email send may be causing the
> problem...
>
|||I suggest you don't send email from the trigger. Have the trigger to insert necessary information into a table
and create a job which runs every x minutes that reads off of this table and does the email sending.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"U A" <umuta@.sabanciuniv.edu> wrote in message news:e144dYXSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
> successfuly finished. But e-mail didn't send. What can I do for send
> e-mail automatically this trigger method?
> thanks.
>
> On 03-06-2004 15:30, Wayne Snyder wrote:
>
|||Thanks your suggestion but it gave same error. I had insert the records
to a new table and send e-mail with a remote perl script. I think,
trigger occasion exits from application.
thanks.
On 03-06-2004 18:55, Tibor Karaszi wrote:
> I suggest you don't send email from the trigger. Have the trigger to insert necessary
information into a table and create a job which runs every x minutes
that reads off of
this table and does the email sending.

application process don't continue after run trigger

Hi,
There is an application about cashier application process. I'd
wrote a trigger for send information messages to users. Send an e-mail
message when TransactType equal to db. But there is a problem, e-mail
message is sending but other application process is not continue and
record is not write to MSSQL database when TransactType equal to db.
Have any suggestion? Why is not application process continue after run
the Trigger proccess.
MSSQL version 7.0
Thanks.
--
CREATE TRIGGER sendmail_trigger ON [Transact]
FOR INSERT
AS
DECLARE
@.ptype varchar (100),
@.psubject varchar (100),
@.pacctid varchar (100),
@.ptransactamount varchar (100),
@.premail varchar (100),
@.pmessage varchar (900),
@.pname varchar (100)
set @.ptype = (select TransactType from Inserted)
if (@.ptype = 'db')
begin
set @.pacctid = (select AcctID from Inserted)
set @.ptransactamount = (select TransactAmount/-50 from Inserted)
set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
set @.psubject = 'Information Message'
set @.pmessage =
'Dear '+ @.pname+',
Debit '+ @.ptransactamount + ' your account.
For your information.'
EXEC master..xp_sendmail @.recipients = @.premail,
@.blind_copy_recipients = 'xxx@.yyy.zzz',
@.subject = @.psubject, @.message = @.pmessage
end
--It is possible that you are getting an error, which may cause a scope
abort... Scope aborts do NOT return control back to the calling Sp etc, they
merely exit...
Comment out lines of code one at a time and see if you can find the line
which is hurting you... I suspect the email send may be causing the
problem...
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
"U A" <umuta@.sabanciuniv.edu> wrote in message
news:u1YkANWSEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> There is an application about cashier application process. I'd
> wrote a trigger for send information messages to users. Send an e-mail
> message when TransactType equal to db. But there is a problem, e-mail
> message is sending but other application process is not continue and
> record is not write to MSSQL database when TransactType equal to db.
> Have any suggestion? Why is not application process continue after run
> the Trigger proccess.
> MSSQL version 7.0
> Thanks.
> --
> CREATE TRIGGER sendmail_trigger ON [Transact]
> FOR INSERT
> AS
> DECLARE
> @.ptype varchar (100),
> @.psubject varchar (100),
> @.pacctid varchar (100),
> @.ptransactamount varchar (100),
> @.premail varchar (100),
> @.pmessage varchar (900),
> @.pname varchar (100)
> set @.ptype = (select TransactType from Inserted)
> if (@.ptype = 'db')
> begin
> set @.pacctid = (select AcctID from Inserted)
> set @.ptransactamount = (select TransactAmount/-50 from Inserted)
> set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
> set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
> set @.psubject = 'Information Message'
> set @.pmessage =
> 'Dear '+ @.pname+',
> Debit '+ @.ptransactamount + ' your account.
> For your information.'
> EXEC master..xp_sendmail @.recipients = @.premail,
> @.blind_copy_recipients = 'xxx@.yyy.zzz',
> @.subject = @.psubject, @.message = @.pmessage
> end
> --
>|||Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
successfuly finished. But e-mail didn't send. What can I do for send
e-mail automatically this trigger method?
thanks.
On 03-06-2004 15:30, Wayne Snyder wrote:
> It is possible that you are getting an error, which may cause a scope
> abort... Scope aborts do NOT return control back to the calling Sp etc, th
ey
> merely exit...
> Comment out lines of code one at a time and see if you can find the line
> which is hurting you... I suspect the email send may be causing the
> problem...
>|||I suggest you don't send email from the trigger. Have the trigger to insert
necessary information into a table
and create a job which runs every x minutes that reads off of this table and
does the email sending.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"U A" <umuta@.sabanciuniv.edu> wrote in message news:e144dYXSEHA.3872@.TK2MSFTNGP10.phx.gbl...

> Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
> successfuly finished. But e-mail didn't send. What can I do for send
> e-mail automatically this trigger method?
> thanks.
>
> On 03-06-2004 15:30, Wayne Snyder wrote:
>|||Thanks your suggestion but it gave same error. I had insert the records
to a new table and send e-mail with a remote PERL script. I think,
trigger occasion exits from application.
thanks.
On 03-06-2004 18:55, Tibor Karaszi wrote:
> I suggest you don't send email from the trigger. Have the trigger to insert necess
ary
information into a table and create a job which runs every x minutes
that reads off of
this table and does the email sending.

application process don't continue after run trigger

Hi,
There is an application about cashier application process. I'd
wrote a trigger for send information messages to users. Send an e-mail
message when TransactType equal to db. But there is a problem, e-mail
message is sending but other application process is not continue and
record is not write to MSSQL database when TransactType equal to db.
Have any suggestion? Why is not application process continue after run
the Trigger proccess.
MSSQL version 7.0
Thanks.
--
CREATE TRIGGER sendmail_trigger ON [Transact]
FOR INSERT
AS
DECLARE
@.ptype varchar (100),
@.psubject varchar (100),
@.pacctid varchar (100),
@.ptransactamount varchar (100),
@.premail varchar (100),
@.pmessage varchar (900),
@.pname varchar (100)
set @.ptype = (select TransactType from Inserted)
if (@.ptype = 'db')
begin
set @.pacctid = (select AcctID from Inserted)
set @.ptransactamount = (select TransactAmount/-50 from Inserted)
set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
set @.psubject = 'Information Message'
set @.pmessage = 'Dear '+ @.pname+',
Debit '+ @.ptransactamount + ' your account.
For your information.'
EXEC master..xp_sendmail @.recipients = @.premail,
@.blind_copy_recipients = 'xxx@.yyy.zzz',
@.subject = @.psubject, @.message = @.pmessage
end
--It is possible that you are getting an error, which may cause a scope
abort... Scope aborts do NOT return control back to the calling Sp etc, they
merely exit...
Comment out lines of code one at a time and see if you can find the line
which is hurting you... I suspect the email send may be causing the
problem...
--
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
"U A" <umuta@.sabanciuniv.edu> wrote in message
news:u1YkANWSEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> There is an application about cashier application process. I'd
> wrote a trigger for send information messages to users. Send an e-mail
> message when TransactType equal to db. But there is a problem, e-mail
> message is sending but other application process is not continue and
> record is not write to MSSQL database when TransactType equal to db.
> Have any suggestion? Why is not application process continue after run
> the Trigger proccess.
> MSSQL version 7.0
> Thanks.
> --
> CREATE TRIGGER sendmail_trigger ON [Transact]
> FOR INSERT
> AS
> DECLARE
> @.ptype varchar (100),
> @.psubject varchar (100),
> @.pacctid varchar (100),
> @.ptransactamount varchar (100),
> @.premail varchar (100),
> @.pmessage varchar (900),
> @.pname varchar (100)
> set @.ptype = (select TransactType from Inserted)
> if (@.ptype = 'db')
> begin
> set @.pacctid = (select AcctID from Inserted)
> set @.ptransactamount = (select TransactAmount/-50 from Inserted)
> set @.premail = (SELECT EmailAddress FROM Users WHERE AcctID=@.pacctid)
> set @.pname = (SELECT UserName FROM Users WHERE AcctID=@.pacctid)
> set @.psubject = 'Information Message'
> set @.pmessage => 'Dear '+ @.pname+',
> Debit '+ @.ptransactamount + ' your account.
> For your information.'
> EXEC master..xp_sendmail @.recipients = @.premail,
> @.blind_copy_recipients = 'xxx@.yyy.zzz',
> @.subject = @.psubject, @.message = @.pmessage
> end
> --
>|||Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
successfuly finished. But e-mail didn't send. What can I do for send
e-mail automatically this trigger method?
thanks.
On 03-06-2004 15:30, Wayne Snyder wrote:
> It is possible that you are getting an error, which may cause a scope
> abort... Scope aborts do NOT return control back to the calling Sp etc, they
> merely exit...
> Comment out lines of code one at a time and see if you can find the line
> which is hurting you... I suspect the email send may be causing the
> problem...
>|||I suggest you don't send email from the trigger. Have the trigger to insert necessary information into a table
and create a job which runs every x minutes that reads off of this table and does the email sending.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"U A" <umuta@.sabanciuniv.edu> wrote in message news:e144dYXSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Many thanks. I had comment out line of EXEC master..xp_sendmail. Process
> successfuly finished. But e-mail didn't send. What can I do for send
> e-mail automatically this trigger method?
> thanks.
>
> On 03-06-2004 15:30, Wayne Snyder wrote:
> > It is possible that you are getting an error, which may cause a scope
> > abort... Scope aborts do NOT return control back to the calling Sp etc, they
> > merely exit...
> >
> > Comment out lines of code one at a time and see if you can find the line
> > which is hurting you... I suspect the email send may be causing the
> > problem...
> >
>|||Thanks your suggestion but it gave same error. I had insert the records
to a new table and send e-mail with a remote perl script. I think,
trigger occasion exits from application.
thanks.
On 03-06-2004 18:55, Tibor Karaszi wrote:
> I suggest you don't send email from the trigger. Have the trigger to insert necessary
information into a table and create a job which runs every x minutes
that reads off of
this table and does the email sending.

Application On Start And Database connection

Hello,
I was wondering if someone could help me answer this question.

If I had a site like Yahoo, that constantly had users accessing it. Would it be a good or bad idea to automatically open a database connection to SQL Server on Application Start? What would you recommend? Would opening and closing the DB connection on every page save alot of overhead and bottleneck? Thank You.> Would it be a good or bad idea to automatically open a database connection to SQL Server on Application Start?

BAD idea. horrible in fact.

open a database connection immediately before you need it and close it immediately after.

remember, connection pooling is handled for you.. so it's hardly a huge overhead.|||Thank You for your help.

Application needs replication

Hi
We have an MS Access application which runs on the server but some laptop
users need the app to work offline. Our solution has been to use Access
replication which automatically syncs data (both ways) when laptop is
connected to the network. We are looking to rewrite the app to vb.net/sql
server. My question is how does replication work in sql server specially in
context of a vb.net front end i.e. what sort of coding/configuration we are
we looking at?
Thanks
RegardsWell since you will have disconnected users; you will need to have a local
instance of the SQL Server installed on the computer. Then you can SQL
Server 2005 Merge Replication Topology to do what yaa needed. Read up on it
at ..
http://technet.microsoft.com/en-us/sqlserver/bb331775.aspx
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John" wrote:
> Hi
> We have an MS Access application which runs on the server but some laptop
> users need the app to work offline. Our solution has been to use Access
> replication which automatically syncs data (both ways) when laptop is
> connected to the network. We are looking to rewrite the app to vb.net/sql
> server. My question is how does replication work in sql server specially in
> context of a vb.net front end i.e. what sort of coding/configuration we are
> we looking at?
> Thanks
> Regards
>
>|||Am I right to believe I need to code as if my app is connected to the same
single sql server and various sql server installs would automatically take
care of the sync between themselves without intervention by app when laptops
are connected to the network?
Thanks
Regards
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:D4FD10E6-2DF6-486A-B6F9-88000C5A076A@.microsoft.com...
> Well since you will have disconnected users; you will need to have a local
> instance of the SQL Server installed on the computer. Then you can SQL
> Server 2005 Merge Replication Topology to do what yaa needed. Read up on
> it
> at ..
> http://technet.microsoft.com/en-us/sqlserver/bb331775.aspx
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John" wrote:
>> Hi
>> We have an MS Access application which runs on the server but some laptop
>> users need the app to work offline. Our solution has been to use Access
>> replication which automatically syncs data (both ways) when laptop is
>> connected to the network. We are looking to rewrite the app to vb.net/sql
>> server. My question is how does replication work in sql server specially
>> in
>> context of a vb.net front end i.e. what sort of coding/configuration we
>> are
>> we looking at?
>> Thanks
>> Regards
>>|||In a sense yes, but actually no. What I mean is, since you will have a
local install of SQL Server on each laptop; you can code the application to
connect to localhost and no coding required for developer for connecting
parts. But in fact; these are all different instances of SQL Server on
different computer, everyone will have different names.
I haven't coded for replication enviornment, but few challanges that face
you developers is handling merge conflicts. Three possible cases can happen:
1) Record is updated, but no longer exists.
2) Insert a record; and causes a duplicate primary key.
3) Updating same records in two locations, both valid but are in conflict.
Replication is usually timed event; so onces the server is on the network,
the local server will talk to the distributor and the publitioning database
to update information as needed. I hope this gives you some ideas. Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John" wrote:
> Am I right to believe I need to code as if my app is connected to the same
> single sql server and various sql server installs would automatically take
> care of the sync between themselves without intervention by app when laptops
> are connected to the network?
> Thanks
> Regards
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:D4FD10E6-2DF6-486A-B6F9-88000C5A076A@.microsoft.com...
> > Well since you will have disconnected users; you will need to have a local
> > instance of the SQL Server installed on the computer. Then you can SQL
> > Server 2005 Merge Replication Topology to do what yaa needed. Read up on
> > it
> > at ..
> > http://technet.microsoft.com/en-us/sqlserver/bb331775.aspx
> >
> > Thanks!
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
> >
> >
> > "John" wrote:
> >
> >> Hi
> >>
> >> We have an MS Access application which runs on the server but some laptop
> >> users need the app to work offline. Our solution has been to use Access
> >> replication which automatically syncs data (both ways) when laptop is
> >> connected to the network. We are looking to rewrite the app to vb.net/sql
> >> server. My question is how does replication work in sql server specially
> >> in
> >> context of a vb.net front end i.e. what sort of coding/configuration we
> >> are
> >> we looking at?
> >>
> >> Thanks
> >>
> >> Regards
> >>
> >>
> >>
>
>|||John,
You have at least two possiblilties, use a replication of the SQL server
using by instance SQL Client, or make your application in a way that it uses
DataSets from the needed information.
The latter is very easy to do and the way I would go in your situation.
Cor|||And you don't need SQL Server installed on the client(s). Another
alternative is SQL Server Compact Edition on the clients. It can also act as
a Subscriber to a SQL Server Publisher. Note that a SQL Server Publisher
must be Workgroup or better--SQL Express does not support this
functionality. In addition, you might consider using the new ADO.NET 3.5
Sync Services due out with Orcas (before the end of the year). In this case
you don't need SQL Server to act as a Publisher.
I discuss most of these options in my Ebook on SQL CE.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"John" <John@.nospam.infovis.co.uk> wrote in message
news:O4l0ZMECIHA.324@.TK2MSFTNGP04.phx.gbl...
> Hi
> We have an MS Access application which runs on the server but some laptop
> users need the app to work offline. Our solution has been to use Access
> replication which automatically syncs data (both ways) when laptop is
> connected to the network. We are looking to rewrite the app to vb.net/sql
> server. My question is how does replication work in sql server specially
> in context of a vb.net front end i.e. what sort of coding/configuration we
> are we looking at?
> Thanks
> Regards
>|||Hi Cor
Thanks. How does one keep data persistent on client side when using
datasets, bearing in mind that clients (laptops) will be disconnected from
the network/sql server often but would need access to sql server data
offline? If you can point to some reading that would be great.
Many Thanks
Regards
"Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
news:14F13D20-64C8-46CB-BEF1-CC700A4A6474@.microsoft.com...
> John,
> You have at least two possiblilties, use a replication of the SQL server
> using by instance SQL Client, or make your application in a way that it
> uses DataSets from the needed information.
> The latter is very easy to do and the way I would go in your situation.
> Cor
>|||Take a look at Sybase SQL Anywhere. It's replication capabilities are truly
amazing.
"John" <John@.nospam.infovis.co.uk> wrote in message
news:O4l0ZMECIHA.324@.TK2MSFTNGP04.phx.gbl...
> Hi
> We have an MS Access application which runs on the server but some laptop
> users need the app to work offline. Our solution has been to use Access
> replication which automatically syncs data (both ways) when laptop is
> connected to the network. We are looking to rewrite the app to vb.net/sql
> server. My question is how does replication work in sql server specially
> in context of a vb.net front end i.e. what sort of coding/configuration we
> are we looking at?
> Thanks
> Regards
>|||John,
The nature from a dataset is to work ofline, it is build for that.
When you do an update, there is looked if there has been a change (this is
called optimistic concurrency). The way as the data is organised makes that
the chanch for that is low or high.
By instance as you are adding and subtrackting values from tablerows,
instead of adding mutation rows, then you can probably forget it.
Cor|||Ah, I expect he means you can serialize the DataTable(s) to an XML file or
somesuch.
Again, the SQL Server Compact Edition (which supports several kinds of
replication) is a better option.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
news:522F9B5D-D648-4D7B-99F1-58EAB1B33923@.microsoft.com...
> John,
> The nature from a dataset is to work ofline, it is build for that.
> When you do an update, there is looked if there has been a change (this is
> called optimistic concurrency). The way as the data is organised makes
> that the chanch for that is low or high.
> By instance as you are adding and subtrackting values from tablerows,
> instead of adding mutation rows, then you can probably forget it.
> Cor|||Thanks Bill.
Regards
"William Vaughn" <billvaNoSPAM@.betav.com> wrote in message
news:OYv3bNdCIHA.6012@.TK2MSFTNGP03.phx.gbl...
> Ah, I expect he means you can serialize the DataTable(s) to an XML file
> or somesuch.
> Again, the SQL Server Compact Edition (which supports several kinds of
> replication) is a better option.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
> news:522F9B5D-D648-4D7B-99F1-58EAB1B33923@.microsoft.com...
>> John,
>> The nature from a dataset is to work ofline, it is build for that.
>> When you do an update, there is looked if there has been a change (this
>> is called optimistic concurrency). The way as the data is organised makes
>> that the chanch for that is low or high.
>> By instance as you are adding and subtrackting values from tablerows,
>> instead of adding mutation rows, then you can probably forget it.
>> Cor
>|||Bill,
I disagree this with you, for extra database you need replication, with a
dataset you just needs common updates. The logical problems stays exactly
the same.
The serialization is completely automaticaly done by Dataset.ReadXML(...)
and Dataset.WriteXML(...), you write it as if this is a problem.
Cor|||Cor, you missed the point. Replication in this case is used for data
sharing--not just data persistence.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
news:C859F647-3DB1-404A-AC9E-10213DDFA7AC@.microsoft.com...
> Bill,
> I disagree this with you, for extra database you need replication, with a
> dataset you just needs common updates. The logical problems stays exactly
> the same.
> The serialization is completely automaticaly done by Dataset.ReadXML(...)
> and Dataset.WriteXML(...), you write it as if this is a problem.
> Cor|||> Cor, you missed the point. Replication in this case is used for data
> sharing--not just data persistence.
I did not miss that Bill, however why would you do that if there are now
simple methods possible in ADONET and not only replication. Using a dataset
is in my idea much easier to handle conflicts by your own program.
Cor

Appending a custom sql where clause

Hello,
I am in the process of evaluating SSRS 2005 to replace an home grown
reporting tool. In my reporting application all users have access all
the tables\fields in the database. In the home grown tool, data
security is implemented by the following mechanism. When users run
reports, a standard sql where clause is appended to the sql generated
by the reproting tool. This standard where clause has @.userID as the
parameter.
Now is there a way in SSRS 2005 I can append a standard where clause to
every report just before it is run? Does it have an event model, I can
hook into?
Thanks
_Gigi JKIn most cases query parameters are mapped to report parameters but they do
not have to be, they can be mapped to expressions. When mapping a query
parameter to an expression you can map the query parameter to a global
variable. One of the global variables available is User!UserID. This
variable has the user (and their domain). If you don't want the domain then
you would strip the domain off.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<gigijk@.gmail.com> wrote in message
news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
> Hello,
> I am in the process of evaluating SSRS 2005 to replace an home grown
> reporting tool. In my reporting application all users have access all
> the tables\fields in the database. In the home grown tool, data
> security is implemented by the following mechanism. When users run
> reports, a standard sql where clause is appended to the sql generated
> by the reproting tool. This standard where clause has @.userID as the
> parameter.
> Now is there a way in SSRS 2005 I can append a standard where clause to
> every report just before it is run? Does it have an event model, I can
> hook into?
> Thanks
> _Gigi JK
>|||Bruce,
Thanks for the reply. Sorry I sent the same question to you directly from my
gmail address as well.
In my case, reporting application will be intergrated into another
application which does not use NT auth. Is there a way I could pass in userID
to reporting tool?
On the original issue, what I want to do is to automatically append a where
clause ( for eg: 'AND sysem_id IN (SELECT System_id FROM Mdu_system where
user_id=@.userID') to every query generated by the reporting tool withou the
user intervention. Would this be posssible.
Once again thank you.
_GJK
"Bruce L-C [MVP]" wrote:
> In most cases query parameters are mapped to report parameters but they do
> not have to be, they can be mapped to expressions. When mapping a query
> parameter to an expression you can map the query parameter to a global
> variable. One of the global variables available is User!UserID. This
> variable has the user (and their domain). If you don't want the domain then
> you would strip the domain off.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <gigijk@.gmail.com> wrote in message
> news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
> > Hello,
> > I am in the process of evaluating SSRS 2005 to replace an home grown
> > reporting tool. In my reporting application all users have access all
> > the tables\fields in the database. In the home grown tool, data
> > security is implemented by the following mechanism. When users run
> > reports, a standard sql where clause is appended to the sql generated
> > by the reproting tool. This standard where clause has @.userID as the
> > parameter.
> >
> > Now is there a way in SSRS 2005 I can append a standard where clause to
> > every report just before it is run? Does it have an event model, I can
> > hook into?
> >
> > Thanks
> > _Gigi JK
> >
>
>|||You can have a hidden parameter (i.e. it does not prompt for it but you can
include it when running the report).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GJK" <GJK@.discussions.microsoft.com> wrote in message
news:D37B86F8-AA55-4FF1-BC01-C426D806AF02@.microsoft.com...
> Bruce,
> Thanks for the reply. Sorry I sent the same question to you directly from
> my
> gmail address as well.
> In my case, reporting application will be intergrated into another
> application which does not use NT auth. Is there a way I could pass in
> userID
> to reporting tool?
> On the original issue, what I want to do is to automatically append a
> where
> clause ( for eg: 'AND sysem_id IN (SELECT System_id FROM Mdu_system where
> user_id=@.userID') to every query generated by the reporting tool withou
> the
> user intervention. Would this be posssible.
> Once again thank you.
> _GJK
> "Bruce L-C [MVP]" wrote:
>> In most cases query parameters are mapped to report parameters but they
>> do
>> not have to be, they can be mapped to expressions. When mapping a query
>> parameter to an expression you can map the query parameter to a global
>> variable. One of the global variables available is User!UserID. This
>> variable has the user (and their domain). If you don't want the domain
>> then
>> you would strip the domain off.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <gigijk@.gmail.com> wrote in message
>> news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
>> > Hello,
>> > I am in the process of evaluating SSRS 2005 to replace an home grown
>> > reporting tool. In my reporting application all users have access all
>> > the tables\fields in the database. In the home grown tool, data
>> > security is implemented by the following mechanism. When users run
>> > reports, a standard sql where clause is appended to the sql generated
>> > by the reproting tool. This standard where clause has @.userID as the
>> > parameter.
>> >
>> > Now is there a way in SSRS 2005 I can append a standard where clause to
>> > every report just before it is run? Does it have an event model, I can
>> > hook into?
>> >
>> > Thanks
>> > _Gigi JK
>> >
>>

Aparent DataReader collisions/conflicts between users

This is causing me such grief! Any help would be MUCH appreciated.

Basic summary of the problem:

I have an Intranet site I have developed that uses a SQL Server backend. It uses SqlConnection and SqlDataReader objects to get data form the database.

When more than one person uses the site at the same time, conflicts occur. It seems that the users' processes are attempting to share the SqlDataReaders, which results in problems because one user tries to open the SqlDataReader but it is already open by the other user, and similarly when they try to close a DataReader.

Full details are as follows:

SQL Server 7 backend database

webserver running Windows Windows 2003 server 5.2 SP1

IIS 6.0

.Net version 1.1 1927

Aparent collisions are occuring when more than one person works on the website at the same time.

I am using SQLConnection and SQLDataReader objects for data access from the database, a typical example being:

in Global.asax.vb:

Application("SQLString") = "workstation id={WebserverName};packet size=4096;user id=sa;password={password};data source={SQLServermachine};persist security info=False;initial catalog={DatabaseName}"

in a local class module:

Dim SQLConnection_docstatustable =New SQLConnection(HttpContext.Current.Application("SQLString"))

PublicFunction GetAll()As DocStatuses

'Returns a collection of all the Document Statuses from the database

Dim TheseDocStatusesAsNew DocStatuses

Dim commandAsNew SqlCommand("select * from lkDocStatus where Deleted = 0", SQLConnection_docstatustable)

Dim drAs SqlDataReader

Try

If SQLConnection_docstatustable.State = ConnectionState.ClosedThen SQLConnection_docstatustable.Open()

dr = command.ExecuteReader(CommandBehavior.SingleResult)

While dr.Read()

TheseDocStatuses.Add(PopulateItem(dr))

EndWhile

Finally

IfNot drIsNothingAndAlsoNot dr.IsClosedThen

dr.Close()

EndIf

SQLConnection_docstatustable.Close()

command.Dispose()

EndTry

Return TheseDocStatuses

EndFunction

The above function uses the following function to read each record as it loops through the datareader:

Function PopulateItem(ByVal drAs IDataRecord)As DocStatus

Dim thisDocStatusAsNew DocStatus

thisDocStatus.DocStatusID = Convert.ToInt32(dr("DocStatID"))

IfNot dr("DocStatus")Is DBNull.ValueThen

thisDocStatus.DocStatusString = Convert.ToString(dr("DocStatus"))

EndIf

Return thisDocStatus

EndFunction

This example is a very small one, but all my database queries are done in pretty much the same way. The main difference is that most include a lot more fields.

I have consistently used try/finally blocks, and closed both the SQL Connection and the DataReader in there, so that everything is being tidied up for each specific user.

Depending on precisely what point the two users are at when the problem happens, I get various different .NET error messages, but they all seem to boil down to collisions between the different users, with particular regard to DataReaders.

Some error messages I have received:

Object reference not set to an instance of an object - pointing to adr = command.ExecuteReader(CommandBehavior.SingleResult) statement (I take it to mean the other user's process has just closed this DataReader).

There is already an open DataReader associated with this Connection which must be closed first - pointing to the same place (I take it to mean the other user's process has just opened this DataReader).

ExecuteReader requires an open and available Connection. The connection's current state is closed - pointing to the same place again (I assume the other users's process has just closed the Connection).

Also:

Internal Connection Fatal Error pointing - toSQLConnection_productstable.Close()(I take this also to mean that the other user's process has just closed this Connection).

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding (I am not sure about this one)

Finally, I get one or two errors that sound like queries may not have not returned all the expected records from the database, perhaps because of a DataReader closing too early.

I understand that .NET 1.1 allows only one datareader per connection, but when I tried turning connection pooling off it made no difference. Nor did allowing a high Min Pool size, although when I tried this I could see the many connections in the Windows Perfomance Manager.

I don't really see why I need to check whether the connection object is closed before opening it, or why I need to check that the datareader is not nothing or closed before closing it, but I found I got worse results still if I didn't do these.

I have tried to replicate this problem on my own computer, but it doesn't happen. This is a standalone PC with SQL Server and webserver all on the same machine. The software is the same, except that I have Windows XP Pro 5.1 SP2, and IIS 5.1 The fact this works OK on mine makes me wonder if there might be a .NET setting different on the two, or perhaps the network might cause problems - their webserver is separate from their SQL one. But the errors I am getting do sound like .NET issues that I could fix by changing the program, if only I could work out what to change!

Regards

David Vaughan

Are any of your data access classes or their functions/properties declared as static or shared?|||I was having issues very similar to yours, and suspected that there was either some sort of cross-contamination of my Connection objects or that the DataReader objects used internally by DataSets were somehow not being closed properly prior to the DataReader's associated Connection being returned to the pool.

Apparently, neither was the case. It's still too early in our testing to tell, but it looks like our actual cuplrits were a handful of procedures that used DataReader objects directly. In each case, the DataReader was either not issued a Close followed by a Dispose, or was not nested inside a try/catch block to do the same on an exception.

So, if I were you, I'd comb through the code once more to see if you have any "bad apples" like this spoiling the bunch. In our case, about 1-2 dozen such procedure calls were to blame, hidden within about 19000 total database calls from managed code over the course of roughly a half hour.|||

misfit815:

Apparently, neither was the case. It's still too early in our testing to tell, but it looks like our actual cuplrits were a handful of procedures that used DataReader objects directly. In each case, the DataReader was either not issued a Close followed by a Dispose, or was not nested inside a try/catch block to do the same on an exception.

I've had the same experience as misfit815. All of my DataReaders are encapsulated using blocks (It's like a Try/Finally block which autodisposes), and I make sure that I don't call other functions that might hit the database inside one of those blocks.