Database Audit

Hi,

We are currently porting our POINT OF SALE system to SQL Server 2005. In our existing software we log database changes (inserts, amendments and deletions) to a flat acsii files so that these files can be FTP'd to remote sites (largest number of sites 100+) and processed on to replicate the data.

I have looked at REPLICATION in MSSQL 2005 but we need two way replication and additional processing. Therefore I dont think we will be able to use this feature.

Therefore what I wanted to do was to try and setup an automated way of capturing database changes to all tables within the database and log these changes to XML to be shipped out to remote sites. Unfortunatly I can only find TABLE TRIGGERS which would require creating 100's of triggers as we have 100's of tables.

Is there anyway of setting up MSSQL server to automatically do something like this... I was looking to see if there was a DATABASE TRIGGER which could perform this action but I cant see anything...

Can anyone advise or is there a simpler way of doing this ?

Ray

I think that Log Shipping may be a useful solution in your situation.

Refer to Books Online, and the topic: Log Shipping.

Database Attache Problems

When I attempt to attache an existing SQL Database in SQLExpress, I get sent to the attach databases dialog. I click on the add button and get an abend with the following message.
Locate Database Files - server\SQLEXPRESS dialog box. c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
Cannot access the specified path or file on the server. Verify that you have the necessary security privledges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Location dialog box. When I click on OK, the Location Database Files dialog box opens, pointing me to my cd drive and does not permit me to alter the drive letter.

Since the database is on my C: drive, this will not work for me.

I have been unable to find any reference to the E: drive for SQLExpress in either that program directory or in the registry.

I went as far as removing the entire suite, then deleting everything that I could find having to do with it, registry entries, settings, common files, etc. I then re-installed the entire package and guess what, same thing.

Anyone have any ideas of how I get around this or how to fix it?

Any and all help will be greatly appreciated.

Thx, Tom

hi,

are the database files in the c:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data directory or elsewhere?

by default SQLExpress installs setting the NetworkService account to be the one to run the service, and this account is "limited" in NTFS permissions (and that's ok... it's just too large in permissions as well ), but should access without problems the provided path... but not c:\ pr the like...

but I already heared problems about the browsing dialog... with no additional workarounds..

but you can try yourself writing the Transact-SQL code to be executed in a "New query window"..

say you created and detached a database named testDB in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ with physical files named testDB.mdf and testDB_log.LDF

just execute, as a logged sysadmin,

SET NOCOUNT ON;

USE master;

GO

USE [master]

GO

CREATE DATABASE [testDB] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testDB.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testDB_log.LDF' )

FOR ATTACH;

GO

this will "manually" attach the db, according that you have enought permissions to the folder containing the physical file (obviously the account running the SQL Server instance has enought permissions on this sample location)..

regards

|||Andrea, thank-you very much for the help. Worked like a charm.

Regards, T

Database Attach Scripts

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?
Something like this:
declare @.dbid int,
@.sql varchar(4000),
@.name sysname,
@.old_name sysname,
@.filename nchar(260);
declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;
open db_cr;
set @.sql = '';
set @.old_name = '';
fetch next from db_cr into @.dbid, @.filename;
while @.@.fetch_status = 0
begin
If @.old_name <> db_name(@.dbid)
select @.sql = @.sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@.dbid)) + ''',
N''' + rtrim(@.filename) + '''';
else
select @.sql = @.sql + ', N''' + rtrim(@.filename) + '''';
select @.old_name = db_name(@.dbid);
fetch next from db_cr into @.dbid, @.filename;
end
close db_cr;
deallocate db_cr;
print @.sql;
Linchi
"Charlie" wrote:

> I have a few existing 2000 instances that we are migrating to 2005 SP2. The
> instances have a lot of databases on them. Is there an easy way to generate
> the attach scripts for migrating?
|||Charlie
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-database.html
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:220D9152-7C48-41BF-8CD8-8C3DD32C2C35@.microsoft.com...
>I have a few existing 2000 instances that we are migrating to 2005 SP2.
>The
> instances have a lot of databases on them. Is there an easy way to
> generate
> the attach scripts for migrating?
|||What does this script do exactly?
"Linchi Shea" wrote:
[vbcol=seagreen]
> Something like this:
> declare @.dbid int,
> @.sql varchar(4000),
> @.name sysname,
> @.old_name sysname,
> @.filename nchar(260);
> declare db_cr cursor
> for select dbid, filename
> from master..sysaltfiles
> where dbid > 4
> order by dbid;
> open db_cr;
> set @.sql = '';
> set @.old_name = '';
> fetch next from db_cr into @.dbid, @.filename;
> while @.@.fetch_status = 0
> begin
> If @.old_name <> db_name(@.dbid)
> select @.sql = @.sql + char(13) + char(10) +
> 'EXEC sp_attach_db N''' + rtrim(db_name(@.dbid)) + ''',
> N''' + rtrim(@.filename) + '''';
> else
> select @.sql = @.sql + ', N''' + rtrim(@.filename) + '''';
> select @.old_name = db_name(@.dbid);
> fetch next from db_cr into @.dbid, @.filename;
> end
> close db_cr;
> deallocate db_cr;
> print @.sql;
> Linchi
> "Charlie" wrote:
|||> What does this script do exactly?
Generate a T-SQL script to sp_attach_db every user database.
Linchi
"Charlie" wrote:
[vbcol=seagreen]
> What does this script do exactly?
> "Linchi Shea" wrote:
|||Where does it get a list? We are moving the user db's to a new environment.
I need to attach the raw files, sepcifying the .mdf and .ldf file locations
and db name...
Make sense?
Maybe I am not understanding what you are telling me...
"Linchi Shea" wrote:
[vbcol=seagreen]
> Generate a T-SQL script to sp_attach_db every user database.
> Linchi
> "Charlie" wrote:
|||The script reads the info from the sysaltfiles table on the current server.
If you are moving the files to a new server, you may need to edit the script
to point to the files in different locations.
Unless you have some overriding reasons to use a different directory
structure or a different drive, it's often simpler to keep the file locations
identical.
Linchi
"Charlie" wrote:
[vbcol=seagreen]
> Where does it get a list? We are moving the user db's to a new environment.
> I need to attach the raw files, sepcifying the .mdf and .ldf file locations
> and db name...
> Make sense?
> Maybe I am not understanding what you are telling me...
> "Linchi Shea" wrote:
|||That is really what I need!
This script does not print anything. A assume you want me to run it in
query analyzer?
"Linchi Shea" wrote:
[vbcol=seagreen]
> The script reads the info from the sysaltfiles table on the current server.
> If you are moving the files to a new server, you may need to edit the script
> to point to the files in different locations.
> Unless you have some overriding reasons to use a different directory
> structure or a different drive, it's often simpler to keep the file locations
> identical.
> Linchi
> "Charlie" wrote:
|||Nevermind.
Sorry!
Thanks for the help!
"Charlie" wrote:
[vbcol=seagreen]
> That is really what I need!
> This script does not print anything. A assume you want me to run it in
> query analyzer?
> "Linchi Shea" wrote:
|||It truncates after 32 db's. Is there any way to avoid this?
"Linchi Shea" wrote:
[vbcol=seagreen]
> The script reads the info from the sysaltfiles table on the current server.
> If you are moving the files to a new server, you may need to edit the script
> to point to the files in different locations.
> Unless you have some overriding reasons to use a different directory
> structure or a different drive, it's often simpler to keep the file locations
> identical.
> Linchi
> "Charlie" wrote:

Database Alias

Hello everyone,
Does anyone know if it is possible to create a database
aliase for an existing database name in SQL Server 2000
Enterprise Edition.Can you give an example of what you would want to do with this? There isn't
a specific ALIAS DATABASE command. But perhaps we can offer a suggestion
that meets your need if we know why you would want to alias the DB name.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Liz" <lrodriguez@.sanantonio.gov> wrote in message
news:06a101c37724$7668e620$a301280a@.phx.gbl...
> Hello everyone,
> Does anyone know if it is possible to create a database
> aliase for an existing database name in SQL Server 2000
> Enterprise Edition.|||We have over 100 users linking to SQL Server via MS Access and oppose to
modifying their odbc connection with new database name I was wondering
if there was a way to create a database alias name.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Data warehouse or data mart

Hi,
I'm working on a project at the moment where have an existing warehouse, and
need to bring in data from new source systems, which are from a similar (but
not quite the same) part of the business. These two parts of the business
merged recently, and they want to have a consolidated view of the data, as
well as replace some existing (Excel and Access based) reporting.
We are coming to the end of the analysis and design phase, and I want to get
some opinions that the approach I'm taking is valid.
I have tried to bring everything into the one database. I've added some new
fact tables for new source systems, as the original and new source systems
don't quite work the same. I've also tried to conform the dimensions as far
as possible, so that the data will aggregate consistently for the management
reporting.
This seems to fit pretty well, and I haven't had to alter the current data
model very much.
My question is basically: Is this the right way to go, or am I better off
breaking the warehouse into separate data marts?
A lot of the reporting will only apply to each part of the business, but
there will be some management reporting that wants a consolidated view of the
new merged business.
Any comments will be appreciated.
Thanks.
Hi,
Based on my scope, there is no necessary to break them into seperate data
marts for this situation because they have similar model and need to be
merged in the same analysis report. You may consider to create different
cube and you could at least share some common dimensions such as
time/location etc.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Data warehouse or data mart
>thread-index: AcXSOg1GLZ3yTwgwTGqbRewN1uy9bQ==
>X-WBNR-Posting-Host: 203.54.233.209
>From: "=?Utf-8?B?V3JlY2s=?=" <Wreck@.community.nospam>
>Subject: Data warehouse or data mart
>Date: Sun, 16 Oct 2005 03:12:02 -0700
>Lines: 31
>Message-ID: <EA2C0FDE-2A52-44DE-88F9-5BA83B6278BF@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.datawarehouse
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:2317
>X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
>Hi,
>I'm working on a project at the moment where have an existing warehouse,
and
>need to bring in data from new source systems, which are from a similar
(but
>not quite the same) part of the business. These two parts of the business
>merged recently, and they want to have a consolidated view of the data, as
>well as replace some existing (Excel and Access based) reporting.
>We are coming to the end of the analysis and design phase, and I want to
get
>some opinions that the approach I'm taking is valid.
>I have tried to bring everything into the one database. I've added some
new
>fact tables for new source systems, as the original and new source systems
>don't quite work the same. I've also tried to conform the dimensions as
far
>as possible, so that the data will aggregate consistently for the
management
>reporting.
>This seems to fit pretty well, and I haven't had to alter the current data
>model very much.
>My question is basically: Is this the right way to go, or am I better off
>breaking the warehouse into separate data marts?
>A lot of the reporting will only apply to each part of the business, but
>there will be some management reporting that wants a consolidated view of
the
>new merged business.
>Any comments will be appreciated.
>Thanks.
>
|||Hi Peter,
That's pretty much the approach I'm taking. The new source systems will have
their own fact tables, with conformed measures, joining to conformed
dimensions.
I'll build separate cubes for the fact tables, and put the old and new cubes
in a single virtual cube, joined on the common dimensions.
Thanks,
Wreck.
"Peter Yang [MSFT]" wrote:

> Hi,
> Based on my scope, there is no necessary to break them into seperate data
> marts for this situation because they have similar model and need to be
> merged in the same analysis report. You may consider to create different
> cube and you could at least share some common dimensions such as
> time/location etc.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> and
> (but
> get
> new
> far
> management
> the
>

Data warehouse or data mart

Hi,
I'm working on a project at the moment where have an existing warehouse, and
need to bring in data from new source systems, which are from a similar (but
not quite the same) part of the business. These two parts of the business
merged recently, and they want to have a consolidated view of the data, as
well as replace some existing (Excel and Access based) reporting.
We are coming to the end of the analysis and design phase, and I want to get
some opinions that the approach I'm taking is valid.
I have tried to bring everything into the one database. I've added some new
fact tables for new source systems, as the original and new source systems
don't quite work the same. I've also tried to conform the dimensions as far
as possible, so that the data will aggregate consistently for the management
reporting.
This seems to fit pretty well, and I haven't had to alter the current data
model very much.
My question is basically: Is this the right way to go, or am I better off
breaking the warehouse into separate data marts?
A lot of the reporting will only apply to each part of the business, but
there will be some management reporting that wants a consolidated view of th
e
new merged business.
Any comments will be appreciated.
Thanks.Hi,
Based on my scope, there is no necessary to break them into seperate data
marts for this situation because they have similar model and need to be
merged in the same analysis report. You may consider to create different
cube and you could at least share some common dimensions such as
time/location etc.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Data warehouse or data mart
>thread-index: AcXSOg1GLZ3yTwgwTGqbRewN1uy9bQ==
>X-WBNR-Posting-Host: 203.54.233.209
>From: "examnotes" <Wreck@.community.nospam>
>Subject: Data warehouse or data mart
>Date: Sun, 16 Oct 2005 03:12:02 -0700
>Lines: 31
>Message-ID: <EA2C0FDE-2A52-44DE-88F9-5BA83B6278BF@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.datawarehouse
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:2317
>X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
>Hi,
>I'm working on a project at the moment where have an existing warehouse,
and
>need to bring in data from new source systems, which are from a similar
(but
>not quite the same) part of the business. These two parts of the business
>merged recently, and they want to have a consolidated view of the data, as
>well as replace some existing (Excel and Access based) reporting.
>We are coming to the end of the analysis and design phase, and I want to
get
>some opinions that the approach I'm taking is valid.
>I have tried to bring everything into the one database. I've added some
new
>fact tables for new source systems, as the original and new source systems
>don't quite work the same. I've also tried to conform the dimensions as
far
>as possible, so that the data will aggregate consistently for the
management
>reporting.
>This seems to fit pretty well, and I haven't had to alter the current data
>model very much.
>My question is basically: Is this the right way to go, or am I better off
>breaking the warehouse into separate data marts?
>A lot of the reporting will only apply to each part of the business, but
>there will be some management reporting that wants a consolidated view of
the
>new merged business.
>Any comments will be appreciated.
>Thanks.
>|||Hi Peter,
That's pretty much the approach I'm taking. The new source systems will have
their own fact tables, with conformed measures, joining to conformed
dimensions.
I'll build separate cubes for the fact tables, and put the old and new cubes
in a single virtual cube, joined on the common dimensions.
Thanks,
Wreck.
"Peter Yang [MSFT]" wrote:

> Hi,
> Based on my scope, there is no necessary to break them into seperate data
> marts for this situation because they have similar model and need to be
> merged in the same analysis report. You may consider to create different
> cube and you could at least share some common dimensions such as
> time/location etc.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> and
> (but
> get
> new
> far
> management
> the
>

Data Viewers not viewable

I came back from the weekend and noticed that none of my data viewers pop-up any longer, neither existing nor new ones. The job pauses at the point where the data would be following through the viewer but I cannot make it continue (since I don't have a "play" or Detach button.) The only way to terminate the job is with the "Stop" button on the Toolbar. Anyone know how I can get these back?

Thanks.Never mind, it was pulled down to the very bottom of my second screen, almost out of sight. I swear...