Tell Me (and Others)

I was doing some extensive software testing in a program that talks to a MS-SQL2005 database. I detected that a table INSERT/UPDATE Trigger was seemingly not firing.

I scrutinised the trigger code and could see nothing wrong, and know that it has been functioning well for several years in other applications, using both MSSQL2000 and MSSQL2005.

So what was different in this program? I searched the web and did not find an answer.

I added some code to a test program to work around the issue. That is when I started catching this error message:

Here I was trying to CALL a stored procedure to update some balances. Again, the stored procedure has been working great for many years with no change. So, what was this message all about? Back to the web again and still no resolution.

That is when I dug deeper and added some more tests into a couple of other test programs I wrote, each of which tried different approaches to see what was really happening.

Strangely, some tested OK with no errors, and others threw the same error! So, what was the difference?

The Answer

The original error it seems is caught by the SQL Native Client ( or perhaps Microsoft Data Access Components (MDAC) ) and translated for the ODBC file drivers I was using. A translation (or miss-translation) is performed and a meaningless message is captured by the ODBC based drivers and passed back to my program as “Executing SQL directly; no cursor”.

Comparing the test programs I was working with pointed me to the answer. It was all to do with permissions! Nothing about “Executing SQL directly; no cursor”.

I found in some programs where I was using Application Role permissions to access the backend SQL, that the Application Role I was using did not have EXECUTE permission on the stored procedure.

Back into MS SQL Server Management Studio I went, and ran this command:

GRANT EXECUTE ON [dbo].[jg_AllBalances11] TO [LMGaus]

Now all my test programs began behaving as expected.

Also, my TRIGGER began working. The trigger was calling the stored procedure to do some of its work, and so now it too performed as expected.

Next time you see the

Executing SQL directly; no cursor

error, check all your permissions!

Hope this helps someone.

John Griffiths

Recently I was invited to join a group of volunteers working on a software development project. As there were to be more than two in the group, I could see problems working through a swarm of emails, cc: emails and all the replies…

That is when I started researching web based Project Management Software.

As the project was to be a low-cost exercise, I think I have found the perfect answer.

We needed three components:-

A domain name ($10 p.a.)

A Site Host ($72) But prices can range from almost free to whatever you want.

Project Managment Software ( Free )

The Site Host:

=============

If you shop around, you can save on the Site Host account. Or even find a free one, but that is not recommended. You may already have a host that will allow an add-on domain at no extra cost. And, make sure that any host offers CPanel access, and not a  management tool they built themselves!

Project Managment Software:

==========================

The site host we selected provides dotProject and PHProjekt system installs via the Fantastico within cPanel.

We went with dotProject

I am truly amazed at the features and functionality in this free software product. You really need to try it to see what it will do.

It is PHP driven and uses a MySQL database for content storage.

I had a quick look at PHProjekt and it too looks great, but we had to choose one so we went with dotProject.

Review of Acronis Migrate Easy

Link: Acronis

OK, we have been battling for several months now with a 4 year old PC (Windows XP) that my Wife uses. The 60GIG hard drive was close to capacity. It was so close that we could not even perform a Defrag!

I had attached 250GIG pocket USB drive and had directed most internet downloads to go to the external USB drive.

She is heavily involved in her love of movies and so does voluntary work for FilmSpotting and their podcast productions. (plus her Blog stuff at MagneticNorth )
This involves downloading large video and sound clips of films. Hence the large accumulation of data on her hard drive.

I knew that the hard drive would need replacing. I dreaded managing getting all her programs and MS-Win-XP onto a new bigger hard drive. Then the crunch came when downloads and FireFox and many other programs began to complain about the HD free space.

Then I learned about Acronis Migrate Easy. I read on-line what was promised. I read several user comments. I believed none of them because of my long term relationship with MS-Windows and programming. But Hey! It is only a PC where I am not doing real development work. Something needs to be done, and it needs doing now. So why not take a small chance and try it out.

The Process:

Downloaded the 15 day trial software, installed it (only just, given the space limitations on the existing HD). Went out and bought a new 500Gig WD hard drive and set it to “Slave”, Installed it, and then ran the Acronis program. I opted for the ‘Automatic’ process (I think the second on the menu) and reboot, watch, walk-away, come back in 50 minutes, and it reported success and was done.

Then I removed the origingal HD, re-configured the new 500Gig HD as IDE Primary (using the jumper block on the back of the HD unit).

Feeling 100% confident, I put all the panels back on the PC case, plugged all the cables back in, and turned it on.

VOILA! All worked perfectly and now we have a PC with enough spare space for several more years! I had been ready with the original Win XP Install CD and activation key, but neither were needed.

Summary:

If you need or want to substitute a newer larger HD to a PC, then go with Acronis. I give it 10/10 for meeting expectations.

John

OK! So you spend all day developing, tweaking a software solution for a customer. Then you find out the actual Windows environment that they are running under… This happened to me recently..

Client wanted/needed a change… In fact, they were having problems with .TPS file on a normally reliable network. They are based about 3000 miles away!

Read more…

The subject error message shows up sometimes when working with Clarion and SQL based data.

When working on a recent conversion project I received the message when testing. I had moved a database from .TPS files to a MS-SQL Server database.

I had seen this message many time before, when getting started with SQL and dealing with DateTime data fields. So, I knew exactly where to look this time. Looking through the table in question, I noticed that I had no DateTime fields in the table. So something else was causing the warning “Record Was Changed By Another Station”. But, as there were no other “Stations” active, I was now in search of the source of the message.

First thing I checked was the order of the fields in my Dictionary, and the order of the fields in the database. This should not cause the error, but who knows… This all checked out OK.

Next I checked the data types were aligned and this too was all OK.

That is when I noticed that I had a STRING field in my dictionary as STRING(12) and in the database it was declared as CHAR(16). Yep, I had deliberately made it wider when I designed the new table but I had forgotten to adjust my Dictionary. Once I adjusted my dictionary to match the database, and re-compiled. all worked as it should.

But why would it report the “Record Changed By Another Station” warning, and not some more relevant message? Probably because the Clarion SQL file driver was doing its job correctly, and comparing my initially saved file buffer (with the 12 bytes I hade in my string in my dictionary). Then, just as I go to save my changed to the database, the SQL File Driver re-reads the data from the database, this time not using my data declarations, but the data as defined in the SQL table. It then does a byte-by-byte compare with the saved buffer. They do no match, so the warning message is displayed.

Bottom line: Make sure your Clarion dictionary declarations match the database fields.

John.

Yesterday I received a support call to say that my program was not working. Great, I thought… that tells me a lot!

Finally the user told me which screen he was on and what the message was displayed. It was “Cannot Connect to Database”. This is a program with a MS-SQL 2000 database. Other programs connecting to different databases were working fine, so the Server was running and they could connect OK.

I managed to get hold of the on-site office manager by phone. I managed to get him to re-locate to the server room, and start the Enterprise Manager. He had no SQL skills and had never used any SQL tools before yesterday. After coaching him for what seemed like an eternity, I was able to have him work in the left-hand pane, and navigate the tree structure there in a half useful manner.

At first attempt, we found a SQL Server Group, and under that there were two SQL Server instances running. He reported the first had no databases, and apart from the master model temp etc.. the second had a few that he half recognised. Things were not looking good, I thought.

It looked like I would need to drop by in person.

I got some stuff ready and phoned him again before I dropped by. I had him go back to the server and get me a few more details. In the meantime, I think he had practiced navigating a windows directory tree.. Now he was able to find all my other databases showing under the 1st listed server. Why he could not see these an hour earlier was a mystery.

Now the problem was evident.. The database in question was flagged “Suspect” within MS-SQL Enterprise Manager. So I did a few on-line searches and found several suggested “fixes”. The one that I had on-site manager try was the one involving sp_resetstatus dbnamehere

That did not work!

Then I read that a critical step was to review the SQL log to see what reason shows up there for the “Suspect” database.

On-site this morning, and I hunted down the appropriate log and quickly found the problem.

The data .MDF file was not where MS-SQL expected to find it. Some moron had moved it!

The Fix:

I got permission to STOP the SQL Server, moved the file to the correct folder, and re-started the SQL Server.

Voila… All working again

John Griffiths

I have posted several hints for working with MS-SQL2005 and get many requests for more.

If you are working with SQL-2005 Express or Standard, and have a hint you would like published, then please let me know and I will add it to the site.

The site I post to is www.sqlkey.com/

John Griffiths

I have seen references to RSS feeds on several sites I visit but had never grasped the concept.

I had even read all about them on Wikipedia, and had been no better informed of the functionality as it relates to me and what I do!

Tonight, my Wife, an non-programmer and who often calls for my help in matters “computer”, started a blog and suggested I add her “RSS” feed from her blog to my Firefox browser.

My response was, OK, I know that there is such a thing as RSS Feeds, as I am constantly offered to avail myself of them, but, hey, I have got by for over 60 years without one!

So, next step, with her standing at my desk, was to go to her new blog site, navigate as per her instructions, and voila, now I have an RSS Feed from her site to by browser.

What has happened here, is a practical demonstration of “how” something works, that I had never been able to grab from reading descriptions. Maybe I had never grabbed the concept as it was something I was locking out! Perhaps so!

But now I do have a live RSS feed from “Magnetic North”

Live and Learn.

John