SP for multi DB's - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP for multi DB's

Author

Topic

GQ
Starting Member

9 Posts

Posted - 2004-02-09 : 14:39:07

Hey all I am new to SP in SQL

I am trying to write a SP to grab data from one DB and place it in another as a stored procedure. Here is the syntax I started.

CREATE PROCEDURE [user].[sp_test1] AS INSERT INTO test(DateS,DateE) Select DateS,DateE from SlaveDB.test
GO

Since I am not sure how to switch DB in a Select can some help me out.

Thanks
Gerald

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-09 : 14:42:22

CREATE PROC dbo.usp_Test
AS

INSERT INTO test (DateS, DateE)
SELECT DateS, DateE
FROM SlaveDB.dbo.test

RETURN
GO

Tara

GQ
Starting Member

9 Posts

Posted - 2004-02-09 : 14:56:11

Tara

Thanks that worked...

Now if I could ask another question. I created a job that executes once a night. I tried starting the job and this is what I get.

SQL Server Scheduled Job 'Test' (0x374E723340C68F469BFF578331F61B6B) - Status: Failed - Invoked on: 2004-02-09 13:01:41 - Message: The job failed. The Job was invoked by User AVS-SQL\Administrator. The last step to run was step 1 (Execute SP).

Thanks again
Gerald

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-09 : 14:59:07

Right click on the job and go to Job History. Click show step details. Go through each step and find out what the error is. If it doesn't show much in the case of a maintenance plan, check the SQL Server Error Log. Post whatever error you find here.

Tara

GQ
Starting Member

9 Posts

Posted - 2004-02-09 : 15:00:50

Job history

INSERT permission denied on object 'test', database 'MasterDB', owner 'dbo'. [SQLSTATE 42000] (Error 229) SELECT permission denied on object 'test', database 'SlaveDB', owner 'dbo'. [SQLSTATE 42000] (Error 229) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-09 : 15:04:14

You don't have INSERT permission on test table in the MasterDB database. The problem is with the account that the SQL Server Agent is using. That account needs the permission. Typically, this account should be local admin on the database server, which means sysadmin inside SQL Server. If it is using the system account, change it to a local admin account (requires a restart). Do that for both services (MSSQLSERVER and SQLSERVERAGENT).

Tara

GQ
Starting Member

9 Posts

Posted - 2004-02-09 : 15:06:09

This is what I get with Administrator account

Invalid object name 'test'. [SQLSTATE 42S02] (Error 208) Invalid object name 'SlaveDB.test'. [SQLSTATE 42S02] (Error 208). The step failed.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-09 : 15:08:44

You need to use the three part naming convention:

SlaveDB.dbo.test

Tara

GQ
Starting Member

9 Posts

Posted - 2004-02-09 : 15:13:04

Well it worked. the strang thing I did is recreate the procedure again under a different name.

I used sa for the job owner

Thansk for all the help..

I am sure I will have lots more questions in the future. Oh does anyone know a good site for query syntax?

Thansk
Gerald

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-09 : 15:14:11

This is the site to use.

Tara

GQ
Starting Member

9 Posts

Posted - 2004-02-09 : 15:57:52

Tara,
I have a question I want to create this procedure to work under my user account not dbo...

So I changed the SP to look like this

REATE PROC user.sp_Test
AS

INSERT INTO test (DateS, DateE)
SELECT DateS, DateE
FROM SlaveDB.user.test

RETURN
GO

this is the error I get..

Invalid object name 'test'. [SQLSTATE 42S02] (Error 208) Invalid object name 'SlaveDB.test'. [SQLSTATE 42S02] (Error 208). The step failed.

I gave the user every permission I could find owner ect....

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-09 : 15:59:04

The test table has to be created under the "user" account as well.

CREATE TABLE user.Test...

I would not recommend doing this anyway though. Keep the owner as dbo. It'll make your life a lot easier.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources