dts error

  • the jobs faile ddue to

    Error string: Could not run BEGIN TRANSACTION in database 'Portalreport' because the database is read-only. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 3906 (F42) Error string: Could not run BEGIN TRANSACTION in database 'Portalreport' because the database is read-only. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147467259 (80004005); Provider Error: 1708 (6AC) Error string: Warning: The table 'xmarket' has been created but its maximum row size (24027) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table wil... Process Exit Code 2. The step failed.

    how to resolve this , i dont ve idea on this , can any one help on this

  • ramyours2003 (9/14/2009)


    the jobs faile ddue to

    Error string: Could not run BEGIN TRANSACTION in database 'Portalreport' because the database is read-only. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 3906 (F42) Error string: Could not run BEGIN TRANSACTION in database 'Portalreport' because the database is read-only. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147467259 (80004005); Provider Error: 1708 (6AC) Error string: Warning: The table 'xmarket' has been created but its maximum row size (24027) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table wil... Process Exit Code 2. The step failed.

    how to resolve this , i dont ve idea on this , can any one help on this

    ok, the 'Portalreport' database is read only, so the commands within the BEGIN TRANSACTION couldnt execute

    The message concerning table 'xmarket' is saying that that the total number of the row, is greater than 8000 bytes, that would only be an issue if you done an insert/update statement and the modified/new information is greater than 8000. purely for information in most cases.

    If you need for this to work. Check to see if the databases are supposed to be in read only status.

    if not, use

    alter database [databasename] set read_write with no_wait

    or go into managment studio and change it that way.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • thanks for ur reply can u bit clear on this

  • Not exactly sure what part was unclear to be honest.

    Has the job failed again? is it still an issue that needs to be looked at.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • the job failed again what is the use of running the query which mentioned ?

  • the database is in readonly mode .failed with the same error what i mentioned in the above

  • ok.

    you have two choices.

    you can either edit the job and remove the references to this database. or disable the job.

    or you can run

    alter database [databasename] set read_write with no_wait

    which will allow the database to be written to and will change it from read only to read write. remember to change the [databasename] to the database name that you want to make this change on.

    before doing any of this, find out if there is a reason why the database is read only, and only when you know the reason, make the decision on what to do.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • i didnt find any information in error or in package logs , may i know the possible reasons for this .

  • (1) The primary SQL-Server documentation is SQL-Server Books-On-Line. This is almost always your first source to solve a puzzle.

    (2) In Books-On-Line, this is part of the description of 'Begin Transaction':

    BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.

    BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources.

    (3) From the description of BEGIN TRANSACTION, we can see that it seems to be used for data modification, such as INSERT, DELETE or UPDATE.

    (4) So, what is a transaction? Again, Books-On-Line says:

    Transactions

    A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

    Microsoft® SQL Server™ operates in three transaction modes:

    Autocommit transactions: Each individual statement is a transaction.

    Explicit transactions: Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

    Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

    (5) Again, a transaction is a change to the data in a database.

    (6) Your database is in read-only status. This means the data can be looked at, but it cannot be modified in any way. When a database is in read-only status, your SQL may have SELECT statements that read the data. But, your SQL cannot have successful statements with UPDATE, DELETE or INSERT. When a database is in read-only status, the only thing you can do with the data is SELECT.

    (7) Therefore, it is not logical to have a BEGIN TRANSACTION statement. There can not be any transaction to complete. Why have a BEGIN TRANSACTION statement when the data is read-only? It is an error.

    (8) If you only want to read the data, remove the BEGIN TRANSACTION statement and leave the SELECT statement. Then your code should run successfully

    (9) If you want to have a real TRANSACTION, and use the UPDATE, INSERT or DELETE statements, you will have to change the database away from READ-ONLY and to some status that allows updates.

    (10) If you change a database from read-only to a status that allows you to modify the data in the database, you need to get permission from the owners of the data. There is almost always a very important reason why a database is in read-only status. Find out why it is in read-only status before you change it to something else.

    Hope this helps.

    David

  • The first thing to resolve is that a BEGIN TRANSACTION statement is an error when the database is in read-only status.

    The next item in your error message concerns a table where the sum of the lengths of all the columns in one row is around three times longer than the maximum size that SQL-Server allows. It is around 27,000 characters, while SQL-Server has a maximum of around 8000 characters.

    What to do about this?

    (1) Make sure the column lengths are correct for the data you actually have. Sometimes when converting a database from MS Access to SQL-Server, for example, column lengths are set by default to a maximum size.

    (2) If the column lengths are correct, then store the data in a different way.

    (a) One different way is to define the data as large objects such as BLOB. Because these are stored separately from the rest of the row, they allow a greater total size for a table row. Blobs can be very hard to store, retrieve and use. Avoid them unless they are needed.

    (b) An easier way is to create several related tables so the 27,000 characters are spread over smaller tables, but using the same unique primary key to link them together.

    Table A - 6000 characters

    Table B - 6000 characters

    Table C - 6000 characters

    Table D - 6000 characters

    Table E - 3000 characters.

    Hope this also helps.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply