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

Partitioning an existing table

I am running SQL Server 2005 and am interested in partitioning a multi-
million row table, that contains a clustered index (which is comprised of two
columns), but the partitioning key is not part of that clustered index.
I have read about partitioning using ALTER TABLE on BOL and have searched the
web for examples of partitioning existing tables, but have had no success.
The only true examples I have come across use a CREATE TABLE statement. I
assume the ALTER TABLE would contain such a mechanism, but apparently I do
not understand. Is this possible using the ALTER TABLE statement where the
partitioning key is not part of the clustered index?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200706/1Hi cbrichards
The way to partition an existing table is to rebuild the clustered index on
a partition scheme. If the index is unique, partition keys must be a subset
of the index keys. So since you are rebuilding the index anyway, you can
redefine it to include the partitioning keys, or to make it nonunique. The
index rebuild would look something like this:
CREATE UNIQUE CLUSTERED INDEX your_index_name ON your_table
(original_index_key1, origininal_index_key2, partitioning_column)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
-- OR --
CREATE CLUSTERED INDEX your_index_name ON your_table (original_index_key1,
origininal_index_key2)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:73b0eb04f61ce@.uwe...
>I am running SQL Server 2005 and am interested in partitioning a multi-
> million row table, that contains a clustered index (which is comprised of
> two
> columns), but the partitioning key is not part of that clustered index.
> I have read about partitioning using ALTER TABLE on BOL and have searched
> the
> web for examples of partitioning existing tables, but have had no success.
> The only true examples I have come across use a CREATE TABLE statement. I
> assume the ALTER TABLE would contain such a mechanism, but apparently I do
> not understand. Is this possible using the ALTER TABLE statement where the
> partitioning key is not part of the clustered index?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200706/1
>

Partitioning an existing table

I am running SQL Server 2005 and am interested in partitioning a multi-
million row table, that contains a clustered index (which is comprised of two
columns), but the partitioning key is not part of that clustered index.
I have read about partitioning using ALTER TABLE on BOL and have searched the
web for examples of partitioning existing tables, but have had no success.
The only true examples I have come across use a CREATE TABLE statement. I
assume the ALTER TABLE would contain such a mechanism, but apparently I do
not understand. Is this possible using the ALTER TABLE statement where the
partitioning key is not part of the clustered index?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200706/1
Hi cbrichards
The way to partition an existing table is to rebuild the clustered index on
a partition scheme. If the index is unique, partition keys must be a subset
of the index keys. So since you are rebuilding the index anyway, you can
redefine it to include the partitioning keys, or to make it nonunique. The
index rebuild would look something like this:
CREATE UNIQUE CLUSTERED INDEX your_index_name ON your_table
(original_index_key1, origininal_index_key2, partitioning_column)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
-- OR --
CREATE CLUSTERED INDEX your_index_name ON your_table (original_index_key1,
origininal_index_key2)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:73b0eb04f61ce@.uwe...
>I am running SQL Server 2005 and am interested in partitioning a multi-
> million row table, that contains a clustered index (which is comprised of
> two
> columns), but the partitioning key is not part of that clustered index.
> I have read about partitioning using ALTER TABLE on BOL and have searched
> the
> web for examples of partitioning existing tables, but have had no success.
> The only true examples I have come across use a CREATE TABLE statement. I
> assume the ALTER TABLE would contain such a mechanism, but apparently I do
> not understand. Is this possible using the ALTER TABLE statement where the
> partitioning key is not part of the clustered index?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200706/1
>

Partitioning an existing table

I am running SQL Server 2005 and am interested in partitioning a multi-
million row table, that contains a clustered index (which is comprised of tw
o
columns), but the partitioning key is not part of that clustered index.
I have read about partitioning using ALTER TABLE on BOL and have searched th
e
web for examples of partitioning existing tables, but have had no success.
The only true examples I have come across use a CREATE TABLE statement. I
assume the ALTER TABLE would contain such a mechanism, but apparently I do
not understand. Is this possible using the ALTER TABLE statement where the
partitioning key is not part of the clustered index?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200706/1Hi cbrichards
The way to partition an existing table is to rebuild the clustered index on
a partition scheme. If the index is unique, partition keys must be a subset
of the index keys. So since you are rebuilding the index anyway, you can
redefine it to include the partitioning keys, or to make it nonunique. The
index rebuild would look something like this:
CREATE UNIQUE CLUSTERED INDEX your_index_name ON your_table
(original_index_key1, origininal_index_key2, partitioning_column)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
-- OR --
CREATE CLUSTERED INDEX your_index_name ON your_table (original_index_key1,
origininal_index_key2)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:73b0eb04f61ce@.uwe...
>I am running SQL Server 2005 and am interested in partitioning a multi-
> million row table, that contains a clustered index (which is comprised of
> two
> columns), but the partitioning key is not part of that clustered index.
> I have read about partitioning using ALTER TABLE on BOL and have searched
> the
> web for examples of partitioning existing tables, but have had no success.
> The only true examples I have come across use a CREATE TABLE statement. I
> assume the ALTER TABLE would contain such a mechanism, but apparently I do
> not understand. Is this possible using the ALTER TABLE statement where the
> partitioning key is not part of the clustered index?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200706/1
>

Parellism not working

We have migrated databases from SQL7 to SQL2000 Standard Edition. Now,
when we run queries against the newer hardware running SQL2000 we
notice that the execution plan shows no parallelism taking place. The
SQL2000 config. has 2 processors and both procs are enabled in the
Processor tab.(use all available processors is selected). I've
upgraded the install to SQL2000 SP3a but there is no change. Does
anyone know what may be going on here?

Thanks,
GCMost queries will not benefit from parallelism. The optimizer will
choose a parallel plan only if it makes sense for a particular query and
the server is not overtaxed.

In many cases, a parallel plan is an indication that you need to do some
index tuning.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0311180934.6f7b8540@.posting.google.c om...
> We have migrated databases from SQL7 to SQL2000 Standard Edition. Now,
> when we run queries against the newer hardware running SQL2000 we
> notice that the execution plan shows no parallelism taking place. The
> SQL2000 config. has 2 processors and both procs are enabled in the
> Processor tab.(use all available processors is selected). I've
> upgraded the install to SQL2000 SP3a but there is no change. Does
> anyone know what may be going on here?
> Thanks,
> GC

parameters show previous value on second execution of report

I'm running a crystal report from c#.net application, taking parameters from textboxes on the screen, using the parameters to do the data selection, and then printing the parameters in the heading of the report.

The first time I run the report, it displays properly, with the correct info in the heading. If I change the parameters and execute it again, the data on the report changes, but the heading still shows the original parameters that I entered.

Has anyone else encountered a problem like this? Or can you suggest a solution?

Thanks,
ChrisPost the code you are using for displaying the report.|||I solved this already by using a trick that fixes another crystal problem with the navigation end page button: I commented the code in the navigate method, and called my PopulateReport method from the page load event.

I don't know why this works, but it solved the problem.

Parameters failing when supplied by a query and set up to execute

I have a query that provides a list of detachments that are used as a parameter for the user to select when running a report.

It works fine.

I want to set up this report to also run on a schedule.

When I set the prameters on the report for any value other than the first one in the list it fails to execute with the following error.

Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError) (there are no other details provided)

The query results are

All
A
B
C
D
E
G
H
I
N
V
W
X

I get this error if I input a value in the list other than All or any value that is not in the list. Any suggestions?

We are running Report Services 2000 SP1

Many thanks Ayla

All,

After additional testing, I have removed the parameter validation and can get the report to work. I hope this is fixed in a future release.

Ayla

|||

please use reporting services SP2... I think it will solve the problem.

dogu

|||

How did you remove the parameter validation?

Penn

|||I think he means remove the configuation of Avaliable values, I just fixed the problem. cheers!!

Parameters failing when supplied by a query and set up to execute

I have a query that provides a list of detachments that are used as a parameter for the user to select when running a report.

It works fine.

I want to set up this report to also run on a schedule.

When I set the prameters on the report for any value other than the first one in the list it fails to execute with the following error.

Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError) (there are no other details provided)

The query results are

All
A
B
C
D
E
G
H
I
N
V
W
X

I get this error if I input a value in the list other than All or any value that is not in the list. Any suggestions?

We are running Report Services 2000 SP1

Many thanks Ayla

All,

After additional testing, I have removed the parameter validation and can get the report to work. I hope this is fixed in a future release.

Ayla

|||

please use reporting services SP2... I think it will solve the problem.

dogu

|||

How did you remove the parameter validation?

Penn

|||I think he means remove the configuation of Avaliable values, I just fixed the problem. cheers!!

Parameters failing when supplied by a query and set up to execute

I have a query that provides a list of detachments that are used as a parameter for the user to select when running a report.

It works fine.

I want to set up this report to also run on a schedule.

When I set the prameters on the report for any value other than the first one in the list it fails to execute with the following error.

Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError) (there are no other details provided)

The query results are

All
A
B
C
D
E
G
H
I
N
V
W
X

I get this error if I input a value in the list other than All or any value that is not in the list. Any suggestions?

We are running Report Services 2000 SP1

Many thanks Ayla

All,

After additional testing, I have removed the parameter validation and can get the report to work. I hope this is fixed in a future release.

Ayla

|||

please use reporting services SP2... I think it will solve the problem.

dogu

|||

How did you remove the parameter validation?

Penn

|||I think he means remove the configuation of Avaliable values, I just fixed the problem. cheers!!

Parameters failing when supplied by a query and set up to execute

I have a query that provides a list of detachments that are used as a parameter for the user to select when running a report.

It works fine.

I want to set up this report to also run on a schedule.

When I set the prameters on the report for any value other than the first one in the list it fails to execute with the following error.

Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError) (there are no other details provided)

The query results are

All
A
B
C
D
E
G
H
I
N
V
W
X

I get this error if I input a value in the list other than All or any value that is not in the list. Any suggestions?

We are running Report Services 2000 SP1

Many thanks Ayla

All,

After additional testing, I have removed the parameter validation and can get the report to work. I hope this is fixed in a future release.

Ayla

|||

please use reporting services SP2... I think it will solve the problem.

dogu

|||

How did you remove the parameter validation?

Penn

|||I think he means remove the configuation of Avaliable values, I just fixed the problem. cheers!!

Parameters

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report work fine, but when I select more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

OK, do you want us to read the whole query ? As you are more involved in the logic of the query I would suggest you turning on the profile to see what is actually fired against the SQL Server database. But as from a first view you are using multivalue parameters like singlevalue and multivalue together in @.pzona = 0 and (@.pzona in @.Pzona whatever that means)

Using the profiler will help you to find your answer to that syntax problem.

HTH, Jens K. Suessmeyer.

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

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report everything work fine, but when I chose more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

|||

You need to make sure that your SQL query in the DataSet uses the "IN" clause instead of "=".

For ex.

Select ColumnA,ColumnB,ColumnC from MyTable

where ColumnA in (@.Parameter)

|||

That error could mean that you are trying to use the IN syntax but the parameter values are of character/string type.

If so, please see this thread -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1 -- it's a little involved, but you need to split the set of multiple parameters up and put them back together with string delimiters...

>L<

Parameters

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report work fine, but when I select more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

OK, do you want us to read the whole query ? As you are more involved in the logic of the query I would suggest you turning on the profile to see what is actually fired against the SQL Server database. But as from a first view you are using multivalue parameters like singlevalue and multivalue together in @.pzona = 0 and (@.pzona in @.Pzona whatever that means)

Using the profiler will help you to find your answer to that syntax problem.

HTH, Jens K. Suessmeyer.

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

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report everything work fine, but when I chose more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

|||

You need to make sure that your SQL query in the DataSet uses the "IN" clause instead of "=".

For ex.

Select ColumnA,ColumnB,ColumnC from MyTable

where ColumnA in (@.Parameter)

|||

That error could mean that you are trying to use the IN syntax but the parameter values are of character/string type.

If so, please see this thread -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1 -- it's a little involved, but you need to split the set of multiple parameters up and put them back together with string delimiters...

>L<

Parameters

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report work fine, but when I select more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

OK, do you want us to read the whole query ? As you are more involved in the logic of the query I would suggest you turning on the profile to see what is actually fired against the SQL Server database. But as from a first view you are using multivalue parameters like singlevalue and multivalue together in @.pzona = 0 and (@.pzona in @.Pzona whatever that means)

Using the profiler will help you to find your answer to that syntax problem.

HTH, Jens K. Suessmeyer.

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

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report everything work fine, but when I chose more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

|||

You need to make sure that your SQL query in the DataSet uses the "IN" clause instead of "=".

For ex.

Select ColumnA,ColumnB,ColumnC from MyTable

where ColumnA in (@.Parameter)

|||

That error could mean that you are trying to use the IN syntax but the parameter values are of character/string type.

If so, please see this thread -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1 -- it's a little involved, but you need to split the set of multiple parameters up and put them back together with string delimiters...

>L<