I am attempting to call a stored procedure from a separate sql server box and I receive the following message...
The object name 'prod1.tolim.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
Here is the statement in my stored proc....
exec PROD1.ToLIM.dbo.ToLIM_ExtractIndex @vDB, @vDBTable, @vLIMSymbol = 'CIN.CINERGY_LOAD', @vLIMColumn = 'CIN.ESAL_LESS_ORPHAN', @vTimeUnits = 'HOURLY_COLUMNS', @dDateStart = '1999-01-01', @dDateEnd = '2005-03-03'
I am executing from my dev server to my prod server and the prod server is a linked server.
Any help would be appreciated.
The error I got was very similar. I was trying to do the following update and "push" the data across to a linked server:
update ServerName.AAD.dbo.t_employeeset ServerName.AAD.dbo.t_employee.work_shift = wa.work_shiftfrom t_employee wawhere ServerName.AAD.dbo.t_employee.id = wa.idand wa.id = '105'
I was getting the following:Server: Msg 117, Level 15, State 2, Line 4The number name 'ServerName.AAD.dbo.t_employee' contains more than the maximum number of prefixes. The maximum is 3.
I couldn't find any other threads with my answer. Maybe I'm alone on this but it took me hours to find this solution, so I thought I'd post it here in hopes it helps out the next person searching on this.
It's a simple, but not obvious, answer. Put the table you are updating in the FROM clause with an alias and update the alias, as follows:
update laset la.work_shift = wa.work_shiftfrom ServerName.AAD.dbo.t_employee la, t_employee wawhere la.id = wa.idand wa.id = '105'
Beautiful, ins't it? That is when you have the answer.
That query should work fine, the only guess I have is the way you are calling it. Try the following:
exec PROD1.ToLIM.dbo.ToLIM_ExtractIndex @vDB, @vDBTable, 'CIN.CINERGY_LOAD', 'CIN.ESAL_LESS_ORPHAN', 'HOURLY_COLUMNS', '1999-01-01', '2005-03-03'
SQL Server is pretty picky about calls made to stored procedures when parameters are named so I'm guessing that's the issue.