July 16, 2008 at 11:44 am
Ok here is a strange one.
Here is a line of code being executed
select * from OPENQUERY ([SQL-MM], 'SET FMTONLY OFF; declare @mydate datetime; set @mydate = getdate(); exec report_db.dbo.usp_Last_Data_Load_Update @loaddate = @mydate, @dataload_sp=''storedprocgoeshere'';select 1;')
Yes it is a work around for a link server issue. I can't use the 4 part syntax, believe me i wish i could. Not worth getting into, just accept that it can't be done.
So the issue is the proc that gets called in the openquery updates the first time it gets called.
The next time it does not. It will not until some part of the tsql changes. And by this i do not mean just the date or the procname, that will work but even putting select 2 at the end or changing anything else that would have no effect on the update.
Note: this is not an issue with the code in the proc. That code simply says set a value = the date you pass in where the value of a column is x. Note x never changes
strange, strange and strange.
Any ideas anyone
July 16, 2008 at 12:10 pm
update :
Jsut to try, I used the declare @sql nvarchar(4000) tactic and packed the entire select * from openqery etc into a string and used execute sp_executesql @sql. Same behavior as before. It updates the data on the first change then does nothing.
Again note that even if i hardcode date and proc name, meaning i can leave the proc name constant and manually change the date getting passed in, so i can control the change in the table that should be reflected, this will not update until some part of the tsql is modifed.
July 16, 2008 at 12:12 pm
Also, i tried to call the proc with recompile and put with recompile into the proc definition. No luck there either.
Note: I do not get any error just no update.
July 16, 2008 at 1:12 pm
Once again thanks for the helpful replies. Same as always.
I was able to use the 4 part syntax by creating a new link sever or whatever 2005 is calling it now.
Some extemely serious caching going on. Several tests were done and same behavior each time with open query. even chaning the sql stopped working when it was used again within x amt of time
Once again thanks to all for your hellp.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply