Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1033
  • Last Modified:

how to assign osql return result into variable

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
changeexpert
Asked:
changeexpert
1 Solution
 
namasi_navaretnamCommented:
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
 
LowfatspreadCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now