Regarding Dynamic query...

Hi,

I have basic design question regarding dynamic query,

When we have to build a dynamic query (which has table name also as an input parameter),

->Is it better to write a stored procedure ..?

or

->directly specify the dynamic query and set the command type as text in .NET code...?

I think,since dynamic queries may not have the advantage of precompliation, it may not yield any performance in using SP's in such case..

Please through some light on this,

TIA

I don't think there's any right answer to this question. Personally I'd prefer to build a stored procedure for this because I like all of my database access code to be centrally located. Others will say it's a waste of time and effort to put this type of dynamic code into a stored procedure because it buys you nothing in terms of performance and it litters the database unnecessarily.

This is the type of topic that can generate a lot of debate. We'll see if anyone else bites. :-)

|||Why do you need to pass in a table name? Why does yourapplication have that much knowledge of your database? It soundsas though you have a serious architectural problem. You'vetightly coupled your application to your database. Figure out howto DECOUPLE the systems so that changing one won't break theother. One of the main benefits of stored procedures isencapsulation; passing in a table name defeats that goal.

sql

Reg:Passing Parameter to Trigger

Hello
Is there any possibilities to pass parameters to Triggers if so How?.
Siva.No, what is the purpose?
"SivaprakashShanmugam" <SivaprakashShanmugam@.discussions.microsoft.com>
wrote in message news:52258289-70A2-45EE-8752-44A9E58A2F8D@.microsoft.com...
> Hello
> Is there any possibilities to pass parameters to Triggers if so How?.
> Siva.|||I dont think it is possible.
Triggers can use 2 system tables inserted and deleted to see what rows where
affected on the table.
Best regards
Mikael
"SivaprakashShanmugam" wrote:

> Hello
> Is there any possibilities to pass parameters to Triggers if so How?.
> Siva.|||You cannot send data as parameters to triggers, you can, however, create a
global temporary table outside the trigger, insert the data into that table,
then retrieve the data in the trigger as appropriate.
But most importatntly (as the other poster have already asked) - why?
ML
http://milambda.blogspot.com/sql

Reg:Error in PULL (RDA) for data sync

Hi Darren & All,

Thanks! As per your guidance, i have tried in the pull method.Now i am getting error "The LocalTablename parameter is already specified".

Coding as follows:

string rdaOleDbConnectString = @."Provider=SQLOLEDB; Data Source=PRASSANA\PRASANA;Initial Catalog=master;User Id=sa;Password=master";
SqlCeRemoteDataAccess rda = null;
try
{
rda = new SqlCeRemoteDataAccess();
rda.LocalConnectionString = "Data Source=E:\\SampleTest.sdf";
rda.InternetUrl = "http://192.168.1.118/TEst/sqlcesa30.dll";
rda.InternetLogin = "IUSR_PRASSANA";
rda.InternetPassword = "PRASSANA";
rda.Pull("company", "Select * from company", rdaOleDbConnectString, RdaTrackOption.TrackingOn);
MessageBox.Show("Pull Method");
}
catch (SqlCeException)
{
Message.show(ex.message);
}
finally
{
// Dispose of the RDA Object
//
rda.Dispose();
}
}
Please guide me in detail, what mistake i have done in this method.

Thanks ,

Prasanna.

Hope these URLs help!

http://support.microsoft.com/kb/920272

and

http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=1&SiteID=1&PostID=274359

Thanks,

Laxmi

Reg:Error in PULL (RDA) for data sync

Hi Darren & All,

Thanks! As per your guidance, i have tried in the pull method.Now i am getting error "The LocalTablename parameter is already specified".

Coding as follows:

string rdaOleDbConnectString = @."Provider=SQLOLEDB; Data Source=PRASSANA\PRASANA;Initial Catalog=master;User Id=sa;Password=master";
SqlCeRemoteDataAccess rda = null;
try
{
rda = new SqlCeRemoteDataAccess();
rda.LocalConnectionString = "Data Source=E:\\SampleTest.sdf";
rda.InternetUrl = "http://192.168.1.118/TEst/sqlcesa30.dll";
rda.InternetLogin = "IUSR_PRASSANA";
rda.InternetPassword = "PRASSANA";
rda.Pull("company", "Select * from company", rdaOleDbConnectString, RdaTrackOption.TrackingOn);
MessageBox.Show("Pull Method");
}
catch (SqlCeException)
{
Message.show(ex.message);
}
finally
{
// Dispose of the RDA Object
//
rda.Dispose();
}
}
Please guide me in detail, what mistake i have done in this method.

Thanks ,

Prasanna.

Hope these URLs help!

http://support.microsoft.com/kb/920272

and

http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=1&SiteID=1&PostID=274359

Thanks,

Laxmi

Refreshing Parameter values when a higher level Parameter changes

Hi All.

I have a parameter (hidden) that gets its value using an expression base on another parameter.
When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes.

When I change the parent parameter again, the value of the child parameter does not seem to change.

How can I make this parameter change automatically when the parent is changed ?

Any help will be appreciated.

Thanks,
Neil

I know a little about this, mainly because i have spent a lot of time doing the reverse - breaking the linkage so that parameters don't update when another changes.

The linkage between parameters is achieved via the ParameterDefinition nodes under the Dataset/Query/rd:MdxQuery/Query/Statement* node. These can be added, but can be hard to get right (it is easy to take them out, hard to put them in). What can be the easiest thing to do is create a new report with just the parent and child (hidden) parameters, by default the designer will make the child one dependant on the parent parameter, and you can then check the generated rdl to see how it is done.

Hope this helps!

*this is based on a report that uses a cube and MDX, i don't have a SQL one to hand that i can give an example from.

|||Thank you!

well, this was a point into the right direction.. I landed up setting the available values to the parent parameter and then whenever the parent parameter changed, so did this child one...

Thanks for the help!!

Refresh default value

Hello,

I have report parameters B with default from query.

This query depend on other report parameters A that updates befor report parameter B.

While user open the report and update report parameter A, the report parameter B get default value with the right default value but when user change his selection in parameter A, the default value in parameter B doesn't changed

Any idea ?

Smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing.There exist uncommon cases where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, RS currently doesn't have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result, there is no choice but to go with a design that optimizes for the common case. (Note: RS 2000 defaulted to smashing the user’s selection with the default when an upstream parameter value changed. The resulted in a large volume of customer complaints).
One option you may want to explore which may help in this particular case: If the Valid Values list changes (as a result of the upstream parameter value changing) and it no longer contains the user’s selection, we are forced to revert to the default. Careful use of the Valid Values list can in many cases simulate the desired behavior of always overriding the user’s selection back to the default.|||

Sorry but I don't understand your answer.

I have parameter "Date Unit" (Shift, Day, Week, Month, Quarter , Year)

I have parameter "From Date"

I want while user select under "Date Unit" :

Shift or Day the from date will be Today - 7 days

Week the from date will be today - 12 weeks

Month, Quarter the from date will be today - 12 month

Year - the from date will ve today - 2 years.

Any option to do that ?

|||You can trying plugging in an expression for the default value of the From Date cascading parameter that checks Parameters!DateUnit.Value but I think this will only work the first time the report is rendered. Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset. I explained the reasons for this behaviour in my previous post.|||

This is my problem

"Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset"

|||

I know this is an older post but I found it while searching for something else. Would this work for your situation?

Write a stored procedure that takes a parameter where that parameter is the "Date Unit" you're selecting as a VARCHAR. In that sproc, you test that value and then return dynamically the correct date you want as the default for your From Date.

The sproc always returns a single row with a single column called DEFAULT_DATE or something, but what it returns is dynamic based on the parameter input. Then you create a dataset using this sproc and set it's input parameter to your Paarameters!DateUnit and set your default for your From Date to use this dataset's DEFAULT_DATE value.

I think I've done that in the past. Every time the user changes the date unit, the from date will change to the appropriate default.

...I think.

:)

sql

Refresh default value

Hello,

I have report parameters B with default from query.

This query depend on other report parameters A that updates befor report parameter B.

While user open the report and update report parameter A, the report parameter B get default value with the right default value but when user change his selection in parameter A, the default value in parameter B doesn't changed

Any idea ?

Smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing.There exist uncommon cases where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, RS currently doesn't have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result, there is no choice but to go with a design that optimizes for the common case. (Note: RS 2000 defaulted to smashing the user’s selection with the default when an upstream parameter value changed. The resulted in a large volume of customer complaints).
One option you may want to explore which may help in this particular case: If the Valid Values list changes (as a result of the upstream parameter value changing) and it no longer contains the user’s selection, we are forced to revert to the default. Careful use of the Valid Values list can in many cases simulate the desired behavior of always overriding the user’s selection back to the default.|||

Sorry but I don't understand your answer.

I have parameter "Date Unit" (Shift, Day, Week, Month, Quarter , Year)

I have parameter "From Date"

I want while user select under "Date Unit" :

Shift or Day the from date will be Today - 7 days

Week the from date will be today - 12 weeks

Month, Quarter the from date will be today - 12 month

Year - the from date will ve today - 2 years.

Any option to do that ?

|||You can trying plugging in an expression for the default value of the From Date cascading parameter that checks Parameters!DateUnit.Value but I think this will only work the first time the report is rendered. Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset. I explained the reasons for this behaviour in my previous post.|||

This is my problem

"Once the user changes the Date Unit parameter and the report refreshes, the default value of the From Date parameter won't be reset"

|||

I know this is an older post but I found it while searching for something else. Would this work for your situation?

Write a stored procedure that takes a parameter where that parameter is the "Date Unit" you're selecting as a VARCHAR. In that sproc, you test that value and then return dynamically the correct date you want as the default for your From Date.

The sproc always returns a single row with a single column called DEFAULT_DATE or something, but what it returns is dynamic based on the parameter input. Then you create a dataset using this sproc and set it's input parameter to your Paarameters!DateUnit and set your default for your From Date to use this dataset's DEFAULT_DATE value.

I think I've done that in the past. Every time the user changes the date unit, the from date will change to the appropriate default.

...I think.

:)

referencing report parameters in code

I'm trying to reference a report parameter in the code window:
Dim CompId As Integer
CompId = Parameters!CompanyId.Value
It gives me the error:
[BC30469] Reference to a non-shared member requires an object reference.
Any ideas (or alternatives) on how to resolve this? Thank you.TechnoSpyke,
The syntax to reference a report parameter has to be done by instantiating a
Reporting Object
For example:
Dim strParamName as string = â'ThisAStringLiteralâ'
Dim strParamValue as string = â'ThisAStringLiteralâ'
Dim Parameters(0) As Reporting.ParameterValue
Parameters(0) = New Reporting.ParameterValue
Parameters(0).Name = strParamName
Parameters(0).Value = strUSerID
For a better documentation, reference the SOAP API call in BOL
rwiethorn
"TechnoSpyke" wrote:
> I'm trying to reference a report parameter in the code window:
> Dim CompId As Integer
> CompId = Parameters!CompanyId.Value
> It gives me the error:
> [BC30469] Reference to a non-shared member requires an object reference.
> Any ideas (or alternatives) on how to resolve this? Thank you.
>
>|||Thanks for the reply, although I still have some problems.
What I wanted was to set a variable based on a report parameter (assuming
that this parameter has already a value), from within the report (code
window). The code below was taken from the BOL (Initializing Custom
Assembly Objects).
<Code>
Dim m_myClass As MyClass
Protected Overrides Sub OnInit()
m_myClass = new MyClass(User!Language, Paramters!Territory)
End Sub
</Code>
This is very similar to what I'm trying to do, yet I get a "[BC30469]
Reference to a non-shared member requires an object reference." everytime I
reference the Parameters.
"rwiethorn" <rwiethorn@.discussions.microsoft.com> wrote in message
news:BA485D54-C3F9-453A-8883-CADAD78570B5@.microsoft.com...
> TechnoSpyke,
> The syntax to reference a report parameter has to be done by instantiating
> a
> Reporting Object
> For example:
> Dim strParamName as string = "ThisAStringLiteral"
> Dim strParamValue as string = "ThisAStringLiteral"
> Dim Parameters(0) As Reporting.ParameterValue
> Parameters(0) = New Reporting.ParameterValue
> Parameters(0).Name = strParamName
> Parameters(0).Value = strUSerID
> For a better documentation, reference the SOAP API call in BOL
> rwiethorn
> "TechnoSpyke" wrote:
>> I'm trying to reference a report parameter in the code window:
>> Dim CompId As Integer
>> CompId = Parameters!CompanyId.Value
>> It gives me the error:
>> [BC30469] Reference to a non-shared member requires an object reference.
>> Any ideas (or alternatives) on how to resolve this? Thank you.
>>|||TechnoSpyke,
Were are you trying to get the value?
-in a custom assembly?
-in the code page of the report?
-in the expression builder of a control on a report?
If you're in a report, the report knows the name of the parameter. You
reference the param by name. So try to assign the value into a variable by
name.
I've not done that, but read about it. Search this newsgroup for params and
search BOL for param usage.
"TechnoSpyke" wrote:
> Thanks for the reply, although I still have some problems.
> What I wanted was to set a variable based on a report parameter (assuming
> that this parameter has already a value), from within the report (code
> window). The code below was taken from the BOL (Initializing Custom
> Assembly Objects).
> <Code>
> Dim m_myClass As MyClass
> Protected Overrides Sub OnInit()
> m_myClass = new MyClass(User!Language, Paramters!Territory)
> End Sub
> </Code>
> This is very similar to what I'm trying to do, yet I get a "[BC30469]
> Reference to a non-shared member requires an object reference." everytime I
> reference the Parameters.
>
> "rwiethorn" <rwiethorn@.discussions.microsoft.com> wrote in message
> news:BA485D54-C3F9-453A-8883-CADAD78570B5@.microsoft.com...
> > TechnoSpyke,
> > The syntax to reference a report parameter has to be done by instantiating
> > a
> > Reporting Object
> >
> > For example:
> > Dim strParamName as string = "ThisAStringLiteral"
> > Dim strParamValue as string = "ThisAStringLiteral"
> > Dim Parameters(0) As Reporting.ParameterValue
> >
> > Parameters(0) = New Reporting.ParameterValue
> > Parameters(0).Name = strParamName
> > Parameters(0).Value = strUSerID
> >
> > For a better documentation, reference the SOAP API call in BOL
> >
> > rwiethorn
> >
> > "TechnoSpyke" wrote:
> >
> >> I'm trying to reference a report parameter in the code window:
> >>
> >> Dim CompId As Integer
> >> CompId = Parameters!CompanyId.Value
> >>
> >> It gives me the error:
> >> [BC30469] Reference to a non-shared member requires an object reference.
> >>
> >> Any ideas (or alternatives) on how to resolve this? Thank you.
> >>
> >>
> >>
>
>|||I am trying to get the parameter value from the code page of the report. My
code looks like this (modified for this purpose):
Protected Overrides Sub OnInit()
Dim compId As Integer
'following code returns error [BC30469]
compId = Parameters!CompanyId
'following code still returns error [BC30469]
compId = Parameters!CompanyId.Value
'following code doesn't return an error, although not sure if I have the
correct value
compId = Report.Parameters("CompanyId").Value
End Sub
"rwiethorn" <rwiethorn@.discussions.microsoft.com> wrote in message
news:A5C1CEDC-1751-48FA-936A-ADEF01259F22@.microsoft.com...
> TechnoSpyke,
> Were are you trying to get the value?
> -in a custom assembly?
> -in the code page of the report?
> -in the expression builder of a control on a report?
> If you're in a report, the report knows the name of the parameter. You
> reference the param by name. So try to assign the value into a variable by
> name.
> I've not done that, but read about it. Search this newsgroup for params
> and
> search BOL for param usage.
> "TechnoSpyke" wrote:
>> Thanks for the reply, although I still have some problems.
>> What I wanted was to set a variable based on a report parameter (assuming
>> that this parameter has already a value), from within the report (code
>> window). The code below was taken from the BOL (Initializing Custom
>> Assembly Objects).
>> <Code>
>> Dim m_myClass As MyClass
>> Protected Overrides Sub OnInit()
>> m_myClass = new MyClass(User!Language, Paramters!Territory)
>> End Sub
>> </Code>
>> This is very similar to what I'm trying to do, yet I get a "[BC30469]
>> Reference to a non-shared member requires an object reference." everytime
>> I
>> reference the Parameters.
>>
>> "rwiethorn" <rwiethorn@.discussions.microsoft.com> wrote in message
>> news:BA485D54-C3F9-453A-8883-CADAD78570B5@.microsoft.com...
>> > TechnoSpyke,
>> > The syntax to reference a report parameter has to be done by
>> > instantiating
>> > a
>> > Reporting Object
>> >
>> > For example:
>> > Dim strParamName as string = "ThisAStringLiteral"
>> > Dim strParamValue as string = "ThisAStringLiteral"
>> > Dim Parameters(0) As Reporting.ParameterValue
>> >
>> > Parameters(0) = New Reporting.ParameterValue
>> > Parameters(0).Name = strParamName
>> > Parameters(0).Value = strUSerID
>> >
>> > For a better documentation, reference the SOAP API call in BOL
>> >
>> > rwiethorn
>> >
>> > "TechnoSpyke" wrote:
>> >
>> >> I'm trying to reference a report parameter in the code window:
>> >>
>> >> Dim CompId As Integer
>> >> CompId = Parameters!CompanyId.Value
>> >>
>> >> It gives me the error:
>> >> [BC30469] Reference to a non-shared member requires an object
>> >> reference.
>> >>
>> >> Any ideas (or alternatives) on how to resolve this? Thank you.
>> >>
>> >>
>> >>
>>

Referencing Parameters in Report Code

I'm trying to refer to a report parameter in my Code block from the Report
Properties page. But it keeps giving me an error that says, "Reference to a
non-shared member requires an object reference." I'm refering the parameter
as Parameters!pBusiness.Label.
Any ideas about how I can see my parameters within the Code block of the
Report itself?you must pass the values in ie
code.dosomething(Param1!value, Param2.value)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"SteveCole63" <SteveCole63@.discussions.microsoft.com> wrote in message
news:C23B3F0A-A341-47AD-A678-6A6DFD9EE114@.microsoft.com...
> I'm trying to refer to a report parameter in my Code block from the Report
> Properties page. But it keeps giving me an error that says, "Reference to
a
> non-shared member requires an object reference." I'm refering the
parameter
> as Parameters!pBusiness.Label.
> Any ideas about how I can see my parameters within the Code block of the
> Report itself?|||Actually Wayne, you can refer to parameters (I discovered after I posted) as
Report.Parameters!pBusiness.Label.
Thanks for the reply!
"Wayne Snyder" wrote:
> you must pass the values in ie
> code.dosomething(Param1!value, Param2.value)
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "SteveCole63" <SteveCole63@.discussions.microsoft.com> wrote in message
> news:C23B3F0A-A341-47AD-A678-6A6DFD9EE114@.microsoft.com...
> > I'm trying to refer to a report parameter in my Code block from the Report
> > Properties page. But it keeps giving me an error that says, "Reference to
> a
> > non-shared member requires an object reference." I'm refering the
> parameter
> > as Parameters!pBusiness.Label.
> >
> > Any ideas about how I can see my parameters within the Code block of the
> > Report itself?
>
>

referencing fields in code

I'd like the user to decide how to group the report. For this I have the
parameter KStar and a piece of custom code.
Under 'Edit group' I use the expression:
=Code.GetGroups(Parameters!KStar.Value )
In the code window I have a function that starts like this:
Public Shared Function GetGroups (Byref KStar As integer)
Select (KStar)
Case 1
Return(Fields!K1.Value)
Case 2
Return(Fields!K2.Value)
It gives me the error:
[BC30469] Reference to a non-shared member requires an object reference.
How do I solve this? I am obviously very new to Reporting Services and I
would appreciate any help.
Thanks!First try replacing Fields!KStar.Value by Report.Fields!KStar.Value, if that
does not work you will have to pass the field values in as a parameter
--
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
"maple" <maple@.discussions.microsoft.com> wrote in message
news:E87A2353-646F-4FA9-AA2D-367BCB91348D@.microsoft.com...
> I'd like the user to decide how to group the report. For this I have the
> parameter KStar and a piece of custom code.
> Under 'Edit group' I use the expression:
> =Code.GetGroups(Parameters!KStar.Value )
> In the code window I have a function that starts like this:
> Public Shared Function GetGroups (Byref KStar As integer)
> Select (KStar)
> Case 1
> Return(Fields!K1.Value)
> Case 2
> Return(Fields!K2.Value)
> It gives me the error:
> [BC30469] Reference to a non-shared member requires an object reference.
> How do I solve this? I am obviously very new to Reporting Services and I
> would appreciate any help.
> Thanks!
>