|
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.
|
|
|
|
|
Hi There,
Recently I faced an issue regarding deadlock. In the deadlock graph I found the isolation level of the Victim and participant Procedures as <b>serializable</b>. The thing is that the isolation level of the corresponding database is Default Read Committed. How the isolation level changes and will it be one of the reason for the deadlock? Kindly advice me on the same<b><i><i><i><i></i></i></i></i></b>
Regards
Mahesh
"Fall Down To Rise Up!!"
|
|
|
|
|
Documentation here[^].
Member 11373638 wrote: How the isolation level changes and will it be one of the reason for the deadlock?
A deadlock occurs when a transaction is waiting for another transaction to finish, where the latter is also waiting on the first. The victim is determined by the priority.
First part is identifying the queries that cause the lock in the first place.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy,
I did all these findings and altered the procedures. But still I am getting the deadlock. I am just struck with this Serializable isolation. Will it be also a reason for the locks?
Regards
-Mahesh
|
|
|
|
|
Member 11373638 wrote: I did all these findings
Which findings?
Member 11373638 wrote: and altered the procedures.
What did you alter?
Member 11373638 wrote: I am just struck with this Serializable isolation.
There is two queries that keep waiting on each other. Once you know which ones, you can check whether they use the default isolation. I don't see a reason why this should be "serialized" and not the default value.
Depending on WHAT the queries do, you can define an alternative. If it is a read, then you might opt for a dirty read - or to wait until the other query is finished. If it are two writes, then you'd either need a queue, or a way to specify whom the victim will be. It will never be possible to update a single field using two queries simultaneous, so there will always be a victim unless the logic changes.
TL;DR yes, but changing the level will not automatically fix it.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy,
Yeah your correct, in my case one query was doing a select and other doing a update on the same record. I changed it and thought it will work fine, but i was wrong and this time also deadlock came. And good document related to deadlocks in case of Update and Select in a same table??
One more thins as per your advice changing the isolation level wont help rite!!
Regards
-Mahesh
|
|
|
|
|
Member 11373638 wrote: in my case one query was doing a select and other doing a update on the same
record.
That's good; I was afraid you'd be doing two insert/update queries, launching them after another, each in a separate thread. For a read, there's multiple ways to handle the situation, but the server will need to be told what to do.
There are isolation levels and query-hints to get the behaviour that you'd want. It'd be a huge coincident if there is always "someone" requesting a record at the moment that the PC is writing it. It is fast in writing a record (and updating the indexes, starting triggers and.. it still takes time)
So, best is to identify why someone is always reading after that update. One cause could be that the piece of code that is updating the record, is also fetching it, immediately, simply to refresh the data on the form. In that case it should block/delay the execution until the update-command returns. Even better; don't do the select at all, but refresh using the values that you already have.
Is it the same PC/terminal that executes the select after the initial update? You could verify quickly (assuming that the problem is repeatable, is it?) using a trace[^].
Member 11373638 wrote: And good document related to deadlocks in case of Update and Select in a same
table??
I favor MSDN for documentation; besides the isolation levels, you'd also need to see the queries that get executed to determine the current and the desired behaviour. The might already contain or need additional hints[^]. The existing query might already contain a bad idea[^]. And there's a page in the ADO.NET section that may be interesting, even though we are only looking at the database. It's worth the read[^]; it mentions a third possibility - if both statements actually belong together, then they should be wrapped in a transaction. (Could be the case if there's a calculated field in there)
That's a lot of documentation to wade through and probably a lot of code to check. Best idea is to start with the bad idea link 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy,
Thanks for your useful information. Both the queries are executed through the same terminal. As per your suggestion I will try to use the hints and lets see whether it really helpful. 
Regards
-Mahesh
|
|
|
|
|
You're welcome 
Out of curiousity, was it caused by the bad idea?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
We're using Entity Framework 4, ASP.NET MVC 4 and SQL Server 2008 R2. We have some join tables for several entities, for many-many relationships. I'm working to create audit tables/data for each of these relationships/join tables. So far in my research, I've found that triggers are common. We use Windows Authentication, and we have a User table that stores authorized domain users. For our audit tables, we'd like to know which user modified the relationship.
The problem I have is, how do we get the ID from our User table based on the user who updated a relationship using our MVC application? I've looked at some options, and the only thing I can come up with so far, in a trigger, is this:
SELECT @username = nt_username FROM sys.sysprocesses WHERE spid = @@SPID
That will retrieve my username without the domain, which is what I want. I can then compare it to our User table and get the ID where the username matches. But is this the best way to go about it?
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
Matt U. wrote: But is this the best way to go about it?
Compared to which other way?
I like triggers; it'll do what it is supposed to do without having to do anything special in the calling code.
The nt_username[^] field from the current process will always be the Windows user name, so that would look correctly too. IIRC then the variable "SYSTEM_USER" should return the same.
There's two or three alternative approaches that come to mind, each of them not as good as triggers.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks for the info! I've been working to put it together using triggers and I can't imagine any way that would be simpler. I think I'll just go that route. I only asked because I'm not entirely familiar with triggers, setting up audit tables, and so on.
djj55: Nice but may have a permission problem
Pete O'Hanlon: He has my permission to run it.
|
|
|
|
|
You're welcome
|
|
|
|
|
As much as I loathe triggers spit audit is the one area where they are the right tool for the job.
We have a Modified and ModifiedBy field on EVERY table and use that in the trigger to identify the culprit. We also do not use EF or windows authentication, our apps use app specific credentials to connect to the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
