RE: how to send query output into a variable

RE: how to send query output into a variable

 

  

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 column
width 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.html?
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




MS Sql Server LazyDBA home page