connecting to SSAS: views

Showing posts with label views. Show all posts

Tuesday, March 20, 2012

problem exporting views

While exporting a database with "export > objects > selecting the views"
I get the error:
Invalid object name 'dbo.myview'
The weird thing is that this view allready exists in the destination dbase.
Any help appreciated.
THX"nicholas" <murmurait1@.hotmail.com> wrote in
news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> While exporting a database with "export > objects > selecting the
> views" I get the error:
> Invalid object name 'dbo.myview'
> The weird thing is that this view allready exists in the destination
> dbase.
> Any help appreciated.
> THX
>
>
Run the following query in your user database. What are the results?
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'myview' and t1.uid = t2.uid
--
Regards
JTC ^..^|||Sorry to ask, but how can I do this?
I tried the SQL query analyser and inserted:
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
but get this message:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'mydatabase.dbo' does not match with a table name or alias
name used in the query
thx a lot
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
> "nicholas" <murmurait1@.hotmail.com> wrote in
> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> > While exporting a database with "export > objects > selecting the
> > views" I get the error:
> > Invalid object name 'dbo.myview'
> >
> > The weird thing is that this view allready exists in the destination
> > dbase.
> >
> > Any help appreciated.
> >
> > THX
> >
> >
> >
> Run the following query in your user database. What are the results?
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = 'myview' and t1.uid = t2.uid
> --
> Regards
> JTC ^..^|||You are supposed to replace "mydatabase" with the name of your database. And "myview" with the name
of your view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> Sorry to ask, but how can I do this?
> I tried the SQL query analyser and inserted:
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
> but get this message:
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'mydatabase.dbo' does not match with a table name or alias
> name used in the query
> thx a lot
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>> "nicholas" <murmurait1@.hotmail.com> wrote in
>> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>> > While exporting a database with "export > objects > selecting the
>> > views" I get the error:
>> > Invalid object name 'dbo.myview'
>> >
>> > The weird thing is that this view allready exists in the destination
>> > dbase.
>> >
>> > Any help appreciated.
>> >
>> > THX
>> >
>> >
>> >
>> Run the following query in your user database. What are the results?
>> select t1.name, t2.name
>> from sysobjects t1, sysusers t2
>> where t1.name = 'myview' and t1.uid = t2.uid
>> --
>> Regards
>> JTC ^..^
>|||Yes, ofcourse, I did that.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> You are supposed to replace "mydatabase" with the name of your database.
And "myview" with the name
> of your view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nicholas" <murmurait1@.hotmail.com> wrote in message
news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> > Sorry to ask, but how can I do this?
> > I tried the SQL query analyser and inserted:
> >
> > select t1.name, t2.name
> > from sysobjects t1, sysusers t2
> > where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
> >
> > but get this message:
> > Server: Msg 107, Level 16, State 2, Line 1
> > The column prefix 'mydatabase.dbo' does not match with a table name or
alias
> > name used in the query
> >
> > thx a lot
> >
> > "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> > news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
> >> "nicholas" <murmurait1@.hotmail.com> wrote in
> >> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> >>
> >> > While exporting a database with "export > objects > selecting the
> >> > views" I get the error:
> >> > Invalid object name 'dbo.myview'
> >> >
> >> > The weird thing is that this view allready exists in the destination
> >> > dbase.
> >> >
> >> > Any help appreciated.
> >> >
> >> > THX
> >> >
> >> >
> >> >
> >>
> >> Run the following query in your user database. What are the results?
> >>
> >> select t1.name, t2.name
> >> from sysobjects t1, sysusers t2
> >> where t1.name = 'myview' and t1.uid = t2.uid
> >>
> >> --
> >> Regards
> >> JTC ^..^
> >
> >
>|||"nicholas" <murmurait1@.hotmail.com> wrote in
news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl:
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> You are supposed to replace "mydatabase" with the name of your
>> database.
> And "myview" with the name
>> of your view.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "nicholas" <murmurait1@.hotmail.com> wrote in message
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
>> > Sorry to ask, but how can I do this?
>> > I tried the SQL query analyser and inserted:
>> >
>> > select t1.name, t2.name
>> > from sysobjects t1, sysusers t2
>> > where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid =>> > t2.uid
>> >
>> > but get this message:
>> > Server: Msg 107, Level 16, State 2, Line 1
>> > The column prefix 'mydatabase.dbo' does not match with a table name
>> > or
> alias
>> > name used in the query
>> >
>> > thx a lot
>> >
>> > "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
>> > news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>> >> "nicholas" <murmurait1@.hotmail.com> wrote in
>> >> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>> >>
>> >> > While exporting a database with "export > objects > selecting
>> >> > the views" I get the error:
>> >> > Invalid object name 'dbo.myview'
>> >> >
>> >> > The weird thing is that this view allready exists in the
>> >> > destination dbase.
>> >> >
>> >> > Any help appreciated.
>> >> >
>> >> > THX
>> >> >
>> >> >
>> >> >
>> >>
>> >> Run the following query in your user database. What are the
>> >> results?
>> >>
>> >> select t1.name, t2.name
>> >> from sysobjects t1, sysusers t2
>> >> where t1.name = 'myview' and t1.uid = t2.uid
>> >>
>> >> --
>> >> Regards
>> >> JTC ^..^
>> >
>> >
>
Like this...
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'tbl_customers' and t1.uid = t2.uid
The idea is that the query will returns all tables named tbl_customers
along with the table owners. Please Copy and Paste the results.
--
Regards
JTC ^..^|||Go back to the proposed query, and fix below things:
Do not remove the quotes around the name of the view in the WHERE clause. Do not fully qualify the
object name in the where clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> You are supposed to replace "mydatabase" with the name of your database.
> And "myview" with the name
>> of your view.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "nicholas" <murmurait1@.hotmail.com> wrote in message
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
>> > Sorry to ask, but how can I do this?
>> > I tried the SQL query analyser and inserted:
>> >
>> > select t1.name, t2.name
>> > from sysobjects t1, sysusers t2
>> > where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
>> >
>> > but get this message:
>> > Server: Msg 107, Level 16, State 2, Line 1
>> > The column prefix 'mydatabase.dbo' does not match with a table name or
> alias
>> > name used in the query
>> >
>> > thx a lot
>> >
>> > "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
>> > news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>> >> "nicholas" <murmurait1@.hotmail.com> wrote in
>> >> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>> >>
>> >> > While exporting a database with "export > objects > selecting the
>> >> > views" I get the error:
>> >> > Invalid object name 'dbo.myview'
>> >> >
>> >> > The weird thing is that this view allready exists in the destination
>> >> > dbase.
>> >> >
>> >> > Any help appreciated.
>> >> >
>> >> > THX
>> >> >
>> >> >
>> >> >
>> >>
>> >> Run the following query in your user database. What are the results?
>> >>
>> >> select t1.name, t2.name
>> >> from sysobjects t1, sysusers t2
>> >> where t1.name = 'myview' and t1.uid = t2.uid
>> >>
>> >> --
>> >> Regards
>> >> JTC ^..^
>> >
>> >
>

Friday, March 9, 2012

problem creation, script with create view

I created a script like this :
use tk_main
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08701')
drop view V_08701
GO
CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
D_ATE,NO_ENVOI,ARRIVEE
GO
grant all on V_08701 to public
GO
if exists (select table_name from information_schema.views where table_name
= 'V95001') drop view V_95001
GO
CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
NO_FAC,NO_ENVOI_TK,ARRIVEE
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08601') drop view V_08601
GO
CREATE VIEW V_08601
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO
IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
GO
but the analyser doesn't like this script.
Can someboady help me out.
Thanks in advance
RalfWhat error messages do you get?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message
news:40618606$0$7376$626a14ce@.news.free.fr...
> I created a script like this :
> use tk_main
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08701')
> drop view V_08701
> GO
> CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
> D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
> D_ATE,NO_ENVOI,ARRIVEE
> GO
> grant all on V_08701 to public
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V95001') drop view V_95001
> GO
> CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
> NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
> NO_FAC,NO_ENVOI_TK,ARRIVEE
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08601') drop view V_08601
> GO
> CREATE VIEW V_08601
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
> IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
> ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
> ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
> POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
> ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
> GO
> --
> but the analyser doesn't like this script.
> Can someboady help me out.
> Thanks in advance
> Ralf
>
>[/color]|||Sorry I forgot to sedn the error :
Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
Ligne 2 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
Ligne 1 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
Ligne 3 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
Ligne 5 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
Ligne 7 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
Ligne 9 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
Ligne 11 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
Ligne 13 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Ralf
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> What error messages do you get?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Ralf Meuser" <rmeuser@.free.fr> wrote in message
> news:40618606$0$7376$626a14ce@.news.free.fr...
> table_name
> table_name
BY
> table_name
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
>|||Since this is an English speaking newsgroup, it would be helpful if you woul
d translate the French messages
instead of letting us do that.
I don't see a problem with this, unless you actually have a line-break in th
e middle of a column name in your
code as well (I assume it is inserted by your newsreader).
Perhaps someone has changed the batch separator (from GO to something else)
in Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message news:406197b3$0$309$626a14ce@.news.free.fr.
.
> Sorry I forgot to sedn the error :
> Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
> Ligne 2 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
> Ligne 1 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
> Ligne 3 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
> Ligne 5 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
> Ligne 7 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
> Ligne 9 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
> Ligne 11 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
> Ligne 13 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
>
> Ralf
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
> dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> BY
> (DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,
PO
>

Wednesday, March 7, 2012

Problem creating indexed views with linked servers

Hi, I have a problem, maybe someone can help me.

I'm traing to create a view with a Linked Server

This query works great:

select id, descr
from SERVER.DB.dbo.TABLE

When I tray to create the view:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select id, descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3
Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'
is invalid for schema binding. Names must be in two-part format and an
object cannot reference itself.

So I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3
The number name 'SERVER.DB.dbo.TABLE' contains more than the maximum
number of prefixes. The maximum is 3.

Then I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select a.id, a.descr
from SERVER.DB.dbo.TABLE as A
GO

I Have this error

Server: Msg 4512, Level 16, State 3, Procedure View1, Line 3
Cannot schema bind view 'dbo.View1' because name
'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Names
must be in two-part format and an object cannot reference itself.

This query alone works great:

select a.id, a.descr
from SERVER.DB.dbo.TABLE as A

The names aren't what I describe here (id is not valid without []).

ANY IDEAS?!??!?!

I don't know what else can I do.

I need help!!!

TANKS A LOT!!!!!!!!Ariel (ap@.data54.com) writes:
> Hi, I have a problem, maybe someone can help me.
> I'm traing to create a view with a Linked Server

Stop! Take a step back and consider what you are doing!

An indexed view is a materialized view. This means that when an update
is made to a base table, this update is propagated to the view, not
only logically, but also physically.

Now, if that table on the remote server was updated would should happen?
Particularly, what would happen if our local server was unavailble? Or
the particular database was unavailable?

In practice, you cannot even have cross-database indexed views on the
same server, and you run into a stopblock already WITH SCHEMABINDING.
If you could create a schema-bounbd view that referenced another database,
each attempt to drop or rename a databaes, or set a it offline, would
need to check all other databases on the server for SCHEMABINDING
references.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Tuesday, March 20, 2012

problem exporting views

problem exporting views

problem exporting views

Friday, March 9, 2012

problem creation, script with create view

Wednesday, March 7, 2012

Problem creating indexed views with linked servers

connecting to SSAS

Blog Archive

About Me