Linking Oracle view to SQL Server express

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying?

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,

Stara

Which driver did you use ? Its long ago that I worked with Oracle linked servers, but you should use the Oracle driver which comes with the clint installation of Oracle rather than the MS one. You could also try to use an Openquery rather than the direct view as this statememt is directly executed on the Oracle system and is not further translated through the driver tier. Are you executing a complicate query whereas calculations are done on the Oracle or SQL Server side ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I have used MSDAORA. How do I use OpenQuery. Can you please provide an example?

Thanks,

Stara

|||

You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here. When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there.

Regards,

Mike

sql

Linking Oracle view to SQL Server express

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying?

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,

Stara

Which driver did you use ? Its long ago that I worked with Oracle linked servers, but you should use the Oracle driver which comes with the clint installation of Oracle rather than the MS one. You could also try to use an Openquery rather than the direct view as this statememt is directly executed on the Oracle system and is not further translated through the driver tier. Are you executing a complicate query whereas calculations are done on the Oracle or SQL Server side ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I have used MSDAORA. How do I use OpenQuery. Can you please provide an example?

Thanks,

Stara

|||

You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here. When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there.

Regards,

Mike

Linking Oracle view to SQL Server express

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying?

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,

Stara

Which driver did you use ? Its long ago that I worked with Oracle linked servers, but you should use the Oracle driver which comes with the clint installation of Oracle rather than the MS one. You could also try to use an Openquery rather than the direct view as this statememt is directly executed on the Oracle system and is not further translated through the driver tier. Are you executing a complicate query whereas calculations are done on the Oracle or SQL Server side ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I have used MSDAORA. How do I use OpenQuery. Can you please provide an example?

Thanks,

Stara

|||

You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here. When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there.

Regards,

Mike

Linking back to ProClarity Analytics reports from SQL Server 2005 Reporting Services failed

Hi, all here,

Thank you very much for your kind attention.

I encountered a problem when linking back to ProClarity Analytics views from SQL Server 2005 Reporting Services Server (the reports on SQL Server 2005 Reporting Services Server is export from ProClarity). The error message is as below:

Server object error 'ASP 0177 : 800401f3'

Server.CreateObject Failed

/pas/en/src/PRSQuery.asp, line 50

800401f3

Note: this error only occured when linking back using option:'data on this report', no error occured when using option'analyze in a ProClarity server page'.

Would please any expert here give me any guidance and advices for that.

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Hi

I encountered the same problem. My solution is posted on my blog:
http://www.cubido.at/Blog/tabid/176/EntryID/177/Default.aspx

LinkeServer Problems...

Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.That's an initialization failure...a pretty generic OLEDB error. Sounds lik
e you have yet to configure the Security mappings correctly. If you are att
empting to use Windows Authentication Delegation, you have a LOT of work ahe
ad of you to configure Kerberos correctly for delegation.
You have to set the SPN for the SS server, the SS service accounts, and make
sure that the accounts are marked correctly for delegation purposes.
Sincerely,
Anthony Thomas
--
"Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message news:%23gTUNqx0
EHA.3704@.tk2msftngp13.phx.gbl...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initiali
ze
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.|||hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initial
ize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:

>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initializ
e
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
>

LinkeServer Problems...

Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.
That's an initialization failure...a pretty generic OLEDB error. Sounds like you have yet to configure the Security mappings correctly. If you are attempting to use Windows Authentication Delegation, you have a LOT of work ahead of you to configure Kerberos correctly for delegation.
You have to set the SPN for the SS server, the SS service accounts, and make sure that the accounts are marked correctly for delegation purposes.
Sincerely,
Anthony Thomas

"Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.
|||hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>
|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:

>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>
|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:

LinkeServer Problems...

Hey NG,
I haved installed a linked server (SQL-Server200), but when I click on the
Table or views the following error occours:
--
Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
--
in my Firewall/Router I have open the Port 1433.
what should I do to make the LinkedServer running well.hey Anthony,
thanx for your answer, but my problem is that I dont't understand anything
of the "Security mappings" and the "Windows Authentication Delegation". I am
only familiar with the normal Windows-Users and -Groups administration.
I have never heard something about SPN (do you mean the Service Principle
Name )...
My question is: Is this a normal configuration I have to do, or the problems
come from a misconfiguration of my system?
Sincerely,
Hans Pickelmann
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> schrieb im Newsbeitrag
news:%23mQjqXL1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> That's an initialization failure...a pretty generic OLEDB error. Sounds
> like you have yet to configure the Security mappings correctly. If you
> are attempting to use Windows Authentication Delegation, you have a LOT of
> work ahead of you to configure Kerberos correctly for delegation.
> You have to set the SPN for the SS server, the SS service accounts, and
> make sure that the accounts are marked correctly for delegation purposes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hans Pickelmann" <info@.dotnetjunkies.de> wrote in message
> news:%23gTUNqx0EHA.3704@.tk2msftngp13.phx.gbl...
> Hey NG,
> I haved installed a linked server (SQL-Server200), but when I click on
> the
> Table or views the following error occours:
> --
> Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
> OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> in my Firewall/Router I have open the Port 1433.
> what should I do to make the LinkedServer running well.
>|||Hans,
Perhaps you installed the linked server incorrectly. Did you run
sp_addlinkedsrvlogin? If you are sure you've set up the server and
login correctly, see if this is the problem:
http://support.microsoft.com/kb/314530. By the way, from the error
message you're getting, it looks like you may have a trace flag enabled
to give more detailed error messages - be sure you want it on, since it
might cause some performance problems elsewhere.
Steve Kass
Drew University
Hans Pickelmann wrote:
>Hey NG,
>I haved installed a linked server (SQL-Server200), but when I click on the
>Table or views the following error occours:
>--
>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>returned 0x80004005: ].
>--
>in my Firewall/Router I have open the Port 1433.
>what should I do to make the LinkedServer running well.
>
>|||Hey Steve,
i installed the linked server with the wizard.
if have read that this Trace Flag gives me more infos about the error...
and i have executed "DBCC TRACEON (7300, 3604)"
after a while of testing I have executed the DBCC-Command "DBCC traceoff
(7300, 3604)"
but the detailed info comes again I have stopped and started the SQL-Server
but this detailed Errormessage comes again.
greetinx from Germany and thanx for your advice
Hans Pickelmann
"Steve Kass" <skass@.drew.edu> schrieb im Newsbeitrag
news:uERxqvZ1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hans,
> Perhaps you installed the linked server incorrectly. Did you run
> sp_addlinkedsrvlogin? If you are sure you've set up the server and login
> correctly, see if this is the problem:
> http://support.microsoft.com/kb/314530. By the way, from the error
> message you're getting, it looks like you may have a trace flag enabled to
> give more detailed error messages - be sure you want it on, since it might
> cause some performance problems elsewhere.
> Steve Kass
> Drew University
>
> Hans Pickelmann wrote:
>>Hey NG,
>>I haved installed a linked server (SQL-Server200), but when I click on the
>>Table or views the following error occours:
>>--
>>Fehler 7399: Der OLE DB-Provider 'SQLOLEDB' meldete einen Fehler.
>>OLE DB-Fehlertrace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
>>returned 0x80004005: ].
>>--
>>in my Firewall/Router I have open the Port 1433.
>>what should I do to make the LinkedServer running well.
>>

Linked views

I have a number of databases on the same SQL Server that all use tables from
a database called RestaurantMgr (also on ths server). I have been accessing
these table using linked views (eg. create view Branches as select BranchID,
BranchName from RestaurantMgr..Branches).
Is this the best way to do this ? Or should I be using something else like
replication, triggers to copy data accross, DTS ?
Linked views seem the simplest method, but if there is a reason not to use
this method, then I want to find out what it is.
Thanks, CraigIf these views aren't referencing large tables or they do not return large
result-sets, then this solution is good enough.
But for read-intensive operations it would be better to keep the actual data
in the same database - this way more appropriate indexes can be created if
necessary.
Of course changes to the original tables would need to be propagated to the
other databases through the use of triggers.
ML|||Thanks for the advice, ML.
When you say that data will "need to be propagated to the other databases
through the use of triggers", does that it is better to use triggers for thi
s
than DTS, replication or any other method.|||Well, triggers are immediate and simple to design. But the destination
database must be available to the trigger at the time of execution.
Maybe using replication might be more efficient. Of course it would need to
be immediate. But do consider whether you should allow the subscribers to
modify the data.
MLsql

Linked Views

I have linked to some SQL Views in my Access FrontEnd. The first time I link to the Views it asks for the Primary Key and I select one. If I refresh the links using the Linked Table Wizard in Access, they seem to loose what I have set as the Primary Key
. Is there any way around this?
Thanks!
You have two options:
--Don't use the wizard. Link in code (you'd programmatically delete
the old links and create new ones).
--If you want to use the wizard, delete the old linked views first,
and relink them from scratch.
--Mary
On Fri, 18 Jun 2004 13:24:02 -0700, Deb
<Deb@.discussions.microsoft.com> wrote:

>I have linked to some SQL Views in my Access FrontEnd. The first time I link to the Views it asks for the Primary Key and I select one. If I refresh the links using the Linked Table Wizard in Access, they seem to loose what I have set as the Primary Ke
y. Is there any way around this?
>Thanks!

Linked Views

I have linked to some SQL Views in my Access FrontEnd. The first time I link to the Views it asks for the Primary Key and I select one. If I refresh the links using the Linked Table Wizard in Access, they seem to loose what I have set as the Primary Key. Is there any way around this?
Thanks!You have two options:
--Don't use the wizard. Link in code (you'd programmatically delete
the old links and create new ones).
--If you want to use the wizard, delete the old linked views first,
and relink them from scratch.
--Mary
On Fri, 18 Jun 2004 13:24:02 -0700, Deb
<Deb@.discussions.microsoft.com> wrote:
>I have linked to some SQL Views in my Access FrontEnd. The first time I link to the Views it asks for the Primary Key and I select one. If I refresh the links using the Linked Table Wizard in Access, they seem to loose what I have set as the Primary Key. Is there any way around this?
>Thanks!

Linked Views

I have linked to some SQL Views in my Access FrontEnd. The first time I lin
k to the Views it asks for the Primary Key and I select one. If I refresh t
he links using the Linked Table Wizard in Access, they seem to loose what I
have set as the Primary Key
. Is there any way around this?
Thanks!You have two options:
--Don't use the wizard. Link in code (you'd programmatically delete
the old links and create new ones).
--If you want to use the wizard, delete the old linked views first,
and relink them from scratch.
--Mary
On Fri, 18 Jun 2004 13:24:02 -0700, Deb
<Deb@.discussions.microsoft.com> wrote:

>I have linked to some SQL Views in my Access FrontEnd. The first time I link to th
e Views it asks for the Primary Key and I select one. If I refresh the links using
the Linked Table Wizard in Access, they seem to loose what I have set as the Primary
Ke
y. Is there any way around this?
>Thanks!

Linked SQL 2000 server and views

I only have basic SQL knowledge and am trying to modify some previously
created views, the problem I have is the views were originally only accessing
tables on the local SQL 2000 server which was fine, but now one of the
databases has been moved to another server and I'm trying to modify the views
to collect data from remote tables although still SQL 2000.
I have added the other server as linked server used a username with a
default database as the one I need to access, so I can view the tables on the
linked server.
From some stuff I read it looked as though I could create view specifying a
remote field by
servername.databasename.owner.table.field but this doesn't work it just says
I can only have 3 prefixes.
Can someone tell how I can do this,
Thanks.
You certainly can create a view referencing a linked server; BOL has an
example in the discussion about partitioned views (under the topic "create
view"). I suspect that there is an error in the syntax you are using to
create / alter the view. Post the actual statement you are using to create
the view and the complete error message; I'm sure someone will be able to
identify the problem.
"Marcus" <Marcus@.discussions.microsoft.com> wrote in message
news:7E3420EF-12AB-47F5-931B-02F045D61DF5@.microsoft.com...
> I only have basic SQL knowledge and am trying to modify some previously
> created views, the problem I have is the views were originally only
accessing
> tables on the local SQL 2000 server which was fine, but now one of the
> databases has been moved to another server and I'm trying to modify the
views
> to collect data from remote tables although still SQL 2000.
> I have added the other server as linked server used a username with a
> default database as the one I need to access, so I can view the tables on
the
> linked server.
> From some stuff I read it looked as though I could create view specifying
a
> remote field by
> servername.databasename.owner.table.field but this doesn't work it just
says
> I can only have 3 prefixes.
> Can someone tell how I can do this,
> Thanks.
|||This is the original view statement I have for getting the data from the
database Landesk_Dmart. I am trying to change it to get data from a remote
server hlflvlandesk8 from a database called landesk8. I have added
hlflvlandesk8 as a linked database with a username and password that defaults
to the landesk8 database. The owner of the table is ld8admin.
I tried referencing by using hlflvlandesk8.landesk8.ld8admin.Computer.<field
name>
which doesn't work.
What do I need to do?
CREATE VIEW dbo.CompSystem_Comp
AS
SELECT Landesk_Dmart.Landesk.Computer.[Computer_Idn],
Landesk_Dmart.Landesk.Computer.DeviceName,
Landesk_Dmart.Landesk.Computer.LoginName,
Landesk_Dmart.Landesk.Computer.Model,
Landesk_Dmart.Landesk.Computer.Manufacturer,
Landesk_Dmart.Landesk.CompSystem.AssetTag,
Landesk_Dmart.Landesk.Computer.Type,
'LastScan' = convert(varchar(12),dateadd(second,
convert(int,HWLastScanDate)-14400,'01/01/1970'), 101)
FROM
Landesk_Dmart.Landesk.Computer
INNER JOIN
Landesk_Dmart.Landesk.CompSystem ON
Landesk_Dmart.Landesk.Computer.Computer_Idn =
Landesk_dmart.Landesk.CompSystem.Computer_Idn
|||Just off the top of my head and without any testing. Try giving an alias
to your tables and using the alias to qualify the columns. Second, I assume
that you are using QA or some similar tool to create the view - in other
words, don't try to create the view using EM and its view editor.
"Marcus Bentley" <MarcusBentley@.discussions.microsoft.com> wrote in message
news:8ECBA77F-70EA-4E01-AF5B-72D7248A7F81@.microsoft.com...
> This is the original view statement I have for getting the data from the
> database Landesk_Dmart. I am trying to change it to get data from a remote
> server hlflvlandesk8 from a database called landesk8. I have added
> hlflvlandesk8 as a linked database with a username and password that
defaults
> to the landesk8 database. The owner of the table is ld8admin.
> I tried referencing by using
hlflvlandesk8.landesk8.ld8admin.Computer.<field
> name>
> which doesn't work.
> What do I need to do?
> CREATE VIEW dbo.CompSystem_Comp
> AS
> SELECT Landesk_Dmart.Landesk.Computer.[Computer_Idn],
> Landesk_Dmart.Landesk.Computer.DeviceName,
> Landesk_Dmart.Landesk.Computer.LoginName,
> Landesk_Dmart.Landesk.Computer.Model,
> Landesk_Dmart.Landesk.Computer.Manufacturer,
> Landesk_Dmart.Landesk.CompSystem.AssetTag,
> Landesk_Dmart.Landesk.Computer.Type,
> 'LastScan' = convert(varchar(12),dateadd(second,
> convert(int,HWLastScanDate)-14400,'01/01/1970'), 101)
> FROM
> Landesk_Dmart.Landesk.Computer
> INNER JOIN
> Landesk_Dmart.Landesk.CompSystem ON
> Landesk_Dmart.Landesk.Computer.Computer_Idn =
> Landesk_dmart.Landesk.CompSystem.Computer_Idn

servers and DROP TABLE statement

I have a procedure that loops through the sysdatabases and information
scehma views to "clean up" drop tables that are no longer used. To
consolidate things the procedure preforms this task remotely using linked
servers. The only problem is the drop table statement is "drop table
[server].[databasename].[owner].[tablename]" which errors with "Server: Msg
117, Level 15, State 1, Line 1 The object name 'server.database.dbo.'
contains more than the maximum number of prefixes. The maximum is 2."
Additionally the system function DatabaseProperty() that I am using to
evaluate if the database is offline or readonly before trying to drop the
table retrurns null because it can not evaluate the status of a linked
server database properly. Is this a limitation of Linked Servers or is there
a linked server property that can be set to support 4 part naming
convention.
Any help or suggestions are appreciated.
Thanks.You can't do DDL over 4-part naming. Consider building the DROP in a variable and use sp_executesql
(on the linked server) to execute the statement:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick Butler" <rick.butler@.technekes.com> wrote in message
news:OCQ%23$jPmFHA.1044@.tk2msftngp13.phx.gbl...
>I have a procedure that loops through the sysdatabases and information scehma views to "clean up"
>drop tables that are no longer used. To consolidate things the procedure preforms this task
>remotely using linked servers. The only problem is the drop table statement is "drop table
>[server].[databasename].[owner].[tablename]" which errors with "Server: Msg 117, Level 15, State 1,
>Line 1 The object name 'server.database.dbo.' contains more than the maximum number of prefixes.
>The maximum is 2." Additionally the system function DatabaseProperty() that I am using to evaluate
>if the database is offline or readonly before trying to drop the table retrurns null because it can
>not evaluate the status of a linked server database properly. Is this a limitation of Linked
>Servers or is there a linked server property that can be set to support 4 part naming convention.
> Any help or suggestions are appreciated.
> Thanks.
>

servers and DROP TABLE statement

I have a procedure that loops through the sysdatabases and information
scehma views to "clean up" drop tables that are no longer used. To
consolidate things the procedure preforms this task remotely using linked
servers. The only problem is the drop table statement is "drop table
[server].[databasename].[owner].[tablename]" which errors with "Server: Msg
117, Level 15, State 1, Line 1 The object name 'server.database.dbo.'
contains more than the maximum number of prefixes. The maximum is 2."
Additionally the system function DatabaseProperty() that I am using to
evaluate if the database is offline or readonly before trying to drop the
table retrurns null because it can not evaluate the status of a linked
server database properly. Is this a limitation of Linked Servers or is there
a linked server property that can be set to support 4 part naming
convention.
Any help or suggestions are appreciated.
Thanks.
You can't do DDL over 4-part naming. Consider building the DROP in a variable and use sp_executesql
(on the linked server) to execute the statement:
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick Butler" <rick.butler@.technekes.com> wrote in message
news:OCQ%23$jPmFHA.1044@.tk2msftngp13.phx.gbl...
>I have a procedure that loops through the sysdatabases and information scehma views to "clean up"
>drop tables that are no longer used. To consolidate things the procedure preforms this task
>remotely using linked servers. The only problem is the drop table statement is "drop table
>[server].[databasename].[owner].[tablename]" which errors with "Server: Msg 117, Level 15, State 1,
>Line 1 The object name 'server.database.dbo.' contains more than the maximum number of prefixes.
>The maximum is 2." Additionally the system function DatabaseProperty() that I am using to evaluate
>if the database is offline or readonly before trying to drop the table retrurns null because it can
>not evaluate the status of a linked server database properly. Is this a limitation of Linked
>Servers or is there a linked server property that can be set to support 4 part naming convention.
> Any help or suggestions are appreciated.
> Thanks.
>

servers and DROP TABLE statement

I have a procedure that loops through the sysdatabases and information
scehma views to "clean up" drop tables that are no longer used. To
consolidate things the procedure preforms this task remotely using linked
servers. The only problem is the drop table statement is "drop table
[server].[databasename].[owner].[tablename]" which errors wi
th "Server: Msg
117, Level 15, State 1, Line 1 The object name 'server.database.dbo.'
contains more than the maximum number of prefixes. The maximum is 2."
Additionally the system function DatabaseProperty() that I am using to
evaluate if the database is offline or readonly before trying to drop the
table retrurns null because it can not evaluate the status of a linked
server database properly. Is this a limitation of Linked Servers or is there
a linked server property that can be set to support 4 part naming
convention.
Any help or suggestions are appreciated.
Thanks.You can't do DDL over 4-part naming. Consider building the DROP in a variabl
e and use sp_executesql
(on the linked server) to execute the statement:
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick Butler" <rick.butler@.technekes.com> wrote in message
news:OCQ%23$jPmFHA.1044@.tk2msftngp13.phx.gbl...
>I have a procedure that loops through the sysdatabases and information sceh
ma views to "clean up"
>drop tables that are no longer used. To consolidate things the procedure pr
eforms this task
>remotely using linked servers. The only problem is the drop table statement
is "drop table
>[server].[databasename].[owner].[tablename]" which errors w
ith "Server: Msg 117, Level 15, State 1,
>Line 1 The object name 'server.database.dbo.' contains more than the maximu
m number of prefixes.
>The maximum is 2." Additionally the system function DatabaseProperty() tha
t I am using to evaluate
>if the database is offline or readonly before trying to drop the table retr
urns null because it can
>not evaluate the status of a linked server database properly. Is this a lim
itation of Linked
>Servers or is there a linked server property that can be set to support 4 p
art naming convention.
> Any help or suggestions are appreciated.
> Thanks.
>

servers

I can create the views in an SQL server based on the tables/views in a
linked server. I can retrieve data using these views. But I cannot save
them. The server freeses when I try to save these views. What may be wrong
here?
Thanks.Not sure what you are saving if you have already created the
view. Sounds like you are working though the GUI tools in
Enterprise Manager maybe?
Try creating the view with normal ddl statements in Query
Analyzer - e.g CREATE VIEW YourView......
-Sue
On Thu, 20 Apr 2006 11:21:44 -0400, "Vik"
<viktorum@.==yahoo.com==> wrote:
>I can create the views in an SQL server based on the tables/views in a
>linked server. I can retrieve data using these views. But I cannot save
>them. The server freeses when I try to save these views. What may be wrong
>here?
>Thanks.
>

servers

I can create the views in an SQL server based on the tables/views in a
linked server. I can retrieve data using these views. But I cannot save
them. The server freeses when I try to save these views. What may be wrong
here?
Thanks.Not sure what you are saving if you have already created the
view. Sounds like you are working though the GUI tools in
Enterprise Manager maybe?
Try creating the view with normal ddl statements in Query
Analyzer - e.g CREATE VIEW YourView......
-Sue
On Thu, 20 Apr 2006 11:21:44 -0400, "Vik"
<viktorum@.==yahoo.com==> wrote:

>I can create the views in an SQL server based on the tables/views in a
>linked server. I can retrieve data using these views. But I cannot save
>them. The server freeses when I try to save these views. What may be wrong
>here?
>Thanks.
>

server, stored procedure and Views (Heterogeneous Errors)

I have linked to another SQL Instance, created a view and a stored procedure
to access it. This works great through MS Query, but not via SQL Triggers or
through calling through code in another application.
The views and sprocs create without errors, it's only when running them.
Any Ideas?Ray
Can you show us how you call the statemnet?
"Ray" <rayc@.rsc.com> wrote in message
news:FEEE24DA-571B-4C46-887B-33D4F772553E@.microsoft.com...
>I have linked to another SQL Instance, created a view and a stored
>procedure
> to access it. This works great through MS Query, but not via SQL Triggers
> or
> through calling through code in another application.
> The views and sprocs create without errors, it's only when running them.
> Any Ideas?|||you will have to use the four part naming convention
linkedservername.database.owner.object
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ray" wrote:

> I have linked to another SQL Instance, created a view and a stored procedu
re
> to access it. This works great through MS Query, but not via SQL Triggers
or
> through calling through code in another application.
> The views and sprocs create without errors, it's only when running them.
> Any Ideas?

server, stored procedure and Views (Heterogeneous Error

I am using SQL passthough and simply running an "exec mysproc".
Then I simply run a "Select * from myView", which is a "Select a,b,c from
xxx.xxx.dbo.table"
This all runs vis MS Query, but if I turn off ANSI NULLS and WARNINGS in the
MS Query options, I get the same error.
"Uri Dimant" wrote:

> Ray
> Can you show us how you call the statemnet?
>
> "Ray" <rayc@.rsc.com> wrote in message
> news:FEEE24DA-571B-4C46-887B-33D4F772553E@.microsoft.com...
>
>Ray
You said that it failed from within a trigger. Try create a trigger
withANSI NULLS and WARNINGS set ON
What is your statement to fire a trigger?
"Ray" <rayc@.rsc.com> wrote in message
news:3CA11337-A751-449C-815A-35AEF75416C8@.microsoft.com...
>I am using SQL passthough and simply running an "exec mysproc".
> Then I simply run a "Select * from myView", which is a "Select a,b,c from
> xxx.xxx.dbo.table"
> This all runs vis MS Query, but if I turn off ANSI NULLS and WARNINGS in
> the
> MS Query options, I get the same error.
> "Uri Dimant" wrote:
>

server Views causing blocking

We have 2 SQL Server 2000 machines. One has a star-schema data
warehouse with fact table, dimension tables, etc. The other SQL Server
has a data mart. The second SQL Server has a linked server set up to
the first server and we have views that grab information from some of
the dimension tables.
Occasionally, when we are doing stuff to the fact table in the data
warehouse (inserts, drop indexes, etc), and we try to do a select from
one of the views on the other sever that only select against the
dimension tables, it causes blocking.
Does it make sense that a linked-server view accessing one table could
cause blocking for an action going against another table?Sure, that makes perfect sense. The view applies a shared lock while
reading the data, and it's blocking your writes.
Unfortunately, there's not much you can do, if you're performing data
modifications during production hours. In a lot of DW scenarios, data would
be loaded at an off-time (e.g. in the middle of the night), and data would
not change at all during the day. In those situations, blocking is not an
issue. In more real-time situations, when working with SQL Server 2000, you
have a choice: Do I want to increase concurrency (decrease blocking), or do
I want to make sure all results are consistent (that is, return committed
data)? If you don't care about consistency, you can try the READ
UNCOMMITTED isolation level, which does not take locks for reads or honor
locks for writes. The danger is that you can get some data back that may not
be consistent -- for instance, if you're doing a query to return both detail
and aggregate data, and an update is occurring during the query, your
aggregation and detail data may not match. Some applications can live with
this, however...
Your other choice may be to replicate or log ship the data over to your data
mart instead of relying on the linked view. Would that be an option for
you?
Luckily, in SQL Server 2005, this will become much less of a problem thanks
to the SNAPSHOT isolation level, which provides similar non-blocking
benefits to the READ UNCOMMITTED isolation level, with consistent results.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
<tadams@.generalcable.com> wrote in message
news:1123849107.004880.142490@.o13g2000cwo.googlegroups.com...
> We have 2 SQL Server 2000 machines. One has a star-schema data
> warehouse with fact table, dimension tables, etc. The other SQL Server
> has a data mart. The second SQL Server has a linked server set up to
> the first server and we have views that grab information from some of
> the dimension tables.
> Occasionally, when we are doing stuff to the fact table in the data
> warehouse (inserts, drop indexes, etc), and we try to do a select from
> one of the views on the other sever that only select against the
> dimension tables, it causes blocking.
> Does it make sense that a linked-server view accessing one table could
> cause blocking for an action going against another table?
>|||Adam,
Thanks for the advice.
I will look into whether we can use the READ UNCOMMITTED isolation
level.
We are also looking at moving the dimensions local to each server. Not
what we would like to do, but it may be our only hope of improving
performance.
Thanks,
Tommy