Any SQL pros?

[chris1911]

We just switched over to SQL from VFP. I didn't have anything to do with the process and I really don't know too much about SQL. I hate databases to begin with to be completely honest.

We have a piece of software on a networked machine that needs to access a table in the database. When I set up the DSN everything goes smoothly and it says it's able to connect to the database. After doing this though, the software tries to access the table and says that table doesn't exist. I've tried setting the user permissions to Grant for every category (security isn't really a concern) to no avail. What the hell is my problem???

[bigbearbear]

I'm not a pro with SQL, and its been a while since I wrote an app that uses it, but I recall that most SQL database has a tool that lets you login and issue SQL commands, and it'll return the result.

I'll recommend using that tool and issue the same SQL command your application is using, and see if it is returning the database table. If not, there's probably an error in the SQL statement used.

[gawker]

Point a SQL Query tool at the database or even Access to see if it can really see the database structure.

[chris1911]

I'm not a pro with SQL, and its been a while since I wrote an app that uses it, but I recall that most SQL database has a tool that lets you login and issue SQL commands, and it'll return the result.

I'll recommend using that tool and issue the same SQL command your application is using, and see if it is returning the database table. If not, there's probably an error in the SQL statement used.

I'm not even sure what command it's using. It's software developed by a 3rd party for shipping and basically it calls up the table by a unique ID number and imports the shipping address. Once done it writes back a tracking number.

I can't even get it to issue a command because it checks to see if the table is found during the setup and wont complete the setup of the application without being able to access that table. I really hate dealing with databases, I REALLY hate it, I'm more of a network guy. I'll try the SQL query tool.

[Hoshnasi]

I've tried setting the user permissions to Grant for every category (security isn't really a concern) to no avail. What the hell is my problem???

Did you make sure to set those permissions on the user that is accessing the DB through the app? Is this Linux or Windows?

[speedrrracer]

I'm guessing it's not the user you think it is

[chris1911]

I'm guessing it's not the user you think it is

There's only one user besides sa. I've even tried using sa when setting up the DSN and it can't find the table. I can go into the management console and see the table there.

[Kodemonkey]

Are you using a system DSN or user DSN? If you are using microsoft SQL, are you using a domain or a workgroup? It's been awhile, but I remember that AD would wreck havoc with user DSNs.

As other's have said, use iSQLw and see if you can send commands to the database. SQL permissions can get pretty granular, it's easy to get in a fight with them. You might want to try making a new user and assigning top level rights to the tables you need to access as well instead of using sa. Just to make sure there isn't a problem with the sa account permissions.

[Kodemonkey]

I'm guessing it's not the user you think it is

He may be right...

During the setup process, the install may be running under system or something else if UAC is running. If UAC is running, disable it if your system policy will let you.

[chris1911]

Are you using a system DSN or user DSN? If you are using microsoft SQL, are you using a domain or a workgroup? It's been awhile, but I remember that AD would wreck havoc with user DSNs.

As other's have said, use iSQLw and see if you can send commands to the database. SQL permissions can get pretty granular, it's easy to get in a fight with them. You might want to try making a new user and assigning top level rights to the tables you need to access as well instead of using sa. Just to make sure there isn't a problem with the sa account permissions.

We are using a system DSN in a workgroup. I'll try a new user and see what happens. The user we're using my boss created today but he had some guy from another company remoting in to do it and I can tell this guy probably knew less about SQL than I do and I'm the first to admit I barely know anything about it.

[SacJDog]

Is the database server hosting multiple databases? My guess is you are referencing the master database and not the correct one.

[chris1911]

Only hosting one database and definitely selecting the correct one in the program setup.

[Kodemonkey]

We are using a system DSN in a workgroup. I'll try a new user and see what happens. The user we're using my boss created today but he had some guy from another company remoting in to do it and I can tell this guy probably knew less about SQL than I do and I'm the first to admit I barely know anything about it.

Okay, I don't have a ton of experience on workgroups. Have you tried creating a user with the same name on the SQL server as the client and give that user admin priveleges on the server? Passwords need to match obviously. Just a shot in the dark. MSQL will dovetail itself into active directory but I don't know what it does with workgroups to be honest.

ETA: I mean not a user in the database, but a user on the OS. Also, you can apply permissions to a database, but depending on the SQL server you have sometimes the tables don't get their permissions set as a child of the database. Make sure your permissions are set on all the tables, indexes, and stored procedures if there are any.

[SacJDog]

I'm guessing this is SQL Server and if so do you have other instance of SQL server loaded. Like SQL2005, SQL2008, SQL2008R2, SQLExpress. And if you have SQL Management studio, connect in with the non-sa credentials and see if the table exists.

[chris1911]

I'm guessing this is SQL Server and if so do you have other instance of SQL server loaded. Like SQL2005, SQL2008, SQL2008R2, SQLExpress. And if you have SQL Management studio, connect in with the non-sa credentials and see if the table exists.

Yeah that's how I've been verifying it was there and that the login works is by going into Management Studio.

I tried creating a new SQL user and made them sysadmin and dbadmin, then added them to the database, then went to the table and granted permission for every option. It still was able to connect to the database but not see the table. I'm going to work on it more tomorrow. It's really irritating because my boss wants me to guide him by watching over his shoulder but he is REALLY slow and while I'm not good at SQL he is painfully worse.