RE: how to send query output into a variable

RE: how to send query output into a variable

 

  

That will do it every time! I take it all is well now?

----- Original Message -----
From: Mordechai Danielov <mssqldba-ezmlmshield-
x31113827.[Email address protected]
Date: Thursday, March 30, 2006 9:39 am
Subject: RE: RE: RE: RE: how to send query output into a variable
To: LazyDBA Discussion <[Email address protected]

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