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.
| Author |
Topic |
|
trxman
Starting Member
7 Posts |
 Posted - 2004-01-08 : 23:49:25
|
| G'dayWhen I export to a text file I cannot define the destination columns. If I press define columns a 2nd time enterprise manger Dr Watson's itself. I am trying to export to about 40 columns to a pipe delimited file.I can do a export data from the table using the wizard. When I save that package and investigate it the destination columns are blank, when I check the transformation they remap to nothing.Any ideas?cheersps Any running sp3a. Have read some stuuf about the effects of 3 but unable to find a fix |
|
|
trxman
Starting Member
7 Posts |
|
|
axisperfect
Starting Member
21 Posts |
Posted - 2004-11-06 : 00:34:09
|
I know this is a bit old, but here's hoping someone remembers?I've got sp3 (I think) installed. RanSELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')and got: 8.00.760,SP3,Developer Edition The production server is similar -- except it's on a standard edition.I can click "define columns" for up to ~30 columns, the moment I add one more select column (from populating from source), enterprise manager kills itself. Arrrrgh.Ideas? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-06 : 02:10:39
|
| Try DTS direct, rather tan through Enterprise Manger?Use BCP instead?Write a SELECT with string concatenation to do it? :-(Edit: Errmmm ... you did Service Page the tools as well as the server? (If they are all on one machine you are good-to-go)Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-06 : 14:21:57
|
| I think graz had a thread on this here some time ago and it was a bug in dts.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
axisperfect
Starting Member
21 Posts |
Posted - 2004-11-07 : 20:58:37
|
| Kristen: DTS direct? How do you do that? It's quite a complicated package (doing prior inserts/updates/selects before the actual export), is there away for me to pull out the t-sql code for it to run it in Query Analyzer?nr: I tried searching for messages by graz in the forum, but I can't see to guess the keywords for that thread: "export", "csv", "bug", "column".. Would you happen to know whether the bug was fix-able? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-08 : 02:13:13
|
| I was meaning to start DTS from the Start : Programs menu, rather than from within Enterprise Manager - but given the complexity, and the other answers, I reckon thats gonna be no use.I don't know enough about DTS to know how to get the SQL out - I suppose if I was doing it I would put any non-trivial SQL in a SProc, so that would be able to be run separately.Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-08 : 04:06:44
|
quote:
Originally posted by axisperfect
Kristen: DTS direct? How do you do that? It's quite a complicated package (doing prior inserts/updates/selects before the actual export), is there away for me to pull out the t-sql code for it to run it in Query Analyzer?
If the SQL code is in variuos SQL tasks in the DTS package it will be trivial to retrieve it. If there are VB Script things going on in the package then it might be more complicated.My syggestion would be along the lines of Kristen.1. Create a view that selects the 40 columns2. Create a procedure that scrubs the data so that it is ready for export3. Use bcp to get export the data.2&3:create proc myExport as-- scrub the data---- exec master.dbo.xp_cmdshell 'bcp db.dbo.myView out "myFile.txt" -T -t="|" -r="\n"'rockmoose |
|
|
|
|
|
|
|
|
Text File export lots of columns