Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJosql

Create SnapShop

Hello,

I'm using Reporting Services 2005.

Is there any way to create a snapshot report from the report ?

While user generated the report he would like to save the current report as a snapshot (The report contain several parameters).

Is there any way pass to the report URL reqest to generate this report as snapshot ?

Thanks

Idan

I don't use snapshots that often so sorry that I can't give you a direct answer.

But you should read these usefull information about snapshots (hopefully it helps!)
Report snapshots: http://msdn2.microsoft.com/en-us/library/ms157204.aspx
Schedule a Report Snapshot (Report Manager): http://msdn2.microsoft.com/en-us/library/ms159217.aspx

|||Thanks but I know this Q and it doesn't help|||

This is not possible. Snapshot generation is always based on the report's default parameters, you can not pass in parameters to generate a snapshot.

You could use the viewer control to simiulate this, but it would mean changing the report's defaults before calling CreateReportExecutionSnapshot, and the user would have to have the correct permission.

|||

In the report URL I can pass the parameters information so why I can't pass option to create the snapshot ?

While use generate the report and see he would like to save this report in the history snapshot why not give him an option to do that

|||The feature has been requested quite a bit and is on our wish list for future enhancements.

Create Report Snapshot from SSIS

I need to create a SSRS report snapshot at the end of an SSIS package (seems like this would be a common task). I tried connecting to the ReportingService web service to do this but I get the following error when after I select the web service in the Web Service Task:

This version of the Web Services Description Language (WSDL) is not supported.

Is there as simple way to do what I want?

The Web Service task has some limitations in respect to what WSDL it can consume. Put bluntly it is not much use in practice.

The easiest way to do this would be to write a custom task. You could do this in a script task, but generating the proxy class from WSDL and installing it for use by the Script Task would be almost as much work as just doing it all in a custom task. There are only a few lines of code required to actually call the methods, it is not hard.

|||

I'm interested in doing the exact same thing. Can you provide a sample or template for how to do this? By writting a custom task, do I still have to create the proxy? What would the custom task look like?

Thanks for your help!

|||

See the ReportingService2005.UpdateReportExecutionSnapshot method.

The proxy class is just WS wrapper class, if you write a custom task, then I would expect you to add the web reference directly into you project, there is no point in creating a proxy. The Script task does not allow you to add web references, so it is a must in that case.

Create Report Snapshot from SSIS

I need to create a SSRS report snapshot at the end of an SSIS package (seems like this would be a common task). I tried connecting to the ReportingService web service to do this but I get the following error when after I select the web service in the Web Service Task:

This version of the Web Services Description Language (WSDL) is not supported.

Is there as simple way to do what I want?

The Web Service task has some limitations in respect to what WSDL it can consume. Put bluntly it is not much use in practice.

The easiest way to do this would be to write a custom task. You could do this in a script task, but generating the proxy class from WSDL and installing it for use by the Script Task would be almost as much work as just doing it all in a custom task. There are only a few lines of code required to actually call the methods, it is not hard.

|||

I'm interested in doing the exact same thing. Can you provide a sample or template for how to do this? By writting a custom task, do I still have to create the proxy? What would the custom task look like?

Thanks for your help!

|||

See the ReportingService2005.UpdateReportExecutionSnapshot method.

The proxy class is just WS wrapper class, if you write a custom task, then I would expect you to add the web reference directly into you project, there is no point in creating a proxy. The Script task does not allow you to add web references, so it is a must in that case.

Create Report Snapshot from SSIS

I need to create a SSRS report snapshot at the end of an SSIS package (seems like this would be a common task). I tried connecting to the ReportingService web service to do this but I get the following error when after I select the web service in the Web Service Task:

This version of the Web Services Description Language (WSDL) is not supported.

Is there as simple way to do what I want?

The Web Service task has some limitations in respect to what WSDL it can consume. Put bluntly it is not much use in practice.

The easiest way to do this would be to write a custom task. You could do this in a script task, but generating the proxy class from WSDL and installing it for use by the Script Task would be almost as much work as just doing it all in a custom task. There are only a few lines of code required to actually call the methods, it is not hard.

|||

I'm interested in doing the exact same thing. Can you provide a sample or template for how to do this? By writting a custom task, do I still have to create the proxy? What would the custom task look like?

Thanks for your help!

|||

See the ReportingService2005.UpdateReportExecutionSnapshot method.

The proxy class is just WS wrapper class, if you write a custom task, then I would expect you to add the web reference directly into you project, there is no point in creating a proxy. The Script task does not allow you to add web references, so it is a must in that case.

Create local snapshot replication for sql 2005 failed

I tried to create a local snapshot replication from data A to database
B for sql 2005. I followed the wizard and it was created successfully.
But,nothing written to the replication folder and the job failed.
I manually executed the sqls and it always failed on
sp_addpublication_snapshot and the error was:
'DB4\Administrator' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
I log in to windows 2003 as administrator and the replication account
id dbsnap. What I have to do to avoid the error?
Is there a detailed step-by=step guide to create a snapshot
replication?
Can someone provide a set of sqls that I can just use to create a local
(or remote) snapshot?
Thanks,
Andy
The scripts are:
use [T2]
exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
@.value = N'true'
GO
-- Adding the snapshot publication
use [T2]
exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
publication of
database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
@.retention = 0,
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
N'true',
@.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot =
N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
= N'active',
@.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false',
@.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
@.allow_dts = N'false',
@.replicate_ddl = 1
GO
exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
1,
@.frequency_interval = 0, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 0, @.frequency_subday_interval = 0,
@.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.active_start_date = 0,
@.active_end_date = 0, @.job_login =
N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
@.publisher_login =
N'sa', @.publisher_password = N'chang5911'
use [T2]
exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
@.source_owner =
N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
@.description = null,
@.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x000000000803509D,
@.identityrangemanagementoption = N'manual', @.destination_table =
N'RETURN_REASON',
@.destination_owner = N'dbo', @.vertical_partition = N'false'
GO
Can you make the admin account( 'DB4\Administrator' ) part of the sysadmin
role on the publisher?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1168789205.120731.163890@.51g2000cwl.googlegro ups.com...
>I tried to create a local snapshot replication from data A to database
> B for sql 2005. I followed the wizard and it was created successfully.
> But,nothing written to the replication folder and the job failed.
> I manually executed the sqls and it always failed on
> sp_addpublication_snapshot and the error was:
> 'DB4\Administrator' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
> I log in to windows 2003 as administrator and the replication account
> id dbsnap. What I have to do to avoid the error?
> Is there a detailed step-by=step guide to create a snapshot
> replication?
> Can someone provide a set of sqls that I can just use to create a local
> (or remote) snapshot?
> Thanks,
> Andy
>
> The scripts are:
> use [T2]
> exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> @.value = N'true'
> GO
> -- Adding the snapshot publication
> use [T2]
> exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> publication of
> database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> @.retention = 0,
> @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
> N'true',
> @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot =
> N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_subscription_copy = N'false',
> @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> = N'active',
> @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false',
> @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> @.allow_dts = N'false',
> @.replicate_ddl = 1
> GO
>
> exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
> 1,
> @.frequency_interval = 0, @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0,
> @.frequency_subday = 0, @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0,
> @.active_end_time_of_day = 235959, @.active_start_date = 0,
> @.active_end_date = 0, @.job_login =
> N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> @.publisher_login =
> N'sa', @.publisher_password = N'chang5911'
>
> use [T2]
> exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> @.source_owner =
> N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> @.description = null,
> @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x000000000803509D,
> @.identityrangemanagementoption = N'manual', @.destination_table =
> N'RETURN_REASON',
> @.destination_owner = N'dbo', @.vertical_partition = N'false'
> GO
>