April 2, 2009 at 1:27 am
Hi,
Running an update query in sql server management studio query window gives an error:
(1 row(s) affected)
(1 row(s) affected)
Msg 911, Level 16, State 1, Procedure COBOChanges, Line 57
Could not locate entry in sysdatabases for database 'Changes'. No entry found with that name. Make sure that the name is entered correctly.
Can some suggest an solution?
Regards,
Malathi
April 2, 2009 at 1:37 am
Hi Malathi,
The SQL you are running would be of benefit to the rest of us.
The obvious things first though - are you sure that a database of that name exists on the server that you are running this procedure on? Or, does the procedure refer to another server for this database, and does the database exist on that server?
BrainDonor.
April 2, 2009 at 1:55 am
Hi,
I have verified that the database is there on the server using sp:
exec sys.sp_databases
And also checked if the table is available using sp on that database:
exec sys.sp_tables
The select statements are getting fired correctly.
I am just trying a simple update statement before adding it to an sp which is used to retreive rows for an customer overdue report.
Regards,
Malathi
April 2, 2009 at 2:16 am
Msg 911, Level 16, State 1, Procedure COBOChanges, Line 57
You are executing a procedure COBOChanges and not just simple update statement.
Is there any trigger in the table in which you are updating the values .?
Have you hard coded the database name somewhere..?
Check everywhere for the database name....copy paste it instead of typing...
April 2, 2009 at 3:32 am
Hi,
There are 2 triggers written on the table:
COBOChanges and ONUPDATECustomerOnBookingOrderHistory
I have observed the error while executing statement:
update customeronbookingorder set Leads = 0 where customeronbookingid = 32251
PF below the COBOChanges Trigger Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/****** Object: Trigger dbo.COBOChanges Script Date: 03/01/2009 09:40:53 ******/
ALTER TRIGGER [COBOChanges] ON [dbo].[CustomerOnBookingOrder]
FOR UPDATE
AS
begin
declare
@DCustomerOnBookingID int, @ICustomerOnBookingID int,
@DCustomerID int, @ICustomerID int,
@DBookingRefNo int, @IBookingRefNo int,
@DLeads bit, @ileads bit,
@DAgeGroup varchar(16), @IAgeGroup varchar(16),
@DAgeOnDepartureDate varchar(5), @IAgeOnDepartureDate varchar(5),
@DPassportExpiryDate smalldatetime, @IPassportExpiryDate smalldatetime,
@DVisaObtained bit, @IVisaObtained bit,
@DHealthInfo varchar(255), @IHealthInfo varchar(255),
@DNotes varchar(255), @INotes varchar(255)
declare @ChangeType as char(1)
declare @UseUserID as int
DECLARE COBO_Cursor CURSOR FOR
SELECT
D.CustomerOnBookingID, I.CustomerOnBookingID,
D.CustomerID, I.CustomerID,
D.BookingRefNo, I.BookingRefNo,
D.Leads, I.Leads,
D.AgeGroup, I.AgeGroup,
D.AgeOnDepartureDate, I.AgeOnDepartureDate,
D.PassportExpiryDate, I.PassportExpiryDate,
D.VisaObtained, I.VisaObtained,
D.HealthInfo, I.HealthInfo,
D.Notes, I.Notes
from [Inserted] I Full join [Deleted] D ON I.CustomerOnBookingID = D.CustomerOnBookingID
OPEN COBO_Cursor
FETCH NEXT FROM COBO_Cursor INTO
@DCustomerOnBookingID, @ICustomerOnBookingID,
@DCustomerID, @ICustomerID,
@DBookingRefNo, @IBookingRefNo,
@DLeads, @ileads,
@DAgeGroup, @IAgeGroup,
@DAgeOnDepartureDate, @IAgeOnDepartureDate,
@DPassportExpiryDate, @IPassportExpiryDate,
@DVisaObtained, @IVisaObtained,
@DHealthInfo, @IHealthInfo,
@DNotes, @INotes
WHILE @@FETCH_STATUS = 0
begin
set @ChangeType = 'U'
if @ICustomerOnBookingID is null set @ChangeType = 'D'
if @DCustomerOnBookingID is null set @ChangeType = 'I'
set @UseUserID = null
EXEC [Changes].[dbo].[ik_ChangeLogInsertI]
@ChangeType,
@UseUserID,
'CustomerOnBookingOrder',
'CustomerOnBookingID',
@DCustomerOnBookingID,
@ICustomerOnBookingID,
@DCustomerOnBookingID,
@ICustomerOnBookingID
EXEC [Changes].[dbo].[ik_ChangeLogInsertI]
@ChangeType,
@UseUserID,
'CustomerOnBookingOrder',
'CustomerID',
@DCustomerOnBookingID,
@ICustomerOnBookingID,
@DCustomerID,
@ICustomerID
EXEC [Changes].[dbo].[ik_ChangeLogInsertI]
@ChangeType,
@UseUserID,
'CustomerOnBookingOrder',
'BookingRefNo',
@DCustomerOnBookingID,
@ICustomerOnBookingID,
@DBookingRefNo,
@IBookingRefNo
EXEC [Changes].[dbo].[ik_ChangeLogInsertI]
@ChangeType,
@UseUserID,
'CustomerOnBookingOrder',
'Leads',
@DCustomerOnBookingID,
@ICustomerOnBookingID,
@DLeads,
EXEC [Changes].[dbo].[ik_ChangeLogInsertC]
@ChangeType,
@UseUserID,
'CustomerOnBookingOrder',
'AgeGroup',
@DCustomerOnBookingID,
@ICustomerOnBookingID,
@DAgeGroup,
@IAgeGroup
EXEC [Changes].[dbo].[ik_ChangeLogInsertC]
@ChangeType,
@UseUserID,
'CustomerOnBookingOrder',
'AgeOnDepartureDate',
@DCustomerOnBookingID,
@ICustomerOnBookingID,
@DAgeOnDepartureDate,
@IAgeOnDepartureDate
EXEC [Changes].[dbo].[ik_ChangeLogInsertD]
@ChangeType,
@UseUserID,
'CustomerOnBookingOrder',
'PassportExpiryDate',
@DCustomerOnBookingID,
@ICustomerOnBookingID,
@DPassportExpiryDate,
@IPassportExpiryDate
EXEC [Changes].[dbo].[ik_ChangeLogInsertI]
@ChangeType,
@UseUserID,
'CustomerOnBookingOrder',
'VisaObtained',
@DCustomerOnBookingID,
@ICustomerOnBookingID,
@DVisaObtained,
@IVisaObtained
EXEC [Changes].[dbo].[ik_ChangeLogInsertC]
@ChangeType,
@UseUserID,
'CustomerOnBookingOrder',
'HealthInfo',
@DCustomerOnBookingID,
@ICustomerOnBookingID,
@DHealthInfo,
@IHealthInfo
EXEC [Changes].[dbo].[ik_ChangeLogInsertC]
@ChangeType,
@UseUserID,
'CustomerOnBookingOrder',
'Notes',
@DCustomerOnBookingID,
@ICustomerOnBookingID,
@DNotes,
@INotes
FETCH NEXT FROM COBO_Cursor INTO
@DCustomerOnBookingID, @ICustomerOnBookingID,
@DCustomerID, @ICustomerID,
@DBookingRefNo, @IBookingRefNo,
@DLeads, @ileads,
@DAgeGroup, @IAgeGroup,
@DAgeOnDepartureDate, @IAgeOnDepartureDate,
@DPassportExpiryDate, @IPassportExpiryDate,
@DVisaObtained, @IVisaObtained,
@DHealthInfo, @IHealthInfo,
@DNotes, @INotes
END
CLOSE COBO_Cursor
DEALLOCATE COBO_Cursor
end
Regards,
Malathi
April 2, 2009 at 4:14 am
Everything seems to be ok. I dont see any problem as such.
Are both the databases in the same server..?.
April 2, 2009 at 6:30 am
Your trigger tries to execute sprocs in the [Changes] database !
EXEC [Changes].[dbo].[ik_ChangeLogInsertI]
Does that database exist on the sql instance ?
OR
Does the user executing the update have the needed auth to execute the sprocs on the Changes db ??
Has cross database ownership been enabled ? (only if strictly needed)
This would be a typical Service Broker usage scenario !!
Mainly to avoid all deadlocking risks !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 11, 2017 at 4:09 am
Malathi-931261 - Thursday, April 2, 2009 1:27 AMHi, Running an update query in sql server management studio query window gives an error: (1 row(s) affected) (1 row(s) affected) Msg 911, Level 16, State 1, Procedure COBOChanges, Line 57 Could not locate entry in sysdatabases for database 'Changes'. No entry found with that name. Make sure that the name is entered correctly. Can some suggest an solution? Regards, Malathi
Hi All
I faced the same issue.
We need to use Square brackets for database name
ie
use [database]
instead of
use database
Hope it helps.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply