parameter problem

hi all,
i have a parameter ParamA that refers to another parameter ParamB.
select ParamA from property where chain like (@.ParamB+'%')
i set ParamB to allow blank value.
how do i enable ParamA if ParamB is blank? cos currently, ParamA will
be disabled for selection if @.ParamB is not specified. please help.
thanks!There is an allow null and a allow blanks propertu for parameters in
reporting services.
"minority" wrote:
> hi all,
> i have a parameter ParamA that refers to another parameter ParamB.
> select ParamA from property where chain like (@.ParamB+'%')
> i set ParamB to allow blank value.
> how do i enable ParamA if ParamB is blank? cos currently, ParamA will
> be disabled for selection if @.ParamB is not specified. please help.
> thanks!
>|||hi Jim, thanks for your reply.
there'll be an error if i set the parameter to allow null because the
available values are selected to be 'From Query'.|||both parameters are also multi-valued

Parameter Mapping format string

Hi,

I have used Data Flow component that refers to named query of data source view. It is a OLE DB source.

The SQL Command property of data flow component shows SELECT * FROM Tablename as I defiend in named query. I have modified the query to accept a parameter as SELECT * FROM Tablename WHERE Status = ?

Now I need to pass the package variable to this parameter. How to I pass using ParameterMapping property?. What is correct way of passing the parameter mapping ? I tried with @.[TestNS:tatus] variable. But it throws error The parameter mapping string is not in the correct format.

Thanks in advance

Hello Prabha!

The best way to set parameter mappings on your OLE DB source is by using the "Parameters" button in the edit dialog for the source component. Once you've clicked that, a pop-up will appear with a grid where you can select variables you want to map to parameters of your SQL statement. If you have problems with this approach, feel free to post a follow up and one of us will be sure to help you from there.

Thanks, -David

|||

Hi David,

I agree that the suggested approch will help in configuing parameterized SQL Command. But my scenario is to use named query as source.

I do not get the 'Parameters' button as I have selected named query option in OLEDB source. However, I am allowed to change the SQL command property to have the parameter place holder '?' under Properties window after configuring the OLEDB connection Manager.

There is also one more property called 'ParameterMapping' under custom properties of OLEDB source. I do not find any reference on the format of the parameter mapping. Can you forward any reference on this ?

Thanks,

Prabha

|||

Sorry, Prabha, I sort of missed that you're using a DSV named query.

Officially, Named Queries don't support parameters, but as you found, there are ways to manually change the SQL in your DSV, such as by changing the xml directly. I wouldn't recommend that because other things using these DSVs are likely to break.

That having been said, I was able to convince the OLE DB source to bind in variables to the parameters I added manually to the Named Query's SQL, by setting the ParameterMapping manually. The format is:

"<param-name>","<variable-guid>";...

But, instead of constructing it manually, which seems kind of error prone, try this:

1) In the OLE DB Source editor, copy the SQL for your named query to the clipboard.

2) Switch to "SQL Command" mode for your OLE DB Source.

3) Paste the query from the clipboard to the SQL editor.

4) Click "Parameters...".

5) For each parameter, choose a variable.

6) Close the parameters screen, close the OLE DB source editor.

7) Find the "ParameterMapping" field in the Properties editor, copy that to the clipboard.

8) Re-open the OLE DB source editor, switch back to the Named Query.

9) Close the OLE DB source editor

10) Paste the ParameterMapping value from the clipboard back into the properties window

A lot of steps, but I think it's better than trying to hand-assemble the thing. Fair warning though, this isn't supported, so it could stop working at any time. From what I know, DSVs don't officially support parameters, so the safe bet would be to switch to SQL Command mode for this.

-David

|||

Thank you so much David, The given work around works well.

- Prabha

Parameter label property is null when passed to subreports

I have a main report that passes a parameter(pTime = Value: "2004" Label:
"Fiscal 2004") to a sub-report. When I print out Parameters!pTime.Label on
the main report it diplays fine. When I print it out on the subreport it
shows a null value. It seems this parameter label property is not passed
along to the subreport.
Is this bug fixed? If not, does anyone know of a work around. I do not
want to have to look up the label in all my subreports.
FYI: The Parameters!pTime.Value property is passed to the subreport fine.
Thanks,
Dan
--
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
dan_zaccarine@.hanfordbay.com
http://www.hanfordbay.com
___________________________Hi Dan,
Thanks for your posting!
I am trying to reproduce it on my side, I will keep you updated when I find
there is anything valueable to add.
Thank you for your patience and corporation.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Dan,
I am afraid that it is by design. An available values list contains a set
of value/label pairs. When the report is run, the user sees the label. When
the user selects a label, the corresponding value is used as the parameter
value .
However, when parameter value is transferred to subreport, it is not
neceesary to manully choose lable and the parameter value from parent
report is transferred to subreport automatically.
I am not sure why you would like to see label of a parameter in subreport?
Is it possible for you to find a workaround in your project?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||> I am not sure why you would like to see label of a parameter in subreport?
I have to display the prompt descriptions(Labels) on the subreports detail
section. I am querying against an AS cube with MDX to populate parameter
lists.
I the use the Value to MDX query cube data in the subreport.
The values are returned in a format similar to this: "[Gender].[Male]"
with a Label of "Male"
I need to display "Male" on the subreport.
> Is it possible for you to find a workaround in your project?
Yes, I have found a method to extract the text I need from the Value using
expressions.
Thanks,
Dan
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
___________________________
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:%23duw3RdAFHA.764@.cpmsftngxa10.phx.gbl...
> Hi Dan,
> I am afraid that it is by design. An available values list contains a set
> of value/label pairs. When the report is run, the user sees the label.
When
> the user selects a label, the corresponding value is used as the parameter
> value .
> However, when parameter value is transferred to subreport, it is not
> neceesary to manully choose lable and the parameter value from parent
> report is transferred to subreport automatically.
> I am not sure why you would like to see label of a parameter in subreport?
> Is it possible for you to find a workaround in your project?
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

PageAudit Property Incorrect redux - with a twist

I am getting this message: "The header for file myfile.mdf is not a valid
database file header. The PageauditProperty is incorrect." However, this is
happening on a full overwrite restore! That is, the Restore goes through
successfully, but then I get the message when the Restore attempts to start
the database.
I created a fresh backup and the same thing happens! If the database was
corrupt, I would have thought the backup process would fail, not the
restore.
Any clues as what to look at?
Thanks for any tips.> If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
BACKUP does not validate database integrity. Run DBCC CHECKDB on your
source database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W." <neilw@.netlib.com> wrote in message
news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I am getting this message: "The header for file myfile.mdf is not a valid
> database file header. The PageauditProperty is incorrect." However, this
> is
> happening on a full overwrite restore! That is, the Restore goes through
> successfully, but then I get the message when the Restore attempts to
> start
> the database.
> I created a fresh backup and the same thing happens! If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
> Any clues as what to look at?
> Thanks for any tips.
>|||Thanks for the suggestion. I was afraid that running CHECKDB would send the
main database into suspect mode if there was some header corruption. Is
that possible? So far it is up and running and I am afraid to do the checkdb
without a good backup.
_______________________________

> If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
BACKUP does not validate database integrity. Run DBCC CHECKDB on your
source database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W." <neilw@.netlib.com> wrote in message
news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I am getting this message: "The header for file myfile.mdf is not a valid
> database file header. The PageauditProperty is incorrect." However, this
> is
> happening on a full overwrite restore! That is, the Restore goes through
> successfully, but then I get the message when the Restore attempts to
> start
> the database.
> I created a fresh backup and the same thing happens! If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
> Any clues as what to look at?
> Thanks for any tips.
>|||Hi
Backup will backup the corruption, it it exists. That is as good a "good
backup" as you can get.
Look at DTS'ing the data out into another DB as a safety measure.
Regards
Mike
"Neil W." wrote:

> Thanks for the suggestion. I was afraid that running CHECKDB would send th
e
> main database into suspect mode if there was some header corruption. Is
> that possible? So far it is up and running and I am afraid to do the check
db
> without a good backup.
> _______________________________
>
> BACKUP does not validate database integrity. Run DBCC CHECKDB on your
> source database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neil W." <neilw@.netlib.com> wrote in message
> news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
>
>|||Thanks for the reply. Will the database copy wizard be sufficient, or will
that also copy over the corruption?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
Hi
Backup will backup the corruption, it it exists. That is as good a "good
backup" as you can get.
Look at DTS'ing the data out into another DB as a safety measure.
Regards
Mike
"Neil W." wrote:

> Thanks for the suggestion. I was afraid that running CHECKDB would send
the
> main database into suspect mode if there was some header corruption. Is
> that possible? So far it is up and running and I am afraid to do the
checkdb
> without a good backup.
> _______________________________
>
> BACKUP does not validate database integrity. Run DBCC CHECKDB on your
> source database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neil W." <neilw@.netlib.com> wrote in message
> news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
this[vbcol=seagreen]
through[vbcol=seagreen]
was[vbcol=seagreen]
>
>|||Hi Neil,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue.
Based on my experience, you are encouraged to check your hardware to see
whether there is any data corruption for your HD or RAID Controller. For
example
PRB: SQL Server Backup Performed on Computer Configured with DPT RAID
Controller Card May Be Invalid
http://support.microsoft.com/kb/268481
For your question, DTS won't copy the corrupted data to the destination.
We appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

PageAudit Property Incorrect redux - with a twist

I am getting this message: "The header for file myfile.mdf is not a valid
database file header. The PageauditProperty is incorrect." However, this is
happening on a full overwrite restore! That is, the Restore goes through
successfully, but then I get the message when the Restore attempts to start
the database.
I created a fresh backup and the same thing happens! If the database was
corrupt, I would have thought the backup process would fail, not the
restore.
Any clues as what to look at?
Thanks for any tips.> If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
BACKUP does not validate database integrity. Run DBCC CHECKDB on your
source database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W." <neilw@.netlib.com> wrote in message
news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I am getting this message: "The header for file myfile.mdf is not a valid
> database file header. The PageauditProperty is incorrect." However, this
> is
> happening on a full overwrite restore! That is, the Restore goes through
> successfully, but then I get the message when the Restore attempts to
> start
> the database.
> I created a fresh backup and the same thing happens! If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
> Any clues as what to look at?
> Thanks for any tips.
>|||Thanks for the suggestion. I was afraid that running CHECKDB would send the
main database into suspect mode if there was some header corruption. Is
that possible? So far it is up and running and I am afraid to do the checkdb
without a good backup.
_______________________________
> If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
BACKUP does not validate database integrity. Run DBCC CHECKDB on your
source database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W." <neilw@.netlib.com> wrote in message
news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I am getting this message: "The header for file myfile.mdf is not a valid
> database file header. The PageauditProperty is incorrect." However, this
> is
> happening on a full overwrite restore! That is, the Restore goes through
> successfully, but then I get the message when the Restore attempts to
> start
> the database.
> I created a fresh backup and the same thing happens! If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
> Any clues as what to look at?
> Thanks for any tips.
>|||Hi
Backup will backup the corruption, it it exists. That is as good a "good
backup" as you can get.
Look at DTS'ing the data out into another DB as a safety measure.
Regards
Mike
"Neil W." wrote:
> Thanks for the suggestion. I was afraid that running CHECKDB would send the
> main database into suspect mode if there was some header corruption. Is
> that possible? So far it is up and running and I am afraid to do the checkdb
> without a good backup.
> _______________________________
> > If the database was
> > corrupt, I would have thought the backup process would fail, not the
> > restore.
> BACKUP does not validate database integrity. Run DBCC CHECKDB on your
> source database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neil W." <neilw@.netlib.com> wrote in message
> news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> >I am getting this message: "The header for file myfile.mdf is not a valid
> > database file header. The PageauditProperty is incorrect." However, this
> > is
> > happening on a full overwrite restore! That is, the Restore goes through
> > successfully, but then I get the message when the Restore attempts to
> > start
> > the database.
> >
> > I created a fresh backup and the same thing happens! If the database was
> > corrupt, I would have thought the backup process would fail, not the
> > restore.
> >
> > Any clues as what to look at?
> >
> > Thanks for any tips.
> >
> >
>
>|||Thanks for the reply. Will the database copy wizard be sufficient, or will
that also copy over the corruption?
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
Hi
Backup will backup the corruption, it it exists. That is as good a "good
backup" as you can get.
Look at DTS'ing the data out into another DB as a safety measure.
Regards
Mike
"Neil W." wrote:
> Thanks for the suggestion. I was afraid that running CHECKDB would send
the
> main database into suspect mode if there was some header corruption. Is
> that possible? So far it is up and running and I am afraid to do the
checkdb
> without a good backup.
> _______________________________
> > If the database was
> > corrupt, I would have thought the backup process would fail, not the
> > restore.
> BACKUP does not validate database integrity. Run DBCC CHECKDB on your
> source database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neil W." <neilw@.netlib.com> wrote in message
> news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> >I am getting this message: "The header for file myfile.mdf is not a valid
> > database file header. The PageauditProperty is incorrect." However,
this
> > is
> > happening on a full overwrite restore! That is, the Restore goes
through
> > successfully, but then I get the message when the Restore attempts to
> > start
> > the database.
> >
> > I created a fresh backup and the same thing happens! If the database
was
> > corrupt, I would have thought the backup process would fail, not the
> > restore.
> >
> > Any clues as what to look at?
> >
> > Thanks for any tips.
> >
> >
>
>|||Hi Neil,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue.
Based on my experience, you are encouraged to check your hardware to see
whether there is any data corruption for your HD or RAID Controller. For
example
PRB: SQL Server Backup Performed on Computer Configured with DPT RAID
Controller Card May Be Invalid
http://support.microsoft.com/kb/268481
For your question, DTS won't copy the corrupted data to the destination.
We appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

PageAudit Property Incorrect redux - with a twist

I am getting this message: "The header for file myfile.mdf is not a valid
database file header. The PageauditProperty is incorrect." However, this is
happening on a full overwrite restore! That is, the Restore goes through
successfully, but then I get the message when the Restore attempts to start
the database.
I created a fresh backup and the same thing happens! If the database was
corrupt, I would have thought the backup process would fail, not the
restore.
Any clues as what to look at?
Thanks for any tips.
> If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
BACKUP does not validate database integrity. Run DBCC CHECKDB on your
source database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W." <neilw@.netlib.com> wrote in message
news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I am getting this message: "The header for file myfile.mdf is not a valid
> database file header. The PageauditProperty is incorrect." However, this
> is
> happening on a full overwrite restore! That is, the Restore goes through
> successfully, but then I get the message when the Restore attempts to
> start
> the database.
> I created a fresh backup and the same thing happens! If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
> Any clues as what to look at?
> Thanks for any tips.
>
|||Thanks for the suggestion. I was afraid that running CHECKDB would send the
main database into suspect mode if there was some header corruption. Is
that possible? So far it is up and running and I am afraid to do the checkdb
without a good backup.
_______________________________

> If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
BACKUP does not validate database integrity. Run DBCC CHECKDB on your
source database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W." <neilw@.netlib.com> wrote in message
news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I am getting this message: "The header for file myfile.mdf is not a valid
> database file header. The PageauditProperty is incorrect." However, this
> is
> happening on a full overwrite restore! That is, the Restore goes through
> successfully, but then I get the message when the Restore attempts to
> start
> the database.
> I created a fresh backup and the same thing happens! If the database was
> corrupt, I would have thought the backup process would fail, not the
> restore.
> Any clues as what to look at?
> Thanks for any tips.
>
|||Hi
Backup will backup the corruption, it it exists. That is as good a "good
backup" as you can get.
Look at DTS'ing the data out into another DB as a safety measure.
Regards
Mike
"Neil W." wrote:

> Thanks for the suggestion. I was afraid that running CHECKDB would send the
> main database into suspect mode if there was some header corruption. Is
> that possible? So far it is up and running and I am afraid to do the checkdb
> without a good backup.
> _______________________________
>
> BACKUP does not validate database integrity. Run DBCC CHECKDB on your
> source database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neil W." <neilw@.netlib.com> wrote in message
> news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
>
>
|||Thanks for the reply. Will the database copy wizard be sufficient, or will
that also copy over the corruption?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
Hi
Backup will backup the corruption, it it exists. That is as good a "good
backup" as you can get.
Look at DTS'ing the data out into another DB as a safety measure.
Regards
Mike
"Neil W." wrote:

> Thanks for the suggestion. I was afraid that running CHECKDB would send
the
> main database into suspect mode if there was some header corruption. Is
> that possible? So far it is up and running and I am afraid to do the
checkdb[vbcol=seagreen]
> without a good backup.
> _______________________________
>
> BACKUP does not validate database integrity. Run DBCC CHECKDB on your
> source database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neil W." <neilw@.netlib.com> wrote in message
> news:%23UfZF9L9EHA.1452@.TK2MSFTNGP11.phx.gbl...
this[vbcol=seagreen]
through[vbcol=seagreen]
was
>
>
|||Hi Neil,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue.
Based on my experience, you are encouraged to check your hardware to see
whether there is any data corruption for your HD or RAID Controller. For
example
PRB: SQL Server Backup Performed on Computer Configured with DPT RAID
Controller Card May Be Invalid
http://support.microsoft.com/kb/268481
For your question, DTS won't copy the corrupted data to the destination.
We appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!