May 26, 2010 at 6:53 am
Hi..would you guys have any idea about executing SP from new database pls?
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
iTHIS is my starting script
USE [EMP_DETAILS]
GO
/****** Object: StoredProcedure [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] Script Date: 05/26/2010 13:56:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] AS
May 26, 2010 at 8:30 am
You should actually be troubleshooting why the SP wasnt automatically run every day when all others did.
If however, you still want to create this SP in another database, in the SP code, qualify all table names with the database name as well. That is,
is your code read as
'select * from myTable',
you should change it to
'select * from EMP_DETAILS.myTable'.
That way, the SP would be created in your current database and the tables would still be referred from EMP_DETAILS database.
May 26, 2010 at 8:38 am
HI..
thanx for ur reply..
i am posting my script could u pls guide me wher the changes to be made.
USE [EMP_DETAILS]
GO
/****** Object: StoredProcedure [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] Script Date: 05/26/2010 15:35:51 ******/
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
CLOSE table_cursor
DEALLOCATE table_cursor
May 26, 2010 at 9:18 pm
Instead of the following statement, use the name of the current database where you want to create the SP.
USE [EMP_DETAILS]
GO
Then, in the remaining SP, wherever you are using table name, use EMP_DETAILS.tablename. You are loading the table name value from a cursor into a variable @TableName. Before using this variable in the dynamic SQL, set @TableName = 'EMP_DETAILS.' + @TableName. That should work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply