Issues with SMO Transfer

I am trying to come up with a way to copy a database from one server to another. I have been running tests using the Transfer method from SMO. I can make it work in some cases and not in others. Currently I am trying to copy a database and am getting an error in a script to create a View. This View is the first one in the list of views from the source database. The problem appears to be that this View refers to another view that has yet to be created in the new database. Is there a way to handle this kind of dependency or is this a limitation to the Transfer method.I'm having the same issue. ScriptTransfer() doesn't seem to generate the scripts in the correct order. Have you been able to resolve it? If so how?|||Have you tried parsing the DependencyTree and scripting the objects based on that order?|||

I was hoping to avoid having to do that. The odd thing about this issue is when we create our database from scripts generated from DB Ghost and then attempt the transfer everything works fine. It's once certain types of changes to that database are made that this problem happens.

Is there an example out there that scripts the entire database using the DependencyTree?

|||

Dim srv As Server
Dim srvConn As ServerConnection
srv = New Server("MyServer")
srvConn = srv.ConnectionContext
srvConn.LoginSecure = True

Dim db As Database
db = srv.Databases("SourceDB")
Dim strDBName As String

strDBName = "DestDB"

Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.CopyData = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.TransferData()

|||

Thanks for the response, but that is essentially the code that I'm using when I run into trouble. Except I'm using the ScriptTransfer() function. But, both methods get the same error.

To try and break it down a little further, I executed the scripts in the StringCollection individually:

strColl = xsfr.ScriptTransfer()

Dim i As Integer

For i = 0 To strColl.Count - 1

dbEngagement.ExecuteNonQuery(strColl(i))

Next

I get an exception complaining about a view that doesn't exist while trying to create another view that is dependent upon it. So for some reason the Transfer object is getting the scripts out of order.

Further information:

I just went into Server Manager to check dependencies on the view in question. Server Manager didn't get the dependencies correct either. It left out the the sub-query when checking objects that this object depends on.

Issues with an output param from a sproc using SQLDataSource

I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.

The proc looks like this currently:

ALTERproc [dbo].[k_sp_Load_IMIS_to_POP_x]

@.vcOutputMsgvarchar(255)OUTPUT

AS

SETNOCOUNTON;

select @.vcOutputMsg='asdf'

The code behind looks like this:

protectedvoid SqlDataSource1_Updated(object sender,SqlDataSourceStatusEventArgs e)

{

//handle error on return

string returnmessage = (string)e.Command.Parameters["@.vcOutputMsg"].Value;

}

On the page source side, the params are defined declaratively:

<UpdateParameters>

<asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int32"/>

<asp:ParameterDirection="InputOutput"Name="vcOutputMsg"Type="String"/>

</UpdateParameters>

When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'"

PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?

I got mine to work. Here's my example.

ASPX

<asp:gridview id="GridView1" runat="server" autogeneratecolumns="false" autogenerateeditbutton="true"datasourceid="SqlDataSource1"><columns><asp:boundfield datafield="ShipperID" headertext="ShipperID" readonly="True" /><asp:boundfield datafield="CompanyName" headertext="CompanyName" readonly="True" /><asp:boundfield datafield="Phone" headertext="Phone" readonly="True" /></columns></asp:gridview><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"onupdated="SqlDataSource1_Updated" selectcommand="SELECT * FROM [Shippers]" updatecommand="sp_GetMessage"updatecommandtype="StoredProcedure"><updateparameters><asp:parameter direction="InputOutput" name="Message" size="50" type="String" /></updateparameters></asp:sqldatasource><asp:label id="Label1" runat="server" />

CODE-BEHIND

protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e){Label1.Text = e.Command.Parameters["@.Message"].Value.ToString();}

STORED PROCEDURE

ALTER PROCEDURE dbo.sp_GetMessage(@.MessageAS VARCHAR(50)OUTPUT)ASBEGINSELECT @.Message ='Hello World!'END

|||

Mine looked identical to yours except for the size on the output param. Once I added it in, it worked beautifully!! Not sure why it wasn't automatically declared when I configged the SQLDatasource to use the update method. But hey, it works now!!!

Thanks for the help!!!!!

Bill

|||

Mine didn't get created either, except I got another error actually referencing the absence of a size. I'm not too sure why you didn't receive the same. I also don't know why the Size attribute is needed.

Issue with Render Stream method in Reporting Services 2005

Hi,
In reporting services 2000 we had provision to pass report parameters
to Render Stream method. Now i am not able to do this in Render Stream
method of report execution api in RS 2005. I need to get the stream in
the form of html based on the parameters passed (e.g;in RS 2000 i used
to do like this...image = rs.RenderStream(reportPath, "HTML4.0",
streamID,historyID, devInfo, values, out optionalString, out
optionalString);). Can any one please tell me how to do this in
Reporting services 2005?Hello,
I have the same issue too, but I got the StreamNotFoundException. Here is my
steps:
1. call render() method. (a ref to streamID is returned)
2. call renderstream() method with above streamID.
Got the exception StreamNotFoundException.
I have no idea why the exception occurs.
The exception is pretty much the same as
http://support.microsoft.com/?kbid=913363
Anyone got an idea?
Thanks
Tai
"Arthas" wrote:
> Hi,
> In reporting services 2000 we had provision to pass report parameters
> to Render Stream method. Now i am not able to do this in Render Stream
> method of report execution api in RS 2005. I need to get the stream in
> the form of html based on the parameters passed (e.g;in RS 2000 i used
> to do like this...image = rs.RenderStream(reportPath, "HTML4.0",
> streamID,historyID, devInfo, values, out optionalString, out
> optionalString);). Can any one please tell me how to do this in
> Reporting services 2005?
>