Resizing an existing DB

I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting.google.com...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.|||Hi Darren,
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J
>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>|||Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> >--Original Message--
> >I created a DB with a preallocated size of 20GB and I
> need to shink
> >this down to a smaller size. If I go into DB properties
> and enter a
> >new value it says that the size must be greater than the
> current size.
> >Is there any way to do this? Also when I try and restore
> the DB is
> >wants 20GB of disk space even though it's only using
> about 1GB of
> >data. Any help will be muchly appreciated.
> >
> >Darren.
> >.
> >|||Ok, now hanging my head in shame.
Moving swiftly along...
>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
>> Hi Darren,
>> Unfortunatly you can't. The smallest size you can shrink
>> your database to is the size the database was initally
set
>> to when you created it, in this case 20gb.
>> Although there are lots of ways of doing it,
personally I
>> would save the data, drop the database, recreate it
with 1
>> gb, then copy the database back into it.
>> J
>>
>>
>> >--Original Message--
>> >I created a DB with a preallocated size of 20GB and I
>> need to shink
>> >this down to a smaller size. If I go into DB properties
>> and enter a
>> >new value it says that the size must be greater than
the
>> current size.
>> >Is there any way to do this? Also when I try and
restore
>> the DB is
>> >wants 20GB of disk space even though it's only using
>> about 1GB of
>> >data. Any help will be muchly appreciated.
>> >
>> >Darren.
>> >.
>> >
>
>.
>|||LOL...
As if we didn't do this all of us, from time to time. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a301280a@.phx.gbl...
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> >--Original Message--
> >Julie,
> >
> >DBCC SHRINKFILE should be able to shrink the size below
> the initial size...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> >> Hi Darren,
> >>
> >> Unfortunatly you can't. The smallest size you can shrink
> >> your database to is the size the database was initally
> set
> >> to when you created it, in this case 20gb.
> >>
> >> Although there are lots of ways of doing it,
> personally I
> >> would save the data, drop the database, recreate it
> with 1
> >> gb, then copy the database back into it.
> >>
> >> J
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I created a DB with a preallocated size of 20GB and I
> >> need to shink
> >> >this down to a smaller size. If I go into DB properties
> >> and enter a
> >> >new value it says that the size must be greater than
> the
> >> current size.
> >> >Is there any way to do this? Also when I try and
> restore
> >> the DB is
> >> >wants 20GB of disk space even though it's only using
> >> about 1GB of
> >> >data. Any help will be muchly appreciated.
> >> >
> >> >Darren.
> >> >.
> >> >
> >
> >
> >.
> >

Resizing an existing DB

I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.
Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting.google.c om...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.

Resizing an existing DB

Hi Darren,
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J

>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>
Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> need to shink
> and enter a
> current size.
> the DB is
> about 1GB of
|||Ok, now hanging my head in shame.
Moving swiftly along...

>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...[vbcol=seagreen]
set[vbcol=seagreen]
personally I[vbcol=seagreen]
with 1[vbcol=seagreen]
the[vbcol=seagreen]
restore
>
>.
>
|||LOL...
As if we didn't do this all of us, from time to time. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a301280a@.phx.gbl...[vbcol=seagreen]
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> the initial size...
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> set
> personally I
> with 1
> the
> restore

Resizing an existing DB

Hi Darren,
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J

>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a3
01280a@.phx.gbl...[vbcol=seagreen]
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> need to shink
> and enter a
> current size.
> the DB is
> about 1GB of|||Ok, now hanging my head in shame.
Moving swiftly along...

>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
set[vbcol=seagreen]
personally I[vbcol=seagreen]
with 1[vbcol=seagreen]
the[vbcol=seagreen]
restore[vbcol=seagreen]
>
>.
>|||LOL...
As if we didn't do this all of us, from time to time. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a3
01280a@.phx.gbl...[vbcol=seagreen]
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> the initial size...
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> set
> personally I
> with 1
> the
> restoresql

Resizing an existing DB

I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting
.google.com...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.

Resetting User Password

Hi,
I've created a user which is attached to a database. This
user exists only in SQL 2000. (no NT login in domain or
on the local box).
This user owns this database and i can't remember the
password. Therefore i'm unable to connect to it through
another computer using the ODBC connection.
I've looked everywhere to see where i can reset the
password. But i can't find it. I'm guessin that using the
T-SQL command prompt stuff is my best bet. But i've also
had problems with this SQL box and i can't connect to it.
Please let me know if there's anyway i can reset the
password. I've tried creating another user and giving it
full access to the db. As a custom application runs ontop
of the SQL database it needs specific permissions which
are builtin to that user
ThanksA sysadmin or a security admin will need to reset the
password using sp_password, e.g.
EXEC sp_password NULL, 'NewPassword', 'SQLLogin'
Use NULL for the old password argument to bypass the
checking of the old password.
You can find more information in books online under
sp_password.
-Sue
On Tue, 20 Jul 2004 18:34:26 -0700, "Jeremy Pond"
<jpond@.railcu.org.au> wrote:

>Hi,
>I've created a user which is attached to a database. This
>user exists only in SQL 2000. (no NT login in domain or
>on the local box).
>This user owns this database and i can't remember the
>password. Therefore i'm unable to connect to it through
>another computer using the ODBC connection.
>I've looked everywhere to see where i can reset the
>password. But i can't find it. I'm guessin that using the
>T-SQL command prompt stuff is my best bet. But i've also
>had problems with this SQL box and i can't connect to it.
>Please let me know if there's anyway i can reset the
>password. I've tried creating another user and giving it
>full access to the db. As a custom application runs ontop
>of the SQL database it needs specific permissions which
>are builtin to that user
>Thanks|||Hi Jeremy,

> This user owns this database and i can't remember the
> password. Therefore i'm unable to connect to it through
> another computer using the ODBC connection.
> I've looked everywhere to see where i can reset the
> password. But i can't find it. I'm guessin that using the
> T-SQL command prompt stuff is my best bet. But i've also
> had problems with this SQL box and i can't connect to it.
> Please let me know if there's anyway i can reset the
> password. I've tried creating another user and giving it
> full access to the db. As a custom application runs ontop
> of the SQL database it needs specific permissions which
> are builtin to that user
Can you stop the MSSQL server on the user's machine? Then you can copy the
database files and attach to another MSSQL Server engine without any
problems, you don't need any password to attach these files. Next, if you
can detach the database from the user's machine and then attach again, you
probably get all passwords disabled...) If you can't detach, then stop the
server, move the original database files and start again, you probably will
be able to kill the dissappeared database from the database list and attach
a new one using your files.
I have got my head broken trying to find a method to protect the MSSQL
database and couldn't and gave up. All newsgroups were keeping the silence
how to increase the security of the database about 15 months ago. I found
finally Sybase ASA and it allows me to keep a higher security of the
database, especially if these files are encrypted. If you were using Sybase
with encryption you should pray to open these files or reset the password, I
suspect that it's impossible, but MSSQL Server allows to walk through the
database without any password if you have a physical access to the server
and can stop the server for a while. That was the main reason why we refused
using the MSSQL server on our client's remote machines. It's not safe. Maybe
a new version... We're still hoping... And we're still using MSSQL Server
for our consolidated database..
Thanks,
D.|||Hi,
In addition to my previous message about detach/attach:
http://www.databasejournal.com/feat...cle.php/1438491
Thanks|||hey,
thanks for the reply, can't try it now during office
hours. But will certainly give it a go. It never occured
to me to do that. But i've now built a spare SQL box to
do that with.
That is an excellent solution. Very very smart. Good
work
you're a champion
thank yousql

reseting 'allow new subscriptions to be created by attaching..."

1st question: can this value be changed from 'false' to 'true' after a
publication is up and running?
2nd question: can this be accomplished without re-initializing the
subscribers?
3rd question: if #1 & #2 are true what steps must be done to do this?
Note: I have already tried the sp_changeMergePublication sp with no
success:
exec sp_changeMergePublication
@.publication = 'rep_test_hub',
@.property = allow_subscription_copy,
@.value = true
Thanks.
1) I am able to do it with a pull subscription.
2) Yes
3) I ran the same command to allow this.
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
"astro" <astro@.bcmn.com> wrote in message
news:N38Bf.42034$7S.25014@.tornado.rdc-kc.rr.com...
> 1st question: can this value be changed from 'false' to 'true' after a
> publication is up and running?
> 2nd question: can this be accomplished without re-initializing the
> subscribers?
> 3rd question: if #1 & #2 are true what steps must be done to do this?
> Note: I have already tried the sp_changeMergePublication sp with no
> success:
> exec sp_changeMergePublication
> @.publication = 'rep_test_hub',
> @.property = allow_subscription_copy,
> @.value = true
>
> Thanks.
>
|||hummm...time to turn trace on and check the logs i guess...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%230rE$5IIGHA.1124@.TK2MSFTNGP10.phx.gbl...
> 1) I am able to do it with a pull subscription.
> 2) Yes
> 3) I ran the same command to allow this.
> --
> 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
> "astro" <astro@.bcmn.com> wrote in message
> news:N38Bf.42034$7S.25014@.tornado.rdc-kc.rr.com...
>
|||BTW - you know that after you have enabled this setting, you have to pull
your subscription to another database using an anonymous pull, and then do
the copy subscription database from this subscriber database, right?
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
"astro" <astro@.bcmn.com> wrote in message
news:OlqBf.42145$7S.2470@.tornado.rdc-kc.rr.com...
> hummm...time to turn trace on and check the logs i guess...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%230rE$5IIGHA.1124@.TK2MSFTNGP10.phx.gbl...
>

Re-set identity field

Hi:

I created a small SQL Express database/ASP.net/C# application and in the learning process. Before I implement it I would like to re-set autonumber / identity field back to 1. Also, I need to start with the blank database. I am not sure how to approach that?

Can you assist?

Thanks

http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

Reset DTS Owner Password

I have a situation where a Developer created a DTS, put an
owner password
and an user password & Finally forgot both.
Developer requested me, if I could retrieve / reset the
password.
Even after spending a lot of time with BOL / Internet on
the same topic, I
could not solve this problem.
Whar are methods to RESET / CHANGE a DTS's OWNER / USER
passwords ?
And
Why can't "sysadmin" members reset a password of a DTS
without knowing the
original password? (I know they can reset login password
without knowing
password !! )
-- Amithttp://www.sqldts.com/default.aspx?212|||does reset owner.
I want to reset package owner password.
As owner password is not available with a sa, DTS Designer does not allow "s
a" to edit package / open package / schedule package.
-- amit

Requsting advice on nightly backup

This is our current schedule for SQL backup:
Backups created by Enterprise Manager
Nightly backup 12:00midnight - Complete backup checked, Transaction Log Backup not checked
Weekly optimization on Saturday - Optimization and Ingetrigy checked.
Transaction Logs Every hour 8am to 9pm - Transaction Log Backup checked.
Is it overkill to include the Transaction Log backup on our Nightly backup ?
Thanks
--
Message posted via http://www.sqlmonster.comHi Jeffrey,
If you are ending transaction log backup at 9 PM and want to take
transaction log backup again at midnight, it will have 3 hrs of transactions.
But you are anyways taking full back up at midnight. I do not understand why
you want to take nightly transaction log backup.
If my understanding is wrong, please update me with more information.
--
Thanks
GYK|||Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this backup plan. You confirmed what I was second guessing that if I have 3 hours of transactions between 9pm and 12 (which is rare) they would be part of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
--
Message posted via http://www.sqlmonster.com|||Keep in mind that the FULL and DIFFERNTIAL database backups are independent
from the LOG transaction log backups.
The transaction log will not remove transactions from the files until after
a transaction log backup is performed if you are running any recovery mode
except simple. A FULL nor a DIFFERENTIAL database backup does NOT remove
transactions from the transaction log regardless of the recovery model.
Sincerely,
Anthony Thomas
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:418f0f5db2de404da1b2043e344372d5@.SQLMonster.com...
Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this
backup plan. You confirmed what I was second guessing that if I have 3
hours of transactions between 9pm and 12 (which is rare) they would be part
of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
--
Message posted via http://www.sqlmonster.com

Requsting advice on nightly backup

This is our current schedule for SQL backup:
Backups created by Enterprise Manager
Nightly backup 12:00midnight - Complete backup checked, Transaction Log Backup not checked
Weekly optimization on Saturday - Optimization and Ingetrigy checked.
Transaction Logs Every hour 8am to 9pm - Transaction Log Backup checked.
Is it overkill to include the Transaction Log backup on our Nightly backup ?
Thanks
Message posted via http://www.sqlmonster.com
Hi Jeffrey,
If you are ending transaction log backup at 9 PM and want to take
transaction log backup again at midnight, it will have 3 hrs of transactions.
But you are anyways taking full back up at midnight. I do not understand why
you want to take nightly transaction log backup.
If my understanding is wrong, please update me with more information.
Thanks
GYK
|||Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this backup plan. You confirmed what I was second guessing that if I have 3 hours of transactions between 9pm and 12 (which is rare) they would be part of the complete backup at midnight
with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.sqlmonster.com
|||Keep in mind that the FULL and DIFFERNTIAL database backups are independent
from the LOG transaction log backups.
The transaction log will not remove transactions from the files until after
a transaction log backup is performed if you are running any recovery mode
except simple. A FULL nor a DIFFERENTIAL database backup does NOT remove
transactions from the transaction log regardless of the recovery model.
Sincerely,
Anthony Thomas

"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:418f0f5db2de404da1b2043e344372d5@.SQLMonster.c om...
Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this
backup plan. You confirmed what I was second guessing that if I have 3
hours of transactions between 9pm and 12 (which is rare) they would be part
of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.sqlmonster.com
|||Don't think of tr log backups as being connected to the full / diff
backups.
One of the good things about tr log backups is that if you have the full
sequence you can retore even if you have a corrupt full backup by going
back to the previous full backup.
Are you testing backups? Until you restore a backup you don't know
whether it is good or not. On important databases I do test restores on
every full backup. You also might consider log shipping the tr log
backups and applying them to the restored full.
I don't like doing integrity checks on production databases but do them
on the restored backup.
Have a look at
http://www.mindsdoor.net/#Administration
http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
http://www.mindsdoor.net/SQLAdmin/s_TestRestore.html
http://www.mindsdoor.net/SQLAdmin/s_nrSyncDir.html
There's stuff there to backup databases, copy backups and do test
restores and to copy the log backups - I'll at the sp to restores to
complete the log shipping sometime.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Requsting advice on nightly backup

This is our current schedule for SQL backup:
Backups created by Enterprise Manager
Nightly backup 12:00midnight - Complete backup checked, Transaction Log Back
up not checked
Weekly optimization on Saturday - Optimization and Ingetrigy checked.
Transaction Logs Every hour 8am to 9pm - Transaction Log Backup checked.
Is it overkill to include the Transaction Log backup on our Nightly backup ?
Thanks
Message posted via http://www.droptable.comHi Jeffrey,
If you are ending transaction log backup at 9 PM and want to take
transaction log backup again at midnight, it will have 3 hrs of transactions
.
But you are anyways taking full back up at midnight. I do not understand why
you want to take nightly transaction log backup.
If my understanding is wrong, please update me with more information.
Thanks
GYK|||Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this bac
kup plan. You confirmed what I was second guessing that if I have 3 hours o
f transactions between 9pm and 12 (which is rare) they would be part of the
complete backup at midnight
with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.droptable.com|||Keep in mind that the FULL and DIFFERNTIAL database backups are independent
from the LOG transaction log backups.
The transaction log will not remove transactions from the files until after
a transaction log backup is performed if you are running any recovery mode
except simple. A FULL nor a DIFFERENTIAL database backup does NOT remove
transactions from the transaction log regardless of the recovery model.
Sincerely,
Anthony Thomas
"Jeffrey Sheldon via droptable.com" <forum@.droptable.com> wrote in message
news:418f0f5db2de404da1b2043e344372d5@.SQ
droptable.com...
Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this
backup plan. You confirmed what I was second guessing that if I have 3
hours of transactions between 9pm and 12 (which is rare) they would be part
of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.droptable.com|||Don't think of tr log backups as being connected to the full / diff
backups.
One of the good things about tr log backups is that if you have the full
sequence you can retore even if you have a corrupt full backup by going
back to the previous full backup.
Are you testing backups? Until you restore a backup you don't know
whether it is good or not. On important databases I do test restores on
every full backup. You also might consider log shipping the tr log
backups and applying them to the restored full.
I don't like doing integrity checks on production databases but do them
on the restored backup.
Have a look at
http://www.mindsdoor.net/#Administration
http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
http://www.mindsdoor.net/SQLAdmin/s_TestRestore.html
http://www.mindsdoor.net/SQLAdmin/s_nrSyncDir.html
There's stuff there to backup databases, copy backups and do test
restores and to copy the log backups - I'll at the sp to restores to
complete the log shipping sometime.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Req code example of selecting MS sql MSDE database stored locally TIA sal

Hello,
I'm trying to find an example in vb.net of how I can have a user select a MS SQL database stored locally
created using MSDE.
TIAStart with this:
http://www.microsoft.com/downloads/details.aspx?familyid=08e3d5f8-033d-420b-a3b1-3074505c03f3&displaylang=en
<sal@.spp.net> wrote in message
news:KK5id.35893$Tq1.10254@.bignews1.bellsouth.net...
> Hello,
> I'm trying to find an example in vb.net of how I can have a user select a
MS SQL database stored locally
> created using MSDE.
> TIA
>
>

reprot subscription error

1).I got an error:
subscription cannot be created because the credentials used to run the
report are not stored, the report is using a user-defined parameter value...
However, can I solve this issue.
2). Is there any step by step guide to configure subscription.
ThanksThis is not complaining about Parameters, but about the data source... Open
the data source (on the report server) that this report uses, and select the
box , credentials stored securely on the server... then choose whichever
suboption suites you..
When a subscription is run, no user is running the report, instead the
server is running the report, so the login has to be stored. The server will
then use that login to run the subscription.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:24A30193-13D9-41E9-8F48-AA572D4101D0@.microsoft.com...
> 1).I got an error:
> subscription cannot be created because the credentials used to run the
> report are not stored, the report is using a user-defined parameter
> value...
> However, can I solve this issue.
> 2). Is there any step by step guide to configure subscription.
> Thanks
>

REPOST:Cannot find Snapshot 2005

Last week I made the following post...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Running Win 2003 & SQL 2005 on both machines.
I created a publication on serverA. I subscribed to it on serverB. I
received the error...
Command attempted:
E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\REPLDATA\unc\unc\R99S-SQLA_DBMOTO_AS400 MASTER
FILES\20061206113840\APVENDP_2.sch
(Transaction sequence number: 0x000049A200000088002A00000000, Command
ID: 16)
Error messages:
The device is not ready.
Thinking that replication was attempting to access the snapshot
locally, I changed to the location of the snapshot in the subscription
to \\serverA\E$\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\REPLDATA\unc\unc\R99S-SQLA_DBMOTO_AS400 MASTER
FILES\20061206113840\APVENDP_2.sch
Note that the location of the snapshot is ....repldata\unc\unc\... in
the error message. I browsed to serverA & there is no
....repldata\unc\unc folder, just a ...repldata\unc folder, then the
folder R99S-SQLA_DBMOTO_AS400 MASTER FILES.
Next I changed the location of the snapshot to reflect where the
subscription was looking ...repldata\unc\unc & now the subscription is
initialized & receiving transactions from the publisher.
With all that said....does anyone know why I had to jump through these
back flips to get replication to work when I did not deviate from the
defaults?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hilary posted the following
It sounds like your default snapshot share location was not specified
correctly. I think it was the double unc which gave you this error
message.
I take it this is a pull, if that is the case you don't need to use a
UNC.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This tells me what the problem is but does not give me any directing in
resolution.
How do I fix this? I do not want to have to do this for every
publication/subscription I create.
AHIA,
Larry...
Larry, you can change the default snapshot location for your publisher from
SSMS by Right-click Replication folder->Distributor Properties->Select the
Publishers page on the left pane->Clink on the ... button for the publisher
you want to change on the right pane->Modify the Default Snapshot Folder
field in the pop-up dialogue box and hit Ok->Hit Ok *again* on the
Distributor Properties dialog box to commit the change. Note that the change
will not be picked up until the next snapshot being generated.
Hope that helps,
-Raymond
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1166644241.462462.124840@.73g2000cwn.googlegro ups.com...
> Last week I made the following post...
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Running Win 2003 & SQL 2005 on both machines.
>
> I created a publication on serverA. I subscribed to it on serverB. I
> received the error...
> Command attempted:
> E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\REPLDATA\unc\unc\R99S-SQLA_DBMOTO_AS400 MASTER
> FILES\20061206113840\APVENDP_2.sch
> (Transaction sequence number: 0x000049A200000088002A00000000, Command
> ID: 16)
>
> Error messages:
> The device is not ready.
>
> Thinking that replication was attempting to access the snapshot
> locally, I changed to the location of the snapshot in the subscription
> to \\serverA\E$\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\REPLDATA\unc\unc\R99S-SQLA_DBMOTO_AS400 MASTER
> FILES\20061206113840\APVENDP_2.sch
>
> Note that the location of the snapshot is ....repldata\unc\unc\... in
>
> the error message. I browsed to serverA & there is no
> ...repldata\unc\unc folder, just a ...repldata\unc folder, then the
> folder R99S-SQLA_DBMOTO_AS400 MASTER FILES.
>
> Next I changed the location of the snapshot to reflect where the
> subscription was looking ...repldata\unc\unc & now the subscription is
>
> initialized & receiving transactions from the publisher.
>
> With all that said....does anyone know why I had to jump through these
>
> back flips to get replication to work when I did not deviate from the
> defaults?
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Hilary posted the following
> It sounds like your default snapshot share location was not specified
> correctly. I think it was the double unc which gave you this error
> message.
> I take it this is a pull, if that is the case you don't need to use a
> UNC.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> This tells me what the problem is but does not give me any directing in
> resolution.
>
> How do I fix this? I do not want to have to do this for every
> publication/subscription I create.
>
> AHIA,
> Larry...
>

Repost + Alternative

Hi,
Haven't found an answer yet but I'm still hoping. When we try to import data
using the Other (ODBC Data Source) and select the DSN created for the Excel
spreadsheet then no data is listed. However, if we choose "Microsoft Excel
97-2000" as the Data Source and select the appropriate excel file when
importing data then the Excel data is listed. What underlying technique is
used when selecting "Microsoft Excel 97-2000" and can we use it in Transact
SQL?
Thanks,
Alain
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server
tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling
that
> some setting (registry) is missing or is incorrect or that a file is
missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> Cortex
Alan,
this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
Not sure if that error is generic but I get that error when I leave the File
open in Excel
Also see KB below on possible issue with version of Jet DLL
http://support.microsoft.com/default...b;en-us;818182
Andy Ball
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Haven't found an answer yet but I'm still hoping. When we try to import
data
> using the Other (ODBC Data Source) and select the DSN created for the
Excel
> spreadsheet then no data is listed. However, if we choose "Microsoft Excel
> 97-2000" as the Data Source and select the appropriate excel file when
> importing data then the Excel data is listed. What underlying technique is
> used when selecting "Microsoft Excel 97-2000" and can we use it in
Transact[vbcol=seagreen]
> SQL?
> Thanks,
> Alain
>
> "Alain Sienaert" wrote:
> tables.
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
provider[vbcol=seagreen]
give[vbcol=seagreen]
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
application[vbcol=seagreen]
run[vbcol=seagreen]
data.
> that
> missing
>
|||Alain,
Thanks for your suggestions however they do not solve the problem.
1) I checked the MSJET40.dll and this is the latest one available. I also
created a Test.udl file with the following properties:
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
When pressing Test Connection the program replies with Test connection
succeeded.
2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
pressing Test Connection the program also replies with Test connection
succeeded.
3) When we include one of our sql statements that fail when run from the
Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
Package without any problems.
Provider:
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Alan,
> this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
> Not sure if that error is generic but I get that error when I leave the
File[vbcol=seagreen]
> open in Excel
> Also see KB below on possible issue with version of Jet DLL
> http://support.microsoft.com/default...b;en-us;818182
>
> Andy Ball
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> data
> Excel
Excel[vbcol=seagreen]
is[vbcol=seagreen]
> Transact
MS[vbcol=seagreen]
> (Windows
> Excel
> provider
> give
MS[vbcol=seagreen]
> (Windows
> Excel
Cannot[vbcol=seagreen]
> application
IDBInitialize::Initialize[vbcol=seagreen]
> run
> data.
files).
>
|||Andy,
You can tell that it is getting late (about 12:45 AM). I call you Alain and
forget to close my message with a greeting.
Kind regards,
Alain
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Alain,
> Thanks for your suggestions however they do not solve the problem.
> 1) I checked the MSJET40.dll and this is the latest one available. I also
> created a Test.udl file with the following properties:
> - Provider: Microsoft OLE DB Provider for ODBC Drivers
> - Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
> When pressing Test Connection the program replies with Test connection
> succeeded.
>
> 2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
> pressing Test Connection the program also replies with Test connection
> succeeded.
>
> 3) When we include one of our sql statements that fail when run from the
> Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
> Package without any problems.
>
>
> Provider:
> "Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
> news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
covers.[vbcol=seagreen]
> File
import[vbcol=seagreen]
> Excel
technique[vbcol=seagreen]
> is
> MS
> MS
> Cannot
> IDBInitialize::Initialize
and[vbcol=seagreen]
to[vbcol=seagreen]
feeling
> files).
>
|||you can call me betty ..:-)
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
What happens if you run the following from QA :
EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
cheers,
Andy.
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> Andy,
> You can tell that it is getting late (about 12:45 AM). I call you Alain
and[vbcol=seagreen]
> forget to close my message with a greeting.
> Kind regards,
> Alain
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
also[vbcol=seagreen]
when[vbcol=seagreen]
the[vbcol=seagreen]
> covers.
the[vbcol=seagreen]
> import
the[vbcol=seagreen]
when[vbcol=seagreen]
> technique
server[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
not[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
> and
Excel[vbcol=seagreen]
> to
the[vbcol=seagreen]
> feeling
is
>
|||Dear Betty ,
Thanks for your assistance so far...
Please find my answers below.
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
Yes, I did run the DTS from a Client workstation. Running it in Design Mode
directly on server does not work. Same error as before:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error. OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned
0x80004005: The provider did
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
No, running the command on the workstation does work. It fails when running
it on the server. Please note that the client workstation account has less
access rights than the account used when running the tests on the server
(administrator account). I guess the client workstation is using its own
configuration (OLE DB objects) to access the appropriate file?
What happens if you run the following from QA : EXEC master.dbo.xp_cmdshell
'dir \\server\file.xls'
It returns the expected information such as Volume, Volume Serial Number,
Date and Timestamp, etc...
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
The SQL Server runs under a domain account with a password that never
expires (functional account). This account has administrator rights on the
sql server and is used when running the tests on the server. Please note
that the Excel file is on the same server.
I have the feeling that something is wrong in the registry or so, since I am
able to access Excel 4.0 files.
Cheers,
Alain
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ux30zW4XEHA.3972@.TK2MSFTNGP12.phx.gbl...
> you can call me betty ..:-)
> 1. So when u run in DTS u are in Design Mode on a Client workstation ?
When[vbcol=seagreen]
> happens if you schedule same package under SQLAgent - does it still work ?
> 2. When you run from SQL Server QA and it fails you are also running from
> the workstation right ?
> What happens if you run the following from QA :
> EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
> 3. What account does SQL Server Service start under ? System or a domain
> account ? When using QA it will used the Service Account credentials to
> access the spreadsheet (presuming you are SA equivalent)
> cheers,
> Andy.
>
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> and
> also
> when
the[vbcol=seagreen]
> the
> the
> the
"Microsoft[vbcol=seagreen]
> when
> server
> SP6a -
is[vbcol=seagreen]
> an
> not
> SP6a -
Driver[vbcol=seagreen]
is[vbcol=seagreen]
> an
Driver][vbcol=seagreen]
format[vbcol=seagreen]
> Excel
seem[vbcol=seagreen]
> the
file
> is
>

Repost - Forms Auth and the ReportServer web

I have an interesting issue. I setup my RS to use Forms Auth. I created a
logon page for the Reports web and and a logon page for the ReportServer
web. The page displays a
company logo, textboxes to enter credentials, logon button, etc. Nothing
extremely fancy.
The issue is this. When I navigate to the Reports web (Report Manager) the
logon page displays with the logo. When I navigate to the
ReportServer web, the logo image doesn't show.
What gives?
--
Adrian Maull
[MCP]Adrian M. wrote:
> The issue is this. When I navigate to the Reports web (Report
> Manager) the logon page displays with the logo. When I navigate to
> the
> ReportServer web, the logo image doesn't show.
> What gives?
It seems that you use a wrong virtual path to your image (i.e.
/images/logo.gif)
Try to use a "full" virtual path (i.e. http://localhost/images/logo.gif)
regards
frank

Reposition Subtotals

Hi,
I have created a very simple tabular report with only one row group which I
have enabled subtotals on. What I am not enjoying is that the subtotals
seem to HAVE to be at the top of the group:
Below is an example. The DEPT header is the row group.
DEPT |INV_NO |VALUE
--
TX10 | - | £600.00
| SI123456 | £200.00
| SI123457 | £200.00
| SI123458 | £200.00
MX10 | - | £900.00
| SI123456 | £300.00
| SI123457 | £300.00
| SI123458 | £300.00
HX10 | - | £300.00
| SI123456 | £100.00
| SI123457 | £100.00
| SI123458 | £100.00
In the above example the subtotal for the group is in the same line as the
row name. Is there a way of getting it to be positioned at the bottom? For
example:
DEPT |INV_NO |VALUE
--
TX10 | - |
| SI123456 | £200.00
| SI123457 | £200.00
| SI123458 | £200.00
| | £600.00
TIA,
JarrydWhen you define a group, by default it shows you the group header, detail
line and group footer.
Have you tried to put subtotals in the group footer?
"Jarryd" <jarryd@.community.nospam> escribió en el mensaje
news:uUWbDFEKIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have created a very simple tabular report with only one row group which
> I have enabled subtotals on. What I am not enjoying is that the subtotals
> seem to HAVE to be at the top of the group:
> Below is an example. The DEPT header is the row group.
> DEPT |INV_NO |VALUE
> --
> TX10 | - | £600.00
> | SI123456 | £200.00
> | SI123457 | £200.00
> | SI123458 | £200.00
> MX10 | - | £900.00
> | SI123456 | £300.00
> | SI123457 | £300.00
> | SI123458 | £300.00
> HX10 | - | £300.00
> | SI123456 | £100.00
> | SI123457 | £100.00
> | SI123458 | £100.00
> In the above example the subtotal for the group is in the same line as the
> row name. Is there a way of getting it to be positioned at the bottom?
> For example:
> DEPT |INV_NO |VALUE
> --
> TX10 | - |
> | SI123456 | £200.00
> | SI123457 | £200.00
> | SI123458 | £200.00
> | | £600.00
> TIA,
> Jarryd
>|||GENIUS!! I knew it had to be something simple. Thanks a mil!
Jarryd
"Mónica" <monica.diaz@.augure.com> wrote in message
news:ejsd0hEKIHA.4752@.TK2MSFTNGP05.phx.gbl...
> When you define a group, by default it shows you the group header, detail
> line and group footer.
> Have you tried to put subtotals in the group footer?
>
> "Jarryd" <jarryd@.community.nospam> escribió en el mensaje
> news:uUWbDFEKIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have created a very simple tabular report with only one row group which
>> I have enabled subtotals on. What I am not enjoying is that the
>> subtotals seem to HAVE to be at the top of the group:
>> Below is an example. The DEPT header is the row group.
>> DEPT |INV_NO |VALUE
>> --
>> TX10 | - | £600.00
>> | SI123456 | £200.00
>> | SI123457 | £200.00
>> | SI123458 | £200.00
>> MX10 | - | £900.00
>> | SI123456 | £300.00
>> | SI123457 | £300.00
>> | SI123458 | £300.00
>> HX10 | - | £300.00
>> | SI123456 | £100.00
>> | SI123457 | £100.00
>> | SI123458 | £100.00
>> In the above example the subtotal for the group is in the same line as
>> the row name. Is there a way of getting it to be positioned at the
>> bottom? For example:
>> DEPT |INV_NO |VALUE
>> --
>> TX10 | - |
>> | SI123456 | £200.00
>> | SI123457 | £200.00
>> | SI123458 | £200.00
>> | | £600.00
>> TIA,
>> Jarryd
>

ReportViewer's ReportError event handler not firing

Hi.
I want to intercept any errors generated when the report's stored procedure
executes. I've created a handler for the ReportError event but it never
fires.
My web page is in C# and has AutoEventWireup="true". I even tried explicitly
adding a handler...
theReportViewer.ReportError += new ReportErrorEventHandler(explicitHandler);
...but my handlers don't get run.
What have I missed? Is there a property that needs turning on as well as
creating an event handler?
I'm running with VS sp1.
Thanks,
AndrewForgot to say the report is a server report.
Andrew|||Hi Andrew,
From your description, you're wondering how to capture some error raised in
store-procedure(used in SSRS sever report query) in your client ASP.NET
reportviewer,correct?
Based on my research, the ReportViewer control's ReportError event is
implemented as below:
In ReportViewer's rendering code(such as PreRender, or ReportArea's control
creation and rendering methods...), it will wrapper the code with a
try...catch... block and if any exceptions occur, it will check the
"ReportError" event handler and invoke it if registered.
So for your scenario, I think it problem is possible due to the server-side
report's processing is not in reportviewer(but in the server reportserver's
processing engine). Therefore, the related store-procedure error is not
propagate to the client-side reportviewr control and you didn't get the
event raised. To verify this, you can try using a client report and raise
some error in it to see whether the event work. Also, based on my test, if
the report server(server report) not available, it will also raise error.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Steven.
I had a poke around with Reflector and was coming to the same conclusion.
Thinking about it, when running async the host page is rendered and returned
whilst the report is still running, so even if the server report errors it
wouldn't have anything to report its error to.
I guess we can always look at the ReportServer log file to see what's
occurred.
Thanks,
Andrew|||Thanks for your reply Andrew,
Yes, your further analysis is also reasonable. Actually, exception
propagating is always quite difficult when dealing with distributed
component or service. Anyway, it is a pleasure to discuss with you on this.
If you meet any other problems later, welcome to discuss here.
Have a nice day!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.