I tried to do the following and got an error:

Duplicate column names are not allowed in result sets obtained through 
OPENQUERY and OPENROWSET. The column name "SPID" is a duplicate.

But it is there only once and if I take it out, it works fine but SPID isn't 
there.

Why is that?
SELECT SPID, Status, Login, HostName, BlkBy,DBName, Command, CPUTime,

DiskIO, LastBatch, ProgramName

INTO #TempSpWho2

FROM OPENROWSET

('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec 
master.dbo.sp_who2')

AS tbl

Select * from #TempSPWho2

Drop table #TempSPWho2


Thanks,

Tom 

The second SPID is in the source code of the SP sp_who2 itself:
    ...
    ,SPID          = convert(char(5),spid)  --Handy extra for 
right-scrolling users.
    ...

I won't make any comment on this "--Handy extra for right-scrolling users" 
column with a duplicate name.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"tshad" <tfs@dslextreme.com> wrote in message 
news:%23o9E3YuyKHA.5040@TK2MSFTNGP02.phx.gbl...
>I tried to do the following and got an error:
>
> Duplicate column names are not allowed in result sets obtained through 
> OPENQUERY and OPENROWSET. The column name "SPID" is a duplicate.
>
> But it is there only once and if I take it out, it works fine but SPID 
> isn't there.
>
> Why is that?
> SELECT SPID, Status, Login, HostName, BlkBy,DBName, Command, CPUTime,
>
> DiskIO, LastBatch, ProgramName
>
> INTO #TempSpWho2
>
> FROM OPENROWSET
>
> ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec 
> master.dbo.sp_who2')
>
> AS tbl
>
> Select * from #TempSPWho2
>
> Drop table #TempSPWho2
>
>
> Thanks,
>
> Tom
> 

So how would you get the SPID that is passed back from sp_who2?

Thanks,

Tom

"Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
news:u5FE7UvyKHA.5360@TK2MSFTNGP06.phx.gbl...
> The second SPID is in the source code of the SP sp_who2 itself:
>    ...
>    ,SPID          = convert(char(5),spid)  --Handy extra for 
> right-scrolling users.
>    ...
>
> I won't make any comment on this "--Handy extra for right-scrolling users" 
> column with a duplicate name.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server 
> (French)
>
>
> "tshad" <tfs@dslextreme.com> wrote in message 
> news:%23o9E3YuyKHA.5040@TK2MSFTNGP02.phx.gbl...
>>I tried to do the following and got an error:
>>
>> Duplicate column names are not allowed in result sets obtained through 
>> OPENQUERY and OPENROWSET. The column name "SPID" is a duplicate.
>>
>> But it is there only once and if I take it out, it works fine but SPID 
>> isn't there.
>>
>> Why is that?
>> SELECT SPID, Status, Login, HostName, BlkBy,DBName, Command, CPUTime,
>>
>> DiskIO, LastBatch, ProgramName
>>
>> INTO #TempSpWho2
>>
>> FROM OPENROWSET
>>
>> ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off 
>> exec master.dbo.sp_who2')
>>
>> AS tbl
>>
>> Select * from #TempSPWho2
>>
>> Drop table #TempSPWho2
>>
>>
>> Thanks,
>>
>> Tom
>>
>
> 

I think that you will have to set up your own version of sp_who2.  Grab the 
code, copy it and remove the second SPID column.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"tshad" <t@dslextreme.com> wrote in message 
news:e2lJxo6yKHA.404@TK2MSFTNGP02.phx.gbl...
> So how would you get the SPID that is passed back from sp_who2?
>
> Thanks,
>
> Tom
>
> "Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
> news:u5FE7UvyKHA.5360@TK2MSFTNGP06.phx.gbl...
>> The second SPID is in the source code of the SP sp_who2 itself:
>>    ...
>>    ,SPID          = convert(char(5),spid)  --Handy extra for 
>> right-scrolling users.
>>    ...
>>
>> I won't make any comment on this "--Handy extra for right-scrolling 
>> users" column with a duplicate name.
>>
>> -- 
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server 
>> (French)
>>
>>
>> "tshad" <tfs@dslextreme.com> wrote in message 
>> news:%23o9E3YuyKHA.5040@TK2MSFTNGP02.phx.gbl...
>>>I tried to do the following and got an error:
>>>
>>> Duplicate column names are not allowed in result sets obtained through 
>>> OPENQUERY and OPENROWSET. The column name "SPID" is a duplicate.
>>>
>>> But it is there only once and if I take it out, it works fine but SPID 
>>> isn't there.
>>>
>>> Why is that?
>>> SELECT SPID, Status, Login, HostName, BlkBy,DBName, Command, CPUTime,
>>>
>>> DiskIO, LastBatch, ProgramName
>>>
>>> INTO #TempSpWho2
>>>
>>> FROM OPENROWSET
>>>
>>> ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off 
>>> exec master.dbo.sp_who2')
>>>
>>> AS tbl
>>>
>>> Select * from #TempSPWho2
>>>
>>> Drop table #TempSPWho2
>>>
>>>
>>> Thanks,
>>>
>>> Tom
>>>
>>
>>
>
>