I'm trying to call a stored procedure from a perl script using the
MSSQL::DBLIB module.  My stored procedure requires three parameters
(date, id #, current timestamp).  Can anybody help me with the syntax
for this?  I would consider using a different module, but I am trying
to incorporate into an already existing script.

SP name:
     my_storedproc
Req. Parms:
     date (yyyy/mm/dd)
     id # (xxxxx)
     current timestamp (yyyy/mm/dd hh:mm:ss)

Thanks.

newbie24 (apomt***@yahoo.com) writes:
You can tell that it's a non-trivial operation, when it takes the
author of the module half an hour to compose a sample.

This is the procedure:

   CREATE PROCEDURE test_sp @d datetime,
                            @i int,
                            @now datetime AS

   PRINT '@d = ' + convert(varchar, @d)
   PRINT '@i = ' + convert(varchar, @i)
   PRINT '@now = ' + convert(varchar, @now)

This is the Perl script:

   use strict;

   use MSSQL::DBlib;
   use MSSQL::DBlib::Const::Datatypes;
   use MSSQL::DBlib::Const::General;

   DBSETLSECURE;

   my $X = MSSQL::DBlib->dblogin();  
   # Login to local server with integrated security.

   $X->dbuse('tempdb');
   1 while ($X->dbresults() != NO_MORE_RESULTS);

   dbmsghandle(\&my_msg_handler);

   $X->dbrpcinit('dbo.test_sp', 0);

   $X->dbrpcparam('@d', 0, SQLDATETIME, -1, 8, '20051212');
   $X->dbrpcparam('@i', 0, SQLINT4, -1, -1, 132);
   $X->dbrpcparam('@now', 0, SQLDATETIME, -1, 17, '20051212 19:12:47');

   $X->dbrpcsend();
   1 while ($X->dbresults() != NO_MORE_RESULTS);
   $X->dbretdata2();

   $X->dbclose();

   dbexit;

   sub my_msg_handler{
      print join("<->", @_), "\n";
   }

As you can see that the date format is not the one you requested. DB-
Library does not look at regional settings as I recall. The message-
handler stuff appears in the example, only to give some output.

Here is the same sequence with Win32::SqlServer:

   use strict;
   use Win32::SqlServer;

   my $X = sql_init();

   $X->sql_sp('dbo.test_sp', ['2005-12-12', 9898, '2006-04-08 00:20:59'])

Check Constraint
More on querying remote server
many small queries vs one large query
Plz help me! I want to use SQL Server2000 DTS Import/Export Wizard functionality from C# application
Default Values for a Column