gistfile1.sql

# i dont remember what this is for

----

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

----


DECLARE @DATABASE varchar(1000)
DECLARE @PATH varchar(1000)

------------------------------
set @DATABASE = 'ftmac' --name of your database
set @PATH = 'D:\user\'  --path to save the files in (sql server must have permissions to write to this directory)
------------------------------

DECLARE @COMMAND varchar(8000)

set @COMMAND = 'SQLCMD -S . -W -h-1 -d ' + @DATABASE + ' -Q "SET NOCOUNT ON ; SELECT rtrim(istaffid), rtrim(school.ccode), rtrim(clastname), rtrim(cfirstname), '''', '''', '''', '''', '''', rtrim(cUserName) FROM staff join school on staff.ischoolid = school.ischoolid" -s "," -o "' + @PATH + 'staff.csv"'
EXECUTE master..xp_cmdshell @COMMAND;


set @COMMAND = 'SQLCMD -S . -W -h-1 -d ' + @DATABASE + ' -Q "SET NOCOUNT ON ; select rtrim(school.ccode), rtrim(class.iclassid), rtrim(class.cname), rtrim(course.cCourseCode), RTRIM(course.cName), RTRIM(class.iIdealCapacity),  RTRIM(class.iMaxCapacity) from school join class on class.ischoolid = school.iSchoolID join course on course.icourseid = class.icourseid where idistrictid = 1" -s "," -o "' + @PATH + 'class.csv"';
EXECUTE master..xp_cmdshell @COMMAND;


set @COMMAND = 'SQLCMD -S . -W -h-1 -d ' + @DATABASE + ' -Q "SET NOCOUNT ON ; select rtrim(school.ccode),	rtrim(class.iclassid), rtrim(classresource.istaffid) from school join Class	on class.iSchoolID = school.iSchoolID join ClassResource on ClassResource.iClassID = class.iClassID where iDistrictID = 1" -s "," -o "' + @PATH + 'staffclass.csv"';
EXECUTE master..xp_cmdshell @COMMAND;


set @COMMAND = 'SQLCMD -S . -W -h-1 -d ' + @DATABASE + ' -Q "SET NOCOUNT ON ; select rtrim(school.ccode),	RTRIM(class.iClassID), RTRIM(student.cStudentNumber), rtrim(student.cLegalFirstName), rtrim(student.cLegalLastName), rtrim(student.dBirthdate) from School join Class on class.iSchoolID = school.iSchoolID join Enrollment	on enrollment.iClassID = class.iClassID join student on student.iStudentID = enrollment.iStudentID" -s "," -o "' + @PATH + 'studentclass.csv"';
EXECUTE master..xp_cmdshell @COMMAND;

----

EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

----