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!?
Thanks.
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"
[code]....
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
View 5 Replies View RelatedWe 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".
View 3 Replies View RelatedCan 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
View 3 Replies View RelatedI 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..".€œ
View 12 Replies View RelatedSQL Server 2005 - June CTP
View 7 Replies View RelatedHi..
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 .....
Anybody has a script for it ? Any clue?
Thanks a lot.
:D
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
Can anyone tell me what I’m doing wrong?
Thanks
GEM
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
View 2 Replies View RelatedWe 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?
Thanks,
Phil
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.
Seth J Hersh
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.
View 4 Replies View RelatedHi 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.
View 2 Replies View RelatedHow 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?
Thanks
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?
View 1 Replies View RelatedHi, is there a way to find all the objects that are currently open on the server, that includes:
Tables, Views, SPs, Extended SPs, Triggers, Rules, Defaults, Indexes on the tables?
I can get some information from syslockinfo table but I can find a way to get the full picture.
Any help on that would be appreciated.
Dim
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.
Hi,
Does any has a script to track how many objects created on a server on all databases with in specified date range?
Appreciate your help.
Thanks
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.
Hi all,
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.
Please suggest me to solve the problem
Thanks in advance
Karna
Hi
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.
Thanks
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...
View 1 Replies View RelatedHi 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.
View 3 Replies View RelatedI'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
View 4 Replies View Relatedhi
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
Any help apprecitaed
Steve
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 ?
Thanks,
Christopher
Hi ,
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,
Regards,
Haroon
Hello,
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?
Thanks,
Ken
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.
Cheers,
Steve.
Hi all,
I am using SSIS Transfer SQL Server Objects Task to copy all the objects of a database to another without the data. The properties that i have set for this task is as:
DropObjectsFirst = True
IncludeExtendedProperties = True
CopyData = false
CopySchema = True
UseCollation =True
IncludeDependentObjects = True
CopyAllObject = False
CopyAllTables = True
CopyAllViews = True
CopyAllStoredProcedures = True
CopyAllUserDefinedFunctions = True
CopyAllDefaults = True
CopyAllUserDefinedDataTypes = True
CopyAllPartitionFunctions = True
CopyAllPartitionSchemes = True
CopyAllSchemas = True
CopyAllSqlAssemblies = True
CopyAllUserDefinedAggregates = True
CopyAllUserDefinedTypes = True
CopyAllXMLSchemaCollections = True
CopyIndexes = True
CopyTriggers = True
CopyFullTextIndexes = True
CopyPrimaryKeys = True
CopyForeignKeys = True
All the security options are set to false.
When I execute this task, it fails (with the errorCode=-1073548784) at a point where it tries to create a table which is inside a schema, because the script for creating the table is executed before creating the schema. So, if have a table [Person].[Employee], the script "Create Table [Person].[Employee]" gives an error
The specified schema name "Person" either does not exist or you do not have permission to use it.
It cannot be a permission related issue as am using the sa account to connect to both the source and the destination.
Hence, I conclude that the SSIS task here tries to copy the table first without creating the schema and so the excution fails.
Is this a bug in SSIS? or am missing anything? and what can be the workaround this?
Hi,
When i'm trying to transfer SQL Server Objects from a SQL Server 2000 Database to another i got the following error: [Transfer SQL Server Objects Task] Error: Table "bank" does not exist at the source.
This just appens in some spcific situations:
- When i select the tables i want to transfer, using the option "CopyAllTables" it works fine;
- When i use specific instance as source. Using the Development machine as source it works fine, when i use the machine from Pre-Production (the one i whant to use) it doesn't .
Can anyone tell me why does this appens!?
Thanks,
VÃtor Ferreira