Duplicate column name for OpenRowSet
in [Programming SQL]
|
From: tshad on 23 Mar 2010 20:09 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
From: Sylvain Lafontaine on 23 Mar 2010 21:56 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(a)dslextreme.com> wrote in message news:%23o9E3YuyKHA.5040(a)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 >
From: tshad on 24 Mar 2010 19:31 So how would you get the SPID that is passed back from sp_who2? Thanks, Tom "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message news:u5FE7UvyKHA.5360(a)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(a)dslextreme.com> wrote in message > news:%23o9E3YuyKHA.5040(a)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 >> > >
From: Sylvain Lafontaine on 24 Mar 2010 20:13 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(a)dslextreme.com> wrote in message news:e2lJxo6yKHA.404(a)TK2MSFTNGP02.phx.gbl... > So how would you get the SPID that is passed back from sp_who2? > > Thanks, > > Tom > > "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message > news:u5FE7UvyKHA.5360(a)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(a)dslextreme.com> wrote in message >> news:%23o9E3YuyKHA.5040(a)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 >>> >> >> > >
|
Pages: 1 Prev: First Function SQL Server 2000 Next: Transaction coding style |