problem while inserting data into a table using stored procedure in master database
-
Hi,
I have created two stored procedures in master database. The first stored procedure creates the tables and the second one inserts the data into the tables. When I am calling these stored procedures from one of the databases, the first one is working fine. But when i am running the second one it is giving the error "Invalid Object Name". It is checking the table inside of master database instead of the database from which I am calling. How could I force the stored procedure so that it will look for that table from the database that I am calling instead of the master database?
Thanks,
Sridhar.
-
You can fully qualify the name of the table in the insert statement. For example:
You got: Insert into dbo.tablename
Change it to: Insert into databasename.dbo.tablename
A.J.
DBA with an attitude -
In that case I need to pass database name and execute the insert statement using Dynamic sql which is not a better option. Is that right? Are there any other options?
Thanks,
Sridhar.
-
Sridhar - could you please provide more information...
1) post the 2 procedures that you're using.
2) you say the first one works - if you use QA to connect to the database in which the tables are supposed to have been created, do you see them ?!
3) if you're using the first stored procedure to create the tables, seems that you're using dynamic sql already..
4) what's the purpose of these tables that you're creating and where does the data come from for these tables ?!
**ASCII stupid question, get a stupid ANSI !!!** -
HI...
Is ur two SPs are belonging to same DB where u trying to call them together if no then u have to qualify (for calling purpose) them with DB name then owner name then Proc name....while creating table u also take care of these thing i.e DB name,Owner name...
Regards,
Papillon
-
Hi,
For the first stored procedure I am not using dynamic sql. I have created two stored procedures named sp_CreateTable and sp_InsertTable in master database. The code is
create procedure dbo.sp_CreateTable
AS
if object_id('dbo.Table1') is null
Create table dbo.Table1
(
Col1 int,
Col2 varchar(100)
)
go
create procedure dbo.sp_InsertData
as
Insert into dbo.Table1(col1, col2)
values(1, 'A')
Insert into dbo.Table1(col1, col2)
values(2, 'B')
I am calling these two stored procedures from database DB1. When I ran the first stored procedure from DB1 it actually created the tables in DB1. But when I am running the second one it is giving the error "Invalid object name dbo.Table1". If there is no solution then I could use dynamic sql but that would be my last option.
Thanks,
Sridhar.
-
As people above have pointed out - you should always use fully qualified names as best practice anyway...
"dbName.dbo.tblName"
Having said that, I don't understand why you're creating tables through stored procedures and inserting data using procedures - unless this is just the first step towards something and the insert data are eventually going to be input parameters passed to the procedure ?!
Also - this may be just me, but I wouldn't name my procedures "sp_AAAAAA" simply because I'd like to be able to tell them apart from the system procedures..
**ASCII stupid question, get a stupid ANSI !!!** -
Why do you need to create a table in database DB1 from a stored procedure in your master database? I don't like creating tables in stored procedures, as creating a table is something that should only be done once... at least in my opinion...
Added: Didn't see Sushila's post before I submitted mine...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply