queries require the ANSI_NULLS and ANSI_WARNINGS options
to be set for the connection. This ensures consistent
query semantics. Enable these options and then reissue
your query." and setting
ANSI_NULL and ANSI_WARNINGS explicitly in the proc don't
seem to fix it. Here is my code:
CREATE PROCEDURE osp_importexcel
@.filename varchar(1000)
AS
Set ANSI_NULLS ON
Set ANSI_WARNINGS ON
declare @.sqlst as nvarchar(1000)
set @.sqlst = 'drop table Mailing'
exec sp_sqlexec @.sqlst
set @.sqlst = 'select * into Mailing from OPENROWSET
(''Microsoft.Jet.OLEDB.4.0'',
''Excel
8.0;Database='+@.filename+''', [Data$])'
exec sp_sqlexec @.sqlst
GO
Any ideas?
Thanks much in advanceDirectly from BOL
For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from
the initial creation time of the stored procedure. Whenever the stored
procedure is subsequently executed, the setting of SET ANSI_NULLS is
restored to its originally used value and takes effect. When invoked inside
a stored procedure, the setting of SET ANSI_NULLS is not changed.
and
SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET
ANSI_NULLS when a stored procedure is created or altered. These original
settings are used when the stored procedure is executed. Therefore, any
client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are
ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET
ANSI_NULLS statements that occur within the stored procedure do not affect
the functionality of the stored procedure.
"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
news:008b01c3d61d$e358d030$a601280a@.phx.gbl...
quote:|||Thanks Scott. So I made sure the server and db has
> I am getting the following error message "Heterogeneous
> queries require the ANSI_NULLS and ANSI_WARNINGS options
> to be set for the connection. This ensures consistent
> query semantics. Enable these options and then reissue
> your query." and setting
> ANSI_NULL and ANSI_WARNINGS explicitly in the proc don't
> seem to fix it. Here is my code:
> CREATE PROCEDURE osp_importexcel
> @.filename varchar(1000)
> AS
> Set ANSI_NULLS ON
> Set ANSI_WARNINGS ON
> declare @.sqlst as nvarchar(1000)
> set @.sqlst = 'drop table Mailing'
> exec sp_sqlexec @.sqlst
> set @.sqlst = 'select * into Mailing from OPENROWSET
> (''Microsoft.Jet.OLEDB.4.0'',
> ''Excel
> 8.0;Database='+@.filename+''', [Data$])'
> exec sp_sqlexec @.sqlst
> GO
> Any ideas?
> Thanks much in advance
>
ANSI_NULLS and ANSI_WARNINGS
quote:
>--Original Message--
>Directly from BOL
>For stored procedures, SQL Server uses the SET ANSI_NULLS
setting value from
quote:
>the initial creation time of the stored procedure.
Whenever the stored
quote:
>procedure is subsequently executed, the setting of SET
ANSI_NULLS is
quote:
>restored to its originally used value and takes effect.
When invoked inside
quote:
>a stored procedure, the setting of SET ANSI_NULLS is not
changed.
quote:
>and
>SQL Server saves the settings of both SET
QUOTED_IDENTIFIER and SET
quote:
>ANSI_NULLS when a stored procedure is created or altered.
These original
quote:
>settings are used when the stored procedure is executed.
Therefore, any
quote:
>client session settings for SET QUOTED_IDENTIFIER and SET
ANSI_NULLS are
quote:
>ignored during stored procedure execution. SET
QUOTED_IDENTIFIER and SET
quote:
>ANSI_NULLS statements that occur within the stored
procedure do not affect
quote:|||I suffer from premature submission...anyway.
>the functionality of the stored procedure.
>
>"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
>news:008b01c3d61d$e358d030$a601280a@.phx.gbl...
>
>.
>
Thanks Scott. I made sure the Server and the DB both have
ANSI_NULLS AND ANSI_WARNINGS set to on then I recreated my
proc and I still get the same error. Any other ideas?
Thanks.
quote:
>--Original Message--
>Directly from BOL
>For stored procedures, SQL Server uses the SET ANSI_NULLS
setting value from
quote:
>the initial creation time of the stored procedure.
Whenever the stored
quote:
>procedure is subsequently executed, the setting of SET
ANSI_NULLS is
quote:
>restored to its originally used value and takes effect.
When invoked inside
quote:
>a stored procedure, the setting of SET ANSI_NULLS is not
changed.
quote:
>and
>SQL Server saves the settings of both SET
QUOTED_IDENTIFIER and SET
quote:
>ANSI_NULLS when a stored procedure is created or altered.
These original
quote:
>settings are used when the stored procedure is executed.
Therefore, any
quote:
>client session settings for SET QUOTED_IDENTIFIER and SET
ANSI_NULLS are
quote:
>ignored during stored procedure execution. SET
QUOTED_IDENTIFIER and SET
quote:
>ANSI_NULLS statements that occur within the stored
procedure do not affect
quote:|||First, it is NOT the server / database setting - it is the setting in effect
>the functionality of the stored procedure.
>
>"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
>news:008b01c3d61d$e358d030$a601280a@.phx.gbl...
>
>.
>
when you create / alter the proc. The best way to do this is via QA where
you EXPLICITLY set the options needed. Using EM is an easy way to create
obscure problems since you can't be certain as to what options are in effect
at any given point in time. Learn to do everything via QA and you will be
better off in the long term.
Secondly, ANSI_WARNINGS is not a setting "saved" with the procedure. The
setting is evaluated when the proc is executed, so your connection must be
setting this off. Often this is set off because developers don't want to
deal with the "null value eliminated from aggregate" message. However, you
can set this within the procedure AFAIK.
As an aside, you can use profiler to watch the exact commands used by EM.
Give it a try - and be amazed.
"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
news:025a01c3d62a$f3e04e80$a501280a@.phx.gbl...[QUOTE]
> I suffer from premature submission...anyway.
> Thanks Scott. I made sure the Server and the DB both have
> ANSI_NULLS AND ANSI_WARNINGS set to on then I recreated my
> proc and I still get the same error. Any other ideas?
> Thanks.
> setting value from
> Whenever the stored
> ANSI_NULLS is
> When invoked inside
> changed.
> QUOTED_IDENTIFIER and SET
> These original
> Therefore, any
> ANSI_NULLS are
> QUOTED_IDENTIFIER and SET
> procedure do not affect|||Scott,
Thanks so much for your patience. I always created all my
procs in EM not realizing that I should be doing it in
QA. Thanks for the tip. Still having the same problem
though, here's what I've done.
I started the Profiler and created the proc in QA like so:
Set ANSI_NULLS ON
Set ANSI_WARNINGS ON
Go
CREATE PROCEDURE osp_excelimport
@.filename varchar(1000)
AS
declare @.sqlst as nvarchar(1000)
set @.sqlst = 'drop table Mailing'
--exec sp_sqlexec @.sqlst
set @.sqlst = 'select * into Mailing from OPENROWSET
(''Microsoft.Jet.OLEDB.4.0'',
''Excel
8.0;Database='+@.filename+''', [Data$])'
exec sp_sqlexec @.sqlst
GO
I then ran this and called the proc and got the same error
message. I didn't see anything in the Profiler that
looked funny. Here is the output:
28 13 Set ANSI_NULLS ON Set ANSI_WARNINGS ON
NULL 72 SQL Query Analyzer sa 75
NULL 2004-01-08 14:57:43.653 NULL NULL
NULL
29 40 Set ANSI_NULLS ON NULL 72
SQL Query Analyzer sa 75 NULL
2004-01-08 14:57:43.653 NULL NULL NULL
30 41 Set ANSI_NULLS ON NULL 72
SQL Query Analyzer sa 75 0
2004-01-08 14:57:43.653 0 0 0
31 40 Set ANSI_WARNINGS ON NULL 72
SQL Query Analyzer sa 75 NULL
2004-01-08 14:57:43.670 NULL NULL NULL
32 41 Set ANSI_WARNINGS ON NULL 72
SQL Query Analyzer sa 75 0
2004-01-08 14:57:43.670 0 0 0
33 12 Set ANSI_NULLS ON Set ANSI_WARNINGS ON
NULL 72 SQL Query Analyzer sa 75
16 2004-01-08 14:57:43.653 0 0 0
34 13 CREATE PROCEDURE osp_excelimport
@.filename varchar(1000) AS declare @.sqlst as nvarchar
(1000) set @.sqlst = 'drop table nashMainMailing' --
exec sp_sqlexec @.sqlst set @.sqlst = 'select * into
nashMainMailing from OPENROWSET(''Microsoft.Je NULL 72
SQL Query Analyzer sa 75 NULL
2004-01-08 14:57:43.700 NULL NULL NULL
35 50 NULL NULL 72 SQL Query Analyzer
sa 75 NULL 2004-01-08 14:57:43.700
NULL NULL NULL
36 46 NULL NULL 72 SQL Query Analyzer
sa 75 NULL 2004-01-08 14:57:43.700
NULL NULL NULL
37 50 NULL NULL 72 SQL Query Analyzer
sa 75 0 2004-01-08 14:57:43.700
NULL NULL NULL
38 50 NULL NULL 72 SQL Query Analyzer
sa 75 NULL 2004-01-08 14:57:43.700
NULL NULL NULL
39 50 NULL NULL 72 SQL Query Analyzer
sa 75 0 2004-01-08 14:57:43.700
NULL NULL NULL
40 50 NULL NULL 72 SQL Query Analyzer
sa 75 NULL 2004-01-08 14:57:43.700
NULL NULL NULL
41 50 NULL NULL 72 SQL Query Analyzer
sa 75 0 2004-01-08 14:57:43.700
NULL NULL NULL
42 50 NULL NULL 72 SQL Query Analyzer
sa 75 NULL 2004-01-08 14:57:43.700
NULL NULL NULL
43 50 NULL NULL 72 SQL Query Analyzer
sa 75 0 2004-01-08 14:57:43.700
NULL NULL NULL
44 12 CREATE PROCEDURE osp_excelimport
@.filename varchar(1000) AS declare @.sqlst as nvarchar
(1000) set @.sqlst = 'drop table nashMainMailing' --
exec sp_sqlexec @.sqlst set @.sqlst = 'select * into
nashMainMailing from OPENROWSET(''Microsoft.Je NULL 72
SQL Query Analyzer sa 75 0
2004-01-08 14:57:43.700 21 0 0
45 13 exec
osp_excelimport " E:\inetpub\wwwroot\JoEllenNash\Text\2004
18
113035.xls" NULL 72 SQL Query Analyzer sa
59 NULL 2004-01-08 14:57:54.640 NULL
NULL NULL
46 40 exec
osp_excelimport " E:\inetpub\wwwroot\JoEllenNash\Text\2004
18
113035.xls" NULL 72 SQL Query Analyzer sa
59 NULL 2004-01-08 14:57:54.640 NULL
NULL NULL
47 12 exec
osp_excelimport " E:\inetpub\wwwroot\JoEllenNash\Text\2004
18
113035.xls" NULL 72 SQL Query Analyzer sa
59 0 2004-01-08 14:57:54.640 20 0
0
48 5 NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
I should also probably tell you that I am running this
from QA on a different server than where the db is. In
addition, when I run the two statments ("drop table.. and
OPENROWSET...) in one QA window it runs fine. I have no
idea but I will keep searching.
Thanks again for all you help.
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:
>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]
>
>.
>
No comments:
Post a Comment