Maintenance Plan for Compatibility Mode 7.0 databases

Hi,
we maintain several databases which must run in Compatibility Mode 7.0.
How can I create a non script-based Maintenance Plan for these databases
in SQL Server 2005 (DBs don't show up in the Wizard).
Regards
Axel Bender> How can I create a non script-based Maintenance Plan for these databases in SQL Server 200
5 (DBs
> don't show up in the Wizard).
Short answer: you can't. A database need to be in 2005 compat mode in order
for most (all?) maint
tasks to be available.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel_bender@.t-online.de> wrote in message news:einlo5$bsr$01$1@.news.t-online.
com...
> Hi,
> we maintain several databases which must run in Compatibility Mode 7.0. Ho
w can I create a non
> script-based Maintenance Plan for these databases in SQL Server 2005 (DBs
don't show up in the
> Wizard).
> Regards
> Axel Bender|||Tibor Karaszi wrote:
> Short answer: you can't. A database need to be in 2005 compat mode in
> order for most (all?) maint tasks to be available.
>
Thanks so far, Tibor,
is there possibly a workaround?
Remark: MPs also work with CM 80 databases.
Kind regards
Axel|||> is there possibly a workaround?
I guess that would be to up the compat level to 80 or 90, but your apps need
to support that of
course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel.bender@.cip-kommunal.de> wrote in message
news:%23hhV%23UkAHHA.204@.TK2MSFTNGP04.phx.gbl...
> Tibor Karaszi wrote:
> Thanks so far, Tibor,
> is there possibly a workaround?
> Remark: MPs also work with CM 80 databases.
> Kind regards
> Axel|||Hi Tibor,
unfortunately this is not a solution; due to the changes MS made to the
parser in SQL 2000, we (until we're able to redesign our app) need to
run in CM 70. Is there - to your knowledge - any reason for MS not to
support MPs on SQL 2005?
Kind regards
Axel|||> Is there - to your knowledge - any reason for MS not to support MPs on SQL
2005?
I don't know. Perhaps one of the priority things that we all have to deal wi
th in out projects?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel.bender@.cip-kommunal.de> wrote in message
news:ujhfvonAHHA.4256@.TK2MSFTNGP04.phx.gbl...
> Hi Tibor,
> unfortunately this is not a solution; due to the changes MS made to the pa
rser in SQL 2000, we
> (until we're able to redesign our app) need to run in CM 70. Is there - to
your knowledge - any
> reason for MS not to support MPs on SQL 2005?
> Kind regards
> Axel

Maintenance Plan for Compatibility Mode 7.0 databases

Hi,
we maintain several databases which must run in Compatibility Mode 7.0.
How can I create a non script-based Maintenance Plan for these databases
in SQL Server 2005 (DBs don't show up in the Wizard).
Regards
Axel Bender> How can I create a non script-based Maintenance Plan for these databases in SQL Server 2005 (DBs
> don't show up in the Wizard).
Short answer: you can't. A database need to be in 2005 compat mode in order for most (all?) maint
tasks to be available.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel_bender@.t-online.de> wrote in message news:einlo5$bsr$01$1@.news.t-online.com...
> Hi,
> we maintain several databases which must run in Compatibility Mode 7.0. How can I create a non
> script-based Maintenance Plan for these databases in SQL Server 2005 (DBs don't show up in the
> Wizard).
> Regards
> Axel Bender|||Tibor Karaszi wrote:
>> How can I create a non script-based Maintenance Plan for these
>> databases in SQL Server 2005 (DBs don't show up in the Wizard).
> Short answer: you can't. A database need to be in 2005 compat mode in
> order for most (all?) maint tasks to be available.
>
Thanks so far, Tibor,
is there possibly a workaround?
Remark: MPs also work with CM 80 databases.
Kind regards
Axel|||> is there possibly a workaround?
I guess that would be to up the compat level to 80 or 90, but your apps need to support that of
course.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel.bender@.cip-kommunal.de> wrote in message
news:%23hhV%23UkAHHA.204@.TK2MSFTNGP04.phx.gbl...
> Tibor Karaszi wrote:
>> How can I create a non script-based Maintenance Plan for these databases in SQL Server 2005 (DBs
>> don't show up in the Wizard).
>> Short answer: you can't. A database need to be in 2005 compat mode in order for most (all?) maint
>> tasks to be available.
> Thanks so far, Tibor,
> is there possibly a workaround?
> Remark: MPs also work with CM 80 databases.
> Kind regards
> Axel|||Hi Tibor,
unfortunately this is not a solution; due to the changes MS made to the
parser in SQL 2000, we (until we're able to redesign our app) need to
run in CM 70. Is there - to your knowledge - any reason for MS not to
support MPs on SQL 2005?
Kind regards
Axel|||> Is there - to your knowledge - any reason for MS not to support MPs on SQL 2005?
I don't know. Perhaps one of the priority things that we all have to deal with in out projects?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel.bender@.cip-kommunal.de> wrote in message
news:ujhfvonAHHA.4256@.TK2MSFTNGP04.phx.gbl...
> Hi Tibor,
> unfortunately this is not a solution; due to the changes MS made to the parser in SQL 2000, we
> (until we're able to redesign our app) need to run in CM 70. Is there - to your knowledge - any
> reason for MS not to support MPs on SQL 2005?
> Kind regards
> Axel

Maintaining user formatting in multiline textbox

In a multiline textbox, how do you maintain paragraph breaks that users may create (like what you see on this forum)?

Are the returns replaced by something else just before being saved to the database? Any assistance would be appreciated!

MichaelWhat I have done with classic ASP is replace all vbCRLF with <BR>. I believe it is the same thing with ASP.NET.

Terri

Maintaining SQL data on a remote host.

Hi,
What is the preferred way to maintain SQL tables on a remote host?
I am a newbie to building ASP.NET websites on a remote host.
A stumbling point has been the maintenance of SQL tables on the remote host.
I understand about doing complete backup and restores,
but I am seeking a quicker way to maintain individual files.
I would like to click and edit but instead am going through the following 30+ clicks.
Is there a easier way?
Thanks.

For example, what I do now to build a new data table for a hosted website.
1) Design table
1a) Name
1b) Fields & Types

2) SQL Server Management Studio Express (assuming existing database)
2a) Select Database & Tables
2b) Add new table
2c) Add fields, Key must be INT for ACCESS
2d) Save as (Name_Table)

3) MS Access (requires ODBC to be setup first through the Windows control panel)
3a) Tables / New / Link / ODBC /Machine_Data_Source
3b) Pick table
3c) Edit data, as needed

4) To transfer data, first:
Select the database in the VWD solution explorer,
then right-click and select the new "Publish to Provider"
4a) Database Publishing Wizard
4b) Choose table to script a backup from
4c) Build script & Copy

5) Start Ipswitch FTP ( this step can be rplaced by 6e below)
5a) locate folder & sql script file and choose destination directory
5b) Transfer file

6) Login to remote host host (1and1)
6a) MS SQL Administration
6b) Admin (MyLittleTools Admin)
6c) Tools
6d) Quey Analyser
6e) Paste script (from step 4)
6f) Submit (Run)
6g) Verify table built

FYI: Script to build and populate the new table "Name_Table"
Built by step 4c above, pasted into remote Hosts Query Analyzer by step 6e above.

/****** Object: Table [dbo].[Name_Table] Script Date: 10/28/2007 18:03:58 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Name_Table]') AND type in (N'U'))
DROP TABLE [dbo].[Name_Table]
GO
/****** Object: Table [dbo].[Name_Table] Script Date: 10/28/2007 18:03:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Name_Table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Name_Table](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Acsz] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Name_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =

ON)
)
END
GO
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (1, NULL, NULL,

NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (2, NULL, NULL,

NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (3, N'Third

name', NULL, NULL, NULL, NULL, NULL, NULL, NULL)

You should take a good look at SSIS (Sql Server Integration Services).

It's quite nice and made for moving data from one database to another.

Another option is to create a database link from one server to another and use t-sql to move the data. Would recommend using SSIS if possible.

Maintaining Replication

I am trying to maintain replication from an app I have written.
Occasionally, a subscription will have a problem and error out. When the
subscription is restarted, it often will not start resynching until if feels
like it.
What I am looking to do is check the status of all subscriptions and start
synchronization for those that have stopped. Also, I want to do this without
resorting to EM.
Thanks,
Austin
Are you using SQL Server 2000 or SQL Server 2005.
2005 has a rich interface called RMO that you can use to build monitoring
applications, In 2000 you can use DMO to but to a lesser extent in terms of
functionality,
"Austin Summers" wrote:

> I am trying to maintain replication from an app I have written.
> Occasionally, a subscription will have a problem and error out. When the
> subscription is restarted, it often will not start resynching until if feels
> like it.
> What I am looking to do is check the status of all subscriptions and start
> synchronization for those that have stopped. Also, I want to do this without
> resorting to EM.
> Thanks,
> --
> Austin
|||You can have a replication alert fire a job on agent failure, or add a 4th
job step to fire when the agent fails, or even have the third job step loop
back to the first job step on step 3 job failure.
Depending on your app you can build logic in here to decide on how to
respond to different classes of errors. For instance if you are using
ActiveX scripts use the status message and parse it looking for errors.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
news:4DD99011-3B98-49DE-BCDE-D818F964D209@.microsoft.com...
>I am trying to maintain replication from an app I have written.
> Occasionally, a subscription will have a problem and error out. When the
> subscription is restarted, it often will not start resynching until if
> feels
> like it.
> What I am looking to do is check the status of all subscriptions and start
> synchronization for those that have stopped. Also, I want to do this
> without
> resorting to EM.
> Thanks,
> --
> Austin
|||Hilary,
How do you modify the agent steps at subscription setup? I am using
sp_addmergesubscription to create the subscription. Is there another sp I
can run to make further changes?
Primarily, this will affect MSDE users who disconnect a laptop and then
reconnect needing their data synched both ways. Also, I am finding that if
the publisher cannot find a subscriber, it varies greatly how long it will
keep searching. One test kept trying for a week, another test stopped after
15 minutes. Is there a stored procedure that will startup bi-directional
synchronization?
Thanks,
Austin
"Hilary Cotter" wrote:

> You can have a replication alert fire a job on agent failure, or add a 4th
> job step to fire when the agent fails, or even have the third job step loop
> back to the first job step on step 3 job failure.
> Depending on your app you can build logic in here to decide on how to
> respond to different classes of errors. For instance if you are using
> ActiveX scripts use the status message and parse it looking for errors.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
> news:4DD99011-3B98-49DE-BCDE-D818F964D209@.microsoft.com...
>
>
|||You can't, you could manually create a profile and then point it at this
profile. ActiveX controls allow you to do this. Have a look at sp_start_job
to start the replication process.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
news:89C88E48-F1C9-4B2F-8867-8803D7CB022D@.microsoft.com...[vbcol=seagreen]
> Hilary,
> How do you modify the agent steps at subscription setup? I am using
> sp_addmergesubscription to create the subscription. Is there another sp I
> can run to make further changes?
> Primarily, this will affect MSDE users who disconnect a laptop and then
> reconnect needing their data synched both ways. Also, I am finding that
> if
> the publisher cannot find a subscriber, it varies greatly how long it will
> keep searching. One test kept trying for a week, another test stopped
> after
> 15 minutes. Is there a stored procedure that will startup bi-directional
> synchronization?
> Thanks,
> --
> Austin
>
> "Hilary Cotter" wrote:
|||Hilary,
Thanks, sp_start_job works nicely. But, I can't seem to find a status flag
that tells me if a subscription has stopped. Does one exist or should I just
run the restart job whether it is needed or not (and simply trap the error)?
Also, I am getting different behavior from subscriptions setup the same way.
On one, the users takes home their laptop and the agent keeps checking all
night and reconnects when the user returns in the morning. On my other
subscription, it tries to reconnect the default 10 times and then quits. I
can't find where that setting is.
Thanks,
Austin
"Hilary Cotter" wrote:

> You can't, you could manually create a profile and then point it at this
> profile. ActiveX controls allow you to do this. Have a look at sp_start_job
> to start the replication process.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
> news:89C88E48-F1C9-4B2F-8867-8803D7CB022D@.microsoft.com...
>
>

Maintaining a log for the users connected to sql server db.

Hi !

I need to maintain a record such as how many time any user (e.g, sa) connects to the sql server. Means whenever any person is connecting to the database through application or directly, then i need to know that through which sql user(e.g sa), any body connected.

Regards,
Shabber Abbas Rizvi.create server side trace file for security Audit events.For more information visit

http://search.atomz.com/search/?sp-q=trace&sp-a=sp10010351

maintainance

I have a SQL server database application,
I just wnated to know that may I maintain the system while user use the
sysetm?
Should I maintain the system while all user off line?
Any information is great appreciated,Hi
It depends on what you are doing, but usually if you can schedule
maintenance when the system is quiet/down it will have less impact. Using
SQL Agent you can schedule your maintainance tasks for quiet times
John
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:9D26D4FF-1404-4E34-B19D-6DF86A34865D@.microsoft.com...
>I have a SQL server database application,
> I just wnated to know that may I maintain the system while user use the
> sysetm?
> Should I maintain the system while all user off line?
> Any information is great appreciated,
>|||As John said, it depends on the action you want to perform. If you have a
24/7 database you sure have no time slot to do a cold backup ;-). But as
John said, i wouldt places IO-based Activity (rebuilding of indexes) in the
primary processing time. If you have any questions for the details of your
actions you want to perform, do not hesitate to ask.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Souris" <Souris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9D26D4FF-1404-4E34-B19D-6DF86A34865D@.microsoft.com...
>I have a SQL server database application,
> I just wnated to know that may I maintain the system while user use the
> sysetm?
> Should I maintain the system while all user off line?
> Any information is great appreciated,
>

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
David Portas
SQL Server MVP
--|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/micr...8924a2b73888ca4|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
--
David Portas
SQL Server MVP
--|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/eb09130dcb79c392/d8924a2b73888ca4?lnk=st&q=stored+procedure+set+based&rnum=3&hl=en#d8924a2b73888ca4|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Maintain Transaction even if need to loop through multiple data

I have a ADP app that executes several SPs with a click of a button
Now I want to combine these SP into 1 transaction so that it can
rollback even if one SP fails.
Problem is several SP need to process recordsets in vba so I have made
while loops in vba for them. I do this to avoid cursors as they no good
performers. My choice of avoiding cursors has broken the 'transaction
boundary' therefore I cannot do a roll back for these. How can I still
use store procedures but still maintain performance and be able to roll
back all the SPs if any of them fails?
Have you considered rewriting the procs in set based fashion such that
you don't need to call them in a loop? A for each loop is really just a
cursor by another name. Without seeing what you are doing it's
difficult to advise otherwise.
David Portas
SQL Server MVP
|||Thanks for the hint -> set based technique.
I've found what I've been looking for:
http://groups.google.com/group/micro...924a2b73888ca4
|||I got another problem.
I got this dynamic sp:
create procedure spFindData()
as
declare @.statement varchar(255),@.intYear int,intCount int
set @.intyear = 2010
while @.intyear >=2004
begin
select @.statement = "select count(*) from sales' + @.intYear
-- I want to assign the result of @.statement to @.intCount, how?
if (@.intCount>0)
begin
return @.intYear
end
@.intYear = @.intYear -1
end

Maintain separate table via trigger vs. indexed view

Hello,
I have a table LargeTable in many columns and many rows. However, I also
need to have a small subset of the rows in LargeTable and only data from
small set of columns; call it SmallTable.
SmallTable gets read very often. So basically I've been using trigger on
LargeTable to watch for any change there to populate the SmallTable.
However, I realized that an indexed view can replace SmallTable.
In general, which one would be a better design in term of performance? I
know that indexed view can be more elegant but I'm interested in
performance. thanks!"Zester" <zeze@.nottospam.com> wrote in message
news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table LargeTable in many columns and many rows. However, I also
> need to have a small subset of the rows in LargeTable and only data from
> small set of columns; call it SmallTable.
> SmallTable gets read very often. So basically I've been using trigger on
> LargeTable to watch for any change there to populate the SmallTable.
> However, I realized that an indexed view can replace SmallTable.
> In general, which one would be a better design in term of performance? I
> know that indexed view can be more elegant but I'm interested in
> performance. thanks!
>
>
What makes you think this is a candidate for an indexed view? You could
create an ordinary view and put a relevant nonclustered index on the base
table. That way you won't incur the same write overhead that an indexed view
has. In SQL Server 2005 you can also include non-key columns in a
nonclustered index.
--
David Portas|||the LargeTable gets read even more often than the SmallTable and LargeTable
serves the main feature of our product. Writing to it is not often but does
occur - most likely only to the columns that have nothing to do with the
dataset needed for SmallTable. The feature using SmallTable is minor, so we
don't want it to interfere with the main feature that needs LargeTable.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uKBFF$3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> I have a table LargeTable in many columns and many rows. However, I also
>> need to have a small subset of the rows in LargeTable and only data from
>> small set of columns; call it SmallTable.
>> SmallTable gets read very often. So basically I've been using trigger on
>> LargeTable to watch for any change there to populate the SmallTable.
>> However, I realized that an indexed view can replace SmallTable.
>> In general, which one would be a better design in term of performance? I
>> know that indexed view can be more elegant but I'm interested in
>> performance. thanks!
>>
> What makes you think this is a candidate for an indexed view? You could
> create an ordinary view and put a relevant nonclustered index on the base
> table. That way you won't incur the same write overhead that an indexed
> view has. In SQL Server 2005 you can also include non-key columns in a
> nonclustered index.
> --
> David Portas
>

Maintain Report State

I have a matrix report where the user can drill down into a hierarchy to view
data for a particular product. The report also includes a parameter list
allowing the user to change the selected product.
The problem with this approach is that the user may drill to a specific
branch and then decide to switch products. At that point the report refreshes
and collapses back to the root node, because I have defined the report to
hide all groups on first entry.
Given that I am using Report Manager with no custom front end, does anyone
know of a good strategy to maintain state so that I can retain which groups
are expanded and collapsed for a particular user between instances of the
report?Modifying the Report Parameter causes the query to be executed. When this
happens the report is rendered from scratch. Hence you will not be able to
maintain the state of how the groups are displayed.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave Morrow" <DaveMorrow@.discussions.microsoft.com> wrote in message
news:413E2C03-1362-4130-A16B-F7E371D2E0F3@.microsoft.com...
>I have a matrix report where the user can drill down into a hierarchy to
>view
> data for a particular product. The report also includes a parameter list
> allowing the user to change the selected product.
> The problem with this approach is that the user may drill to a specific
> branch and then decide to switch products. At that point the report
> refreshes
> and collapses back to the root node, because I have defined the report to
> hide all groups on first entry.
> Given that I am using Report Manager with no custom front end, does anyone
> know of a good strategy to maintain state so that I can retain which
> groups
> are expanded and collapsed for a particular user between instances of the
> report?

Maintain Jobs, View Logs

I have a user who needs to check whether 2 jobs have run on a SQL Server 200
0 instance, and be able to view logs.
Is there a way of doing this, and not allowing them access to anything else?Hi
Does the use have EM on his/her workstation?
Does the user have permissions to run the job?
See this example written by Neil
/*** Scheduled task reporter for SQL 2000
**** Copyright Neil Boyle 2003.
**** Use entirely at your own risk
***/
use MSDB
go
select convert(varchar(22), j.name) as job_name
, case freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For weekly, add in the days of the week
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) --
Monthly on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday
of the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule
, case freq_subday_type -- FOr when a job funs every few seconds, minutes
or hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency
-- All the subsrings are because the times are stored as an integer with no
leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning
(01:30:00)
, substring (right (stuff (' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_start_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time),
6) ,5 ,2) as start_at
,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,5 ,2) end as end_at
from sysjobs j
join sysJobSchedules s
on j.job_id = s.job_id
order by j.name, start_at
"JamesNZ" <JamesNZ.24lo0y@.mail.webservertalk.com> wrote in message
news:JamesNZ.24lo0y@.mail.webservertalk.com...
> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?
>
> --
> JamesNZ
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1430463.html
>|||Here is one solution which should work for you:
1. Create a SP using the following script. Just mention your job names in
the following line of the script:
--sj.name in ('Job1', 'Job2') and
CREATE PROCEDURE dbo.read_jobhistory
as
select sjh.server, sj.name, case sjh.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end as 'Job Status',
sjh.sql_message_id, sjh.sql_severity,sjh.message,
convert(datetime, cast (sjh.run_date as char(8)) + ' ' +
left(right('000000' + cast (sjh.run_time as varchar(6)),6),2) + ':' +
substring(right('000000' + cast (sjh.run_time as varchar(6)),6),3,2) + ':' +
right('000000' + cast (sjh.run_time as varchar(6)),2))as 'Run Date',
sjh.run_duration, sjh.operator_id_emailed,
sjh.operator_id_netsent, sjh.operator_id_paged, sjh.retries_attempted
from
msdb..sysjobhistory sjh
join msdb..sysjobs sj
on sjh.job_id = sj.job_id
where
sj.name in ('Job1', 'Job2') and
step_id = 0
and sjh.run_date >= convert(char(10),getdate(),112)
order by sjh.server, sj.name, sjh.run_date, sjh.run_time
2. Grant Execution on this SP to the concerned user.
3. The concerned user can get the report for the today's date just by
executing the SP.
"JamesNZ" wrote:

> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?

Maintain Jobs, View Logs

I have a user who needs to check whether 2 jobs have run on a SQL Server
2000 instance, and be able to view logs.
Is there a way of doing this, and not allowing them access to anything
else?
--
JamesNZ
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message1430463.htmlHi
Does the use have EM on his/her workstation?
Does the user have permissions to run the job?
See this example written by Neil
/*** Scheduled task reporter for SQL 2000
**** Copyright Neil Boyle 2003.
**** Use entirely at your own risk
***/
use MSDB
go
select convert(varchar(22), j.name) as job_name
, case freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For weekly, add in the days of the week
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) --
Monthly on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday
of the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule
, case freq_subday_type -- FOr when a job funs every few seconds, minutes
or hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency
-- All the subsrings are because the times are stored as an integer with no
leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning
(01:30:00)
, substring (right (stuff (' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_start_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time),
6) ,5 ,2) as start_at
,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,5 ,2) end as end_at
from sysjobs j
join sysJobSchedules s
on j.job_id = s.job_id
order by j.name, start_at
"JamesNZ" <JamesNZ.24lo0y@.mail.webservertalk.com> wrote in message
news:JamesNZ.24lo0y@.mail.webservertalk.com...
> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?
>
> --
> JamesNZ
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1430463.html
>|||Here is one solution which should work for you:
1. Create a SP using the following script. Just mention your job names in
the following line of the script:
--sj.name in ('Job1', 'Job2') and
CREATE PROCEDURE dbo.read_jobhistory
as
select sjh.server, sj.name, case sjh.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end as 'Job Status',
sjh.sql_message_id, sjh.sql_severity,sjh.message,
convert(datetime, cast (sjh.run_date as char(8)) + ' ' +
left(right('000000' + cast (sjh.run_time as varchar(6)),6),2) + ':' +
substring(right('000000' + cast (sjh.run_time as varchar(6)),6),3,2) + ':' +
right('000000' + cast (sjh.run_time as varchar(6)),2))as 'Run Date',
sjh.run_duration, sjh.operator_id_emailed,
sjh.operator_id_netsent, sjh.operator_id_paged, sjh.retries_attempted
from
msdb..sysjobhistory sjh
join msdb..sysjobs sj
on sjh.job_id = sj.job_id
where
sj.name in ('Job1', 'Job2') and
step_id = 0
and sjh.run_date >= convert(char(10),getdate(),112)
order by sjh.server, sj.name, sjh.run_date, sjh.run_time
2. Grant Execution on this SP to the concerned user.
3. The concerned user can get the report for the today's date just by
executing the SP.
"JamesNZ" wrote:
> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?

Maintain Jobs, View Logs

I have a user who needs to check whether 2 jobs have run on a SQL Server
2000 instance, and be able to view logs.
Is there a way of doing this, and not allowing them access to anything
else?
JamesNZ
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1430463.html
Hi
Does the use have EM on his/her workstation?
Does the user have permissions to run the job?
See this example written by Neil
/*** Scheduled task reporter for SQL 2000
**** Copyright Neil Boyle 2003.
**** Use entirely at your own risk
***/
use MSDB
go
select convert(varchar(22), j.name) as job_name
, case freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For weekly, add in the days of the week
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) --
Monthly on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday
of the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule
, case freq_subday_type -- FOr when a job funs every few seconds, minutes
or hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency
-- All the subsrings are because the times are stored as an integer with no
leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning
(01:30:00)
, substring (right (stuff (' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_start_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time),
6) ,5 ,2) as start_at
,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,5 ,2) end as end_at
from sysjobs j
join sysJobSchedules s
on j.job_id = s.job_id
order by j.name, start_at
"JamesNZ" <JamesNZ.24lo0y@.mail.webservertalk.com> wrote in message
news:JamesNZ.24lo0y@.mail.webservertalk.com...
> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?
>
> --
> JamesNZ
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1430463.html
>
|||Here is one solution which should work for you:
1. Create a SP using the following script. Just mention your job names in
the following line of the script:
--sj.name in ('Job1', 'Job2') and
CREATE PROCEDURE dbo.read_jobhistory
as
select sjh.server, sj.name, case sjh.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end as 'Job Status',
sjh.sql_message_id, sjh.sql_severity,sjh.message,
convert(datetime, cast (sjh.run_date as char(8)) + ' ' +
left(right('000000' + cast (sjh.run_time as varchar(6)),6),2) + ':' +
substring(right('000000' + cast (sjh.run_time as varchar(6)),6),3,2) + ':' +
right('000000' + cast (sjh.run_time as varchar(6)),2))as 'Run Date',
sjh.run_duration, sjh.operator_id_emailed,
sjh.operator_id_netsent, sjh.operator_id_paged, sjh.retries_attempted
from
msdb..sysjobhistory sjh
join msdb..sysjobs sj
on sjh.job_id = sj.job_id
where
sj.name in ('Job1', 'Job2') and
step_id = 0
and sjh.run_date >= convert(char(10),getdate(),112)
order by sjh.server, sj.name, sjh.run_date, sjh.run_time
2. Grant Execution on this SP to the concerned user.
3. The concerned user can get the report for the today's date just by
executing the SP.
"JamesNZ" wrote:

> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?

Maintain History

Hi All
I have a table that contains details regarding branch offices and as these
offices regularly change details such as the branch name and address I need
to always have the current details and also maintain a history of historical
details for each branch office. Initially I was think of having a duplicate
table with a datetime column to store when the last change was made and also
a column that contained the details of who changed the branch office details.
I was going to control this through the application but have identified that
there is any additional vendor application that also change details in this
table and the application can not be modified.
Is the best way to maintain a history of branch office details in this
scenario to create a trigger on the table and populate a duplicate table with
the details of the branch office as they where before being modified or is
there a better way to do this?
Thanks
- DavidHi David
There is a simple auditing example (E) in the Create Trigger topic in books
online. There may be an impact on the system if you implement these on all
your tables, in both the amount of storage used and time taken to make
changes. To reduce the performance degredation it is best to keep the trigger
as simple as you can , if any reconciliation is necessary then you can do
that during a quiet period of possibly offline.
Alternatively you can purchase a log reading program, you may want to log at
LogExplorer from Lumigent www.lumigent.com or LogPI which has been aquired
by Goldengate see www.logpi.com
John
"David" wrote:
> Hi All
> I have a table that contains details regarding branch offices and as these
> offices regularly change details such as the branch name and address I need
> to always have the current details and also maintain a history of historical
> details for each branch office. Initially I was think of having a duplicate
> table with a datetime column to store when the last change was made and also
> a column that contained the details of who changed the branch office details.
> I was going to control this through the application but have identified that
> there is any additional vendor application that also change details in this
> table and the application can not be modified.
> Is the best way to maintain a history of branch office details in this
> scenario to create a trigger on the table and populate a duplicate table with
> the details of the branch office as they where before being modified or is
> there a better way to do this?
> Thanks
> - David

Maintain History

Hi All
I have a table that contains details regarding branch offices and as these
offices regularly change details such as the branch name and address I need
to always have the current details and also maintain a history of historical
details for each branch office. Initially I was think of having a duplicate
table with a datetime column to store when the last change was made and also
a column that contained the details of who changed the branch office details
.
I was going to control this through the application but have identified that
there is any additional vendor application that also change details in this
table and the application can not be modified.
Is the best way to maintain a history of branch office details in this
scenario to create a trigger on the table and populate a duplicate table wit
h
the details of the branch office as they where before being modified or is
there a better way to do this?
Thanks
- DavidHi David
There is a simple auditing example (E) in the Create Trigger topic in books
online. There may be an impact on the system if you implement these on all
your tables, in both the amount of storage used and time taken to make
changes. To reduce the performance degredation it is best to keep the trigge
r
as simple as you can , if any reconciliation is necessary then you can do
that during a quiet period of possibly offline.
Alternatively you can purchase a log reading program, you may want to log at
LogExplorer from Lumigent www.lumigent.com or LogPI which has been aquired
by Goldengate see www.logpi.com
John
"David" wrote:

> Hi All
> I have a table that contains details regarding branch offices and as these
> offices regularly change details such as the branch name and address I nee
d
> to always have the current details and also maintain a history of historic
al
> details for each branch office. Initially I was think of having a duplica
te
> table with a datetime column to store when the last change was made and al
so
> a column that contained the details of who changed the branch office detai
ls.
> I was going to control this through the application but have identified t
hat
> there is any additional vendor application that also change details in thi
s
> table and the application can not be modified.
> Is the best way to maintain a history of branch office details in this
> scenario to create a trigger on the table and populate a duplicate table w
ith
> the details of the branch office as they where before being modified or is
> there a better way to do this?
> Thanks
> - David

Maintain datatype for reporting services from OLAP

I'm trying to extract data from my OLAP and present it using reporting services. The problem is that all the values extracted from the OLAP seems converted into string instead of keeping their original type. Am I doing something wrong in the setting? Please let me know how can I check the data type in reporting services. Thanks!

I haven't seen this conversion. The datatype that is set in the properties for the dimension attribute is what I see in my report. Check in Analysis Services for the attribute properties datatype that is set there and verify that something that you expect as a number is defined as a number.

There isn't a property in Reporting Services that tells you the data type, but you can check to see if something is numeric by using the ISNUMERIC(Field) to determine if the field returned in the MDX is seen by RS is a number.

You can also convert the field to what ever datatype you need in Reporting Services.

David

|||Dear David,

I would like to try checking up the attribute properties datatype. However, I can't find anywhere that I can do this in the SQL Server Management Studio. Could you please give me a guide for that?

Regards,
Alex|||

Hi Alex,

Unfortunately you can't see them in SS Management Studio, but you can via BI Studio, you will have to run BI studio and if you don't have a project choose File/Open and then Analysis Services Database. Click the dimension, and then look at the properties for the Attribute Heirarchy, go to the Source/Key Column and click the ... This will show you the data type that is listed for the source column.

This should match the column that is in your DSV that is created for the project.

David

|||Hi David,

Thanks for your speedy response first. Actually, I have done couples of time of what you've mentioned. I am certainly sure that BI studio show me the datatype is what I expected. However, once I get into the reporting services. All of them becomes string datatype. I need to use cint and other function to convert them back to their supposed datatype. Is there anything wrong with my MDX statement? I just use the drap & drag approach to create the DataSet for reporting services.

Regards,
Alex|||Hi David,

I've just done a few more tests. I found that the datatype are maintained when it comes to the reporting services. I use the IsNumeric and IsDate functions to check for this. However, the problem is even they're in the correct type. It seems that the sorting and formating function are not handling them well. First, I would like to order by an integer value field and I got the list like this. 1, 10, 2, 20, 3, 30, 4 and so on. Second, if I change the format code of a date field, it doesn't change at all. I do this by right click on the textbox containing the date and choose properties -> format tab and input d into the format code field. Do you have any idea about this? Thanks!

Regards,
Alex

Maintain Blank DB Copy

Hi
I have a development database that gets filled with test
data. I would like to maintain a 'blank' copy i.e one
that only contains the data the application needs to run,
that is automatically updated with structural changes,
new SP's etc without any new 'user' data being added to
it.
Can anyone give me any pointers as to the most painless
way of doing this?
Thanks in advance
GrahamI prefer to have the DDL scripts to create the database and then INSERT statements to create the
base stuff, this way you don't have to mess with restore or attach.
Here are some options to generate DDL script, some including data:
You can use SCPTXFER to generate DDL from code as described in:
DTS Does Not Copy Identity, Indexes, Primary Key or Other Constraints (Q220163)
http://www.support.microsoft.com/?id=220163
Generate TSQL scrip with DDL and data:
http://www.rac4sql.net/objectscriptr_main.asp
Generate TSQL script with data:
http://www.sqlscripter.com
Generate TSQL script with data:
http://www.lockwoodtech.com/index.asp?PageId=auto_inserts&TitleID=products
Generate TSQL script with data:
http://www.largosqltools.com/DataInsertScripts.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Graham" <graham@.DONT_SPAM_MEremarkabledesignPLEASE.com> wrote in message
news:0c0f01c3a9e4$680c2590$a401280a@.phx.gbl...
> Hi
> I have a development database that gets filled with test
> data. I would like to maintain a 'blank' copy i.e one
> that only contains the data the application needs to run,
> that is automatically updated with structural changes,
> new SP's etc without any new 'user' data being added to
> it.
> Can anyone give me any pointers as to the most painless
> way of doing this?
> Thanks in advance
> Graham|||Graham wrote:
> Hi
> I have a development database that gets filled with test
> data. I would like to maintain a 'blank' copy i.e one
> that only contains the data the application needs to run,
> that is automatically updated with structural changes,
> new SP's etc without any new 'user' data being added to
> it.
> Can anyone give me any pointers as to the most painless
> way of doing this?
> Thanks in advance
> Graham
Million ways to do this but...
My personal preference would be to create the blank database, dettach
it and make a copy of the dbs data and log files (set them to
readonly). You can then reattach and continue. When you want to go back
to blank you would just detach current db, delete files and copy the
blankcopyones and rename back to origonal names (remove read
attributes) and reattach.
Tibors way is also a very good option.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============

maintain audit trail of access to SQL server 2000 database

Is there any way to maintain audit trail of access to my SQL server 2000 database by any user ? I need to log the timestamp of any insert/update/delete to any record in a table within the database by the user.

Alex

Hi,

You need to enable Audit Schema Object Access Event for an audit, which is responsible for recording access to all schema objects including tables. Further you need to filer by database name, such that only events in a database you are interested in are recorded. Since you want to audit insert/update/delete for tables in database you need to place additional filters:

ObjectType = 'U' -- means user table

Permissions = 0x1a -- 0x2 | 0x8 | 0x10

You can further filer by table name (ObjectName column) if you wish.

The setup of the event described above can be easily done using SQL Server Profiler. Use "Column Filters" button to setup filters.

Let me know if you have further questions,

Ruslan

|||

ruslan ovechkin1 wrote:

Hi,

You need to enable Audit Schema Object Access Event for an audit, which is responsible for recording access to all schema objects including tables. Further you need to filer by database name, such that only events in a database you are interested in are recorded. Since you want to audit insert/update/delete for tables in database you need to place additional filters:

ObjectType = 'U' -- means user table

Permissions = 0x1a -- 0x2 | 0x8 | 0x10

You can further filer by table name (ObjectName column) if you wish.

The setup of the event described above can be easily done using SQL Server Profiler. Use "Column Filters" button to setup filters.

Let me know if you have further questions,

Ruslan

After enabling this, is it possibel to take report for the same ?

Vishal