linking two datasets: 2012

Friday, March 30, 2012

Linking Server to MySql

Hello All,
I am stuck with this really frustrating problem...

I am using a linked server to run a qeury to mysql with sp_addlinked server etc...

The problem i am having is I am using this in a trigger with sql server 2000 and it always errors there for the row doesnt get inserted.

the error is
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'insert into mail_relay(ip_addr,timestamp) values('myip',1039087122)'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

With mysql I cant use 4 part names cause I get this error
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

So I have to run
select * from openquery(MailServer,'insert into mail_relay(ip_addr,timestamp) values(''myip'',1039087122)')

and mysql also doesnt support multiple queries in the one statement so i cant do this to stop the error:
select * from openquery(MailServer,'insert into mail_relay(ip_addr,timestamp) values(''myip'',1039087122);select null')

so my question is,
I want to be able to stop the error message being reported in my trigger so the row still gets inserted

I am sure it is possible.. I really hope so... any help would be really greatly appreciated

thanks guysCan you execute this insert statement out of a trigger? Does it still fail with the same error?

Could you also list the settings of the link.|||Originally posted by zmichailov
Can you execute this insert statement out of a trigger? Does it still fail with the same error?

Could you also list the settings of the link.

Outside of the trigger the error still occurs...
Here is a the way i set the connection up...

EXEC sp_addlinkedserver
'MailServer',
'',
'MSDASQL',
NULL,
NULL,
'DSN=MailServer;UID=myuser;pwd=mypass;'
EXEC sp_addlinkedsrvlogin
@.rmtsrvname='MailServer',
@.useself=false,
@.locallogin=null,
@.rmtuser='myuser',
@.rmtpassword='mypass'
Exec sp_serveroption 'MailServer', 'data access', 'true'|||Two more questions:
1. What is the defualt database (catalog) for the remote login? Does MySQL have only one database per server?

2. What happens when you execute this statement out of any trigger and transaction:
select *
from MailServer...mail_relay|||Originally posted by zmichailov
Two more questions:
1. What is the defualt database (catalog) for the remote login? Does MySQL have only one database per server?

2. What happens when you execute this statement out of any trigger and transaction:
select *
from MailServer...mail_relay

Hey, Sorry for delay in reply,
in answer to these questions

1. The default database on the DSN is vpopmail, I am using the latest MyODBC driver for this...
In mySql you can have as many dbs as your want to on any server.

2. i get the following error message
Server: Msg 7313, Level 16, State 1, Line 17
Invalid schema or catalog specified for provider 'MSDASQL'.

i also found MyOLEDB ole db provider but It would create an instanmce of it it just errors saying

Server: Msg 7302, Level 16, State 1, Line 17
Could not create an instance of OLE DB provider 'MySQLProv'.

Well any help would be great, thanks again|||i have exactly same problem, do you have some solution?|||This may help.

http://bside.typepad.com/lifebsideben/2003/08/mysql_mssql_lin.htmlsql

Linking Server to a different Domain

Hi:
I am trying to create a Linked Server to SQL Server but residing in a differ
ent domain. It is giving me a error message that It does not recognize this
SQL Server. Please let me know how should create this. Any help is greatl
y appreciated.
ThanksHi Sal
Can you access the other server via the query analyzer usingSQL Server
authentication?
-Paritosh
"Sal" <anonymous@.discussions.microsoft.com> wrote in message
news:7158838B-EBD4-4B90-ACB2-47E443DEEAE2@.microsoft.com...
> Hi:
> I am trying to create a Linked Server to SQL Server but residing in a
different domain. It is giving me a error message that It does not
recognize this SQL Server. Please let me know how should create this. Any
help is greatly appreciated.
> Thanks
>|||Sal,
you probably need to add an entry into your hosts file. Once it's added,
test you can ping the servername. Also, you'll need to use SQL Server logins
or pass-through authentication (or the windows guest login).
HTH,
Paul Ibison|||Yes. I acn connect with query analyzer.|||On Thu, 22 Apr 2004 08:31:04 -0700, Sal
<anonymous@.discussions.microsoft.com> wrote:

>Hi:
>I am trying to create a Linked Server to SQL Server but residing in a diffe
rent domain.
>It is giving me a error message that It does not recognize this SQL Server.
>Please let me know how should create this. Any help is greatly appreciated
.
>
Mapping a drive from the one Server to the other using DOMAIN1\USER1
syntax often gets around the fact the user accounts on the two
machines are in different domains as does making a user with the same
name and password in both domains.
C|||I was able to Create Linked Server by creating an ODBC on the server and the
n using that ODBC to create Linked Server as Data Source but It would not al
low me to create Linked Server by specifying the SQL Server Name. It someho
w does not recognize the SQ
L Server on a different server.
For now I got to work by using the ODBC as data source for Linked Server but
if you were able to create by just specifying the sql server name, please l
et me know.
Thanks
Sal

Linking Server to a different Domain

Hi:
I am trying to create a Linked Server to SQL Server but residing in a different domain. It is giving me a error message that It does not recognize this SQL Server. Please let me know how should create this. Any help is greatly appreciated.
Thanks
Hi Sal
Can you access the other server via the query analyzer usingSQL Server
authentication?
-Paritosh
"Sal" <anonymous@.discussions.microsoft.com> wrote in message
news:7158838B-EBD4-4B90-ACB2-47E443DEEAE2@.microsoft.com...
> Hi:
> I am trying to create a Linked Server to SQL Server but residing in a
different domain. It is giving me a error message that It does not
recognize this SQL Server. Please let me know how should create this. Any
help is greatly appreciated.
> Thanks
>
|||Sal,
you probably need to add an entry into your hosts file. Once it's added,
test you can ping the servername. Also, you'll need to use SQL Server logins
or pass-through authentication (or the windows guest login).
HTH,
Paul Ibison
|||Yes. I acn connect with query analyzer.
|||On Thu, 22 Apr 2004 08:31:04 -0700, Sal
<anonymous@.discussions.microsoft.com> wrote:

>Hi:
>I am trying to create a Linked Server to SQL Server but residing in a different domain.
>It is giving me a error message that It does not recognize this SQL Server.
>Please let me know how should create this. Any help is greatly appreciated.
>
Mapping a drive from the one Server to the other using DOMAIN1\USER1
syntax often gets around the fact the user accounts on the two
machines are in different domains as does making a user with the same
name and password in both domains.
C
|||I was able to Create Linked Server by creating an ODBC on the server and then using that ODBC to create Linked Server as Data Source but It would not allow me to create Linked Server by specifying the SQL Server Name. It somehow does not recognize the SQ
L Server on a different server.
For now I got to work by using the ODBC as data source for Linked Server but if you were able to create by just specifying the sql server name, please let me know.
Thanks
Sal

Linking Server to a different Domain

Hi
I am trying to create a Linked Server to SQL Server but residing in a different domain. It is giving me a error message that It does not recognize this SQL Server. Please let me know how should create this. Any help is greatly appreciated
ThankHi Sal
Can you access the other server via the query analyzer usingSQL Server
authentication?
-Paritosh
"Sal" <anonymous@.discussions.microsoft.com> wrote in message
news:7158838B-EBD4-4B90-ACB2-47E443DEEAE2@.microsoft.com...
> Hi:
> I am trying to create a Linked Server to SQL Server but residing in a
different domain. It is giving me a error message that It does not
recognize this SQL Server. Please let me know how should create this. Any
help is greatly appreciated.
> Thanks
>|||Sal,
you probably need to add an entry into your hosts file. Once it's added,
test you can ping the servername. Also, you'll need to use SQL Server logins
or pass-through authentication (or the windows guest login).
HTH,
Paul Ibison|||Yes. I acn connect with query analyzer.|||On Thu, 22 Apr 2004 08:31:04 -0700, Sal
<anonymous@.discussions.microsoft.com> wrote:
>Hi:
>I am trying to create a Linked Server to SQL Server but residing in a different domain.
>It is giving me a error message that It does not recognize this SQL Server.
>Please let me know how should create this. Any help is greatly appreciated.
>
Mapping a drive from the one Server to the other using DOMAIN1\USER1
syntax often gets around the fact the user accounts on the two
machines are in different domains as does making a user with the same
name and password in both domains.
C|||I was able to Create Linked Server by creating an ODBC on the server and then using that ODBC to create Linked Server as Data Source but It would not allow me to create Linked Server by specifying the SQL Server Name. It somehow does not recognize the SQL Server on a different server.
For now I got to work by using the ODBC as data source for Linked Server but if you were able to create by just specifying the sql server name, please let me know.
Thank
Sal

Linking Server problem

Hi masters:
I get the following error when i try to create a stored procedure that query
an SQL linked Server...
[SQLSTATE 42000]
"Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures
consistent query semantics. Enable these options and then reissue your query."
this is my stored procedure script
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
The funny thing is that the same code run without problems when i query the
linked server from the Query Analyzer... and i don't get the above error.
Please help
Thanks
RodrigoHi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer|||This is a multi-part message in MIME format.
--060600060209060500070603
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
No, this is wrong (please verify your guesses before providing them).
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the same
values for the ANSI_NULLS & ANSI_WARNINGS settings. Both servers have
to have them enabled (turned on).
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc and
used when the proc is executed. So, when you create or alter the proc
(with the linked server reference) make sure that "SET ANSI_NULLS ON" is
there before the ALTER PROC or CREATE PROC statement (just to be sure).
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA session
it used ANSI_NULLS on but when you executed the proc from that same
session it used the ANSI_NULLS value that was saved with the proc (ie.
ANSI_NULLS off). That's my guess.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:
>Hi rodrigo,
>Stored procedure run locally means it will execute on the server were u
>will create it .
>so u cannot use linkedserver in a procedure.
>u have to run it outside the procedure.
>if u wanted to schedule it write ur code in dts or job window.
>hope this help
>from
>killer
>
>
--060600060209060500070603
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>No, this is wrong (please verify your guesses before providing
them). 
 
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc. 
 
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the
same values for the ANSI_NULLS & ANSI_WARNINGS settings. Both
servers have to have them enabled (turned on). 
 
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc
and used when the proc is executed. So, when you create or alter the
proc (with the linked server reference) make sure that "SET ANSI_NULLS
ON" is there before the ALTER PROC or CREATE PROC statement (just to be
sure). 
 
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA
session it used ANSI_NULLS on but when you executed the proc from that
same session it used the ANSI_NULLS value that was saved with the proc
(ie. ANSI_NULLS off). That's my guess. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
doller wrote:
<blockquote
cite="mid1123641009.412382.267360@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">Hi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer
</pre>
</blockquote>
</body>
</html>
--060600060209060500070603--|||You should put the Ansi_nulls and warnings settings in your sp...you can use
something like this in EM's sp-property window if that's what you're using:
---
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
---
Greets, Lee-Z
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:F1CE4631-A9E7-4CB9-A560-9D1EDAE0774F@.microsoft.com...
> Hi masters:
> I get the following error when i try to create a stored procedure that
> query
> an SQL linked Server...
> [SQLSTATE 42000]
> "Heterogeneous queries require the ANSI_NULLS and
> ANSI_WARNINGS options to be set for the connection. This ensures
> consistent query semantics. Enable these options and then reissue your
> query."
> this is my stored procedure script
> CREATE PROCEDURE TEST1 AS
> select * from openquery(srvmis,'select * from PC_agrupa_cde')
> GO
> The funny thing is that the same code run without problems when i query
> the
> linked server from the Query Analyzer... and i don't get the above error.
> Please help
> Thanks
> Rodrigosql

Linking Server problem

Linking Seperate Datasets

I am wondering if it is possible to have 2 datasets in the rdl report that
are linked together by some key.
Or do I just have to combine the information in the two datasets using a
query into one dataset. If at all possible i would like to use two datasets
that are linked.You can have as many datasets as you like in one rdl report. However,
generally you can only use one dataset in a given table, matrix, list, etc.
If you require to present things in different ways you may be best to use
one dataset with different sorting, filtering and grouping.
HTH,
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Arlyn" wrote:
> I am wondering if it is possible to have 2 datasets in the rdl report that
> are linked together by some key.
> Or do I just have to combine the information in the two datasets using a
> query into one dataset. If at all possible i would like to use two datasets
> that are linked.
>
>

Linking Scheduled Jobs

I currently have 4 scheduled jobs which are all scheduled for 4
different days and was wondering if there was anyway these could be be
stopped automatically in the event of a job failure, consider the
example below

Monday
Job runs to produce a file requesting BACS Payments.

Wednesday
Job runs to create a file that can be used to allocate the money
recieved from the bacs payment to a particular service.

If the job that runs on monday fails it would be nice for the job that
is due to run on wednesday to be automatically suspended but I can't
find anyway for this to be done.

Any help in this problem would be appreciated

Stuart FergusonStuart Ferguson wrote:
> I currently have 4 scheduled jobs which are all scheduled for 4
> different days and was wondering if there was anyway these could be be
> stopped automatically in the event of a job failure, consider the
> example below
> Monday
> Job runs to produce a file requesting BACS Payments.
> Wednesday
> Job runs to create a file that can be used to allocate the money
> recieved from the bacs payment to a particular service.
> If the job that runs on monday fails it would be nice for the job that
> is due to run on wednesday to be automatically suspended but I can't
> find anyway for this to be done.
> Any help in this problem would be appreciated
> Stuart Ferguson

I'm not sure if there is a way to do exactly what you want, but you
could do a workaround by creating a table that holds some value to
indicate a successful job. The first part of the Wed. job could be to
check the value of that table, if it isn't what it is supposed to be, it
can terminate.

For example:

CREATE TABLE Job_Status (Completed DATETIME NOT NULL)

Monday's Job:

TRUNCATE TABLE Job_Status
-- Run Monday's job
As last step of Monday's job:
INSERT INTO Job_Status (Completed) VALUES (CURRENT_TIMESTAMP)

Wednesday's Job:

IF EXISTS (SELECT * FROM Job_Status)
-- Do Job

If you wanted to get fancy you could make the code more complex to check
for job history and keep a record of when jobs fail or not, etc.

Zach|||Stuart Ferguson (stuart_ferguson1@.btinternet.com) writes:
> I currently have 4 scheduled jobs which are all scheduled for 4
> different days and was wondering if there was anyway these could be be
> stopped automatically in the event of a job failure, consider the
> example below
> Monday
> Job runs to produce a file requesting BACS Payments.
> Wednesday
> Job runs to create a file that can be used to allocate the money
> recieved from the bacs payment to a particular service.
> If the job that runs on monday fails it would be nice for the job that
> is due to run on wednesday to be automatically suspended but I can't
> find anyway for this to be done.

As Zach says, you would somehow implement some sort of your own buisness
logic.

One way, would have two extra steps in the Monday job. When step 1 succeeds,
the job should proceed to step 2 which enable the job for Wednesday. When
the step fails, the should proceed to step 3 to disable the Wednesday.

To enable/disable jobs, you would have to use the job-control stored
procedures that are described in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Linking SAS-datasets to an Access Project 2003

I installed the SAS ODBC-driver 9.1 and can choose the SAS tables (datasets)
but then I get this message:
"Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access
this provider through a linked server."
Linked server?
What do I do? Is it possible to link SAS datasets to an Access Project or to
SQL Server 2000?
By definition, Access Projects are a presentation layer that store all
their data in a SQL Server backend. You should simply point SAS to the
real SQL backend database
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Linking SAS-datasets to an Access Project 2003

I installed the SAS ODBC-driver 9.1 and can choose the SAS tables (datasets)
but then I get this message:
"Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access
this provider through a linked server."
Linked server?
What do I do? Is it possible to link SAS datasets to an Access Project or to
SQL Server 2000?By definition, Access Projects are a presentation layer that store all
their data in a SQL Server backend. You should simply point SAS to the
real SQL backend database
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbellsql

Linking same table

I have a table that looks like this:
Acct Reason
1 MA01
1 MA18
1 MA19
2 MA01
2 MA03
3 MA01
3 MA03
3 MA07
4 MA01
4 MA03
In the above example, if an account has a MA18 or MA07, I DO NOT want the
account. Therefore, the resulting data would be account 2 and 4.
The above table needs to be a parent table (i.e. demographic table and
orders would be a child tables).
How do I create a SQL statement that returns any account that DOES NOT have
a MA18 or MA07? Do I create a view? This is way over my head.
THANKS,
MEGSelect * from account a1
where not exists (select * from account a2 where a1.acct = a2.acct and
(a2.Reason = 'MA18' or a2.Reason = 'MA07'))
Daniel
"MEG" <MEG@.discussions.microsoft.com> a crit dans le message de
news:054DD409-4897-414F-A175-E0084428547B@.microsoft.com...
> I have a table that looks like this:
> Acct Reason
> 1 MA01
> 1 MA18
> 1 MA19
> 2 MA01
> 2 MA03
> 3 MA01
> 3 MA03
> 3 MA07
> 4 MA01
> 4 MA03
> In the above example, if an account has a MA18 or MA07, I DO NOT want the
> account. Therefore, the resulting data would be account 2 and 4.
> The above table needs to be a parent table (i.e. demographic table and
> orders would be a child tables).
> How do I create a SQL statement that returns any account that DOES NOT
have
> a MA18 or MA07? Do I create a view? This is way over my head.
> THANKS,
> MEG|||SELECT *
FROM Table O
WHERE NOT EXISTS( SELECT 1 FROM Table T
WHERE T.Acct = O.acct
AND T.Reason IN('MA18','MA07'))
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"MEG" <MEG@.discussions.microsoft.com> wrote in message
news:054DD409-4897-414F-A175-E0084428547B@.microsoft.com...
>I have a table that looks like this:
> Acct Reason
> 1 MA01
> 1 MA18
> 1 MA19
> 2 MA01
> 2 MA03
> 3 MA01
> 3 MA03
> 3 MA07
> 4 MA01
> 4 MA03
> In the above example, if an account has a MA18 or MA07, I DO NOT want the
> account. Therefore, the resulting data would be account 2 and 4.
> The above table needs to be a parent table (i.e. demographic table and
> orders would be a child tables).
> How do I create a SQL statement that returns any account that DOES NOT
> have
> a MA18 or MA07? Do I create a view? This is way over my head.
> THANKS,
> MEG|||A table can join to an aliased version of itself.
"MEG" <MEG@.discussions.microsoft.com> wrote in message
news:054DD409-4897-414F-A175-E0084428547B@.microsoft.com...
> I have a table that looks like this:
> Acct Reason
> 1 MA01
> 1 MA18
> 1 MA19
> 2 MA01
> 2 MA03
> 3 MA01
> 3 MA03
> 3 MA07
> 4 MA01
> 4 MA03
> In the above example, if an account has a MA18 or MA07, I DO NOT want the
> account. Therefore, the resulting data would be account 2 and 4.
> The above table needs to be a parent table (i.e. demographic table and
> orders would be a child tables).
> How do I create a SQL statement that returns any account that DOES NOT
have
> a MA18 or MA07? Do I create a view? This is way over my head.
> THANKS,
> MEG|||"MEG" <MEG@.discussions.microsoft.com> wrote in message
news:054DD409-4897-414F-A175-E0084428547B@.microsoft.com...
> I have a table that looks like this:
> Acct Reason
> 1 MA01
> 1 MA18
> 1 MA19
> 2 MA01
> 2 MA03
> 3 MA01
> 3 MA03
> 3 MA07
> 4 MA01
> 4 MA03
> In the above example, if an account has a MA18 or MA07, I DO NOT want the
> account. Therefore, the resulting data would be account 2 and 4.
Something like (untested):
SELECT DISTINCT Acct
FROM AcctTable
WHERE NOT EXISTS (SELECT * FROM AcctTable WHERE reason IN ('MA18','MA07'))
Good Luck,
Jim|||Is a VIEW and an ALIAS similiar other than a VIEW being stored and an ALIAS
created on the fly?
I am using Crystal Reports so I don't think I can create something on the fl
y.
THANKS,
MEG
"JohnnyAppleseed" wrote:

> A table can join to an aliased version of itself.
> "MEG" <MEG@.discussions.microsoft.com> wrote in message
> news:054DD409-4897-414F-A175-E0084428547B@.microsoft.com...
> have
>
>|||A table referenced in a query can be reassigned a new name.
For example select ... from MyTable as MT ..
I'm calling this a table alias. A view is just a method to store a query in
SQL Server, and it can contain a query with an alias, but not necessarily.
Below is a query I wrote for another guy this morning. In it he is wanting
to self join a table called [offline] back to itself and list those records
that exist for 2004 but not 2005. Notice that [offline] is left joined to
[offline] and each is referenced using a different alias (B04 vs. B05).
select
B04.booking_year,
B04.category1,
B04.category2
from
offline as B04
left join
offline as B05
on B05.booking_year = 2005 and
B05.category1 = B04.category1 and
B05.category2 = B04.category 2
where
B04.booking_year = 2004 and
B05.id is NULL
When using Crystal Reports, I highly reccomend that you store your queries
in a View or Stored Procedure, making them easier to manage and re-use
across report templates. There is nothing uglier than a project with two
dozen CR templates and each one has a slightly version of the same query.
"MEG" <MEG@.discussions.microsoft.com> wrote in message
news:913DD957-E0B3-4592-8FA4-CA3A2EEE92E3@.microsoft.com...
> Is a VIEW and an ALIAS similiar other than a VIEW being stored and an
ALIAS
> created on the fly?
> I am using Crystal Reports so I don't think I can create something on the
fly.
> THANKS,
> MEG
> "JohnnyAppleseed" wrote:
>
the|||Your reply was very beneficial. I agree that the view/stored procedure is
the best method.
In Crystal Reports, I don't know how to pass a parameter to the view/stored
procedure (i.e. in your example if you wanted to have a parameter for 2004
and one for 2005).
Any thoughts?
THANKS,
MEG
"JohnnyAppleseed" wrote:

> A table referenced in a query can be reassigned a new name.
> For example select ... from MyTable as MT ..
> I'm calling this a table alias. A view is just a method to store a query i
n
> SQL Server, and it can contain a query with an alias, but not necessarily.
> Below is a query I wrote for another guy this morning. In it he is wanting
> to self join a table called [offline] back to itself and list those records
> that exist for 2004 but not 2005. Notice that [offline] is left joined to
> [offline] and each is referenced using a different alias (B04 vs. B05).
> select
> B04.booking_year,
> B04.category1,
> B04.category2
> from
> offline as B04
> left join
> offline as B05
> on B05.booking_year = 2005 and
> B05.category1 = B04.category1 and
> B05.category2 = B04.category 2
> where
> B04.booking_year = 2004 and
> B05.id is NULL
> When using Crystal Reports, I highly reccomend that you store your queries
> in a View or Stored Procedure, making them easier to manage and re-use
> across report templates. There is nothing uglier than a project with two
> dozen CR templates and each one has a slightly version of the same query.
> "MEG" <MEG@.discussions.microsoft.com> wrote in message
> news:913DD957-E0B3-4592-8FA4-CA3A2EEE92E3@.microsoft.com...
> ALIAS
> fly.
> the
>
>|||Calling a SP or paramaterized view from a CR template is cumbersome. When I
worked with CR 8.5 and VB 6.0, I would define an external ADO Recordset,
load the recordset with data, and then bind the recordset to the report
design object. This also involves building a .TTX file that has the same
data structure as your recordset, and then binding the data source of the
template to that TTX file. It sounds complicated, but it's actually less
troublesome once you get everything setup. Using deja.com, search in the
*crystal* newsgroups for keyword "TTX", and you will find several
discussions. Also there are examples on the http://www.businessobjects.com/
support website.
"MEG" <MEG@.discussions.microsoft.com> wrote in message
news:0186A86C-82C3-4359-BF93-73FE1262CF02@.microsoft.com...
> Your reply was very beneficial. I agree that the view/stored procedure is
> the best method.
> In Crystal Reports, I don't know how to pass a parameter to the
view/stored
> procedure (i.e. in your example if you wanted to have a parameter for 2004
> and one for 2005).
> Any thoughts?
> THANKS,
> MEG
> "JohnnyAppleseed" wrote:
>
in
necessarily.
wanting
records
to
queries
query.
the
want
and
NOT

Linking Reports in different projects

Is there a way to link reports from one project to reports in a second
project?You do this from report manager. Open up the properties of the report you
want to create a linked report of and then specify the location the linked
report should go in.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sfibich" <sfibich@.pfgc.com> wrote in message
news:eQM9U%2334EHA.1692@.TK2MSFTNGP10.phx.gbl...
> Is there a way to link reports from one project to reports in a second
> project?|||Bruce L-C [MVP] wrote:
> You do this from report manager. Open up the properties of the report you
> want to create a linked report of and then specify the location the linked
> report should go in.
>
But this does not work when reports are in different projects or is
there something I am missing?|||The way it works for me is each project is a directory on the web server. If
I have a project/directory that I want the same report to show in both then
from report manager (not from the IDE, you can not do this from within VS)
follow the directions I gave you below. Try one, it will make sense once you
do one.
"sfibich" <sfibich@.pfgc.com> wrote in message
news:ugXBpP44EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Bruce L-C [MVP] wrote:
> > You do this from report manager. Open up the properties of the report
you
> > want to create a linked report of and then specify the location the
linked
> > report should go in.
> >
> But this does not work when reports are in different projects or is
> there something I am missing?|||Bruce L-C [MVP] wrote:
> The way it works for me is each project is a directory on the web server. If
> I have a project/directory that I want the same report to show in both then
> from report manager (not from the IDE, you can not do this from within VS)
> follow the directions I gave you below. Try one, it will make sense once you
> do one.
>
> "sfibich" <sfibich@.pfgc.com> wrote in message
> news:ugXBpP44EHA.1564@.TK2MSFTNGP09.phx.gbl...
>>Bruce L-C [MVP] wrote:
>>
>>You do this from report manager. Open up the properties of the report
> you
>>want to create a linked report of and then specify the location the
> linked
>>report should go in.
>>
>>But this does not work when reports are in different projects or is
>>there something I am missing?
>
>
Oh, now I see. Sorry I was so thick headed about this. This is going
to be a nightmare for me as I have many reports that need to be in sub
projects because of security reasons but most reports in one level need
to link down to the next level.
Thanks agian for you help.|||If I'm just creating a link from a textbox, I simply use the URL option of
the action property of the textbox to link to the report. You may still have
some security issues, but it works well for me.
"sfibich" wrote:
> Is there a way to link reports from one project to reports in a second
> project?
>

Linking reports

Does anyone know if it is possible to link two reports together?
For example, if you have a report showing customers with the number of
orders they have placed, can you then link to another report showing
details of the actual orders.
Hope this makes sense.
ChrisYes possible using drill through reports.
Amarnath
"chris.j.stubbs@.gmail.com" wrote:
> Does anyone know if it is possible to link two reports together?
> For example, if you have a report showing customers with the number of
> orders they have placed, can you then link to another report showing
> details of the actual orders.
> Hope this makes sense.
> Chris
>

Linking reports

Hello,
I am currently working on a project that involves tracking progress over several months. So, for the month of July, I need to show the results of June, May, etc. The easiest way I can think of doing this is to "connect" to the previous month's report. Can this be done? The only other options I can think of are:
A. have my July month recalculate all the values to the beginning of the year.
B. create an new table in my DB that will store the carry-over variables from the months before.
Any input would be appreciated.
No, you should make a query which give you all the summarized data. Then organize them into the report. And put a link in each month column to jump to the detailed report of the selected month.

The best solution is to design cubes and obtain data from it.

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

Jens K. Suessmeyer

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

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

Thanks,

Stara

|||

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

Jens K. Suessmeyer

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

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

Thanks,

Stara

|||

Regards,

Mike

Linking Oracle Servers in SQL Server 2005 Express

"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164125803.967272.173060@.f16g2000cwb.googlegr oups.com...
> Hi Everyone,
> I am trying to link an Oracle server to my instance of SQL Server 2005
> Express. I have used the following code to add the linked server:
> USE MASTER
> GO
> EXEC sp_addlinkedserver
> @.server = 'NameForLinkedServer',
> @.srvproduct = 'Oracle',
> @.provider = 'OraOLEDB.Oracle',
> @.datasrc = 'ActualNameOfOracleServer'
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
> GO
> Exec sp_addlinkedsrvlogin
> @.rmtsrvname='NameForLinkedServer',
> @.rmtuser='USERNAME',
> @.rmtpassword='PASSWORD'
> GO
> This all runs very nicely and completes without errors. I then try to
> query the linked server I have just added, and the whole thing just
> hangs there "executing" the query (Management Studio Express). I have
> also tried this with the MSDAORA (Microsoft) provider, and that returns
> a "cannot initialise object" error.
> I can access the Oracle server with the Oracle tools that I have
> installed, and I can access the tables in the Oracle server by linking
> them in MS Access (using a User DSN). It just wont work in SQL Server
> it seems.
> Can anybody help with this one? I am truly stuck and desperately want
> to avoid having to use Access for this (we still use Access 97 would
> you believe as a corporate standard!).
>
Remember to set AllowInProcess for OraOLEDB.Oracle.
EG
USE MASTER
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
sp_dropserver @.server=N'NameForLinkedServer', @.droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//HOSTNAME/SERVICE'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = N'NameForLinkedServer',
@.useself = N'False',
@.rmtuser = N'USERNAME',
@.rmtpassword = N'PASSWORD'
GO
GO
SELECT * FROM OPENQUERY(NameForLinkedServer,'SELECT 1 D FROM DUAL')
David
|||Thanks for that David,
I have given it a try, and the SQL instructions have run and made the
changes. Unfortunately there is still no action when trying to query
the linked server. I have had a quick look at the network performance
on the pc, and there is not even any network activity.
Do you know if there is a way that I could perhaps query the User DSN
from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
and two-step the solution since it doesnt seem to want to play nicely
with me...
Cheers
The Frog
|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164185311.741862.54970@.e3g2000cwe.googlegrou ps.com...
> Thanks for that David,
> I have given it a try, and the SQL instructions have run and made the
> changes. Unfortunately there is still no action when trying to query
> the linked server. I have had a quick look at the network performance
> on the pc, and there is not even any network activity.
> Do you know if there is a way that I could perhaps query the User DSN
> from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
> and two-step the solution since it doesnt seem to want to play nicely
> with me...
>
A couple of things to check out:
Make sure
-you have rebooted since installing the Oracle Client.
-the SQL Service account have permissions for the Oracle client folders.
David
|||Thanks again David,
I have checked the permissions and also rebooted the machine a few
times just to be sure. They all seem in order. I did notice something
when playing with the Oracle Client software however that may be
important: The server is an Oracle 9i server, but the client software
is 8.17.
I have downloaded an ODBC driver from Oracle, but have not been able to
try it because I need something called the Oracle Universal
Installer(?). Apparently this installer comes with the client
software, but I am unable to locate it (only the SQL Plus, and another
called Oracle ODBC Test).
Do you think it may be that my driver is simply too old? I would have
thought that MS Access would have also complained (but since it is
Access 97 maybe the 8.17 driver software is actually more sophistocated
than it is?). I was thinking that there may be an incompatability
between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
guess.
Thankyou so much for trying to help with this, it is greatly
appreciated.
Cheers
The Frog
David Browne wrote:[vbcol=seagreen]
> "The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
> news:1164185311.741862.54970@.e3g2000cwe.googlegrou ps.com...
<SNIP>
|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164201695.690139.214840@.b28g2000cwb.googlegr oups.com...
> Thanks again David,
> I have checked the permissions and also rebooted the machine a few
> times just to be sure. They all seem in order. I did notice something
> when playing with the Oracle Client software however that may be
> important: The server is an Oracle 9i server, but the client software
> is 8.17.
> I have downloaded an ODBC driver from Oracle, but have not been able to
> try it because I need something called the Oracle Universal
> Installer(?). Apparently this installer comes with the client
> software, but I am unable to locate it (only the SQL Plus, and another
> called Oracle ODBC Test).
> Do you think it may be that my driver is simply too old? I would have
> thought that MS Access would have also complained (but since it is
> Access 97 maybe the 8.17 driver software is actually more sophistocated
> than it is?). I was thinking that there may be an incompatability
> between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
> guess.
> Thankyou so much for trying to help with this, it is greatly
> appreciated.
>
That is a very old driver. I would the latest OleDb provider, available at
http://www.oracle.com/technology/tech/windows/ole_db/index.html.
David
|||Thankyou once again David,
I am downloading the product from the link now. I will install and
trial it today, and post here again with the results a little later on.
Just thinking about this issue logically, it probably is the driver
thats the cause of the problem. Its a large file, and the company
internet connection is a little slow, so I will have to come back on
this one.
Thanks again for all the help.
Cheers
The Frog
|||Hi David,
I have installed the software, and copied the TNSNames.ora file to the
network\admin directory. I seem to be getting network activity now, and
I am getting a response from the Oracle server. The message is an error
of some kind (ORA-12154), which when I look it up states that the issue
is with the TNSNAMES.ora file. I run the network configurator tool for
the product, and am able to test the connection - and it works (once it
has the right username and password).
I have tried to configure the Linked Server several different ways, but
basically the script we discussed earlier is what is being used to
create the server. I have noticed in the servername for the linked
server that it was not exactly the same as the name in the TNSNAMES.ora
file. The ora file has a name like this:
oracledatabase.sub-domain-domain. When I change the script to match
this the response is instant with the same error message above. If the
script drops the domain extensions from the name query takes a few
seconds before returning a 'not going to happen' response.
You have helped me so much with this, I cannot thank you enough for
getting me this far. I hope I am not impinging on your time and
patience too much with this. I am comfortable once I am in SQL Server,
but I am really not an Oracle guy (I have not had enough experience
with it to date).
Thankyou once again
The Frog
|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164294579.219182.26860@.h54g2000cwb.googlegro ups.com...
> Hi David,
> I have installed the software, and copied the TNSNames.ora file to the
> network\admin directory. I seem to be getting network activity now, and
> I am getting a response from the Oracle server. The message is an error
> of some kind (ORA-12154), which when I look it up states that the issue
> is with the TNSNAMES.ora file. I run the network configurator tool for
> the product, and am able to test the connection - and it works (once it
> has the right username and password).
> I have tried to configure the Linked Server several different ways, but
> basically the script we discussed earlier is what is being used to
> create the server. I have noticed in the servername for the linked
> server that it was not exactly the same as the name in the TNSNAMES.ora
> file. The ora file has a name like this:
> oracledatabase.sub-domain-domain. When I change the script to match
> this the response is instant with the same error message above. If the
> script drops the domain extensions from the name query takes a few
> seconds before returning a 'not going to happen' response.
> You have helped me so much with this, I cannot thank you enough for
> getting me this far. I hope I am not impinging on your time and
> patience too much with this. I am comfortable once I am in SQL Server,
> but I am really not an Oracle guy (I have not had enough experience
> with it to date).
>
Now, with the new driver, you can take advantage of one of the best new
features of the Oracle client: bypassing the tnsnames.ora file. With the
10g client you simply don't need a tnsnames.ora file. <<sound of much
rejoicing>>
Instead you specify //hostname/servicename where hostname is the IP address
(or DNS name) of the oracle server, and servicename is the instance.
David
|||David you are an absolute LEGEND!
It works like a charm. The script I used in the end is as follows:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.useself= FALSE,
@.rmtuser='remoteusername',
@.rmtpassword='remoteuserpassword'
GO
This was done with the Visual Studio Developer tools installed from the
link you provided above, to make sure all the drivers etc... were up to
date.
Its quick, clean, and scriptable all the way through. I cant thank you
enough. If you are ever in Germany, the beer and schnitzels are on me

Thankyou, thankyou, thankyou
Indebted to your services
The Frog

Linking Oracle Servers in SQL Server 2005 Express

Hi Everyone,
I am trying to link an Oracle server to my instance of SQL Server 2005
Express. I have used the following code to add the linked server:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = 'ActualNameOfOracleServer'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.rmtuser='USERNAME',
@.rmtpassword='PASSWORD'
GO
This all runs very nicely and completes without errors. I then try to
query the linked server I have just added, and the whole thing just
hangs there "executing" the query (Management Studio Express). I have
also tried this with the MSDAORA (Microsoft) provider, and that returns
a "cannot initialise object" error.
I can access the Oracle server with the Oracle tools that I have
installed, and I can access the tables in the Oracle server by linking
them in MS Access (using a User DSN). It just wont work in SQL Server
it seems.
Can anybody help with this one? I am truly stuck and desperately want
to avoid having to use Access for this (we still use Access 97 would
you believe as a corporate standard!).
Cheers, and thanks in Advance
The Frog :)"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164125803.967272.173060@.f16g2000cwb.googlegroups.com...
> Hi Everyone,
> I am trying to link an Oracle server to my instance of SQL Server 2005
> Express. I have used the following code to add the linked server:
> USE MASTER
> GO
> EXEC sp_addlinkedserver
> @.server = 'NameForLinkedServer',
> @.srvproduct = 'Oracle',
> @.provider = 'OraOLEDB.Oracle',
> @.datasrc = 'ActualNameOfOracleServer'
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
> GO
> Exec sp_addlinkedsrvlogin
> @.rmtsrvname='NameForLinkedServer',
> @.rmtuser='USERNAME',
> @.rmtpassword='PASSWORD'
> GO
> This all runs very nicely and completes without errors. I then try to
> query the linked server I have just added, and the whole thing just
> hangs there "executing" the query (Management Studio Express). I have
> also tried this with the MSDAORA (Microsoft) provider, and that returns
> a "cannot initialise object" error.
> I can access the Oracle server with the Oracle tools that I have
> installed, and I can access the tables in the Oracle server by linking
> them in MS Access (using a User DSN). It just wont work in SQL Server
> it seems.
> Can anybody help with this one? I am truly stuck and desperately want
> to avoid having to use Access for this (we still use Access 97 would
> you believe as a corporate standard!).
>
Remember to set AllowInProcess for OraOLEDB.Oracle.
EG
USE MASTER
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
sp_dropserver @.server=N'NameForLinkedServer', @.droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//HOSTNAME/SERVICE'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = N'NameForLinkedServer',
@.useself = N'False',
@.rmtuser = N'USERNAME',
@.rmtpassword = N'PASSWORD'
GO
GO
SELECT * FROM OPENQUERY(NameForLinkedServer,'SELECT 1 D FROM DUAL')
David|||Thanks for that David,
I have given it a try, and the SQL instructions have run and made the
changes. Unfortunately there is still no action when trying to query
the linked server. I have had a quick look at the network performance
on the pc, and there is not even any network activity.
Do you know if there is a way that I could perhaps query the User DSN
from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
and two-step the solution since it doesnt seem to want to play nicely
with me...
Cheers
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> Thanks for that David,
> I have given it a try, and the SQL instructions have run and made the
> changes. Unfortunately there is still no action when trying to query
> the linked server. I have had a quick look at the network performance
> on the pc, and there is not even any network activity.
> Do you know if there is a way that I could perhaps query the User DSN
> from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
> and two-step the solution since it doesnt seem to want to play nicely
> with me...
>
A couple of things to check out:
Make sure
-you have rebooted since installing the Oracle Client.
-the SQL Service account have permissions for the Oracle client folders.
David|||Thanks again David,
I have checked the permissions and also rebooted the machine a few
times just to be sure. They all seem in order. I did notice something
when playing with the Oracle Client software however that may be
important: The server is an Oracle 9i server, but the client software
is 8.17.
I have downloaded an ODBC driver from Oracle, but have not been able to
try it because I need something called the Oracle Universal
Installer(?). Apparently this installer comes with the client
software, but I am unable to locate it (only the SQL Plus, and another
called Oracle ODBC Test).
Do you think it may be that my driver is simply too old? I would have
thought that MS Access would have also complained (but since it is
Access 97 maybe the 8.17 driver software is actually more sophistocated
than it is?). I was thinking that there may be an incompatability
between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
guess.
Thankyou so much for trying to help with this, it is greatly
appreciated.
Cheers
The Frog
David Browne wrote:
> "The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
> news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> > Thanks for that David,
> >
<SNIP>|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164201695.690139.214840@.b28g2000cwb.googlegroups.com...
> Thanks again David,
> I have checked the permissions and also rebooted the machine a few
> times just to be sure. They all seem in order. I did notice something
> when playing with the Oracle Client software however that may be
> important: The server is an Oracle 9i server, but the client software
> is 8.17.
> I have downloaded an ODBC driver from Oracle, but have not been able to
> try it because I need something called the Oracle Universal
> Installer(?). Apparently this installer comes with the client
> software, but I am unable to locate it (only the SQL Plus, and another
> called Oracle ODBC Test).
> Do you think it may be that my driver is simply too old? I would have
> thought that MS Access would have also complained (but since it is
> Access 97 maybe the 8.17 driver software is actually more sophistocated
> than it is?). I was thinking that there may be an incompatability
> between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
> guess.
> Thankyou so much for trying to help with this, it is greatly
> appreciated.
>
That is a very old driver. I would the latest OleDb provider, available at
http://www.oracle.com/technology/tech/windows/ole_db/index.html.
David|||Thankyou once again David,
I am downloading the product from the link now. I will install and
trial it today, and post here again with the results a little later on.
Just thinking about this issue logically, it probably is the driver
thats the cause of the problem. Its a large file, and the company
internet connection is a little slow, so I will have to come back on
this one.
Thanks again for all the help.
Cheers
The Frog|||Hi David,
I have installed the software, and copied the TNSNames.ora file to the
network\admin directory. I seem to be getting network activity now, and
I am getting a response from the Oracle server. The message is an error
of some kind (ORA-12154), which when I look it up states that the issue
is with the TNSNAMES.ora file. I run the network configurator tool for
the product, and am able to test the connection - and it works (once it
has the right username and password).
I have tried to configure the Linked Server several different ways, but
basically the script we discussed earlier is what is being used to
create the server. I have noticed in the servername for the linked
server that it was not exactly the same as the name in the TNSNAMES.ora
file. The ora file has a name like this:
oracledatabase.sub-domain-domain. When I change the script to match
this the response is instant with the same error message above. If the
script drops the domain extensions from the name query takes a few
seconds before returning a 'not going to happen' response.
You have helped me so much with this, I cannot thank you enough for
getting me this far. I hope I am not impinging on your time and
patience too much with this. I am comfortable once I am in SQL Server,
but I am really not an Oracle guy (I have not had enough experience
with it to date).
Thankyou once again
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164294579.219182.26860@.h54g2000cwb.googlegroups.com...
> Hi David,
> I have installed the software, and copied the TNSNames.ora file to the
> network\admin directory. I seem to be getting network activity now, and
> I am getting a response from the Oracle server. The message is an error
> of some kind (ORA-12154), which when I look it up states that the issue
> is with the TNSNAMES.ora file. I run the network configurator tool for
> the product, and am able to test the connection - and it works (once it
> has the right username and password).
> I have tried to configure the Linked Server several different ways, but
> basically the script we discussed earlier is what is being used to
> create the server. I have noticed in the servername for the linked
> server that it was not exactly the same as the name in the TNSNAMES.ora
> file. The ora file has a name like this:
> oracledatabase.sub-domain-domain. When I change the script to match
> this the response is instant with the same error message above. If the
> script drops the domain extensions from the name query takes a few
> seconds before returning a 'not going to happen' response.
> You have helped me so much with this, I cannot thank you enough for
> getting me this far. I hope I am not impinging on your time and
> patience too much with this. I am comfortable once I am in SQL Server,
> but I am really not an Oracle guy (I have not had enough experience
> with it to date).
>
Now, with the new driver, you can take advantage of one of the best new
features of the Oracle client: bypassing the tnsnames.ora file. With the
10g client you simply don't need a tnsnames.ora file. <<sound of much
rejoicing>>
Instead you specify //hostname/servicename where hostname is the IP address
(or DNS name) of the oracle server, and servicename is the instance.
David|||David you are an absolute LEGEND!
It works like a charm. The script I used in the end is as follows:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.useself= FALSE,
@.rmtuser='remoteusername',
@.rmtpassword='remoteuserpassword'
GO
This was done with the Visual Studio Developer tools installed from the
link you provided above, to make sure all the drivers etc... were up to
date.
Its quick, clean, and scriptable all the way through. I cant thank you
enough. If you are ever in Germany, the beer and schnitzels are on me
:)
Thankyou, thankyou, thankyou
Indebted to your services
The Frog

Linking Oracle Servers in SQL Server 2005 Express

"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164125803.967272.173060@.f16g2000cwb.googlegroups.com...
> Hi Everyone,
> I am trying to link an Oracle server to my instance of SQL Server 2005
> Express. I have used the following code to add the linked server:
> USE MASTER
> GO
> EXEC sp_addlinkedserver
> @.server = 'NameForLinkedServer',
> @.srvproduct = 'Oracle',
> @.provider = 'OraOLEDB.Oracle',
> @.datasrc = 'ActualNameOfOracleServer'
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
> GO
> Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
> GO
> Exec sp_addlinkedsrvlogin
> @.rmtsrvname='NameForLinkedServer',
> @.rmtuser='USERNAME',
> @.rmtpassword='PASSWORD'
> GO
> This all runs very nicely and completes without errors. I then try to
> query the linked server I have just added, and the whole thing just
> hangs there "executing" the query (Management Studio Express). I have
> also tried this with the MSDAORA (Microsoft) provider, and that returns
> a "cannot initialise object" error.
> I can access the Oracle server with the Oracle tools that I have
> installed, and I can access the tables in the Oracle server by linking
> them in MS Access (using a User DSN). It just wont work in SQL Server
> it seems.
> Can anybody help with this one? I am truly stuck and desperately want
> to avoid having to use Access for this (we still use Access 97 would
> you believe as a corporate standard!).
>
Remember to set AllowInProcess for OraOLEDB.Oracle.
EG
USE MASTER
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
sp_dropserver @.server=N'NameForLinkedServer', @.droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'Oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//HOSTNAME/SERVICE'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = N'NameForLinkedServer',
@.useself = N'False',
@.rmtuser = N'USERNAME',
@.rmtpassword = N'PASSWORD'
GO
GO
SELECT * FROM OPENQUERY(NameForLinkedServer,'SELECT 1 D FROM DUAL')
David|||Thanks for that David,
I have given it a try, and the SQL instructions have run and made the
changes. Unfortunately there is still no action when trying to query
the linked server. I have had a quick look at the network performance
on the pc, and there is not even any network activity.
Do you know if there is a way that I could perhaps query the User DSN
from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
and two-step the solution since it doesnt seem to want to play nicely
with me...
Cheers
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
> Thanks for that David,
> I have given it a try, and the SQL instructions have run and made the
> changes. Unfortunately there is still no action when trying to query
> the linked server. I have had a quick look at the network performance
> on the pc, and there is not even any network activity.
> Do you know if there is a way that I could perhaps query the User DSN
> from SQL Server 2005? Or perhaps a file DSN? Maybe the answer is to try
> and two-step the solution since it doesnt seem to want to play nicely
> with me...
>
A couple of things to check out:
Make sure
-you have rebooted since installing the Oracle Client.
-the SQL Service account have permissions for the Oracle client folders.
David|||Thanks again David,
I have checked the permissions and also rebooted the machine a few
times just to be sure. They all seem in order. I did notice something
when playing with the Oracle Client software however that may be
important: The server is an Oracle 9i server, but the client software
is 8.17.
I have downloaded an ODBC driver from Oracle, but have not been able to
try it because I need something called the Oracle Universal
Installer(?). Apparently this installer comes with the client
software, but I am unable to locate it (only the SQL Plus, and another
called Oracle ODBC Test).
Do you think it may be that my driver is simply too old? I would have
thought that MS Access would have also complained (but since it is
Access 97 maybe the 8.17 driver software is actually more sophistocated
than it is?). I was thinking that there may be an incompatability
between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
guess.
Thankyou so much for trying to help with this, it is greatly
appreciated.
Cheers
The Frog
David Browne wrote:[vbcol=seagreen]
> "The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
> news:1164185311.741862.54970@.e3g2000cwe.googlegroups.com...
<SNIP>|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164201695.690139.214840@.b28g2000cwb.googlegroups.com...
> Thanks again David,
> I have checked the permissions and also rebooted the machine a few
> times just to be sure. They all seem in order. I did notice something
> when playing with the Oracle Client software however that may be
> important: The server is an Oracle 9i server, but the client software
> is 8.17.
> I have downloaded an ODBC driver from Oracle, but have not been able to
> try it because I need something called the Oracle Universal
> Installer(?). Apparently this installer comes with the client
> software, but I am unable to locate it (only the SQL Plus, and another
> called Oracle ODBC Test).
> Do you think it may be that my driver is simply too old? I would have
> thought that MS Access would have also complained (but since it is
> Access 97 maybe the 8.17 driver software is actually more sophistocated
> than it is?). I was thinking that there may be an incompatability
> between the 8.17 driver and SQL 2005 with MDAC 2.8 SP1. Its just a
> guess.
> Thankyou so much for trying to help with this, it is greatly
> appreciated.
>
That is a very old driver. I would the latest OleDb provider, available at
http://www.oracle.com/technology/te..._db/index.html.
David|||Thankyou once again David,
I am downloading the product from the link now. I will install and
trial it today, and post here again with the results a little later on.
Just thinking about this issue logically, it probably is the driver
thats the cause of the problem. Its a large file, and the company
internet connection is a little slow, so I will have to come back on
this one.
Thanks again for all the help.
Cheers
The Frog|||Hi David,
I have installed the software, and copied the TNSNames.ora file to the
network\admin directory. I seem to be getting network activity now, and
I am getting a response from the Oracle server. The message is an error
of some kind (ORA-12154), which when I look it up states that the issue
is with the TNSNAMES.ora file. I run the network configurator tool for
the product, and am able to test the connection - and it works (once it
has the right username and password).
I have tried to configure the Linked Server several different ways, but
basically the script we discussed earlier is what is being used to
create the server. I have noticed in the servername for the linked
server that it was not exactly the same as the name in the TNSNAMES.ora
file. The ora file has a name like this:
oracledatabase.sub-domain-domain. When I change the script to match
this the response is instant with the same error message above. If the
script drops the domain extensions from the name query takes a few
seconds before returning a 'not going to happen' response.
You have helped me so much with this, I cannot thank you enough for
getting me this far. I hope I am not impinging on your time and
patience too much with this. I am comfortable once I am in SQL Server,
but I am really not an Oracle guy (I have not had enough experience
with it to date).
Thankyou once again
The Frog|||"The Frog" <andrew.hogendijk@.eu.effem.com> wrote in message
news:1164294579.219182.26860@.h54g2000cwb.googlegroups.com...
> Hi David,
> I have installed the software, and copied the TNSNames.ora file to the
> network\admin directory. I seem to be getting network activity now, and
> I am getting a response from the Oracle server. The message is an error
> of some kind (ORA-12154), which when I look it up states that the issue
> is with the TNSNAMES.ora file. I run the network configurator tool for
> the product, and am able to test the connection - and it works (once it
> has the right username and password).
> I have tried to configure the Linked Server several different ways, but
> basically the script we discussed earlier is what is being used to
> create the server. I have noticed in the servername for the linked
> server that it was not exactly the same as the name in the TNSNAMES.ora
> file. The ora file has a name like this:
> oracledatabase.sub-domain-domain. When I change the script to match
> this the response is instant with the same error message above. If the
> script drops the domain extensions from the name query takes a few
> seconds before returning a 'not going to happen' response.
> You have helped me so much with this, I cannot thank you enough for
> getting me this far. I hope I am not impinging on your time and
> patience too much with this. I am comfortable once I am in SQL Server,
> but I am really not an Oracle guy (I have not had enough experience
> with it to date).
>
Now, with the new driver, you can take advantage of one of the best new
features of the Oracle client: bypassing the tnsnames.ora file. With the
10g client you simply don't need a tnsnames.ora file. <<sound of much
rejoicing>>
Instead you specify //hostname/servicename where hostname is the IP address
(or DNS name) of the oracle server, and servicename is the instance.
David|||David you are an absolute LEGEND!
It works like a charm. The script I used in the end is as follows:
USE MASTER
GO
EXEC sp_addlinkedserver
@.server = 'NameForLinkedServer',
@.srvproduct = 'oracle',
@.provider = 'OraOLEDB.Oracle',
@.datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
GO
Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
GO
Exec sp_addlinkedsrvlogin
@.rmtsrvname='NameForLinkedServer',
@.useself= FALSE,
@.rmtuser='remoteusername',
@.rmtpassword='remoteuserpassword'
GO
This was done with the Visual Studio Developer tools installed from the
link you provided above, to make sure all the drivers etc... were up to
date.
Its quick, clean, and scriptable all the way through. I cant thank you
enough. If you are ever in Germany, the beer and schnitzels are on me

Thankyou, thankyou, thankyou
Indebted to your services
The Frogsql

Linking Oracle Servers

Has anyone successfully linked to a Linux Oracle server running 9i? We are unable to get the link working.....have installed various drivers and still get connection refused. Any thoughts?

Try to install oracle client on your machine,and it should work

Best Regards

|||

We have installed the client on the MS SQL server, installed Oracle OLE DB, etc. Still receive the error that we cannot initialize the MSDAORA. Not sure why. Using MS Access we are able to connect to the data via an ODBC connection (on the same box). However, we cannot use the ODBC to connect directly to the Oracle server (Oracle 9i on a Linux platform). Any other thoughts are appreciated.

|||Try to use Oracle ODBC driver instead of Microsoft ODBC for oracle (His name should be Oracle in <oraclehome>)

Linking Oracle DB to SQL DB

Hello
Where can I find information on how to link an Oracle
database to a SQL Server. Recently I wrote a small
database in Access linking the tables I need from the an
Oracle database using ODBC drivers but had alot of
connectivity and locking problems. I was advised to link
the Oracle server to SQL Server using Access as frontend.
I am familiar with SQL Server, .net and Access but not
familiar with Oracle. I played with the Linked Server in
SQL an was able to link other SQL servers but not Oracle
giving an MSDAORA error.
I have read articles that you can't just link the Oracle
server using OLEDB or other connectivity sources you must
install a client from Oracle. I downloaded an OUIRelease
after reading the release notes I don't really know what
to do...
Any information is greatly appreciated
Frustrated
Susan
SusanHi Susan,
Try the following article for steps on how to set up a
linked server to Oracle:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Tue, 24 Feb 2004 11:29:34 -0800, "Susan"
<anonymous@.discussions.microsoft.com> wrote:

>Hello
>Where can I find information on how to link an Oracle
>database to a SQL Server. Recently I wrote a small
>database in Access linking the tables I need from the an
>Oracle database using ODBC drivers but had alot of
>connectivity and locking problems. I was advised to link
>the Oracle server to SQL Server using Access as frontend.
>I am familiar with SQL Server, .net and Access but not
>familiar with Oracle. I played with the Linked Server in
>SQL an was able to link other SQL servers but not Oracle
>giving an MSDAORA error.
>I have read articles that you can't just link the Oracle
>server using OLEDB or other connectivity sources you must
>install a client from Oracle. I downloaded an OUIRelease
>after reading the release notes I don't really know what
>to do...
>Any information is greatly appreciated
>Frustrated
>Susan
>Susan|||Thanks for all the info
I was able to install the client with connection success..
but now I still get the error 7399 ole db
provider 'MSDAORA' reported an error. I will go through
the support links you provided. If I can't connect by
tomorrow I'll post again..
Thanks again
Susan
>--Original Message--
>Hi Susan,
>Try the following article for steps on how to set up a
>linked server to Oracle:
>HOW TO: Set Up and Troubleshoot a Linked Server to Oracle
in
>SQL Server
>http://support.microsoft.com/?id=280106
>-Sue
>On Tue, 24 Feb 2004 11:29:34 -0800, "Susan"
><anonymous@.discussions.microsoft.com> wrote:
>
link
frontend.
in
must
OUIRelease
>.
>

linking Oracle 9i server from Sql Server 2005

we'd like to link an Oracle 9i database running on Unix to our sql server 2005 Business Intelligence server to import the data from the Oracle database. When we attempt to use the Microsoft ODBC for Oracle we get the error that the Oracle client and networking tools are not installed. When we attempt to use the Oracle RDB we get the error connection refused.

What steps do we need to do to connect the servers and run queries?

The Microsoft ODBC driver only provides full support for Oracle 7, and some limited support for Oracle 8 through emulation. It is unsupported for Orcale 9. You can see the roadmap for our data components via: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/Dnmdac/html/data_mdacroadmap.asp. Please contact Oracle for their ODBC driver. Thanks

Friday, March 30, 2012

Linking Server to MySql

Linking Server to a different Domain

Linking Server to a different Domain

Linking Server to a different Domain

Linking Server problem

Linking Server problem

Linking Server problem

Linking Seperate Datasets

Linking Scheduled Jobs

Linking SAS-datasets to an Access Project 2003

Linking SAS-datasets to an Access Project 2003

Linking same table

Linking Reports in different projects

Linking reports

Linking reports

Linking Oracle view to SQL Server express

Linking Oracle view to SQL Server express

Linking Oracle view to SQL Server express

Linking Oracle Servers in SQL Server 2005 Express

Linking Oracle Servers in SQL Server 2005 Express

Linking Oracle Servers in SQL Server 2005 Express

Linking Oracle Servers

Linking Oracle DB to SQL DB

linking Oracle 9i server from Sql Server 2005

linking two datasets

Blog Archive

About Me