October 31, 2006 at 4:00 pm
I have two servers MTA23 and MTA26. I linked from 26 to 23 so I could copy data from a table on 23to insert into an identical table on 26.
This query works OK in query analyzer on 26. When I cut and paste the code into a stored procedureand execute it on 26, it doesn't do anything, though I get no error message.
CREATE PROCEDURE dp_MoveFromMTA23 @MovedDate datetime = null AS
set ansi_nulls on
set ansi_warnings on
if @moveddate is null set @moveddate = getdate()
update mta23.pointchecks.dbo.p_pointchecks_test
set moveddate = @moveddate
where moveddate is null
insert into p_pointchecks_test
select * from mta23.pointchecks.dbo.p_pointchecks_test
where moveddate = @moveddate
GO
What am I doing wrong?
November 1, 2006 at 2:07 am
I'd check your logic with the dates .. do you use the exact same query in QA or just the last statement?
btw you shouldn't really need the set commands inside your proc, but you might like to add set nocount on
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 1, 2006 at 8:51 am
usually I find that when a linked query fails silently it is often something simple - like the table or column on the target server not existing ... or equally daft things.
Probably not that helpful to you tho!
November 1, 2006 at 2:34 pm
I would make sure if there is any null in the movedate in the server 23. In particular, after testing your script, you might not have any null in this column if there was no data with null inserted.
November 15, 2006 at 10:48 am
I believe now that this is some kind of login permissions issue, or some other config issue. From MTA26 I can get select results from MTA23, but cannot do inserts. I'm a sysadmin on both servers, and in both sql instances.
Versions of this query have been running locally (on MTA23) for several years with no problems.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply