Can I back up a DataSource

I am setting up all my reports to use a shared data source which will have
its "Credentials stored securely in the report server". The datasource uses a
windows account which has the "Use as Windows credentials when connecting to
the data source" option set. The account used will have exec rights on the
appropriate stored procedures to return the data.
My question is: is there any way that I can back up this data source so that
I can restore it in the case of a server rebuild or would I have to manually
re-create it?Report Server stores everything (including the rdl for the reports) in the
database. If you backup the SQL database ReportServer you should be in good
shape.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:0061CEB5-A5C2-4FF0-B9AD-B234FDFBB507@.microsoft.com...
> I am setting up all my reports to use a shared data source which will have
> its "Credentials stored securely in the report server". The datasource
uses a
> windows account which has the "Use as Windows credentials when connecting
to
> the data source" option set. The account used will have exec rights on the
> appropriate stored procedures to return the data.
> My question is: is there any way that I can back up this data source so
that
> I can restore it in the case of a server rebuild or would I have to
manually
> re-create it?
>
>

Can I avoid slammer attack without install sp3 for sql server 2k

Hi,

I guess there is some problem setting up transactional replication
after i install sp3 for sql server 2k. I get primary key violation in sp_MSget_repl_commands. So I need to revert back to sp2. But what should i do to avoid slammer attack with sp2 installed on my server

Thanks
Nikhil.This is a known issue. You should contact PSS and ask them to look up
SRX030113606317.

Can i add SQL statement to Parameter Fields?

Hi,
I am new to Crystal Reports. May i know can i add SQL statement to parameter fields under setting default values?
WillyYes, you can add SQL statement, but the statement, is a litlle diferent... not much.
Menu -> Report -> Select Expert ->
Click Show formula >>> then hit the button Formula editor...

Hi,

I am new to Crystal Reports. May i know can i add SQL statement to parameter fields under setting default values?

Willy|||Thanks a lot

Can columns be added to an error output?

When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?

I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.

IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;

// Add extra column here, e.g. ErrorMessage

Answering my own question. The answer is 'YES', you can enhance the error output.

You can add the columns as follows in ProvideComponentProperties. The part I was missing was since the error output was synchronous, the column index needed to be looked in in the input buffer, not the output buffer. Native SQL ADO destination adapter error codes are much more convenient then the next to useless error codes produced by the OLEDB destination adapter.

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

// In ProvideComponentProperties()
// Add error message to error output column collection
// do so after the call to .IsErrorOut, to ensure that ErrorCode and ErrorColumn
// are added first for consistency
IDTSOutputColumnCollection90 outputColumnCollection =
outError.OutputColumnCollection;
IDTSOutputColumn90 outputColumn = outputColumnCollection.New();
outputColumn.Name = ERR_MESSAGE_COLUMN_NAME;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0);

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

// In PreExecute()
// Get the input and the external column collection
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSExternalMetadataColumnCollection90 externalcols =
input.ExternalMetadataColumnCollection;

// Deterine index of error Message column
IDTSOutput90 output = ComponentMetaData.OutputCollection["Error Output"];
IDTSOutputColumnCollection90 outputColumnCollection =
output.OutputColumnCollection;
errMessageColumnIndex = BufferManager.FindColumnByLineageID(
input.Buffer, outputColumnCollection[ERR_MESSAGE_COLUMN_NAME].LineageID);

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

// In ProcessInput(int inputID, PipelineBuffer buffer)
if (m_rowdisp == DTSRowDisposition.RD_RedirectRow)
{
#region set native error code and message
SqlException sqlEx = (e as SqlException);
if (sqlEx != null) {
// Retrieve the native SqlException error code
errorCode = sqlEx.Number;
}
if (String.IsNullOrEmpty(sqlEx.Message))
buffer.SetNull(errMessageColumnIndex);
else
errorMessage = sqlEx.Message;
// Retrieve and load the native SqlException message
buffer[errMessageColumnIndex] = (errorMessage.Length <= 250 ?
errorMessage : errorMessage.Substring(0, 250));
#endregion

buffer.DirectErrorRow(errorOutputID, errorCode, iCol);
}

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

|||

Can you give me more details on this. I will wait to see if you reply beofre I elaborate..

It sounds like this is something I am looking to do based on my post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509511&SiteID=1

|||

Rob,

The code above works for a custom component. If you want to implement your functionality as a custom component then it will work. In the other thread that you linked to you said you were attempting this i a script component - and that is a slightly different kettle of fish.

-Jamie

|||

Wow, you are everywhere Jamie. Yes, now that I see what he was doing, you are correct. Not sure where to go now, but we will continue to develop this process. and I will check back here and some other places.

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

|||

ronemac wrote:

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

If they were to do it (and that's a huge if) then the earliest you could expect it is in the next release of SQL Server. That is due to be Summer 2008.

-Jamie

Can columns be added to an error output?

When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?

I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.

IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;

// Add extra column here, e.g. ErrorMessage

Answering my own question. The answer is 'YES', you can enhance the error output.

You can add the columns as follows in ProvideComponentProperties. The part I was missing was since the error output was synchronous, the column index needed to be looked in in the input buffer, not the output buffer. Native SQL ADO destination adapter error codes are much more convenient then the next to useless error codes produced by the OLEDB destination adapter.

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

// In ProvideComponentProperties()
// Add error message to error output column collection
// do so after the call to .IsErrorOut, to ensure that ErrorCode and ErrorColumn
// are added first for consistency
IDTSOutputColumnCollection90 outputColumnCollection =
outError.OutputColumnCollection;
IDTSOutputColumn90 outputColumn = outputColumnCollection.New();
outputColumn.Name = ERR_MESSAGE_COLUMN_NAME;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0);

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

// In PreExecute()
// Get the input and the external column collection
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSExternalMetadataColumnCollection90 externalcols =
input.ExternalMetadataColumnCollection;

// Deterine index of error Message column
IDTSOutput90 output = ComponentMetaData.OutputCollection["Error Output"];
IDTSOutputColumnCollection90 outputColumnCollection =
output.OutputColumnCollection;
errMessageColumnIndex = BufferManager.FindColumnByLineageID(
input.Buffer, outputColumnCollection[ERR_MESSAGE_COLUMN_NAME].LineageID);

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

// In ProcessInput(int inputID, PipelineBuffer buffer)
if (m_rowdisp == DTSRowDisposition.RD_RedirectRow)
{
#region set native error code and message
SqlException sqlEx = (e as SqlException);
if (sqlEx != null) {
// Retrieve the native SqlException error code
errorCode = sqlEx.Number;
}
if (String.IsNullOrEmpty(sqlEx.Message))
buffer.SetNull(errMessageColumnIndex);
else
errorMessage = sqlEx.Message;
// Retrieve and load the native SqlException message
buffer[errMessageColumnIndex] = (errorMessage.Length <= 250 ?
errorMessage : errorMessage.Substring(0, 250));
#endregion

buffer.DirectErrorRow(errorOutputID, errorCode, iCol);
}

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

|||

Can you give me more details on this. I will wait to see if you reply beofre I elaborate..

It sounds like this is something I am looking to do based on my post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509511&SiteID=1

|||

Rob,

The code above works for a custom component. If you want to implement your functionality as a custom component then it will work. In the other thread that you linked to you said you were attempting this i a script component - and that is a slightly different kettle of fish.

-Jamie

|||

Wow, you are everywhere Jamie. Yes, now that I see what he was doing, you are correct. Not sure where to go now, but we will continue to develop this process. and I will check back here and some other places.

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

|||

ronemac wrote:

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

If they were to do it (and that's a huge if) then the earliest you could expect it is in the next release of SQL Server. That is due to be Summer 2008.

-Jamie

Can anyone help with waittype 0x0044?

Hi,

I wonder if anyone can shed any light on the following as i just can't
explain it.

A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.

The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.

There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.

In sysprocess it showing the following :-

spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732

the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-

select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

gives results :-

613151115052100x0000000008624080

I just can't explain why it is so slow when nothing else is ruuning.

Anyone have any ideas on what i can check on?

Thanks

Ian.ianwr (ianwrigglesworth@.yahoo.co.uk) writes:

Quote:

Originally Posted by

A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.

Would the update cause the rows to grow? For instance, if this is
a new column that was added as nullable, and is now being populated?
In that case the table will need to grow, and could take some time.
Not the least if the data file has to grow as well.

Quote:

Originally Posted by

There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.
>
In sysprocess it showing the following :-
>
spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732

In sys.dm_exec_requests there is a wait_type which is likely to be
more informative than 0x0044.

Quote:

Originally Posted by

Anyone have any ideas on what i can check on?

Obviously a

SELECT COUNT(*) FROM tbl (NOLOCK) WHERE col <expected value

will you some progress information.

--
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|||Hi Erland,

Thanks for the view to use, unfortunately when i arrived this morning
the task had stopped and took about 29 hours to run.

Going to keep an eye on things and check out the san as well today,
Thanks for the info anyway, if it happens again i'll repost

Thanks

Ian,

Can Anyone help us

Hi,
I encounter a problem when setting a subscription in rs(with sp2)
:
Failure sending mail: The server rejected the sender address. The
server response was: 503 Authentication needed for local user.
this is my configuration in rsreportserver.config:
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>mail.gillion.com.cn</SMTPServer>
<SMTPServerPort>25</SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing></SendUsing>
<SMTPAuthenticate>0</SMTPAuthenticate>
<From>houlh@.gillion.com.cn</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
IS anythig wrong?
thx a lot!It sounds like your SMTP server requires authentication. RS only support
anonymous and NTLM authentication. If you server uses NTLM then you need to
make sure the user that the ReportServer service runs under has permission
to send mail. If you server requires basic auth then you can use the local
SMTP server to relay the messages. Set the SMTPServerPickupDirectory to the
local SMTP's pickup directory and the SendUsing element to 1. Then
configure the local SMTP server to relay the messages.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"weiyf" <weiyf@.gillion.com.cn> wrote in message
news:%23oQUFspYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I encounter a problem when setting a subscription in rs(with sp2)
> :
> Failure sending mail: The server rejected the sender address. The
> server response was: 503 Authentication needed for local user.
> this is my configuration in rsreportserver.config:
> <Configuration>
> <RSEmailDPConfiguration>
> <SMTPServer>mail.gillion.com.cn</SMTPServer>
> <SMTPServerPort>25</SMTPServerPort>
> <SMTPAccountName></SMTPAccountName>
> <SMTPConnectionTimeout></SMTPConnectionTimeout>
> <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
> <SMTPUseSSL></SMTPUseSSL>
> <SendUsing></SendUsing>
> <SMTPAuthenticate>0</SMTPAuthenticate>
> <From>houlh@.gillion.com.cn</From>
> <EmbeddedRenderFormats>
> <RenderingExtension>MHTML</RenderingExtension>
> </EmbeddedRenderFormats>
> <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> </ExcludedRenderFormats>
> <SendEmailToUserAlias>True</SendEmailToUserAlias>
> <DefaultHostName></DefaultHostName>
> <PermittedHosts></PermittedHosts>
> </RSEmailDPConfiguration>
> </Configuration>
> IS anythig wrong?
> thx a lot!
>

Calling Webservice

Hi,
I have set the Reporting WebServices WebService Directory Security in
IIS to Windows Authentication. The authentication setting in the
config file is set to "Windows" with Impersonation set to "true".
I created an account called "RSAdminUser" with which to call the
webservice.
When calling the "ListReportsUsingDataSource" method, after setting
the credentials to this "RSAdminUser", I get the following error:
"System.Web.Services.Protocols.SoapException: The permissions granted
to user 'SERVER\RSAdminUser' are insufficient for performing this
operation. --> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
The permissions granted to user 'SERVER\RSAdminUser' are insufficient
for performing this operation. at
Microsoft.ReportingServices.Library.RSService.ListReportsUsingDataSource(String
path) at Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
DataSource, CatalogItem[]& Reports) -- End of inner exception stack
trace -- at Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
DataSource, CatalogItem[]& Reports)"
I have methodically given this user permissions to all of the relevant
folders and files that I can think of, but the only thing that makes
this work is making "RSAdminUser" a member of the "Administrators"
group. Is it possible that only members of the "Administrators" group
are allowed to execute this method?
Is there an alternative solution? What permissions does this account
need to execute this method?
Thanks in advance,
AmyHi Amy:
Reporting services uses roles based authorization, and the default
setup only places the local administrator into a role.
While RSAdminUser might have rights to access all the files and
folders in the reporting services installation, you still have to put
the user into a "role". Roles are how RS determines who can browse a
folder, who can view a report, who can manage a data source etc.
From your reports home page you can go to Site Settings -> Configure
site wide security (link is at the bottom). Perhaps you should define
a new role for RSAdminUser and grant just enough authorization for the
user to get the things done you need.
Hope this helps,
--
Scott
http://www.OdeToCode.com
On 3 Aug 2004 05:04:48 -0700, amydudley@.webmail.co.za (Amy) wrote:
>Hi,
>I have set the Reporting WebServices WebService Directory Security in
>IIS to Windows Authentication. The authentication setting in the
>config file is set to "Windows" with Impersonation set to "true".
>I created an account called "RSAdminUser" with which to call the
>webservice.
>When calling the "ListReportsUsingDataSource" method, after setting
>the credentials to this "RSAdminUser", I get the following error:
>"System.Web.Services.Protocols.SoapException: The permissions granted
>to user 'SERVER\RSAdminUser' are insufficient for performing this
>operation. --> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
>The permissions granted to user 'SERVER\RSAdminUser' are insufficient
>for performing this operation. at
>Microsoft.ReportingServices.Library.RSService.ListReportsUsingDataSource(String
>path) at Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
>DataSource, CatalogItem[]& Reports) -- End of inner exception stack
>trace -- at Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
>DataSource, CatalogItem[]& Reports)"
>I have methodically given this user permissions to all of the relevant
>folders and files that I can think of, but the only thing that makes
>this work is making "RSAdminUser" a member of the "Administrators"
>group. Is it possible that only members of the "Administrators" group
>are allowed to execute this method?
>Is there an alternative solution? What permissions does this account
>need to execute this method?
>Thanks in advance,
>Amy