bugs, Information_schema appeared as a user in master database

Hi, All
Recently, I was running a script in sql 2000 on window 2000 server like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipp ed'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
Yifei
Hi
You may want to try
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
'.INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipp ed'') = 0'
Although if that gives you thre right view you may get nothing back as the
OBJECT_ID function could cause problems, you may therefore want to resort to
using sysobjects.
John
"Yifei Jiang" wrote:

> Hi, All
> Recently, I was running a script in sql 2000 on window 2000 server like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipp ed'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via group
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the db
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the problem?
> any help is appreciated.
> Thanks
> Yifei
>
>
>
|||Hi, John,
Thank you for replay,
I think the issue relate to sql 2005, because from em on the server, the
information_shema does not show as user, from em installed sql2005,
information_shema shows as user. Same as another machine that installed sql
2005 and does not run the script.
The reason because I did not check the user until I run the script and have
to check it and find the problem.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...[vbcol=seagreen]
> Hi
> You may want to try
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> '.INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipp ed'') = 0'
> Although if that gives you thre right view you may get nothing back as the
> OBJECT_ID function could cause problems, you may therefore want to resort
> to
> using sysobjects.
> John
>
>
> "Yifei Jiang" wrote:
|||The original post has no mention of SQL 2005. If you use Management
Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
under system views in the master database. If you use Enterprise
manager to look at a SQL 2000 database there is no division between
system and user views. If you used a cursor instead of using the
undocumented xp_execresultset this should work.
John
Scott wrote:[vbcol=seagreen]
> Hi, John,
> Thank you for replay,
> I think the issue relate to sql 2005, because from em on the server, the
> information_shema does not show as user, from em installed sql2005,
> information_shema shows as user. Same as another machine that installed sql
> 2005 and does not run the script.
> The reason because I did not check the user until I run the script and have
> to check it and find the problem.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
|||Hi, John,
You are right, what I want to say that sql 2005 caused the information_shema
as a user in master db. it is nothing wrong with running script.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1134379113.719483.236400@.o13g2000cwo.googlegr oups.com...
> The original post has no mention of SQL 2005. If you use Management
> Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> under system views in the master database. If you use Enterprise
> manager to look at a SQL 2000 database there is no division between
> system and user views. If you used a cursor instead of using the
> undocumented xp_execresultset this should work.
> John
> Scott wrote:
>
|||Hi
In SQL 2005 you need to be more aware of the difference between a schema and
a user. I think Kalen wrote about this some time ago in SQL Server magazine,
but I can't put my hands on this, but you may want to read
http://msdn.microsoft.com/msdnmag/is...y/default.aspx
John
"Yifei" wrote:

> Hi, John,
> You are right, what I want to say that sql 2005 caused the information_shema
> as a user in master db. it is nothing wrong with running script.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1134379113.719483.236400@.o13g2000cwo.googlegr oups.com...
>
>

bugs, Information_schema appeared as a user in master database

Hi, All
Recently, I was running a script in sql 2000 on window 2000 server like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
YifeiHi
You may want to try
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
'.INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
Although if that gives you thre right view you may get nothing back as the
OBJECT_ID function could cause problems, you may therefore want to resort to
using sysobjects.
John
"Yifei Jiang" wrote:

> Hi, All
> Recently, I was running a script in sql 2000 on window 2000 server like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via gro
up
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the d
b
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the proble
m?
> any help is appreciated.
> Thanks
> Yifei
>
>
>|||Hi, John,
Thank you for replay,
I think the issue relate to sql 2005, because from em on the server, the
information_shema does not show as user, from em installed sql2005,
information_shema shows as user. Same as another machine that installed sql
2005 and does not run the script.
The reason because I did not check the user until I run the script and have
to check it and find the problem.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...[vbcol=seagreen]
> Hi
> You may want to try
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> '.INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
> Although if that gives you thre right view you may get nothing back as the
> OBJECT_ID function could cause problems, you may therefore want to resort
> to
> using sysobjects.
> John
>
>
> "Yifei Jiang" wrote:
>|||The original post has no mention of SQL 2005. If you use Management
Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
under system views in the master database. If you use Enterprise
manager to look at a SQL 2000 database there is no division between
system and user views. If you used a cursor instead of using the
undocumented xp_execresultset this should work.
John
Scott wrote:[vbcol=seagreen]
> Hi, John,
> Thank you for replay,
> I think the issue relate to sql 2005, because from em on the server, the
> information_shema does not show as user, from em installed sql2005,
> information_shema shows as user. Same as another machine that installed sq
l
> 2005 and does not run the script.
> The reason because I did not check the user until I run the script and hav
e
> to check it and find the problem.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...|||Hi, John,
You are right, what I want to say that sql 2005 caused the information_shema
as a user in master db. it is nothing wrong with running script.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
> The original post has no mention of SQL 2005. If you use Management
> Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> under system views in the master database. If you use Enterprise
> manager to look at a SQL 2000 database there is no division between
> system and user views. If you used a cursor instead of using the
> undocumented xp_execresultset this should work.
> John
> Scott wrote:
>|||Hi
In SQL 2005 you need to be more aware of the difference between a schema and
a user. I think Kalen wrote about this some time ago in SQL Server magazine,
but I can't put my hands on this, but you may want to read
[url]http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx[/url
]
John
"Yifei" wrote:

> Hi, John,
> You are right, what I want to say that sql 2005 caused the information_she
ma
> as a user in master db. it is nothing wrong with running script.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
>
>sql

bugs, Information_schema appeared as a user in master database

Hi, All
Recently, I was running a script in sql 2000 on window 2000 server like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
YifeiHi
You may want to try
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
'.INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
Although if that gives you thre right view you may get nothing back as the
OBJECT_ID function could cause problems, you may therefore want to resort to
using sysobjects.
John
"Yifei Jiang" wrote:
> Hi, All
> Recently, I was running a script in sql 2000 on window 2000 server like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via group
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the db
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the problem?
> any help is appreciated.
> Thanks
> Yifei
>
>
>|||Hi, John,
Thank you for replay,
I think the issue relate to sql 2005, because from em on the server, the
information_shema does not show as user, from em installed sql2005,
information_shema shows as user. Same as another machine that installed sql
2005 and does not run the script.
The reason because I did not check the user until I run the script and have
to check it and find the problem.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> Hi
> You may want to try
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> '.INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> Although if that gives you thre right view you may get nothing back as the
> OBJECT_ID function could cause problems, you may therefore want to resort
> to
> using sysobjects.
> John
>
>
> "Yifei Jiang" wrote:
>> Hi, All
>> Recently, I was running a script in sql 2000 on window 2000 server like:
>> declare @.sql nvarchar(4000)
>> declare @.db sysname ; set @.db = DB_NAME()
>> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
>> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.''
>> +
>> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
>> INFORMATION_SCHEMA.ROUTINES ' +
>> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> exec master.dbo.xp_execresultset @.sql,@.db
>> to grant stored procedures execution permition to role, after running the
>> script, the privilige is grant it to that role, but I got very strange
>> things happened on sql server.
>> 1. Master database get Information_Shema as a user. dbaccess "via group
>> membership"
>> 2.Master database got system_function_schema as a user , dbaccess "via
>> group
>> membership"
>> 3.guest user show in every database in the instance, dbaccess "via group
>> membership"
>> if I delete guest account, show me message "the user is not in
>> database", sp_helpuser did not show guest user, enterprice manager show
>> it
>> as a user.
>> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
>> delete it successful, however, a couple minutes later, it appears in the
>> db
>> as dbaccess via group membership.
>> it is really painful for me. could anyone give me a hint to fix the
>> problem?
>> any help is appreciated.
>> Thanks
>> Yifei
>>
>>
>>|||The original post has no mention of SQL 2005. If you use Management
Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
under system views in the master database. If you use Enterprise
manager to look at a SQL 2000 database there is no division between
system and user views. If you used a cursor instead of using the
undocumented xp_execresultset this should work.
John
Scott wrote:
> Hi, John,
> Thank you for replay,
> I think the issue relate to sql 2005, because from em on the server, the
> information_shema does not show as user, from em installed sql2005,
> information_shema shows as user. Same as another machine that installed sql
> 2005 and does not run the script.
> The reason because I did not check the user until I run the script and have
> to check it and find the problem.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> > Hi
> >
> > You may want to try
> >
> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> > '.INFORMATION_SCHEMA.ROUTINES ' +
> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >
> > Although if that gives you thre right view you may get nothing back as the
> > OBJECT_ID function could cause problems, you may therefore want to resort
> > to
> > using sysobjects.
> >
> > John
> >
> >
> >
> >
> > "Yifei Jiang" wrote:
> >
> >> Hi, All
> >>
> >> Recently, I was running a script in sql 2000 on window 2000 server like:
> >>
> >> declare @.sql nvarchar(4000)
> >> declare @.db sysname ; set @.db = DB_NAME()
> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> >>
> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.''
> >> +
> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> >> INFORMATION_SCHEMA.ROUTINES ' +
> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >>
> >> exec master.dbo.xp_execresultset @.sql,@.db
> >>
> >> to grant stored procedures execution permition to role, after running the
> >> script, the privilige is grant it to that role, but I got very strange
> >> things happened on sql server.
> >>
> >> 1. Master database get Information_Shema as a user. dbaccess "via group
> >> membership"
> >> 2.Master database got system_function_schema as a user , dbaccess "via
> >> group
> >> membership"
> >> 3.guest user show in every database in the instance, dbaccess "via group
> >> membership"
> >> if I delete guest account, show me message "the user is not in
> >> database", sp_helpuser did not show guest user, enterprice manager show
> >> it
> >> as a user.
> >> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> >> delete it successful, however, a couple minutes later, it appears in the
> >> db
> >> as dbaccess via group membership.
> >>
> >> it is really painful for me. could anyone give me a hint to fix the
> >> problem?
> >>
> >> any help is appreciated.
> >>
> >> Thanks
> >>
> >> Yifei
> >>
> >>
> >>
> >>
> >>
> >>|||Hi, John,
You are right, what I want to say that sql 2005 caused the information_shema
as a user in master db. it is nothing wrong with running script.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
> The original post has no mention of SQL 2005. If you use Management
> Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> under system views in the master database. If you use Enterprise
> manager to look at a SQL 2000 database there is no division between
> system and user views. If you used a cursor instead of using the
> undocumented xp_execresultset this should work.
> John
> Scott wrote:
>> Hi, John,
>> Thank you for replay,
>> I think the issue relate to sql 2005, because from em on the server, the
>> information_shema does not show as user, from em installed sql2005,
>> information_shema shows as user. Same as another machine that installed
>> sql
>> 2005 and does not run the script.
>> The reason because I did not check the user until I run the script and
>> have
>> to check it and find the problem.
>> Thanks
>> Yifei
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
>> > Hi
>> >
>> > You may want to try
>> >
>> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
>> > ''.'' +
>> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
>> > '.INFORMATION_SCHEMA.ROUTINES ' +
>> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> >
>> > Although if that gives you thre right view you may get nothing back as
>> > the
>> > OBJECT_ID function could cause problems, you may therefore want to
>> > resort
>> > to
>> > using sysobjects.
>> >
>> > John
>> >
>> >
>> >
>> >
>> > "Yifei Jiang" wrote:
>> >
>> >> Hi, All
>> >>
>> >> Recently, I was running a script in sql 2000 on window 2000 server
>> >> like:
>> >>
>> >> declare @.sql nvarchar(4000)
>> >> declare @.db sysname ; set @.db = DB_NAME()
>> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
>> >>
>> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
>> >> ''.''
>> >> +
>> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
>> >> INFORMATION_SCHEMA.ROUTINES ' +
>> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> >>
>> >> exec master.dbo.xp_execresultset @.sql,@.db
>> >>
>> >> to grant stored procedures execution permition to role, after running
>> >> the
>> >> script, the privilige is grant it to that role, but I got very strange
>> >> things happened on sql server.
>> >>
>> >> 1. Master database get Information_Shema as a user. dbaccess "via
>> >> group
>> >> membership"
>> >> 2.Master database got system_function_schema as a user , dbaccess "via
>> >> group
>> >> membership"
>> >> 3.guest user show in every database in the instance, dbaccess "via
>> >> group
>> >> membership"
>> >> if I delete guest account, show me message "the user is not in
>> >> database", sp_helpuser did not show guest user, enterprice manager
>> >> show
>> >> it
>> >> as a user.
>> >> 4. run sp_grantdbaccess guest. the guest get permitted to use
>> >> database. I
>> >> delete it successful, however, a couple minutes later, it appears in
>> >> the
>> >> db
>> >> as dbaccess via group membership.
>> >>
>> >> it is really painful for me. could anyone give me a hint to fix the
>> >> problem?
>> >>
>> >> any help is appreciated.
>> >>
>> >> Thanks
>> >>
>> >> Yifei
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>|||Hi
In SQL 2005 you need to be more aware of the difference between a schema and
a user. I think Kalen wrote about this some time ago in SQL Server magazine,
but I can't put my hands on this, but you may want to read
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx
John
"Yifei" wrote:
> Hi, John,
> You are right, what I want to say that sql 2005 caused the information_shema
> as a user in master db. it is nothing wrong with running script.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
> > The original post has no mention of SQL 2005. If you use Management
> > Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> > under system views in the master database. If you use Enterprise
> > manager to look at a SQL 2000 database there is no division between
> > system and user views. If you used a cursor instead of using the
> > undocumented xp_execresultset this should work.
> >
> > John
> >
> > Scott wrote:
> >> Hi, John,
> >>
> >> Thank you for replay,
> >>
> >> I think the issue relate to sql 2005, because from em on the server, the
> >> information_shema does not show as user, from em installed sql2005,
> >> information_shema shows as user. Same as another machine that installed
> >> sql
> >> 2005 and does not run the script.
> >>
> >> The reason because I did not check the user until I run the script and
> >> have
> >> to check it and find the problem.
> >>
> >> Thanks
> >>
> >> Yifei
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> >> > Hi
> >> >
> >> > You may want to try
> >> >
> >> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
> >> > ''.'' +
> >> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> >> > '.INFORMATION_SCHEMA.ROUTINES ' +
> >> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >> >
> >> > Although if that gives you thre right view you may get nothing back as
> >> > the
> >> > OBJECT_ID function could cause problems, you may therefore want to
> >> > resort
> >> > to
> >> > using sysobjects.
> >> >
> >> > John
> >> >
> >> >
> >> >
> >> >
> >> > "Yifei Jiang" wrote:
> >> >
> >> >> Hi, All
> >> >>
> >> >> Recently, I was running a script in sql 2000 on window 2000 server
> >> >> like:
> >> >>
> >> >> declare @.sql nvarchar(4000)
> >> >> declare @.db sysname ; set @.db = DB_NAME()
> >> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> >> >>
> >> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
> >> >> ''.''
> >> >> +
> >> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> >> >> INFORMATION_SCHEMA.ROUTINES ' +
> >> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >> >>
> >> >> exec master.dbo.xp_execresultset @.sql,@.db
> >> >>
> >> >> to grant stored procedures execution permition to role, after running
> >> >> the
> >> >> script, the privilige is grant it to that role, but I got very strange
> >> >> things happened on sql server.
> >> >>
> >> >> 1. Master database get Information_Shema as a user. dbaccess "via
> >> >> group
> >> >> membership"
> >> >> 2.Master database got system_function_schema as a user , dbaccess "via
> >> >> group
> >> >> membership"
> >> >> 3.guest user show in every database in the instance, dbaccess "via
> >> >> group
> >> >> membership"
> >> >> if I delete guest account, show me message "the user is not in
> >> >> database", sp_helpuser did not show guest user, enterprice manager
> >> >> show
> >> >> it
> >> >> as a user.
> >> >> 4. run sp_grantdbaccess guest. the guest get permitted to use
> >> >> database. I
> >> >> delete it successful, however, a couple minutes later, it appears in
> >> >> the
> >> >> db
> >> >> as dbaccess via group membership.
> >> >>
> >> >> it is really painful for me. could anyone give me a hint to fix the
> >> >> problem?
> >> >>
> >> >> any help is appreciated.
> >> >>
> >> >> Thanks
> >> >>
> >> >> Yifei
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >
>
>

BUG REPORT: ReadWriteVariables different between Script Task and Script Component

I have just spent two hours banging my head off the wall to figure this one out... wondering if I had a problem with variable scope or something...

I had a Script Task which was taking a few package level variables to use in the script.

So I put the list of variables in the ReadWriteVariables property.

Specified them like this:

"User::FileBeingProcessed, User::FileSource"

And all was well....

Then later on decided to use the same set of variables in a Script Component contained in a Data Flow.

And kept kept getting a "..variable not found.." error dialog when I clicked on Design Script.

Finally the solution came to me: Spaces in the list of variables !

In a script task you can have a space after a comma in the list... but in a script component you CAN'T !!

So what works in a Script Task is :

Task : "User::FileBeingProcessed, User::FileSource"

But that will not work in a Script Component...

See the difference?:

Task :"User::FileBeingProcessed, User::FileSource"
Component:"User::FileBeingProcessed,User::FileSource"

I know the BOL says to use a comma separated list.. but.. some consistency would be nice...

PJ

I agree..that's a bit...err...cra@.p!

I always thought both used the syntax without a space in there and hence that what I always use - and hence I never have a problem with it.

Bug it! http://lab.msdn.microsoft.com/productfeedback/default.aspx

-Jamie

|||

Oh yeah, if you worked without the spaces you'd never see the problem... its just if you're an awkward fecker like me... heh heh...

Very misleading tho.. I was thrashing around for ages worried about scope (since the Script component is only down inside a data flow) and then applying service packs.. etc...

Still.. yer eventually glad just to find out yer not going crazy...

heh,

PJ

Bug

I have a client that I wrote a script for them to change the clustered index
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.
Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/default...09&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>
|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/default...09&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> index
new[vbcol=seagreen]
> query
> to
> went
> able
of[vbcol=seagreen]
> my
others[vbcol=seagreen]
> it
found[vbcol=seagreen]
> SP3a.
> on
> problem
and[vbcol=seagreen]
90[vbcol=seagreen]
ahve[vbcol=seagreen]
all
>

Bug

I have a client that I wrote a script for them to change the clustered index
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/defaul...509&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/defaul...509&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> index
new[vbcol=seagreen]
> query
> to
> went
> able
of[vbcol=seagreen]
> my
others[vbcol=seagreen]
> it
found[vbcol=seagreen]
> SP3a.
> on
> problem
and[vbcol=seagreen]
90[vbcol=seagreen]
ahve[vbcol=seagreen]
all[vbcol=seagreen]
>

Bug

I have a client that I wrote a script for them to change the clustered index
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> >
> > I have a client that I wrote a script for them to change the clustered
> index
> > from the PK field (which is an identity) to another field. The new
> > clustering field happens to contain some nulls (which is not supposed to
> > happen but..). Anyway, they ran the script and the new table with the
new
> > clustered index was created without apparent errors. Then they ran a
> query
> > that looks like this:
> >
> > SELECT *
> > FROM myTable
> > WHERE clusterField is not null and someOtherField is null
> >
> > The return set returned records where the clusterField is null. I tried
> to
> > duplicate this on one of my servers and didn't have the problem. So I
> went
> > to their office and ran it and the problem persisted. After a couple of
> > hours of testing I found that the problem only existed above a certain
> > threshold of records in the table (about 1.1 million). After not being
> able
> > to find a solution I went home to do some more troubleshooting on some
of
> my
> > servers and found that on one server it had the problem but on two
others
> it
> > didn't have the problem. the one server that had the problem was using
> > service pack 3 while the two that worked were on SP3a. I thought I
found
> > the problem and told my client to make sure that their servers were on
> SP3a.
> > They had on that was on SP3 and one on 3a. They tested the script again
> on
> > the server with 3a and still had the problem. I don't know where to go
> > next. I'm going to post the exact script that I used to exhibit the
> problem
> > in hopes that someone can find a solution or verify that this is a bug
and
> > under what circumstances it occurs.
> >
> >
> >
> > CREATE TABLE [HIST_NEW] (
> > [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> > [LOANOID] [bigint] NULL ,
> > [USERFILEOID] [bigint] NULL ,
> > [LOANPURGEOID] [bigint] NULL ,
> > [SystemDate] [varchar] (8) DEFAULT (''),
> > [SystemDateSQL] [datetime] default getdate(),
> > [SystemTime] [varchar] (6) DEFAULT (''),
> > [SystemTImeSQL] [datetime] default getdate(),
> > [TransactionID] [varchar] (5) DEFAULT (''),
> > [ActionCode] [varchar] (1) DEFAULT (''),
> > [BatchDate] [varchar] (8) DEFAULT (''),
> > [BatchDateSQL] [datetime] default getdate(),
> > [CompanyAgent] [varchar] (20) DEFAULT (''),
> > [DocumentID] [varchar] (20) DEFAULT (''),
> > [EffDate] [varchar] (8) DEFAULT (''),
> > [EffDateSQL] [datetime] default getdate(),
> > [Entry] [int] NULL ,
> > [ExpDate] [varchar] (8) DEFAULT (''),
> > [ExpDateSQL] [datetime] default getdate(),
> > [HistoryNumber] [int] NULL ,
> > [LNUM] [varchar] (18) DEFAULT (''),
> > [MailDate] [varchar] (8) DEFAULT (''),
> > [MailDateSQL] [datetime] default getdate(),
> > [ModCount] [smallint] NULL ,
> > [Policy] [varchar] (15) DEFAULT (''),
> > [UserID] [varchar] (10) DEFAULT (''),
> > [lenderNumber] [char] (4) DEFAULT (''),
> > CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> > ) ON [PRIMARY]
> > GO
> >
> > --create a new clustered index on the LOANOID column
> > create clustered index cix_hist on hist_new (LOANOID) with fillfactor =90
> >
> >
> > declare @.counter
> > set @.counter = 1
> >
> > while @.counter < 3500000
> > BEGIN
> > INSERT HIST_NEW (LOANOID, LNUM)
> > SELECT case when @.counter %6 = 0 then null else @.counter end,
> > case when @.counter %500 = 0 then null else @.counter end,
> >
> > SET @.counter = @.counter + 1
> > END
> >
> >
> > --This query should not return nulls in the LOANOID field but is does in
> > some cases.
> > select lnum, LOANOID
> > from HIST_new
> > where LOANOID is not null
> > and lnum is null
> >
> >
> >
> > I appreciate any insight that can be provided on this issue. Also, I
ahve
> > been running this on SQL Server Standard Edition on Win 2K with SP4 in
all
> > cases and on various types of hardware.
> >
> >
>

Breakpoints in script component

Does anybody know if the breakpoints are supported in the script component?

I've got a script that I want to debug and make sure it's doing what it should be but everytime I set a breakpoint it loses them when I click ok on the script editor. I've tried setting precompile to false but thats not working. What do I need to do to be able to use breakpoints?

Saitham8Nope, breakpoints in script components are not supported in this version. Only script tasks support breakpoints.|||why? if breakpoints in script components are disabled how are we supposed to debug our code?|||

Saitham8 wrote:

why?

The usual time/resources limitations - did not have time to implement them.

|||go figure. (somebody really needs to create a work around for this time limitation thing :P ) any chance of it getting put in at a later time? and is there a work around?|||the only way i know how to debug a script component at run-time is with the data viewer and the row count component.

Breakpoint doesn't work

I've got a breakpoint in a script task. The script task executes successfully but doesn't break.

Short and sweet this one. Anyone any idea why it might not?

-JamieHey Jamie, I believe I read somewhere in the pipe that breakpoints weren't going to work until the prod release...|||

JAson_scoobyjw wrote:

Hey Jamie, I believe I read somewhere in the pipe that breakpoints weren't going to work until the prod release...

Nah, I know this has worked in the past and I've read posts today from people that have had it working.

-Jamie|||We don't support breakpoints in script data flow component in this release.

The script task breakpoints should work, except when the package is executed using 64-bit runtime on x64 machines.

Jamie - are you using CTP 16? I remember in some older builds the script task breakpoints did not work if the PreCompile property of the task was true. I think it is fixed in CTP 16 (but it could be after CTP 16 - for RTM, not sure).|||

Michael Entin SSIS wrote:

We don't support breakpoints in script data flow component in this release.

The script task breakpoints should work, except when the package is executed using 64-bit runtime on x64 machines.

Jamie - are you using CTP 16? I remember in some older builds the script task breakpoints did not work if the PreCompile property of the task was true. I think it is fixed in CTP 16 (but it could be after CTP 16 - for RTM, not sure).

Hi Michael,
Yeah, I am using Sept CTP/IDW 16 and I do have PreCompile=TRUE.

The package is at the office and I am currently at home so I'll check this out (i.e. set PreCompile=FALSE) on monday.

-Jamie|||http://blogs.conchango.com/jamiethomson/archive/2005/10/15/2271.aspx|||I found that if you are in debug at a breakpoint on a looping bit of code. If you press F5 the code then continues and doesn't break on the break point again, even though the statement with the breakpoint is executed again.

I thought F5 ran the code and if a breakpoint is found it should stop?|||Is it true that breakpoints won't work in a script component?

I just wrote a script source component to reorder the columns in incoming CSV's based on the column names in the first row.

I set some break points to debug, but they get ignored every time I try to run it. My "precompile" flag is set to false per some earlier posts, but that doesn't seem to affect the issue.|||SSIS does not currently support breakpoints in script components.

You will need to put some logging information in your script component see my post http://www.sqljunkies.com/WebLog/simons/archive/2005/08/03/SSIS_Script_Component_Debugging.aspx

Breaking monthly values down into daily values

Hi all, I'm new to MDX and am getting very confused with a script.

I'm running into problems breaking down monthly measures to daily values. If I have a monthly measure of 50, I would like to divide it by the number of days in the month to come up with daily values.

I believe I have set the proper granularity for the measurement relationship against the time dimension and have added the following script to my MDX script:

[Date].[Date].Members = [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count

When I submit a query like this:

SELECT [Measures].[Measurement Objective] ON 0,
MTD([Date].[Calendar].[Date].&[20070207]) ON 1
FROM [Cube]

Everything looks good. The query returns seven rows, each with a properly scaled version of the monthly measurement. However, when I write the following query to return a single MTD value:

SELECT [Measures].[Measurement Objective] ON 0
FROM [Cube]
WHERE MTD([Date].[Calendar].[Date].&[20070207])

It doesn't work. It gives me the error:

The MDX Function CURRENTMEMBER failed because the coordinate for the
'Calendar Year' attribute contains a set

I'm sure this is just a matter of me misunderstanding MDX. Any help would be appreciate.
Thanks,
Richard

This is caused by the fact that you have used the .CurrentMember function in your calculation, but then have used a set (the MTD function returns a set of date members) in the WHERE clause. This means that there is not a single current member.

By introducing the ability to have sets in the WHERE clause, Microsoft have added extra complexity to writing calculated members.

The following variation should do the trick:

Code Snippet

[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].Members), [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count)

The EXISTING() function returns the set of date members in the current context (the month to date members in your example query) and the Generate() function effectively "loops" over these members calculating your original expression. In this case the [Date].[Calendar].CurrentMember is evaluated within the context of each iteration of the "loop".

|||Thanks for the reply Darren.

I tried out the script and it didn't work. I was getting a divide-by-zero error in the results. Then I tried a simpler version without the division by the number of days in the month:

[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].Members), [Measures].CurrentMember)

which, given what you outlined, should just return the original monthly value (which is 128 for the month I'm using.) However, when I run the query each day has a value of 11891189. When I use the MTD function, it returns a concatenation of 11891189 * the number of days in the MTD set. The underlying measure is an integer.

Everything you outlined seems logical, but I'm just not sure where the 11891189 is coming from. I'm also restricting the scope of the MDX script to the single measure I'm testing.

Cheers,
Richard
|||

Actually I think I am missing a [Date] and the reference needs to be [Date].[Date].[Date].members

eg

[Date].[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].[Date].Members), [Measures].CurrentMember)

because [Date] is the dimension, [Date].[Date] is the hierarchy (which includes [Date].[Date].[All] which I think where the 11891189 might be coming from) [Date].[Date].[Date] is the actual attribute level.|||Hi Darren, it still didn't work and I've been doing a lot of investigation into the problem. I see what you mean by the problems of sets within the WHERE clause.

Instead of taking my original approach, I decided to use the Time Intelligence wizard within AS2005 and the MDX script that it generated worked like a charm. The extra YTD/MTD/QTD members in the time dimension makes a lot of sense.

Thanks again for your help
Richard

Break line inside print statement

Hello,
I need to break one line that im printing inside one script. The line is very large and i don=B4t know how to do it.
Best RegardsNot sure exactly what you mean, but perhaps below?
SELECT 'Hello' + CHAR(13) + CHAR(10) + 'there'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:9c5101c43422$e3448ef0$a101280a@.phx.gbl...
Hello,
I need to break one line that im printing inside one
script. The line is very large and i don´t know how to do
it.
Best Regards|||I'm not sure I understand correctly but
select "Hello" + char(13) + char(10) + "World"
will come out as
Hello
World
rgds
Paul
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:9c5101c43422$e3448ef0$a101280a@.phx.gbl...
Hello,
I need to break one line that im printing inside one
script. The line is very large and i don´t know how to do
it.
Best Regards