Solved

how to assign osql return result into variable

Posted on 2003-11-04
2
1,006 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
ID: 9684116
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
ID: 9687143
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

685 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