Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
andreliem
Starting Member
8 Posts |
 Posted - 2006-05-26 : 17:57:55
|
| I'm trying to create a very simple stored procedure which adds a row to a table. For some odd reason the stored procedure keeps complaining by throwing this error:-----------------------------------------------SQLException: Invalid Object name 'Listserves'.-----------------------------------------------The reason this is weird is because I recreated the stored procedure to "testsp" and this worked. If I call it anything else (well a bunch of other regular names) like "Listserves_Create" it throws the error. Then when I deleted "testsp" and recreated it, it didn't work. Something tells me this problem is related to SQL Server itself?One thing I did notice is that the "Syntax Check" isn't working properly on this database instance. For example, if I put a incorrect column name for my INSERT sql it won't complain. But if I do the same thing in a database instance that is running fine it will complain with this standard message:----------------------------------------Microsoft SQL-DMO (ODBC SQLState:42S22)Error 207: Invalid Column name 'asdasdas'.----------------------------------------So something tells me that the stored procedures I just started creating are not linking properly to the database tables... really odd.Any ideas? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-26 : 21:00:58
|
| This sounds like it might be to do with the user.That would account for the insert isssue - in this database it can't find the table as it isn't owned by the same user and so doesn't throw the column error - would throw an error that it can't find the table when run.Not sute about not finding the sp but check the owner when you create the sp.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
andreliem
Starting Member
8 Posts |
Posted - 2006-05-29 : 12:45:23
|
| That's what I was thinking could be the underlying problem (something to do with permission user issues).In this case, the owner is the standard "dbo" while the web application that is calling the server resides on my local computer "localhost". I have given my local computer the proper permissions to insert rows and it works with read select calls, so I'm still a but stumped.I think this is a server setup issue, perhaps my connection to the server changed unknowingly and the credentials changed too... I may try running the web app on the server instead. If you have any more insight into this that would be great.Many thanks for the help... |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-05-29 : 14:20:09
|
| can you paste the suspected code?May the Almighty God bless us all! |
|
|
|
andreliem
Starting Member
8 Posts |
Posted - 2006-05-29 : 14:56:28
|
Here's a stored procedure that doesn't work:----------------------CREATE PROCEDURE Listserves_Create @Name varchar(50),@Description textASINSERT INTO test ("Name", "Description") VALUES ('1234', '5678')GO----------------------Here's what I can deduce so far:1-It's not the syntax (at least I think).2-INSERT is the only type that causes problem. Doesn't matter if the table is new or an existing one, new INSERT SProcs won't find the tables.3-SELECT, DELETE, UPDATE all work fine.4-Old Stored Procedures work fine, it's just the new ones since last week that don't work. (Server guy told me nothing changed)5-It's not specific to any database instance. New databases and old existing databases now have the same problem. 6-"Check Syntax" command from SQL Enterprise Manager doesn't seem to detect tables anymore. I can put gibberish names like "SELECT ASDLASKJD FROM ADS" for tables/fields that don't exist and it won't complain.quote:
Originally posted by cmdr_skywalker
can you paste the suspected code?May the Almighty God bless us all!
|
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-29 : 20:17:26
|
| Could it be that quoted identifiers has been set off?tryINSERT INTO test (Name, Description) VALUES ('1234', '5678')orINSERT INTO test ([Name], [Description]) VALUES ('1234', '5678')==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
andreliem
Starting Member
8 Posts |
Posted - 2006-05-30 : 12:13:56
|
Using the square brackets / removing the quotes for identifiers worked! Thanks a million...Still, what I don't understand is why was it that before last week I could get away without them? Now it seems mandatory? Seems a bit odd that it just happened. The only change that appeared around the time this bug occurred was a microsoft trojan malicious s/w update. Also, why would this not affect Update queries? I'm not a DBA so that probably explains a lot of my confusion, but being a programmer I always exepct consistent behaviour with rules/syntax. quote:
Originally posted by nr
Could it be that quoted identifiers has been set off?tryINSERT INTO test (Name, Description) VALUES ('1234', '5678')orINSERT INTO test ([Name], [Description]) VALUES ('1234', '5678')==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
|
|
|
|
|
Weird Stored Procedure problem (naming problem?)