1

I perform an ETL and download about 100 tables to my Companies data warehouse to perform reports and analysis on. Unfortunately I have no say on the database design and practices of the company I am downloading this information from. I connect to the DB I download the data from with a jdbc:vortex:// URL. I download it to my MS SQL Server 2012 Warehouse.

Having said that, a critical table I need has over 400 columns. I can use DBVisualizer Free to query their data but when I query this table I get the error:

[Error Code: -1, SQL State: 07000] dbFetch exception: VISION: Too many columns 408 (max: 256)

I also get this same error when I use Pentaho Kettle (Data Integration Services) to try to download this table into my warehouse.

I get this error even if I try to only grab a couple of columns from the table. I figure there has to be a way to allow pentaho and db visualizer to increase the number of columns but I can't find this information ANYWHERE. Most people aren't stupid enough to have more than 256 columns in a table so I am having a very difficult time finding a solution for this problem.

user1452180
  • 21
  • 1
  • possible solution - create 2 views of that big table - each with about half the columns... then query those. – Randy Jul 06 '12 at 15:23
  • is you're using SQL Server, why not use SSIS? – swasheck Jul 06 '12 at 15:33
  • I can't use SSIS because I use a JDBC connection to gather the data. SSIS Does not support this. I also can't create 2 views because first I have to actually get the data before I can create views against it. The problem here is I can't download the source table (which is beyond my control to edit. All I can do is download it as is) because it has too many columns – user1452180 Jul 06 '12 at 19:24

2 Answers2

1

This is a setting in the JDBC driver, which is documented by the creator of this JDBC driver. The same question was asked on the Pentaho forum.

Thomas Morgner
  • 412
  • 2
  • 2
0

The Vortex driver accepts a max_column property that I used to fix the very same issue. Try setting max_column to something bigger than 400.

Properties dbProps = new Properties();
dbProps.setProperty("max_column", "500");

Connection dbCon = DriverManager.getConnection("jdbc:vortex...", dbProps);

In DbVisualizer, when looking at the Connection, go to the Properties tab, and add max_column under the Driver Properties section.

dangowans
  • 2,263
  • 3
  • 25
  • 40