Incorrect values in RestoreHistory table

We have a SQL Server which is setting the wrong recovery bit in the table
msdb..restorehistory
i.e.
when databases restored WITH RECOVERY the recovery field has a value of 0
when databases restored WITH NORECOVERY the recovery field has a value of 1
I cannot find out why this is happening. Please assist
ThanksThe BOL is incorrect, 0 means Recovery, and 1 NORECOVERY.
>--Original Message--
>We have a SQL Server which is setting the wrong recovery
bit in the table
>msdb..restorehistory
>i.e.
>when databases restored WITH RECOVERY the recovery field
has a value of 0
>when databases restored WITH NORECOVERY the recovery
field has a value of 1
>I cannot find out why this is happening. Please assist
>Thanks
>
>.
>|||Thanks,
I thought I had verified this with the other servers, but
after double checking with the scritped test below, I
notice that you are correct
Is there any way to send feedbacks to Microsoft about
this, as I frequently find such things
/************Test RestoreHistory Entries******************/
create database test
backup database test to disk = '%temp%\t'
restore database test from disk = 't'
restore database test from disk = 't' with norecovery
restore database test from disk = 't' with recovery
select * from msdb..restorehistory where
destination_database_name = 'test' order by restore_date
drop database test
declare @.bdir varchar(255)
exec
master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
\MSSQLServer\MSSQLServer',
'BackupDirectory', @.bdir OUTPUT
set @.bdir = 'del "'+@.bdir+'\t"'
exec master..xp_cmdshell @.bdir
/*********************************************************/
>--Original Message--
>The BOL is incorrect, 0 means Recovery, and 1 NORECOVERY.
>>--Original Message--
>>We have a SQL Server which is setting the wrong recovery
>bit in the table
>>msdb..restorehistory
>>i.e.
>>when databases restored WITH RECOVERY the recovery field
>has a value of 0
>>when databases restored WITH NORECOVERY the recovery
>field has a value of 1
>>I cannot find out why this is happening. Please assist
>>Thanks
>>
>>.
>.
>|||Mike,
> Is there any way to send feedbacks to Microsoft about
> this, as I frequently find such things
Yes, there's a feedback option in Books Online. Top left of the right pane.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:0ea801c3b2d4$cbe095d0$a001280a@.phx.gbl...
> Thanks,
> I thought I had verified this with the other servers, but
> after double checking with the scritped test below, I
> notice that you are correct
> Is there any way to send feedbacks to Microsoft about
> this, as I frequently find such things
> /************Test RestoreHistory Entries******************/
> create database test
> backup database test to disk = '%temp%\t'
> restore database test from disk = 't'
> restore database test from disk = 't' with norecovery
> restore database test from disk = 't' with recovery
> select * from msdb..restorehistory where
> destination_database_name = 'test' order by restore_date
> drop database test
> declare @.bdir varchar(255)
> exec
> master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
> \MSSQLServer\MSSQLServer',
> 'BackupDirectory', @.bdir OUTPUT
> set @.bdir = 'del "'+@.bdir+'\t"'
> exec master..xp_cmdshell @.bdir
> /*********************************************************/
>
> >--Original Message--
> >The BOL is incorrect, 0 means Recovery, and 1 NORECOVERY.
> >>--Original Message--
> >>We have a SQL Server which is setting the wrong recovery
> >bit in the table
> >>msdb..restorehistory
> >>
> >>i.e.
> >>when databases restored WITH RECOVERY the recovery field
> >has a value of 0
> >>when databases restored WITH NORECOVERY the recovery
> >field has a value of 1
> >>
> >>I cannot find out why this is happening. Please assist
> >>
> >>Thanks
> >>
> >>
> >>.
> >>
> >.
> >sql

Incorrect syntax with Declare

Good morning
I hope this will be a simple one, but I am trying to write a view to query my SQL server database, setting a variable using "declare". The code runs fine and returns a number of records, but when I try to save it, it comes up with "Incorrect syntax near the keyword DECLARE", and will not save. I am a bit of a novice when it comes to SQL, but I don't understand why it runs, but won't save. Here is my variable:
DECLARE @.ANCHORDATE AS DATETIME
SET @.ANCHORDATE = CASE WHEN datepart(mm, getdate()) BETWEEN 4 AND 9 THEN dateadd(mm, 3, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) ELSE dateadd(mm, - 9,
DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) END

Thanks for any help.I am not 100% sure but you can try not to use DECLARE and try and save it. Let me know if it helps.|||I have replaced all the instances of the variable with the actual definition, and the view runs and saves fine. I was hoping to be able to keep it simple by not having to type in the variable definition each time. I don't understand why it can run, but saving it returns an error.

Inconsistent behaviour when setting up parameters

Hi guys,

i am having an issue setting up two parameters in the report designer (SSRS 2005). The report is calling a stored proc, these two parameters are optional, when the user decides to run a report i don't want them to input anything for these parameters, they should both be set to null when calling the stored proc (changing the signature of the sproc is not an option).

So, in the Report Parameter dialog, i set both parameters to "Allow null value", "Internal", and default value of null. This is when the first issue strikes: if i "OK" out of the dialog, then save, then go back into the dialog, the designer has "forgotten" that one of the params was internal (the check box is not checked) - but the other one is still okay. How can i prevent the designer forgetting things like this?

The second problem is that even though (at least one of) the parameters is set to internal, when using IE to connect to the report server and run the report the input fields for those params still show up - i don't want the user seeing them. These param input fields are both hidden if i run the report through the reporting services control (ie just right click on the report in the solution explorer and select "run"). Why the inconsistent behaviour? How can i prevent the user seeing those fields when using IE? (if i just set them to hidden then i get an error message "

The 'poolList' parameter is missing a value" when i try to run the report).

The rdl xml for these two paramaters is:

<ReportParameter Name="poolList">

<DataType>String</DataType>

<Nullable>true</Nullable>

</ReportParameter>

<ReportParameter Name="poolGroupList">

<DataType>String</DataType>

<Nullable>true</Nullable>

</ReportParameter>

Nowhere in there do i see that the parameter is internal - where does the designer stick that sort of information?

Thanks for any answers!

sluggy

A report parameter being internal means that it does not have a prompt. Hence, in the underlying RDL file, there is no <Prompt> element under the <ReportParameter> element. So, from the RDL snippet shown in your posting everything looks correct.

Regarding report server: I recommend that you delete the already published report from the report server before republishing the report with updated parameter information. Otherwise, the old parameter settings and the new parameter settings may get merged (because the administrator on the report server could have decided to change the default value etc. for the published report).

-- Robert

|||

Hi Robert,

thanks for the heads-up regarding the merging of the reports, i will watch out for it when this project goes live as we won't have control over that report server.

I have fixed one of my problems. To get rid of the "The 'blah' parameter is missing a value" message all i had to do was remove those parameters in the parameters tab of the configure dataset dialog. As they are named params and they are assigned default values in the sproc i didn't even need to try to pass them. This also means i don't have those input fields showing up in IE but not the control.

Although this still doesn't explain why the designer was persistently forgetting settings :)

sluggy

IncludeLink and RenderFormat

We are providing customers the ability to create subscriptions to reports. We had planned on setting the IncludeReport extension to true and letting customers select the render format. However, we determined that due to email size limits, we can no longer go that route. Instead, I will need to set IncludeReport = false and IncludeLink = true. I saw that the link in the email takes the user to ReportServer, where they then have to select their export format.

Is there any way around this behavior? Ideally what I'd like to have happen is that the user receives an email with a link to their report, and when they click on that link they get a file in the format described in RenderFormat. I provide the user with my own UI when they view the report/add subscriptions on our site, so to have them go to a new UI (ReportServer) after clicking the link in the email seems like it would be confusing to them.

In case this helps anyone, thought I'd post my solution:

Instead of using MSRS's generated link, I have set both IncludeReport and IncludeLink to false. I then generate my own link and it to the comments section of the email using ExtensionSettings. When the user clicks on the link, they are taken to an aspx page where I export the file programmatically (see example code from msdn: http://support.microsoft.com/?kbid=875447) using the format specified in the subscription properties.