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

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

 

  

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


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