Best Recovery model

What would be the best Recovery Model for: a database which is 4 gig in size and imports via MSAccess queries and also stored procedures approximately 400,000 meg of data each month (and some other update queries are run against it) and it is also queried off of for totals on weekly basis?

The problem is that the SQL Server box only has 512 meg of memory and the tranlog on this database grows tremendously each import and when update queries are run against it. This tends to slow things down a bit on our other databases. We are getting a new SQL Server box but until then, what would be the best recovery model? I currently have it as Bulk-Logged and allow the tranlog to grow by 10% (with a base of 250 meg). The tranlog grows to up to 5-10 gig and in order to shrink it, I have to change the recovery model to Simple, and then back to Bulk-Logged in order to shrink it (I've tried all the dbcc shrinkdatabase, dbcc shrinkfile, dbcc showcontig, and dbcc checkdb commands as well as BACKUP LOG dbName WITH TRUNCACTE_ONLY and nothing will shrink it unless I change the recovery model to simple.)Perhaps you could turn off transaction logging during bulk data loads.|||Thanks for the repy blindman,

I tried that and it slowed down the importing to a crawl (1-2 hours verses 15 minutes). I know I'm pushing the limits on the SQL Server box.

Best practice to pull data from sql server 2000 to sql server 2005 with dynamic queries

Hi There,

I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?

Thanks a lot!!

If you need to query sql server 2000 database to migrate data, then sql server 2005 import/export wizard might be a good one. Please take a look at http://msdn2.microsoft.com/en-us/library/ms141209.aspx. If you don't need to do query, bcp utility might be a good candidate. Please take a look at http://msdn2.microsoft.com/en-us/library/ms162802.aspx.

Thanks,

Junfeng

|||

First, set up the SQL 2000 server as a 'Linked Server' for the SQL 2005 server (See Books Online for details about Linked Servers.)

Then, using 'four-part naming conventions', you can just query between the two servers.

This example, when executed on the SQL 2005 server, would take data from the SQL 2000 server and insert it into the SQL 2005 server:

Code Snippet

INSERT INTO MyTable (Col1, Col2, Col3, etc.)
SELECT Col1, Col2, Col3, etc.
FROM MySQL2000Server.MyDatabase.dbo.MyTable
WHERE MyID IN ( 1, 2, 5, 10, 25 )

|||

Hi There,

Here's my requirements...

I have to run a query B against database B with results (list of ids) from a query A run against database A and then push the results back to database A. Query B is constructed dynamically from the results obtained from Query A. So I wouldlike to know what would be the best way to achive this?

Thanks a lot!!

Best practice for remote queries

Hi
Would it still be currently regarded as best practice to use OPENQUERY
to run remote queries?
Regards
RichardIf you have a linked server defined you can use
SELECT <> FROM Server.DataBase.dbo.Table
<richhollis@.gmail.com> wrote in message
news:1133219788.491783.204870@.g49g2000cwa.googlegroups.com...
> Hi
> Would it still be currently regarded as best practice to use OPENQUERY
> to run remote queries?
> Regards
> Richard
>

Best Performance?

Assume a heavy transaction environment.
Let's say we have the same database model,
same hardware, file usage, type of data, queries etc. on two servers.
Server A is configured with a RAID 5 on all the data disks (9 of them),
and Server B is configured with RAID 5 on the data disks (5 of them) but
transaction log and tempdb is separated to its own mirrored disks (4 of
them - 2 each).
Which SQL server will run faster in terms of query response time AND write
performance? Database in Server A or Server B?Assumimng disc io is the limiting factor then I would say B would be =most likely to outperform A. But even better would be lose the raid 5 =and use 0+1 on 4 discs. or possibly on 6 and lose the separation of =tempdb. Of course this will lose you some space over the raid 5, but =will perform better. Hardware raid vs S/W will alo make a difference. =How much separating tempdb gives you totally depends on the number of =queries generating large temporary results.
Opinions are likely to vary a lot and you haven't given much info to go =on.
Mike John
"Rick" <bob@.bob.net> wrote in message =news:3f858779$0$103$8f4e7992@.newsreader.goldengate.net...
> Assume a heavy transaction environment.
> > Let's say we have the same database model,
> same hardware, file usage, type of data, queries etc. on two servers.
> Server A is configured with a RAID 5 on all the data disks (9 of =them),
> and Server B is configured with RAID 5 on the data disks (5 of them) =but
> transaction log and tempdb is separated to its own mirrored disks (4 =of
> them - 2 each).
> > Which SQL server will run faster in terms of query response time AND =write
> performance? Database in Server A or Server B?
> >|||I completely agree with Mike John's remark to drop RAID5 completely,
certainly for a 'heavy transaction environment' as you describe. Check out
this link for some reasons why
http://www.miracleas.dk/BAARF/BAARF2.html
(OK it's Oracle/Unix, but it is as tru for the SQLServer/Win world too..)
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uRds7fpjDHA.2676@.TK2MSFTNGP11.phx.gbl...
Assumimng disc io is the limiting factor then I would say B would be most
likely to outperform A. But even better would be lose the raid 5 and use 0+1
on 4 discs. or possibly on 6 and lose the separation of tempdb. Of course
this will lose you some space over the raid 5, but will perform better.
Hardware raid vs S/W will alo make a difference. How much separating tempdb
gives you totally depends on the number of queries generating large
temporary results.
Opinions are likely to vary a lot and you haven't given much info to go on.
Mike John
"Rick" <bob@.bob.net> wrote in message
news:3f858779$0$103$8f4e7992@.newsreader.goldengate.net...
> Assume a heavy transaction environment.
> Let's say we have the same database model,
> same hardware, file usage, type of data, queries etc. on two servers.
> Server A is configured with a RAID 5 on all the data disks (9 of them),
> and Server B is configured with RAID 5 on the data disks (5 of them) but
> transaction log and tempdb is separated to its own mirrored disks (4 of
> them - 2 each).
> Which SQL server will run faster in terms of query response time AND write
> performance? Database in Server A or Server B?
>|||On Thu, 9 Oct 2003 11:06:17 -0500, "Rick" <bob@.bob.net> wrote:
>Assume a heavy transaction environment.
> Let's say we have the same database model,
>same hardware, file usage, type of data, queries etc. on two servers.
>Server A is configured with a RAID 5 on all the data disks (9 of them),
>and Server B is configured with RAID 5 on the data disks (5 of them) but
>transaction log and tempdb is separated to its own mirrored disks (4 of
>them - 2 each).
>Which SQL server will run faster in terms of query response time AND write
>performance? Database in Server A or Server B?
Well, like the other guys said, one would probably anticipate B
running a bit faster, but it's also going to depend on how much data
is cached, that is, on RAM size, and just how heavily tempdb gets
used, and for that matter, how heavily the log gets used. If it were
100% simple queries, A might even win, for a more usual mix of 90%
simple queries, 5% complex queries, and 5% highly localized updates,
you're probably close to the break-even point, assuming you're not
running short of disk IO bandwidth or CPU in either case A or case B.
J.

Best method for running several queries?

I have an SQL file saved from QA. It has several queries used for testing.
These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
specific statement to run in that file. This keeps everything from running
at once.
Problem is that I access the server from several computers via QA. The SQL
file with all of the above queries is usually on one computer. Should I
just store the SQL file on the SQL Server machine as an SQL file or a stored
procedure? What is best for this? This file isn't something I would ever
want an app to have access to. It's strictly for manual testing purposes
via QA.
Thanks,
BrettScript files are considered source code. So, you would want to put it in a
souce control server somewhere and just grab it when you need it. Storing
the script inside sqlserver is probably not a good idea in this case.
-oj
"Brett" <no@.spam.net> wrote in message
news:ORw4XLLLFHA.2136@.TK2MSFTNGP14.phx.gbl...
>I have an SQL file saved from QA. It has several queries used for testing.
>These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
>specific statement to run in that file. This keeps everything from running
>at once.
> Problem is that I access the server from several computers via QA. The
> SQL file with all of the above queries is usually on one computer. Should
> I just store the SQL file on the SQL Server machine as an SQL file or a
> stored procedure? What is best for this? This file isn't something I
> would ever want an app to have access to. It's strictly for manual
> testing purposes via QA.
> Thanks,
> Brett
>

Best filtering solution for performance?

Hello,

i have a report with approximately 20 reportparameters from queries. This is really slowing down the system, although the dataload (rows sent back) is not that huge. I read on the internet that there are basicly three approaches.

1, Query parameter with alot of diffrent datasources

2, Table filtering

3, Stored procedures

I wonder which approach is the best and why it takes such a long time to get the report with the report parameters (before generating the report itself).

Thank you for your help!

I would think stored procedures would be the best approach. Have you tried using SQL profile to trace where the slowdown is?

cheers,

Andrew

|||

Hello,

thank you for you fast reponse! I now changed to Stored Procedures, i thin in long term it is better to use SP. But now i Have problems with multivalued parameters, which will be sent as an array to SQL Server. I found some articles about this matter an will try to solve it. The performance problem seems to be solved although I don't know why. Will look into that later. Thanks fpr the profiler tip! Best regards!

Best Enterprise Datawarehouse

We currently run a MASSIVE enterprise data warehouse on SQL Server 2000 and it is struggling a bit with both user queries and daily loads.
Need opinions on
1. Is SQL Server the best product for the job
2. What are the alternatives
CheersWhat is massive ? Also, what is your hardware specs ?|||And what are you doing with it? Are you creating cubes off of it?|||Have about 7 + Terabytes of data - growing at 100 gb a month

Team of dedicated App Dev staff designing and developing OLAP cubes and publishing on the intranet using third party software|||What type of cubes ? What version of sql are you using ?|||What window do you have available to perform the daily loads? Approx how long does each load take?|||At that size I am sure MS would love to help you (and use you in their marketing :) )|||You can easily deploy SQL Analaysis Services in this case, check ver 2000 for more enhancements and information on MS SQL homepage.

HTH|||No one seems to have opinions on any other products other than SQL Server? I guess it is a SQL Server forum but like I have already said - this is our current platform and is Struggling !!!!|||Originally posted by aldo_2003
Have about 7 + Terabytes of data - growing at 100 gb a month

Team of dedicated App Dev staff designing and developing OLAP cubes and publishing on the intranet using third party software

Gotta ask...what's the subject of the data?

3rd party software?

I don't think (damn it, again) that ANY 3rd party software plans for anything that massive...

And EVERYONE is writting adhoc queries...right?

"This sounds like a job for "Super-Silver bullet""

And yes, MS would love to talk to you....|||Originally posted by aldo_2003
No one seems to have opinions on any other products other than SQL Server? I guess it is a SQL Server forum but like I have already said - this is our current platform and is Struggling !!!!

Be happy using Sybase IQ (http://www.sybase.com/products/bi/sybaseiq) !
=> T-SQL compliance
=> DSS database
=> performance++|||You have given very few information regarding your environment and the reasons you are struggling, other blaming SQL. We would be glad to help you if you could tell us more about your data warehouse. So we can see where your bottleneck is. But if you are looking for the answer of 'Yes, you need to switch to O.... and S.. platform', please go to O.... forum. Thanks.

behavior of SQL on joined queries

Hi all,

Currently our product has a setup that stores information about
transactions in a transaction table. Additionally, certain transactions
pertain to specific people, and extra information is stored in another
table. So for good or ill, things look like this right now:

create table TransactionHistory (
TrnID int identity (1,1),
TrnDT datetime,
--other information about a basic transaction goes here.
--All transactions have this info
Primary Key Clustered (TrnID)
)

Create Index TrnDTIndex on TransactionHistory(TrnDT)

create table PersonTransactionHistory (
TrnID int,
PersonID int,
--extended data pertaining only to "person" transactions goes
--here. only Person transactions have this
Primary Key Clustered(TrnID),
Foreign Key (TrnID) references TransactionHistory (TrnID)
)

Create Index TrnPersonIDIndex on PersonTransactionHistory(Person)

A query about a group of people over a certain date range might fetch
information like so:

select * from TransactionHistory TH
inner join PersonTransactionHistory PTH
on TH.TrnID = PTH.TrnID
where PTH.PersonID in some criteria
and TH.TrnDT between some date and some date

In my experience, this poses a real problem when trying to run queries
that uses both date and personID criteria. If my guesses are correct this
is because SQL is forced to do one of two things:

1 - Use TrnPersonIDIndex to find all transactions which match the person
criteria, then for each do a lookup in the PersonTransactionHistory to
fetch the TrnID and subsequently do a lookup of the TrnID in the clustered
index of the TransactionHistory Table, and finally determine if a given
transaction also matches the date time criteria.

2 - Use TrnDTIndex to final all transaction matching the date criteria,
and then perform lookups similar to the above, except for personID instead
of datetime.

Compounding this is my suspicion (based on performance comparison of when
I specify which indexes to use in the query vs when I let SQL Server
decide itself) that SQL sometimes chooses a very non optimal course. (Of
course, sometimes it chooses a better course than me - the point is I want
it to always be able to pick a good enough course such that I don't have
to bother specifying). Perhaps the table layout is making it difficult for
SQL Server to find a good query plan in all cases.

Basically I'm trying to determine ways to improve our table design here to
make reporting easier, as this gets painful when running report for
large groups of people during large date ranges. I see a few options based
on my above hypothesis, and am looking for comments and/or corrections.

1 - Add the TrnDT column to the PersonTransactionHistory Table as
well. Then create a foreign key relationship of PersonTransactionHistory
(TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and create
indexes on PersonTransactionHistory with (TrnDT, PersonID) and
(PersonID, TrnDT). This seems like it would let SQL Server make
much more efficient execution plans. However, I am unsure if SQL server
can leverage the FK on TrnDT to use those new indexes if I give it a query
like:

select * from TransactionHistory TH
inner join PersonTransactionHistory PTH
on TH.TrnID = PTH.TrnID
where PTH.PersonID in some criteria
and TH.TrnDT between some date and some date

The trick being that SQL server would know that it can use PTH.TrnDT and
TH.TrnDT interchangably because of the foreign key (this would support all
the preexisting existing queries that explicitly named TH.TrnDT - any that
didn't explicitly specify the table would now have ambigious column
names...)

2 - Just coalesce the two tables into one. The original intent was to save
space by not requiring extra columns about Persons for all rows, many of
which did not have anything to do with a particular person (for instance a
contact point going active). In my experience with our product, the end
user's decisions about archiving and purging have a much bigger impact
than this, so in my opinion efficient querying is more important than
space. However I'm not sure if this is an elegant solution either. It also
might require more changes to existing code, although the use of views
might help.

We also run reports based on other criteria (columns I replaced with
comments above) but none of them are as problematic as the situation
above. However, it seems that if I can understand the best way to solve
this, I will be able to leverage that approach if other types of reports
become problematic.

Any opinions would be greatly appreciated. Also any references to good
sources regarding table and index design would be helpful as well (online
or offline references...)

thanks,
DaveMetal Dave (metal@.spam.spam) writes:
> create table TransactionHistory (
> TrnID int identity (1,1),
> TrnDT datetime,
> --other information about a basic transaction goes here.
> --All transactions have this info
> Primary Key Clustered (TrnID)
> )
> Create Index TrnDTIndex on TransactionHistory(TrnDT)
> create table PersonTransactionHistory (
> TrnID int,
> PersonID int,
> --extended data pertaining only to "person" transactions goes
> --here. only Person transactions have this
> Primary Key Clustered(TrnID),
> Foreign Key (TrnID) references TransactionHistory (TrnID)
> )
> Create Index TrnPersonIDIndex on PersonTransactionHistory(Person)

Given your query, it could be a good idea to have the clustered index
on TrnDT and PersonID instead. The main problem now with the queries
is that SQL Server will have to make a choice between Index Seek +
Bookmark Lookup on the one hand, and Clustered Index Scan on the other.
This is a guessing game that does not always end up the best way.

Of course, you may have other queries that are best off with clustering
on the Pkey, but this does not seem likely. (Insertion may however
benefit from a montonically increasing index. A clustered index on
PersonID may cause fragmentation.)

> 1 - Add the TrnDT column to the PersonTransactionHistory Table as
> well. Then create a foreign key relationship of PersonTransactionHistory
> (TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and create
> indexes on PersonTransactionHistory with (TrnDT, PersonID) and
> (PersonID, TrnDT). This seems like it would let SQL Server make
> much more efficient execution plans. However, I am unsure if SQL server
> can leverage the FK on TrnDT to use those new indexes if I give it a query
> like:
> select * from TransactionHistory TH
> inner join PersonTransactionHistory PTH
> on TH.TrnID = PTH.TrnID
> where PTH.PersonID in some criteria
> and TH.TrnDT between some date and some date

Well, take a copy of the database and try it!

(But first try changing the clustered index.)

> 2 - Just coalesce the two tables into one. The original intent was to save
> space by not requiring extra columns about Persons for all rows, many of
> which did not have anything to do with a particular person (for instance a
> contact point going active).

Depends a little on the ration. If the PersonTransactionHistory is 50%
of all rows in the main table, collapsing into one is probably the best.
If it's 5%, I don't think it is.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 26 Oct 2004, Erland Sommarskog wrote:

> Given your query, it could be a good idea to have the clustered index
> on TrnDT and PersonID instead. The main problem now with the queries
> is that SQL Server will have to make a choice between Index Seek +
> Bookmark Lookup on the one hand, and Clustered Index Scan on the other.
> This is a guessing game that does not always end up the best way.
> Of course, you may have other queries that are best off with clustering
> on the Pkey, but this does not seem likely. (Insertion may however
> benefit from a montonically increasing index. A clustered index on
> PersonID may cause fragmentation.)

My intuition agrees with you regarding the index in this case. I'm
pretty sure the clustered bookmark scan kills us on many reports. However
I haven't looked with enough depth at the wide variety of queries we use
to know for sure where I should put the clustered index so I'm reserving
judgement for now. I'd also like to study a bit more first so that I
don't replace one hasty decision with another - it might solve ad
individual problem but exacerbate others.

For instance, I think

select * from PersonTransactionHistory PTH
inner join TransactionHistory TH on PTH.TrnID = TH.TrnID
where PTH.PersonID = 12345

would be harmed by moving the TH clustered index from TH.TrnID to
TH.TrnDT, as it would now have to make the same lookup vs scan choice in
order to perform the join. Does that make sound reasonable? And since it's
rare for us to access PTH without the inner join to TH, there are probably
many queries like this.

> > 1 - Add the TrnDT column to the PersonTransactionHistory Table as
> > well. Then create a foreign key relationship of PersonTransactionHistory
> > (TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and create
> > indexes on PersonTransactionHistory with (TrnDT, PersonID) and
> > (PersonID, TrnDT). This seems like it would let SQL Server make
> > much more efficient execution plans. However, I am unsure if SQL server
> > can leverage the FK on TrnDT to use those new indexes if I give it a query
> > like:
> > select * from TransactionHistory TH
> > inner join PersonTransactionHistory PTH
> > on TH.TrnID = PTH.TrnID
> > where PTH.PersonID in some criteria
> > and TH.TrnDT between some date and some date
> Well, take a copy of the database and try it!

I appreciate the value of experimentation and normally would do that but
if it didn't work that wouldn't necesarily prove to me that I wasn't
simply doing something wrong like not making the foreign key specific
enough or putting something in my query which made SQL server ignore this
potential valuable relationship. So I was basically wondering if there
were any good docs regarding what types of information SQL Server will and
will no leverage in its choices or whether someone familiar with those
rules had some feedback off the top of their head.

> > 2 - Just coalesce the two tables into one. The original intent was to save
> > space by not requiring extra columns about Persons for all rows, many of
> > which did not have anything to do with a particular person (for instance a
> > contact point going active).
> Depends a little on the ration. If the PersonTransactionHistory is 50%
> of all rows in the main table, collapsing into one is probably the best.
> If it's 5%, I don't think it is.

It's probably between 20% and 40% depending on the particular
installation. It's your rationale that for 50% the space saved is
negligible whereas for 5% is is not? For me it's as more about limiting
the changes to the client software (definitely keeping the tables
separate) vs speeding up queries (possible coalescing) rather than a space
consideration. I did a test once and recall discovering we took up nearly
as much or more space with our indexes than our tables anyway, so
coalescing might make a big space difference anyway. (This amount of index
space suprised me but I'm not sure if there is a good rule of thumb for
how much space indexes should take.)

Rereading the post I probably should have just asked for good table design
references right up front. Any takers?

Thanks for the feedback.

Dave|||Metal Dave (metal@.spam.spam) writes:
> For instance, I think
> select * from PersonTransactionHistory PTH
> inner join TransactionHistory TH on PTH.TrnID = TH.TrnID
> where PTH.PersonID = 12345
> would be harmed by moving the TH clustered index from TH.TrnID to
> TH.TrnDT, as it would now have to make the same lookup vs scan choice in
> order to perform the join. Does that make sound reasonable? And since it's
> rare for us to access PTH without the inner join to TH, there are probably
> many queries like this.

Let's assume for the example that the clustered index in FTH is on PersonID.
Then the join against TH on TrnID will be akin to Index Seek + Bookmark
Lookup, no matter if the index on TrnID is clustered or not. In both
cases you would expect a plan with a Nested Loop join which means that
for each in FTH you look up a row in TH. The only difference if the index
on TrnID is non-clustered, is that you will get a few more reads for
each access. Which indeed is not neglible, since it multiplies with the
number of rows for PersonID.

And just like "SELECT * FROM tbl WHERE nonclusteredcol = @.val" has a
choice between index seek and scan, so have this query. Rather than
nested loop, the optimizer could go for hash or merge join which would
mean a single scan of TH. I would guess that the probability for this is
somewhat higher with a NC index on TrnID.

Of course, you opt to change only FTH, if you like.

> I appreciate the value of experimentation and normally would do that but
> if it didn't work that wouldn't necesarily prove to me that I wasn't
> simply doing something wrong like not making the foreign key specific
> enough or putting something in my query which made SQL server ignore this
> potential valuable relationship. So I was basically wondering if there
> were any good docs regarding what types of information SQL Server will and
> will no leverage in its choices or whether someone familiar with those
> rules had some feedback off the top of their head.

SQL Server does look at constraints, but really how intelligent it is,
I have not dug into. Thus, my encouragement of experimentation.

> It's probably between 20% and 40% depending on the particular
> installation. It's your rationale that for 50% the space saved is
> negligible whereas for 5% is is not?

Actually, I was more thinking in terms of performance, but space and
performance are related. My idea was that with 50%, the space saved is not
worth the extra complexity, and performance may suffer. With 5%, you save a
lot of space, since FTH would be a small table.

Your concern of having to change the client is certainly not one to be
neglected, and if this is costly in development time, I don't think it's
worth it.

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

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

Beginner with crosstab queries

I am having headaches trying to display some data in one table.
An example of some of the contents from the table are below:

ID ENTRY_ID SCHOOL
--------
1 10 Arizona
1 20 Arizona State
1 30 Texas
2 10 Baylor
2 20 Texas
3 10 Colorado

As you can see each ID has its own entry id, depending on how many school are assigned to that ID. The table has 1 to up to 10 entry_ids for each ID. The schools can be any of 70+ schools in the database, not just these that I listed.

I would like to list each school in its own column like below:

school1 school2 school3
----------
Arizona Arizona State Texas

school1 school2 school3
----------
Baylor Texas null

school1 school2 school3
----------
Colorado null null

I already know ahead of time that the highest count of entry_id per id is 10 because of a Having query I ran. Will I need to create 10 separate Select statements for each scenario??

This is what I have so far, but this is for ids with 5 entry_ids:
SELECT a.school, b.school, c.school, d.school, e.school
FROM table a,
table b,
table c,
table d,
table e
WHERE a.id = b.id
AND b.id = c.id
AND c.id = d.id
AND d.id = e.id
AND a.school > b.school
AND b.school > c.school
AND c.school > d.school
AND d.school > e.school

Unfortunately, it will not work for anything other than rows with 5 entry_ids per ID. I would hate to write out 10 different UNIONs if there is an easier way.

Your help is much appreciated and I hope I have given enough detail for an answer.Try this link (http://asktom.oracle.com/pls/ask/f?p=4950:8:7213782571237635933::NO::F4950_P8_DISPL AYID,F4950_P8_CRITERIA:7086279412131,)
:rolleyes:

Beginner help stored procedures, also .net

Hi,
I'm not qualified enough to be called a beginner -- I could use some help.
I've written a few fairly simple queries and run them through the query
analyzer. I'd like to know 2 things:
How can I turn them into stored procedures?
How would I then run those stored procedures through vb.net?
Any help would be appreciated.
Thanks,
ArtArt
CREATE PROCEDURE myFirstSp
@.par INT
AS
SELECT OrderDate FROM Orders WHERE OrderId=@.par
--usage
EXEC myFirstSp @.par=10248
You will find more info about stored procedures in the BOL.
"Art" <Art@.discussions.microsoft.com> wrote in message
news:DEF405CC-9E63-4D9A-8405-A281CC991ABD@.microsoft.com...
> Hi,
> I'm not qualified enough to be called a beginner -- I could use some help.
> I've written a few fairly simple queries and run them through the query
> analyzer. I'd like to know 2 things:
> How can I turn them into stored procedures?
> How would I then run those stored procedures through vb.net?
> Any help would be appreciated.
> Thanks,
> Art|||You can find samples and tutorials on MSDN.com and ASP.net
"Art" <Art@.discussions.microsoft.com> wrote in message
news:DEF405CC-9E63-4D9A-8405-A281CC991ABD@.microsoft.com...
> Hi,
> I'm not qualified enough to be called a beginner -- I could use some help.
> I've written a few fairly simple queries and run them through the query
> analyzer. I'd like to know 2 things:
> How can I turn them into stored procedures?
> How would I then run those stored procedures through vb.net?
> Any help would be appreciated.
> Thanks,
> Art

Begining Queries

I am a novice SQL guy so you all take it easy on me.
I am trying to run a query and one of the columns of my has values that is
referenced in another table, i.e. for EmployedStatus I have a bunch a
numbers from aother table. I go to that table to see what the numbers mean
and there you go. What I would like to do is, say I am working with person
profile table and instead of showing the column "EmployedStatus" as a 63
(for example) for a record, I would like it to show what that 63 means from
the "desription" column of the table that the 63 is referenced to. I hope
that make sense and I would appreciate any help anyone can stand to stomach!
Thanks,
Aaron
It's ok Aaron, we all started out somewhere.
For reference, look in Books Online for the topic JOIN TABLES -its a complex set of topics and takes most of us a lot of practice to get it right.
Now about your problem. I'll create an example. (You can copy and paste this code into Query Analyzer and run it.)
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)
CREATE TABLE TableB
( Status int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status
--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
>I am a novice SQL guy so you all take it easy on me.
> I am trying to run a query and one of the columns of my has values that is
> referenced in another table, i.e. for EmployedStatus I have a bunch a
> numbers from aother table. I go to that table to see what the numbers mean
> and there you go. What I would like to do is, say I am working with person
> profile table and instead of showing the column "EmployedStatus" as a 63
> (for example) for a record, I would like it to show what that 63 means from
> the "desription" column of the table that the 63 is referenced to. I hope
> that make sense and I would appreciate any help anyone can stand to stomach!
> Thanks,
> Aaron
>
|||Thanks Arnie for the info, worked great.
If I could ask one more; How would I modify this code to to be able to
reference more than one field that uses the same table? For example to
continue with our example, I have a an EmployedStatusID that is referenced
by a number in an ID Table and it corresponding Description of Full-Time,
Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
in the same ID table with it's corresponding Description as Full-Time,
Half-Time, etc.. I tried to use the AND along with the Join and when I
added more than one, no data was displayed. If I run separate queries on
both, they show up correctly. Any suggestions?
Thanks again,
Aaron
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23JTR$oK$GHA.3584@.TK2MSFTNGP05.phx.gbl...
It's ok Aaron, we all started out somewhere.
For reference, look in Books Online for the topic JOIN TABLES -its a complex
set of topics and takes most of us a lot of practice to get it right.
Now about your problem. I'll create an example. (You can copy and paste this
code into Query Analyzer and run it.)
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)
CREATE TABLE TableB
( Status int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status
--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message
news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...
>I am a novice SQL guy so you all take it easy on me.
> I am trying to run a query and one of the columns of my has values that is
> referenced in another table, i.e. for EmployedStatus I have a bunch a
> numbers from aother table. I go to that table to see what the numbers
> mean
> and there you go. What I would like to do is, say I am working with
> person
> profile table and instead of showing the column "EmployedStatus" as a 63
> (for example) for a record, I would like it to show what that 63 means
> from
> the "desription" column of the table that the 63 is referenced to. I hope
> that make sense and I would appreciate any help anyone can stand to
> stomach!
> Thanks,
> Aaron
>
|||Aaron,
As you noticed, trying to retrieve fields from a JOIN table where the JOIN criteria is different doesn't work.
So there are a couple of different ways to make this work. One involves using a JOIN with a second virtual copy of the table, and the other involves using a sub-SELECT.
First, the JOIN using a second copy of the same table, and then the sub-select.
SET NOCOUNT ON
CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int,
StudentStatus int
)
CREATE TABLE Status
( StatusID int,
Description varchar(50)
)
INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63, 23 )
INSERT INTO Status VALUES ( 63, 'On Leave' )
INSERT INTO Status VALUES ( 21, 'Full Time' )
INSERT INTO Status VALUES ( 21, 'Half Time' )
INSERT INTO Status VALUES ( 23, 'Part Time' )
--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = s1.Description,
StudentStatus = s2.Description
FROM PersonProfile p
JOIN Status s1
ON p.EmployedStatus = s1.StatusID
JOIN Status s2
ON p.StudentStatus = s2.StatusID
--Use a Sub-SELECT
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.EmployedStatus
),
StudentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.StudentStatus
)
FROM PersonProfile p
--Clean up
DROP TABLE PersonProfile
DROP TABLE Status
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Aaron" <support@.pinetreeit.com> wrote in message news:129A1BD8-33A4-40D8-8B41-BC3A33BE3AEC@.microsoft.com...[vbcol=seagreen]
> Thanks Arnie for the info, worked great.
> If I could ask one more; How would I modify this code to to be able to
> reference more than one field that uses the same table? For example to
> continue with our example, I have a an EmployedStatusID that is referenced
> by a number in an ID Table and it corresponding Description of Full-Time,
> Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
> in the same ID table with it's corresponding Description as Full-Time,
> Half-Time, etc.. I tried to use the AND along with the Join and when I
> added more than one, no data was displayed. If I run separate queries on
> both, they show up correctly. Any suggestions?
> Thanks again,
> Aaron
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23JTR$oK$GHA.3584@.TK2MSFTNGP05.phx.gbl...
> It's ok Aaron, we all started out somewhere.
> For reference, look in Books Online for the topic JOIN TABLES -its a complex
> set of topics and takes most of us a lot of practice to get it right.
> Now about your problem. I'll create an example. (You can copy and paste this
> code into Query Analyzer and run it.)
> SET NOCOUNT ON
> CREATE TABLE PersonProfile
> ( EmployeeID int IDENTITY,
> LastName varchar(20),
> FirstName varchar(20),
> EmployedStatus int
> )
> CREATE TABLE TableB
> ( Status int,
> Description varchar(50)
> )
> INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
> INSERT INTO TableB VALUES ( 63, 'On Leave' )
> --Join the two tables
> SELECT
> p.EmployeeID,
> p.LastName,
> p.FirstName,
> CurrentStatus = b.Description
> FROM PersonProfile p
> JOIN TableB b
> ON p.EmployedStatus = b.Status
> --Clean up
> DROP TABLE PersonProfile
> DROP TABLE TableB
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Aaron" <support@.pinetreeit.com> wrote in message
> news:F61A75EB-2968-4356-A560-A5C2A9853B7E@.microsoft.com...