Hi,
One of my users is supposed to have full access to all other dimesions except for one and thus I have defined a customized role on one of 4 dimensions that I have.
When I test the role (from the front-end), I am getting a strange message in my front-end :confused: and could it be that I also have to define a role for this user on all other dimensions? Should I define the role on the server or the client :confused: I am a bit confused and will appreciate your help.
Thanks.Are you testing the role from your machine or from the users machine? What happens when they log on to your machine? Can you post the error message that you are getting?|||Are you testing the role from your machine or from the users machine? What happens when they log on to your machine? Can you post the error message that you are getting?
Hi and thanks for the reply.
At the moment I am testing this from my own machine by logging in (from my machine) as the test user and then trying to use the cube. Could this be the reason? The front-end that I'm using is MSDA (Microsoft Data Analyzer) and the error message that I get is as follows:
Formula error - cannot bind: unknown member: "[Product].[_MAXIMAL_MIN_]"
Any clues please.sql
Showing posts with label defined. Show all posts
Showing posts with label defined. Show all posts
Wednesday, March 28, 2012
Question Regarding Views and Indexes
If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.
Question Regarding Views and Indexes
If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.
Wednesday, March 21, 2012
Question on Settings in Connection
I've programmed a user defined function (SQL2000), which in a specific query
references a linked server (another SQL instance, BTW contained in same
physical server). The sintaxis is ok, but i couldn't apply the definition
because of following error:
"Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistente
query semantics. Enable these options and then reissue your query."
I set the corresponding settings in both servers, section Connections of
Server's properties, but to no avail.
Which is the trick here? How is resolved the 'connection' issue referred in
the error message?
Thanks in advanceMiguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> I've programmed a user defined function (SQL2000), which in a specific
> query references a linked server (another SQL instance, BTW contained in
> same physical server). The sintaxis is ok, but i couldn't apply the
> definition because of following error:
> "Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
> options to be set for the connection. This ensures consistente
> query semantics. Enable these options and then reissue your query."
> I set the corresponding settings in both servers, section Connections of
> Server's properties, but to no avail.
> Which is the trick here? How is resolved the 'connection' issue referred
> in the error message?
The trick is to stop using Enterprise Manager for editing functions and
stored procedures. Use Query Analyzer instead, this is a far better tool
for the task.
The particular problem here, is that Enterprise Manager creates functions
and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
settings are saved with the procedure/function. Thus you need to recreate
the function with ANSI_NULLS ON. (In Query Analyzer all needed options
are ON by default.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many thanks, it resolves the problem.
In ahead I will take this great tip in account.
"Erland Sommarskog" wrote:
> Miguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> The trick is to stop using Enterprise Manager for editing functions and
> stored procedures. Use Query Analyzer instead, this is a far better tool
> for the task.
> The particular problem here, is that Enterprise Manager creates functions
> and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
> settings are saved with the procedure/function. Thus you need to recreate
> the function with ANSI_NULLS ON. (In Query Analyzer all needed options
> are ON by default.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>
references a linked server (another SQL instance, BTW contained in same
physical server). The sintaxis is ok, but i couldn't apply the definition
because of following error:
"Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistente
query semantics. Enable these options and then reissue your query."
I set the corresponding settings in both servers, section Connections of
Server's properties, but to no avail.
Which is the trick here? How is resolved the 'connection' issue referred in
the error message?
Thanks in advanceMiguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> I've programmed a user defined function (SQL2000), which in a specific
> query references a linked server (another SQL instance, BTW contained in
> same physical server). The sintaxis is ok, but i couldn't apply the
> definition because of following error:
> "Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
> options to be set for the connection. This ensures consistente
> query semantics. Enable these options and then reissue your query."
> I set the corresponding settings in both servers, section Connections of
> Server's properties, but to no avail.
> Which is the trick here? How is resolved the 'connection' issue referred
> in the error message?
The trick is to stop using Enterprise Manager for editing functions and
stored procedures. Use Query Analyzer instead, this is a far better tool
for the task.
The particular problem here, is that Enterprise Manager creates functions
and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
settings are saved with the procedure/function. Thus you need to recreate
the function with ANSI_NULLS ON. (In Query Analyzer all needed options
are ON by default.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many thanks, it resolves the problem.
In ahead I will take this great tip in account.
"Erland Sommarskog" wrote:
> Miguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> The trick is to stop using Enterprise Manager for editing functions and
> stored procedures. Use Query Analyzer instead, this is a far better tool
> for the task.
> The particular problem here, is that Enterprise Manager creates functions
> and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
> settings are saved with the procedure/function. Thus you need to recreate
> the function with ANSI_NULLS ON. (In Query Analyzer all needed options
> are ON by default.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>
Subscribe to:
Posts (Atom)