question

ashok2012 avatar image
ashok2012 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?
t-sqlinsertsql 2008r2
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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?
1 Like 1 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
does Studentsdb.dbo.StudentDetail exist? Your query isn't directly referencing it, is there a trigger?
1 Like 1 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Trigger will be fired as you are inserting into StudentMaster. What does the procedure Studentsdb.[dbo].[ProcessStudent] do?
1 Like 1 ·
ashok2012 avatar image ashok2012 commented ·
Hi Yes there was wrong with copy and paste. Let me correct it. It was correct in client has given text.
0 Likes 0 ·
ashok2012 avatar image ashok2012 commented ·
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?
0 Likes 0 ·
Show more comments

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Sorry my mistake should have said select suser_sname(owner_sid) from sys.databases where name = 'Studentdb'
2 Likes 2 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
try `select owner_sid from sys.databases where name = 'Studentdb'`
1 Like 1 ·
ashok2012 avatar image ashok2012 commented ·
When I checked select suser_name(owner_sid) from sys.databases where name = 'Studentdb' it was NULL It means?
0 Likes 0 ·
ashok2012 avatar image ashok2012 commented ·
When I checked I have got below as result 0x010500000000000515000000EF7AD6B2DE76062B75B3A20B53040000
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.