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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-10 : 09:23:06
|
| oody writes "I am having a problem with the following code:CREATE TABLE tmpUser ( [UBR_Column] [varchar] (255) NULL , [Product] [varchar] (255) NULL ) ON [PRIMARY]insert into tmpUser(UBR_Column, Product)values ('asdfasa', '234234')select * from tmpUserIf I log onto Query Analyser as myself 'user1' and run this code, it creates the table user1.tmpUser, adds the record and then displays it.But if I put the code into a stored proc 'CreateAndInsertIntoUserTable', then execute this:Exec CreateAndInsertIntoUserTableIt Creates the table user1.tmpUser and then throws an error:Server: Msg 208, Level 16, State 1, Procedure CreateAndInsertIntoUserTable, Line 14Invalid object name 'tmpUser'.What is wrong with this?It will be used by many users so eventually there will be many table with the same name but owned by different users.i.e.user1.tmpUseruser2.tmpUseruser3.tmpUseruser4.tmpUserThe only other way I can think of is to creating Dynamic SQL which builds in the userID prefix into the statement which I am desperately trying to avoid.Any ideas as to why it does not allow me to do this insert from a stored proc?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-10 : 09:45:41
|
| 1. Temp tables must be preceded with a # character, without it it's not a temp table, and you'll get "object already exists" errors.2. When the stored procedure compiles, it checks ALL object references to ensure that tables, columns etc. exists. Since you're using the CREATE TABLE command that table doesn't exist at compile time. Changing it over to a true temp table should solve the issue. |
 |
|
|
|
|
|
|
|
INSERT INTO user owned table from SP's