Update An Schema Table When Structure Changes Are Made
Mar 22, 2006
Hello all, I was wondering. I am having trouble in my old age remembering to update my schema table when I make changes to the database. Is there a DB trigger or something that I could write to update this table for me when I edit or update a table structure, view, Trigger, or stored procedure?
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
I was wondering does anyone know a way to get the structure, with relationships, of a Sql Database and generate a XML Schema. I want to use the Schema to build a CrystalReport.
I just ended a case with microsoft because I had an issue with Analysis services. The thing is, one of the solutions given by microsoft was to install cumulative update 5 for SQL Server 2005 SP2. Now SQL Server is using all the memory on the server which has windows 2003 server R2 Enterprise SP2 (8GB of Ram) /3GB and /PAE enabled. The weird thing is non of the sql server processes displayed in Task manager shows more than 200MB of memory. But, the server only has 500MB of memory available and 7GB of PF Usage. How did I find out that SQL Server was using the memory, well I shut down the service and 6.8 GB of memory available and the PF Usage went down to just 1.8GB. Turned on again and then the same situation.
well it seems SQL Server is reserving memory or something.
1) As per my project requirement, if the changes in SSAS cube are approved, they should be committed back to the actual SQL Server 2012 tables. Is that possible, if yes how?
2) For rolling back to original data I truncate the relevant writeback table and process the cube.
Hi, we are almost finished developing our database and we have a table we want to monitor because it is getting information deleted from it and it has a delete trigger on it but we want to track the changes to the table and were wondering how to track specific changes to a user database? We want to see who is making the change, what the change they are making is and also what is the time they are making it. I have used SELECT * FROM SYSPROCESSES and I am running SQL TRACE with filter on MS SQLEW, and MS TRANS, and Visual Basic with SQL statements on tblRoute,( the table that I want to monitor) and I want to know if there is any other way to monitor this table more closely?
hiI want to increase a varchar(5000)to varchar(8000) on a table thathas approximately million rows.....What is the impact on the serveror any good recomendations of a action to accomplish this in the best andfastest way.thanks davep
I have created two table with same data structure. I need realtime effects (i.e. data) on both tables - Table1 & Table2.
Following Points to Consider.
1. Both tables are in the same database.
2. Table1 is using for data entry & I wants the same data in the Table2.
3. If any row insert, update & delete occers on Table1, the same effect should be done on Table2.
4. I need real time data insert, update & delete on Table2.
I knew that using triggers it could be possible, I have successfully created a trigger for inserting new rows (using logical table "Inserted") in Table2 but not succeed for update & delete yet.
I want to understand how can I impletement this successfully without any ambiguity.
I have attached data structure for tables. Thanx...
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
declare @str varchar(2000) set @str = 'SELECT * INTO #TmpTable FROM FormHistory' exec (@str) SELECT * FROM #TmpTable
gives the following error:
Invalid object name '#TmpTable'.
This is a very cutdown version of what I am trying to achieve so it might not seem obvious why I am writing it into a string and using exec but in the real code I do need to do this. I have cut it right back to try to get to the bottom of why this doesn't work. I suspect the # in the string is causing the problems.
New to SQL Server Compact Edition and I am getting my feet wet with the following tutorial: http://msdn.microsoft.com/msdntv/episode.aspx?xml=episodes/en/20060831MobileRB/manifest.xml
I follow the tutorial exactly, but the data I edit through the Edit Dialog Form is not saved to the SQL Server table even though all of the edits appear in the datagrid. They never make it back into the table.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem 1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right? 2) these two table have One Prinary Key column with IDENTITY feature. Any Help Greatly appriciated.Regards.
We have a big problem here with merge replication, specifically whenever a schema change occurs. We are replicating schema changes, and triggers/stored procedures. The example is that we changed about 150 stored procedures and about 30 triggers. This is then replicated to the subscriber database (which is also a merge publisher for further remote systems that were offline at the time) over a 10Mb link - hardly low bandwidth. However, the replication process takes about an hour and a half - considering the SQL on the primary server took less than a minute to run this is a big suprise.
We've run a trace to see if we can identitfy what is going on. There seems to be a great number of calls to sp_MSunmarkschemaobject - we are still waiting for a trace to complete to fully analyse this however it looks like it calls this repeatedly for every stored procedure in the database. We are currently re-testing to one of the remote servers with the merge agent set to the slow profile (not much hope that this will alter the poor performance).
This task looks to be excessive - and certainly does not seem to function in a sensible manner. Has anyone else had similar issues or have any suggestions. This is very infuriating as it means that the servers are effectively offline for a minimum of and hour and a half (in fact the remoter servers take over 4 hours !).
Structure Number1 Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate Structure Number2 Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryNativeId(P.K.), CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate Can anyone tell me that between above 2 structure what is better and why? I just add CategoryNativeId(P.K.) in Number 2 structure.
How can I setup a table structure for the diagram shown in the attached bitmap?
1) I need to create a product using labour and materials. 2) I need to create hardware using labour and materials. 3) I need to be able to include some hardware in some products. 4) Some materials in the product are made of a culmination of materials. E.G., Concrete is made of sand, stone, and cement.
Any suggestions?
So far I have :
USE NORTHWIND
Create Table tbProducts ( ProductID int NOT NULL, Product varchar(50) )
go
ALTER TABLE tbProducts ADD CONSTRAINT tbProducts_pk PRIMARY KEY (ProductID) GO
CREATE Table tbMaterials ( MaterialID int NOT NULL, Material varchar (50) )
GO
ALTER TABLE tbMaterials ADD CONSTRAINT tbMaterials_pk PRIMARY KEY (MaterialID) GO
CREATE Table tbLabour ( LabourCode char (2) NOT NULL, Labour varchar(50) )
GO ALTER TABLE tbLabour ADD CONSTRAINT tbLabour_pk PRIMARY KEY (LabourCode) GO
CREATE Table tbProductMaterials ( fkProductID int NOT NULL, fkMaterialID int NOT NULL, Quantity Float NOT NULL )
GO ALTER TABLE tbProductMaterials ADD CONSTRAINT tbProductMaterials_pk PRIMARY KEY (fkProductID, fkMaterialID) GO
I'm new in creating tables. and I was asked to create table(s) to capture information using SQL server 2005 (express edition)
please read below and share your thoughts and opinions o what's the efficient and best way to structure the tables.
I need to capture user's input daily and every hour, text can be from 20-100 characters, and my company is expecting to have 50,000+users in the future...Also the hrs's text can be stored for max 120 days, after that data would be deleted from the table(s) An example is put all together, (not sure if it is the right and efficient way)
An example is put all together, (not sure if it is the rigth and effectient way) and if database would support that.. and if it will be fast enougth to search for data
(table structure) - (example with 3 users) userId | timestap | message 1 12/12/2007 10:00 some message 1 12/12/2007 11:00 some message 1 12/12/2007 12:00 some message 1 12/12/2007 13:00 some message 1 12/12/2007 14:00 some message 2 12/12/2007 10:00 some message 2 12/12/2007 11:00 some message 3 12/12/2007 12:00 some message 3 12/12/2007 13:00 some message 3 12/12/2007 14:00 some message ...etc..
Hi,I am new to XML and need a structure of table in XML format, which needsto be created as a table in another server.The Source and destinationservers are SQL Server 2000. Could anyone help me on this.Balaji.S.--Posted via http://dbforums.com
Hi, I have one field "Status" with 15 different types of values. Like 1, 2, 3, 4 .. 15. Now this Status field contains the values like this: Id Status 1 1,2 2 3 3 1,2,3 4 13,15
What is the best way to organize this information in SQLServer table? My idea is Create one table with the following volumns: ID, Status1, Status2, Status3, ... Status14, Status15.
Now the above data look like this: Id Status1 Status2 Status3 ... Status13 Status14 Status15 1 1 1 0 0 0 0 2 0 0 1 0 0 0 3 1 1 1 4 0 0 0 1 0 1
So, In future If they add new column they can easily add column like status16. And status table look like this Status StatusID Description 1 a 2 b .. 15
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely, Bryan Ax
I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?
I'd like to do something like:
CREATE TABLE #tempTable LIKE anotherTable
..instead of...
CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...
All,On my aspx page i have 2 sets of checkboxlists, each with a number of items and i am storing that info in my db. currently i have my table set up this way: InfoID--PK autoUserID-intCheckBoxList1ID-intCheckBoxList2ID-int So a user can select more than 1 item in each checkbox, so this table will contain multiple user-ids and each id from the respective checkboxes. My question is, is there a better way to store this data? I hope this is clear, any input would be greatly appreciated.Thank you.
Hi,I have a table for storing my picture albums here it is:CREATE TABLE [UserAlbum] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [UserID] [int] default(0) , [AlbumName] [nvarchar] (100) NULL , [Audience] [int] default(0) , [Date_added] [smalldatetime] NULL , CONSTRAINT PK_CmmNet_userAlbum_ID PRIMARY KEY CLUSTERED ( ID )) ON [PRIMARY]GO it has a field called "audience" as the name defines i want to choose who will see my albumsit is integer and as follows:0 value=everyone1=friends only2=private as i started to write a query for it i came into this problem: for exampleif i write SELECT * FROM UserAlbum where audience=1 then only friends can see the infowhat happens to everyone with value of 0?when i set it to 0, friends should also be able to see the info because it is set for everybody i noticed i should use "in cluase" like thisSELECT * FROM UserAlbum where audience in ('0','1') > so in this way both everyone and also friends can see the info or maybe i should add 3 different fields for private,public,friends it seems to be simple but at the same time harddoes anyone have a suggestion?
Hi i have a student table and also an societies table, and a student can belong to as many societies as possible, for example student John Taylor belongs to the Football society, rugby societey and dance society, where as student Jim Jenkins belongs to the Football society, rugby society, arts society and graphics society. My tables so far look like this; STUDENTS; student_id, student_name SOCIETIES; society_id, society_name
My question is how can i make the scenario above reflect in my table structure, would i need to create another table, if so what fields or columns should i place in this new table, thank you.
I'm a VB.net programmer working in an environment that doesn't have a true sql db administrator. We have a situation where we're thinking about adding a new database (db2) with the same Tables used in an existing database (db1). We were hoping to avoid this, but circumstances are forcing us to think about it. Are there any sql tools out there to maintain a dual-db like this? What are the main disadvantages to this?