January 27, 2014 at 1:11 pm
This effects both SQL Server 2012 and SQL Server 2008 R2, but I'm posting this here.
Some years ago, when .NET 3.5 and Visual Studio 2008 was the hot kid on the block, I wrote a Windows Communication Foundation (WCF) service to read and write data to a database. I've got another routine that will read the data from a local SQL 2008 R2 Express database, call the WCF service and now store it into a SQL 2012 database on the backend. This is working half of the time, but the other half of the time it isn't saving data at all in 8 fields. All 8 fields are nullable, all are tinyints. I'm trying to find any pattern at all that would be causing this, and the only thing I can think of is those records that are saved are (almost) exclusively values other than 0. The users are telling me that in some of the fields they'll enter something and try to save it, but it doesn't save all of the data. However, it's clearly saving it for half of them, at least half of the time. The fields don't have a default value specified. The data is always saved in the local SQL Express database, even when it's not saved in the backend database. The backend database is running in a VM off-site.
What could be causing this to occur?
Kindest Regards, Rod Connect with me on LinkedIn.
January 27, 2014 at 1:37 pm
I'd start with running a serverside trace on the 2012 server to determine if the commands are even getting there.
If the commands are reaching the server, then it's something we could deal with at the SQL level.
My guess is they're not, and they're being lost in the network. That's a host of other issues, where you'll be wiresharking the entire route to see where it gets dropped.
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
January 27, 2014 at 1:43 pm
In addition to what was said already:
1) You may want to add some logging to your code, on both ends, to use for analytics of what was prepped/sent and what was received/attempted-to-insert.
2) Did you check for constraints, unique indexes, other stuff that would cause data to be rejected when it hits the final table?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 27, 2014 at 1:55 pm
Evil Kraig F (1/27/2014)
I'd start with running a serverside trace on the 2012 server to determine if the commands are even getting there.If the commands are reaching the server, then it's something we could deal with at the SQL level.
My guess is they're not, and they're being lost in the network. That's a host of other issues, where you'll be wiresharking the entire route to see where it gets dropped.
Hi Craig,
I forgot to mention that I had already run a trace against it. The only thing it revealed was this:
exec sp_executesql N'SELECT ClientNumber, CaseNumber, Followup, DaysADLProbLast30, TroubledByADLProb, ImportOfADLProb, RateNeedADLTx, ClientMisrepADL,
ClientMisunderstandADL, MakeFriends, KeepFriends, ResolveConflict, KnowOthersFeel, TellOwnFeelings, AskForNeeds, LikesSelf, LikedByOthers,
TroubledByInterpProb, ImportOfInterpTx, RateNeedInterpTx, ClientMisrepInterp, ClientMisunderstandInterp, AdditionalComments
FROM ASIADLInterpersonal
WHERE (ClientNumber = @Param1) AND (CaseNumber = @Param2)',N'@Param1 int,@Param2 tinyint',@Param1=151905,@Param2=1
The table involved with the relevant data, is the ASIADLInterpersonal table. There's no insert or update going on here.
Kindest Regards, Rod Connect with me on LinkedIn.
January 27, 2014 at 2:26 pm
OK, I think I've found out what the problem was. There wasn't a call to the save routine, in the user control that hosted those 8 data elements. I've put the same into that save routine and I believe that will fix the issue. If it doesn't, I'll be back to post more.
Kindest Regards, Rod Connect with me on LinkedIn.
January 27, 2014 at 2:28 pm
I hate to say it, then, but you've basically answered your own question.
The command's not getting to the server, and the service you're using to send it over isn't getting an error from the transmission. I've seen this before with timeouts, and the network just never responds to the sender.
You'll need to get networking involved. Perhaps someone around here has more knowledge of dealing with this, but I usually lean heavily on my infrastructure guys once issues like this start.
Ninja'd! 🙂 ... Or the code could just be bad... yeah, that's it... :w00t:
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
January 27, 2014 at 4:26 pm
Rod at work (1/27/2014)
OK, I think I've found out what the problem was. There wasn't a call to the save routine, in the user control that hosted those 8 data elements. I've put the same into that save routine and I believe that will fix the issue. If it doesn't, I'll be back to post more.
Hmm, guess that makes it a PICNIC - Problem In Chair, Not In Computer!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 28, 2014 at 7:46 am
Craig, it was your suggestion of running a server side trace that put me onto the possibility that the code had left something out. All I saw was the SELECT out of the table. I used ADO.NET objects and would have called the UpdateChanges() method (I think that's what it's called), which I suppose does an UPDATE statement somewhere/somehow behind the scenes. Since I didn't see it I decided to take another look at the code. I'm glad you made that suggestion, thanks.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply