Re: RE: RE: how to send query output into a variable

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
>

MS Sql Server LazyDBA home page