Solved

how to assign osql return result into variable

Posted on 2003-11-04
2
1,000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 33
Converting Teradata SQL to Oracle SQL (exadata) 3 28
SQL Error in WHERE Clause 5 39
conditional join based on column 4 0
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

912 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

23 Experts available now in Live!

Get 1:1 Help Now