Hello friends,
I needed a suggestion, I am currently working on a reporting website that generates reports and i need to store all the reports in the database.
I usually go by row wise processing as it can be easily controlled but the problem is there will be a lot of reports, that is an estimation of 30,000 rows in a month and i m not sure if sql server can hold more than 2 billion rows per table.
Please Help!!
Hello everybody!
I have question about indexed and not indexed Persisted columns on sql server 2005. It's a bug?
First?, my version of SQL Server is
Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86) Aug 11 2007 03:13:58 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Now I create two tables and try four select queries:
Code Snippet
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
GO
create table t1 (id int primary key, id_bigint as cast(id as bigint))
GO
create table t2 (id int primary key, id_bigint as cast(id as bigint) persisted)
GO
select * from t1 -- (1)
-- Clustered index scan with two times Compute Scalar
GO
select * from t2 -- (2)
-- Clustered index scan with one times Compute Scalar
GO
create index IX_t2 on t2 (id_bigint)
GO
select * from t2 -- (3)
-- Index Scan with one times Compute Scalar
GO
select * from t2 where id_bigint = 0 -- (4)
-- Index Seek with one times Compute Scalar
GO
drop table t1
GO
drop table t2
GO
SET ANSI_PADDING OFF
1. I don't understand why access to computed column raise scalar computation wto times?
2. I don't understand why access to persisted computed column raise any scalar computation?
3. I don't understand why access to persisted computed column over index required any scalar computations?
Can anyone from Microsoft SQL Server Team told me about this mistake?
It's a BUG or I incorrect understand value of the "PERSISTED" word?
--
Thanks with avanced.
WBR, Roman S. Golubin
grominc[at]gmail.com
Hi,
I have to implement a complex algorithm that processes each row and each column.I have bunch complex steps at the end of which a table gets populated with the processed columns.
My question is whether it is possible and feasible to do this kind of processing using CLR integration or should I stick to T-SQL ?
One of the steps of processing involved per column is as follows:-
1)For each column,find successive invalid values from the start of the column.Invalid value= any value <0
2)find the invalid value depth of each column(no of successive invalid values from start)
3)If after these invalid vlaues,there is a valid value and another invalid value,replace current invalid value with valid value.
ie replace invalid value only if it has a valid value above it.
4)find the column with maximum invalue value depth and delete that many rows from the table.
Here's an example:-
Suppose there are 2 columsn colA and ColB.The columns have different datatypes eg decimal,int,string etc.
For simplicity colA and colB are ints.
RowID is keeping track of the row number.
suppose colA has the following data
RowID ColA
-----------
1 0
2 -5
3 -3
4 1
5 4
6 -9
7 5
8 8
Step1)successive invalid values from start=0,-5,-3
Step2)Invalid value depth=3(because there are 3 rows from step 1)
Step3)0,-5,-3 do not have any valid value above them.But -9 has a valid value 4 above it.So replace -9 with 4.
so colA after the algo will look as follows
RowID ColA
------------
1 0
2 -5
3 -3
4 1
5 4
6 4(replaced -9 with 4)
7 5
8 8
Now do the next column colB
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 -5
8 -8
Step1)successive invalid values from start=-6,0,0,-7
Step2)depth of invalid values=4
Step3)Next invalid value -5 occurs at RowID 7
and has a valid value 8 above it.
Replace -5 with previous valid vlaue ie 8.
RowID 8 has invalid value -8 .Its previous invalid value(-5) got replaced by a valid value 8.So replace RowID8 also with value of RowID 7 ie 8
Output at the end of these steps
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 8(replaced -5 with 8)
8 8(replaced -8 with 8)
Step4:Get the maximum invalid value depth
In this case ColB had depth=4 which is greater than ColA which had dept=3.
So delete 4 rows from the beginning of the table
So the output will be
RowID colA colB
----------------------------------------
5 4 4
6 4(replaced -9 with 4) 8
7 5 8 (replaced -5 with 8)
8 8 8(replaced -8 with 8)
Thanks in advance for your help
coolaqua
Hi,
I have built a package in which i use a derived column to create a new set of columns and then pass the same to another target transformation.
The issue now what I am facing is, the re are certain number of records coming from source(16 rows) and gets processed before the Derived Column transformation, but after that, no records gets processed after the derived column transformation.
The package status shows as Success, but there is no records being written in the target table.
Any idea what could be the issue here?
Thanks,
Manish
Hi,
I have to implement a complex algorithm that processes each row and each column.I have bunch complex steps at the end of which a table gets populated with the processed columns.
How do I do this using CLR integration?
One of the steps of processing involved per column is as follows:-
1)For each column,find successive invalid values from the start of the column.Invalid value= any value <0
2)find the invalid value depth of each column(no of successive invalid values from start)
3)If after these invalid vlaues,there is a valid value and another invalid value,replace current invalid value with valid value.
ie replace invalid value only if it has a valid value above it.
4)find the column with maximum invalue value depth and delete that many rows from the table.
Here's am example:-
Suppose there are 2 columsn colA and ColB.The columns have different datatypes eg decimal,int,string etc.
For simplicity colA and colB are ints.
RowID is keeping track of the row number.
suppose colA has the following data
RowID ColA
-----------
1 0
2 -5
3 -3
4 1
5 4
6 -9
7 5
8 8
Step1)successive invalid values from start=0,-5,-3
Step2)Invalid value depth=3(because there are 3 rows from step 1)
Step3)0,-5,-3 do not have any valid value above them.But -9 has a valid value 4 above it.So replace -9 with 4.
so colA after the algo will look as follows
RowID ColA
------------
1 0
2 -5
3 -3
4 1
5 4
6 4(replaced -9 with 4)
7 5
8 8
Now do the next column colB
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 -5
8 -8
Step1)successive invalid values from start=-6,0,0,-7
Step2)depth of invalid values=4
Step3)Next invalid value -5 occurs at RowID 7
and has a valid value 8 above it.
Replace -5 with previous valid vlaue ie 8.
RowID 8 has invalid value -8 .Its previous invalid value(-5) got replaced by a valid value 8.So replace RowID8 also with value of RowID 7 ie 8
Output at the end of these steps
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 8(replaced -5 with 8)
8 8(replaced -8 with 8)
Step4:Get the maximum invalid value depth
In this case ColB had depth=4 which is greater than ColA which had dept=3.so delete 4 rows from the beginning of the table
So the table will be
RowID colA colB
----------------------------------------
5 4 4
6 4(replaced -9 with 4) 8
7 5 8 (replaced -5 with 8)
8 8 8(replaced -8 with 8)
Thanks in advance for your help
coolaqua
I am working on a SSIS package. I want error records to be redirected to a different table. Natively, the package passes the Error Code and Column Code (don't know what to call it, but it's a number). I found a script to get the error description, but can't find an equivalent to get the Column name.
Any ideas?
Row.ErrorDescription = _
Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
Hi,I'm constructing a query that will performs a lot o datetimecalculumns to generate columns.All that operations are dependent of a base calculum that is performedon the query and its result is stored in a columna returned.I wanna find a way of reusing this generated column, to avoidreprocessing that calculumn to perform the other operations, causethat query will be used in a critical application, and all saving isfew.Thanks a lot.
View 2 Replies View RelatedHi,
I have to implement a complex algorithm that processes each row and each column.I have bunch complex steps at the end of which a table gets populated with the processed columns.
My question is as to what is the best way to do this?CLR integration or T-SQL?
Also I would appreciate any ideas as to how to go about using either approaches.
One of the steps of processing involved per column is as follows:-
1)For each column,find successive invalid values from the start of the column.Invalid value= any value <0
2)find the invalid value depth of each column(no of successive invalid values from start)
3)If after these invalid vlaues,there is a valid value and another invalid value,replace current invalid value with valid value.
ie replace invalid value only if it has a valid value above it.
4)find the column with maximum invalue value depth and delete that many rows from the table.
Here's am example:-
Suppose there are 2 columsn colA and ColB.The columns have different datatypes eg decimal,int,string etc.
For simplicity colA and colB are ints.
RowID is keeping track of the row number.
suppose colA has the following data
RowID ColA
-----------
1 0
2 -5
3 -3
4 1
5 4
6 -9
7 5
8 8
Step1)successive invalid values from start=0,-5,-3
Step2)Invalid value depth=3(because there are 3 rows from step 1)
Step3)0,-5,-3 do not have any valid value above them.But -9 has a valid value 4 above it.So replace -9 with 4.
so colA after the algo will look as follows
RowID ColA
------------
1 0
2 -5
3 -3
4 1
5 4
6 4(replaced -9 with 4)
7 5
8 8
Now do the next column colB
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 -5
8 -8
Step1)successive invalid values from start=-6,0,0,-7
Step2)depth of invalid values=4
Step3)Next invalid value -5 occurs at RowID 7
and has a valid value 8 above it.
Replace -5 with previous valid vlaue ie 8.
RowID 8 has invalid value -8 .Its previous invalid value(-5) got replaced by a valid value 8.So replace RowID8 also with value of RowID 7 ie 8
Output at the end of these steps
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 8(replaced -5 with 8)
8 8(replaced -8 with 8)
Step4:Get the maximum invalid value depth
In this case ColB had depth=4 which is greater than ColA which had dept=3.so delete 4 rows from the beginning of the table
So the table will be
RowID colA colB
----------------------------------------
5 4 4
6 4(replaced -9 with 4) 8
7 5 8 (replaced -5 with 8)
8 8 8(replaced -8 with 8)
Thanks in advance for your help
coolaqua
I have a DTS that imports data from an orcle database into SQL Server.
Doesn't the processing mostly occur on the SQL Server, not on the
oracle database from which the data is being imported?
The oracle database is vendor provieded and they are saying
our SQL Server DTS package is killing their server.
Any insight is appreciated.
Thanks
Hey All,
I've got a process that creates records in my database based on XML input that I've gotten. What I am doing it giving this XML to a stored procedure to handle a specific task, then modify the XML and send it to the next stored procedure.
For instance, the XML could hold header records with detail records, I would first send the XML to a stored procedure that creates the header records, then updates the XML so the XML now knows the identity values of the header records I have just created, and then send the XML to the next stored procedure to create the details for those headers.
All works great and fine, but I have a problem with writing the identity values back to the XML. It seems I can only change one item in the XML at a time and thus need to loop this. For many records this really takes a long time.
Here is some sample code of what I'm doing (please excuse any typos, this is a simplified version of the code) :
declare @lvSeq numeric(15)
declare @lvRowNo int
declare @lvNumRows int
insert into myHeaderTable (
recid, recdesc
) select
ref.value('@recid', 'nvarchar(25)') recid,
ref.value('@recdesc', 'nvarchar(250)') recdesc
from @pXML.nodes('//headers/header') R(ref)
select @lvRowNo=1, @lvNumRows = @pXML.value('count(//headers/header)', 'int')
while (@lvRowNo<=@lvNnumRows) begin
select @lvSeq = recseq
from myHeaderTable
where recid = @pXML.value('//headers/header[position()=sql:variable("@lvRowNo")]/@recid)
set @pXML.modify('replace value of (//headers/header[position()=sql:variable("@lvRowNo")]/@recseq with sql:variable("@lvSeq")')
select @lvRowNo=@lvRowNo+1
end
Obviously I am looking for a better way to update the XML with the sequences. The insert takes a second, the loop takes minutes with large XML sets. I guess MSSQL is searching the whole XML to find the item to update.
It would be nice if I didn't have to loop through the XML. One solution I was thinking off is to store the XML in a temporary table with a single record per header item. Then I could do the modify in one go and recreate the XML by simply selecting the contents of the temporary tabel. I have no idea if this is possible.
So something like this:
select
ref.value('@recid','nvarchar(25)') recid,
ref.value('.','XML') XMLData -- this gives an error
into #TMP_XML
from @pXML.nodes('//headers/header') R(ref)
insert into myHeaderTable (
recid, recdesc
) select
recid,
ref.value('@recdesc', 'nvarchar(250)') recdesc
from #TMP_XML CROSS APPLY XMLData.nodes('/header') R(ref)
update #TMP_XML
set XMLData.modify('replace ....')
from myheadertable
where #TMP_XML.recid = myheadertable.recid
-- recreate XML here, not sure how....
Is it possible to read a mail message,
store its msgtext in a variable,
decrypt the retreived string,
and execute the string.(wich is a sql statement)
If it is possible wich directions should I take
to implement the above.
Hi…all
I will just explain whole scenario what I m facing in tricky problem..
We have xml files coming at regular interval by some other source into sql server 2000…daily having records near @10000 to 70000…we have job scheduled to run it regular interval…we doing this by some filter criteria… suppose the flow is like staging table into secondary table and then final into primary table….
We design DTS package accordingly means take the records from staging table put into secondary table and then into primary table…(near @ 8 task involved in it…)
Suppose xml file came at 8:30 am and our DTS package will run at 9:00 am…and then 11:00 am and the 1:00 pm like that….what I observing from many days is that after running job at 9:00 am successfully some good data still pending in secondary table not processed into primary table. But when again job ran at 11:00 am it processed that pending good records into primary table…some times when I ran this job manually through DTS design level the good data that pending in secondary table processed!!!
My question is that why this job not processed all the good records in single shot????
T.I.A
Papillon
Hello everyone. I need help regarding the following:Given the following table:CREATE TABLE T1 (C1 nvarchar(10), C2 money)INSERT INTO T1 VALUES ('A',1)INSERT INTO T1 VALUES ('B',2)INSERT INTO T1 VALUES ('C',3)let's say that i have this table in a local server and i want to uploadit to a remote server and in the remote server upload it to a databasethat contains the same table.the uploading part can be done by another application in the remoteserver, but i want i need is a way to transfer the data at the fastestpossible way.what steps do i need to follow?tia,Rey Guerrero
View 10 Replies View RelatedWhich component should be used to process dataset as a whole, and not on per row basis? I have need to process dataset conditionally (condition based on dataset), e.g. if a special row is present in dataset than dataset should be processed in a special way. Should I maybe use one Script Transformation to determine if dataset satisfies condition (and store that result into a variable), and then based on that condition (variable value) perform or not processing (using Conditional Split and Script Transformation)?
View 7 Replies View RelatedHello all,
I am having trouble trying to construct the following process in SSIS/SQL
2005:
1. Grab a set of unprocessed rows (ProcessDT = null) in an 'Action' table
2. For each of these rows, execute multiple stored procedures base on the
action type
If actiontype = 1, exec spAct1a @param1, @parm2
exec spAct1b @param1, @parm2, @param3, @param4
If actiontype = 2, exec spAct2a @param1, @parm2, @param3
exec spAct2b @param1, @parm2, @param3
etc....
3. Update ProcessDT so it's not processed again
4. Repeat until all rows are processed
Note - all sp params are contained in additional columns in the Action
table. Basically the Action table is a store for post-event processing of
sorts
but is order dependent, hence the row by row processing. And some of my
servers are 2000 so Service Broker is not an option (yet).
I first attempted to do this totally within the control flow - using an ado
recordset/foreach loop control, but I could not figure out how to run
conditional
process paths based on the ActionTypeID. I then tried to do this within the
dataflow using on OLEDB data source, a conditional split, and an oledb
command control
which almost got me there - the problem being for each row I need to execute
multiple sp's and it appears as if the oledb command only gives me one sp.
Any help would be appreciated!
Thanks in advance!
I'm populating a new table based on information in an existing table. The new table is a list of all "items" and contains a primary key. The old table is a database of receipts where items can appear many times in any order.
I have put together the off-the-shelf components to do this, using a lookup transformation to see if the item is already in the new table. Problem is, because there's so much repetition in the old table I need to process the old table one row at a time. Batch processing is generating errors because the lookup doesn't detect duplicates within the buffer.
I tried setting the "DefaultBufferMaxRows" property of the task to 1, but that doesn't seem to have any effect.
To get the data from the old table, I'm using an OLE DB source. To get the data into the new table, I'm using the OLE DB Command transformation with parameters to execute an INSERT statement.
This is a job I have to do exactly once, so I don't care if I have to run it overnight. I'd rather have a simple, easy to understand but inefficient script so I understand what it's doing completely.
Any help on forcing SSIS to process one row at a time?
Lee Silverman
JackRabbit Sports
Hi,
I am verifying my reports processing time. I get the information from the Reporting Service DB - [ExecutionLogs] table. I have the following information:
[TimeEnd] €“ time that reports generation ends.
[TimeStart] - time that reports generation starts.
[TimeDataRetrieval] - amount of time spent running the data sources.
[TimeProcessing] - time spent processing the report.
[TimeRendering] - time spent generating the output format.
If this information is correct the following statement should be true:
([TimeEnd] - [TimeStart]) = ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])
But it isn't, ([TimeEnd] - [TimeStart]) is always bigger then ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).
Why does this happen?
Regards,
Rodrigo
can anyone give me query processing for Database mirroring
View 1 Replies View RelatedWhen using the AS processing task with a connection to "an Analysis Services project in this solution", only some processing options are available for processing dimensions. For instance, it is not possible to select "Process Update". Once I change the connection manager to point to the deployed cube database, I can choose from all the options. Is this by design?
View 1 Replies View RelatedI need help from sql experts for the following problem
DOCTYPE DATE QTY PRD LOT
Purchase 1 jan 20+ AA 2007FW
Purchase 4 jan 50+ AA 2007SS
Purchase 9 jan 10+ AA 2007FW
Sale 3 jan 10- AA
Sale 4 jan 20- AA
Returned Good 4 feb 10 AA
As you can see I don't have the LOT code in sales records, so I must update these records with the following logic:
I have to assign LOT code to sales in FIFO order:
from the table above the 3rd of January I find the first sale of 10, take the first LOT (ascending date order), check for qty on hand (20+), consume 10 from LOT (10 remaining), update sale record with 2007FW LOT code.
Then I find next sale of 20, as before I take the first LOT with qty on hand to consume, again it's the first record with only 10 remaining so I set LOT qty on hand to zero but I have 10 more to allocate to a LOT code. So I find next available LOT of 50 and consume 10, with 40 remaining.
It's important to remember that I can sell part of a "lot", have to track the remaining goods per LOT
Is it possible in SQL to do that in batch mode? How? If I have to split sale record consuming 2 or more LOTS, how can I do? Can you show me SQL or good hints?
Thanx in advance
Hi All,
I have 3 cubes in a single SSAS database and these cubes should be processed using the following schedule
Cube 1 - Every Day
Cube 2 - Every Week
Cube 3 - Every Month
Cube 4 - Every Day
The issue that I face is that these cubes share the dimensions and so I cant do a FULL process of these SHARED Dimensions as it will affec other cubes.
I can expect additions and deletions to my dimension data , but the structure remains the same. It would be great if someone can suggest how to go about processing the dimensions. I am confused with the number of options(Process Incremental, Process Update etc.,) available for processing the dimensions.
I will creating a SSIS package to automate the processing. One more question is say, if Cube 2 fails during a day and Cube 1 has succesfully processed on the same day earlier, how do I revert back to the old state of Cube 2? Does this mean that I need to do a back up of the SSAS database before processing each cube?
Apologies if I had missed anything.
Thanks!
Hi,
I am in the midst of designing a new Data Warehouse system. As we get further into the design of the system, the more we are realising how complex our ETL is going to be and that the amount of time it will take to run could be significant i.e. a few days! My question is obviously I don't want to have a down time in my relational system for this long and prevent my users from accessing the data for days at a time each month. So what functionality should I be looking at to allow me to maintain a working copy of the data that users can query whilst I perform database updates and then perform a quick promotion of the updated data to users for querying?
If you can point me in the direction of the right functionality in SQL Server 2005 and possible some relevant white papers that cover this sort of scenario I would be grateful.
regards
Colin
Stored procedure works:
PROCEDURE dbo.VerifyZipCode@CZip nvarchar(5),@CZipVerified nvarchar(5) outputAS IF (EXISTS(SELECT ZIPCode FROM ZipCensus11202006 WHERE ZIPCode = @CZip)) SET @CZipVerified = 'Yes' ELSE SET @CZipVerified = 'Not Valid Zip'RETURN
Need help calling and processing sp information:
protected void C_Search_btn_Click(object sender, EventArgs e){ SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString); SqlCommand cmd = new SqlCommand("VerifyZipCode", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CZip", UserZipCode_tbx.Text);
. . . how do you set up output parameter, call the SP and capture output. . . ? if (@CZipVerified == "Not Valid Zip") { TextBox5.Text = "Zip code not valid please re-enter zip"; } else { Continue processing page }
}
How would I go about checking incoming e-mails? For example, on a certain e-mail address, I would get e-mails formatted in a certain way. According to the response, some scripts need to run/ some sql tables updates etc. How can one do this in (ASP) .NET with SQL Server? Anyone did this kind of stuff before?
View 3 Replies View RelatedSQL 7 SP3
Hi.
I have a trigger that fires for any updates/deletes/adds and logs information via a sp call.
If I have more than one record, is there a better way to process them other than using a cursor ?
Yes, they have to be processed through the sp.
Thanks,
Craig
When I process a new cube I recieve an error "Error (211): Unknown dimension member ' 8'; Time: 8/11/00 1:09:45 PM". Any ideas about what this error is about ?
View 2 Replies View Relatedhii...
Is there anybody out there that can help me on how can I know the processing time taken for one transaction by using SQL Analyzer??
1)For example, I want to update using Analyzer and I would like to know time taken to do this update???
2) How to reduce processing time by using Store Procedures that using cursor?? I have add in some commit statement in my update statement.. Is there any other ways??
Please advise
I've noticed lately that my queries through ADO/VB are taking alot longer to process at certain times. The query and the result information never changes, only that at certain times the query takes alot longer than usual. I thought that I possibly need more licenses, or it might be network traffic. I currently use MS SQL Server 2000 small business Ed(5 Cals).
Has anyone any information about performance problems due to licensing issues?
Thanks,
Dave Cohoon
Hello DBA's:
I need to extract a particular file from our AS 400 system on a daily basis and do some processing on it. Also I want to do the daily processing only on those records that have been added/updated since the initial load.
Here is the approach and possible implementation.
Approach
I have the DB2 source data containing 10 columns (Col1 to Col5 together form the key) and the rest are attributes. I am interested only in the key and two attributes. So I load my table with only Col1 to Col7 ( 5 for key and the two attributes). I then do my processing on this table.
Here is the implementation given by a member of dbforums -
You'll then have to deal with 3 potential actions,
INSERT: New records on the file.
DELETES: Records that don't exists.
UPDATES: Records that are on the file, but attributes have changed.
You had given me this code template.
CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))
CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
GO
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED
INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL--NO CHANGE
SELECT '1','2','x','y','z' UNION ALL-- UPDATE (My comment - Instead of an update I want to insert a new record)
SELECT '1','3','g','h','i' UNION ALL--NO CHANGE
SELECT '2','3','a','b','c'--INSERT
GO
SELECT * FROM myTable99
SELECT * FROM myTable00
GO
--DO DELETES FIRST (My comment - Before deleting, I want to copy the rows that I am going to delete on a separate table to maintain history. Then I want to delete from a). I don't get the logic. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =. why the where clause condition)
DELETE FROM a
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- INSERT (My comment - I don't get the logic of the where. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =)
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- UPDATE
UPDATE a
SET Col3 = b.Col3
, Col4 = b.Col4
, Col5 = b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO
------------
Can anybody look at My comments and answer them or revise this code template if need be?
Brett Kaiser - I sent you an e-mail on this. Can you respond to it when time permits.
Thanks
Hi All,
When I try to process my mail with xp_findnextmsg or xp_readmail or xp_sendmail i get these errors:
Server: Msg 18025, Level 16, State 1, Line 0
xp_findnextmsg: failed with mail error 0x80004005
Queries processed: 0.
Server: Msg 18025, Level 16, State 1, Line 0
xp_readmail: failed with mail error 0x80004005
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80004005
my configuration is:
ms sql 2000 sp3
outlook 2003 as mapi client
sql server is started under account for which mapi profile is configured
Does anyone know what is wrong, and how to solve this problem?
Thanks
Hi everyone!
I created a DTS which does some data transformations before processing some cubes. It finished processing in abt 10mins when I run this DTS manually. However, when I schedule this DTS to run, it took around 3 over hours to run. Does anybody know where the problem lies? I have been looking for a solution for this for a long time and I'm hoping that somebody can help me...
Thank you!! :)
Michelle
Hi,
I am trying to Process a analysis server Cube and I am getting an error message saying
syntax error converting the varchar value A.H to column of type int.; 22018.
Can somebody tell me how to remove this error.
Thanks,
Praveen
I did Index defragmentation a week ago . for 1 database only , In the middle of rebuild I kill the process twice cause It takes more than 1 hour so I killed it and wonder how many high level fragmented indexes left ...
View 2 Replies View Related