New SP2 Excel Render Bug (Excel Formulas)

Hi,
I have a report that converts expression to Excel formulas. The Excel
formulas in a table are incorrectly rendered in a way that the references
are all off by 6 or so cells. When I set the "Omit Formulas" device setting
to true the values are correct, though there are no formulas. I don't
remember this failure in SP1.
Thanks,
Bryan
PS. Is there a way to make "OmitFormulas=true" static when the request is
made from the report manager?BTW, This bug only happens when requesting the render from the report
server. It doesn't happen in VS.NET.
"BDB" <reply@.to.group.com> wrote in message
news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a report that converts expression to Excel formulas. The Excel
> formulas in a table are incorrectly rendered in a way that the references
> are all off by 6 or so cells. When I set the "Omit Formulas" device
> setting to true the values are correct, though there are no formulas. I
> don't remember this failure in SP1.
> Thanks,
> Bryan
> PS. Is there a way to make "OmitFormulas=true" static when the request is
> made from the report manager?
>|||Setting the DeviceInfo defaults has been added in SQL 2005 RS but is not
available with SQL 2000. As for the bug, nothing has changed in this area so
I would imagine it happened with SP1 as well.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"BDB" <reply@.to.group.com> wrote in message
news:eoGuJAoSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> BTW, This bug only happens when requesting the render from the report
> server. It doesn't happen in VS.NET.
> "BDB" <reply@.to.group.com> wrote in message
> news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a report that converts expression to Excel formulas. The Excel
>> formulas in a table are incorrectly rendered in a way that the references
>> are all off by 6 or so cells. When I set the "Omit Formulas" device
>> setting to true the values are correct, though there are no formulas. I
>> don't remember this failure in SP1.
>> Thanks,
>> Bryan
>> PS. Is there a way to make "OmitFormulas=true" static when the request
>> is made from the report manager?
>|||No, I've tested both ways. This bug was introduced in SP2.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ePzjhGpSFHA.2324@.TK2MSFTNGP10.phx.gbl...
> Setting the DeviceInfo defaults has been added in SQL 2005 RS but is not
> available with SQL 2000. As for the bug, nothing has changed in this area
> so I would imagine it happened with SP1 as well.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "BDB" <reply@.to.group.com> wrote in message
> news:eoGuJAoSFHA.3720@.TK2MSFTNGP10.phx.gbl...
>> BTW, This bug only happens when requesting the render from the report
>> server. It doesn't happen in VS.NET.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a report that converts expression to Excel formulas. The Excel
>> formulas in a table are incorrectly rendered in a way that the
>> references are all off by 6 or so cells. When I set the "Omit Formulas"
>> device setting to true the values are correct, though there are no
>> formulas. I don't remember this failure in SP1.
>> Thanks,
>> Bryan
>> PS. Is there a way to make "OmitFormulas=true" static when the request
>> is made from the report manager?
>>
>|||Can you post or send me your RDL (remove the online. part in my e-mail
address)?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"BDB" <reply@.to.group.com> wrote in message
news:uFBYKQqSFHA.3552@.TK2MSFTNGP10.phx.gbl...
> No, I've tested both ways. This bug was introduced in SP2.
>
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ePzjhGpSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>> Setting the DeviceInfo defaults has been added in SQL 2005 RS but is not
>> available with SQL 2000. As for the bug, nothing has changed in this area
>> so I would imagine it happened with SP1 as well.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eoGuJAoSFHA.3720@.TK2MSFTNGP10.phx.gbl...
>> BTW, This bug only happens when requesting the render from the report
>> server. It doesn't happen in VS.NET.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a report that converts expression to Excel formulas. The Excel
>> formulas in a table are incorrectly rendered in a way that the
>> references are all off by 6 or so cells. When I set the "Omit
>> Formulas" device setting to true the values are correct, though there
>> are no formulas. I don't remember this failure in SP1.
>> Thanks,
>> Bryan
>> PS. Is there a way to make "OmitFormulas=true" static when the request
>> is made from the report manager?
>>
>>
>|||Sure. Thank you.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:%2376cRJrSFHA.3300@.TK2MSFTNGP10.phx.gbl...
> Can you post or send me your RDL (remove the online. part in my e-mail
> address)?
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "BDB" <reply@.to.group.com> wrote in message
> news:uFBYKQqSFHA.3552@.TK2MSFTNGP10.phx.gbl...
>> No, I've tested both ways. This bug was introduced in SP2.
>>
>> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
>> news:ePzjhGpSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>> Setting the DeviceInfo defaults has been added in SQL 2005 RS but is not
>> available with SQL 2000. As for the bug, nothing has changed in this
>> area so I would imagine it happened with SP1 as well.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eoGuJAoSFHA.3720@.TK2MSFTNGP10.phx.gbl...
>> BTW, This bug only happens when requesting the render from the report
>> server. It doesn't happen in VS.NET.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a report that converts expression to Excel formulas. The Excel
>> formulas in a table are incorrectly rendered in a way that the
>> references are all off by 6 or so cells. When I set the "Omit
>> Formulas" device setting to true the values are correct, though there
>> are no formulas. I don't remember this failure in SP1.
>> Thanks,
>> Bryan
>> PS. Is there a way to make "OmitFormulas=true" static when the
>> request is made from the report manager?
>>
>>
>>
>|||This issue was resolved by rebuilding and redeploying the RDL.
Much thinks to Brian Welcker for his help.
Bryan
"BDB" <reply@.to.group.com> wrote in message
news:OZrPaFzSFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Sure. Thank you.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:%2376cRJrSFHA.3300@.TK2MSFTNGP10.phx.gbl...
>> Can you post or send me your RDL (remove the online. part in my e-mail
>> address)?
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:uFBYKQqSFHA.3552@.TK2MSFTNGP10.phx.gbl...
>> No, I've tested both ways. This bug was introduced in SP2.
>>
>> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
>> news:ePzjhGpSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>> Setting the DeviceInfo defaults has been added in SQL 2005 RS but is
>> not available with SQL 2000. As for the bug, nothing has changed in
>> this area so I would imagine it happened with SP1 as well.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eoGuJAoSFHA.3720@.TK2MSFTNGP10.phx.gbl...
>> BTW, This bug only happens when requesting the render from the report
>> server. It doesn't happen in VS.NET.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a report that converts expression to Excel formulas. The
>> Excel formulas in a table are incorrectly rendered in a way that the
>> references are all off by 6 or so cells. When I set the "Omit
>> Formulas" device setting to true the values are correct, though there
>> are no formulas. I don't remember this failure in SP1.
>> Thanks,
>> Bryan
>> PS. Is there a way to make "OmitFormulas=true" static when the
>> request is made from the report manager?
>>
>>
>>
>>
>

New Login - SQL Server 2005

I need to create a new login that can only see 2 Views.

I have tried everything, but when I connect to the server with MS Access or MS Excel, and sign in using that Login, I can still a bunch of views. I only want this Login to be able to run either view and not see anything else.

This is what I have done so far:

1) Created Login zzz (w/ SQL Ser Auth)

2) Set the default database to database abc (Nothing is selected in Server Roles)

3) Set User Mapping to database abc, user = zzz

4) Database roll membership is public. (It won't let me change it)

5) Added user zzz to the two Views (Granted Select only)

Now when I connect to the server from Excel

Select new database query, add new data source (connection tests successful)

When I go to select the default table I can see the two views, but I can also see a whole list of other views (all_columns, all_objects, etc..)

It looks like it's everything in the System Views Folder.(Not only can I see the other views, I can query them)

I can individually remove the Public User from each view in the System View folder and it disappears from the default table list..

Do I need to do that for each item in the system views folder? There has to be upwards of 200 views. Anyway to change them all at once?

And then there is the views listed under Information_Schema, the public user isn't setup on those.I can't find a way to get rid of these.

all_columns and all_objects are system catalogs that have catalog security, which means that the user cannot see information for objects he doesn't have access to. The user will be able to query all_columns and he will see results, but those will be results for system objects, not for your application objects. You don't need to add further restrictions, there is really no point to it, because that information is public and the user could obtain it as well by installing his own copy of SQL Server Express and making the same query.

Thanks
Laurentiu

|||I need to setup a user that people will use in conjuction with OutlookSoft, these will be financial users, not techy. I was trying to make it as simple as posiable for them. Sign in, see 2 views, pick one.

Thanks for your help.|||you could use a cursor to loop through all the views in sys.all_objects and deny select permission on all the system views except the 2 you are interested in. That would ensure that only those 2 views are visible.|||Thanks, I will give that a try.

new line in bcp

I am exporting sql server table data to excel using bcp... But if any field
contain carrige return , that fields break up into to next line. Is their
any solution to itOne other way might be to create an ODBC connection to Excel and export
through this data source
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Vikram" <aa@.aa> wrote in message
news:ew#myvQPGHA.1532@.TK2MSFTNGP12.phx.gbl...
> I am exporting sql server table data to excel using bcp... But if any
field
> contain carrige return , that fields break up into to next line. Is their
> any solution to it
>|||If you do not want the carriage returns in the data, create a view
that returns all the columns of the table but removes the carriage
returns from the particular column(s) using REPLACE(). Then BCP out
from the view, instead of from the table.
If you must preserve the carriage returns in the data, don't use BCP.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Mar 2006 14:11:29 +0530, "Vikram" <aa@.aa> wrote:

>I am exporting sql server table data to excel using bcp... But if any field
>contain carrige return , that fields break up into to next line. Is their
>any solution to it|||But I cant use views as I am calling stored procedure from bcp... And stored
procedure return data from temp tables...
Is there is no way by which bcp can preserve carrige return in the data.
Because when we use DTS, it export the data perfectly. I even cannot use DTS
as sp return data from temp table...
Any suggestion will be helpful?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:uubc02t9b70m4qtavu7cohq4vmuvi1oqlr@.
4ax.com...
> If you do not want the carriage returns in the data, create a view
> that returns all the columns of the table but removes the carriage
> returns from the particular column(s) using REPLACE(). Then BCP out
> from the view, instead of from the table.
> If you must preserve the carriage returns in the data, don't use BCP.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Mar 2006 14:11:29 +0530, "Vikram" <aa@.aa> wrote:
>
field|||Vikram (aa@.aa) writes:
> But I cant use views as I am calling stored procedure from bcp... And
> stored procedure return data from temp tables...
You using the queryout option? Anyway, if you are already using temp
tables, you have all possibilities to modify the data.

> Is there is no way by which bcp can preserve carrige return in the data.
I guess the question is not how get BCP to preserve the CR in the data -
it bulks out whatever that is, but how to a file should look like for
Excel to accept it with the newlines preserved. I don't know Excel well
enough to say how the file should look like.
Once you are equipped with that knowledge, you can address this by either
formatting the data when you select it, or use a format file, to have BCP
to do it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

New Import/Export Wizard error

I am making the adjustment from SQL Server 2000 to SQL Server 2005. In the
past I have created named ranges in Excel that look like data tables (header
row followed by multiple data rows) and used DTS to create database tables i
n
SQL Server 2000. It has worked for several years. Now, using the SQL
Server 2005 import/export wizard, the same named ranges cause the following
error.
- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for
component "Source - B11Tbl" (1).
(SQL Server Import and Export Wizard)
Can anyone explain why it 'blows-up' and what I might do to fix it?
Thank you!Hi Mitch
Which version are you running?
John
"Mitch" wrote:

> I am making the adjustment from SQL Server 2000 to SQL Server 2005. In th
e
> past I have created named ranges in Excel that look like data tables (head
er
> row followed by multiple data rows) and used DTS to create database tables
in
> SQL Server 2000. It has worked for several years. Now, using the SQL
> Server 2005 import/export wizard, the same named ranges cause the followin
g
> error.
> - Validating (Error)
> Messages
> Error 0xc00470fe: Data Flow Task: The product level is insufficient f
or
> component "Source - B11Tbl" (1).
> (SQL Server Import and Export Wizard)
> Can anyone explain why it 'blows-up' and what I might do to fix it?
> Thank you!
>|||Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
I hope this gives the version info needed.
Mitch
"John Bell" wrote:
> Hi Mitch
> Which version are you running?
> John
> "Mitch" wrote:
>|||Hi
That does not tell me the edition!
You may want to test if it works with SP1.
John
"Mitch" wrote:
> Microsoft SQL Server Management Studio 9.00.1399.00
> Microsoft Analysis Services Client Tools 2005.090.1399.00
> Microsoft Data Access Components (MDAC) 2000.085.1117.00
> (xpsp_sp2_rtm.040803-2158)
> Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
> Microsoft Internet Explorer 6.0.2900.2180
> Microsoft .NET Framework 2.0.50727.42
> Operating System 5.1.2600
> I hope this gives the version info needed.
> Mitch
> "John Bell" wrote:
>|||Microsoft SQL Server Standard Edition
"John Bell" wrote:
> Hi
> That does not tell me the edition!
> You may want to test if it works with SP1.
> John
> "Mitch" wrote:
>|||Hi Mitch
Then I can't see why you are getting this message! Have you tried this
without the named ranges or a different version of Excel?
John
"Mitch" wrote:
> Microsoft SQL Server Standard Edition
> "John Bell" wrote:
>|||I'll have the IT people check the installation settings for SQL Server 2005.
The 2000 version of DTS still works just fine... The problem is only with
the 2005 version. Thank you for your help!
"John Bell" wrote:
> Hi Mitch
> Then I can't see why you are getting this message! Have you tried this
> without the named ranges or a different version of Excel?
> John
> "Mitch" wrote:
>|||Hi Mitch
SQL 2005 has most thing off by default, but I can't remember having to
change anything special for excel files, although I haven't tried named
ranges.
John
"Mitch" wrote:
> I'll have the IT people check the installation settings for SQL Server 200
5.
> The 2000 version of DTS still works just fine... The problem is only with
> the 2005 version. Thank you for your help!
> "John Bell" wrote:
>|||I get the error message several seconds after identifying the source and it
does not progress (show any more windows) from that point. The message
suggests that it is a Microsoft Jet Database Engine error message. We
watched the server during the attempt (several times) and absolutely nothing
is happening on the server end (SQL Server). My workstation has 2.0 GB RAM
so I doubt that RAM is a problem. The funny part is that the data transfer
works using SQL Server 2000 (which I no longer have installed). Any
suggestions?
"John Bell" wrote:
> Hi Mitch
> SQL 2005 has most thing off by default, but I can't remember having to
> change anything special for excel files, although I haven't tried named
> ranges.
> John
>
> "Mitch" wrote:
>|||Hi
It does sound like a driver issue, I have tested importing named ranges
without any issues.
John
"Mitch" wrote:
> I get the error message several seconds after identifying the source and i
t
> does not progress (show any more windows) from that point. The message
> suggests that it is a Microsoft Jet Database Engine error message. We
> watched the server during the attempt (several times) and absolutely nothi
ng
> is happening on the server end (SQL Server). My workstation has 2.0 GB RA
M
> so I doubt that RAM is a problem. The funny part is that the data transfe
r
> works using SQL Server 2000 (which I no longer have installed). Any
> suggestions?
> "John Bell" wrote:
>