Advertisement

08.01.2008 at 07:24AM PDT, ID: 23614269
[x]
Attachment Details

Dynamic SQL User to create excel for separate records in SQL Server 2000 table

Asked by printmedia in MS SQL Server

Hi all.

I posted a question last week about creating excel worksheets for distinct recrods in a SQL Server 2000 table, and MonkeyPushButton was kind enough to give me the solution. But now, when I'm using real data instead of test data, the data is not populated in the correct columns.

For example, as you can see below I am selecting Entity_Code, Name, State, TopParentNumber and TopParentName, the template (.xls) has column names in the same order. But when it creates the file, it does not populate the data in the correct column. So instead of having the TopParentName data in the TopParentName column, that information actually appears in the TopParentNumber column.

Any idea why this is happening? Thank you in advance.Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:

DECLARE
	@LOC int,
	@FolderPath varchar(255),
	@TemplateFilePath varchar(255)
 
SET @TemplateFilePath = 'D:\ACCN EXCEL FILE TEST\i.xls'
SET @FolderPath = 'D:\ACCN EXCEL FILE TEST\'
 
DECLARE c_MyCurs CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT  LOC
FROM dbo.LOC_1
WHERE (Excel_Status = 0)
OPEN c_MyCurs
FETCH NEXT FROM c_MyCurs INTO @LOC
WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @OLEDBInitString varchar(8000),
		@FilePath varchar(1000),	
		@CopyCmd varchar(1000)
	SET @FilePath = @FolderPath + 'ExcelFile_' + CAST(@LOC as varchar) + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '_'+ cast(datepart(hh,getdate()) as varchar)+ '_'+cast(datepart(mi,getdate()) as varchar)+'_'+cast(datepart(ss,getdate()) as varchar)+'.xls'  
	SET @CopyCmd = 'COPY /Y "' + @TemplateFilePath + '" "' + @FilePath + '"'
	EXEC master.dbo.xp_cmdshell @CopyCmd, no_output
	SET @OLEDBInitString = 'Data Source="' + @FilePath + '";Extended properties=Excel 5.0;'
	DECLARE @Insert varchar(8000)
	SET @Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''' + @OLEDBInitString + ''')...[Sheet1$] 
			SELECT Entity_Code, Name,State, TopParentNumber,TopParentName  FROM LOC_1 WHERE EXCEL_STATUS = 0 AND LOC= ' + CAST(@LOC as varchar) 
	
EXEC (@Insert)
	
	UPDATE 
       	 dbo.ACCN_Final
	SET 
        	PATH = @FolderPath, 
       	 ATTACHMENT = 'ExcelFile_' + CAST(@LOC as varchar) + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '_'+ cast(datepart(hh,getdate()) as varchar)+ '_'+cast(datepart(mi,getdate()) as varchar)+'_'+cast(datepart(ss,getdate()) as varchar)+'.xls'  
	WHERE 
        LOC= @LOC and Sent= 0
 
	FETCH NEXT FROM c_MyCurs INTO @LOC
END
CLOSE c_MyCurs
DEALLOCATE c_MyCurs
 
Keywords: Dynamic SQL User to create excel for …
Title
1 Select distinct
2 templating help
3 Access to Excel Template
4 Exporting data to an Excel template
5 NOT DISTINCT
6 Dynamic form for Access Database Q…
 
Loading Advertisement...
 
[+][-]08.01.2008 at 07:35AM PDT, ID: 22138646

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 07:45AM PDT, ID: 22138757

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 07:49AM PDT, ID: 22138796

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 07:56AM PDT, ID: 22138871

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 08:01AM PDT, ID: 22138932

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 08:16AM PDT, ID: 22139120

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 08:27AM PDT, ID: 22139247

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 09:07AM PDT, ID: 22139593

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 09:18AM PDT, ID: 22139701

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 09:27AM PDT, ID: 22139800

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 09:39AM PDT, ID: 22139880

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: MS SQL Server
Sign Up Now!
Solution Provided By: EugeneZ
Participating Experts: 2
Solution Grade: A
 
 
[+][-]08.01.2008 at 12:28PM PDT, ID: 22141257

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 12:35PM PDT, ID: 22141315

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 12:36PM PDT, ID: 22141327

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]08.01.2008 at 12:46PM PDT, ID: 22141416

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628