Convert SQL Server script to BODS Script

[former_member281308]

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.

[Former Member]

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

[former_member434646]

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

[former_member187605]

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

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

[kalyani_kolli]

Yes..Thank you for correction i forgot to mention backslash's

[former_member281308]

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.

[kalyani_kolli]

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

[former_member442248]

Write a database procedure and call it from DS.

This will be helpful Click Here

Shaz