DTS Export Wizard

Is there a way for me to globally select "Delete rows in destination table"
when performing an export via the Export Wizard?
It is not obviously visible to me, and I have a great deal of tables. I've
been clicking "Edit" for each one, and this isnt practical for obvious
reasons.
Can anyone point me in the right direction? Thanks.there is an option under 'select object...' dialog.
http://msdn.microsoft.com/library/e...elpwiz_0nci.asp
or you can create a dml task and delete the data before insert new ones.
-oj
"Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
news:eAbKC$1TGHA.4608@.tk2msftngp13.phx.gbl...
> Is there a way for me to globally select "Delete rows in destination
> table" when performing an export via the Export Wizard?
> It is not obviously visible to me, and I have a great deal of tables. I've
> been clicking "Edit" for each one, and this isnt practical for obvious
> reasons.
> Can anyone point me in the right direction? Thanks.
>

DTS Export to Excel Too many Recs?

Hi All,

I'm trying to export around 115,000 rows from ms sql 2000 into Excel 2000, using the manual process. I just need this one time dump.

I am able to successfully export around +- 65,000 rows, but the operation fails after that.

I need to be able to get all the rows out, so using TOP obviously doesn't work.

Is there some "version" or modified way to use TOP to get...say, rows 65,000 to 90,000 then 90,001 to 115,000 ?

This would'nt be an issue if the db I'm working with was MySQL...I'd just use the LIMIT function and pull out 3 different chucks. Is there anything similar to LIMIT...or something converse to TOP in MS SQL? Or perhaps another way to dump the table then export it all (or in portions) into Excel?

Thanks!You need to have a key field(s) that you can sort on. So lets say you have a unique id like excel_id numbered from 1 to 120000:

select top 65000 ... from table order by excel_id
select top 55000 ... from table order by excel_id desc|||If you want an inner subset like 65000-90000 then you would use something like:

select top 25000 ... from (select top 55000 from table order by field desc) order by field|||If you do have a key then you can use KEY BETWEEN 65001 and 90000, etc. ...and if you were using MySQL we wouldn't have even bothered to look at the question...In short, - DON'T USE IT!|||Are you using a standard DTS data pump to export the data? Or are you using something else?

If you are using a data pump then why not take care of the problem programaticly? Select all the records and keep an internal counter so you know when you reach 65000 and when you do change your target to a new spreadsheet zero the counter and continue on...

Just a thought,...|||There are plenty of other ways to do this as well, it just depends on the requirements for your export...|||With a programmatic solution, your performance will suffer. Allow the database to do its job.|||Thanks for all the replies/suggestions!

There is no key field.
I couldn't find a solution, so I just dumped the data, opened it in textpad, cut out three chunks (~35,000 rows) and imported them into 3 diff .xls files.

Interesting enough for those in the anti MySQL crowd ... chances are, this very forum almost certainly uses MySQL. I say this for two reasons. 1. PHP server side scripting. 2. This is a vBulletin forum.
Not to mention the growing interest in open source products vs proprietary!

Cheers!

DTS Excel Import, Transform - how do I use "OR" clause in SQL Query

Howdy. I'm trying to build a query that will take an Excel file and
pull a few rows of data from a particular sheet. I'm having a problem
with my WHERE clause - I can can tell it to import WHERE a field
matches a value, or WHERE the field matches another value, but not
both.
I've tried bunches of different variations, but can't get it to work.
Is there any way to do this?

Works:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where ((F1 = 'Body'))

Works:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Cash'

Doesn't:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Body' OR F1 = 'Cash'

Doesn't:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where (F1 = 'Body' OR F1 = 'Cash')

Doesn't:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where ((F1 = 'Body') OR (F1 = 'Cash'))Did you try:

select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Body'
union all
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Cash'

or

select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 in ( 'Body', 'Cash')

"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0409010509.1f10c665@.posting.google.c om...
> Howdy. I'm trying to build a query that will take an Excel file and
> pull a few rows of data from a particular sheet. I'm having a problem
> with my WHERE clause - I can can tell it to import WHERE a field
> matches a value, or WHERE the field matches another value, but not
> both.
> I've tried bunches of different variations, but can't get it to work.
> Is there any way to do this?
> Works:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where ((F1 = 'Body'))
> Works:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where F1 = 'Cash'
> Doesn't:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where F1 = 'Body' OR F1 = 'Cash'
> Doesn't:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where (F1 = 'Body' OR F1 = 'Cash')
> Doesn't:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where ((F1 = 'Body') OR (F1 = 'Cash'))

Dts Error

Hi,
Am having errors like this one:
The numner of failling rows exceeds the maximun specified
Could not allocate space for onject 'asdsadsad' in database 'xyz'
because the 'PRIMARY' filegroup is full
Any ideas? :)Sounds like the database is full.
Either increase the size or allow it to automatically increase.

If the disk is full then you have learnt to always put a limit on file sizes.

DTS doesnt recognize CRLF in Text File

I'm using DTS to import a text file (fixed field). In my sample data, I have 21 rows of data.

DTS only brings in 15 or so, because it fails to recognize the CRLF on some of the rows, and just treats it and the subsequent row as part of the previous row.

The text file is being produced via FTP from an IBM Iseries. It imports just fine into Notepad and Excel.

Anyone have any ideas why DTS would have trouble with this ?

Thanks

Gregnevermind...apparently DTS requires flat text files to be padded with blanks in order to make all the records exactly the same length. I don't know why DTS is so picky about this when every other Micro$oft application isn't.

I reckon I'll just make it comma delimited and be done with it.