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 sequential text files

Hello everybody

I have a rather strange problem I guess. Most likely I did something wrong but I can't find it right now. So this is what happens.

Well, I receive a number of text files and I have to import them into SQL server 7. I use Vbscript (ActiveX import) to modify some data before putting it all into my tables. Sql server imports all records perfectly but sometimes the sequential order of the text file is not respected when I look into the table. I'd like to explain this a bit better.

Let's say I have 3 types of records in the textfile. "NATIONAL" , "DISTRICT" and "AREA". The textfiles (fixed columns) look a bit like this.

0123321334253 NATIONAL 432748234
2347234712357 DISTRICT 234852348
2348235724137 DISTRICT 242752348
6975067587684 DISTRICT 664568483
6534534537453 AREA 324537453
6578305607965 AREA 485834690
3247573284920 AREA 453753648
6795673435734 AREA 457352888
4564573486943 AREA 583475386

So the order of the records is really important. Mostly SQL Server imports all records correctly but sometimes (without changing the inputfiles nor the DTS package !!!!) it mixes up the order of the records. After importation into a table it could look like this.

3247573284920 AREA 453753648
6795673435734 AREA 457352888
0123321334253 NATIONAL 432748234
2347234712357 DISTRICT 234852348
6534534537453 AREA 324537453
6578305607965 AREA 485834690
4564573486943 AREA 583475386
2348235724137 DISTRICT 242752348
6975067587684 DISTRICT 664568483

How is that possible? Most likely it's a setting I configured wrongly but what worries me is that mostly everything goes right and without changing anything it could be mixed up.

Can you please help me?

Anyway, thanks a lot!

Greetz,

Bart.SQL Server will maintain the record order as inserted UNLESS you apply a clustered index. I would check for indexes the next time this happens. Can anyone esle in your organization change the schema?

Also, do you use only one process to read the input files? Two process could also produce a mixed order.

Could you add an identity column to preserve the inserted order?|||Hello Paul,

First of all a big thank you for your reply.

Well, let me answer your questions first ;)

I don't use clustered indexes and I don't use multiple read processes. So I only import one text file at a time.

I have also tried to add an identity field to a record. In my ActiveX import script (vbscript) I test on the used level. If it's "NATIONAL" I put "1" into the ID column, when it's "RESEAU" I put "2" into the ID column and so on.

So I import everything into a temporary table and afterwards I put everything with an "ORDER BY ID ASC" into the final table.

You won't believe it, but even after that "ORDER BY" I SOMETIMES, not always, have a wrong order into the final table. So it's possible that in the final table I have such an ID column

1
2
2
2
2
3
3
3
3
3
4
4
3
3
3
5
5
5

:mad: You can surely understand that this irritates me a lot.

How is that possible?

Greetz, :confused:

Bart.|||By definition record order is unimortant in a relational structure. SQL Server does not guarantee row order because internally it stores the data in pages that may or may not be sequential.

Why not just apply the 'ORDER BY' clause when you retrieve the records from the database -- that way it makes no difference which order they are stored. An index on that field would speed this operation up a great deal.

BTW, IDENTITY fields are auto-incrementing -- you should not be trying to insert discrete values.

Regards,
Jason Woosley
SQL Server DBA|||Thanks for your reply Jason.

Your last solution is exactly what I tried last week. And indeed, this works perfectly. I only forgot to post this answer onto the messageboard.

Greetz,

Bart.sql

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 importing access field into mssql

having a problem with one single field
its coming up with the error message "data for source column 2 ('general
notes') is too large for the specified buffer size"
how would i fix this ?
thanks
mark
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> What medium are you using to transfer the data and from
> what source.
> Do the field sizes of the to and from match ?
> J
>
im converting from access using DTS - this is the only field i've had
trouble with
the original field is a memo field which im trying to transfer into a sql
nvarchar field with 2000 chars - there is a lot of data in the original
source tho - and its not nice data!
cheers
mark
|||Try NTEXT as destination field instead of NVARCHAR one
"mark" <mark@.remove.com> wrote in message news:1Q4qc.90$LH3.0@.newsfe1-win...
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> im converting from access using DTS - this is the only field i've had
> trouble with
> the original field is a memo field which im trying to transfer into a sql
> nvarchar field with 2000 chars - there is a lot of data in the original
> source tho - and its not nice data!
|||Hello,
According to the Access help a memo field can store up to
65000 + character.
The nvarchar(2000) can store up to 1000 characters (look
up unicode in BOL). Try changing the SQL Server datatype
to a text or binary.
J

>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
>im converting from access using DTS - this is the only
field i've had
>trouble with
>the original field is a memo field which im trying to
transfer into a sql
>nvarchar field with 2000 chars - there is a lot of data
in the original
>source tho - and its not nice data!
>cheers
>mark
>
>.
>
|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e12301c43c1d$dbca1a70$a101280a@.phx.gbl...
> Hello,
> According to the Access help a memo field can store up to
> 65000 + character.
> The nvarchar(2000) can store up to 1000 characters (look
> up unicode in BOL). Try changing the SQL Server datatype
> to a text or binary.
> J
>
thanks everyone for the help, converting to ntext did the trick!
mark

problem importing access field into mssql

having a problem with one single field
its coming up with the error message "data for source column 2 ('general
notes') is too large for the specified buffer size"
how would i fix this ?
thanks
markWhat medium are you using to transfer the data and from
what source.
Do the field sizes of the to and from match ?
J
>--Original Message--
>having a problem with one single field
>its coming up with the error message "data for source
column 2 ('general
>notes') is too large for the specified buffer size"
>how would i fix this ?
>thanks
>mark
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> What medium are you using to transfer the data and from
> what source.
> Do the field sizes of the to and from match ?
> J
>
im converting from access using DTS - this is the only field i've had
trouble with
the original field is a memo field which im trying to transfer into a sql
nvarchar field with 2000 chars - there is a lot of data in the original
source tho - and its not nice data!
cheers
mark|||Try NTEXT as destination field instead of NVARCHAR one
"mark" <mark@.remove.com> wrote in message news:1Q4qc.90$LH3.0@.newsfe1-win...
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> > What medium are you using to transfer the data and from
> > what source.
> >
> > Do the field sizes of the to and from match ?
> >
> > J
> >
> im converting from access using DTS - this is the only field i've had
> trouble with
> the original field is a memo field which im trying to transfer into a sql
> nvarchar field with 2000 chars - there is a lot of data in the original
> source tho - and its not nice data!|||Hello,
According to the Access help a memo field can store up to
65000 + character.
The nvarchar(2000) can store up to 1000 characters (look
up unicode in BOL). Try changing the SQL Server datatype
to a text or binary.
J
>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
>> What medium are you using to transfer the data and from
>> what source.
>> Do the field sizes of the to and from match ?
>> J
>im converting from access using DTS - this is the only
field i've had
>trouble with
>the original field is a memo field which im trying to
transfer into a sql
>nvarchar field with 2000 chars - there is a lot of data
in the original
>source tho - and its not nice data!
>cheers
>mark
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e12301c43c1d$dbca1a70$a101280a@.phx.gbl...
> Hello,
> According to the Access help a memo field can store up to
> 65000 + character.
> The nvarchar(2000) can store up to 1000 characters (look
> up unicode in BOL). Try changing the SQL Server datatype
> to a text or binary.
> J
>
thanks everyone for the help, converting to ntext did the trick!
marksql

problem importing access field into mssql

having a problem with one single field
its coming up with the error message "data for source column 2 ('general
notes') is too large for the specified buffer size"
how would i fix this ?
thanks
mark"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> What medium are you using to transfer the data and from
> what source.
> Do the field sizes of the to and from match ?
> J
>
im converting from access using DTS - this is the only field i've had
trouble with
the original field is a memo field which im trying to transfer into a sql
nvarchar field with 2000 chars - there is a lot of data in the original
source tho - and its not nice data!
cheers
mark|||Try NTEXT as destination field instead of NVARCHAR one
"mark" <mark@.remove.com> wrote in message news:1Q4qc.90$LH3.0@.newsfe1-win...
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> im converting from access using DTS - this is the only field i've had
> trouble with
> the original field is a memo field which im trying to transfer into a sql
> nvarchar field with 2000 chars - there is a lot of data in the original
> source tho - and its not nice data!|||Hello,
According to the Access help a memo field can store up to
65000 + character.
The nvarchar(2000) can store up to 1000 characters (look
up unicode in BOL). Try changing the SQL Server datatype
to a text or binary.
J

>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
>im converting from access using DTS - this is the only
field i've had
>trouble with
>the original field is a memo field which im trying to
transfer into a sql
>nvarchar field with 2000 chars - there is a lot of data
in the original
>source tho - and its not nice data!
>cheers
>mark
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e12301c43c1d$dbca1a70$a101280a@.phx.gbl...
> Hello,
> According to the Access help a memo field can store up to
> 65000 + character.
> The nvarchar(2000) can store up to 1000 characters (look
> up unicode in BOL). Try changing the SQL Server datatype
> to a text or binary.
> J
>
thanks everyone for the help, converting to ntext did the trick!
mark