Showing posts with label services. Show all posts

Friday, March 30, 2012

Parameter Problems ! ! !

Hi,
I have gone through all the suggestions on this site, but non of them have
solved my problem. I am new to reporting services, and I am facing a problem
and I dont' know whether the url based reporting is going to solve this for
me (i really hope it can).
I have a datagrid with link button which basically passes the parameters to
a report like id, startDate and and an endDate, so basically the report shows
data for that id between the startdate and the endDate. Now the parameters
are passed fine and it shows up on the text boxes in the report, however the
problem is that though I want to use the id as a parameter from the url, I do
not want to give the user a text box to change that value i.e. basically i
want it hidden and at the same time I want it to be able to use the value i
pass in the url... can this be done.. I would really appreciate if someone
could direct me on the right path...I have tried hiding prompts throught the
reporting manager, and using just a "space" in the reporting string, but
there's still text box showing up which I don't want to show... sooo plzzz
some one help me out. Thank you.. in advanceYes. It can be done.
Here is an example Url.
http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>
Replace the values that in brackets that match your requirements.
<ReportDirectory> - use if you've created a folder where your report
resides.
<ReportName> - the name of the report that you want to render.
<Parameter> - the parameter name that you are going to pass a value to the
report.
<ParameterValue> - this is the actual parameter value being passed.
If you have additional parameters you can add them to the end of the Url
using this syntax:
&<ParameterName2>=<ParameterValue2>
http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>&<ParameterName2>=<ParameterValue2>
I hope this helps. I use it all the time in my c# applications.
Yosh
"Safder" <Safder@.discussions.microsoft.com> wrote in message
news:44BA0D0A-53E3-4031-B8A8-43E0BC0BAFA3@.microsoft.com...
> Hi,
> I have gone through all the suggestions on this site, but non of them have
> solved my problem. I am new to reporting services, and I am facing a
> problem
> and I dont' know whether the url based reporting is going to solve this
> for
> me (i really hope it can).
> I have a datagrid with link button which basically passes the parameters
> to
> a report like id, startDate and and an endDate, so basically the report
> shows
> data for that id between the startdate and the endDate. Now the parameters
> are passed fine and it shows up on the text boxes in the report, however
> the
> problem is that though I want to use the id as a parameter from the url, I
> do
> not want to give the user a text box to change that value i.e. basically i
> want it hidden and at the same time I want it to be able to use the value
> i
> pass in the url... can this be done.. I would really appreciate if someone
> could direct me on the right path...I have tried hiding prompts throught
> the
> reporting manager, and using just a "space" in the reporting string, but
> there's still text box showing up which I don't want to show... sooo
> plzzz
> some one help me out. Thank you.. in advance|||Hi,
Thanks for the suggestion, unfortunately this did not work, is there any
pre-reqs to doing this .. i mean do i have to change any properties of the
report before doing this.? if not.. i tried using this same url you have
suggested, but all the text boxes still appear. Also i'de like to specify
that i have 3 parameters out of which i want to show only two. The three
parameters once again are id, startDate and endDate. So I'de like to show the
start and end dates and would like to give the user the option to change
those parameters, however i do not want to give the user the option to change
the id, and hence i want the "id" parameter to be hidden. Please let me know
if i can do this. Thank you.
"Yosh" wrote:
> Yes. It can be done.
> Here is an example Url.
> http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>
> Replace the values that in brackets that match your requirements.
> <ReportDirectory> - use if you've created a folder where your report
> resides.
> <ReportName> - the name of the report that you want to render.
> <Parameter> - the parameter name that you are going to pass a value to the
> report.
> <ParameterValue> - this is the actual parameter value being passed.
> If you have additional parameters you can add them to the end of the Url
> using this syntax:
> &<ParameterName2>=<ParameterValue2>
> http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>&<ParameterName2>=<ParameterValue2>
> I hope this helps. I use it all the time in my c# applications.
> Yosh
>
> "Safder" <Safder@.discussions.microsoft.com> wrote in message
> news:44BA0D0A-53E3-4031-B8A8-43E0BC0BAFA3@.microsoft.com...
> > Hi,
> > I have gone through all the suggestions on this site, but non of them have
> > solved my problem. I am new to reporting services, and I am facing a
> > problem
> > and I dont' know whether the url based reporting is going to solve this
> > for
> > me (i really hope it can).
> > I have a datagrid with link button which basically passes the parameters
> > to
> > a report like id, startDate and and an endDate, so basically the report
> > shows
> > data for that id between the startdate and the endDate. Now the parameters
> > are passed fine and it shows up on the text boxes in the report, however
> > the
> > problem is that though I want to use the id as a parameter from the url, I
> > do
> > not want to give the user a text box to change that value i.e. basically i
> > want it hidden and at the same time I want it to be able to use the value
> > i
> > pass in the url... can this be done.. I would really appreciate if someone
> > could direct me on the right path...I have tried hiding prompts throught
> > the
> > reporting manager, and using just a "space" in the reporting string, but
> > there's still text box showing up which I don't want to show... sooo
> > plzzz
> > some one help me out. Thank you.. in advance
>
>|||In SQL 2005 simply check the hidden property... In SQL 2000 do NOT give the
parameter a label and it should be hidden from the user.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Safder" wrote:
> Hi,
> I have gone through all the suggestions on this site, but non of them have
> solved my problem. I am new to reporting services, and I am facing a problem
> and I dont' know whether the url based reporting is going to solve this for
> me (i really hope it can).
> I have a datagrid with link button which basically passes the parameters to
> a report like id, startDate and and an endDate, so basically the report shows
> data for that id between the startdate and the endDate. Now the parameters
> are passed fine and it shows up on the text boxes in the report, however the
> problem is that though I want to use the id as a parameter from the url, I do
> not want to give the user a text box to change that value i.e. basically i
> want it hidden and at the same time I want it to be able to use the value i
> pass in the url... can this be done.. I would really appreciate if someone
> could direct me on the right path...I have tried hiding prompts throught the
> reporting manager, and using just a "space" in the reporting string, but
> there's still text box showing up which I don't want to show... sooo plzzz
> some one help me out. Thank you.. in advance|||If I'm understanding you, you want to have an ID parameter filled in
programmatically but not available to the user to change. I have the same
requirement. All I do is set the parameter to hidden under Report : Report
Parameters and check the Hidden checkbox.|||That is for RS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bob Fisher" <bob.fisher@.mben.com> wrote in message
news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> If I'm understanding you, you want to have an ID parameter filled in
> programmatically but not available to the user to change. I have the same
> requirement. All I do is set the parameter to hidden under Report :
> Report Parameters and check the Hidden checkbox.
>|||Hi,
Thank you for the suggestion, I tried what you told me to do in SQL Server
2000 i.e. from "Report manager" i checked the "Prompt user" but i didnt give
any "prompt string" , but what happens in that case is only a text box
without a label shows up,and that does not help I am lookin for something
which does not show a label or a text box. Please let me konw if it's
possible. Bob fisher actually summed up pretty well about what i want,
however his suggestion was for 2005, i am lookin for something which would
work for 2000. Thank you
"Wayne Snyder" wrote:
> In SQL 2005 simply check the hidden property... In SQL 2000 do NOT give the
> parameter a label and it should be hidden from the user.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Safder" wrote:
> > Hi,
> > I have gone through all the suggestions on this site, but non of them have
> > solved my problem. I am new to reporting services, and I am facing a problem
> > and I dont' know whether the url based reporting is going to solve this for
> > me (i really hope it can).
> > I have a datagrid with link button which basically passes the parameters to
> > a report like id, startDate and and an endDate, so basically the report shows
> > data for that id between the startdate and the endDate. Now the parameters
> > are passed fine and it shows up on the text boxes in the report, however the
> > problem is that though I want to use the id as a parameter from the url, I do
> > not want to give the user a text box to change that value i.e. basically i
> > want it hidden and at the same time I want it to be able to use the value i
> > pass in the url... can this be done.. I would really appreciate if someone
> > could direct me on the right path...I have tried hiding prompts throught the
> > reporting manager, and using just a "space" in the reporting string, but
> > there's still text box showing up which I don't want to show... sooo plzzz
> > some one help me out. Thank you.. in advance|||how would you do it in Reporting services 2000
"Bruce L-C [MVP]" wrote:
> That is for RS 2005.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bob Fisher" <bob.fisher@.mben.com> wrote in message
> news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> > If I'm understanding you, you want to have an ID parameter filled in
> > programmatically but not available to the user to change. I have the same
> > requirement. All I do is set the parameter to hidden under Report :
> > Report Parameters and check the Hidden checkbox.
> >
> >
>
>|||Hi Safder
if you want your paramaeter to be hidden from user. After deployment to
Portal, you will have to go to report manager and go to you report properties
and go to parameters section and uncheck the prompt user check box
"Safder" wrote:
> how would you do it in Reporting services 2000
> "Bruce L-C [MVP]" wrote:
> > That is for RS 2005.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bob Fisher" <bob.fisher@.mben.com> wrote in message
> > news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> > > If I'm understanding you, you want to have an ID parameter filled in
> > > programmatically but not available to the user to change. I have the same
> > > requirement. All I do is set the parameter to hidden under Report :
> > > Report Parameters and check the Hidden checkbox.
> > >
> > >
> >
> >
> >sql

Parameter problem with OLAP and spaces

Hello all,

I have an issue with parameters. Let me explain.....

I have report1 which uses analysis services as it's data source. The report displays a list of our companies sites along with some costs etc. Site names can include spaces and numbers but they are stored in the relational database as varchar[50]. On the relevant text box of report1 I have defined a data detsination using report2 and passing it the site name field.value.

On report2 I have added the site name field as a filter dimension and set it as a parameter. On the report parameters I have set the parameter as being non queried.

When I run report1 and click a value to "drill down" it only works for values that have no spaces and no numbers. If If convert spaces to underscores I get a little further, but sites that have numbers, for example 15_OxfordStreet still do not work. The error message I get is..

"Parser: The syntax for '_Oxford' is incorrect.

Can anyone suggest anything to help?

Many thanks

Matt

You may need to escape the member names. To avoid similar issues, the SSAS team introduced undocumented function UrlEscapeFragment. If you open the sample AW cube, flip to the Actions tab in the Cube Designer, select the Sales Reason Comparision action, and expand the Parameters section, you will see how this function is used to escape the member name.|||

Teo,

Thanks for your reply. I'm still learning SQL 2005 and although I tried your suggestion I could not get it to work. However I did fix the problem. The issue was that I was passing through the "friendly name" of the value, instead of [data1].[field name].&[0] etc. as the jump to parameter!

Arrrrgggg...so simple when you think about it!

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

sql

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005

Hi All,

Parameter passing in SSIS 2005 sometimes appears to be a cumbursome task. I have been digging into this topic for quite some time and here i note down some simple steps to demonstrate parameter passing at Package level.

(1) Create a SSIS project using Business Intelligence 2005 Or VS 2005.

(2) Create datasource (.ds) and Data Source View as required.

(3) A default SSIS Package by the name Package.dtsx is created. Double click this and you are shown tabs for Control Flow, Data Flow, Event Handlers, Package Explorer. On the Control Flow, drap and drop Execute SQL Task from Control Flow Items in the toolbar.

(4) Lets now create a variable at Package level. Right click anywhere in the control flow box (not on the Task created in Step 3 above). Click on the Variables on the context menu displayed. Variables window appears on the left of the screen. Click the Add Variable box in this window to create a variable. Name it var1 (or whatever you may like), Scope as Package, DataType as String and Value as MyValue. This is only the default value.

(5) Now let us edit the SQL Task created in Step 3. Double on it, on the General tab you can change its Name, Description. Set ResultSet as None. We shall proceed to execute a stored procedure by name MySPName and pass it a parameter. Set ConnectionType as OLE DB. Select the connection you creates in step 2. Set SQLSourceType as Direct input. SQLStatement as MySPName ? . Note the ? mark after the name of the stored procedure. This is important to accept the variable value (var1) created in Step 4.

(6) Select Parameter Mapping tab now. Click on Add button. Select the Variable Name as User::var1. This is the user created variable of Step 4. Select Direction as Input, DataType as Varchar and Parameter Name as @.var1. Click on OK now.

(7) This sets up the basic of parameter passing. Compile the project to verify everything works. Right Click on the SQL Task and select Execute Task. This will execute the package taking default value of the variable. This can be used along with dtexec command with /set option to pass the parameter at command prompt.

Hi Rupesh,

Can we manually pass the parameter (source file name) to SSIS package from .net application?

if yes then what would be approach for this?

Please provide me some pointers on this.

Thanks,

Anshu

|||

Hi Rupesh,

I followed ur steps to pass parameter in SSIS 2005.

I am passing a datetime variable to my stored procedure usp_GenerateCompEvents ? @.as_of_date with the "?" as you had mentioned in ur message in my sqlstatement.

And I also set my resultset to none in the General Tab.

And I mapped the parameter to a user defined variable of data type DBDATE and when I executed the task it gave me an error message

"[Execute SQL Task] Error: Executing the query "usp_GenerateCompEvents ? @.as_of_date" failed with the following error: "The type is not supported.DBTYPE_DBDATE". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

When I changed the datatype to DATE it gave me the error msg

"[Execute SQL Task] Error: Executing the query "usp_GenerateCompEvents ? @.as_of_date" failed with the following error: "Incorrect syntax near '@.as_of_date'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

When I changed the datatype to DBTIMESTAMP it gave me the error msg

"[Execute SQL Task] Error: Executing the query "usp_GenerateCompEvents ? @.as_of_date" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

Pls. let me know what's wrong with the Parameter Mapping or is there any syntax error with the SQLStatement.

Thanks,

Sridevi

|||

Sorry for replying late Anshu. The steps which i have mentioned is used to manually pass the parameter values. If i could understand the scenario you want about passing parameter from a .net application, i could post a better reply.Anyways, You can execute a package in the command prompt as:

dtexec /set \Package.Variables[User::var1].Properties[Value];yoursourcefilename /f D:\MyPackageLocation\Package.dtsx'

where yoursourcefilename is the name of the file you want to pass to the package located at MyPackageLocation.

Now you can use the Process and ProcessStartInfo class from your .net application to run the same command and pass the value like:

Code Snippet

string myFileName = "abc.txt";

string txtToRun = "dtexec /set \Package.Variables[User::var1].Properties[Value];"+myFileName+" /f :\MyPackageLocation\Package.dtsx";

Process.Start("CMD.exe", txtToRun); // in System.Diagnostics namespace

Hope this idea might help.. Thanks... Rupesh

|||

I guess you are following Step 5. Also, it seems you have written usp_GenerateCompEvents ? @.as_of_date in the sql statement instead of usp_GenerateCompEvents ? . Try removing the @.as_of_date variable from your statement.

Regards, Rupesh...

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005

Hi All,

(1) Create a SSIS project using Business Intelligence 2005 Or VS 2005.

(2) Create datasource (.ds) and Data Source View as required.

Hi Rupesh,

Can we manually pass the parameter (source file name) to SSIS package from .net application?

if yes then what would be approach for this?

Please provide me some pointers on this.

Thanks,

Anshu

|||

Hi Rupesh,

I followed ur steps to pass parameter in SSIS 2005.

I am passing a datetime variable to my stored procedure usp_GenerateCompEvents ? @.as_of_date with the "?" as you had mentioned in ur message in my sqlstatement.

And I also set my resultset to none in the General Tab.

And I mapped the parameter to a user defined variable of data type DBDATE and when I executed the task it gave me an error message

When I changed the datatype to DATE it gave me the error msg

When I changed the datatype to DBTIMESTAMP it gave me the error msg

Pls. let me know what's wrong with the Parameter Mapping or is there any syntax error with the SQLStatement.

Thanks,

Sridevi

|||

dtexec /set \Package.Variables[User::var1].Properties[Value];yoursourcefilename /f D:\MyPackageLocation\Package.dtsx'

where yoursourcefilename is the name of the file you want to pass to the package located at MyPackageLocation.

Now you can use the Process and ProcessStartInfo class from your .net application to run the same command and pass the value like:

Code Snippet

string myFileName = "abc.txt";

string txtToRun = "dtexec /set \Package.Variables[User::var1].Properties[Value];"+myFileName+" /f :\MyPackageLocation\Package.dtsx";

Process.Start("CMD.exe", txtToRun); // in System.Diagnostics namespace

Hope this idea might help.. Thanks... Rupesh

|||

Regards, Rupesh...

sql

Wednesday, March 28, 2012

Parameter Passing and Oracle Issues

I am relatively new to reporting services so I've been figuring this out as I go, but basically I am in the process of converting BI reports from another reporting service to the MS reporting services. The reports themselves have been recreated without a hitch but the parameters gave me issues. Frankly I needed more control over the parameters then RS allowed in order to match the features in the previous BI solution.

To solve this I decided to build a vb .net or asp .net front end and get the parameters, do whatever needs to be done, then pass them to a report viewer. Great, except a lot of my parameters are "from query" and the whole point of using these new reports is to avoid having oracle installed on the client computers. The only way I know of now to get these "from query" parameters is to run the query client side, meaning oracle needs to be installed.

Basically I am looking for either a way to obtain the "from query" parameter options from a report and pull them out for use in a vb.net or asp.net application, or another idea of how to get around this problem. Basically I want to be able to handle the parameters completely within the app, then just generate the report.

Hi Jeremicus,

A few thoughts:

Can you explain what you were trying to do with the parameters in the report that was causing you problems? We might be able to come up with a work around without having to go to an external source.

If you are creating an asp.net page, you should be able to query oracle and display the results without installing anything on the clients. Whatever odbc / oracle drivers you're using will be installed on the web server themselves.

You're not going to want to run a report to use data in an application. You should run the query directly in your application. Then if you want to display a report, you can pass the data using parameters.

HTH,

Jessica

|||Well my main problems with the parameters were things to do with multi-valued parameters. Basically in the previous reporting setup they could easily choose to leave input boxes blank, or put in a value, or put in multiple ones (either typing them in as a comma delimited string or selecting from a list).

Since the multi value parameters in RS wont allow you to not select something, this removes the ability to leave them blank, and the workarounds to avoid this aren't overly user friendly in the setting that these reports would be used, so basically I wanted to handle all that on my own and give more flexibility to the users and just do all the parameter formatting or other less pretty stuff 'behind the scenes'.

I might just go with the asp page instead of the application, I was kind of hoping for the app over the asp page but it'll have to do if I can't figure a way around this.
sql

Parameter not working

I am pretty new to reporting services and hope some of you experts can help.

I created a report from AS cube that has a column "Description" with 30 distinct values. The report has a multi-value parameter "Description" with the following details:

Data Type : String
Value Field: Description
Label Field : Description

The filter that i have on the table is =(Fields!Description.Value) = Cstr(Parameters!Description.Value(0))

When i preview the report i only see the top 1 item from the values that i select from the parameter list.

What am i doing wrong?

Any help is appreciated.

Thanks

Rookie,

Try

=(Fields!Description.Value) in Join(Parameters!Description.Value,", ")

because you have a multi field value this would be a better approach.

Ham

|||

Thanks for replying.

I just tried that. Now when i select more than one item in the parameter list, nothing displays. It works only if i select one value from the parameter list.

Any ideas?

|||

Rookie,

I was able to return results with setting my filters to

Expression =Cstr(Fields!Income1.Value)

Operator = In

Value = Join(Parameters!Report_Parameter_0.Value,"', '")

My parameter values were 1,2, 3, 4,

my Income was 1

when I selected 1 parameter, I recieve the expected results.

Ham

|||

Yeah it works for me only when i select one value, if i select two values in the parameter, nothing displays.

Have no clue whats happening.

|||

Rookie,

I played around with this for a bit, I was able to return multiple value with this expression in my filter.

Split(Join(Parameters!Inc.Value,","),",")

Ham

|||

Rookie,

Try this is in your filter expression Split(Join(Parameters!Inc.Value,","),",")

Ham

|||

Thanks a lot hammer2. This made it work.

Thanks

parameter length limitation

Hi,
I am using sql reporting services 2000, i am trying to pass a parameter with
the length of 3000 character and since the URL limitation is 2040 i used the
post method as follow
<FORM name=Form1 method="post" target="_self" >
<input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
<input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
<input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
<input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
<input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
<input name="PIN" type=hidden value="" ID=""/>
<input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
ID="Text2"/>
<input name="RegionId" type=hidden value="<%=request.form("regions") %>"
ID="Text3"/>
<input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
ID="Text4"/>
<input name="CommStatus" type=hidden value="" ID="Text5"/>
<input name="WIN" type=hidden value="" ID="Text6"/>
<input name="WorkOrder" type=hidden value="" ID="Text7"/>
<input name="County" type=hidden value="" ID="Text8"/>
<input name="StateRoute" type=hidden value="" ID="Text9"/>
<input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
%> " ID="Text10"/>
<input name="SearchParameter" type=hidden value="" ID="Text11"/>
<input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
<script>
document.Form1.action= "<% response.write request.form("formaction") %>"; //
form action which will be the url to the reporting server
document.Form1.submit();
</script>
even after i used this method i still have parameter length limitation. is
there any way to go around this problem?
Thanks
</form>Robert,
What kind of parameter are you passing that it has to be 3,000 chars?!
Are you passing SQL into a report through a parameter?
-Josh
Robert wrote:
> Hi,
> I am using sql reporting services 2000, i am trying to pass a parameter with
> the length of 3000 character and since the URL limitation is 2040 i used the
> post method as follow
>
> <FORM name=Form1 method="post" target="_self" >
>
> <input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
> <input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
> <input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
> <input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
> <input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
>
> <input name="PIN" type=hidden value="" ID=""/>
> <input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
> ID="Text2"/>
> <input name="RegionId" type=hidden value="<%=request.form("regions") %>"
> ID="Text3"/>
> <input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
> ID="Text4"/>
> <input name="CommStatus" type=hidden value="" ID="Text5"/>
> <input name="WIN" type=hidden value="" ID="Text6"/>
> <input name="WorkOrder" type=hidden value="" ID="Text7"/>
> <input name="County" type=hidden value="" ID="Text8"/>
> <input name="StateRoute" type=hidden value="" ID="Text9"/>
> <input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
> %> " ID="Text10"/>
> <input name="SearchParameter" type=hidden value="" ID="Text11"/>
> <input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
> <script>
> document.Form1.action= "<% response.write request.form("formaction") %>"; //
> form action which will be the url to the reporting server
> document.Form1.submit();
> </script>
> even after i used this method i still have parameter length limitation. is
> there any way to go around this problem?
> Thanks
> </form>|||Josh
user can choose different documents and then i send the documentid(s) to
report server and stored procedure on the back is going to do some processing
on it and then return it.
so some times may this documentid have length of 3000 or more because these
documentids are commas seprated.
do you have any suggestion that may help me?
Thanks
"Josh" wrote:
> Robert,
> What kind of parameter are you passing that it has to be 3,000 chars?!
> Are you passing SQL into a report through a parameter?
> -Josh
>
> Robert wrote:
> > Hi,
> > I am using sql reporting services 2000, i am trying to pass a parameter with
> > the length of 3000 character and since the URL limitation is 2040 i used the
> > post method as follow
> >
> >
> > <FORM name=Form1 method="post" target="_self" >
> >
> >
> > <input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
> > <input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
> > <input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
> > <input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
> > <input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
> >
> >
> > <input name="PIN" type=hidden value="" ID=""/>
> > <input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
> > ID="Text2"/>
> > <input name="RegionId" type=hidden value="<%=request.form("regions") %>"
> > ID="Text3"/>
> > <input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
> > ID="Text4"/>
> > <input name="CommStatus" type=hidden value="" ID="Text5"/>
> > <input name="WIN" type=hidden value="" ID="Text6"/>
> > <input name="WorkOrder" type=hidden value="" ID="Text7"/>
> > <input name="County" type=hidden value="" ID="Text8"/>
> > <input name="StateRoute" type=hidden value="" ID="Text9"/>
> > <input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
> > %> " ID="Text10"/>
> > <input name="SearchParameter" type=hidden value="" ID="Text11"/>
> > <input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
> >
> > <script>
> > document.Form1.action= "<% response.write request.form("formaction") %>"; //
> > form action which will be the url to the reporting server
> >
> > document.Form1.submit();
> > </script>
> >
> > even after i used this method i still have parameter length limitation. is
> > there any way to go around this problem?
> >
> > Thanks
> > </form>
>|||OK... this suggestion will only work with SSRS 2005, so I will also
recommend an upgrade.
One of the enhancements offered in SSRS 2005 is multi-valued
parameters. With the introduction of this option, they also added a
JOIN function that will convert a parameter array into a character
delimited string. Sooo...
You create a docID parm (probably string would be best) that is
multi-value. Then, whatever you are using to call the report and pass
that huge string can build the URL like this:
http://serverName/ReportServer/Pages/ReportViewer.aspx?/Folder1/Folder2/ReportName&docID=abc1&docID=abc2&docID=abc3
Create another parameter called docIDstring and mark it as hidden or
internal. (If you want to be able to pass the string already formatted
as CSV, use hidden so that you can pass it in the URL. If you are ONLY
going to use the approach that I am suggesting, mark it as internal.)
Set the default value of the internal/hidden parm to:
=JOIN(Parameters!docID.Value,","). Multi-value parameters are treated
as an array, and this JOIN function will convert the array to CSV.
If you used this expression with the URL that is listed above,
docIDstring would = "abc1,abc2,abc3".
Sorry it only works with SSRS 2005, but you probably need to upgrade
sometime anyway.
Hope this helps!
-Josh
Robert wrote:
> Josh
> user can choose different documents and then i send the documentid(s) to
> report server and stored procedure on the back is going to do some processing
> on it and then return it.
> so some times may this documentid have length of 3000 or more because these
> documentids are commas seprated.
> do you have any suggestion that may help me?
> Thanks
> "Josh" wrote:
> >
> > Robert,
> >
> > What kind of parameter are you passing that it has to be 3,000 chars?!
> > Are you passing SQL into a report through a parameter?
> >
> > -Josh
> >
> >
> > Robert wrote:
> > > Hi,
> > > I am using sql reporting services 2000, i am trying to pass a parameter with
> > > the length of 3000 character and since the URL limitation is 2040 i used the
> > > post method as follow
> > >
> > >
> > > <FORM name=Form1 method="post" target="_self" >
> > >
> > >
> > > <input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
> > > <input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
> > > <input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
> > > <input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
> > > <input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
> > >
> > >
> > > <input name="PIN" type=hidden value="" ID=""/>
> > > <input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
> > > ID="Text2"/>
> > > <input name="RegionId" type=hidden value="<%=request.form("regions") %>"
> > > ID="Text3"/>
> > > <input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
> > > ID="Text4"/>
> > > <input name="CommStatus" type=hidden value="" ID="Text5"/>
> > > <input name="WIN" type=hidden value="" ID="Text6"/>
> > > <input name="WorkOrder" type=hidden value="" ID="Text7"/>
> > > <input name="County" type=hidden value="" ID="Text8"/>
> > > <input name="StateRoute" type=hidden value="" ID="Text9"/>
> > > <input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
> > > %> " ID="Text10"/>
> > > <input name="SearchParameter" type=hidden value="" ID="Text11"/>
> > > <input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
> > >
> > > <script>
> > > document.Form1.action= "<% response.write request.form("formaction") %>"; //
> > > form action which will be the url to the reporting server
> > >
> > > document.Form1.submit();
> > > </script>
> > >
> > > even after i used this method i still have parameter length limitation. is
> > > there any way to go around this problem?
> > >
> > > Thanks
> > > </form>
> >
> >|||the problem with this approach is that the url can only be 2040 character and
mine for sure will be more.
Thanks
"Josh" wrote:
> OK... this suggestion will only work with SSRS 2005, so I will also
> recommend an upgrade.
> One of the enhancements offered in SSRS 2005 is multi-valued
> parameters. With the introduction of this option, they also added a
> JOIN function that will convert a parameter array into a character
> delimited string. Sooo...
> You create a docID parm (probably string would be best) that is
> multi-value. Then, whatever you are using to call the report and pass
> that huge string can build the URL like this:
> http://serverName/ReportServer/Pages/ReportViewer.aspx?/Folder1/Folder2/ReportName&docID=abc1&docID=abc2&docID=abc3
> Create another parameter called docIDstring and mark it as hidden or
> internal. (If you want to be able to pass the string already formatted
> as CSV, use hidden so that you can pass it in the URL. If you are ONLY
> going to use the approach that I am suggesting, mark it as internal.)
> Set the default value of the internal/hidden parm to:
> =JOIN(Parameters!docID.Value,","). Multi-value parameters are treated
> as an array, and this JOIN function will convert the array to CSV.
> If you used this expression with the URL that is listed above,
> docIDstring would = "abc1,abc2,abc3".
> Sorry it only works with SSRS 2005, but you probably need to upgrade
> sometime anyway.
> Hope this helps!
> -Josh
>
> Robert wrote:
> > Josh
> >
> > user can choose different documents and then i send the documentid(s) to
> > report server and stored procedure on the back is going to do some processing
> > on it and then return it.
> >
> > so some times may this documentid have length of 3000 or more because these
> > documentids are commas seprated.
> > do you have any suggestion that may help me?
> > Thanks
> >
> > "Josh" wrote:
> >
> > >
> > > Robert,
> > >
> > > What kind of parameter are you passing that it has to be 3,000 chars?!
> > > Are you passing SQL into a report through a parameter?
> > >
> > > -Josh
> > >
> > >
> > > Robert wrote:
> > > > Hi,
> > > > I am using sql reporting services 2000, i am trying to pass a parameter with
> > > > the length of 3000 character and since the URL limitation is 2040 i used the
> > > > post method as follow
> > > >
> > > >
> > > > <FORM name=Form1 method="post" target="_self" >
> > > >
> > > >
> > > > <input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
> > > > <input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
> > > > <input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
> > > > <input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
> > > > <input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
> > > >
> > > >
> > > > <input name="PIN" type=hidden value="" ID=""/>
> > > > <input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
> > > > ID="Text2"/>
> > > > <input name="RegionId" type=hidden value="<%=request.form("regions") %>"
> > > > ID="Text3"/>
> > > > <input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
> > > > ID="Text4"/>
> > > > <input name="CommStatus" type=hidden value="" ID="Text5"/>
> > > > <input name="WIN" type=hidden value="" ID="Text6"/>
> > > > <input name="WorkOrder" type=hidden value="" ID="Text7"/>
> > > > <input name="County" type=hidden value="" ID="Text8"/>
> > > > <input name="StateRoute" type=hidden value="" ID="Text9"/>
> > > > <input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
> > > > %> " ID="Text10"/>
> > > > <input name="SearchParameter" type=hidden value="" ID="Text11"/>
> > > > <input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
> > > >
> > > > <script>
> > > > document.Form1.action= "<% response.write request.form("formaction") %>"; //
> > > > form action which will be the url to the reporting server
> > > >
> > > > document.Form1.submit();
> > > > </script>
> > > >
> > > > even after i used this method i still have parameter length limitation. is
> > > > there any way to go around this problem?
> > > >
> > > > Thanks
> > > > </form>
> > >
> > >
>|||I see.
How many chars is each document ID?
-Josh
Robert wrote:
> the problem with this approach is that the url can only be 2040 character and
> mine for sure will be more.
> Thanks
>
> "Josh" wrote:
> >
> > OK... this suggestion will only work with SSRS 2005, so I will also
> > recommend an upgrade.
> >
> > One of the enhancements offered in SSRS 2005 is multi-valued
> > parameters. With the introduction of this option, they also added a
> > JOIN function that will convert a parameter array into a character
> > delimited string. Sooo...
> >
> > You create a docID parm (probably string would be best) that is
> > multi-value. Then, whatever you are using to call the report and pass
> > that huge string can build the URL like this:
> >
> > http://serverName/ReportServer/Pages/ReportViewer.aspx?/Folder1/Folder2/ReportName&docID=abc1&docID=abc2&docID=abc3
> >
> > Create another parameter called docIDstring and mark it as hidden or
> > internal. (If you want to be able to pass the string already formatted
> > as CSV, use hidden so that you can pass it in the URL. If you are ONLY
> > going to use the approach that I am suggesting, mark it as internal.)
> >
> > Set the default value of the internal/hidden parm to:
> > =JOIN(Parameters!docID.Value,","). Multi-value parameters are treated
> > as an array, and this JOIN function will convert the array to CSV.
> >
> > If you used this expression with the URL that is listed above,
> > docIDstring would = "abc1,abc2,abc3".
> >
> > Sorry it only works with SSRS 2005, but you probably need to upgrade
> > sometime anyway.
> >
> > Hope this helps!
> >
> > -Josh
> >
> >
> >
> > Robert wrote:
> > > Josh
> > >
> > > user can choose different documents and then i send the documentid(s) to
> > > report server and stored procedure on the back is going to do some processing
> > > on it and then return it.
> > >
> > > so some times may this documentid have length of 3000 or more because these
> > > documentids are commas seprated.
> > > do you have any suggestion that may help me?
> > > Thanks
> > >
> > > "Josh" wrote:
> > >
> > > >
> > > > Robert,
> > > >
> > > > What kind of parameter are you passing that it has to be 3,000 chars?!
> > > > Are you passing SQL into a report through a parameter?
> > > >
> > > > -Josh
> > > >
> > > >
> > > > Robert wrote:
> > > > > Hi,
> > > > > I am using sql reporting services 2000, i am trying to pass a parameter with
> > > > > the length of 3000 character and since the URL limitation is 2040 i used the
> > > > > post method as follow
> > > > >
> > > > >
> > > > > <FORM name=Form1 method="post" target="_self" >
> > > > >
> > > > >
> > > > > <input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
> > > > > <input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
> > > > > <input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
> > > > > <input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
> > > > > <input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
> > > > >
> > > > >
> > > > > <input name="PIN" type=hidden value="" ID=""/>
> > > > > <input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
> > > > > ID="Text2"/>
> > > > > <input name="RegionId" type=hidden value="<%=request.form("regions") %>"
> > > > > ID="Text3"/>
> > > > > <input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
> > > > > ID="Text4"/>
> > > > > <input name="CommStatus" type=hidden value="" ID="Text5"/>
> > > > > <input name="WIN" type=hidden value="" ID="Text6"/>
> > > > > <input name="WorkOrder" type=hidden value="" ID="Text7"/>
> > > > > <input name="County" type=hidden value="" ID="Text8"/>
> > > > > <input name="StateRoute" type=hidden value="" ID="Text9"/>
> > > > > <input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
> > > > > %> " ID="Text10"/>
> > > > > <input name="SearchParameter" type=hidden value="" ID="Text11"/>
> > > > > <input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
> > > > >
> > > > > <script>
> > > > > document.Form1.action= "<% response.write request.form("formaction") %>"; //
> > > > > form action which will be the url to the reporting server
> > > > >
> > > > > document.Form1.submit();
> > > > > </script>
> > > > >
> > > > > even after i used this method i still have parameter length limitation. is
> > > > > there any way to go around this problem?
> > > > >
> > > > > Thanks
> > > > > </form>
> > > >
> > > >
> >
> >sql

Monday, March 26, 2012

parameter is missing a value error

I am trying to populate a report "rdlc" file within a reportviewer control in a webpage. It works fine when is ran as a RDL file as a report services solution. Now that I created my datasource that will populate my report, I get an error "The 'beg_date' parameter is missing a value", which is a field that is specified back from the result of the procedure. Anyone know how to approach this?

Private connAs String Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load conn = System.Configuration.ConfigurationManager.ConnectionStrings("ESQLConn").ConnectionString()End Sub Protected Sub Button1_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Button1.Click AddReportDataset()End Sub Private Sub AddReportDataset()Dim ds1As ReportDataSource =New ReportDataSource("DataSource1", sd1())Dim ds2As ReportDataSource =New ReportDataSource("DataSource2", sd2())Dim ds3As ReportDataSource =New ReportDataSource("DataSource3", sd3())Me.rptViewer.LocalReport.DataSources.Add(ds1)Me.rptViewer.LocalReport.DataSources.Add(ds2)Me.rptViewer.LocalReport.DataSources.Add(ds3)End Sub Private Function sd1()As SqlDataSourceDim compnameAs ControlParameter =New ControlParameter compname.DefaultValue =Me.txtCompany.Text.ToStringDim begdateAs ControlParameter =New ControlParameter begdate.DefaultValue =Me.txtBegDate.Text.ToStringDim enddateAs ControlParameter =New ControlParameter enddate.DefaultValue =Me.txtEndDate.Text.ToStringDim sqldsAs New SqlDataSource sqlds.ConnectionString = conn sqlds.SelectCommand ="csp_rpt_ef0115_db" sqlds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure sqlds.SelectParameters.Add(compname) sqlds.SelectParameters.Add(begdate) sqlds.SelectParameters.Add(enddate)Return sqldsEnd Function Private Function sd2()As SqlDataSourceDim sqldsAs New SqlDataSource sqlds.ConnectionString = conn sqlds.SelectCommand ="SELECT DISTINCT RTRIM(Act_Type) + ISNULL(Act_Sub_Type,'') AS Type FROM AR_Act" sqlds.SelectCommandType = SqlDataSourceCommandType.Text Return sqlds End Function Private Function sd3() As SqlDataSource Dim sqlds As New SqlDataSource sqlds.ConnectionString = conn sqlds.SelectCommand = "select Item from dbo.Pick_Items with (nolock)
where fPick_Name='asxdbLocations" sqlds.SelectCommandType = SqlDataSourceCommandType.TextReturn sqldsEnd Function

hello,

you seem to be commenting some of you code out by using ' instead of "

See your original post and look at the text in green which is commented out. Could this be the problem?

Regards

Marco

|||

use those link it might help you http://www.c-sharpcorner.com/UploadFile/asif.blog/DynamicDataGrouping08042006115320AM/DynamicDataGrouping.aspx

Parameter in WHERE clause using Functions

I'm trying to use a report parameter in my where clause to compare against
the month portion of a field. Reporting Services (version 2000) does not seem
to like this. Here's the WHERE clause I'm trying to get to work (of course
it's all on one line):
WHERE (company = @.Company) AND (MONTH(close_time) = @.DSMonth1) AND
(YEAR(close_time) = @.DSYear1)
When I try to execute the query, I get an error that states:
Invalid object name "dbo.RAW_DW.MONTH"
Invalid object name "dbo.RAW_DW.YEAR"
It seems Reporting Services thinks MONTH and YEAR are fields when I'm just
trying to use the MONTH and YEAR functions to extract that data from the
datetime field (close_time) and compare it to the report parameter. It may
just be a formatting issue on my part.
Does anyone know what's wrong with the clause?
Thanks in advance.
BrendaHi Brenda,
I dont think that the error you are getting is related to the WHERE clause
in this case. Do you have RAW_DW.Month and ...YEAR in your overall query?
What are the data types of the two parameters, @.DSMonth1 and @.DSYear1?
Without seeing the full query, it is hard to troubleshoot the error, if
indeed these are valid objects in your database.
Rodney Landrum
"BLKeller" <BLKeller@.discussions.microsoft.com> wrote in message
news:69A3593E-8832-46BB-ADD1-A1079829C95E@.microsoft.com...
> I'm trying to use a report parameter in my where clause to compare against
> the month portion of a field. Reporting Services (version 2000) does not
> seem
> to like this. Here's the WHERE clause I'm trying to get to work (of course
> it's all on one line):
> WHERE (company = @.Company) AND (MONTH(close_time) = @.DSMonth1) AND
> (YEAR(close_time) = @.DSYear1)
> When I try to execute the query, I get an error that states:
> Invalid object name "dbo.RAW_DW.MONTH"
> Invalid object name "dbo.RAW_DW.YEAR"
> It seems Reporting Services thinks MONTH and YEAR are fields when I'm just
> trying to use the MONTH and YEAR functions to extract that data from the
> datetime field (close_time) and compare it to the report parameter. It may
> just be a formatting issue on my part.
> Does anyone know what's wrong with the clause?
> Thanks in advance.
> Brenda

Parameter Error on Deployment

New to report services and trying out my first report. I have a report with a simple parameter lookup. When I preview it in Visual Studio it works fine. I can select the parameter value and it returns back the correct information.

When trying to preview the deployed version of the report I get the following error after selecting the parameter value and clicking preview. I am not sure where I need to declare the variable @.owners.

Thanks

Reporting Services Error

An error has occurred during report processing. (rsProcessingAborted) Get Online Help Query execution failed for data set 'ANDON'. (rsErrorExecutingCommand) Get Online Help ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.Owners'.

Microsoft Reporting Services

I guess you are using @.Owner in your sql query and not defined it as a parameter for that dataset.

So in Data tab, select the dataset Edit the dataset by clicking on "..." button. and go to Paramaters tab. Type @.Owner under Name column and type "=Parameters!Owner.Value".

I'm asssuimg that Owner is already defined as one of the report parameters (Report -> Report Parameters). If not, create this parameter and then do the above.

Shyam

|||

Thanks for the response.

I have my SQL query defined as

SELECT idfld, andon, createdate, issuetype, origname, owner, notes, outcome, responded, resolution
FROM dbo.issue
WHERE (owner = @.Owner)

I went into the Data Tab/Parameters Tab and @.Owner = Parameters!Owner.value was already set

In my report parameters I have Owner defined and it is pulling information from a Select Distinct owner query.

Still works in the view mode and I still get the error when I look at the deployed report.

|||

I guess you have set Available values for your parameter from a query. Try setting a default value for your parameter.

Shyam

|||

Yes, I am getting the values for my parameter from a query. I tried setting a default value and I still get the same error.

|||

Is your parameter multi-valued?

Also check the following link:

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

What is the provider type you are using on your report server and on your local machine (from visual studio)? Based on that check how you should handle as described in the link above.

Shyam

|||

"What is the provider type you are using on your report server and on your local machine (from visual studio)? "

That was the key. I had to modify the data source on the Reports Server to match what I was using on my report. Thanks!

|||make sure that the parameter is consistent throughout your code. Parameters are case sensitive - In your post, the error message referred to @.Owners - with an "s". In your posts it was referred to as @.Owner and @.owner. Reporting services sends the error when there is any difference between the report parameter in the parameter drop down and its references in the underlying code.

Parameter Error on Deployment

Thanks

Reporting Services Error

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Query execution failed for data set 'ANDON'. (rsErrorExecutingCommand) Get Online Help

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.Owners'.

Microsoft Reporting Services

I guess you are using @.Owner in your sql query and not defined it as a parameter for that dataset.

So in Data tab, select the dataset Edit the dataset by clicking on "..." button. and go to Paramaters tab. Type @.Owner under Name column and type "=Parameters!Owner.Value".

I'm asssuimg that Owner is already defined as one of the report parameters (Report -> Report Parameters). If not, create this parameter and then do the above.

Shyam

|||

Thanks for the response.

I have my SQL query defined as

SELECT idfld, andon, createdate, issuetype, origname, owner, notes, outcome, responded, resolution
FROM dbo.issue
WHERE (owner = @.Owner)

I went into the Data Tab/Parameters Tab and @.Owner = Parameters!Owner.value was already set

In my report parameters I have Owner defined and it is pulling information from a Select Distinct owner query.

Still works in the view mode and I still get the error when I look at the deployed report.

|||

I guess you have set Available values for your parameter from a query. Try setting a default value for your parameter.

Shyam

|||

Yes, I am getting the values for my parameter from a query. I tried setting a default value and I still get the same error.

|||

Is your parameter multi-valued?

Also check the following link:

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

What is the provider type you are using on your report server and on your local machine (from visual studio)? Based on that check how you should handle as described in the link above.

Shyam

|||

"What is the provider type you are using on your report server and on your local machine (from visual studio)? "

That was the key. I had to modify the data source on the Reports Server to match what I was using on my report. Thanks!

Parameter doesn''t get supplied to stored procedure

I'm rather new at reporting services, so bear with me on this one.

I have a stored procedure in MSSQL called prc_failedSLA_per_week, that has two parameters (@.startWeek and @.endWeek). I've added a dataset in reporting services which runs my procedure. when I run it from the data tab in RS, I get prompted for values on these two parameters, and a table gets generated. But if I try to preview my report, I get the error "Procedure of function "prc_failedSLA_per_week" expects parameter "@.startWeek", which was not supplied".

I've tried to find info on this in the help file to no avail, not even google helps me in any way. I guess it's some kind of newbie mistake, but I simply can't figure out what to do.

Any help would be greatly appreciated!
Nevermind, I got it solved. Seem that I had to press the refresh button in the dataset for the parameters to work, which wasn't mentioned anywhere that I looked. Unlogical, but now it works at least :-/
|||same thing happened to me. I actually thought that the code was messed up (code behind). So deleted my report and recreated another one. But then i refreshed my dataset over and over and now its workin!! THANKS!!!

Parameter doesn't get supplied to stored procedure

I'm rather new at reporting services, so bear with me on this one.

I've tried to find info on this in the help file to no avail, not even google helps me in any way. I guess it's some kind of newbie mistake, but I simply can't figure out what to do.

Parameter calendar control : week start date

With Reporting Services (SQL Server 2005), if you include a parameter with a
data type of DateTime, you get a calendar control for the parameter on your
report.
How do you change the first day of the week from Sunday to Monday?
Thanks,
CraigIs your question related to calender control, because you have asked
seperately,
in fact you cant change the calender control. Ofcourse in sql server yes you
can by giving
SET DATEFIRST 1 (Monday)
SET DATEFIRST 7 (Sunday - Default)
Amarnath
"CraigHB" wrote:
> With Reporting Services (SQL Server 2005), if you include a parameter with a
> data type of DateTime, you get a calendar control for the parameter on your
> report.
> How do you change the first day of the week from Sunday to Monday?
> Thanks,
> Craig

Friday, March 23, 2012

Parameter area in RS2000

Hi,

Is there any way to have some kind of control over the controls in the reporting services htmlviewer parameter area.

What i really wan't to do is set a minimum width to the combobox's in that area, or a fixed width,it would be good too.

Thanks.

To keep it short and simple, no.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Unlike Rs2005 there is no css file for the htmlviewer in rs2000? Or anything that resembles that?

Thank's

|||Oh sure yes there is a css file, I was not aware that you can influence the display behaviour of the controls by these files.

http://www.sqlserver2005.de|||

At least RS2005 we can. I think that in RS2000 things are a bit diferent. I can′t seem to find the file htmlviewer.css (this is the file we can tweak in RS2005 to provide some control over the display behaviour), on anything that resembles.

Thanks.

Friday, March 30, 2012

Parameter Problems ! ! !

Parameter problem with OLAP and spaces

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005

Wednesday, March 28, 2012

Parameter Passing and Oracle Issues

Parameter not working

parameter length limitation

Monday, March 26, 2012

parameter is missing a value error

Parameter in WHERE clause using Functions

Parameter Error on Deployment

Reporting Services Error

Parameter Error on Deployment

Reporting Services Error

Parameter doesn''t get supplied to stored procedure

Parameter doesn't get supplied to stored procedure

Parameter calendar control : week start date

Friday, March 23, 2012

Parameter area in RS2000

Page Numbers in Excel

Blog Archive

About Me