Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    94

    Unanswered: Perl - SQL Server

    Hi

    Is any one use Perl scripts to to do any work against SQL Server?. If yes, want to know how can I write Perl script to do SQL Server maintainance work. Please forward me if you have perl scripts.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    16
    Hi Ramshree,

    yes connecting to SQLServer via Perl is very possible. It is something that a good cross section of OpenLink users have performed.

    Firstly you will need ODBC connectivity to the database from the machine you intend to create the Perl Script on.

    Have a look at http://www.openlinksw.com for downloading free fully functional evaluation driver.

    Single tier or Multi Tier, they both perform the same thing, just a matter of preference as to which you want.

    For the Perl side rather then duplicating work , I would like to point you to the following site http://www.iodbc.org here you will find a Perl HOWTO, and PHP if you interested, as well as postings from other users and OpenLink support staff, so you will be in good hands.

    HTH

    BR

    John

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You can use SQL-DMO or ADO to connect to SQL Server to run queries and perform maintenance routines. SQL-DMO is installed when you install the client software on your PC and ADO is free and can be download from Microsoft.

    I do all of my work using Perl, here is a sample program, you'll need to write your error handling. It uses SQL-DMO to connect to a server run DBCCs on a database then dump the database out to a predefined dump device.
    Code:
    use Win32::OLE;
    use Win32::OLE::Const ('Microsoft SQLDMO Object Library');
    
    #-------------------------------------------------------------------------------
    # Program variables declaration
    #-------------------------------------------------------------------------------
    my ($strText);
    my ($oServer)       = new Win32::OLE("SQLDMO.SQLServer");
    my ($oBackup)       = new Win32::OLE("SQLDMO.Backup");
    my ($dbccDB);
    
    $oServer->{'LoginSecure'} = 1;
    $oServer->Connect ("ATLAS");
    # Use this for non-trusted connections
    #$oServer->Connect ("server", "Login", "Password");
    if ( Win32::OLE::LastError() ) {
        print "Error:: on connecting to $server [" . Win32::OLE::LastError()  . "] " . Win32::FormatMessage( Win32::OLE::LastError() );
        exit(1);
    }
    
    $dbccDB = $oServer->Databases->{"fsp"};
    
    $strText = $dbccDB->CheckAllocations;
    if ( Win32::OLE::LastError() ) {
        print "CheckAllocations $strText";
    }
    
    $strText = $dbccDB->CheckTables;
    if ( Win32::OLE::LastError() ) {
        print "CheckTables $strText";
    }
    
    $strText = $dbccDB->CheckCatalog;
    if ( Win32::OLE::LastError() ) {
        print "CheckCatalog $strText";
    }
    
    $oBackup->{'TruncateLog'}           = SQLDMOBackup_Log_Truncate;
    $oBackup->{'Action'}                = SQLDMOBackup_Database;
    $oBackup->{'Database'}              = $dbccDB->{'Name'};
    $oBackup->{'Initialize'}            = $fastdump == 1 ? 0 : 1;
    $oBackup->{'Devices'}               = $dbccDB->{'Name'} . "Backup";
    
    $oBackup->SQLBackup($oServer);
    if ( Win32::OLE::LastError() ) {
        print "Error:: Dumping database [" . Win32::OLE::LastError()  . "] " . Win32::FormatMessage( Win32::OLE::LastError() );
    }
    #-------------------------------------------------------------------------------
    #   Disconnect from SQL Server and release object.
    #-------------------------------------------------------------------------------
    $oServer->DisConnect;
    $oServer->Close;
    
    exit;
    
    1;
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •