New to Pl/SQl and SQL

I am new to this group and beginner to SQL and PL/SQL, I installed Microsoft SQL Server 2000, learned the basic SQL statments and ran them in the SQL Query Analyzer.

Now I am learning some PL/SQL tutorials and was wondering if I could run PL/SQL scripts in the SQL Query Analyzer ? Any help would be highly appreciated.PL/SQL is Oracle's procedural extension to the SQL standard and I believe will only run against Oracle databases.|||If you want to try pl/sql and don't have oracle I believe the open source database postgres (www.postgres.org) is very similar and has a pl/sql equivalent in pg/sql|||Originally posted by axis
PL/SQL is Oracle's procedural extension to the SQL standard and I believe will only run against Oracle databases.

Thanks for your response, I dont have oracle database, I will try it against oracle db.

new to notifications services and want example of setting up email notifications

I am new to SQL 2005 and want to setup a notification service to email me when a job fails. How do I begin to set that up? Any basic examples I can check out?

This forum is dedicate to SQL Server Notification Services; you'll probably have more luck getting responses in the SQL Server Tools forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1) or the microsoft.public.sqlserver.server newsgroup.

HTH...

sql

new to notifications services and want example of setting up email notifications

I am new to SQL 2005 and want to setup a notification service to email me when a job fails. How do I begin to set that up? Any basic examples I can check out?

This forum is dedicate to SQL Server Notification Services; you'll probably have more luck getting responses in the SQL Server Tools forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1) or the microsoft.public.sqlserver.server newsgroup.

HTH...

new to ms sql having trouble with very basic commands

I'm more used to mysql, which i haven't coded for a while, but how do you show tables? or show databases you have available from the osql prompt?hello?|||Use the Information Schema

-- Show Tables
SELECT * FROM INFORMATION_SCHEMA.TABLES

besides the TABLES view of the Schema, there are many others

Here is a list of the views provided by SQL Server 2000

CHECK_CONSTRAINTS
Holds information about constraints in the database

COLUMN_DOMAIN_USAGE
Identifies which columns in which tables are user-defined datatypes

COLUMN_PRIVILEGES
Has one row for each column level permission granted to or by the current user

COLUMNS
Lists one row for each column in each table or view in the database

CONSTRAINT_COLUMN_USAGE
Lists one row for each column that has a constraint defined on it

CONSTRAINT_TABLE_USAGE
Lists one row for each table that has a constraint defined on it

DOMAIN_CONSTRAINTS
Lists the user-defined datatypes that have rules bound to them

DOMAINS
Lists the user-defined datatypes

KEY_COLUMN_USAGE
Lists one row for each column that's defined as a key

PARAMETERS
Lists one row for each parameter in a stored procedure or user-defined function

REFERENTIAL_CONSTRAINTS
Lists one row for each foreign constraint

ROUTINES
Lists one row for each stored procedure or user-defined function

ROUTINE_COLUMNS
Contains one row for each column returned by any table-valued functions

SCHEMATA
Contains one row for each database

TABLE_CONSTRAINTS
Lists one row for each constraint defined in the current database

TABLE_PRIVILEGES
Has one row for each table level permission granted to or by the current user

TABLES
Lists one row for each table or view in the current database

VIEW_COLUMN_USAGE
Lists one row for each column in a view including the base table of the column where possible

VIEW_TABLE_USAGE
Lists one row for each table used in a view

VIEWS
Lists one row for each view

-------------
You probably need to get hold of the SQL books online and the SQL tools; that will help quite a bit.|||thanks, thats been most helpful,

I'm going to bookmark this page :)

which sql tools are you reffering to?

New to MDX - Querying Dimensions

This seems like a basic need that I don't understand.

I would like to populate cells with "aggregated" attributes.

For example, I have a Store with measures such as Sales and Expenses. I'd like to include in the report the Location and Size, both of which are dimensions. I want one record per store. The size of the store may change, so if I add Size to the axis, then I may get duplicate rows. Instead, I want the most recent store size to populate the cell.

I would like the query to return Store, Sales, Expenses, Location and Size; the latter two being attributes I don't want to slice my cube with.

What is the best way to accomplish this in MDX?

Thanks.

So, Store is a Type 2 dimension. Do location and size describe the store? If so, these are attributes within your Store dimension, right?

If these are all part of the same dimension, then you need some property from which you can determine which is the most recent version of a store. In your cube, how would you determine that two stores reference the same object and that one is a newer version of the other?

Bryan

|||

If location and size are completely separate dimensions then you are going to have trouble. The only way that AS can figure out that a particular store has a particular size is to look for Sales or Expense records at the intersection of those two dimensions. I would think that Size and Location should be modelled as attributes of the store dimension (as type 2 changing attributes) and it is probably going to make things easier if you create a "Current Size" attribute.

|||

Thanks for the replies.

Actually Stores is not a slowly changing dimension. I avoided a Snowflake Schema to keep things simple/fast.

Size and location are materialized dimensions.

I don't mind revising the Schema if necessary, but I want to make sure that if I make such a low-level change to the design that it's the right choice.

I want to get the size of the store based on the latest transaction date (it's the logic for the report....). With my limited knowledge of MDX, this is what I came up with:

DistinctCount(Descendants([Size],1))

This gives me the count of the number of sizes for the store along the time dimension in my query for the given period. The problem is that Count (as opposed to DistinctCount) returns the total number of unique sizes. Only distinct count returns the correct number of intersecting sizes. I can only get the count. I can't get the members. The following variations don't work:

Count(Distinct(Descendants([Size],1)))

Count(NonEmpty(Descendants([Size],1)))

Count(Descendants(NonEmpty([Size]),1))

Note that I'm using Store, Size, and Location as an analogy. I can't give too many actual details about my schema as a matter of confidentiality.

Any ideas on how to accomplish my goal?

Thanks.

|||

It can be done with independant dimensions, but the perfoermance might not be that good. Basically, to find what you are after SSAS would have to look at all the possible combinations of Store, Size and Date and then find the last non-empty one.

Off the top of my head I think something like the following might work.

Generate([Store].[Store].[Store]

,TAIL(

NONEMPTY(

CROSSJOIN(

[Size].[Size].[Size]

,[Date].[Date].[Date]

)

,[Measures].[Sales]

)

,1

)

)

You don't necessarily need a snowflake to implement a slowly changing dimension. You should also note that snowflake and star don't really affect the performance for MOLAP based cubes. They do affect the processing time and in SSAS 2005, snowflake will usually be faster. It's hard to say if separate dimensions or attributes of one dimension will be faster without knowing more specifics, but you will often see performance improvments by reducing the number of distinct dimensions. In the Store analogy I would say that the sizes would not change all that often, so I would expect that implementing it as an SCD would result in a performance increase.

sql

New to MDX - Querying Dimensions

This seems like a basic need that I don't understand.

I would like to populate cells with "aggregated" attributes.

For example, I have a Store with measures such as Sales and Expenses. I'd like to include in the report the Location and Size, both of which are dimensions. I want one record per store. The size of the store may change, so if I add Size to the axis, then I may get duplicate rows. Instead, I want the most recent store size to populate the cell.

I would like the query to return Store, Sales, Expenses, Location and Size; the latter two being attributes I don't want to slice my cube with.

What is the best way to accomplish this in MDX?

Thanks.

So, Store is a Type 2 dimension. Do location and size describe the store? If so, these are attributes within your Store dimension, right?

If these are all part of the same dimension, then you need some property from which you can determine which is the most recent version of a store. In your cube, how would you determine that two stores reference the same object and that one is a newer version of the other?

Bryan

|||

If location and size are completely separate dimensions then you are going to have trouble. The only way that AS can figure out that a particular store has a particular size is to look for Sales or Expense records at the intersection of those two dimensions. I would think that Size and Location should be modelled as attributes of the store dimension (as type 2 changing attributes) and it is probably going to make things easier if you create a "Current Size" attribute.

|||

Thanks for the replies.

Actually Stores is not a slowly changing dimension. I avoided a Snowflake Schema to keep things simple/fast.

Size and location are materialized dimensions.

I don't mind revising the Schema if necessary, but I want to make sure that if I make such a low-level change to the design that it's the right choice.

I want to get the size of the store based on the latest transaction date (it's the logic for the report....). With my limited knowledge of MDX, this is what I came up with:

DistinctCount(Descendants([Size],1))

This gives me the count of the number of sizes for the store along the time dimension in my query for the given period. The problem is that Count (as opposed to DistinctCount) returns the total number of unique sizes. Only distinct count returns the correct number of intersecting sizes. I can only get the count. I can't get the members. The following variations don't work:

Count(Distinct(Descendants([Size],1)))

Count(NonEmpty(Descendants([Size],1)))

Count(Descendants(NonEmpty([Size]),1))

Note that I'm using Store, Size, and Location as an analogy. I can't give too many actual details about my schema as a matter of confidentiality.

Any ideas on how to accomplish my goal?

Thanks.

|||

It can be done with independant dimensions, but the perfoermance might not be that good. Basically, to find what you are after SSAS would have to look at all the possible combinations of Store, Size and Date and then find the last non-empty one.

Off the top of my head I think something like the following might work.

Generate([Store].[Store].[Store]

,TAIL(

NONEMPTY(

CROSSJOIN(

[Size].[Size].[Size]

,[Date].[Date].[Date]

)

,[Measures].[Sales]

)

,1

)

)

You don't necessarily need a snowflake to implement a slowly changing dimension. You should also note that snowflake and star don't really affect the performance for MOLAP based cubes. They do affect the processing time and in SSAS 2005, snowflake will usually be faster. It's hard to say if separate dimensions or attributes of one dimension will be faster without knowing more specifics, but you will often see performance improvments by reducing the number of distinct dimensions. In the Store analogy I would say that the sizes would not change all that often, so I would expect that implementing it as an SCD would result in a performance increase.

New Member | Need Tutorial

Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAKHere's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK|||Here is a link to Books Online
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
--
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
? Administering Microsoft SQL Server 2000
? Programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
--
Br,
Mark Broadbent
mcdba , mcse+i
============="GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||If your new to SQL Server as an Administrator then I would
recommend the book SQL Server 2000 Step By Step. Its a
really good book for the beginner, I use it in mentoring
new dba's.
J
>--Original Message--
>Hi All,
> I am a new member of this group. Can any one guide me to
get more basic and
>intermediate level of learning MS-SQL-Server. I need
tutorials, downloads
>thank you
>GAK
>
>.
>

New Member | Need Tutorial

Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK
Here's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK
|||Here is a link to Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
Administering Microsoft SQL Server 2000
Programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||w
"GAK" wrote:

> Hi All,
> I am a new member of this group. Can any one guide me to get more basic and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
>

New Member | Need Tutorial

Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAKHere's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK|||Here is a link to Books Online
http://www.microsoft.com/sql/techin.../2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
Administering Microsoft SQL Server 2000
programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||w
"GAK" wrote:

> Hi All,
> I am a new member of this group. Can any one guide me to get more basic a
nd
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
>