Database Modification (Insert/Update/Delete) Event Notifications

Hello,

I am new to SQL Server and am building a C# application which will use a local SQL Server Express 2005installation. My application will be interacting with a DB Schema with approx 250 tables and the schema is already defined. My application is responsible for monitoring changes to the data in the DB. It must watch for all data changes in the database (Insert/Update/Delete). I am looking for a way to monitor changes to the DB data due to Insert/Update/Delete activity.

Is there a simple way in SQL Server for my application to receive events whenever the table content is modified. I have come up with a few ideas myself, for example adding trigger to each table to update an additional Event table that I could look at or even receive an Query Event on , but this swould mean creating and maitaining 750 triggers (250 tables x 3).

I can't help but feel there must be a simpler way, such as some type of SQL Audit or Monitoring feature that is already built in that could notifiy an application whenever there are DB data changes? Does such a feature exist? What would be the best way to accomplish this?

MarKGB

Trigger is invented mainly for this. If you have to audit every table, you could combine insert/update/delete event into 1 trigger. That will result in 250 triggers.|||

Hi,

if you are using SQL Server 2005 you can use the new query notification for getting the notification of changes to the database. In SQL Server 2000 I would suggest to go the way you already mentioned. Creating an event table and filling this via triggers based on the tables. Perhaps you can create a generic autiting trigger (Look in google for that there have been some samples arounf how to implement this.)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Great!, I didn't know you could combine the events to one trigger. That will certainly help reduce the amount of coding. Thanks!

Mark

|||

I did as you suggested and googled 'generic audit trigger' and got a lot of hits. In fact, I found a nice example that uses a generic CLR trigger and was able to get it working. The example can be found here:

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

I think using this in combination with a Query Event will give me what I am looking for.

Thanks for your help.

Mark

Database Mirroring. Asp application (IIS 6.0) does''t forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Client error '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

|||

Been banging my head against this for a few days, have applied Hotfix for Microsoft.Net Framework 2.0 (KB916002) see http://support.microsoft.com/kb/912151 this is meant to fix default timeout in dB driver - didn't help. Just tried your suggestion about connection pooling, added 'Pooling=False;' to connect string - didn't help.

Is it sensible to turn off connection pooling? my understanding was this provides a big boost to site performance so would MS make us sacrifice performance for resilience? Has anyone got this failover working?

sql

Database Mirroring. Asp application (IIS 6.0) does't forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Client error '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

Database Mirroring. Asp application (IIS 6.0) does't forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Clienterror '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

|||

Been banging my head against this for a few days, have applied Hotfix for Microsoft.Net Framework 2.0 (KB916002) see http://support.microsoft.com/kb/912151 this is meant to fix default timeout in dB driver - didn't help. Just tried your suggestion about connection pooling, added 'Pooling=False;' to connect string - didn't help.

Is it sensible to turn off connection pooling? my understanding was this provides a big boost to site performance so would MS make us sacrifice performance for resilience? Has anyone got this failover working?

Database Mirror failover from Principal to Mirror

All,
I have two sql servers setup for Data Mirroring. when I do a failover to the
mirror server. The application can not connect to the database Mirror server
that has become the Principal server. I'm using sql authentication for the
application.
I have to drop the user in the Database and recreate it to allow it to
connnect to the database. Below is the error message:
The server principal "nyccs" is not able to access the database "jztest"
under the current security context.
What security context do I have to setup the data Mirror inorder to
automatically failover without any failures.
Thanks,
Naana.
Message posted via http://www.droptable.comHave you read and followed the info here?
http://msdn2.microsoft.com/en-us/library/ms177412.aspx
In specific, check this section:
http://msdn2.microsoft.com/en-us/library/ms366346.aspx
"Naana via droptable.com" wrote:

> All,
> I have two sql servers setup for Data Mirroring. when I do a failover to t
he
> mirror server. The application can not connect to the database Mirror serv
er
> that has become the Principal server. I'm using sql authentication for the
> application.
> I have to drop the user in the Database and recreate it to allow it to
> connnect to the database. Below is the error message:
> The server principal "nyccs" is not able to access the database "jztest"
> under the current security context.
> What security context do I have to setup the data Mirror inorder to
> automatically failover without any failures.
> Thanks,
> Naana.
> --
> Message posted via http://www.droptable.com
>

Database migration from Sybase database to SQL Server 2005

We are going to be migrating an application and the first step is to
migration the Sybase database to SQL Server 2005 database. Can anyone shed
some light into this and help me understand the process involved? What are
the possible pitfalls? What should the approach be?
Thanks,
Newbie
Hi
I have never migrated from Sybase, but I would expect to treat it in a
similar way to migrating from SQL 6.5 to a higher version. If your code is
not already in Source Code control, then I you may want to make that a
pre-requisite for the migration. You should then be able to get to a stage
where you can build a SQL 2005 database from this code rather than use a
migration tool. When migrating the data you can use SSIS, although if you
require more control it may be worth considering using a two stage process
such as exporting the data into flat files and then importing it.
John
"NewToMigration" wrote:

> We are going to be migrating an application and the first step is to
> migration the Sybase database to SQL Server 2005 database. Can anyone shed
> some light into this and help me understand the process involved? What are
> the possible pitfalls? What should the approach be?
> Thanks,
> Newbie
>
|||Thanks, John, for your response. I will make sure I keep this in mind.
Regards,
Newbie
John Bell wrote:[vbcol=seagreen]
>Hi
>I have never migrated from Sybase, but I would expect to treat it in a
>similar way to migrating from SQL 6.5 to a higher version. If your code is
>not already in Source Code control, then I you may want to make that a
>pre-requisite for the migration. You should then be able to get to a stage
>where you can build a SQL 2005 database from this code rather than use a
>migration tool. When migrating the data you can use SSIS, although if you
>require more control it may be worth considering using a two stage process
>such as exporting the data into flat files and then importing it.
>John
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1
|||Hi
I forgot to add...
You will need to make sure that you do a full regression test.
Therefore having the "old" system to compare against will be useful!
John
"NewToMigration via droptable.com" wrote:

> Thanks, John, for your response. I will make sure I keep this in mind.
> Regards,
> Newbie
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200602/1
>

Database migration from Sybase database to SQL Server 2005

We are going to be migrating an application and the first step is to
migration the Sybase database to SQL Server 2005 database. Can anyone shed
some light into this and help me understand the process involved? What are
the possible pitfalls? What should the approach be?
Thanks,
NewbieHi
I have never migrated from Sybase, but I would expect to treat it in a
similar way to migrating from SQL 6.5 to a higher version. If your code is
not already in Source Code control, then I you may want to make that a
pre-requisite for the migration. You should then be able to get to a stage
where you can build a SQL 2005 database from this code rather than use a
migration tool. When migrating the data you can use SSIS, although if you
require more control it may be worth considering using a two stage process
such as exporting the data into flat files and then importing it.
John
"NewToMigration" wrote:

> We are going to be migrating an application and the first step is to
> migration the Sybase database to SQL Server 2005 database. Can anyone shed
> some light into this and help me understand the process involved? What are
> the possible pitfalls? What should the approach be?
> Thanks,
> Newbie
>|||Thanks, John, for your response. I will make sure I keep this in mind.
Regards,
Newbie
John Bell wrote:[vbcol=seagreen]
>Hi
>I have never migrated from Sybase, but I would expect to treat it in a
>similar way to migrating from SQL 6.5 to a higher version. If your code is
>not already in Source Code control, then I you may want to make that a
>pre-requisite for the migration. You should then be able to get to a stage
>where you can build a SQL 2005 database from this code rather than use a
>migration tool. When migrating the data you can use SSIS, although if you
>require more control it may be worth considering using a two stage process
>such as exporting the data into flat files and then importing it.
>John
>
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200602/1|||Hi
I forgot to add...
You will need to make sure that you do a full regression test.
Therefore having the "old" system to compare against will be useful!
John
"NewToMigration via droptable.com" wrote:

> Thanks, John, for your response. I will make sure I keep this in mind.
> Regards,
> Newbie
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200602/1
>

Database migration from Sybase database to SQL Server 2005

We are going to be migrating an application and the first step is to
migration the Sybase database to SQL Server 2005 database. Can anyone shed
some light into this and help me understand the process involved? What are
the possible pitfalls? What should the approach be?
Thanks,
NewbieHi
I have never migrated from Sybase, but I would expect to treat it in a
similar way to migrating from SQL 6.5 to a higher version. If your code is
not already in Source Code control, then I you may want to make that a
pre-requisite for the migration. You should then be able to get to a stage
where you can build a SQL 2005 database from this code rather than use a
migration tool. When migrating the data you can use SSIS, although if you
require more control it may be worth considering using a two stage process
such as exporting the data into flat files and then importing it.
John
"NewToMigration" wrote:
> We are going to be migrating an application and the first step is to
> migration the Sybase database to SQL Server 2005 database. Can anyone shed
> some light into this and help me understand the process involved? What are
> the possible pitfalls? What should the approach be?
> Thanks,
> Newbie
>|||Thanks, John, for your response. I will make sure I keep this in mind.
Regards,
Newbie
John Bell wrote:
>Hi
>I have never migrated from Sybase, but I would expect to treat it in a
>similar way to migrating from SQL 6.5 to a higher version. If your code is
>not already in Source Code control, then I you may want to make that a
>pre-requisite for the migration. You should then be able to get to a stage
>where you can build a SQL 2005 database from this code rather than use a
>migration tool. When migrating the data you can use SSIS, although if you
>require more control it may be worth considering using a two stage process
>such as exporting the data into flat files and then importing it.
>John
>> We are going to be migrating an application and the first step is to
>> migration the Sybase database to SQL Server 2005 database. Can anyone shed
>[quoted text clipped - 3 lines]
>> Thanks,
>> Newbie
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1|||Hi
I forgot to add...
You will need to make sure that you do a full regression test.
Therefore having the "old" system to compare against will be useful!
John
"NewToMigration via SQLMonster.com" wrote:
> Thanks, John, for your response. I will make sure I keep this in mind.
> Regards,
> Newbie
> John Bell wrote:
> >Hi
> >
> >I have never migrated from Sybase, but I would expect to treat it in a
> >similar way to migrating from SQL 6.5 to a higher version. If your code is
> >not already in Source Code control, then I you may want to make that a
> >pre-requisite for the migration. You should then be able to get to a stage
> >where you can build a SQL 2005 database from this code rather than use a
> >migration tool. When migrating the data you can use SSIS, although if you
> >require more control it may be worth considering using a two stage process
> >such as exporting the data into flat files and then importing it.
> >
> >John
> >
> >> We are going to be migrating an application and the first step is to
> >> migration the Sybase database to SQL Server 2005 database. Can anyone shed
> >[quoted text clipped - 3 lines]
> >> Thanks,
> >> Newbie
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1
>sql

Database Migration

I have a SQL database called "Acts" on SQL2000 server that
was created by installation of an application. On that SQL
server we have created Logins for this particular "Acts"
database so users can use the application. Management has
now asked us to move this database to another server with
a different server name. My question is can I just
backup "Acts" database on the old server and restore it on
the new server. Will that bring over the logins ID and
their info or if it doesn't what would I have to do to get
that info. Or would I have to backup the entire SQL2000
databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
from my old server and than restore all these databases on
my new server.
Any help is appreciated...
thanks,
JayBackup and restore is a good method to migrate a database. You can also use
sp_detach_db and sp_attach_db and copy the actual database files.
Regardless of which method you choose, you will need to create the logins
for the 'Acts' users on your new server and then link them to the users in
your newly migrated database. Look up sp_change_users_login in BOL for
details.
I strongly discourage migrating system databases between servers. If there
are any scheduled jobs, you can script them and apply them to the new server
much easier than you can fix a migrated MSDB database.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jay" <desiguys@.hotmail.com> wrote in message
news:0df101c35769$5b6d53e0$a301280a@.phx.gbl...
> I have a SQL database called "Acts" on SQL2000 server that
> was created by installation of an application. On that SQL
> server we have created Logins for this particular "Acts"
> database so users can use the application. Management has
> now asked us to move this database to another server with
> a different server name. My question is can I just
> backup "Acts" database on the old server and restore it on
> the new server. Will that bring over the logins ID and
> their info or if it doesn't what would I have to do to get
> that info. Or would I have to backup the entire SQL2000
> databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
> from my old server and than restore all these databases on
> my new server.
>
> Any help is appreciated...
> thanks,
> Jay
>|||These should help:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Jay" <desiguys@.hotmail.com> wrote in message
news:0df101c35769$5b6d53e0$a301280a@.phx.gbl...
> I have a SQL database called "Acts" on SQL2000 server that
> was created by installation of an application. On that SQL
> server we have created Logins for this particular "Acts"
> database so users can use the application. Management has
> now asked us to move this database to another server with
> a different server name. My question is can I just
> backup "Acts" database on the old server and restore it on
> the new server. Will that bring over the logins ID and
> their info or if it doesn't what would I have to do to get
> that info. Or would I have to backup the entire SQL2000
> databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
> from my old server and than restore all these databases on
> my new server.
>
> Any help is appreciated...
> thanks,
> Jay
>

Database management

Hi,
I want to import, export and backup databases through my application.
can anybody tell me the best way to program database management?
Thanx for any help!
HansEither use your favorite API (like ADO, ADO.NET etc) and just execute the SQ
L statements, like
BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQLD
MO. Just be aware that
DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to i
nvest too much effort
into this API.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
> Hi,
> I want to import, export and backup databases through my application.
> can anybody tell me the best way to program database management?
> Thanx for any help!
> Hans|||Hans,
Tibor makes a good point, however SQL-DMO *should* be backwards
compatible with SQL Server 2005. i.e. all SQL-DMO code should still
work, but will not support SQL Server 2005 features, only 2000/7 ones.
If you want SQL Server 2005 functionality then you would need to use SMO.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Tibor Karaszi wrote:
> Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to
invest too much effort
> into this API.
>|||Thanx for your suggestions!
Hans.
"Tibor Karaszi" wrote:

> Either use your favorite API (like ADO, ADO.NET etc) and just execute the
SQL statements, like
> BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQ
LDMO. Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to
invest too much effort
> into this API.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hans" <Hans@.discussions.microsoft.com> wrote in message
> news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
>
>

Database management

Hi,
I want to import, export and backup databases through my application.
can anybody tell me the best way to program database management?
Thanx for any help!
HansEither use your favorite API (like ADO, ADO.NET etc) and just execute the SQL statements, like
BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQLDMO. Just be aware that
DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
into this API.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
> Hi,
> I want to import, export and backup databases through my application.
> can anybody tell me the best way to program database management?
> Thanx for any help!
> Hans|||Hans,
Tibor makes a good point, however SQL-DMO *should* be backwards
compatible with SQL Server 2005. i.e. all SQL-DMO code should still
work, but will not support SQL Server 2005 features, only 2000/7 ones.
If you want SQL Server 2005 functionality then you would need to use SMO.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Tibor Karaszi wrote:
> Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
> into this API.
>|||Thanx for your suggestions!
Hans.
"Tibor Karaszi" wrote:
> Either use your favorite API (like ADO, ADO.NET etc) and just execute the SQL statements, like
> BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQLDMO. Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
> into this API.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hans" <Hans@.discussions.microsoft.com> wrote in message
> news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
> > Hi,
> >
> > I want to import, export and backup databases through my application.
> >
> > can anybody tell me the best way to program database management?
> >
> > Thanx for any help!
> > Hans
>
>

Database management

Hi,
I want to import, export and backup databases through my application.
can anybody tell me the best way to program database management?
Thanx for any help!
Hans
Either use your favorite API (like ADO, ADO.NET etc) and just execute the SQL statements, like
BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQLDMO. Just be aware that
DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
into this API.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
> Hi,
> I want to import, export and backup databases through my application.
> can anybody tell me the best way to program database management?
> Thanx for any help!
> Hans
|||Hans,
Tibor makes a good point, however SQL-DMO *should* be backwards
compatible with SQL Server 2005. i.e. all SQL-DMO code should still
work, but will not support SQL Server 2005 features, only 2000/7 ones.
If you want SQL Server 2005 functionality then you would need to use SMO.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Tibor Karaszi wrote:
> Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
> into this API.
>
|||Thanx for your suggestions!
Hans.
"Tibor Karaszi" wrote:

> Either use your favorite API (like ADO, ADO.NET etc) and just execute the SQL statements, like
> BACKUP, BULK INSERT etc. Or use an API designed for this purpose, i.e., SQLDMO. Just be aware that
> DMO will be "replaced" by SMO in SQL Server 2005, so you might not want to invest too much effort
> into this API.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hans" <Hans@.discussions.microsoft.com> wrote in message
> news:45A47639-9166-4A33-91FE-C7EF7B35DFFC@.microsoft.com...
>
>

Database Maintenance plans log

I receive every 30 min 5 event into my event viewer application log telling
me something like this
18265 :
Log backed up: Database: UserDatabases, creation date(time):
2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
devices: 1, device information: (FILE=1, TYPE=DISK:
{'H:\SQLBackup\UserDatabases\UserDatabases_tlog_20 0505120800.TRN'}).
Is there a way to remove the log to event viewer from a maintenante plan?
First its not giving anything good and its hard to find error since my event
viewer is full of those information.
Hi
You might be logging it into an Event Viewer. Just try to update the same or
try to remove the logging
best Regards,
Chandra
http://chanduas.blogspot.com/
"fatp" wrote:

> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_20 0505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>
|||Short answer: No. These entries are not written by Maint Plan nor by Agent. They are written by SQL
Server. Each time SQL Server does a backup, it will write that to the eventlog. For other error
messages, you can configure whether they should be written to the eventlog or not, but that
configuration (sp_altermessage) is ignored for backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fatp" <fatp@.discussions.microsoft.com> wrote in message
news:AC8C5172-D6CA-4E33-8F36-D7D59B822692@.microsoft.com...
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_20 0505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>

Database Maintenance plans log

I receive every 30 min 5 event into my event viewer application log telling
me something like this
18265 :
Log backed up: Database: UserDatabases, creation date(time):
2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
devices: 1, device information: (FILE=1, TYPE=DISK:
{'H:\SQLBackup\UserDatabases\UserDatabases_tlog_200505120800.TRN'}).
Is there a way to remove the log to event viewer from a maintenante plan?
First its not giving anything good and its hard to find error since my event
viewer is full of those information.Hi
You might be logging it into an Event Viewer. Just try to update the same or
try to remove the logging
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"fatp" wrote:
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>|||Short answer: No. These entries are not written by Maint Plan nor by Agent. They are written by SQL
Server. Each time SQL Server does a backup, it will write that to the eventlog. For other error
messages, you can configure whether they should be written to the eventlog or not, but that
configuration (sp_altermessage) is ignored for backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fatp" <fatp@.discussions.microsoft.com> wrote in message
news:AC8C5172-D6CA-4E33-8F36-D7D59B822692@.microsoft.com...
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>

Database Maintenance plans log

I receive every 30 min 5 event into my event viewer application log telling
me something like this
18265 :
Log backed up: Database: UserDatabases, creation date(time):
2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dum
p
devices: 1, device information: (FILE=1, TYPE=DISK:
& #123;'H:\SQLBackup\UserDatabases\UserDat
abases_tlog_200505120800.TRN'}).
Is there a way to remove the log to event viewer from a maintenante plan?
First its not giving anything good and its hard to find error since my event
viewer is full of those information.Hi
You might be logging it into an Event Viewer. Just try to update the same or
try to remove the logging
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"fatp" wrote:

> I receive every 30 min 5 event into my event viewer application log telli
ng
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of d
ump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> & #123;'H:\SQLBackup\UserDatabases\UserDat
abases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my eve
nt
> viewer is full of those information.
>|||Short answer: No. These entries are not written by Maint Plan nor by Agent.
They are written by SQL
Server. Each time SQL Server does a backup, it will write that to the eventl
og. For other error
messages, you can configure whether they should be written to the eventlog o
r not, but that
configuration (sp_altermessage) is ignored for backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fatp" <fatp@.discussions.microsoft.com> wrote in message
news:AC8C5172-D6CA-4E33-8F36-D7D59B822692@.microsoft.com...
> I receive every 30 min 5 event into my event viewer application log tellin
g
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of d
ump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> & #123;'H:\SQLBackup\UserDatabases\UserDat
abases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my eve
nt
> viewer is full of those information.
>

Database Mail and Application Roles

I am in the middle of converting our application to use SQL 2005. I
need to support "Database Mail" (I guess it was called SQLiMail) as a
replacement for SQLMail.
As soon as I change into the Application Role lose my access to MSDB
and sp_send_dbmail.
How can I maintain the Application Role and still send mail?
Thanks
-ChadHas anyone using Application Roles in SQL 2005 been able to use the old
xp_ procs like xp_sendmail? I keep getting permission issues and I'm
having a problem building work-arounds.
Thanks
-Chad

Database Mail and Application Roles

I am in the middle of converting our application to use SQL 2005. I
need to support "Database Mail" (I guess it was called SQLiMail) as a
replacement for SQLMail.
As soon as I change into the Application Role lose my access to MSDB
and sp_send_dbmail.
How can I maintain the Application Role and still send mail?
Thanks
-Chad
Has anyone using Application Roles in SQL 2005 been able to use the old
xp_ procs like xp_sendmail? I keep getting permission issues and I'm
having a problem building work-arounds.
Thanks
-Chad

Database Mail and Application Roles

I am in the middle of converting our application to use SQL 2005. I
need to support "Database Mail" (I guess it was called SQLiMail) as a
replacement for SQLMail.
As soon as I change into the Application Role lose my access to MSDB
and sp_send_dbmail.
How can I maintain the Application Role and still send mail?
Thanks
-ChadHas anyone using Application Roles in SQL 2005 been able to use the old
xp_ procs like xp_sendmail? I keep getting permission issues and I'm
having a problem building work-arounds.
Thanks
-Chad

Database Logon problem Crystal Report

I am using following code in form load.
it works fine on one PC but when i run the same application on other pc with different version of Windows Xp it is asking for database logon for report.

I did tried using my provided login info using code but it is executing front end query as well as query in the report.

Any Suggestions will be appreciated.
Thanks in advance



ConnStr = "PROVIDER=MSDAORA.1;PERSIST SECURITY INFO=FALSE;USER ID=scott;PASSWORD=tiger;DATA SOURCE=orcl"
conObj = New OleDbConnection(ConnStr)

strsql = "select * from emp"

cmdObj.Connection = conObj

cmdObj.CommandText = strsql

daObj.SelectCommand = cmdObj

daObj.Fill(dsObj, "test")

rptDoc.Load("\\server\reports\abc.rpt")

srcCr = rptDoc

srcCr.SetDataSource(dsObj)

CRV.ReportSource = rptDoc

CRV.Show()i used CR viewer of CR XI ,it solved the problem. other system had cr viewer 9.5

Database login

I have a web based application that could have many clients/users.
Should I create a user account for each client browsing the web site.
They can only access certain data after they have been verified from a
user table. They use a common database login to access the data. This
is planned to be a public free site. So hopefully the client count
will be high.create login that the application uses to get to the DB... instead of
creating a login for each user.|||Ok thats they wey I'm approaching it now.|||Are you using an ODBC or DSN less, either way assuming the users should
have the same permissions then 1 user is created in the db with a certain
level of permissions. You may also have an admin section , therfore the
admin user may have a different set of permissions
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"nathan001" <nathan@.thelosthorizon.com> wrote in message
news:1143477924.849156.7120@.z34g2000cwc.googlegroups.com...
> Ok thats they wey I'm approaching it now.
>|||From what you describe, it sounds like you need ASP.NET Forms
Authentication. At a minimum, you will need to implement a table with one
row for each subscriber.
ASP.NET Forms Authentication Overview
http://msdn2.microsoft.com/en-us/library/7t6b43z4.aspx
As for database (server) logins, you will need to implement one login for
each type of user (admin, subscriber, guest, etc.) and then use SQL Server
role based permissions to restrict each user's access to specific database
objects.
SQL Server 2000 Administrator's Pocket Consultant - Chapter 5: SQL Server
2000 Security
http://www.microsoft.com/technet/pr...s/c05ppcsq.mspx
SQL Server 2000 Operations Guide: Security Administration
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
SQL Server 2000 Resource Kit - Chapter 10: Implementing Security
http://www.microsoft.com/technet/pr...fr=true

If this database is hosted by a 3rd party internet service provider, then
you will need to coordinate with the server administrator to setup the
logins and permissions, becuase these configurations require server admin
rights. If your ISP provides this functionality through their website
management tool, then that's great, but that would be exceptional.
"nathan001" <nathan@.thelosthorizon.com> wrote in message
news:1143473709.083112.37370@.i40g2000cwc.googlegroups.com...
>I have a web based application that could have many clients/users.
> Should I create a user account for each client browsing the web site.
> They can only access certain data after they have been verified from a
> user table. They use a common database login to access the data. This
> is planned to be a public free site. So hopefully the client count
> will be high.
>