Execting SP in anotherdatabse - 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 2005 Forums

Transact-SQL (2005)

Execting SP in anotherdatabse

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-05-26 : 08:23:28

Hi..

i was wndering whether some one have any idea about exectiing SP in new databse.

i created my stored procedure in EMP_DETAILS database, i scheduled this SP to run daily morning from the EMP_DETAILS database.

now i need to move this SP from EMP_DETAILS TO EMP_REGISTRATION database.

in my SP script i am using EMP_DETAILS database tables data,how can i point to EMP_DETAILS databse tables data if i created the same SP in EMP_REGISTRATION ??

would you guys have any idea about this pls?

thanx

Satya

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 08:30:39

You can reference a table in a different database with:

FROM OtherDatabase.dbo.TableName

but the persona/application executing the Sproc will need permission-chaining to the other database.

If you only want to grant EXECUTE permission on the Sproc, and NOT grant any permissions on the table(s) itself, then you have to go through some hoops! (and some of those may only exist in SQL 2008, if my memory serves me correctly)

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 08:32:34

Looks like SQL 2005 is enough:

http://www.sommarskog.se/grantperm.html

satya068
Posting Yak Master

233 Posts

Posted - 2010-05-26 : 08:43:18

Hello Kristene,

thanx for ur reply.

actually i have 4 database and i scheduled four stroed procedures independently.

strage is 3 stroed procedures in 3 databses whic i scheduled every day morning are executing fine...BUT...the 4th one whic i scheduled from the datbase EMD_DETAILS is not picking up..i need to kickoff manually every day morning.

so here wat i ma trying is... the 4th SP whic is not executing through schedule from the EMD_DETAILS this one i want to move to another databse and wat to try form there whether that SP is able to execute through scheduler r not?

i am confused how the data pick from EMD_DETAILS databse tables ifi execute the SP from another databse whic is EMP_REGISTRATION.

Satya

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 09:43:08

"i am confused how the data pick from EMD_DETAILS databse tables ifi execute the SP from another databse whic is EMP_REGISTRATION."


USE EMP_REGISTRATION

CREATE PROCEDURE MySproc
AS
...
SELECT Co1, Col2, ...
FROM EMD_DETAILS.dbo.MyTable

satya068
Posting Yak Master

233 Posts

Posted - 2010-05-26 : 09:56:11

kristen this is my SP script in EMP_DETAILS database could you guide me where the changes needed to execute from EMP_REGISTRATION pointing to EMP_DETAILS databse.

USE [EMP_DETAILS]
GO
/****** Object: StoredProcedure [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] Script Date: 05/26/2010 14:49:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] AS
DECLARE @TableName sysname,
@SQL nvarchar(max),
@SQL1 nvarchar(max),
@SQL2 nvarchar(max),
@INSERT_DATE datetime,
@UPDATE_DATE datetime,
@INSERT_COUNT int,
@UPDATE_COUNT int

DECLARE @TODAY VARCHAR(10)
SET @TODAY = CONVERT(varchar(10), getdate(), 121)

DECLARE @DATABASE varchar(255)
TRUNCATE TABLE RECORD_COUNT
TRUNCATE TABLE INSERT_COUNT
TRUNCATE TABLE UPDATE_COUNT
DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
WHERE (NAME like 'RDB_PAS%'
and name not like 'RDB_PAS%DATA_TO_LOAD')
OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

print @SQL

EXEC SP_EXECUTESQL @SQL

PRINT 'got to step 1'

--SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when INSERT_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as INSERT_COUNT' +
--' FROM [' + @TableName + ']'

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, INSERT_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,INSERT_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT count(*) as INSERT_COUNT FROM RDB_PAS_APPOINTMENTS WHERE INSERT_DATE >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
--AND INSERT_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

print @SQL1
EXEC SP_EXECUTESQL @SQL1
PRINT 'got to step 3'

SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,UPDATE_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, UPDATE_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,UPDATE_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when UPDATE_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as UPDATE_COUNT' +
--' FROM [' + @TableName + ']'

print @SQL2
EXEC SP_EXECUTESQL @SQL2

FETCH NEXT FROM table_cursor INTO @TableName

END

Satya

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 10:18:16

OK, that's a different question.

If you want to execute it from EMP_REGISTRATION then just do:

EXEC EMP_DETAILS.dbo.SP_FULL_PAS_DAILY_RECORD_COUNT

and that will default to accessing the data in the EMP_DETAILS database, so no changes to the Sproc required. However there may be permissions problems, as I described above.

If you are scheduling it you can just tell SQL Agent to USE the EMP_DETAILS database

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 10:23:07

If you want to execute if from EMP_REGISTRATION and have it reference tables in EMP_REGISTRATION then pass the name of the "target" database as a parameter, and include that in your dynamic SQL syntax


CREATE PROCEDURE [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT]
    @strTargetDB varchar(128)
AS
DECLARE @TableName sysname,
...
SELECT @SQL =
'Insert into ' + QUOTENAME(@strTargetDB) + '.dbo.RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' 
+ @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT '
+ 'FROM ' + QUOTENAME(@strTargetDB) + '.dbo.['+ @TableName+']'
...