Hi. I am redesigning a database to be more normalized. There are 4 columns that appear in many of the tables that I plan to add to a new table. Here is what the database looks like now:table1 uniquecolumn1 uniquecolumn2 uniquecolumn3 samecolumn1 samecolumn2 samecolumn3 samecolumn4 uniquecolumn4 etc.table2 uniquecolumn1 uniquecolumn2 uniquecolumn3 samecolumn1 samecolumn2 samecolumn3 samecolumn4 uniquecolumn4 etc.table3 uniquecolumn1 uniquecolumn2 uniquecolumn3 samecolumn1 samecolumn2 samecolumn3 samecolumn4 uniquecolumn4 etc.If I add a 4th table with the columns that are the same in each of the tables, I'd have this:table1 uniquecolumn1 uniquecolumn2 uniquecolumn3 uniquecolumn4 etc.table2 uniquecolumn1 uniquecolumn2 uniquecolumn3 uniquecolumn4 etc.table3 uniquecolumn1 uniquecolumn2 uniquecolumn3 uniquecolumn4 etc.table4 samecolumn1 samecolumn2 samecolumn3 samecolumn4To link table4 to the other tables, I'd define foreign keys on table4 that reference the primary keys of tables 1-3. What I'd like to avoid is having a composite key on table4 made up of the primary keys of tables 1-3. That doesn't sound correct. How do I do this? Also when it comes time to load data, I know that I need to populate tables 1-3 first, but how do I then uniquely identify the rows in tables 1-3 that must go into table4? Thanks for the help!
I m designing a table which will hold about 40 million records. The data which is going to be inserted into the table is a alphanumeric unique code eg: (CABBXFGRET). Similarly there will be 40M codes, all are indexed starting with character 'C', with the second alphabet used as an index for about 5 million codes each.
Is it advisable to have 1 table and load all the records in it or is it more sensible to split the codes in to various tables based on index.
Eg: All codes starting with 'CA.....' till 'CC.....' in one table. etc.
So that the search logic can be designed based on the index of the code.
Logically considering this is helping us reduce the latency in search due to the heavy volume. But i m concerned that this doesnt follow the rules of normalization.
Any suggestions from the DB gurus out there is very much appeciated.
Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID ) and the newly inserted row with a insert time stamp so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond.
example of Data would be like
exisiting data
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME 12 DDS 14 M XYZ ST 2/4/06 NULL 14 hgS 17 M ABC ST 3/4/07 NULL
New row to insert would be
12 DDS 15 M DFG ST 4/5/07
the data should reflect
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME 12 DDS 14 M XYZ ST 2/4/06 4/5/07
12 DDS 15 M DFG ST 4/5/07 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
Please provide your input as much as you can even though it might not be a 100% solution.
Here I will describe my problem. 1. We are loading large amount of data from database on background thread which is starting on Application_start event in global.aspx.cs file.The data is later cached for subsquent request to improve the performance. 2. Now when we put the application on web farm garden, it is not able to load the application. 3. We are sending the request the servers through Router kind of application. 4 This application is working fine on single server enviornment.
I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run???
I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. How to design the table..
Product name : Nestle milk | Rainbow milk packages : CTN,OTR, NOs |
CTN, NOs Price: 50,20,5 | 40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos | CTN=8 Nos
I have a big problem and i'm not able to find any hint on the Network.
I have a window2000 pc, VS2005,II5 and SQLServer 2005(dev edition)
I created an SSIS Package (query to DB and the result is loaded into an Excel file) that works fine.
I imported the dtsx file inside my "Stored Packages".
I would like to load and run the package programmatically on a Remote Scenario using the web services.
I created a solution with web service and web page that invoke the web service.
When my code execute: Microsoft.SqlServer.Dts.Runtime.Application.LoadFromDtsServer(packagePath, ".", Nothing)
I got the Error: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.
The error message doesn't help so much and there is nothing on the www to give me and advice....
I have an assembly, call it A1, that I've deployed to a SQL Server 2005 database. I can use the managed stored procedures from A1 in SQL Server no problem.
In A1 there is a bit of code which uses the Assembly.Load() method, so load another assembly and use instances of class found in that external assembly. However, when I run the managed stored proc in A1 that uses Assembly.Load() I get the error:
Could not load file or assembly 'A1, Version=1.0.0.0, Culture=neutral,PublicKeyToken='????' or one of its dependencies. The system cannot find the file specified.
(note: for security I've changed some of the above line).
I re-built the project, re-deployed it and ran the code in SQL Server - it worked. I could create an instance of a System.Data.DataSet for example. So why can't I load my own custom assembly? My assembly does have a strong name and it's installed in the GAC. I wrote a console app to try and Assembly.Load() my custom assembly and that worked fine (it was also running on the same server as the SQL Server).
So it's defiantely the SQL Server that can't 'see' my customer assembly. What do I need to do this assembly so that SQL Server will allow me to Assembly.Load it, just as it can with System.Data?
Hi allHave you ever used [Web assistant Wizard] in Sql server to create a web page from a predefined [Select] statement or Stored procedure??when i want to do so and when i complete the Wizard i encounter with the following Error message : ---------------------------Microsoft SQL-DMO (ODBC SQLState: 42000)---------------------------Error 22272: Cannot load the DLL xpweb70.dll, or one of the DLLs it references. Reason: 126(error not found).---------------------------OK --------------------------- Could anyone help me please?Kind Regards.
How is load balancing working in SQL Server 2000. We want to have two different servers as a back end for our web based eCommerce application. Is this possible? While there be any modification requirement in the application?
Appreciate if somebody take out a moment and help me here. I'm having a production database backing up every night at 10:00PM. I've one more development server sitting side by and want this development server to load the production backup copy every night. But the problem what I'm encoutering is, When there are users connected to my development server, It wont get load saying "Database is in Use".
Therefore is there a command, I kill all users at one short and have a task which will load the database? If so, Please let me know how to do this.
Hi! We make complete data load once a week. Now we need to make additional steps to append data on daily basis. We have primary key on the table and it doesn't allow appending duplicate rows. What steps should we create to append data?
What is the meaning of Loadbalancing and how can I use this term to my advantage. Q2. How would I know that I need to do load balancing and how can I set it up.
I have wrote load balancing in BOL and did not get any thing on it. can anyone refer me to where I can read more about it.
We have a Intranet Application which uses IIS / SQL Server 7.0, we are trying to support the application for 30,000 + users.
Since SQL Server can only support upto 32676 user connections (Theoretically), is it possible achieve this with clustering / dynamic load balancing. Does MSCS and SQL 7.0 support this. Any other ideas, 3rd party software etc?????
I'm trying to load data from one server to another every night by creating a dts package and then scheduling the same at night for some reason its failing and i have no clue why is it happening here is the error what it gives me:
DTSRun: Loading... Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 713 Error: -2147467259 (80004005); Provider Error: 11 (B) Error string: [DBMSSOCN]General network error. Check your network documentation. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
Could any one tell me what's goin on and how to solve this i will be very greatful for you.
And another thing is when i created this package and ran at that time or any other time without scheduling them its working good with successful results.
Hi everobody! When I run : LOAD DATABASE db1 FROM DISK = 'c:mssqldatadb1_backup.dat' go
I got error message: Msg 3201, Level 16, State 1 Can't open dump device 'c:mssqldatadb1_backup.dat', device error or device off line. Please consult the SQL Server error log for more details. How can I fix the problem? Thank you. Alona
does anybody know how to restore transaction log dumps (several dumps in one dump device) with TSQL statements ?
I tried "Load transaction devicename" this fires an error message which complains that i have to use right sequence. How do i tell the load command which sequence it shall use ?
You can use "load headeronly" command which gives you all entrys from the dumpdevice. How can i use this information ?
We have setup SQL Server 2000 with a VB application. I want to stress test the application / server by stimulating 400+ users. Is there any tool or utility available to do so?
Does everyone have experience to load a big database? My SQL Server 2K is running on W2K. The database is 4GB, and the biggest table has 12 million records. Thanks a lot.
Hello,I was trying to user the code below to run a DTS job. The job failswhen I get to the piece of code that begins:EXEC @rc = sp_OAMethod @PackageToken,'LoadFromSQLServer', ...It looks like it tries to look for the DTS package, because it takes 45seconds to execute when the server name is correct, leading me tobelieve that it is properly finding the server. Am "MyDTSPackage" isthe name of I saved my DTS Package under save as. I enter it in thelist below using single quotes just like I enter the server name.I am wondering if it is a permissions issue. I am running the thisstored procedure from SQL Query Analyzer:EXEC usp_OATest(The Stored Procedure gets created without a problem...it is running itthat is causing issues.)The error message is the custom one that shows at theError:line.Also, master..xp_cmdshell 'DTSRun....' works fine, but I would like tobe able to use this other method.Thanks in advance for any help!RyanCREATE PROC usp_OATestASDECLARE @rc intDECLARE @PackageToken intDECLARE @GlobalVariableToken int--Load DTS PackageEXEC @rc = sp_OACreate 'DTS.Package',@PackageToken OUTPUTIF @rc <> 0 GOTO ErrorEXEC @rc = sp_OAMethod @PackageToken,'LoadFromSQLServer',NULL,'ServerName',NULL,NULL,256,NULL,NULL,NULL,'MyDTSPackage'IF @rc <> 0 GOTO Error <------ERROR DETECTED HERE--execute packageEXEC @rc = sp_OAMethod @PackageToken,'Execute'IF @rc <> 0 GOTO Error--destroy packageEXEC sp_OADestroy @PackageTokenIF @rc <> 0 GOTO ErrorGOTO DoneError:EXEC sp_OAGetErrorInfo @PackageTokenRAISERROR('Error during package preparation or execution', 16, 1)Done:RETURN(@rc)GOEXEC usp_OATest
Hi dear members,Can onyone please tell me that how can we load multiple files/ or evena single .SQL file stored on any physical location(Hard Disk) from SQLprompt.i have written some scripts in diffrent files, now i want to run thosescripts, Do i always need to manually open those scripts and run it onquery analyser or is there some way out.Please HELP!!!
How much traffic/load can a database server running MS SQL server takebefore it can't handle it anymore? And when that time comes, what are therecourses? Am I able to load balance it between separate servers?
I have 3 GB XML file and I dont know what is right way to load it into tables. I am new to SSIS. Is SSIS is right tool????? Do I need developers help????
the connection string in my application daynamic ..changed by changing the development environmet ..how can i load a data from file to sql server destination without hard coded the connection
currently am having a active / passive cluster sql 2000 server, due to the amount of transactions am moving to new high end servers with sql 2005 cluster.
incase the new cluster also doesnt stands the load what approach i should use similar to load balancing.
