Parameter entered has the wrong format.. you must be ....me!

Hi

I have the problem that the below defined paramter gets entered in the database as a interger. the Field in the DB is a nvarchar(5) and the controll that suplies the value is a TextBox

this is the parameter definition:
<asp:ControlParameterControlID="tbComment"Name="Comment"PropertyName="Text"Type="String"/>

Why do I get this error, why does ASP to whant to make an integerfrom this text field? When putting a interger value in the textbox all works well and the data gets posted to the database.

I use aSqlDataSource with automatic generated script.

look forwart to a solution

walter

Do you use a stored procedure or ...? make sure the parameter (comment) is not passed to a parameter of type int.

parallel execution

Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it

Regards
MThe easiest way is probably to have 3 copies of your Data Flow, each using a different File Connection Manager, and three copies of your File Connection Manager, each for a different file. You will also need to uncheck the "Table Lock" option on the destinations. If there are no precedences defined for your Data Flows, they will execute in parallel. Ugly, but simple.
There used to be a parallel execution option on the Loop task that would have allowed you to do this with one Data Flow, but it was removed.
It is also possible to use a master package that executes your current package three times in parallel, thus avoiding the Data Flow duplication above, but you'll face the challenge of having each instance of the package open a separate file. You would need to have three different Package Configuration files to define different values for a parameter that you would use in an Expression to set the ConnectionString property on your File Connection Manager. Complicated, but probably a superior design if you're up for it. You'll still need to turn off table locking.
Note that you'll need multiple processors to make this a worthwhile endeavor. Also, if your files are very large tempdb will suffer without the table locking.|||
Note that you'll need multiple processors to make this a worthwhile endeavor. Also, if your files are very large tempdb will suffer without the table locking.

Hey Jay,
Is it mandatory to have multiple processors for parallel execution for the above mentioned issue.
I have done all the above things that you have suggested, but it stills seems that the loading is happening sequentially. Please note that the my machine is single processor machine.

Regards
Meghana|||It shouldn't be mandatory, but I wouldn't expect you to see any benefits without multiple processors. I ran a test on a single processor machine and did not have any difficulty getting my Data Flows to run concurrently. I also discovered that disabling the table locking is not necessary.
One thing to check is the MaxConcurrentExecutables property. The only way I could force my package to run sequentially is to set this value to 1. The default value is -1, which means number of processors plus two, so it should let you load three files simultaneously.
I also tested performance. Using a 100k line flat file going directly into a local SQL table, I saw a 10% decrease in performance with two parallel files and 34% decrease with three.
|||

mmhaise wrote:

Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it

Regards
M

Have you tried using a MULTIFLATFILE connection manager? This will enable you to load data from all of the files using just a single data-flow (which is executed just once).
If the metadata of the 3 files is identical this should work very nicely.

If the metadata is different then you can use 2 or 3 Derived Column Transforms to make the pipeline metadata identical and then use a UNION ALL transform to combine the 3 data-paths into one. Again, this is a single data-flow that you execute just the once.
If you are intent on loading the 3 seperately then executing a package 3 times as Jay suggested would be the "cleanest" approach because you can reuse functionality rather than having 3 seperate but identical data-flows.

I would definately look into using the MULTIFLATFILE connection manager though if I were you.

-Jamie|||

Is it possible to make the MULTIFLATFILE connection load in parallel? I have tried using one to load 4 csv file in parallel to the same destination table. The data flow is loading them sequentially.

I have another package with four separate file connection managers and data flows; however, I thought it would be nice to use the MULTIFLATFILE since the files have identical metadata. We require them to load in parallel.

parallel execution

Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it

Regards
MThe easiest way is probably to have 3 copies of your Data Flow, each using a different File Connection Manager, and three copies of your File Connection Manager, each for a different file. You will also need to uncheck the "Table Lock" option on the destinations. If there are no precedences defined for your Data Flows, they will execute in parallel. Ugly, but simple.
There used to be a parallel execution option on the Loop task that would have allowed you to do this with one Data Flow, but it was removed.
It is also possible to use a master package that executes your current package three times in parallel, thus avoiding the Data Flow duplication above, but you'll face the challenge of having each instance of the package open a separate file. You would need to have three different Package Configuration files to define different values for a parameter that you would use in an Expression to set the ConnectionString property on your File Connection Manager. Complicated, but probably a superior design if you're up for it. You'll still need to turn off table locking.
Note that you'll need multiple processors to make this a worthwhile endeavor. Also, if your files are very large tempdb will suffer without the table locking.|||
Note that you'll need multiple processors to make this a worthwhile endeavor. Also, if your files are very large tempdb will suffer without the table locking.

Hey Jay,
Is it mandatory to have multiple processors for parallel execution for the above mentioned issue.
I have done all the above things that you have suggested, but it stills seems that the loading is happening sequentially. Please note that the my machine is single processor machine.

Regards
Meghana|||It shouldn't be mandatory, but I wouldn't expect you to see any benefits without multiple processors. I ran a test on a single processor machine and did not have any difficulty getting my Data Flows to run concurrently. I also discovered that disabling the table locking is not necessary.
One thing to check is the MaxConcurrentExecutables property. The only way I could force my package to run sequentially is to set this value to 1. The default value is -1, which means number of processors plus two, so it should let you load three files simultaneously.
I also tested performance. Using a 100k line flat file going directly into a local SQL table, I saw a 10% decrease in performance with two parallel files and 34% decrease with three.
|||

mmhaise wrote:

Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it

Regards
M

Have you tried using a MULTIFLATFILE connection manager? This will enable you to load data from all of the files using just a single data-flow (which is executed just once).
If the metadata of the 3 files is identical this should work very nicely.

If the metadata is different then you can use 2 or 3 Derived Column Transforms to make the pipeline metadata identical and then use a UNION ALL transform to combine the 3 data-paths into one. Again, this is a single data-flow that you execute just the once.
If you are intent on loading the 3 seperately then executing a package 3 times as Jay suggested would be the "cleanest" approach because you can reuse functionality rather than having 3 seperate but identical data-flows.

I would definately look into using the MULTIFLATFILE connection manager though if I were you.

-Jamie|||

Is it possible to make the MULTIFLATFILE connection load in parallel? I have tried using one to load 4 csv file in parallel to the same destination table. The data flow is loading them sequentially.

I have another package with four separate file connection managers and data flows; however, I thought it would be nice to use the MULTIFLATFILE since the files have identical metadata. We require them to load in parallel.

Pagination of data

Hi
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
RegardsTake a look at this article.
http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
--|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
David Portas
SQL Server MVP
--|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>|||Check out:
http://www.aspfaq.com/show.asp?id=2120
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>

Pagination of data

Hi
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
Regards
Take a look at this article.
http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>
|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
David Portas
SQL Server MVP
|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
|||Check out:
http://www.aspfaq.com/show.asp?id=2120
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>

pagination advice

Hi

I'm still quite new to MSSQL so excuse the trivial questions.
I've already tried searching through the forums on pagination as im sure its a big subject but couldn't find the answers i'm after.

I'm using MSSQL 2000 and asp .net 2.0 and basically need to paginate my results. My database contains roughly 250,000 rows of data, and one query would approximately return 30 results at the most. I would want to have 5 results per page.

I'm starting from scratch so was wondering what technique i could use. I can't use OFFSET as thats MySQL, is there an equivalent?
I've heard mixed opinions on Cursors, but so far thats the only way i can see at the moment! :S

thanksNo cursors, please.

Use a query that assigns an ordinal value to each record in the recordset (you will need a unique column or group of columns by which you can reliably order the results).

Then have your interface submit a request for a result set returning only rows N through M.