am getting a new error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. The provider did not give any information about the
error.
OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The
provider did not give any information about the error.].
I don't know what I did because the query ran fine
before. Here's the stmt:
select * into nashMainMailing from OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\200418112958.xls', [Data$])
quote:
>--Original Message--
>First, it is NOT the server / database setting - it is
the setting in effect
quote:
>when you create / alter the proc. The best way to do
this is via QA where
quote:
>you EXPLICITLY set the options needed. Using EM is an
easy way to create
quote:
>obscure problems since you can't be certain as to what
options are in effect
quote:
>at any given point in time. Learn to do everything via
QA and you will be
quote:
>better off in the long term.
>Secondly, ANSI_WARNINGS is not a setting "saved" with the
procedure. The
quote:
>setting is evaluated when the proc is executed, so your
connection must be
quote:
>setting this off. Often this is set off because
developers don't want to
quote:
>deal with the "null value eliminated from aggregate"
message. However, you
quote:
>can set this within the procedure AFAIK.
>As an aside, you can use profiler to watch the exact
commands used by EM.
quote:Now you are just confusing things. My suggestion. Get your query working
>Give it a try - and be amazed.
>"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
>news:025a01c3d62a$f3e04e80$a501280a@.phx.gbl...
have[QUOTE]
my[QUOTE]
ANSI_NULLS[QUOTE]
not[QUOTE]
altered.[QUOTE]
executed.[QUOTE]
SET[QUOTE]
message[QUOTE]
message "Heterogeneous[QUOTE]
options[QUOTE]
reissue[QUOTE]
don't[QUOTE]
>
>.
>
within query analyzer as a script. Once that works, create a stored
procedure (don't know exactly what this buys you but that is your issue) to
do the same. Then work on getting the procedure to run.
Also, why are you using sp_sqlexec? It is my understanding that this was
deprecated in v7. After looking at the source, you would be better off IMHO
using "exec (<your string> )" since that is all the procedure does. However,
that brings up the issue of separate batches. So we're back to my advice
from above. Get the basic import query working first, then "improve" it bit
by bit to match your requirements.
"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
news:051a01c3d63d$787d1bc0$a301280a@.phx.gbl...[QUOTE]
> I was trying the run the OPENROWSET stmt from QA and now I
> am getting a new error:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error. The provider did not give any information about the
> error.
> OLE DB error trace [OLE/DB
> Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The
> provider did not give any information about the error.].
> I don't know what I did because the query ran fine
> before. Here's the stmt:
> select * into nashMainMailing from OPENROWSET
> ('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=E:\200418112958.xls', [Data$])
>
> the setting in effect
> this is via QA where
> easy way to create
> options are in effect
> QA and you will be
> procedure. The
> connection must be
> developers don't want to
> message. However, you
> commands used by EM.
> have
> my
> ANSI_NULLS
> not
> altered.
> executed.
> SET
> message
> message "Heterogeneous
> options
> reissue
> don't|||I have a tendancy to do that
I was trying to get myquery working in QA when I got the new error message.
What I have found is that somehow SQL doesn't have access
to the temp dir anymore. I don't know how that would have
changed but I will continue working on it.
Regarding sp_sqlexec, it is deprecated. However, I
couldn't get it to work any other way. Doing something
like exec "myquery" results in the following error.
The name "myquery" is not a valid identifier.
However, I think your right and I need to find a better
solution. BOL recommends "Remove or comment out all
references to sp_sqlexec." OK that does me no good. I
need an alternative.
Oh well. Thanks again for all your help.
quote:
>--Original Message--
>Now you are just confusing things. My suggestion. Get
your query working
quote:
>within query analyzer as a script. Once that works,
create a stored
quote:
>procedure (don't know exactly what this buys you but that
is your issue) to
quote:
>do the same. Then work on getting the procedure to run.
>Also, why are you using sp_sqlexec? It is my
understanding that this was
quote:
>deprecated in v7. After looking at the source, you would
be better off IMHO
quote:
>using "exec (<your string> )" since that is all the
procedure does. However,
quote:
>that brings up the issue of separate batches. So we're
back to my advice
quote:
>from above. Get the basic import query working first,
then "improve" it bit
quote:|||To execute the query contained in a string, you need to use the correct
>by bit to match your requirements.
>"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
>news:051a01c3d63d$787d1bc0$a301280a@.phx.gbl...
now I[QUOTE]
the[QUOTE]
via[QUOTE]
the[QUOTE]
message[QUOTE]
recreated[QUOTE]
ideas?[QUOTE]
SET[QUOTE]
effect.[QUOTE]
and[QUOTE]
consistent[QUOTE]
OPENROWSET[QUOTE]
>
>.
>
syntax
declare @.lc_command varchar(4000)
set @.lc_command = 'select * from ... '
exec (@.lc_command) -- the parentheses are VERY important
In general, when MS deprecates something, it usually offers upgrade
suggestions. In BOL, there is a "what's new" section that covers the
changes between versions. In the simplest case, you could merely extract
the code in the procedure and use it (see above - your syntax is the
problem). MS has also provided a new procedure which is much more
sophisticated - sp_executesql .
One last comment. Unless you are certain about your design, it is often
more useful to post the "what" of the problem, including the "how" that you
are currently using. In this case, you are trying to import data from an
excel file into the database using the most problem-prone features. Does it
need to be dynamic? Does it need to be a stored procedure? Are you certain
that SQL Server (and the account under which it runs) has access to the
file? Have you specified the location of the file correctly (remember, the
server is accessing the file and all paths are relative to that computer)?
One last comment - really. Search the NGs (particularly .programming).
Most issues have been covered to some degree in the past (along with
solutions, design ideas, and a lot of code!).
"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
news:008601c3d6d0$568c9020$a301280a@.phx.gbl...[QUOTE]
> I have a tendancy to do that
I was trying to get my> query working in QA when I got the new error message.
> What I have found is that somehow SQL doesn't have access
> to the temp dir anymore. I don't know how that would have
> changed but I will continue working on it.
> Regarding sp_sqlexec, it is deprecated. However, I
> couldn't get it to work any other way. Doing something
> like exec "myquery" results in the following error.
> The name "myquery" is not a valid identifier.
> However, I think your right and I need to find a better
> solution. BOL recommends "Remove or comment out all
> references to sp_sqlexec." OK that does me no good. I
> need an alternative.
> Oh well. Thanks again for all your help.
> your query working
> create a stored
> is your issue) to
> understanding that this was
> be better off IMHO
> procedure does. However,
> back to my advice
> then "improve" it bit
> now I
> the
> via
> the
> message
> recreated
> ideas?
> SET
> effect.
> and
> consistent
> OPENROWSET|||I posted this reply earlier but it is still not showing up
so hear we go again.
I was running my query in QA when I got the error
message. Some searching indicates that there may be a
permissions problem in the temp dir. However, I don't
know how that changed from the morning when the query ran
fine. So I will continue working with my query in QA.
Regarding sp_sqlexec, it is deprecated. However, I
couldn't get exec ("my string") to work before because I
didn't have the parens. Thank you so much!!! The BOL only
says "Remove or comment out all references to sp_sqlexec"
without showing what an alternative should be.
Thanks again
quote:
>--Original Message--
>Now you are just confusing things. My suggestion. Get
your query working
quote:
>within query analyzer as a script. Once that works,
create a stored
quote:
>procedure (don't know exactly what this buys you but that
is your issue) to
quote:
>do the same. Then work on getting the procedure to run.
>Also, why are you using sp_sqlexec? It is my
understanding that this was
quote:
>deprecated in v7. After looking at the source, you would
be better off IMHO
quote:
>using "exec (<your string> )" since that is all the
procedure does. However,
quote:
>that brings up the issue of separate batches. So we're
back to my advice
quote:
>from above. Get the basic import query working first,
then "improve" it bit
quote:|||Does it need to be dynamic? Yes.
>by bit to match your requirements.
>"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
>news:051a01c3d63d$787d1bc0$a301280a@.phx.gbl...
now I[QUOTE]
the[QUOTE]
via[QUOTE]
the[QUOTE]
message[QUOTE]
recreated[QUOTE]
ideas?[QUOTE]
SET[QUOTE]
effect.[QUOTE]
and[QUOTE]
consistent[QUOTE]
OPENROWSET[QUOTE]
>
>.
>
Does it need to be a stored procedure? Yes.
Are you certain that SQL Server (and the account under
which it runs) has access to the file? No.
Have you specified the location of the file correctly
(remember, the server is accessing the file and all paths
are relative to that computer)? Yes.
Thanks for the tips. I'm off to search the NG for
something like "importing an excel spreadsheet into SQL".
quote:
>--Original Message--
>To execute the query contained in a string, you need to
use the correct
quote:
>syntax
>declare @.lc_command varchar(4000)
>set @.lc_command = 'select * from ... '
>exec (@.lc_command) -- the parentheses are VERY important
>In general, when MS deprecates something, it usually
offers upgrade
quote:
>suggestions. In BOL, there is a "what's new" section
that covers the
quote:
>changes between versions. In the simplest case, you
could merely extract
quote:
>the code in the procedure and use it (see above - your
syntax is the
quote:
>problem). MS has also provided a new procedure which is
much more
quote:
>sophisticated - sp_executesql .
>One last comment. Unless you are certain about your
design, it is often
quote:
>more useful to post the "what" of the problem, including
the "how" that you
quote:
>are currently using. In this case, you are trying to
import data from an
quote:
>excel file into the database using the most problem-prone
features. Does it
quote:
>need to be dynamic? Does it need to be a stored
procedure? Are you certain
quote:
>that SQL Server (and the account under which it runs) has
access to the
quote:
>file? Have you specified the location of the file
correctly (remember, the
quote:
>server is accessing the file and all paths are relative
to that computer)?
quote:
>One last comment - really. Search the NGs
(particularly .programming).
quote:
>Most issues have been covered to some degree in the past
(along with
quote:
>solutions, design ideas, and a lot of code!).
>"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
>news:008601c3d6d0$568c9020$a301280a@.phx.gbl...
access[QUOTE]
have[QUOTE]
that[QUOTE]
would[QUOTE]
message[QUOTE]
about[QUOTE]
error.].[QUOTE]
is[QUOTE]
do[QUOTE]
an[QUOTE]
what[QUOTE]
with[QUOTE]
your[QUOTE]
both[QUOTE]
procedure.[QUOTE]
of[QUOTE]
ANSI_NULLS is[QUOTE]
stored[QUOTE]
ANSI_WARNINGS[QUOTE]
proc[QUOTE]
>
>.
>
