DDL via ExecuteNonQuery()

[timexsinclair2068]

I'm trying to run an ExecuteNonQuery("CREATE TABLE...") and I get a
-1. I'm using SQL Server 2000 and this has been happening since I
installed VS2005 (which also installed SQL Server 2005).

I'm starting to think it has to do with pemissions. Maybe SQL Server
2005 messed up with SQL Server 2000's permissions? I don't believe
it's possible, but I cannot think of any other alternative.

The thing is that this is not happening when I execute the same DDL
(i.e. CREATE Table...) in the Enterprise Manager or the Query
Analyzer. It only happenes via ADO.NET.

Any ideas?

Thanks!
Paul

 

[William \(Bill\) Vaughn]

Are you using the same credentials for both? What about the initial catalog?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[timexsinclair2068]

Thanks for your help Bill.

Yes. I am using "SQL Server and Windows" authentication for both. The
funny thing is I can execute DML queries, but not DDL.

 

[William \(Bill\) Vaughn]

Sure sounds like a rights issue to me. OS? Vista requires different user
rights.
Dig into SSMS and see what rights are assigned to the account being used
from ADO.NET.
Turn on the profiler to see what credentials are being passed.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[timexsinclair2068]

I'm using XP Pro SP2.

The account being used from ADO.NET is the same one I use from the
Enterprise Manager (SQL Server 2000 doesn't use SSMS). So, in theory,
it shouldn't be a credentials problem. I guess?

 

[Patrice]

Don't you have an error message to show us (or does your app hides them ?).

 

[timexsinclair2068]

Thanks Patrice.

I found out what the problem was. ExecuteNonQuery is returning -1 if a
DDL succeeds but also -1 if there's a rollback -no exception raised,
so no message. Then I had no way to discern if it succeeded or it
rolled-back.

I'm now not relying on returned values, but on exceptions raised.

Thank you all for your replies!