Avatar of masterorb
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/Software/Server_Software/Web_Servers/ColdFusion/Q_24091063.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
Web ServersMicrosoft SQL ServerColdFusion Language

Avatar of undefined
Last Comment
masterorb

8/22/2022 - Mon
SidFishes

first guess would be that cfadmin doesn't have permissions to the new db
ASKER CERTIFIED SOLUTION
Jones911

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
masterorb

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
ASKER
masterorb

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).
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
masterorb

THANK YOU!