Absurd project: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

Help in creating xsd file

Hello everybody,
I have following XML and i do want to create xsd file to BulkLoad data
into SQL SERVER table, so pls help me how can i create xsd file out of
following XML file...
<PTLV_PROJ>
<PROJECT>
<PID>10649280</PID>
<PDESC>FS2 - TAX MEETINGS GENERAL</PDESC>
<ACTIVITIES>
<ACT>
<AID>0000</AID>
<ADESC>General</ADESC>
</ACT>
<ACT>
<AID>10000</AID>
<ADESC>General test</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
<PROJECT>
<PID>10649489</PID>
<PDESC>AREA KNOWLEDGE NETWORK</PDESC>
<ACTIVITIES>
<ACT>
<AID>20000</AID>
<ADESC>General</ADESC>
</ACT>
<ACT>
<AID>3001</AID>
<ADESC>AWS CHAMPION</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
</PTLV_PROJ>Hi
You post may be better answered in microsoft.public.sqlserver.xml
Check out the following on how to use updategrams
http://msdn.microsoft.com/library/d...
egram_375f.asp
http://msdn.microsoft.com/library/d...asp?frame=true
http://msdn.microsoft.com/library/d...ations_0gqb.asp
John
"mvp" wrote:

> Hello everybody,
> I have following XML and i do want to create xsd file to BulkLoad data
> into SQL SERVER table, so pls help me how can i create xsd file out of
> following XML file...
>
> <PTLV_PROJ>
> <PROJECT>
> <PID>10649280</PID>
> <PDESC>FS2 - TAX MEETINGS GENERAL</PDESC>
> <ACTIVITIES>
> <ACT>
> <AID>0000</AID>
> <ADESC>General</ADESC>
> </ACT>
> <ACT>
> <AID>10000</AID>
> <ADESC>General test</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> <PROJECT>
> <PID>10649489</PID>
> <PDESC>AREA KNOWLEDGE NETWORK</PDESC>
> <ACTIVITIES>
> <ACT>
> <AID>20000</AID>
> <ADESC>General</ADESC>
> </ACT>
> <ACT>
> <AID>3001</AID>
> <ADESC>AWS CHAMPION</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> </PTLV_PROJ>

Help in "Limit"

we all know
mysql: select * from table limit ?1, ?2

equals

sqlserver: SELECT TOP ?2 *
FROM table WHERE (IDENTITYCOL NOT IN
(SELECT TOP ?1 IDENTITYCOL
FROM table order by IDENTITYCOL))
order by IDENTITYCOL

but the below SQL in mysql,how to convert?I enmesh.........
select pageid,pagename,pageaddr,pageauditflag,pageartaudi tflag,startplaytime
from pageinfo where entryid= ?1 and startplaytime= ?2
limit ?3, ?4

thanks!do it the same way -- pretend ?1 and ?2 don't exist, put ?3 where ?1 is and ?4 where ?2 is in the TOP query|||do it the same way -- pretend ?1 and ?2 don't exist, put ?3 where ?1 is and ?4 where ?2 is in the TOP query
you mean if the query is:
select * from AselectPageInfo_view where entryid=1 and startplaytime='2006-03-08' limit 0,1
I can convert it to below:
SELECT TOP 1 * FROM (select * from AselectPageInfo_view where
entryid=1 and startplaytime='2006-03-08' ) WHERE (pageid NOT IN (SELECT
TOP 0 pageid FROM (select * from AselectPageInfo_view where entryid='1'
and startplaytime='2006-03-08' ) order by pageid)) order by pageid

right?
but it can't run!
what's wrong?
Please help!thanks!|||oh,May be this is right!
SELECT TOP 1 * FROM AselectPageInfo_view WHERE
(pageid NOT IN (SELECT TOP 0 pageid FROM
AselectPageInfo_view where
entryid='1' and startplaytime='2006-03-08' order by pageid))
and (entryid='1' and startplaytime='2006-03-08') order by pageid

Expert,Please check it,is it right?
Thank you from bottom of my heart!|||SELECT TOP 0 is not going to return anything, is it?

:)|||no，it return result( one line )
expert, the query that i write in #4 is right?|||i mean
select * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08' limit 0,1
whether equals
SELECT TOP 1 * FROM AselectPageInfo_view WHERE
(pageid NOT IN (SELECT TOP 0 pageid FROM
AselectPageInfo_view where
entryid='1' and startplaytime='2006-03-08' order by pageid))
and (entryid='1' and startplaytime='2006-03-08') order by pageid|||please tell me how many rows you get for this --

SELECT TOP 0 pageid FROM
AselectPageInfo_view where
entryid='1' and startplaytime='2006-03-08' order by pageid|||oh,it's zero|||do you know why?|||i change the 0 to 1
that can get one row
the 0 is reason?|||hello,expert,why we need know the result? 1 and 0 we can change to another parameter,
i just want to know the two query whether equals?|||no, they are not the same|||oh,expert,can you tell me which query can equals
select * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08' limit 0,1
because the limit can't be used in mssql|||that query just gets 1 row -- and since there's no ORDER BY, it could be any row

so the equivalent in microsoft sql server isselect top 1 * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08'|||and please stop calling me "expert"

i have a name|||ok,I know,Rudy Limeback :)|||that query just gets 1 row -- and since there's no ORDER BY, it could be any row

so the equivalent in microsoft sql server isselect top 1 * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08'

well,if i change the reference variables, your query is not right
i mean if the query like this:

select * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08' limit 5,10
depend on your opinion,I should write the query like below,right?
select top 10 * from AselectPageInfo_view where entryid=1 and
startplaytime='2006-03-08'|||well,if i change the reference variables, your query is not right i did not say you could change my query and still get similar results

all you asked was the equivalence to LIMIT 1, and i gave you that equivalence

if you want the equivalent to LIMIT 5,10, you need to first ask yourself what is the ORDER BY that determines the sequence of rows?|||i did not say you could change my query and still get similar results

all you asked was the equivalence to LIMIT 1, and i gave you that equivalence

if you want the equivalent to LIMIT 5,10, you need to first ask yourself what is the ORDER BY that determines the sequence of rows?
of course,it ispageid|||please show your entire query, i do not see pageid anywhere in there|||ok,i want to change a SQL which in mysql to mssql

select pageinfo.pageid,pageinfo.pagename,pageinfo.pageadd r,
pageinfo.pageauditflag,pageinfo.pageartauditflag,p ageinfo.startplaytime
from pageinfo where pageinfo.entryid=?1 and pageinfo.startplaytime=?2
limit ?3,?4"

first, i creat a view

CREATE VIEW dbo.AselectPageInfo_view
AS
SELECT pageid, pagename, pageaddr, pageauditflag, pageartauditflag,
startplaytime, entryid
FROM appsdb.pageinfo

second, i need change the query

select * from AselectPageInfo_view where entryid=?1 and
startplaytime=?2 limit ?3,?4

but i don't know whether the query which i changed is right?|||thank you for your kindly help:)|||i am going to say this only one more time

where is your ORDER BY clause?

without an ORDER BY clause, nothing you do will turn out correctly|||Rydy, I applaud your patience!|||Rydy, I applaud your patience!
My bad! .. Rudy|||thanks for the kind words, GDMI

i do like to give people lots of opportunity to catch up, eh

;)|||oh，I know
thank you！|||<<< quoted text and followup removed >>>

and rudy, you are my favorite expert.|||thanks, sean, this thread has been quite a challenge for me, and i'm not completely happy with my performance (you can see in a couple of places where my exasperation shows through)

it's nice to get positive feedback :)|||Rudy - you are my favourite Database Guru of the Year (2005) too :)|||Rudy - you are my favourite Database Guru of the Year (2005) too :)

he's been my favorite since a long time... :D

Help Identifying Full table scans with objectname

Hello
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon

Help Identifying Full table scans with objectname

Help I have gone brain dead

I have create a table called production. Where production information is entered on each shift for each day (3 shifts). When I first created the table, there was no need to track equipment without production. Now it is!! What I need to do is be able to query the table by line, shift and date for a date range and determine which lines, shifts and date are missing production data. The machines are numbered from 2 to 12 and the column name is LineNum. I have columns named Production, Shift, and EntryDate. I know that I am probably going to feel stupid when I see the fix, but for the life of me, I just don't see how to do it without recordset and then processing it in vbscript.
Thanks in advance,
LeeLee, I'm not sure if I eally understand the problem, but it sounds like the following

SELECT LineNum, Shift, EntryDate
FROM production
WHERE Production IS NULL
AND EntryDate BETWEEN @.begindate AND @.enddate

@.begindate and @.enddate represent the date range you're searching for. This seems awfully simple, though, perhaps I'm misunderstanding the problem?|||If you can, post here the tables structure in order for us to have a good understanding of your situation.

ionut|||I keep playing until I found something that give me an "okay" way of getting the information. I created a table called NumLine with all possible lines in it then joined the two tables. This method returns me data (count) as long as the line has ran at least one shift. The problem is if the line does not run at least one shift, I will not know it. My manager said that he would deal with this short term.

SELECT DISTINCT Prod.ProDate, Prod.Line, Max(Prod.Shift) AS MaxOfShift, Count(Prod.Shift) AS CountOfShift, Prod.ProDate, Prod.Dept

FROM Prod RIGHT JOIN tblNumLine ON Prod.Line = tblNumLine.NumLine

GROUP BY Prod.ProDate, Prod.Line, Prod.ProDate, Prod.Dept, tblNumLine.NumLine

HAVING (((Prod.ProDate) Between 'MMColParamBeg' And 'MMColParamEnd') AND ((Count(Prod.Shift))<3) ))

tblNumLine
NumLine

tblProd
LineNum
Shift
Production
EntryDate

Thanks,
Lee|||Yes! When I thought over what I had accomplished by creating a new table and the structure of the query, end result NOTHING! I could have accomplished the same with a simple Select and Count(Shift) Where Count(Shift) < 3 Group by Shift. What I need is to be able to return missing production even if it is all 3 shifts.

Thanks to all for your patience with an knucklehead,
Lee

Help getting started with @@Identity

OK, so, from what people tell me I should be using @.@.Idnetity. What Im trying to do is insert data into a table, than revrieve the id from the new row in that table, than use that id in another sql insert statment later down the road. Currnetly the way im doing it is with a sql insert, than executte the scalar, than execute a reader, which is causeing me much grief.

This is my sql statment here:

Dim

sqlInsertAsNew SqlCommand("INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)", sqlConn)

From what I understand, using @.@.Identity, I can retrive data from my insert statment without using a sperate select statment. Can you guys point me in the right direction, ive looked alot in the forms and such, most I found is either assuming you know what todo, or is security related.

Well,

I just posted a very similar question to yours in the same forum. Wish I would have noticed your post first :->

Thanks

Josh

|||After reading a colleges code I figured out what to do, however I dont think I could explain it. If anyone wants to see my code, feel free to ask.|||

I do not thing you can retrive data from database without a select statement. to use @.@.identtiy, you have to have one column in the table are identity column, and you can not insert any data into this column. usually this column is used as a primary key of the table, so to get the primary key of the row which you just inserted you can do this

INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)
select @.@.iidentity

this statement will return the idtentiy number of the row you just inserted. but you have to make sure on table author there is no trigger to insert data into another table with a identity column, otherwise you will get the identity number of the other table

to avoid this, you probably should alway useselect scope_identity()as much as you can

Hope this help

|||

DavidDu:
I do not thing you can retrive data from database without a select statement. to use @.@.identtiy, you have to have one column in the table are identity column, and you can not insert any data into this column. usually this column is used as a primary key of the table, so to get the primary key of the row which you just inserted you can do this
INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)
select @.@.iidentity
this statement will return the idtentiy number of the row you just inserted. but you have to make sure on table author there is no trigger to insert data into another table with a identity column, otherwise you will get the identity number of the other table
to avoid this, you probably should alway useselect scope_identity()as much as you can
Hope this help

IDENTITY is not a column it is a property of a column.

Wednesday, March 28, 2012

help getting parameters back from a stored procedure

Hello everyone,

I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.

--I've been getting the following error in visual basic 6 when i try to call this:
run-time error '-2147217900 (80040e14)': syntax access violation

--This is the error you get when you try to just run the code in query analyzer:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

--what code i was trying to use (in query analyzer):
{call EXEC CreateTempTables (@.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0')}

--code that i was trying to use in vb 6:
Public Sub TemporaryTables( _
ByVal bytRQSodfilFlag As Byte, _
ByVal bytBulkRanFlag As Byte, _
ByVal bytBulkForFlag As Byte, _
ByVal bytBulkJitFlag As Byte, _
ByVal bytDeleteFlag As Byte, _
ByVal cnPlant As String)

Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRQSodfil As Parameter
Dim objBulkRan As Parameter
Dim objBulkFor As Parameter
Dim objBulkJit As Parameter
Dim objParamID As Parameter
Dim objRQSodfilFlag As Parameter
Dim objBulkRanFlag As Parameter
Dim objBulkForFlag As Parameter
Dim objBulkJitFlag As Parameter
Dim objDeleteFlag As Parameter
Dim objErrorNum As Parameter
Dim intErrorNum As Integer

' setup command variable
Set objCmd = New ADODB.Command
Set objConn = New ADODB.Connection
objConn.Open cnPlant
objCmd.CommandText = "EXEC CreateTempTables"
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = objConn

' setup parameters
Set objRQSodfil = objCmd.CreateParameter("@.RQSodfil", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objRQSodfil
Set objBulkRan = objCmd.CreateParameter("@.BulkRan", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkRan
Set objBulkFor = objCmd.CreateParameter("@.BulkFor", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkFor
Set objBulkJit = objCmd.CreateParameter("@.BulkJit", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkJit
Set objParamID = objCmd.CreateParameter("@.ID", adChar, adParamInputOutput, 2, 0)
objCmd.Parameters.Append objParamID
Set objRQSodfilFlag = objCmd.CreateParameter("@.RQSodfilFlag", adTinyInt, adParamInput, , bytRQSodfilFlag)
objCmd.Parameters.Append objRQSodfilFlag
Set objBulkRanFlag = objCmd.CreateParameter("@.BulkRanFlag", adTinyInt, adParamInput, , bytBulkRanFlag)
objCmd.Parameters.Append objBulkRanFlag
Set objBulkForFlag = objCmd.CreateParameter("@.BulkForFlag", adTinyInt, adParamInput, , bytBulkForFlag)
objCmd.Parameters.Append objBulkForFlag
Set objBulkJitFlag = objCmd.CreateParameter("@.BulkJitFlag", adTinyInt, adParamInput, , bytBulkJitFlag)
objCmd.Parameters.Append objBulkJitFlag
Set objDeleteFlag = objCmd.CreateParameter("@.DeleteFlag", adTinyInt, adParamInput, , bytDeleteFlag)
objCmd.Parameters.Append objDeleteFlag
Set objErrorNum = objCmd.CreateParameter("@.ErrorNum", adInteger, adParamInputOutput, , 0)
objCmd.Parameters.Append objErrorNum

' execute command
Set rsTableInfo = objCmd.Execute(, , adExecuteRecord)

' find returned parameters
gstrRQSodfilName = rsTableInfo.Fields("@.RQSodfil")
gstrBulkRanName = rsTableInfo.Fields("@.BulkRan")
gstrBulkForName = rsTableInfo.Fields("@.BulkFor")
gstrBulkJitName = rsTableInfo.Fields("@.BulkJit")
gstrID = rsTableInfo.Fields("@.ID")
intErrorNum = rsTableInfo.Fields("@.ErrorNum")

End Sub

any help would be appreciatedactually i just solved my own problem:

at the end of the stored procedure i selected the columns i wanted to return and that did it.

select @.RQSodfil,@.BulkRan,@.BulkFor,@.BulkJit,@.ID,@.ErrorNum

i also just did a regular sql statement where i called the EXEC command to run my stored procedure.

EXEC CreateTempTables @.RQSodfil = 'a', @.BulkRan = 'a', @.BulkFor = 'a', @.BulkJit = 'a', @.ID = '0', @.RQSodfilFlag = '1', @.BulkRanFlag = '0', @.BulkForFlag = '0', @.BulkJitFlag = '0', @.DeleteFlag = '0', @.ErrorNum = '0'

hopefully if someone else has the same problem i've had they can see what i did.

help for T-SQL code generator for DataMart

I replicated a table from DW to DM

but i filtered the table by month.

now i have several tables with the same schema on the datamart

the table has five keys. what i want to do is to write a

sqlwizard code that will automatically write an update statement from the replicated

table. What the wizard will do is read the fields of the DM table then identify the keys and

generate the source code for update.

lets name the proc sqlwiz

exec sqlwiz (DMtable1,dwtable1)

the sp should return the desired update statement like this

update dmtable1 set DM.nonkeyfield1= dw.nonkeyfield1,

DM.nonkeyfield2= dw.nonkeyfield2,
DM.nonkeyfield3= dw.nonkeyfield3
from dwtable1 dw where
dm.keyfield1=dw.keyfield1 and
dm.keyfield2=dw.keyfield2

pls use any of the northwind table with composite pk.

my DM is sql2k5.

the sp can also be used for generating update codes for vb.net

thanks,

joey

1. You will need to use Dynamic SQL
2. Make use of INFORMATION_SCHEMA.COLUMNS

Help for Trigger

Hello

I would like to get the content of a field based in the field Name.
Suppose a table with a field Named 'LastName' for wich there is a trigger
after update
I store the field name in a local variable

Set @.ColName = 'LastName'

How can I retrieve the value of the @.ColName from the inserted table using
the @.Colname variable ?

I tried this:

Set @.Cmd = 'DECLARE @.DataValue varchar(100) Set @.DataValue = (Select i.' +
@.ColName + ' from inserted i) print @.DataValue'
exec (@.Cmd)

print @.Cmd gives

DECLARE @.DataValue varchar(100) Set @.DataValue = (Select i.LastName from
inserted i) print @.DataValue

But I got the following error

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'inserted'.

Any idea why ?

Thanks for your help

ThierryOn Fri, 23 Apr 2004 20:57:10 +0200, Thierry Marneffe wrote:

>Hello
>I would like to get the content of a field based in the field Name.
>Suppose a table with a field Named 'LastName' for wich there is a trigger
>after update
>I store the field name in a local variable
>Set @.ColName = 'LastName'
>How can I retrieve the value of the @.ColName from the inserted table using
>the @.Colname variable ?
>I tried this:
>Set @.Cmd = 'DECLARE @.DataValue varchar(100) Set @.DataValue = (Select i.' +
>@.ColName + ' from inserted i) print @.DataValue'
>exec (@.Cmd)
>print @.Cmd gives
>DECLARE @.DataValue varchar(100) Set @.DataValue = (Select i.LastName from
>inserted i) print @.DataValue
>But I got the following error
>Server: Msg 208, Level 16, State 1, Line 1
>Invalid object name 'inserted'.
>Any idea why ?
>Thanks for your help
>Thierry

Hi Thierry,

The inserted and deleted pseudo-tables can only be used in the
trigger. Invoking dynamic SQL creates a new environment, so you can't
use the inserted and deleted tables there.

If you don't use dynamic SQL, all will be swell:

CREATE TRIGGER TestIt
ON MyTable AFTER UPDATE
AS
DECLARE @.DataValue varchar(100)
SET @.DataValue =
(SELECT LastName
FROM inserted)
PRINT @.DataValue
go

Of course, this trigger will still result in an error if you perform
an update that affects more than one row - always remember that a
trigger is fired exactly once for each update statement, regardless of
the number of rows that match the search criteria (can be anything
from 0 up to the complete table) and regardless of whether the data
was actually chaged or not (ie UPDATE MyTable SET MyColumn = MyColumn
will fire the trigger and will have the complete table in the inserted
ande deleted pseudo-tables).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Help for stored procedure and Null...

Hi,
I have write a stored procedure which makes update in a numeric (int) field.

Depending on data in other table, in some case the result of query get a
Null value instead a zero value...

How can I tell to Update query to NOT update field if the value is Null ?

I hope my word clear...

here the stored procedure:

UPDATE dbo.ANAUTENTI

SET dist1punti = dist1punti +

(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepunti

FROM TEMPIMPORTAZIONEDIST1 INNER JOIN

ANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =
ANAARTICOLI.codartdist1

WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))

WHERE (piva IN

(SELECT piva

FROM TEMPIMPORTAZIONEDIST1

GROUP BY piva))

Thanks in advance

Piero

Italypiero (g.pagnoni@.pesaroservice.com) writes:
> Depending on data in other table, in some case the result of query get a
> Null value instead a zero value...
> How can I tell to Update query to NOT update field if the value is Null ?

UPDATE tbl
SET col = col + coalesce((SELECT ...), 0)

The coalesce function takes list of arguments and returns the first non-NULL
value in the list, or NULL if all values are NULL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> ha scritto nel messaggio
news:Xns944153FCAB58Yazorman@.127.0.0.1...
> UPDATE tbl
> SET col = col + coalesce((SELECT ...), 0)
>
> The coalesce function takes list of arguments and returns the first
non-NULL
> value in the list, or NULL if all values are NULL.

It works fine !
Thank You very much !

Piero
Italysql

help for query

Hello,
I have a table "log" with the following fields:

-log_id(int)
-cust_id(string)
-type(int)
-service_id(int)

type may be 0,1 or 10

I need to fetch one row for each type for each service_id for a specific
cust_id.
That is, if I have 3 different service_id's and I assume that at least
one row for each type exists with the specific cust_id, I should get 3*3
= 9 rows: no more, no less.
I prefer not to use a stored procedure.
How may I do?
Is a self-join the right way?

Thank you
Regards

--
elimina carraro per rispondere via emailYou don't appear to have any criteria for
the one row you want if there are more than one.
If that's the case and log_id is unique, this should work

select max(log_id),cust_id,type,service_id
from log
group by cust_id,type,service_id|||markc600@.hotmail.com ha scritto:
> You don't appear to have any criteria for
> the one row you want if there are more than one.
> If that's the case and log_id is unique, this should work
> select max(log_id),cust_id,type,service_id
> from log
> group by cust_id,type,service_id

It work great, thanks!

I got the same result with the following, but was much more complicated:

select *
from log a, log b
where a.cust_id='myid' and a.type IN (0,10,1) and a.log_id=b.log_id
and b.log_id IN (select top 1 log_id from log c where
c.service_id=a.service_id and c.type=a.type)
order by a.service_id

--
elimina carraro per rispondere via email

Help for Newbie!

I have a table with 10 records: 10, 9, 8, 7 ... and so on. I want to SELECT the top 3 records starting with the second record on the list (which in this case is 9.) So the result should be 9, 8, and 7. What would be the correct SQL statement for this?

I tried "SELECT TOP 3 * FROM tableXXX WHERE (FieldID<>(SELECT TOP 1 * FROM tableXXX ORDER BY FieldID DESC) ORDER BY FieldID DESC" but sure as heck it didn't work. Can anyone help?drop the * on the 2nd select and only qry for the pkval :

SELECT Top 3 *
FROM Contacts
WHERE idCont <> (SELECT Top 1 idCont FROM Contacts)|||Thank you, Mike. It works beautifully! The complete statement is:

SELECT Top 3 *
FROM tbl_XXX
WHERE (idXXX <>(SELECT Top 1 idXXX FROM tbl_XXX ORDER BY idXXX DESC))
ORDER BY idXXX DESC

Again, thank you. You are great help.

Help for INTRO function

Hello.
I need to create a new table from a query result, then I've written this SQL
command:
"SELECT Name, Number FROM Friends INTRO Result"
BUT
SQL give me an error!!
Please Help Me
Bye
CrisI assume you mean INTO not INTRO
SELECT Name, Number INTO result FROM Friends
http://sqlservercode.blogspot.com/|||Cristian wrote:
> Hello.
> I need to create a new table from a query result, then I've written this S
QL
> command:
> "SELECT Name, Number FROM Friends INTRO Result"
> BUT
> SQL give me an error!!
> Please Help Me
> Bye
> Cris
It's INTO not INTRO.
However, the resulting table won't have any keys or constraints.
Although SELECT INTO is handy as a quick and dirty way to create
tables, it may be better to do something like:
CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
NULL, PRIMARY KEY ...)
INSERT INTO Result (name, number)
SELECT name, number
FROM Friends ;
David Portas
SQL Server MVP
--|||Also,
If you need an autonumber in the destination table and need the data ordered
you must create the table first.
Good code:-
Create table myTable ( id int identity(1,1) not null, name varchar(50) not
null )
INSERT INTO myTable ( name ) SELECT name FROM friends ORDER BY name
if you use "select identity(int,1,1) AS id , name into MyTable from ..."
the order is not guaranteed and so it is advised against.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134382059.521678.242420@.g44g2000cwa.googlegroups.com...
> Cristian wrote:
SQL
> It's INTO not INTRO.
> However, the resulting table won't have any keys or constraints.
> Although SELECT INTO is handy as a quick and dirty way to create
> tables, it may be better to do something like:
> CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
> NULL, PRIMARY KEY ...)
> INSERT INTO Result (name, number)
> SELECT name, number
> FROM Friends ;
> --
> David Portas
> SQL Server MVP
> --
>

Monday, March 26, 2012

Help for database schema

Hello All,

My project uses MS SQL server database and is not too big database (have aound 200 table).

Now I have to create Database schema for my database as my project needs to be integrated with some other product.

I don't know much about database schema and how to start with it.

Can someone please give me some inputs on following:

1) What exactly database schema should include?

2) How should I start creating the schema for my database?

3) Are there any tools for doing this?

Thanks in advance1) CREATE TABLE statements, plus INSERT statements
2) Enterprise Manager will script the CREATE TABLE statements (but not the INSERT statements, i'm afraid)
3) tons of 'em -- do a search for SQL Server admin tools|||if your database is already built and you are looking to reverse engineer it, check out the link in my sig.|||My project uses MS SQL server database and is not too big database (have aound 200 table).I've never seen a database with 200 tables that needed 200 tables. It is indicative of poor design.|||I've never seen a database with 200 tables that needed 200 tables. It is indicative of poor design.That's a bit absolute isn't it?

EDIT - misinterpreted indicative on my first pass.|||Well, he states my project, and a one mans project with 200 tables? Well that clearly indicates poor design.|||Over egging it a bit aren't you? Do you even know what he is modelling?

BTW - I doubt (but do not know) that this has been his project from inception. I suspect it is purchased\ inherited. But as I say I don't know so I won't go so far as to say it is "clearly indicated".|||here's a project i worked on (as the sole data modeller) for an insurance company

i'm happy to report that it came in at just under 200 tables

thank $deity, eh, because i know now that as soon as it goes over the 200 table limit, it's b0rked|||Yeah, I know about data modelling. And I do know that a project with some 200 tables is quite complex, and I do think that more than one person should be involved in the data modelling. In smaller projects, one man can do the whole job, but in projects of the size we are talking about here, I would definitely not be comfortable as the only modeller. With so high complexity i feel pretty safe when I state that the project should involve at least two persons working as a team. One single man increases the chance of some issues being overlooked.

Edit: As I understand the word indicates, it does not mean it has to be that way, just that it's very likely. If you would use a different word for this, please let me know, as English is not my native language :)|||That's a bit absolute isn't it?
I have absolutely never seen a 200 table database that was a good design. I'm merely stating a fact.|||Thanks for all your comments.

Ya i do agree that its a poor design.

Anyways I have created the schema for the DB.

Thanks.|||Ya i do agree that its a poor design.

but how and why do u all of a sudden agree that the design was poor?|||Well, he has the advantage of being able to look at it.sql

Help for bcp command for export data to txt file

I have follow the syntax to export table to .txt file like this.

bcp UL.dbo.CFP OUT C:\a.txt -T -c -S NOTEBOOK-F47731

But it always show error which is:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

I have try it many time. Can anyone help me to solve this problem?

Sorry if this is a simplistic answer, but have you tried putting single quotes around your query like so...

'bcp UL.dbo.CFP OUT C:\a.txt -T -c -S NOTEBOOK-F47731'

Here's how I'm using bcp at the moment.

USE MYDATABASE
GO

DECLARE @.LINE_ITEM_DATA SYSNAME
SET @.LINE_ITEM_DATA = 'bcp MYDATABASE.LINE_ITEM_DETAIL OUT c:\LineItemData.txt -T -c -t ","'
EXEC XP_CMDSHELL @.LINE_ITEM_DATA, NO_OUTPUT

Hope that helps!

help for a SQL Query

I would like to display a query result in this way
http://xavier.bouedo.free.fr/texte.gif

in the first table, it's the result that I would like to have and in the second is the result I have

If u want to see my draft
http://xavier.bouedo.free.fr/table.gif

thank for ur help
XavierXavier,

I don't know what language you use to display your data, but in C#.Net or VB.Net I would create a dataset with two tables; one with Name (the parent)(Select distinct; if needed) and the other table with the Message (the children). Then create a relationship between the two tables in your code and use nested repeaters to display the data.

I can't really think of another way.
Does anybody have more experience with SQL to have a more elegant solution?

Does this help Xavier?
KJ.|||thanks for ur solution, I use sql server 2000.|||OK. That was a very high level discussion. Let me know if you want a few code examples. I use nested repeaters, datalists or datagrids. It all depends on the circumstances.

You basically have a typical master-detail scenario. You have to manipulate the data on the .Net side to display it the way you want. SQL server can't really do it for you. (I think!)

I use C# and SQL Server 2000.

help for a search procedure

hello,
please help me to give me solution
i have a table
CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
CITY VARCHAR2(30), STREET VARCHAR2(40))
MY DATA IS
INSERT INTO T_STUDENT_MT VALUES (1,
'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
INSERT INTO T_STUDENT_MT VALUES (2,
'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
INSERT INTO T_STUDENT_MT VALUES (3,
'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO T_STUDENT_MT VALUES (4,
'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
PLEASE SUGGEST TO WRITE A PROCEDURE FOR
'CONDITION 1: If i pass iNAME to null it should NOT check in where
condition
'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
LIKE iNAME || '%'
CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
AS
BEGIN
--SINGE QUERY TO RETURN STUDENTT DATA
END;
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!kamal hussain wrote:
> hello,
> please help me to give me solution
> i have a table
> CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
> NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
> CITY VARCHAR2(30), STREET VARCHAR2(40))
>
> MY DATA IS
> INSERT INTO T_STUDENT_MT VALUES (1,
> 'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
> INSERT INTO T_STUDENT_MT VALUES (2,
> 'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
> INSERT INTO T_STUDENT_MT VALUES (3,
> 'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
> INSERT INTO T_STUDENT_MT VALUES (4,
> 'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
>
> PLEASE SUGGEST TO WRITE A PROCEDURE FOR
> 'CONDITION 1: If i pass iNAME to null it should NOT check in where
> condition
> 'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
> LIKE iNAME || '%'
>
> CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
> COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
> AS
> BEGIN
> --SINGE QUERY TO RETURN STUDENTT DATA
> END;
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I'm assuming condition 1 means if the variable @.Name is NULL, then you
want all the rows.
Try this:
CREATE PROCEDURE STUDENTSEARCH(
@.NAME VARCHAR(50) = NULL
)
AS
SELECT [ID]
,[NAME]
,FATHERNAME
,COUNTRY
,STATE
,CITY
,STREET
FROM T_STUDENT_MT
WHERE ((@.NAME IS NOT NULL AND [NAME] = @.NAME)
OR (@.NAME IS NULL))
OR (LEN(@.NAME)=1 AND [NAME] LIKE @.NAME + '%')
Note: In SQL Server there isn't a data type of VARCHAR2 and the
concatenation symbol is the plus sign (+).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQi0xPoechKqOuFEgEQJmOwCg1I3RunB5ASmn
7rtRnj7EZDpoKTkAoMrL
wTkd98HDDTfkao35SpHRNH7S
=WkyP
--END PGP SIGNATURE--

Help Finding Current Size of a Field

How do I find the current size of each field in a table's column?
I have a table with a field for notes/memos. I need to see which ones are about to reach the size limit and what the current size is.
Does this make sense?
Thank you,
KarenSELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable'|||thanks!

Karen

Help find better option:Views from table in various Db Viz local d

I am having performance issues Timeout, blocking , long running stored
procedures etc.
Application is written in C sharp and uses database sql2000 sp3.
I have about 25 databases, view is used that joins tables from all db with
left outer join and multiple conditions in where clause.
Online updates are taking place which again goes back to that particular
table in spec. db. Triggers are used at few places to update the view. Every
where stored procedures are used extensively instead of t-sql. In sp at
places table variables are used to minimise locks.
In the initial phase only with each database having few thousand records
user start getting timeout ,slow response & blocking complaints.
Should I copy the view to have local copy of data and have job that run
every min or 2 to update local table . In that too I am not clear that how
useful it will be to keep local copy bcz. it will grow huge in size with
growth in all db.
Very complex thing I see is continuous updates back and forth , which may
keep local copy block for most of the time or else user wan't have latest
data available in their app.
Any one who has worked with similar situation can be lot more useful,
I will highly appreciate any suggestions or discussions from sql experts.
Thanks,Views can be good or bad. If you follow the rules for creating partitioned
views then only the needed table(s) will be scanned. Else, every table in a
view will be scanned which would introduce more unnecessary locks.
http://msdn.microsoft.com/library/e...des_06_9mlv.asp
-oj
"Sameer Raval" <Sameer Raval @.discussions.microsoft.com> wrote in message
news:DB036029-CA4E-463D-B475-19BCE875ACA7@.microsoft.com...
>I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
> I have about 25 databases, view is used that joins tables from all db with
> left outer join and multiple conditions in where clause.
> Online updates are taking place which again goes back to that particular
> table in spec. db. Triggers are used at few places to update the view.
> Every
> where stored procedures are used extensively instead of t-sql. In sp at
> places table variables are used to minimise locks.
> In the initial phase only with each database having few thousand records
> user start getting timeout ,slow response & blocking complaints.
> Should I copy the view to have local copy of data and have job that run
> every min or 2 to update local table . In that too I am not clear that how
> useful it will be to keep local copy bcz. it will grow huge in size with
> growth in all db.
> Very complex thing I see is continuous updates back and forth , which may
> keep local copy block for most of the time or else user wan't have latest
> data available in their app.
> Any one who has worked with similar situation can be lot more useful,
> I will highly appreciate any suggestions or discussions from sql
> experts.
> Thanks,
>
>
>
>
>
>
>
>|||Sameer
> I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
Have you started to investigate the queries? Have you defined indexes on the
tables?
http://www.sql-server-performance.com
"Sameer Raval" <Sameer Raval @.discussions.microsoft.com> wrote in message
news:DB036029-CA4E-463D-B475-19BCE875ACA7@.microsoft.com...
> I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
> I have about 25 databases, view is used that joins tables from all db with
> left outer join and multiple conditions in where clause.
> Online updates are taking place which again goes back to that particular
> table in spec. db. Triggers are used at few places to update the view.
Every
> where stored procedures are used extensively instead of t-sql. In sp at
> places table variables are used to minimise locks.
> In the initial phase only with each database having few thousand records
> user start getting timeout ,slow response & blocking complaints.
> Should I copy the view to have local copy of data and have job that run
> every min or 2 to update local table . In that too I am not clear that how
> useful it will be to keep local copy bcz. it will grow huge in size with
> growth in all db.
> Very complex thing I see is continuous updates back and forth , which may
> keep local copy block for most of the time or else user wan't have latest
> data available in their app.
> Any one who has worked with similar situation can be lot more useful,
> I will highly appreciate any suggestions or discussions from sql
experts.
> Thanks,
>
>
>
>
>
>
>
>

Help extracting data from second row of result set

How do I select a field from the second row of the top two rows of data selected from a table.

(SELECT TOP(2) scp.DisplayName

FROM dbo.StudentContact sc

INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID

WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1

Order By scp.LastName,

scp.FirstName

I want to select the top two rows and select the name of the person in the second row to appear in my result.

Try:

select top 1 DisplayName

from

(

SELECT TOP(2) scp.DisplayName, scp.LastName, scp.FirstName

FROM dbo.StudentContact sc

INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID

WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1

Order By scp.LastName,

scp.FirstName

) as t

order by LastName DESC, FirstName DESC

If you are using SQL Server 2005, then use:

;with cte

(

SELECT scp.DisplayName, row_number() over(order by scp.LastName, scp.FirstName) as rn

FROM dbo.StudentContact sc

INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID

WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1

)

select DisplayName

from cte

where rn = 2;

AMB

|||

Thanks for your reply, I am using SQL Server 2005 so I opted for the second option. I have incorporated it into my query but I am getting a null value for the second guardian. I am including the entire query this time, sorry I didn't do this the first time. If you can give any help it would be greatly appreciated.

IF OBJECT_ID('dbo.p_Export') IS NOT NULL

DROP PROCEDURE [dbo].[p_Export]

<Purpose>This query produces a list student information with their lunch codes for import into Comalex Cafe.</Purpose>

Coded against version history file 7/16/07 12:25pm

EXEC p_Export @.XmlAcadSessionList = '<root><item ID="18" /><item ID="282" /><item ID="9" /><item ID="10" /><item ID="11" /><item ID="12" /></root>'

CREATE PROCEDURE p_Export

(

@.XmlAcadSessionList XML,

@.StudentFilterID INT = NULL

)

;with Guardian

(

SELECT sc.StudentID,scp.DisplayName, row_number() over(order by sc.StudentID,scp.LastName, scp.FirstName) as rn

FROM dbo.StudentContact sc

INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID

WHERE sc.IsCustodian = 1 AND sc.ReceivesMailings = 1

)

SELECT

Snum = Substring(stu.StudentNumber,2,14),

Lname = stu.LastName,

Fname = stu.FirstName,

DOB = REPLICATE('0', 2 - LEN(CONVERT(VARCHAR(2),DATEPART(mm,stu.DateOfBirth)))) + CONVERT(VARCHAR(2),DATEPART(mm,stu.DateOfBirth))

+ REPLICATE('0', 2 - LEN(CONVERT(VARCHAR(2),DATEPART(dd,stu.DateOfBirth)))) + CONVERT(VARCHAR(2),DATEPART(dd,stu.DateOfBirth))

+ (CONVERT(VARCHAR(4),DATEPART(yyyy,stu.DateOfBirth))),

[Status] = CASE WHEN

lp.StateCode = '1'

THEN 'D'

WHEN lp.StateCode = '2'

THEN 'R'

WHEN lp.StateCode = '3'

THEN 'F'

WHEN lp.StateCode NOT IN ('2', '3')

THEN 'D'

END,

Fill3 = space(20),

GradeLevel = sas.GradeLevelName,

Home = (SELECT stf.DisplayName FROM dbo.Staff stf

INNER JOIN dbo.Homeroom hm ON hm.HomeroomID = sas.HomeroomID

WHERE stf.StaffID = hm.TeacherID),

Race = (SELECT TOP(1) (CASE WHEN e.StateCode = 'H' THEN 'O' WHEN e.StateCode NOT IN ('W', 'B', 'A', 'I') THEN 'O' Else e.StateCode END)--need to add code to set code to O for H

FROM dbo.Person p

LEFT JOIN dbo.PersonEthnicity pe on pe.PersonID = p.PersonID

INNER JOIN dbo.Ethnicity e on e.EthnicityID = pe.EthnicityID

WHERE p.StudentNumber = stu.StudentNumber

ORDER BY pe.IsPrimary DESC

Asnum = stu.AltStudentNumber,

[Language] = CASE WHEN lan.[Name] Like 'Spanish'

THEN 'S'

ELSE 'E'

END,

Addr1 = RTRIM(adr.StreetNumber) + ' ' +RTRIM(adr.AddressLine1),

City = adr.City,

[State] = adr.State,

Zip = adr.PostalCode,

Phone = pt.AreaCode+pt.LocalNumber,

Fill4 = space(20),

[Gname1]= (SELECT TOP(1) scp.DisplayName

FROM dbo.StudentContact sc

INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID

WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1

Order By scp.LastName,

scp.FirstName

Gname2= (Select g.DisplayName from dbo.studentacadsession sas1 INNER JOIN Guardian g ON g.StudentID = sas1.StudentID where g.StudentID = sas.StudentID AND g.rn = 2),

Fill5 = space(20),

Fill6 = space(20),

Fill7 = space(20),

Fill8 = space(20),

Fill9 = space(20),

Fill10 = space(20),

Fill11 = space(20),

Fill12 = space(20),

Fill13 = space(20),

Fill14 = space(20),

Fill15 = space(20),

Fill16 = space(20),

Fill17 = space(20),

Fill18 = space(20),

Fill19 = space(20),

Fill20 = space(20),

Fill21 = space(20),

Fill22 = space(20),

Fill23 = space(20),

Fill24 = space(20),

Fill25 = space(20),

Fill26 = space(20),

Fill27 = space(20),

Fill28 = space(20),

Fill29 = space(20),

Active = CASE When sas.CurrentEnrollmentStatus = 'C'

THEN 'T'

ELSE 'F'

END

FROM

dbo.StudentAcadSession sas

INNER JOIN dbo.AcadSession asn ON asn.AcadSessionID = sas.AcadSessionID

INNER JOIN dbo.Student stu ON stu.StudentID = sas.StudentID

LEFT JOIN dbo.LunchProgram lp ON lp.LunchProgramID = sas.LunchProgramID

LEFT JOIN dbo.PersonLanguage pl ON pl.PersonID = sas.StudentID

LEFT JOIN dbo.Language lan ON lan.LanguageID = pl.LanguageID

LEFT JOIN dbo.Address adr on adr.AddressID = stu.MailingAddressID

LEFT JOIN dbo.PersonTelephone pt ON pt.PersonID = sas.StudentID

AND pt.IsPrimary = 1

WHERE

(EXISTS

(

SELECT

FROM

@.XmlAcadSessionList.nodes('/root/item') X(a)

WHERE

X.a.value('@.ID','INT') = asn.AcadSessionID

)

AND

asn.IsClosed = 0)

AND

( ( @.StudentFilterID IS NULL ) OR ( EXISTS( SELECT * FROM dbo.[FilterKeySet] fks WHERE fks.[FilterID] = @.StudentFilterID AND fks.[ItemID] = sas.[StudentID] ) ) )

ORDER BY stu.StudentNumber

|||

I would like to help, but how can I reproduce your problem?

Can you post the estructure of the tables involved and sample data?

AMB

|||

tbl 1

IF OBJECT_ID('dbo.Person') IS NOT NULL

DROP TABLE [dbo].[Person]

CREATE TABLE [dbo].[Person] (

[PersonID] INT IDENTITY (3,1) NOT NULL

CONSTRAINT [CPK_Person] PRIMARY KEY CLUSTERED ( [PersonID] ),

[PersonGUID] UNIQUEIDENTIFIER NOT NULL

CONSTRAINT [UNCC_Person_PersonGUID] UNIQUE NONCLUSTERED ( [PersonGUID] ),

[PersonNumber] VARCHAR(15) NOT NULL

CONSTRAINT [UNCC_Person_PersonNumber] UNIQUE NONCLUSTERED ( [PersonNumber] ),

[StudentNumber] VARCHAR(15) NOT NULL

CONSTRAINT [DF_Person_StudentNumber] DEFAULT(''),

[StaffNumber] VARCHAR(15) NOT NULL

CONSTRAINT [DF_Person_StaffNumber] DEFAULT(''),

[AltPersonNumber] VARCHAR(15) NULL,

[DisplayName] VARCHAR(255) NOT NULL,

[FirstName] VARCHAR(50) NOT NULL

CONSTRAINT [DF_Person_FirstName] DEFAULT(''),

[MiddleName] VARCHAR(50) NOT NULL

CONSTRAINT [DF_Person_MiddleName] DEFAULT(''),

[LastName] VARCHAR(75) NOT NULL,

[PreferredName] VARCHAR(75) NOT NULL

CONSTRAINT [DF_Person_PreferredName] DEFAULT(''),

[DateOfBirth] DATETIME NULL,

[SocialSecurityNumber] CHAR(9) NOT NULL

CONSTRAINT [DF_Person_SocialSecurityNumber] DEFAULT(''),

[ProperTitleID] SMALLINT NULL,

[GenerationID] SMALLINT NULL,

[GenderID] SMALLINT NULL,

[GenderDescriptor] VARCHAR(50) NULL,

[EducationLevelID] SMALLINT NULL,

[MaritalStatusID] SMALLINT NULL,

[ReligionID] SMALLINT NULL,

[ResidencyStatusID] SMALLINT NULL,

[PhotographID] INT NULL,

[MailingAddressID] INT NULL,

[PhysicalAddressID] INT NULL,

[UserID] INT NULL,

[CountryOfResidence] CHAR(3) NOT NULL

CONSTRAINT [DF_Person_CountryOfResidence] DEFAULT(''),

[EmployerName] VARCHAR(150) NOT NULL

CONSTRAINT [DF_Person_EmployerName] DEFAULT(''),

[IsHomeless] BIT NOT NULL

CONSTRAINT [DF_Person_IsHomeless] DEFAULT (0),

[RowVersion] UNIQUEIDENTIFIER NOT NULL

CONSTRAINT [DF_Person_RowVersion] DEFAULT (NEWID())

) ON [PRIMARY]

exec sp_addextendedproperty N'MS_Description', N'The person table contains common information about students, teachers and other staff members, parents, emergency contacts, and other persons in the system.',

N'user', N'dbo', N'table', N'Person'

exec sp_addextendedproperty N'MS_Description', N'The primary key of the table.',

N'user', N'dbo', N'table', N'Person', N'column', N'PersonID'

exec sp_addextendedproperty N'MS_Description', N'Identifier that may be used to identify a unique person across multiple databases or systems. Unlike the PersonID, the PersonGUID should be kept with the original record if the data is transferred to another database.',

N'user', N'dbo', N'table', N'Person', N'column', N'PersonGUID'

exec sp_addextendedproperty N'MS_Description', N'An easy to read identifier of the person.',

N'user', N'dbo', N'table', N'Person', N'column', N'PersonNumber'

exec sp_addextendedproperty N'MS_Description', N'The full name of the person as it should be displayed by the system.',

N'user', N'dbo', N'table', N'Person', N'column', N'DisplayName'

exec sp_addextendedproperty N'MS_Description', N'The person''s first name. Empty quotes if unknown.',

N'user', N'dbo', N'table', N'Person', N'column', N'FirstName'

exec sp_addextendedproperty N'MS_Description', N'The person''s middle name. Empty quotes if unknown.',

N'user', N'dbo', N'table', N'Person', N'column', N'MiddleName'

exec sp_addextendedproperty N'MS_Description', N'The person''s last name. Last name is required for all persons.',

N'user', N'dbo', N'table', N'Person', N'column', N'LastName'

exec sp_addextendedproperty N'MS_Description', N'The name that the person prefers to be called. The person''s full name should not be used. Empty quotes indicates that the person has no preferred name; the first name is assumed.',

N'user', N'dbo', N'table', N'Person', N'column', N'PreferredName'

exec sp_addextendedproperty N'MS_Description', N'The date on which the person was born, not including time. Null indicates an unknown date of birth.',

N'user', N'dbo', N'table', N'Person', N'column', N'DateOfBirth'

exec sp_addextendedproperty N'MS_Description', N'The person''s unformatted social security number. Null represents not known or not applicable.',

N'user', N'dbo', N'table', N'Person', N'column', N'SocialSecurityNumber'

exec sp_addextendedproperty N'MS_Description', N'The person''s title. Examples: {Mr, Ms, Dr}. Null represents not known or not applicable.',

N'user', N'dbo', N'table', N'Person', N'column', N'ProperTitleID'

exec sp_addextendedproperty N'MS_Description', N'The person''s generational suffix. Examples: {Jr, Sr, III}. Null represents not known or not applicable.',

N'user', N'dbo', N'table', N'Person', N'column', N'GenerationID'

exec sp_addextendedproperty N'MS_Description', N'The person''s gender. Examples: {male, female}. Null represents not known.',

N'user', N'dbo', N'table', N'Person', N'column', N'GenderID'

exec sp_addextendedproperty N'MS_Description', N'The person''s one character gender descriptor according to the GenderID; Cached for quick access. F indicates Female, M indicates Male, Null represents not known.',

N'user', N'dbo', N'table', N'Person', N'column', N'GenderDescriptor'

exec sp_addextendedproperty N'MS_Description', N'The highest level of education completed by the person. Null represents not known.',

N'user', N'dbo', N'table', N'Person', N'column', N'EducationLevelID'

exec sp_addextendedproperty N'MS_Description', N'The current marital status of the person. Null represents not known.',

N'user', N'dbo', N'table', N'Person', N'column', N'MaritalStatusID'

exec sp_addextendedproperty N'MS_Description', N'The person''s self-identified religion. Null represents not known.',

N'user', N'dbo', N'table', N'Person', N'column', N'ReligionID'

exec sp_addextendedproperty N'MS_Description', N'The residency status of the person from the perspective of the country of the owner of the data.',

N'user', N'dbo', N'table', N'Person', N'column', N'ResidencyStatusID'

exec sp_addextendedproperty N'MS_Description', N'An image of the person.',

N'user', N'dbo', N'table', N'Person', N'column', N'PhotographID'

exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s mailing address.',

N'user', N'dbo', N'table', N'Person', N'column', N'MailingAddressID'

exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s physical address.',

N'user', N'dbo', N'table', N'Person', N'column', N'PhysicalAddressID'

exec sp_addextendedproperty N'MS_Description', N'The name of the country of the person''s primary residence.',

N'user', N'dbo', N'table', N'Person', N'column', N'CountryOfResidence'

exec sp_addextendedproperty N'MS_Description', N'The name of the person''s employer. Null if the person''s employee is not known or not applicable.',

N'user', N'dbo', N'table', N'Person', N'column', N'EmployerName'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the person is currently homeless. 1 = homeless, 0 = not homeless.',

N'user', N'dbo', N'table', N'Person', N'column', N'IsHomeless'

exec sp_addextendedproperty N'MS_Description', N'A uniqueidentifier used to manage optimistic concurrency.',

N'user', N'dbo', N'table', N'Person', N'column', N'RowVersion'

tbl 1 sample data

PersonID PersonGUID PersonNumber StudentNumber StaffNumber AltPersonNumber DisplayName FirstName MiddleName LastName PreferredName DateOfBirth SocialSecurityNumber ProperTitleID GenerationID GenderID GenderDescriptor EducationLevelID MaritalStatusID ReligionID ResidencyStatusID PhotographID MailingAddressID PhysicalAddressID UserID CountryOfResidence EmployerName IsHomeless RowVersion

-- -- -- -- -- - -- -- - - -- - -- -- -

3 6A6A8238-D35D-483D-A2BB-1223E3C2830F P1001 S1001 NULL Allenton, Alice Alice Allenton Alice 1990-10-10 00:00:00.000 367296330 NULL NULL 2 F NULL NULL NULL NULL NULL 1 2 NULL 0 1D551B0E-F4C1-483C-B974-AFE7B2C8C2D5

4 A614FB9F-B4A6-487F-9A95-D7822CF90921 P1002 S1002 NULL Daniels, Darrell Darrell Daniels Darrell 1992-02-14 00:00:00.000 434257090 NULL NULL 1 M NULL NULL NULL NULL NULL 3 3 NULL 0 83B4EFAD-4E3E-4AA2-A77F-7E4FEE7433BD

5 FE955667-D634-47EC-B0C4-434CEE3C4B7B P1003 S1003 NULL Aventon, Arlene Arlene Aventon Arlene 1990-11-24 00:00:00.000 881807495 NULL NULL 2 F NULL NULL NULL NULL NULL 4 5 NULL 0 F34C80CD-1AE3-46FE-9CE4-F00142685E05

6 23D316DD-3100-4A56-9F36-9D8B34D21121 P1004 S1004 NULL Brown, Betty Betty Brown Betty 1990-10-10 00:00:00.000 460805371 NULL NULL 2 F NULL NULL NULL NULL NULL 6 7 NULL 0 5344672C-F50F-4574-8DD7-3A61B5A2F7B2

7 DF49BECF-76AA-40E4-AF30-210A9C03BFAC P1005 S1005 NULL Edwards, Ethan Ethan Edwards Ethan 1992-02-14 00:00:00.000 504931759 NULL NULL 1 M NULL NULL NULL NULL NULL 8 8 NULL 0 BACC7CE4-6C1C-4FC4-9591-CC66AA89EF3A

8 6F7B664C-AC44-4379-BE23-694B8A86A07E P1006 S1006 NULL Buck, Barbara Barbara Buck Barbara 1990-11-24 00:00:00.000 515164939 NULL NULL 2 F NULL NULL NULL NULL NULL 9 10 NULL 0 94951FD2-831A-44E5-8494-ACB0D6368EC3

9 577E4F3C-F674-47C0-9A21-1860952F0102 P1007 ST1001 NULL AStaff, Alpha Alpha AStaff Alpha NULL 111998888 NULL NULL 1 M NULL NULL NULL NULL NULL NULL NULL NULL 0 4402F5FC-23D8-4FDC-A32B-C694287E4171

Tbl 2

IF OBJECT_ID('dbo.StudentContact') IS NOT NULL

DROP TABLE [dbo].[StudentContact]

CREATE TABLE [dbo].[StudentContact]

(

[StudentID] INT NOT NULL,

[ContactID] INT NOT NULL,

[RelationshipID] SMALLINT NOT NULL,

[Description] VARCHAR(255) NOT NULL

CONSTRAINT [DF_StudentContact_Description] DEFAULT (''),

[ReceivesMailings] BIT NOT NULL

CONSTRAINT [DF_StudentContact_ReceivesMailings] DEFAULT (0),

[CanPickUp] BIT NOT NULL

CONSTRAINT [DF_StudentContact_CanPickUp] DEFAULT (0),

[IsRelated] BIT NOT NULL

CONSTRAINT [DF_StudentContact_IsRelated] DEFAULT(0),

[IsCustodian] BIT NOT NULL

CONSTRAINT [DF_StudentContact_IsCustodian] DEFAULT(0),

[IsEmergencyContact] BIT NOT NULL

CONSTRAINT [DF_StudentContact_IsEmergencyContact] DEFAULT(0),

CONSTRAINT [CPK_StudentContact] PRIMARY KEY CLUSTERED ( [StudentID], [ContactID] )

) ON [PRIMARY]

exec sp_addextendedproperty N'MS_Description', N'The StudentContact table contains student-person relationships in the system.',

N'user', N'dbo', N'table', N'StudentContact'

exec sp_addextendedproperty N'MS_Description', N'The student for whom the person is a contact.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'StudentID'

exec sp_addextendedproperty N'MS_Description', N'The person who is the contact.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'ContactID'

exec sp_addextendedproperty N'MS_Description', N'Defines the relationship between the student and the contact.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'RelationshipID'

exec sp_addextendedproperty N'MS_Description', N'Free text description of how the contact relates to the student.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'Description'

exec sp_addextendedproperty N'MS_Description', N'Indicates the contact receives mail-outs from the school for this student.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'ReceivesMailings'

exec sp_addextendedproperty N'MS_Description', N'Indicates the contact can pick up this student from school.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'CanPickUp'

exec sp_addextendedproperty N'MS_Description', N'Indicates the contact is a blood relation to the student.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'IsRelated'

exec sp_addextendedproperty N'MS_Description', N'Indicates the contact is the legal custodian of the student.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'IsCustodian'

exec sp_addextendedproperty N'MS_Description', N'Indicates the contact should be notified in an emergency.',

N'user', N'dbo', N'table', N'StudentContact', N'column', N'IsEmergencyContact'

Tbl 2 Sample Data

StudentID ContactID RelationshipID Description ReceivesMailings CanPickUp IsRelated IsCustodian IsEmergencyContact

-- -- -- - --

55 5 3 0 0 0 1 0

69 91 1 1 1 1 1

82 4 1 1 1 1 1 1

8842 0 0 0 1 0

91 3 1 1 1 1 1 1

tbl 3

IF OBJECT_ID('dbo.Student') IS NOT NULL

DROP TABLE [dbo].[Student]

CREATE TABLE [dbo].[Student] (

[StudentID] INT IDENTITY (3,1) NOT NULL

CONSTRAINT [CPK_Student] PRIMARY KEY CLUSTERED ( [StudentID] ),

[StudentGUID] UNIQUEIDENTIFIER NOT NULL

CONSTRAINT [UNCC_Student_StudentGUID] UNIQUE NONCLUSTERED ( [StudentGUID] ),

[StudentNumber] VARCHAR(15) NOT NULL

CONSTRAINT [UNCC_Student_StudentNumber] UNIQUE NONCLUSTERED ( [StudentNumber] ),

[DisplayName] VARCHAR(255) NOT NULL,

[FirstName] VARCHAR(50) NOT NULL

CONSTRAINT [DF_Student_FirstName] DEFAULT(''),

[MiddleName] VARCHAR(50) NOT NULL

CONSTRAINT [DF_Student_MiddleName] DEFAULT(''),

[LastName] VARCHAR(75) NOT NULL,

[PreferredName] VARCHAR(75) NOT NULL

CONSTRAINT [DF_Student_PreferredName] DEFAULT(''),

[DateOfBirth] DATETIME NULL,

[SocialSecurityNumber] CHAR(9) NOT NULL

CONSTRAINT [DF_Student_SocialSecurityNumber] DEFAULT(''),

[NextYearSchoolID] INT NULL,

[ProperTitleID] SMALLINT NULL,

[GenerationID] SMALLINT NULL,

[GenderID] SMALLINT NULL,

[GenderDescriptor] CHAR(1) NULL,

[MaritalStatusID] SMALLINT NULL,

[ReligionID] SMALLINT NULL,

[ResidencyStatusID] SMALLINT NULL,

[PhotographID] INT NULL,

[MailingAddressID] INT NULL,

[MailingAddressMultiLine] VARCHAR(230) NULL,

[MailingAddressSingleLine] VARCHAR(225) NULL,

[MailingAddressIsListed] BIT NULL,

[PhysicalAddressID] INT NULL,

[ResidesOutOfDistrict] BIT NOT NULL

CONSTRAINT [DF_Student_ResidesOutOfDistrict] DEFAULT( 0 ),

[LimitedEnglishID] SMALLINT NULL,

[MigrantFamilyID] SMALLINT NULL,

[BirthCertNumber] VARCHAR(30) NOT NULL

CONSTRAINT [DF_Student_BirthCertNumber] DEFAULT(''),

[BirthCertVerifyNumber] VARCHAR(30) NOT NULL

CONSTRAINT [DF_Student_BirthCertVerifyNumber] DEFAULT(''),

[Section504Qualification] CHAR(3) NOT NULL

CONSTRAINT [DF_Student_Section504Qualification] DEFAULT ('NA'),

[AltStudentNumber] VARCHAR(15) NOT NULL

CONSTRAINT [DF_Student_AltStudentNumber] DEFAULT(''),

[StateIDNumber] VARCHAR(30) NOT NULL

CONSTRAINT [DF_Student_StateIDNumber] DEFAULT(''),

[CountryOfResidence] CHAR(3) NOT NULL

CONSTRAINT [DF_Student_CountyOfResidence] DEFAULT(''),

[EmployerName] VARCHAR(150) NOT NULL

CONSTRAINT [DF_Student_EmployerName] DEFAULT(''),

[IsHomeless] BIT NOT NULL

CONSTRAINT [DF_Student_IsHomeless] DEFAULT (0),

[IsImmigrant] BIT NOT NULL

CONSTRAINT [DF_Student_IsImmigrant] DEFAULT (0),

[IsForeignExchange] BIT NOT NULL

CONSTRAINT [DF_Student_IsForeignExchange] DEFAULT (0),

[IsAllowedInetAccess] BIT NOT NULL

CONSTRAINT [DF_Student_IsAllowedInetAccess] DEFAULT (0),

[HasMedicalAlert] BIT NOT NULL

CONSTRAINT [DF_Student_HasMedicalAlert] DEFAULT (0),

[IsGraduate] BIT NOT NULL

CONSTRAINT [DF_Student_IsGraduate] DEFAULT (0),

[IsHomeSchooled] BIT NOT NULL

CONSTRAINT [DF_Student_IsHomeSchooled] DEFAULT (0),

[IsTuition] BIT NOT NULL

CONSTRAINT [DF_Student_IsTuition] DEFAULT (0),

[TelephonyPIN] VARCHAR(8) NOT NULL,

[InternetPassword] VARCHAR(20) NOT NULL,

--REVIEW IIB: Schema change document says this should be renamed to FormattedTelephoneNumber.

[TelephoneNumber] VARCHAR(21) NULL,

[IsListed] BIT NULL,

[EmailAddress] VARCHAR(128) NULL,

[OriginalEnrollmentDate] DATETIME NULL,

[ExpectedGraduationDate] DATETIME NULL,

[CachedSchoolList] TEXT NULL,

[SpecialInstructions] VARCHAR(4096) NOT NULL

CONSTRAINT [DF_Student_SpecialInstructions] DEFAULT(''),

[SpEdStatusID] SMALLINT NULL,

[PrimaryExcepID] SMALLINT NULL,

[SecondaryExcepID] SMALLINT NULL,

[LeastRestEnvID] SMALLINT NULL,

[CaseManagerID] INT NULL,

[EligibilityDate] DATETIME NULL,

[ReevaluationDate] DATETIME NULL,

[IEPBeginDate] DATETIME NULL,

[IEPEndDate] DATETIME NULL,

[DateEnrolledInLEA] DATETIME NULL,

[ReportingSchoolID] INT NULL,

[IntegratedWithSETS] BIT NOT NULL

CONSTRAINT [DF_Student_IntegratedWithSETS] DEFAULT(0),

[UserID] INT NOT NULL,

[RowVersion] UNIQUEIDENTIFIER NOT NULL

CONSTRAINT [DF_Student_RowVersion] DEFAULT (NEWID())

) ON [PRIMARY]

exec sp_addextendedproperty N'MS_Description', N'The Student table contains district-wide information about students in the system.',

N'user', N'dbo', N'table', N'Student'

exec sp_addextendedproperty N'MS_Description', N'The primary key of the table.',

N'user', N'dbo', N'table', N'Student', N'column', N'StudentID'

exec sp_addextendedproperty N'MS_Description', N'Identifier that may be used to identify a unique student across multiple databases or systems. Unlike the StudentID, the StudentGUID should be kept with the original record if the data is transferred to another database.',

N'user', N'dbo', N'table', N'Student', N'column', N'StudentGUID'

exec sp_addextendedproperty N'MS_Description', N'An easy to read identifier of the student.',

N'user', N'dbo', N'table', N'Student', N'column', N'StudentNumber'

exec sp_addextendedproperty N'MS_Description', N'The full name of the student as it should be displayed by the system.',

N'user', N'dbo', N'table', N'Student', N'column', N'DisplayName'

exec sp_addextendedproperty N'MS_Description', N'The student''s first name. Empty quotes if unknown.',

N'user', N'dbo', N'table', N'Student', N'column', N'FirstName'

exec sp_addextendedproperty N'MS_Description', N'The student''s middle name. Empty quotes if unknown.',

N'user', N'dbo', N'table', N'Student', N'column', N'MiddleName'

exec sp_addextendedproperty N'MS_Description', N'The student''s last name. Last name is required for all students.',

N'user', N'dbo', N'table', N'Student', N'column', N'LastName'

exec sp_addextendedproperty N'MS_Description', N'The name that the student prefers to be called. The student''s full name should not be used. Empty quotes indicates that the Student has no preferred name; the first name is assumed.',

N'user', N'dbo', N'table', N'Student', N'column', N'PreferredName'

exec sp_addextendedproperty N'MS_Description', N'The date on which the student was born, not including time. Null indicates an unknown date of birth.',

N'user', N'dbo', N'table', N'Student', N'column', N'DateOfBirth'

exec sp_addextendedproperty N'MS_Description', N'The student''s unformatted social security number. Null represents not known.',

N'user', N'dbo', N'table', N'Student', N'column', N'SocialSecurityNumber'

exec sp_addextendedproperty N'MS_Description', N'ID (FK) of the school this student will attend next year.',

N'user', N'dbo', N'table', N'Student', N'column', N'NextYearSchoolID'

exec sp_addextendedproperty N'MS_Description', N'The student''s title. Examples: {Mr, Ms, Dr}. Null represents not known or not applicable.',

N'user', N'dbo', N'table', N'Student', N'column', N'ProperTitleID'

exec sp_addextendedproperty N'MS_Description', N'The student''s generational suffix. Examples: {Jr, Sr, III}. Null represents not known or not applicable.',

N'user', N'dbo', N'table', N'Student', N'column', N'GenerationID'

exec sp_addextendedproperty N'MS_Description', N'The student''s gender. Examples: {male, female}. Null represents not known.',

N'user', N'dbo', N'table', N'Student', N'column', N'GenderID'

exec sp_addextendedproperty N'MS_Description', N'The student''s one character gender descriptor according to the GenderID; Cached for quick access. F indicates Female, M indicates Male, Null represents not known.',

N'user', N'dbo', N'table', N'Student', N'column', N'GenderDescriptor'

exec sp_addextendedproperty N'MS_Description', N'The current marital status of the student. Null represents not known.',

N'user', N'dbo', N'table', N'Student', N'column', N'MaritalStatusID'

exec sp_addextendedproperty N'MS_Description', N'The student''s self-identified religion. Null represents not known.',

N'user', N'dbo', N'table', N'Student', N'column', N'ReligionID'

exec sp_addextendedproperty N'MS_Description', N'The residency status of the student from the perspective of the country of the owner of the data.',

N'user', N'dbo', N'table', N'Student', N'column', N'ResidencyStatusID'

exec sp_addextendedproperty N'MS_Description', N'An image of the student.',

N'user', N'dbo', N'table', N'Student', N'column', N'PhotographID'

exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s mailing address.',

N'user', N'dbo', N'table', N'Student', N'column', N'MailingAddressID'

exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s physical address.',

N'user', N'dbo', N'table', N'Student', N'column', N'PhysicalAddressID'

exec sp_addextendedproperty N'MS_Description', N'The level of written and verbal proficiency that the student possesses in the English language. Null represents that the student''s primary language, or mother-tongue, is English. Any other value represents a limited English proficiency.',

N'user', N'dbo', N'table', N'Student', N'column', N'LimitedEnglishID'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether a student belongs to a migrant family. Null represents that the student''s family is not migrant. Any other value represents the cateogory of migrant family to which the student belongs.',

N'user', N'dbo', N'table', N'Student', N'column', N'MigrantFamilyID'

exec sp_addextendedproperty N'MS_Description', N'The birth certificate number from the student''s birth certificate.',

N'user', N'dbo', N'table', N'Student', N'column', N'BirthCertNumber'

exec sp_addextendedproperty N'MS_Description', N'A reference to the method used to verify the birth certificate. The data usually represents the name or identifier of the person who performed a visual verification of the physical document.',

N'user', N'dbo', N'table', N'Student', N'column', N'BirthCertVerifyNumber'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether a student has a metnal or physical disability that qualifies under Section 504 of the Rehabilitation Act of 1973. (Empty Quotes) Indicates no qualified disability. (504) Indicates a qualified disability. (IEP) Indicates a qualified disability for a student with an IEP.',

N'user', N'dbo', N'table', N'Student', N'column', N'Section504Qualification'

exec sp_addextendedproperty N'MS_Description', N'Used to identify a student to another agency or organization.',

N'user', N'dbo', N'table', N'Student', N'column', N'AltStudentNumber'

exec sp_addextendedproperty N'MS_Description', N'The number assigned by the State Dept of Education.',

N'user', N'dbo', N'table', N'Student', N'column', N'StateIDNumber'

exec sp_addextendedproperty N'MS_Description', N'The abbreviation of the country of the student''s primary residence.',

N'user', N'dbo', N'table', N'Student', N'column', N'CountryOfResidence'

exec sp_addextendedproperty N'MS_Description', N'The name of the student''s employer. Null if the student''s employer is not known or not applicable.',

N'user', N'dbo', N'table', N'Student', N'column', N'EmployerName'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is currently homeless. (1) homeless. (0) not homeless.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsHomeless'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is an immigrant. (1) immigrant. (0) not an immigrant.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsImmigrant'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is a foreign exchange student. (1) foreign exchange student. (0) not a foreign exchange student.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsForeignExchange'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is allowed internet access at the school. (1) The student is allowed internet access. (0) The student is not allowed internet access.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsAllowedInetAccess'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is a graduate. (1) The student is a graduate. (0) The student is not a graduate.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsGraduate'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is home schooled. (1) The student is home schooled. (0) The student is not home schooled.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsHomeSchooled'

exec sp_addextendedproperty N'MS_Description', N'Indicates whether the students parents pay tution. (1) The students parents pay tuition. (0) The students parents do not pay tuition.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsTuition'

exec sp_addextendedproperty N'MS_Description', N'The person''s primary telephone number. Cached for quick access. Additional telephone numbers are stored in the PersonTelephone table.',

N'user', N'dbo', N'table', N'Student', N'column', N'TelephoneNumber'

exec sp_addextendedproperty N'MS_Description', N'Boolean indicating if the telephone number is listed on external lists.',

N'user', N'dbo', N'table', N'Student', N'column', N'IsListed'

exec sp_addextendedproperty N'MS_Description', N'The person''s primary email address. Cached for quick access. Additional email addresses are stored in the PersonEmail table.',

N'user', N'dbo', N'table', N'Student', N'column', N'EmailAddress'

exec sp_addextendedproperty N'MS_Description', N'The date the student was originally enrolled in the district. NULL indicates that the student has never been enrolled in the district. This value is maintained by the system.',

N'user', N'dbo', N'table', N'Student', N'column', N'OriginalEnrollmentDate'

exec sp_addextendedproperty N'MS_Description', N'Date that the student is expected to graduate. NULL indicates that this information is unknown.',

N'user', N'dbo', N'table', N'Student', N'column', N'ExpectedGraduationDate'

exec sp_addextendedproperty N'MS_Description', N'An XML formatted cached list of the schools in which the student has been enrolled.',

N'user', N'dbo', N'table', N'Student', N'column', N'CachedSchoolList'

EXEC sp_addextendedproperty N'MS_Description', N'Indicator used to determine if a student is receiving Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'SpEdStatusID'

EXEC sp_addextendedproperty N'MS_Description', N'The primary reason why the student is reciving Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'PrimaryExcepID'

EXEC sp_addextendedproperty N'MS_Description', N'The secondary reason why the student is reciving Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'SecondaryExcepID'

EXEC sp_addextendedproperty N'MS_Description', N'Represents the student least restrictive environment for Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'LeastRestEnvID'

EXEC sp_addextendedproperty N'MS_Description', N'Reference to a staff member that is in chanrge of the students Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'CaseManagerID'

EXEC sp_addextendedproperty N'MS_Description', N'Date in which a student has become eligible for an Exceptionality.',

N'user', N'dbo', N'table', N'Student', N'column', N'EligibilityDate'

EXEC sp_addextendedproperty N'MS_Description', N'Date in which to reevaluate a student to see if they still need Special Education Services for there exceptionality.',

N'user', N'dbo', N'table', N'Student', N'column', N'ReevaluationDate'

EXEC sp_addextendedproperty N'MS_Description', N'Date that represents when a student has begon an Individulized Education Program.',

N'user', N'dbo', N'table', N'Student', N'column', N'IEPBeginDate'

EXEC sp_addextendedproperty N'MS_Description', N'Date that represents when a student has ended an Individulized Education Program.',

N'user', N'dbo', N'table', N'Student', N'column', N'IEPEndDate'

EXEC sp_addextendedproperty N'MS_Description', N'Date in which a student first started receiving Special Education Services.',

N'user', N'dbo', N'table', N'Student', N'column', N'DateEnrolledInLEA'

EXEC sp_addextendedproperty N'MS_Description', N'A refrence to the school that a student is considered to report from.',

N'user', N'dbo', N'table', N'Student', N'column', N'ReportingSchoolID'

EXEC sp_addextendedproperty N'MS_Description', N'Indicator to determine if a student has been Integrated with SETSWeb.',

N'user', N'dbo', N'table', N'Student', N'column', N'IntegratedWithSETS'

exec sp_addextendedproperty N'MS_Description', N'The user ID of the student, for use with the Home Module.',

N'user', N'dbo', N'table', N'Student', N'column', N'UserID'

exec sp_addextendedproperty N'MS_Description', N'A uniqueidentifier used to manage optimistic concurrency.',

N'user', N'dbo', N'table', N'Student', N'column', N'RowVersion'

Tbl 3 Sample Data

StudentID StudentGUID StudentNumber DisplayName FirstName MiddleName LastName PreferredName DateOfBirth SocialSecurityNumber NextYearSchoolID ProperTitleID GenerationID GenderID GenderDescriptor MaritalStatusID ReligionID ResidencyStatusID PhotographID MailingAddressID MailingAddressMultiLine MailingAddressSingleLine MailingAddressIsListed PhysicalAddressID ResidesOutOfDistrict LimitedEnglishID MigrantFamilyID BirthCertNumber BirthCertVerifyNumber Section504Qualification AltStudentNumber StateIDNumber CountryOfResidence EmployerName IsHomeless IsImmigrant IsForeignExchange IsAllowedInetAccess HasMedicalAlert IsGraduate IsHomeSchooled IsTuition TelephonyPIN InternetPassword TelephoneNumber IsListed EmailAddress OriginalEnrollmentDate ExpectedGraduationDate CachedSchoolList SpecialInstructions SpEdStatusID PrimaryExcepID SecondaryExcepID LeastRestEnvID CaseManagerID EligibilityDate ReevaluationDate IEPBeginDate IEPEndDate DateEnrolledInLEA ReportingSchoolID IntegratedWithSETS UserID RowVersion

-- -- -- -- -- - - -- - - -- - -- - -- -- - -- - - -- -- - - -- -- -- -- -- -- - - -- - -- - -- -- -- -- -- -- --

3 6A6A8238-D35D-483D-A2BB-1223E3C2830F S1001 Allenton, Alice Alice Allenton Alice 1990-10-10 00:00:00.000 367296330 NULL NULL NULL 2 F NULL NULL NULL NULL 1 PO Box 59

148 Starling Road

Vienna, MO 65582 PO Box 59148 Starling Road Vienna, MO 65582 0 2 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-1234 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 3 1D551B0E-F4C1-483C-B974-AFE7B2C8C2D5

4 A614FB9F-B4A6-487F-9A95-D7822CF90921 S1002 Daniels, Darrell Darrell Daniels Darrell 1992-02-14 00:00:00.000 434257090 NULL NULL NULL 1 M NULL NULL NULL NULL 3 81 Darby Hollow Street

Apt 29

Richland, MO 65556 81 Darby Hollow StreetApt 29 Richland, MO 65556 0 3 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4321 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 4 83B4EFAD-4E3E-4AA2-A77F-7E4FEE7433BD

5 FE955667-D634-47EC-B0C4-434CEE3C4B7B S1003 Aventon, Arlene Arlene Aventon Arlene 1990-11-24 00:00:00.000 881807495 NULL NULL NULL 2 F NULL NULL NULL NULL 4 PO Box 300

815 West Eubanks

Vienna, MO 65582 PO Box 300815 West Eubanks Vienna, MO 65582 0 5 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4333 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 5 F34C80CD-1AE3-46FE-9CE4-F00142685E05

6 23D316DD-3100-4A56-9F36-9D8B34D21121 S1004 Brown, Betty Betty Brown Betty 1990-10-10 00:00:00.000 460805371 NULL NULL NULL 2 F NULL NULL NULL NULL 6 PO Box 806

187 West Franklin

Vienna, MO 65582 PO Box 806187 West Franklin Vienna, MO 65582 0 7 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-1234 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 6 5344672C-F50F-4574-8DD7-3A61B5A2F7B2

7 DF49BECF-76AA-40E4-AF30-210A9C03BFAC S1005 Edwards, Ethan Ethan Edwards Ethan 1992-02-14 00:00:00.000 504931759 NULL NULL NULL 1 M NULL NULL NULL NULL 8 3331 East Broadway

Apt 93

Stoutland, MO 65560 3331 East BroadwayApt 93 Stoutland, MO 65560 0 8 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4321 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 7 BACC7CE4-6C1C-4FC4-9591-CC66AA89EF3A

8 6F7B664C-AC44-4379-BE23-694B8A86A07E S1006 Buck, Barbara Barbara Buck Barbara 1990-11-24 00:00:00.000 515164939 NULL NULL NULL 2 F NULL NULL NULL NULL 9 PO Box 9196

3331 Hwy JJ

Stoutland, MO 65560 PO Box 91963331 Hwy JJ Stoutland, MO 65560 0 10 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4333 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 8 94951FD2-831A-44E5-8494-ACB0D6368EC3

|||

We appreciate your effort in posting the code and sample data.

However, in order to help us help you, we really need the sample data in a form that does not require that we spend much time creating and populating the tables.

Please view this link which should help you understand how to prepare the data so we can easily use it.

(Keep in mind, the less time we have to spend in 'setting up' the problem, the more likely you will get folks here to volunteer their time to help you..)

|||

Thanks for the link, this makes it much easier for me also......

INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(5,'45847C59-0EEC-4F0A-B3A7-74B8B8F877BE','S1001','Burr, Aaron','Aaron','','Burr','','Feb 6 1989 12:00:00:000AM','902061756',5,NULL,NULL,2,'M',NULL,NULL,NULL,4,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','',0,0,0,0,1,0,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','Not allowed to associate with Alexander Hamilton.',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,3,'2D1228D2-650D-4831-8269-7D5B42F66995')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(6,'A9B6D8C2-8D95-42E2-92E6-02F496A764BB','S1002','Franklin, Benjamin','Benjamin','','Franklin','Ben','Jan 17 1966 12:00:00:000AM','901171706',5,NULL,NULL,2,'M',NULL,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','The Philadelphia Inquirer',0,0,0,0,0,0,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,4,'5B4F017C-1A9C-4ABF-9F93-C57AD53DED5F')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(7,'50DD3F56-2882-4223-B993-53FB765E1B2F','S1004','Gerry, Elbridge','Elbridge','','Gerry','','Jul 17 1989 12:00:00:000AM','907171744',5,NULL,NULL,2,'M',NULL,NULL,NULL,6,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','',0,0,0,0,0,0,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,6,'C87C1F1B-B27F-444F-B7B3-0E565652A0BA')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(8,'7C02B0FC-D46E-43BF-A19A-6BD30301BC37','S1003','Hamilton, Alexander','Alexander','','Hamilton','','Jan 11 1990 12:00:00:000AM','901111755',5,NULL,NULL,2,'M',NULL,NULL,NULL,7,1,'287 Convent Ave New York, NY 10031','287 Convent Ave New York, NY 10031',0,1,0,NULL,NULL,'','','NA','','','','US Treasury Dept',0,0,0,0,0,0,0,0,'','','(212) 283-5154 x34555',0,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,5,'29707034-4806-4F6E-A00C-B3E174FCE297')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(12,'6194B294-F02E-41CB-8494-57B1DD290E45','S1005','Washington, Martha','Martha','','Washington','','May 15 1991 12:00:00:000AM','902104557',NULL,NULL,NULL,1,'F',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','',0,0,0,0,0,1,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,7,'48601CC4-5162-42DE-8B79-45CB9FCCDFDA')

INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(5,8,69,'Dueling Partner',0,0,0,0,0)
INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(5,10,69,'Guardian',0,0,0,1,0)
INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(7,6,69,'',0,0,0,0,0)
INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(7,10,69,'',0,0,0,0,0)

INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(3,'E742AD65-ADA7-4D50-87B4-18BEDF2946C0','P1001','','ST1002',NULL,'Adams, John','John','','Adams','','Oct 30 1989 12:00:00:000AM','911311735',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,'','',0,'71F9A6CC-9002-4E0B-8E0B-C6B46EE3D6E6')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(4,'5A3AE32D-7511-4272-BDF8-AFE9F6C4A0AA','P1002','','ST1005',NULL,'Adams, John Quincy','John','Quincy','Adams','','Jul 11 1989 12:00:00:000AM','907111767',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,3,NULL,NULL,NULL,'','',0,'0BB294F5-0843-40F3-9BB7-8429C4E04AED')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(5,'45847C59-0EEC-4F0A-B3A7-74B8B8F877BE','P1003','S1001','',NULL,'Burr, Aaron','Aaron','','Burr','','Feb 6 1989 12:00:00:000AM','902061756',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,4,NULL,NULL,NULL,'','',0,'2D1228D2-650D-4831-8269-7D5B42F66995')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(6,'A9B6D8C2-8D95-42E2-92E6-02F496A764BB','P1004','S1002','',NULL,'Franklin, Benjamin','Benjamin','','Franklin','Ben','Jan 17 1966 12:00:00:000AM','901171706',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,5,NULL,NULL,NULL,'','The Philadelphia Inquirer',0,'5B4F017C-1A9C-4ABF-9F93-C57AD53DED5F')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(7,'50DD3F56-2882-4223-B993-53FB765E1B2F','P1005','S1004','',NULL,'Gerry, Elbridge','Elbridge','','Gerry','','Jul 17 1989 12:00:00:000AM','907171744',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,6,NULL,NULL,NULL,'','',0,'C87C1F1B-B27F-444F-B7B3-0E565652A0BA')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(8,'7C02B0FC-D46E-43BF-A19A-6BD30301BC37','P1006','S1003','',NULL,'Hamilton, Alexander','Alexander','','Hamilton','','Jan 11 1990 12:00:00:000AM','901111755',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,7,1,1,NULL,'','US Treasury Dept',0,'29707034-4806-4F6E-A00C-B3E174FCE297')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(9,'AF52AF69-8645-416F-AD46-5288F3977202','P1007','','ST1003',NULL,'Jefferson, Thomas','Thomas','','Jefferson','','Apr 13 1990 12:00:00:000AM','104131743',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,8,2,3,NULL,'','',0,'AAAC30E6-551A-4349-B3B0-B16EFABCAAB7')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(10,'397F411E-F501-4454-894C-EA751F9DE7E4','P1008','','ST1004',NULL,'Madison, James Jr','James','','Madison','','Mar 16 1990 12:00:00:000AM','903161751',NULL,1,2,'M',NULL,NULL,NULL,NULL,9,4,NULL,8,'','',0,'42DFC849-1989-41EB-8AE2-E2A108F6053F')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(11,'3B8D0472-B748-4F4F-BA67-F0E9A023BC28','P1009','','ST1001',NULL,'Washington, George','George','','Washington','','Feb 22 1990 12:00:00:000AM','902221732',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,10,NULL,NULL,NULL,'','',0,'E63B1391-E7EC-4921-BABB-8825C82A7A70')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(12,'6194B294-F02E-41CB-8494-57B1DD290E45','P1010','S1005','',NULL,'Washington, Martha','Martha','','Washington','','May 15 1991 12:00:00:000AM','902104557',NULL,NULL,1,'F',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','',0,'48601CC4-5162-42DE-8B79-45CB9FCCDFDA')

|||Actually, you should use the TOP query since that will give better performance than the ROW_NUMBER one. And you don't seem to be filtering on the row number value in your query also. So it should produce more rows than the TOP 2.

Friday, March 30, 2012

Help in creating xsd file

Help in "Limit"

Help Identifying Full table scans with objectname

Help Identifying Full table scans with objectname

Help I have gone brain dead

Help getting started with @@Identity

Wednesday, March 28, 2012

help getting parameters back from a stored procedure

help for T-SQL code generator for DataMart

Help for Trigger

Help for stored procedure and Null...

help for query

Help for Newbie!

Help for INTRO function

Monday, March 26, 2012

Help for database schema

Help for bcp command for export data to txt file

help for a SQL Query

help for a search procedure

Help Finding Current Size of a Field

Help find better option:Views from table in various Db Viz local d

Help extracting data from second row of result set

Absurd project

Blog Archive

About Me