IMEX=1 in OpenDataSource

I'm having an issue with selecting data from an Excel 2002 spreadsheet using
OpenDataSource (or DTS for that matter). Excel will sometimes identify the
column type as numeric and ignore all character data in the column,
returning nulls instead of the data (known issue -
http://support.microsoft.com/?id=194124). I've editted my registry to force
the Jet drive to always use text as the data types for columns; however, the
registry is ignored unless the "IMEX=1" switch appears in the connection
string. All the examples of using the switch that I've found have been for
using it with the command object in VBScript. Can this switch be used with
the OpenDataSource TSQL command? If so, what is the syntax and do I need to
have Office installed on the sql server?
Thanks!!!
WalterHi
It can be used according to http://www.connectionstrings.com/ e.g.
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended
Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
There is also a MAXSCANROWS extended property
http://support.microsoft.com/defaul...kb;en-us;278973
John
"Walt Mallon" wrote:

> I'm having an issue with selecting data from an Excel 2002 spreadsheet usi
ng
> OpenDataSource (or DTS for that matter). Excel will sometimes identify th
e
> column type as numeric and ignore all character data in the column,
> returning nulls instead of the data (known issue -
> http://support.microsoft.com/?id=194124). I've editted my registry to for
ce
> the Jet drive to always use text as the data types for columns; however, t
he
> registry is ignored unless the "IMEX=1" switch appears in the connection
> string. All the examples of using the switch that I've found have been fo
r
> using it with the command object in VBScript. Can this switch be used wit
h
> the OpenDataSource TSQL command? If so, what is the syntax and do I need
to
> have Office installed on the sql server?
> Thanks!!!
> Walter
>
>|||Got it!!! Had some quotes in the wrong place:
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\CEXP-Mixed-Analyze.xls;Extended properties="Excel
8.0;HDR=YES;IMEX=1"')...[data$]
That one worked!!!
Thanks.
"Walt Mallon" <waltmallon@.hotmail.nospam.com> wrote in message
news:e3M07$AyFHA.2652@.TK2MSFTNGP14.phx.gbl...
> I'm having an issue with selecting data from an Excel 2002 spreadsheet
> using OpenDataSource (or DTS for that matter). Excel will sometimes
> identify the column type as numeric and ignore all character data in the
> column, returning nulls instead of the data (known issue -
> http://support.microsoft.com/?id=194124). I've editted my registry to
> force the Jet drive to always use text as the data types for columns;
> however, the registry is ignored unless the "IMEX=1" switch appears in the
> connection string. All the examples of using the switch that I've found
> have been for using it with the command object in VBScript. Can this
> switch be used with the OpenDataSource TSQL command? If so, what is the
> syntax and do I need to have Office installed on the sql server?
> Thanks!!!
> Walter
>

Image positioning

I have a a few image fields within the body of a report. No matter what I
do, remove vertical spacing, position manually etc. I cannot seem to get
them to render in the correct position within the body of the report while
previewing in visual studio. Apparently I do not understand the left to
right, top to bottom positioning. Does anyone have input on this? If I
remove vertical spacing, they overlap, if I move the image below the first
down a little to get rid of the overlap, I get an inch of white space. The
report has sub reports and a lot of other information and when rendered
displays one record per page. Very frustrating. Any Ideas gang?
Clint JenningsI want to thank everyone for the quick response on this. If anyone one is
interested, I wrapped the images with a rectangle and they stayed in place.
"Clint Jennings" wrote:
> I have a a few image fields within the body of a report. No matter what I
> do, remove vertical spacing, position manually etc. I cannot seem to get
> them to render in the correct position within the body of the report while
> previewing in visual studio. Apparently I do not understand the left to
> right, top to bottom positioning. Does anyone have input on this? If I
> remove vertical spacing, they overlap, if I move the image below the first
> down a little to get rid of the overlap, I get an inch of white space. The
> report has sub reports and a lot of other information and when rendered
> displays one record per page. Very frustrating. Any Ideas gang?
> Clint Jennings
>
>