do log files also store select statement?

do log files also store select statement?Do they store any info wrt indexes. If I had clustered and non clustered
indexes , does the tran log backup files hold any info about the indexes.
Will i see reduced size in log file backups if i drop the indexes
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:08bc01c35c85$9fdf4080$a301280a@.phx.gbl...
> Nope. You can not rollback a select statement and it is
> not a transaction. TO see the current log you can issue
> select * from ::fn_dblog(null,null)
> HTH
> Ray Higdon MCSE, MCDBA, CCNA
> >--Original Message--
> >
> >
> >.
> >|||Hassan
The transaction logs capture all changes to your database,
as Ray said they don't record selects (except select into)
because they don't change the database.
When you insert, update or delete as well as making
changes to the data you can be making changes to the
indexes. So yes by dropping indexes (although the drop
will by caught by the log), you will reduce your logging a
small amount.
However indexes are usually there to aid performance, I
would be very careful about droping indexes to keep the
log smaller. The impact on your system is likely to be
higher than the small saving in log space. I would not
advise it unless you perform a very good audit of your
indexes and find that some are not being used.
If you are having problems with your log size, you may be
better off with performing transaction log backups more
often.
Hope this helps
John

Do I need to Reindex after truncate table?

Hi All,
A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
truncate table although the index(es) is still there but the data page
is 0.
Thank you for your help.
BobAfter truncating a table there is nothing to reindex. The index definitions
are still there and will apply to the new records.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"dengcharles@.hotmail.com" wrote:
> Hi All,
> A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
> statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
> truncate table although the index(es) is still there but the data page
> is 0.
> Thank you for your help.
> Bob
>|||Regarding your statement that "index(es) is still there but the data page is
0" you can use sp_spaceused 'your_table' to verify that everything is 0
(rows, data, index, etc).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
> After truncating a table there is nothing to reindex. The index definitions
> are still there and will apply to the new records.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "dengcharles@.hotmail.com" wrote:
> > Hi All,
> >
> > A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
> > statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
> > truncate table although the index(es) is still there but the data page
> > is 0.
> >
> > Thank you for your help.
> >
> > Bob
> >sql

Do I need to Reindex after truncate table?

Hi All,
A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
truncate table although the index(es) is still there but the data page
is 0.
Thank you for your help.
BobAfter truncating a table there is nothing to reindex. The index definitions
are still there and will apply to the new records.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"dengcharles@.hotmail.com" wrote:

> Hi All,
> A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
> statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
> truncate table although the index(es) is still there but the data page
> is 0.
> Thank you for your help.
> Bob
>|||Regarding your statement that "index(es) is still there but the data page is
0" you can use sp_spaceused 'your_table' to verify that everything is 0
(rows, data, index, etc).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> After truncating a table there is nothing to reindex. The index definition
s
> are still there and will apply to the new records.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "dengcharles@.hotmail.com" wrote:
>

Do I need to Reindex after truncate table?

Hi All,
A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
truncate table although the index(es) is still there but the data page
is 0.
Thank you for your help.
Bob
After truncating a table there is nothing to reindex. The index definitions
are still there and will apply to the new records.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"dengcharles@.hotmail.com" wrote:

> Hi All,
> A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
> statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
> truncate table although the index(es) is still there but the data page
> is 0.
> Thank you for your help.
> Bob
>
|||Regarding your statement that "index(es) is still there but the data page is
0" you can use sp_spaceused 'your_table' to verify that everything is 0
(rows, data, index, etc).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> After truncating a table there is nothing to reindex. The index definitions
> are still there and will apply to the new records.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "dengcharles@.hotmail.com" wrote:

Do I need DatePart or similar

I have a function that uses the following statement in it

SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref

WHERE (src_terrier.datadate = @.dt_src_date) AND
(src_terrier.Areacode = @.chr_div) AND
(src_centre_list.Portfolio_no = @.vch_portfolio_no) AND
(src_centre_list.propcat = @.vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)

The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows

src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.

How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.

Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.

Anybody confused by that , cause I am!

Regards

yes, one way is using DatePart to compare the day,month and year separately.
Then you do not have to change the input format of mmddyyyy to ddmmyyyy.

Ex:

SET DATEFORMAT mdy
DECLARE @.Inputdate datetime
SET @.Inputdate = '04/28/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate1 datetime
SET @.Tabledate1 = '28/04/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate2 datetime
SET @.Tabledate2 = '01/04/2006'

if ( datepart(dd,@.Inputdate)= datepart(dd,@.Tabledate1) AND
datepart(mm,@.Inputdate)= datepart(mm,@.Tabledate1) AND
datepart(yyyy,@.Inputdate)= datepart(yyyy,@.Tabledate1) )
PRINT 'SAME DATES'
ELSE
PRINT 'DIFFERENT DATES'

If ( datepart(mm,@.Tabledate1)= datepart(mm,@.Tabledate2) AND datepart(yyyy,@.Tabledate1)= datepart(yyyy,@.Tabledate2) )
PRINT 'SAME MONTH AND YEAR'
ELSE
PRINT 'DIFF MONTH/YEAR'

GO

|||

The query search condition (WHERE clause) doesn't quite match what you are explaining. If you want to ignore the day part in the datetime value then you can use an expression like below:

select dateadd(day, 1 - day(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP )

Please substitute CURRENT_TIMESTAMP with the date column or variable as required. Note that if you use functions or expressions on columns then the index if any on that column cannot be used to efficiently seek to the value. So performance of the query might suffer if your primary search/join condition is based on the date column. For example, you can modify search condition below:

(src_terrier.datadate = @.dt_src_date)

to:

(dateadd(day, 1 - day(src_terrier.datadate), src_terrier.datadate) = @.dt_src_date)

Assuming that @.dt_src_date is specified as YYYYMM01 then it will match fine. Another way to optimize the search is to do the following:

(src_terrier.datadate >= @.dt_src_date and

src_terrier.datadate < dateadd(month, 1, @.dt_src_date) - 1)

In this search condition, we use a lower and upper bound for the date values which is month start and month end. So it will match any date value in "datadate" column for the specific YYYYMM combination in @.dt_src_date.

|||

In addition to my previous question relating to datepart

I have two entries in a table

site_ref src_date budget_rent budget_occ

AE 01/04/2006 123456.0000 69

AE 01/05/2006 371540.2432 74

When I run the following query

SELECT site_ref, budgeted_net_rent, budgeted_occupancy
FROM src_tbl_rental
WHERE (src_date >= @.dt_src_date) AND (src_date < DATEADD(month, 1, @.dt_src_date) - 1)

I get a result set that includes both of the above lines, when what I was trying to achieve was just the value for the first one only. I know that the date is entered in the database as 01/04/2006 as part of an import from excel. Not sure wther it is actually yyyy-mm-dd etc in reality, but from a visual point of view it certainly is dd/mm/yyyyy.

If I manually enter the (@.dt_src_date) parameter of 01/04/2006 then it returns only the one line. The problem I have is that the parameter that I mentioned actually needs to provide dd/mm/yyyy data for the bulk of the other functions etc, I want this query to only return a value that will always the mm/yyyy element of the parameter value and prefixed by 01/.

Anybody help me out here?

Regards

|||

Problem Resolved by using

WHERE (src_date >= CONVERT(varchar(6), @.dt_src_date, 112) + '01') AND (src_date < DATEADD(month, 1, CONVERT(varchar(6), @.dt_src_date, 112) + '01') - 1)

Regards

sql

Do I need DatePart or similar

I have a function that uses the following statement in it

SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref

WHERE (src_terrier.datadate = @.dt_src_date) AND
(src_terrier.Areacode = @.chr_div) AND
(src_centre_list.Portfolio_no = @.vch_portfolio_no) AND
(src_centre_list.propcat = @.vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)

The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows

src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.

How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.

Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.

Anybody confused by that , cause I am!

Regards

yes, one way is using DatePart to compare the day,month and year separately.
Then you do not have to change the input format of mmddyyyy to ddmmyyyy.

Ex:

SET DATEFORMAT mdy
DECLARE @.Inputdate datetime
SET @.Inputdate = '04/28/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate1 datetime
SET @.Tabledate1 = '28/04/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate2 datetime
SET @.Tabledate2 = '01/04/2006'

if ( datepart(dd,@.Inputdate)= datepart(dd,@.Tabledate1) AND
datepart(mm,@.Inputdate)= datepart(mm,@.Tabledate1) AND
datepart(yyyy,@.Inputdate)= datepart(yyyy,@.Tabledate1) )
PRINT 'SAME DATES'
ELSE
PRINT 'DIFFERENT DATES'

If ( datepart(mm,@.Tabledate1)= datepart(mm,@.Tabledate2) AND datepart(yyyy,@.Tabledate1)= datepart(yyyy,@.Tabledate2) )
PRINT 'SAME MONTH AND YEAR'
ELSE
PRINT 'DIFF MONTH/YEAR'

GO

|||

The query search condition (WHERE clause) doesn't quite match what you are explaining. If you want to ignore the day part in the datetime value then you can use an expression like below:

select dateadd(day, 1 - day(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP )

Please substitute CURRENT_TIMESTAMP with the date column or variable as required. Note that if you use functions or expressions on columns then the index if any on that column cannot be used to efficiently seek to the value. So performance of the query might suffer if your primary search/join condition is based on the date column. For example, you can modify search condition below:

(src_terrier.datadate = @.dt_src_date)

to:

(dateadd(day, 1 - day(src_terrier.datadate), src_terrier.datadate) = @.dt_src_date)

Assuming that @.dt_src_date is specified as YYYYMM01 then it will match fine. Another way to optimize the search is to do the following:

(src_terrier.datadate >= @.dt_src_date and

src_terrier.datadate < dateadd(month, 1, @.dt_src_date) - 1)

In this search condition, we use a lower and upper bound for the date values which is month start and month end. So it will match any date value in "datadate" column for the specific YYYYMM combination in @.dt_src_date.

|||

In addition to my previous question relating to datepart

I have two entries in a table

site_ref src_date budget_rent budget_occ

AE 01/04/2006 123456.0000 69

AE 01/05/2006 371540.2432 74

When I run the following query

SELECT site_ref, budgeted_net_rent, budgeted_occupancy
FROM src_tbl_rental
WHERE (src_date >= @.dt_src_date) AND (src_date < DATEADD(month, 1, @.dt_src_date) - 1)

I get a result set that includes both of the above lines, when what I was trying to achieve was just the value for the first one only. I know that the date is entered in the database as 01/04/2006 as part of an import from excel. Not sure wther it is actually yyyy-mm-dd etc in reality, but from a visual point of view it certainly is dd/mm/yyyyy.

If I manually enter the (@.dt_src_date) parameter of 01/04/2006 then it returns only the one line. The problem I have is that the parameter that I mentioned actually needs to provide dd/mm/yyyy data for the bulk of the other functions etc, I want this query to only return a value that will always the mm/yyyy element of the parameter value and prefixed by 01/.

Anybody help me out here?

Regards

|||

Problem Resolved by using

WHERE (src_date >= CONVERT(varchar(6), @.dt_src_date, 112) + '01') AND (src_date < DATEADD(month, 1, CONVERT(varchar(6), @.dt_src_date, 112) + '01') - 1)

Regards

Do Foreign Key Boost Performance?

Hello,
can Foreign Keys boost performance resp. Select or Where Statement in combination with a join?
Silasnope. but indexes do and I index all of my FKs. FKs are all about maintaining data integrity and they are very important.|||Do brakes boost your car's performance?|||In a very few incredibly specific circumstances foreign keys can (in an MS SQL Server DB) boost performance. I never consider them in terms of a performance boost. I would advise you don't either - there are an incalcable number of things to consider first.|||gotta link?|||One caveat to my previous post. I have read that using cascading deletes in conjunction with foreign keys requires less overhead than maintaining the data integrity on your own. But as far as I know plain vanilla foreign keys on their own do not impact performance. If I am wrong, please let me know.|||Yeah - a bit like Sean I'd like to stress I only addressed a small part of the performance aspect of your question. Their purpose in maintaining integrity is far more important than any performance overhead.gotta link?Yuppers:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74552&SearchTerms=foreign,key,pootle,kristen|||does youse guys ever link anyplace other than sqlteam?

buncha wannabe (http://en.wiktionary.org/wiki/wannabe)s

:p|||does youse guys ever link anyplace other than sqlteam?

buncha wannabe (http://en.wiktionary.org/wiki/wannabe)s

:p

whenever my boss introduces me as the resident guru I do feel like a wannabe and a fraud.

but back to my point here, I went to the msdn link and am I right that FK's on their own without setting db options or setting cascading deletes to on do not improve performance in a perceptible way?|||"setting cascading deletes to on" is a bit of misnomer

as soon as you define the foreign key, it gets an ON DELETE action

if you specify nothing, it defaults to NO ACTION

SQL Server 2000 offers only NO ACTION and DELETE as options

SQL Server 2005 offers NO ACTION, CASCADE, SET NULL, and SET DEFAULT

it's safe to say that all of these actions are more efficiently done by the database than by application code

but as far as SELECT queries are concerned, no, foreign keys don't speed those up (except insofar as the columns are indexed, which they usually are when someone defines foreign keys)|||Do brakes boost your car's performance?

Yes....in terms of stopping performance! ;)|||Yes....in terms of stopping performance! ;)If you are at least marginally sane, you won't operate a car that you can't stop safely. A car without brakes is crippled, its top performance is negligable because I can walk faster than I'll try to drive it.

Give me a 1970 Volvo that is rusted out, with one bad carburator, and good brakes and it will easily reach the speed limit... It might be smelly, ugly, and loud, but it will go as fast as the law allows.

Give me a 2007 Lamborghini, fresh off the sales floor with bad brakes, and while the engine is fantastic and the transmission is good enough, the amount of torque that can reach the pavement is limited (by law) to what will get me to ten kph.

Since the Volvo with brakes can travel at more than ten times the top legal speed of the Lamborghini, I feel pretty comfortable with the assertion that good brakes will significantly increase performance!

-PatP|||For those of you that are slow studies, the above analogy applies just as well to databases as it applies to vehicles... Foreign keys are the "safety gear" that is needed to keep a database from becoming corrupt.

Getting wrong answers fast is the fetish of amatuer programmers and DBAs. Wrong answers are still wrong, it doesn't matter how fast you get them.

Having foreign key constraints that you don't need will not hurt you (or query performance). Having them may help reduce elapsed time for queries because of how the engine enforces the foreign key and runs queries, but it will not hurt queries. There is some (time) cost to loading data with foreign key constraints, but that is a tiny cost compared to either a wrong answer that it caught and corrected by the business and trivial compared to the cost of a wrong answer that is actually used by the business!

-PatP|||oops, you posted first, and a nice lesson there about FKs

i wanted to mention that good performance without reliable brakes is a huge risk

last year i drove my car (a '69 beetle) 20 miles to the shop using only the handbrake, as the brake cable had snapped off, and i can tell you, i drove real careful (note to the pedants: yes, i am aware i used an adjective where i should've used an adverb, but that's just my style, innit :))

this year the clutch pedal rusted half off, and i had to drive it to the same shop without being able to get it into first gear, and let me tell you, that was no picnic, you just cannot let yourself come to a complete stop because then you can't get it into second, either, and in third you'll just stall when you try to go (luckily i only had to push it while in traffic once)

anyhow, my point to databases was that speed is irrelevant if something else is wrong

then i was going to quote that familiar "to err is human, but to really screw things up, let your programmers put apps into production without data reliability"|||THAT is why I love old vehicles... Especially Beetles :D|||The question was: Do foreign keys boost performance?
I'm the only one that answered the question and provided evidence (from the horses mouth too - and I don't mean the wannabees).
ergo:
Poots - 1
The rest - 0

Besides - Silas gave up on us ages ago.|||Isn't that always the score? ;)|||Isn't that always the score? ;)Spoken like a true protege ;)|||...I feel pretty comfortable with the assertion that good brakes will significantly increase performance!

-PatP

When I wrote 'stopping performance' I meant the ability to be able to stop, not reducing the ability to go. There is a difference I believe!|||Guys, can we just drop this topic now and move on to evaluating the importance of regularly scheduled oil changes?

DMX Query, Group by

I'm having some problem with this DMX prediction query. This is the first time I'm trying out the GROUP BY statement in the DMX query and I keep getting "Parse: the statement dialect could not be resolved due to ambiguity." message.

Is Group By supported by the DMX? What am I missing? If not supported, could I insert the result into a temporary table using SELECT ... INTO.. FROM and run a group by on a temporary table?

This is what the DMX query looks like...

SELECT
t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName],
Sum(t.[Profit]) as Profit
From
[Revenue Estimate DT]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName],
[ChildrenStatusName],
[EducationName],
[Profit],
[IncomeName],
[HomeOwnerName],
[SexName],
[Country],
[ProductTypeCode],
[ProductName],
[MailCount],
[OrderAmount],
[SalesAmount],
[MailCost]
FROM
(SELECT AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, SUM(MailCount) AS MailCount, SUM(OrderAmount) AS OrderAmount, SUM(SalesAmount)
AS SalesAmount, SUM(MailCost) AS MailCost, SUM(Profit) AS Profit, MIN(RevenueEstimateID) AS ReKey
FROM [DataMining.RevenueEstimate.Predict]
GROUP BY AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, ClientID
HAVING (ClientID = 1)) as [Prediction]
') AS t
ON
[Revenue Estimate DT].[Age Group Name] = t.[AgeGroupName] AND
[Revenue Estimate DT].[Education Name] = t.[EducationName] AND
[Revenue Estimate DT].[Income Name] = t.[IncomeName] AND
[Revenue Estimate DT].[Home Owner Name] = t.[HomeOwnerName] AND
[Revenue Estimate DT].[Sex Name] = t.[SexName] AND
[Revenue Estimate DT].[Country] = t.[Country] AND
[Revenue Estimate DT].[Product Type Code] = t.[ProductTypeCode] AND
[Revenue Estimate DT].[Product Name] = t.[ProductName] AND
[Revenue Estimate DT].[Mail Count] = t.[MailCount] AND
[Revenue Estimate DT].[Order Amount] = t.[OrderAmount] AND
[Revenue Estimate DT].[Sales Amount] = t.[SalesAmount] AND
[Revenue Estimate DT].[Mail Cost] = t.[MailCost] AND
[Revenue Estimate DT].[Profit] = t.[Profit] AND
[Revenue Estimate DT].[Children Status Name] = t.[ChildrenStatusName]
GROUP BY t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName]

Hello

GROUP BY is not supported in DMX, and neither are temporary tables. A solution would be to execute the query, store the results inside SQL Server, then execute the group by inside the relational engine.

You can find some details on executing predictions from the relational engine in this article: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx

|||Or you could do as Bogdan suggests without storing the results and performing SQL operations on an OPENROWSET DMX query result

dml without generating log transactions ?

Hi There

I know the answer to this is probably no, but had to ask anyway.

Is there a way to perform a dml statement without generating anything in the transaction log ?

The reason i ask is that i have a database that uses simply recovery model, however i need to move a 1 billion row table to this DB, i know that even though it is in simple recovery it is one transaction, it will be written to the log until committed then the space will be released in the log file.

I am using a simple: insert into DW_DB..table select * from DB..other_table.

I have dropped all indexes before the operation.

However this is a big problem, the log for the db in simple recovery that i am moving the data to grew to 128 Gig and the disk ran out of space, the other drives on the machine do not have much space.

Is there a way i can move the billion row table into the new DB without generating such a huge log ?

Thanx

Hi There

Part 2 for the question:

The transaction has rolled back, however there is still 27 gigs space used in the transaction log, there are no open transactions in the db, the db is in simple recovery, i cannot backup the log as it is simple recovery, what is this 27 gigs in the transaction and how do i clear it ?

Thanx

|||

Please ignore my second comment, this problem went away after checkpointing the database, however any feedback ont he original post would be greatly appreciated.

Thanx

|||You can use select into command which is bulk operation and it is minimally logged in the case of simple recovery model.|||Thank you , this worked perfectly.

Divide by zero error trapping

I have the following line in a select statement which comes up with a
divide by zero error.

CAST(CASE Splinter_Status
WHEN 'SUR' THEN 0
ELSE CASE WHEN Sacrifice>=1
THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
(m.Gross+m.Sacrifice)
ELSE 0
END
END AS Float)AS Bond2,

The error happens on the section (m.Gross + m.Sacrifice) as this can
equal zero and throws out the part of the calc that divides by it. It
is correct in some instances that it does so. The full SQL statement
has a large number of these expressions so I need a method I can apply
to any line if possible.

I know that it is mathmatically correct to error where this value is
zero, but what I want to do is set the output of the entire expression
to zero if there is an error.

Realistically an error such as this could happen at a few points in
the expression (or one of many others), so I need to find a way of
catching any error in the expression and setting the return value to
0. I thought of using a CASE statement, but wondered if there was a
better way of looking at this as the case statement would have to
check each variation where it could throw an error.

Any ideas ?

Thanks

RyanRyan (ryanofford@.hotmail.com) writes:
> I have the following line in a select statement which comes up with a
> divide by zero error.
> CAST(CASE Splinter_Status
> WHEN 'SUR' THEN 0
> ELSE CASE WHEN Sacrifice>=1
> THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
> (m.Gross+m.Sacrifice)
> ELSE 0
> END
> END AS Float)AS Bond2,
> The error happens on the section (m.Gross + m.Sacrifice) as this can
> equal zero and throws out the part of the calc that divides by it. It
> is correct in some instances that it does so. The full SQL statement
> has a large number of these expressions so I need a method I can apply
> to any line if possible.
> I know that it is mathmatically correct to error where this value is
> zero, but what I want to do is set the output of the entire expression
> to zero if there is an error.

SQL Server does happen to other some alternatives in this case, but I would
strongly recomment that you have something like:

ELSE CASE WHEN Sacrifice>=1 AND m.Gross+m.Sacrifice <> 0
THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
(m.Gross+m.Sacrifice)
ELSE 0
END

The alternatives is to set ANSI_WARNINGS OFF, ARITHABORT OFF and
ARITHIGNORE ON. In this case, SQL Server will silenly set the result to
NULL, which you then would have to apply coalesce to get a 0. But since
these settings are not compatible with indexed views and indexed
computed columns, you can get other problems, and overall it is, in
my opinion, an obscure way of doing things.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Probably the easiest way to accomplish what is you is:

CAST(CASE Splinter_Status
WHEN 'SUR' THEN 0
ELSE CASE WHEN Sacrifice>=1
THEN COALESCE(( 3*m.Premium/100-(m.Sacrifice *
3*m.Premium/100)/
NULLIF(m.Gross+m.Sacrifice,0) ),0)
ELSE 0
END
END AS Float)AS Bond2,

Hope this helps,
Gert-Jan

Ryan wrote:
> I have the following line in a select statement which comes up with a
> divide by zero error.
> CAST(CASE Splinter_Status
> WHEN 'SUR' THEN 0
> ELSE CASE WHEN Sacrifice>=1
> THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
> (m.Gross+m.Sacrifice)
> ELSE 0
> END
> END AS Float)AS Bond2,
> The error happens on the section (m.Gross + m.Sacrifice) as this can
> equal zero and throws out the part of the calc that divides by it. It
> is correct in some instances that it does so. The full SQL statement
> has a large number of these expressions so I need a method I can apply
> to any line if possible.
> I know that it is mathmatically correct to error where this value is
> zero, but what I want to do is set the output of the entire expression
> to zero if there is an error.
> Realistically an error such as this could happen at a few points in
> the expression (or one of many others), so I need to find a way of
> catching any error in the expression and setting the return value to
> 0. I thought of using a CASE statement, but wondered if there was a
> better way of looking at this as the case statement would have to
> check each variation where it could throw an error.
> Any ideas ?
> Thanks
> Ryan

--
(Please reply only to the newsgroup)|||Works perfectly thank you !

R

Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in message news:<41251949.4D0CF7B3@.toomuchspamalready.nl>...
> Probably the easiest way to accomplish what is you is:
> CAST(CASE Splinter_Status
> WHEN 'SUR' THEN 0
> ELSE CASE WHEN Sacrifice>=1
> THEN COALESCE(( 3*m.Premium/100-(m.Sacrifice *
> 3*m.Premium/100)/
> NULLIF(m.Gross+m.Sacrifice,0) ),0)
> ELSE 0
> END
> END AS Float)AS Bond2,
> Hope this helps,
> Gert-Jan
>
> Ryan wrote:
> > I have the following line in a select statement which comes up with a
> > divide by zero error.
> > CAST(CASE Splinter_Status
> > WHEN 'SUR' THEN 0
> > ELSE CASE WHEN Sacrifice>=1
> > THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
> > (m.Gross+m.Sacrifice)
> > ELSE 0
> > END
> > END AS Float)AS Bond2,
> > The error happens on the section (m.Gross + m.Sacrifice) as this can
> > equal zero and throws out the part of the calc that divides by it. It
> > is correct in some instances that it does so. The full SQL statement
> > has a large number of these expressions so I need a method I can apply
> > to any line if possible.
> > I know that it is mathmatically correct to error where this value is
> > zero, but what I want to do is set the output of the entire expression
> > to zero if there is an error.
> > Realistically an error such as this could happen at a few points in
> > the expression (or one of many others), so I need to find a way of
> > catching any error in the expression and setting the return value to
> > 0. I thought of using a CASE statement, but wondered if there was a
> > better way of looking at this as the case statement would have to
> > check each variation where it could throw an error.
> > Any ideas ?
> > Thanks
> > Ryan

Divide by zero display "0"

Hello,
how would I write an if statement to ensure that a "0" is displayed if a
divide 0/0?
Thanksiif(<field.total>=0,0,<field.numerator>/(<field.total>)
U. Tokklas
"Fab" wrote:
> Hello,
> how would I write an if statement to ensure that a "0" is displayed if a
> divide 0/0?
> Thanks
>
>|||Note: IIF is a function call. All arguments get evaluated before invoking
the function. Therefore, a DivisionByZero can still happen.
Use the following pattern:
=iif( Fields!Total.Value = 0, 0, Fields!X.Value / iif(Fields!Total.Value=0,
1, Fields!X.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tokklas" <Tokklas@.discussions.microsoft.com> wrote in message
news:7381410F-FE63-4DAA-9D8D-3F426D24CCD8@.microsoft.com...
> iif(<field.total>=0,0,<field.numerator>/(<field.total>)
> U. Tokklas
>
> "Fab" wrote:
>> Hello,
>> how would I write an if statement to ensure that a "0" is displayed if a
>> divide 0/0?
>> Thanks
>>
>>

Divide by zero

In the following query I sometimes have DIVIDE BY ZERO issues. How can I
put the CASE (SOME VARIABLE) WHEN 0 THEN 0 ELSE statement into the query so
that I don't get the error.
SUM(MedExp) is sometimes = 0
SUM(TotalMonths) is sometimes = 0
SUM(TotalMem) is sometimes = 0
I'm not sure how to set percentof, pmpm, and permem to zero if the "divide
by" (one of the three above) is zero?
Here is the query. Any help would be great.
SELECT MCO, SUM([Pharmacy Exp]) AS phexp, (SUM([Pharmacy Exp]) /
SUM(MedExp)) as percentof, (SUM([Pharmacy Exp]) / SUM(TotalMonths)) AS pmpm,
(SUM([Pharmacy Exp]) / SUM(TotalMem)) AS permem from sheet1$ where medexp <>
'' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
domicile LIKE '%nj%') GROUP BY MCO order by permem descTry,
SELECT
MCO,
SUM([Pharmacy Exp]) AS phexp,
isnull((SUM([Pharmacy Exp]) / nullif(SUM(MedExp), 0)), 0) as percentof,
isnull((SUM([Pharmacy Exp]) / nullif(SUM(TotalMonths), 0)), 0) AS pmpm,
isnull((SUM([Pharmacy Exp]) / nullif(SUM(TotalMem), 0)), 0) AS permem
from
sheet1$ where medexp <> ''
and totalmem <> ''
and period = '3Q04'
AND (
domicile LIKE '%ct%'
OR domicile LIKE '%ma%'
OR domicile LIKE '%nh%'
OR domicile LIKE '%me%'
OR domicile LIKE '%ri%'
OR domicile LIKE '%vt%'
OR domicile LIKE '%ny%'
OR domicile LIKE '%nj%'
)
GROUP BY
MCO
order by
permem desc;
AMB
"William" wrote:

> In the following query I sometimes have DIVIDE BY ZERO issues. How can I
> put the CASE (SOME VARIABLE) WHEN 0 THEN 0 ELSE statement into the query s
o
> that I don't get the error.
> SUM(MedExp) is sometimes = 0
> SUM(TotalMonths) is sometimes = 0
> SUM(TotalMem) is sometimes = 0
> I'm not sure how to set percentof, pmpm, and permem to zero if the "divide
> by" (one of the three above) is zero?
> Here is the query. Any help would be great.
> SELECT MCO, SUM([Pharmacy Exp]) AS phexp, (SUM([Pharmacy Exp]) /
> SUM(MedExp)) as percentof, (SUM([Pharmacy Exp]) / SUM(TotalMonths)) AS pmp
m,
> (SUM([Pharmacy Exp]) / SUM(TotalMem)) AS permem from sheet1$ where medexp
<>
> '' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
> domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
> domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
> domicile LIKE '%nj%') GROUP BY MCO order by permem desc
>
>|||Try this
SELECT MCO, SUM([Pharmacy Exp]) AS phexp,
(CASE WHEN SUM(MedExp) > 0 THEN SUM([Pharmacy Exp]) /SUM(MedExp) ELSE
SUM([Pharmacy Exp]) END ) as percentof,
(CASE WHEN SUM(TotalMonths) > 0 THEN SUM([Pharmacy Exp]) / SUM(TotalMonths)
ELSE SUM([Pharmacy Exp]) END ) AS pmpm,
(CASE WHEN SUM(TotalMem) > 0 THEN SUM([Pharmacy Exp]) / SUM(TotalMem) ELSE
SUM([Pharmacy Exp]) END ) AS permem,
from sheet1$ where medexp <>
'' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
domicile LIKE '%nj%') GROUP BY MCO order by permem desc
Thank you
Baiju
"William" <da@.northernit.net> wrote in message
news:VPb4e.1793$uw2.417@.twister.nyroc.rr.com...
> In the following query I sometimes have DIVIDE BY ZERO issues. How can I
> put the CASE (SOME VARIABLE) WHEN 0 THEN 0 ELSE statement into the query
so
> that I don't get the error.
> SUM(MedExp) is sometimes = 0
> SUM(TotalMonths) is sometimes = 0
> SUM(TotalMem) is sometimes = 0
> I'm not sure how to set percentof, pmpm, and permem to zero if the "divide
> by" (one of the three above) is zero?
> Here is the query. Any help would be great.
> SELECT MCO, SUM([Pharmacy Exp]) AS phexp, (SUM([Pharmacy Exp]) /
> SUM(MedExp)) as percentof, (SUM([Pharmacy Exp]) / SUM(TotalMonths)) AS
pmpm,
> (SUM([Pharmacy Exp]) / SUM(TotalMem)) AS permem from sheet1$ where medexp
<>
> '' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
> domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
> domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
> domicile LIKE '%nj%') GROUP BY MCO order by permem desc
>

Distributed transactions

Hi everyone!
My problem: I′m Apttempting to execute a Insert statement on a table in my
server with a SP from another. Like this:
INSERT INTO TABLE EXEC [TheOtherServer].[DataBase].dbo.stored_procedure
and I get this error message:
Server: Msg 7391, Level 16, State 1, Line 9
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
My cuestion is: How can I configure the other server to allow distributed
transactions? and, Is this a solution or am I wrong?
Thanks a lotHi
You may wish to check that MS DTC is configured and working correctly.
http://support.microsoft.com/defaul...kb;en-us;329332
This may also help
http://support.microsoft.com/defaul...kb;en-us;306212
John
"Huacuz" wrote:

> Hi everyone!
> My problem: I′m Apttempting to execute a Insert statement on a table in m
y
> server with a SP from another. Like this:
> INSERT INTO TABLE EXEC [TheOtherServer].[DataBase].dbo.stored_procedure
> and I get this error message:
> Server: Msg 7391, Level 16, State 1, Line 9
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> My cuestion is: How can I configure the other server to allow distributed
> transactions? and, Is this a solution or am I wrong?
> Thanks a lot

distributed transaction conundrum

The following statement joins two tables in databases on different
servers
(n.b. rose is the 'linked' server):
select top 10 hhid, c.s21_cust_num
from rose.customer.dbo.customer rc
join customer c
on rc.s21_cust_num = c.s21_cust_num
where c.store_num = 232
When I run it interactively from query analyzer, it executes without
error and produces a result set.
When I create a stored procedure and run it in the SQL debugger, I
receive the following set of DTC errors (which is the same thing that
happens when our program tries to update the table on the 'remote'
server).:
create procedure selectcustomer
as
select top 10 hhid, c.s21_cust_num
from rose.customer.dbo.customer rc
join customer c
on rc.s21_cust_num = c.s21_cust_num
where c.store_num = 232
go
Server: Msg 7391, Level 16, State 1, Procedure selectcustomer, Line 5
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not
be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
There is a firewall between the two servers, but it seems strange that
the queries would run interactively but fail when running under
program control.
Is there an explanation for that?| The following statement joins two tables in databases on different
| servers
| (n.b. rose is the 'linked' server):
|
| select top 10 hhid, c.s21_cust_num
| from rose.customer.dbo.customer rc
| join customer c
| on rc.s21_cust_num = c.s21_cust_num
| where c.store_num = 232
|
| When I run it interactively from query analyzer, it executes without
| error and produces a result set.
|
| When I create a stored procedure and run it in the SQL debugger, I
| receive the following set of DTC errors (which is the same thing that
| happens when our program tries to update the table on the 'remote'
| server).:
|
| create procedure selectcustomer
|
| as
|
| select top 10 hhid, c.s21_cust_num
| from rose.customer.dbo.customer rc
| join customer c
| on rc.s21_cust_num = c.s21_cust_num
| where c.store_num = 232
|
| go
|
| Server: Msg 7391, Level 16, State 1, Procedure selectcustomer, Line 5
| [Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not
| be performed because the OLE DB provider 'SQLOLEDB' was unable to
| begin a distributed transaction.
|
| [OLE/DB provider returned message: New transaction cannot enlist in
| the specified transaction coordinator. ]
|
| OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
|
| There is a firewall between the two servers, but it seems strange that
| the queries would run interactively but fail when running under
| program control.
--
This Technet article might help:
HOWTO: Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;306843
Cheers,
--
Eric Cárdenas
SQL Server support|||"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:<MmeQtGrvDHA.3520@.cpmsftngxa07.phx.gbl>...
> This Technet article might help:
> HOWTO: Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/default.aspx?scid=kb;en-us;306843
> Cheers,
> --
> Eric Cárdenas
> SQL Server support
Been there, and yes, I've had that problem. But I have another problem, too.
I created a linked server pointed to the SQL server on my workstation. In
the following code both tables t1 and t2 are in the same db, but in the
sproc I'm referencing one through a linked server (called SQL01). It runs
from a query analyzer window but fails in the debugger, with the same 7391
error. Can you tell me why does it fail in the debugger?
drop table t1
create table t1
(k1 int identity, nk1 int, att1 char(10), date1 datetime, varchar1 varchar
(30))
drop table t2
create table t2
(nk2 int, att2 char (10), date2 datetime, varchar2 varchar (30))
insert into t1
(nk1, att1, date1, varchar1)
values
(100, 'pot', getdate(), 'this is a test record')
insert into t1
(nk1, att1, date1, varchar1)
values
(200, 'pan', getdate(), 'this is another test record')
go
drop procedure movedata
go
create procedure movedata
as
set xact_abort on
insert into sql01.pos_rawdata.dbo.t2
(nk2, att2, date2)
select nk1, att1, date1 from t1
update rt2
set varchar2 = t1.varchar1
from t1, sql01.pos_rawdata.dbo.t2 rt2 where t1.nk1 = rt2.nk2 and nk1 = 100
go
select * from t2
Thanks for any insights you might have.|||This is just a thought, but doesn't the debugger have to begin a transaction while debugging in order to allow you teh rollback option? If this is the case, then everything you need in order to begin a DISTRIBUTED TRANSACTION needs to be in place. I notice that you included the SET XACT_ABORT ON command, which I know is one of the required conditions. The rest can be found in the BOL.
I hope this helps somehow.
Matthew Bando
BandoM@.(REmove)CSCTechnologies.com
>--Original Message--
>"Eric Cardenas" <ecard@.anonymous.com> wrote in message
>news:<MmeQtGrvDHA.3520@.cpmsftngxa07.phx.gbl>...
>> This Technet article might help:
>> HOWTO: Troubleshoot MS DTC Firewall Issues
>> http://support.microsoft.com/default.aspx?scid=3Dkb;en-
us;306843
>> Cheers,
>> --
>> Eric C=E1rdenas
>> SQL Server support
>Been there, and yes, I've had that problem. But I have another problem, too.
>I created a linked server pointed to the SQL server on my workstation. In
>the following code both tables t1 and t2 are in the same db, but in the
>sproc I'm referencing one through a linked server (called SQL01). It runs
>from a query analyzer window but fails in the debugger, with the same 7391
>error. Can you tell me why does it fail in the debugger?
>drop table t1
>create table t1
>(k1 int identity, nk1 int, att1 char(10), date1 datetime, varchar1 varchar
>(30))
>drop table t2
>create table t2
>(nk2 int, att2 char (10), date2 datetime, varchar2 varchar (30))
>insert into t1
>(nk1, att1, date1, varchar1)
>values
>(100, 'pot', getdate(), 'this is a test record')
>insert into t1
>(nk1, att1, date1, varchar1)
>values
>(200, 'pan', getdate(), 'this is another test record')
>go
>drop procedure movedata
>go
>create procedure movedata
>as
>set xact_abort on
>insert into sql01.pos_rawdata.dbo.t2
>(nk2, att2, date2)
>select nk1, att1, date1 from t1
>update rt2
>set varchar2 =3D t1.varchar1
>from t1, sql01.pos_rawdata.dbo.t2 rt2 where t1.nk1 =3D rt2.nk2 and nk1 =3D 100
>go
>select * from t2
>Thanks for any insights you might have.
>
>.
>|||I think that was a really good call.
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message
news:02af01c3bf4a$bf73fa80$a101280a@.phx.gbl...
This is just a thought, but doesn't the debugger have to
begin a transaction while debugging in order to allow you
teh rollback option? If this is the case, then
everything you need in order to begin a DISTRIBUTED
TRANSACTION needs to be in place. I notice that you
included the SET XACT_ABORT ON command, which I know is
one of the required conditions. The rest can be found in
the BOL.
I hope this helps somehow.
Matthew Bando
BandoM@.(REmove)CSCTechnologies.com

Distributed transaction

Hi all,
I am trying to merge data of 2 tables on different servers with an insert statement.

INSERT INTO SERVER1.db.owner.table
select s2.* from SERVER2.db.owner.table as s2
LEFT JOIN SERVER1.db.owner.table as s1
ON s1.key=s2.key
where s1.key is null

I got this error.
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

while the select query is giving the result.
I have done simmilar inserts on some other tables which worked fine

I have created sp_addlinkedserver.
and DTC set on both servers.

any help will be greatly appreciatedDid you execute this on Server1 or Server2? What OS and sp are on the respective servers?

-PatP|||Are you doing it within a transaction? You can successfully bypass engagement of MSDTC by preceeding your INSERT with SET IMPLICIT_TRANSACTIONS OFF.|||Thanks for the reply.
I have tried it on both servers .
os:w2k and sp3.
It is not with in transaction.
error still occurs.