Order by problem within a View

Hi,

I have created a view which uses 3 tables, i also have a sort on one of the columns. However when I open the view the sort does not work. It does however sort the view correctly when executing the query within design view

Can anyone explain this or is it a bug within SQL Server Express 2005?

thanks

David

This is an expected behavior. Any ORDER BY that is attached to a view when it is defined is basically ignored. If you want the columns of a view to be ordered, the order by must be specified at the time you construct your actual query from the view. The long and short of it is that you cannot pre-set the ORDER BY behavior by definiting it as part of a view definition.

ORDER BY Clause with multiple tables

Hi All
I am having a problem with an ORDER BY clause when selecting information from multiple tables. Eg
SELECT i.InvoiceId, pd.PayDescription, u.UserName
FROM Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId
LEFT OUTER JOIN tblUsers ON i.UserId = u.UserId
ORDER BY pd.PayDescription
this is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?
I am writing this for MSSQL Server 2000
Thanks
BraidenYou can definitely do an ORDER BY like that. What's the problem? Are you receiving an error?
(The abbreviated example you've supplied would of course return anerror because there is no definitition for the pd alias in the query.)
|||

Hi
This is my current stored procedure, it is implementing a server side paging algorithm.
--CODE BEGINS
CREATE PROCEDURE [dbo].[ap_APPInvoiceSearchSEL]

--Declare input parameters
@.ActivityInvoiceTypeID as int,
@.CommunityVisitorID as int,
@.PayRunID as bigint,
@.InvoiceDateFrom as datetime,
@.InvoiceDateTo as datetime,
@.PayStatusID as int,
@.PageNum as bigint,
@.PageSize as bigint,
@.SortExpression as varchar(50)

AS
BEGIN

-- Create a Variable Table to hold search results in
DECLARE @.SearchResults TABLE
(
SearchResultID bigint IDENTITY,
InvoiceID bigint,
SiteName varchar(50),
ActivityInvoiceTypeDescription varchar(50),
CommunityVisitorName varchar(100),
InvoiceDate datetime,
ActivityDate datetime,
PayStatusDescription varchar(50),
PayRunID bigint,
PayRunDate datetime,
ActivityAmountClaimed decimal(9),
TravelAmountClaimed decimal(9),
VehicleAllowanceClaimed decimal(9),
TotalAmountClaimed decimal(9),
RecordCount bigint

)

--Declare variables
DECLARE @.RecordCount as bigint,
@.StartRecord as bigint,
@.EndRecord as bigint

--Find the number of results
SELECT @.RecordCount = Count(i.InvoiceID)
FROM vwInvoice i LEFT OUTER JOIN tblCommunityVisitor c ON i.CommunityVisitorID = c.CommunityVisitorID
LEFT OUTER JOIN tblJAGUser u ON u.UserID = c.UserID
LEFT OUTER JOIN tluActivityInvoiceType it ON i.ActivityInvoiceTypeID = it.ActivityInvoiceTypeID
LEFT OUTER JOIN tluPayStatus ps ON i.PayStatusID = ps.PayStatusID
LEFT OUTER JOIN tblPayRun pr ON i.PayRunID = pr.PayRunID
LEFT OUTER JOIN tblSite s ON i.SiteID = s.SiteID

WHERE (i.ActivityInvoiceTypeID = @.ActivityInvoiceTypeID OR @.ActivityInvoiceTypeID is null)
AND (i.CommunityVisitorID = @.CommunityVisitorID OR @.CommunityVisitorID is null)
AND (i.PayRunID = @.PayRunID OR @.PayRunID is null)
AND (i.InvoiceDate >= @.InvoiceDateFrom OR @.InvoiceDateFrom is null)
AND (i.InvoiceDate <= @.InvoiceDateTo OR @.InvoiceDateTo is null)
AND (i.PayStatusID = @.PayStatusID OR @.PayStatusID is null)

INSERT INTO
@.SearchResults (InvoiceID,
SiteName,
ActivityInvoiceTypeDescription,
CommunityVisitorName,
InvoiceDate,
ActivityDate,
PayStatusDescription,
PayRunID,
PayRunDate,
ActivityAmountClaimed ,
TravelAmountClaimed,
VehicleAllowanceClaimed,
TotalAmountClaimed,
RecordCount)

SELECT i.InvoiceID,
s.SiteName,
it.ActivityInvoiceTypeDescription,
u.FirstName + ' ' + u.LastName as CommunityVisitorName,
i.InvoiceDate as InvoiceDate,
i.ActivityDate,
ps.PayStatusDescription,
i.PayRunID,
pr.PayRunDate,
i.ActivityAmountClaimed,
i.TravelAmountClaimed,
i.VehicleAllowanceClaimed,
i.TotalAmountClaimed,
@.RecordCount As RecordCount

FROM vwInvoice i LEFT OUTER JOIN tblCommunityVisitor c ON i.CommunityVisitorID = c.CommunityVisitorID
LEFT OUTER JOIN tblJAGUser u ON u.UserID = c.UserID
LEFT OUTER JOIN tluActivityInvoiceType it ON i.ActivityInvoiceTypeID = it.ActivityInvoiceTypeID
LEFT OUTER JOIN tluPayStatus ps ON i.PayStatusID = ps.PayStatusID
LEFT OUTER JOIN tblPayRun pr ON i.PayRunID = pr.PayRunID
LEFT OUTER JOIN tblSite s ON i.SiteID = s.SiteID

WHERE (i.ActivityInvoiceTypeID = @.ActivityInvoiceTypeID OR @.ActivityInvoiceTypeID is null)
AND (i.CommunityVisitorID = @.CommunityVisitorID OR @.CommunityVisitorID is null)

AND (i.PayRunID = @.PayRunID OR @.PayRunID is null)
AND (i.InvoiceDate >= @.InvoiceDateFrom OR @.InvoiceDateFrom is null)
AND (i.InvoiceDate <= @.InvoiceDateTo OR @.InvoiceDateTo is null)
AND (i.PayStatusID = @.PayStatusID OR @.PayStatusID is null)

ORDER BY

CASE @.SortExpression
WHEN 'InvoiceID' THEN i.InvoiceID
WHEN 'SiteName' THEN s.SiteName
WHEN 'ActivityInvoiceTypeDescription' THEN it.ActivityInvoiceTypeDescription
WHEN 'CommunityVisitorName' THEN u.FirstName
WHEN 'ActivityDate' THEN i.ActivityDate
WHEN 'PayStatusDescription' THEN ps.PayStatusDescription
WHEN 'ActivityAmountClaimed' THEN i.ActivityAmountClaimed
WHEN 'TravelAmountClaimed' THEN i.TravelAmountClaimed
WHEN 'VehicleAllowanceClaimed' THEN i.VehicleAllowanceClaimed
WHEN 'TotalAmountClaimed' THEN i.totalAmountClaimed
ELSE i.InvoiceID
END

--Determine page positions
SET @.StartRecord = ((@.PageNum-1) * @.PageSize) + 1
SET @.EndRecord = @.PageNum * @.PageSize

--Now get the page of search results from the temp table
SELECT *
FROM @.SearchResults
WHERE SearchResultID BETWEEN @.StartRecord AND @.EndRecord

END
GO
--CODE ENDS
After further investigation i have narrowed it down to the final select case statement, which works find when the @.SortExpression variables resolves to a field that is in the invoice table. However when it does not i get a rather perculiar error saying"Syntax error converting datetime from character string."
Thanks
Braiden

|||Hi,
I observed that the ORDER BY works successfully when the order by column is one of the following types: int, boolean, datetime
But when the order by column is varchar or nvarchar, the sql statement fails with the following error message
Syntax error converting datetime from characterstring.

declare @.column nvarchar(100)
set @.column = 'firstname'
select *
from Customers
order by
case @.column
when 'cityid' then cityid
when 'firstname' then firstname
when 'birthdate' then birthdate
when 'active' then active
end
Eralper
http://www.kodyaz.com

|||Hi,
I realised that the problem occurs if your case statement has at leaston different column type side by side with varchar or nvarchar datatype.
If you do not have a column with data type string, the order by with case runs successfully.
Or in the case statement if you have only varchar data types (no othertype like datetime, boolean, int, etc) then it runs again successfully.

CASE @.SortExpression
WHEN 'InvoiceID' THEN i.InvoiceID
WHEN 'SiteName' THEN s.SiteName
WHEN 'ActivityInvoiceTypeDescription' THEN it.ActivityInvoiceTypeDescription
WHEN 'CommunityVisitorName' THEN u.FirstName
WHEN 'ActivityDate' THEN i.ActivityDate
WHEN 'PayStatusDescription' THEN ps.PayStatusDescription
WHEN 'ActivityAmountClaimed' THEN i.ActivityAmountClaimed
WHEN 'TravelAmountClaimed' THEN i.TravelAmountClaimed
WHEN 'VehicleAllowanceClaimed' THEN i.VehicleAllowanceClaimed
WHEN 'TotalAmountClaimed' THEN i.totalAmountClaimed
ELSE i.InvoiceID
END
If you really need a functionality like this, you should use run a copy of the script for string data types

Or you can use dynamic sql statements
Eralper
|||Hi again,
You can use multiple CASE statement in ORDER BY instead of one
declare @.column nvarchar(100)
set @.column = 'firstname'
select *
from Customers
order by
case when @.column = 'cityid' then cityid end,
case when @.column = 'firstname' then firstname end

You can check the article at http://www.extremeexperts.com/SQL/Articles/CASEinORDER.aspx

|||Thanks aloteralper
That was a big help, i have got it working now
Braiden

Order By

Hi,
I'm trying to understand a particular behaviour of ORDER BY.

Suppose I have 3 tables with a similar column name "samecol".

When I execute something like :

SELECT Tab1.a, Tab2.b, Tab3.c
FROM Tab1, Tab2, Tab3
ORDER BY samecol

Surprisingly, without specifying if it must order by Tab1.samecol or Tab2.samecol for instance, the query returns me a recordset.

Do you know if it chooses a table by default ?
Or it randomly order my datas ?

ThanksOriginally posted by Linirlan
Hi,
I'm trying to understand a particular behaviour of ORDER BY.

Suppose I have 3 tables with a similar column name "samecol".

When I execute something like :

SELECT Tab1.a, Tab2.b, Tab3.c
FROM Tab1, Tab2, Tab3
ORDER BY samecol

Surprisingly, without specifying if it must order by Tab1.samecol or Tab2.samecol for instance, the query returns me a recordset.

Do you know if it chooses a table by default ?
Or it randomly order my datas ?

Thanks
The answer is DBMS-specific. For example, the above is not possible in Oracle:

SQL> select emp.ename, dept.dname
2 from emp, dept
3 order by deptno;
order by deptno
*
ERROR at line 3:
ORA-00918: column ambiguously defined

(Both tables have a column called deptno.)

Orbiting the AS400

I need to link to tables in an AS400 on a customer site. They use IBM CA and
have the latest 5.2 version. (IBM CA includes both ODBC and OLE DB drivers).
I tried configuring the IBM OLE DB for AS400 driver using both graphical and
sp_addlinkedserver, but with no success. The best I can get is a link that
appears to connect without errors but the clicking on the tables or vies
icon I just get an empty recordset back. I have not been able to figure out
exactly what **should** be the correct configuration, in particular most
examples give connection strings, and this option does not appear to be
available with this driver:-(
Worse is to come, thinking that the problem may just be with access to the
schema information, I tried running a simple query against the AS400 even
thougth the list of tables was empty. SQL Server crashed, and it was a
production system. So, I installed CA on my laptop and tried again using my
"local" db. Identical.
Surfing around for help I found that many people seem to have similar
problems, and the suggested cure was to use the OLE DB for ODBC driver, and
it **almost** works. I see the tables and can run queries, but results are
sometimes incomplete. Selecting a table of 469 rows in query analyzer I only
got back 401 rows. Tinking it was a buffer problem I tried changing the
number of columns, but always got 401, except when I used Select * which
yields 392. At least one field is unique and I have tried using this on it's
own.
The ODBC DSN runs fine and returns 469 rows in queries sent from Access and
Excel.
Back to qury analyzer, Select Count(AnyField) always returns 469 rows,
suggesting that it is a datagrid/display problem, so I tried something like:
Select sqltable.GPART from sqltable where Not Exists (Select GPART from
as400table )
this returns all the GPARTs in the sqltable that are missing from the
"Select * from as400 table".
So, I surfed for solutions to this problem, and found that I was not alone
with this type of problem either. Using OLE DB for ODBC with the CA ODBC
driver can return missing rows.
Suggested cures were updating the ODBC driver (have been using latest and
greatest version from outset) or.....switching to OLE DB.
So having done the whole loop, I wonder if anybody had any idea where I can
go from here. I would very much like to hear if **anybody** has ever
succesfully linked in an AS400.
BTW, I know there is a Microsoft OLE DB driver for the AS400, but as far as
I can make out it requires an SNA server installation. Is this correct? I
have seen the license fee is around $2500, which is a bit steep if all you
are interested in is the OLE DB driver!Hi Rog,
Thanks for your post. The Microsoft OLE DB Provider for DB2, distributed
with Microsoft? Host Integration Server 2000, allows Microsoft SQL Server?
2000 distributed queries to query data in DB2 databases. The network
components needed to communicate with an IBM computer run in an SNA network.
For more information regarding Microsoft OLE DB Provider for DB2, please
refer to the following articles:
OLE DB Provider for DB2
http://msdn.microsoft.com/library/d...-us/acdata/ac_8
_qd_12_1gvm.asp
218590 INF: Configuring Data Sources for the Microsoft OLE DB Provider for
DB2
http://support.microsoft.com/?id=218590
216428 Configuring Microsoft ODBC Driver for DB2
http://support.microsoft.com/?id=216428
Also, as to the problem with IBM OLE DB for AS400 driver provided by IBM, I
am not familiar with the detailed information of this driver, therefore,
the help I am able to provide is very limited. For the best help, I suggest
you talk with IBM support engineers about this driver problem.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Oracle10 ODBC connection

I have a windows 2003 server with sql server 2000
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PWD=password ;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures this
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get.
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PWD=password ;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and no
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie
|||"Arnie" wrote:

> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i

> - Arnie
>
>

Oracle10 ODBC connection

I have a windows 2003 server with sql server 2000
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures t
his
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no
default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get."HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and n
o
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie|||"Arnie" wrote:

> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i

> - Arnie
>
>

Oracle to Sybase - Data Migration

Hi All,

I need to move data from some tables in oracle to sybase. Its jus kind of ad-hoc process - need to do it manually. I thought of doing it using DTS tool.

In sql server 2005 - using Integration Services - how to achieve this ? any help is greatly appreciated.

thanks.
Mani

It rather depends on the Sybase and Oracle connectivity that you have available. If you have the OLEDB drivers, then the import export wizard in SSIS should work for you. Right click on the SSIS packages node in an SSIS project in BI Dev Studio and select "SSIS Import and Export Wizard"

Donald

Oracle to SQLServer 2000 migration

Hi,

I need to migrate an Oracle database to SQLServer 2000 (tables, views, stored procedures, triggers, ...).

Can someone give me some good advice on how to do it properly?

I would appreciate for example a small list of steps to perform for doing this migration.

Thanks,
Isabel Sousa.try this

http://www.microsoft.com/sql/techinfo/deployment/2000/MigrateOracle.asp

You would probably want to take the opportunity to redesign though.

Oracle to SQL Server Migration

Hi,
We have an Oracle database with 3200 tables. We want to migrate this
database tables (both structure and data) to SQL Server 2005.
What would be the best way to do that? Is there any automated migration tool
for doing that?
Is there any white paper that explains Oracle to SQL Server migration paths?
Thank you,
MaxWhy don't you post this to Oracle ng's.Imo users of db's other than
MS have more experience with other dbs than do MS users.
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:ev2rw$LZGHA.1180@.TK2MSFTNGP03.phx.gbl...
> Hi,
>
> We have an Oracle database with 3200 tables. We want to migrate this
> database tables (both structure and data) to SQL Server 2005.
>
> What would be the best way to do that? Is there any automated migration
> tool for doing that?
>
> Is there any white paper that explains Oracle to SQL Server migration
> paths?
>
> Thank you,
> Max
>
>|||You can review several migration solutions.
SSMA is a free tool.
You can access blow url.
http://www.microsoft.com/sql/solutions/ssm/ssmav2.mspx
Also, you can search microsoft.com using keword "SwisSQL".
"Maxwell2006"?? ??? ??:

> Hi,
>
> We have an Oracle database with 3200 tables. We want to migrate this
> database tables (both structure and data) to SQL Server 2005.
>
> What would be the best way to do that? Is there any automated migration to
ol
> for doing that?
>
> Is there any white paper that explains Oracle to SQL Server migration path
s?
>
> Thank you,
> Max
>
>|||> Why don't you post this to Oracle ng's.Imo users of db's other than
> MS have more experience with other dbs than do MS users.
IMHO, Max is more likely to get more/better help here with the migration
task. It won't hurt to ping Oracle forums too but I believe there is more
experience here migrating from Oracle to SQL Server than you'll find in
Oracle forums.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Dassin" <steve@.nospamrac4sql.net> wrote in message
news:%23HU9PHNZGHA.3752@.TK2MSFTNGP03.phx.gbl...
> Why don't you post this to Oracle ng's.Imo users of db's other than
> MS have more experience with other dbs than do MS users.
> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
> news:ev2rw$LZGHA.1180@.TK2MSFTNGP03.phx.gbl...
>|||In addition to hongju's response, check out
http://www.microsoft.com/technet/in...t.msp
x.
Hope this helps.
Dan Guzman
SQL Server MVP
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:ev2rw$LZGHA.1180@.TK2MSFTNGP03.phx.gbl...
> Hi,
>
> We have an Oracle database with 3200 tables. We want to migrate this
> database tables (both structure and data) to SQL Server 2005.
>
> What would be the best way to do that? Is there any automated migration
> tool for doing that?
>
> Is there any white paper that explains Oracle to SQL Server migration
> paths?
>
> Thank you,
> Max
>
>|||Ok where to find the devil as regards to 'the devil is in the details'.:)
www.rac4sql.net
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uk2FVrNZGHA.1580@.TK2MSFTNGP02.phx.gbl...
> IMHO, Max is more likely to get more/better help here with the migration
> task. It won't hurt to ping Oracle forums too but I believe there is more
> experience here migrating from Oracle to SQL Server than you'll find in
> Oracle forums.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Steve Dassin" <steve@.nospamrac4sql.net> wrote in message
> news:%23HU9PHNZGHA.3752@.TK2MSFTNGP03.phx.gbl...
>|||Having done that several times, I' d like to warn you: that's a very
serious undertaking. IMHO existing tools will only get you that far.
Blocking/concurrency implemetation is enirely different, and there are
lots of other differences. I can give you dozens of scenarios when a
"ported" application raises no syntax errors but gives different
results and/or performs very poorly.
To make long story short, be ready to rewrite at least some of your
code.|||As far as blocking/concurrency is concerned, SQL Server 2005 supports
versioning based concurrency now. So migration should be a bit smoother than
in previous SQL Server versions.
Bob Beauchemin
http://www.SQLskills.com
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1145590726.530114.67950@.e56g2000cwe.googlegroups.com...
> Having done that several times, I' d like to warn you: that's a very
> serious undertaking. IMHO existing tools will only get you that far.
> Blocking/concurrency implemetation is enirely different, and there are
> lots of other differences. I can give you dozens of scenarios when a
> "ported" application raises no syntax errors but gives different
> results and/or performs very poorly.
> To make long story short, be ready to rewrite at least some of your
> code.
>|||I agree, whilst on the Ascend course last year, which was run by, errrrm,
BOB! (Hi Bob) my face lit up when he told, and demonstrated the Row
versioning functionality.
Some code changes may be unavoidable, but SQL2005 can work almost exactly
like Oracle, although this does cost a little in overall performance
(currently, it seems to be about .5%) If a program is developed on SQL
only, I'd not suggest using it, but in an oracle simulation mode, it will
save alot of hassle.
Back to the original question. There are tools around which will port schema
and datastructures. I've not had too much experience with these, but it
seems that these will need checking, not only that the structure of the new
tables are the equivilant, but that all the constraints have also been
copied. In short the tools will get you started, but I doubt that they can
be trusted to complete the job.
Regards
Colin Dawson
www.cjdawson.com
"Bob Beauchemin" <bobb_no_spam@.SQLskills.com> wrote in message
news:%23aCJOYQZGHA.4612@.TK2MSFTNGP03.phx.gbl...
> As far as blocking/concurrency is concerned, SQL Server 2005 supports
> versioning based concurrency now. So migration should be a bit smoother
> than in previous SQL Server versions.
> Bob Beauchemin
> http://www.SQLskills.com
> "Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
> news:1145590726.530114.67950@.e56g2000cwe.googlegroups.com...
>|||Also refer http://www.akadia.com/services/sqlsrv2ora.html
Madhivanan

Oracle to SQL 2005 Replication

Greetings,
I am looking for a tool that i can use to replicate oracle tables into Sql
2005 database. Or if i can pull the data out of oracle using a sql tool that
would be perfect. The database tables are only 2GB or so. Can someone share
some insight on whether this is feasible or not.
Thanks In Advance
Deon
Transactional replication for Oracle Publishers (v 8.0.5 upwards) is built on
the Microsoft SQL Server transactional replication publishing architecture
for SQL 2005.
Pls have a look in BOL for more details.
Cheers,
Paul Ibison
|||Thanks Paul.
What is BOL?
"Paul Ibison" wrote:

> Transactional replication for Oracle Publishers (v 8.0.5 upwards) is built on
> the Microsoft SQL Server transactional replication publishing architecture
> for SQL 2005.
> Pls have a look in BOL for more details.
> Cheers,
> Paul Ibison

Oracle linked server tables

I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David
|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>
|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen
|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen
|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>
|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "this
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they could
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen
|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, but
> I don't see how they help me in my need. I can't see anything that gets me
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
> 9.2 databases. I want to be able to view the Oracle tables in SS Management
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can't
> get any farther with the resources at hand, and MS docs, as I said, come up
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen
|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen
|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>

Oracle linked server tables

I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "thi
s
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they cou
ld
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, b
ut
> I don't see how they help me in my need. I can't see anything that gets m
e
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Orac
le
> 9.2 databases. I want to be able to view the Oracle tables in SS Manageme
nt
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can
't
> get any farther with the resources at hand, and MS docs, as I said, come u
p
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>

Oracle linked server tables

I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
Standard edition installation. I can construct and run queries as long as I
know the table names and schemas.
However, tables for the linked servers are not readily visible. Is there
any way to accomplish this? I'd like to be able to browse the tables within
Management Studio as if they were native. I tried Publications but got an
error saying that "heterogeneous publications are supported on Enterprise
editions." ?
Thanks for any help,
Randall Arnold"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
> Standard edition installation. I can construct and run queries as long as
> I know the table names and schemas.
> However, tables for the linked servers are not readily visible. Is there
> any way to accomplish this? I'd like to be able to browse the tables
> within Management Studio as if they were native. I tried Publications but
> got an error saying that "heterogeneous publications are supported on
> Enterprise editions." ?
>
Not in the tree view, but these stored procedures should give you what you
want:
sp_linkedservers
sp_catalogs
sp_tables_ex
sp_columns_ex
David|||David,
Thanks for the answer, but I don't understand. Where do I find those
stored procedures? I've just skimmed the surface of SQL server up until
now so a lot of the higher functions are new to me. Microsoft
documentation is virtually useless as it says "here's what you need to
do" but consistently fails to say how.
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
>> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
>> Standard edition installation. I can construct and run queries as long
>> as I know the table names and schemas.
>> However, tables for the linked servers are not readily visible. Is there
>> any way to accomplish this? I'd like to be able to browse the tables
>> within Management Studio as if they were native. I tried Publications
>> but got an error saying that "heterogeneous publications are supported on
>> Enterprise editions." ?
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> David,
> Thanks for the answer, but I don't understand. Where do I find those
> stored procedures? I've just skimmed the surface of SQL server up until
> now so a lot of the higher functions are new to me. Microsoft
> documentation is virtually useless as it says "here's what you need to
> do" but consistently fails to say how.
> Randall Arnold
You find them in the MASTER table. Most of the "build in" SP's are
placed in the MASTER table (if not all..). Furthermore you can look them
up in Books On Line where you can get the syntax for executing them as well.
Regards
Steen|||Thanks Steen.
I am not exactly impressed with Books Online. As I said in the previous
post, I tend to find a lot of "what you need to do" but not very much "this
is how you do it", especially where fundamental details are concerned.
That's extremely frustrating to someone trying to learn. Too many
assumptions made on the parts of the doc guys. At the very least they could
provide links to how-to info within those high level docs. I provide that
feedback every chance I get.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Randall Arnold wrote:
>> David,
>> Thanks for the answer, but I don't understand. Where do I find those
>> stored procedures? I've just skimmed the surface of SQL server up until
>> now so a lot of the higher functions are new to me. Microsoft
>> documentation is virtually useless as it says "here's what you need to
>> do" but consistently fails to say how.
>> Randall Arnold
> You find them in the MASTER table. Most of the "build in" SP's are placed
> in the MASTER table (if not all..). Furthermore you can look them up in
> Books On Line where you can get the syntax for executing them as well.
> Regards
> Steen|||Well, thanks to Steen I did manage to find the system stored procedures, but
I don't see how they help me in my need. I can't see anything that gets me
to browsing the tables of linked servers.
I'd be more than willing to buy a book on this subject, and in fact I've
been looking, but I can't find one that covers this subject at the level I
need.
Bottom line, I've been using SQL Server at a basic level for a few years
(and using Access as a front end for tables and queries) and I'm having to
ramp up my level of involvement. I have to create a SQL server database
that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
9.2 databases. I want to be able to view the Oracle tables in SS Management
Studio the same way I do native tables. Importing doesn't do me any good
because I can't see a way to actually dynamically link to the tables as
opposed to a static import.
The SQL Server database is set. The linked servers are there. I just can't
get any farther with the resources at hand, and MS docs, as I said, come up
VERY short in the tutorial department.
Again, and and all guidance appreciated!
Randall Arnold
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:efMVTgxQGHA.5080@.TK2MSFTNGP10.phx.gbl...
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:%23s768fwQGHA.336@.TK2MSFTNGP12.phx.gbl...
>> I've successfully linked 2 Oracle 9.2 databases to my SQL Server 2005
>> Standard edition installation. I can construct and run queries as long
>> as I know the table names and schemas.
>> However, tables for the linked servers are not readily visible. Is there
>> any way to accomplish this? I'd like to be able to browse the tables
>> within Management Studio as if they were native. I tried Publications
>> but got an error saying that "heterogeneous publications are supported on
>> Enterprise editions." ?
> Not in the tree view, but these stored procedures should give you what you
> want:
> sp_linkedservers
> sp_catalogs
> sp_tables_ex
> sp_columns_ex
> David
>|||Randall Arnold wrote:
> Thanks Steen.
> I am not exactly impressed with Books Online. As I said in the previous
> post, I tend to find a lot of "what you need to do" but not very much "this
> is how you do it", especially where fundamental details are concerned.
> That's extremely frustrating to someone trying to learn. Too many
> assumptions made on the parts of the doc guys. At the very least they could
> provide links to how-to info within those high level docs. I provide that
> feedback every chance I get.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>> Randall Arnold wrote:
>> David,
>> Thanks for the answer, but I don't understand. Where do I find those
>> stored procedures? I've just skimmed the surface of SQL server up until
>> now so a lot of the higher functions are new to me. Microsoft
>> documentation is virtually useless as it says "here's what you need to
>> do" but consistently fails to say how.
>> Randall Arnold
>> You find them in the MASTER table. Most of the "build in" SP's are placed
>> in the MASTER table (if not all..). Furthermore you can look them up in
>> Books On Line where you can get the syntax for executing them as well.
>> Regards
>> Steen
>
I've used Books On Line for so long that I don't really remember my
first impression, but today I find it an excellent ressource and I can't
do my work without it. SQL 2005 Books On Line is a bit different and I
haven't quite get used to it yet, but I'm quite sure that it's just as
good as the old version...;-).
Regards
Steen|||Randall Arnold wrote:
> Well, thanks to Steen I did manage to find the system stored procedures, but
> I don't see how they help me in my need. I can't see anything that gets me
> to browsing the tables of linked servers.
> I'd be more than willing to buy a book on this subject, and in fact I've
> been looking, but I can't find one that covers this subject at the level I
> need.
> Bottom line, I've been using SQL Server at a basic level for a few years
> (and using Access as a front end for tables and queries) and I'm having to
> ramp up my level of involvement. I have to create a SQL server database
> that ties together tables spanning 3 or 4 domains, at least 2 of them Oracle
> 9.2 databases. I want to be able to view the Oracle tables in SS Management
> Studio the same way I do native tables. Importing doesn't do me any good
> because I can't see a way to actually dynamically link to the tables as
> opposed to a static import.
> The SQL Server database is set. The linked servers are there. I just can't
> get any farther with the resources at hand, and MS docs, as I said, come up
> VERY short in the tutorial department.
> Again, and and all guidance appreciated!
> Randall Arnold
Hi
I don't know what it is that fails for you when using the SP's but they
actually do work. The result may not be as simple as you want though.
I have an ORACLE server that are set up as a linked server on one of my
SQL servers, and the following works for me.
1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:
sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
This will then show you the fields in the table.
I don't know if this will suit your needs, but honestly I don't think
you can expect to get a "full blown" and easy overwiev of the table
structure of a linked server. If you need more than this, I think you'll
have to use the administration interface for the ORACLE server.
Regards
Steen|||I'm sure Books Online is great for people who have more experience than I do
with SQL Server. What I need are more tutorials, and I can't find them.
I still can't get this to work, and that's the source of my frustration. My
job is at stake.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:Ojw%23dSBRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
>> Thanks Steen.
>> I am not exactly impressed with Books Online. As I said in the previous
>> post, I tend to find a lot of "what you need to do" but not very much
>> "this is how you do it", especially where fundamental details are
>> concerned. That's extremely frustrating to someone trying to learn. Too
>> many assumptions made on the parts of the doc guys. At the very least
>> they could provide links to how-to info within those high level docs. I
>> provide that feedback every chance I get.
>> Randall Arnold
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:Op0hpO5QGHA.2176@.TK2MSFTNGP10.phx.gbl...
>> Randall Arnold wrote:
>> David,
>> Thanks for the answer, but I don't understand. Where do I find those
>> stored procedures? I've just skimmed the surface of SQL server up
>> until
>> now so a lot of the higher functions are new to me. Microsoft
>> documentation is virtually useless as it says "here's what you need to
>> do" but consistently fails to say how.
>> Randall Arnold
>> You find them in the MASTER table. Most of the "build in" SP's are
>> placed in the MASTER table (if not all..). Furthermore you can look them
>> up in Books On Line where you can get the syntax for executing them as
>> well.
>> Regards
>> Steen
>>
> I've used Books On Line for so long that I don't really remember my first
> impression, but today I find it an excellent ressource and I can't do my
> work without it. SQL 2005 Books On Line is a bit different and I haven't
> quite get used to it yet, but I'm quite sure that it's just as good as the
> old version...;-).
> Regards
> Steen|||Steen,
The problem is, as I said in another post, that I don't know what to do with
these stored procedures. I can't see how to execute them. I'm used to
running SQL Server stored procedures from an Access data project, and that's
really straight-forward. I see no way to run an sp in Management Studio.
The Help was no help whatsoever. Again: the docs say what to do, or what
something is, but not how I go about using it.
Very, very exasperating for someone trying to learn.
I also don't have direct access to the Oracle server. IT was reluctant to
even allow the read-only access I have. We are very bureaucratic here, and
developers are frowned upon.
I've determined, though, that I don't actually need to view the tables in SS
Management Studio-- I just need to link them into a new Access Data Project.
However, there is a known bug in Access that keeps DSNs from working, so
Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
server fails to save the connection information for the linked Oracle server
into its properties sheet. And for reasons I absolutely cannot fathom, MS
refuses to allow those property sheets to be edited once they're created!
So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
I am supposed to create and deploy this aggregated database, and the usual
MS nonsense keeps it from happening. I'm just a wee bit irritated about
that situation...
I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Randall Arnold wrote:
>> Well, thanks to Steen I did manage to find the system stored procedures,
>> but I don't see how they help me in my need. I can't see anything that
>> gets me to browsing the tables of linked servers.
>> I'd be more than willing to buy a book on this subject, and in fact I've
>> been looking, but I can't find one that covers this subject at the level
>> I need.
>> Bottom line, I've been using SQL Server at a basic level for a few years
>> (and using Access as a front end for tables and queries) and I'm having
>> to ramp up my level of involvement. I have to create a SQL server
>> database that ties together tables spanning 3 or 4 domains, at least 2 of
>> them Oracle 9.2 databases. I want to be able to view the Oracle tables
>> in SS Management Studio the same way I do native tables. Importing
>> doesn't do me any good because I can't see a way to actually dynamically
>> link to the tables as opposed to a static import.
>> The SQL Server database is set. The linked servers are there. I just
>> can't get any farther with the resources at hand, and MS docs, as I said,
>> come up VERY short in the tutorial department.
>> Again, and and all guidance appreciated!
>> Randall Arnold
> Hi
> I don't know what it is that fails for you when using the SP's but they
> actually do work. The result may not be as simple as you want though.
> I have an ORACLE server that are set up as a linked server on one of my
> SQL servers, and the following works for me.
> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
> REMARKS. Here you can find the table/view you want to get the info for.
> In my case the TABLE_CAT column are NULL in all cases, but I have values
> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
> 2. If I want to get the details of a specific table, I look up the table
> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
> belonging to the TABLE_SHEM "SYS".
> If I want to see the details of this table, I run:
> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
> This will then show you the fields in the table.
> I don't know if this will suit your needs, but honestly I don't think you
> can expect to get a "full blown" and easy overwiev of the table structure
> of a linked server. If you need more than this, I think you'll have to use
> the administration interface for the ORACLE server.
> Regards
> Steen
>|||Randall Arnold wrote:
> Steen,
> The problem is, as I said in another post, that I don't know what to do with
> these stored procedures. I can't see how to execute them. I'm used to
> running SQL Server stored procedures from an Access data project, and that's
> really straight-forward. I see no way to run an sp in Management Studio.
> The Help was no help whatsoever. Again: the docs say what to do, or what
> something is, but not how I go about using it.
> Very, very exasperating for someone trying to learn.
> I also don't have direct access to the Oracle server. IT was reluctant to
> even allow the read-only access I have. We are very bureaucratic here, and
> developers are frowned upon.
> I've determined, though, that I don't actually need to view the tables in SS
> Management Studio-- I just need to link them into a new Access Data Project.
> However, there is a known bug in Access that keeps DSNs from working, so
> Microsoft recommends using an ODC. Well, that doesn't work, either: SQL
> server fails to save the connection information for the linked Oracle server
> into its properties sheet. And for reasons I absolutely cannot fathom, MS
> refuses to allow those property sheets to be edited once they're created!
> So, due to this simple act of stupidity, I am just flat stuck in a catch-22.
> I am supposed to create and deploy this aggregated database, and the usual
> MS nonsense keeps it from happening. I'm just a wee bit irritated about
> that situation...
> I really appreciate your efforts, though. Thanks to MS, I guess I'm doomed.
> Randall Arnold
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
>> Randall Arnold wrote:
>> Well, thanks to Steen I did manage to find the system stored procedures,
>> but I don't see how they help me in my need. I can't see anything that
>> gets me to browsing the tables of linked servers.
>> I'd be more than willing to buy a book on this subject, and in fact I've
>> been looking, but I can't find one that covers this subject at the level
>> I need.
>> Bottom line, I've been using SQL Server at a basic level for a few years
>> (and using Access as a front end for tables and queries) and I'm having
>> to ramp up my level of involvement. I have to create a SQL server
>> database that ties together tables spanning 3 or 4 domains, at least 2 of
>> them Oracle 9.2 databases. I want to be able to view the Oracle tables
>> in SS Management Studio the same way I do native tables. Importing
>> doesn't do me any good because I can't see a way to actually dynamically
>> link to the tables as opposed to a static import.
>> The SQL Server database is set. The linked servers are there. I just
>> can't get any farther with the resources at hand, and MS docs, as I said,
>> come up VERY short in the tutorial department.
>> Again, and and all guidance appreciated!
>> Randall Arnold
>> Hi
>> I don't know what it is that fails for you when using the SP's but they
>> actually do work. The result may not be as simple as you want though.
>> I have an ORACLE server that are set up as a linked server on one of my
>> SQL servers, and the following works for me.
>> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
>> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
>> REMARKS. Here you can find the table/view you want to get the info for.
>> In my case the TABLE_CAT column are NULL in all cases, but I have values
>> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
>> 2. If I want to get the details of a specific table, I look up the table
>> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
>> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
>> belonging to the TABLE_SHEM "SYS".
>> If I want to see the details of this table, I run:
>> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
>> This will then show you the fields in the table.
>> I don't know if this will suit your needs, but honestly I don't think you
>> can expect to get a "full blown" and easy overwiev of the table structure
>> of a linked server. If you need more than this, I think you'll have to use
>> the administration interface for the ORACLE server.
>> Regards
>> Steen
>
Sorry...I never understood that you didn't knew how to actually run the
sp's. You simple have to open a query window and then type in e.g.
EXEC sp_tables_ex 'YourOracleServerName' and then hit Execute or F5.
This will excute the query.
Regards
Steen|||Thanks for your patience with my ignorance Steen.
At first I couldn't find a query window, but stumbled across the Database
Engine Query icon on the toolbar and that does the trick.
After some more digging, I realized that all I need to do is change the
connection properties of the linked servers that the Microsoft Access linked
table wizard creates. But as I said before, Microsoft for reasons that
completely mistify me locks the property sheet after the linked server is
created. I'd love to find a way to edit it but I haven't so far.
What I did find was another server procedure, sp_helplinkedsrvlogin, that MS
*claims* will enable the admin to change the logins for those linked
servers. I ran it, and it did pull up a table of every linked server and
its properties. What the sp did NOT enable, however, was (once again)
editing of those properties! Despite the fact that KB article 280106
implies that it does.
So, I remain stuck. I could navigate the Oracle tables IF I could get them
into my existing Access data Project. But the adp can't see my existing
linked servers and every time its wizard creates one the new linked server
lacks the proper connection values and I'm unable to edit them.
: (
Randall Arnold
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:eQiJgJFRGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Randall Arnold wrote:
>> Steen,
>> The problem is, as I said in another post, that I don't know what to do
>> with these stored procedures. I can't see how to execute them. I'm used
>> to running SQL Server stored procedures from an Access data project, and
>> that's really straight-forward. I see no way to run an sp in Management
>> Studio. The Help was no help whatsoever. Again: the docs say what to do,
>> or what something is, but not how I go about using it.
>> Very, very exasperating for someone trying to learn.
>> I also don't have direct access to the Oracle server. IT was reluctant
>> to even allow the read-only access I have. We are very bureaucratic
>> here, and developers are frowned upon.
>> I've determined, though, that I don't actually need to view the tables in
>> SS Management Studio-- I just need to link them into a new Access Data
>> Project. However, there is a known bug in Access that keeps DSNs from
>> working, so Microsoft recommends using an ODC. Well, that doesn't work,
>> either: SQL server fails to save the connection information for the
>> linked Oracle server into its properties sheet. And for reasons I
>> absolutely cannot fathom, MS refuses to allow those property sheets to be
>> edited once they're created! So, due to this simple act of stupidity, I
>> am just flat stuck in a catch-22. I am supposed to create and deploy this
>> aggregated database, and the usual MS nonsense keeps it from happening.
>> I'm just a wee bit irritated about that situation...
>> I really appreciate your efforts, though. Thanks to MS, I guess I'm
>> doomed.
>> Randall Arnold
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:u$Rjr0BRGHA.4956@.TK2MSFTNGP09.phx.gbl...
>> Randall Arnold wrote:
>> Well, thanks to Steen I did manage to find the system stored
>> procedures, but I don't see how they help me in my need. I can't see
>> anything that gets me to browsing the tables of linked servers.
>> I'd be more than willing to buy a book on this subject, and in fact
>> I've been looking, but I can't find one that covers this subject at the
>> level I need.
>> Bottom line, I've been using SQL Server at a basic level for a few
>> years (and using Access as a front end for tables and queries) and I'm
>> having to ramp up my level of involvement. I have to create a SQL
>> server database that ties together tables spanning 3 or 4 domains, at
>> least 2 of them Oracle 9.2 databases. I want to be able to view the
>> Oracle tables in SS Management Studio the same way I do native tables.
>> Importing doesn't do me any good because I can't see a way to actually
>> dynamically link to the tables as opposed to a static import.
>> The SQL Server database is set. The linked servers are there. I just
>> can't get any farther with the resources at hand, and MS docs, as I
>> said, come up VERY short in the tutorial department.
>> Again, and and all guidance appreciated!
>> Randall Arnold
>> Hi
>> I don't know what it is that fails for you when using the SP's but they
>> actually do work. The result may not be as simple as you want though.
>> I have an ORACLE server that are set up as a linked server on one of my
>> SQL servers, and the following works for me.
>> 1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
>> number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
>> REMARKS. Here you can find the table/view you want to get the info for.
>> In my case the TABLE_CAT column are NULL in all cases, but I have values
>> in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.
>> 2. If I want to get the details of a specific table, I look up the table
>> by using sp_tables_ex as above, and then find the TABLE_SCHEM and
>> TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
>> belonging to the TABLE_SHEM "SYS".
>> If I want to see the details of this table, I run:
>> sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'
>> This will then show you the fields in the table.
>> I don't know if this will suit your needs, but honestly I don't think
>> you can expect to get a "full blown" and easy overwiev of the table
>> structure of a linked server. If you need more than this, I think you'll
>> have to use the administration interface for the ORACLE server.
>> Regards
>> Steen
>>
> Sorry...I never understood that you didn't knew how to actually run the
> sp's. You simple have to open a query window and then type in e.g.
> EXEC sp_tables_ex 'YourOracleServerName' and then hit Execute or F5. This
> will excute the query.
> Regards
> Steen|||Randall Arnold wrote:
> Thanks for your patience with my ignorance Steen.
> At first I couldn't find a query window, but stumbled across the Database
> Engine Query icon on the toolbar and that does the trick.
> After some more digging, I realized that all I need to do is change the
> connection properties of the linked servers that the Microsoft Access linked
> table wizard creates. But as I said before, Microsoft for reasons that
> completely mistify me locks the property sheet after the linked server is
> created. I'd love to find a way to edit it but I haven't so far.
> What I did find was another server procedure, sp_helplinkedsrvlogin, that MS
> *claims* will enable the admin to change the logins for those linked
> servers. I ran it, and it did pull up a table of every linked server and
> its properties. What the sp did NOT enable, however, was (once again)
> editing of those properties! Despite the fact that KB article 280106
> implies that it does.
> So, I remain stuck. I could navigate the Oracle tables IF I could get them
> into my existing Access data Project. But the adp can't see my existing
> linked servers and every time its wizard creates one the new linked server
> lacks the proper connection values and I'm unable to edit them.
> : (
> Randall Arnold
Hi Randall
You're right that the article says that you can use
sp_helplinkedsrvlogin to set the login info, but that's not correct.
If you look up sp_helplinkedsrvlogin in Books On Line, it says that it
"Provides information about login mappings defined against a specific
linked server used for distributed queries and remote stored procedures".
I'm not sure where it is you can't change the properties for the linked
server. I'm not familiar with this MS Access Linked Server wizard, but
you you use EnterpriseManager (SQL 2000) or Microsoft SQL Server
Management Studio (SQL2005) you can the possibility to manage linked
servers. In Enterprise Manager is under "Security" -> "Linked Servers".
In Management Studio you'll find it under "Server Object" ->" Linked
Servers".
There are also a number of stored procedures that can be used -
sp_linkedservers
sp_addlinkedserver
sp_addlinkedsrvlogin
sp_droplinkedsrvlogin
sp_dropserver
You can look them up in Books On Line where you can find the syntax and
description.
Linked servers might not always be the easiest thing to work with, and
also I'm not quite sure if you can get all the info you're looking for
in this case.
Regards
Steen

Oracle DSV: Problems creating relationships with reported inconsistent datatypes.

I am adding tables to the DSV and adding the relationships but SSAS keeps complaining that the data types of the FK and PK tables do not match, even though I can see that they are in fact the same, ie NUMBER with no scale/precision set. Presumably this means Oracle uses a default?

Any ideas as to the fix? I have created a NamedQuery as a SELECT * FROM <table> and this seems to work but does seem to be a hack and is very annoying!

Searching connect, I found at that this is a bug. however, it was reported as fixed in SP1 but I am using SP2 CTP.. so I have added a new bug report for this under SP2.

the only workaround is to go back and edit the XML file. (View Code in the solution explorer context menu).

Oracle 9i -> SQL Server 2005: Snapshot agent aborts suddenly uninterrupted

Hi - I hope some of you can help me with this problem.

I'm trying to run the snapshot agent for 77 published tables, but the agent stops suddenly uninterrupted. It stops in the middle of taking a snapshot of a table containing 81,719,531 rows. The error message is pasted below.

Message
2006-06-14 05:33:33.53 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57200000 total rows copied)
2006-06-14 05:34:12.57 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57300000 total rows copied)
2006-06-14 05:34:51.23 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57400000 total rows copied)
2006-06-14 05:35:29.99 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57500000 total rows copied)
2006-06-14 05:36:09.82 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57600000 total rows copied)
2006-06-14 05:36:49.38 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57700000 total rows copied)
2006-06-14 05:37:28.56 [89%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_21_21" (57800000 total rows copied)
2006-06-14 05:37:35.36 [89%] The replication agent was shut down in response to an abort request.

Does anyone know what is causing this error and how I can possibly solve it?

Best Regards,
JB Plum

Was SQLServerAgent stopped by any chance?

-Raymond

|||

If this problem reproduces consistently without any attempts to manually shutdown the agent (ctrl-c, logoff, job or SQLServerAgent shutdown etc), it would appear that there is a very bad memory corruption somewhere and we would very much like to track it down. It would be really helpful to us if you can come up with an isolated repro (the rows that are causing trouble) and post it here or contact our product support engineers who will collect the relevant information. Are you running 64bit version of SQL Server by any chance?

-Raymond

|||

The SQLServerAgent was not by any chance stopped. I've produced the error twice.

JB Plum

|||

As mentioned in another post, there have been no attempts to manually shut down the agent.

As a matter of fact, I actually am running the 64bit version of SQL Server - is it of any importance?

Finally; I'm going to do some other work the next few days on the rest of the published tables, and then I'll try to take a snapshot of only the one table that seem's to cause the error - If you wish I can get back to you with the result of that test?

Best regards,

JB Plum

|||

To be honest, the situation would make more sense to me if the snapshot agent crashes on you instead of printing out the abort message which comes from a rather deliberate code path. An interesting thing to try would be to run the snapshot agent from the command line and see if you can still reproduce the problem. The reason why 64bit may be important is that we have different code paths for communiticating the "shutdown handle" between SQLServerAgent and the snapshot agent due to different handle sizes on different architectures. Since a bug in that area was fixed for SP1, I would not be surprised if there are more issues lurking in that area.

-Raymond

|||

Before going off to widely exotic theories, I should probably ask if there are any funny messages in the sqlserveragent log. It is also possible that a timeout is triggered somewhere given the relatively large interval between different bcp messages (which you can reduce by specifying a smaller bcp batch size for the snapshot agent)

-Raymond

|||

Ok - I'll try to run the snapshot agent from the command line and get back to you with the result in a few days.

If I get you right, you are saying, that If I can run the snapshot agent from the command line without errors, opposite to the error I'm getting by running the agent from SQL Server Management Studio, then there might be a bug related to the 64bit version of SQL Server?

//JB Plum

|||

So I'll try to specify another bcp batch size for the snapshot agent also. Could any other parameters be relevant also? For instance the commit batch size?

JB Plum

|||

Reducing the bcp batch size will be sufficient, thanks for your help.

-Raymond

|||

Ok, now I've runned the snapshot agent with a BcpBatchSize of 10000 rows and I stille get the error (see below).

Message
2006-06-16 18:50:10.27 [30%] Bulk copied 10000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72860000 total rows copied)
2006-06-16 18:50:12.41 [30%] Bulk copied 10000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72870000 total rows copied)
2006-06-16 18:50:14.87 [30%] Bulk copied 10000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72880000 total rows copied)
2006-06-16 18:50:16.96 [30%] Bulk copied 10000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72890000 total rows copied)
2006-06-16 18:50:19.37 [30%] Bulk copied 10000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72900000 total rows copied)
2006-06-16 18:50:21.93 [30%] Bulk copied 10000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72910000 total rows copied)
2006-06-16 18:50:24.55 [30%] Bulk copied 10000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72920000 total rows copied)
2006-06-16 18:50:26.15 [30%] The replication agent was shut down in response to an abort request.

Do you have any other idea in this area of what could cause the error ?

Now I'll run the snapshot agent from the command line to see if this produces the same error as running the snapshot agent from the SQL Server Management Studio. Then I'll get back to you again, if you haven't any other ideas?

Best regards,

JB Plum

|||

JB, if you can run the snapshot agent to completion from the command line, chances are good that the problem is somewhere in the interfacing code between SQLServerAgent and the snapshot agent (which may only happen on 64bit platform). In any case, I can't really say anything for certain given that I can only rely on your description of the problem on these forum postings. I may sound like I am nagging, but 1) have you checked the SQLServerAgent log and see if there were anything unusual that happened when the snapshot agent abort? 2) Are you running a clustered instance of SQLServer[Agent]?

-Raymond

|||

Ok, Raymond - here's our current situation.

I've now runned the snapshot agent from the command line without any difference. I used the "-OutputVerboseLevel 2" parameter - below is a sample of the output from the errorfile.

2006-06-19 00:31:35.58 [30%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72000000 total rows copied)
2006-06-19 00:31:58.76 [30%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72100000 total rows copied)
2006-06-19 00:32:23.00 [30%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72200000 total rows copied)
2006-06-19 00:32:46.59 [30%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72300000 total rows copied)
2006-06-19 00:33:10.37 [30%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72400000 total rows copied)
2006-06-19 00:33:34.34 [30%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72500000 total rows copied)
2006-06-19 00:33:58.22 [30%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72600000 total rows copied)
2006-06-19 00:34:22.22 [30%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72700000 total rows copied)
2006-06-19 00:34:45.37 [30%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72800000 total rows copied)
2006-06-19 00:35:08.34 [30%] Bulk copied 100000 rows from "ORAMSSQLREPL"."HREPL_ARTICLE_77_77" (72900000 total rows copied)
2006-06-19 00:35:16.11 [30%] The replication agent was shut down in response to an abort request.

Yes - I have checked the SQL Server Agent Log and nothing unsual happens when the SQL Server Agent aborts.

No - I'm not running a clustered instance of SQL Server

Now I'm going to start the snapshot agent with the "-BcpBatchSize 1" parameter. Then I will have a look at the first row not copied to see if the data in that row could have something to do with the error.

How can I else get this error solved?

//JB Plum

|||

This is as strange as I have feared, the only way we can track down the root cause is to use the debugger. Are you running the agent through a terminal service\remote desktop session? From your previous outputs, it doesn't look like the problem is deterministic either, but if possible, it would be really helpful if you can isolate the set of rows causing the problem. Are there anything interesting in the system event logs?

-Raymond

|||

JB, if you are interested, sent a private email to rmak@.microsoft.com so I can send you a few bootleg binaries that you can try out in an attempt to isolate the problem.

-Raymond