Building text files

Ok, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total.

I have a table set up with my parameters and output file names.

Question 1: Process from DTS
I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files?

Question 2: Process from a stored proc
I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc).

PS: The file data cannot include cilumn headings

TIA -
bpdI'd go with the sproc and bcp out

Just change the IN to OUT

SET @.cmd = 'bcp ' + @.db_name + '..ETRS_ASI_FED_TEMP in '
+ @.FilePathAndName + ' -t"\t" -c -S' + @.@.servername + ' -Uscrub -Pscrub'

SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''''

Select @.Command_String

Exec(@.Command_String)|||Given this problem, I would probably try a VB or PERL script to extract the data. I know how to call bcp from PERL, but VB is still a bit new to me. Fortunately, if the data set you are exporting is small (few thousand rows), you could get away with just using FileObject writes. Biggest problem I have had with bcp is remembering to check the error file for any problems. Again, easy for me in PERL, but VB...|||Thanks! bcp is what I was looking for. Glad I can avoid DTS all together.

-bpd

Building a conditional WHERE clause

Hello experts, I have a sproc that gets several params, default is null. I'
d
like to build a WHERE clause on the fly for just the params with values.
Something like
SELECT * from foo
WHERE if( @.a IS NOT NULL foo.a = @.a) and if( @.b IS NOT NULL foo.b = @.b)IF is a control flow statement... You cannot use it in a query.
How about :
WHERE a = COALESCE(@.a, a)
AND b = COALESCE(@.b, b)
You can also use CASE but it's a bit more drawn out:
WHERE a = CASE WHEN @.a IS NOT NULL THEN @.a ELSE a END
AND b = CASE WHEN @.b IS NOT NULL THEN @.b ELSE b END
(And the latter will almost certainly lead to scans rather than ss, if a
and/or be is indexed.)
A
On 3/5/05 12:45 PM, in article
3AD07FB0-A316-4ED0-A29D-EB3E5F06499A@.microsoft.com, "Coffee guy"
<Coffeeguy@.discussions.microsoft.com> wrote:

> Hello experts, I have a sproc that gets several params, default is null.
I'd
> like to build a WHERE clause on the fly for just the params with values.
> Something like
> SELECT * from foo
> WHERE if( @.a IS NOT NULL foo.a = @.a) and if( @.b IS NOT NULL foo.b = @.b)
>
>|||Aaaah, thanks!
"Aaron [SQL Server MVP]" wrote:

> IF is a control flow statement... You cannot use it in a query.
> How about :
> WHERE a = COALESCE(@.a, a)
> AND b = COALESCE(@.b, b)
> You can also use CASE but it's a bit more drawn out:
> WHERE a = CASE WHEN @.a IS NOT NULL THEN @.a ELSE a END
> AND b = CASE WHEN @.b IS NOT NULL THEN @.b ELSE b END
> (And the latter will almost certainly lead to scans rather than ss, if
a
> and/or be is indexed.)
> A
>
> On 3/5/05 12:45 PM, in article
> 3AD07FB0-A316-4ED0-A29D-EB3E5F06499A@.microsoft.com, "Coffee guy"
> <Coffeeguy@.discussions.microsoft.com> wrote:
>
>