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

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

 

  

Thank you all for help, the 4000 char limit mistery was painfully simple -
table definition for #repl_text_holder was using nvarchar for sp_text
column. Agh.

-----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


MS Sql Server LazyDBA home page