First of all, I am using ColdFusion MX 7 and SQL Server 2000.
My site uses CF client vars and I want to store them in a database, via a normal CF datasource. I know that this is the best way to store them. I know that it's bad bad bad to store them in the registry ( I DON'T know how bad it is to store them in cookies, which is what I'm doing until this problem is fixed). I also know that within the database, client variables are stored in the special tables CDATA and CGLOBAL.
I know that the CF administrator is supposed to be able to create these tables automatically, given a fresh clean database and provided that you have certain driver(s ?).
So I went in to make my datasource for client storage. First of all, the CF administrator could not create my tables for me. I don't know why, the error it gave was very general, just --cannot create client tables on this datasource. OK, so I figured I didn't have the right drivers and I tried to make the tables manually.
This proved really hard. Adobe has VERY general instructions for how to set up these tables here:
I guess they have to be so general because the tables have to function in Access, MS SQL, Oracle, etc. ... however, I was pretty shocked when I couldn't find ANY specific script by ANYONE about how to set up these tables in SQL Server 2000. I would think this would be a very common task. (The only scripts out there are about how to set up CDATA and CGLOBAL in Access!)
So, I did my best. I followed Adobe's instructions. I tried many different configurations, variations on what Adobe said to do. The latest one I have is in the two images below, but tryst me, I tried everything. But still... every time I try to use a client variable that has been configured to use the datasource, I get this error:
"500 Operation failed on the data source named "my_company" "
I don't know what this is. It isn't a CF error. It looks like a SQL error to me, but I can't find a description of what error 500 is in SQL anywhere. For further clues, the title of this page is "JRun Servlet Error". Not sure what that has to do with SQL.
The final thing: if I try to access the CDATA or CGLOBAL tables directly with a <cfquery>, there's no problem! I can select, insert, etc. So It's not a permissions issue. It only breaks on client variables.
In any case, it seems that everyone else has had a very easy time setting up the CDATA and CGLOBAL tables. So I hope someone can help me have an easy time too!