Why is this new version of SQL Manager so difficult to use Tasks I was able to do in one step, now I have to do in several steps:
1) Genrate Drop Scripts for tables, views, stored procedures
2) Generate Create Scripts for tables, views, stored procedures
3) Import data to the server
I used to be able to do all this in Enterprise Manager in one DTS package. Which I could automate to synchronize data beteween two databases. Is there some other/new way I am not aware of
Another problem is when I open remote hosting server, it shows me hundreads of databases, I used to be able to see only my database in Enterprise Manager.
I wonder If I can still use the old Enterprise Manager.
I got so frustrated with this new manager that I am considering to downgrade my SQL Server back.
Where is the old Enterprise Manager :(
dangster_gangster
You are correct that "it" is nothing like the old DTS - it is a completely new piece of software. It is not called DTS anymore - it is called SSIS. And I can understand that it looks complex the first time you see it but trust me - it isn't.
DDL stands for Data Definition Language. DDL is a subset of all T-SQL commands that are used to create, change and drop objects. So, your CREATE TABLE and DROP TABLE scripts are examples of some DDL.
Not sure what you mean by "the ones with the settings". Which settings
You can easily create some DDL that will drop a table if it exists and then create it for you. SQL Server management Studio (SSMS) will do this for you as far as I am aware (I don't have an instance to hand - sorry) and you can then copy that DDL into an Execute SQL Task in a SSIS package.
It is just my personal preference but I would never use a wizard to do a job as important as the one you are trying to do. Use the wizard to create a skeleton package, sure. But make you review it to understand what it is doing and edit it if necassary.
Try what I've said above and reply here with any problems. There's plenty of people willing to help out!
-Jamie
wasabe dog
FelipeLopez
RF,
I've just taken a look at the "Export Data..." functionality in SSMS - which I have never looked at before.
You can transfer tables easily. You have the option to create the destination table just as you did in SQL 2000 as far as I am aware. Not only that, you have the option to drop and recreate the destination table which will be useful if the table already exists.
All of this is available by right-clicking a database and pointing to "Tasks"-->"Export Data..."
Is this functionality not satisfactory for you
-Jamie
Hadi Riazi
Ok, I think I got the connection working now. I had to go and Edit properties of SQL Server Object Tasks control...
However now when I execute the package I get the following error:
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
I do not see Login property in the SQL Server Object Tasks control. Is this a field in the table I have no idea
xxRahxx
I have also noticed that the package generated by Wizard, references some files, which do not exist. I wonder how it works
C:\Documents and Settings\Administrator.H001335\Local Settings\Temp\InnerPackage.dtsx
sjmueller
Thanks, let me explain.
I have two servers: one at home (development) and one live (hosting). I would like to synchronize those two databases. The one from my laptop would always ovewrite the hosting one. Since it is hosting environment I can not create database on the remote server, just tables and other db objects...
The perfect slution would be if I can exclude few tables (the ones with the settings). I used to be able to do it very easily in Enterprise Manager. There was Wizard allowing to export all objects. The one included in 2005 does not recreate objects...
I am new to this new DDL,I have opened the DTS package in Visual Studion, but it seems very complex... Not like the old DTS packages at all...
jonyeh
Hi,
I have created new ISS package found there is a control called "transfer SQl Server Objects Task". I have setup the properties. I have copied the source and destination connection from the package generated by wizard. I have pasted it in Connection Manager. I wonder if I need to setup some properties in the control... When I run it I get the following error:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Transfer SQL Server Objects Task [Transfer SQL Server Objects Task]: An error occurred with the following error message: """ is not an SMOServer connection.".
Error at Transfer SQL Server Objects Task [Transfer SQL Server Objects Task]: An error occurred with the following error message: """ is not an SMOServer connection.".
Error at Transfer SQL Server Objects Task [Transfer SQL Server Objects Task]: An error occurred with the following error message: "The "" database at the source server could not be found.".
Error at Transfer SQL Server Objects Task [Transfer SQL Server Objects Task]: An error occurred with the following error message: "The "" database at the destination server could not be found.".
Error at Transfer SQL Server Objects Task: There were errors during task validation.
AndrewSorokin
Hi, I have tried as you suggested. However got the following problems:
1) After Clicking Edit button close to the table name, the option Drop and recreate table is disabled. Any idea
2) Once it is working. Can I apply "Drop and recreate Table" to all, without doing one by one
Thanks a lot for your help.
joe20050
You can drop tables, create tables and populate tables using an SSIS package.
You can create DDL using SQL Server Management Studio very easily (right-click on a table...all the options are in the context menu).
Can you try the above and then be more specific about that you think cannot be done.
Thanks
-Jamie