When you declare the @var, it is automatically given a value of null.
So when the first record is retrieved from the SELECT statement, it
will effectively perform @var = '' + your_field. For each additional
record retrieved it will perform @var = @var + ' ' + your_field.

As for your column header problem in osql, try adding the flag -h-1 to
your osql call (make sure there are no spaces between any of the 4
characters). This will suppress column headers in the output.

Trevor

----- Original Message -----
From: Mordechai Danielov <mssqldba-ezmlmshield-
x43941986.[Email address protected]
Date: Tuesday, March 28, 2006 11:14 pm
Subject: RE: how to send query output into a variable
To: LazyDBA Discussion <[Email address protected]

> What I originally thought I would be able to do was something like:
> set @var=exec proc --generates a T-SQL script
> exec @var --Executes it
>
> What I wound up works, but it is rather ugly:
> 1. exec proc into a table (a real, permanent table - because of
> the next
> step)
> 2. bcp the content into a file (space separated)
> - this had to be done because osql would put an extra line of
> dashes at the
> top, and I could not find a way to get rid of it, and depending on
> columnwidth it could cause a syntax error (e.g. when a single dash
> was by itself
> on a new line)
> - also, I could not use a temp table because BCP would compain
> when I tried
> to run bcp "exec proc" queryout ..., so I had to do bcp "select
> column from
> table" queryout
> 3. osql uses the file created in the previous step as input and
> runs it on
> the destination server (thanks Maureen).
>
> Like I said, rather ugly, and now I will have to sit with it to
> dress it
> with proper error handling, because so many things can go wrong.
> But, it
> works...
>
> BTW, I thought "coalesce" is for returning non-null values, how does
> coalesce(@var + ' ','') work?
>
> -----Original Message-----
> From: Trevor Weir
> [mailto:mssqldba-ezmlmshield-x71843657.[Email address protected]
> Sent: Tuesday, March 28, 2006 6:41 PM
> To: LazyDBA Discussion
> Subject: RE: how to send query output into a variable
>
>
> Something like this perhaps?
>
> Declare @var varchar(8000)
> Select @var = coalesce(@var + ' ','') + your_field
> From your_query here
>
> Exec('exec master.dbo.xp_cmdshell ''osql -S .... -Q "' + @var + '"')
>
> Trevor
>
> -----Original Message-----
> From: Mordechai Danielov
> [mailto:mssqldba-ezmlmshield-x74503902.[Email address protected]
> Sent: Tuesday, March 28, 2006 1:25 PM
> To: LazyDBA Discussion
> Subject: RE: how to send query output into a variable
>
>
> Thanks, but I wanted to find a purely SQL solution if possible.
> I like Tad's suggestion, but the ouput file from the SQL query
> comes out
> looking weird. If I can get it to look normal, I should be able to
> osql out,
> and osql in via xp_cmdshell.
>
> -----Original Message-----
> From: Jim Dorame
> [mailto:mssqldba-ezmlmshield-x31533749.[Email address protected]
> Sent: Tuesday, March 28, 2006 12:50 PM
> To: LazyDBA Discussion
> Subject: RE: how to send query output into a variable
>
> Check this out. It may be of use to you.
>
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1135562,00.h
tml?
> FromTaxonomy=%2Fpr%2F301764
>
> -----Original Message-----
> From: Mordechai Danielov
> [mailto:mssqldba-ezmlmshield-x52267166.[Email address protected]
> Sent: Tuesday, March 28, 2006 11:44 AM
> To: LazyDBA Discussion
> Subject: how to send query output into a variable
>
>
> Hello all,
>
> I am running a query that generates a T-SQL script. I need to run that
> script on a different server, which is straightforward enough with
> osql.Problem is that the output of the first query is in several
> rows, and I need
> it to be one string that can be passed to osql. I tried to use
> varchar(8000)variable and concotenate the text with a loop, but
> that was a waste of time.
> Any ideas?
>
> Thanks
>
>
>
> -------------------------------------------------------------------
> --
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> -------------------------------------------------------------------
> --
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> -------------------------------------------------------------------
> --
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> -------------------------------------------------------------------
> --
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
> -------------------------------------------------------------------
> --
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>

MS Sql Server LazyDBA home page