|
Have you tried calling the stored procedure from an SQL client? If you have did, was the call successful?
I noticed that the stored procedure declaration does not contain @ in front of the parameter names after the procedure name, is this correct?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
If I remove @ in front of parameters, it throw an error saying no such parameter. If I keep it @ then it just inserts. I am using ibm series.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Judging by this:DB2 stored procedure examples[^] you don't need any @s so try removing them from the stored procedure and your call.
Disclaimer - I have never used DB2.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I removed from Stored procedure. It inserts the data into table but sill throws exception. But if I remove from my call in VB.NET code, it says no such parameter found and so no insertion of data.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Instead of this:
Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(@PrmCICID,@PrmCIFN,@PrmCILN,@PrmCIADDR,@PrmCICITY,@PrmCISTATE,@PrmCIZIP,@PrmCIPHONE,@PrmCIEMAIL,@PrmCICLDATE)", Cn)
Try this:
Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(?,?,?,?,?,?,?,?,?,?)", Cn)
and read this[^]
[edit]
? added
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I tried the one which you said and Now this is throwing
SQL0440 Routine QZ9AFC1C3BBACE2001 in not found with specified parameters.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
sudevsu wrote: I tried the one which you said and Now this is throwing
The one with the "?" question marks for parameters?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
with ?
I get this error
SQL0104 Token PRMCICID was not valid. Valid tokens: ) ,.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Read this[^] article - I think it should help.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Thanks Guy. I wish this should resolve ASAP. It is taking my whole lot of time. 
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
How to check calling a stored procedure from SQL Client?
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
You will need something like Toad [^]then call the stored procedure from there.
I always test stored procedures form a client before running them from .Net.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Ok Thank you. I did that now, it is perfectly ok there. But from code it goes into catch and throws same error no matter what. 
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
DB2 != SQL Server
DB2 doesn't use @ as a parameter marker, it uses ? as a positional marker or : for named markers.
But this isn't a prepared statement so just try to remove all @
Disclaimer, I'm not working with DB2 either.
|
|
|
|
|
You mean just remove all @. Do I need to add ? or something instead of @?
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Not in the procedure just remove them like this:
INSERT INTO DEVSUSH . CLCUSINF ( CICID , CIFN , CILN , CIADDR , CICITY , CISTATE , CIZIP , CIPHONE , CIEMAIL , CICLDATE )
VALUES ( PRMCICID , PRMCIFN , PRMCILN , PRMCIADDR , PRMCICITY , PRMCISTATE , PRMCIZIP , PRMCIPHONE , PRMCIEMAIL , PRMCICLDATE ) ;
But your call from vb.net to the database is a prepared statement so that should be changed to:
Try
Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(:PrmCICID,:PrmCIFN,:PrmCILN,:PrmCIADDR,:PrmCICITY,:PrmCISTATE,:PrmCIZIP,:PrmCIPHONE,:PrmCIEMAIL,:PrmCICLDATE)", Cn)
strCIPHONE = strCIPHONE.Replace("-", "")
cm.Parameters.Add("PRMCICID", iDB2DbType.iDB2Numeric).Value = Convert.ToInt32(strCICID)
cm.Parameters.Add("PrmCIFN", iDB2DbType.iDB2VarChar).Value = strCIFN
cm.Parameters.Add("PrmCILN", iDB2DbType.iDB2VarChar).Value = strCILN
cm.Parameters.Add("PrmCIADDR", iDB2DbType.iDB2VarChar).Value = strCIADDR
cm.Parameters.Add("PrmCICITY", iDB2DbType.iDB2VarChar).Value = strCICITY
cm.Parameters.Add("PrmCISTATE", iDB2DbType.iDB2VarChar).Value = strCISTATE
cm.Parameters.Add("PrmCIZIP", iDB2DbType.iDB2Numeric).Value = Convert.ToInt32(strCIZIP)
cm.Parameters.Add("PrmCIPHONE", iDB2DbType.iDB2Numeric).Value = Convert.ToInt64(strCIPHONE)
cm.Parameters.Add("PrmCIEMAIL", iDB2DbType.iDB2VarChar).Value = strCIEMAIL
cm.Parameters.Add("PrmCICLDATE", iDB2DbType.iDB2Date).Value = strCICLDATE
cm.ExecuteNonQuery()
Catch ex As Exception
LogError.LogErrorIntoTextFile(ex, "InsertCust")
End Try
Should note that the error message you're getting is not from DB2 but from VB.Net, stating that there is a mismatch in the parameters. Or rather that it can't find an overload of the procedure having the right parameters.
The reason it works is that it tries to apply the parameters in the order they've been added like as if you had used ? as a marker. Like this:
Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(?,?,?,?,?,?,?,?,?,?)", Cn)
|
|
|
|
|
A thought, have you checked that there are no triggers spit attached to the table and the error is generated from the trigger instead of your procedure (this is the reason I loathe triggers spit)?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Remember, when you fix this post back under your original post with how you fixed it 
(this can be very useful to other people who come to this forum looking for a solution to the same issue you had)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Sure. But I didn't fix this yet.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
I have googled and it seems that this is a known issue - however I can find no guaranteed fix.
A person needed their name changing so the admins changed their name in AD and all seemed to go fine.
However I noticed that the SQL Server stored procedures using system_user or user are still returning the old name from AD.
There are some posts out there on google acknowledging that the SID maintains the link between AD and SQL Server however the name in SQL Server is not updated.
I am loath to bounce the server as it is used pretty much 24/7(and apparently it is not guaranteed to work).
So I am wondering if anyone else has seen this and knows how to get the name in SQL Server to pick up the new AD name(the user logs on via a group policy rather than as themselves).
[edit]
See SUCCESS post below for information regarding fix.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 16-Jan-15 4:38am.
|
|
|
|
|
I suspect you'll need to rename the login within SQL.
Try:
ALTER LOGIN [Domain\OldUsername] WITH Name = [Domain\NewUsername];
If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks, the thing is they log in under a group i.e. sql_production_group rather than their own login.
So unfortunately there is no login associated with them outside the group level.
We have some 100+ people associated with this group and to change the group login would be too much of a pain for the admins.
[edit]
Ooh, I just had a thought! If I create a new group and associate them with that, this may force SQL Server to refresh the name associated with the SID - I will try this tomorrow.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I've seen a few people suggesting that resetting the token cache resolves the issue:
DBCC FREESYSTEMCACHE('TokenAndPermUserStore');
Another suggestion is to create and then drop a login for the old username[^].
There's also a suggestion to disable the server's local SID cache[^], but that needs a restart anyway, and could have a negative effect on performance.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I tried the create and drop user suggestion, however I did not run a query under the user. It may be that a query needs running under the user for the SID to be refreshed.
I will try the cache refresh - I didn't try that as it will apparently slow things down for a bit(I should write the technical manuals with descriptions like that).
I will try these two things tomorrow and report back on the results(can't be fagged to log in from home tonight).
Thanks
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Success!
I tried the freecache and created a user under the old name and ran a query under that old name.
I then deleted the user and all is fine now.
The system_user is picking up the new user name.
The one thing I did not do is check with the user if the SID had been refreshed overnight, before I made these changes, and whether everything was fixed before I made these changes.
Anyway thanks for you help and for replying to my call for help so quickly.
[edit]
subject amended
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 16-Jan-15 4:53am.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
