SQL Server 2000 SP3

I'm attempting to create a DTS package that exports data from a stored proc
to a text file. When the package is run I get the
following error:
"Incomplete file format information - file cannot be opened."

The proc pulls data from a single table and aliases the column names in the
result set. I'm wondering if this might be the source of the problem as
exporting from the table directly works fine. Could aliased column names
fluster the DTS?

I've tried different storage locations for the exported file to ensure the
issue isn't permissions.

I may be on the wrong track, it's just a guess really. Any help is
appreciated. TIA.

Andrew

Hello Andrew" andrew_test(dont spam its not,


aliased column names should not present an issue.


In the proc do you have as the first line

SET NOCOUNT ON

Maybe there are multiple resultsets coming back to the proc.


I would aslo expect to see this error when a file is not availbale to be
written to so say you were using the file as a source and the thing that
wrote the file to text had not finished yet.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hello Allan,

Thank you for responding so quickly. It's like having a DTS SWAT team
available... :-)

I added SET NOCOUNT ON to the beginning of the proc, however I still get the
same error. The proc builds a dynamic SQL statement and calls EXEC(
@strSQL ) to get the result set. I'd be glad to post the proc code if you
think it would be helpful.

The .txt file is the destination and should be created on the fly.

Thanks again for your attention to this.

Andrew

Hello Andrew" andrew_test(dont spam its not,

So EXEC() executes in a different security context to the proc. It executes
as the user calling the proc.

Besides that have you seen this

http://support.microsoft.com/default...EN-US;Q247108&



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hello Allan,

The user calling the proc is in the System Administrators server role and
the proc owner is dbo. I did see the kb article you referenced although
that refers to 7.0 and I'm running 2k SP3.

Part of the proc's code is shown below:


/*** Begin code sample ***/
create procedure proc_MyProc ...
....
while( @@FETCH_STATUS = 0 )
begin
set @strSQL = @strSQL + 'ltrim(rtrim(dbo.fn_StripSingleChar(' +
@strFieldName + ', '','' ))) as ' + @strFieldName + ', '

fetch next from curFields into @strFieldName
end

set @strSQL = left( @strSQL, len( @strSQL ) - 1 ) + ' from ' + @strTable
EXEC( @strSQL )
/*** End code sample ***/

When creating the DTS package the following line is used for the query:
EXEC proc_MyProc 'myTable'

It seems as though the DTS cannot determine the source columns, but I'm not
sure. I've got a couple work-arounds (bcp, etc... ) but would like to use
the DTS to keep things consolidated.

Thank you.

Hello Andrew" andrew_test(dont spam its not,

So when you go to map the source and destination columns in the transform
data task you can see everything?


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

No, the Transformations tab is effectively empty. There are no source or
destination columns.

Hello Andrew" andrew_test(dont spam its not,

I didn't think so. The query you have does not expose columns to the outside
world. The driver is unable to read the insofmration from the query. The
query does what you want but the Transform Data Task cannot read the column
info and therefore cannot use it.

Make sense?


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hello Allan,

Yes, that makes sense. The proc is designed so that if the underlying table
structure changes the proc won't break/need updated.

I'm sure I don't fully comprehend all that it takes to write a tool such as
the SQL DTS but I do think this particular scenario should work. All that
really needs to happen is a result set be copied to a text file. I know
there could be unforeseen circumstances here such as BLOB's being in the
result set etc..., but there should be some freedom to allow the DTS to
execute the task even if it cannot determine the names and types of the
result set. IMHO.

I'm grateful for your expertise and help Allan. Thank you.

Andrew


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Hello Andrew" andrew_test(dont spam its not,

OK So let's say it could read the statement. Your statement then completely
changes. DTS will not scrap the source and destination columns and build
new ones and then auto remap things.

It will not do this even when it can read from a DB table which is why I
have an elaborate demo package to show how it can be done with a lot of jiggery
pokery.




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com