RE: how to send query output into a variable

ps - just for the heck of it,

select sum(len(sp_text)+1) from #repl_text_holder

and see what you get.... if it is over 8000, you may just have to go
back to your loop

Trevor

> > declare @var varchar(8000),@lf char(8)
> > set @lf = char(10)
> > select @var=coalesce(@var + @lf,'') + sp_text
> > from #repl_text_holder
> > order by line_no
> >
> > print @var

----- Original Message -----
From: tweir <mssqldba-ezmlmshield-x58714384.[Email address protected]
Date: Wednesday, March 29, 2006 3:43 pm
Subject: Re: RE: RE: RE: RE: how to send query output into a variable
To: LazyDBA Discussion <[Email address protected]

> so close.... just change the "declare @lf" back to char(1) (not
> char 8)
>
> ----- Original Message -----
> From: Mordechai Danielov <mssqldba-ezmlmshield-
> x67057861.[Email address protected]
> Date: Wednesday, March 29, 2006 12:26 pm
> Subject: RE: RE: RE: RE: how to send query output into a variable
> To: LazyDBA Discussion <[Email address protected]
>
> > Did you mean :
> >
> > declare @var varchar(8000),@lf char(8)
> > set @lf = char(10)
> > select @var=coalesce(@var + @lf,'') + sp_text
> > from #repl_text_holder
> > order by line_no
> >
> > print @var
> >
> > In any case, this brings the char count to 3927
> >
> >
> > -----Original Message-----
> > From: tweir [mailto:mssqldba-ezmlmshield-x4086402.[Email address
> > protected]
> > Sent: Wednesday, March 29, 2006 12:17 PM
> > To: LazyDBA Discussion
> > Subject: Re: RE: RE: RE: how to send query output into a variable
> >
> > I'm not too sure about that - how about trying this instead and
> > see if
> > it shows everything (note the line feed, you dont need CR +LF,
> > just LF):
> >
> > declare @var varchar(8000),@lf char(1)
> > set @var = char(10)
> > select @var=coalesce(@var + @lf,'') + sp_text
> > from #repl_text_holder
> > order by line_no
> >
> > print @var
> >
> > Trevor
> >
> > ----- Original Message -----
> > From: Mordechai Danielov <mssqldba-ezmlmshield-
> > x63302917.[Email address protected]
> > Date: Wednesday, March 29, 2006 12:09 pm
> > Subject: RE: RE: RE: how to send query output into a variable
> > To: LazyDBA Discussion <[Email address protected]
> >
> > > Trevor,
> > >
> > > You are right, even though BOL is mum about this method it
> works and
> > > replaces the loop. Now about the output, select gives 4000
> chars
> > > and print
> > > 3986. I am ready to accept Mike's theory about undercover nvarchar
> > > conversion, but don't know which internal function to blame.
> > Even
> > > so, why
> > > does print statement loose 14chars?
> > >
> > > Thanks,
> > >
> > > Mordechai
> > >
> > > -----Original Message-----
> > > From: tweir [mailto:mssqldba-ezmlmshield-x51763188.[Email
> > address
> > > protected]
> > > Sent: Wednesday, March 29, 2006 11:29 AM
> > > To: LazyDBA Discussion
> > > Subject: Re: RE: RE: how to send query output into a variable
> > >
> > >
> > > Mordechai,
> > >
> > > Instead of doing a "select @var" at the end, try a "print
> @var" -
> >
> > > I
> > > have a funny feeling you will see the entire 8000 characters.
> > >
> > > Take my word for it - this is much cleaner than the loop you
> > have
> > > and
> > > it will work.
> > >
> > > Replace the entire code snippet you sent with the following:
> > >
> > > declare @var varchar(8000)
> > > select @var=coalesce(@var + ' ','') + sp_text
> > > from #repl_text_holder
> > > order by line_no
> > >
> > > print @var
> > >
> > > Trevor
> > >
> > >
> > > ----- Original Message -----
> > > From: Mordechai Danielov <mssqldba-ezmlmshield-
> > > x46372186.[Email address protected]
> > > Date: Wednesday, March 29, 2006 10:05 am
> > > Subject: RE: RE: how to send query output into a variable
> > > To: LazyDBA Discussion <[Email address protected]
> > >
> > > > Yes, that was part of the problem. When I set that option to
> > > 8000
> > > > I get up
> > > > to 4000 characters, instead of previous 256. still 4000
> > > characters are
> > > > misteriously missing (it's not like I am using nvarchar).
> Here
> > > is the
> > > > relevant T-SQL segment
> > > >
> > > > @var varchar(8000)
> > > > set @var=''
> > > > declare @counter_up int, @counter_down int
> > > > set @counter_up=1
> > > > set @counter_down=(select max(line_no) from #repl_text_holder)
> > > > SET CONCAT_NULL_YIELDS_NULL OFF
> > > > while @counter_down >0
> > > > BEGIN
> > > > set @var=@var+(select sp_text as " " from #repl_text_holder
> where> > > line_no=@counter_up)
> > > > set @counter_up=@counter_up+1
> > > > set @counter_down=@counter_down-1
> > > > END
> > > > SET CONCAT_NULL_YIELDS_NULL ON
> > > > select @var
> > > >
> > > > Anyhow, I am starting to see that this method has its
> > > limitations,
> > > > because I
> > > > can not put any code page characters (such as CHAR(13) or
> > > > CHAR(10)) into a
> > > > varchar variable, which makes it very hard to use it to
> > execute
> > > a
> > > > script.
> > > > BTW, thanks to Trevor's tip, I was able to eliminate some of
> > the
> > > > ugliness in
> > > > this process putting temporary text into a temp table (where
> > it
> > > > belongs) and
> > > > replacing bcp with osql thereby eliminating some of the
> clean
> > up
> > > > logic.
> > > > -----Original Message-----
> > > > From: Wes Wilson
> > > > [mailto:mssqldba-ezmlmshield-x93563415.[Email address
> > protected]
> > > > Sent: Wednesday, March 29, 2006 9:33 AM
> > > > To: LazyDBA Discussion
> > > > Subject: RE: RE: how to send query output into a variable
> > > >
> > > >
> > > > Is it possible that you are using Query Analyzer and are
> only
> > > > seeing the
> > > > first 200 characters in the output?
> > > >
> > > > To make sure, go to tools, options. On the results tab, make
> > > sure your
> > > > maximum characters per column is 8000.
> > > >
> > > > Just a thought,
> > > > Wes
> > > >
> > > >
> > > >
> > > > Wes Wilson
> > > > REGISTRAT, Inc.
> > > > Sr. Systems Administrator / Data Base Administrator
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: Mordechai Danielov
> > > > [mailto:mssqldba-ezmlmshield-x20961942.[Email address
> > protected]
> > > > Sent: Wednesday, March 29, 2006 9:06 AM
> > > > To: LazyDBA Discussion
> > > > Subject: RE: RE: how to send query output into a variable
> > > >
> > > > Hi Trevor,
> > > >
> > > > Thanks for the header flag, I must have read right past in
> > BOL.
> > > Is
> > > > therealso a way to get rid of extra CRs between lines?
> > > >
> > > > About concatenating values into a variable, I am not sure
> what
> > > you
> > > > mean.If you are talking about CONCAT_NULL_YIELDS_NULL
> setting,
> > > > that's not a
> > > > problem because I specifically initiate the variable as set
> > @var=''.> > This is a general problem that I have not been able
> to
> > solve so far:
> > > > It seems to me, that I should be able to create a table with
> > an
> > > > identity(int) and varchar columns, then put the text into
> the
> > > > varchar, and let
> > > > the integer happily increment, then put this table in a loop
> > and do
> > > > something like @text_var= @text_var + (select text from
> table
> > where> > integer=@counter), but it does not work. I tried to
> make
> > the
> > > @text_var> as big as varchar(8000), it would still concatenate
> > > only up to 200-300
> > > > characters, and ignore the rest.
> > > >
> > > > -----Original Message-----
> > > > From: tweir [mailto:mssqldba-ezmlmshield-x58428195.[Email
> address> > > protected]
> > > > Sent: Wednesday, March 29, 2006 7:19 AM
> > > > To: LazyDBA Discussion
> > > > Subject: Re: RE: how to send query output into a variable
> > > >
> > > > 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
> > > > addressprotected]
> > > > 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
> > > > >
> > > >
> > > >
> > > > -------------------------------------------------------------
> --
> > --
> > > --
> > > > --
> > > > 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
> > >
> > >
> > >
> > > ---------------------------------------------------------------
> --
> > --
> > > --
> > > 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