February 9, 2011 at 11:17 am
Hello All,
I can run a procedure succesffully from SSMS but when I add the procedure to a job step and run the job either scheduled or manually I get a syntax error.
Executed as user: MYDOMAIN\AgentAccount. EXEC dbo.myProc 'ABC','DEF',170 [SQLSTATE 01000] (Message 0) Insert Error: Column name or number of supplied values does not match table definition. [SQLSTATE 21S01] (Error 213). The step failed.
I tried recreating the job to no avail.
The procedure I am calling creates a cursor and then within the cursor calls sp_executesql
exec sp_executesql @ProcedureString
(If anyone knows a better way to call a procedure multiple times passing different variables to it I'd be happy to hear about it).
The job was working. I did make changes to the called procedure would could result in the error message. Its almost as if the job is calling the old procedure.
I'm stumped.
February 9, 2011 at 11:35 am
Let me start by saying this is pretty difficult to debug without more knowledge of the procedure and the changes you made.
That said for the dynamic SQL, you should be able to call the procedure from within the cursor without using dynamic SQL. Do the following
Exec dbo.ProcName @Variable1, @Variable2, ....
where @Variable1 and @Variable2 get different values through each record processed in the cursor.
As for the procedure being called....do you have a schema problem? For example if you have procedures within MySchema but the new procedure was created in the dbo schema (or vice versa), you might not be calling the right proc.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
February 9, 2011 at 1:23 pm
February 9, 2011 at 1:26 pm
ChazMan has a good point.
- Can you post the sproc ddl ?
- did you schema qualify all objects addressed withing the sproc ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 9, 2011 at 2:07 pm
I have isolated to an insert into a temp table here. This is the proc being called.
ALTER PROCEDURE [dbo].[myProc]
@Account_Group varchar(50),
@Type varchar(10),
@SessionID int
AS
CREATE TABLE dbo.#Accounts (Account varchar(50))
IF @AccountType = 'Account'
BEGIN
INSERT INTO dbo.#Accounts --If I comment this out it works
SELECT @Account_Group
ELSE
...other stuff thats not the issue
February 9, 2011 at 2:22 pm
Silly as it sounds, try it without the dbo. in front of the #Accounts
Also, I assume IF @AccountType = 'Account' is supposed to be @Type, from the parameters.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 9, 2011 at 2:34 pm
Below is what I have tried and the errors created.
INSERT INTO #Accounts (#Accounts.Account) VALUES (@Account_Group)
Error: Invalid column name 'Account'.
INSERT INTO #Accounts SELECT @Account_Group
Insert Error: Column name or number of supplied values does not match table definition
I guess i could eliminate the temp table
February 9, 2011 at 2:48 pm
My main procedure and the procedure it was calling both had a temp table called #Accounts. I changed the name in the called procedure and it worked. Thanks again to those who responded! I will now cease banging my head against my desk.
February 9, 2011 at 3:01 pm
FROM BOL, CREATE TABLE
If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
February 9, 2011 at 3:02 pm
EDIT: Late update, ignore.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply