Build a Connection String Programmatically

I'm getting really frustrated with using the web.config to store all database information. It seems like you really don't have any real flexibilty when moving an application from server to server (between dev, test and prod). Seems like you have to create separate config files when you move it every time. Seems like you always run into a risk of over writing one of them if you are not careful.

Is there a good way to design the connection object to be able to tell which server it is on and determine if it's a test, dev or prod type of connection. I have used a basic three connection strings that it will compare the computer name with the key. But again, the connection string is hard coded in the web config. At least this way I am able to move all code together without the worry of overwritting the config file. I just have to beleive there is a better way to build something like this. I've heard of setting up enviroment variables on the server, but not sure how safe that is.

I'm looking everywhere on the web, but get really lame examples. If anyone knows a good method or a sample I can check out, that would be great.

Thanks!

Im not sure about building a connectionstring dynamically, but the generally accepted way to solve this is with a User.config file for you and the host. You keep all the normal crap in the webconfig, and then your connection string and any other variables in the userconfig. Here is a good example.

http://blogs.msdn.com/rprabhu/articles/433979.aspx

Build 9.0.3050

I downloaded SP2 on March 10, 2007, and applied it. The system to which I applied it reports 9.0.3050 as the version number, not 9.0.3042. Is this due to the post-SP2 fix?My system, which was updated with the bugged SP2, and then the subsequent hotfix, also reports 9.0.3050. So that seems like a reasonable assumption.|||

refer this link for latest fixes in SQL Server 2005...

http://support.microsoft.com/kb/933508

SP2 Release version was --9.00.3042.00

Latest version with this fix(Mar 05) is -- 9.00.3050.00

Madhu

Build = Error: Value cannot be null.

Hello Everyone,
I am having a heck of a time with this report. I have five datasets,
all interacting with stored procedures in SQL Server 2005, all using
the same parameter. The datasets run fine individually. I can run the
report fine. When I try to build or deploy the report, I get this:
-- Build started: Project: JobApplication, Configuration: Debug
--
Error: Value cannot be null.
========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
==========
Not very descriptive unfortunately :( I'm not even sure where to
look.
Any ideas? Any help would be greatly appreciated.
Have a great day,
RyanDid one of your datasets change 'mysteriously' to text, rather than Stored
Procedure? I think I have seen the error before, but I can't remember if it
is related to this problem or not.
"Ryan" wrote:
> Hello Everyone,
> I am having a heck of a time with this report. I have five datasets,
> all interacting with stored procedures in SQL Server 2005, all using
> the same parameter. The datasets run fine individually. I can run the
> report fine. When I try to build or deploy the report, I get this:
> -- Build started: Project: JobApplication, Configuration: Debug
> --
> Error: Value cannot be null.
> ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> ==========> Not very descriptive unfortunately :( I'm not even sure where to
> look.
> Any ideas? Any help would be greatly appreciated.
> Have a great day,
> Ryan
>|||Just a guess, you have refered a value which is a PK and in the report
properties you have clicked "Allow Null"
Amarnath, MCTS
"Ryan" wrote:
> Hello Everyone,
> I am having a heck of a time with this report. I have five datasets,
> all interacting with stored procedures in SQL Server 2005, all using
> the same parameter. The datasets run fine individually. I can run the
> report fine. When I try to build or deploy the report, I get this:
> -- Build started: Project: JobApplication, Configuration: Debug
> --
> Error: Value cannot be null.
> ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> ==========> Not very descriptive unfortunately :( I'm not even sure where to
> look.
> Any ideas? Any help would be greatly appreciated.
> Have a great day,
> Ryan
>|||Thank you both for answering . . . in case anybody is interested:
I tried both stored procedure and text type datasets . . . both with
the same result. I'll check to make sure when it is set as a stored
procedure, it stays that way :)
Hmmm, I assume the Allow Null is for a parameter? If so, there is one
the one parameter and the "Allow null value" is unchecked.
Again, thank you for the responses; I'll give them a whirl. In the
mean time, I found that uploading the report manually works fine.
Ryan
On Mar 23, 12:56 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Just a guess, you have refered a value which is a PK and in the report
> properties you have clicked "Allow Null"
> Amarnath, MCTS
> "Ryan" wrote:
> > Hello Everyone,
> > I am having a heck of a time with this report. I have five datasets,
> > all interacting with stored procedures in SQL Server 2005, all using
> > the same parameter. The datasets run fine individually. I can run the
> > report fine. When I try to build or deploy the report, I get this:
> > -- Build started: Project: JobApplication, Configuration: Debug
> > --
> > Error: Value cannot be null.
> > ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> > ==========> > Not very descriptive unfortunately :( I'm not even sure where to
> > look.
> > Any ideas? Any help would be greatly appreciated.
> > Have a great day,
> > Ryan

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
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >
>
>

Bugs without KB articles in Cumulative Update 5 (build 3215)

There are still a handful of bugs listed as fixed in build 3215 that don't
have KB articles. Will a KB be posted soon and how would I know in advance if
I am affected by these bugs without a KB to read?
The particular bug I'm interested in is 50002184: "You receive incorrect
results when you run a query in Microsoft SQL Server 2005."
Searches in these forums and elsewhere have come up empty for me. Based
purely on that brief bug description I have no way of knowing whether I am
impacted or not. And with hotfixes the recommendation is to not apply unless
you are explicitly affected.
Got any advice?If you notice Update 4 KB was last updated on Jan 15th, way after it ewas
released. You might get some answers if you subscribe to KBALERTZ under SQL.
Chris
"ahallock" <ahallock@.discussions.microsoft.com> wrote in message
news:24EF3CA9-52C9-4C91-AE60-C45B17DA1880@.microsoft.com...
> There are still a handful of bugs listed as fixed in build 3215 that don't
> have KB articles. Will a KB be posted soon and how would I know in advance
> if
> I am affected by these bugs without a KB to read?
> The particular bug I'm interested in is 50002184: "You receive incorrect
> results when you run a query in Microsoft SQL Server 2005."
> Searches in these forums and elsewhere have come up empty for me. Based
> purely on that brief bug description I have no way of knowing whether I am
> impacted or not. And with hotfixes the recommendation is to not apply
> unless
> you are explicitly affected.
> Got any advice?|||I don't know about this specific bug. But not every bug or bug fix will have
a public KB article.
Linchi
"ahallock" wrote:
> There are still a handful of bugs listed as fixed in build 3215 that don't
> have KB articles. Will a KB be posted soon and how would I know in advance if
> I am affected by these bugs without a KB to read?
> The particular bug I'm interested in is 50002184: "You receive incorrect
> results when you run a query in Microsoft SQL Server 2005."
> Searches in these forums and elsewhere have come up empty for me. Based
> purely on that brief bug description I have no way of knowing whether I am
> impacted or not. And with hotfixes the recommendation is to not apply unless
> you are explicitly affected.
> Got any advice?|||Also look in this blog http://blogs.technet.com/hot/default.aspx
Chris
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:uq2MuesXIHA.4896@.TK2MSFTNGP06.phx.gbl...
> If you notice Update 4 KB was last updated on Jan 15th, way after it ewas
> released. You might get some answers if you subscribe to KBALERTZ under
> SQL.
> Chris
> "ahallock" <ahallock@.discussions.microsoft.com> wrote in message
> news:24EF3CA9-52C9-4C91-AE60-C45B17DA1880@.microsoft.com...
>> There are still a handful of bugs listed as fixed in build 3215 that
>> don't
>> have KB articles. Will a KB be posted soon and how would I know in
>> advance if
>> I am affected by these bugs without a KB to read?
>> The particular bug I'm interested in is 50002184: "You receive incorrect
>> results when you run a query in Microsoft SQL Server 2005."
>> Searches in these forums and elsewhere have come up empty for me. Based
>> purely on that brief bug description I have no way of knowing whether I
>> am
>> impacted or not. And with hotfixes the recommendation is to not apply
>> unless
>> you are explicitly affected.
>> Got any advice?
>|||That looks like an excellent source of news that I didn't know about. Thank
you for bringing it to my attention!
"Chris Wood" wrote:
> Also look in this blog http://blogs.technet.com/hot/default.aspx

BugS QL Server 2005 Beta 2

Dear Newsgroup Members !
I think, and that's my real thinking, i never saw a SQL-Server where has so
many Bugs inside.
Ok, it's a Beta, but that's no OK.
After 3 Days work and 4 Installations the SQL-Server run exactly. OK, that's
live.
But the great BUG after a lot other befor, is the BUG where you can delete
some Databases in the SQL Server Managment Studio with 1 Action and without
any Delete-Question. The Databases are in Nirwana.
I get my Version up and wish you a good time.
Thanks
GeraldGerald (Gerald@.discussions.microsoft.com) writes:
> I think, and that's my real thinking, i never saw a SQL-Server where
> has so many Bugs inside.
> Ok, it's a Beta, but that's no OK.
> After 3 Days work and 4 Installations the SQL-Server run exactly. OK,
> that's live. But the great BUG after a lot other befor, is the BUG where
> you can delete some Databases in the SQL Server Managment Studio with 1
> Action and without any Delete-Question. The Databases are in Nirwana.
> I get my Version up and wish you a good time.
Beware that beta 2 is over a year old now. You find the most recent drop
here: http://www.microsoft.com/sql/2005/productinfo/ctp.mspx.
As for deleting database, I tried this and I got a dialog about
dropping an object. If you just pressed Delete by mistake, you should
react.
Also, please use the SQL 2005 newsgroups for SQL 2005, as these groups
are monitored by the SQL Server Devs. Access information here:
http://go.microsoft.com/fwlink/?linkid=31765
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

bugs in subreport

I have a table contain a sub report . when I run on preview (visual studio), no problem . but when I run from my application , the report viewer just show "Report been generated" , never show the report .

after try and error I discover that when the query from main report return no records , plus the table contain a sub report , the problem occur only if you run it from your application using report viewer . not from visual studio . is this a bugs? anyway to resolve this or work around?

thks

a couple of things..

on your main report, you should probobaly set up a static dataset that always returns data... some like select from table where 1=1 or something like that. You can hide that information.

Secondly, besure to set up a description in the no rows property of the main and sub reports.

Bugs & Issues

Is there a web page where I can monitor the new/old bugs
and issues related to SQL Server & Analysis Services, SP's
e.t.c ?
Thanks.
You can see a list of hotfixes and the bugs that they fix at
http://www.aspfaq.com/2160 (anything past 8.00.760 is most likely a bug fix
that deals with at least one symptom).
As for a general list of all bugs, no, Microsoft does not publish this
information.
http://www.aspfaq.com/
(Reverse address to reply.)
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:b78c01c48938$7353c460$a501280a@.phx.gbl...
> Is there a web page where I can monitor the new/old bugs
> and issues related to SQL Server & Analysis Services, SP's
> e.t.c ?
> Thanks.
|||Thanks............

>--Original Message--
>You can see a list of hotfixes and the bugs that they fix
at
>http://www.aspfaq.com/2160 (anything past 8.00.760 is
most likely a bug fix
>that deals with at least one symptom).
>As for a general list of all bugs, no, Microsoft does not
publish this
>information.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Nick" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:b78c01c48938$7353c460$a501280a@.phx.gbl...
SP's
>
>.
>
sql

Bugs & Issues

Is there a web page where I can monitor the new/old bugs
and issues related to SQL Server & Analysis Services, SP's
e.t.c '
Thanks.You can see a list of hotfixes and the bugs that they fix at
http://www.aspfaq.com/2160 (anything past 8.00.760 is most likely a bug fix
that deals with at least one symptom).
As for a general list of all bugs, no, Microsoft does not publish this
information.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:b78c01c48938$7353c460$a501280a@.phx.gbl...
> Is there a web page where I can monitor the new/old bugs
> and issues related to SQL Server & Analysis Services, SP's
> e.t.c '
> Thanks.|||Thanks............
>--Original Message--
>You can see a list of hotfixes and the bugs that they fix
at
>http://www.aspfaq.com/2160 (anything past 8.00.760 is
most likely a bug fix
>that deals with at least one symptom).
>As for a general list of all bugs, no, Microsoft does not
publish this
>information.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Nick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:b78c01c48938$7353c460$a501280a@.phx.gbl...
>> Is there a web page where I can monitor the new/old bugs
>> and issues related to SQL Server & Analysis Services,
SP's
>> e.t.c '
>> Thanks.
>
>.
>

Bugs & Issues

Is there a web page where I can monitor the new/old bugs
and issues related to SQL Server & Analysis Services, SP's
e.t.c '
Thanks.You can see a list of hotfixes and the bugs that they fix at
http://www.aspfaq.com/2160 (anything past 8.00.760 is most likely a bug fix
that deals with at least one symptom).
As for a general list of all bugs, no, Microsoft does not publish this
information.
http://www.aspfaq.com/
(Reverse address to reply.)
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:b78c01c48938$7353c460$a501280a@.phx.gbl...
> Is there a web page where I can monitor the new/old bugs
> and issues related to SQL Server & Analysis Services, SP's
> e.t.c '
> Thanks.|||Thanks............

>--Original Message--
>You can see a list of hotfixes and the bugs that they fix
at
>http://www.aspfaq.com/2160 (anything past 8.00.760 is
most likely a bug fix
>that deals with at least one symptom).
>As for a general list of all bugs, no, Microsoft does not
publish this
>information.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Nick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:b78c01c48938$7353c460$a501280a@.phx.gbl...
SP's[vbcol=seagreen]
>
>.
>

Bugs

What do you do when you think that you've got a bug? Do
you have to call MS Support? Or do they have a free way
to report a bug?First, check the documentation and search the MS Knowledge Base to see if
it's a known issue:
http://support.microsoft.com/defaul...h;en-gb;kbhowto
Use Google to search the archives of the sqlserver newsgroups to see if your
problem has been discussed before.
If you're still not having any luck then post your problem here. If possible
post some some code that will actually reproduce the problem so that others
can test it out and suggest solutions.
You can also go via MS Product Support. If they acknowledge it as an
undocumented bug then you shouldn't be charged for the call.
David Portas
SQL Server MVP
--

Bugs

What do you do when you think that you've got a bug? Do
you have to call MS Support? Or do they have a free way
to report a bug?First, check the documentation and search the MS Knowledge Base to see if
it's a known issue:
http://support.microsoft.com/default.aspx?scid=fh;en-gb;kbhowto
Use Google to search the archives of the sqlserver newsgroups to see if your
problem has been discussed before.
If you're still not having any luck then post your problem here. If possible
post some some code that will actually reproduce the problem so that others
can test it out and suggest solutions.
You can also go via MS Product Support. If they acknowledge it as an
undocumented bug then you shouldn't be charged for the call.
--
David Portas
SQL Server MVP
--

Bugs

What do you do when you think that you've got a bug? Do
you have to call MS Support? Or do they have a free way
to report a bug?
First, check the documentation and search the MS Knowledge Base to see if
it's a known issue:
http://support.microsoft.com/default...;en-gb;kbhowto
Use Google to search the archives of the sqlserver newsgroups to see if your
problem has been discussed before.
If you're still not having any luck then post your problem here. If possible
post some some code that will actually reproduce the problem so that others
can test it out and suggest solutions.
You can also go via MS Product Support. If they acknowledge it as an
undocumented bug then you shouldn't be charged for the call.
David Portas
SQL Server MVP
sql

buggy sql like command with wildcards to xml

I have this stored procedure, witch works nice under the query mode
create procedure MKR
@.mkrvar nvarchar(50)
AS
SELECT *
FROM QUADRA
WHERE (SUBSTRING(QUADRA, 3, 9) LIKE '%@.mkrvar%')
GO
so i call it like this on my browser :
http://mywebsite/?sql=exec MKR '57' FOR XML NESTED&root=root
is not working...oh !
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (SUBSTRING(QUADRA, 3, 9)
LIKE '%57%')FOR XML NESTED&root=root
it doesnt work too
so i trought that might be something buggy...donno...so i tried this
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '%57%') FOR
XML RAW&root=root
ok...it doesnt work...why ?
but this work
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '57') FOR XML
RAW&root=root
so...why i cant use wildcards to generate an xml ?
i need a fix or a workarround for this asap, if someone could help :D
thanks
max
% is an escape character in a URL. For example you will see spaces
converted to %20.
Try %%57%% instead.
"Antonio Max" <maxspam@.bol.com.br> wrote in message
news:eiuMJVxKEHA.3292@.TK2MSFTNGP11.phx.gbl...
>I have this stored procedure, witch works nice under the query mode
> create procedure MKR
> @.mkrvar nvarchar(50)
> AS
> SELECT *
> FROM QUADRA
> WHERE (SUBSTRING(QUADRA, 3, 9) LIKE '%@.mkrvar%')
> GO
> so i call it like this on my browser :
> http://mywebsite/?sql=exec MKR '57' FOR XML NESTED&root=root
> is not working...oh !
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (SUBSTRING(QUADRA, 3, 9)
> LIKE '%57%')FOR XML NESTED&root=root
> it doesnt work too
> so i trought that might be something buggy...donno...so i tried this
>
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '%57%') FOR
> XML RAW&root=root
> ok...it doesnt work...why ?
>
> but this work
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '57') FOR
> XML
> RAW&root=root
> so...why i cant use wildcards to generate an xml ?
> i need a fix or a workarround for this asap, if someone could help :D
> thanks
> max
>
>
|||thanks a lot
forgot this;;.. eerr
anyway...it looks like this now
http://mywebsite/?sql=SELECT%20*%20F...DRA%20WHERE%20(QUADRA%20LIKE%20
'%2557%25')%20FOR%20XML%20RAW&root=root
where %25 is the % sign on url encoding char table
thanks
max
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:#T#EIIzKEHA.892@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> % is an escape character in a URL. For example you will see spaces
> converted to %20.
> Try %%57%% instead.
>
> "Antonio Max" <maxspam@.bol.com.br> wrote in message
> news:eiuMJVxKEHA.3292@.TK2MSFTNGP11.phx.gbl...
9)[vbcol=seagreen]
FOR
>

BugCheck in SQL Server 2000 SP3a?

When running a particular really badly written query on SQL Server 2000 SP3a,
I'm seeing a dump file generated about every 4 minutes. The top of the dump
is:
* BEGIN STACK DUMP:
* 09/19/04 12:40:38 spid 0
*
* Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
Line
0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000190
Anyone from Microsoft interested in the rest of the dump?
Regards,
Jonathan
http://kerblog.com/earlyedition/archive/2004/09/22/176.aspxJonathan,
Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
can check the SQL Server 2000 hotfixes released by Microsoft since then to
see if your case is covered. If not, you can report this to MS by phoning in
an incident to Microsoft PSS.
Ron
--
Ron Talmage
SQL Server MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> When running a particular really badly written query on SQL Server 2000
SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the
dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/archive/2004/09/22/176.aspx|||Thanks, Ron.
Am I going to be charged for reporting this bug?
Regards,
Jonathan
"Ron Talmage" wrote:
> Jonathan,
> Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
> can check the SQL Server 2000 hotfixes released by Microsoft since then to
> see if your case is covered. If not, you can report this to MS by phoning in
> an incident to Microsoft PSS.
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> > When running a particular really badly written query on SQL Server 2000
> SP3a,
> > I'm seeing a dump file generated about every 4 minutes. The top of the
> dump
> > is:
> >
> > * BEGIN STACK DUMP:
> >
> > * 09/19/04 12:40:38 spid 0
> >
> > *
> >
> > * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> > Line
> > 0+00000000)
> >
> > * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> >
> > * Access Violation occurred reading address 00000190
> >
> >
> > Anyone from Microsoft interested in the rest of the dump?
> >
> > Regards,
> >
> > Jonathan
> >
> > http://kerblog.com/earlyedition/archive/2004/09/22/176.aspx
>
>|||Hi, Jonathan
Thanks for your posting!
From your descriptions, I understood that you wonder whether it will be a
free case if you raise this your dump files to us checking it is an known
issue for us. Correct me if I was wrong.
Based on my scope, if this is confirmed to be an known issue and no hotfix
or workaround is available, it will be definitely a free case. If there is
an hotfix or workaround found to resolved this, I am afraid it will not be
a refund. Moreover, just asking for hotfix from an KB will also be free
case.
FYI, if you want to know more detailed information about this, you can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Jonathan,
I am sorry to say that checking dumps file is not supported in the
newsgroup and this kind of issues can be caused by various factors, it is
difficult to locate the root cause in a newsgroup thread. If you want to
find the root cause of that, you will have to turn to PSS for help.
Thank you for your patience and understanding.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Mingqing,
Thanks for your reply! You understand my question well. I didn't see any
matching hotfix, but is it possible/advisable for me to post my dump file
here and have it checked?
Thank you,
Jonathan
""Mingqing Cheng [MSFT]"" wrote:
> Hi, Jonathan
> Thanks for your posting!
> From your descriptions, I understood that you wonder whether it will be a
> free case if you raise this your dump files to us checking it is an known
> issue for us. Correct me if I was wrong.
> Based on my scope, if this is confirmed to be an known issue and no hotfix
> or workaround is available, it will be definitely a free case. If there is
> an hotfix or workaround found to resolved this, I am afraid it will not be
> a refund. Moreover, just asking for hotfix from an KB will also be free
> case.
> FYI, if you want to know more detailed information about this, you can
> contact Microsoft Product Support directly to discuss additional support
> options you may have available, by contacting us at 1-(800)936-5800 or by
> choosing one of the options listed at
> http://support.microsoft.com/default.aspx?scid=sz;en-us;top
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Well, I just found KB article 875445, which is a hotfix that seems to fix my
core dump. Let's see...
-- J
http://kerblog.com/earlyedition/archive/2004/09/27/184.aspx
"Jonathan Levine" wrote:
> When running a particular really badly written query on SQL Server 2000 SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/archive/2004/09/22/176.aspx|||Hi Jonathan,
I would strongly recommend you open a new case with PSS asking for that
htofix. PSS will make sure that hotfix is suitable for your issue. You can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top.
Anyway, asking a hotfix will be a free case. You are welcome to post the
result here let us know whether that hotfix resolved your issue. If not,
you could continue discussing it with PSS to find the root cause.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mingqing,
""Mingqing Cheng [MSFT]"" wrote:
> I would strongly recommend you open a new case with PSS asking for that
> htofix. PSS will make sure that hotfix is suitable for your issue.
Thank you, I already did so and got the hotfix.
Regards,
Jonathan

BugCheck in SQL Server 2000 SP3a?

When running a particular really badly written query on SQL Server 2000 SP3a,
I'm seeing a dump file generated about every 4 minutes. The top of the dump
is:
* BEGIN STACK DUMP:
* 09/19/04 12:40:38 spid 0
*
* Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
Line
0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000190
Anyone from Microsoft interested in the rest of the dump?
Regards,
Jonathan
http://kerblog.com/earlyedition/arch...09/22/176.aspx
Jonathan,
Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
can check the SQL Server 2000 hotfixes released by Microsoft since then to
see if your case is covered. If not, you can report this to MS by phoning in
an incident to Microsoft PSS.
Ron
Ron Talmage
SQL Server MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> When running a particular really badly written query on SQL Server 2000
SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the
dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/arch...09/22/176.aspx
|||Thanks, Ron.
Am I going to be charged for reporting this bug?
Regards,
Jonathan
"Ron Talmage" wrote:
> Jonathan,
> Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
> can check the SQL Server 2000 hotfixes released by Microsoft since then to
> see if your case is covered. If not, you can report this to MS by phoning in
> an incident to Microsoft PSS.
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> SP3a,
> dump
>
>
|||Hi, Jonathan
Thanks for your posting!
From your descriptions, I understood that you wonder whether it will be a
free case if you raise this your dump files to us checking it is an known
issue for us. Correct me if I was wrong.
Based on my scope, if this is confirmed to be an known issue and no hotfix
or workaround is available, it will be definitely a free case. If there is
an hotfix or workaround found to resolved this, I am afraid it will not be
a refund. Moreover, just asking for hotfix from an KB will also be free
case.
FYI, if you want to know more detailed information about this, you can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default...d=sz;en-us;top
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Mingqing,
Thanks for your reply! You understand my question well. I didn't see any
matching hotfix, but is it possible/advisable for me to post my dump file
here and have it checked?
Thank you,
Jonathan
""Mingqing Cheng [MSFT]"" wrote:
> Hi, Jonathan
> Thanks for your posting!
> From your descriptions, I understood that you wonder whether it will be a
> free case if you raise this your dump files to us checking it is an known
> issue for us. Correct me if I was wrong.
> Based on my scope, if this is confirmed to be an known issue and no hotfix
> or workaround is available, it will be definitely a free case. If there is
> an hotfix or workaround found to resolved this, I am afraid it will not be
> a refund. Moreover, just asking for hotfix from an KB will also be free
> case.
> FYI, if you want to know more detailed information about this, you can
> contact Microsoft Product Support directly to discuss additional support
> options you may have available, by contacting us at 1-(800)936-5800 or by
> choosing one of the options listed at
> http://support.microsoft.com/default...d=sz;en-us;top
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Jonathan,
I am sorry to say that checking dumps file is not supported in the
newsgroup and this kind of issues can be caused by various factors, it is
difficult to locate the root cause in a newsgroup thread. If you want to
find the root cause of that, you will have to turn to PSS for help.
Thank you for your patience and understanding.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Well, I just found KB article 875445, which is a hotfix that seems to fix my
core dump. Let's see...
-- J
http://kerblog.com/earlyedition/arch...09/27/184.aspx
"Jonathan Levine" wrote:

> When running a particular really badly written query on SQL Server 2000 SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/arch...09/22/176.aspx
|||Hi Jonathan,
I would strongly recommend you open a new case with PSS asking for that
htofix. PSS will make sure that hotfix is suitable for your issue. You can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top.
Anyway, asking a hotfix will be a free case. You are welcome to post the
result here let us know whether that hotfix resolved your issue. If not,
you could continue discussing it with PSS to find the root cause.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
""Mingqing Cheng [MSFT]"" wrote:
> I would strongly recommend you open a new case with PSS asking for that
> htofix. PSS will make sure that hotfix is suitable for your issue.
Thank you, I already did so and got the hotfix.
Regards,
Jonathan

Bug?-when using nested subreports in combination with list

Someone please help me with a workaround on this issue:
I have created three reports: (ReportA, ReportB and ReportC).
ReportA includes a subreport (ReportB). ReportB consists of one list. The list is bound to a dataset. The list consists of one subreport (ReportC). ReportC is an empty report.

What is the problem:
When running Report A,The report generation fails when the dataset that is used in ReportB returns zero rows and the report is rendered across two or more pages.
The same problem also occurs when the dataset returns one or more rows and every row are filtered away using a filter expression on the list in ReportB

To make sure that the report is rendered across two or more pages, just add some dummy data to reportA.

When reportB is run, the problem does not occur.

The first page is genereted correctly, but when I navigate to the second page, the error occur.

Anyone experienced the same issue?

This sounds similar to something I am experiencing. I am using SQL 2005.

I have 5 levels of nested reports.

The first page always displays as expected.|||Microsoft has confirmed to me that this issue is a bug and that this should be solved in Service Pack 1.|||

Wow, that's great.

My PM actually solved this on for us with a work around. It's a problem related to a common business issue "asymetrical heirarchy". If there is no data in the 3rd level, it affects the turn down control on the top level. We are currently re-writing our queries to always produce results, and then hide the display when the childID = parentID.

Microsoft SRS 2005 already handles this issue better than most others, but if they're going to fix it all the way, that's great!

|||

Hello,

when RS2005 SP1 wil be released?

Thanks in adavnce

Bug?-when using nested subreports in combination with list

Someone please help me with a workaround on this issue:
I have created three reports: (ReportA, ReportB and ReportC).
ReportA includes a subreport (ReportB). ReportB consists of one list. The list is bound to a dataset. The list consists of one subreport (ReportC). ReportC is an empty report.

What is the problem:
When running Report A,The report generation fails when the dataset that is used in ReportB returns zero rows and the report is rendered across two or more pages.
The same problem also occurs when the dataset returns one or more rows and every row are filtered away using a filter expression on the list in ReportB

To make sure that the report is rendered across two or more pages, just add some dummy data to reportA.

When reportB is run, the problem does not occur.

The first page is genereted correctly, but when I navigate to the second page, the error occur.

Anyone experienced the same issue?

This sounds similar to something I am experiencing. I am using SQL 2005.

I have 5 levels of nested reports.

The first page always displays as expected.|||Microsoft has confirmed to me that this issue is a bug and that this should be solved in Service Pack 1.|||

Wow, that's great.

My PM actually solved this on for us with a work around. It's a problem related to a common business issue "asymetrical heirarchy". If there is no data in the 3rd level, it affects the turn down control on the top level. We are currently re-writing our queries to always produce results, and then hide the display when the childID = parentID.

Microsoft SRS 2005 already handles this issue better than most others, but if they're going to fix it all the way, that's great!

|||

Hello,

when RS2005 SP1 wil be released?

Thanks in adavnce

sql

Bug?-when using nested subreports in combination with list

Someone please help me with a workaround on this issue:
I have created three reports: (ReportA, ReportB and ReportC).
ReportA includes a subreport (ReportB). ReportB consists of one list. The list is bound to a dataset. The list consists of one subreport (ReportC). ReportC is an empty report.

What is the problem:
When running Report A,The report generation fails when the dataset that is used in ReportB returns zero rows and the report is rendered across two or more pages.
The same problem also occurs when the dataset returns one or more rows and every row are filtered away using a filter expression on the list in ReportB

To make sure that the report is rendered across two or more pages, just add some dummy data to reportA.

When reportB is run, the problem does not occur.

The first page is genereted correctly, but when I navigate to the second page, the error occur.

Anyone experienced the same issue?

This sounds similar to something I am experiencing. I am using SQL 2005.

I have 5 levels of nested reports.

The first page always displays as expected.|||Microsoft has confirmed to me that this issue is a bug and that this should be solved in Service Pack 1.|||

Wow, that's great.

My PM actually solved this on for us with a work around. It's a problem related to a common business issue "asymetrical heirarchy". If there is no data in the 3rd level, it affects the turn down control on the top level. We are currently re-writing our queries to always produce results, and then hide the display when the childID = parentID.

Microsoft SRS 2005 already handles this issue better than most others, but if they're going to fix it all the way, that's great!

|||

Hello,

when RS2005 SP1 wil be released?

Thanks in adavnce

bug?: two shared data sources, one update effects the other

Is this a bug? I have two shared data sources (one points to a production
server, the other points to a development server). One is named MyDB.rdl, the
other is named MyDBDev.rdl. Both of these are SQL Server data sources, and
they use a specific username and password.
I'm using Visual Studio 2003 as my development environment.
Here's the bug. If I change the username or password for one of the data
sources, it updates the other data sources as well. If I change the username
on the MyDB.rdl datasource from Fred to Jill, the MyDBDev.rdl datasource is
changed as well!!
Is this a known bug, or am I doing something wrong? Is there a workaround?
Thanks,
MarcShared data source file would have extention .rds
Could you please provide more details?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mhf" <mhf@.discussions.microsoft.com> wrote in message
news:2CABBB0A-9C90-48F5-A94B-9A1E1A0DE9E7@.microsoft.com...
> Is this a bug? I have two shared data sources (one points to a production
> server, the other points to a development server). One is named MyDB.rdl,
> the
> other is named MyDBDev.rdl. Both of these are SQL Server data sources, and
> they use a specific username and password.
> I'm using Visual Studio 2003 as my development environment.
> Here's the bug. If I change the username or password for one of the data
> sources, it updates the other data sources as well. If I change the
> username
> on the MyDB.rdl datasource from Fred to Jill, the MyDBDev.rdl datasource
> is
> changed as well!!
> Is this a known bug, or am I doing something wrong? Is there a workaround?
> Thanks,
> Marc|||Lev,
Thanks for the reply. Yes, I mistyped there, I meant to day .rds files. The
two shared data source files have an extension of .rds and when I change one
(in Visual Studio) it modifies both of them. The SQL login name and passwords
are not stored in the .rds file, so I don't see the actual change persisted
there. I'm not sure where the changes are actually stored.
"Lev Semenets [MSFT]" wrote:
> Shared data source file would have extention .rds
> Could you please provide more details?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "mhf" <mhf@.discussions.microsoft.com> wrote in message
> news:2CABBB0A-9C90-48F5-A94B-9A1E1A0DE9E7@.microsoft.com...
> > Is this a bug? I have two shared data sources (one points to a production
> > server, the other points to a development server). One is named MyDB.rdl,
> > the
> > other is named MyDBDev.rdl. Both of these are SQL Server data sources, and
> > they use a specific username and password.
> >
> > I'm using Visual Studio 2003 as my development environment.
> >
> > Here's the bug. If I change the username or password for one of the data
> > sources, it updates the other data sources as well. If I change the
> > username
> > on the MyDB.rdl datasource from Fred to Jill, the MyDBDev.rdl datasource
> > is
> > changed as well!!
> >
> > Is this a known bug, or am I doing something wrong? Is there a workaround?
> >
> > Thanks,
> >
> > Marc
>
>|||Would you email those .rds files to me?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mhf" <mhf@.discussions.microsoft.com> wrote in message
news:530A9866-1EEC-4CAB-B44A-CD199B3BBD10@.microsoft.com...
> Lev,
> Thanks for the reply. Yes, I mistyped there, I meant to day .rds files.
> The
> two shared data source files have an extension of .rds and when I change
> one
> (in Visual Studio) it modifies both of them. The SQL login name and
> passwords
> are not stored in the .rds file, so I don't see the actual change
> persisted
> there. I'm not sure where the changes are actually stored.
>
> "Lev Semenets [MSFT]" wrote:
>> Shared data source file would have extention .rds
>> Could you please provide more details?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "mhf" <mhf@.discussions.microsoft.com> wrote in message
>> news:2CABBB0A-9C90-48F5-A94B-9A1E1A0DE9E7@.microsoft.com...
>> > Is this a bug? I have two shared data sources (one points to a
>> > production
>> > server, the other points to a development server). One is named
>> > MyDB.rdl,
>> > the
>> > other is named MyDBDev.rdl. Both of these are SQL Server data sources,
>> > and
>> > they use a specific username and password.
>> >
>> > I'm using Visual Studio 2003 as my development environment.
>> >
>> > Here's the bug. If I change the username or password for one of the
>> > data
>> > sources, it updates the other data sources as well. If I change the
>> > username
>> > on the MyDB.rdl datasource from Fred to Jill, the MyDBDev.rdl
>> > datasource
>> > is
>> > changed as well!!
>> >
>> > Is this a known bug, or am I doing something wrong? Is there a
>> > workaround?
>> >
>> > Thanks,
>> >
>> > Marc
>>

Bug?: rsMultiReportItemsInPageSectionExpression when one report item, but referenced twice

When I reference the same report item twice in my footer expression I get an
error saying that I've refereced two items. My motivation is the database
field on each page footer. I have a custom assembly instance that stores
the value from the first page, then returns it when I am passing in empty
values. I tried the page number from the globals collection but ran into
#Error...anyway, getting off base. My issue is that the report expression
references a single item twice. Should I be able to do this? If not,
should the error message be more specific?
Expression:
=IIf(IsNothing(ReportItems!txtSchoolNameHidden.Value)=False,
Code.loc.SchoolName(ReportItems!txtSchoolNameHidden.Value),
Code.loc.SchoolName(""))
Error:
[rsMultiReportItemsInPageSectionExpression] The Value expression for the
textbox 'txtSchoolName' refers to more than one report item. An expression
in a page header or footer can refer to only one report item.Submitted bug to Microsoft via Product Feedback
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:%23RtLLbKNGHA.3728@.tk2msftngp13.phx.gbl...
> When I reference the same report item twice in my footer expression I get
> an error saying that I've refereced two items. My motivation is the
> database field on each page footer. I have a custom assembly instance
> that stores the value from the first page, then returns it when I am
> passing in empty values. I tried the page number from the globals
> collection but ran into #Error...anyway, getting off base. My issue is
> that the report expression references a single item twice. Should I be
> able to do this? If not, should the error message be more specific?
> Expression:
> =IIf(IsNothing(ReportItems!txtSchoolNameHidden.Value)=False,
> Code.loc.SchoolName(ReportItems!txtSchoolNameHidden.Value),
> Code.loc.SchoolName(""))
> Error:
> [rsMultiReportItemsInPageSectionExpression] The Value expression for the
> textbox 'txtSchoolName' refers to more than one report item. An
> expression in a page header or footer can refer to only one report item.
>

Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
--
markusOn Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:

>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn
'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.

>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/defaul...kb;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.
4ax.com...
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
>
'case_jn'[vbcol=seagreen]
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
>
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:

>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/defaul...kb;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
--
markusThat looks like a bug with ISNULL; I switched it to use COALESCE instead and
it fixed the problem...
Hopefully someone reading this knows how to submit a SQL bug report to MS?
"mark" <wimark@.smsNOSAPM.at> wrote in message
news:#EJS#RvJEHA.620@.tk2msftngp13.phx.gbl...
> Hi,
> there is a small test case (SQL Server 2000 - 8.00.760):
> -- create a small table
> create table test1 (nr int null)
> insert into test1 values (1)
> insert into test1 values (2)
> -- now create a view
> create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> If you perform this select, the isnull_jn col is always 'J',
> although it should be 'N' in the second row:
> select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> from test1 left outer join testview on test1.nr = testview.nr
> nr jn isnull_jn
> -- -- --
> 1 J J
> 2 NULL J <<<=== error, should be N!
>
> If you redefine the query to:
> select test1.nr, jn, case when jn is null then 'N' else jn end as
'case_jn'
> from test1 left outer join testview on test1.nr = testview.nr
> nr jn case_jn
> -- -- --
> 1 J J
> 2 NULL N <<== ok!
> everything works fine.
> Any suggestions?
> --
> markus
>|||ISNULL is kind of notoriously NOT the same thing as COALESCE and CASE, so I
was skeptical that this was really a bug. But below is a repro which uses
ISNULL with a view and an "inline view" or derived table.
Now despite any differences between INSULL and COALESCE, these 2 queries
should return the same data. They do not.
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview as select nr, 'J' jn from test1 where nr = 1
select
test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1
left outer join (select nr, 'J' jn from test1 where nr = 1) testview
on test1.nr = testview.nr
where test1.nr=2
select
test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1
left outer join testview
on test1.nr = testview.nr
where test1.nr=2
--
nr jn isnull_jn
2 N
(1 row(s) affected)
nr jn isnull_jn
2 J
(1 row(s) affected)
David
Should definitely return the same data
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ebxMLqvJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> That looks like a bug with ISNULL; I switched it to use COALESCE instead
and
> it fixed the problem...
> Hopefully someone reading this knows how to submit a SQL bug report to MS?
>
> "mark" <wimark@.smsNOSAPM.at> wrote in message
> news:#EJS#RvJEHA.620@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > there is a small test case (SQL Server 2000 - 8.00.760):
> >
> > -- create a small table
> > create table test1 (nr int null)
> > insert into test1 values (1)
> > insert into test1 values (2)
> > -- now create a view
> > create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> >
> > If you perform this select, the isnull_jn col is always 'J',
> > although it should be 'N' in the second row:
> > select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> > from test1 left outer join testview on test1.nr = testview.nr
> > nr jn isnull_jn
> > -- -- --
> > 1 J J
> > 2 NULL J <<<=== error, should be N!
> >
> >
> > If you redefine the query to:
> > select test1.nr, jn, case when jn is null then 'N' else jn end as
> 'case_jn'
> > from test1 left outer join testview on test1.nr = testview.nr
> > nr jn case_jn
> > -- -- --
> > 1 J J
> > 2 NULL N <<== ok!
> > everything works fine.
> >
> > Any suggestions?
> > --
> > markus
> >
> >
>|||On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.
>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
--
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.4ax.com...
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
> >-- create a small table
> >create table test1 (nr int null)
> >insert into test1 values (1)
> >insert into test1 values (2)
> >-- now create a view
> >create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> >
> >If you perform this select, the isnull_jn col is always 'J',
> >although it should be 'N' in the second row:
> >select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> >from test1 left outer join testview on test1.nr = testview.nr
> >nr jn isnull_jn
> >-- -- --
> >1 J J
> >2 NULL J <<<=== error, should be N!
> >
> >
> >If you redefine the query to:
> >select test1.nr, jn, case when jn is null then 'N' else jn end as
'case_jn'
> >from test1 left outer join testview on test1.nr = testview.nr
> >nr jn case_jn
> >-- -- --
> >1 J J
> >2 NULL N <<== ok!
> >everything works fine.
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
> >
> >Any suggestions?
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:
>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)