Parameter is missing a value

Hi Folks,

I'm receiving the "Parameter is missing a value" error message while testing my report.

I have five parameters, two of which are hidden that gets prefilled from the query below. The hidden parameters are DatatechClient and DatatechProduct.

SELECT ClientNameProduct, DatatechClient, DatatechProduct
FROM V_TranslationTable
WHERE (CMRNum = @.Cmr) AND (AcctNum = @.ClientNum)

Three parameters are shown and the report works fine as long as the CMRNum and AcctNum is found in the V_TranslationTable however, the error generates when they are not found. I looked through the other threads in this forum that deals with "Parameter is missing a value" but it didn't appear to be a solution shown.

Thanks in advance for any assistance you give.

I take it that when either CMRNum or AcctNum don't exist, you don't want the report to error out. Use this in your query - the report should run and return nothing, but won't error out:

Where IsNull(CMRNum,'') = @.Cmr AND IsNull(AcctNum,'') = @.ClientNum

|||

Hi Jamvir,

Thanks for responding.

I need to give more clearity. My report consists of six datasets and I'm working with RS2005. @.Cmr and @.ClientNum gets populated by a query in my first two datasets. My two hidden parameters @.DatatechClient and @.DatatechProduct are a part of my third dataset and they are the columns that are coming back blank from the query below.

SELECT ClientNameProduct, DatatechClient, DatatechProduct
FROM V_TranslationTable
WHERE (CMRNum = @.Cmr) AND (AcctNum = @.ClientNum)

Blank parameters for @.DatatechClient and or @.DatatechProduct are being passed to my fifth dataset query below.

SELECT EntityID, PrimaryDirInd, IsOnContract, [5DigitCode], EntityName, Addr1, City, State, Zip, Attn, Phone, ClientID, ProductID
FROM V_EntityDirectories
WHERE ((DirID = @.DirNum) AND (ClientID = @.DatatechClient) AND (ProductID = @.DatatechProduct))
ORDER BY EntityID

Also blank parameters for @.DatatechClient and or @.DatatechProduct are being passed to my sixth dataset query below.

SELECT PkgHdngs.ClientID, PkgHdngs.ProductID, PkgHdngs.DirID, PkgHdngs.HdngSeqNum, PkgHdngs.HdngText, PkgHdngs.HdngCode, PkgHdngs.DirVer, HdngAds.AdUnitID, HdngAds.AdRate
FROM PkgHdngs INNER JOIN
HdngAds ON PkgHdngs.ClientID = HdngAds.ClientID AND PkgHdngs.ProductID = HdngAds.ProductID AND PkgHdngs.DirID = HdngAds.DirID AND PkgHdngs.DirVer = HdngAds.DirVer AND PkgHdngs.HdngSeqNum = HdngAds.HdngSeqNum
WHERE ((PkgHdngs.DirID = @.DirID) AND (PkgHdgns.ClientID = @.DatatechClient) AND (PkgHdgns.ProductID = @.DatatechProduct))
ORDER BY PkgHdngs.ClientID, PkgHdngs.ProductID, PkgHdngs.DirID

Best regards

|||

Have Non queried default values for DatatechClient and DatatechProduct parameters and use the follwing expression for it:

=IIf(Count(Fields!DatatechClient.Value, "DataSet3") > 0, First(Fields!DatatechClient.Value, "DataSet3"), "SomeDefaultValueOrBlank")

and similarly for DatatechProduct

Paramaterised Query to ORACLE database - not all variables bound

Hi

when I run this query against an Oracle 9 datasource I get a message saying 'not all variables bound'

select <some fields> from star
where nr_jahr_star = :Year

my datasource is correct as the same report runs fine if I just put in a fixed year and I have the Oracle client software on both my PC and the RS server

I've tried various combinations with the parameter settings also with no luck.

Thanks

Steven

Year is a reserved word - did you try changing the parameter name?

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!