Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 8th, 2004, 01:23 PM
Authorized User
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default DTS Scheduled Jobs Errors

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified.
(Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error,
column 1 ('ServerId', DBTYPE_I8), status 9: Permission denied.)
(Microsoft OLE DB Provider for SQL Server (80040e21):
Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0

If anyone could help, thanks,


CarlWalden
__________________
CarlWalden
  #2 (permalink)  
Old March 8th, 2004, 01:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Are you running the package under sa? Does the SQL Server machine account have access to the source file (I'm assuming this is the Excel file in your previous message)?
  #3 (permalink)  
Old March 8th, 2004, 02:04 PM
Authorized User
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, the package is running under SA, and the Server has access to the source and it is Excel. Thanks for your help.

CarlWalden
  #4 (permalink)  
Old March 8th, 2004, 06:25 PM
Authorized User
 
Join Date: Jul 2003
Location: Clearwater, FL, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In the options panel for the Data Transformation task, do you have the "Enable Identity Insert" turned on?

Thanks,
Jeff


  #5 (permalink)  
Old March 9th, 2004, 07:02 AM
Authorized User
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Joe Fawcett made the following comments:
If an identity column number is used it cannot be re-used, even if the record is deleted without manual intervention. Are you sure that no records were inserted and then removed, even via a transaction that was rolled back? Perhaps you could create a new table of identical structure except without the identity, import your old data and then set the identity seed etc? I was able to create the tbl and I got the following warning:
Warning: The table 'tblServer' has been created but its maximum row size (19003) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'tblServer' has been created but its maximum row size (19003) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. What does it mean? Regarding Jbelina queries in the options panel I have only ticked 'Source Error rows' and then on the SQL Server, I have ticked check constraints but the enable identity insert is not on (untick). Thanks for all your help. I shall remain.



CarlWalden
  #6 (permalink)  
Old March 9th, 2004, 09:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

SQL has a row size limitation, and it appears you are going WAY over it for one of your rows. How many fields do you have in the table, and how big is each field (large, medium, small, etc)? You may have to normalize the table.
  #7 (permalink)  
Old March 9th, 2004, 10:46 AM
Authorized User
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bmains,

Thanks for your inputs if you could further help me here how do I go about the normalisation procedures? If you put couple of line towards the procedures I would be really happy. Thanks and I look forward to hearing from you.

Regards,

CarlWalden
  #8 (permalink)  
Old March 9th, 2004, 03:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hello,

You'll probably want to get a good database book to go over normalization. I can't discuss all the theory here (as it would be to long), but basically:

1. Identify the repeating fields
2. Remove repeating fields and put in a separate table. For example, you duplicate the name of a credit card company in the table, which has the values Visa, MasterCard, etc., you rip out the credit card companies from the field, put the companies in a separate table and add an ID field, as such:
CreditCardCompany
-------------------
ID int identity
Name varchar(50)

and in the table, link to the ID as such

Table CreditCardCompany
--------------- ---------------------
..
..
FK:CreditCardID -> PK:CreditCardID

Hope this helps, let me know,

Brian
  #9 (permalink)  
Old March 10th, 2004, 08:43 AM
Authorized User
 
Join Date: Jul 2003
Location: Clearwater, FL, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The following article should help you learn more about normalization:

http://www.databasejournal.com/sqlet...le.php/1428511


  #10 (permalink)  
Old March 10th, 2004, 01:12 PM
Authorized User
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks folks for all your help but I have another questions some of you suggested normalization. That meant total redesign of the entire database, but what I have been able to do was the optimization of the entire tables and I can honestly say to you that all the kind of warnings and bottlenecks, etc, etc, have now been sorted. Now my question is this what are the difference between optimization & normalization? My basic understanding of normalization is to maintain referential integrity while optimization is on-going procedures to turn the database as best as possible. Am I right in thinking towards such direction?


CarlWalden
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
DTS scheduled jobs not finishing abob SQL Server DTS 0 July 13th, 2007 07:48 AM
Can only members of sysadmin schedule DTS jobs? Crowandazi SQL Server DTS 1 March 10th, 2005 02:53 AM
Unable to Schedule or See Existing DTS Jobs Crowandazi SQL Server DTS 1 March 4th, 2005 03:43 PM
DTS Scheduled Jobs Errors in DB (Misc) Walden SQL Server DTS 1 March 9th, 2004 08:13 AM
DTS Scheduled Jobs Errors in DB Walden SQL Server DTS 1 March 8th, 2004 11:55 AM



All times are GMT -4. The time now is 10:22 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.