Embarrassingly simple question about using stored proceedures

The stored procedure sp_columns returns several fields but i want just the
COLUMN_NAME
if the stored procedure was a table I would write the query
Select COLUMN_NAME from sp_columns
Whats the simplest way of doing this for a stored proceedure
many thanks
David HEither re-write the procedure or use below technique:
CREATE TABLE #tmp...
INSERT #tmp
EXEC sp_columns
SELECT ... FROM #tmp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:BEEA10DA-869E-4A9B-B884-56B6070B6725@.microsoft.com...
> The stored procedure sp_columns returns several fields but i want just the
COLUMN_NAME
> if the stored procedure was a table I would write the query
> Select COLUMN_NAME from sp_columns
> Whats the simplest way of doing this for a stored proceedure
> many thanks
> David H
>|||An even simpler approach would be to ditch sp_columns and use the
INFORMATION_SCHEMA views instead. For example, the get the columns of the
Authors table in the pubs database use:
USE pubs
GO
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'pubs'
AND TABLE_NAME = 'authors'
or
SELECT COLUMN_NAME
FROM pubs.INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'pubs'
AND TABLE_NAME = 'authors'
You can get more information INFORMATION_SCHEMA views in BOL:
http://msdn.microsoft.com/library/d...br />
4pbn.asp
Cheers,
Stefan
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmygI4YDEHA.628@.TK2MSFTNGP10.phx.gbl...
> Either re-write the procedure or use below technique:
> CREATE TABLE #tmp...
> INSERT #tmp
> EXEC sp_columns
> SELECT ... FROM #tmp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "David Hills" <dhills@.pcfe.ac.uk> wrote in message
> news:BEEA10DA-869E-4A9B-B884-56B6070B6725@.microsoft.com...
the
> COLUMN_NAME
>|||That's a good point, Stefan!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stefan Delmarco [MSFT]" <StefanDe@.online.microsoft.com> wrote in messag
e
news:eAuYCTZDEHA.3240@.TK2MSFTNGP10.phx.gbl...
> An even simpler approach would be to ditch sp_columns and use the
> INFORMATION_SCHEMA views instead. For example, the get the columns of the
> Authors table in the pubs database use:
> USE pubs
> GO
> SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.Columns
> WHERE TABLE_CATALOG = 'pubs'
> AND TABLE_NAME = 'authors'
> or
> SELECT COLUMN_NAME
> FROM pubs.INFORMATION_SCHEMA.Columns
> WHERE TABLE_CATALOG = 'pubs'
> AND TABLE_NAME = 'authors'
> You can get more information INFORMATION_SCHEMA views in BOL:
>
http://msdn.microsoft.com/library/d..._ia-iz_4pbn.asp[
color=darkred]
> Cheers,
> Stefan
> --
> This posting is provided "AS IS" with no warranties, and confers no[/color]
rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OmygI4YDEHA.628@.TK2MSFTNGP10.phx.gbl...
> the
>|||Many thanks, that's just what I needed to know to do this
SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'sipr'
AND COLUMN_NAME LIKE '%rlg%'
TABLE_NAME COLUMN_NAME
--
d21_eed44 rlg_code
D04_RLG rlg_code
D04_RLG rlg_name
D04_RLG rlg_snam
SRS_RLG rlg_code
SRS_RLG rlg_name
SRS_RLG rlg_snam
D04_STU stu_rlgc
D04_CONTCT stu_rlg
D51_STU_CTS stu_rlgc
D51_STU_V26 stu_rlgc
INS_STU stu_rlgc

Email Subscription Problem

I have an install of Reporting Services which when trying to send an email based subscription, returns the following error;

Failure sending mail: Retrieving the COM class factory for component with CLSID {CD000001-8B95-11D1-82DB-00C04FB1625D} failed due to the following error: 80040154.

No errors are shown in the RS logs or the events logs.

Does anybody have any ideas what could be causing this?

I don't know what version you are using but when it first came out it was hard for most people to install it but I did not get a hard time because I ignored all Windows questions. So I remember it asking for Exchange server you will use with it, if you did not configure Exchange, SMTP or SQL Server Agent to use for your mail that could be the reason for the error. Hope this helps.|||Thanks for your reply. I've double-checked all the configuration and everything appears fine. :(sql

else if statement

I have this function and is given me the error below the code and want to know which is my mistake

create function pt
(@.idpt INT,
@.option INT)
Returns Real
AS
Begin
if (@.option=1)
Begin
Return ( select sqrt(a.px*a.px + a.py*a.py)
from electron as a
where @.idpt = a.idap)
end
else if(@.option=2)
Begin
Return ( select sqrt(a.px*a.px + a.py*a.py)
from muon as a
where @.idpt = a.idap)
End
else
Begin
Return ( select sqrt(a.px*a.px + a.py*a.py)
from jet as a
where @.idpt = a.idap)
end
end

Msg 455, Level 16, State 2, Procedure pt, Line 22
The last statement included within a function must be a return statement.
Try putting a dummy "RETURN NULL" after the large IF block. I think the compiler isn't realizing that at least one of the 3 return statements you've got will always be called, and it's throwing a fit. The extra RETURN will never get called, but it'll at least keep the compiler happy.
|||

Try the code below.

Chris

Code Snippet

CREATE FUNCTION pt (@.idpt INT, @.option INT)

RETURNS REAL

AS

BEGIN

DECLARE @.Output REAL

IF (@.option = 1)

BEGIN

SELECT @.Output = SQRT(a.px * a.px + a.py * a.py)

FROM electron AS a

WHERE @.idpt = a.idap

END

ELSE

IF (@.option = 2)

BEGIN

SELECT @.Output = SQRT(a.px * a.px + a.py * a.py)

FROM muon AS a

WHERE @.idpt = a.idap

END

ELSE

BEGIN

SELECT @.Output = SQRT(a.px * a.px + a.py * a.py)

FROM jet AS a

WHERE @.idpt = a.idap

END

RETURN @.Output

END

Eliminating (x row(s) affected) message

I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.Kevin wrote on Fri, 2 Jun 2006 07:39:02 -0700:

> I am sure this has been asked before. But, in searching for this message I
> get a number of returns.
> How do I eliminate the (x row(s) affected) message from queries in a
> stored procedure?
> Thank you.
Use SET NOCOUNT ON at the start of the stored proc.
Dan|||Try:
SET NOCOUNT ON
... at the beginning of each proc.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:741BD8D2-F958-471B-95FD-F694ACFDD0AD@.microsoft.com...
I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.

Eliminating (x row(s) affected) message

I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.Kevin wrote on Fri, 2 Jun 2006 07:39:02 -0700:
> I am sure this has been asked before. But, in searching for this message I
> get a number of returns.
> How do I eliminate the (x row(s) affected) message from queries in a
> stored procedure?
> Thank you.
Use SET NOCOUNT ON at the start of the stored proc.
Dan|||Try:
SET NOCOUNT ON
... at the beginning of each proc.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:741BD8D2-F958-471B-95FD-F694ACFDD0AD@.microsoft.com...
I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.

Eliminate records

Hello,
The output of the following query returns two identical
records, i need to eliminate all records that are
identical but if i use the "distinct" before the substring
function its returned one error.
This is the query that i'm using:
select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Tex
tdata),217)
as [TextData],
objid
into DestinationT
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star'
Thanks,
Best regardsDid you try using:
Select DISTINCT Derived.* into DestinationT FROM
( select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Tex
tdata),217) as [TextData],
objid
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star') Derived
... (untested)
WIll this help?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe001c44a19$f48dfb80$a301280a@.phx
.gbl...
> Hello,
> The output of the following query returns two identical
> records, i need to eliminate all records that are
> identical but if i use the "distinct" before the substring
> function its returned one error.
> This is the query that i'm using:
> select EvClassDesc,
> STime,
> substring(Textdata,patindex('%exec%',Tex
tdata),217)
> as [TextData],
> objid
> into DestinationT
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star'
> Thanks,
> Best regards|||Thanks Vinod

>--Original Message--
>Did you try using:
>Select DISTINCT Derived.* into DestinationT FROM
>( select EvClassDesc,
> STime,
> substring(Textdata,patindex('%
exec%',Textdata),217) as [TextData],
> objid
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star')
Derived
>... (untested)
>WIll this help?
>--
>HTH,
>Vinod Kumar
>MCSE, DBA, MCAD, MCSD
>http://www.extremeexperts.com
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techin...ctdoc/2000/book
s.asp
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
> news:17fe001c44a19$f48dfb80$a301280a@.phx
.gbl...
substring[vbcol=seagreen]
exec%',Textdata),217)[vbcol=seagreen]
>
>.
>

Eliminate records

Hello,
The output of the following query returns two identical
records, i need to eliminate all records that are
identical but if i use the "distinct" before the substring
function its returned one error.
This is the query that i'm using:
select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217 )
as [TextData],
objid
into DestinationT
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star'
Thanks,
Best regards
Did you try using:
Select DISTINCT Derived.* into DestinationT FROM
( select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217 ) as [TextData],
objid
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star') Derived
... (untested)
WIll this help?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
> Hello,
> The output of the following query returns two identical
> records, i need to eliminate all records that are
> identical but if i use the "distinct" before the substring
> function its returned one error.
> This is the query that i'm using:
> select EvClassDesc,
> STime,
> substring(Textdata,patindex('%exec%',Textdata),217 )
> as [TextData],
> objid
> into DestinationT
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star'
> Thanks,
> Best regards
|||Thanks Vinod

>--Original Message--
>Did you try using:
>Select DISTINCT Derived.* into DestinationT FROM
>( select EvClassDesc,
> STime,
> substring(Textdata,patindex('%
exec%',Textdata),217) as [TextData],
> objid
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star')
Derived
>... (untested)
>WIll this help?
>--
>HTH,
>Vinod Kumar
>MCSE, DBA, MCAD, MCSD
>http://www.extremeexperts.com
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinf...tdoc/2000/book
s.asp
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
substring[vbcol=seagreen]
exec%',Textdata),217)
>
>.
>

Eliminate records

Hello,
The output of the following query returns two identical
records, i need to eliminate all records that are
identical but if i use the "distinct" before the substring
function its returned one error.
This is the query that i'm using:
select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217)
as [TextData],
objid
into DestinationT
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star'
Thanks,
Best regardsDid you try using:
Select DISTINCT Derived.* into DestinationT FROM
( select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217) as [TextData],
objid
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star') Derived
... (untested)
WIll this help?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
> Hello,
> The output of the following query returns two identical
> records, i need to eliminate all records that are
> identical but if i use the "distinct" before the substring
> function its returned one error.
> This is the query that i'm using:
> select EvClassDesc,
> STime,
> substring(Textdata,patindex('%exec%',Textdata),217)
> as [TextData],
> objid
> into DestinationT
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star'
> Thanks,
> Best regards|||Thanks Vinod
>--Original Message--
>Did you try using:
>Select DISTINCT Derived.* into DestinationT FROM
>( select EvClassDesc,
> STime,
> substring(Textdata,patindex('%
exec%',Textdata),217) as [TextData],
> objid
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star')
Derived
>... (untested)
>WIll this help?
>--
>HTH,
>Vinod Kumar
>MCSE, DBA, MCAD, MCSD
>http://www.extremeexperts.com
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
>> Hello,
>> The output of the following query returns two identical
>> records, i need to eliminate all records that are
>> identical but if i use the "distinct" before the
substring
>> function its returned one error.
>> This is the query that i'm using:
>> select EvClassDesc,
>> STime,
>> substring(Textdata,patindex('%
exec%',Textdata),217)
>> as [TextData],
>> objid
>> into DestinationT
>> from OriginalT
>> where objid = 1111111 and EvClassDesc = 'SP:Star'
>> Thanks,
>> Best regards
>
>.
>