Syntax reference for sql commands in excel 2003 via DTS packages

Hello,

Is there (on internet) an official reference for the syntax to use trying to
query data from an excel 2003 database in a DTS package ? here are the
details:

We use excel 2003 sp3, sql server 2000 sp4, mdac 2.8 sp1

I created a new package. Inside, I created a new connection.

This connection has a "data source" called "Microsoft Excel 97-2000"

After this I created a new data driven task that uses this connection, and
to access the data on my excel database I tried to use the following sql
sentence:

SELECT FIELDNAME FROM [SHEETNAME]

This works, but this doesn't (I want to erase all the "." chars):

select replace(fieldname,'.','' ) as col1 from [SHEETNAME]

.... as the function "replace" doesn't exists for this provider.


I achieved this using another approach using activex script:

DTSDestination("fieldname") = Replace(DTSSource("fieldname"),"." ,"")
Main = DTSTransformstat_InsertQuery

But this is slightly slower (not important for me)...


What I'm asking here is where can I find AN OFFICIAL REFERENCE for wich
functions commands sql syntax etc is allowed for this provider called
"Microsoft Excel 97-2000"

And for getting the highest grade (optional) , I'd like to understand a way
to know if this provider is from Microsoft, and what are the other
possibilities and info about them...


--
Roger Tranchez
MCTS
..NET 2005 and Database developer

Hi Roger,
I understand that you could not use REPLACE function for your Excel data source in your DTS package.
If I have misunderstood, please let me know.

I have not found any document talking about the official functions for this scenario, but I am trying to internally consult our product team to see whether or not we have such an official
document for supported SQL functions for "Microsoft Excel 97-2003" provider. The process may need more time. I appreciate your patience.

Additionally is it convenient for you to create a linked server for your Excel data source in your SQL Server and then you can directly execute the T-SQL statement to query the records? You
may refer to my following steps:
1. Create a linked server in your SQL Server instance with the following T-SQL statement:
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\changliw\My Documents\Book1.xls',
NULL,
'Excel 5.0'

2. Create a DTS package, drag a SQL Server data source, drag a Execute SQL Task and input the following T-SQL statement into the task:
Select REPLACE(NAME1,'.','') from [ExcelSource]...[Sheet1$]

If you have any other questions or concerns, please feel free to let me know.

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg-at-microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Roger,
I have not been able to get a response from our product team, now I am trying to follow up them.
The process may need a little long time. Could you please also leave me
(changliw_at_microsoft_dot_com) an email response so that I can timely update you via email?

Also could you please let me know if my suggestion of using linked server worked for you? Have a
nice day!


Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg-at-microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Roger,
Just clarify that currently we have no public documents talking about the functions. I was
informed that the Jet driver does not support those SQL functions now.

Please feel free to let me know if you have any other questions or concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg-at-microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======