Hi,
I know this is probably an easy question that has been answered before,
but after crawling through google groups for two days now I find I am
still stuck.

My end aim is to use an external program (using CT-lib) to manipulate
results from stored procedures (such as sp_who, sp_spaceused etc).

It seems to do this one of the ways I have seen in SQL is like this:

--
SET NOCOUNT ON
CREATE TABLE #TMPWHO (
FID INT,
SPID INT,
STATUS CHAR(100),
LOGINAME CHAR(100),
ORIGNAME CHAR(100),
HOSTNAME CHAR(100),
BLK INT,
DBNAME CHAR(100),
CMD CHAR(100),
BLKXL INT)
INSERT INTO #TMPWHO EXEC sp_who
SELECT * FROM #TMPWHO
DROP TABLE #TMPWHO
go
--

I am using ASE 12.5.1 on Windows XP Pro. When I put the above into isql
I get:
--
Msg 156, Level 15, State 2:
Line 14:
Incorrect syntax near the keyword 'exec'.
--

I am obviously doing something wrong, but I cannot seem to fix it. Can
someone please let me know what I am doing wrong, or if there is a
better way of achieving what I want, don't be shy

Regards
Luke

PS: I am relatively new to Sybase ASE and not an experienced DBA.

On Fri, 20 Aug 2004 11:36:29 +0800, Luke wrote:

If you are going to use an external program anyway then I would
suggest fetching the results, storing them in memory in the client and
manipulating them there.

However, to get the output from a stored proc into a table you can, in
some cases, define a proxy table against the stored proc. Something like:

create existing table <name> (<column list>)
external procedure at <server>.<db>.<owner>.<procname>

This assumes that you have CIS enabled, and that the proc only returns a
single result set. If that is the case you can now use a normal SELECT to
execute the proc.

Please read up on proxy tables and CIS in the Sybase manuals (the "create
existing table" command in the Command Reference manual, among other
places)

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

"Luke" <no (AT) spam (DOT) invalid> wrote

I think the problem is that Sybase INSERT doesn't allow that. I could be
wrong, but, I think you'll find MS SQL Server does it, but Sybase ASE
doesn't.

Mind you, if your capturing this in an external program, why don't you just
execute the procedure and pick up the result set. You don't need to get the
proc's result into a temp table first and then select it out. Just execute
the proc. It should do the same thing.

Regards,
Simon.

Hi,

you can do as follow


create existing table <table name>
( specify the column as similar to sp_who or other procedure output)

external procedure at "<servername>.<dbname>.<dbo>.<procedure name>

ex
--
create existing table xyz
( fid varchar(2),
spid varchar(2),
..............
)
external procedure at "india.chennai.jerrie.sp_who>

after that issue an select query against the xyz

Simon Andrew wrote:
Thats actually what I tried to do in the first place, but for some
reason when I go to do my ct_fetch (after ct_results etc) all I get is
my return value as "CS_ROW_FAIL" for a few times (it may even be the
number of rows I am expecting) and then I get CS_END_DATA and I have no
results to show for it.

This is the technique I would prefer, but I can't get it to work. It
works fine with normal "select" statements, but when I change the type
to CS_RPC_CMD in ct_command and try and execute stored procedures (like
sp_who, sp_helpdevice etc), the results don't work. I even tried using
CS_LANG_CMD and doing things like "exec sp_who" as the sql, but that
didn't work either.

Any other ideas?

Thanks for your help.

Regards
Luke

On Mon, 23 Aug 2004 11:56:00 +0800, Luke wrote:

You've obviously got some error in your code, because this should work
just fine.

I would suggest that you look at some of the C source code that's out
there (e.g. the C part of DBD::Sybase [look in dbdimp.c] or of
Sybase::CTlib [from sybperl]). You can download gzipped tar files from
http://www.peppler.org/downloads

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html