The "openrowset" stuff is foreign to me, so forgive me if my answer
chokes on that.
Normally, if you can do this without the execute line, you can simply:
select @db_id = b.id from blah blah blah
But you must have the exec() statement, then here is a trick I've used.
Not necessarily the most efficient, but it works.
create table #MyIDTable (db_id int)
-- run your set @SQL statements here
-- replace the execute line with something like this:
execute ('insert into #MyIDTable (db_id) ' + @sql)
-- Now, get your ID
select @db_id = db_id from #MyIDTable
Something like that should get you by.
-----Original Message-----
From: Tipton Josh (orl1jdt) [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 08:19
To: SQL
Subject: sp help
How can I set @db_id = to the id that is returned from this query
@P_UID char(15),
@P_UPWD char(15),
@P_SRVR_NA sysname,
@P_SRVR_DB_NA sysname
AS
begin
declare @sql varchar(2000), @DB_ID int
set @sql = "select b.id from openrowset('MSDASQL', 'DRIVER={SQL
Server};SERVER="
set @sql = @sql +
@P_SRVR_NA+";UID="+LTRIM(RTRIM(@P_UID))+";PWD="+LTRIM(RTRIM(@P_UPWD))+"'
,"
SET @SQL = @SQL + @P_SRVR_DB_NA+".DBO.SYSOBJECTS) AS B WHERE
B.NAME='SOORDER' "
set @db_id = exec(@SQL)
end
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Message: http://www.houseoffusion.com/lists.cfm?link=i:6:1726
Archives: http://www.houseoffusion.com/lists.cfm?link=t:6
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:>
