October 14, 2009 at 10:27 am
Hi, can anyone shed some light on this weird problem...
I've created a stored procedure. When I run the stored procedure it works fine.
The code to run the procedure is simply this:
exec [dbo].[hth_update_automated_contact_lists] null -- @single_list_id
When I put the stored procedure in a job, with the exact same parameters, it starts spewing out weird data. I've narrowed it down to a dynamic sql statement:
declare @list_conditions_query nvarchar(max)
set @list_conditions_query='select id, conditions from _list where conditions is not null'
if @single_list_id is not null
set @list_conditions_query=@list_conditions_query+' and id='''+convert(nvarchar(36),@single_list_id)+''''
INSERT INTO #list_conditions (list_ID, conditions)
EXEC sp_executesql @list_conditions_query
So the above DOES work when the procedure is run with a standard exec command
The procedure can take a list id parameter - @single_list_id, but is usually null and is null in the job.
To stop the job from messing up, I have to do a regular select statement, so instead of the insert/exec piece of code I have this:
INSERT INTO #list_conditions (list_ID, conditions)
select id, conditions
from _list
where conditions is not null
which really should be doing the exact same thing.
I thought it might be the id to nvarchar conversion but actually it shouldn't get that far anyway so it can't be that.. (any better of doing this by the way?)
Why might it mess up in a scheduled job?
October 14, 2009 at 11:11 am
You have this code:
if @single_list_id is not null
set @list_conditions_query=@list_conditions_query+' and id='''+convert(nvarchar(36),@single_list_id)+''''
INSERT INTO #list_conditions (list_ID, conditions)
EXEC sp_executesql @list_conditions_query
Though I think the preferred way to pass variables into dynamic sql with sp_executesql is this way:
IF @single_list_id IS NOT NULL
BEGIN
INSERT INTO #list_conditions(list_ID, conditions)
EXEC sp_executesql @list_conditions_query
END
ELSE
BEGIN
SET @list_conditions_query = @list_conditions_query + ' and id=@single_list_id'
INSERT INTO #list_conditions(list_ID, conditions)
EXEC sp_executesql @list_conditions_query, N'@single_list_id INT',@single_list_id=@single_list_id
END
Apart from this, I see no reason (and I don't have enough detail information and data) why the procedure gives different results ran from SSMS than from a job. Have you looked at triggers for example? Did you run profiler while running the job and while running the proc from SSMS?
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
October 15, 2009 at 7:48 am
Hi, thanks for your reply and code.
I have been doing some testing and know what is happening (though I still don't know why..)
So the @listconditions variable just stores a bit of SQL code that select a list of IDs. When run through a stored procedure normally it pulls out the complete code. For some bizarre reason when it is run through the job it pulls out only some of what is in the conditions field. It's really weird. It's like it is taking either an old version of what was in the coditions field from.. somewhere(?) or it is just taking the first few lines so half the query code goes missing. This seems unlikely though, unless SPs run through jobs treat nvarchar(max) fields differently which would be pretty crazy.
The only thing I can think of is that it is for some strange readon pulling something out of the test database, though I don't see why. I've checked this in the procedure and the job and there is nowhere that it could be doing this mistakenly.
Very confusing.. It's probably something very simple though
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply