Table Names Are Not Changing With Change In Schema Name
Aug 8, 2007
Hello Everyone,
I am working on a dtsx package wherein i am sending the data from OLE DB Source (SQL Server) to OLE DB Destination (Oracle). For development purpose i use DEVLOPMENT environment on oracle but for unit testing i have to use QA or Some other Schema. when i use DEVELOPMENT Schema in ole db destination, tables are accessed under Schema name eg. "DEVELOPMENT"."EMPLOYEE", but when i m chenging schema name to QA table names are not changing as "QA"."EMPLOYEE". Data Flow Task is pushing the data to DEVELOPMENT environment only.
Can Anyone suggest me any remedy for it ?
Or this is one more BUG in SQL Server 2005.
I have created a table on SQL Server from SAS. The table gets created fine. However, the table schema has my user ID in it (AD-ENTmyuserid.Table1). How can I change the schema to dbo.(dbo.Table1)? It's fine if I have to make this change in SQL Server Management Studio.
I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?
I read in another thread that "It is not possible to create packages or new objects within packages using SSIS." I also read in the books online that "The input and the input columns of the Excel destination have no custom properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong. So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)
Is to Possible to Create a Triggers to capture Schema (alter table, Drop table) Changes only for certain tables.I don’t want schema change for entire database.
I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.
And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?
I'm admitedly a bit new to the world of replication, so please bear with me. I've got two SQL Server 2000 servers running in different locations. Server A does transactional replication over a push subscription to server B. If I need to make a minor change to one of the replicated tables (for example, dropping a no longer used column or changing a varchar field's length) do I need to drop the subscription, make the changes and then re-initialize the schemas and data?
For minor changes, I really hate having to knock out the site runnign off server B while the subscription is re-initialized and data is bulk copied back over. If I want to just make the changes manually on both servers will that cause problems down the line?
Hi, I have a SP which returns a select query on a temp table so I get to choose column names when I create the #table.
Can I determine column names myself based on the results of another query (in the SP) before, or (preferably) after I create the #table and populate it.
My query is used to bind to a datagrid so I could use....
dataGrid.Columns[index].HeaderText and set it to a particular output parameter, but I want to keep the code in the SQL.
I can't seem to find a sample code, either here or on the net - - so I'll go ahead and ask...
What I'm looking for is a sample of how to query a database, so that I can populate a listbox with the table names from a database - - and then, populate another listobx with the field names from the database, in order to build a user-driven sql statement builder....
either of the above (at least the field name part) - either a code sample, or a link, will be greatly appreciated) - -
Hello everyone. I wanted to learn to assign and change a schema names used in my table. I dont want a dbo. in my tables. I want to give a personal name like the adventureworks database has. (Such as sales.salesdetails, humanresources.employee and all)
> So how i do create a table with a schema name sales and > How do i change my for example dbo.employee to sales.employee ??
I tried Create table sales.employee { statements }
This is a followup to my last post. If a field name is changed in the database, what is the easiest way to determine what stored procedures and triggers that reference that field are now broken?
I changed the name of a server that runs SQL Server 2005 and Reporting Services web site. I can't connect back to reporting services now using the new server name. I had to do some things to get SQL Server and Analysis Services to work, but I can't find any information on getting Reporting Services to run, I found something about changing config files, but no direction on what those files are.
I wihsh to discuss whether to use fully qualified names:[database].[schema].objectof objects to operate (create, query..) on is good or not?If someone change order of sql code blocks in my script - this may causelose of it's context (like: use master / use <mydb>..). I wish to have mysript independed on changes like this and always produce correct result.Does using full name make use of 'use <db>' statement unnecessary?
When reverse engineering a SQL Server 2005 database (using VS 2007 Professional), I can't figure out a way to display the schema name in front of the table name in the database diagram. For instance, If I have a table named Person.Type, it just comes over as Type. It knows what the schema is because it's in the properties box for that object. It just doesn't appear in the name on the shape itself. Is there a setting somewhere that I'm missing that will display the schema in the name?
I have a replication in which publisher, distributer and subscriber all runing on sql server 2005. all the tables that needs to be replicated are under 'dbo' schema on publisher.
Subscriber is a datawarehouse so i dont want to put tables coming from a system to go under dbo schema as there might be other application replicating same name tables to warehouse. i have created a schema for my application on warehouse but dont know how to tell replication to create tables under application schema created on subscriber. I am using snapshot replication that can be reinitialized if required.
I allowed the SQL Destination Editor to design my table from Output of a flat text file. Everything was varchar(50), but that was cool, because I got to see the data in the new staging tables it created. I went back and tweaked the data types and sizes for various columns to be more appropriate through the table designer in SQL Server Management Studio.
After doing so I get an error trying to edit the package, specifically parts of the destination in the data flow. I get the error "An error occurred due to no connection. A connection is required when requesting metadata... blah blah". I pick the TEST CONNECTION in the Connection Manager, and it works fine.
I am sure this is probably a basic issue of mechanics of use that I don't yet know because I am completely new to SSIS. Can someone please provide a hint, perhaps what I did wrong, and also, if you can see it how to redeem the error. Thanks!
I have a local install of MSDE on my workstation. I have also defined alinked server with another SQL Server on our network. I accepted all thedefaults when I installed MSDE and the server got named my machine name.Everything is working great.Our IT department now needs to change my machine name. What effect willthis have on my current SQL installation?Help and suggestions appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
I created my database on a remote server. It now has lots of tables and stored procedures When I created it, the server created a schema named for me, the user, so all my tables and stored procedures are named like johndoe.tablename. I would like to rename the schema to something less personal more professional. Can it be as simple as "ALTER SCHEMA johndoe RENAME professional"? Also, I would like to create a local copy of the databse, so I can develop offline, without the 2 second delay. SQL Server Management Studio Express lets me generate scripts, which I have been using to make backups. Can I use the script file to recreate the database on my local machine?
Why is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error “Invalid column Name EMP#”. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs.
Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?
If I have stored procedures that reference linked servers, is there any way to avoid changing the procedures when moving between environments (dev, qa, prod)?
Do i resort to dynamic sql? Use case statements to switch between environments?
I can easily move "Table1", with schema "dbo", into schema TestSchema. But I can't see how to do it using only the GUI tools provided in Management Studio. Any ideas?
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
I need to know if the anyone has had success using any sort of tracking tool to manage schema in SQL 7. I'm responsible for several SQL servers, each with upwards of 10 databases - most still in deelopment phase. We use Erwin to store our models and Visual sourcesafe for stored procs and sql scripts. But now as a Dba I'm looking either:
(1) create a custom database to keep track of schema changes (as they movefrom development to test to prod). OR
(2) buy a 3rd party tools to keep track of schema changes? Platinum?
It's getting too much for a single Dba to manage a busy devel shop with 20 db's. Schema is comming in too often. Only some of our db's are in Erwin.
Our company wants to allow our customers the ability to import employee data. Each customer's employee data changes depending on things like organisation structure etc. so the format of the data to be imported needs to change. We can do a lot of this with dynamic SQL, but are looking at moving it to SSIS as we think it will save us a lot of pain later on and for other features in SSIS.
We're stuck at a fairly early part of the process as we don't know how to dynamically import our spreadsheet. So far our best idea is to connect to the excel connection as the only step in the data task and then to use a script task on the control task, write code to connect to the excel source (set the excel source to not treat the first row as headers so we can do a 'select top 1 *...' ) then create a dynamic sql command to create the table.
Once this is done we would then have another data task that actually puts the data into the newly created table. This all sounds very difficult though. What are the options for doing what we want to do... have a feeling that we're missing something basic.
Is it possible to turn off transaction logging when making a table schema change? For example, when expanding a varchar field from 10 to 40 characters? This is occurring on a hosted site for a table with about 150,000 records. The db size is 200 Mb. If I try this normally with transaction logging enabled, I get the dreaded log file is full message, even if I first truncate it. I was thinking about doing the following instead:
taking the db offline creating a backup disabling logging change the schema re-enable logging put db back online If a problem occurs during the schema change, I would just restore from the backup. Please let me know the following:
Is it possible to turn of the transaction logging for the schema change? Do you see any problems with the above. Alternatives? One alternative is to look into increasing the db size. A second is to add a new field (named temp) to the table, copy the old field to the new field, delete the old field, add another field (with the original field name) with the new schema, copy the temp field to the new field, and finally delete the temp field. This should require less space in the transaction log. Unfortnately, it could possibly affect some linked Access databases with the new field order. Thanks in advance.
There is a report when you click servername, report and run SCHEMA CHANGE HISTORY I had my SQL 2005 running for a few weeks and this is many listed from day started is there a way to recycle this and clean it up on a weekly basic
Simple question, I hope. I need to add a column to a table of a database that is mirrored. How do I have to do that? Do I need to stop mirroring? Is it sufficient to simply pause mirroring? If I make the change on the principal db, what do I need to do the make the same change on the mirror?
How can I keep schema changes in the subscriber's database when I replicate the snapshot from the publisher?
I just want to move data from a remote server, but it seems that the tables are being dropped which is not good as we use the subscriber as a development box.
