Hitting an Oracle view

I have been asked to include a view from our Oracle system in one of my SQL queries. As a programmer I can write querries all day long, but I have no administration skills.

Can someone just give me the basics on creating this ability? On my development system I have installed the Oracle client and configured an ODBC driver that points to the Oracle views. This has allowed me to create a linked table in Access, but I have never attempted to port this to SQL Server 2k.

I assume I just load the drivers on the production system as before, but do I then use an ODBC driver again, or is there a better method with SQL Server? If its the ODBC method, then how do I add a linked table in SQL?

Sorry for the basic questions, and I really appreciate any help.

Thansks,

RobI'd suggest just using the Microsoft provided drivers unless you need specific Oracle functionality. It makes life a lot simpler in the long run.

Just use Enterprise Mangler or sp_addlinkedserver to make the Oracle server a linked server from your SQL Server, and life ought to be lovely. I don't remember needing to add any drivers to make it work, but I almost always use the Enterprise Edition of MS-SQL so I'm not always aware of what ships/installs with other editions.

-PatP|||Thanks for that tip - I have looked into sp_addlinkedserver and I can probably figure it out, but using the mangler just seems easier to me. If I start up the register server wizard, it seems to only be looking for SQL servers. I don't see any way to change the provider. Is it possible that the developer edition I am using doesn't have the functionality?|||Like so:

-PatPsql

History Limit

I use SQL Server Management Studio and when I View History for a Job there
seem to be a 100 line limit. Some of my jobs have more than 100 steps so
some don't show. Is there a place you can increase that limit a bit?
Thanks
John
Yes -
Check properties of SQL Server Agent and then go to History.
There you can play with your settings.
Enjoy
Immy
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:uAdCacj4GHA.512@.TK2MSFTNGP06.phx.gbl...
>I use SQL Server Management Studio and when I View History for a Job there
>seem to be a 100 line limit. Some of my jobs have more than 100 steps so
>some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>
|||John Holt wrote:
> I use SQL Server Management Studio and when I View History for a Job there
> seem to be a 100 line limit. Some of my jobs have more than 100 steps so
> some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>
Right-click on SQL Server Agent, choose Properties, go to the History tab...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

History Limit

I use SQL Server Management Studio and when I View History for a Job there
seem to be a 100 line limit. Some of my jobs have more than 100 steps so
some don't show. Is there a place you can increase that limit a bit?
Thanks
JohnYes -
Check properties of SQL Server Agent and then go to History.
There you can play with your settings.
Enjoy
Immy
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:uAdCacj4GHA.512@.TK2MSFTNGP06.phx.gbl...
>I use SQL Server Management Studio and when I View History for a Job there
>seem to be a 100 line limit. Some of my jobs have more than 100 steps so
>some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>|||John Holt wrote:
> I use SQL Server Management Studio and when I View History for a Job there
> seem to be a 100 line limit. Some of my jobs have more than 100 steps so
> some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>
Right-click on SQL Server Agent, choose Properties, go to the History tab...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

History Limit

I use SQL Server Management Studio and when I View History for a Job there
seem to be a 100 line limit. Some of my jobs have more than 100 steps so
some don't show. Is there a place you can increase that limit a bit?
Thanks
JohnYes -
Check properties of SQL Server Agent and then go to History.
There you can play with your settings.
Enjoy
Immy
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:uAdCacj4GHA.512@.TK2MSFTNGP06.phx.gbl...
>I use SQL Server Management Studio and when I View History for a Job there
>seem to be a 100 line limit. Some of my jobs have more than 100 steps so
>some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>|||John Holt wrote:
> I use SQL Server Management Studio and when I View History for a Job there
> seem to be a 100 line limit. Some of my jobs have more than 100 steps so
> some don't show. Is there a place you can increase that limit a bit?
> Thanks
> John
>
Right-click on SQL Server Agent, choose Properties, go to the History tab...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

History Cube Design Question

Hi,

In SSAS 2005 - I have History cube with 2 partitions 2005 and 2006 data. Both has different tables in data source view. In cube both partitions are map to same measure group, so there is no different measure groups for 2005 and 2006 just to make it clear.

Time to time I need to add new measures in latest cube (2006 partition) data those measures don't have data for 2005 partition.

Right now when I add new measures in 2006 I have to full process the 2005 and 2006 both partitions. Is there any way or way to design my cube so that when I add new measures to cube I can only full process 2006 partition. If user selects my new measures with 2005 date they can get null/zero that's fine.

Thank you for sharing your ideas - Ashok

This is my third post, previous two different questions no one replied....I start thinking either I ask stupid questions or this site is not good for me. Fingers cross this time...Take it easy....

Are you saying you have dimesion members referenced by the later partition that do not exist for the earlier partition?

When you process the later partition you could uncheck the 'process related objects' option. And you could also process any changed dimensions seperately. I think this should work, but you'd have to try it out for yourself to determine any difficulties.

Try doing this using the impact analysis feature to get a good idea as to what will be affected before actually doing the processing.

|||

Hi Ashok,

What if you create a new measure group which contains only the new measures created for the 2006 partition? To facilitate this, you could add a named query fact table to the DSV, which returns the relevant columns from the 2006 table. Then, you should only need to process this measure group, when you add a new 2006 measure.

|||

Thanks Deepak and Dork,

My Dimension members are not changed for any partitions I am just adding new measures in later partition.

Let me correct one thing first my 2005 and 2006 partitions are based on same database view but I have added filter to partitions by joining with DIM_DT like YR = 2005 and YR = 2006.

Any way, Deepak your solution works fine only thing is now there is new measure group for end users and at one point I have to marge this with rest of the partitions and do full process for 2005 and 2006, may be end of the year will be good time.

Because I also have a cube with different linked cubes (like virtual cube in 2000) I created a new calculated measures in virtual cube for each new measures in new measure group and made it new measures visible false so at least users don't see new measure group they see new calculated measures. I think is looks more complex finally keeping new measure group for current year is good idea.

Let's see how it goes. Thanks again - Ashok

|||

Guys How about if I keep 10 extra user defined fields in fact table and map them when I have new measures and make them visible?

-Ashok

sql

Hints in Views

Can we use hints with in the view definition ?
Thanks in advance.
RajYes, just be sure that you really need the hint, as specifying the wrong
hint can impact performance negatively.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Vish" <mocherla_v@.hotmail.com> wrote in message
news:ujpIbVeRDHA.3132@.tk2msftngp13.phx.gbl...
> Can we use hints with in the view definition ?
> Thanks in advance.
> Raj
>|||Raj,
I hope the below article helps...it is right from BOL.
View Hints
View hints can be used only for indexed views. (An indexed
view is a view with a unique clustered index created on
it.) If a query contains references to columns that are
present both in an indexed view and base tables, and
Microsoft SQL ServerT query optimizer determines that
using the indexed view provides the best method for
executing the query, then the optimizer utilizes the index
on the view. This function is supported only on the
Enterprise and Developer Editions of the Microsoft SQL
Server 2000.
However, in order for the optimizer to consider indexed
views, the following SET options must be set to ON:
ANSI_NULLS, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL,
ANSI_PADDING, ARITHABORT, QUOTED_IDENTIFIERS
In addition, the NUMERIC_ROUNDABORT option must be set to
OFF.
To force the optimizer to use an index for an indexed
view, specify the NOEXPAND option. This hint may be used
only if the view is also named in the query. SQL Server
2000 does not provide a hint to force a particular indexed
view to be used in a query that does not name the view
directly in the FROM clause; however, the query optimizer
considers the use of indexed views even if they are not
referenced directly in the query.
View hints are allowed only in SELECT statements; they
cannot be used in views that are the table source in
INSERT, UPDATE, and DELETE statements.
Syntax
< view_hint > ::={ NOEXPAND [ , INDEX ( index_val [ ,...n ] ) ] }
Arguments
NOEXPAND
Specifies that the indexed view is not expanded when the
query optimizer processes the query. The query optimizer
treats the view like a table with clustered index.
INDEX ( index_val [ ,...n ] )
Specifies the name or ID of the indexes to be used by SQL
Server when it processes the statement. Only one index
hint per view can be specified.
INDEX(0) forces a clustered index scan and INDEX(1) forces
a clustered index scan or seek.
If multiple indexes are used in the single hint list, the
duplicates are ignored and the rest of the listed indexes
are used to retrieve the rows of the indexed view. The
ordering of the indexes in the index hint is significant.
A multiple index hint also enforces index ANDing and SQL
Server applies as many conditions as possible on each
index accessed. If the collection of hinted indexes does
not contain all columns referenced in the query, a fetch
is performed after retrieving all the indexed columns.
DeeJay
>--Original Message--
>Can we use hints with in the view definition ?
>Thanks in advance.
>Raj
>
>.
>

hii

Hiiiiii
I want a qry which will give me the list of views which are not used in
any SPs in that database. like suppose i have a view view1 and I have
used that view in one of my SPs and i have a view called . View2 its
just created but never called in any SPs. Somebody ll help me ?(reneeshprabha@.gmail.com) writes:

Quote:

Originally Posted by

I want a qry which will give me the list of views which are not used in
any SPs in that database. like suppose i have a view view1 and I have
used that view in one of my SPs and i have a view called . View2 its
just created but never called in any SPs. Somebody ll help me ?

That would be:

SELECT o.name
FROM sysobjects o
WHERE NOT EXISTS (SELECT *
FROM sysdepends d ON o.id = d.depid)
AND o.type = 'V'

However, be very very careful. If you recreate a view, all dependency
information is lost, and thus that view will appear as unused in this
query.

It may be better to script the stored procedures to a text file, and
then search the files for occurrences of the view name.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

High CPU usage help

Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
I/O is 36345 and Memory is 912
So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
0? If its waiting it shouldnt be doing much of anything. Our SQL server will
just randomly start crawling
JP
..NET Software Developer
The information you are seeing is cumulative for that spid. It isnt what it
is using at that point in time, but those statistics are the resources used
for the life of that spid.
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"JP" wrote:

> Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
> activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
> also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
> I/O is 36345 and Memory is 912
> So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
> 0? If its waiting it shouldnt be doing much of anything. Our SQL server will
> just randomly start crawling
> --
> JP
> .NET Software Developer

High CPU usage help

Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
I/O is 36345 and Memory is 912
So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
0? If its waiting it shouldnt be doing much of anything. Our SQL server will
just randomly start crawling
--
JP
.NET Software DeveloperThe information you are seeing is cumulative for that spid. It isnt what it
is using at that point in time, but those statistics are the resources used
for the life of that spid.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"JP" wrote:
> Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
> activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
> also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
> I/O is 36345 and Memory is 912
> So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
> 0? If its waiting it shouldnt be doing much of anything. Our SQL server will
> just randomly start crawling
> --
> JP
> .NET Software Developer

High CPU usage help

Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
activity log is showing several ProcessIDs that say "AWAITING COMMAND" and i
t
also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
I/O is 36345 and Memory is 912
So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
0? If its waiting it shouldnt be doing much of anything. Our SQL server will
just randomly start crawling
JP
.NET Software DeveloperThe information you are seeing is cumulative for that spid. It isnt what it
is using at that point in time, but those statistics are the resources used
for the life of that spid.
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"JP" wrote:

> Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
> activity log is showing several ProcessIDs that say "AWAITING COMMAND" and
it
> also says the Status is SLEEPING, However the CPU# is 235789 and the Phyic
al
> I/O is 36345 and Memory is 912
> So if the status is Sleeping and AWAITNG COMMAND, why are these numbers no
t
> 0? If its waiting it shouldnt be doing much of anything. Our SQL server wi
ll
> just randomly start crawling
> --
> JP
> .NET Software Developer

Hiding the report parameters when i click on the View report button

hi All,
i am working in SQL reporting services 2005, i have requirement that i
need to show all the report parameters in the report layout and user
can enter or select the values when i click on the view report button,
the report parameters should be hided. i am not sure that we could hide
the report parameters when i click on the view report button.
anyone knows how to implement this. please let me know.
Thank you in advance.
VinodAny idea on the below issue!!!!
thanks
Vinod
Vinod wrote:
> hi All,
>
> i am working in SQL reporting services 2005, i have requirement that i
> need to show all the report parameters in the report layout and user
> can enter or select the values when i click on the view report button,
> the report parameters should be hided. i am not sure that we could hide
> the report parameters when i click on the view report button.
> anyone knows how to implement this. please let me know.
> Thank you in advance.
> Vinod

Hiding Parts of a report.

I'm using a PageViewer control in SharePoint to view a Report in SQL Server,
so the URL that is pulling up is something like
http://servername/ReportServer/Pages/ReportViewer.aspx?%2fOceanSpray.SharePoint.OsciReports%2fSavings+By+Plant&rs:Command=Render
The report comes up and displays the report information the way that I want
it to, but what I would like to be able to do in some instance where I'm
doing this is to header the header information of the report. By the header
I'm refering to the Page Navigation, Zoom, Find, Export and Print Toolbar
that gets added above the report information.
Is there a way to remove this Toolbar?Search for URL Access in Books on line -.. The toolbar option ie
http://servername/reportserver?/Sales/YearlySalesSummary&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Paul" wrote:
> I'm using a PageViewer control in SharePoint to view a Report in SQL Server,
> so the URL that is pulling up is something like
> http://servername/ReportServer/Pages/ReportViewer.aspx?%2fOceanSpray.SharePoint.OsciReports%2fSavings+By+Plant&rs:Command=Render
> The report comes up and displays the report information the way that I want
> it to, but what I would like to be able to do in some instance where I'm
> doing this is to header the header information of the report. By the header
> I'm refering to the Page Navigation, Zoom, Find, Export and Print Toolbar
> that gets added above the report information.
> Is there a way to remove this Toolbar?

hiding folders and list view

Hello I have a few questions hoping someone can help

Any way to hide a report completely ? ie data source folders, subreports called from hyperlinks etc

Anyway off hiding the List view and restrict the user from selecting list view ?

Anyway off customising the reporting services top area which is in yellow and black ie put a logo in this area ?

thanks

Hi,

You cannot hide a report completly in the SSRS. It is not a problem to see the reports in details view. It is a communication issue with the end users not to use the subreports. Also, you cannot remove the "show details" button.

For custom look, you should create your ASP.NET application and use the ReportViewer control. In this case you can hide your reports

Regards,

Janos

|||

Do you want to hide the report or subreports?

If report,why do you want to hide a report,when permissions can play a role in this task.

Do you want to show the hyperlink conditionally.you have the option to write act as hyperlink as a function in the navigation property fx itself.

Why not,you can place a picture in the header? make it as template and use it when ever you want