HELP HELP varchar

I have a field type of "varchar"

It can not accept to Store Arabic Characters!!! plus i can't change it into ntext or nvarchar my system which goin to export the data into SQL server by suing insert command will hang. so im stucked with varchar!!!

here some sample of my data stored currently in My SQL SERVER 2000

" /   "

Plzzzz help me if there any other way to do it

Do you have the right collation set in your database?

Have a look in BOL (Books on Line) at COLLATE - there's a lot of info in there about how to deal with charachters from different languages and alphabets.

/Kenneth

|||If you want to store arabic characters you will have to change your column data types to nvarchar. So you will have to investigate the problem you are describing while using the nvarchar column. What is the actual problem here ? I am not sure / doubt that this is related to collation problems, but further investigation might help us to solve the problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

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 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 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

go

If you are using SQL Server 2005, then use:

;with cte

as

(

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;

go

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]

GO

/*

<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

)

AS

;with Guardian

as

(

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

GO

|||

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]

GO

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]

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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

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

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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]

GO

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]

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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]

GO

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]

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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

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

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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.

Help adding fields to a table

I have a list of values in one field in a table, and I want to modify another
table that I have to add a new field for each value in the other field. In
case this doesn't make sense, I've posted examples below -
Table 1
Field 1
values - Apple, Pear, Banana, Orange
Table 2
I want to create four fields, one for each of the values in field 1 of table 1
I thought about doing this as a loop, but the only way I see to create a
field is using alter table, and that only allows me to enter a text name for
the field, instead of calling another field or value from another field.
Any ideas on how to do this? If it matters, I'm using SQL Query Analyzer
v.8.0.
Thanks,
Dan
You can use something like this ..
DECLARE @.SQL varchar(8000)
SET @.SQL = 'SELECT * FROM SYSDATABASES'
EXEC (@.SQL)
Create a dynamic query and execute it using EXEC.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dan" wrote:

> I have a list of values in one field in a table, and I want to modify another
> table that I have to add a new field for each value in the other field. In
> case this doesn't make sense, I've posted examples below -
> Table 1
> Field 1
> values - Apple, Pear, Banana, Orange
> Table 2
> I want to create four fields, one for each of the values in field 1 of table 1
>
> I thought about doing this as a loop, but the only way I see to create a
> field is using alter table, and that only allows me to enter a text name for
> the field, instead of calling another field or value from another field.
> Any ideas on how to do this? If it matters, I'm using SQL Query Analyzer
> v.8.0.
>
> Thanks,
> Dan
>

Help - Updating A Field In Query Analyzer

Hi All,

I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code:

USE DBMyDatabase

UPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
WHERE IDField=TblOther.IDOld

SELECT Pk, IDField
FROM TblToBeUpdated

What am I doing wrong? The error code I get is:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.

Thanks.

HenryUPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
FROM TblToBeUpdated
inner join TblOther on TblToBeUpdated.IDField=TblOther.IDOld|||I very rarely use this syntax, but as I remember it:UPDATE tblToBeUpdated
SET IDField = b.IDNew
FROM tblToBeUpdated
JOIN tblOther AS b
ON (b.IDOld = tblToBeUpdated.IDField) should do the trick!

-PatP|||Thanks. Works beautifully.

Henry

Help - Update Field in SQL Server

I am trying to write a password change page for my website. I want logged in users to be able to change their password which is stored in a SQL table. I was able to get the following code to work

Dim myCommand As New SqlCommand( _
"cmdChangePassword @.Email='"+request("email")+"', @.OldPassword='"+TextBox1.text+"',@.NewPassword='"+TextBox3.text+"'", myConnection)

This information is passed to a stored procedure which updates the table. However, I want to have a return value that shows that the password was changed. I changed the code as follows:

Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50).Value = request("email")

Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = "+TextBox1.text+"

Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = "+TextBox3.text+"

With this new code the password is not being changed. However, I am not receiving any errors.

BDyou can check your db if the pwd is being changed, to see if the sp is working properly.

(1) in your SP, you can return an integer 0/1 for success or failure and appropriately throw a msgbox saying pswd has been changed.
and modify your code slightly and have an output parameter.

or
(2) query the db again with username=@.username and pwd=@.newpswd and see if you get any records. to make sure you get the xact record you can add more conditions. so if you do get a record, then the db has been updated with the new pswd.

apparently, this one requires an xtra trip to the db.

HTH|||The SP is set to return the integer. I haven't added that code yet. I have tested the SP and know that it works. However, the password is not being changed. The email address, old password and new password need to be passed to the SP. As long as the email address and old password match, the password will be changed. I am assuming there is something wrong with my code and that those items are not being passed successfully.|||heres an example of the code that i am using

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
Dim myDataReader As SqlDataReader
myConnection = New SqlConnection("server=local;database=Northwind;Integrated Security=SSPI ")
myCommand = New SqlCommand()
myCommand.Connection = myConnection
myCommand.CommandText = "finalize_movein"
myCommand.CommandType = CommandType.StoredProcedure
'input parameter
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.userid"
myParameter.Direction = ParameterDirection.Input
myParameter.SqlDbType = SqlDbType.int
myParameter.Value = id
myCommand.Parameters.Add(myParameter)
'output parameter:
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.finalcnum"
myParameter.Direction = ParameterDirection.Output
myParameter.SqlDbType = SqlDbType.int
myCommand.Parameters.Add(myParameter)
' Open the connection to the SQL Server
myConnection.Open()
myCommand.ExecuteNonQuery()
return convert.toint32((myCommand.Parameters("@.finalcnum").Value))
myconnection.close

and have your SP defined like this :

CREATE PROCEDURE finalize_movein (@.userid int ,@.finalcnum bigint OUTPUT) as
begin...

HTH|||BD - please wrap your code in CODE tags to make it distinguishable from your comments.

It looks to me that your problem is in the way you are setting your parameter values. For some reason you have surrounded them with double quotes(") and plus signs(+). Remove those and you should have better luck. I changed the way you were setting up your @.email parameter to make it more consistent with the rest of your code, plus I added a ReturnValue parameter:

Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim myParm0 As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Integer)
myParm0.Direction = ParameterDirection.ReturnValue
Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = request("email")
Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = TextBox1.text
Dim myParm3 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm3.Direction = ParameterDirection.Input
myParm3.Value = TextBox3.text

|||That worked! Thanks for your help!

Brian|||Sorry, I have one more question. I am now able to change the password. However, I want to display a message to the user that the password has been changed. I have the following variable defined.

Code

Dim Result As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int)

Result.Direction = ParameterDirection.ReturnValue

myCommand.Connection.Open()
myCommand.ExecuteNonQuery()

if Result.Value>0 then MSG.text= "Your password has been changed"

myCommand.Connection.Close()

End Code

In my html I have the following:

<asp:Label id="MSG" runat="server"></asp:Label
Any ideas what I am missing?

Brian|||Normally, the ReturnValue will be 0 if there are no errors. I don't know what your stored procedure looks like, but assuming it follows normal practice, this line:

if Result.Value>0 then MSG.text= "Your password has been changed"

should be this:

if myCommand.Parameters("@.ReturnValue").Value=0 then MSG.text= "Your password has been changed"

Terri|||That's perfect. Thanks again for everybody's help.

Brian

Help - how to select from a comma delimited field ?

Dear SQL,

Lets say I have a field: User_Names

and it can have more than one name
example: "Yovav,John,Shon"

How can I select all the records that has "Yovav" in them ?

I try some like this:
SELECT * FROM User_Table WHERE User_Names IN ('Yovav')

but it only works if the field User_Names only has "Yovav" in it with no extras...

is there some SQL function to tell if string is found in a field ?

Any hope 4 me ?Try

SELECT * FROM User_Table WHERE User_Names LIKE '%Yovav%'|||Thank Q 4 lightning up my night in such times of overSQLing...

finally I can have some sleep :-)

and Thank GOD 4 this forum !!!

hellp with the sql is needed

hello i am a beginner:

on the .mdf sql.s 2005Ex

i need to present:

the 12 month as field name

whan the first filed is the customer name

and the rest is the sum(sale)

so the result will be like this

name 1 2 3 4 5 6 7 8 9 10 11 12

name1 $1 $2 0$ 8$ $1 $2 0$ 8$ $1 $2 0$ 8$

name2 $1 $2 0$ 8$ $1 $23 0$ 8$ $1 $23 0$ 8$

name3 $1 $2 0$ 8$ $1 $2 0$ 8$ $13 $2 0$ 8$

name4 $1 $2 0$ 83$ $1 $2 0$ 8$ $1 $2 0$ 8$

well so far i reached this - wich do the sum but on 12 row per name: well .. so if you could help me ?

SELECT
CASE WHEN MONTHs = 1 THEN MONEY ELSE 0 END AS '1',
CASE WHEN MONTHs = 2 THEN MONEY ELSE 0 END AS '2',
CASE WHEN MONTHs = 3 THEN MONEY ELSE 0 END AS '3',
CASE WHEN MONTHs = 4 THEN MONEY ELSE 0 END AS '4',
CASE WHEN MONTHs = 5 THEN MONEY ELSE 0 END AS '5',
CASE WHEN MONTHs = 6 THEN MONEY ELSE 0 END AS '6',
CASE WHEN MONTHs = 7 THEN MONEY ELSE 0 END AS '7',
CASE WHEN MONTHs = 8 THEN MONEY ELSE 0 END AS '8',
CASE WHEN MONTHs = 9 THEN MONEY ELSE 0 END AS '9',
CASE WHEN MONTHs = 10 THEN MONEY ELSE 0 END AS '10'

FROM

(SELECT DISTINCT MONTH(tblActoin.MyDate) AS MONTHs, SUM(tblActoin.amount) AS MONEY, tblIdentity.name
FROM tblActoin INNER JOIN
tblIdentity ON tblActoin.IdentityID = tblIdentity.id
GROUP BY tblActoin.MyDate, tblIdentity.name) AS derivedtbl_1

thanks

try this

SELECT Names,
sum(CASE WHEN MONTHs = 1 THEN MONEY ELSE 0 END) AS '1',
sum(CASE WHEN MONTHs = 2 THEN MONEY ELSE 0 END) AS '2',
sum(CASE WHEN MONTHs = 3 THEN MONEY ELSE 0 END) AS '3',
sum(CASE WHEN MONTHs = 4 THEN MONEY ELSE 0 END) AS '4',
sum(CASE WHEN MONTHs = 5 THEN MONEY ELSE 0 END) AS '5',
sum(CASE WHEN MONTHs = 6 THEN MONEY ELSE 0 END) AS '6',
sum(CASE WHEN MONTHs = 7 THEN MONEY ELSE 0 END) AS '7',
sum(CASE WHEN MONTHs = 8 THEN MONEY ELSE 0 END) AS '8',
sum(CASE WHEN MONTHs = 9 THEN MONEY ELSE 0 END) AS '9',
sum(CASE WHEN MONTHs = 10 THEN MONEY ELSE 0 END) AS '10,
sum(CASE WHEN MONTHs = 11 THEN MONEY ELSE 0 END) AS '11',
sum(CASE WHEN MONTHs = 12 THEN MONEY ELSE 0 END) AS '12'

FROM

(SELECT DISTINCT MONTH(tblActoin.MyDate) AS MONTHs, SUM(tblActoin.amount) AS MONEY, tblIdentity.name as Names
FROM tblActoin INNER JOIN
tblIdentity ON tblActoin.IdentityID = tblIdentity.id
GROUP BY tblActoin.MyDate, tblIdentity.name) AS derivedtbl_1)

group by Names

|||Yes!!! Thank you!!!