Making a .txt file from SQL server2000 table og SP.

Hi all.
We use DTS and transformation manager to export data to flat .txt files.
E.g. pricelists, produkt stock information.
If we want to export many columns (more than 15 to 20) the Define columns
function in the DTS return blanks so I can't map the fields to the .txt
file. Then all EM hangs. We have tried a lot to fix this. I have attached
one select that fails.
Well, is there any other ways to automate output of .txt files for
SQLServer2000 prosedures or tables. E.g some 3.part programs that we can
scedule using SQL server agent?
Thanx all
geir
SELECT Top 10 RTrim(custItegraRptFilExport.Varenr) as ItemKey,
RTrim(custItegraRptFilExport.Varenr) as Itemid,
RTrim(custItegraRptFilExport.ProduktDesc1) as ItemName,
RTrim(custItegraRptFilExport.ProduktDesc1) + ' ' +
RTrim(custItegraRptFilExport.ProduktDesc1) as LongDesc,
RTrim(custItegraRptFilExport.GruppeLev1Txt) + ' ' +
RTrim(custItegraRptFilExport.GruppeLev2Txt) + ' ' +
RTrim(custItegraRptFilExport.GruppeLev3Txt) as Searchwords,
'' as SubItemOf,
'' as ReplacesItems,
RTrim(custItegraRptFilExport.ProdusentNavn) as Manufacturer,
RTrim(custItegraRptFilExport.LeverandProduktNr) as MfrItemID,
custItegraRptFilExport.Varenr as UNSPSC,
'13.1' as UNSPSC_Ver,
'http://www.itegra.no/aspx/prdinfo.aspx?plid=' +
Cast(custItegraRptFilExport.ProduktLagerID as varchar) as ImageURL,
'' as InfoURL,
custItegraRptFilExport.PrdBildeFileNavn as FileName,
'' as AttachmentFileName,
'' as AttachmentName,
'' as AttachmentDescription,
custItegraRptFilExport.PrdEAN as EAN,
'' as NATO_ID,
'' as Risk,
Cast(custItegraRptFilExport.Pris as decimal(13,2)) as Price,
1 as QuantityInPrice,
custItegraRptFilExport.Enhet,
Cast(custItegraRptFilExport.MvaPst as decimal(9,0)) as VAT,
1 as OrderMultiple,
1 as MinOrder,
Datediff(dd, getdate(), custItegraRptFilExport.ETADateFormat) as ETA,
'' as ETAText,
10 as Priority,
1 as InnerUnit,
Cast(custItegraRptFilExport.PrdKjopEnhAntall as decimal(9,0)) as
QuantityInUnit
FROM custItegraRptFilExport
WHERE custItegraRptFilExport.KundeID = 37177
Hi
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
--command
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SSERVERName -Usa -Ppass
--QA
exec master..xp_cmdshell 'BCP northwind..ww out
d:\test1.txt -c -C850 -SServerName -Usa -Ppass'
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23oumw4mnFHA.3316@.tk2msftngp13.phx.gbl...
> Hi all.
> We use DTS and transformation manager to export data to flat .txt files.
> E.g. pricelists, produkt stock information.
> If we want to export many columns (more than 15 to 20) the Define columns
> function in the DTS return blanks so I can't map the fields to the .txt
> file. Then all EM hangs. We have tried a lot to fix this. I have attached
> one select that fails.
> Well, is there any other ways to automate output of .txt files for
> SQLServer2000 prosedures or tables. E.g some 3.part programs that we can
> scedule using SQL server agent?
> Thanx all
> geir
>
> SELECT Top 10 RTrim(custItegraRptFilExport.Varenr) as ItemKey,
> RTrim(custItegraRptFilExport.Varenr) as Itemid,
> RTrim(custItegraRptFilExport.ProduktDesc1) as ItemName,
> RTrim(custItegraRptFilExport.ProduktDesc1) + ' ' +
> RTrim(custItegraRptFilExport.ProduktDesc1) as LongDesc,
> RTrim(custItegraRptFilExport.GruppeLev1Txt) + ' ' +
> RTrim(custItegraRptFilExport.GruppeLev2Txt) + ' ' +
> RTrim(custItegraRptFilExport.GruppeLev3Txt) as Searchwords,
> '' as SubItemOf,
> '' as ReplacesItems,
> RTrim(custItegraRptFilExport.ProdusentNavn) as Manufacturer,
> RTrim(custItegraRptFilExport.LeverandProduktNr) as MfrItemID,
> custItegraRptFilExport.Varenr as UNSPSC,
> '13.1' as UNSPSC_Ver,
> 'http://www.itegra.no/aspx/prdinfo.aspx?plid=' +
> Cast(custItegraRptFilExport.ProduktLagerID as varchar) as ImageURL,
> '' as InfoURL,
> custItegraRptFilExport.PrdBildeFileNavn as FileName,
> '' as AttachmentFileName,
> '' as AttachmentName,
> '' as AttachmentDescription,
> custItegraRptFilExport.PrdEAN as EAN,
> '' as NATO_ID,
> '' as Risk,
> Cast(custItegraRptFilExport.Pris as decimal(13,2)) as Price,
> 1 as QuantityInPrice,
> custItegraRptFilExport.Enhet,
> Cast(custItegraRptFilExport.MvaPst as decimal(9,0)) as VAT,
> 1 as OrderMultiple,
> 1 as MinOrder,
> Datediff(dd, getdate(), custItegraRptFilExport.ETADateFormat) as ETA,
> '' as ETAText,
> 10 as Priority,
> 1 as InnerUnit,
> Cast(custItegraRptFilExport.PrdKjopEnhAntall as decimal(9,0)) as
> QuantityInUnit
> FROM custItegraRptFilExport
> WHERE custItegraRptFilExport.KundeID = 37177
>
|||Hi Uri.
Thanx a lot. This was the hint I needed. I guess I can use a prosedurename
instead of the tablename if I want to do this without CREATE and DROP a
table.
Anyway, thanx again.
-geir
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O5yYz%23mnFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> --command
> bcp northwind.dbo.ww out d:\test1.txt -c -t, -SSERVERName -Usa -Ppass
> --QA
> exec master..xp_cmdshell 'BCP northwind..ww out
> d:\test1.txt -c -C850 -SServerName -Usa -Ppass'
>
> "Geir Holme" <geir@.multicase.no> wrote in message
> news:%23oumw4mnFHA.3316@.tk2msftngp13.phx.gbl...
>