External Scheduler Integration

Anyone have a simple script that might be used by an external scheduler
such as Control-M to kick off .sql files and return back appropriate
return codes and error messages.
SpencerHi
Check out the osql utilitiy in books online and use the -b option to return
a value in %ERRORLEVEL%
John
"stabbert" wrote:
> Anyone have a simple script that might be used by an external scheduler
> such as Control-M to kick off .sql files and return back appropriate
> return codes and error messages.
> Spencer
>

External Scheduler Integration

Anyone have a simple script that might be used by an external scheduler
such as Control-M to kick off .sql files and return back appropriate
return codes and error messages.
SpencerHi
Check out the osql utilitiy in books online and use the -b option to return
a value in %ERRORLEVEL%
John
"stabbert" wrote:

> Anyone have a simple script that might be used by an external scheduler
> such as Control-M to kick off .sql files and return back appropriate
> return codes and error messages.
> Spencer
>

External Procedure error

I'm trying to compile the following function:

CREATE OR REPLACE FUNCTION shell(cmd IN VARCHAR2)
RETURN PLS_INTEGER
AS
EXTERNAL LIBRARY EXTPROCSHELL_LIB
NAME "extprocsh"
LANGUAGE C
PARAMETERS (cmd STRING);

but keep getting an error that says EXTPROCSHELL_LIB needs to be declared. Somewhere I read that this probably means that the shared library doesn't exist and try recreating it.

Our DBA assures me that he created the library in the Oracle home lib directory and I do see the compiled C program there and also saw the path of his library creation and everything looks fine.

Any suggestions here?Hello,

do you have created a library object with the same name ?
See "CREATE LIBRARY ..."

The error you have posted does not mean, that you dont have created the lib in the oracle directory.
Its says, that Oracle can not find the library object that points to the library in the oracle directoy ...

Hope that helps ?

Manfred Peter
Alligator Company GmbH
http://www.alligatorsql.comsql

Extended Stored Proc & Progress notification

In my extended stored procedure, I'm trying to return rows as data is
available. The client is expecting to use this data to show progress
notification (10%, 20% etc etc).
Even with variations of the srv_describe, srv_sendrow & srv_senddone
I'm not able to get the client to display the data as it becomes
available. Instead, the rows come all together at the end of the call.
Has anyone succeeded in acheiving this? What am I doing wrong?
Any help appreciated,
Kartik
----
Here is a snippet of the code..
...
srv_describe ( This.srvproc_,
1,
"messages",
SRV_NULLTERM,
SRV_TDS_NVARCHAR, // Dest data type.
(DBINT) 512, // Dest data length.
SRV_TDS_NVARCHAR, // Source data type.
(DBINT) 512, // Source data length.
NULL );
...
...
srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
);
if ( !srv_sendrow( This.srvproc_ ) )
{
ASSERT ( 0 );
}
...
...
srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
rowcount );
...Kartik,
I doubt that this is possible. what you are describing is callbacks or
events and SQL Server 2000 doesn't support this.
regards
Greg O
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403041544.df23472@.posting.google.com...
> In my extended stored procedure, I'm trying to return rows as data is
> available. The client is expecting to use this data to show progress
> notification (10%, 20% etc etc).
> Even with variations of the srv_describe, srv_sendrow & srv_senddone
> I'm not able to get the client to display the data as it becomes
> available. Instead, the rows come all together at the end of the call.
> Has anyone succeeded in acheiving this? What am I doing wrong?
> Any help appreciated,
> Kartik
> ----
> Here is a snippet of the code..
> ...
> srv_describe ( This.srvproc_,
> 1,
> "messages",
> SRV_NULLTERM,
> SRV_TDS_NVARCHAR, // Dest data type.
> (DBINT) 512, // Dest data length.
> SRV_TDS_NVARCHAR, // Source data type.
> (DBINT) 512, // Source data length.
> NULL );
> ...
> ...
> srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> );
> if ( !srv_sendrow( This.srvproc_ ) )
> {
> ASSERT ( 0 );
> }
> ...
> ...
> srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> rowcount );
> ...|||Greg,
I can receive the notification in the extended stored proc. Now to
inform the client about the notification, I had intended to send a
single row with the message. But somehow, the client seems to be
getting all the rows all at once at the end of the extended stored
procedure.
The documentation seems to indicate that the API does provide a way to
send data as it is produced.
Thanks,
Kartik
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in message news:<#OymsAmAEHA.2292@.TK2MSFTNGP12.phx.gbl>...
> Kartik,
> I doubt that this is possible. what you are describing is callbacks or
> events and SQL Server 2000 doesn't support this.
> regards
> Greg O
>
> "Kartik Shah" <k2shah10@.hotmail.com> wrote in message
> news:7f618d19.0403041544.df23472@.posting.google.com...
> > In my extended stored procedure, I'm trying to return rows as data is
> > available. The client is expecting to use this data to show progress
> > notification (10%, 20% etc etc).
> >
> > Even with variations of the srv_describe, srv_sendrow & srv_senddone
> > I'm not able to get the client to display the data as it becomes
> > available. Instead, the rows come all together at the end of the call.
> >
> > Has anyone succeeded in acheiving this? What am I doing wrong?
> >
> > Any help appreciated,
> > Kartik
> >
> > ----
> > Here is a snippet of the code..
> >
> > ...
> > srv_describe ( This.srvproc_,
> > 1,
> > "messages",
> > SRV_NULLTERM,
> > SRV_TDS_NVARCHAR, // Dest data type.
> > (DBINT) 512, // Dest data length.
> > SRV_TDS_NVARCHAR, // Source data type.
> > (DBINT) 512, // Source data length.
> > NULL );
> > ...
> >
> > ...
> > srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> > srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> > );
> > if ( !srv_sendrow( This.srvproc_ ) )
> > {
> > ASSERT ( 0 );
> > }
> > ...
> >
> > ...
> > srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> > rowcount );
> > ...

Extended Stored Proc & Progress notification

In my extended stored procedure, I'm trying to return rows as data is
available. The client is expecting to use this data to show progress
notification (10%, 20% etc etc).
Even with variations of the srv_describe, srv_sendrow & srv_senddone
I'm not able to get the client to display the data as it becomes
available. Instead, the rows come all together at the end of the call.
Has anyone succeeded in acheiving this? What am I doing wrong?
Any help appreciated,
Kartik
----
Here is a snippet of the code..
...
srv_describe ( This.srvproc_,
1,
"messages",
SRV_NULLTERM,
SRV_TDS_NVARCHAR, // Dest data type.
(DBINT) 512, // Dest data length.
SRV_TDS_NVARCHAR, // Source data type.
(DBINT) 512, // Source data length.
NULL );
...
...
srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
);
if ( !srv_sendrow( This.srvproc_ ) )
{
ASSERT ( 0 );
}
...
...
srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
rowcount );
...Kartik,
I doubt that this is possible. what you are describing is callbacks or
events and SQL Server 2000 doesn't support this.
regards
Greg O
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403041544.df23472@.posting.google.com...
> In my extended stored procedure, I'm trying to return rows as data is
> available. The client is expecting to use this data to show progress
> notification (10%, 20% etc etc).
> Even with variations of the srv_describe, srv_sendrow & srv_senddone
> I'm not able to get the client to display the data as it becomes
> available. Instead, the rows come all together at the end of the call.
> Has anyone succeeded in acheiving this? What am I doing wrong?
> Any help appreciated,
> Kartik
> ----
> Here is a snippet of the code..
> ...
> srv_describe ( This.srvproc_,
> 1,
> "messages",
> SRV_NULLTERM,
> SRV_TDS_NVARCHAR, // Dest data type.
> (DBINT) 512, // Dest data length.
> SRV_TDS_NVARCHAR, // Source data type.
> (DBINT) 512, // Source data length.
> NULL );
> ...
> ...
> srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> );
> if ( !srv_sendrow( This.srvproc_ ) )
> {
> ASSERT ( 0 );
> }
> ...
> ...
> srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> rowcount );
> ...|||Greg,
I can receive the notification in the extended stored proc. Now to
inform the client about the notification, I had intended to send a
single row with the message. But somehow, the client seems to be
getting all the rows all at once at the end of the extended stored
procedure.
The documentation seems to indicate that the API does provide a way to
send data as it is produced.
Thanks,
Kartik
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in message news:<#OymsAm
AEHA.2292@.TK2MSFTNGP12.phx.gbl>...
> Kartik,
> I doubt that this is possible. what you are describing is callbacks or
> events and SQL Server 2000 doesn't support this.
> regards
> Greg O
>
> "Kartik Shah" <k2shah10@.hotmail.com> wrote in message
> news:7f618d19.0403041544.df23472@.posting.google.com...|||Hi,
In my experience that isn't the case. The process is internal to SQL Server
(within the memory space) so you would have to jump out of your process and
return to the calling process. I see no facility that does this.
You might want to get in touch with a guy names Gert ER Drapers at
www.sqldev.net. Gert was the architect of the SQL Server Tools in SQL
Server. Gert's a good bloke and will be able to tell you if it can or can't
be done. If it can he can tell you the correct way to do it.
regards
Greg O
http://www.ag-software.com
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403050653.2505cc9c@.posting.google.com...
> Greg,
> I can receive the notification in the extended stored proc. Now to
> inform the client about the notification, I had intended to send a
> single row with the message. But somehow, the client seems to be
> getting all the rows all at once at the end of the extended stored
> procedure.
> The documentation seems to indicate that the API does provide a way to
> send data as it is produced.
> Thanks,
> Kartik
>
> "Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
message news:<#OymsAmAEHA.2292@.TK2MSFTNGP12.phx.gbl>...

Ext. SPs

I need to be able to create my own MSSQ UDF to return the current system
date, offset by a configurable number of minutes, depending on a value in a
table. The reason I am trying to do this is, I want to be able to, for
testing purposes, fake the system into thinking that time has elapsed.
Changing the time on the computer is not an option.

I would like to call the UDF MyGetDate and to replace all code occurrence of
getdate() in the database with this call. This includes column default value
constraints and stored procedures.

The problem is that MSSQL does not allow the function GETDATE with a UDF. I
thought to try an fake it out by having the UDF call a SP, which in turn
called GETDATE. When I did this, I got the error 'Only functions and
extended stored procedures can be executed from within a function.'

I guess I can go down the road to try and learn how to write an extended
stored procedure to return the current time, but I imagine that there is a
learning curve here.

I realize that all COLUMN default CONSTRAINT with GETDATE could be handled
by create ADD AND UPDATE TRIGGERS that populate thisIt's a kludge but you can create a UDF that reads a value from a table.
Then schedule a Sql Agent Job to run every minute, updating that
table's value.|||Chad (chad.dokmanovich@.unisys.com) writes:
> I need to be able to create my own MSSQ UDF to return the current system
> date, offset by a configurable number of minutes, depending on a value
> in a table. The reason I am trying to do this is, I want to be able to,
> for testing purposes, fake the system into thinking that time has
> elapsed. Changing the time on the computer is not an option.
> I would like to call the UDF MyGetDate and to replace all code
> occurrence of getdate() in the database with this call. This includes
> column default value constraints and stored procedures.
> The problem is that MSSQL does not allow the function GETDATE with a
> UDF. I thought to try an fake it out by having the UDF call a SP, which
> in turn called GETDATE. When I did this, I got the error 'Only functions
> and extended stored procedures can be executed from within a function.'
> I guess I can go down the road to try and learn how to write an extended
> stored procedure to return the current time, but I imagine that there is a
> learning curve here.

Using a UDF in all sorts of constraints, could have performance issues,
and if that UDF calls an extended procedure that does not make things
better.

You could save the show with:

CREATE FUNCTION kalle(@.d datetime) RETURNS datetime AS
BEGIN
RETURN dateadd(DAY, 12, @.d)
END
go
select dbo.kalle(getdate())

Yes, that will be somewhat bulkier, but it should get the job one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Expression syntax

what is the syntax for IN in Expression?

I am tryin to do @.ID IN (1,2,3,4) then return 1 else 0...

FINDSTRING("1,2,3,4",@.[User::ID],1) > 0|||

Thank you so much

Expression help

Howdy, I think this will be aneasy question. I want to return the Max
of three database fields.
I might have Serious = 20 and Medium = 10 and Low = 30
In that case I want it to return 30.
if it is Serious = 2 Medium = 0 and Low = 5
I want it to return 5
If it is serious = 0 Medium =10 Low =3
I want it to return 10.
I am looking at the Max function (aggregate) and can't figure out the
syntaxt. Any help would be appreciated.try
=Math.Max(Fields!Low.Value,Math.Max(Fields!Medium.Value,Fields!Serious.Value))
"Mandoskippy" wrote:
> Howdy, I think this will be aneasy question. I want to return the Max
> of three database fields.
> I might have Serious = 20 and Medium = 10 and Low = 30
> In that case I want it to return 30.
>
> if it is Serious = 2 Medium = 0 and Low = 5
> I want it to return 5
>
> If it is serious = 0 Medium =10 Low =3
> I want it to return 10.
> I am looking at the Max function (aggregate) and can't figure out the
> syntaxt. Any help would be appreciated.
>