DTS/bcp - export to a text file.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS/bcp - export to a text file.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-26 : 09:13:54

Suhas writes "Hi,

I am using DTS (MS SQL Server 7.0) to run a query that
would output data to a text file, but got the following
error message: "Incomplete file format information - file
cannot be opened".

Here is my query:

DECLARE @GMTOffset integer

SELECT @GMTOffset = 6 * 3600

SELECT a.PartnerKEY,
a.DocumentName,
a.FunctionalGroupId,
a.TransactionSetId,
a.ComplianceStatus as ASNComplianceStatus,
dateadd(second,(a.TimeCreated -
@GMTOffset),'1/1/1970') as ASNTimeCreated,
a.ReferenceData,
b.AckStatus,
dateadd(second,(b.AckTime -
@GMTOffset),'1/1/1970') as FAAckTime,
b.DocumentControlNumber as ASNSTControlNumber,
b.GroupControlNumber as ASNGSControlNumber,
b.InterchangeControlNumber as ASNISAControlNumber
INTO ##ASNInfo
FROM Document_tb a, Track_tb b
WHERE a.DocumentKEY = b.DocumentKEY
AND a.Direction = 1
AND a.TransactionSetId = '856'
AND a.LocationStatus = 1
AND a.TestMode = 0;

SELECT a.*,
b.ComplianceStatus as FAComplianceStatus,
c.DocumentControlNumber as FASTControlNumber,
c.GroupControlNumber as FAGSControlNumber,
c.InterchangeControlNumber as FAISAControlNumber,
dateadd(second,(b.TimeCreated -
@GMTOffset),'1/1/1970') as FATimeCreated
INTO ##ASNFAInfo
FROM ##ASNInfo a, Document_tb b, Track_tb c
WHERE a.ASNGSControlNumber = b.ReferenceData
AND a.FunctionalGroupId = b.DocumentName
AND a.PartnerKEY = b.PartnerKEY
AND b.DocumentKEY = c.DocumentKEY
AND b.DocumentName = 'SH'
AND b.Direction = 0
AND b.TransactionSetId = '997'
AND b.LocationStatus = 0
AND b.TestMode = 0;

SELECT a.*
FROM ##ASNFAInfo a;

I am not quite sure why it is failing (I have tried using
a global temp table, since I am accessing data across
multiple steps). Also, can I use bcp to run this query? How?

Any suggestions?

Thanks,
Suhas

PS: I am choosing the "source" as: Microsoft OLE DB
Provider for SQL Server" & my "destination" is a comma
delimited text file."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-26 : 10:00:27

Easier just to put it all in a stored procedure.
Given that you have create a global temp table then something like this at the end of the SP before dropping the tables.

exec master..cmdshell('bcp dbname..##ASNFAInfo out c:\mytext.txt -Ssvrname -Uusrname -Ppassword -c')

Try it first on just a fixed table to get it working.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources