.:: MS-SQL, JTurbo and autoincrement columns ::.

Hi all!
Does anyone knows how to get if a column is autoincrement or not, using the DatabaseMetaData in java, or using de JTurbo driver for it?
Is there any other way to figure this out?
:confused:
Thank you very much in advance for any replies.select columnproperty(object_id('table_name'), 'column_name', 'isidentity')

(urgent)Insert data from a excel Spread sheet to SQl server

Hi,

I have a spread sheet which has 4 columns called cusip, Chartheader, growthdates and NAV.. and i also have the same number of columns in the Sql server... and I want to add another column called Rownumber and set it as int indentity... and when i try to import the data to sql server i am getting this error called

Received an invalid column length from the bcp client for colid 1.

How should i fix it.

Regards

Karen

usual way is:

1). Import data from Excel sheet;

2). Change the column names, format data if necessary;

3). Add an identity column.

|||

Without the int indentity column the import works fine... The reason i want to add the row number to the table is becauase after i transfer it to a staging database i am scrubbing the data and then inserting it to another database. But in the process of transferring data from the staging database to the actual databse the dates get jumbled up and is not in the same order that it was in the staging database.. and because of that my data in a particular sub report is not right.. this is what i am doing... in my asp.net program

If (flGrowth10K.PostedFile.FileName.ToLower.EndsWith(".xls")Or flGrowth10K.PostedFile.FileName.ToLower.EndsWith(".csv"))Then flGrowth10K.PostedFile.SaveAs(location6)' Connection String to Excel WorkbookTry Dim excelConnectionStringAs String =String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location6)'"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=location;Extended Properties=Excel 8.0" ' Create Connection to Excel Workbook Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select Cusip,FundName,ChartHeader,GrowthDates,GrowthNAV FROM [Sheet1$]", connection) connection.Open()' Create DbDataReader to Data Worksheet Using drAs Data.Common.DbDataReader = command.ExecuteReader()' SQL Server Connection StringDim sqlConnectionStringAs String = System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION)'"Data Source=.; Initial Catalog=Test;Integrated Security=True"Dim myconnectionAs New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))Dim queryAs String ="Truncate Table Growth" myconnection.Open()Dim cmdAs New SqlCommand(query, myconnection) cmd.CommandType = CommandType.Text cmd.ExecuteScalar() myconnection.Close()' Bulk Copy to SQL Server Using bulkCopyAs SqlBulkCopy =New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName ="Growth" bulkCopy.WriteToServer(dr)End UsingEnd Using connection.Close()End UsingCatch exAs ExceptionThrow exEnd Try End If Try oImportFunds =New ImportFunds(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))With oImportFunds .PeriodID = ddlPeriod.SelectedItem.Value .save()End With Catch exAs Exception lblErrorMsg.Text ="Error: Import Failed" + ex.Message lblErrorMsg.Visible =True End Try

I was wondering if i have to add Rownumber to the select clause in the oledbstatement too...

any help will be appreciated

Regards

Karen

|||

I am sorry that I didn't know you did it in your application. The error happen, I think, because your excel columns don't match your table's columns (Your table column 1 is an identity integer data now. Your application tried to insert Cusipinto this first column). If I was you, I will let the program work first, then mannually add the identity column after data imported.

|||

Thanks for your suggestion... But i cannot do that cause everytime the user updates the tables using the spread sheets i cannot go in add the row number. The reason is we have given full access to that co. to use our database. .. is there a way to specify in the oledb statement that the first column is an int identity... something in the select statement. right now i dont have the Row number in my database... so it works fine.. but on some imports things get imported as it is in the staging database and some some entries get jumbled up.

Regards

Karen

|||

Just curious(I doubt it will work) to see what happens if you change your SELECT to "Select 0, Cusip,FundName,ChartHeader,GrowthDates,GrowthNAV FROM [Sheet1$]"

|||

che3358,

I think so it worked the way u told me to do...any ways let me update my dbase and try running it from a different server...

and will let u know the results.//

Regards

Karen

|||

here's a quick and dirty way to do it, suitable for a one-time solution:

copy the table definition (less the identify and timestamp columns) and create a new table, temp_whatever

Make sure the column order in the table matches the column order in the spreadsheet.

Open the table in sql server manager.

Copy the data in the spreadsheet, minus column headings.

Paste it into the temp table.

use this statement, adjusting for table and column names, and ignoring all identify and timestamp columns:

insert into real_table (col1name, col2name, col3name,...) select col1name, col2name, col3name, ... from temp_whatever.

when done, drop temp_whatever.

:)

(Urgent) Help need with in selecting too many columns.

Hi,

I have a dbf file and that file has around 154 columns and in that i want to pull just 88 columns to my sql server database... I am using a OLedb connection and a data reader to read the data from the DBF file and then using a sqlbulcopy to insert the data into SQL server 2005 database. I have created a destination table for the 88 columns. This is my select statement for the dbf file. I have also used a Rownumber which is Int identity so i am using a 0 in the first column.

Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select 0,* from FUND.DBF", connection)

Now my question is Since i want to pull 88 columns instead of 154 column, I was thinking to give a select statement like

("Select, 0,Column1,....Column88 From Fund.DBF", connection)

SO instead of doing this is there is a way that i can specify in the sql statement that will tell it not to pull the rows after the 88th column.

Any help will be appreciated.

Regards,

Karen

I know of a way to do it but it isn't elegant

SQL stores metadata about its data in system tables. You could check the column meta-data table for all columns of a certain table, then write a cursor to build a dynamic sql select for the first 88 rows and execute the statement

If you are using SQL Server 2005, here is the select statement to pull all columns for a specific table

select *from sys.columnsjoin sys.tableson sys.Columns.object_id = sys.tables.object_idand sys.tables.[name] ='TableName'

|||

But i need to ignore the data after Column 88 in a dbf file... is it possible to do it? if not i can write a select statement to just select the fields i want.

|||

Yes, if you did what I said above you wouldn't have to write out all 88 columns. You would write a cursor that would loop through all the columns in the sys.Columns table while the count is less than or equal to 88

|||

Unfortunately, the only ways I can think of doing so requires naming all the columns that you do want. Of course that depends on the system you are pulling the data from, but with standard sql, there is no such way at least not one that is universal across all SQL implementations.

You can however, make an initial request to the datasource, pulling back 0 rows (SELECT * FROM ... WHERE 1=0) into a dataset. Then you iterate through the first 88 columns of the empty dataset to get the column names, and build up a select command. Of course this is slower, and has more overhead. It is probably easier, faster, and more efficient just to name all 88 columns.