April 26, 2011 at 9:58 am
This is the second time I've had this problem, a completely different package (first time is here). It's driving me nuts because I don't want to have to update the package and move it through our environments.
I have a variable called Percentage set as a DOUBLE with .25 as the value. The variable feeds into an Execute T-SQL Task as a parameter of type DOUBLE. This is the only parameter on this task, there is no result set.
The package executes a stored procedure with the syntax "EXEC MySchema.MyProc ?; " (no quotes). The proc takes the parameter, does a whole bunch of staging data massage, then inserts records into three tables. It works fine in Production.
There were some code changes to the proc, but only internal calculations. Nothing that changes the parameter or the results (still inserts with nothing being returned to the package). I restored a copy of Production down to Test, loaded a copy of the latest package from TFS, and now this step is erroring out with that horrid error:
Error: 0xC002F210 at Create Feed Records, Execute SQL Task: Executing the query "Exec MySchema.MyProc ?;" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The proc works fine in SSMS. There is no syntax error, I have not altered the package at all, and there has never been a ResultSet. As I said earlier, this is the second time I've had trouble with the Double type.
I'd really really rather not have to alter the package at all as this causes me even more work (and our testers more work).
Does anyone have any ideas why something like this would fail in one environment when it's been working in another?
April 26, 2011 at 10:15 am
I have to start with the "is it plugged in" sort of questions as those are the things I find would most often trip me up.
Have you gone over the stored procedure? Is it possible that in the test environment someone has a SELECT statement at the end so they can see the results of the proc and make sure it is working properly?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 26, 2011 at 10:24 am
I would have the same kind of questions. Did anything in the proc change at all?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 10:26 am
Stefan Krzywicki (4/26/2011)
Have you gone over the stored procedure? Is it possible that in the test environment someone has a SELECT statement at the end so they can see the results of the proc and make sure it is working properly?
I've dropped and recreated the procedure in that database before I started, as part of the SDLC update. No one else, to my knowledge, is messing with this proc.
But just in case, I just dropped and recreated it again, the package failed again on that same step with the same error, and I've done a sp_helpText on the proc to make sure the one I think I'm using is the one I'm using.
I tried changing the variable to Object, but that didn't work this time. And Object doesn't exist as a parameter mapping data type.
There has to be something else going on if everything worked in Dev (I tested this last week) and works in Production. Something only our Test environment has.
April 26, 2011 at 10:39 am
SQLRNNR (4/26/2011)
I would have the same kind of questions. Did anything in the proc change at all?
As I stated in my initial post, the only changes were internal calculations on the data massage (the first temp table, way before the INSERT statements), an added table to a JOIN clause, and an ON clause for a LEFT OUTER JOIN in the first temp table INSERT. That's it.
Here's the English pseudo code:
Drop Temp Tables that I'll be using (4 of them)
Create & populate a date variable
Delete data from the 3 user tables where it has the same date variable as above (for those days when this gets run multiple times)
Delete unwanted data from Staging
Update '01/01/1900' dates to NULL
SELECT...INTO First Pass data goes into first temp table (altered code is here - it's a change in counting, an addition of a new table via a JOIN, and the altered ON clause of an existing JOIN. But there are no new columns.)
Create Temp Table 2
Second Pass data goes to Temp 2
Pivot Data into Temp Table 3
Update Temp 3 (about 6 different update statements)
Delete dups from Temp 3
SELECT...INTO Temp 4, Last Pass data aggregations and combinations of other Temp Tables
Update a Count column in Temp 4
Delete unwanted records that have no usable data from Temp 4
Insert Table 1
Insert Table 2
Insert Table 3
END OF PROC
I'm not sure how the changes I made could have caused a problem with the parameter or the result set since it was in that very first SELECT...INTO statement. And I double-checked that INTO MyTable is not commented out.
When I run it manually in SSMS, all I get are row counts (that's with NOCOUNT commented out). With NOCOUNT uncommented, I get "Command(s) Completed Successfully" with no row counts.
April 26, 2011 at 10:48 am
You know what really ticks me off?
Flipping the variable to Object, testing the task with Decimal and Float, flipping both task and variable back to Double...
And the X#*@#! package works in BIDS again. As if there was never a problem. Yet it failed 7 times before in BIDS, even when initially set to Double and Double.
EDIT: Tested it again in SSMS (Run Package) and it works there too now. All I did in addition to the above was drop & recreate the proc again (without changing the proc itself). I don't know why it failed. I don't know why it's working now. I think it's time to dunk my head in the lagoon and wait for the alligators to come visit. @sigh.
April 26, 2011 at 11:03 am
Brandie Tarvin (4/26/2011)
You know what really ticks me off?Flipping the variable to Object, testing the task with Decimal and Float, flipping both task and variable back to Double...
And the X#*@#! package works in BIDS again. As if there was never a problem. Yet it failed 7 times before in BIDS, even when initially set to Double and Double.
I was afraid it was that kind of error. I'm glad you worked it out. I've frequently had errors in SSIS where I had to put down a new instance of the same tool, set everything up exactly as it is in the old tool and delete the old tool before it'd work.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 26, 2011 at 11:07 am
Thank you both for being a sounding board. I do appreciate it.
April 27, 2011 at 8:08 am
Hey, Brandie,
Glad you worked this out.
I know one problem I sometimes have with SSIS packages is when I make a change to the code, save, but forget to rebuild the package. While I'm within BIDS executing my tasks everything is hunk-dory. But when, say, an Agent job calls the package, the bug I thought I just fixed is still in there (b/c the package hasn't been rebuilt).
Not sure if this could explain the issue you experienced but I thought I'd throw it out there.
April 27, 2011 at 8:44 am
Steve, I wish it were a rebuild issue. That I could understand. But I was running the package straight from SSMS using the Run Package wizard when the error first popped up and nothing in the package had changed. I'd imported it straight from our TFS file system which had the same version as Production (I checked to make sure).
But thanks for the suggestion. Hopefully that will help future DBAs who come across this thread.
April 27, 2011 at 8:57 am
Curiouser and curiouser...
I've had too many of those: I get the darn system to work but have no idea how or what did the trick. So you have the relief of a now functioning system but the frustration of knowing that if it acts up again, you're back at square one.
April 27, 2011 at 9:30 am
I have a sling at my desk, and a stone with the server's name on it. Next time something like this happens...
@=)
April 27, 2011 at 9:27 pm
Brandie Tarvin (4/26/2011)
You know what really ticks me off?Flipping the variable to Object, testing the task with Decimal and Float, flipping both task and variable back to Double...
And the X#*@#! package works in BIDS again. As if there was never a problem. Yet it failed 7 times before in BIDS, even when initially set to Double and Double.
EDIT: Tested it again in SSMS (Run Package) and it works there too now. All I did in addition to the above was drop & recreate the proc again (without changing the proc itself). I don't know why it failed. I don't know why it's working now. I think it's time to dunk my head in the lagoon and wait for the alligators to come visit. @sigh.
I've had this before. First time I forgot to reapply user permissions after the proc drop/create. Other times were not so simple.
However, I've seen similar issues from my olde programming days where the proc signature changed oh-so-slightly in the binary whereas the ascii text looked just fine. Given that DTSX packages are XML behind the scenes it may be an interesting academic exercise to compare the before and after versions for changes. Might turn up something, or it might not. I'm assuming your source control has a 'differences' function - you certainly wouldn't do it manually.
Steve.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply