Implicit queries: error

Showing posts with label error. Show all posts

Friday, March 30, 2012

Import Password Protected File Via DTS

I am trying to import a password-protected Excel file into SQL Server
using DTS. I am getting an error that it can't decrypt file. Does
anyone know how I can pass the password to the file during the DTS
execution. Please help.

Thanks,
MichelleThe Excel connection object has a Password property - you can set it
using a dynamic properties task or an ActiveX task.

Simon

Import of MSDTC transaction failed

Error: 8509, State: 1, Message: Import of MSDTC transaction failed: Result
Code = 0x8004d00e
I can not find any help on many websites.
How to do it?
The error message says that SQL Server can not enlist in the transaction
that is provide because the transaction is already implicitly or explicitly
committed or aborted.
Which OS are you on? If you are using Windows XP or Windows Server 2003 you
can use DTC trace to find out why the transaction import failed. If you do
not have this OS, you could use AppMetrics from ExtermeSoft see
http://www.xtremesoft.com
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"lv mingtong" <lmt@.taeco.com.discuss> wrote in message
news:eUww2DIlEHA.1644@.tk2msftngp13.phx.gbl...
> Error: 8509, State: 1, Message: Import of MSDTC transaction failed: Result
> Code = 0x8004d00e
> I can not find any help on many websites.
> How to do it?
>
|||I too am having this issue. The db is running on Win XP Professional,
SQL Server 2K + ASP.NET app. It is an intermittent issue and occurs
every 4th hit to any page. I am using
System.EnterpriseServices.AutoCompleteAttribute(Tr ue) to auto commit
the transaction. The strange thing is that if I hit the maching
remotely it is fine...If I do it locally the error will appear.
Eg.
Scenario #1: Works and is fine:
Machine A = Web App, dll's etc
Machine B = DB server
Scenario #2: Generates the error "Import of MSDTC transaction failed:
Result Code = 0x8004d00e."
Machine A = Web App, dll's etc + DB server
It is a strange issue that is not constant.
Any suggestions will be attempted...no matter how bizzare

Thanks
|||I too am having this issue. The db is running on Win XP Professional,
SQL Server 2K + ASP.NET app. It is an intermittent issue and occurs
every 4th hit to any page. I am using
System.EnterpriseServices.AutoCompleteAttribute(Tr ue) to auto commit
the transaction. The strange thing is that if I hit the maching
remotely it is fine...If I do it locally the error will appear.
Eg.
Scenario #1: Works and is fine:
Machine A = Web App, dll's etc
Machine B = DB server
Scenario #2: Generates the error "Import of MSDTC transaction failed:
Result Code = 0x8004d00e."
Machine A = Web App, dll's etc + DB server
It is a strange issue that is not constant.
Any suggestions will be attempted...no matter how bizzare

Thanks
sql

Import of MSDTC transaction failed

Error: 8509, State: 1, Message: Import of MSDTC transaction failed: Result
Code = 0x8004d00e
I can not find any help on many websites.
How to do it?The error message says that SQL Server can not enlist in the transaction
that is provide because the transaction is already implicitly or explicitly
committed or aborted.
Which OS are you on? If you are using Windows XP or Windows Server 2003 you
can use DTC trace to find out why the transaction import failed. If you do
not have this OS, you could use AppMetrics from ExtermeSoft see
http://www.xtremesoft.com
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"lv mingtong" <lmt@.taeco.com.discuss> wrote in message
news:eUww2DIlEHA.1644@.tk2msftngp13.phx.gbl...
> Error: 8509, State: 1, Message: Import of MSDTC transaction failed: Result
> Code = 0x8004d00e
> I can not find any help on many websites.
> How to do it?
>|||I too am having this issue. The db is running on Win XP Professional,
SQL Server 2K + ASP.NET app. It is an intermittent issue and occurs
every 4th hit to any page. I am using
System.EnterpriseServices.AutoCompleteAttribute(True) to auto commit
the transaction. The strange thing is that if I hit the maching
remotely it is fine...If I do it locally the error will appear.
Eg.
Scenario #1: Works and is fine:
Machine A = Web App, dll's etc
Machine B = DB server
Scenario #2: Generates the error "Import of MSDTC transaction failed:
Result Code = 0x8004d00e."
Machine A = Web App, dll's etc + DB server
It is a strange issue that is not constant.
Any suggestions will be attempted...no matter how bizzare :)
Thanks|||I too am having this issue. The db is running on Win XP Professional,
SQL Server 2K + ASP.NET app. It is an intermittent issue and occurs
every 4th hit to any page. I am using
System.EnterpriseServices.AutoCompleteAttribute(True) to auto commit
the transaction. The strange thing is that if I hit the maching
remotely it is fine...If I do it locally the error will appear.
Eg.
Scenario #1: Works and is fine:
Machine A = Web App, dll's etc
Machine B = DB server
Scenario #2: Generates the error "Import of MSDTC transaction failed:
Result Code = 0x8004d00e."
Machine A = Web App, dll's etc + DB server
It is a strange issue that is not constant.
Any suggestions will be attempted...no matter how bizzare :)
Thanks

Monday, March 26, 2012

import html file error

Im trying to import an html file using dts. When I click on the file when
using dts designer, I get the message:
External table is not in the expected format.
Any ideas?
SQL2K SP3
TIA, ChrisR
In message <udOcQh54EHA.936@.TK2MSFTNGP12.phx.gbl>, ChrisR
<bla@.noemail.com> writes
>Im trying to import an html file using dts. When I click on the file when
>using dts designer, I get the message:
>External table is not in the expected format.
>Any ideas?
>--
>SQL2K SP3
>TIA, ChrisR
>
Does the error not say it all? The HTML connection cannot
parse/recognise a valid html table in that file. No table, no import.
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

import html file error

Im trying to import an html file using dts. When I click on the file when
using dts designer, I get the message:
External table is not in the expected format.
Any ideas?
--
SQL2K SP3
TIA, ChrisRIn message <udOcQh54EHA.936@.TK2MSFTNGP12.phx.gbl>, ChrisR
<bla@.noemail.com> writes
>Im trying to import an html file using dts. When I click on the file when
>using dts designer, I get the message:
>External table is not in the expected format.
>Any ideas?
>--
>SQL2K SP3
>TIA, ChrisR
>
Does the error not say it all? The HTML connection cannot
parse/recognise a valid html table in that file. No table, no import.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Import From TAB File Error

I am trying to import data into a table in my database using DTS.
The table (SYS_DD_SIC_Codes) has the following structure:
[SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
The import procedure works, but then fails at the very last line in the TAB
file, giving an error that it cannot insert a NULL value into [SIC_ID] for
the last line in the TAB file.
This is correct as the fields are all NOT NULL, but why is it trying to
insert a NULL value when none exist in the TAB file to insert?Do you have an extra line in your text file?
>--Original Message--
>I am trying to import data into a table in my database
using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very
last line in the TAB
>file, giving an error that it cannot insert a NULL value
into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why
is it trying to
>insert a NULL value when none exist in the TAB file to
insert?
>
>.
>|||There is probably a blank line at the end of the file. You
could try adding a where clause along the lines of: where
sic_id is not null.
-Sue
On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
>I am trying to import data into a table in my database using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very last line in the TAB
>file, giving an error that it cannot insert a NULL value into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why is it trying to
>insert a NULL value when none exist in the TAB file to insert?
>|||I have checked and there is not an extra line at the end of the file!
"keene" <anonymous@.discussions.microsoft.com> wrote in message
news:1978201c41ca2$dd0a3590$a401280a@.phx.gbl...
> Do you have an extra line in your text file?
> >--Original Message--
> >I am trying to import data into a table in my database
> using DTS.
> >
> >The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_80] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> >The import procedure works, but then fails at the very
> last line in the TAB
> >file, giving an error that it cannot insert a NULL value
> into [SIC_ID] for
> >the last line in the TAB file.
> >
> >This is correct as the fields are all NOT NULL, but why
> is it trying to
> >insert a NULL value when none exist in the TAB file to
> insert?
> >
> >
> >.
> >|||I have checked and there is not an extra line at the end of the file!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hsv7705e7u3eg7braj1hhqv5qsk5de57j4@.4ax.com...
> There is probably a blank line at the end of the file. You
> could try adding a where clause along the lines of: where
> sic_id is not null.
> -Sue
> On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
> >I am trying to import data into a table in my database using DTS.
> >
> >The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> > [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> >The import procedure works, but then fails at the very last line in the
TAB
> >file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
> >the last line in the TAB file.
> >
> >This is correct as the fields are all NOT NULL, but why is it trying to
> >insert a NULL value when none exist in the TAB file to insert?
> >
>|||Check the last line, maybe you're missing a tab (so the line is being
imported as 3 columns rather than four)?
"Keith" <@..> wrote in message news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> I am trying to import data into a table in my database using DTS.
> The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> The import procedure works, but then fails at the very last line in the
TAB
> file, giving an error that it cannot insert a NULL value into [SIC_ID] for
> the last line in the TAB file.
> This is correct as the fields are all NOT NULL, but why is it trying to
> insert a NULL value when none exist in the TAB file to insert?
>|||I have checked this, but it is correct that only 3 columns out of the 4 are
being imported. The first column is an IDENTITY column which autonumbers
itself so does not require data importing into it. I have made sure that
DTS is aware of the IDENTITY column.
I am so confused by this.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O5Zv6sKHEHA.2576@.TK2MSFTNGP09.phx.gbl...
> Check the last line, maybe you're missing a tab (so the line is being
> imported as 3 columns rather than four)?
>
> "Keith" <@..> wrote in message
news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> > I am trying to import data into a table in my database using DTS.
> >
> > The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> > [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> > The import procedure works, but then fails at the very last line in the
> TAB
> > file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
> > the last line in the TAB file.
> >
> > This is correct as the fields are all NOT NULL, but why is it trying to
> > insert a NULL value when none exist in the TAB file to insert?
> >
> >
>

import from Excel error

While attempting to import data from Excel using SSMS and the import/export wizard, I received the following error:

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 "{11CD789E-0DCD-48C8-81F9-1065D87B5ADF}" 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.
({BB3EBEA7-7F0E-4346-A8A5-60E176732365})

Any clues as what causes this and how to resolve the problem. I am using Office XP.

Do you have Microsoft Jet OLE DB provider installed? It should be there by default. Check whether you are missing msjetoledb40.dll, or whether it is correctly registered may help.

HTH

wenyang

|||The dll is installed and I ran regsvr32; it registered successfully. However, the problem still exists.

Friday, March 23, 2012

Import export failed : Data conversion failed

[Source - chn_employee_vew_test_txt [1]] Error: Data conversion failed. The data conversion for column "Column 42" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Source - chn_employee_vew_test_txt [1]] Error: The "output column "Column 42" (136)" failed because truncation occurred, and the truncation row disposition on "output column "Column 42" (136)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

I using Locale (People's Republic of China) and code page of 936 (Simplied Chinese GBK) with header row delimiter {CR}{LF}.

I am using flat file import method.

Whenever the server process the Column 42 with value "11,Nanjing Rd.W, China" which contain 'comma' or '.' it will hit error importing with above message. When i manually change the column value to non comma or '.' (11 Nanjing Rd W China) in the flat file it is ok.

I am using SQL server 2005.

Please advise what need to be done to avoid this error ?

Thanks in advance and any idea or suggestion is very much appreciated as i have try to solve this issue for over a week but still not able to find any answer on it.

Please help.

regards,

kong

Check the Quoted_Identifier and text_qualifier values in SSIS, if you are running from a workstation then make sure SSIS server component is installed

Wednesday, March 21, 2012

import excel

need some help with getting this stored procedure working.
I'm trying to import excel spreadsheet into an existing table
getting error message of syntax near "*"
===========================================
CREATE PROCEDURE [dbo].importExcel
AS
--Create linked server
EXEC sp_addlinkedserver 'ExcelSource', 'Jet
4.0','Microsoft.Jet.OLEDB.4.0','C:\temp\tables.xls ', NULL,'Excel 5.0'
GO
--login to linked server
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
-- import spreadsheet data into database table...
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
DECLARE @.SQLString1 VARCHAR(100)
SET @.SQLString1 = 'SELECT * FROM ExcelSource...[table$]'
DECLARE @.SQLString2 VARCHAR(100)
SET @.SQLString2 = 'INSERT INTO table ' + @.SQLString1
-- convert variable from VARCHAR to NVARCHAR
DECLARE @.S2 NVARCHAR(1000)
SET @.S2 = CAST(@.SQLString2 as NVarchar(1000))
EXECUTE sp_executesql @.S2
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
GO
hi,
"TJS" <nospam@.here.com> ha scritto nel messaggio
news:10qlms38norp6e3@.corp.supernews.com
> need some help with getting this stored procedure working.
> I'm trying to import excel spreadsheet into an existing table
> getting error message of syntax near "*"
> SET @.SQLString1 = 'SELECT * FROM ExcelSource...[table$]'
what is the name of the Excel sheet?
usually it defaults to Sheet1$, so the correct syntax is
SET @.SQLString1 = 'SELECT * FROM ExcelSource...[Sheet1$]'
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||the name is correct , I am only testing at this time.
I did remove the extra GO statements and that seems to have resolved the
error messages.
I now have to find an ASP file to dynamically generate the sql statements
for each spreadsheet table and pass them into the stored procedure because
the live database tables have primary keys. That unfortunately requires
using column lists...
I can't believe somebody hasn't already done all this ?
sql

Import errors from Excel to SQL 2005 DB Table

How frustrating is this, all I want to peform is a one column to one column Import here!

Why am I getting this error when simply trying to import from my excel workbook to my table? I know excel has a 255 char cell, but I specified to only account for 100 characters during the SSIS Import wizard in Management Studio. This is using SQL 2005.
See the setup here http://www.webfound.net/excel_import.jpg
Then I get this error during the import:
Validating (Error)
Messages
Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "F1" with a length of 255 to database column "Name" with a length of 100.
(SQL Server Import and Export Wizard)

Warning 0x80047076: Data Flow Task: The output column "F2" (18) on output "Excel Source Output" (9) and component "Source - 'Root Levels$'" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "F1" with a length of 255 to database column "Name" with a length of 100.
(SQL Server Import and Export Wizard)

Warning 0x80047076: Data Flow Task: The output column "F2" (18) on output "Excel Source Output" (9) and component "Source - 'Root Levels$'" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - 'Root Levels$'" (1).
(SQL Server Import and Export Wizard)

My Excel doc simply has 2 columns of text, the second I'm ignoring. I am only importing the first column.

It seems to me what you are frustrated with are only warnings... the error you are receiving is this:

"Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - 'Root Levels$'" (1).
(SQL Server Import and Export Wizard)"

A couple questions for you...

1) What version/SKU of SQL Server are you using? (e.g. SQL Server 2005 Express Edition SP1)
2) Is SQL Server Integration Services also installed?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

I am having the same problem. I have tried importing simple CSV, Access 2003, Access 2007, and I get the same problem no matter what.

Messages

Warning 0x80047076: Data Flow Task: The output column "Column 0" (10) on output "Flat File Source Output" (2) and component "Source - ABBREV_txt" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

Warning 0x80047076: Data Flow Task: The output column "Column 1" (13) on output "Flat File Source Output" (2) and component "Source - ABBREV_txt" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

etc...

I am running SQL Server 2005 downloaded from MSDN.

I am admin everywhere and am using windows authen and sa user w/SQL Server - both no effect.

Mel

|||

SQL Server 2005 Express Edition has just been refreshed with Service Pack 2. Could you install the latest version and try again?

SQL Server 2005 Service Pack 2:
http://www.microsoft.com/sql/sp2.mspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Import errors from Excel to SQL 2005 DB Table

How frustrating is this, all I want to peform is a one column to one column Import here!

It seems to me what you are frustrated with are only warnings... the error you are receiving is this:

"Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - 'Root Levels$'" (1).
(SQL Server Import and Export Wizard)"

A couple questions for you...

1) What version/SKU of SQL Server are you using? (e.g. SQL Server 2005 Express Edition SP1)
2) Is SQL Server Integration Services also installed?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

I am having the same problem. I have tried importing simple CSV, Access 2003, Access 2007, and I get the same problem no matter what.

Messages

etc...

I am running SQL Server 2005 downloaded from MSDN.

I am admin everywhere and am using windows authen and sa user w/SQL Server - both no effect.

Mel

|||

SQL Server 2005 Express Edition has just been refreshed with Service Pack 2. Could you install the latest version and try again?

SQL Server 2005 Service Pack 2:
http://www.microsoft.com/sql/sp2.mspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Import errors from Excel to SQL 2005 DB Table

How frustrating is this, all I want to peform is a one column to one column Import here!

It seems to me what you are frustrated with are only warnings... the error you are receiving is this:

"Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - 'Root Levels$'" (1).
(SQL Server Import and Export Wizard)"

A couple questions for you...

1) What version/SKU of SQL Server are you using? (e.g. SQL Server 2005 Express Edition SP1)
2) Is SQL Server Integration Services also installed?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

I am having the same problem. I have tried importing simple CSV, Access 2003, Access 2007, and I get the same problem no matter what.

Messages

etc...

I am running SQL Server 2005 downloaded from MSDN.

I am admin everywhere and am using windows authen and sa user w/SQL Server - both no effect.

Mel

|||

SQL Server 2005 Express Edition has just been refreshed with Service Pack 2. Could you install the latest version and try again?

SQL Server 2005 Service Pack 2:
http://www.microsoft.com/sql/sp2.mspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

sql

Import Error

I receive unhelpful errors from attempting to import a ms access table into sql server 2005 using the import wizard. Here is the report.

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Error)

Messages

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (73).
(SQL Server Import and Export Wizard)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Success)

- Copying to [Company].[dbo].[EMPLOYEE] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

I received this type of error once before and got around it by removing all constraints from the table. I was able to import the table when the data types were nvarchar. I changed the data types to varchar and this occurred. How do I know what the message is specifically referring to or can I see more details of the import command. This is my first posting.

Charley,

Data conversion transformation requires SQL Server enterprise edition to be executed. see if this link provides more information

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=594086&SiteID=1

Import error

I am attempting to import an access mdb file from a remote server.
When I run the import wizard I get the follwing error:
"Cannot start your application. The Workgroup information file is missing or
opened exclusively by another user."
I have checked to see if the lock file is there but its not.
Any ideas ?Not sure if this will resolve the problem but you can check the Advanced
properties of the the Access connection and edit the System Database value
to point to the location of the mdw file.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Import dedicated Records from a Flatfile to a DB

Hello all

I got a Problem when I try to store Data from a Flatfile to a DB.

The following Error appears in the Progress Control:

An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Violation of PRIMARY KEY constraint 'PK_Products_1'. Cannot insert duplicate key in object 'dbo.Products'.".

I have a Flat File Source, and would like to store the needed records in a DB.

In Column 0 in the Flatfile I have multiple Entries with equal Values.

In the DB this Column is set as Primary Key and can only have one Record with the same Value in this Column.

How can I read out (or store) only one Record with the same Value from the Flatfile to store it in the DB?

How can I check if there is a Record from the Flatfile in the DB with the same value in the Primary Key?

How can I change any of the remaining Columns with different Values in the DB to matchwith the Flatfile?

Thanks in advance for any answer

Chaepp

Chaepp wrote:

Hello all
I got a Problem when I try to store Data from a Flatfile to a DB.
The following Error appears in the Progress Control:
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Products_1'. Cannot insert duplicate key in object 'dbo.Products'.".
I have a Flat File Source, and would like to store the needed records in a DB.
In Column 0 in the Flatfile I have multiple Entries with equal Values.
In the DB this Column is set as Primary Key and can only have one Record with the same Value in this Column.

How can I read out (or store) only one Record with the same Value from the Flatfile to store it in the DB?

How can I check if there is a Record from the Flatfile in the DB with the same value in the Primary Key?

How can I change any of the remaining Columns with different Values in the DB to match with the Flatfile?

Thanks in advance for any answer
Chaepp

your problem seems to cry out for a lookup transformation. you would need to lookup the primary key in the source to see if it exists. if the key already exists, then redirect the row to the error output where you can stage the data for further processing.|||

Hello,

Thanks for your quick answer.

The DB Table is empty and I like to load the data from the Flatfile....

Then I get the error. I tried to go with Lookup, but I have still the same problem...

Could you give me an idea how to solve the problem?

Thanks for an early answer.

Regards

Chaepp

|||

Chaepp wrote:

Hello,
Thanks for your quick answer.
The DB Table is empty and I like to load the data from the Flatfile....
Then I get the error. I tried to go with Lookup, but I have still the same problem...
Could you give me an idea how to solve the problem?
Thanks for an early answer.
Regards
Chaepp

ok, i'll take another stab at it. you could try to use the ole db transformation to insert the rows, and redirect the error rows.|||

Sorry, I'm a beginner.

What do you mean with 'ole db transformation'? The Ole DB Command?

Regards

Chaepp

|||Hi ,
I like to see how this one pans out, as i cannot see how a Lookup would work. By definition Chaepp is importing to a table where the data does not already exist thus the lookup would fail. He could redirect this 'failure' as his error output, but then all his errors would be cached and thus still fail for the same reason as he would have multiple failures on the same data and thus when the data flow passes into the destination to actually write to his database table, it will still fall over. I could see a Lookup working if it would write each row it did not find to the destination table and thus subsequent finds , ie PK violations, could be re-directed, but only if the table is being updated in real time from the lookup which it does not do. I'm thinking a Script component here, not lookup.

Dave|||I tested this in a script component and got it to work. The input iwas a simple flat file of one column that had a couple of duplicates. Destination was a one colum table with a PK on it. The arraylist checks if the current row exists in the array already, if it does it gets ignored, otherwise we add a row to the output buffer which is then sent down the line to the destination task. This works but I could not get it to work from scratch - I had to cut and paste a script component I'd used before into the package because i could not get a new one to generate the Public Class Output0Buffer.... statements. (These are in the file with ' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! at the top so they are system generated as part of the Script Task, I just need to figure out how i got them generated the first time and then we can get the code below to work from a new Script component)

Dim alist As New ArrayList

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Not alist.Contains(Row.Name) Then
alist.Add(Row.Name)
Output0Buffer.AddRow()
Output0Buffer.NameOut = Row.Name
End If
End Sub

the Name property of Row comes from the flat file as the Input column
NameOut of Outpu0Buffer is the Script Output column|||

Thank you very much for your help.

I have found another solution:

I simply use the Aggregate function and it works perfect....

Regards

Chaepp

|||

Chaepp wrote:

Hi
Thank you very much for your help.
I have found another solution:
I simply use the Aggregate function and it works perfect....
Regards
Chaepp

can you please be a little more specific? i don't see how the aggregate transformation would solve your problem.

btw, by "ole db" i meant "ole db command". sorry about the confusion.

Monday, March 12, 2012

Import data from MS SQL Server - error

I have a remote database which I need to make a local copy of. I have sucessfully scripted the db and created it locally. however I cannot import the data to the local server. I get this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot truncate table

i think i am doing everything ok but it still wont work.dbo.xxx beacause its being referenced by a foreign key constraint.

Any ideaas welcome.

Gary

Apparently, the process is attempting to TRUNCATE the table -and since there is an established PK-FK relationship on the table, TRUNCATE will not work.

You can either
(a) DROP the FK CONSTRAINT, inport the data, recreate the FK CONSTRAINT, or
(b) USING "DELETE FROM MyTable", remove all of the rows of data, or
(c) Load the Parent table first before loading the child table.

|||

my question is why don't u use backup/restore method to get the database created localy. the only reason for not doing so is .. you don't want all the object created in the local copy... if that is the case then ok... otherwise go for backup/restore method

Madhu

import data from excel sheet to sql server

i am using an OPENROWSET method to import from excel data to sql server. i a
m getting an error like
ADO error:[OLE/DB provider returned message: Operation wa canceled.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitializ
Initialize returned 0x80040e4e
Could not initialize data source object of OLE DB provider "MSDASQL'.
Deferred prepare could not be completed
i want to reinstall the OLE DB Driver.can anyone tell me the exact link to d
owload the OLE DB driver.
From http://www.developmentnow.com/g/100...ver-connect.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.comThe provider is installed with MDAC. You can download MDAC
versions and also download component checker to check the
MDAC version from:
http://msdn.microsoft.com/data/ref/mdac/downloads/
The exact link depends on which version you want to
reinstall.
-Sue
On Tue, 01 Aug 2006 10:19:53 GMT, shybi<ajesh@.k.st> wrote:

>i am using an OPENROWSET method to import from excel data to sql server. i
am getting an error like
>ADO error:[OLE/DB provider returned message: Operation wa canceled.]
>OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitializ
>Initialize returned 0x80040e4e
>Could not initialize data source object of OLE DB provider "MSDASQL'.
>Deferred prepare could not be completed
>i want to reinstall the OLE DB Driver.can anyone tell me the exact link to
dowload the OLE DB driver.
>From http://www.developmentnow.com/g/100...elopmentnow.com|||I think you want to be using a different provider instead of "MSDASQL."
Try: Microsoft.Jet.OLEDB.4.0
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"shybi" <ajesh@.k.st> wrote in message
news:13b25c23-b877-4ac8-92dd-2d242c204a77@.developmentnow.com...
>i am using an OPENROWSET method to import from excel data to sql server. i
>am getting an error like
> ADO error:[OLE/DB provider returned message: Operation wa canceled.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitializ
> Initialize returned 0x80040e4e
> Could not initialize data source object of OLE DB provider "MSDASQL'.
> Deferred prepare could not be completed
> i want to reinstall the OLE DB Driver.can anyone tell me the exact link to
> dowload the OLE DB driver.
> From
> http://www.developmentnow.com/g/100...ver-connect.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

import data from Excel failed?

Hi,
I needed to import data from an Excel file to a table. The error I got was:
Error during Transformation 'DirectCopyXform' for Row number 4. Errors
encountered so far in this task: 1. TransformCopy
'DirectCopyXform' conversion error: Destination does not allow NULL on
column pair 1 (source column 'University ID' (DBTYPE_WSTR),
destination column 'universityid' (DBTYPE_STR)).
The university ID is of txt type in Excel. The universityid in SQL is of
varchar(9) and cannot by NULL.
Here is what I did. In EM, I selected All Tasks->Import Data..., then
selected Microsoft Excel 97-2000 as data source and MS SQL server as
destination. But when I clicked Preview to see how data in Excel was mapped
to the table, I noticed the universityid column is empty for some entries.
But their university ID cell is not empty in Excel. They just don't have
that tiny green arrow at the left upper coner of the cell as other Universit
y
IDs that are mapped fine in the import data preview. What might be the
problem and how should I fix?
Thanks in advance for any help.
BingI don't know Why it happens. It happens to me all the time. But you can
work around it.
Save the file as "Text (Tab delimited)" in execl then load the text file to
SQL server.
Regards,
Saravanan
"bing" <bing@.discussions.microsoft.com> wrote in message
news:BBBB27AB-8F68-45C6-812F-30386273CB44@.microsoft.com...
> Hi,
> I needed to import data from an Excel file to a table. The error I got
> was:
> Error during Transformation 'DirectCopyXform' for Row number 4. Errors
> encountered so far in this task: 1. TransformCopy
> 'DirectCopyXform' conversion error: Destination does not allow NULL on
> column pair 1 (source column 'University ID' (DBTYPE_WSTR),
> destination column 'universityid' (DBTYPE_STR)).
> The university ID is of txt type in Excel. The universityid in SQL is of
> varchar(9) and cannot by NULL.
> Here is what I did. In EM, I selected All Tasks->Import Data..., then
> selected Microsoft Excel 97-2000 as data source and MS SQL server as
> destination. But when I clicked Preview to see how data in Excel was
> mapped
> to the table, I noticed the universityid column is empty for some
> entries.
> But their university ID cell is not empty in Excel. They just don't have
> that tiny green arrow at the left upper coner of the cell as other
> University
> IDs that are mapped fine in the import data preview. What might be the
> problem and how should I fix?
> Thanks in advance for any help.
> Bing|||"Saravanan Palanivelu" wrote:

> I don't know Why it happens. It happens to me all the time. But you can
> work around it.
> Save the file as "Text (Tab delimited)" in execl then load the text file t
o
> SQL server.
>
Thanks very much for the quick response and most import the tip, Saravanan.
It works great. The only inconvenience is the wizard for importing data fro
m
text does not provide a way to skip, say, the last <number> of lines. For
some reason, the last several lines in my input text file have all 'NULL's
which I want them to be skipped. It can only skip if you know the line
number first.
Bing

Friday, March 30, 2012

Import Password Protected File Via DTS

Import of MSDTC transaction failed

Import of MSDTC transaction failed

Monday, March 26, 2012

import html file error

import html file error

Import From TAB File Error

Import From TAB File Error

Import From TAB File Error

import from Excel error

Friday, March 23, 2012

Import export failed : Data conversion failed

Wednesday, March 21, 2012

import excel

Import errors from Excel to SQL 2005 DB Table

Import errors from Excel to SQL 2005 DB Table

Import errors from Excel to SQL 2005 DB Table

Import Error

Import error

Import dedicated Records from a Flatfile to a DB

Monday, March 12, 2012

Import data from MS SQL Server - error

import data from excel sheet to sql server

import data from Excel failed?

Implicit queries

Blog Archive

About Me