I a DB deployed in the field at numerous customer sites. I have made changes
to the DB in my dev environment and now I need a way to update the DBs out in
the field to the new DB structure. Examples of updates are adding a new column
to a table, or adding some more code to a stored proc.
So what do I do ? I need to be able to email comething to the customer that
they can run that will make my recent updates to their DB structure. It can't
screw up their existing data either.
So what's the solution ? A big SQL script wil ALTER TABLE commands ?
How do I updated stored procs through a script ? I need help seriously.
What is the accepted industry standard method of updating deployed DBs
when a new version is released ? Please Please help.
Hi. I have a question that I have been thinking about for some time. Have in mind that I'm somewhat a SQL newbe. I work on a few webapplications that are database driven applications. When we desides to change something in the database, we normaly do the changes on our replicat development database, and after some testing the changes are suppose to be implamented in the live, public database. Now, what is the best way, or recomended way to do this? Today we bo it manualy. But at one time I experimented with the export/import wizard to se if it was possible. What I want to do is change de design of a tabel, without loosing the original data, and without geting the data from the development database into the live database. To export an stored proccedure, I usualy just create a SQL-script for the procedurs in question and the runthe script against the public DB using the Query Analyser. But if I do the same procedure on a table, I loss all the data in the database. Do I make any sense at all? Any tips/idea/best practise on this subject? /Johan Christensson
I have a SSIS package that simply moves data from a SQL database A to another SQL database B. I have update (increased) the size of a nvarchar column, on both A and B.I am wondering if there is a way to "refresh" somehow the SSIS package so I don't have to rebuild and redeploy it.The error I get now is a truncation error: "Text was truncated or one or more characters had no match in the target code page".
Hi,I've just installed SQL server and then IIS and SQLCE tools.I created a virtual directory and was trying to update the NTFSpermissions from SQLServer Connectivity management when i got thefollowing error - Access Control List (structure) invalid.Has anyone come across this and if so, what did u do to fix it.ThanksLyn
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,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
One company can have many site addresses; One company can have many trades And one trade can have many activities.
Ok seems very basic at this stage does'nt it, but if i give some data the complexity changes completly for example:
the trades are as follows: TRADE TABLE ID 1: DESC: Printing ID 2: DESC: Artwork ID 3: DESC: Photography ID 4: DESC: Reprographics.
now in this case only the printing trade has activities: TRADE ACTIVITIES TABLE ID 1: DESC: Flexo ID 2: DESC: Digital
So, in the above i am saying which ever company picks Printing as there trade, then they have the option of specifying which particular printing trade it is that they do.
Again simple enough by using one-to-many from the Trade Table to the Trade Activity table.
The problem is that not all trades have associated trade activities, for example the artwork trade activity is just artwork, but i need to be able to allow this to be so in the future.
When I try to create a view to select a particular company, with there associated trade and trade activity, I do not get any result at all because not all of the trade activities have any records assigned to them.
Please can anyone help.
I need to be able to pull back all companies with their associated trades and associated trade activities.
As my learning process continues, I have found that Database structure is probably the most important & most difficutl part of development. Like database normallization etc.
How can I master database structure, ? but I want learning other than any database software like Access or SQL Server, I m looking for a generallized learning.
Hello I am a final year student and for my final year project I have decided to try and create a price comparison website due to the fact that i feel it would be a good project to work on and develop my technical skills in C# and microsoft sql server 2005, since these are mainly the two programs I would say i have more experience with. Basically I want to create a website such as www.pricerunner.com. At this point in time i have started playing around with how i feel the database will look, in terms of tables and columns, below is what i have produced; Company Company_ID Company_name ADDRESS Website link contact Products Product_ID Product_name Product_Category Product_Price
I am now stuck as how i will link each company with a product bearing in mind a company will stock different categories of products, also do you think i need any more columns in any of the above tables, as well as am i missing out on anything, I am hoping someone with more knowlegde than myself can shed some knowledge on my problem, thank you.
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.
I'm sure there is a simple way to do this but I cannot think of it.I have an ASP.NET application that I am publishing to a webhost. I have chosen to use FTP to copy my files (aspx, etc.). They work as far as links etc. Now, the next step is to create my SQL database on their server as they instruct, which I have done in name only. (Oh, if it makes a difference, I access the hosting service using Plesk control panel.) Is there any simple way to get my database structure from my PC to the host server? All responses welcome. Thanks.
Our company would like to start using SQL 7.0 Snapshot replication for some of our customers. My problem is that we often have to make changes to the actual structure of the DB. Will I need to disable replication each time we need to make a change to the db structure on the publisher and subscribers?? Thank you...
Hi All, I need to get some info on how I can get the script of the entire database stucture. I need to write a procedure to do this. I know in SQL Server I could get the script in a mouse click. But I need to write a procedure to do the task. Is there a package in SQL Server that I could use to accomplish this task? Any thoughts will be of help. Thanks in advance. sj
Could anybody tell me the detail structure of the .mdf file. Actully I want to read the mdf file with out using MS SQL 2000. I know few details like it has 96 bytes header and pages are divided in 8Kb etc. But I want to know the exact details, so that I can read the Table names and columns at least.
I am very new to SQL server, and am completely stumped here. I need to transfer the data from an old database to a new one (both SQL Server 6.5 DBs.) The only problem is that the database has been restructured, and I can't for the life of me figure out how to get the data imported. I am working on manually making all of the structure changes, but there must be a better way. Any ideas? Thank you SO much!
When you attach an MDF that was created using SQL 7.0 to SQL Server 2000, the MDF file structure gets updated. The resulting MDF can no longer be used by SQL 7.0.
My Question: does anyone know how to read the file structure version of a detached MDF file?
If the file structure is 7.0, I would like to warn the user and allow her to cancel the attach + conversion to SQL 2000.
I have 2 databases with corresponding history databases. As the system is under development, the 2 databases are changing in structure i.e the table structure(new tables as well as changes to existing ones) and indexes will change every week. What is the best solution to replicate the changes on the history database.
This is a sort of database structure question that could I guess apply to any type of database...
Say I have 4 production lines and I want to log serial numbers on the start of every line and off the end of every line.
Is there any advantage in having a separate table for each Production Line and serial number logging point? (i.e. t_Line1Start, t_Line1End, t_Line2Start etc) or is it just the same as having one big table and having a LineNumber field and a StartEnd field?
Is SQL Server just as happy dealing with fewer transactions to several tables as it is to many transactions to one table?
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 have to change collation for certain database. I think the only solution is to export or create through scripts the empty db structure (tables, PK & FK, defaults) and thereafter import data. Since I don't have empty structure of this database I have to create somehow from the existing one which is with data, can somebody suggest how can I do this - take out the structure of the database from the db with data on it. I have 256 tables and I have to order them first with the dependencies of the tables?!
I will be really thankfull if somebody can help me on this issue.
hi all.. if im about to start a new database.. how do i start? please advise on how do i start sketching a new db structure... how do u guys set up ur db table struc.. any software to do this designing? any links or tutorial will be appriciated..
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..
I need to create a table that supports a potentially infinite amount of subcategories. An example would be: Food Pastries(Parent=Food) Cupcakes(Parent=Pastries) Chocolate(Parent=Cupcakes) Vanilla(Parent=Cupcakes) Danish(Parent=Pastries) Meat(Parent=Food) Beef(Parent=Meat) Chicken(Parent=Meat) Cajun(Parent=Chicken) Fried(Parent=Chicken)
The subcategories will vary in depth.
Can someone demonstrate a table create statement and then the proper select to pull this type of data?
