Problem in Importing Excel data into MS SQL 2000

Hi all,

I want to import MS Excel data into MS SQL 2000 and I am programming this in VB 6.0. I am referring the article http://support.microsoft.com/kb/316934/EN-US/ for this. I can able to import Excel data successfully into SQL database table. But the problem is, the order of exported data in SQL table is not matching that of MS Excel data. All the rows were jumbled, which made it totally unusable.

Please guide me how to Import Excel data into MS SQL in its original order only. Any other method other than what I am following is present; please suggest me to keep the original order.

Regards,

Rajeev Vandakar

Bangalore

As long as the data is correct on each row, there is no way to 'guarantee' that SQL Server will store data in any particular order -UNLESS there is a CLUSTERED index on the table, and if so, the data will be stored in the index order.

Otherwise, with SQL Server, we do NOT concern ourselves with the order of data storage, we use ORDER BY in our queries to cause the resultset to be in the order desired.

I suspect you may have a PRIMARY KEY with a CLUSTERED Index, and that is why it 'appears' that your data is being jumbled.

Check in Books Online about how to use the ORDER BY clause in your queries to un-jumble your data.

|||

Thank you Arnie Rowland for reply.

I can use ORDER BY clause in MS SQL after importing data. But this will not solve my problem here. Because the way data is present in Excel. In Excel some 12 colums present. Rows were organised in sections and to indicate start of a section 3rd colummn (meant for person name) have section name and remaining columns were left blank. So after importing into SQL, if I use ORDER BY clause, these rows, meant for sectin name will come in the top! If Excel data moves to SQL in as-it-is, same order, my problem will be solved. How to do this?

Regards,

Rajeev Vandakar

|||

As I indicated before, SQL Server (in fact the SQL language specification), clearly states that there is no guarantee about the order that data is stored in a table.

If you MUST load the table with data in a particular order, I suggest that you might wish to add a column to the Excel file, fill that column with some indicator of row order, and add a clustered index (or primary key) to the SQL table using that column.

Then the data will be ordered as you desire.

Without some way to place the rows in a particular order, SQL Server does not care, does not enforce order, and does not produce reliably ordered data.

sql

Problem in importing data from excel to sql2005

Hi
I have to import data from a number of excel files to corresponding tables in SQL 2005. The excel files are created using excel 4.0. I have created an excel connection manager and provided it with the path of the excel sheet.Next i have added an excel source from the toolbox to the dataflow. I have set the connection manger, data access mode, and the name of the excel sheet (the wizard detects the sheet correctly) in the dialog window i get when i double click the excel source. Every thing goes fine till here. Now when i select the 'columns' in this dialog window or the preview button, i get this error

TITLE: Microsoft Visual Studio

Error at Data Flow Task [Excel Source [1]]: An OLE DB error has occurred. Error code: 0x80004005.
Error at Data Flow Task [Excel Source [1]]: Opening a rowset for "test4$" failed. Check that the object exists in the database.

ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

Any ideas about why is this happening?
UmerDid you specify the Excel 4.0 as the version in the connection manager?|||yes i have specified the version 4.0 in the connection manager

Problem in Importing data from excel to sql server

I have excel file that has field named Purpose. Its max length is 400 character. I import this file to sql server database table. And also i change the purpose field in sql server database table with nvarchar 400. But when i run this job, it gave me error message:

Error at source for row number 1215. Errors encountereed so far in this task: 1.
Data for source column 18 ('Purpose') is too large for the specified buffer size.

What should i do so that i still can import the data from excel to sql server database?

Thanks for your help.I have far fewer problems when I take spreadsheets and put them in Access and then import them into sql server from access than I do just importing from excel.

problem importing photo

hi all
please i want to now how i can import (Employee Photo) that saved in a folder (Photos) depend on (Employee ID) field.
please i need your helpIt's in the help.
Dynamic images or images, dynamic or something.|||i m sorry , but i can't find it please tell me where i can find Picture command or OLE Object command i went thourgh the help and still ...
please send me examples or more inforamtion at

halbarwani@.hotmail.com

thank you

i need your help|||My Crystal installation has just stopped working so I can't be exact but as far as I recall you put any old picture onto the report (helps if it's the same size etc. ) and then right click the image. Choose the 2nd or 3rd option (graphic properties?) and go to the common tab, I think. There's a check box called something like graphic location - click on the formula box next to it and enter the formula there.

It's definately in the help.
Type Images into the help index, and I'm pretty sure it's a sub-heading called Dynamic that explains this.|||Got Crystal working again, so
1) Choose Insert, Picture menu option and browse for a picture. Put it on the report.
2) Right click the picture and choose Format Graphic
3) Go to the Picture tab
4) Click the formula box for Graphic Location
5) Enter your formula.|||thank you very very much Mr.JaganEllis

but still i can't see Graphic Location at the Picture tab,
i'm sorry to upset u but it's very importent for me to display report with employee photo.
i'm using (cr 9.2.0) with lotus notes database, is this version support??!

thank you and sorry again|||Sorry, I've no idea if CR 9.2.0 supports this, but if there's no option on your Picture tab (or any other tab within Format Graphic) and if you can't find it in the help then maybe it's not.

Anyone else using 9.2.0 confirm this, or able to suggest anything else?|||See if you find it here
http://support.businessobjects.com/

Problem importing from Oracle

Hello all, I hoping to get some input on how to get around this odd problem. I use the SSIS Import Wizard to create a package that imports data from an Oracle 9 database into SQL 2005. One of the 'columns' selected from Oracle is actually two floating point columns multiplied together.

ie: SELECT one * two FROM table

In the SSIS wizard the data preview for this shows up as it should, with the column data in floating point form. However after actually importing the data, all the numbers get imported as integers even though they are going into a floating point column.

Any ideas how to get around this?

Thanks

Try explicitly casting them as a float in the SELECT.

Problem importing from Excel

When I try to import data from Excel with the dts wizard I get the following eror message when I select Excel as a data source:

TITLE: SQL Server Import and Export Wizard

An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.

ADDITIONAL INFORMATION:

Exception has been thrown by the target of an invocation. (mscorlib)

The connection type "EXCEL" specified for connection manager "{D4D59FCE-C0A4-4AA2-B374-766665A74159}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({B2F473AA-8E07-40AA-AF01-9AD13DE7B4B8})

The connection type "EXCEL" specified for connection manager "{D4D59FCE-C0A4-4AA2-B374-766665A74159}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({B2F473AA-8E07-40AA-AF01-9AD13DE7B4B8})

BUTTONS:

OK

That is the complete error message as I get it. I used the copy message text feature and pasted it directly here.

I really hope someone can help with this one as it has me completely stumped and also unable to finish an assignment.

If this is a college assignment, consider consulting with your professor or other students. I find in my classes that many students doing an identical assignment will encounter the same software problems.

Additionally you may want to try exporting the data from Excel to a CSV and then importing the CSV file and see if you have better luck there.

Hopefully someone else can chime in some better help specific to your error.

Problem importing Excel data into SSMS

Hi,

When I try to use the Import and Export Wizard to import a good .xls file I keep getting this error:

"Could not find installable ISAM. (Microsoft JET Database Engine)"

It occurs in both SSMS or SSIS. It also occurs with other flat files like .csv.

The OS is XP, w/ office 2003.

here are the full error details:

at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)

I don't understand why OleDb connection is involved to the Jet when this is SQL2K5 full version.

Please anyone help.

Carl

http://databases.aspfaq.com/database/how-do-i-solve-could-not-find-installable-isam-errors.html for all the issues to resolve.|||

Thanks Satya,

This link is dead. I had previously found this resource and chased down the Google cached version

http://72.14.253.104/search?q=cache:Vyh8_PKhHrwJ:databases.aspfaq.com/database/how-do-i-solve-could-not-find-installable-isam-errors.html+how-do-i-solve-could-not-find-installable-isam-errors.html&hl=en&lr=&strip=1

All it says is to reinstall the MDAC. Well, when I try to do that the system (XP) says there is no need for it as a more recent version is included as part of the OS.

This article describes registry changes to the Jet engine.

http://support.microsoft.com/default.aspx/kb/283881

This machine's registry does not have these keys at all, even though both Excel 2k3 and Access 2k3 work just fine. The fix is to reinstall or repair the Office Pro installation.

I have poor experiences with reinstalling Office 2003. Many customized settings are kept in the registry and wiped out in the reinstall/repair. Before I embark on this journey I don't understand why I even need Office for the operation I am trying to do?

BTW the same error occurred when I tried to import a csv file too, not just an .xls

I wrote a little c# class to convert the file to xml and then standard DAL to "import" the data into SQL Server db. Worked like a charm, but in the future I would still like to be able to do it in SSMS. Most likely on a remote server there would not even be any installation of Office.

Thanks again

Carl

|||See this blog entry too http://blogs.wdevs.com/Gaurang/archive/2005/06/15/5112.aspx and I appreciate your feedback.|||

Satya,

great tip here http://blogs.wdevs.com/Gaurang/archive/2005/06/15/5112.aspx

They talk about extended properties in a connection string.

If I were not doing the connection programmatically but using the SSMS Object explorer, where should I find the connection for the (local) server instance?

Databases -> MyDatabase ->Security ->Users ->dbo
Here there is a link in the lower left to view the connection properties but no extended properties field.

The only place I could find such a field is in
Databases -> System Databases -> master ->Security ->Users -> SERVERNAME\SQLServer2005MSFTEUser$SERVERNAME$MSSQLSERVER

That however is not the db I want to import to. I added an extended property anyway "Excel 8.0;IMEX=1" just for kicks but it didn't do any good.

Any more ideas?

Carl

|||

You can use SP_ADDEXTENDEDPROPERTY as per this http://msdn2.microsoft.com/en-us/library/ms190243.aspx link.

|||

My question was not how to do it programmatically but how to do it using the SSMS GUIs.

I guess the pros do not like using the GUIs too much, and I am starting to see why.

Thanks

Carl

Problem importing data from linked server

Sorry if this is the wrong newsgroup. If so, please point me to the right
one.
We use a linked server to import data from an old SQL 2000 database to a new
SQL 2000 database. We create a linked server like this:
exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
'Password'
Then we execute a series of "INSERT INTO" statements to import data like
this:
INSERT INTO ATABLE( FIELD1, FIELD2, ... )
SELECT T.FIELD1, T.FIELD2, ...
FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName') AS
T
There are about 10 "INSERT INTO" statements and all execute fine except for
one. The failing one (the 7th one) returns this error:
"MSDTC on server 'ServerName' is unavailable."
My DTC service is inactive, but that shouldn't matter since all other 9
statements executed fine. If I enable the service and rerun the failing SQL
statement, it returns this error:
"The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
What's strange is that *all* "INSERT INTO" statements have the same format
as shown above, and only one of them fails. All others after that failing
statement run fine. This also happens using SQL Query Analyzer and happens
no matter how many times I run the script. Can anyone explain to me what's
going on here and how to fix it?
Thanks,
Jon E. Scott
Blue Orb Software
http://www.blueorbsoft.comHi
I can only think that this is a network problem! Check the SQL Server and
Event Logs, try changing protocols and possibly try four part naming instead
of OPENQUERY.
John
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>|||Is the server you are linking to running Server 2003?
nivek
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>sql

Problem importing data from linked server

Sorry if this is the wrong newsgroup. If so, please point me to the right
one.
We use a linked server to import data from an old SQL 2000 database to a new
SQL 2000 database. We create a linked server like this:
exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
'Password'
Then we execute a series of "INSERT INTO" statements to import data like
this:
INSERT INTO ATABLE( FIELD1, FIELD2, ... )
SELECT T.FIELD1, T.FIELD2, ...
FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName') AS
T
There are about 10 "INSERT INTO" statements and all execute fine except for
one. The failing one (the 7th one) returns this error:
"MSDTC on server 'ServerName' is unavailable."
My DTC service is inactive, but that shouldn't matter since all other 9
statements executed fine. If I enable the service and rerun the failing SQL
statement, it returns this error:
"The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
What's strange is that *all* "INSERT INTO" statements have the same format
as shown above, and only one of them fails. All others after that failing
statement run fine. This also happens using SQL Query Analyzer and happens
no matter how many times I run the script. Can anyone explain to me what's
going on here and how to fix it?
Thanks,
Jon E. Scott
Blue Orb Software
http://www.blueorbsoft.com
Hi
I can only think that this is a network problem! Check the SQL Server and
Event Logs, try changing protocols and possibly try four part naming instead
of OPENQUERY.
John
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>
|||Is the server you are linking to running Server 2003?
nivek
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>

Problem importing data from linked server

Sorry if this is the wrong newsgroup. If so, please point me to the right
one.
We use a linked server to import data from an old SQL 2000 database to a new
SQL 2000 database. We create a linked server like this:
exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
'Password'
Then we execute a series of "INSERT INTO" statements to import data like
this:
INSERT INTO ATABLE( FIELD1, FIELD2, ... )
SELECT T.FIELD1, T.FIELD2, ...
FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName') AS
T
There are about 10 "INSERT INTO" statements and all execute fine except for
one. The failing one (the 7th one) returns this error:
"MSDTC on server 'ServerName' is unavailable."
My DTC service is inactive, but that shouldn't matter since all other 9
statements executed fine. If I enable the service and rerun the failing SQL
statement, it returns this error:
"The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
What's strange is that *all* "INSERT INTO" statements have the same format
as shown above, and only one of them fails. All others after that failing
statement run fine. This also happens using SQL Query Analyzer and happens
no matter how many times I run the script. Can anyone explain to me what's
going on here and how to fix it?
--
Thanks,
Jon E. Scott
Blue Orb Software
http://www.blueorbsoft.comHi
I can only think that this is a network problem! Check the SQL Server and
Event Logs, try changing protocols and possibly try four part naming instead
of OPENQUERY.
John
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>|||Is the server you are linking to running Server 2003?
nivek
"Jon E. Scott" <NOSPAMsupport@.blueorbsoft.comNOSPAM> wrote in message
news:%23VenwxNIFHA.3108@.tk2msftngp13.phx.gbl...
> Sorry if this is the wrong newsgroup. If so, please point me to the right
> one.
> We use a linked server to import data from an old SQL 2000 database to a
> new
> SQL 2000 database. We create a linked server like this:
> exec sp_addlinkedserver 'OldDatabase', '', 'SQLOLEDB', 'ServerName', ''
> exec sp_addlinkedsrvlogin 'OldDatabase', 'false', NULL, 'UserName',
> 'Password'
> Then we execute a series of "INSERT INTO" statements to import data like
> this:
> INSERT INTO ATABLE( FIELD1, FIELD2, ... )
> SELECT T.FIELD1, T.FIELD2, ...
> FROM OPENQUERY( OldDatabase, 'SELECT * FROM DatabaseName.dbo.TableName')
> AS
> T
> There are about 10 "INSERT INTO" statements and all execute fine except
> for
> one. The failing one (the 7th one) returns this error:
> "MSDTC on server 'ServerName' is unavailable."
> My DTC service is inactive, but that shouldn't matter since all other 9
> statements executed fine. If I enable the service and rerun the failing
> SQL
> statement, it returns this error:
> "The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> What's strange is that *all* "INSERT INTO" statements have the same format
> as shown above, and only one of them fails. All others after that failing
> statement run fine. This also happens using SQL Query Analyzer and
> happens
> no matter how many times I run the script. Can anyone explain to me
> what's
> going on here and how to fix it?
> --
> Thanks,
> Jon E. Scott
> Blue Orb Software
> http://www.blueorbsoft.com
>
>

Problem importing data from Interbase using DTS

Hi im trying to import a old Interbase 6 database using MSSQL 2000 DTS
import wizard.
Im using Firebird / Interbase 1.5 ODBC driver as provider for the Interbase.
Everything works perfect if i choose to import one table at a time. But if i
choose 2 or more the wizards excutes the DDL (tables ar created) then it
just locks up. No error, just no progress. I have tried other ODBC provider
but with the exact same result.
Any idea why the DTS Data Iimport wizard locks up?
Thanks in regards
Anders, Denmark
Hi
If the ODBC driver does not return control to DTS, DTS looks like it has
hung.
That indicates a problem with the driver as not with DTS.
This problem does not occur with Oracle, Access or SQL Server drivers.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anders K. Jacobsen [DK]" <none@.at.all> wrote in message
news:uWFBqpE4EHA.208@.TK2MSFTNGP12.phx.gbl...
> Hi im trying to import a old Interbase 6 database using MSSQL 2000 DTS
> import wizard.
> Im using Firebird / Interbase 1.5 ODBC driver as provider for the
Interbase.
> Everything works perfect if i choose to import one table at a time. But if
i
> choose 2 or more the wizards excutes the DDL (tables ar created) then it
> just locks up. No error, just no progress. I have tried other ODBC
provider
> but with the exact same result.
> Any idea why the DTS Data Iimport wizard locks up?
> Thanks in regards
> Anders, Denmark
>
|||> If the ODBC driver does not return control to DTS, DTS looks like it has
> hung.
> That indicates a problem with the driver as not with DTS.
> This problem does not occur with Oracle, Access or SQL Server drivers.
Strange. Do you know of any Interbase ODBC provider wich is "compatible"
with DTS import?
I have tried:
EasySoft ODBC for interbase
XTG ODBC for interbase
FireBird / Interbase ODBC for interbase
IBProvider ODBC for interbase
All with the same result. Creates DDL then hang when starting to convert
data, if one table is choosen it works perfectly.
Suggestions to ODBC drivers?
Anders
|||Hi Anders,
I am using the Gemini Firebird / Interbase ODBC Driver for exactly what you
are trying to do. We're slowly converting all our clients from Interbase to
SQL Server. I use this driver for a DTS package which pulls about 80 tables
from Interbase source into our SQL Server target database. However, I use an
individual Transform Data task for each table. It's a nightmare to maintain
as we evolve the database structure but it runs really nicely, with multiple
TD tasks executing in parallel.
http://www.ibdatabase.com/
https://secure.shareit.com/shareit/c...oductid=148695
Hope this helps,
Joe Geretz
"Anders K. Jacobsen [DK]" <none@.at.all> wrote in message
news:ulIOTdF4EHA.2404@.TK2MSFTNGP14.phx.gbl...
> Strange. Do you know of any Interbase ODBC provider wich is "compatible"
> with DTS import?
> I have tried:
> EasySoft ODBC for interbase
> XTG ODBC for interbase
> FireBird / Interbase ODBC for interbase
> IBProvider ODBC for interbase
> All with the same result. Creates DDL then hang when starting to convert
> data, if one table is choosen it works perfectly.
> Suggestions to ODBC drivers?
> Anders
>
>
|||> I am using the Gemini Firebird / Interbase ODBC Driver for exactly what
> you are trying to do. We're slowly converting all our clients from
> Interbase to SQL Server. I use this driver for a DTS package which pulls
> about 80 tables from Interbase source into our SQL Server target database.
> However, I use an individual Transform Data task for each table. It's a
> nightmare to maintain as we evolve the database structure but it runs
> really nicely, with multiple TD tasks executing in parallel.
We try to create views on the IB database wich match our new tablestructure
seems quite maintainable not sure though. Anyway. I have already tried with
the Gemini ODBC driver...same results. Its real strange. Because everything
else works.
|||Which Task are you using to transfer the data?
It sounds like you're issuing SQL to insert into X select * from Y? This is
conveniently maintained, but you can't do this with the Data Pump. Or can
you?
- Joe Geretz -
"Anders K. Jacobsen [DK]" <none@.at.all> wrote in message
news:%23gg2PBu4EHA.824@.TK2MSFTNGP11.phx.gbl...
> We try to create views on the IB database wich match our new
> tablestructure seems quite maintainable not sure though. Anyway. I have
> already tried with the Gemini ODBC driver...same results. Its real
> strange. Because everything else works.
>
>

Problem importing data from an Access memo field into a SQL Server ntext field.

I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.
I'd appreciate any insights into what may be causing this problem, and what I can do about it.
Thanks in advance for any help!
255 is the default change the settings to accept more than 255, Access memo must be either image to store the file in Word or Text both can grow to 2gigs because Varchar 8000, Char 8000, NVarchar 4000 and NChar 4000 maybe too small for Access memo column. Hope this helps.|||

Thank you for your reply.
If you don't mind, what "settings" are you referring to and how do I change them? For example, are you talking about settings in Access, SQL Server and/or DTS? If I have to change settings via DTS, I've been using the DTS Wizard. Can I change the settings you're referring to with this Wizard?
I haven't been able to find any information on this problem, so I could use all the details you're able to provide.
Thanks again!

|||Try this link for a fix because it is a bug and keep the link because the site is run by a DTS expert. Hope this helps.
http://www.sqldts.com/?297|||

I finally was able to find the cause of my problem, and thought I'd put it out there for somebody who might suffer the same confusion one day. It turns out that Microsoft Query Analyzer will only display the first first 255 (or 256) characters of a field (this may be common knowledge - but I didn't know that). I had assumed that, since I couldn't see the entire result, DTS had truncated the data when importing from Microsoft Access. That wasn't the case. Everything was always fine.
Thanks again for the feedback!

Problem importing data from a table in Access database

Hi,
I needed to import an Access database to sql server 2000. However, in one
table, the table definition got created but could not import the data. I
tried importing the data again, however, I am getting the following message:
Error at Destination for the row number 251. Errors encountered so far in
the task: 1/
Insert row error, column 5('AuditStartDate', DBTYPE_DBTIMESTAMP), status 6:
Data overflow. Invalid character value for cast specification.
The AuditStartDate column in Sql Server is currently having a datatype
smalldatetime with a length 4 while in the Access database the AuditStartDat
e
is Date/Time datatype with mm/dd/yyyy data e.g. 07/01/1992.
I appreciate any help on this. Thanks.ALTER TABLE ALTER COLUMN on the destination table and modify the data type t
o
DATETIME. This should give you a bigger range.
Sincerely,
Anthony Thomas
"Jack" wrote:

> Hi,
> I needed to import an Access database to sql server 2000. However, in one
> table, the table definition got created but could not import the data. I
> tried importing the data again, however, I am getting the following messag
e:
> Error at Destination for the row number 251. Errors encountered so far in
> the task: 1/
> Insert row error, column 5('AuditStartDate', DBTYPE_DBTIMESTAMP), status 6
:
> Data overflow. Invalid character value for cast specification.
> The AuditStartDate column in Sql Server is currently having a datatype
> smalldatetime with a length 4 while in the Access database the AuditStartD
ate
> is Date/Time datatype with mm/dd/yyyy data e.g. 07/01/1992.
> I appreciate any help on this. Thanks.sql

Problem importing data from a table in Access database

Hi,
I needed to import an Access database to sql server 2000. However, in one
table, the table definition got created but could not import the data. I
tried importing the data again, however, I am getting the following message:
Error at Destination for the row number 251. Errors encountered so far in
the task: 1/
Insert row error, column 5('AuditStartDate', DBTYPE_DBTIMESTAMP), status 6:
Data overflow. Invalid character value for cast specification.
The AuditStartDate column in Sql Server is currently having a datatype
smalldatetime with a length 4 while in the Access database the AuditStartDate
is Date/Time datatype with mm/dd/yyyy data e.g. 07/01/1992.
I appreciate any help on this. Thanks.
ALTER TABLE ALTER COLUMN on the destination table and modify the data type to
DATETIME. This should give you a bigger range.
Sincerely,
Anthony Thomas
"Jack" wrote:

> Hi,
> I needed to import an Access database to sql server 2000. However, in one
> table, the table definition got created but could not import the data. I
> tried importing the data again, however, I am getting the following message:
> Error at Destination for the row number 251. Errors encountered so far in
> the task: 1/
> Insert row error, column 5('AuditStartDate', DBTYPE_DBTIMESTAMP), status 6:
> Data overflow. Invalid character value for cast specification.
> The AuditStartDate column in Sql Server is currently having a datatype
> smalldatetime with a length 4 while in the Access database the AuditStartDate
> is Date/Time datatype with mm/dd/yyyy data e.g. 07/01/1992.
> I appreciate any help on this. Thanks.

Problem importing data from a table in Access database

Hi,
I needed to import an Access database to sql server 2000. However, in one
table, the table definition got created but could not import the data. I
tried importing the data again, however, I am getting the following message:
Error at Destination for the row number 251. Errors encountered so far in
the task: 1/
Insert row error, column 5('AuditStartDate', DBTYPE_DBTIMESTAMP), status 6:
Data overflow. Invalid character value for cast specification.
The AuditStartDate column in Sql Server is currently having a datatype
smalldatetime with a length 4 while in the Access database the AuditStartDate
is Date/Time datatype with mm/dd/yyyy data e.g. 07/01/1992.
I appreciate any help on this. Thanks.ALTER TABLE ALTER COLUMN on the destination table and modify the data type to
DATETIME. This should give you a bigger range.
Sincerely,
Anthony Thomas
"Jack" wrote:
> Hi,
> I needed to import an Access database to sql server 2000. However, in one
> table, the table definition got created but could not import the data. I
> tried importing the data again, however, I am getting the following message:
> Error at Destination for the row number 251. Errors encountered so far in
> the task: 1/
> Insert row error, column 5('AuditStartDate', DBTYPE_DBTIMESTAMP), status 6:
> Data overflow. Invalid character value for cast specification.
> The AuditStartDate column in Sql Server is currently having a datatype
> smalldatetime with a length 4 while in the Access database the AuditStartDate
> is Date/Time datatype with mm/dd/yyyy data e.g. 07/01/1992.
> I appreciate any help on this. Thanks.

problem importing csv delimited text file into a sql server 2005 table

I am using the Bulk Insert command and trying to import a CSV delimited text file into a table and I am having problems with the quote field delimiters ", " The command below works but it takes in all the "" quotes as well and the field delimiter comma , works only if the commas are the separators only. If I have a comma within a address field for example then the data gets imported into the wrong fields. What can I use to identify that the text qualifier is ". I don't see where I can use the bulk insert command to determine this. Is there another command that I can use or am I using this command incorrectly. I thank you in advance for any response or suggestion you may have.

BULK INSERT AdventureWorks.dbo.MbAddress

FROM 'a:\mbAddress.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR=',',

ROWTERMINATOR='\n',

CODEPAGE = '1252',

KEEPIDENTITY,

KEEPNULLS,

FIRSTROW=2)

Here is a sample ascii file I am importing as well you can see that 6330 has a extra comma in the address line.

"AddressAutoID","Memkey","Type","BadAddress","Address1","Address2","Address3","City","State","Zip","Foreign","CarrierRoute","Dpbc","County","CountyNo","ErrorCode","ChangeDate","UserID"
6317,26517,1,0,"1403 W. Kline Ave","","","MILWAUKEE","WI","53221","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6318,26225,1,0,"501 Dunford Dr","","","BURLINGTON","WI","53105","","",0.00,"RACINE",101,"",1/25/2006 0:00:00,"admin"
6319,20101,1,0,"2115 Cappaert Rd #35","","","MANITOWOC","WI","54220","","",0.00,"MANITOWOC",71,"",1/25/2006 0:00:00,"admin"
6320,23597,1,0,"728 Woodland Park Dr","","","DELAFIELD","WI","53018","","",0.00,"WAUKESHA",133,"",1/25/2006 0:00:00,"admin"
6321,23392,1,0,"7700 S. 51st St","","","FRANKLIN","WI","53132","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6322,26537,1,0,"W188 S6473 GOLD DRIVE","","","MUSKEGO","WI","53150","","",0.00,"WAUKESHA",133,"",1/26/2006 0:00:00,"admin"
6323,25953,1,0,"3509 N. Downer Ave","","","MILWAUKEE","WI","53211","","",0.00,"MILWAUKEE",79,"",1/26/2006 0:00:00,"admin"
6324,19866,1,0,"10080 E. Mountain View Lake Rd. #145","","","SCOTTSDALE","AZ","85258","","",0.00,"MARICOPA",13,"",1/27/2006 0:00:00,"admin"
6325,25893,1,0,"W129 N6889 Northfield Dr. Apt 114","","","MENOMONEE FALLS","WI","53051-0517","","",0.00,"WAUKESHA",133,"",1/27/2006 0:00:00,"admin"
6326,26569,1,0,"8402 64th Street","","","KENOSHA","WI","53142-7577","","",0.00,"KENOSHA",59,"",1/27/2006 0:00:00,"admin"
6327,24446,4,0,"83 Sweetbriar Br","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",1/30/2006 0:00:00,"admin"
6328,19547,1,0,"4359 MERCHANT AVENUE","","","SPRING HILL","FL","34608","","",0.00,"HERNANDO",53,"",2/8/2006 0:00:00,"admin"
6329,26524,1,0,"264 Lakeridge Drive","","","OCONOMOWOC","WI","53066","","",0.00,"WAUKESHA",133,"",2/10/2006 0:00:00,"admin"
6330,23967,1,0,"3423 HICKORY ST","100 Tangerine Blvd., Brownsville, TX 78521-4368","Texas Phone Number: 956-546-4279","SHEBOYGAN","WI","53081","","",0.00,"SHEBOYGAN",117,"",2/15/2006 0:00:00,"admin"
6331,25318,1,0,"3960 S. Prairie Hill Lane Unit 107","","","Greenfield","WI","53228","","",0.00,"MILWAUKEE",79,"",2/20/2006 0:00:00,"admin"
6332,24446,1,0,"83 Sweetbriar BR","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",2/21/2006 0:00:00,"admin"
6333,26135,1,0,"P.O. Box 8 127 Main Street","","","CASCO","WI","54205","","",0.00,"KEWAUNEE",61,"",2/21/2006 0:00:00,"admin"

Hi

You can do this by using a formatfile. You can define each field in a formatfile and specify a differend field terminator for each field.

For example:

<?xml version="1.0"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="""," MAX_LENGTH="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="""\n" MAX_LENGTH="30"/>

[....]

</RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLNVARCHAR"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLNVARCHAR"/> </ROW> </BCPFORMAT>

=== Edited by Hatzi74 @. 25 Apr 2006 2:49 PM UTC===
Edit2: You could also use the Data Import Wizzard... Select "Flatfile" as source, and then match the fielddelimiters for every field the way you need them to be...

For example... After a Text -> Number you would need --",-- as a delimiter if you go from text -> text you will need --","-- as a delimiter and when coming from number -> text you need --,"--

Hope this makes sense. Also the row delimiter could be adjusted to match --"\n-- if the last field is a text field. But this will leave the field "dirty" (It will include a tailing ")

|||

Thank you for your quick reply. I did try a non xml format file using the bcp command and it came out like the info below. However, It worked on importing but I still had the same problem with the commas as the delimiter. I wanted to add the quotes as a delimiter but I could not figure out how to do that in this type of file.

I will try a xml format this time but I am not sure by your instructions how to tell it that the file has quotes for the text qualifiers. I think you are saying I need a "," as a delimiter

Thank you so much for your time and effort. I am going to try it.

Most sincere,

Sarah

my format file below (.fmt type)

9.0
18
1 SQLCHAR 0 100 "," 1 AddressAutoID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 Memkey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "," 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "," 5 Address1 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 100 "," 6 Address2 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 100 "," 7 Address3 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 100 "," 8 City SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 100 "," 9 State SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "," 10 Zip SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 "," 11 Foreign SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "," 12 CarrierRoute SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 100 "," 13 Dpbc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "," 14 County SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 "," 15 CountyNo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "," 16 ErrorCode SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 "," 17 ChangeDate SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "\r\n" 18 UserID SQL_Latin1_General_CP1_CI_AS

|||

Try this:

3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 ",""" 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 """," 5 Address1 SQL_Latin1_General_CP1_CI_AS

|||

I am trying that, but it does not seem to be working.

Sarah

|||

I think it is getting confused with the quotes because they seem to be reserved for the program or something. I get an error.

do you know the bcp command I need to get the xml format? I will try that as well.

Sarah

|||

Hi

I just wanted to thank you for your reply and that you somewhat directed me into the right direction.

I just wanted to let you know that I finally figured it out. I had to strip the " quotes out of the ascii file and replace them with | pipes and then I created the fmt (format file) and imported the data. Here is a copy of the fmt file just for your own information. I had to tell it exactly where the pipes started and ended for each field delimter. I tried setting up the format file to look for the quotes but it had a problem with that because " " were being used in the format file.

Anyways I got it and now I am having a problem accessing the A:\ drive, using the bulk insert command. sql server thinks I am accessing the actual servers A:\ and not my local drive. wow what a nightmare just to read a simple ascii file into a table. Unfortunatley the operator has to import data from floppy diskettes. I will repost this problem.

Anyways, thank you very much for your imput.

Most sincere,

Sarah Diane Reid

Sample Format File below:

9.0
18
1 SQLCHAR 0 100 "," 1 AddressAutoID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 Memkey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 ",|" 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "|,|" 5 Address1 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 100 "|,|" 6 Address2 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 100 "|,|" 7 Address3 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 100 "|,|" 8 City SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 100 "|,|" 9 State SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "|,|" 10 Zip SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 "|,|" 11 Foreign SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "|," 12 CarrierRoute SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 100 ",|" 13 Dpbc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "|," 14 County SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 ",|" 15 CountyNo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "|," 16 ErrorCode SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 ",|" 17 ChangeDate SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "|\r\n" 18 UserID SQL_Latin1_General_CP1_CI_AS

Problem copying table

We upgraded to SQL Server 2005 & I'm having trouble with the Import and Export Wizard in the Management Studio. We periodically need to export some data to a different database to save it while we update the 'real' database which basically starts it over with empty tables. Under SQL Server 2000 that wasn't a problem. Under 2005 it is.

The database comes from a vendor and nearly every table in it has a timestamp column & when I try to import/export the rows I get a Validation Error: Error 0xc0202048: Data Flow Task: Attempting insertion into the row version column "timestamp". Cannot insert into a row version column.

Now I can write a query that omits the timestamp column & the import/export works perfectly-but a couple of the tables have over a hundred fields! (Oh, what I'd give for an 'except' analog to the * selection.)

Any suggestion how to get around this? Thanks.You cannot insert specific values to a column when the datatype is timestamp. Depending on the source of your data, you most likely want to change the datatype of the destination column to either binary(8), varbinary(8) or datetime. More information about timestamp columns in SQL Server is available in BOL (http://msdn2.microsoft.com/en-us/library/ms182776.aspx).|||You know, I thought my question was clear. I know what the problem is, what I don't know is how to solve it-and since the app comes from a vendor changing it myself isn't an option. (Is it really an option for you, roac, or do you just not work with applications you don't develop yourself?)

FWIW I submitted a request to the vendor to remove/change the timestamp column-no response, yet, which is why I'm looking for a solution that I can implement.

So far it looks like I'm stuck-either wait for the vendor to make the change or start writing queries. I figure at least two solid days' work to write the queries, and then there's testing & keeping them updated.|||Ah, I see now, I obviously overlooked a few details :)

Hopefully you have a test-environment where you can try the following procedure:

1. Backup the database
2. Restore on alternate location
3. Use ApexSQL Diff (http://www.apexsql.com/sql_tools_diff.asp) to script changes to structure
4. Restore database where backup was taken
5. Run script to apply changes in structure.

As far as I can see this approach should work. If the database is not deleted and recreated during upgrade, and you are using Enterprise Edition, you could use a Database Snapshot instead of backups.|||Wow, Calvin. That was a pretty rude response to someone who honestly was trying to help you.
I have a script that might assist you in writing queries for these lengthy tables, but I hesitate to post it because it may not be exactly what you want.|||roac, I'll take a look at ApexSQL. Right off I don't see how it will help as your description sounds like a way to change the timestamp column and what I need is a way to backup/restore all the data except the timestamp column. But maybe ApexSQL will let me do that-I'll take a look & thanks.

Blindman, I'd be happy to take a look at your script. Thanks.|||ApexSQL diff is a tool for scripting the code neccessary to change one version of a data structure to another. So, you can create a script that change the data structure of the data that you already have to the version created by the application's upgrade.|||Attached is a script for creating formatted lists of column names for easy cut-n-paste into code. Make sure when you run it that you have QA set to output results in text mode.