Help On Best Approach To Update A Database
Apr 18, 2008
Okay guys here is the problem I have a database call PRD in the production server I have the same database on the development server. What would be the best way of updating the data on the development server with the data of the production server. The database consists of about 183 tables. What ever the approach is I’m going have to do it about 2 to 3 times a day, basically we are currently developing and testing lots of applications and we would always like to be working with the newest data.
Thank You
View 2 Replies
ADVERTISEMENT
Jun 1, 2004
I need to create an update trigger which will have from 3-6 fields which can be updated in a master table (it could be different with each record, but it will always be these fields). Based upon that I will need to update corresponding tables. Isn't there a limit based upon the location in the table how you can do this. Is the best way to handle this with a bunch of if's?
View 1 Replies
View Related
Jul 20, 2005
Hello,I have a rather large table in MS SQL 2000 that I'm writing reports inCrystal from, but I'm unsure how to get the various data files (fromdifferent departments) into the table. Below is a small chunk of thetable:DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;Be nefitsI'm getting data from multiple places, but as I import the data, Iwant it to populate the table with DeptCode and Type being the primarykeys.If someone sends me the following file:DeptCode;Type;AveMonthVolume;WorkedFTE1000;Budget;100;2001010;Target;233;433And I get the following file from another source:DeptCode;Type;TotHours;Benefits1000;Budget;433;4001010;Target;33;43I want a simple way to import all this into the table so it looks likethis:DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;Be nefits1000;Budget;100;200;433;4001010;Target;233;433;33;43The data will be coming in delimited text and in Excel format. Iassume I can import his into MS SQL Enterprise Manager directly, but Ican't find any simple way of running in update query to import mydata. Also, I'd hate to use MS Access as the middle-man if at allpossible... but if this is the only option, I'll do it.Does anyone have other suggestions? Above is only a small example...the table has about 25 columns with various data I'll be collectingfrom 3-4 departments, so it's a hodge podge of data to combine intoone report.Thanks for any suggestions or ideas in solving this. Thanks inadvance...Alex.
View 2 Replies
View Related
May 28, 2008
Hi
Please take a look at this tables
As you can see muscle_category_table and muscle_table have names and descriptions columns in german (DE).
Now I want to have the option to add additional languages for those properties and ask you:
Would you recommend to move name and description into new approprate language table e.g. muscle_lang or Do you have another suggestion which makes maintenance and evolving easier?
Thank you for your suggestions
View 2 Replies
View Related
May 27, 2008
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
View 2 Replies
View Related
Jul 23, 2005
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies
View Related
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
Mar 30, 2004
Let me see if I can explain this.
I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.
Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.
Step 2
The next tables data needs to be limited from the data retrieved in step 1 (I’d like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.
Step 3
The returned rows here, need to be limited to key values returned from step 2
Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.
What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.
I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.
What is the best approach?
View 2 Replies
View Related
Mar 20, 2006
I've been working on this project, and had it working in MySQL, but it was badly done and couldn't last more than a few hours without growing so large that everything slowed way down. I don't expect anyone to tell me exactly what to do, just please provide an outline of what the best way to approach this in SQL Server 2005 is.
To simplify it, I have one table "Items" and another table "ItemPrices". Items has an id and a name. Each row in ItemPrices has an id for the item, a price and two datestamps (added, last updated).
On average, there's about 15,000 active items, 50% of them have new prices every couple minutes, so I'm looking at what seems like a ton of data being constantly imported. There's probably a good way to do this but I only know the bad way :)
So.. what I want to be able to do is have maybe a stored procedure (?) that takes the item name and price as parameters. (In MySQL I was using "INSERT... ON DUPLICATE KEY UPDATE")
A. If it's a new item name, it will add a row to the Items table and a row to ItemPrices
B. If it's an existing item with the same price as the current price (the most recent price for that item in ItemPrices) it will update the "last updated" date field
C. If it's a new price it will insert a row into ItemPrices for that item
Also, I want historical pricing data, but if I ever release this, 95% of the users will just be looking at current prices. I need the current prices to be very fast to query, in my MySQL version I was using something like this: "SELECT... join on lastupdated=(SELECT Max(lastupdated) FROM ItemPrices ...", after I had 300k price updates querying a list of items took like 15 seconds.. there's got to be a better way? What should I do to make this faster?
Does this make any sense? Hopefully someone can lead me in the right direction. Thank you very much!
View 3 Replies
View Related
May 24, 2008
Hello all.
I use MDX on a cube which provides data about animal population.
The cube contains the keyfigure "ANIMALS" that takes the number of animals.
The cube has a dimension "VERSION" which is used to identify the keyfigure
as a target or an actual value (possible values: "actual" and "target")
The cube has another dimension "ZONE" for the population zones.
Possible values for zones: "A", "B", "C" and "D".
Now I want to create an MDX statement, that gives me a result row like this:
Actual number of animals (as sum of all 4 zones) in column no. 1,
Target number of animals (as sum of all 4 zones) in column no. 2,
Achieved percentage (as actual number / target number * 100) in column no. 3.
Until here my statement works and it looks like this:
WITH MEMBER [VERSION].[achieved] AS '[VERSION].[actual] / [VERSION].[target] * 100'
SELECT {[VERSION].[actual], [VERSION].[target], [VERSION].[achieved]} on COLUMNS
FROM [$MYCUBE]
WHERE ([Measures].[ANIMALS])
It surely is possible that the achieved value for all zones together is equal to or greater than 100%,
while single zones might have an achieved values less than 100%.
In order to account on this, i would like column no. 4 to display one of these words:
"ok" if none of the single zones has an achieved value smaller than 100%,
"warning" if any of the single zones has an achieved value between 96 and 99%,
"alert" if any of the single zones has an achieved value smaller than 95%.
That means, i want e.g. the word "yellow" if the lowest achieved value
of the 4 zones is between 96 and 99. I want to have "red" if the lowest value
is smaller than 95.
I am quite new to MDX and I have struggled quite a long time with this.
I would be grateful for a hint on how i have to modify / enhance my MDX statement.
Regards. Peter.
View 1 Replies
View Related
Sep 27, 2006
I have what I feel like is a simple package I am working to create. I am teaching myself SSIS as I go along.
Source server SQL 2000 database allows NULL values in columns.
Destination Server also SQL 2000 but the database required a value in each column.
So I do a basic source select what I want. I next need to read the values and determine if null then insert a space, do some column matching and insert them into the destination sever.
I believe I should use a Derived Column and an expression ISNULL to accomplish what I want.
Maybe there is a better way. Suggestion and comment appreciated.
Ryan
View 1 Replies
View Related
May 18, 2006
Greetings my friends
I am attempting to solve the following problem using SSIS, actually I am attempting to convert a SQL Server 2000 DTS package in to a SSIS package.
The package does the following :
1) Retrieve the maximum Price_ID (PK) from a PRICE dimension table.
2) Populate a staging table with data coming from a source system where the PRICE_ID > (Price_ID from above)
3) Update the actual DIM table with the new data help in the staging table.
For this task I want to learn the use of the Lookup component which I think is appropriate.
My questions are as follows :
If I create a global variable to hold the maximum PRICE_ID (see point 1). How do I get to use the variable in the my Data Flow Data source?!
I am totally confused... I don't even know where to start with this.
Your help would be appreciated.
Thanks SQL friends.
View 6 Replies
View Related
Jun 21, 2006
I've been banging my head for a while now, and it is sore! :-P
I'm a best practice/Microsoft approach type of person and want to make sure I do things correctly.
I have a database, kind of like a forum.
Obviously executing multiple queries in one "batch" (stored proc) would have an impact on the performance.
Now, I would like to give a more detailed/specific error back to the caller (either by aid of error code or whatever) with such situations like...
"EditReply"
Edit reply takes the threadID, replyID and userID.
Before actually commiting the changes, it needs to check:
1) does the user exist in the database? (during the editing of the reply, perhaps the user may have been deleted before running the stored proc, who knows)
2) does the thread exist?
3) does the reply exist?
if the conditions are met, only then will it go ahead and update the database. Now that is 3 queries, and 4 statements overall to make a change to a field/table.
Obviously if one of the commands returns false, in other words if say "does the thread exist" returns 0 (thread doesnt exist) it will return back to the caller an errorcode, which they will handle in their application. Thats all fine but the question is
Am I doing this correctly? (no) - how can I improve this? What do I need to think about?
Of course I would like to give a more detailed error back to the caller (aid of errorcode designed in the application overall) instead of just "no, databases not updated".
In this situation, am I wrongly assuming that the database designers use this type of approach?
Please help, I value your feedback and suggestions. I want to improve and think of the right lines of doing these things.
View 12 Replies
View Related
Aug 7, 2006
Hello,
I'm doing my best here, but need some help. I have a client that has a company list that they want searched by key word. This is exported from another program (in excel) that they want used and searched on their website.
Bad news, is each Keyword is listed with the company separately. So if a company has 5 different key words, they will be listed in the excel file 5 times.
The info I have is Name, Address, City, State, Phone, Keywords:
So example of excel is:Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Green
Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Furry
Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Large
Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Circular
Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Blue
Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Furry
Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Small
I am able to import this large (4.2 MB) file into a table called fctable
What i'm trying to do is write SQL scripts or queries that can insert into a Company Table and Keyword Table.
I'm trying to write this through asp.net 2.0 (so the excel file is uploaded) and have tried to write my inserts like
INSERT INTO [Company] ([Name], [address], [City], [State], [Phone]) VALUES (SELECT DISTINCT
Name, Address, City, State, Phone
FROM fctable )
But that doesn't seem to be working.
This is the only way my client can get the info to me, and it will be changed probably twice per month, so I'd hate to have to try to manipulate an excel file 24 times a year to import.
Any suggestions Appreciated
View 2 Replies
View Related
Feb 27, 2008
Hello everyone.
I am new to.Net and here is what I have to do.
I needto update a SQL table with data coming from a XML file. I have seen some Microsoft documentation on this (the nice SQL statement that updates and inserts in the same stored procedure) but I don'tknow what is the best approach for passing my XML file to the stored procedure. The XML contains about 12 000 records, kind of phonebook info (name, email, phone).
What would be the best approach to do this? What objects should I use?
Thanks a million,
Ben
View 2 Replies
View Related
Oct 18, 1999
Environment
NT Server 4.0 w/ SP4
SQL Server 7.0 w/ SP1
Win98 Client w/ Lotus Approach 9.5
I recently added SQL 7.0 to be a back end for my Approach front end. I transferred all the data from a dbase IV in approach to SQL. Most of the conversions worked ok. I have two big problems.
1) One particular repeat panel in Approach loses the children records of the master record. If I delete some of the records, more will appear. It's as if there is an imaginary limit of the number of records it can read in the repeat panel. I don't have this problem with any other records and children in repeat panels. I called Lotus and they don't have an answer. This is important because the children records need to be summed up so I can have a running total.
2) I original configured the clients to use the TCP/IP Netlib w/ the default port. I couldn't open enough databases so I changed to Multiprotocol. This allowed certain clients to open more databases, but others can't open additional databases. Also, after the change, the NT authentication login has had problems. I had to change to the SQL login to get all my clients back on line. Sometimes the same client can't open more than 10 databases while other times it will open 15. There is no consistent patten to when it can and can't open the additional databases.
If anyone knows how to fix either of these problems, I would greatly appreciate any advice. I'm getting tired of my boss yelling at me.
Thanks.
Keith
akumaboyz@aol.com
View 1 Replies
View Related
May 12, 2008
I have a select statement where I need to test two values that are returned and perform a different calculation if they return null.
Basically,
if TESTA is null and TESTB is not null
return TESTB
if TESTB is null and TESTA is not null
return TESTA
ELSE if both are not null
TestA + TestB / 2 is value is returned
is this possible to do in a select statement? thanks in advance.
See select statement below:
Select S.StudentDimKey,
(select ISNULL(R.Score, NULL) from CLT_StudentAssessmentRawFact R, CLT_LessonPlanDim L where L.LessonPlanKey = 1 and L.LessonPlanDimKey = R.LessonPlanDimKey and StudentDimKey = S.StudentDimKey) as TESTA,
(select ISNULL(R.Score, NULL) from CLT_StudentAssessmentRawFact R, CLT_LessonPlanDim L where L.LessonPlanKey = 2 and L.LessonPlanDimKey = R.LessonPlanDimKey and StudentDimKey = S.StudentDimKey) as TESTB,
from CLT_StudentPlacementFact P, CLT_StudentDim S, CLT_ClassHierarchyDim H, CLT_StudentClassFact C
where P.StudentDimKey = S.StudentDimKey
and H.ClassHierarchyDimKey = C.ClassHierarchyDimKey
and S.StudentDimKey = C.StudentDimKey
and S.StatusCode = 'A'
and S.CurrentRecord = 1
and S.SchoolID = 87577
View 2 Replies
View Related
Feb 1, 2007
im practicing set based approaches... and what im trying to do is grab each value from a table , scramble it and put it back in the table... i dont want the solution to this as id rather figure it out myself for practice...
the thing im stuck at is i can do this with a cursor but i want to avoid cursors in future, how would i use a set based approach to get each value of a table and work with it?
View 4 Replies
View Related
Jul 20, 2005
Hi,I am trying a Set Approach instead of Using of Cursor (which works).I am attaching the SQL to create tables and the my Procedure, and apiece of code to execute the Procedure.I would like the Procedure ReplaceTags to work with 'a' the same aswith 'C'.Thanks in advance.Hareesh/*****************************//* Create Tables */IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =OBJECT_ID(N'GlobalTags') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)DROP TABLE GlobalTagsGOCREATE TABLE GlobalTags(Project VARCHAR(50) NULL,TagName VARCHAR(50) NULL,[Value] VARCHAR(50) NULL)GOIF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =OBJECT_ID(N'ProductDetails') and OBJECTPROPERTY(id, N'IsUserTable') =1)DROP TABLE ProductDetailsGOCREATE TABLE ProductDetails(Project VARCHAR(50) NULL,KeyName VARCHAR(50) NULL,[Value] VARCHAR(50) NULL)GO/*********************************//* Populate Tables */TRUNCATE TABLE GlobalTagsINSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'FirstName', 'John')INSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'LastName', 'Doe')INSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'PhoneNo', '5248')INSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'ZIPCode', '55555')TRUNCATE TABLE ProductDetailsINSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'FirstName', '%FirstName%')INSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'LastName', '%LastName%')INSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'PhoneNo', '%PhoneNo%')INSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'ZIPCode', '%ZIPCode%')/****************************//* Procedure */IF EXISTS (SELECT * FROM sysobjects WHERE name = 'ReplaceTags')DROP PROCEDURE ReplaceTagsGOCREATE PROCEDURE ReplaceTags(@aProjectName VARCHAR(50),@aProcessType CHAR(1))ASBEGINDECLARE @TagName VARCHAR(50)DECLARE @Value VARCHAR(50)IF @aProcessType = 'C'BEGINDECLARE REPLACE_CURSOR CURSOR FAST_FORWARD READ_ONLY FORSELECT TagName, Value FROM GlobalTagsWHERE Project = @aProjectNameOPEN REPLACE_CURSORFETCH NEXT FROM REPLACE_CURSOR INTO @TagName, @ValueWHILE (@@FETCH_STATUS = 0)BEGINUPDATE ProductDetailsSETValue =CASE WHEN CHARINDEX('%' + @TagName + '%', Value, 1) > 0THENREPLACE(Value, '%' + @TagName + '%', @Value)ELSEValueENDWHERE Project = @aProjectNameFETCH NEXT FROM REPLACE_CURSOR INTO @TagName, @ValueENDCLOSE REPLACE_CURSORDEALLOCATE REPLACE_CURSORENDELSEBEGINUPDATE ProductDetailsSETValue =CASE WHEN CHARINDEX('%' + GlobalTags.TagName + '%',ProductDetails.Value, 1) > 0 THENREPLACE(ProductDetails.Value, '%' +GlobalTags.TagName + '%', GlobalTags.Value)ELSEProductDetails.ValueENDFROM ProductDetails INNER JOIN GlobalTagsON (ProductDetails.Project = GlobalTags.Project)WHERE ProductDetails.Project = @aProjectNameENDEND/***********************************//* Run Procedure */EXECUTE ReplaceTags 'ProjectName', 'a'-- EXECUTE ReplaceTags 'ProjectName', 'C'SELECT * FROM GlobalTagsSELECT * FROM ProductDetails/* End*//***********************************/
View 6 Replies
View Related
Apr 25, 2008
Lets say I have a dynamic table of 10 to 30 varchar(255) columns that I do not know the names of.
Each of these columns is updated with either an 'OK' or an error msg.
Here's the issue. I have a Status column (varchar(MAX)) which I want to be the concatenated sum of all the other columns. I'd like to do this via a trigger because the way I understand it, theres no way a trigger can skip over another update/trigger and write the wrong 'Status' value.
Example Row 1:
RandomColName1 = "1||Access Denied"
RandomColName2 = "0||OK"
RandomColName3 = "2||ID10T Error at Kbrd"
Status = "[RandomColName1] Access Denied | [RandomColName3] = "ID10T Error at Kbrd"
Example Row 2:
RandomColName1 = "0||OK"
RandomColName2 = "0||OK"
RandomColName3 = "0||OK"
Status = "0||OK"
The rules:
1. There is no chance of someone updating the same column at the same time.
2. There's a good chance that other columns in the same row will be updated at the same time.
3. Sometimes a column can be updated every 5 secs.
4. About 100 to 5000 Rows
I'm a SQL newb and this would be my first trigger to write (lol) but I feel if it could be done without causing a DEADLOCK then it should be done this way. Unless someone with more experience can shed some light
With up to 300 .net clients over fast and slow links, I don't want to xfer the whole 5000 row / 50 column table back to the app and have it weed it out, especially if the client only needs 100 of the rows. On the same token, I don't want to make an individual call back to the SQL server should the client need 4000 rows.
Thanks for the help!
View 9 Replies
View Related
Jul 4, 2007
I have a scenario in mind and was wondering if anyone had any suggestions on an approach.
Lets say I have a dataset where I have captured various attributes about blade servers in a rack. I have internal temperature, fan speed, disk reads, cpu temperature and failure events. Each of these are continuous variables except for the failure event, which has only two states, 'true' or 'false' which indicates whether a failure occurred at that point in time.
The table looks like this:
MachineID
Timestamp
Temperature
FanSpeed
Reads
CPUTemp
Failure
At the time of failure, only the MachineID, Timestamp and Failure values will be populated in the table. The failures at this time are not categorized or discriminated from each other in any way although they will be in the future. We're looking to use a mining algorithm to determine which variable is the best predictor of failure, or combination of variables. What do you think is the right approach? How might this approach change once the failures do become categorized and differentiated?
Thanks,
Frank
View 4 Replies
View Related
Aug 13, 2007
Background
I use four database connections strings.
I have about 30 packages that will use one or more of the connection stings.
I store the connection strings in XML configuration files.
I know that I can share configuration files across packages. Should I ?
Have one configuration file with all 4 connection strings? If I use this approach will I get errors in the packages that only use 1 of the connection strings OR
Create four separate configuration files (CnnString1.dtsconfig, CnnString2.dtsconfig, CnnString3.dtsconfig, CnnString4.dtsconfig) and use the appropriate ones for each package
Take another approach
View 5 Replies
View Related
May 23, 2008
Let me use a simplied example for what I'm trying to do.
Say, I have the following source tables.
T_EmpDept
Emp_Name, Dept_Name
John , IT
Mary , IT
Ted , HR
T_Employee
EmpID, Emp_Name
1 , John
2 , Mary
3 , Ted
T_Dept
DeptID, Dept_Name
1 , IT
2 , HR
Now, I wan to populate a target table that looks like
T_Target
EmpID, DeptID
1 , 1
2 , 1
3 , 2
So, it's basically a lookup by name and translated to the appropriate keys.
The way I've done this before, is I do one lookup at a time in serial (one data stream) and get the keys I need. But I want to do them in parallel (split the data stream in two), which I believe involves a Multicast Task and some sort of a Merge Task. I used to shy away from the parallel lookup because I never really understood how to bring the two data streams back together.
So, the question I have is this. Are Multicast and Merge task the right tasks to use for this? I don't want to go researching something that may not be appropriate.
Lastly, if someone has a link that can lay this out for me, I would appreciate it.
View 7 Replies
View Related
Oct 9, 2007
I don't have much background with SSIS or SQLServer or development for that matter. I was thrust in to this and I apologize if my questions are not up to snuff in any way.
I have built an SSIS package based on the import/export wizard and have customized it by adding a For Each Loop container for processing multiple source data files and it works great so far but I need to add some advanced functionality to make it more flexible. I'm having some problems with the best way to enhance the solution and I'm getting frustrated with variables and expressions, which is what I think I need to use. Hopefull what I need is something that's doable.
The package processes mutliple source files using the For Each Loop. The data is stored in a destination table that is dropped and recreated each time. I've also added a couple of CREATE INDEX statements to the source SQL.
I need to make the SQL DROP, CREATE TABLE and INDEX statements dynamic based on part of the filename string. I'm capturing the filename in the FOR EACH container using Variable Mapping. What I need to do is parse out part of the string from the filename and use it in the SQL statements. An example would be CREATE TABLE XXX_[stringvar] where [string] comes from the variable mapping done in the For Each loop.
My thinking was to setup a new variable within the scope of the SSIS package and enable Evaluate as Expression and then build an expression for the varible where I parse out the string from the filename variable that already exists. So if the filename was filename.filetype and I needed the last 4 characters from the filename the string value would be whatever characters were in 'name' each time. But there isn't an expression builder editor built in to the variable properties expression element, so I'm not clear that I can actually create a variable in SSIS and then have it's value be set using an expression. It seems to me that if this was doable, then the expression builder I've seen elsewhere in SSIS would be visable.
Then I was hoping I could edit the SQL behind the DROP and CREATE statements where I've hard coded the table name and insert the variable right in the SQL. My SQL isn't that strong and I don't know how to work with variables but I was thinking the SQL would look like CREATE TABLE XXX_@[stringvar].... I believe another approach would be to setup another string variable for the entire SQL statement and build the statement string dynamically. But I don't know if this is necessary or better.
Anyway, I hope someone can help and that my original post is clear enough.
Thanks
View 4 Replies
View Related
Jul 12, 2007
Hello everyone,
I'm creating a database for a new application and I'm currently facing a design problem, regarding a business requierment for the membership module.
The Membership Module of the application has several business requierments, specified by the client. One of them is the ability to add and remove details about their members. So far, I've created a schema, named Person, which will contain a number of tables responsible for everything related with the membership, as an individual.
To help you guys understand the design I'm trying to implement, I'll post the fields of two of the tables that belong to the Person schema, as follows:
Person.Base
Id (uniqueidentifier)
UniqueIdTypeId (uniqueidentifier)
UniqueId (uniqueidentifier)
Password (char(88))
PasswordSalt (char(10))
PasswordRecoveryQuestion (nvarchar(256)) [NULLABLE]
PasswordRecoveryAnswer (char(88)) [NULLABLE]
CreationDate (datetime)
AuthenticationWindowStartDate (datetime)
AuthenticationWindowAttemptCount (tinyint)
IsActive (bit)
IsBanned (bit)
IsLocked (bit)
Status (bit)
Person.Emails
PersonId (uniqueidentifier)
EmailAddress (nvarchar(256))
CreationDate (datetime)
ValidationCode (char(10))
ValidationDate (datetime) [NULLABLE]
IsValid (bit)
IsPrimary (bit)
IsRollbackTarget (bit)
Status (bit)
So far so good. This design works great to preserve the data integrety. Nonetheless, this is where the problems start. Now, imagin you need to let someone from that company add an item to the user (through the application). Let's say we want to allow the company application manager to add an item to the person called "PreviousEmployer". Such item would then be used for statics, thus would probably need to be indexed.
In order to meet this business requierment I would create some addicional tables. Let's get started:
Person.CustomFields
Id (uniqueidentifier)
Name (nvarchar(50))
Description (nvarchar(3000)) [NULLABLE]
Status (bit)
Person.CustomField_Value
FieldId (uniqueidentifier)
Value (nvarchar(450))
This could work just fine if both are indexed (that's why the nvarchar size is set to 450). But I'm guessing this is far from the optimal solution for many reasons, one of them being the efficiency of the index if the company decides to go and use this for a flag (true or false [bit]). Another "solution" breaking scenario would be if the company wants to add the CV of the person, situation in which we were unlikely to be able to add a file in this datafield.
How would you guys approach this issue? The bottom line is that the client needs to be able to add pretty mcuh any type of custom field and perform searches againts it. So, besides being a dynamic solution it needs to be efficient.
Best regards and thanks in advance.
View 11 Replies
View Related
Mar 8, 2007
I am new to DTS/SSIS and would like some input on an approach to solve a problem.
I have a solution using SQL Server 2005 and another legacy solution running on Access. Status records are written to a table in SQL Server and as they are written, I need to write a record to a table in Access. This needs to be as real-time as possible.
I thought about writting a managed stored procedure in C# so I could also access MS Access, but someone pointed me to DTS.
Records are constantly written to the status table and need to remain in there even after they are processed.
Can you recommend an approach or an article to read?
Thanks,
Steve
View 1 Replies
View Related
Aug 1, 2007
Hi there! I'm looking for advice/suggestions on how to approach a query that's going to be much more complex than I have previously had to deal with.
I have a web form where users can select from a number of possible search parameters (say, City, State, Zip Code, etc...) and pull a list of users from this database, and can select the specific data fields they want displayed as well. The complicated part is that the possible search parameters (and data to display) are spread out over several tables.
How should I go about tackling this little monster? If it's any help, I am using Visual Web Developer 2005 Express Edition and Microsoft ASP.NET Web Matrix to create this form.
Thanks a million,
Kaiti
View 2 Replies
View Related
Dec 15, 2004
Hi,
I have 2 sets
Set1 has few UNIQUE items say workitem Ids 2,3,4
Set2 has multiple items say workitems Ids is 1,2,2,4,3,5,4,3,6,7,8
I need to look for Set1 in Set2 so i get 2,3,4 etc...
which is the best way
Set2 Left join Set1
OR
Select item from Set2 where item in (Set1)
View 3 Replies
View Related
Apr 6, 2008
I have a single table on database A that needs to be replicated to two databases B and C in as-near-to-real-time as possible. The table is cleared every morning, and receives about 40,000 records per hour. Records are only added to the table, never updated or deleted.
I'm wondering what is the best approach to replicate this table :
1 - Transactional Replication
2 - A trigger on the INSERT on table in A that then inserts the record into B and C
3 - Timed jobs running on B and C that pull new records from A
thanks all in advance.
View 1 Replies
View Related
Feb 17, 2015
What is the best approach that I can take for the below requirement.
I am designing a tool that basically stores the information of employees. Once a person joins any organization, they need to enter their personal details through this tool.
The basic information that a person is required to enter includes
Firstname, Lastname,DOB, Phonenumber, permanentaddress,tempaddress,etc.,
Along with these details, the user also needs to upload their photo and their educational details and upload all relevant certificates.
I have designed a table for employee personal details as 'Employeedetails' that includes columns
Empid
EmpFirstname,
Emplastname,
EmpPhonenumber,...
Now to store the employee photo and their educational certificates, I wanted to know , what would be the right approach.
I have googled on this and came across the concept of Filetable in sqlsever 2012. But it behaves independent of other tables. How would I store employee details and have a reference to filetable that can store images and other docs in a physical location. Or is it a good way of storing images and docs in DB directly.
View 0 Replies
View Related
May 9, 2008
I have an online store with products and categories. The requirements are: Each product can be in one or more categories; a product must be in at least one category.
To accomplish the first requirement, I have a Product table, Category table, and a many-to-many Product2Category table. (Probably not relevant, but a requirement is the Category table is self-referencing to provide sub-categories.)
My question is for the second requirement that each Product be in at least one category. My assumption would be to add a column to the Product table called "ParentCategoryId", which is a foriegn key of the Category table's identity column.
What would be the best approach to ensure a product is in at least one category?
Thank you.
View 2 Replies
View Related
Mar 18, 2008
SELECT DISTINCT WO_NO, wo_type, wo_status (incomp,compl,open), wo_status_code, wo_create_date, wo_post_date
I need to do a stored proc in sql server 2005 where I have to count the total work orders based on their wo_type + wo_status, i.e. open, compltd, incomplete. For example I may have (wo_types) AB, AC, AD, orders that are in open status. I need to count the total AB_Type, AC_Type, AD_Type then the total of all 3 in another column (as Total Open). I must do this for all wo_status.
I also have to allow the user to be able to enter the date they want. I.e. if they would want to know on 3/17/08 how many orders were opened that day only. I know I have to use a parameter for this but how do i do that?
Should I do separate select statments for each status, multiple tables ?
Also for 'open' wo_status the wo_status_code can change from O to S but it is still considered 'open'. For this wo_status how can I get the most recent status_code for the open order, i.e., I want to count a particular 'open'work order's - most recent status_code in the work order count for a given wo_type -->> the order should only be counted once on any given date entered. So if the status changed from O then S the same at 1pm then 2pm, respectively, then only the 2pm status should be counted. Thanks in advance for your assistance.
View 2 Replies
View Related
Oct 7, 2005
My SQL acumen stems from just a couple courses, and everything since fromthe trenches. Fun + angst over time.I'm needing some advice on joins. Though I understand the basics, I'mhaving problems abstracting from instances where it's easy to think aboutdiscrete key values (. . . and studentid = 1234) to entire sets of users,with the joins doing their work.For example, currently I'm going nuts trying to return dates for whichattendance has not been taken for students, but should have been. Studentshave active and inactive periods of enrollment in our schools, so we have ahistory table of when they were active and inactive -- as well as two moretables that layer other bounds on eligible dates (what range of dates fallwithin a given school's term? What of holidays and staff institute days?).I also have a populated calendar table, and a table where students areidentified. Finally, there's a site history table which is a REAL pain inthe butt for me to think about.CREATE TABLE Student (StudentID int IDENTITY(1,1) NOT NULL,CurrentStatus varchar(2) NOT NULL)CREATE TABLE Calendar (Dateid int NOT NULL,Date datetime NULL,Workdaybit NULL )CREATE TABLE DailyAttendance (StudentID int NOT NULL,AttendanceDate datetime NOT NULL,SiteID varchar(6) NOT NULL,Attend_Status varchar(2) NOT NULL)(the last field is, e.g., present or absent)CREATE TABLE StudentActivityHistory (StudentID int NOT NULL,StatusStartDate datetime NOT NULL,StatusEndDate datetime NULL,Activity_Statusvarchar(2) NULL,StudentStatusHistoryIDint IDENTITY(1,1) NOT NULL)(the activity_status is either A or I; the important records in this tableare the 'A' records. A student's most recent status record always has anend date of '12/31/9999 12:00:00 AM', whether that's an A or I record. Nodates not between start/end dates of students' A records would needattendance taken. students may have many periods of activity -- A records-- as well as many inactive periods.)CREATE TABLE SiteTerms (SiteID varchar(6) NOT NULL,Term varchar(3) NOT NULL,StartOfTerm datetime NOT NULL,Quarter varchar(2) NOT NULL,SchoolYear varchar(9) NOT NULL,EndOfTerm datetime NOT NULL)(different schools vary their term start and end dates. No dates notbetween term start and end dates would need attendance taken by studentsassigned to and active in that school during that period.)CREATE TABLE SiteExceptionDays (SiteID varchar(6) NOT NULL,SchoolDayStartTimedatetime NOT NULL,SchoolDayEndTime datetime NOT NULL,SchoolDayType varchar(2) NOT NULL)(there are two kinds of days -- partial attendance, and no attendance. Inshort, if the type of day is "N" no attendance needs to be taken forstudents assigned to that school and active on that day)CREATE TABLE StudentSiteHistory (StudentID int NOT NULL,SiteStartDate datetime NOT NULL,SiteID varchar(6) NOT NULL,SiteEndDate datetime NULL,StudentSiteHistoryIDint IDENTITY(1,1) NOT NULL)(Pain. The attendance table tells which site a student was assigned whenattendance was taken. To find which school a student was a assigned to ondays attendance was NOT taken, this table's implicated 'cause it's the onlyway of connecting everything else together)Dangitall, I know this can be done but I've beat my head against the wall.Due diligence has gotten me a headache and a hankerin' for whiskey, and I'mnot much of a drinker. Is there anyone in the group for whom this kind ofthing is a no-brainer? I'd just as soon get some tips on how to approachthis kind of thing, and figure it out myself with some guidance.Any takers? Gotta run, dang I'm late for something.TIA--Scott
View 4 Replies
View Related
