Need to sum columns: February 2012

Saturday, February 25, 2012

Need Urgent Help...............

Hi i am manish,working wth sql server2000
Need Urgent Help............
Currently,we are preparing our one software,
I want to enfore some security check ,so that user of that software not able
to watch what kind of data that software contains
security i need for that
1) application level: no other external software are allowed
2)default Windows NT groups are to be disable
3) user should able to execute any kind select ,etc query
Regards
ManishHave a look at Application Roles in BOL. By activating an application role
from your application and by only granting permissions to that application
role, you should be able to get the behaviour you require
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Manish Mokadam" <manish.mokadam@.honeywell.com> wrote in message
news:471E447A-D6B2-4361-8814-AF59362FA34D@.microsoft.com...
> Hi i am manish,working wth sql server2000
> Need Urgent Help............
> Currently,we are preparing our one software,
> I want to enfore some security check ,so that user of that software not
able to watch what kind of data that software contains
> security i need for that
> 1) application level: no other external software are allowed
> 2)default Windows NT groups are to be disable
> 3) user should able to execute any kind select ,etc query
>
> Regards
> Manish

Need urgent help.. for SQL Server reporting services installation..

hi,

I have SQL Server Express edition installed on my PC , but now I need to use SQL Server reporting service, there is SP1 for express edition contains reporting service with express edition. Here..

http://msdn.microsoft.com/vstudio/express/sql/download/

Do I need to download 213 MB SQL Server 2005 Express Edition Toolkit SP1 ?

or there is any other method to use reporting services ?

Thanks

You will have to download the full install again, there is no way of installing the components seperatly. With SQl 2000 there used to be a seperate DL for Reporting Services, but in the 2005 stream they are all included in the one package.

Need URGENT help.

Hello-
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operation.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours'
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
--
Regards,
MZeeshanHi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours'
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
--
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the data
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> > Hello-
> >
> > I am trying to restore a large sized database (100+ GB) on a machine that
> > didn't have database in the beginning, so its a create DB + restore
> > operation.
> >
> > Below is the machine configuration:
> >
> > Machine Configuration: quad processor (Pentium 3 Xeon)
> > Memory: 2 GB
> > HDD: Fiber Array Channel with separate controllers.
> > Version: SQL Server 2000/SP3 (Standard Version)
> > OS: Windows 2003/SP1 (sp presence is a guess).
> >
> > I understand the process will take some time to create database first
> > before
> > starting restoration. So far, its been running for over 2 1/2 hours
> > without
> > any sign of restoration (I have kept STATS=1 for getting any indication of
> > restoration start).
> >
> > Now, I have three(3) questions:
> >
> > 1. What should be the approximate DB creation time in such type of
> > environment? 3-4 hours'
> >
> > 2. How come I know if there is a problem? Currently the restore process
> > has
> > wait type of ASYNC_IO_COMPLETION with very large wait time.
> >
> > 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> > db creation/restoration) across all processers? OR this is only true in
> > some
> > instances say index rebuild?
> >
> > Thanks in advance!!!
> >
> > --
> > Regards,
> > MZeeshan
>
>|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Fist SQL Server has to create the DB and allocate all the pages for the
>> data
>> and log. This can take the longest.
>> Once this is done, the restore can really happen.
>> A 100GB DB can take anything between 30 minutes and 10 hours to create,
>> depending on the IO performance of the disk subsystem.
>> Regards
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a machine
>> > that
>> > didn't have database in the beginning, so its a create DB + restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 hours
>> > without
>> > any sign of restoration (I have kept STATS=1 for getting any indication
>> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY workload
>> > (here
>> > db creation/restoration) across all processers? OR this is only true in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > --
>> > Regards,
>> > MZeeshan
>>|||This is a multi-part message in MIME format.
--=_NextPart_000_0595_01C56968.94534E90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
>
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
>
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
>
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
>
> --
> Regards,
> MZeeshan
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Fist SQL Server has to create the DB and allocate all the pages for the
>> data
>> and log. This can take the longest.
>>
>> Once this is done, the restore can really happen.
>>
>> A 100GB DB can take anything between 30 minutes and 10 hours to create,
>> depending on the IO performance of the disk subsystem.
>>
>> Regards
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: mike@.epprecht.net
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a machine
>> > that
>> > didn't have database in the beginning, so its a create DB + restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 hours
>> > without
>> > any sign of restoration (I have kept STATS=1 for getting any
indication
>> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore
process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY workload
>> > (here
>> > db creation/restoration) across all processers? OR this is only true
in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > --
>> > Regards,
>> > MZeeshan
>>
>>
>>
--=_NextPart_000_0595_01C56968.94534E90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Also, not only do you have to wait =for all data files and transaction log files to be created and zeroed out, SQL Server =will first run a BACKUP VERIFICATION and you will first have to wait for SQL =Server to read through the backup file first. The file read itself can =take minutes to hours depending on size, disk throughput, and =backup file multiplexing.
If you need fast recovery, you should =consider backing up to multiple files per backup. This will allow you to =run parallel read operations for the verification and actual restore phases; =however, the raw database creation will still be solely dependent on the =disk subsystem throughput.
If you also need fast backup times, =consider multiple data files per filegroup. This will allow SQL Server to =run parallel operations for the backup process.
An alternative to these methods would =be to use one of several 3rd-party backup tools. Lightspeed by Imceda =would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)" wrote in =message news:eVKp0GXaFHA.2996=@.TK2MSFTNGP10.phx.gbl...Those operations generally do not get helped by parallelism due to their IO loads.Getting the fastest disk subsystem, and configuring =it correctly, does more to help than STD/EE editions upgrade.Regards-- --Mike = Epprecht, Microsoft SQL Server MVPZurich, SwitzerlandIM: =mike@.epprecht.netMVP =Program: http://www.microsoft.com/mvpBlog: http://www.msmvps.com/epprecht/<=/A>"MZeeshan" Thanks!>> Yes, it took just around 5 hours to create the = database and currently in> restoration phase.>> =About my last question: Have you ever noticed any visible improvement in => any> system when license is upgraded from Standard to Enterprise?>> Database creation, backup/restoration and =index rebuilds are some of the> common activities happening on this box.>> -- > Regards,> MZeeshan>>> "Mike Epprecht (SQL MVP)" wrote:>> Hi>> Fist SQL Server =has to create the DB and allocate all the pages for the > =data> and log. This can take the longest.>> Once this =is done, the restore can really happen.>> A 100GB DB can =take anything between 30 minutes and 10 hours to create,> =depending on the IO performance of the disk subsystem.>> Regards> -- > --> Mike Epprecht, Microsoft =SQL Server MVP> Zurich, Switzerland>> IM: =mike@.epprecht.net>&g=t;> MVP Program: http://www.microsoft.com/mvp>> Blog: http://www.msmvps.com/epprecht/<=/A>>> "MZeeshan" news:188=596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...> > Hello-> >> > I am trying to restore a =large sized database (100+ GB) on a machine > > =that> > didn't have database in the beginning, so its a create DB + restore> > operation.> >> > =Below is the machine configuration:> >> > Machine Configuration: quad processor (Pentium 3 Xeon)> > =Memory: 2 GB> > HDD: Fiber Array Channel with separate controllers.> > Version: SQL Server 2000/SP3 (Standard Version)> > OS: Windows 2003/SP1 (sp presence is a guess).> >> > I understand the process =will take some time to create database first> > before> => starting restoration. So far, its been running for over 2 1/2 hours> > without> > any sign of =restoration (I have kept STATS=3D1 for getting any indication > > =of> > restoration start).> >> > Now, I have = three(3) questions:> >> > 1. What should =be the approximate DB creation time in such type of> > =environment? 3-4 hours'> >> > 2. How come I know if there =is a problem? Currently the restore process> > =has> > wait type of ASYNC_IO_COMPLETION with very large wait =time.> >> > 3. Can upgrading to Enterprise version help =parallelize ANY workload > > (here> > db =creation/restoration) across all processers? OR this is only true in> > some> > instances say index rebuild?> >> > Thanks in advance!!!> =>> > -- > > Regards,> > MZeeshan>>> =

--=_NextPart_000_0595_01C56968.94534E90--|||This is a multi-part message in MIME format.
--=_NextPart_000_0024_01C569A2.12FAEC60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
ANthonys suggestion of using striped backups to improve restore speed is =a great idea...
And in response to your third question, you will not see an difference =in backup/restore speeds if you upgrade to Enterprise Edition.
-- Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message =news:eSs5dLZaFHA.3184@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction =log files to be created and zeroed out, SQL Server will first run a =BACKUP VERIFICATION and you will first have to wait for SQL Server to =read through the backup file first. The file read itself can take =minutes to hours depending on size, disk throughput, and backup file =multiplexing.
If you need fast recovery, you should consider backing up to multiple =files per backup. This will allow you to run parallel read operations =for the verification and actual restore phases; however, the raw =database creation will still be solely dependent on the disk subsystem =throughput.
If you also need fast backup times, consider multiple data files per =filegroup. This will allow SQL Server to run parallel operations for =the backup process.
An alternative to these methods would be to use one of several =3rd-party backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
-- "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message =news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to =their IO loads.
Getting the fastest disk subsystem, and configuring it correctly, =does more to help than STD/EE editions upgrade.
Regards-- --
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
>
> Yes, it took just around 5 hours to create the database and =currently in
> restoration phase.
>
> About my last question: Have you ever noticed any visible =improvement in > any
> system when license is upgraded from Standard to Enterprise?
>
> Database creation, backup/restoration and index rebuilds are some =of the
> common activities happening on this box.
>
> -- > Regards,
> MZeeshan
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Fist SQL Server has to create the DB and allocate all the pages =for the >> data
>> and log. This can take the longest.
>>
>> Once this is done, the restore can really happen.
>>
>> A 100GB DB can take anything between 30 minutes and 10 hours to =create,
>> depending on the IO performance of the disk subsystem.
>>
>> Regards
>> -- >> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: mike@.epprecht.net
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a =machine >> > that
>> > didn't have database in the beginning, so its a create DB + =restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database =first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 =hours
>> > without
>> > any sign of restoration (I have kept STATS=3D1 for getting any =indication >> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type =of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore =process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY =workload >> > (here
>> > db creation/restoration) across all processers? OR this is only =true in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > -- >> > Regards,
>> > MZeeshan
>>
>>
>>
--=_NextPart_000_0024_01C569A2.12FAEC60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

ANthonys suggestion of using striped =backups to improve restore speed is a great idea...
And in response to your third question, =you will not see an difference in backup/restore speeds if you upgrade to =Enterprise Edition.
-- Wayne Snyder, MCDBA, SQL Server MVPMariner, =Charlotte, NChttp://www.mariner-usa.com">www.mariner-usa.com(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it'scommunity of SQL Server professionals.http://www.sqlpass.org">www.sqlpass.org
"Anthony Thomas" wrote in =message news:eSs5dLZaFHA.3184=@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait =for all data files and transaction log files to be created and zeroed out, SQL =Server will first run a BACKUP VERIFICATION and you will first have to wait for =SQL Server to read through the backup file first. The file read itself can =take minutes to hours depending on size, disk throughput, and =backup file multiplexing.

If you need fast recovery, you =should consider backing up to multiple files per backup. This will allow you to =run parallel read operations for the verification and actual restore =phases; however, the raw database creation will still be solely dependent on =the disk subsystem throughput.

If you also need fast backup times, =consider multiple data files per filegroup. This will allow SQL Server to =run parallel operations for the backup process.

An alternative to these methods =would be to use one of several 3rd-party backup tools. Lightspeed =by Imceda would be a good candidate.

Sincerely,

Anthony Thomas

--
"Mike Epprecht (SQL MVP)" wrote in =message news:eVKp0GXaFHA.2996=@.TK2MSFTNGP10.phx.gbl...Those operations generally do not get helped by parallelism due to their =IO loads.Getting the fastest disk subsystem, and =configuring it correctly, does more to help than STD/EE editions upgrade.Regards-- =--Mike Epprecht, Microsoft SQL Server MVPZurich, SwitzerlandIM: =mike@.epprecht.netMVP =Program: http://www.microsoft.com/mvpBlog: http://www.msmvps.com/epprecht/<=/A>"MZeeshan" Thanks!>> Yes, it took just around 5 hours to create =the database and currently in> restoration phase.>> =About my last question: Have you ever noticed any visible improvement in => any> system when license is upgraded from Standard to Enterprise?>> Database creation, backup/restoration =and index rebuilds are some of the> common activities happening on this = box.>> -- > Regards,> MZeeshan>>> "Mike Epprecht (SQL MVP)" wrote:>> Hi>> Fist SQL =Server has to create the DB and allocate all the pages for the > data> and log. This can take the =longest.>> Once this is done, the restore can really =happen.>> A 100GB DB can take anything between 30 minutes and 10 hours to create,> depending on the IO performance of the disk subsystem.>> Regards> -- => --> Mike Epprecht, =Microsoft SQL Server MVP> Zurich, Switzerland>> =IM: mike@.epprecht.net>&g=t;> MVP Program: http://www.microsoft.com/mvp>> Blog: http://www.msmvps.com/epprecht/<=/A>>> "MZeeshan" news:188=596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...> > Hello-> >> > I am trying to restore =a large sized database (100+ GB) on a machine > > =that> > didn't have database in the beginning, so its a create DB + restore> > operation.> >> => Below is the machine configuration:> >> > =Machine Configuration: quad processor (Pentium 3 Xeon)> > =Memory: 2 GB> > HDD: Fiber Array Channel with separate controllers.> > Version: SQL Server 2000/SP3 (Standard = Version)> > OS: Windows 2003/SP1 (sp presence is a guess).> >> > I understand the process =will take some time to create database first> > =before> > starting restoration. So far, its been running for over 2 1/2 hours> > without> > any sign of =restoration (I have kept STATS=3D1 for getting any indication > > of> > restoration start).> >> => Now, I have three(3) questions:> >> > 1. =What should be the approximate DB creation time in such type =of> > environment? 3-4 hours'> >> > 2. How =come I know if there is a problem? Currently the restore =process> > has> > wait type of ASYNC_IO_COMPLETION with very =large wait time.> >> > 3. Can upgrading to =Enterprise version help parallelize ANY workload > > =(here> > db creation/restoration) across all processers? OR this is only =true in> > some> > instances say index rebuild?> >> > Thanks in advance!!!> >> > -- > > Regards,> > =MZeeshan>>>

--=_NextPart_000_0024_01C569A2.12FAEC60--

Need URGENT help.

Hello-
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operatio
n.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours'
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
Regards,
MZeeshanHi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours'
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the da
ta
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>
>|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>
indication[vbcol=seagreen]
process[vbcol=seagreen]
in[vbcol=seagreen]|||ANthonys suggestion of using striped backups to improve restore speed is a g
reat idea...
And in response to your third question, you will not see an difference in ba
ckup/restore speeds if you upgrade to Enterprise Edition.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:eSs5dLZaFHA.3184
@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction log fi
les to be created and zeroed out, SQL Server will first run a BACKUP VERIFIC
ATION and you will first have to wait for SQL Server to read through the bac
kup file first. The file read itself can take minutes to hours depending on
size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the ver
ification and actual restore phases; however, the raw database creation will
still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per filegro
up. This will allow SQL Server to run parallel operations for the backup pr
ocess.
An alternative to these methods would be to use one of several 3rd-party bac
kup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eVKp0GXa
FHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>

Need URGENT help.

Hello-
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operation.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours?
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
Regards,
MZeeshan
Hi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours?
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan
|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the data
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>
>
|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
|||Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas

"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
Regards--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
indication[vbcol=seagreen]
process[vbcol=seagreen]
in[vbcol=seagreen]
|||ANthonys suggestion of using striped backups to improve restore speed is a great idea...
And in response to your third question, you will not see an difference in backup/restore speeds if you upgrade to Enterprise Edition.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:eSs5dLZaFHA.3184@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction log files to be created and zeroed out, SQL Server will first run a BACKUP VERIFICATION and you will first have to wait for SQL Server to read through the backup file first. The file read itself can take minutes to hours depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files per backup. This will allow you to run parallel read operations for the verification and actual restore phases; however, the raw database creation will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per filegroup. This will allow SQL Server to run parallel operations for the backup process.
An alternative to these methods would be to use one of several 3rd-party backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas

"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:

Need urgent help, this is driving me crazy. SSRS 2000

Hi all,

I am trying to build a reportviewer so that I could use reporting services (due to company's security policy).

I am getting a VERY weird error here.

Before I get on ahead, here is the scenario.

I had created a rdl that will give a certain report based on two parameters. At the moment, I didn't assign the parameters to any default values since i don't want the report to start running. These two parameters are run based on stored procedures to get the starting date and an ending date.

I had tried to view this report on Report Manager and it works.

I had set the parameters this way. NOTE: ReportingServer is the disgused name of the reportserver I am using for privacy purposes

Code Snippet

Dim rs As New ReportingServer.ReportingService

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

'Render arguments

Dim results() As Byte

Dim format As String = "HTML4.0"

Dim historyID As String = Nothing

'PDF Rendering extensions don't have any useful DeviceInfo settings

'the toolbar shown below only affects HTML rendering

Dim devInfo As String = "<DeviceInfo><HTMLFragment>True</HTMLFragment><JavaScript>True</JavaScript><Toolbar>True</Toolbar><Parameters>True</Parameters></DeviceInfo>"

Dim credentials() As ReportingServer.DataSourceCredentials = New ReportingServer.DataSourceCredentials() {}

Dim showHideToggle As String = ""

Dim encoding As String = ""

Dim mimeType As String = ""

Dim warnings() As ReportingServer.Warning = New ReportingServer.Warning(0) {}

Dim reportHistoryParameters() As ReportingServer.ParameterValue = New ReportingServer.ParameterValue() {}

Dim streamIDs() As String = Nothing

Dim sh As ReportingServer.SessionHeader = New ReportingServer.SessionHeader

rs.SessionHeaderValue = sh

But when I started building this reportviewer.aspx, it gives me this error on the following line:-

Code Snippet

results = rs.Render(ReportPath, format, historyID, devInfo, Parameters, credentials, _

showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)

with the error

{"This report requires a default or user-defined value for the report parameter 'start_date'. To run or subscribe to this report, you must provide a parameter value. --> This report requires a default or user-defined value for the report parameter 'start_date'. To run or subscribe to this report, you must provide a parameter value. --> This report requires a default or user-defined value for the report parameter 'start_date'. To run or subscribe to this report, you must provide a parameter value."}

this is occuring on the line where it needs to render the report.

As simple as it is, i tried the following:-

NOTE: some variables has been changed to protect some identity.

Code Snippet

Dim ReportRenderer As New RenderSQLReports

Dim Parameters As int.rrd.sqlreporting_test.ParameterValue() = Nothing

ReportRenderer.RenderReport(Parameters, "/Folder/reportname", "pdffilename.pdf")

What I don't get is that i made sure that it doesn't have any default values.

So I went into this scenario and tried teh following:-

Code Snippet

Dim ReportRenderer As New RenderSQLReports

Dim Parameters() As Reportingservices.ParameterValue = New Reportingservices.ParameterValue(1) {}

Parameters(0) = New Reportingservices.ParameterValue

Parameters(0).Label = "start_date"

Parameters(0).Name = "start_date"

Parameters(0).Value = "10/1/1999 12:00:00 AM"

Parameters(1) = New Reportingservices.ParameterValue

Parameters(1).Label = "end_date"

Parameters(1).Name = "end_date"

Parameters(1).Value = "11/1/1999 12:00:00 AM"

ReportRenderer.RenderReport(Parameters, "/Folder/reportname", "pdffilename.pdf")

And this is what happens. It blows up on this line again. with this error.

Code Snippet

results = rs.Render(ReportPath, format, historyID, devInfo, Parameters, credentials, _

showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)

With the errors

{"Default value or value provided for the report parameter 'start_date' is not a valid value. --> Default value or value provided for the report parameter 'start_date' is not a valid value. --> Default value or value provided for the report parameter 'start_date' is not a valid value."}

This is probably occuring because the date I had provided is not found in the list of dates. I really don't want to default a date at the moment, to allow the user to select a date.

So I finally gave it a date...... a valid date.

Code Snippet

Dim ReportRenderer As New RenderSQLReports

Dim Parameters() As Reportingservices.ParameterValue = New Reportingservices.ParameterValue(1) {}

Parameters(0) = New Reportingservices.ParameterValue

Parameters(0).Label = "start_date"

Parameters(0).Name = "start_date"

Parameters(0).Value = "10/1/2004 12:00:00 AM"

Parameters(1) = New Reportingservices.ParameterValue

Parameters(1).Label = "end_date"

Parameters(1).Name = "end_date"

Parameters(1).Value = "11/1/2004 12:00:00 AM"

ReportRenderer.RenderReport(Parameters, "/Folder/reportname", "pdffilename.pdf")

LO and behold, it works BUT . now i get this error in the aspx code.

Runtime error has occured.

Microsoft JScript runtime error: 'Report' is undefined

on this weird line:- (that was generated automatically)

</script><script language="javascript" type="text/javascript" src="?rs:Command=Get&rc:GetImage=8.00.1038.00Report.js">

</script><script language="javascript" type="text/javascript">

<!--

function OnLoadReport()

{

var pageHits = null;

var rep = new Report(1, 16, pageHits, false, docMapIds); <-where error is occuring.

if (parent != self) parent.OnLoadReport(rep);

}

//-->

</script>

It did show the report, but I don't understand why it is still getting an error here. Moreover, I didn't even get the HTMLViewer too even when I had assigned the devinfo....

Really need some help here, as this is quite urgent.... I am at a loss at this, and maybe that there is an option that I am not seeing or something I am missing. please help .

Bernard

-So close yet so far, How cruel are the software bugs-

alright ..

I am solving this bit by bit, but the most important is that I want to get the html report with the Toolbar and parameters to show.

So far I am setting up the default values at the moment, and so far it is rendering the report.

How do I show te Toolbar (HtmlVIEwer) and the Parameters section to show on html ?

I Tried the following in the Devinfo

Dim devInfo As String = "<DeviceInfo><Toolbar>True</Toolbar></DeviceInfo>"

And I got the following error in the Aspx code.
Microsoft JScript runtime error: 'Report' is undefined

on this weird line:- (that was generated automatically)

</script><script language="javascript" type="text/javascript">

<!--

function OnLoadReport()

{

var pageHits = null;

var rep = new Report(1, 16, pageHits, false, docMapIds); <-where error is occuring.

if (parent != self) parent.OnLoadReport(rep);

}

//-->

</script>

if anybody could tell me what i need to do, that will be really useful and helpful.

Thanks !

Bernard

-So close yet so far, How cruel are the software bugs-

|||

Btong wrote:

alright ..

I am solving this bit by bit, but the most important is that I want to get the html report with the Toolbar and parameters to show.

So far I am setting up the default values at the moment, and so far it is rendering the report.

How do I show te Toolbar (HtmlVIEwer) and the Parameters section to show on html ?

I Tried the following in the Devinfo

Dim devInfo As String = "<DeviceInfo><Toolbar>True</Toolbar></DeviceInfo>"

And I got the following error in the Aspx code.
Microsoft JScript runtime error: 'Report' is undefined

on this weird line:- (that was generated automatically)

<script language="javascript" type="text/javascript" src="?rs:Command=Get&rc:GetImage=8.00.1038.00Report.js">
</script><script language="javascript" type="text/javascript">



</script>

if anybody could tell me what i need to do, that will be really useful and helpful.

Thanks !

Bernard

-So close yet so far, How cruel are the software bugs-

Alright Folks.

Got some good news, Managed to build my own HTMLViewer and ReportViewer with some available source code. I am able to set up the HTML for HTMLViewer and ReportViewer. However, in the course of doing so, I came across a very good question.

I had tried to set up the ASMX and use the property "Parameters" and "Toolbar" to set up the HTMLViewer but to no avail.

Setting "<Parameter>True</Parameter>" in the Devinfo string did not make the Parameters section appear in the HTML.

Setting "<Toolbar>True</Toolbar>" in the devinfo string caused a javascript error as described below:-

Microsoft JScript runtime error: 'Report' is undefined

this occured in :-

</script><script language="javascript" type="text/javascript">

<!--

function OnLoadReport()

{

var pageHits = null;

var rep = new Report(1, 16, pageHits, false, docMapIds); <-where error is occuring.

if (parent != self) parent.OnLoadReport(rep);

}

//-->

</script>

If I set these parameters to false, My report does generate in html.

I am wondering whether is this a bug? if this is so, is there a hotfix for it ? Or am I missing something crucial at this point ?

Can someone confirm this ? Thanks !

Bernard

-So close yet so far, How cruel are the software bugs-

|||

hi All,

Just had a very interesting conversation with one of the SSRS coders/creators and I am very enlightened in this situation.

I had just confirmed the following:-

1) This is not a bug per say. It was never considered that the rendering the report through API will let you set up the PArameters or the toolbar section.

2) the devinfo is really use for the URL access for the reports.

3) If rendering the reports through API, you have to either set almost all properties to False OR set a null in the "<devinfo></devinfo>" in the devinfo string or it won't render. (getting the javascript error as described as above)

4) This devinfo only works if you are on the Report Manager page and won't work if you are trying to view the htmlviewer in another browser.

There are probably a lot of SSRS developers who may wonder "Why go through all the trouble trying to figure this out" OR "so what?"

Well I could see a couple of scenarios here.

1) Clients have full access to Report Manager. (hence the URL access)

2) Developers have to come up with some way to retrieve the parameters, render the report once. (this prevents the clients from accessing the report manager)

Now I could have gone through the second route, but I figured that maybe I could use the HTMLViewer option you would see in the Report Manager without having to expose the URL directly to the clients. (This is due to Company's policy, firewall issues as well as security issues and sensitive data issues).

I didn't want to build a dozen "middlemen" pages just to collect the parameters and then throw it to the rdl via asmx. (waste of time modifying all of them and really unsensible too).

In the end, I managed to build a class that will help in this situation. Not exposing the url directly when a user tries to view the source, and no one has to know where or what the url is, my ReportViewer page prevents that.

I thought that by using the devinfo through ASMX would provide some sort of Report Manager capability without exposing the direct URL.
No one has yet to ask if there is ever a fix that would allow the devinfo string to be utilized via ASMX (it seems that I was the first one to bring this up). So hopefully this may or may not provide some sort of closure to anyone who might be in the same situation as I am.

Thanks for reading this post !

Need Urgent Help!

I imported xl spreadsheet data into sql server. Some data is converted with
<NULL> value even though the data exists in the xl spreadsheet column.
The funny part is it is not <NULL> for the entire column. For eg. I have a
column called 'Part Number' , some rows have the correct 'Part Number'
whereas others have the 'NULL' value, even though the Part Number is not null.
This happens for a few columns.
I already tried converting problem column formatting to text or numeric, but
it didn't make any difference.
Does someone have any idea why it must be happening and how I can import all
data.
Thank you in advance,
-Me
http://www.sqldts.com/default.aspx?254
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
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B059ABB1-DF2B-44AE-BAC8-52343F72EDB7@.microsoft.com...
>I imported xl spreadsheet data into sql server. Some data is converted with
> <NULL> value even though the data exists in the xl spreadsheet column.
> The funny part is it is not <NULL> for the entire column. For eg. I have a
> column called 'Part Number' , some rows have the correct 'Part Number'
> whereas others have the 'NULL' value, even though the Part Number is not
> null.
> This happens for a few columns.
> I already tried converting problem column formatting to text or numeric,
> but
> it didn't make any difference.
> Does someone have any idea why it must be happening and how I can import
> all
> data.
> Thank you in advance,
> -Me
>

Need Urgent Help!

I imported xl spreadsheet data into sql server. Some data is converted with
<NULL> value even though the data exists in the xl spreadsheet column.
The funny part is it is not <NULL> for the entire column. For eg. I have a
column called 'Part Number' , some rows have the correct 'Part Number'
whereas others have the 'NULL' value, even though the Part Number is not nul
l.
This happens for a few columns.
I already tried converting problem column formatting to text or numeric, but
it didn't make any difference.
Does someone have any idea why it must be happening and how I can import all
data.
Thank you in advance,
-Mehttp://www.sqldts.com/default.aspx?254
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
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B059ABB1-DF2B-44AE-BAC8-52343F72EDB7@.microsoft.com...
>I imported xl spreadsheet data into sql server. Some data is converted with
> <NULL> value even though the data exists in the xl spreadsheet column.
> The funny part is it is not <NULL> for the entire column. For eg. I have a
> column called 'Part Number' , some rows have the correct 'Part Number'
> whereas others have the 'NULL' value, even though the Part Number is not
> null.
> This happens for a few columns.
> I already tried converting problem column formatting to text or numeric,
> but
> it didn't make any difference.
> Does someone have any idea why it must be happening and how I can import
> all
> data.
> Thank you in advance,
> -Me
>

Need Urgent Help!

I imported xl spreadsheet data into sql server. Some data is converted with
<NULL> value even though the data exists in the xl spreadsheet column.
The funny part is it is not <NULL> for the entire column. For eg. I have a
column called 'Part Number' , some rows have the correct 'Part Number'
whereas others have the 'NULL' value, even though the Part Number is not null.
This happens for a few columns.
I already tried converting problem column formatting to text or numeric, but
it didn't make any difference.
Does someone have any idea why it must be happening and how I can import all
data.
Thank you in advance,
-Mehttp://www.sqldts.com/default.aspx?254
--
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
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B059ABB1-DF2B-44AE-BAC8-52343F72EDB7@.microsoft.com...
>I imported xl spreadsheet data into sql server. Some data is converted with
> <NULL> value even though the data exists in the xl spreadsheet column.
> The funny part is it is not <NULL> for the entire column. For eg. I have a
> column called 'Part Number' , some rows have the correct 'Part Number'
> whereas others have the 'NULL' value, even though the Part Number is not
> null.
> This happens for a few columns.
> I already tried converting problem column formatting to text or numeric,
> but
> it didn't make any difference.
> Does someone have any idea why it must be happening and how I can import
> all
> data.
> Thank you in advance,
> -Me
>

need urgent help with sa account...

background sql2k on win2k3
i started getting this error msg when accessing properties page of 'sa'
and 'buildin/administrators' accounts in sql.
Error 21776 [SQL-DMO]the name 'dbo' was not found in the Users
Collection. If the name is qualified name,
use [] to separate various parts of the name, and try again.
i previously disabled the sa right for 'buildin/administrators' and now
i can't add the sa rights back to the group because of this err. i
can't modifiy the 'sa' account for the same reason. i tried to delete
'buildin/administrators' group and add it back but now i can't add it
back either.
1. how to add back the 'buildin/administrators' group?
2. how to fix this error? it doesn't happen to other non-sa accounts.
thank you!!What commadn where you executing to add the security group to sql server
again, Where you able to connect anyway (so did you turned on mixed auth. ?)
Jens Suessmeyer.
"=== Steve L ===" <steve.lin@.powells.com> schrieb im Newsbeitrag
news:1114720021.174631.14500@.g14g2000cwa.googlegroups.com...
> background sql2k on win2k3
> i started getting this error msg when accessing properties page of 'sa'
> and 'buildin/administrators' accounts in sql.
> Error 21776 [SQL-DMO]the name 'dbo' was not found in the Users
> Collection. If the name is qualified name,
> use [] to separate various parts of the name, and try again.
> i previously disabled the sa right for 'buildin/administrators' and now
> i can't add the sa rights back to the group because of this err. i
> can't modifiy the 'sa' account for the same reason. i tried to delete
> 'buildin/administrators' group and add it back but now i can't add it
> back either.
> 1. how to add back the 'buildin/administrators' group?
> 2. how to fix this error? it doesn't happen to other non-sa accounts.
> thank you!!
>|||> 1. how to add back the 'buildin/administrators' group?
EXEC sp_grantlogin 'BUILTIN\Administrators'
EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sy

min'

> 2. how to fix this error? it doesn't happen to other non-sa accounts.
This error may be caused by an incorrect database owner in one or more of
your databases. You can correct the owner with sp_changedbowner like the
example below. See http://support.microsoft.com/kb/q218172/ for more
information.
USE MyDatabase
EXEC sp_changedbowner ''MyOwnerLogin'
Hope this helps.
Dan Guzman
SQL Server MVP
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1114720021.174631.14500@.g14g2000cwa.googlegroups.com...
> background sql2k on win2k3
> i started getting this error msg when accessing properties page of 'sa'
> and 'buildin/administrators' accounts in sql.
> Error 21776 [SQL-DMO]the name 'dbo' was not found in the Users
> Collection. If the name is qualified name,
> use [] to separate various parts of the name, and try again.
> i previously disabled the sa right for 'buildin/administrators' and now
> i can't add the sa rights back to the group because of this err. i
> can't modifiy the 'sa' account for the same reason. i tried to delete
> 'buildin/administrators' group and add it back but now i can't add it
> back either.
> 1. how to add back the 'buildin/administrators' group?
> 2. how to fix this error? it doesn't happen to other non-sa accounts.
> thank you!!
>|||Dan, Thanks for saving my day!!! simply executed the two commands you
sent me. 'BUILTIN\Administrators' was back and all the problems
mysteriously went away. i did detach like 8 databases from the other
server and attached to this server before this happened. i think that
might be one of the problems. although i'm not sure in general, who
should be the owner of all the databases in SQL Server land. since my
accout has sa right, quite often it showes i'm the owner of a database,
but sometimes it's sa, and sometimes it's the domain account created
for running the service (with sa privileges).|||I usually specify the 'sa' login as the owner of production databases and
make it a practice to change the owner following a database restore or
attach. This works well when only sy

min role members can create
dbo-owned objects. In cases where non-sy

min role members create
dbo-owned objects, you can create a login that is used only for database
ownership.
Hope this helps.
Dan Guzman
SQL Server MVP
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1114788026.433970.131740@.o13g2000cwo.googlegroups.com...
> Dan, Thanks for saving my day!!! simply executed the two commands you
> sent me. 'BUILTIN\Administrators' was back and all the problems
> mysteriously went away. i did detach like 8 databases from the other
> server and attached to this server before this happened. i think that
> might be one of the problems. although i'm not sure in general, who
> should be the owner of all the databases in SQL Server land. since my
> accout has sa right, quite often it showes i'm the owner of a database,
> but sometimes it's sa, and sometimes it's the domain account created
> for running the service (with sa privileges).
>

need urgent help with directory structure...

I accidently changed the directory sub folders orders in the
c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's
for reporting service)
now the reporting service won't start.
can anyone tell me what the sub direcotries should look like under that
directory?
thank you!!!Reporting Services
\LogFiles
\ReportManager
\...
\ReportServer
\...
\RSTempFiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1143834522.169647.108420@.i39g2000cwa.googlegroups.com...
>I accidently changed the directory sub folders orders in the
> c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's
> for reporting service)
> now the reporting service won't start.
> can anyone tell me what the sub direcotries should look like under that
> directory?
> thank you!!!
>

Need urgent help with a db restore

using sql server 2000.
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover the
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...|||Perform a log backup now and restore the latest db backup, and all subsequent tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. More info and options:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...

Need urgent help with a db restore

using sql server 2000.
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover th
e
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:

> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none
.
> We did a full db backup monday night at 7pm, did the weekly trans log back
up
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data w
as
> lost (using Export data) by trying to move a stored procedure (as an objec
t)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover
the
> data that was entered after the 7:30 trans log backup up to 4:40pm the nex
t
> day (Tuesday)?
> Thanks for any and all help.
> Russ...|||Perform a log backup now and restore the latest db backup, and all subsequen
t tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. Mor
e info and options:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none
.
> We did a full db backup monday night at 7pm, did the weekly trans log back
up
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data w
as
> lost (using Export data) by trying to move a stored procedure (as an objec
t)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover
the
> data that was entered after the 7:30 trans log backup up to 4:40pm the nex
t
> day (Tuesday)?
> Thanks for any and all help.
> Russ...

Need urgent help with a db restore

using sql server 2000.
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover the
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...
add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:

> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...
|||Perform a log backup now and restore the latest db backup, and all subsequent tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. More info and options:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...

Need urgent help to sort this out!

I've made this example and it loads a picture into a database. (MsSql )
Take a look at the code, it works just fine however it leaves a process in sleeping mode "avaiting command" in Enterprise manager under "Management/current Activity/Process Info"
Is it supposed to be like this or is it supposed to be reemoved after .net is finished??
Code snip
_______________________________________________________

Dim connAsNew SqlConnection("Data Source = (local);Initial Catalog = " & "test;User ID = NAME; Password=PASSWORD;")

Dim cmdAsNew SqlCommand("Select * from tab_bild", cnn)

Try

conn.Open()

Dim myDatareaderAs SqlDataReader

myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

DoWhile (myDatareader.Read())

Response.ContentType = myDatareader.Item("PersonImageType")

Response.BinaryWrite(myDatareader.Item("PersonImage"))

Loop

conn.Close()

Response.Write("Picture info succesfully retrieved")

Catch SQLexcAs SqlException

Response.Write("Read failed, Reason: " & SQLexc.ToString())

EndTry

EndSub
________________________________________________________________

Please can someone explain this for me or sort this out for me.
All help is welcome even if its only points me too a direction.

Regards
Tombola

You should move yourconn.Close() into a Finally block.
|||Thanks for the reply Morton!
I suspect that you mean something like this.
----------------

Try

conn.Open()

Dim myDatareaderAs SqlDataReader

myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

DoWhile (myDatareader.Read())

Response.ContentType = myDatareader.Item("PersonImageType")

Response.BinaryWrite(myDatareader.Item("PersonImage"))

Loop

Response.Write("Bild info succesfully retrieved")

Catch SQLexcAs SqlException

Response.Write("Read failed, Reason: " & SQLexc.ToString())

Finally

conn.Close()

EndTry

----------------
However it still leaves a sleeping process, but it will eventually time out, and be killed, I suppose.
I dont think this would bee such a good solution tough, what if the server is short off memory and there is a lot of sleeping processes that just waits to be timed out. The system would eventually freeze I think. Both IIS and MsSql harvests memory if I remember right.
Anyway the question would still be, Should the .net SqlClient leave a sleeping process on the server when the SqlClient is finished with all its doings?
Regards
Tombola|||I believe what you are seeing is a phenomenon of connection pooling. This is a good thing.
You should likely add a cmd.Dispose() after the conn.Close(). AndI would also add a conn.Dispose() after the conn.Close() for goodmeasure. It's a good idea to Dispose any object which implementthe IDisposable interface once you are done with it.

Need urgent help on Aggregate Function

Hi,

I need to do something similar to Proclarity in SSAS 2005. In Proclarity i can create a named member by selecting a few members from a dimension. The underlying mdx looks like this

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

Once i select the named member all the calculated measures reflect the result based on the members selected in the aggregate function.

How can I do something similar in SSAS 2005. I dont want a named set and calculated member does not work if i use the plain mdx as above. But i also tried using

just to try to to see teh behaviour but to no avail. after processing the cube and selecting this meausre shows up empty cells.

How can I solve this problem.

thanks in adavance for helping

Could you explain why "calculated member does not work if i use the plain mdx as above" - what dimension/hiererchy did you create the member on, and what results did you get?|||

Thanks for asking this question. I think i made a mistake by leaving the parent hierarchy as default "Measure" and Parent Member as empty. I have modifed the Parent hirerachy to Channel.Channel and Parent Member to Channel.All Channels. I am processing the cube now and would get back to you soon with the results.

Thanks once again

|||

after i put the correct hierarchy and parent member the mdx works just fine.

Thanks a million for your help.

need urgent help in T-SQL

-- TOP MEDIAN
BEGIN
DECLARE @.medvarcnt int
DECLARE @.medianValue float
DECLARE @.medianfield varchar(255)
DECLARE @.SQLSTR Nvarchar(800)
SET @.medianfield = 'Cluster_Top'

CREATE TABLE #medianlist (rid int IDENTITY(1,1), medianval int)

SET @.SQLSTR = ('INSERT #medianlist SELECT ' + @.medianfield + ' AS medianval FROM ' + @.result_table_name + ' ORDER BY

' + @.medianfield + ' DESC')
SET @.SQLSTR = CAST (@.SQLSTR AS NVARCHAR(800))
EXECUTE sp_executesql @.SQLSTR
SET @.medvarcnt = (SELECT COUNT(*) FROM #medianlist)

IF @.medvarcnt % 2 = 0
BEGIN
--even
line17 set @.medianValue = (SELECT SUM(medianval)/2 FROM #medianlist WHERE rid >=(@.medvarcnt/2) and rid <=

(@.medvarcnt/2)+1)
line19 set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))
set @.sql = @.sql + ' Where Testcell = ''' + @.testcell + ''' '
print(@.medianValue) --exec(@.sql)
END
ELSE
BEGIN
--odd
set @.medianValue = (SELECT medianval FROM #medianlist where rid =(@.medvarcnt/2)+1)
set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))
set @.sql = @.sql + ' Where Testcell = ''' + @.testcell + ''' '
print(@.medianValue) --exec(@.sql)
END
DROP TABLE #medianlist

END

[addedon]March 17, 2007, 7:31 pm[/addedon]i'm trying to create a median solution with stored procedure...and from the coding i post above, i encounter the error stating failure to change from varchar to float everytime i execute it...

i suspect its happen on line 17 and 18 ...T-SQL got it as varchar...and when i use it back as variable in line 19 (whereas it suppose to take it as a float value....) thus error occur....

make it simple...@.medianvalue should be an int (let's say 18) in line 17 and 18...but at line 19 ,system still take it as whole sentence in varchar...hope everyone can understand what i try to tell...

anyone expert can provide me with solution? thanx

Try this as a replacement for Line 19:

set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))

..and here's an explanation as to why you should do this:

http://msdn2.microsoft.com/en-us/library/ms190309.aspx

You need to review the code sample that you provided for further occurrences of attempting to append an INT to an NVARCHAR - with a quick glance I spotted one more.

Chris

|||

yes, thanks for your solution, chris...it works perfectly right now.

i should be more observant next time.

|||

there's a problem again after undergone some testing...

i expect @.medianvalue to be equals to 5.50 (should be accurate until 2 decimal) but the result coming out is shown as 5.

is there anything wrong with my coding? i have try CAST the SUM value into FLOAT but it doesnt work...

For more information, data type of the field Top_Median that i'm going to update is FLOAT.

any help is very much appreciated.

|||

Could it be the datatype specified in this line that's causing the problem?
CREATE TABLE #medianlist (rid int IDENTITY(1,1), medianval int)
Chris

|||i've changed it to type float. This solve the problem , thanks again Chris.

Saturday, February 25, 2012

Need Urgent Help...............

Need urgent help.. for SQL Server reporting services installation..

Need URGENT help.

Need URGENT help.

Need URGENT help.

Need urgent help, this is driving me crazy. SSRS 2000

Need Urgent Help!

Need Urgent Help!

Need Urgent Help!

need urgent help with sa account...

need urgent help with directory structure...

Need urgent help with a db restore

Need urgent help with a db restore

Need urgent help with a db restore

Need urgent help to sort this out!

Need urgent help on Aggregate Function

need urgent help in T-SQL

Need to sum columns

Blog Archive

About Me