Parsing Variable Length Delimited Records

I am running SQLServer 2000 to parse and store records in the EDIX12 format. This consists of variable length delimited records which I am passing to the "transforms" tab to process with VBScript.
The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions?
How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there.
Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it.
Thanks for yuor help,
--greg

SSIS has built-in functionality for importing text files although that functionality doesn't handle variable number of columns too well.

Fear not though - the script component is your friend here. I highly recommend Donald Farmer's book which includes a chapter on importing text files using the script component.

-Jamie

Parsing Variable Length Delimited Records

I am running SQLServer 2000 to parse and store records in the EDIX12 format. This consists of variable length delimited records which I am passing to the "transforms" tab to process with VBScript.
The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions?
How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there.
Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it.
Thanks for yuor help,
--greg

SSIS has built-in functionality for importing text files although that functionality doesn't handle variable number of columns too well.

Fear not though - the script component is your friend here. I highly recommend Donald Farmer's book which includes a chapter on importing text files using the script component.

-Jamie

Paramter Passing

Is it possible to pass variables/parameters to a report other thank through
a url using ASP, as this will compromise our security ?
Thanks in advanceTwo options. 1. Use web services. 2. Pull the sensitive parameters by using
the global user!userid. This value tells you who is running the report and
you can then use that to query the database to find out the sensitive
parameters. You might need to do something like having a table with the
username, report and sensitive parameters that your write to before your
application calls the report. And, as I said, you can also use web services.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Con" <conh@.melbournehosting.com> wrote in message
news:u8z7eiPvEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Is it possible to pass variables/parameters to a report other thank
through
> a url using ASP, as this will compromise our security ?
> Thanks in advance
>

parameters via url not being seen

Iâ'm having a problem passing parameters to my report thru the url.
Iâ've read the online text, the manuals we have here, and the newsgroup
entries.
I created a "non-queried" parameter in my report: TestParam
I can test this parameter when running this report in Report Manager. I
enter a value, click View Report, and the data displays in a text box that
displays data based on the data entered into the parameter box.
When I call the report from a vb.net client application thru a url, the
report loads up in a Report Manager window, with NO errors, but no data
displays in either the parameter box or the text box. It is waiting for
parameter input, just as it normally would when bringing up the report in
report manager.
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
Iâ've tried this with and without the Command=Render. I've verified the
parameter name is correct and is the correct case.
Am I still missing something? Is there something more that I should do
with the report parameter before it will accept input from a url?Example from BOL
http://<Webservername>/reportserver?/<reportfolder>/employee+sales+summary&ReportYear=2004&ReportMonth=06&EmpID=24&rs:Command=Render&rs:Format=HTML4.0
so does this work?
http://server/Reports/Pages/Report.aspx?/fFolderPath/ReportName&TestParam=test&rs:Command=Render
--
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.
"David" <David@.discussions.microsoft.com> wrote in message
news:8632D6C4-002C-41E4-9E79-C1C74DC31784@.microsoft.com...
> I'm having a problem passing parameters to my report thru the url.
> I've read the online text, the manuals we have here, and the newsgroup
> entries.
> I created a "non-queried" parameter in my report: TestParam
> I can test this parameter when running this report in Report Manager. I
> enter a value, click View Report, and the data displays in a text box that
> displays data based on the data entered into the parameter box.
> When I call the report from a vb.net client application thru a url, the
> report loads up in a Report Manager window, with NO errors, but no data
> displays in either the parameter box or the text box. It is waiting for
> parameter input, just as it normally would when bringing up the report in
> report manager.
>
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
> I've tried this with and without the Command=Render. I've verified the
> parameter name is correct and is the correct case.
> Am I still missing something? Is there something more that I should do
> with the report parameter before it will accept input from a url?
>|||The second looks like it should work but what happens is exactly what he is
seeing, the parameters do not get filled in. You need to use reportserver
syntax as you show from BOL (the difference is RS portal (Report Manager)
versus ReportServer).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23Z40Ny2RFHA.3288@.TK2MSFTNGP14.phx.gbl...
> Example from BOL
>
http://<Webservername>/reportserver?/<reportfolder>/employee+sales+summary&ReportYear=2004&ReportMonth=06&EmpID=24&rs:Command=Render&rs:Format=HTML4.0
> so does this work?
>
http://server/Reports/Pages/Report.aspx?/fFolderPath/ReportName&TestParam=test&rs:Command=Render
> --
> 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.
> "David" <David@.discussions.microsoft.com> wrote in message
> news:8632D6C4-002C-41E4-9E79-C1C74DC31784@.microsoft.com...
> > I'm having a problem passing parameters to my report thru the url.
> >
> > I've read the online text, the manuals we have here, and the newsgroup
> > entries.
> >
> > I created a "non-queried" parameter in my report: TestParam
> >
> > I can test this parameter when running this report in Report Manager. I
> > enter a value, click View Report, and the data displays in a text box
that
> > displays data based on the data entered into the parameter box.
> >
> > When I call the report from a vb.net client application thru a url, the
> > report loads up in a Report Manager window, with NO errors, but no data
> > displays in either the parameter box or the text box. It is waiting for
> > parameter input, just as it normally would when bringing up the report
in
> > report manager.
> >
> >
>
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
> >
> > I've tried this with and without the Command=Render. I've verified the
> > parameter name is correct and is the correct case.
> >
> > Am I still missing something? Is there something more that I should do
> > with the report parameter before it will accept input from a url?
> >
> >
>

Parameters Passing

Hi,
In MSRS 2000 I use pass the parameters from a web page like this
ReportViewer1.ReportPath = "/RptProj/EMPINFO&EMPNO=00004";
ReportViewer1.ServerUrl = "http://<ServerName>/ReportServer";
In MSRS 2005 Iam trying this but it is not working (Even with "&" insted of the "?")
ReportViewer1.ServerReport.ReportPath = "/ReportingProject/EmpReport?ManagerID=3";
ReportViewer1.ServerReport.ReportServerUrl = new Uri("http://<ServerName>/ReportServer");
can any body tell me what what wrong in this & how do I fix it??

which mode you use? Local mode or Remote mode?

|||Remote mode

Parameters passed to Stored Procedure from ASP

I'm relatively new to stored procedure writing. My situation is an ASP page allowing multiple selections from a <select> option passing 1 to x number of options to a stored proc. for the WHERE clause.
For instance, a user selects 1,3,5 and 6. These would need to be passed to the sp and then:

...FROM [tablename]
WHERE (Number = @.param1) OR (Number = @.param2) OR (Number = @.param3) OR (Number = @.param4) etc...

This sp ties into a Crystal Report and in the above scenario, should return data for colums equal to 1 3 5 and 6. If only 1 and 6 had been selected on the ASP page, then only those two would be assigned a value in the sp.

Any suggestions?
Thanks in advance...

IIS 5.0, Win 2k, MSSQL 7.0Just create an ado connection/command objects and execute the stored procedure. What are the ranges for the parameters ?|||Looks like you want to say something like

WHERE col1 IN (@.param1, @.param2, @.param3, @.param4)

Or

WHERE col1 IN (@.param1, @.param2)

whatever the case may be...is that right?

Or are they diferent columns|||Well, if the ASP was passing three different parameters (Start Date, End Date, Details) they would be passed to the sp like(whereas the strStartDate, strEndDate and strDetails were assigned the Request.Form values):

Set ThisParam = StoredProcParamCollection.item(1)
ThisParam.SetCurrentValue cstr(strStartDate), 12

Set ThisParam = StoredProcParamCollection.item(2)
ThisParam.SetCurrentValue cstr(strEndDate), 12

Set ThisParam = StoredProcParamCollection.item(3)
ThisParam.SetCurrentValue cstr(strDetails), 12

However, in my scenario, I need to allow for multiple selections in one Request.Form("select") collection so to speak passed to the sp.

Does that make sense??
:-\|||What is the maximum number of selections in the select box - and will this keep growing ?|||The select box as 12 selections. The user can pick as few as one or as many as all. Basically, any combination. Say they pick 1 & 2. I need to pass those selections to the sp and use those parameters in the where

WHERE [columnname] = @.parameter1 or [columnname] = @.parameter2 or [columnname] = @.parameter3(parameter 3 remains default value since only 1 and two were passed in)

(The parameters are assign the passed values or remain default if no value passed)|||How come you don't ask for a variable result set from the SELECT..can't they pick their own fields too?

Don't want to use the D word....

Have you run a sql statement with all 13 parameters?

Can we see the sproc...

Is it like CREATE PROC mySproc @.Param1 = null, @.Param2 = null

Maybe you can pass all of them

and do WHERE Col1 = ISNULL(@.Param1,Col1) AND...|||You could set up one parameter and pass a delimited string containing all your selections.

You then split the string up and use the in statement to do your selection.

It's not pretty but it will work.

Let me know if you want details of how to do this.|||Brett & rokslide, thanks for the help. Basically, the options aren't added from a db connection into the asp page. So, hard coding the 12 values (which never change as they are campus locations) isn't a problem. So, the intent was to pass any combination of selected campuses, pass them to a sp and generate a report for the campuses selected.

...FROM [tablename]
WHERE (Number = @.param1) OR (Number = @.param2) OR (Number = @.param3) OR (Number = @.param4) etc...

Is basically what I need to do, but I like the delimited idea and splitting the string and doing the select through iteration.

Parameters in Reporting Services

Hi,
I am facing problem in reporting services using parameter field.I am
passing values to parameter field empids like '10,12'.I want the data of
employee 10 and 12.while creating the parameter how can i mention.In t-sql
empid IN @.eID is it correct,how can i mention in parameter field.
Thanks in advance
Malli.There are several ways you may do this...If your user can make a properly
formatted comma delimited list your SQL might look like this
="Select * from employee where employeeid in (" +
Parameters!EmployeeID.Value + "))"
you could also make a table valued function which parses the parameter and
returns a table which contains one row for each employee id. YOu then join
that to the employees table
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
"Malli" <Malli@.discussions.microsoft.com> wrote in message
news:E7538F08-2BA3-4590-8FE6-B84C3EB96BC3@.microsoft.com...
> Hi,
> I am facing problem in reporting services using parameter field.I am
> passing values to parameter field empids like '10,12'.I want the data of
> employee 10 and 12.while creating the parameter how can i mention.In t-sql
> empid IN @.eID is it correct,how can i mention in parameter field.
>
> Thanks in advance
> Malli.|||Hi Wayne,
i might have a similar issue and what you propose is not working for me. I
don't know if it was the same issue for Malli but at least it is the same
topic.
I open a report in Design mode and i am on the Data pane.
I want to have a recordset with one field being a parameter where the user
can enter comma separated values (i will inform the user in the parameter
prompt).
Now, i can make the test of the query in the Data pane.
- when i'm typing IN (@.Status) and in the pop-up the value 1 then it works
fine
- when i'm typing IN (@.Status) and in the pop-up the value 1,5 then it
doesn't work
- when i'm typing IN (1;5), it is transformed to IN (N'1','5') and it works
(thank you Microsoft for that new syntax !)
- when i'm typing IN (@.Status) and in the pop-up the value 1;5 then it
doesn't work
So, how to write the query for the report in the Data pane ?
What should the user enter as value in its text box ?
Thanks a lot to solve this mysterious behaviour.....Dominique
************************************************************
"Wayne Snyder" wrote:
> There are several ways you may do this...If your user can make a properly
> formatted comma delimited list your SQL might look like this
> ="Select * from employee where employeeid in (" +
> Parameters!EmployeeID.Value + "))"
> you could also make a table valued function which parses the parameter and
> returns a table which contains one row for each employee id. YOu then join
> that to the employees table
>
> --
> 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
> "Malli" <Malli@.discussions.microsoft.com> wrote in message
> news:E7538F08-2BA3-4590-8FE6-B84C3EB96BC3@.microsoft.com...
> > Hi,
> > I am facing problem in reporting services using parameter field.I am
> > passing values to parameter field empids like '10,12'.I want the data of
> > employee 10 and 12.while creating the parameter how can i mention.In t-sql
> > empid IN @.eID is it correct,how can i mention in parameter field.
> >
> >
> > Thanks in advance
> >
> > Malli.
>
>

Parameters Help!

I am passing parameters in my report. When I bring up the reports it shows
all 15 parameters. I know there is a hide parameters button but is there a
way to change the default view in Report Manager so that the parameters
aren't displayed when the report pops up?
Thanks in advance
osSure, you can also set default parameter values as well.
Log in as someone that has admin privileges on the Report Manager
(http://localhost/reports)
Go to the report in question.
Select the properties page.
Select the parameters link on the left.
Set the appropriate defaults/visibility.
"OriginalStealth" <OriginalStealth@.discussions.microsoft.com> wrote in
message news:02467DA9-8605-4E63-A21F-FFAB5912DEA5@.microsoft.com...
>I am passing parameters in my report. When I bring up the reports it shows
> all 15 parameters. I know there is a hide parameters button but is there
> a
> way to change the default view in Report Manager so that the parameters
> aren't displayed when the report pops up?
> Thanks in advance
> os|||Another alternative is to remove the "Prompt" inside the report parameter
definition
This way your parameter will be hidden
--
Best Regards
Maria Sartzetaki
"OriginalStealth" wrote:
> I am passing parameters in my report. When I bring up the reports it shows
> all 15 parameters. I know there is a hide parameters button but is there a
> way to change the default view in Report Manager so that the parameters
> aren't displayed when the report pops up?
> Thanks in advance
> os

Parameters and passing values

I am trying to pass two parameters to a stored procedure that works fine whe
n
I use the code, for example:
param_user.Value = "blahblah@.com"
The problem is the value I need is in a variable, but when I use:
param_user.Value = variable name
I get no records returned.
I cant seem to be able to pass the variable into the param.value...any ideas
?
Thanks in advanceSee replies in
microsoft.public.sqlserver.programming
John
"free70@.community.nospam"
<free70@.community.nospam@.discussions.microsoft.com> wrote in message
news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>I am trying to pass two parameters to a stored procedure that works fine
>when
> I use the code, for example:
> param_user.Value = "blahblah@.com"
> The problem is the value I need is in a variable, but when I use:
> param_user.Value = variable name
> I get no records returned.
> I cant seem to be able to pass the variable into the param.value...any
> ideas?
> Thanks in advance
>|||Thanks John - I posted to the wrong group...
"John Bell" wrote:

> See replies in
> microsoft.public.sqlserver.programming
>
> John
> "free70@.community.nospam"
> <free70@.community.nospam@.discussions.microsoft.com> wrote in message
> news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>
>

Parameters and passing values

I am trying to pass two parameters to a stored procedure that works fine when
I use the code, for example:
param_user.Value = "blahblah@.com"
The problem is the value I need is in a variable, but when I use:
param_user.Value = variable name
I get no records returned.
I cant seem to be able to pass the variable into the param.value...any ideas?
Thanks in advance
See replies in
microsoft.public.sqlserver.programming
John
"free70@.community.nospam"
<free70@.community.nospam@.discussions.microsoft.com > wrote in message
news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>I am trying to pass two parameters to a stored procedure that works fine
>when
> I use the code, for example:
> param_user.Value = "blahblah@.com"
> The problem is the value I need is in a variable, but when I use:
> param_user.Value = variable name
> I get no records returned.
> I cant seem to be able to pass the variable into the param.value...any
> ideas?
> Thanks in advance
>
|||Thanks John - I posted to the wrong group...
"John Bell" wrote:

> See replies in
> microsoft.public.sqlserver.programming
>
> John
> "free70@.community.nospam"
> <free70@.community.nospam@.discussions.microsoft.com > wrote in message
> news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>
>