need brief answers

what are pseudo tables
how queries run by MSDE
how stored procedures run in backend by MSDE
how triggers run by MSDE
where triggers and stored proc stored in MSDE and in which form
where logs are being maintained of transactions/DML statement by default

what are pseudo tables - Never heard of them, you may mean Derived Tables (A table created in a query that lives only during that query), or Views (a sql statement describing a virtual table)

how queries run by MSDE - You have to use a tool, like Query Analyzer or a client application to send queies to the MSDE engine

how stored procedures run in backend by MSDE - Same as above

how triggers run by MSDE - Triggers are run automatically by the database engine when the proper event happens to a table with a trigger on it.

where triggers and stored proc stored in MSDE and in which form - They are stored in system tables, you access them to create and edit them through Enterprise Manager or writing a DDL query in Query Analyzer

where logs are being maintained of transactions/DML statement by default - The log file(s) are where ever you tell the database to put them when you create a database. By default I think they are in the <SQl Server install location>\Data folder.

|||I sometimes use the term "pseudo table" to describe the INSERTED and DELETED logical tables that are available in triggers. I am not sure if that is what applies here or not.|||ah, good point. I have never heard that term though, always heard them referred to as virtual tables.

Need assistance

Am a newbie to SQL QUERIES (reporting) - Please assist
Scenario:
I have a simple table where I have columns like
empno
empname
latedate
reasonlate
justification
This table contains mulitple data for a single empno as you can see the
structure
for eg:
empno empname latedate reasonlate justification
101 Kevin 10/1/2005 Business Training day 1
101 Kevin 10/2/2005 Business Training day 2
101 Kevin 10/3/2005 Business Training day 3
103 Tracy 10/1/2005 Personal Sick
103 Tracy 10/3/2005 Business Seminar
...
...
I need to have a report like
Kevin (101)
10/1/2005 Business Training day 1
10/2/2005 Business Training day 2
10/3/2005 Business Training day 3
Tracy
10/1/2005 Personal Sick
10/3/2005 Business Seminar
FYI: I also have another table as EmpMaster with empno and name, so
that I can fetch those data from there.
Someone please assist me in preparing this report
Thankyou in advance
Best Regards
ShaninThis script returns data for a specified employee:
declare @.emp_name <datatype>
set @.emp_name = 'Kevin'
select latedate
,reasonlate
,justification
from dbo.EmpMaster
inner join <other_table>
on <other_table>.empno = dbo.EmpMaster.empno
where (dbo.EmpMaster.empname = @.emp_name)
You could design a procedure based on the query above.
To help you find a better solution we'd need to see DDL, more sample data
and preferably a more elaborate description of expected results.
ML|||Thank you ML for the reply
I actually need to use this script in an ASP file and generate a
report, I will not be able use hard coding, should be dynamic.
I forgot to provide the information that the core data is in a table
called HRDATA where I have those columns
empno
empname
latedate
reasonlate
justification
and have another master table viz. EmpMaster just in case we need to
join or fetch empno and empname alone
By the way what's DDL ?
Regards
Shanin|||DDL = Data Definition Language
For relevant info, please see:
http://www.aspfaq.com/etiquette.asp?id=5006
If you need to access data from a web page, then using a stored procedure is
the best way to do it. The procedure itself may not be dynamic, but the
purpose it serves is far from being "hard coded".
After you provide us with better specifications, we can help you design a
better solution.
Maybe this might also be a genuine opportunity to wipe the dust from your
copy of Books Online. :)
ML|||The specification was the one I provided above, ok let me be more
precise
I have a table viz. HRDATA andthe columns to be considered from this
are
empno - float(8)
empname - varchar(50)
loccode - varchar(50)
latedate - datetime
reasonlate-varchar(100)
justification-varchar(900)
approved-numeric(9)
now each time a employee submits his data from a ASP web form for a
certain date, a record is created in this table, hence for each date he
is late or absent he shall submit his justification, and hence the
records are like below
empno empname loccode latedate reasonlate justification
approved
101 Kevin JJ 10/1/2005 Business Training
day 1 1
101 Kevin JJ 10/2/2005 Business Training
day 2 1
101 Kevin JJ 10/3/2005 Business Training
day 3 1
103 Tracy OL 10/1/2005 Personal Sick
2
103 Tracy OL 10/3/2005 Business Seminar
1
now the HR Manager needs a report like the one below, grouped by
Empname, number and loccode
Kevin (101) - JJ
10/1/2005 Business Training day 1
10/2/2005 Business Training day 2
10/3/2005 Business Training day 3
Tracy (103) - OL
10/1/2005 Personal Sick
10/3/2005 Business Seminar
I have another master table viz. EmpMaster where I have all the
employees name, no, and loccode. Hope am repeating what I said in my
first post.
Ths is the actual scenario, I guess it's very simple for SQL GURUS..
Regards
Shanin|||Thankx guys, I have made it possible through ASP coding itself.
Regards
Shanin|||> now the HR Manager needs a report like the one below, grouped by
> Empname, number and loccode
The query below will provide the needed data. However, the grouping in your
specification is really report formatting (i.e. group section headers) and
is beyond the scope of SQL. The particulars depend on your reporting tool.
SELECT
empno,
empname,
loccode,
latedate,
reasonlate,
justification
FROM HRDATA
ORDER BY
empname,
empno,
loccode
The EmpMaster table isn't needed since HRDATA contains the required info.
In fact, redundant non-key data like empname is a big red flag in a
relational database. There are some cases where redundancy is deliberately
introduced but I get the feeling that poor design is the case here. Which
name would your HR Manager expect when you have different names in both
tables for the same employee (empno)?
In the future, please include the DDL scripts and sample data like the
following. Many of your fellow SQL Server community members will take the
time to develop and test a solution to your problem. Scripts eliminate
ambiguity and is quite time consuming to construct these scripts from
narrative.
CREATE TABLE HRDATA
(
empno float(8),
empname varchar(50),
loccode varchar(50),
latedate datetime,
reasonlate varchar(100),
justification varchar(900),
approved numeric(9)
)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/1/2005', 'Business', 'Training day 1', 1)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/2/2005', 'Business', 'Training day 2', 1)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/3/2005', 'Business', 'Training day 3', 1)
INSERT INTO HRDATA VALUES
(103, 'Tracy', 'OL', '10/1/2005', 'Personal', 'Sick', 2)
INSERT INTO HRDATA VALUES
(103, 'Tracy', 'OL', '10/3/2005', 'Business', 'Seminar', 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Cupid Shan" <shaninraja@.gmail.com> wrote in message
news:1130751379.384631.160800@.g44g2000cwa.googlegroups.com...
> The specification was the one I provided above, ok let me be more
> precise
> I have a table viz. HRDATA andthe columns to be considered from this
> are
> empno - float(8)
> empname - varchar(50)
> loccode - varchar(50)
> latedate - datetime
> reasonlate-varchar(100)
> justification-varchar(900)
> approved-numeric(9)
> now each time a employee submits his data from a ASP web form for a
> certain date, a record is created in this table, hence for each date he
> is late or absent he shall submit his justification, and hence the
> records are like below
> empno empname loccode latedate reasonlate justification
> approved
> 101 Kevin JJ 10/1/2005 Business Training
> day 1 1
> 101 Kevin JJ 10/2/2005 Business Training
> day 2 1
> 101 Kevin JJ 10/3/2005 Business Training
> day 3 1
> 103 Tracy OL 10/1/2005 Personal Sick
> 2
> 103 Tracy OL 10/3/2005 Business Seminar
> 1
> now the HR Manager needs a report like the one below, grouped by
> Empname, number and loccode
> Kevin (101) - JJ
> 10/1/2005 Business Training day 1
> 10/2/2005 Business Training day 2
> 10/3/2005 Business Training day 3
> Tracy (103) - OL
> 10/1/2005 Personal Sick
> 10/3/2005 Business Seminar
> I have another master table viz. EmpMaster where I have all the
> employees name, no, and loccode. Hope am repeating what I said in my
> first post.
> Ths is the actual scenario, I guess it's very simple for SQL GURUS..
> Regards
> Shanin
>

Need Advice/Help on querying a different db

I have 2 different database's on the same server. I'm trying to create a stored proc that resides in Reporting database but queries against the Call database. 4 part naming convention gives me an error of 'invalid object name' What am I forgetting here?My first guess would be that you forgot how to spell the object name. The next guess would be that you are doing something that doesn't make sense, like SELECT * FROM myStoredProcedure or the equivalent.

-PatP|||stored procedure lies in ABC4_Dev1_Reporting
Data resides in ABC_Dev1_Call1

From Reporting db in query analyzer I cannot get the following query to run

select * from dbo.ABC_Dev1_Call1.Call (Call is the table)

What am I missing?
Thanks in advance!|||Ah, a snippet of code is worth a thousand words of description! I'd use:SELECT *
FROM ABC_Dev1_Call1.dbo.Call-PatP|||Ah... very interesting... I'm claiming a bad case of the friday's on that one. I owe you one. Thanks a million. Have a good weekend.|||Listen...if Pat collected them all...he'd be hammered all year...

You threw me with the 4 part naming convention....

See what happens when I take a break and get a sandwich...

Need a solution ... urgently

These two queries when executed seperately give results in under 10 secs

A union between these two does not give results even after 20 minutes ...

Any idea why this is happening


SELECT
T001W.NAME1,
t25a5.bezek SKU,
QTY = SUM(CASE MSEG.BWART
WHEN '101' THEN MSEG.ERFMG
WHEN '102' THEN (-1)*MSEG.ERFMG END),
YPLNT.VKGRP
FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a5
WHERE
MSEG.MANDT = MKPF.MANDT
AND MKPF.MANDT = MARA.MANDT
and mkpf.mandt =yplnt.mandt
and mkpf.mandt = t25a5.mandt
AND MKPF.MBLNR = MSEG.MBLNR
AND MARA.MATNR = MSEG.MATNR
AND YPLNT.PPLNT= MSEG.WERKS
AND MSEG.WERKS = T001W.WERKS
and t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3)
AND MARA.PRDHA <> ''
AND MKPF.VGART IN ('WR','WF')
AND MKPF.MJAHR=YEAR(@.BUDAT1)
AND MSEG.AUFNR IS NOT NULL
AND MSEG.BWART IN ('101','102')
GROUP BY t25a5.bezek,T001W.NAME1,YPLNT.VKGRP

SELECT
T001W.NAME1,
t25a2.bezek SKU,
QTY = SUM(CASE MSEG.BWART
WHEN '101' THEN MSEG.ERFMG
WHEN '102' THEN (-1)*MSEG.ERFMG END),
YPLNT.VKGRP

FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a2
WHERE
MSEG.MANDT = MKPF.MANDT
AND MKPF.MANDT = MARA.MANDT
and mkpf.mandt =yplnt.mandt
and mkpf.mandt = t25a2.mandt
AND MKPF.MBLNR = MSEG.MBLNR
AND MARA.MATNR = MSEG.MATNR
AND YPLNT.PPLNT=MSEG.WERKS
AND MSEG.WERKS = T001W.WERKS
and t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3)
AND MARA.PRDHA <> ''
AND MKPF.MJAHR=YEAR(@.BUDAT1)
AND MKPF.VGART IN ('WR','WF')
AND MSEG.AUFNR IS NOT NULL
AND MSEG.BWART IN ('101','102')
GROUP BY t25a2.bezek,T001W.NAME1,YPLNT.VKGRPYou can use a table variable. Insert the first result set in. Then insert the second result set in. Union tries to eliminate duplications and that takes time. Union All is faster than Union but you may get duplicates.|||Yeah ..thanks for confirming my suspicions ...I was also thinking along the same lines coz there are no duplicates ...

But I am unable to understand why this is happening ... both the result set contain only about 50 rows each ...

20 minutes is too much ...|||SELECT
T001W.NAME1,
t25a5.bezek SKU,
QTY = SUM(CASE MSEG.BWART
WHEN '101' THEN MSEG.ERFMG
WHEN '102' THEN (-1)*MSEG.ERFMG END),
YPLNT.VKGRP
FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a5
WHERE
MSEG.MANDT = MKPF.MANDT
AND MKPF.MANDT = MARA.MANDT
and mkpf.mandt =yplnt.mandt
and mkpf.mandt = t25a5.mandt
AND MKPF.MBLNR = MSEG.MBLNR
AND MARA.MATNR = MSEG.MATNR
AND YPLNT.PPLNT= MSEG.WERKS
AND MSEG.WERKS = T001W.WERKS
AND MARA.PRDHA <> ''
AND MKPF.VGART IN ('WR','WF')
AND MKPF.MJAHR=YEAR(@.BUDAT1)
AND MSEG.AUFNR IS NOT NULL
AND MSEG.BWART IN ('101','102')

and (
t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3)
OR
t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3)
)

GROUP BY t25a5.bezek,T001W.NAME1,YPLNT.VKGRP|||Sorry ... wouldnt work hanafih ...|||Are you running this in Query Analyzer or a stored procedure? Either way, run DBCC DBCC FREEPROCCACHE and try it again. If a stored procedure, just do sp_recompile. It shouldn't take that long to run. Also, have you looked at the execution plan to see if there is anything weird going on there?|||Enigma dude! What kind of schema is this? I've heard of "star" and "snowflake", but I tried diagraming these relationships and all I got was "yarnball". I'm not surprised that SQL Server chokes on UNIONing those WHERE clauses.

Do you happen to have an ERD of these tables? Please post it.|||Hey blind dude ... dont blame me for this schema ...

Don't ya know ... The best-run businesses run ? ;)|||Sorry ... wouldnt work hanafih ...

Then there's something fundamentally wrong with your query or database schema.

The two queries differ by only

and t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3)

and t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3)

consider:

use pubs

select au_id
from authors
where state = 'CA'
union
select au_id
from authors
where state = 'UT'

select au_id
from authors
where state = 'UT' OR STATE='CA'

The UNION is not one whit different from the OR. If ORing the condition is returning a different result set from UNIONing the seperate queries, your data does not adhere to relational set theory.|||The two queries differ by only

and t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3)

and t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3)
how do you propose i include the two tables in the select and from clauses|||Enigma, I figured it was probably a schema you inherited. Just the same, your code would be clearer and (possibly) more efficient if you linked your tables with JOINs rather than in the WHERE clause. SQL Server will attempt to convert your WHERE clause syntax to a standard JOIN syntax prior to execution, but given the complexity of your links and the addition of UNION, the complexity may be too much for it to handle.|||Yeah Blind dude ...
Let me say it again ... I think you did not get the joke

The best-run businesses run A Crappy database schema (www.sap.com)

hmm .. let me try what you have said ... but i do not think it would help much :D|||ummm...as Frank Barone would say...

Holy Crap...

Did you run profiler to check it out?

Did you check sp_who and sp_lock?

Holy Crap...|||Yeah I did do those things Brett ... those were the first things that came to my mind when the query did not finish even after 10 minutes ... no deadlocks ... no live locks ...|||I'm assuming that it's not a coincidence...and the problem is repeatable...

What happens if you put each query in it own window and execute them at the same time...

Or schedule two jobs to launch at the exact same time...

I'd be interested if you get any contention from that...

What's the plan say?

How much data is the tables?|||In a second Query Analyzer window, run this:

select spid, blocked, cpu, physical_io, waittype, lastwaittype, waitresource
from master..sysprocesses
where blocked > 0
or spid = (spid that is running query)

On SQL 2000 SP2, I ran into a problem of never-ending queries. The query would run with parallelism, then each parallel part would wait for every other part to finish first. In SP3, they sort of fixed the problem. In SP3, the query gives partial results, along with an error.|||the estimated execution plan showed no problems ... lost the patience to wait it out for the actual execution plan ...

mcrowley ... will tryout that tomorrow ...

brett ... on the verge of passing out now ...er ... i mean going to sleep ...|||May you dream of nothing that is related to sql...unless she's cute ;)