Can't update,delete or insert in sql server 2000

[gorack]

Why i can't insert, delete or update in a sql server 2000 database with an odbc connection or an ole db connection (ADO) ?

 

[tlbroadbent]

Just a guess. The login you use doesn't have permissions on the tables or database on the server. You don't give us much to go on. Please provide more details. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[gorack]

I have a data warehouse in my workstation in sql server 2000.
I installed Sql server 2000 with windows nt authentication.

I create a database and i create tables.

In sql server 2000, when i create stored procedure and i insert, delete or update table it's fine.

But when i use access with an odbc link or a vba script with an odbc or an ole db link i can't insert, delete or update.

And when i use any table from the northwind database in sql server and i put an odbc link in access, i can erase, update or insert data.

Even in vb6 with the data window i can do the same things (delete, insert or update).

Can someone help me ?

 

[SQLSister]

if the connection is not using your login and it very well may not be, then you need to check the rights on the login the connection uses. AS Terry said, it probablly doesn't have the same permissions you have.

 

[gorack]

sql sister how do i do that ? with the login ?

 

[SQLSister]

Look at the code for your connection string. What is the login specified?

Then go to ENterprise MAnager and look up the properties for that user and any roles it is assigned to within the database. Do this by right-cliking on the username, selecting properties and clicking permissions. you'll also see the roles listed on the page with the permissions button.

Compare with your permissions.

Or ask your dba to do this once you know what login you are dealing with. You'll want this person involved in any permissions changes anyway as the dba probably has a way he prefers to set up permissions (for instance if you can get way with only seting permissions to stored procedures and not base tables, this is better. It is also better to set permissionss for roles rahter than indvidual logins.)

 

[gorack]

I am de dba and programmer of the datawarehouse.

So i must know why in vb6 in the data window it work (delete, insert or update) ?

i use the same connection as in vb6 use.

 

[SQLSister]

If the connection is the same then I would think it must be some kind of network or firewall issue. If you are connecting through the web perhaps the database server is not allowing changes when the connection comes from the web vice an internal network connection. I'd get the network admin person involved in the problem at any rate.

 

[gorack]

It's surely not a network connection because the sql server 2000 is local.

Tere is no DBA who can help me ?

 

[tlbroadbent]

Do the tables you created have a unique constraint (primary key or unique index) or a timestamp column? If not, create a primary key or other unique constraint on the tables. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[topher0303]

Maybe if you define "it will not update" will help a bit. Are you getting an error? if so, please post it. or is there no error and the data is not getting updated?

Chris.