new programmer here and need help

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

New to SQL Server Programming

new programmer here and need help

Author

Topic

pjnovak007
Starting Member

19 Posts

Posted - 2010-07-02 : 10:58:17

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-02 : 11:51:45

If I understand correctly, just change this condition:
WHILE @@fetch_status = 0 Begin

to

WHILE @@fetch_status = 0 AND @databaseName <> 'Fish4' Begin

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-02 : 11:52:09

This?

DECLARE curPublishers Cursor
LOCAL
FORWARD_ONLY
READ_ONLY
For Select publisherId,
databaseName,
databaseLocation 
From Publishers
WHERE databaseName <> 'Fish4'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources