re: This recordset is not updatable

Hi all,

I created a form that links to a view. The view joins the two tables and both of these tables are defined w/ Primary keys. But when I try to update the table is giving an error in Access form: "This recordset is not updatable".
Would anyone have an explanation for this?
I'm using AC2K and SQL Server 2K.
Thanks!You can only update columns in the view from one table with one update statement. You can only update columns if you have all of the columns in the declared PK for the table in the view.

-PatP

RDA tracking - no primary key on table

Hello,

Can a table be tracked if it does not have a primary key? I am using rda.Pull method in VS2005 using C#. If not, what are my options? Any help is appreciated.

Thanks.

A primary is required if using RDA tracking. See http://msdn2.microsoft.com/en-us/library/ms172971.aspx

"A primary key must be defined on the updatable recordset returned by the SELECT statement. "

RDA Push Command Failing ...

Hi,

I am using the Pull command to pull two fields, on is the primary ID (int) non identity and the other is Description which comes down as an ntext type. This works fine but if I change the description and use the push command I get the following error:-

The Query processor could not produce a query from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

I am really stuck with this one so if anyone can shed some light on it I would be much appreciated.

Cheers,

Jiggy!

Hi All,

I have found the problem. Basically I am intergrating with another desktop / web based application making a PDA version so the SQL Server Database is already in place. I have found that each table has a cluster index on the primary key for performance issues. Can someone please advice if there is a fix for this or is the Push command not compatible with Clustered Index's?

Cheers very much,

Jiggy!

|||Just so you do not feel alone in the world... and MAYBE point you in a useful direction...

This has to do with the parameters being set by the connection, most likely. The way to test this is to run the same SQL statement in Query Analyzer and see if it works, if it does, then you need to attempt to set different paramters on your connection and/or command.

Hope this helps a little, if you find the solution, and/or paramters to pass, I'd love to hear about it.

-Joshua

RDA Push Command Failing ...

Hi,

I am using the Pull command to pull two fields, on is the primary ID (int) non identity and the other is Description which comes down as an ntext type. This works fine but if I change the description and use the push command I get the following error:-

The Query processor could not produce a query from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

I am really stuck with this one so if anyone can shed some light on it I would be much appreciated.

Cheers,

Jiggy!

Hi All,

I have found the problem. Basically I am intergrating with another desktop / web based application making a PDA version so the SQL Server Database is already in place. I have found that each table has a cluster index on the primary key for performance issues. Can someone please advice if there is a fix for this or is the Push command not compatible with Clustered Index's?

Cheers very much,

Jiggy!

|||Just so you do not feel alone in the world... and MAYBE point you in a useful direction...

This has to do with the parameters being set by the connection, most likely. The way to test this is to run the same SQL statement in Query Analyzer and see if it works, if it does, then you need to attempt to set different paramters on your connection and/or command.

Hope this helps a little, if you find the solution, and/or paramters to pass, I'd love to hear about it.

-Joshua

rda dropping primary key index

I am using RDA to download a tables

I use TrackingOffWithIndexes

the issue is that when I download a small set of data the primary key index is there

when I download a larger set. the index gets dropped (no error) just slowness when querying the table

the max database size is set to 1024mb and the temp database size is set to 1024mb

the actual db size (with the missing index) is 262 mb... lot's of room left!

when I try to create the index manually after the download in sql server management studio I get this error:

Not enough storage is available to complete this operation

I am using 2GB sd cards and nothing else is on the card.

any ideas?

Regards,

eric [at] westgen com

This indicates an out of error condition, make sure you dispose of the RDA objects before creating the index in code.|||

Thanks for the reply but

when running the code I get NO error... just a missing index

the index should come down automatically as long as it can fit in the db size... but it does not.. it only comes down whith a smaller set of data.

The error I get when creating the index manually is in sql server management studio... and there is no rda object to get rid of

can you please explain your reply a little more

Regards,

eric

|||How wide is your index, please detail schema and create index statement. Do you create the index with the sdf file on your hard disk or on the SD card?|||

Hi,

The table could have in excess of 1 million rows... I am not sure at how many it decides not to download the index/primary key constraint but it seems to be around the point where the database reaches 150mb although as stated previously the max size and max temp file size are set to 1024 mb

The table structure is:

Bull_id int

Cow_id int

Inbreeding_Value real

the primary key is bull_id, cow_id

I used this script to try to manually apply the index/key

ALTER TABLE INBREEDING ADD
CONSTRAINT PK_INBREEDING PRIMARY KEY
(
COW_Id,
BULL_Id
)

the db was on the device at the time I did not try from the desktop

the goal here is not to do this manually but to have the index be downloaded with the rda pull command

Thanks a lot for your help!!!

regards,

ric

|||You may be hitting a limitation in SQL CE, like max table size = 512 MB? For limits, see SQL CE BOL: Database Objects|||

Thanks again Erik

I checked the sql mobile limitations... and according to the docs the table size limit is the same as the db size limit.

I didn't see any other limits that seemed relevant

I will continue my search for an answer/solution

Regards,

ric

|||

Hi Erik,

I want to correct my previous post

you are right... there is a 512 mb size limit on tables

I will investigate on wether or not this is the cause of my issue

thanks again

ric

|||

based on my calcs

5335074 rows has a table size of 120mb even if the index took twice as much memory as the rows the total size would still be under the 512mb limit

so I still can't explain why RDA pull is dropping the primary key/index when the table reaches a certain number of rows

Regards,

ric

|||If you can provide sample data and you Pull code off-line (see my email i my profile), then I can see if I can reproduce, if so it may be a case for Microsoft to fix.|||

Hi..

I have meet the same problem, too.

I create a table with 1,380,000 rows data,

the db real size around 114 MB.

The primary key size is nchar(6).

When I use RDA pull, I found that the primary

key in the PDA disappear. So, It took a long time

to get query response.

But when I delete some rows to 680,000 rows of data.

After I pull, The primary key can pull from the SQL Server.

PS: I didn't change any code. Just delete some rows.

Is that SQL-Mobile's bug!!!

VIckrey

|||Maybe a connection timeout problem, see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1624137&SiteID=1|||

ErikEJ wrote:

Maybe a connection timeout problem, see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1624137&SiteID=1[/quote]

No, there is no any error.

HRESULT=S_OK..!@.#$

|||

Vickrey,

Have you found a solution?

I still can't get mine to work with so many rows

I am convinced this is a sql mobile bug

I cannot even add the key back (with code) once the rows are downloaded

There must be some max row bug when the table has a key.

Please let me know if you come across anything.

Regards,

eric

rda dropping primary key index

I am using RDA to download a tables

I use TrackingOffWithIndexes

the issue is that when I download a small set of data the primary key index is there

when I download a larger set. the index gets dropped (no error) just slowness when querying the table

the max database size is set to 1024mb and the temp database size is set to 1024mb

the actual db size (with the missing index) is 262 mb... lot's of room left!

when I try to create the index manually after the download in sql server management studio I get this error:

Not enough storage is available to complete this operation

I am using 2GB sd cards and nothing else is on the card.

any ideas?

Regards,

eric [at] westgen com

This indicates an out of error condition, make sure you dispose of the RDA objects before creating the index in code.|||

Thanks for the reply but

when running the code I get NO error... just a missing index

the index should come down automatically as long as it can fit in the db size... but it does not.. it only comes down whith a smaller set of data.

The error I get when creating the index manually is in sql server management studio... and there is no rda object to get rid of

can you please explain your reply a little more

Regards,

eric

|||How wide is your index, please detail schema and create index statement. Do you create the index with the sdf file on your hard disk or on the SD card?|||

Hi,

The table could have in excess of 1 million rows... I am not sure at how many it decides not to download the index/primary key constraint but it seems to be around the point where the database reaches 150mb although as stated previously the max size and max temp file size are set to 1024 mb

The table structure is:

Bull_id int

Cow_id int

Inbreeding_Value real

the primary key is bull_id, cow_id

I used this script to try to manually apply the index/key

ALTER TABLE INBREEDING ADD
CONSTRAINT PK_INBREEDING PRIMARY KEY
(
COW_Id,
BULL_Id
)

the db was on the device at the time I did not try from the desktop

the goal here is not to do this manually but to have the index be downloaded with the rda pull command

Thanks a lot for your help!!!

regards,

ric

|||You may be hitting a limitation in SQL CE, like max table size = 512 MB? For limits, see SQL CE BOL: Database Objects|||

Thanks again Erik

I checked the sql mobile limitations... and according to the docs the table size limit is the same as the db size limit.

I didn't see any other limits that seemed relevant

I will continue my search for an answer/solution

Regards,

ric

|||

Hi Erik,

I want to correct my previous post

you are right... there is a 512 mb size limit on tables

I will investigate on wether or not this is the cause of my issue

thanks again

ric

|||

based on my calcs

5335074 rows has a table size of 120mb even if the index took twice as much memory as the rows the total size would still be under the 512mb limit

so I still can't explain why RDA pull is dropping the primary key/index when the table reaches a certain number of rows

Regards,

ric

|||If you can provide sample data and you Pull code off-line (see my email i my profile), then I can see if I can reproduce, if so it may be a case for Microsoft to fix.|||

Hi..

I have meet the same problem, too.

I create a table with 1,380,000 rows data,

the db real size around 114 MB.

The primary key size is nchar(6).

When I use RDA pull, I found that the primary

key in the PDA disappear. So, It took a long time

to get query response.

But when I delete some rows to 680,000 rows of data.

After I pull, The primary key can pull from the SQL Server.

PS: I didn't change any code. Just delete some rows.

Is that SQL-Mobile's bug!!!

VIckrey

|||Maybe a connection timeout problem, see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1624137&SiteID=1|||

ErikEJ wrote:

Maybe a connection timeout problem, see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1624137&SiteID=1[/quote]

No, there is no any error.

HRESULT=S_OK..!@.#$

|||

Vickrey,

Have you found a solution?

I still can't get mine to work with so many rows

I am convinced this is a sql mobile bug

I cannot even add the key back (with code) once the rows are downloaded

There must be some max row bug when the table has a key.

Please let me know if you come across anything.

Regards,

eric

rda dropping primary key index

I am using RDA to download a tables

I use TrackingOffWithIndexes

the issue is that when I download a small set of data the primary key index is there

when I download a larger set. the index gets dropped (no error) just slowness when querying the table

the max database size is set to 1024mb and the temp database size is set to 1024mb

the actual db size (with the missing index) is 262 mb... lot's of room left!

when I try to create the index manually after the download in sql server management studio I get this error:

Not enough storage is available to complete this operation

I am using 2GB sd cards and nothing else is on the card.

any ideas?

Regards,

eric [at] westgen com

This indicates an out of error condition, make sure you dispose of the RDA objects before creating the index in code.|||

Thanks for the reply but

when running the code I get NO error... just a missing index

the index should come down automatically as long as it can fit in the db size... but it does not.. it only comes down whith a smaller set of data.

The error I get when creating the index manually is in sql server management studio... and there is no rda object to get rid of

can you please explain your reply a little more

Regards,

eric

|||How wide is your index, please detail schema and create index statement. Do you create the index with the sdf file on your hard disk or on the SD card?|||

Hi,

The table could have in excess of 1 million rows... I am not sure at how many it decides not to download the index/primary key constraint but it seems to be around the point where the database reaches 150mb although as stated previously the max size and max temp file size are set to 1024 mb

The table structure is:

Bull_id int

Cow_id int

Inbreeding_Value real

the primary key is bull_id, cow_id

I used this script to try to manually apply the index/key

ALTER TABLE INBREEDING ADD
CONSTRAINT PK_INBREEDING PRIMARY KEY
(
COW_Id,
BULL_Id
)

the db was on the device at the time I did not try from the desktop

the goal here is not to do this manually but to have the index be downloaded with the rda pull command

Thanks a lot for your help!!!

regards,

ric

|||You may be hitting a limitation in SQL CE, like max table size = 512 MB? For limits, see SQL CE BOL: Database Objects|||

Thanks again Erik

I checked the sql mobile limitations... and according to the docs the table size limit is the same as the db size limit.

I didn't see any other limits that seemed relevant

I will continue my search for an answer/solution

Regards,

ric

|||

Hi Erik,

I want to correct my previous post

you are right... there is a 512 mb size limit on tables

I will investigate on wether or not this is the cause of my issue

thanks again

ric

|||

based on my calcs

5335074 rows has a table size of 120mb even if the index took twice as much memory as the rows the total size would still be under the 512mb limit

so I still can't explain why RDA pull is dropping the primary key/index when the table reaches a certain number of rows

Regards,

ric

|||If you can provide sample data and you Pull code off-line (see my email i my profile), then I can see if I can reproduce, if so it may be a case for Microsoft to fix.|||

Hi..

I have meet the same problem, too.

I create a table with 1,380,000 rows data,

the db real size around 114 MB.

The primary key size is nchar(6).

When I use RDA pull, I found that the primary

key in the PDA disappear. So, It took a long time

to get query response.

But when I delete some rows to 680,000 rows of data.

After I pull, The primary key can pull from the SQL Server.

PS: I didn't change any code. Just delete some rows.

Is that SQL-Mobile's bug!!!

VIckrey

|||Maybe a connection timeout problem, see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1624137&SiteID=1|||

ErikEJ wrote:

Maybe a connection timeout problem, see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1624137&SiteID=1[/quote]

No, there is no any error.

HRESULT=S_OK..!@.#$

|||

Vickrey,

Have you found a solution?

I still can't get mine to work with so many rows

I am convinced this is a sql mobile bug

I cannot even add the key back (with code) once the rows are downloaded

There must be some max row bug when the table has a key.

Please let me know if you come across anything.

Regards,

eric

RB - Logical Primary Key

There is a Set Logical Primary Key option in DSV. Curious as to what is the Logical Primary Key. What is it behind the scene?Where does the logical PK reside? Is there going to be a performance difference using the logical PK?

Raw File Source issue

I have a single file that contains records destined for multiple tables. The "first" record is considered primary and the other records are considered "secondary" (meaning that they have foreign keys to the primary table).

In order to properly insert this I needed to use two data flows. The first data flow directed the primary rows to the primary table and the secondary rows get directed to a raw file destination. The second data flow read in from the raw file and wrote out the rows to the appropriate tables.

But here is my problem.

This darn validation! While I think validation is a great idea, the extensive use of it in what seems like EVERY aspect of SSIS seems to cause more headaches than not...

When I deploy my package and try to run it I get an error because the raw file source DOES NOT EXIST. Of course it does not exist, it gets created when the package runs... I cannot deploy something that does not exist yet.

I even have a problem while I am trying to work with the package in VS. The only way to get the package to run is to disable the second data flow so it does not try to validate it. Run the package so the raw file is created. And then re-enable the second data flow again. (Which then I guess I could take the raw file and deploy it with my package but that just seems silly.... deploying temporary files... that would be like deploying Internet Explorer with the Temporary Internet Files folders....)

And of course with that type of solution my package could never "clean up" after itself...

Try setting DelayValidation to TRUE for all source / destination components

Thanks,
Sankaranarayanan MG

|||I have used DelayValidation on other objects but I do not see any property of that sort when looking at the Raw File Source. The only thing I see with the word valid is ValidateExternalMetadata. Should I be looking elsewhere?|||

Yes, DelayValidation is a task property not a component property so you would need to set it on the DataFlow task that contains the component you need to have validation delayed on. Note that this delays the validation for all the components in the task not just the one component you need it for.

HTH,

Matt