Skip to Content

Convert SQL Server script to BODS Script

Hi All,

Below the Script is SQL Server script

IF OBJECT_ID('tempdb..#tblDebugLog') IS NOT NULL

DROP TABLE #tblDebugLog

How to write above the Script in SAP BODS?

Please let us know.

Thanks,

krish.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Jan 23 at 01:19 PM

    You must escape the single quotes in your 2nd parameter with a backslash:

    ...OBJECT_ID(\'tempdb..#tblDebugLog\')...

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 23 at 01:03 PM

    Hi Kalyani,

    Thanks for replay!. I have used to same code but it doesn't work. I am getting syntax error.

    Syntax Error: near <tempdb> found <identifier> expecting <')',',')>

    Thanks,

    krish.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 23 at 08:43 AM

    Write a database procedure and call it from DS.

    This will be helpful Click Here

    Shaz

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 23 at 10:06 AM

    You can try the below script.. it may help you

    SQL('Database_Name',

    'Begin try

    Begin tran;

    If exsits (OBJECT_ID('tempdb..#tblDebugLog') IS NOT NULL)

    begin

    DROP TABLE #tblDebugLog

    end

    End try

    Begin Catch

    rollback;

    throw;

    End Catch

    Commit;');

    Best Regards,

    Kalyani

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 30 at 06:38 AM

    Hi Krish,

    Existing SQL Script:

    IF OBJECT_ID('tempdb..#tblDebugLog') IS NOT NULL

    DROP TABLE #tblDebugLog

    Converted BODS Script are as below:

    Step 1:First you want to create global variable (For e.g. $Test_Object_Id is a global variable) and assign the OBJECT_ID values to that $Test_Object_Id global variable.

    Step 2:Write below converted BODS script and execute.

    If($Test_Object_Id is not null)

    begin

    sql( 'Datastore_Name' , 'DROP TABLE #tblDebugLog');

    end

    Regards,

    Dhanraj

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 30 at 10:34 AM

    Hello Kris,

    Hope your req is as below

    Needs to dropped the object if exist else 'some task ' Isn't it ? If this is correct then the below is the best way User script and write the below code.

    $Objectcnt=sql('SQLSERVER_SRC','SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'<table_name>\' ' );If ($Objectcnt >0)
    begin
    sql('SQLSERVER_SRC','drop table C22type' );
    print('object has been dropped' );
    end
    else begin
    print('No objct foudn' );
    end

    Note : You have to have use the ODBC connection the direct SQL server connetion not support the SQL fnction
    Add comment
    10|10000 characters needed characters exceeded