Want a $100 Amazon Gift Certificate? Solve my SQL Server 2005 problem!

So I’m beginning to think that SQL Server 2005 is a completely broken product (I know, many of you are saying ‘Of course it is! Microsoft sucks!’). After my upgrade fiasco, I waited until I had a new computer to install SQL Server 2005. When I installed it on my new box, everything was wonderful. It installed, it worked, I learned how to use the new Management Studio, and it makes looking at DB info much better.

But then, but then! I made the mistake of trying to copy data from one server to another. This is something, that as a developer, I do all the time. I probably ran a DTS Import/Export task every other day in SQL Server 2000. I thought the Import/Export data task in SQL Server 2005 would work the same way, but it doesn’t. It only copies data. Not only that, but by default, it appends data, rather than replacing it. And to get it to overwrite data, and more importantly. to enable identity inserts, you have to manuall change it for every table you want to copy. Which, when copying up or down some 100+ tables, really, really sucks.

That’s not the end of it, however. When copying data, even with identity insert turned on, it doesn’t copy the data. It Selects Into. Which means that objects can, and often will, change ID’s. Which can sometimes have rather horrible effects on data integrity. For instance, the pencilneck user in the Pencilcase has a specific ID that has allowed me to hard-code certain rules around that ID. But the first time I copied data, the pencilneck user came across with a wholly new ID. Which, as I just showed, broke things. Bleh.

So instead of DTS, there’s this whole new piece of software, called the SQL Server Business Intelligence Development Studio, in which you can create ‘SQL Server Integration Services’, or SSIS, which has replaced DTS. In there, there’s a task called “Copy SQL Server Objects”, which is exactly what I want to do – it’s what I did in DTS all the time.

However! It appears to be broken. On our shared servers, all the tables are owned by the DBO user. But we login to the server with a different username, say, “pnecker”. When I try and copy objects, I get an error that says ‘[Transfer SQL Server Objects Task] Error: Table “table” does not exist at the source. ‘ Why? Because it runs a query on the sysobjects table to get all tables owned by the login you’re using to access the server. Which seems kindof dumb to me. But whatever. So let’s say I then change the ownership of the table to pnecker, so that [pnecker].[table] does exist. Now, when I try to run the task, I get the following error, which is completely meaningless to me:

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: “ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E37 Description: “Invalid object name ‘pnecker.table’.”. helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}”.

I’ve done a bunch of searches online for different parts of this error, and can find nothing. I have found 3 forum posts of people experiencing the same error as me, but no one, as far as I can tell, has found a solution. So! If any of you out there know why I’m receiving this error, I could really, really, really, really use your help. In fact, I need it so much that if you can provide me an answer that works, I’ll buy you something from your local Amazon, up to $100CAD.

So here’s how this will work. If you can show me how to transfer objects in SQL Server 2005, without having to change ownership of all my tables to the user account I happen to be logging in with, I’ll give you a $100 Gift Certificate. If you can solve the second part, but not the first, so I can transfer objects, but have to change ownerhsip of my tables, you’ll get a $50 gift certificate. Answers can be posted in comments, or emailed to me, at sql2005 @ this domain. If you give me the clue that allows me to find the answer, you’ll still get $50, so really, what have you got to lose?