I am attempting to set up a DTS package to export a view to tab-delimited
text, but there seems to be a bug that prevents me from having more that 22
columns involved. I can set up the package just fine if I only have 22
columns in my view, but if I add any more (even a small CHAR(3) field) then
DTS will not generate the field definitions for the text file.

In the package, I set up both connections just fine. When I add the
Transform Data Task, I choose the source view and can preview it just fine.
Then, I click on the Destination and DTS correctly pulls in all the
fieldsfrom the view. But, when I cilck on [Execute] DTS returns to the
destination tab and the field list is blank. If at that point I click on
[Define Columns...] then the entire SQL console crashes.

On the text file connection, I've tried both drive-letter and UNC path with
the same effect (... and paths valid for either my workstation or the server).

I've also tried various combinations of fields to see if the total data
length was the issue, but apparently it's the number of columns. For
example, if "Field22" is CHAR(40) and all is well with DTS, but adding
"Field23" CHAR(3) to the view causes it to fail, I could remove "Field22"
and add "Field23" - still all is okay. But if I then add "Field24" CHAR(1)
then it fails again. The point is, the combined data length is shorter with
23 and 24 (total length is 4) than having Field22 (total length 40) but it
still fails because there are more than 22 columns defined in the second
scenario.
--
-Chris

I had the same problem, took quite some time but I finally fixed it.
Believe it or not a MS security patch fixed the problem. I was running
SQL Server 2000 with SP3 when I ran into this problem. After I
installed MS security patch MS03-031 it worked just fine. Use the link
below to see Microsoft's bulletin.

http://www.microsoft.com/technet/sec.../MS03-031.mspx

RADDEX

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Chris,

I do agree with RADDEX that this scenario might caused by known issues in
DTS Wizard so that higher version hotfix may fix this issue. You could try
the Security Patch mentioned by RADDEX or contacting PSS asking the hotfix
from the following knowledge base articles:

FIX: An access violation exception may occur when you try to perform a Bulk
Import operation to insert data in a SQL Server table
http://support.microsoft.com/kb/839458

FIX: Rows are not successfully inserted into a table when you use the BULK
INSERT command to insert rows
http://support.microsoft.com/kb/837401

NOTE that just asking for hotfix from PSS will be a FREE Support incident.
You can contact Microsoft Product Support directly to discuss additional
support options you may have available, by contacting us at 1-(800)936-5800
or by choosing one of the options listed at
http://support.microsoft.com/directory/overview.asp


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Get Secure! - http://www.microsoft.com/security

This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Hello, Chris!
You wrote on Wed, 8 Dec 2004 13:57:02 -0800:

C> Then, I click on the Destination and DTS correctly pulls in all the
C> fieldsfrom the view. But, when I cilck on [Execute] DTS returns to
C> the destination tab and the field list is blank. If at that point I
C> click on [Define Columns...] then the entire SQL console crashes.
Hi Chris, Michael

I meet the same trouble, but I don't think that field's amount
is the issue. I can export really wide views only when this views belongs to
dbo. When another user is view owner, export fails exactly as you describe.
I'm not belonging to system administrator role on server where I get this
trouble. On my local server I can export any views.

This is a DTS engine's bug. Same trouble I get with my custom
task, that export data from table to text file. In this task i make
transformation by hand, but task fails when Execute metod called.

How about your situation? Do you system admin? Try to change
ownership of view to dbo (sp_changeobjectowner) and try to export.

Michael, can we ask Microsoft to help us if we don't have
support contract with MS? I can describe this situation in details.



Sincerely yours,

With best regards, Alexander Sinitsin. E-mail: al_sin[dog]ukr.net