Skip to Content
0

Convert SQL Server script to BODS Script

Jan 22 at 11:52 AM

87

avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Dirk Venken
Jan 23 at 01:19 PM
0

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

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
avatar image
Former Member Jan 23 at 01:03 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Shazin Siddiqui Jan 23 at 08:43 AM
0

Write a database procedure and call it from DS.

This will be helpful Click Here

Shaz

Share
10 |10000 characters needed characters left characters exceeded
kalyani kolli Jan 23 at 10:06 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 30 at 06:38 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 30 at 10:34 AM
0

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
Share
10 |10000 characters needed characters left characters exceeded