asked
(S0002/208) Invalid object name insert into tablename
Hi I am getting following error from My clients My Server is using SQL 2008 R2. We have recently migrated from SQL 2005. Database Compatiblity is SQL 2008 R2 Level 100. We Recently change Compatiblity level from SQL 2000 to SQL 2008 R2 When I have Tried to Insert Records from JDBC
Drivers.It is an automated Call. I’m getting stranger errors inserting data to the orders table. [ADA.1.316] Cannot execute the SQL statement "INSERT INTO dbo.StudentMaster(Registerno, StudentName,Department,Age,Year,JoinDate,Semester) VALUES (?, ?, ?, ?, ?, ?, ?)". " (S0002/208) Invalid object name 'Studentsdb.dbo.StudentDetail'." Invalid object name 'Studentsdb.dbo.StudentDetail'. The process is a an automated one using a dedicated JDBC connector. I can’t see the actual SQL insert statement that has the correct values. The ? question marks are just placeholders indicating the data files that should be filled. If the drivers I have are not compatible, then we will have to retest the interface as I can’t introduce new drivers without regression testing all the other JDBC connections in the middleware. How to fix this Error? May I need upgrade on JDBC drivers or to use proper databasename.schemaname.tablename?
It looks as though there are line breaks between the database.schema. and the tablename. Is that an artefact of your copy & paste, or what's actually causing the error?
does Studentsdb.dbo.StudentDetail exist? Your query isn't directly referencing it, is there a trigger?
Trigger will be fired as you are inserting into StudentMaster. What does the procedure Studentsdb.[dbo].[ProcessStudent] do?
yes Studentsdb.dbo.StudentDetail is existing in DB. StudentMaster table have type of After/FOR trigger Trigger would not be initiated because there is no records inserted on Studentmaster table. Trigger would be fire after records inserted on Studentmaster table bcos it is a For/after type of trigger May I correct?
OK so now we have at least identified how this object is being referenced from the initial INSERT. I would have thought that any user permissions issues would have given a different error message, but I'm also aware that you have just migrated from SQL 2005. So I'm guessing here but maybe the security context is different - can you check what account is the database owner select suser_name(owner_sid) from sys.databases where name = 'Studentdb' if this is not 'sa' or some other non-user account, then maybe try and set the owner to 'sa' exec sp_changedbowner 'sa' Also...I can't let this go with out saying...... your trigger is dangerously flawed. You assume that only one row will be inserted, and then execute the stored procedure ProcessStudent. If you insert 10 rows, I expect you want that procedure to be executed 10 times? It won't - it will run once. If you run an insert of multiple rows, then what is the result of Select @Regid = Registerno from Inserted It may be the first value, the last value, or any in between - in other words it's not what you think. Now you may say that you only ever insert one row at a time, but that's not how SQL works - and you should code defensively because of it.
Sorry my mistake should have said select suser_sname(owner_sid) from sys.databases where name = 'Studentdb'
try `select owner_sid from sys.databases where name = 'Studentdb'`
18 People are following this question.
