help with coding for a new db person

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

General SQL Server Forums

Database Design and Application Architecture

help with coding for a new db person

Author

Topic

pjnovak007
Starting Member

19 Posts

Posted - 2010-07-02 : 11:25:57

I have this code and my boss wants me to skip a database called "Fish4" how would i do that?

The job isnt working because it's trying to find the database and the database was deleted (old) I want to keep the historical data so i dont want to delete - just not update this database anymore.

-----------------------------------
USE [CrossDBReports]
GO
/****** Object: StoredProcedure [dbo].[adm_PopulateUIInfo] Script Date: 07/02/2010 10:48:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[adm_PopulateUIInfo]
@startDate DateTime,
@endDate DateTime
AS
BEGIN

SET NOCOUNT ON

Declare @publisherId UniqueIdentifier
Declare @databaseName VarChar(50)
Declare @databaseLocation VarChar(50)
Declare @sql VarChar(1024)

Create Table #tmpUI
(
usrId UniqueIdentifier PRIMARY KEY Clustered
,emailAddress VarChar(100)
,ClientName VarChar(100)
,clientAssignedId VarChar(75)
,numJobs int
,numApplicants int
,percentViewedApplicants int
,isUsedFolders Bit Default 0
,isModifyScore Bit Default 0
,isModifyQuestions Bit Default 0
,isPrintButton Bit Default 0
,isForwardApp Bit Default 0
,isUsedNotes Bit Default 0
,isCreateFolders Bit Default 0
,isRenewAd Bit Default 0
,loginCount Int Default 0
,loginDate DateTime
,clientId UniqueIdentifier
,publisherId UniqueIdentifier
)

DECLARE curPublishers Cursor
LOCAL
FORWARD_ONLY
READ_ONLY
For Select publisherId,
databaseName,
databaseLocation
From Publishers

Open curPublishers

FETCH NEXT
FROM curPublishers
INTO @publisherId,@databaseName,@databaseLocation

WHILE @@fetch_status = 0 Begin
Select @sql = null
Select @sql = 'Insert Into #tmpUI (
usrId
,emailAddress
,ClientName
,clientAssignedId
,numJobs
,numApplicants
,percentViewedApplicants
,isUsedFolders
,isModifyScore
,isModifyQuestions
,isPrintButton
,isForwardApp
,isUsedNotes
,isCreateFolders
,isRenewAd
,loginCount
,loginDate
,clientId)' +
' exec [' + @databaseLocation + '].' +
@databaseName + '.dbo.qry_UIReport ''' +
Cast(@publisherId As VarChar(39)) + ''', ' +
'''' + Cast(@startDate As VarChar(30)) + ''', ' +
'''' + Cast(@endDate As VarChar(30)) + ''', ' + 'Null'

exec (@sql)

Update #tmpUI Set publisherId=@publisherId Where publisherId Is Null

FETCH NEXT
FROM curPublishers
INTO @publisherId,@databaseName,@databaseLocation
End

Close curPublishers
Deallocate curPublishers

Update UIInfo
Set usrId=UI.usrId
,emailAddress=UI.emailAddress
,ClientName=UI.clientName
,clientAssignedId=UI.clientAssignedId
,numJobs=UI.numJobs
,numApplicants=UI.numApplicants
,percentViewedApplicants=UI.percentViewedApplicants
,isUsedFolders=UI.isUsedFolders
,isModifyScore=UI.isModifyScore
,isModifyQuestions=UI.isModifyQuestions
,isPrintButton=UI.isPrintButton
,isForwardApp=UI.isForwardApp
,isUsedNotes=UI.isUsedNotes
,isCreateFolders=UI.isCreateFolders
,isRenewAd=UI.isRenewAd
,loginCount=UI.loginCount
,loginDate=UI.loginDate
,clientId=UI.clientId
,publisherId=UI.publisherId
From #tmpUI UI
Inner Join dbo.UIInfo U On U.usrId=UI.usrId

Insert Into UIInfo (
usrId
,emailAddress
,ClientName
,clientAssignedId
,numJobs
,numApplicants
,percentViewedApplicants
,isUsedFolders
,isModifyScore
,isModifyQuestions
,isPrintButton
,isForwardApp
,isUsedNotes
,isCreateFolders
,isRenewAd
,loginCount
,loginDate
,clientId
,publisherId)
Select usrId
,emailAddress
,ClientName
,clientAssignedId
,numJobs
,numApplicants
,percentViewedApplicants
,isUsedFolders
,isModifyScore
,isModifyQuestions
,isPrintButton
,isForwardApp
,isUsedNotes
,isCreateFolders
,isRenewAd
,loginCount
,loginDate
,clientId
,publisherId
From #tmpUI UI
Where Not Exists (Select usrId
From dbo.UIInfo
Where UI.usrId=dbo.UIInfo.usrId)

Drop Table #tmpUI

Set NoCount Off;
END

Patrick

pjnovak007
Starting Member

19 Posts

Posted - 2010-07-02 : 11:58:28

Is this a hard one or did i not explain correctly and it's confusing?

Patrick

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-02 : 13:42:59

Duplicate post.Follow the thread below...there are a couple of solutions posted.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146804

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources