I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
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/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards
Saturday, February 25, 2012
Deferred Name Resolution gone wild.
I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
Regards
Hello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
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/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
Regards
Hello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
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/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards
Deferred Name Resolution gone wild.
I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
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/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
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/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards
Deferred constraints?
Is this on the list for Katmai? (BOL shows only what is available in the current CTP, so it is hard to know).
thanks
This is not planned for Katmai. -- Umachandar Jayachandran Microsoft SQL Server Performance Team SQL Server Engine Team Tips Blog at http://blogs.msdn.com/sqltips/default.aspx This posting is provided "AS IS" with no warranties, and confers no rights. "Lakusha"@.discussions.microsoft.com wrote on Thu, 12 Jul 2007 07:11:08 -0700: L> Is this on the list for Katmai? (BOL shows only what is available in L> the current CTP, so it is hard to know). L> thanksDeferred constraints sql server 2005
Is there any way to defer constriants in sql server 2005? I have found some sites that say use the keyword deferred but that always give me an "Incorrect syntax near 'deferred' "error.
Hi Jeff,
Can you expand on the meaning of "deferred constraint", please?
If what you are trying, is to disable a constraint to do some bulk operations and then enable it back, then see "alter table" in BOL.
with check / nocheck --> is to check or nocheck the data when you enable the constraint
check / nocheck --> to enable / disable the constraint
Example:
use tempdb
go
create table dbo.t1(
c1 int constraint ck_c1 check (c1 > 0)
)
go
insert into dbo.t1 values(-1)
go
alter table dbo.t1
nocheck constraint ck_c1
go
select objectproperty(object_id('ck_c1'), 'CnstIsDisabled')
go
insert into dbo.t1 values(-1)
go
select * from dbo.t1
go
alter table dbo.t1
with nocheck check constraint ck_c1
go
select objectproperty(object_id('ck_c1'), 'CnstIsNotTrusted')
go
-- will give error because there is a value breaking the constraint
alter table dbo.t1
with check check constraint ck_c1
go
delete dbo.t1 where c1 = -1
go
alter table dbo.t1
with check check constraint ck_c1
go
select objectproperty(object_id('ck_c1'), 'CnstIsNotTrusted')
go
drop table dbo.t1
go
Be careful when enabling the constraint back, remember to use "with check" to make is trusted. The query optimizer uses constraints when choosing the execution plan, but they need to be trusted.
AMBDefeat SQLDUMPER?
Every time DTEXEC.EXE crashes, SQLDUMPER.EXE process pops up and quickly exits. But I would like my JIT debugger to attach like it happens with other processes. Possible to do? Please inform how..
Thanks.There is no way to disable sqldumper except to attach your debugger to the process before the crash. However, you can use your debugger (at least VS and WinDBG) to open the generated crash dump and look at it.
Thanks.There is no way to disable sqldumper except to attach your debugger to the process before the crash. However, you can use your debugger (at least VS and WinDBG) to open the generated crash dump and look at it.
Defaut Value for new column in replication
Hi,
I am dealing with merge replication.
I need to add a new column in a table (already in replication) has 500000
records.
I have set 0 as default value for that column.
I want to update that column with a return value of a user-defined function.
Is it possible to call a user-defined function as a default value?
So that it will get sync while adding the column in replication else I have
to update the column separately. Same to be replicated for 20 databases
results in huge amount of data.
Is there any alternative for this?
Please advice.
Thanks,
Soura.
Hi Sounder,
Yes. U can give the UDF as a Default value.
U can do it as a normal default one. In the default Value column specify the
UDF name with the corresponding Owner name and pass the values for parameter.
Thanks,
Herbert
"SouRa" wrote:
> Hi,
> I am dealing with merge replication.
> I need to add a new column in a table (already in replication) has 500000
> records.
> I have set 0 as default value for that column.
> I want to update that column with a return value of a user-defined function.
> Is it possible to call a user-defined function as a default value?
> So that it will get sync while adding the column in replication else I have
> to update the column separately. Same to be replicated for 20 databases
> results in huge amount of data.
> Is there any alternative for this?
> Please advice.
> Thanks,
> Soura.
>
I am dealing with merge replication.
I need to add a new column in a table (already in replication) has 500000
records.
I have set 0 as default value for that column.
I want to update that column with a return value of a user-defined function.
Is it possible to call a user-defined function as a default value?
So that it will get sync while adding the column in replication else I have
to update the column separately. Same to be replicated for 20 databases
results in huge amount of data.
Is there any alternative for this?
Please advice.
Thanks,
Soura.
Hi Sounder,
Yes. U can give the UDF as a Default value.
U can do it as a normal default one. In the default Value column specify the
UDF name with the corresponding Owner name and pass the values for parameter.
Thanks,
Herbert
"SouRa" wrote:
> Hi,
> I am dealing with merge replication.
> I need to add a new column in a table (already in replication) has 500000
> records.
> I have set 0 as default value for that column.
> I want to update that column with a return value of a user-defined function.
> Is it possible to call a user-defined function as a default value?
> So that it will get sync while adding the column in replication else I have
> to update the column separately. Same to be replicated for 20 databases
> results in huge amount of data.
> Is there any alternative for this?
> Please advice.
> Thanks,
> Soura.
>
DefaultValue
Hi all,
I have a table like
Tablename: Test
tst_id uniqueidentifier Not null PK
= RowGuid
tst_ts2_id uniqueidentifier Not null
Default '?
tst_code Varchar 50 Not null
And Another table
Tablename: Test2
ts2_id uniqueidentifier Not null PK
ts2_name Varchar 50 Not null
ts2_default Boolean False
there is defined a FK tst_ts2_id and ts2_id
I would to insert into table Test a row with the folloing insert
INSERT INTO test
(tst_code)
VALUES ('123456')
Now I would, that there is a trigger, or something else, that inserts me an
tst_ts2_id the ID from Table Test2, where ts2_default = True.
How to du. I tryed with trigger, but it seams, that the trigger fires to
late, if I delete the FK, then it will be inserted.
I wrote a function that returns the Id to insert, but is it possible, that
like default-value can be insertted the result of a skalar function ?
Thank you all
Klaus AstnerI'm not certain if this is what you are trying to do and as you did not post
DDL or the statements or triggers that you tried I made some guesses, but
the following worked fine for me:
create table Test2 (
ts2_id uniqueidentifier Not null primary key default(newid()),
ts2_name Varchar(50) Not null,
ts2_default bit default(0)
)
go
create function dbo.def_ts2id() returns uniqueidentifier
as
begin
declare @.guid uniqueidentifier
select @.guid = ts2_id from test2 where ts2_default = 1
return @.guid
end
go
create table Test (
tst_id uniqueidentifier Not null primary key default(newid()),
tst_ts2_id uniqueidentifier Not null foreign key references test2(ts2_id)
default(dbo.def_ts2id()),
tst_code Varchar(50) Not null
)
go
insert into test2(ts2_name,ts2_default) values('default',1)
go
INSERT INTO test(tst_code) VALUES('123456')
go
select * from test
select * from test2
go
Result:
tst_id tst_ts2_id
tst_code
-- -- --
---
18CBDA2A-6B29-43C9-8A42-B86B9102E6D4 70DC14A0-F744-4006-8CE0-A9C8695D8E83
123456
ts2_id ts2_name
ts2_default
-- ---
-- --
70DC14A0-F744-4006-8CE0-A9C8695D8E83 default
1
Craig
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Astner Klaus" <k.astnerremove_this@.virgilio.it> wrote in message
news:eFHHSRPXDHA.2648@.TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I have a table like
> Tablename: Test
> tst_id uniqueidentifier Not null PK
> = RowGuid
> tst_ts2_id uniqueidentifier Not null
> Default '?
> tst_code Varchar 50 Not null
>
> And Another table
> Tablename: Test2
> ts2_id uniqueidentifier Not null PK
> ts2_name Varchar 50 Not null
> ts2_default Boolean False
>
> there is defined a FK tst_ts2_id and ts2_id
>
> I would to insert into table Test a row with the folloing insert
>
> INSERT INTO test
> (tst_code)
> VALUES ('123456')
> Now I would, that there is a trigger, or something else, that inserts me
an
> tst_ts2_id the ID from Table Test2, where ts2_default = True.
>
> How to du. I tryed with trigger, but it seams, that the trigger fires to
> late, if I delete the FK, then it will be inserted.
> I wrote a function that returns the Id to insert, but is it possible, that
> like default-value can be insertted the result of a skalar function ?
>
> Thank you all
>
> Klaus Astner
>
>
>
I have a table like
Tablename: Test
tst_id uniqueidentifier Not null PK
= RowGuid
tst_ts2_id uniqueidentifier Not null
Default '?
tst_code Varchar 50 Not null
And Another table
Tablename: Test2
ts2_id uniqueidentifier Not null PK
ts2_name Varchar 50 Not null
ts2_default Boolean False
there is defined a FK tst_ts2_id and ts2_id
I would to insert into table Test a row with the folloing insert
INSERT INTO test
(tst_code)
VALUES ('123456')
Now I would, that there is a trigger, or something else, that inserts me an
tst_ts2_id the ID from Table Test2, where ts2_default = True.
How to du. I tryed with trigger, but it seams, that the trigger fires to
late, if I delete the FK, then it will be inserted.
I wrote a function that returns the Id to insert, but is it possible, that
like default-value can be insertted the result of a skalar function ?
Thank you all
Klaus AstnerI'm not certain if this is what you are trying to do and as you did not post
DDL or the statements or triggers that you tried I made some guesses, but
the following worked fine for me:
create table Test2 (
ts2_id uniqueidentifier Not null primary key default(newid()),
ts2_name Varchar(50) Not null,
ts2_default bit default(0)
)
go
create function dbo.def_ts2id() returns uniqueidentifier
as
begin
declare @.guid uniqueidentifier
select @.guid = ts2_id from test2 where ts2_default = 1
return @.guid
end
go
create table Test (
tst_id uniqueidentifier Not null primary key default(newid()),
tst_ts2_id uniqueidentifier Not null foreign key references test2(ts2_id)
default(dbo.def_ts2id()),
tst_code Varchar(50) Not null
)
go
insert into test2(ts2_name,ts2_default) values('default',1)
go
INSERT INTO test(tst_code) VALUES('123456')
go
select * from test
select * from test2
go
Result:
tst_id tst_ts2_id
tst_code
-- -- --
---
18CBDA2A-6B29-43C9-8A42-B86B9102E6D4 70DC14A0-F744-4006-8CE0-A9C8695D8E83
123456
ts2_id ts2_name
ts2_default
-- ---
-- --
70DC14A0-F744-4006-8CE0-A9C8695D8E83 default
1
Craig
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Astner Klaus" <k.astnerremove_this@.virgilio.it> wrote in message
news:eFHHSRPXDHA.2648@.TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I have a table like
> Tablename: Test
> tst_id uniqueidentifier Not null PK
> = RowGuid
> tst_ts2_id uniqueidentifier Not null
> Default '?
> tst_code Varchar 50 Not null
>
> And Another table
> Tablename: Test2
> ts2_id uniqueidentifier Not null PK
> ts2_name Varchar 50 Not null
> ts2_default Boolean False
>
> there is defined a FK tst_ts2_id and ts2_id
>
> I would to insert into table Test a row with the folloing insert
>
> INSERT INTO test
> (tst_code)
> VALUES ('123456')
> Now I would, that there is a trigger, or something else, that inserts me
an
> tst_ts2_id the ID from Table Test2, where ts2_default = True.
>
> How to du. I tryed with trigger, but it seams, that the trigger fires to
> late, if I delete the FK, then it will be inserted.
> I wrote a function that returns the Id to insert, but is it possible, that
> like default-value can be insertted the result of a skalar function ?
>
> Thank you all
>
> Klaus Astner
>
>
>
defaults in the DB
How do i know whats the default values on which table and which column and whats the default value
It should be like this
Table column constraint_name value
emp emp_id DF_emp_emp_no 1
emp join_date DF_emp_cur_date getdate()
How can i get these results on all the tables in my DB.
Thanks.sp_help '<table_name>'
After the column list of index(es), the proc will list any constraints by column name.|||select object_name(c.id)+'.'+col_name(c.id, c.colid)+' - '+
object_name(c.constid)+' value: '+text
from sysconstraints c
inner join syscomments sc on c.constid = sc.id
where(c.status & 5)= 5
and objectproperty(c.id,'IsMSShipped')= 0
order by object_name(c.id)|||Buy that man a drink!
It should be like this
Table column constraint_name value
emp emp_id DF_emp_emp_no 1
emp join_date DF_emp_cur_date getdate()
How can i get these results on all the tables in my DB.
Thanks.sp_help '<table_name>'
After the column list of index(es), the proc will list any constraints by column name.|||select object_name(c.id)+'.'+col_name(c.id, c.colid)+' - '+
object_name(c.constid)+' value: '+text
from sysconstraints c
inner join syscomments sc on c.constid = sc.id
where(c.status & 5)= 5
and objectproperty(c.id,'IsMSShipped')= 0
order by object_name(c.id)|||Buy that man a drink!
Defaults for Functions and Parameters
I need to be able to get the defaults specified for SQL functions and procedure using sql. I see in the SQL Server 2005 docuementation that for CLR Functions, the defaults used in the create are store in sys.parameters. I need to find out what the defaults are for SQL functions and procedures. I know they are store in the definition of the function (or Procedure), but I want to avoid parsing the text. Thank you.
CREATE FUNCTION [dbo].[myfunc] (@.first int = 1001)
RETURNS int
AS
BEGIN.......
There is no way to get the defaults for TSQL SPs/UDFs without parsing the definition.defaults
Can someone give me a simple example of how you use the
defaults in sql server 2000? I cant figure it outSee following exmaple:
(while creating table)
CREATE TABLE multinulls (
a int NULL,
b char(5) default 'hello'
)
you can also create default as an object and bind it to table's column
Ex:
CREATE DEFAULT abc_const AS 'abc'
GO
sp_bindefault abc_const, 'test_defaults.char1'
GO
-Vishal
"bill" <bkelleman@.pcdata1.com> wrote in message
news:02a901c377be$aada4a30$a101280a@.phx.gbl...
> Can someone give me a simple example of how you use the
> defaults in sql server 2000? I cant figure it out|||What exactly do you mean? How to insert default values or How to set it up?
Setup.
For example Open EM, Create a table two columns
Column 1 name it Log_Date datetime with a default of getdate()
Column 2 name it test_data varchar(20)
Open the table and insert something on the test_data column click on the
exclamation point to execute
or simply use query analyzer
BEGIN TRANSACTION
CREATE TABLE dbo.test0001
(
log_date datetime NULL,
test_data varchar(20) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.TEST001 ADD CONSTRAINT
DF_Table1_log_date DEFAULT getdate() FOR log_date
GO
COMMIT
INSERT INTO TEST0001(TEST_DATA)
SELECT 'TEST1'
SELECT * FROM TEST0001
If you create a new default on a column to an existing table with data then
you need to do something like
insert into test0001 default values
select * from test0001
Hope this helps
Yovan Fernandez
"bill" <bkelleman@.pcdata1.com> wrote in message
news:02a901c377be$aada4a30$a101280a@.phx.gbl...
> Can someone give me a simple example of how you use the
> defaults in sql server 2000? I cant figure it out
defaults in sql server 2000? I cant figure it outSee following exmaple:
(while creating table)
CREATE TABLE multinulls (
a int NULL,
b char(5) default 'hello'
)
you can also create default as an object and bind it to table's column
Ex:
CREATE DEFAULT abc_const AS 'abc'
GO
sp_bindefault abc_const, 'test_defaults.char1'
GO
-Vishal
"bill" <bkelleman@.pcdata1.com> wrote in message
news:02a901c377be$aada4a30$a101280a@.phx.gbl...
> Can someone give me a simple example of how you use the
> defaults in sql server 2000? I cant figure it out|||What exactly do you mean? How to insert default values or How to set it up?
Setup.
For example Open EM, Create a table two columns
Column 1 name it Log_Date datetime with a default of getdate()
Column 2 name it test_data varchar(20)
Open the table and insert something on the test_data column click on the
exclamation point to execute
or simply use query analyzer
BEGIN TRANSACTION
CREATE TABLE dbo.test0001
(
log_date datetime NULL,
test_data varchar(20) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.TEST001 ADD CONSTRAINT
DF_Table1_log_date DEFAULT getdate() FOR log_date
GO
COMMIT
INSERT INTO TEST0001(TEST_DATA)
SELECT 'TEST1'
SELECT * FROM TEST0001
If you create a new default on a column to an existing table with data then
you need to do something like
insert into test0001 default values
select * from test0001
Hope this helps
Yovan Fernandez
"bill" <bkelleman@.pcdata1.com> wrote in message
news:02a901c377be$aada4a30$a101280a@.phx.gbl...
> Can someone give me a simple example of how you use the
> defaults in sql server 2000? I cant figure it out
Defaults
1) Defaults are applied before transactions.
Defaults incur more overhead than constraints but less than triggers.
Defaults are less functional than constraints or triggers.
2) Defaults are applied after transactions.
Defaults incur more overhead than constraints but less than triggers.
Defaults are less functional than constraints or triggers.
3) Defaults are applied before transactions.
Defaults incur more overhead than constraints but less than triggers.
Defaults are more functional than constraints but less than triggers.
4) Defaults are applied after transactions.
Defaults incur less overhead than constraints or triggers.
Defaults are more functional than constraints but less than triggers.
5)5)Defaults are applied before transactions.
Defaults can only be applied to one column per row.
Defaults are less functional than constraints or triggers.
I go with option number 4. All are agree with me ?
Thanks
JOHN
No - but the statements are poorly worded. Comparing the functionality of
defaults and constraints is inappropriate (even though MS has lumped them
together - a default is a type of constraint) since they have different
purposes. IMO, a default is less "functional" than a constraint.
Certainly, both are less functional than triggers. The terminology "before
transactions" and "after transactions" is meaningless. If I had to guess, I
would choose "before transactions" simply because the default is applied
before the insert statement completes.
I would choose 5 but the middle statement can be interpreted in different
ways. The part about "per row" is especially confusing (perhaps
intentionally) - defaults (and constraints and triggers) are never defined
at the row-level. A default is associated with a single column, but each
column can have a default.
"John" <naissani@.hotmail.com> wrote in message
news:OU4$buBIFHA.608@.TK2MSFTNGP10.phx.gbl...
> 1) Defaults are applied before transactions.
> Defaults incur more overhead than constraints but less than
triggers.
> Defaults are less functional than constraints or triggers.
> 2) Defaults are applied after transactions.
> Defaults incur more overhead than constraints but less than
triggers.
> Defaults are less functional than constraints or triggers.
> 3) Defaults are applied before transactions.
> Defaults incur more overhead than constraints but less than
triggers.
> Defaults are more functional than constraints but less than
triggers.
> 4) Defaults are applied after transactions.
> Defaults incur less overhead than constraints or triggers.
> Defaults are more functional than constraints but less than
triggers.
> 5)5)Defaults are applied before transactions.
> Defaults can only be applied to one column per row.
> Defaults are less functional than constraints or triggers.
>
>
> I go with option number 4. All are agree with me ?
>
> Thanks
> JOHN
>
>
|||On Thu, 3 Mar 2005 13:05:30 -0500, John wrote:
> I go with option number 4. All are agree with me ?
Hi John,
I don't.
Defaults are applied, neither before or after transactions, but as part
of a transaction. Anything that is applied before or after a transaction
would not be affected by a rollback of that transaction. If I ever see a
case where the insert statement is rolled back but the default values
used in that statement persist in the database, I'll be on the phone
yelling "BUG" in MSPSS' ears before you can say "Rollback".
Defaults incur less overhead than triggers. I have never compared the
overhead of defaults to that of constraints, since this would be a
meaningless comparison. I can't replace either one with the other, they
yield different functionality. It would be about as meaningless as
comparing the costs of your car with that of your microwave oven.
You could call defaults less functional than triggers, but in the same
sense as calling a chair less functional than a pile of wood, a supply
of nails, a saw, and a hammer. You can make anything you want from the
wood, including a chair, but it takes much work. If you just want to sit
down for a while, using the chair is by far the better option.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Defaults incur more overhead than constraints but less than triggers.
Defaults are less functional than constraints or triggers.
2) Defaults are applied after transactions.
Defaults incur more overhead than constraints but less than triggers.
Defaults are less functional than constraints or triggers.
3) Defaults are applied before transactions.
Defaults incur more overhead than constraints but less than triggers.
Defaults are more functional than constraints but less than triggers.
4) Defaults are applied after transactions.
Defaults incur less overhead than constraints or triggers.
Defaults are more functional than constraints but less than triggers.
5)5)Defaults are applied before transactions.
Defaults can only be applied to one column per row.
Defaults are less functional than constraints or triggers.
I go with option number 4. All are agree with me ?
Thanks
JOHN
No - but the statements are poorly worded. Comparing the functionality of
defaults and constraints is inappropriate (even though MS has lumped them
together - a default is a type of constraint) since they have different
purposes. IMO, a default is less "functional" than a constraint.
Certainly, both are less functional than triggers. The terminology "before
transactions" and "after transactions" is meaningless. If I had to guess, I
would choose "before transactions" simply because the default is applied
before the insert statement completes.
I would choose 5 but the middle statement can be interpreted in different
ways. The part about "per row" is especially confusing (perhaps
intentionally) - defaults (and constraints and triggers) are never defined
at the row-level. A default is associated with a single column, but each
column can have a default.
"John" <naissani@.hotmail.com> wrote in message
news:OU4$buBIFHA.608@.TK2MSFTNGP10.phx.gbl...
> 1) Defaults are applied before transactions.
> Defaults incur more overhead than constraints but less than
triggers.
> Defaults are less functional than constraints or triggers.
> 2) Defaults are applied after transactions.
> Defaults incur more overhead than constraints but less than
triggers.
> Defaults are less functional than constraints or triggers.
> 3) Defaults are applied before transactions.
> Defaults incur more overhead than constraints but less than
triggers.
> Defaults are more functional than constraints but less than
triggers.
> 4) Defaults are applied after transactions.
> Defaults incur less overhead than constraints or triggers.
> Defaults are more functional than constraints but less than
triggers.
> 5)5)Defaults are applied before transactions.
> Defaults can only be applied to one column per row.
> Defaults are less functional than constraints or triggers.
>
>
> I go with option number 4. All are agree with me ?
>
> Thanks
> JOHN
>
>
|||On Thu, 3 Mar 2005 13:05:30 -0500, John wrote:
> I go with option number 4. All are agree with me ?
Hi John,
I don't.
Defaults are applied, neither before or after transactions, but as part
of a transaction. Anything that is applied before or after a transaction
would not be affected by a rollback of that transaction. If I ever see a
case where the insert statement is rolled back but the default values
used in that statement persist in the database, I'll be on the phone
yelling "BUG" in MSPSS' ears before you can say "Rollback".
Defaults incur less overhead than triggers. I have never compared the
overhead of defaults to that of constraints, since this would be a
meaningless comparison. I can't replace either one with the other, they
yield different functionality. It would be about as meaningless as
comparing the costs of your car with that of your microwave oven.
You could call defaults less functional than triggers, but in the same
sense as calling a chair less functional than a pile of wood, a supply
of nails, a saw, and a hammer. You can make anything you want from the
wood, including a chair, but it takes much work. If you just want to sit
down for a while, using the chair is by far the better option.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
DefaultMember Slows Down Time Intelligence
I searched for this and found no web hits so I thought I'd post a warning here.
As of SP2, the function Hierarchy.DefaultMember is still not optimized very well in MDX expressions and queries. By replacing it with the literal member equivalent from the hierarchy (eg. Hierarchy.[Current Period]), I've been able to improve performance of my time intelligence queries by a scale of magnitude or more.
As a fair warning about the output from the time intelligence wizard, you need to fix the generated calculations in the following ways:
Get rid of DefaultMember references
Replace * operator for the CrossJoin() function
Hope these hints save others as much time as I've wasted discovering them. Please let me know if anybody else has general hints to improve the performance of time intelligence calculations.
Chris mentioned your post and gave some more guidelines at:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1076.entry
DefaultMember Slows Down Time Intelligence
I searched for this and found no web hits so I thought I'd post a warning here.
As of SP2, the function Hierarchy.DefaultMember is still not optimized very well in MDX expressions and queries. By replacing it with the literal member equivalent from the hierarchy (eg. Hierarchy.[Current Period]), I've been able to improve performance of my time intelligence queries by a scale of magnitude or more.
As a fair warning about the output from the time intelligence wizard, you need to fix the generated calculations in the following ways:
Get rid of DefaultMember references
Replace * operator for the CrossJoin() function
Hope these hints save others as much time as I've wasted discovering them. Please let me know if anybody else has general hints to improve the performance of time intelligence calculations.
Chris mentioned your post and gave some more guidelines at:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1076.entry
DefaultMember of Time Dimension (Analysis Services 2005)
I have a very small time dimension. This time dimension has only Year, Quarter, and Month (plus DateID which is just an identity column). I have one hierarchy for Y-Q-M. The DateID attribute is hidden.
I have the default member of the Date ID attribute set to:
Tail(NonEmptyCrossjoin([Date].[Date ID].Members, 1), 1).Item(0).Item(0)
This works if I do not choose any dates, meaning if my last date is December of 2006, I see the values for December 2006 by default. However, if I add the Month attribute into the browser as a row or a column, I only see October, November, and December (though every month has data)
If I remove the DefaultMember property, I can see all months data when I add the months to the columns or rows, but the data doesn't default to the month (without manually selecting a date) like I want it to. I tried removing the Y-Q-M hierarchy and just making a Y-M hierarchy, but still the same results.
I swear this was working once upon a time (maybe before the latest SP install?) ... I cannot recall exactly.
has anyone else experienced this? am I missing something?
Thanks,
Jason
Hi jsaido,
The behaviour of your solution is right, but unfortunatly is not right for you.
It's named autoexist.
Why you don't use Named sets?
For example:
CREATE SET CURRENTCUBE.[Current Date]
AS Tail(NonEmptyCrossJoin([Date].[Date ID].[Date ID].Members, 1), 1);
CREATE SET CURRENTCUBE.[Current Montht]
AS EXISTS([Date].[Month].[Month].members, [Current Date]);
CREATE SET CURRENTCUBE.[Current Quarter]
AS EXISTS([Date].[Quarter].[Quarter].members, [Current Date]);
CREATE SET CURRENTCUBE.[Current Year]
AS EXISTS([Date].[Year].[Year].members, [Current Date]);
|||I did try named sets - my delivery vehicle for this cube is Excel 2003 pivot tables which do not support named sets.DefaultMember of Time Dimension (Analysis Services 2005)
I have a very small time dimension. This time dimension has only Year, Quarter, and Month (plus DateID which is just an identity column). I have one hierarchy for Y-Q-M. The DateID attribute is hidden.
I have the default member of the Date ID attribute set to:
Tail(NonEmptyCrossjoin([Date].[Date ID].Members, 1), 1).Item(0).Item(0)
This works if I do not choose any dates, meaning if my last date is December of 2006, I see the values for December 2006 by default. However, if I add the Month attribute into the browser as a row or a column, I only see October, November, and December (though every month has data)
If I remove the DefaultMember property, I can see all months data when I add the months to the columns or rows, but the data doesn't default to the month (without manually selecting a date) like I want it to. I tried removing the Y-Q-M hierarchy and just making a Y-M hierarchy, but still the same results.
I swear this was working once upon a time (maybe before the latest SP install?) ... I cannot recall exactly.
has anyone else experienced this? am I missing something?
Thanks,
Jason
Hi jsaido,
The behaviour of your solution is right, but unfortunatly is not right for you.
It's named autoexist.
Why you don't use Named sets?
For example:
CREATE SET CURRENTCUBE.[Current Date]
AS Tail(NonEmptyCrossJoin([Date].[Date ID].[Date ID].Members, 1), 1);
CREATE SET CURRENTCUBE.[Current Montht]
AS EXISTS([Date].[Month].[Month].members, [Current Date]);
CREATE SET CURRENTCUBE.[Current Quarter]
AS EXISTS([Date].[Quarter].[Quarter].members, [Current Date]);
CREATE SET CURRENTCUBE.[Current Year]
AS EXISTS([Date].[Year].[Year].members, [Current Date]);
|||I did try named sets - my delivery vehicle for this cube is Excel 2003 pivot tables which do not support named sets.Defaulting the User's ID in a column
Hello,
I'm new to SQL Server and I'm wondering how I can default the User's Id in to a column when they create/modify a record.
Better yet, is there a way to get a list of allowable functions for the Default Parm?
thx so much.
Use the system_user.
ALTER TABLE MyTable
ADD ChangeUser varchar(100) DEFAULT system_user
Most of the system functions are available as DEFAULTS. Check Books Online.
|||if you want to know the database username then system_user will not be the one. sometimes, you can have many database user names mapped to single login. try this and understand.
drop table TestuserDefault
create table TestuserDefault (userid int)
ALTER TABLE TestuserDefault
ADD systemuser varchar(100) DEFAULT system_user
ALTER TABLE TestuserDefault
ADD dbuser varchar(100) DEFAULT current_user
ALTER TABLE TestuserDefault
ADD dbuser1 varchar(100) DEFAULT user
ALTER TABLE TestuserDefault
ADD dbuser2 varchar(100) DEFAULT user_name()
insert into TestuserDefault(userid) select 1
select *from TestuserDefault
Madhu
|||
And of course, if you have multiple users mapped to the same login, there is no way that SQL Server can provide you the User identification information -you will have to do that in the application code, that is, if your application even tracks UserID information.
However, in those situations where IntegratedSecurity is being used, SYSTEM_USER will provide the correct UserName.
Defaulting the export option
Is there a way to control the contents of the report export drop down
list? Specifically, I would like to set a default export to PDF
(instead of the "Select a format" entry)You can force the rendering format for that report but I don't think there is
a way to default the drop-down selection.
Here is a PDF render format against the AdventureWorks sample reports (the
report gets generated in PDF):
//localhost/ReportServer?/AdventureWorks Sample Reports/Company
Sales&rs:Format=PDF&rs:Command=Render
"Paul" wrote:
> Is there a way to control the contents of the report export drop down
> list? Specifically, I would like to set a default export to PDF
> (instead of the "Select a format" entry)
list? Specifically, I would like to set a default export to PDF
(instead of the "Select a format" entry)You can force the rendering format for that report but I don't think there is
a way to default the drop-down selection.
Here is a PDF render format against the AdventureWorks sample reports (the
report gets generated in PDF):
//localhost/ReportServer?/AdventureWorks Sample Reports/Company
Sales&rs:Format=PDF&rs:Command=Render
"Paul" wrote:
> Is there a way to control the contents of the report export drop down
> list? Specifically, I would like to set a default export to PDF
> (instead of the "Select a format" entry)
Defaulting Start and End dates to the previous month.
I have two parameters in my report (StartDate and EndDate). I want to default these parameters to the previous month.
For example... If today is 5/17/2007, I want StartDate to be 4/1/2007 and EndDate to be 4/30/2007. If today would be January 30th 2007, I would want StartDate to be 12/1/2006 and EndDate to be 12/31/2006.
How can I do this?
Nevermind... I used the following two functions. However, if you know of a better way to do this, please let me know.
Public Function EndDt() As Date
Dim Today As Date = Date.Today
Dim Day As Integer = Today.Day
Dim EndDate As Date
EndDate = Today.AddMonths(1)
EndDate = Today.AddDays(-Day)
Return EndDate
End Function
Public Function StartDt() As Date
Dim Today As Date = Date.Today
Dim Day As Integer = Today.Day - 1
Dim StartDate As Date
StartDate = Today.AddMonths(-1)
StartDate = Today.AddDays(-Day)
Return StartDate
End Function
|||I can't think of a quick direct way
but I do want to suggest using BETWEEN, and first of month (so that you don't have to worry about # of days in a month)
e.g. for 05/17/2007
you'd want SQL code to be
Code Snippet
column BETWEEN 04/01/2007 AND 05/01/2007since BETWEEN is inclusive, but only up to '05/01/2007 00:00:00', anything after 05/01/2007 midnight will NOT show up
so in backend SQL
it'll be like
Code Snippet
SELECT
previous_month = CAST( MONTH(getdate())-1 AS varchar) + '/01/' + CAST(YEAR(getdate()) AS varchar)
, current_month = CAST( MONTH(getdate()) AS varchar) + '/01/' + CAST(YEAR(getdate()) AS varchar)
then you get
previous_month current_month
4/01/2007 5/01/2007
Defaulting Parameters based on time of day?
I need to set up some parameters which are based on the time of day. For instance, night shift vs/ day shift. If I run the report during the day, default to the day shift criteria (5 am to 5 pm), and likewise for the night shift (5 pm to 5 am). On the night shift, I also need to accomodate for the change in dates as well. Is this even possible in a single report? HELP!!
Thanks!
Does it really need to be parameters, or could you just check the time in your SQL or report code?defaulting newly created objects to DBO
Hi, i know that for non-sysadmin role members, non-qualified objects
will be owned by the creating user.
Is it possible to change this so that objects created by users
belonging to database role 'db_owner' default to dbo?
Thanks,
RafetJust have the CREATE statement use dbo as the schema.
create table dbo.test
Randy Dyess
www.Database-Security.Info|||Not really. You could use sp_addalias but it's not
recommended to go this route and sp_addalias is provided for
backwards compatibility only.
Members of db_owner should qualify the objects they are
creating with dbo. It's considered good practice to always
qualify objects with the owner name - when creating or
referencing objects. Qualifying objects improves performance
and readability.
-Sue
On 5 Mar 2004 07:16:48 -0800, rducic@.hotmail.com (Rafet)
wrote:
>Hi, i know that for non-sysadmin role members, non-qualified objects
>will be owned by the creating user.
>Is it possible to change this so that objects created by users
>belonging to database role 'db_owner' default to dbo?
>Thanks,
>Rafet|||I am puzzled about this too. I am about to start using sp_addalias though.
I think Microsoft needs to think this area through a little better. I know
sp_addalias may go away some day, but the current version doesn't handle thi
s well.
In test, we like to give developers logins that link to the dbo user so that
we can have the system enforce all objects getting created by dbo. Then wh
en we create these in production we can be comfortable that the user is the
same and any code that refe
rences the user will always be "dbo" and not "joe_developer".
While it is best practice for developers to always reference user name with
objects, in practice it is harder to enforce without the system to do it for
us. Our company employs consultants regularly and they all seem to have di
fferent habits we end up ha
ving to work on with them.
will be owned by the creating user.
Is it possible to change this so that objects created by users
belonging to database role 'db_owner' default to dbo?
Thanks,
RafetJust have the CREATE statement use dbo as the schema.
create table dbo.test
Randy Dyess
www.Database-Security.Info|||Not really. You could use sp_addalias but it's not
recommended to go this route and sp_addalias is provided for
backwards compatibility only.
Members of db_owner should qualify the objects they are
creating with dbo. It's considered good practice to always
qualify objects with the owner name - when creating or
referencing objects. Qualifying objects improves performance
and readability.
-Sue
On 5 Mar 2004 07:16:48 -0800, rducic@.hotmail.com (Rafet)
wrote:
>Hi, i know that for non-sysadmin role members, non-qualified objects
>will be owned by the creating user.
>Is it possible to change this so that objects created by users
>belonging to database role 'db_owner' default to dbo?
>Thanks,
>Rafet|||I am puzzled about this too. I am about to start using sp_addalias though.
I think Microsoft needs to think this area through a little better. I know
sp_addalias may go away some day, but the current version doesn't handle thi
s well.
In test, we like to give developers logins that link to the dbo user so that
we can have the system enforce all objects getting created by dbo. Then wh
en we create these in production we can be comfortable that the user is the
same and any code that refe
rences the user will always be "dbo" and not "joe_developer".
While it is best practice for developers to always reference user name with
objects, in practice it is harder to enforce without the system to do it for
us. Our company employs consultants regularly and they all seem to have di
fferent habits we end up ha
ving to work on with them.
Defaulting Date Parameter
Hello,
I have a report parameter StartDate. Properties are
DataType
ataTime
Prompt: StartDate
Default Values:
Non Queried : =NOW()
I set the default value to Now(). When I go to preview, the StartDate parameter is blank and its been locked & grayed out. I also tried
Today() and Globals!SystemTime but that does not work either. Is there any other solution to make this work?
Thanks
Raj
I have not seen any responses on this. Wanted to see if there are any ideas/ thoughts on this.|||Try:
Code Snippet
=cDate(FormatDateTime(Now, DateFormat.ShortDate))
Larry|||HI,You can give the following expression for the default value of startdate;
DateValue(now()).
Cheers,
Shri|||
Is this the only parameter in your report? If it is, then you shouldn't be having any problems.
If not, try entering the values for all the parameters that come before StartDate. Then you should be able to see the default value & it should not be greyed out any more.
-Aayush
DefaultCodePage issue from Db2 source
Hi everyone,
We're stuck with this. We've got a Db2 source connection and when we try 'Preview' button appears the following error:
TITLE: Microsoft Visual Studio
The component reported the following warnings:
Warning at {420E9420-175B-4C2F-856A-EA5D51C23627} [OLE DB Source [1]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Choose OK if you want to continue with the operation.
Choose Cancel if you want to stop the operation.
Does anyone have ever used DB2 driver from a dtsx package? If so, let me know if you had any related problem with.
Another day it happens with Oracle.
Thanks in advance for your time,
The DB2 OLE DB provider does expose code page information, so it is warning you of this fact. It also suggests a workaround/fix. The OLE-DB Source has a AlwaysUseDefaultCodePage property, it is exposed in the property grid, not the custom UI. Set this to true. You could also just ignore the error, it will work, even in the preview window.
The same applies to Oracle, again they have not implemented code page support in the provider, same solution.
|||DarrenSQLIS wrote:
The DB2 OLE DB provider does expose code page information, so it is warning you of this fact. It also suggests a workaround/fix. The OLE-DB Source has a AlwaysUseDefaultCodePage property, it is exposed in the property grid, not the custom UI. Set this to true. You could also just ignore the error, it will work, even in the preview window.
The same applies to Oracle, again they have not implemented code page support in the provider, same solution.
Follow Darren's advice. Set the "AlwaysUseDefaultCodePage" to true and your warning will go away.|||
hi guys, not tested yet. That's for a colleague of mine.
thanks
defaultcodepage is set to false
when ever i drop a ole db source or destination control on data flow, upon clicking to edit it complains about -- defaultcodepage is set to false. When i check in the properties and set it to true; i get no error. What is it all about?
kushpaw
codepage has to do with whether your columns in the table have data that are in other languages.... seting it to true will ask SSIS to take the defualt translation that it thinks fits your data.
Default_Member
How can I make a default_member dynamic.
Somethin like this:
ALTERCUBECURRENTCUBE
UPDATEDIMENSION [Time],
DEFAULT_MEMBER=[Time].[By Year].[Year].&[ + datepart("YYYY",now()) + ];
Thanks in advance
Using a Default Member other than the ALL member may introduce confusion in your results. Still, if this is required, I wouldn't try to determine it dynamically for something like a year which will change relatively infrequently.
You can use AMO to change a dimension's default member programattically. Before you process your dimension, calculate the desired default member, compare it to the current default member, and then change it if necessary. I'm not certain if in doing this you invalidate the dimension's data. If so, you would need to reprocess the dimension and any relating measure groups.
Good luck,
Bryan
Default Web Site using wrong IP
I just installed sql server 2005 on a windows 2003 server. I chose the option to install but do not configure. After restarting, I used the Reporting SErvices Configuration Manager and it all looks ok. When I browse to //localhost/reports I get a rs page with the message of:
The request failed with HTTP status 400: Bad Request.
One slightly unusual thing is SQL Server 2005 was installed as a named instance ("(local)\SQL2K5") - the server's default instance is a sql 2000 installation, which had RS installed and running. Before configuring RS for 2k5, I deleted the VDs for Reports and ReportServer from IIS, so that I could recreate them to use 2K5.
The new ReportServer VD appears to work (although no reports are installed to test).
Thanks for any help getting this issue resolved!
I have a bunch of these in Application Log... I have a feeling it's related.. I don't know what it means though
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
Anyone?
I'm not sure the applog error that you're catching is related, so let's try to fix it and then see if the original problem goes away (I suspect it won't):
1. Click Start | Run and run dcomcnfg.exe
2. In MMC, open Component Services | My Computer
3. Right-click My Computer, choose Properties, select the COM Security tab. Click on the edit default button in Launch permissions options area
4. Add network service and give it appropriate perms (Local Launch and Local Activation permissions)
If that doesn't do the trick:
1. Start DCOMCNFG
2. Under Component Services -> My Computer -> Dcom Config -> Netman click
Properties, then Security tab
3. In Launch and Activation Permissions box click Edit.
4. Add Network Service with Local Launch and Local Activation permissions
Good luck!
|||Nope... you were right - neither worked.I have since created 2 new VDs - Reports2K5 and ReportServer2k5, which correspond to my instance of SQL SErver 2005. The REportServer2K5 appears to be working OK... Reports2K5 (manager) still gives me the 400 error.
Would this error be logged anywhere?
Thanks for your help.|||
The site (Default web site) was using a specific IP address.. I changed it to All Unassigned and it works!
|||Good catch!|||Is there a way to configure SQL 2005 Reporting Services using an IIS Website with a specific IP?|||
I'm getting the same event error:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
I went into Component Services and Netman has local launch permissions and can find no reference for "Network Connection Manager Class" (the name the ID translates to based upon registry lookup). What in Component Services should I be looking for and where? This is a brand new installation of 2003 Server Professional w/ Exchange 2003 and all the latest service packs/patches. Thanks,
John
|||Had very similar on 4 servers, all using unassigned , but they all had host headers specified. Short summary below to explain.
Each server had three web sites all bound to unassigned ip address, using host headers and diffrent ssl ports to separate ou the traffic.
Report server had previously been working ok until the final site config when the host headers were implemented.
As one site could act as a default then the problem was resolved by adding an additional binding for port 80 with no header specified.
All boxes running fine now.
The origonal posting pointed me in the right direction and as I was also implmenting http endpoints in SQL , I could have spent awhile on red herings. Much thanks.
|||I believe this errors can be fixed by the following 899965
http://support.microsoft.com/?kbid=899965
|||had a similar problem and found out the following:
If you want to run Reporting Services under a different port than normal, you have to change these two config files:
rsreportserver.config
and
RSWebApplication.config
Enter the full path (like http://servername:port/ReportServer) to <ReportServerUrl> (delete <ReportServerVirtualDirectory> then!) or resp. <UrlRoot>
The result is then
<Configuration>
<UI>
<ReportServerUrl>http://mbs05:8088/reportserver</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
.....
and
<Configuration>
... <Service>
....
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://mbs05:8088/reportserver</UrlRoot>
<UnattendedExecutionAccount>
...
Hope this helps,
Martin
PS: see also http://msdn2.microsoft.com/en-us/library/ms159261(SQL.90).aspx
I switched the host headers around (so the default website has host headers, and the RS website doesn't) now it works perfectly.
|||
Folks,
I'm trying to resolve the same error but having a bit of trouble following through the instructions in the KB.
If one of you could please set me right it would be much appreciated.
Firstly, the error I'm getting in the event log is as follows:
"The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool."
So I go to the regedit and look it up.
I search for BA126AD1-2166-11D1-B1D0-00805FC1270E
And Regedit duly comes up with:
HKEY_CLASSES_ROOT\CLSID\{BA126AD1-2166-11D1-B1D0-00805FC1270E}
Which identifies this as the "Network Connection Manager Class"
Allright..now the KB article then tells me to :
If a Windows Security Alert message prompts you to keep blocking the Microsoft Management Console program, click to unblock the program.
If the AppGUID identifier is listed instead of the friendly name, locate the program by using this identifier.
And this is where I'm stuck.. cos there is nothing there that corresponds to a Network Connection Manager Class or to the CLSID... (is that what they mean in the doc when they refere to AppGUID ?)
So, I can't continue on with the fix.. because I can't find the right app to fix the launch and activation permissions for....
I'd really appreciate a bit of help on this one...
Thanks
PJ
|||
Another question on this...just to be sure I'm chasing the right tail....
When I point a browser at the site http://localhost/Reportserver$DeptServer05 I get the following message...
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Object reference not set to an instance of an object.Default Web Site using wrong IP
I just installed sql server 2005 on a windows 2003 server. I chose the option to install but do not configure. After restarting, I used the Reporting SErvices Configuration Manager and it all looks ok. When I browse to //localhost/reports I get a rs page with the message of:
The request failed with HTTP status 400: Bad Request.
One slightly unusual thing is SQL Server 2005 was installed as a named instance ("(local)\SQL2K5") - the server's default instance is a sql 2000 installation, which had RS installed and running. Before configuring RS for 2k5, I deleted the VDs for Reports and ReportServer from IIS, so that I could recreate them to use 2K5.
The new ReportServer VD appears to work (although no reports are installed to test).
Thanks for any help getting this issue resolved!
I have a bunch of these in Application Log... I have a feeling it's related.. I don't know what it means though
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
Anyone?
I'm not sure the applog error that you're catching is related, so let's try to fix it and then see if the original problem goes away (I suspect it won't):
1. Click Start | Run and run dcomcnfg.exe
2. In MMC, open Component Services | My Computer
3. Right-click My Computer, choose Properties, select the COM Security tab. Click on the edit default button in Launch permissions options area
4. Add network service and give it appropriate perms (Local Launch and Local Activation permissions)
If that doesn't do the trick:
1. Start DCOMCNFG
2. Under Component Services -> My Computer -> Dcom Config -> Netman click
Properties, then Security tab
3. In Launch and Activation Permissions box click Edit.
4. Add Network Service with Local Launch and Local Activation permissions
Good luck!|||Nope... you were right - neither worked.
I have since created 2 new VDs - Reports2K5 and ReportServer2k5, which correspond to my instance of SQL SErver 2005. The REportServer2K5 appears to be working OK... Reports2K5 (manager) still gives me the 400 error.
Would this error be logged anywhere?
Thanks for your help.||| The site (Default web site) was using a specific IP address.. I changed it to All Unassigned and it works!
|||Good catch!|||Is there a way to configure SQL 2005 Reporting Services using an IIS Website with a specific IP?|||
I'm getting the same event error:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
I went into Component Services and Netman has local launch permissions and can find no reference for "Network Connection Manager Class" (the name the ID translates to based upon registry lookup). What in Component Services should I be looking for and where? This is a brand new installation of 2003 Server Professional w/ Exchange 2003 and all the latest service packs/patches. Thanks,
John
|||Had very similar on 4 servers, all using unassigned , but they all had host headers specified. Short summary below to explain.
Each server had three web sites all bound to unassigned ip address, using host headers and diffrent ssl ports to separate ou the traffic.
Report server had previously been working ok until the final site config when the host headers were implemented.
As one site could act as a default then the problem was resolved by adding an additional binding for port 80 with no header specified.
All boxes running fine now.
The origonal posting pointed me in the right direction and as I was also implmenting http endpoints in SQL , I could have spent awhile on red herings. Much thanks.
|||I believe this errors can be fixed by the following 899965
http://support.microsoft.com/?kbid=899965
|||had a similar problem and found out the following:
If you want to run Reporting Services under a different port than normal, you have to change these two config files:
rsreportserver.config
and
RSWebApplication.config
Enter the full path (like http://servername:port/ReportServer) to <ReportServerUrl> (delete <ReportServerVirtualDirectory> then!) or resp. <UrlRoot>
The result is then
<Configuration>
<UI>
<ReportServerUrl>http://mbs05:8088/reportserver</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
.....
and
<Configuration>
... <Service>
....
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://mbs05:8088/reportserver</UrlRoot>
<UnattendedExecutionAccount>
...
Hope this helps,
Martin
PS: see also http://msdn2.microsoft.com/en-us/library/ms159261(SQL.90).aspx
I switched the host headers around (so the default website has host headers, and the RS website doesn't) now it works perfectly.
|||
Folks,
I'm trying to resolve the same error but having a bit of trouble following through the instructions in the KB.
If one of you could please set me right it would be much appreciated.
Firstly, the error I'm getting in the event log is as follows:
"The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool."
So I go to the regedit and look it up.
I search for BA126AD1-2166-11D1-B1D0-00805FC1270E
And Regedit duly comes up with:
HKEY_CLASSES_ROOT\CLSID\{BA126AD1-2166-11D1-B1D0-00805FC1270E}
Which identifies this as the "Network Connection Manager Class"
Allright..now the KB article then tells me to :
If a Windows Security Alert message prompts you to keep blocking the Microsoft Management Console program, click to unblock the program.
If the AppGUID identifier is listed instead of the friendly name, locate the program by using this identifier.
And this is where I'm stuck.. cos there is nothing there that corresponds to a Network Connection Manager Class or to the CLSID... (is that what they mean in the doc when they refere to AppGUID ?)
So, I can't continue on with the fix.. because I can't find the right app to fix the launch and activation permissions for....
I'd really appreciate a bit of help on this one...
Thanks
PJ
|||
Another question on this...just to be sure I'm chasing the right tail....
When I point a browser at the site http://localhost/Reportserver$DeptServer05 I get the following message...
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Object reference not set to an instance of an object.Default Web Site using wrong IP
I just installed sql server 2005 on a windows 2003 server. I chose the option to install but do not configure. After restarting, I used the Reporting SErvices Configuration Manager and it all looks ok. When I browse to //localhost/reports I get a rs page with the message of:
The request failed with HTTP status 400: Bad Request.
One slightly unusual thing is SQL Server 2005 was installed as a named instance ("(local)\SQL2K5") - the server's default instance is a sql 2000 installation, which had RS installed and running. Before configuring RS for 2k5, I deleted the VDs for Reports and ReportServer from IIS, so that I could recreate them to use 2K5.
The new ReportServer VD appears to work (although no reports are installed to test).
Thanks for any help getting this issue resolved!
I have a bunch of these in Application Log... I have a feeling it's related.. I don't know what it means though
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
Anyone?
I'm not sure the applog error that you're catching is related, so let's try to fix it and then see if the original problem goes away (I suspect it won't):
1. Click Start | Run and run dcomcnfg.exe
2. In MMC, open Component Services | My Computer
3. Right-click My Computer, choose Properties, select the COM Security tab. Click on the edit default button in Launch permissions options area
4. Add network service and give it appropriate perms (Local Launch and Local Activation permissions)
If that doesn't do the trick:
1. Start DCOMCNFG
2. Under Component Services -> My Computer -> Dcom Config -> Netman click
Properties, then Security tab
3. In Launch and Activation Permissions box click Edit.
4. Add Network Service with Local Launch and Local Activation permissions
Good luck!
|||Nope... you were right - neither worked.I have since created 2 new VDs - Reports2K5 and ReportServer2k5, which correspond to my instance of SQL SErver 2005. The REportServer2K5 appears to be working OK... Reports2K5 (manager) still gives me the 400 error.
Would this error be logged anywhere?
Thanks for your help.||| The site (Default web site) was using a specific IP address.. I changed it to All Unassigned and it works!|||Good catch!|||Is there a way to configure SQL 2005 Reporting Services using an IIS Website with a specific IP?|||
I'm getting the same event error:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
I went into Component Services and Netman has local launch permissions and can find no reference for "Network Connection Manager Class" (the name the ID translates to based upon registry lookup). What in Component Services should I be looking for and where? This is a brand new installation of 2003 Server Professional w/ Exchange 2003 and all the latest service packs/patches. Thanks,
John
|||Had very similar on 4 servers, all using unassigned , but they all had host headers specified. Short summary below to explain.
Each server had three web sites all bound to unassigned ip address, using host headers and diffrent ssl ports to separate ou the traffic.
Report server had previously been working ok until the final site config when the host headers were implemented.
As one site could act as a default then the problem was resolved by adding an additional binding for port 80 with no header specified.
All boxes running fine now.
The origonal posting pointed me in the right direction and as I was also implmenting http endpoints in SQL , I could have spent awhile on red herings. Much thanks.
|||I believe this errors can be fixed by the following 899965
http://support.microsoft.com/?kbid=899965
|||had a similar problem and found out the following:
If you want to run Reporting Services under a different port than normal, you have to change these two config files:
rsreportserver.config
and
RSWebApplication.config
Enter the full path (like http://servername:port/ReportServer) to <ReportServerUrl> (delete <ReportServerVirtualDirectory> then!) or resp. <UrlRoot>
The result is then
<Configuration>
<UI>
<ReportServerUrl>http://mbs05:8088/reportserver</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
.....
and
<Configuration>
... <Service>
....
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://mbs05:8088/reportserver</UrlRoot>
<UnattendedExecutionAccount>
...
Hope this helps,
Martin
PS: see also http://msdn2.microsoft.com/en-us/library/ms159261(SQL.90).aspx
I switched the host headers around (so the default website has host headers, and the RS website doesn't) now it works perfectly.|||
Folks,
I'm trying to resolve the same error but having a bit of trouble following through the instructions in the KB.
If one of you could please set me right it would be much appreciated.
Firstly, the error I'm getting in the event log is as follows:
"The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool."
So I go to the regedit and look it up.
I search for BA126AD1-2166-11D1-B1D0-00805FC1270E
And Regedit duly comes up with:
HKEY_CLASSES_ROOT\CLSID\{BA126AD1-2166-11D1-B1D0-00805FC1270E}
Which identifies this as the "Network Connection Manager Class"
Allright..now the KB article then tells me to :
If a Windows Security Alert message prompts you to keep blocking the Microsoft Management Console program, click to unblock the program.
If the AppGUID identifier is listed instead of the friendly name, locate the program by using this identifier.
And this is where I'm stuck.. cos there is nothing there that corresponds to a Network Connection Manager Class or to the CLSID... (is that what they mean in the doc when they refere to AppGUID ?)
So, I can't continue on with the fix.. because I can't find the right app to fix the launch and activation permissions for....
I'd really appreciate a bit of help on this one...
Thanks
PJ
|||
Another question on this...just to be sure I'm chasing the right tail....
When I point a browser at the site http://localhost/Reportserver$DeptServer05 I get the following message...
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Object reference not set to an instance of an object.
Default Web Site using wrong IP
I just installed sql server 2005 on a windows 2003 server. I chose the option to install but do not configure. After restarting, I used the Reporting SErvices Configuration Manager and it all looks ok. When I browse to //localhost/reports I get a rs page with the message of:
The request failed with HTTP status 400: Bad Request.
One slightly unusual thing is SQL Server 2005 was installed as a named instance ("(local)\SQL2K5") - the server's default instance is a sql 2000 installation, which had RS installed and running. Before configuring RS for 2k5, I deleted the VDs for Reports and ReportServer from IIS, so that I could recreate them to use 2K5.
The new ReportServer VD appears to work (although no reports are installed to test).
Thanks for any help getting this issue resolved!
I have a bunch of these in Application Log... I have a feeling it's related.. I don't know what it means though
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
Anyone?
I'm not sure the applog error that you're catching is related, so let's try to fix it and then see if the original problem goes away (I suspect it won't):
1. Click Start | Run and run dcomcnfg.exe
2. In MMC, open Component Services | My Computer
3. Right-click My Computer, choose Properties, select the COM Security tab. Click on the edit default button in Launch permissions options area
4. Add network service and give it appropriate perms (Local Launch and Local Activation permissions)
If that doesn't do the trick:
1. Start DCOMCNFG
2. Under Component Services -> My Computer -> Dcom Config -> Netman click
Properties, then Security tab
3. In Launch and Activation Permissions box click Edit.
4. Add Network Service with Local Launch and Local Activation permissions
Good luck!
|||Nope... you were right - neither worked.I have since created 2 new VDs - Reports2K5 and ReportServer2k5, which correspond to my instance of SQL SErver 2005. The REportServer2K5 appears to be working OK... Reports2K5 (manager) still gives me the 400 error.
Would this error be logged anywhere?
Thanks for your help.||| The site (Default web site) was using a specific IP address.. I changed it to All Unassigned and it works!
|||Good catch!|||Is there a way to configure SQL 2005 Reporting Services using an IIS Website with a specific IP?|||
I'm getting the same event error:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
I went into Component Services and Netman has local launch permissions and can find no reference for "Network Connection Manager Class" (the name the ID translates to based upon registry lookup). What in Component Services should I be looking for and where? This is a brand new installation of 2003 Server Professional w/ Exchange 2003 and all the latest service packs/patches. Thanks,
John
|||Had very similar on 4 servers, all using unassigned , but they all had host headers specified. Short summary below to explain.
Each server had three web sites all bound to unassigned ip address, using host headers and diffrent ssl ports to separate ou the traffic.
Report server had previously been working ok until the final site config when the host headers were implemented.
As one site could act as a default then the problem was resolved by adding an additional binding for port 80 with no header specified.
All boxes running fine now.
The origonal posting pointed me in the right direction and as I was also implmenting http endpoints in SQL , I could have spent awhile on red herings. Much thanks.
|||I believe this errors can be fixed by the following 899965
http://support.microsoft.com/?kbid=899965
|||had a similar problem and found out the following:
If you want to run Reporting Services under a different port than normal, you have to change these two config files:
rsreportserver.config
and
RSWebApplication.config
Enter the full path (like http://servername:port/ReportServer) to <ReportServerUrl> (delete <ReportServerVirtualDirectory> then!) or resp. <UrlRoot>
The result is then
<Configuration>
<UI>
<ReportServerUrl>http://mbs05:8088/reportserver</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
.....
and
<Configuration>
... <Service>
....
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://mbs05:8088/reportserver</UrlRoot>
<UnattendedExecutionAccount>
...
Hope this helps,
Martin
PS: see also http://msdn2.microsoft.com/en-us/library/ms159261(SQL.90).aspx
I switched the host headers around (so the default website has host headers, and the RS website doesn't) now it works perfectly.
|||
Folks,
I'm trying to resolve the same error but having a bit of trouble following through the instructions in the KB.
If one of you could please set me right it would be much appreciated.
Firstly, the error I'm getting in the event log is as follows:
"The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool."
So I go to the regedit and look it up.
I search for BA126AD1-2166-11D1-B1D0-00805FC1270E
And Regedit duly comes up with:
HKEY_CLASSES_ROOT\CLSID\{BA126AD1-2166-11D1-B1D0-00805FC1270E}
Which identifies this as the "Network Connection Manager Class"
Allright..now the KB article then tells me to :
If a Windows Security Alert message prompts you to keep blocking the Microsoft Management Console program, click to unblock the program.
If the AppGUID identifier is listed instead of the friendly name, locate the program by using this identifier.
And this is where I'm stuck.. cos there is nothing there that corresponds to a Network Connection Manager Class or to the CLSID... (is that what they mean in the doc when they refere to AppGUID ?)
So, I can't continue on with the fix.. because I can't find the right app to fix the launch and activation permissions for....
I'd really appreciate a bit of help on this one...
Thanks
PJ
|||
Another question on this...just to be sure I'm chasing the right tail....
When I point a browser at the site http://localhost/Reportserver$DeptServer05 I get the following message...
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Object reference not set to an instance of an object.Default Web Site using wrong IP
I just installed sql server 2005 on a windows 2003 server. I chose the option to install but do not configure. After restarting, I used the Reporting SErvices Configuration Manager and it all looks ok. When I browse to //localhost/reports I get a rs page with the message of:
The request failed with HTTP status 400: Bad Request.
One slightly unusual thing is SQL Server 2005 was installed as a named instance ("(local)\SQL2K5") - the server's default instance is a sql 2000 installation, which had RS installed and running. Before configuring RS for 2k5, I deleted the VDs for Reports and ReportServer from IIS, so that I could recreate them to use 2K5.
The new ReportServer VD appears to work (although no reports are installed to test).
Thanks for any help getting this issue resolved!
I have a bunch of these in Application Log... I have a feeling it's related.. I don't know what it means though
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
Anyone?
I'm not sure the applog error that you're catching is related, so let's try to fix it and then see if the original problem goes away (I suspect it won't):
1. Click Start | Run and run dcomcnfg.exe
2. In MMC, open Component Services | My Computer
3. Right-click My Computer, choose Properties, select the COM Security tab. Click on the edit default button in Launch permissions options area
4. Add network service and give it appropriate perms (Local Launch and Local Activation permissions)
If that doesn't do the trick:
1. Start DCOMCNFG
2. Under Component Services -> My Computer -> Dcom Config -> Netman click
Properties, then Security tab
3. In Launch and Activation Permissions box click Edit.
4. Add Network Service with Local Launch and Local Activation permissions
Good luck!
|||Nope... you were right - neither worked.I have since created 2 new VDs - Reports2K5 and ReportServer2k5, which correspond to my instance of SQL SErver 2005. The REportServer2K5 appears to be working OK... Reports2K5 (manager) still gives me the 400 error.
Would this error be logged anywhere?
Thanks for your help.||| The site (Default web site) was using a specific IP address.. I changed it to All Unassigned and it works!|||Good catch!|||Is there a way to configure SQL 2005 Reporting Services using an IIS Website with a specific IP?|||
I'm getting the same event error:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.
I went into Component Services and Netman has local launch permissions and can find no reference for "Network Connection Manager Class" (the name the ID translates to based upon registry lookup). What in Component Services should I be looking for and where? This is a brand new installation of 2003 Server Professional w/ Exchange 2003 and all the latest service packs/patches. Thanks,
John
|||Had very similar on 4 servers, all using unassigned , but they all had host headers specified. Short summary below to explain.
Each server had three web sites all bound to unassigned ip address, using host headers and diffrent ssl ports to separate ou the traffic.
Report server had previously been working ok until the final site config when the host headers were implemented.
As one site could act as a default then the problem was resolved by adding an additional binding for port 80 with no header specified.
All boxes running fine now.
The origonal posting pointed me in the right direction and as I was also implmenting http endpoints in SQL , I could have spent awhile on red herings. Much thanks.
|||I believe this errors can be fixed by the following 899965
http://support.microsoft.com/?kbid=899965
|||had a similar problem and found out the following:
If you want to run Reporting Services under a different port than normal, you have to change these two config files:
rsreportserver.config
and
RSWebApplication.config
Enter the full path (like http://servername:port/ReportServer) to <ReportServerUrl> (delete <ReportServerVirtualDirectory> then!) or resp. <UrlRoot>
The result is then
<Configuration>
<UI>
<ReportServerUrl>http://mbs05:8088/reportserver</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
.....
and
<Configuration>
... <Service>
....
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://mbs05:8088/reportserver</UrlRoot>
<UnattendedExecutionAccount>
...
Hope this helps,
Martin
PS: see also http://msdn2.microsoft.com/en-us/library/ms159261(SQL.90).aspx
I switched the host headers around (so the default website has host headers, and the RS website doesn't) now it works perfectly.|||
Folks,
I'm trying to resolve the same error but having a bit of trouble following through the instructions in the KB.
If one of you could please set me right it would be much appreciated.
Firstly, the error I'm getting in the event log is as follows:
"The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool."
So I go to the regedit and look it up.
I search for BA126AD1-2166-11D1-B1D0-00805FC1270E
And Regedit duly comes up with:
HKEY_CLASSES_ROOT\CLSID\{BA126AD1-2166-11D1-B1D0-00805FC1270E}
Which identifies this as the "Network Connection Manager Class"
Allright..now the KB article then tells me to :
If a Windows Security Alert message prompts you to keep blocking the Microsoft Management Console program, click to unblock the program.
If the AppGUID identifier is listed instead of the friendly name, locate the program by using this identifier.
And this is where I'm stuck.. cos there is nothing there that corresponds to a Network Connection Manager Class or to the CLSID... (is that what they mean in the doc when they refere to AppGUID ?)
So, I can't continue on with the fix.. because I can't find the right app to fix the launch and activation permissions for....
I'd really appreciate a bit of help on this one...
Thanks
PJ
|||
Another question on this...just to be sure I'm chasing the right tail....
When I point a browser at the site http://localhost/Reportserver$DeptServer05 I get the following message...
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Object reference not set to an instance of an object.
Subscribe to:
Posts (Atom)