We have an instance of sql running on one box. We're going to be making a copy of a database on another server. I've been working on a local copy of the db and I've had to add a few new stored procedures and tables along with some fts catalogs. When we set up the copy on the production server, I figured I'd set up the new version that I've been working on so it has the latest structures in place. However, we then have to pull all the data over from the existing database to the new one. Obviously the new tables won't get any data input from the pull, but aside from creating a dtsx project to pull it all over, is there any other way to do it a little easier and faster? Thanks.
Is there an easy way to add descriptions or comments to database objects? So, for instance, I might have a table that a number of analysts are using, and I'd like an easy way to save comments or descriptive data about each of the columns in the table. That way, all consumers of the DB can easily look up the meaning of each column, table, stored procedure, etc. I could maintain all of this information in a system separate from my RDBMS, but I'm wondering if there are existing ways to do this already built into SQL Server. I've seen something similar to this built into Oracle systems.
I'm currently usiing SSIS as part of a data migration project. I have used the SSIS import export wizard to generate a package to transfer all the tables from one database to another. When I look at the generated package in can see that it connects to a number of 'temporary' text file objects that contain large blocks of SQL code.
The thing that strikes me is that much of the real package activity is effectively encapsulated in these objects and yet thay are not able to be included in my SSIS project within BIDS. I could not deploy this package to another environment without manually locating and deploying the text objects separately and more importantly these objects are excluded from source control.
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX) DECLARE @Handle AS INT DECLARE @TransactionCount AS INT SELECT @Config = ' <ConfigurationDirectory > <ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
We are trying to create some alerts in our SQL Server 2014 BI edition.Issue is that, after I chose "Type" as "SQL Server performance condition alert" nothing is listed in the "Object" list box.SQL Server event alerts are working. Issue is only with "SQL Server performance condition alert".
Hi, I am a Database called "Cache" user and in it and when creating a new table I can create a column of type Object. So basically, I can create an object eg. Address that have the following items StreetNo, StreetName, PCode somewhere in my database; then create a table called eg. Employees that have a column called EmployeeAddress of type Address (as my Address Object). Can this be done in SQL Server!?
Can anyone refer me to good 'recipes' or sources of information on thistopic??I have Visual Studio Tools for Office, which installs SS 2005 Expresslocally to my XP box, and I want to develop in SS 2005, then copy thetables or queries or reports etc. to a SS 2005 Standard server.Thank you, Tom
I was trying to transfer a SQL Server 2000 database to SQL Server 2005 using SQL Server Objects Task. However, The following error message was encountered: "[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".€œ
I need a script to export all the objects from my DB, I can't use DTS because this server is on another private LAN , so the only thing that i can do is drop a *.sql file on a common sharing server and my other server will execute it from that location.
What do you recommend? I know i can do it from My Enterprise Manager but i need to run it on a schedule time .....
I’m trying to create a DTS Package to copy my sql Server objects to a test Server. The server I’m copying from is UMTS1 and the server I’m copying to is UMTSDEV. The database name is ProgramSpecs and exists on bother servers. My login is assigned to all server roles on both servers. I have created databases on both servers manually so I’m pretty sure I have all the necessary permissions. I’m using the DTS task ‘Copy Sql Server Objects’ to copy sql server objects and have selected “Drop Destination objects first”.
When I try to execute the package I get the following error: Error source: MS SQL DMO Error Description: Invalid OLEVERB Structure [SQL DMO] create file error or UMTS1.ProgramSpecs.LOG
I want to copy a database from one server to another. I'm happy abouthow to do this but also want to copy a number of DTS packages, jobs andalerts that relate to this database. Is there any way that I can copythem or will I need to create them again on the new server.Many ThanksLaurence Breeze
We have just installed SQL and C# Express. We have lots of experience with SQL, but none with C#. With both the SQL and C# apps running; if we create a new table, view etc, we cannot see that new object from C# only the objects that existed when we opened the apps for the first time (each time).
If we close everything and re-open everything from scratch, the new objects show..!?!?
Thank you for any information on this difficult problem.
Is there a way to set up the "Transfer SQL Server objects" task and have it dynamically copy of certain tables/data? I see the TablesList values in the "Objects to copy" section and I'm seeing it can be an expression (which I can assign a variable too), but what value is that string if you have multiple tables?
Our company intends to write fully-managed .net code to replace all objects in SQL Server.
The development environment for SQL Server has only tables, each with an associated Primary Key. No other objects exist *whatsoever* in the database: Stored Procedures, Foreign Keys, Triggers, Functions, Views, Indexes.
Many of our main data tables are 1,000,000+ rows.
All functionality exists in .net.
Has anyone else taken this 100% .net approach? Are there any issues to achieving success? Are there any known pitfalls due to practical issues?
Thanks for any comments, in terms of viability and your own personal experience, you might be able to offer.
Hi All,I am looking to store a DataSet into a Table in SQL Server 2005, or any object for that matter. I cannot find any code to perform. Can anyone help?Many Thanks,Peppa.
How can we store native .NET objects like arraylists, hashtables in Sql Server? Is this possible? If so, can anybody provide some insight on how to do this?
When created database in Microsoft SQL Server, many no. of default objects will be created.when executed the command "sp_tables", so many no.of system tables will appear in addition to our own created tables. How to Hide them?
My goal is need to synch couple of tables in QA and Prod. I used "Copy SQL Server Objects Task" in DTS package with copy option "replace existing data". It is trying to truncate and insert new data. But BOL is says "Overwrite existing data in the destination objects with the new data from the specified source". Let me know your thought about this.
I am in the process of migrating from SQL Server 2008 over to 2012. The issue is that on some of the Stored Procedures object names are not matching the name inside of the procedure itself. So, for instance the object name under Programability in SSMS is different than the name in the CREATE PROC. Perhaps the object name was changed. The CREATE PROC procedure sits in the sys.sql_modules table and in the column "DEFINITION" there lies the code. The code is in text. EXAMPLE
CREATE PROC [SCHEMA].OBJECTNAME AS OR IT COULD BE CREATE PROCEDURE [OBJECT] ( Select * from etc )
I need a way of changing the [SCHEMA].OBJECTNAME to match the object name in programmabillity.
From SQL Server 2000 Import wizard if we use Copy Objects & data between SQL Server Databases it'll copies tables with primary key. Similar to this what is the alternative method in SQL Server 2005.
Would anyone be able to tell me how to save a database and all its objects e.g. Procedures and views etc. onto a CD and then open it on another SQL Server? For SQL Server 7.0 and 2000.
ok...this (going on eight years now) sql server user has a seeminglyvery simple problem with his new developers edition of sql server 2005.I'm trying to script multiple tables and indexes in the mmc...and usingthe usual control/shift keys to select multiple objects isn't working.Is this a bug in the mmc, or am I losing my mind or is there somethingI haven't yet seen in bol?thanks in advance...
Hi guys,I am pretty happy with the new system views in SQL 2005 but there isone thing I could not figure out: What is the view that let you seewhat object belongs to what DB? Sorry for the simple question, Cheers,G.
I'm looking for help in getting the Transfer Sql Server Objects task to work. The goal is to transfer the tables, views, sprocs,udfs from a SqlServer 2000 database to a Sql Server 2005 database.When I set the task up and run it, it fails complaining about an invalid foreign key relation. The relation in question is valid,and used in the current production environment under SS2000, so I think the real problem is something else. I've seen reports thatthis kind of error occurs when the Transfer task encounters a foreign key involving a table that hasn't been transferred yet.As an aside, this kind of transfer worked flawlessly under Enterprise Manager when I used the DTS Wizard to move the objects betweentwo instances of SqlServer2000. Unfortunately, Enterprise Manager doesn't appear to work with SqlServer2005.Any help and/or pointers would be greatly appreciated (including other strategies on how to affect the transfer -- I tried using theTransfer Database task, too, but it failed when it supposedly couldn't find the path on the target server -- I say supposedlybecause it was able to find the path in order to delete the pre-existing files on the target server)!- Mark
I need to knwo if its possible to easily store / retrieve c# objects in SQL Server 2005 as user defined data types or some other type. We need to be able to store collections of simple objects as a single value and be able to and recreate the c# object. For example one type would be a hashtable of objects that just have 2 public members Date and Value
Hi, i have a ssis package that copies a database using online method of the database transfer task. I realize that this task does not copy my keys and indexes, therefore i went and used the SQL Transfer Object Task which would transfer my keys and indexes, but does not copy the default value of a column, is there something i need to change/turn on for this to work ?
i am copying objects from one DB to other usign Transfer SQL Server Objects Task. i am getting this error
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "DROP TABLE [dbo].[testCopy] " failed with the following error: "Cannot drop the table 'dbo.testCopy', because it does not exist or you do not have permission.". 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}".
it is true that object doesnt exist on the destination. but i want it to see if the object is there then drop otherwise just copy the object there,
I am trying to copy a subset of tables, stored procedures, and views from one database to another database on a named instance of SQL Server 2005. I am attempting to use the Transfer SQL Server Objects task in Integration Services.
I am able to create both Source and Destination connections, and specify the objects I want to transfer. When I run the package, the task turns yellow while it's processing, then green when it completes.
At this point, no objects have been transferred to my destination database. No tables, no procs, no views, nothing. No error has been generated. No information is written to an SSIS log file, other than the fact that objects are being transferred, and then the package is finished.
Does this have something to do with the fact that I'm using a named instance of SQL Server 2005?
Hi. I'm totally new to SSIS (SP1), and I'm having a raft of troubles transferring DB's using the 'Transfer SQL Server Objects Task', which seems to be the closest replacement for the old DTS Transfer Objects tool.
I'm trying to transfer a DB from an SQL Server 2000, where I only have SQL Server authentication (it's a shared hosting environment) to my local SQL Server 2005 server, where I'm logged in using Windows Authentication (although I have the same issues if I use SQL Authentication locally).
Here's the list of errors I'm getting. If I don't select 'copy all tables', I receive a "table does not exist at source" error. If I do copy all tables, suddenly it can find the same table.If I select "Drop objects first", and the object doesn't exist in the destination, in throws an error.The "replace data" doesn't appear to work - it throws a "cannot insert duplicate key" error if the table I'm copying already contains data.If I copy Primary Keys, and one of the tables doesn't have a primary key, I get a "set identity" error, presumably because it's trying to set an ID on that table at the destination.Finally, if I delete all data at the destination, I'm currently receiving a "Invalid character value for cast specification". Which I don't get at all - why is it casting at all, if it's copying objects - they should be identical on both sides?Any and all suggestions would be really appreciated. I'm also having (different) problems transferring objects between SQL Server 2005 servers, but nothing in production, so it's less important.
