Solved

how to assign osql return result into variable

Posted on 2003-11-04
2
999 Views
Last Modified: 2007-12-19
Hi,

Tried to do the following:

declare @cmd varchar(100)
declare @name varchar(100)
set @cmd = 'osql /U user /P password /S server /D database /Q "Select field from table"
exec master..xp_cmdshell @cmd

the above will return a value of field, for example "JohnDoe"

how I can assign this value into variable @name
0
Comment
Question by:changeexpert
2 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
Is sql Select field from table guaranteed to return only one row? You may want to send the output to an outfile using -o option and then read the first row in the output file.

Why not try dynamic sql (This is the better way and sp_executesql can return field1 as output parameter)

You can use sp_executesql proc

Example:
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once. */
SET @SQLString =
     N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@level tinyint'

/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 25 total points
Comment Utility
have you tried

declare @cmd varchar(100)
declare @name varchar(100)
set @cmd = 'osql /U user /P password /S server /D database /Q "Select field from table"

declare table #temp (tid int identity(1,1) cmddata varchar(8000))

insert into #temp (cmddata)
exec master..xp_cmdshell @cmd

select top 1 @name = cmddata from #temp

0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now