October 20, 2010 at 3:50 am
Hi All,
I am getting below error messages when executing below update sql.
UPDATE a
SET club_status = (Select * from Openquery(LNK_NETFORUM_DEV,
'select netFORUMDB_INCMDEV.dbo.client_ri_org_status_by_date(club_cst_key,ISNULL(end_date,GETDATE()))'))
FROM tmp_club_final a
OLE DB provider "SQLNCLI10" for linked server "LNK_NETFORUM_DEV" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'end_date'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'club_cst_key'.
LNK_NETFORUM_DEV is the linked server and I am calling UDF dbo.client_ri_org_status_by_date. Columns club_cst_key and end_date belongs to table tmp_club_final.
Can we use tmp_club_final in this query since it reside on the server where I have created a linked server. I think in this statement compiler is trying to find this object on netFORUMDB_INCMDEV database or not getting valid reference? I am stuck here because don't know whether we can use local tables with the sql where trying to access tables or UDF using linked server. :unsure:
Please help me to resolve this issue.
Thanks in advance
Bhushan
October 20, 2010 at 1:03 pm
Try this code, and let me know the output.
Declare @Query varchar(Max)
SELECT @Query = ISNULL(@Query,'') +
'UPDATE a
SET club_status = (Select * from Openquery(LNK_NETFORUM_DEV,
''select netFORUMDB_INCMDEV.dbo.client_ri_org_status_by_date(' + convert(varchar,club_cst_key) + ',ISNULL(''' + convert(varchar,end_date,101) + ''',GETDATE()))''))
FROM tmp_club_final a; '
From tmp_club_final
Print @Query
Exec (@Query)
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
October 20, 2010 at 1:07 pm
I seem to remember not being able to query UDFs through a linked server, and I think I had to solve it by using OpenRecordset. Was a few years ago, so it might not be true any more.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2010 at 1:17 pm
You are right, you can not access a function directly through link server. like
Select [LinkServer].[database].[schema].[function] ()
but you can access function by pass-through queries. as given below:
Select OpenQuery([LinkServer],'Select [database].[schema].[function]()')
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
October 21, 2010 at 12:22 am
Hi Muhammad,
It's running without error but not updating club_status in table unfortunately.
October 21, 2010 at 3:01 am
can you share its output? i just added a where clause in dynamic sql.
Declare @Query varchar(Max)
SELECT @Query = ISNULL(@Query,'') +
'UPDATE a
SET club_status = (Select * from Openquery(LNK_NETFORUM_DEV,
''select netFORUMDB_INCMDEV.dbo.client_ri_org_status_by_date(' + convert(varchar,club_cst_key) + ',ISNULL(''' + convert(varchar,end_date,101) + ''',GETDATE()))''))
FROM tmp_club_final a
Where club_cst_key='+ varchar(convert,club_cst_key) +' and end_date=''' + convert(varchar,end_date,101) + '''; '
From tmp_club_final
Print @Query
Exec (@Query)
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
October 21, 2010 at 4:47 am
Hi Muhammad,
It's giving an error as below
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ','.
October 21, 2010 at 7:34 am
I tested it on my end, it worked fine. now share your output with me by executing below code.
Declare @Query varchar(Max)
SELECT @Query = ISNULL(@Query,'') +
'UPDATE a
SET club_status = (Select * from Openquery(LNK_NETFORUM_DEV,
''select netFORUMDB_INCMDEV.dbo.client_ri_org_status_by_date(' + convert(varchar,club_cst_key) + ',ISNULL(''''' + convert(varchar,isnull(end_date,getdate()),101) + ''''',GETDATE()))''))
FROM tmp_club_final a
Where club_cst_key='+ convert(varchar,club_cst_key) +' and end_date=''' + convert(varchar,isnull(end_date,getdate()),101) + '''; '
From tmp_club_final
Print @Query
Exec (@Query)
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
May 29, 2012 at 7:03 am
Hi
I am try to do this buut I get the same error
DECLARE @begin_time DATETIME, @end_time DATETIME, @begin_lsn BINARY(10), @end_lsn BINARY(10), @stsql as varchar(100);
SELECT @begin_time = GETDATE()-1, @end_time = GETDATE();
SELECT * from OPENQUERY([Dorsa1],'[Tehran].[dbo].sys.fn_cdc_map_time_to_lsn("smallest greater than", @begin_time)' )
May 29, 2012 at 7:10 am
remove one of schemas, use dbo or sys.
Tehran.Sys.[function_name]
Or
Tehran.dbo.[function_name]
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply