No execution plan in profiler

I'm trying to track down a problem with a very simple sql insert statement.
INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM
tblRecordingId
This statement has recently begun to cause timeouts (but not all the time).
When it does work, I get this plan:
Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
[tblRecordingId].[AccountId]=RaiseIfNull(82440)))
|--Top(1)
|--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
|--Stream
Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
|--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
CPU=16, Reads=121, Writes=0, Duration=10860.
When it causes a timeout, there's no execution plan in the profiler. CPU=15,
Reads=40, Writes=0, Duration=40000.
tblRecordingId contains 2 int columns and 590 rows. There are no indexes or
keys.
I did add deadlock to the profiler but didn't get any entries. The server is
not doing much other work (cpu usage is very low.)
I also tried it in the query analyzer. The query usually executes in less
than a second, but sometimes it takes more than a minute.
Any ideas on what the problem is or hints on what to search for?
PeterMost probably a locking problem. The subquery that gets the MAX value does a
table scan. Can you add
an index on the RecordingId column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter Laan" <plnews2000@.yahoo.se> wrote in message news:e7S7Uly0FHA.3780@.TK2MSFTNGP12.phx.
gbl...
> I'm trying to track down a problem with a very simple sql insert statement
.
> INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM tblRecord
ingId
> This statement has recently begun to cause timeouts (but not all the time)
. When it does work, I
> get this plan:
> Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
> SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
> [tblRecordingId].[AccountId]=RaiseIfNull(82440)))
> |--Top(1)
> |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
> |--Stream Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
> |--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
> CPU=16, Reads=121, Writes=0, Duration=10860.
> When it causes a timeout, there's no execution plan in the profiler. CPU=1
5, Reads=40, Writes=0,
> Duration=40000.
> tblRecordingId contains 2 int columns and 590 rows. There are no indexes o
r keys.
> I did add deadlock to the profiler but didn't get any entries. The server
is not doing much other
> work (cpu usage is very low.)
> I also tried it in the query analyzer. The query usually executes in less
than a second, but
> sometimes it takes more than a minute.
>
> Any ideas on what the problem is or hints on what to search for?
>
> Peter
>|||Peter Laan wrote:
> I'm trying to track down a problem with a very simple sql insert
> statement.
> INSERT INTO tblRecordingId SELECT 82417, MAX(RecordingId)+1 FROM
> tblRecordingId
> This statement has recently begun to cause timeouts (but not all the
> time). When it does work, I get this plan:
> Table Insert(OBJECT:([myroc].[dbo].[tblRecordingId]),
> SET:([tblRecordingId].[RecordingId]=RaiseIfNull([Expr1004]),
> [tblRecordingId].[AccountId]=RaiseIfNull(82440)))
> |--Top(1)
> |--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
> |--Stream
> Aggregate(DEFINE:([Expr1002]=MAX([tblRec
ordingId].[RecordingId])))
> |--Table Scan(OBJECT:([myroc].[dbo].[tblRecordingId]))
> CPU=16, Reads=121, Writes=0, Duration=10860.
> When it causes a timeout, there's no execution plan in the profiler.
> CPU=15, Reads=40, Writes=0, Duration=40000.
> tblRecordingId contains 2 int columns and 590 rows. There are no
> indexes or keys.
> I did add deadlock to the profiler but didn't get any entries. The
> server is not doing much other work (cpu usage is very low.)
> I also tried it in the query analyzer. The query usually executes in
> less than a second, but sometimes it takes more than a minute.
>
> Any ideas on what the problem is or hints on what to search for?
>
> Peter
Tibor is correct. The table scan is the problem. Do you not have an
index on the RecordingID column? If not, you need to add one. Also, are
you using the MAX command to return the next key value for inserting? If
so, you really need a unique index on the column and the statement may
not guarantee unique results. I would recommend that you either use an
identity column to have SQL Server auto-generate the next key value or
you use an another table that holds the next value and update it and
grab the key when needed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||[posted and mailed, vnligen svara i nys
Peter Laan (plnews2000@.yahoo.se) writes:
> When it causes a timeout, there's no execution plan in the profiler.
> CPU=15, Reads=40, Writes=0, Duration=40000.
This is because the timeout is something that occurs in the client that
causes it to cancel the batch, and the plan event is not generated until
the query completes.
Beware that when you get command timeouts, you must issue something
like "IF @.@.trancount > 0 ROLLBACK TRANSACTION", as any transaction
started by the batch is not rolled back automatically.
Or even better, set the command timeout to 0 which means wait forever.
Does make life simpler.

> tblRecordingId contains 2 int columns and 590 rows. There are no indexes
> or keys.
As Tibor and David said, for this type of query you must have an index.

> I did add deadlock to the profiler but didn't get any entries.
A deadlock is not a timeout. That's when two processes are waiting for
each to other release resources. As Tibor and David said, you probably
had a blocking scenario.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks to all who replied! As you correctly guessed it was a blocking
problem.
Petersql

No duplicates exist but get Msg 2627, Cannot insert duplicate key in object

On SQL Server 2000, a transaction of thousands of INSERTs contains these three consecutive INSERT statements:

INSERT INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)
VALUES ('05b3b88a-f0f2-4e7e-a82f-73a7bd177a96', 0, 1)

INSERT INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)
VALUES ('05e51bee-ac3b-4257-90f1-f34adcb185cb', 0, 128)

INSERT INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)
VALUES ('05f33ded-979b-41ee-9d6c-ac5adc4d8635', -1000000000, 1000000000)

The transaction fails with a Msg 2627 error, Violation of PRIMARY KEY constraint 'ARR_Constraint2'. Cannot insert duplicate key in object 'PARAMETER_ATTRIBUTES'. SQL Profiler reveals that the last statement executed is the second INSERT.

The primary key is on the ID column. What is particularly significant is that before running the transaction, this query returns no rows:

select * from PARAMETER_ATTRIBUTES
where id in (
'05b3b88a-f0f2-4e7e-a82f-73a7bd177a96', -- pk value in first insert
'05e51bee-ac3b-4257-90f1-f34adcb185cb', -- pk value in second insert
'05f33ded-979b-41ee-9d6c-ac5adc4d8635') -- pk value in third insert

In other words, all three INSERTs contain primary key values that do not exist in the table. As you can see, each INSERT is inserting a unique value for the primary key. I am asserting that there are no duplicates. How can the fact of a Msg 2627 error be reconciled with the facts of no duplicates? Can SQL Profiler not be trusted to show the specific statement causing a duplicate key problem? In other words, in a transaction of 1000s of statements, does a dup key problem cause SQL Profiler to stop before it displays the statement causing the error?

Is there, by any chance, a trigger involved?

It does not look, on the surface, that you can both get no results from the select and the duplicate error. Therefore, we need to question the assumptions. Make sure that you can make the select return results from a known existant string. You might put the select after each of the selects.

What datatype are you using for the id?

That is how I would approach the debugging.

|||

No triggers exist.

The id column, the primary key column, is a uniqueidentifier.

|||Can you run the insert with just those three statements? One of the other batch inserts earlier in the order could contain an id with the same value. What you really need to do is run the select... where id in.... right before the insert, then just don't insert any rows after the insert, and see if there were rows existing the moment before statement 2 is run.|||The id value only exists in that one INSERT statement. I suspect that the last INSERT shown in SQL Profiler is not the INSERT causing the problem.|||

Further testing reveals that the the duplicate key INSERT occurs 100s of lines before the last statement shown in SQL Profiler.

Here is a detailed explanation of what is happening:

1. A very popular and well regarded database update utility is being used to update 100s of very similar databases.

2. This vendor's product puts all of the 10,000s of statements in a single transaction.

3. Within the single transaction are many batches of statements.

4. Within a particular batch, there is a statement that causes a duplicate key error. Not on the QA database, not on the many real databases, just on one developer's database.

5. The last statement in the batch does not cause a duplicate key error. It was the statement I was looking at because it was the last one the SQL Profiler displayed. I also looked at the immediately preceding and following (first statement of next batch) INSERT statements. None of these statements could or did cause a duplicate key error.

What we can conclude from this is that depending on how statements are grouped and processed, the last statement displayed by SQL Profiler in an abort situation may not physically be anywhere near the statement actually causing the problem.

No Data Needed To be Merged - But Only for INSERT ??

Hi All,
I'm receiving some strange behaviour from replication between SQL Server 2K/SQL CE.
Replicating UPDATE's to a table on my server works fine as viewed in the Replication
Monitor and the updated rows in the table.
However, if I replicate INSERTS, then Replication Monitor reports No Data Needed
To be Merged and the rows are not inserted in the table.
Has anyone come across this issue?
Cheers,
ciaran
Does your Table use Identity Values ?
If so, then you will need to set it to (Not For Replication).
|||Hi Gerry,
My table does use Identity Values, and it does have NOT FOR Replication set.
I also have Identity Ranges set.
Some more info. that has just come to light. Some rows DO insert, while other
rows do not. At one stage the SQL CE database that was on the devices was
physically deleted and re-initialised. Could it be that the old rows were left
lying around and that somehow this is preventing me from doing my inserts on
these rows? In which case what can I do? Delete rows from MSmerge_contents?
Thanks for the help !
|||By re-initialised do you mean you re-initialised the Subscription (i.e. under Replication Monitor / Publishers / right click on the publication and selected Reinitialise All Subscriptions ?)
Try running the option (again right click on the Publisher) and selet Validate all Subscriptions to see if
the subscriber is in sync with the publisher.
Check out BOL "Validating Replicatated Data" for more info on this.
sql

no blocking with bulk insert

I am running in QA:
Bulk insert tableA from 'C:\file.tab'
In another window ,
select * from tableA gives me no records .. I would assume some kind of
blocking while the bulk insert does it jobs. So what exactly does bulk
insert to ? The job is running and the tab file has 50 million records but
still on the second window i can query and in sp_lock all I can see for the
table is an IX tab lock and theres no blocking too. I am using SQL 2000
So whats going on ?In addition after like 15 mins, it started blocking.. So my question is what
was it doing till then.. Does it try to read the file first or something ?
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e#4f7ykUDHA.2112@.TK2MSFTNGP10.phx.gbl...
> I am running in QA:
> Bulk insert tableA from 'C:\file.tab'
> In another window ,
> select * from tableA gives me no records .. I would assume some kind of
> blocking while the bulk insert does it jobs. So what exactly does bulk
> insert to ? The job is running and the tab file has 50 million records but
> still on the second window i can query and in sp_lock all I can see for
the
> table is an IX tab lock and theres no blocking too. I am using SQL 2000
> So whats going on ?
>
>|||No your are experiencing the effect of lock escalation. When you start the
bulk insert with no batch size or a batch size of 0 (zero) the complete file
will be inserted as one batch, so the lock accumulate and at a certain point
in time escalate to a table lock.
In order to prevent this you can take two approaches:
1) Use a batch size of 2499 to prevent the lock escalation form happening
2) Before you start the bulk insert take a rowlock on an entry in the table,
I always do this by inserting a dummy record at the end of the table. Keep
the lock. In another session do the bulk insert, because there is a lock
present you can not escalate to a table lock.
I prefer option 1.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uBzxW5kUDHA.2012@.TK2MSFTNGP10.phx.gbl...
> In addition after like 15 mins, it started blocking.. So my question is
what
> was it doing till then.. Does it try to read the file first or something ?
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e#4f7ykUDHA.2112@.TK2MSFTNGP10.phx.gbl...
> > I am running in QA:
> >
> > Bulk insert tableA from 'C:\file.tab'
> >
> > In another window ,
> >
> > select * from tableA gives me no records .. I would assume some kind of
> > blocking while the bulk insert does it jobs. So what exactly does bulk
> > insert to ? The job is running and the tab file has 50 million records
but
> > still on the second window i can query and in sp_lock all I can see for
> the
> > table is an IX tab lock and theres no blocking too. I am using SQL 2000
> >
> > So whats going on ?
> >
> >
> >
>

Nice problem: How to combine different columns into a table

It's rather easy to combine resultset from the same table structure...we can either insert the entries or union the results.

But let's say you select different columns from different tables and want to combine them to form a new table, how would you do it (assuming you can't join those tables since they are not related), assuming they all return the same number of rows.

select col1 from table1
go
select col2 from table2
go

Now I want to combine them so table3 is made of col1 and col2.Go into more detail as to what you are trying to do - if I understand what you are attempting to do, how do you guarantee the relationship between col1 and col2, so that when an insert into table3 occurs you have the correct col1 and col2 together ?|||Originally posted by rnealejr
Go into more detail as to what you are trying to do - if I understand what you are attempting to do, how do you guarantee the relationship between col1 and col2, so that when an insert into table3 occurs you have the correct col1 and col2 together ?

I basically go through gathering a list of statistical info for all users.
So say I have 10 users. I do one pass of some rather complex queries to get the data for first column. Then I do another pass for another column. Now I want to combine them all into one table.

There is no need to guarantee the relationship between columns, this is merely for reporting purposes so once combined there will never be row insertion to the table ever.

e.g.

User [dataset 1] [dataset 2] [dataset 3]
1 10 20 2
2 23 30 4

Thanks!|||Originally posted by waspfish
I basically go through gathering a list of statistical info for all users.
So say I have 10 users. I do one pass of some rather complex queries to get the data for first column. Then I do another pass for another column. Now I want to combine them all into one table.

There is no need to guarantee the relationship between columns, this is merely for reporting purposes so once combined there will never be row insertion to the table ever.

e.g.

User [dataset 1] [dataset 2] [dataset 3]
1 10 20 2
2 23 30 4

Thanks!

1. Create an @.table with an identity. Fill that table.
2. Create another @.table with an identity. Fill that with the second table.
3. Query both tables and relate them using their identities.|||you mean something like this >>>

select id, (select count(*) from sysobjects), (select max(id) from sysindexes) from syscolumns

Next Available Identity Value

Hi all,
is there a way in SQL Server for me to get the next available Identity value from an Identity column?
(Idon'twant to perform an insert, and then get the identity through @.@.IDENTITY!)
Thanks
Tryst
SELECT (IDENT_CURRENT('tableName') + 1)
But this is not safe! If 2 processes call this they can get the same ID number.
|||OK - Thanks Darrel.
I may have to give that a miss then, esp is it nots 100% secure.
Thanks
Tryst
|||I was assuming you were reading it and then returning it to yourbusiness object and waiting a while. If you are getting the IDnumber and then immediately updating, then you could wrap it in atransaction and set thetransaction levelto serializable. That would decrease throughput, but if you'renot supporting that many users, maybe it's ok. -- But if that'sall you're doing, why not use autonumber?
|||Hi Darrell,
what I am doing is inserting an entry into the database, where theentry will contain a column that will reference the primary key to thatentry. The idea is that I will be storing XML data in the database, andif the XML data is more than 1024 chars then the XML will be spreadacross multiple rows. Therefore, I need a Column that tells me what rowthese XML rows relate to (which is the first row that contain the XMLdata). I then have an order column which will determine the order I canassemble the XML data from these rows. So, my problem was, for thefirst row that will contain XML data that will span multiple rows (oreven singular rows - XML data < 1024 chars) how do I get to know theprimary for this entry so that I can insert it into the referencecolumn. The way I done this was to do an insert, and then quicklyfollowed by an update after using @.@.IDENTITY.
Thanks
Tryst

newline in format string

Can I insert a newline character within a reporting services format string?
I guess \n doesn't work since it is interpreted as a VisualBasic format
string...? Is there an alternative?
Thanks,
Michael CarrvbNewLine
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Carr" <mcarr@.umich.edu> wrote in message
news:uOApiLweEHA.3200@.TK2MSFTNGP09.phx.gbl...
> Can I insert a newline character within a reporting services format
string?
> I guess \n doesn't work since it is interpreted as a VisualBasic format
> string...? Is there an alternative?
> Thanks,
> Michael Carr
>|||Hmm I think I may have located a bug...
1. If I use vbNewline in the FORMAT STRING, i.e. = "M/dd/yy" & vbNewline &
"h:mm tt", then the vbNewline gets rendered (using the HTML renderer, at
least) out to a linefeed in the HTML textfile which does NOT translate into
a linefeed in the browser.
2. On the other hand, if I use vbNewline in the VALUE itself, i.e. =Format(Fields!Date.Value, "M/dd/yy") & vbNewline & Format(Fields!Date.Value,
"h:mm tt"), then the vbNewline is rendered out as <br/>
This seems to be an inconsistency in the HTML renderer ... ?
Michael Carr
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:eXqVC9weEHA.3680@.TK2MSFTNGP11.phx.gbl...
> vbNewLine
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Michael Carr" <mcarr@.umich.edu> wrote in message
> news:uOApiLweEHA.3200@.TK2MSFTNGP09.phx.gbl...
> > Can I insert a newline character within a reporting services format
> string?
> >
> > I guess \n doesn't work since it is interpreted as a VisualBasic format
> > string...? Is there an alternative?
> >
> > Thanks,
> > Michael Carr
> >
> >
>|||Please clarify the steps to reproduce. I tried the first expression on SP1
and I get <br/>. Here's the expression I used: ="1/1/2004" & vbNewLine &
"12:00:00".
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Carr" <mcarr@.umich.edu> wrote in message
news:OhZEbx0eEHA.2604@.TK2MSFTNGP12.phx.gbl...
> Hmm I think I may have located a bug...
> 1. If I use vbNewline in the FORMAT STRING, i.e. = "M/dd/yy" & vbNewline &
> "h:mm tt", then the vbNewline gets rendered (using the HTML renderer, at
> least) out to a linefeed in the HTML textfile which does NOT translate
into
> a linefeed in the browser.
> 2. On the other hand, if I use vbNewline in the VALUE itself, i.e. => Format(Fields!Date.Value, "M/dd/yy") & vbNewline &
Format(Fields!Date.Value,
> "h:mm tt"), then the vbNewline is rendered out as <br/>
> This seems to be an inconsistency in the HTML renderer ... ?
> Michael Carr
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:eXqVC9weEHA.3680@.TK2MSFTNGP11.phx.gbl...
> > vbNewLine
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Michael Carr" <mcarr@.umich.edu> wrote in message
> > news:uOApiLweEHA.3200@.TK2MSFTNGP09.phx.gbl...
> > > Can I insert a newline character within a reporting services format
> > string?
> > >
> > > I guess \n doesn't work since it is interpreted as a VisualBasic
format
> > > string...? Is there an alternative?
> > >
> > > Thanks,
> > > Michael Carr
> > >
> > >
> >
> >
>|||Ravi,
Please see the report below (and attached) for an example. Note that this
renders correctly in the Report Editor Preview, but does not render
correctly in the Report Manager.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
�Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Date</Value>
�/Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
�Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Date</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Date">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>= "M/d/yyyy" & vbNewline & "h:mm tt"</Format>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Date</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Date.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Date_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Date_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Format(Fields!Date.Value, "M/d/yyyy") & vbNewline &
Format(Fields!Date.Value, "h:mm tt")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Top>0.5in</Top>
<Width>4.33333in</Width>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<Left>1.125in</Left>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="SkyScheduler">
<rd:DataSourceID>6617abfe-bebd-49ae-a365-417ab349c748</rd:DataSourceID>
<DataSourceReference>SkyScheduler</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Date">
<DataField>Date</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>SkyScheduler</DataSourceName>
<CommandText>SELECT CONVERT(smalldatetime, '1/1/2000 3:00pm') AS
Date</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>6c9977cc-7283-416b-a107-65d98a0c8dd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:ODeNfFnfEHA.708@.TK2MSFTNGP09.phx.gbl...
> Please clarify the steps to reproduce. I tried the first expression on SP1
> and I get <br/>. Here's the expression I used: ="1/1/2004" & vbNewLine &
> "12:00:00".
begin 666 BRExample.rdl
M/#]X;6P@.=F5R<VEO;CTB,2XP(B!E;F-O9&EN9STB=71F+3@.B/SX-"CQ297!O
M<G0@.>&UL;G,](FAT=' Z+R]S8VAE;6%S+FUI8W)O<V]F="YC;VTO<W%L<V5R
M=F5R+W)E<&]R=&EN9R\R,# S+S$P+W)E<&]R=&1E9FEN:71I;VXB('AM;&YS
M.G)D/2)H='1P.B\O<V-H96UA<RYM:6-R;W-O9G0N8V]M+U-13%-E<G9E<B]R
M97!O<G1I;F<O<F5P;W)T9&5S:6=N97(B/@.T*(" \4FEG:'1-87)G:6X^,6EN
M/"]2:6=H=$UA<F=I;CX-"B @./$)O9'D^#0H@.(" @./%)E<&]R=$ET96US/@.T*
M(" @.(" @./%1A8FQE($YA;64](G1A8FQE,2(^#0H@.(" @.(" @.(#Q(96EG:'0^
M,"XW-6EN/"](96EG:'0^#0H@.(" @.(" @.(#Q3='EL92 O/@.T*(" @.(" @.(" \
M2&5A9&5R/@.T*(" @.(" @.(" @.(#Q486)L95)O=W,^#0H@.(" @.(" @.(" @.(" \
M5&%B;&52;W<^#0H@.(" @.(" @.(" @.(" @.(#Q(96EG:'0^,"XR-6EN/"](96EG
M:'0^#0H@.(" @.(" @.(" @.(" @.(#Q486)L94-E;&QS/@.T*(" @.(" @.(" @.(" @.
M(" @.(#Q486)L94-E;&P^#0H@.(" @.(" @.(" @.(" @.(" @.(" \4F5P;W)T271E
M;7,^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(#Q497AT8F]X($YA;64](G1E>'1B
M;W@.Q(CX-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" \4W1Y;&4^#0H@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" \4&%D9&EN9TQE9G0^,G!T/"]0861D:6YG3&5F
M=#X-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q497AT06QI9VX^3&5F=#PO
M5&5X=$%L:6=N/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @./%!A9&1I;F="
M;W1T;VT^,G!T/"]0861D:6YG0F]T=&]M/@.T*(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @./%!A9&1I;F=4;W ^,G!T/"]0861D:6YG5&]P/@.T*(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @./%!A9&1I;F=2:6=H=#XR<'0\+U!A9&1I;F=2:6=H
M=#X-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" \+U-T>6QE/@.T*(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(#Q:26YD97@.^-3PO6DEN9&5X/@.T*(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(#QR9#I$969A=6QT3F%M93YT97AT8F]X,3PO<F0Z1&5F875L
M=$YA;64^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @./$-A;D=R;W<^=')U93PO
M0V%N1W)O=SX-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" \5F%L=64^1&%T93PO
M5F%L=64^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(#PO5&5X=&)O>#X-"B @.(" @.
M(" @.(" @.(" @.(" @.(#PO4F5P;W)T271E;7,^#0H@.(" @.(" @.(" @.(" @.(" @.
M/"]486)L94-E;&P^#0H@.(" @.(" @.(" @.(" @.(" @./%1A8FQE0V5L;#X-"B @.
M(" @.(" @.(" @.(" @.(" @.(#Q297!O<G1)=&5M<SX-"B @.(" @.(" @.(" @.(" @.
M(" @.(" @./%1E>'1B;W@.@.3F%M93TB=&5X=&)O>#(B/@.T*(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(#Q3='EL93X-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q0
M861D:6YG3&5F=#XR<'0\+U!A9&1I;F=,969T/@.T*(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @./%1E>'1!;&EG;CY,969T/"]497AT06QI9VX^#0H@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" \4&%D9&EN9T)O='1O;3XR<'0\+U!A9&1I;F="
M;W1T;VT^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" \4&%D9&EN9U1O<#XR
M<'0\+U!A9&1I;F=4;W ^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" \4&%D
M9&EN9U)I9VAT/C)P=#PO4&%D9&EN9U)I9VAT/@.T*(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(#PO4W1Y;&4^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @./%I);F1E
M>#XT/"]:26YD97@.^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @./')D.D1E9F%U
M;'1.86UE/G1E>'1B;W@.R/"]R9#I$969A=6QT3F%M93X-"B @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" \0V%N1W)O=SYT<G5E/"]#86Y'<F]W/@.T*(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(#Q686QU93Y$871E/"]686QU93X-"B @.(" @.(" @.(" @.
M(" @.(" @.(" @./"]497AT8F]X/@.T*(" @.(" @.(" @.(" @.(" @.(" @./"]297!O
M<G1)=&5M<SX-"B @.(" @.(" @.(" @.(" @.(" \+U1A8FQE0V5L;#X-"B @.(" @.
M(" @.(" @.(" @./"]486)L94-E;&QS/@.T*(" @.(" @.(" @.(" @./"]486)L95)O
M=SX-"B @.(" @.(" @.(" \+U1A8FQE4F]W<SX-"B @.(" @.(" @./"](96%D97(^
M#0H@.(" @.(" @.(#Q$971A:6QS/@.T*(" @.(" @.(" @.(#Q486)L95)O=W,^#0H@.
M(" @.(" @.(" @.(" \5&%B;&52;W<^#0H@.(" @.(" @.(" @.(" @.(#Q(96EG:'0^
M,"XR-6EN/"](96EG:'0^#0H@.(" @.(" @.(" @.(" @.(#Q486)L94-E;&QS/@.T*
M(" @.(" @.(" @.(" @.(" @.(#Q486)L94-E;&P^#0H@.(" @.(" @.(" @.(" @.(" @.
M(" \4F5P;W)T271E;7,^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(#Q497AT8F]X
M($YA;64](D1A=&4B/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q3='EL93X-
M"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q0861D:6YG3&5F=#XR<'0\+U!A
M9&1I;F=,969T/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @./$9O<FUA=#X]
M(")-+V0O>7EY>2(@.)F%M<#L@.=F).97=L:6YE("9A;7 [(")H.FUM('1T(CPO
M1F]R;6%T/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @./%1E>'1!;&EG;CY,
M969T/"]497AT06QI9VX^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" \4&%D
M9&EN9T)O='1O;3XR<'0\+U!A9&1I;F=";W1T;VT^#0H@.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" \4&%D9&EN9U1O<#XR<'0\+U!A9&1I;F=4;W ^#0H@.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" \4&%D9&EN9U)I9VAT/C)P=#PO4&%D9&EN
M9U)I9VAT/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#PO4W1Y;&4^#0H@.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @./%I);F1E>#XQ/"]:26YD97@.^#0H@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @./')D.D1E9F%U;'1.86UE/D1A=&4\+W)D.D1E9F%U
M;'1.86UE/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q#86Y'<F]W/G1R=64\
M+T-A;D=R;W<^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @./%9A;'5E/CU&:65L
M9',A1&%T92Y686QU93PO5F%L=64^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(#PO
M5&5X=&)O>#X-"B @.(" @.(" @.(" @.(" @.(" @.(#PO4F5P;W)T271E;7,^#0H@.
M(" @.(" @.(" @.(" @.(" @./"]486)L94-E;&P^#0H@.(" @.(" @.(" @.(" @.(" @.
M/%1A8FQE0V5L;#X-"B @.(" @.(" @.(" @.(" @.(" @.(#Q297!O<G1)=&5M<SX-
M"B @.(" @.(" @.(" @.(" @.(" @.(" @./%1E>'1B;W@.@.3F%M93TB1&%T95\Q(CX-
M"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" \4W1Y;&4^#0H@.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" \4&%D9&EN9TQE9G0^,G!T/"]0861D:6YG3&5F=#X-"B @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q497AT06QI9VX^3&5F=#PO5&5X=$%L
M:6=N/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @./%!A9&1I;F=";W1T;VT^
M,G!T/"]0861D:6YG0F]T=&]M/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M/%!A9&1I;F=4;W ^,G!T/"]0861D:6YG5&]P/@.T*(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @./%!A9&1I;F=2:6=H=#XR<'0\+U!A9&1I;F=2:6=H=#X-"B @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" \+U-T>6QE/@.T*(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(#QR9#I$969A=6QT3F%M93Y$871E7S$\+W)D.D1E9F%U;'1.86UE
M/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q#86Y'<F]W/G1R=64\+T-A;D=R
M;W<^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @./%9A;'5E/CU&;W)M870H1FEE
M;&1S(41A=&4N5F%L=64L(")-+V0O>7EY>2(I("9A;7 [('9B3F5W;&EN92 F
M86UP.R!&;W)M870H1FEE;&1S(41A=&4N5F%L=64L(")H.FUM('1T(BD\+U9A
M;'5E/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" \+U1E>'1B;W@.^#0H@.(" @.(" @.
M(" @.(" @.(" @.(" \+U)E<&]R=$ET96US/@.T*(" @.(" @.(" @.(" @.(" @.(#PO
M5&%B;&5#96QL/@.T*(" @.(" @.(" @.(" @.(" \+U1A8FQE0V5L;',^#0H@.(" @.
M(" @.(" @.(" \+U1A8FQE4F]W/@.T*(" @.(" @.(" @.(#PO5&%B;&52;W=S/@.T*
M(" @.(" @.(" \+T1E=&%I;',^#0H@.(" @.(" @.(#Q$871A4V5T3F%M93Y$871A
M4V5T,3PO1&%T85-E=$YA;64^#0H@.(" @.(" @.(#Q4;W ^,"XU:6X\+U1O<#X-
M"B @.(" @.(" @./%=I9'1H/C0N,S,S,S-I;CPO5VED=&@.^#0H@.(" @.(" @.(#Q&
M;V]T97(^#0H@.(" @.(" @.(" @./%1A8FQE4F]W<SX-"B @.(" @.(" @.(" @.(#Q4
M86)L95)O=SX-"B @.(" @.(" @.(" @.(" @./$AE:6=H=#XP+C(U:6X\+TAE:6=H
M=#X-"B @.(" @.(" @.(" @.(" @./%1A8FQE0V5L;',^#0H@.(" @.(" @.(" @.(" @.
M(" @./%1A8FQE0V5L;#X-"B @.(" @.(" @.(" @.(" @.(" @.(#Q297!O<G1)=&5M
M<SX-"B @.(" @.(" @.(" @.(" @.(" @.(" @./%1E>'1B;W@.@.3F%M93TB=&5X=&)O
M>#<B/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q3='EL93X-"B @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(#Q0861D:6YG3&5F=#XR<'0\+U!A9&1I;F=,969T
M/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @./%!A9&1I;F=";W1T;VT^,G!T
M/"]0861D:6YG0F]T=&]M/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @./%!A
M9&1I;F=4;W ^,G!T/"]0861D:6YG5&]P/@.T*(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @./%!A9&1I;F=2:6=H=#XR<'0\+U!A9&1I;F=2:6=H=#X-"B @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" \+U-T>6QE/@.T*(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(#Q:26YD97@.^,SPO6DEN9&5X/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(#QR9#I$969A=6QT3F%M93YT97AT8F]X-SPO<F0Z1&5F875L=$YA;64^#0H@.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @./$-A;D=R;W<^=')U93PO0V%N1W)O=SX-
M"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" \5F%L=64@.+SX-"B @.(" @.(" @.(" @.
M(" @.(" @.(" @./"]497AT8F]X/@.T*(" @.(" @.(" @.(" @.(" @.(" @./"]297!O
M<G1)=&5M<SX-"B @.(" @.(" @.(" @.(" @.(" \+U1A8FQE0V5L;#X-"B @.(" @.
M(" @.(" @.(" @.(" \5&%B;&5#96QL/@.T*(" @.(" @.(" @.(" @.(" @.(" @./%)E
M<&]R=$ET96US/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" \5&5X=&)O>"!.86UE
M/2)T97AT8F]X."(^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @./%-T>6QE/@.T*
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @./%!A9&1I;F=,969T/C)P=#PO4&%D
M9&EN9TQE9G0^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" \4&%D9&EN9T)O
M='1O;3XR<'0\+U!A9&1I;F=";W1T;VT^#0H@.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" \4&%D9&EN9U1O<#XR<'0\+U!A9&1I;F=4;W ^#0H@.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" \4&%D9&EN9U)I9VAT/C)P=#PO4&%D9&EN9U)I9VAT
M/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#PO4W1Y;&4^#0H@.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @./%I);F1E>#XR/"]:26YD97@.^#0H@.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @./')D.D1E9F%U;'1.86UE/G1E>'1B;W@.X/"]R9#I$969A=6QT
M3F%M93X-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" \0V%N1W)O=SYT<G5E/"]#
M86Y'<F]W/@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q686QU92 O/@.T*(" @.
M(" @.(" @.(" @.(" @.(" @.(" \+U1E>'1B;W@.^#0H@.(" @.(" @.(" @.(" @.(" @.
M(" \+U)E<&]R=$ET96US/@.T*(" @.(" @.(" @.(" @.(" @.(#PO5&%B;&5#96QL
M/@.T*(" @.(" @.(" @.(" @.(" \+U1A8FQE0V5L;',^#0H@.(" @.(" @.(" @.(" \
M+U1A8FQE4F]W/@.T*(" @.(" @.(" @.(#PO5&%B;&52;W=S/@.T*(" @.(" @.(" \
M+T9O;W1E<CX-"B @.(" @.(" @./$QE9G0^,2XQ,C5I;CPO3&5F=#X-"B @.(" @.
M(" @./%1A8FQE0V]L=6UN<SX-"B @.(" @.(" @.(" \5&%B;&5#;VQU;6X^#0H@.
M(" @.(" @.(" @.(" \5VED=&@.^,BXQ-C8V-VEN/"]7:61T:#X-"B @.(" @.(" @.
M(" \+U1A8FQE0V]L=6UN/@.T*(" @.(" @.(" @.(#Q486)L94-O;'5M;CX-"B @.
M(" @.(" @.(" @.(#Q7:61T:#XR+C$V-C8W:6X\+U=I9'1H/@.T*(" @.(" @.(" @.
M(#PO5&%B;&5#;VQU;6X^#0H@.(" @.(" @.(#PO5&%B;&5#;VQU;6YS/@.T*(" @.
M(" @./"]486)L93X-"B @.(" \+U)E<&]R=$ET96US/@.T*(" @.(#Q3='EL92 O
M/@.T*(" @.(#Q(96EG:'0^,FEN/"](96EG:'0^#0H@.(#PO0F]D>3X-"B @./%1O
M<$UA<F=I;CXQ:6X\+U1O<$UA<F=I;CX-"B @./$1A=&%3;W5R8V5S/@.T*(" @.
M(#Q$871A4V]U<F-E($YA;64](E-K>5-C:&5D=6QE<B(^#0H@.(" @.(" \<F0Z
M1&%T85-O=7)C94E$/C8V,3=A8F9E+6)E8F0M-#EA92UA,S8U+30Q-V%B,S0Y
M8S<T.#PO<F0Z1&%T85-O=7)C94E$/@.T*(" @.(" @./$1A=&%3;W5R8V52969E
M<F5N8V4^4VMY4V-H961U;&5R/"]$871A4V]U<F-E4F5F97)E;F-E/@.T*(" @.
M(#PO1&%T85-O=7)C93X-"B @./"]$871A4V]U<F-E<SX-"B @./%=I9'1H/C8N
M-6EN/"]7:61T:#X-"B @./$1A=&%3971S/@.T*(" @.(#Q$871A4V5T($YA;64]
M(D1A=&%3970Q(CX-"B @.(" @.(#Q&:65L9',^#0H@.(" @.(" @.(#Q&:65L9"!.
M86UE/2)$871E(CX-"B @.(" @.(" @.(" \1&%T849I96QD/D1A=&4\+T1A=&%&
M:65L9#X-"B @.(" @.(" @.(" \<F0Z5'EP94YA;64^4WES=&5M+D1A=&54:6UE
M/"]R9#I4>7!E3F%M93X-"B @.(" @.(" @./"]&:65L9#X-"B @.(" @.(#PO1FEE
M;&1S/@.T*(" @.(" @./%%U97)Y/@.T*(" @.(" @.(" \1&%T85-O=7)C94YA;64^
M4VMY4V-H961U;&5R/"]$871A4V]U<F-E3F%M93X-"B @.(" @.(" @./$-O;6UA
M;F1497AT/E-%3$5#5"!#3TY615)4*'-M86QL9&%T971I;64L("<Q+S$O,C P
M," S.C P<&TG*2!!4R!$871E/"]#;VUM86YD5&5X=#X-"B @.(" @.(" @./')D
M.E5S94=E;F5R:6-$97-I9VYE<CYT<G5E/"]R9#I5<V5'96YE<FEC1&5S:6=N
M97(^#0H@.(" @.(" \+U%U97)Y/@.T*(" @.(#PO1&%T85-E=#X-"B @./"]$871A
M4V5T<SX-"B @./$QE9G1-87)G:6X^,6EN/"],969T36%R9VEN/@.T*(" \<F0Z
M4VYA<%1O1W)I9#YT<G5E/"]R9#I3;F%P5&]'<FED/@.T*(" \<F0Z1')A=T=R
M:60^=')U93PO<F0Z1')A=T=R:60^#0H@.(#QR9#I297!O<G1)1#XV8SDY-S=C
M8RTW,C@.S+30Q-F(M83$P-RTV-60Y.&$P8SAD9#(\+W)D.E)E<&]R=$E$/@.T*
M(" \0F]T=&]M36%R9VEN/C%I;CPO0F]T=&]M36%R9VEN/@.T*(" \3&%N9W5A
>9V4^96XM55,\+TQA;F=U86=E/@.T*/"]297!O<G0^
`
end|||Ravi,
Did you get a chance to look at the example I posted?
Michael Carr
"Michael Carr" <mcarr@.umich.edu> wrote in message
news:uBDHDenfEHA.3676@.TK2MSFTNGP12.phx.gbl...
> Ravi,
> Please see the report below (and attached) for an example. Note that this
> renders correctly in the Report Editor Preview, but does not render
> correctly in the Report Manager.|||I will look into this today.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Carr" <mcarr@.umich.edu> wrote in message
news:eOjzh9ShEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Ravi,
> Did you get a chance to look at the example I posted?
> Michael Carr
> "Michael Carr" <mcarr@.umich.edu> wrote in message
> news:uBDHDenfEHA.3676@.TK2MSFTNGP12.phx.gbl...
> > Ravi,
> >
> > Please see the report below (and attached) for an example. Note that
this
> > renders correctly in the Report Editor Preview, but does not render
> > correctly in the Report Manager.
>|||I have logged this as a bug in HTML renderer which may be fixed in a future
release. Thanks for reporting this issue.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:OXh8fEThEHA.3916@.TK2MSFTNGP11.phx.gbl...
> I will look into this today.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Michael Carr" <mcarr@.umich.edu> wrote in message
> news:eOjzh9ShEHA.3864@.TK2MSFTNGP10.phx.gbl...
> > Ravi,
> >
> > Did you get a chance to look at the example I posted?
> >
> > Michael Carr
> >
> > "Michael Carr" <mcarr@.umich.edu> wrote in message
> > news:uBDHDenfEHA.3676@.TK2MSFTNGP12.phx.gbl...
> > > Ravi,
> > >
> > > Please see the report below (and attached) for an example. Note that
> this
> > > renders correctly in the Report Editor Preview, but does not render
> > > correctly in the Report Manager.
> >
> >
>

Newline Character?

Hi, is there an newline character (e.g. \n ) which explicitly instructs the report designer to insert a new line ? I haven't been able to find anything in the Reporting Definition Language Specification Paper and I was wondering if anyone knows the answer.
Thanks.You can use VB's new line constant : vbNewLine, like this :

="General" & vbNewLine & "Ledger"

|||Thanks - worked like a charm

NewLine and Carriage return issue in PDF format

If I insert the following expression in a textbox it will display
correctly in HTML format but not in PDF format. Can anyone show me how
to accomplish the same thing so that it will display correctly in both
HTML and and PDF format.
vbCrLf & vbCrLf & vbCrLf & "My Text"
In HTML format, it will display "My Text" on the 4th "row". Whereas in
PDF format, it displays "My Text" on the first row of the textbox.This has to be an issue (bug) with PDF and how it formats initial blank
space before the literal text. I am not sure if this is addressed in a
service pack for 2000 or in 2005 (I am using 2000 to test this) but here is
a workaround that I found that places an ASCII record seperator in the first
position before the CRLF's as:
=CHR(30)&vbCrLf & vbCrLf & vbCrLf & "My Text"
That should work.
Rodney Landrum
<Nergock@.gmail.com> wrote in message
news:1143588675.322590.215140@.u72g2000cwu.googlegroups.com...
> If I insert the following expression in a textbox it will display
> correctly in HTML format but not in PDF format. Can anyone show me how
> to accomplish the same thing so that it will display correctly in both
> HTML and and PDF format.
> vbCrLf & vbCrLf & vbCrLf & "My Text"
> In HTML format, it will display "My Text" on the 4th "row". Whereas in
> PDF format, it displays "My Text" on the first row of the textbox.
>

newby - AUTOINCREMENT problem.

Hi,

I'm enclosed a snippet of test code which highlights my problem. The Stored
procedure insertValue should insert text into the parent, then insert other
text into the child table but the 2 tables should auto increment in sync
(i.e. so that they both end up with the same id numbers). I've tried taking
the auto increment out of the child table but then I don't know how to get
the right parent id into the child table.

Any advice appreciated - this is my first database, so I'm just in the
learning process really. Code follows:

CREATE TABLE Parent
(id INTEGER DEFAULT AUTOINCREMENT,
parenttext VARCHAR(16),
PRIMARY KEY (id))!

CREATE TABLE Child
(childID INTEGER INTEGER DEFAULT AUTOINCREMENT,
childtext VARCHAR(16),
FOREIGN KEY (childid) REFERENCES Parent(id),
PRIMARY KEY (childID))!

CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))
BEGIN
insert into parent (parenttext) values (p);
insert into child (childtext) values (c);
END!

call insertValues('from parent', 'from child')!
select * from parent, child where parent.id = child.childid!In message <436cefc6$1_3@.mk-nntp-2.news.uk.tiscali.com>, Mary Walker
<123@.123.com> writes
>Hi,
>I'm enclosed a snippet of test code which highlights my problem. The Stored
>procedure insertValue should insert text into the parent, then insert other
>text into the child table but the 2 tables should auto increment in sync
>(i.e. so that they both end up with the same id numbers). I've tried taking
>the auto increment out of the child table but then I don't know how to get
>the right parent id into the child table.
>Any advice appreciated - this is my first database, so I'm just in the
>learning process really. Code follows:

OK. The first piece of advice is don't rely on the autonumber to return
any particular value. There are ways that the values in the two tables
could get out of sync and it would be a PITA to fix.

There are two options that I could suggest.

One is that you create the parent record and then take the autonumber
value from there and explicitly insert it into the child table. Don't
use an autonumber in child table, use a simple integer instead. This is
the way I recommend that you do it.

The second method has limited applicability but can make things easier.
First redesign your child table with an integer key. Then create a query
that links the two tables. You can now update the query instead of
updating the two tables separately.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.|||>> this is my first database, so I'm just in the learning process really. <<

Get a book on RDBMS and learn why an auto-increment can NEVER be a
relational key. Learn why a table name should be a collective or
plural name (unless there is only one row in the table). Learn way
"id" is too vague to be data element name. Learn why camelCase is a
bitch to read --hint: where does your eye jump when you see an
Uppercase letter?

I know you want to have a "Magical Universal Key" that will solve all
your design problems, without having to really think or learn anything.
What is your **real key** in your **real** problem?

Going to a Newsgroup to get what usually takes a few YEARS of college
and experience does not work.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1131243682.809956.301200@.g49g2000cwa.googlegr oups.com...
>>> this is my first database, so I'm just in the learning process really.
>>> <<
> Get a book on RDBMS and learn why an auto-increment can NEVER be a
> relational key. Learn why a table name should be a collective or
> plural name (unless there is only one row in the table). Learn way
> "id" is too vague to be data element name. Learn why camelCase is a
> bitch to read --hint: where does your eye jump when you see an
> Uppercase letter?
> I know you want to have a "Magical Universal Key" that will solve all
> your design problems, without having to really think or learn anything.
> What is your **real key** in your **real** problem?
> Going to a Newsgroup to get what usually takes a few YEARS of college
> and experience does not work.

LOL. Thanks very much you for reply. I have actually learnt a lot by
simply reading your reply - but I'll buy the book anyway :-)|||In your book 'sql for smarties' you have an example with a table named
'Warehouse' - if you where to follow your own naming standards then it
should be called 'Warehouses'.

'id' is fine within the context of the table, if the column belongs to the
table 'Child' then its obviously Child.id.

What key would you use for a message board? Would you still key it on
subject and posting date or like the rest of us and how NNTP works, create a
guid with the domain?

The auto-number (IDENTITY property) is usually used as a artificial (or
surrogate) key, there is no such thing as a relational key - I think you
mean 'natural key', see: http://en.wikipedia.org/wiki/Natural_key.

Quoting from that aritcle...

"
The main disadvantage of choosing a natural key is that it may need to
change if your business requirements change. For example, if you have chosen
CustomerNumber as the primary key for a customer, and, subsequently,
CustomerNumber becomes alphanumeric instead of numeric, then as well as
changing the type of the column, you will need to make changes to all other
tables where CustomerNumber is used as a foreign key.

Retrieved from "http://en.wikipedia.org/wiki/Natural_key"

"

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1131243682.809956.301200@.g49g2000cwa.googlegr oups.com...
>>> this is my first database, so I'm just in the learning process really.
>>> <<
> Get a book on RDBMS and learn why an auto-increment can NEVER be a
> relational key. Learn why a table name should be a collective or
> plural name (unless there is only one row in the table). Learn way
> "id" is too vague to be data element name. Learn why camelCase is a
> bitch to read --hint: where does your eye jump when you see an
> Uppercase letter?
> I know you want to have a "Magical Universal Key" that will solve all
> your design problems, without having to really think or learn anything.
> What is your **real key** in your **real** problem?
> Going to a Newsgroup to get what usually takes a few YEARS of college
> and experience does not work.|||Hi Mary,

The SCOPE_IDENTITY() will return the last inserted IDENTITY value for that
connection.

insert into parent (parenttext) values (p);
print scope_identity() -- gives the id from parent

insert into child (childtext) values (c);
print scope_identity() -- gives id from child

Does that help?

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Mary Walker" <123@.123.com> wrote in message
news:436cefc6$1_3@.mk-nntp-2.news.uk.tiscali.com...
> Hi,
> I'm enclosed a snippet of test code which highlights my problem. The
> Stored procedure insertValue should insert text into the parent, then
> insert other text into the child table but the 2 tables should auto
> increment in sync (i.e. so that they both end up with the same id
> numbers). I've tried taking the auto increment out of the child table but
> then I don't know how to get the right parent id into the child table.
> Any advice appreciated - this is my first database, so I'm just in the
> learning process really. Code follows:
> CREATE TABLE Parent
> (id INTEGER DEFAULT AUTOINCREMENT,
> parenttext VARCHAR(16),
> PRIMARY KEY (id))!
> CREATE TABLE Child
> (childID INTEGER INTEGER DEFAULT AUTOINCREMENT,
> childtext VARCHAR(16),
> FOREIGN KEY (childid) REFERENCES Parent(id),
> PRIMARY KEY (childID))!
> CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))
> BEGIN
> insert into parent (parenttext) values (p);
> insert into child (childtext) values (c);
> END!
> call insertValues('from parent', 'from child')!
> select * from parent, child where parent.id = child.childid!

Newbie's Last Question

Current code is below:
||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||
INSERT INTO FORUM_ALLOWED_MEMBERS
(
Member_ID,
Forum_ID
)
SELECT
FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS + '%'
WHERE ISNULL(FORUM_MEMBERS.M_MARSTATUS,'') <> '' AND
NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A
WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND
A.Forum_ID = FORUM_FORUM.Forum_ID) AND
EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE >= 20050322000000)
||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||
F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150. BlaBl
a" .......
F_MARSTATUS Values = ... "147" "148" 149" "150" "151" "152" "153" ........
Current code add members to forum which subject is like member's m_marstatus
value.
How to add members to forums which subject contains m_marstatus value's -3 a
nd +3.
For example M_MARSTATUS = 150, member will be allowed by forums between "147
. BlaBla" and "153. BlaBla"
Can i use 7 different inner joins to do thissuch as:
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS + '%'
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS -3 + '%'
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS -2 + '%'
etc.I'm sorry to say that you are now experiencing the difficulties of coding
against a poorly designed database schema (structure) . The first thing to
do would be to redesign the tables so they are at least in "3rd Normal form"
.
If you don't know what that means, read up on it a bt...
Your immediate problem is caused by having violated one of the first rules
of database normalization, that is of putting more than one piece of
information into a single column. Each attribute, or aspect about a "thing"
should be in it's own column. You have Marital Status, and Subject mixed
together in one column. Then, you also have multiple individual data
elements of the SAME type in that single column. If you fix these issues yo
u
will find the coding problem you are facing will just go away.
"LacOniC" wrote:

> Current code is below:
> ||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||
> INSERT INTO FORUM_ALLOWED_MEMBERS
> (
> Member_ID,
> Forum_ID
> )
> SELECT
> FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS + '%'
> WHERE ISNULL(FORUM_MEMBERS.M_MARSTATUS,'') <> '' AND
> NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A
> WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND
> A.Forum_ID = FORUM_FORUM.Forum_ID) AND
> EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE >= 20050322000000)
> ||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||
> F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150. Bla
Bla" .......
> F_MARSTATUS Values = ... "147" "148" 149" "150" "151" "152" "153" ......
.
> Current code add members to forum which subject is like member's m_marstat
us value.
> How to add members to forums which subject contains m_marstatus value's -3
and +3.
> For example M_MARSTATUS = 150, member will be allowed by forums between "1
47. BlaBla" and "153. BlaBla"
> Can i use 7 different inner joins to do thissuch as:
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS + '%'
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS -3 + '%'
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS -2 + '%'
> etc.
>
>|||This is Snitz Forum Database. If i edit database structure, i get more probl
ems. (For example when i update
forum version)
"You have Marital Status, and Subject mixed together in one column. "
Didn't understand this one. They are in different tables and columns.
Thank you.|||In your first post, you said...
F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150.
BlaBla"
I assumed the numeric values are Marital Status, and the "BlaBla" (based on
the column name "F_SUBJECT"), have something to do with, Subjects? And thi
s
is all in the same column, no?
"LacOniC" wrote:

> This is Snitz Forum Database. If i edit database structure, i get more
problems. (For example when i update
> forum version)
> "You have Marital Status, and Subject mixed together in one column. "
> Didn't understand this one. They are in different tables and columns.
> Thank you.
>
>|||Probably my mistake. Column values are such as below:
F_SUBJECT
...
147. BlaBla
148. BlaBla
150. BlaBla
...
M_MARSTATUS
...
147
148
149
...
M_MARSTATUS column holds an user information, F_SUBJECT column holds forum s
ubject. They are relative but not
same. So i use LIKE.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:4A0BFEA5-5996-4442-A411-BEFB84A611FA@.microsoft.com...
> In your first post, you said...
> F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150.
> BlaBla"
> I assumed the numeric values are Marital Status, and the "BlaBla" (based o
n
> the column name "F_SUBJECT"), have something to do with, Subjects? And t
his
> is all in the same column, no?
> "LacOniC" wrote:
>
update|||If F_Subject contains...
147. BlaBla
148. BlaBla
150. BlaBla
What is the value "147" in the first row above represent? Is it a Subject?
If it is some Subject, you can't link it (using LIKE) to the values in the
M_MARSTATUS
column. If it IS Marital Status, then it should be in it's own column as a
foreign Key, NOT concatenated wit hSubject data in the F_SUBJET Column.
"LacOniC" wrote:

> Probably my mistake. Column values are such as below:
> F_SUBJECT
> ...
> 147. BlaBla
> 148. BlaBla
> 150. BlaBla
> ...
> M_MARSTATUS
> ...
> 147
> 148
> 149
> ...
> M_MARSTATUS column holds an user information, F_SUBJECT column holds forum
subject. They are relative but not
> same. So i use LIKE.
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:4A0BFEA5-5996-4442-A411-BEFB84A611FA@.microsoft.com...
> update
>
>|||Oh no. I don't hold marital status values in that column or anywhere else. I
use that column to hold user's
sh information.
If user belongs to "150. period of school", he should see that period and ot
hers that is closed his period.
(147,148,149,151,152,153).
With query that i wrote in first message, user just see 150.|||If you don't "hold marital status values in that column or anywhere else"...
Then how can you be joining this column (FORUM_FORUM.F_SUBJECT), with the
FORUM_MEMBERS.M_MARSTATUS column? If the numbers in this column are NOT
marital statuses, then what is this join doing '
From your original post...
FROM FORUM_MEMBERS
INNER JOIN FORUM_FORUM ON
FORUM_FORUM.F_SUBJECT LIKE '%' +
FORUM_MEMBERS.M_MARSTATUS + '%'
"LacOniC" wrote:

> Oh no. I don't hold marital status values in that column or anywhere e
lse. I use that column to hold user's
> sh information.
> If user belongs to "150. period of school", he should see that period
and others that is closed his period.
> (147,148,149,151,152,153).
> With query that i wrote in first message, user just see 150.
>
>|||I wrote what column includes which value. I didn't say MaritalStatus cloumn
is empty. It just doesn't hold
what it says. It holds numbers as 150. In fact i can't say what join is doin
g there, lol. But it works very
well. =)|||Laconic,
Well if it works, then the value in there must mean something... Your
first task, before anyone can help you, (including yourself) is t ofind out
what is in the database, (Tables and Columns) and understand what the query
is supposed to be doing, and what it is doing...
That includes knowing, and understanding, the data that is in the database.
Until you do, you really shouldn't even be messing with it. If you're the
DBA, your first task should be to hire someone who knows enough about
database technology to do that. I say that reluctantlly, but, based on your
comments in this thread, it seems that you have a great deal to learn about
SQL and RDBMS technology in general, before your skills will be at the level
necessary to do what needs doing on this system.
"LacOniC" wrote:

> I wrote what column includes which value. I didn't say MaritalStatus c
loumn is empty. It just doesn't hold
> what it says. It holds numbers as 150. In fact i can't say what join is do
ing there, lol. But it works very
> well. =)
>
>

Newbie: UPDATE a Table

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:529989
Hi,
I want to insert some new data in a Table. There is a possibility that there
are duplicate rows .
An Insert is obviously not allowed due to PK violatons.
The new data reside on a temporary Table and there are NO duplicates!
Will an Update do?
I simply want to :
"If the row exists replace it, if not insert the new one"
TIA
-steveIf you know you are replacing the old rows when you have a match, then
why not just delete them and then insert this scrubbed data, like this?
BEGIN
DELETE FROM Foo
WHERE Foo.key_col
IN (SELECT key_col FROM Bar);
INSERT INTO Foo
SELECT * FROM Bar;
END;|||Ok, thanx!
But, for info, there is no command that will do this in one step ?
and in my case the PK consists of multiple columns. However i get an error:
DELETE FROM tblMeasQAHor
WHERE
(tblMeasQAHor.MENVid, tblMeasQAHor.date_time, tblMeasQAHor.CodePol,
tblMeasQAHor.Valide, tblMeasQAHor.val)
IN
(SELECT MENVid, date_time, CodePol, Valide, val FROM tempQAHor)
why? i tried a few combinations and it doesn't work. If i only leave one
column then it's OK.
TIA
"--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de news:
1117220035.893367.120570@.g14g2000cwa.googlegroups.com...
> If you know you are replacing the old rows when you have a match, then
> why not just delete them and then insert this scrubbed data, like this?
>
> BEGIN
> DELETE FROM Foo
> WHERE Foo.key_col
> IN (SELECT key_col FROM Bar);
> INSERT INTO Foo
> SELECT * FROM Bar;
> END;
>|||You'll need to do this in 2 steps. Delete then Insert
For the delete, test this out before you execute it on production code.
Replace temp1
with whatever your temp table name is
DELETE FROM tblMeasQAHor
FROM tblMeasQAHor t1
JOIN temp1 t2 on t1.MENVid = t2.MENVid
AND t1.date_time = t2.date_time
AND t1.CodePol = t2.CodePol
AND t1.Valide = t2.Valide
AND t1.val = t2.val
"steve" <steve@.here.com> wrote in message
news:n1Lle.40566$8j3.864446@.weber.videotron.net...
> Ok, thanx!
> But, for info, there is no command that will do this in one step ?
> and in my case the PK consists of multiple columns. However i get an
error:
> DELETE FROM tblMeasQAHor
> WHERE
> (tblMeasQAHor.MENVid, tblMeasQAHor.date_time, tblMeasQAHor.CodePol,
> tblMeasQAHor.Valide, tblMeasQAHor.val)
> IN
> (SELECT MENVid, date_time, CodePol, Valide, val FROM tempQAHor)
>
> why? i tried a few combinations and it doesn't work. If i only leave one
> column then it's OK.
> TIA
> "--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de news:
> 1117220035.893367.120570@.g14g2000cwa.googlegroups.com...
>|||I just want to clarify. If you want to do it the way Joe recommends, you'll
need to do it in 2 steps. You could also do a correlated subquery
(untested)
INSERT INTO tblMeasQAHor
SELECT *
FROM temp1 t1
WHERE NOT EXISTS (
SELECT 'X' FROM tblMeasQAHor t2 WHERE t1.MENVid = t2.MENVid
AND t1.date_time = t2.date_time
AND t1.CodePol = t2.CodePol
AND t1.Valide = t2.Valide
AND t1.val = t2.val)
I'm not sure which you prefer. Also, I generally recommend using column
names in the insert and select. I don't know what the table looks like
structurally so I went with the select * approach.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:eY77X9vYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> You'll need to do this in 2 steps. Delete then Insert
> For the delete, test this out before you execute it on production code.
> Replace temp1
> with whatever your temp table name is
> DELETE FROM tblMeasQAHor
> FROM tblMeasQAHor t1
> JOIN temp1 t2 on t1.MENVid = t2.MENVid
> AND t1.date_time = t2.date_time
> AND t1.CodePol = t2.CodePol
> AND t1.Valide = t2.Valide
> AND t1.val = t2.val
>
> "steve" <steve@.here.com> wrote in message
> news:n1Lle.40566$8j3.864446@.weber.videotron.net...
> error:
this?
>