Application Role Usage

I am new to the security part of SQL server and I'm having a problem with
using the application role while running a query from Excel. I wrote the sp
I'm using and it works fine but I can't get the application role to work. I
have set it up in EM but I'm not sure how to put it in the code in Microsoft
query. Do I have to put the exec sp_setapprole 'role', 'password' statement
in the sp or before I execute the procedure. I tried putting it before and i
t
did not work, maybe I had the wrong syntax or something. Below is what I
wrote:
Exec sp_setapprole 'role', 'password'
GO
Exec GetDefectReport
GOThis should work I think...do you get an error message?
Did you give the approle execute permission on the SP (maybe underlying
tables?)
Does the sp return data for Excel to display (I take it, that's what it has
to do...)?
Lee-Z
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:4C25B4B8-FEC4-49C4-9276-F5B0AF2A6FD1@.microsoft.com...
>I am new to the security part of SQL server and I'm having a problem with
> using the application role while running a query from Excel. I wrote the
> sp
> I'm using and it works fine but I can't get the application role to work.
> I
> have set it up in EM but I'm not sure how to put it in the code in
> Microsoft
> query. Do I have to put the exec sp_setapprole 'role', 'password'
> statement
> in the sp or before I execute the procedure. I tried putting it before and
> it
> did not work, maybe I had the wrong syntax or something. Below is what I
> wrote:
> Exec sp_setapprole 'role', 'password'
> GO
> Exec GetDefectReport
> GO|||The error message is that the syntax is wrong around GO. I wrote the sp in
the query analyzer and now i am calling it from Excel using Microsoft Query.
"Lee-Z" wrote:

> This should work I think...do you get an error message?
> Did you give the approle execute permission on the SP (maybe underlying
> tables?)
> Does the sp return data for Excel to display (I take it, that's what it ha
s
> to do...)?
> Lee-Z
>
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:4C25B4B8-FEC4-49C4-9276-F5B0AF2A6FD1@.microsoft.com...
>
>|||have never tried approle with MS Query, but try to execute the sp_SetAppRole
statement in menu "File"-> "Execute SQL" from MS-Query (leave out the GO
part). Make sure you select your database in the dropdown box.
After that you should be able to get data from your original query (leave
out the "GO" here as well)...
good luck
Lee-Z
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:1FDE810C-6F0F-4E7C-8537-E5987C88D297@.microsoft.com...[vbcol=seagreen]
> The error message is that the syntax is wrong around GO. I wrote the sp in
> the query analyzer and now i am calling it from Excel using Microsoft
> Query.
> "Lee-Z" wrote:
>|||You are not going to be able to use Microsoft Query and get
application roles to perform reliably. Application roles are
connection-specific, and the tools open additonal connections under
the covers in order to speed up connections. This is an issue even if
you code data access in ADO code (which is the usual way to go about
it). The following article, "SQL application role errors with OLE DB
resource pooling" describes the problem and the workaround:
http://support.microsoft.com/defaul...b;en-us;Q229564
--Mary
On Fri, 12 Aug 2005 06:59:20 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The error message is that the syntax is wrong around GO. I wrote the sp in
>the query analyzer and now i am calling it from Excel using Microsoft Query
.
>"Lee-Z" wrote:
>|||Thanks for the help Lee and Mary
"Mary Chipman [MSFT]" wrote:

> You are not going to be able to use Microsoft Query and get
> application roles to perform reliably. Application roles are
> connection-specific, and the tools open additonal connections under
> the covers in order to speed up connections. This is an issue even if
> you code data access in ADO code (which is the usual way to go about
> it). The following article, "SQL application role errors with OLE DB
> resource pooling" describes the problem and the workaround:
> http://support.microsoft.com/defaul...b;en-us;Q229564
> --Mary
> On Fri, 12 Aug 2005 06:59:20 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
>
>

Application Interface

I would like to call reports from a Delphi 7.0 Application, and direct the
output to "Print, PDF, Excel or Email".
Any suggestions or articles would be helpful, so as not to reinvent the
wheel.
Thanx in advance,
Greg RowlandTake a look at this BOL topic:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_overview_v1_31pv.asp.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg Rowland" <greg@.waveltd.com> wrote in message
news:eDMpNqOaEHA.596@.TK2MSFTNGP11.phx.gbl...
> I would like to call reports from a Delphi 7.0 Application, and direct the
> output to "Print, PDF, Excel or Email".
> Any suggestions or articles would be helpful, so as not to reinvent the
> wheel.
> Thanx in advance,
> Greg Rowland
>

Append into EXCEL file from report

Is it possible to append data from the report into an exising Excel file?
ThanksNope.
Amarnath
"Mark Goldin" wrote:
> Is it possible to append data from the report into an exising Excel file?
> Thanks
>
>

append date to exporting file name in SQL reporting service

When exporting a report to another format, say excel,PDF; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to pdf, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services ?

example : Report name : Testreport

Exported file should be : Testingreport-November-22-2007.pdf

please help me in this

Thanks
sukuTry here:

http://www.codeproject.com/sqlrs/PDFUsingSQLRepServices.asp|||I would suggest placing the timestamp inside of the report.
ie. "Report was generated November 22 2007 11:01am".
This way the client cannot change the "report date".

Because it is a PDF and thus you won't be editing the document you can sort it by "Date Modified" in explorer if you want to sort the files.

append date to exporting file name in SQL reporting service

When exporting a report to another format, say excel,PDF; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to pdf, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services ?

example : Report name : Testreport

Exported file should be : Testingreport-November-22-2007.pdf

please help me in this

Thanks
suku

Quote:

Originally Posted by sukumaster

When exporting a report to another format, say excel,PDF; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to pdf, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services ?

example : Report name : Testreport

Exported file should be : Testingreport-November-22-2007.pdf

please help me in this

Thanks
suku

Please post the over all operation you are trying to perform?
Are you trying to use shell script to ftp the file to any other server?
If Yes, then renaming a file would become easy!!

append data to an excel file,

I have a DTS that exports a view to an excel file, is it possible to append
data to an excel file, it seems if it sees that the sheet is available it
creates a new sheet,
I have a logo in my excel file and I want to append the rows below it.JIM
create procedure file_write_text(@.file_name varchar(1000),
@.text varchar(8000),
@.append bit = 0)
as
/*
exec file_write_text 'c:\TEST.XLS','hello'
exec file_write_text 'c:\TEST.XLS',' world',1
*/
declare @.fso int
declare @.ts int
declare @.rv int
exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
if @.rv <> 0 goto eh
if @.append = 1
begin
--open the text stream for append, will fail if the file doesn't exist
exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
if @.rv <> 0 goto eh
end
else
begin
--create a new text file, overwriing if necessary
exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT, @.file_name, -1
if @.rv <> 0 goto eh
end
exec @.rv = sp_oamethod @.ts,"write",null ,@.text
if @.rv <> 0 goto eh
exec @.rv = sp_oamethod @.ts,"close"
if @.rv <> 0 goto eh
exec sp_oadestroy @.ts
exec sp_oadestroy @.fso
return 0
eh:
declare @.es varchar(512)
declare @.ed varchar(512)
exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
raiserror(@.ed,16,1)
exec sp_oadestroy @.ts
exec sp_oadestroy @.fso
return 1
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
>I have a DTS that exports a view to an excel file, is it possible to append
> data to an excel file, it seems if it sees that the sheet is available it
> creates a new sheet,
> I have a logo in my excel file and I want to append the rows below it.
>|||Thanks for the reply Uri, is this for text file or Excel file (xls, not csv)
?
"Uri Dimant" wrote:

> JIM
> create procedure file_write_text(@.file_name varchar(1000),
> @.text varchar(8000),
> @.append bit = 0)
> as
> /*
> exec file_write_text 'c:\TEST.XLS','hello'
> exec file_write_text 'c:\TEST.XLS',' world',1
> */
> declare @.fso int
> declare @.ts int
> declare @.rv int
> exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
> if @.rv <> 0 goto eh
> if @.append = 1
> begin
> --open the text stream for append, will fail if the file doesn't exist
> exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
> if @.rv <> 0 goto eh
> end
> else
> begin
> --create a new text file, overwriing if necessary
> exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT, @.file_name, -1
> if @.rv <> 0 goto eh
> end
> exec @.rv = sp_oamethod @.ts,"write",null ,@.text
> if @.rv <> 0 goto eh
> exec @.rv = sp_oamethod @.ts,"close"
> if @.rv <> 0 goto eh
> exec sp_oadestroy @.ts
> exec sp_oadestroy @.fso
> return 0
> eh:
> declare @.es varchar(512)
> declare @.ed varchar(512)
> exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
> raiserror(@.ed,16,1)
> exec sp_oadestroy @.ts
> exec sp_oadestroy @.fso
> return 1
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
>
>|||It should be doable for both types
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D751008B-0C20-4F1E-A07C-FA6108F2CD08@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply Uri, is this for text file or Excel file (xls, not
> csv)?
>
> "Uri Dimant" wrote:
>

append data to an excel file,

I have a DTS that exports a view to an excel file, is it possible to append
data to an excel file, it seems if it sees that the sheet is available it
creates a new sheet,
I have a logo in my excel file and I want to append the rows below it.JIM
create procedure file_write_text(@.file_name varchar(1000),
@.text varchar(8000),
@.append bit = 0)
as
/*
exec file_write_text 'c:\TEST.XLS','hello'
exec file_write_text 'c:\TEST.XLS',' world',1
*/
declare @.fso int
declare @.ts int
declare @.rv int
exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
if @.rv <> 0 goto eh
if @.append = 1
begin
--open the text stream for append, will fail if the file doesn't exist
exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
if @.rv <> 0 goto eh
end
else
begin
--create a new text file, overwriing if necessary
exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT, @.file_name, -1
if @.rv <> 0 goto eh
end
exec @.rv = sp_oamethod @.ts,"write",null ,@.text
if @.rv <> 0 goto eh
exec @.rv = sp_oamethod @.ts,"close"
if @.rv <> 0 goto eh
exec sp_oadestroy @.ts
exec sp_oadestroy @.fso
return 0
eh:
declare @.es varchar(512)
declare @.ed varchar(512)
exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
raiserror(@.ed,16,1)
exec sp_oadestroy @.ts
exec sp_oadestroy @.fso
return 1
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
>I have a DTS that exports a view to an excel file, is it possible to append
> data to an excel file, it seems if it sees that the sheet is available it
> creates a new sheet,
> I have a logo in my excel file and I want to append the rows below it.
>|||Thanks for the reply Uri, is this for text file or Excel file (xls, not csv)?
"Uri Dimant" wrote:
> JIM
> create procedure file_write_text(@.file_name varchar(1000),
> @.text varchar(8000),
> @.append bit = 0)
> as
> /*
> exec file_write_text 'c:\TEST.XLS','hello'
> exec file_write_text 'c:\TEST.XLS',' world',1
> */
> declare @.fso int
> declare @.ts int
> declare @.rv int
> exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
> if @.rv <> 0 goto eh
> if @.append = 1
> begin
> --open the text stream for append, will fail if the file doesn't exist
> exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
> if @.rv <> 0 goto eh
> end
> else
> begin
> --create a new text file, overwriing if necessary
> exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT, @.file_name, -1
> if @.rv <> 0 goto eh
> end
> exec @.rv = sp_oamethod @.ts,"write",null ,@.text
> if @.rv <> 0 goto eh
> exec @.rv = sp_oamethod @.ts,"close"
> if @.rv <> 0 goto eh
> exec sp_oadestroy @.ts
> exec sp_oadestroy @.fso
> return 0
> eh:
> declare @.es varchar(512)
> declare @.ed varchar(512)
> exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
> raiserror(@.ed,16,1)
> exec sp_oadestroy @.ts
> exec sp_oadestroy @.fso
> return 1
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
> >I have a DTS that exports a view to an excel file, is it possible to append
> > data to an excel file, it seems if it sees that the sheet is available it
> > creates a new sheet,
> > I have a logo in my excel file and I want to append the rows below it.
> >
>
>|||It should be doable for both types
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D751008B-0C20-4F1E-A07C-FA6108F2CD08@.microsoft.com...
> Thanks for the reply Uri, is this for text file or Excel file (xls, not
> csv)?
>
> "Uri Dimant" wrote:
>> JIM
>> create procedure file_write_text(@.file_name varchar(1000),
>> @.text varchar(8000),
>> @.append bit = 0)
>> as
>> /*
>> exec file_write_text 'c:\TEST.XLS','hello'
>> exec file_write_text 'c:\TEST.XLS',' world',1
>> */
>> declare @.fso int
>> declare @.ts int
>> declare @.rv int
>> exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
>> if @.rv <> 0 goto eh
>> if @.append = 1
>> begin
>> --open the text stream for append, will fail if the file doesn't exist
>> exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
>> if @.rv <> 0 goto eh
>> end
>> else
>> begin
>> --create a new text file, overwriing if necessary
>> exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT,
>> @.file_name, -1
>> if @.rv <> 0 goto eh
>> end
>> exec @.rv = sp_oamethod @.ts,"write",null ,@.text
>> if @.rv <> 0 goto eh
>> exec @.rv = sp_oamethod @.ts,"close"
>> if @.rv <> 0 goto eh
>> exec sp_oadestroy @.ts
>> exec sp_oadestroy @.fso
>> return 0
>> eh:
>> declare @.es varchar(512)
>> declare @.ed varchar(512)
>> exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
>> raiserror(@.ed,16,1)
>> exec sp_oadestroy @.ts
>> exec sp_oadestroy @.fso
>> return 1
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
>> >I have a DTS that exports a view to an excel file, is it possible to
>> >append
>> > data to an excel file, it seems if it sees that the sheet is available
>> > it
>> > creates a new sheet,
>> > I have a logo in my excel file and I want to append the rows below it.
>> >
>>

append data to an excel file

I have a DTS that exports a view to an excel file, is it possible to append data to an excel file, it seems if it sees that the sheet is available it creates a new sheet,

I have a logo in my excel file and I want to append the rows below it.

If you're looking to populate a Worksheet with data at a specific location I think you will probably need to use Excel automation instead of a batch or data access method. See the following for more info:

How to transfer data to an Excel workbook by using Visual Basic .NET

Anyway to Stop DSN & DSNless connections to SQL Server?

I'm trying to setup a new server but need to stop people accessing the database via Access, Excel etc

Is there any way to set SQL server up to stop connections from such applications?

Is there any way to allow only certain users to connect via a DSN

We are not using windows authentication on the server

Cheers

No, you cannot restrict connections based on the application that made them. But you can restrict connections based on the account they were made with. You can try to have your application connect to SQL Server using a specific account and not grant any connection rights to the users - then they would only be able to access the database through the application. Of course, if they would debug the application, they could figure out how it connects to SQL Server (the account and the password), but this would at least prevent accidental connections from other applications.

Thanks
Laurentiu

Anytime I connect to an Excel file from SSIS05, I start getting endemic system.outofmemoryexcept

This is my error text... how do I get this nonsense to stop?!

===================================

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.IO.TextWriter.Write(Char[] buffer)
at System.IO.TextWriter.Write(String value)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

Can we get some more information? Is this when you connect to any Excel file, or to one particular one?

If this only happens when connecting to a specific file, how much data is being returned?

Is there any information in task manager that you can give us to help? How much memory is available, or in use? Do you see memory usage spiking in a particular process when connecting to Excel?

Donald

Anyone using Excel 2007 as client ?

Overall pretty cool, but apparently you can't select a single level of a dimension in the pivot table.

For instance, I have level Year, Quarter, Month, Week, Day.

Having to look at last week's sales per day, I'd be selecting a specific week and I'd have the Day level only as row labels.

This worked in Excel 2003, but I can't figure out how to leave out the Year, Q, M and Week levels.

In the PivotTable Field List, you have a check box next to the Time dimension, but nothing next to each level in the dimension. So you have to have all levels of the dimension in the rows of the pivot.

This means a lot of useless drillthrough for the users as they go down from Year to Quarter to Month to Week to Days.

Am I missing something ?

In Excel 2007 you can display the Day level only by 'hiding' the other levels. If you drill down to the lowest level day, right click on any item | Show/Hide Fields | Uncheck the Year level - the year level is hidden. Repeat for the other levels you want to hide.

Hope this helps!

|||

Thanks Zaheera it does help, that's what I needed.

I think this is a feature that needs improvement, since I need 17 clicks before I have drilled down to Days, and then removed the levels one by one.

|||

To accomplish this in fewer clicks - you can right click on an item in the highest level | Expand/Collapse | Expand to <lowest level>. In one-click you've drilled down to the lowest level. Now you'll need to hide each level one by one as described above (which can be a pain - I agree). Or you can do this in one-step in VBA (Alt + F11 to open the editor, Cntrl + G to go to the immediate window): Activecell.PivotField.CubeField.HiddenLevels = 3 and the top 3 levels will be hidden at once.