Is there any way of Migrating reports from one server to another programmatically thru VB.NET?

Hi,
I wanna know, is there any way of transfering/migrating reports from source server to destination server along with model references programmatically using VB.NET?
If there is a way, can anyone please give me a VB.NET Sample code to achieve my goal?

I will be grateful if anyone help me in this regard!!

Thanks in Advance
Shaun

This isn't exactly what you asked for, but it'll get you started. There are two samples -- One to publish the report and fix up it's data source, and another to grab the model definition and publish it to a server.

In the first sample, you'll need to change the part where the report defintion is loaded from a file to use a call to GetReportDefinition() (http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.getreportdefinition.aspx)

Later on in the first sample, the the Data Source reference should point to your model instead of the data source in the code itself.

In the second sample, the code reads a model and then republishes it to the *same* server. Create another instance of the web reference to the web service which points to the "other" machine, and use IT to publish to the new destination server.

private void button1_Click(object sender, EventArgs e)
{

SampleApp.localhost.ReportingService2005 rs = new SampleApp.localhost.ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

try
{
byte[] reportDefinition;
Warning[] warnings;

FileStream stream = File.OpenRead(@."C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports\Company Sales.rdl");
reportDefinition = new Byte[stream.Length];
stream.Read(reportDefinition, 0, (int)stream.Length);
stream.Close();

// Create a report which utilizes a pre-existing shared data source

// Optionally, set properties on the report by replacing the null in the last parameter of
// CreateReport with an array of properties

localhost.Property[] reportProps = new Property;
reportProps[0] = new Property();
reportProps[0].Name = "PageHeight";
reportProps[0].Value = "279.4";
reportProps[1] = new Property();
reportProps[1].Name = "PageWidth";
reportProps[1].Value = "215.9";
reportProps[2] = new Property();
reportProps[2].Name = "TopMargin";
reportProps[2].Value = "12.7";
reportProps[3] = new Property();
reportProps[3].Name = "BottomMargin";
reportProps[3].Value = "12.7";
reportProps[4] = new Property();
reportProps[4].Name = "LeftMargin";
reportProps[4].Value = "12.7";
reportProps[5] = new Property();
reportProps[5].Name = "RightMargin";
reportProps[5].Value = "12.7";

warnings = rs.CreateReport("Company Sales", "/AdventureWorks Sample Reports", true, reportDefinition, reportProps);

if (warnings!=null)
{
Console.WriteLine("Warnings occured when creating report");
foreach (Warning warning in warnings)
Console.WriteLine("\t" + warning.Code + ": " + warning.Message);
}

try
{
// Report was created, now fix up datasource reference to make sure report points at correct ds
DataSourceReference reference = new DataSourceReference();
DataSource[] dsarray = new DataSource[1];
DataSource ds = new DataSource();
reference.Reference = "/Data Sources/AdventureWorks";
ds.Item = (DataSourceReference)reference;
ds.Name = "AdventureWorks";
dsarray[0] = ds;
rs.SetItemDataSources("/AdventureWorks Sample Reports/Company Sales", dsarray);
}
catch (SoapException ex)
{
Console.WriteLine("Error Code: " + ex.Detail["ErrorCode"].InnerXml);
Console.WriteLine("Message: " + ex.Detail["Message"].InnerXml);
}

}

....and here's how to get the model:

private void button2_Click(object sender, EventArgs e)
{
localhost.ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
//To hold SMDL as byte array
Byte[] modelDef;

//Work on pre-published Model

//... if it's not already there. See CreateDataSource.cs for more inforamtion

//Get definition of model, save in byte array
modelDef = rs.GetModelDefinition("/Models/Adventure Works");

//Publish Model

try
{
rs.CreateModel("CopyOfAdventureWorksModel", "/Models", modelDef, null);
}

catch (System.Web.Services.Protocols.SoapException ex)
{
Console.WriteLine(ex.Detail.InnerXml.ToString());
}
}

|||Russell, thank you very much for your patience and response!!
I will get back to you at the earliest, coz I need to give this code to my client!!!
Will reply to you after he gets back to me!!

Is there any way of accessing the resultset of an EXEC comand?

Hello guys,
I have a trouble in here to do something that would be very simple to solve
in an Oracle database thru the DBMS_SQL package.
I have to execute a query inside a stored procedure, but i don't know what
is the column name to bring in the resultset before running the procedure, i
have to do some work inside the stored procedure to get this column name. So
,
at this part of my program, i have to build a query string dinamically as it
follows:
SELECT @.SQL = 'SELECT' + Column_Name + ' FROM Table'
EXEC(@.SQL)
The problem is that i can't access the resultset of a SELECT executed with
the EXEC command.
I've already tried to use the OPENROWSET method, which seems to be too much
for my needs, but in this case the problem is that the query argument of thi
s
method should be a constant. I've tried also the API cursors library
(sp_cursoropen, sp_cursorfetch) but i can't find a way of binding the values
of the resultsets to any variables in my T-SQL program, so i felt in the sam
e
case as the queries executed with EXEC, i have no way to access its
resultsets.
Does anybody know a way of solving this problem? (Sorry for my english, i'm
brazilian doing my best to express myself).
Thanks,
Paulo RicardoYes, you can use a Temp Table.. here's an example using Northwid Database...
Use Northwind
Create Table #NewTab
(CID VarChar(20),
CustNm VarChar(80),
ContNm VarChar(80))
Declare @.S VarChar(300)
Set @.S = 'Select CustomerID, CompanyName, ContactName From Customers'
-- ---
Insert #NewTab(CID, CustNm, ContNm) Exec (@.S)
-- ---
Select * From #NewTab
-- --
hth, Charly
Charles Bretana Jr.
Arete Industries Inc.
"Paulo Ricardo" wrote:

> Hello guys,
> I have a trouble in here to do something that would be very simple to solv
e
> in an Oracle database thru the DBMS_SQL package.
> I have to execute a query inside a stored procedure, but i don't know what
> is the column name to bring in the resultset before running the procedure,
i
> have to do some work inside the stored procedure to get this column name.
So,
> at this part of my program, i have to build a query string dinamically as
it
> follows:
> SELECT @.SQL = 'SELECT' + Column_Name + ' FROM Table'
> EXEC(@.SQL)
> The problem is that i can't access the resultset of a SELECT executed with
> the EXEC command.
> I've already tried to use the OPENROWSET method, which seems to be too muc
h
> for my needs, but in this case the problem is that the query argument of t
his
> method should be a constant. I've tried also the API cursors library
> (sp_cursoropen, sp_cursorfetch) but i can't find a way of binding the valu
es
> of the resultsets to any variables in my T-SQL program, so i felt in the s
ame
> case as the queries executed with EXEC, i have no way to access its
> resultsets.
> Does anybody know a way of solving this problem? (Sorry for my english, i'
m
> brazilian doing my best to express myself).
> Thanks,
> Paulo Ricardo|||Paulo
Also you can use sp_executesql stored procedure.
declare @.sql nvarchar(1000),
@.col sysname,
@.orderid int
select @.col='OrderID', @.orderid=10248
set @.sql = 'select * from Northwind..Orders where '+@.col+'= @.ordid'
exec sp_executesql @.sql, N'@.ordid int',@.orderid
"Paulo Ricardo" <Paulo Ricardo@.discussions.microsoft.com> wrote in message
news:B55806A1-E2BD-40F0-B62D-EE54E201EA0E@.microsoft.com...
> Hello guys,
> I have a trouble in here to do something that would be very simple to
solve
> in an Oracle database thru the DBMS_SQL package.
> I have to execute a query inside a stored procedure, but i don't know what
> is the column name to bring in the resultset before running the procedure,
i
> have to do some work inside the stored procedure to get this column name.
So,
> at this part of my program, i have to build a query string dinamically as
it
> follows:
> SELECT @.SQL = 'SELECT' + Column_Name + ' FROM Table'
> EXEC(@.SQL)
> The problem is that i can't access the resultset of a SELECT executed with
> the EXEC command.
> I've already tried to use the OPENROWSET method, which seems to be too
much
> for my needs, but in this case the problem is that the query argument of
this
> method should be a constant. I've tried also the API cursors library
> (sp_cursoropen, sp_cursorfetch) but i can't find a way of binding the
values
> of the resultsets to any variables in my T-SQL program, so i felt in the
same
> case as the queries executed with EXEC, i have no way to access its
> resultsets.
> Does anybody know a way of solving this problem? (Sorry for my english,
i'm
> brazilian doing my best to express myself).
> Thanks,
> Paulo Ricardo

Is there any way in a sproc to LOOP thru the records of a table ?

Hi. It seems to be very simple, actually, but I don't know if it is
feasible in TSQL. I have a sproc which gathers in one place many calls
to different other sprocs, all of them taking a 'StoreGroupe'
parameter. I would like to add a case where if the call has NO
StoreGroupe parameter, the sproc should LOOP thru all records in table
StoreGroupeTable, read the column StoreCode, and pass that value as a
param to the other sprocs, as in:

CREATE PROCEDURE MySproc

(
@.StoreGroupe nvarchar(6) = NULL
)

AS
if (@.StoreGroupe is not null)
Begin
Exec _Sproc1 @.StoreGroupe
Exec _Sproc2 @.StoreGroupe
Exec _Sproc3 @.StoreGroupe
Exec _Sproc4 @.StoreGroupe
............
End
Else
Begin
A 'Group Code' has NOT been specified
I want to take all the StoreGroups in table
StoreGroupeTable, in turn.
I would like to do SOMETHING LIKE THIS:
Do While not [StoreGroupeTable].EOF
Read [Code] from [StoreGroupeTable]
Set @.StoreGroupe = The value I just read
Exec _Sproc1 @.StoreGroupe
Exec _Sproc2 @.StoreGroupe
Exec _Sproc3 @.StoreGroupe
Exec _Sproc4 @.StoreGroupe
............
Loop
End
GO

Is that feasible in a sproc, or do I have to do this in the client
(ADO) ?

Thanks a lot.
Alex.You can do this using a cursor - executing a stored proc repeatedly for
each value in a column is one of the (few) cases where they're useful.
In performance terms it would probably be better to rewrite proc1,
proc2 etc. to operate on a set of values using set-based code (but that
may not be possible, of course, depending on what the procs are doing).

declare @.StoreGroupe int

declare cur cursor local static
for select SomeCode from dbo.StoreGroupes

open cur
fetch next from cur into @.StoreGroupe
while @.@.fetch_status = 0
begin
exec dbo.proc1 @.StoreGroupe
exec dbo.proc2 @.StoreGroupe
-- etc.
fetch next from cur into @.StoreGroupe
end

close cur
deallocate cur

Simon|||Thanks a lot, Simon, I'll implement this right now.

Thanks again ! :-))))

Alex.|||You have not learned to think in SQL yet and are still writing 3GL
procedural code. Let's get back to the basics of an RDBMS. Rows are not
records; fields are not columns; tables are not files.

>> I have a sproc which gathers in one place many calls to different other sprocs, all of them taking a 'StoreGroupe' parameter. <<

Why?? You have forgotten or never learned the very Basics of
programming. Remember why we NEVER begin a name with an underscore?
Remember coupling and cohesion in your first Software Engineering
class?

Since you did not tell us anything about the modules, give us DDL or
even a hint of a specification, it is impossible to tell exactly what
is happening, but these are the basics. However, each module should
handle a NULL parameter on its own. If you reallllly want to write
stinking bad code, then use a CURSOR, keep writing separate modules
that are incomplete, etc.

If you post more specs, you will get more help. Otherwise yiou will
get kludges.|||plz tell me the punchline-- why do we never use underscores?

ps - i agree, loops on the TSQL side are for newbies