masterorb
asked on
Cannot set up CDATA and CGLOBAL tables to store client vars on MS SQL Server 2000
Hello, this problem may be related to another problem I have with ColdFusion client variables simply not showing up in IE ( https://www.experts-exchange.com/questions/24091063/client-variables-will-not-work-in-ColdFusion-on-IE.html ).
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:
http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=00001707.htm
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!
Thanks,
Ned
CFDATA.bmp
CGLOBAL.bmp
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:
http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=00001707.htm
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!
Thanks,
Ned
CFDATA.bmp
CGLOBAL.bmp
SidFishes
first guess would be that cfadmin doesn't have permissions to the new db
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow! Thanks Jones, that fixed it right up! I set up the user as db_owner to create the tables and took away those privileges once they were made.
I am still having this problem where client vars don't work in IE at all, so that must be a different thing, but I really thank you for this.
datasource.bmp
I am still having this problem where client vars don't work in IE at all, so that must be a different thing, but I really thank you for this.
datasource.bmp
ASKER
That image above was the wrong image, from the CF Administrator -- didn't mean to post it. Of course those privileges were turned on as well (I had those from the beginning).
ASKER
THANK YOU!