Solved

Dynamic filename path in stored procedure

Posted on 2010-08-30
2
239 Views
Last Modified: 2012-05-10
Hi, I am trying to set the file path of my xml output file in my stored procedure to be dynamic.  I can't seem to get it to work; not sure what I'm doing wrong. I keep getting the error "Error writing to file Illegal characters in path." I've attached my code.
ALTER PROCEDURE [dbo].[spResumeXMLGeneration] 
@username varchar(50),
@profile varchar(50)

	AS
Declare @xml nvarchar(max)
Declare @filepath  nvarchar(max)
Set @filepath = 'C:\"' + @username + '".xml'


Set @xml =
(
Select 
employee.ProjectName,
employee.Employeeno,
employee.EmployeeName,
employee.Work_Summary,
employee.Accreditation_Affliation,
employee.PE_Certification,
employee.LEED_Certification,
employee.CPD_Certification,
employee.RoleonProject,
--subquery for Projects
(SELECT 
ProjectName,
Location

	
	FROM 
	Profiles_Employee_Projects_XML as project
	
	WHERE
	Username = employee.Username and
	
	Profile = employee.Profile and
	EmployeeID = employee.EmployeeID and 
	project.IncludeonResume = 1
	order by project.Sort_Order
	
	
	
	FOR
		XML PATH ('Projects'),TYPE -- Column is typed so it nests as XML, not text.
	)	
		,


--subquery for Education
(SELECT 
School_Facility,
Degree_Completed,
Completion_Year

	
	FROM 
	Profiles_Employee_Education as education
	
	WHERE
	Username = employee.Username and
	
	Profile = employee.Profile and
	EmployeeID = employee.EmployeeID 
	
	 
	order by education.Completion_Year

	
	FOR
		XML PATH ('Education'),TYPE -- Column is typed so it nests as XML, not text.
	)	
		,
		--subquery for Education

(SELECT 
ReferenceTitle,
ReferenceName,
ReferenceCompany,
ReferenceEmail,
ReferencePhone

	
	FROM 
	Profiles_Employee_References as reference
	
	WHERE
	Username = employee.Username and
	
	Profile = employee.Profile and
	EmployeeID = employee.EmployeeID 
	

	
	FOR
		XML PATH ('References'),TYPE -- Column is typed so it nests as XML, not text.
	)	
		





from Profiles_Employee_Info as employee
where Username = @username and Profile = @profile

FOR
XML PATH('Employee'),Elements, Root('Resumes') -- The element name for each row.

)
exec dbo.writetofile @xml, @filepath

Open in new window

0
Comment
Question by:imstac73
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33558937

Set @filepath = 'C:\"' + @username + '".xml'

should be:


Set @filepath = 'C:\' + @username + '.xml'
0
 

Author Comment

by:imstac73
ID: 33558987
Awesome, that worked.  Thanks so much!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question