SQL Server Performance

Apparent Column Limits in SQL 2005 B2

Discussion in 'SQL Server 2005 General DBA Questions' started by OPYAG, Jun 29, 2005.

  1. OPYAG New Member

    I am combining 3 tables that were imported from a spreadsheet with 3 sheets. They are being put into SQL due to column limits/sheet in Excel. When I combine them I create view that refrences the 3 tables with a full outer join and then create a second view that selects distinct records, this iw what will be use for data when we run mail merges. When the number of colums is 850 or so i can combine the tables. Anything over that and I start getting an error "SQL Execution Error"
    Executed SQL Statement: Select BlaBla......
    Error Source: Microsoft.VisualStudio.DataTools




  2. OPYAG New Member

    Completeing my last post.....<br /><br />Error Message: Exception has been thrown by the target of an invocation<br /><br />I have been doing testing and it is definately the number of collums that is throwing errors. When I open a design editor I can click and unclick colums on anytable as sussessfully execute SQL but once it goes over a certian number(850 or so) I get this <u>extremely </u> non specific error. Please help. <br /><br /><img src='C:SQLError.gif' border='0' />
  3. satya Moderator

    http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=17528 - I can see relevant information and as it is in Beta you can log this to SQL 2005 Beta newsgroup for MS reference solution.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. OPYAG New Member

    satya,
    Thank you. I have tried these fixes but am still getting the same errors. I am not runing projects or solutions. How would these fixes have helped me?
  5. FrankKalis Moderator

    850 columns?
    Don't you think you should normalized your schema a bit? Do you really need all those columns?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  6. OPYAG New Member

    Unfortunately I do. The Merge software, (that I did not ok) will only work with 1 datasource. I work for a broker/dealer so our account information can be vastly complicated. To ensure all potential PDF forms are properly filled we require this many variables. Do you have suggestions for a more Normalized Schema?
  7. FrankKalis Moderator

    Without knowing your schema now, how can one suggest anything?
    But usually whenever one has such a schema most columns actually contain nothing but the null marker. When you say, you are somehow stuck because some other software request this, there seems to be no chance to change it anyway.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  8. FrankKalis Moderator

    I forgot. Even with 850 column you are nowhere near a limit here. According to BOL each base table can have up to 1,024 columns. A SELECT statement can contain 4,096 columns. So this does not seem to the problem in your case.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  9. OPYAG New Member

    It seems to be a "cosmetic" issue with how SQL 2005 B2 displays results after you Execute SQL. I spoke incorrectly, the Beta 2 will actually allow me to use 585 columns, anything more and I get the errors above. I need 798. When I point the merge software the view I created it works like a charm. Looks to be a harmless, but frusterating bug.

    Frank,
    You are correct when most of the colums are blank, but to anticipate every possible variable this the minimum. This isn't actually that many when considering there are more than 200 forms in the library. I did get the developers to remove 35 duplicate columns.
  10. FrankKalis Moderator

Share This Page