SQL Error - Invalid Object Name Stored Procedure

Question

I implement and support a software applicatoin that allows me to create macros within the application. One of the macro steps is to execute a SQL procedure. When I attempt to run a macro that is executing a stored proc (_TSi_CustomerAccountCorrection) I'm seeing this error:

Error 0 executing [__TSi__CustomerAccountCorrection].
Invalid object name 'TSI_ProService.dbo.Customer'.

If I run this stored proc in SQL Query Analyzer (SQL 2000 using Integrated Security), the proc runs just fine. The application I'm running it through uses a SQL connection string that is set to use integrated security and I'm logged on as the same exact user as well.

Unfortunately, I do not have the source code for the application. I was hoping that someone might recognize what is going on here or at least give me a suggestion as to what the issue might be.

As far as I can see, when running the stored proc through the application and through the query analyzer is exactly the same.

Here is the stored proc:

CREATE  PROCEDURE [dbo].[__TSi__CustomerAccountCorrection] AS
set xact_abort on
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRANSACTION trans1
DECLARE cur_cust CURSOR FOR
 SELECT [AccountNumber] FROM DBSTR1.HQ.dbo.PSD_CustomerExport
DECLARE @accountNumber varchar(50);
DECLARE @custAccountNumber varchar(50);
DECLARE @query varchar(500);
OPEN cur_cust;

FETCH NEXT FROM cur_cust INTO @accountNumber;

WHILE @@FETCH_STATUS = 0 BEGIN
 set @custAccountNumber = (SELECT TOP 1 [Account] FROM [Customer] WHERE [UserData2] = @accountNumber)

 if @custAccountNumber is null
 begin
  SET @custAccountNumber = @accountNumber
 end

 --UPDATE DBSTR1.HQ.dbo.PSD_CustomerExport SET [VcrAccountNumber] = @custAccountNumber WHERE [AccountNumber] = @accountNumber
 UPDATE DBSTR1.HQ.dbo.PSD_CustomerExport SET [VcrAccountNumber] = @custAccountNumber WHERE CURRENT OF cur_cust

 FETCH NEXT FROM cur_cust INTO @accountNumber;
END
CLOSE cur_cust;
DEALLOCATE cur_cust;
COMMIT TRANSACTION trans1
GO

Any suggestions?

You sure it's running on the same user ? Feels like a default sql schema error. — David
– David, Commented Aug 8, 2011 at 20:22

JNK · Accepted Answer · 2011-08-08 20:23:34Z

1

It's not executing in the same DB context when you run it remotely vs. when you run it in SSMS.

Notice that the customer table is not using the three part name. SQL Server is making assumptions about the Database and schema that the customer table is in and is guessing wrong.

It is using whatever the default context is for the connection, which from your error code is likely the database TSI_ProService and the schema defaults to dbo. One or both of these are incorrect for that table.

What DB and schema are that table in? Use the 3PN (Database.schema.object) in the call in the proc and I think it will probably work fine.

answered Aug 8, 2011 at 20:23

JNK

65.5k15 gold badges127 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Joel Over a year ago

What I don't understand is that the table is TSIPro_Service.dbo.Customer... By the way I typed the error incorrectly the error was TSIPro_Service not "TSI_ProService"

JNK Over a year ago

@user - It doesn't matter what DB it's in if that DB is not the default for the connection you are initializing for the remote call.

Joel Over a year ago

I'm a little slow... so are you telling me that I should replace "Customer" with "TSIPro_Service.dbo.Customer"?

JNK Over a year ago

Yes. It's generally ALWAYS a good idea in procedures/views/scripts to fully qualify you object names unless you have a good reason NOT to.

Joel Over a year ago

Oh ok... thanks for the advice and the tip! Unfortunately, I do not have my customers laptop right now so I cannot check this until later and I can try it out... I'll let you know and thanks again for everything!

Collectives™ on Stack Overflow

SQL Error - Invalid Object Name Stored Procedure

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related