January 22, 2015 at 6:28 am
Hello!
I have two server with SQL-2008 in different place with different name. Each server have the same structure database but different data.
In this database (the name is the same in each server) I have a View that use a Link server for retrieve the data from the other server. This view exists in both server too, but when I run the view for retrieve data from the other server I receive a error.
Example for explain better:
I have two server A and B, a database db in both server and a view V in both server too. the view V have something like that:
select A.DB.dbo.table1 ...........
In server A the view run perfect without error. but when run in server B I receive this error:
what could be Happened ??? when the servers was in SQL Server 2005 both view work fine but after the migration to SQL Server 2008 I have the error.
-----------error:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1114.CodCuentaIngreso" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1108.Monto" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1108.CodTipoCredito" could not be bound.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col9388'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col9386'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col9385'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col9381'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col9380'.
January 22, 2015 at 7:13 am
Show us some code and linked server script please.
-- Gianluca Sartori
January 22, 2015 at 7:50 am
this is the view in server A: SERVER B is the other server when data is retrieve.
(DB is the database)
--NOTE: I only put 2 columns for security reason of the information and name.... with this part of the script I receive the same error anyway. If I run the inside subquery it run perfect . but is not the idea. or if I change the case for this:
CASE t.Tipo
WHEN 'R' THEN
CASE WHEN c.CodAyudaEcon = '' THEN dc.Monto
when c.CodAyudaEcon IS null then dc.Monto
ELSE 0
END
ELSE dc.Monto
--remember that this view is the same in server B, but when I put B is A. AND RUN PERFECT from server B 🙁
----THE Script!!
SELECT tmp.Fecha Fecha, tmp.Monto Monto
FROM (
Select c.FechaTrans Fecha,
CASE t.Tipo
WHEN 'R' THEN
CASE ISNULL(c.CodAyudaEcon, '')
WHEN '' THEN dc.Monto
ELSE 0
END
ELSE dc.Monto
END Monto
FROM .DB.dbo.CreditoEstud c
JOIN .DB.dbo.Moneda m
ON m.CodMoneda = c.CodMoneda
JOIN .DB.dbo.TipoCredito t
ON t.CodTipoCredito = c.CodTipoCredito
JOIN .DB.dbo.DetalleCredEstud dc
ON dc.CodTipoCredito = c.CodTipoCredito
AND dc.NumCredito = c.NumCredito
JOIN .DB.dbo.DebitoEstud d
ON d.NumDebito = dc.NumDebito
JOIN .DB.dbo.ConceptoTrans ct
ON ct.CodConcepto = d.CodConcepto
WHERE c.StNulo = 'F'
AND c.FechaTrans BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(dd, -7, GETDATE()), 103), 103) AND CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 103)+' 23:59', 103)
) AS tmp
WHERE tmp.Monto > 0
January 22, 2015 at 7:56 am
I don't see any of the objects mentioned in the error message, nor the linked server script.
-- Gianluca Sartori
January 22, 2015 at 7:57 am
this is the script for the link server B in server A, is the same in both server
EXEC master.dbo.sp_addlinkedserver @server = N'B', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'B',@useself=N'False',@locallogin=NULL,@rmtuser=N'USER',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'B', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
January 22, 2015 at 8:00 am
I don't know the error put this column and table that I don't have.. but the columns: monto is in the select and codtipocredito is in the where clauses.....
January 22, 2015 at 8:03 am
for the script that I show you the error is : (the column monto and codtipocredito is in the query)
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.Monto" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.CodTipoCredito" could not be bound.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col4040'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col4037'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col4033'.
January 22, 2015 at 8:08 am
Let's see if I understand correctly:
1) You have a view named "V" on server A and B. The view definition is the one you posted.
2) You have linked servers from A to B and from B to A
3) SELECT * FROM A.db.dbo.V works
4) SELECT * FROM B.db.dbo.V doesn't work and you get the error message you posted
Correct?
-- Gianluca Sartori
January 22, 2015 at 8:15 am
partially correct:
3) SELECT * FROM B.db.dbo.V works (if I run in server B)
4) SELECT * FROM B.db.dbo.V (if I run in server A, doesn't work and I get the error message that I posted
and the views works fine before I migrate from SQL 2005 to SQL 2008 in both server :/
January 22, 2015 at 8:21 am
Does the following work from server A?
SELECT * FROM OPENQUERY(B,'SELECT * FROM db.dbo.V')
-- Gianluca Sartori
January 22, 2015 at 8:32 am
No,
the result is:
OLE DB provider "SQLNCLI10" for linked server "B" returned message "Deferred prepare could not be completed.".
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'V', database 'DB', schema 'dbo'.
January 22, 2015 at 8:37 am
ok, ok, I put select permission and the view V, and run again, the sentence openquery run perfect but why the view don't ???
January 22, 2015 at 8:58 am
Does the error message change now when using the view?
-- Gianluca Sartori
January 22, 2015 at 9:00 am
not,
I have the same error:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1010.Monto" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1010.CodTipoCredito" could not be bound.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col4046'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col4043'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col4039'.
January 22, 2015 at 9:33 am
I'm under the impression that the view doesn't get passed through correctly.
Is this used in combination with other local tables or just a plain SELECT * FROM B.db.dbo.V ?
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply