Linked Server Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Linked Server Stored Procedure

Author

Topic

talib.ali.khan
Starting Member

3 Posts

Posted - 2012-02-06 : 02:10:27

Hi Folks,

I am facing some issue .The scenario is like this : I've created a LINKED SERVER in SQL Server 2005
to connect to ORACLE database(so far so good) .After that i had to call their Stored Procedures, in which i was facing some problems.
So i decided to create stored procedures in SQL with the script similart to what the Oracle Stored Procedure looks like.
I used EXEC (' ') AT <SERVER NAME> syntax to create the SP. The main issue is the WHERE clause which is like illustrated below

SYNTAX in SQL SP
----------------
WHERE (LOG_FUN_ID =? OR LOG_FUN_ID=NULL)
AND (LOG_STATUS_CODE=? OR LOG_STATUS_CODE=NULL)
AND (LOG_CHNL_ID=? OR LOG_CHNL_ID=NULL)
AND (T1.LOG_CREATION_TMSTMP LIKE ? || ''%'' OR T1.LOG_CREATION_TMSTMP=NULL)
AND (T1.LOG_CREATION_TMSTMP LIKE ? || ''%'' OR T1.LOG_CREATION_TMSTMP=NULL)

group by LOG_FUN_ID,LOG_STATUS_CODE

',
@LOG_FUN_ID,@LOG_STATUS_CODE,@LOG_CHNL_ID,@LOG_CRET_FRM,@LOG_CRET_TO) AT <servername>

----------------------------------------

SYNTAX in ORACLE SP
-------------------

WHERE (LOG_CREATION_TMSTMP >= to_timestamp(p_LOG_CRET_FRM, 'DD-MM-YYYY HH24:MI:SS') or p_LOG_CRET_FRM is null)
AND (LOG_CREATION_TMSTMP < to_timestamp(p_LOG_CRET_TO, 'DD-MM-YYYY HH24:MI:SS') or p_LOG_CRET_TO is null)
AND (LOG_CHNL_ID = p_LOG_CHNL_ID or p_LOG_CHNL_ID is null)
AND (LOG_FUN_ID = p_LOG_FUN_ID or p_LOG_FUN_ID is null)
AND (LOG_STATUS_CODE like p_LOG_STATUS_CODE ||'%' or p_LOG_STATUS_CODE is null)

group BY log_fun_id,log_status_code ;

The parameters are preceded by "p" in the above Oracle script.

So the SQL Script is not returning any records even though data is present in Oracle DB.

---------

Best Regards,
Talib Ali Khan

duncanwill
Starting Member

20 Posts

Posted - 2012-02-06 : 08:10:17

The overall problem is not clear from your post but the two sets of SQL are not the same.

Take the "LOG_FUN_ID" column criteria for example. Here in the MS SQL you are saying:

Where LOG_FUN_ID (the column) = parameter value or where LOG_FUN_ID (the column) is null

But in Oracle your SQL is checking the parameter for NULL (not the column). The Oracle version is saying:

Where LOG_FUN_ID (the column) = parameter value or where parameter is null

talib.ali.khan
Starting Member

3 Posts

Posted - 2012-02-06 : 08:32:33

quote:
Originally posted by duncanwill

The overall problem is not clear from your post but the two sets of SQL are not the same.

Take the "LOG_FUN_ID" column criteria for example. Here in the MS SQL you are saying:

Where LOG_FUN_ID (the column) = parameter value or where LOG_FUN_ID (the column) is null

But in Oracle your SQL is checking the parameter for NULL (not the column). The Oracle version is saying:

Where LOG_FUN_ID (the column) = parameter value or where parameter is null

Hi,

That's what i'm trying to convey that the syntax is not working in SQL whereas the OR operator is working properly in Oracle.The DYNAMIC parameters are working fine when used with just "?" but whereas if i add OR statement like this
(LOG_FUN_ID =? OR LOG_FUN_ID=NULL)
it's not retrieving any record. I know i'm missing some correct syntax to make the condition look exactly like that's present in ORACLE SP.

---------

Best Regards,
Talib Ali Khan

duncanwill
Starting Member

20 Posts

Posted - 2012-02-06 : 09:41:13

My point is that the two SQL statements are not the same and will not therefore return the same results.

I am not sure why you have a question mark (?) as the parameter (is it an ADO connection of sort sort i.e. from SSIS?), but the MS SQL version of the Oracle line i have highlighted would be:

WHERE (LOG_FUN_ID =? OR ? IS NULL)

the syntax "= NULL" is wrong (at least it is not at all what you want) you need to use "IS NULL" and in your Oracle example you are checking whether the parameter (not the column) is null.

talib.ali.khan
Starting Member

3 Posts

Posted - 2012-02-06 : 09:58:54

The question mark is being used to pass parameters dynamically with respect to the format of EXEC ('<Select Statement>') AT <Server Name> I tried using the SAME query that's present in ORACLE but it's not returning records. The point to be noted here is that if i use OR operator instead of AND , it's working fine for only one parameter at a time but not for more than 1.

---------

Best Regards,
Talib Ali Khan

duncanwill
Starting Member

20 Posts

Posted - 2012-02-06 : 10:06:56

Have you changed the code as per my comments?
1) Have you swapped the "= NULL" to "IS NULL".
2) Have you changed the check on the column name LOG_FUN_ID to be a check on the parameter instead?

Points 1 and 2 mean the SQL in your MS example is different to the "working" Oracle version. The "OR LOG_STATUS_CODE=NULL" will always be false.

I suggest stripping back the SQL to the bare basics and using a simple one line WHERE clause to test the correct syntax of the OR statement against a known set of results. You appear to be comparing apples with oranges in your SQL (unless you have updated it in which case can you re-post it?)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources