SQL Management Object method of CDW failing

[SemperFiDownUnda]

Seems our server is having some issues.
Using the Copy Database Wizard with "SQL Management Object method"

It adds the log for package successfully
It adds the task for transferring database objects successfully
It creates the package successfully
It starts the SQL Server Agent Job successfully
It executes teh SQL Server Agent job and fails.

Now it has created the database successfully but it seems the first thing it tries to do is creat a view and fails with this error

ERROR : errorCode=-1073548784 description=Executing the query "CREATE VIEW BAV_CC_COA_BUDGET
AS
SELECT DISTINCT 
COMPANY, COST_CENTRE, COA_CODE, SUM(JULY) AS JULY, SUM(AUGUST) AS AUGUST, SUM(SEPTEMBER) AS SEPTEMBER, SUM(OCTOBER) 
AS OCTOBER, SUM(NOVEMBER) AS NOVEMBER, SUM(DECEMBER) AS DECEMBER, SUM(JANUARY) AS JANUARY, SUM(FEBRUARY) AS FEBRUARY, 
SUM(MARCH) AS MARCH, SUM(APRIL) AS APRIL, SUM(MAY) AS MAY, SUM(JUNE) AS JUNE, SUM(ANNUAL) AS BUDGET_ANNUAL, RPT_MONTH
FROM         dbo.GL_BAV
WHERE     (BAV = 'B')
GROUP BY COMPANY, COST_CENTRE, COA_CODE, RPT_MONTH"

failed with the following error: "Invalid object name 'dbo.GL_BAV'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace:    at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
   at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()

that table actually exsists in the source database. Not sure why this is trying to create the views before the tables have been transfered.



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Catadmin]

Is this actual code you are writing in Visual Studio 2005 or an SSIS project done through BIDS?

If SSIS, what Task are you actually using? There is no Copy Database Wizard that I see. Just a Transfer Database and Transfer SQL Server Objects. Also, SMO has to be coded via Visual Studio, not BIDS.

If Visual Studio, I think we're going to need to see your code, but it might be more helpful if you posted in the Visual Basic .Net forum.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SemperFiDownUnda]

Sorry for not being clear there.
In Management studio you can bring up a context menu on user databases and under "Tasks" you can select "Import"

This is supposed to be kind of like the old "Import" from SQL 2000.

It copies the database in 1 of 2 ways.
Detach and attach method where it detaches the database from one server and copies the files over and reattaches the database. I know this works but it causes 2 problems. First you can not run it while the database is being used. 2nd the SQL Server does no validation on the objects.

The second method is supposed to use the copy object method which works while users are connected and validates the objects.

The wizard walks you through the steps much like the old wizard in Enterprise manager.

You define the source, destination, what types of objects (Stored procs, users, permissions, and a few others, no way to select individual objects like specific views or tables)

You define if the package should drop and recreate and existing objects or abort the package if already there. If the package should be saved or run immediately.

This isn’t a .Net issue. It is strictly a management studio admin issue. The SQL you see above was created by the wizard.

What seems to be happening, but I can’t confirm this, is SSIS is trying to resolve the table used in that view but for one reason or another it doesn’t exsists.




Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Catadmin]

Ahhh. Yes. When SQL Server saves views, it always checks to make sure the tables exist (and I think it checks the columns also) before it saves it. So, since your table doesn't exist, it won't save it.

I have an idea for you to test. Create a database on your dev server and import a couple of tables. Then create a single view. Try to use this Copy Database method to move it to another Dev/Test server. See if it does the same thing.

Also, check out sys.objects to see if the view is listed above your table in the catalog view. Do NOT use an Order By statement. I'm wondering if it's running down in the order things are listed in sys.objects or not.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SemperFiDownUnda]

I've done that.
I created a table. Copied the database and it worked.
I deleted the dest database then added a view to the source db and copied agian and it worked.

the table in the info above does exist and is in the dbo schema.

I'll check sys.objects on monday. I didn't think of that. It does seem to be trying to create it out of order.

Thanks for the tip.

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[mrdenny]

Some of the new SSIS moving objects will try to move the objects in the wrong order.

I haven't seen the same thing, but something similar when using the Orbject Transfer Object in SSIS to move tables. It was trying to move a custom data type and then the rule that the data type was bound to instead of moving the rule first then the custom data type.

First have you installed Service Pack 1 on your workstation (or the machine with the managment studio on it)? The issue may have been fixed by the service pack. If you have the service pack and the hot fix installed then submit the bug to Microsoft at

http://connect.microsoft.com.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SemperFiDownUnda]

Thanks MrDenny I'll try that too. As far as I know I'm not running SP1 for SS2005 management studio

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first