sqlserver2000 date: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

increase size of varchar column.. table being replicated..

I have a table that is being replicated.. i would like to
increase the size of this column.. Is there a way to do
this without dropping the subscription?
Thanks,
niv
It can be done indirectly but it's not nice! You could add a new column with
the new datatype (sp_repladdcolumn), do an update on the table to populate
the column, then drop the column (sp_repldropcolumn). Do this again to
create the column having the same original name.
Alternatively, as you say, you can drop the publication then recreate from
scratch.
We're hoping that such things will be simpler in SQL Server 2005.
Regards,
Paul Ibison
sql

increase Ram?

i have DB 2GB on disk.
if i increase tha ram up to 4GB, the sql sever use with the
ram to his temporary table while process quiry?Hi

SQL 6.5 had an option to load tempdb in RAM but this is not available in
newer versions. In the later versions you can pin tables in memory but if
your table is temporary it would not be a candidate for this as small static
tables are more suitable for this option.

The ability to use more than 2GB of memory is dependent on the version of
SQL Server you are running and the version of Windows it is being run on. To
enable more than 2GB memory check out:
http://msdn.microsoft.com/library/d..._ar_sa_6b3k.asp

http://msdn.microsoft.com/library/d...server_1fnd.asp

John

"Mtcc" <m> wrote in message news:3fbdf127$1@.news.012.net.il...
> i have DB 2GB on disk.
> if i increase tha ram up to 4GB, the sql sever use with the
> ram to his temporary table while process quiry?

increase CPU time

Hi All,
I've a problem regarding increased CPU time.....
I've a stored procedure which joins approx 6
table and i'm using table variable to hold data. Stored procedure is working
fine and absolutely OK but when it runs on LIVE server through Web
Application, It increase CPU time and increase READS Per runs.
i added SET NOCOUNT ON and SET NOCOUNT OFF
as well as i added WITH RECOMPILE option, but no use it is still giving too
much reads per run.
is anybody having solution of this problem? Please
reply me ASAP...
Sword is hanging on my neck ';' help me
Manish SukhijaManish
What reads? Logical?
How about indexes defined on the tables? Do you see the optimizer is
available to create an efficient execution plan , i mean it uses the
indexes?
Actually if want more accurate answer please post DDL+ Sample data
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:49ACC575-E519-4473-9B0C-E7337BF94B17@.microsoft.com...
> Hi All,
> I've a problem regarding increased CPU time.....
> I've a stored procedure which joins approx 6
> table and i'm using table variable to hold data. Stored procedure is
> working
> fine and absolutely OK but when it runs on LIVE server through Web
> Application, It increase CPU time and increase READS Per runs.
> i added SET NOCOUNT ON and SET NOCOUNT OFF
> as well as i added WITH RECOMPILE option, but no use it is still giving
> too
> much reads per run.
> is anybody having solution of this problem? Please
> reply me ASAP...
> Sword is hanging on my neck ';' help me
> Manish Sukhija|||Hi Uri,
as i defined that i've stored procedure which joins approximately 6
table and when it runs om live server and when it is being checked by hosted
party in Profiler it shows
database is producing a high amount of cpu usage as well as page
reads in a perticular Stored procedure...
then i put a index on a column of one of joined table,
it reduce some time, now i want to know that is this the only solution to
decrease CPU time or is there any other way around, if this is only way then
on which table and ofcourse on which column should i put index. As you know
Stored procedure joins 6 table and they are having so may columns.
so should i put index on all columns of 6
table but as fas as i know it's not best practise to put index on all column
s
it can make adverse afftect on performance.
make me right if i'm wrong... or is there any other
way to decrease CPU time in that Stored procedure.
If you want code of that Stored procedure
i'll send it to you...
Manish
"Uri Dimant" wrote:

> Manish
> What reads? Logical?
> How about indexes defined on the tables? Do you see the optimizer is
> available to create an efficient execution plan , i mean it uses the
> indexes?
> Actually if want more accurate answer please post DDL+ Sample data
>
>
>
> "Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in messag
e
> news:49ACC575-E519-4473-9B0C-E7337BF94B17@.microsoft.com...
>
>|||Manish
Do you have FOREIGN KEY constraints? Does the key of referncing table have
an index?
http://www.sql-server-performance.c...or_counters.asp
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:1A433AA4-D9A0-47E2-82A4-1EEB092DD27B@.microsoft.com...
> Hi Uri,
> as i defined that i've stored procedure which joins approximately
> 6
> table and when it runs om live server and when it is being checked by
> hosted
> party in Profiler it shows
> database is producing a high amount of cpu usage as well as page
> reads in a perticular Stored procedure...
> then i put a index on a column of one of joined table,
> it reduce some time, now i want to know that is this the only solution to
> decrease CPU time or is there any other way around, if this is only way
> then
> on which table and ofcourse on which column should i put index. As you
> know
> Stored procedure joins 6 table and they are having so may columns.
> so should i put index on all columns of 6
> table but as fas as i know it's not best practise to put index on all
> columns
> it can make adverse afftect on performance.
> make me right if i'm wrong... or is there any other
> way to decrease CPU time in that Stored procedure.
> If you want code of that Stored procedure
> i'll send it to you...
> Manish
>
> "Uri Dimant" wrote:
>|||You may want to look at DBCC SHOWCONTIG if you aren't already running some
kind of defrag maintenence on your indexes.
--
Regards,
Jamie
"Manish Sukhija" wrote:

> Hi All,
> I've a problem regarding increased CPU time.....
> I've a stored procedure which joins approx 6
> table and i'm using table variable to hold data. Stored procedure is worki
ng
> fine and absolutely OK but when it runs on LIVE server through Web
> Application, It increase CPU time and increase READS Per runs.
> i added SET NOCOUNT ON and SET NOCOUNT OFF
> as well as i added WITH RECOMPILE option, but no use it is still giving to
o
> much reads per run.
> is anybody having solution of this problem? Please
> reply me ASAP...
> Sword is hanging on my neck ';' help me
> Manish Sukhijasql

Wednesday, March 28, 2012

Increase columns width of merge replicated table

We are using merge replication and it is working fine. We are looking for increase in on numeric column 10,5 instead of 7,2. How I can do it?
Any suggession highly appreciated by us.
ThanksI have only been able to accomplish this by copying out the data and the rowguid for each record, sp_repldropcolumn the old column, sp_repladdcolumn'ing it back in, and copying the data back in. (might not have sproc names exactly right)

I remember reading about a builtin sproc that you could execute that would run certain commands on a database or table that was in replication, but I can't find it's name, and I don't know it's limitations.

increase aggregations (design storage) programmatically (was "Please help thank

Fact table gets update every day with thousands of records, I been increase the aggregations manually. Is there a way I can increase aggregations (design storage) programmatically . My cub gets process ones in day with VB program. I want to increase aggregations before processing cub through VB.

It's argent

please help me

thanksCan you do a full process? If you're not running into time constraints, then I'd do a full process.|||As fact table's records increases, dont I have to increase the aggregation in the cub?|||No. Aggregations are dimension related, so as long as your not creating new dimensions then you don't have to worry about increasing aggregations. However, if processing time is not an issue, I'd do a full process.|||I am confused according to your statement
When I create cub with single records fact table the aggregations are zero.
When I create cub with thousands of records in fact table the aggregations are 850 or more.|||Here's what I'm trying to say:

The theoretical maximum number of possible aggregations in a cube is the product of the number of levels in each cube dimension. As you add levels and dimensions to a cube, the number of possible aggregations increases exponentially. The higher the number of dimensions and levels in a cube, the greater its complexity. In the example in Figure 2, the Time dimension has four levels, the Customers dimension has five levels, and the Products dimension has five levels. This yields a theoretical maximum number of aggregations of 100 (5 customer levels x 5 product levels x 4 time levels). However, this number increases exponentially as you add dimensions or levels. For example, if you add the Day level to the Time dimension, the theoretical maximum number of aggregations increases to 125 (5 x 5 x 5). Now, suppose you add two more dimensions to this cube, each with three levels. The theoretical maximum number of aggregations increases to 1125 (5 x 5 x 5 x 3 x 3). A cube with nine dimensions containing five levels each yields theoretical maximum number of aggregations of 1,953,125. A cube of this complexity is considered a cube of medium complexity. A cube of high complexity might have 20 dimensions with five levels each and yield a theoretical maximum number of aggregations of approximately 95 trillion. As you can see, you can directly affect the theoretical maximum number of aggregations in a cube by changing the number of dimensions or the number of levels. Having multiple dimensions with deep hierarchies improves the ability of users to perform analysis, but having too many of either can lead to resource problems during querying and processing.

Follow this link for more info...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx|||Yes I understand that

What I did is, my fact table was MT (no records) I added one dummy record and created cub out of it, basically dimensions contains single level. I have 40 dimensions and 35 measures. I created a script out of it and sending the script to user to create the cub in their analysis server. In the script I had prompt for users data source name. Users fact table contains millions of records. When user processes the cub with millions of records the dimensions will have multiple levels. So user has to recreate the aggregations?

Sorry, I know you are trying to clarify my doubts but I am still confused.

Thank you so much.|||I think I remember now. You're sharing an identical cube structure with someone, but you're each pointing to different data sources, correct?

If that is the case, the user would have to re-process. Especially since the dimensions will have multiple levels once your script has run against their data. With the numbers you've provided, I wouldn't do a full process. However, again, aggregations are based on dimensions, so you don't need to programatically increase the number of aggregations as new records are added to the fact. Even if new dimension levels are being created, I wouldn't increase the number of aggregations. Just select a particular "performance gain level" and let AS do the rest (i.e. 30%). Having said that, you should probably look into usage based optimization. I have a similar sized cube as you've described and I know that there are a lot of wasted aggregations in the cube (fully processed). It only gets updated once a month, so it's not a big deal to do a full process. Remember, as your cube gets more complex, it's unlikely that your users are making the most of that complexity. That's why usage base optimization makes sense. I haven't done usage based optimization "programatically", so I'm no help there.

I hope that makes sense.|||Thanks so much

User doesnt know about analysis server.
I want to provide user to click option to design storage.

After crating the cub on user server,

I am looking for VB code to
1. Count dimension members
2. Design storage

So user doesnt have to do it manually .sql

Incorrect values in RestoreHistory table

We have a SQL Server which is setting the wrong recovery bit in the table
msdb..restorehistory
i.e.
when databases restored WITH RECOVERY the recovery field has a value of 0
when databases restored WITH NORECOVERY the recovery field has a value of 1
I cannot find out why this is happening. Please assist
ThanksThe BOL is incorrect, 0 means Recovery, and 1 NORECOVERY.
>--Original Message--
>We have a SQL Server which is setting the wrong recovery
bit in the table
>msdb..restorehistory
>i.e.
>when databases restored WITH RECOVERY the recovery field
has a value of 0
>when databases restored WITH NORECOVERY the recovery
field has a value of 1
>I cannot find out why this is happening. Please assist
>Thanks
>
>.
>|||Thanks,
I thought I had verified this with the other servers, but
after double checking with the scritped test below, I
notice that you are correct
Is there any way to send feedbacks to Microsoft about
this, as I frequently find such things
/************Test RestoreHistory Entries******************/
create database test
backup database test to disk = '%temp%\t'
restore database test from disk = 't'
restore database test from disk = 't' with norecovery
restore database test from disk = 't' with recovery
select * from msdb..restorehistory where
destination_database_name = 'test' order by restore_date
drop database test
declare @.bdir varchar(255)
exec
master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
\MSSQLServer\MSSQLServer',
'BackupDirectory', @.bdir OUTPUT
set @.bdir = 'del "'+@.bdir+'\t"'
exec master..xp_cmdshell @.bdir
/*********************************************************/
>--Original Message--
>The BOL is incorrect, 0 means Recovery, and 1 NORECOVERY.
>>--Original Message--
>>We have a SQL Server which is setting the wrong recovery
>bit in the table
>>msdb..restorehistory
>>i.e.
>>when databases restored WITH RECOVERY the recovery field
>has a value of 0
>>when databases restored WITH NORECOVERY the recovery
>field has a value of 1
>>I cannot find out why this is happening. Please assist
>>Thanks
>>
>>.
>.
>|||Mike,
> Is there any way to send feedbacks to Microsoft about
> this, as I frequently find such things
Yes, there's a feedback option in Books Online. Top left of the right pane.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:0ea801c3b2d4$cbe095d0$a001280a@.phx.gbl...
> Thanks,
> I thought I had verified this with the other servers, but
> after double checking with the scritped test below, I
> notice that you are correct
> Is there any way to send feedbacks to Microsoft about
> this, as I frequently find such things
> /************Test RestoreHistory Entries******************/
> create database test
> backup database test to disk = '%temp%\t'
> restore database test from disk = 't'
> restore database test from disk = 't' with norecovery
> restore database test from disk = 't' with recovery
> select * from msdb..restorehistory where
> destination_database_name = 'test' order by restore_date
> drop database test
> declare @.bdir varchar(255)
> exec
> master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft
> \MSSQLServer\MSSQLServer',
> 'BackupDirectory', @.bdir OUTPUT
> set @.bdir = 'del "'+@.bdir+'\t"'
> exec master..xp_cmdshell @.bdir
> /*********************************************************/
>
> >--Original Message--
> >The BOL is incorrect, 0 means Recovery, and 1 NORECOVERY.
> >>--Original Message--
> >>We have a SQL Server which is setting the wrong recovery
> >bit in the table
> >>msdb..restorehistory
> >>
> >>i.e.
> >>when databases restored WITH RECOVERY the recovery field
> >has a value of 0
> >>when databases restored WITH NORECOVERY the recovery
> >field has a value of 1
> >>
> >>I cannot find out why this is happening. Please assist
> >>
> >>Thanks
> >>
> >>
> >>.
> >>
> >.
> >sql

Incorrect Values

Not to sure how to explain this, but will give it my best shot.

In the table I have values as listed below:

Reference
--------
5(a)
5(b)
5(b)(c)
50(a)(b)(c)
50(a)(b)(e)
55
55(a)(f)(g)

When a user searches for the 5 it should only bring rows 1,2 and 3. When he search for 50 it should only bring out rows 4 and 5. You cant use the LIKE keyword, as this brings out everything starting with 5.

Thanks...

Hope that make senseIf you could switch to canonical form so that the "55" becomes "55()", then you could use LIKE by including the "(" in the pattern. If not, you'll have to either parse the reference value, or write a multi-stage search function that returns the set of matching references (by doing an inclusive search, then discarding the false positive values).

-PatP|||Same as Pat mentioned, Addition is, it will handle records which dont have '(' value.

set nocount on
go

create table #ReferenceTable
(
Reference varchar(200)
)
go
--insert query
insert into #ReferenceTable select '5(a)'
union
select '5(b)'
union
select '5(b)(c)'
union
select '50(a)(b)(c)'
union
select '50(a)(b)(e)'
union
select '55'
union
select '55(a)(f)(g)'
go
--selection query---
declare @.searchvalue varchar(100)
set @.searchvalue='50'
select * from #ReferenceTable where Reference= @.searchvalue or Reference like @.searchvalue+'(%'|||including the "(" in the pattern
where Reference+'(' like '5(%'

incorrect value

Hi,
I'm using crystal reports with firebird database. I installed the firebird ODBC Driver and I
created a DSN connection.
I've in my table a field of type number(15,2) and when I add it in the Crystal report a
incorret value is displayed.
Ex:
Table value field 5,45 and is displayed 0,05 int the crystal report.
The firebird dont have currency type.
number type only.

How I can do for the crystal report to use it number as currencey?

Please, help me.Hi
I dont know about the firebird database.

but please try to insert the value on the filed

like 547 without using comma

regards

Incorrect table Definitions

I am using Transactional replication and from time to time I get an Invalid
column error on the distribution agent for either a stored procedure or view.
When I take a look at the table definition script I see that it is missing
the newest columns that were added. Does anyone else have this problem?
Does anyone know what causes this and how to fix it? The only fix I've found
so far is to drop the table from replication and add it again and produce a
new snapshot and then it seems to see the new columns.
Where are these extra columns? On the Publisher or subscriber?
It sounds like they are on the subscriber, which means someone is changing
the schema there. Make schema changes on the publisher using
sp_repladdcolumn or sp_repldropcolumn.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:B8E2DAEF-629C-4BCD-B87B-33650C14B0B9@.microsoft.com...
> I am using Transactional replication and from time to time I get an
Invalid
> column error on the distribution agent for either a stored procedure or
view.
> When I take a look at the table definition script I see that it is
missing
> the newest columns that were added. Does anyone else have this problem?
> Does anyone know what causes this and how to fix it? The only fix I've
found
> so far is to drop the table from replication and add it again and produce
a
> new snapshot and then it seems to see the new columns.
|||The Extra columns are ones we added with scripts to the publisher database.
But then when we push a snapshot those new columns are not getting scripted.
I'm not sure why the replication script generator would generate the scripts
any differently than when you use Enterprise manager's script generating
tools, but they are not working the same.
"Hilary Cotter" wrote:

> Where are these extra columns? On the Publisher or subscriber?
> It sounds like they are on the subscriber, which means someone is changing
> the schema there. Make schema changes on the publisher using
> sp_repladdcolumn or sp_repldropcolumn.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "jencis10" <jencis10@.discussions.microsoft.com> wrote in message
> news:B8E2DAEF-629C-4BCD-B87B-33650C14B0B9@.microsoft.com...
> Invalid
> view.
> missing
> found
> a
>
>
|||Reply at bottom.
"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:797A9487-C13E-4746-B594-518CA0757521@.microsoft.com...[vbcol=seagreen]
> The Extra columns are ones we added with scripts to the publisher
> database.
> But then when we push a snapshot those new columns are not getting
> scripted.
> I'm not sure why the replication script generator would generate the
> scripts
> any differently than when you use Enterprise manager's script generating
> tools, but they are not working the same.
> "Hilary Cotter" wrote:
Don't you have to mark the replication for reinitialistion prior to creating
the new snapshot? I'm still a beginner on the replication side (well, most
of SQL Server :P), but whenever I make changes to publication properties the
EM dialog always points out that the publication has to be reinitialised, so
I'd assume that for the snapshot agent to pick up changes to the schema the
same thing would need to be done.
Dan
|||Yes, you do have to reinitialize and I am doing that as well. Basically we
drop the article (table) from both the subscription and publication, then I
modify the table structure, add the table back into the publication and
subscription and then reinitialize the subscription. Then I push the new
snapshot and look at the generated files and see that the table was not
scripted with the new columns.
"Daniel Crichton" wrote:

> Reply at bottom.
> "jencis10" <jencis10@.discussions.microsoft.com> wrote in message
> news:797A9487-C13E-4746-B594-518CA0757521@.microsoft.com...
> Don't you have to mark the replication for reinitialistion prior to creating
> the new snapshot? I'm still a beginner on the replication side (well, most
> of SQL Server :P), but whenever I make changes to publication properties the
> EM dialog always points out that the publication has to be reinitialised, so
> I'd assume that for the snapshot agent to pick up changes to the schema the
> same thing would need to be done.
> Dan
>
>
|||"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:5EA098FA-2F35-421C-B426-038EF2AC1158@.microsoft.com...
> Yes, you do have to reinitialize and I am doing that as well. Basically
> we
> drop the article (table) from both the subscription and publication, then
> I
> modify the table structure, add the table back into the publication and
> subscription and then reinitialize the subscription. Then I push the new
> snapshot and look at the generated files and see that the table was not
> scripted with the new columns.
Oh well, that's my involvement finished then - so far I've not needed to
modify any replicated tables, and as everything is still in development I'd
likely be lazy and use EM to rebuild them anyway. Sorry.
Dan

incorrect syntax question

i have a table of data that the user can enter into, the data type is set to "text" and has worked in some test so far, but when i type data in '' marks such as :

'text here'

it gives me an incorrect syntax error, is there a way around this? or is the '' charectors invalid? thanks John

A single quote is a string separator. So if your data has single quotes you might have to excape it with double quotes: example:Select'test''s'

|||Are you using parameters? If not, you should. It will help avoid this kind of problem. Others might suggest doubling the apostrophes, but while it works, that is not the answer.|||

Mikesdotnetting:
Are you using parameters? If not, you should. It will help avoid this kind of problem. Others might suggest doubling the apostrophes, but while it works, that is not the answer.

Agreed. Yes

|||

parameters will stop me getting this error? awesome! thanks for your help John

|||

I have an insert query which gives me a similar error, I cant see why its not working, the error is

A potentially dangerous Request.Form value was detected from the client (ctl00$ContentPlaceHolder1$CommentBox="<b>test text</b>").

My code is :

Connection.Open();
SqlCommand InsertItem = new SqlCommand("INSERT INTO TestTable(Inserted) VALUES ('@.item')", Connection);
InsertItem.Parameters.Add("@.item", SqlDbType.VarChar).Value = Textbox1.Text;
InsertItem.ExecuteNonQuery();
Connection.Close();
I simply tryed to insert the text string <b>test text</b>

Thanks John

|||

You dont need to put quotes if you are using parameterized queries.

SqlCommand InsertItem = new SqlCommand("INSERT INTO TestTable(Inserted) VALUES (@.item)", Connection);

|||It's objecting to the fact that you are trying to input html tags. Set ValidateRequest to false in the @.Page directive:http://www.asp.net/learn/whitepapers/request-validation/|||

thats awesome thanks, that answered every question i could come up with! haha John

Monday, March 26, 2012

Incorrect syntax near the keyword ELSE.

Hi,

I have written a stored procedure to add the records to the table in DB from the report I generate, but the sored procedure gives me this error:

Incorrect syntax near the keyword 'ELSE'.

I am using Sql Server 2005.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
DECLARE @.PKID INT
DECLARE @.TagID INT

SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode

SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID
END

DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT

SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens=@.UniqueUserOpens-UniqueUserOpens,@.UniqueUserMessageClicks=@.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks=@.UniqueUserLinkClicks-UniqueUserLinkClicks,@.TotalLinkClicks=@.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes=@.Unsubscribes-Unsubscribes,@.Bounces=@.Bounces-Bounces,@.SpamComplaints=@.SpamComplaints-SpamComplaints,
@.Cost=@.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost
WHERE ReportDate=@.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

ELSE
SET NOCOUNT ON

INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)

SELECT
@.ReportDate,
@.SiteID,
@.PKID,
@.Sent,
@.Delivered,
@.TotalOpens,
@.UniqueUserOpens,
@.UniqueUserMessageClicks,
@.UniqueUserLinkClicks,
@.TotalLinkClicks,
@.Unsubscribes,
@.Bounces,
@.SpamComplaints,
@.Cost,
@.DataFeedID,
@.TagID

SET NOCOUNT OFF

Hi,

I think you find that the End Statement must be immediately before the Else (IF... BEGIN...END ELSE). Certainly if you move the END to just before the ELSE the SP compiles OK.

Hope this helps,

Paul

|||

How could I miss that.

Thanks a lot!!!

|||

No problems, sometimes these things just need a fresh pair of eyes.

Cheers

|||Try
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
SET NOCOUNT ON -- moved this
DECLARE @.PKID INT
DECLARE @.TagID INT
SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode
SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID
DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT
SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens = @.UniqueUserOpens-UniqueUserOpens,
@.UniqueUserMessageClicks = @.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks = @.UniqueUserLinkClicks-UniqueUserLinkClicks,
@.TotalLinkClicks = @.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes = @.Unsubscribes-Unsubscribes,
@.Bounces = @.Bounces-Bounces,
@.SpamComplaints = @.SpamComplaints-SpamComplaints,
@.Cost = @.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate AND SiteID=@.SiteID AND TagPKID=@.PKID
UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost
WHERE ReportDate=@.ReportDate AND SiteID=@.SiteID AND TagPKID=@.PKID
END
ELSE
INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens,
UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)
VALUES ( -- Should be values
@.ReportDate, @.SiteID, @.PKID, @.Sent, @.Delivered, @.TotalOpens, @.UniqueUserOpens,
@.UniqueUserMessageClicks, @.UniqueUserLinkClicks, @.TotalLinkClicks, @.Unsubscribes,
@.Bounces, @.SpamComplaints, @.Cost, @.DataFeedID, @.TagID)
SET NOCOUNT OFF|||You have an ELSE with no matching IF.

Incorrect syntax near my_stored_procedure

I have a stored procedure (sp) named 'ins_MemberPayment'. This procedure creates a record of a member's payment in the MemberPayments table. The sp looks like this:
___
CREATE PROCEDURE ins_MemberPayment
(@.MemberId VarChar(10),
@.CCNum Char(4),
@.Amount smallmoney)
AS

INSERT INTO MemberPayments
(MemberId, PaymentDate, CCNum, Amount)
VALUES
(@.MemberId, GetDate(), @.CCNum, @.Amount)
GO
--

The ASP.NET page looks like this:
___
Dim UserName As String = txtUserName.Text 'hotrodjimmy73
Dim CreditCard As String = Trim(txtCreditCard.Text) '5464655458776221
Dim Amount As String = "1.00"

Dim cnn As New SqlConnection(Application("SQLConnectionString"))
Dim trans As SqlTransaction
Dim cmd2 As New SqlCommand(dbo() & "ins_MemberPayment", cnn, trans)
cmd.CommandType = CommandType.StoredProcedure

With cmd2.Parameters
.Add("@.MemberID", UserName)
.Add("@.CCNum", Right(CreditCard, 4))
.Add("@.Amount", Convert.ToDecimal(Amount))
End With

cnn.Open()
cmd2.ExecuteNonQuery()
cnn.Close()
--
When I run the page, I get the error:
___
Incorrect syntax near 'ins_MemberPayment'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'ins_MemberPayment'.

Source Error:

Line 261: cmd2.ExecuteNonQuery()
--

Does anybody see the error? I'm blind to it after looking for a couple hours now.In this line

Dim cmd2 As New SqlCommand(dbo() & "ins_MemberPayment", cnn, trans)

what is the purpose of the dbo() function call?

Terri|||What does dbo() evaluate to? Does it translate to "dbo."?

Try this alternative syntax and see if it works.


Dim cm as SqlCommand = New SqlCommand()
cm.Connection= cnn
cm.CommandType= CommandType.StoredProcedure
cm.CommandText= "ins_MemberPayment"
cm.Transaction = trans

|||Oh, sorry. I should have simplified that for the purpose of posting to the forum. The dbo function call simply returns a conditional string: "dbo." if the page is running in it's production environment, and "" if it is running on my local development machine.

It's not relevant for the question at hand.|||I didn't want to "lead the witness" by originally saying that I have a hunch that the problem has something to do with smallmoney and string conversion. But now that I've tested this in Query Analyzer, I'm pretty sure this is the case.

I can get the procedure to work when passing it a value of 12 or 12.00, but as soon as I put single quotes around it, I get the error:

Implicit conversion from data type varchar to smallmoney is not allowed. Use the CONVERT function to run this query.

If I hope to accomplish the conversion in ASP.NET before sending the value to SQL Server, what do I need to convert it too? Int16, Int32, Double?

What's the "RIGHT" data type to convert to?|||The right .NET Framework data type is SQLMoney. (Here's across reference map of SQL Server and .NET Framework data types).

I suggest adding your parameters as such:


With cmd2.Parameters
 .Add("@.MemberID", SqlDbType.Varchar, 10, UserName)
 .Add("@.CCNum", SqlDbType.Char,4,Right(CreditCard, 4))
 .Add("@.Amount", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(Amount))
End With

Terri|||Using your code I get:

Value of type 'System.Data.SqlTypes.SqlMoney' cannot be converted to 'String'.|||Going back to your originally supplied code, change this:

cmd.CommandType = CommandType.StoredProcedure

to this:

cmd2.CommandType = CommandType.StoredProcedure

Terri|||Oops. Thanks for the fresh pair of eyes. That did one good thing for me; It allowed me to get more accurate errors returned.

Now it works as:

.Add("@.Amount", SqlTypes.SqlMoney.Parse(Amount))

But not as:

.Add("@.Amount", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(Amount))

Hmm. Strange. Oh, well. It works for my purposes. THANKS!!|||I normally don't add my parameters that way, so I am sure I provided you with syntactical errors :-(

But I am glad you got it working now!

Terri

Friday, March 23, 2012

Incorrect Syntax Near '-'

Hi,
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
JackHi,
Put the server name in sqare brackets [].
[SQL-SERVER2]
Thanks
Hari
SQL Server MVP
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SP that running every 20 min, the SP will update some tables from
> DB on another SQL server.
> ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
> SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
> server name in SP, but during test SP, i got error Incorrect Syntax Near
> '-', seems i can't use '-' when refering server, is it normal?
> how could i correct this issue other than rename server?
> Appreicate your help.
> Jack
>|||Brilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have a SP that running every 20 min, the SP will update some tables
from
> > DB on another SQL server.
> >
> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
> > server name in SP, but during test SP, i got error Incorrect Syntax Near
> > '-', seems i can't use '-' when refering server, is it normal?
> >
> > how could i correct this issue other than rename server?
> >
> > Appreicate your help.
> >
> > Jack
> >
> >
>|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Put the server name in sqare brackets [].
>> [SQL-SERVER2]
>> Thanks
>> Hari
>> SQL Server MVP
>> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
>> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a SP that running every 20 min, the SP will update some tables
> from
>> > DB on another SQL server.
>> >
>> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
>> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced
>> > the
>> > server name in SP, but during test SP, i got error Incorrect Syntax
>> > Near
>> > '-', seems i can't use '-' when refering server, is it normal?
>> >
>> > how could i correct this issue other than rename server?
>> >
>> > Appreicate your help.
>> >
>> > Jack
>> >
>> >
>>
>|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/articles/DatabaseNamingConventions.aspx
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Put the server name in sqare brackets [].
>> [SQL-SERVER2]
>> Thanks
>> Hari
>> SQL Server MVP
>> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
>> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a SP that running every 20 min, the SP will update some tables
> from
>> > DB on another SQL server.
>> >
>> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
>> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced
>> > the
>> > server name in SP, but during test SP, i got error Incorrect Syntax
>> > Near
>> > '-', seems i can't use '-' when refering server, is it normal?
>> >
>> > how could i correct this issue other than rename server?
>> >
>> > Appreicate your help.
>> >
>> > Jack
>> >
>> >
>>
>

Incorrect Syntax Near '-'

Incorrect Syntax Error

hi. i am relatively new to sql. i am wondering how to resolve my "incorrect syntax error" on an IP Address string i am trying to add.
the table has IPAddress as data type "text" and length 16 (which won't let me change the length). It seems that sql is looking for a data type with no more than one decimal (money, float, etc). How do i resolve this? thanks in advance. PeterYou need to change the datatype of the column. This can be done using SQL Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_5rg2.asp) or the ALTER TABLE ALTER COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) command from SQL Query Analyzer (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp).

-PatP|||Hi. THanks for the reply. Well, i am new to sql but not that new. i have tried changing the data type a number of times. I tried varchar, text, nvarchar, and a few others. I did this in the designer view of the SQL EM. I still get the same response... "incorrect syntax error". For numerical data types that expect one decimal place, i understand this, but i don't get why i am getting this error for string-like data types such as var char, etc. more suggestions appreciated.

Originally posted by Pat Phelan
You need to change the datatype of the column. This can be done using SQL Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_5rg2.asp) or the ALTER TABLE ALTER COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) command from SQL Query Analyzer (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp).

-PatP|||This requires the "digital ball pein" to fix it, because you can't change the type of a TEXT, NTEXT, or IMAGE column. The following script demonstrates:CREATE TABLE dbo.foo2 (
fooId INT IDENTITY
, thingie TEXT
)
GO

ALTER TABLE dbo.foo2
ALTER COLUMN thingie VARCHAR(20) -- Server: Msg 4928, Level 16, State 1, Line 1
-- Cannot alter column 'thingie' because it is 'text'.
GO

CREATE TABLE dbo.foo3 (
fooId INT IDENTITY
, thingie VARCHAR(20)
)
GO

INSERT INTO dbo.foo3 (thingie)
SELECT thingie
FROM dbo.foo2The short answer is that you must build a new table, and copy the data from the old table into the new table. Then you should be good to go.

-PatP|||hi. thanks again. i droped the table as you suggested and redesigned it
with the IPAddress row as varchar(30) instead of text and ran the insert statement and sql is still complaining about anything that comes after the first decimal point in the IP address. for and inserted IP of 121.111.12.1 sql complains that there is an "incorrect syntax near '.12'".
i am really stumped. if you have other suggestions, much appreciated.|||You never showed us what your insert statment looked like. I ran this and it worked fine.

insert into foo3 ([thingie]) values('121.111.12.1')|||Here's the whole thing(ie) ;-)

CREATE TABLE dbo.foo3 (
fooId INT IDENTITY
, thingie VARCHAR(20)
)
GO
insert into foo3 ([thingie]) values('121.111.12.1')
GO
SELECT [fooId], [thingie] FROM [TESTDB].[dbo].[foo3]
GO|||It would help if you posted the actual SQL command it's performing.

From what you have said it looks like you aren't quoting the IP string.

Since it's a VARCHAR field, you should insert/update the IP with single quotes around it.

Sloppy oracle-centric SQL warning!

insert into iplist using select 'SERVERNAME','121.111.12.1' from other_table

Wednesday, March 21, 2012

Incorrect Results with t-sql Query in SQL Server 2005

I'm seeing some change in behavior for a query in SQL Server 2005 (compared to behavior in SQL Server 2000). The query is as follows:
create table #projects (projectid int) insert into #projects select projectid from tblprojects where istemplate = 0 and projecttemplateid = 365

Select distinct tblProjects.ProjectID
from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
select projectid
from tblMilestones
where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
and enabled = 1 )
This is dynamic SQL generated by the application when a user requests a report with variable parameters. It works fine in SQL Server 2000. It outputs 47 records which is correct.

In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).

If I modify the query even the slightest bit by:
1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
and enabled = 1 )"
To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) )
and enabled = 1 "

2) Changing " Select distinct tblProjects.ProjectID"
To " Select distinct tblProjects.ProjectID+''"

3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table

4) Adding: OPTION (FORCE ORDER)

5) OR completely fixing the query (remove redundant loops, etc)

...it works fine (outputs 47 records). It also works if I created new tables (eg. tMilestones instead of tblMilestones) and inserted about 10 records into each and ran the query referencing these new tables.

I reindexed the tables, updated stats, updated usage, ran DBCC FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave the way it does in SQL Server 2000 without modifying the query/adding the query hint.

Have you come across this? Any ideas on what might be causing the "TOP" operation. (Somewhat resembles the bug mentioned in this article: http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server 2005 yet?).

I will appreciate any new insights you might have on this issue.
Thanks much,
Smitha

P.S. Results of Showplan Text:

StmtText

SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText

Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
select tblMilestones.projectid from tblMilestones
where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006' and '4/22/2006' )
and tblMilestones.enabled = 1 )

(1 row(s) affected)

StmtText

|--Stream Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
| |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo].[tblMilestones].[Enabled],0)=(1)))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1014], [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
| | |--Merge Join(Inner Join, MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014]), RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] = [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014] = [Uniq1014]))
| | | |--Sort(ORDER BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC, [Uniq1014] ASC))
| | | | |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDate]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] >= '2006-04-15 00:00:00.000' AND [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <= '2006-04-22 00:00:00.000') ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691)) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProjects_1]), SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join)
|--Table Scan(OBJECT:([tempdb].[dbo].[#projects]), WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempdb].[dbo].[#projects].[projectid]))
|--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)

(15 row(s) affected)

StmtText
--
SET STATISTICS PROFILE OFF

(1 row(s) affected)

It would be great if you reported this at

http://lab.msdn.microsoft.com/productfeedback. If you do,

you are more likely to get the attention of the right

people, and you might find out if this is a known bug, and

if so, what the status is.

Steve Kass

Drew University

Smitha_Expesite@.discussions.microsoft.com wrote:

> I'm seeing some change in behavior for a query in SQL Server 2005

> (compared to behavior in SQL Server 2000). The query is as follows:

> create table #projects (projectid int) insert into #projects select

> projectid from tblprojects where istemplate = 0 and projecttemplateid =

> 365

> Select distinct tblProjects.ProjectID

> from tblProjects WITH (NOLOCK)

> inner join #projects on #projects.projectid = tblprojects.projectid

> Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID =

> tblMilestones.ProjectID

> and tblProjects.projectID in (

> select projectid

> from tblMilestones b

> where (parent = 683691 AND PrimaryDate between

> '4/15/2006' and '4/22/2006' )

> and enabled = 1 )

> This is dynamic SQL generated by the application when a user requests a

> report with variable parameters. It works fine in SQL Server 2000. It

> outputs 47 records which is correct.

> In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as

> a TOP operator and outputs just 1 record. (Results of Showplan Text at

> the end of this post).

> If I modify the query even the slightest bit by:

> 1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006'

> and '4/22/2006' )

> and enabled = 1 )"

> To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and

> '4/22/2006' ) )

> and enabled = 1 "

> 2) Changing " Select distinct tblProjects.ProjectID"

> To " Select distinct tblProjects.ProjectID+''"

> 3) Removing the Distinct keyword, storing into a Temp table, then

> performing a distinct on the temp table

> 4) Adding: OPTION (FORCE ORDER)

> 5) OR completely fixing the query (remove redundant loops, etc)

> ...it works fine (outputs 47 records). It also works if I created new

> tables (eg. tMilestones instead of tblMilestones) and inserted about 10

> records into each and ran the query referencing these new tables.

> I reindexed the tables, updated stats, updated usage, ran DBCC

> FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave

> the way it does in SQL Server 2000 without modifying the query/adding

> the query hint.

> Have you come across this? Any ideas on what might be causing the "TOP"

> operation. (Somewhat resembles the bug mentioned in this article:

> http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently

> fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server

> 2005 yet?).

> I will appreciate any new insights you might have on this issue.

> Thanks much,

> Smitha

> P.S. Results of Showplan Text:

> StmtText

> SET STATISTICS PROFILE ON

> (1 row(s) affected)

> StmtText

> Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)

> inner join #projects on #projects.projectid = tblprojects.projectid

> Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID =

> tblMilestones.ProjectID

> and tblProjects.projectID in (

> select tblMilestones.projectid from tblMilestones

> where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006'

> and '4/22/2006' )

> and tblMilestones.enabled = 1 )

> (1 row(s) affected)

> StmtText

> |--Stream

> Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectI

> D]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))

> |--Nested Loops(Inner Join, OUTER

> REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))

> |--Nested Loops(Inner Join, OUTER

> REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))

> |

> |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo]

> .[tblMilestones].[Enabled],0)=(1)))

> | | |--Nested Loops(Inner Join, OUTER

> REFERENCES:([Uniq1014],

> [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)

> | | |--Merge Join(Inner Join,

> MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID],

> [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID],

> [Uniq1014]),

> RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] =

> [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND

> [Uniq1014] = [Uniq1014]))

> | | | |--Sort(ORDER

> BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC,

> [Uniq1014] ASC))

> | | | | |--Index

> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDa

> te]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate]

>>= '2006-04-15 00:00:00.000' AND

> [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <=

> '2006-04-22 00:00:00.000') ORDERED FORWARD)

> | | | |--Index

> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]

> ),

> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691))

> ORDERED FORWARD)

> | | |--Clustered Index

> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid])

> ,

> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[Expesi

> teProductionCopy].[dbo].[tblMilestones].[ProjectID] AND

> [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)

> | |--Index

> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProject

> s_1]),

> SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[Expesite

> ProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)

> |--Top(TOP EXPRESSION:((1)))

> |--Nested Loops(Inner Join)

> |--Table Scan(OBJECT:([tempdb].[dbo].[#projects]),

> WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempd

> b].[dbo].[#projects].[projectid]))

> |--Clustered Index

> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid])

> ,

> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[Expesi

> teProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)

> (15 row(s) affected)

> StmtText

> --

> SET STATISTICS PROFILE OFF

> (1 row(s) affected)

>|||OK...I will report it to the Feedback folks. Thanks!|||

The Hotfix that you mentioned (http://www.kbalertz.com/Feedback_910392.aspx) is fixed in SQL Server 2005 RTM, so you may have discovered a new bug in SQL. If so, I would like to fix it. However, I do not have enough information to do that yet.

The first thing I would recommend is to send me a clone of your database. This is a version with no data, but with all the statistics intact (as though the tables were large), so we will get the same plans.

Please contact me for help making the clone.

Marc Friedman

marcfr@.removethispart.microsoft.com

|||Sorry about not getting back earlier...the issue has been fixed - not sure how or why. I couldn't get the approval to send you a clone of our database. Thanks for your input.

Incorrect Results with t-sql Query in SQL Server 2005

In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).

2) Changing " Select distinct tblProjects.ProjectID"
To " Select distinct tblProjects.ProjectID+''"

3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table

4) Adding: OPTION (FORCE ORDER)

5) OR completely fixing the query (remove redundant loops, etc)

I will appreciate any new insights you might have on this issue.
Thanks much,
Smitha

P.S. Results of Showplan Text:

StmtText

SET STATISTICS PROFILE ON

(1 row(s) affected)

(15 row(s) affected)

StmtText
--
SET STATISTICS PROFILE OFF

(1 row(s) affected)

It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is. Steve Kass Drew University Smitha_Expesite@.discussions.microsoft.com wrote:
> I'm seeing some change in behavior for a query in SQL Server 2005
> (compared to behavior in SQL Server 2000). The query is as follows:
>
> create table #projects (projectid int) insert into #projects select
> projectid from tblprojects where istemplate = 0 and projecttemplateid =
> 365
>
> Select distinct tblProjects.ProjectID
> from tblProjects WITH (NOLOCK)
> inner join #projects on #projects.projectid = tblprojects.projectid
> Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID =
> tblMilestones.ProjectID
> and tblProjects.projectID in (
> select projectid
> from tblMilestones b
> where (parent = 683691 AND PrimaryDate between
> '4/15/2006' and '4/22/2006' )
> and enabled = 1 )
>
> This is dynamic SQL generated by the application when a user requests a
> report with variable parameters. It works fine in SQL Server 2000. It
> outputs 47 records which is correct.
>
> In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as
> a TOP operator and outputs just 1 record. (Results of Showplan Text at
> the end of this post).
>
> If I modify the query even the slightest bit by:
> 1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006'
> and '4/22/2006' )
> and enabled = 1 )"
> To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and
> '4/22/2006' ) )
> and enabled = 1 "
>
> 2) Changing " Select distinct tblProjects.ProjectID"
> To " Select distinct tblProjects.ProjectID+''"
>
> 3) Removing the Distinct keyword, storing into a Temp table, then
> performing a distinct on the temp table
>
> 4) Adding: OPTION (FORCE ORDER)
>
> 5) OR completely fixing the query (remove redundant loops, etc)
>
> ...it works fine (outputs 47 records). It also works if I created new
> tables (eg. tMilestones instead of tblMilestones) and inserted about 10
> records into each and ran the query referencing these new tables.
>
> I reindexed the tables, updated stats, updated usage, ran DBCC
> FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave
> the way it does in SQL Server 2000 without modifying the query/adding
> the query hint.
>
> Have you come across this? Any ideas on what might be causing the "TOP"
> operation. (Somewhat resembles the bug mentioned in this article:
> http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently
> fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server
> 2005 yet?).
>
> I will appreciate any new insights you might have on this issue.
> Thanks much,
> Smitha
>
>
> P.S. Results of Showplan Text:
>
> StmtText
>
> SET STATISTICS PROFILE ON
>
> (1 row(s) affected)
>
> StmtText
>
>
>
>
>
>
>
> Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)
> inner join #projects on #projects.projectid = tblprojects.projectid
> Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID =
> tblMilestones.ProjectID
> and tblProjects.projectID in (
> select tblMilestones.projectid from tblMilestones
> where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006'
> and '4/22/2006' )
> and tblMilestones.enabled = 1 )
>
> (1 row(s) affected)
>
> StmtText
>
>
>
>
>
>
> |--Stream
> Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectI
> D]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
> |
> |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo]
> .[tblMilestones].[Enabled],0)=(1)))
> | | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([Uniq1014],
> [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
> | | |--Merge Join(Inner Join,
> MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID],
> [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID],
> [Uniq1014]),
> RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] =
> [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND
> [Uniq1014] = [Uniq1014]))
> | | | |--Sort(ORDER
> BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC,
> [Uniq1014] ASC))
> | | | | |--Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDa
> te]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate]
> >>= '2006-04-15 00:00:00.000' AND
>
> [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <=
> '2006-04-22 00:00:00.000') ORDERED FORWARD)
> | | | |--Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]
> ),
> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691))
> ORDERED FORWARD)
> | | |--Clustered Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid])
> ,
> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[Expesi
> teProductionCopy].[dbo].[tblMilestones].[ProjectID] AND
> [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
> | |--Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProject
> s_1]),
> SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[Expesite
> ProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
> |--Top(TOP EXPRESSION:((1)))
> |--Nested Loops(Inner Join)
> |--Table Scan(OBJECT:([tempdb].[dbo].[#projects]),
> WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempd
> b].[dbo].[#projects].[projectid]))
> |--Clustered Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid])
> ,
> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[Expesi
> teProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
>
> (15 row(s) affected)
>
> StmtText
> --
> SET STATISTICS PROFILE OFF
>
> (1 row(s) affected)
>
>
>
>
>
>|||OK...I will report it to the Feedback folks. Thanks!|||

Please contact me for help making the clone.

Marc Friedman

marcfr@.removethispart.microsoft.com

|||Sorry about not getting back earlier...the issue has been fixed - not sure how or why. I couldn't get the approval to send you a clone of our database. Thanks for your input.sql

Incorrect Results with t-sql Query in SQL Server 2005

In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).

2) Changing " Select distinct tblProjects.ProjectID"
To " Select distinct tblProjects.ProjectID+''"

3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table

4) Adding: OPTION (FORCE ORDER)

5) OR completely fixing the query (remove redundant loops, etc)

I will appreciate any new insights you might have on this issue.
Thanks much,
Smitha

P.S. Results of Showplan Text:

StmtText

SET STATISTICS PROFILE ON

(1 row(s) affected)

(15 row(s) affected)

StmtText
--
SET STATISTICS PROFILE OFF

(1 row(s) affected)

Please contact me for help making the clone.

Marc Friedman

marcfr@.removethispart.microsoft.com

|||Sorry about not getting back earlier...the issue has been fixed - not sure how or why. I couldn't get the approval to send you a clone of our database. Thanks for your input.

Incorrect results if no TOP clause

I have a problem with a query. When I omit the TOP clause I get more
rows returned than I expect (12 rows out of a 119 in the table), but
when I include TOP I get 5 rows from the same data with the otherwise
unchanged table.
Thing is, the query where I include TOP 1000 is the correct result.
I'm on SQL Server 2000, select @.@.version -
"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 1) "
I've cut the query down quite a bit, the table
create table Test (
CDMA_PDSN63 FLOAT,
AVE__CORE_46_CountINTEGER,
AVE__CORE_46_Sum FLOAT);
The query is
SELECT * FROM (
SELECT
-- TOP 1000 -- comment this in to get correct results
D.CDMA_PDSN63, NM.AVE__CORE_46x AVE__CORE_46
FROM (SELECT DISTINCT CDMA_PDSN63
FROM(SELECT CDMA_PDSN63 FROM Test ) AS dims
) AS D,
(SELECT CDMA_PDSN63,
CASE when (Sum( AVE__CORE_46_Count))=0 then NULL else
SUM(AVE__CORE_46_Sum) / SUM( AVE__CORE_46_Count) end AS AVE__CORE_46x
FROM Test GROUP BY CDMA_PDSN63) AS NM
WHERE D.CDMA_PDSN63*=NM.CDMA_PDSN63
) as MainQuery
WHERE ROUND(MainQuery.AVE__CORE_46,0) >= 120
--AND MainQuery.AVE__CORE_46 IS NOT NULL - Note 1
--ORDER BY CDMA_PDSN63 - Note 2
Note 1: I tried adding this but it makes not difference
Note 2: this was in the original query, it makes sense to have it but
again it makes no difference, I've used in on the inner and outer
select, no change
I've also tried TOP 1000 in the outer select again no difference.
Changing the outer join to a regular join fixes the problem, but I
need the outer join in the original query. (In the original query
there are many more sub-selects and several more outer join clauses to
put them back together.)
If I use small values for TOP n (e.g. 1, 2, ... < 10) the results are
even stranger, I get a subset of the expected rows, and not the same
number as n.
I tried setting a rowcount as well, no difference.
I've seen several other queries on this newsgroup which talk about
similar problems but the threads have never been concluded with a
clear answer.
Any ideas? Thanks
allan
> "Microsoft SQL Server 2000 - 8.00.194
You're on RTM! Install Service Pack 3a, right away, please! There are many
query processor bugs that have been fixed since the product was released.
Thanks for the CREATE TABLE, but once you've done that, you're going to have
to provide us with enough sample data to reproduce your problem (as well as
tell us which rows you were expecting in the result set!). Otherwise, it's
impossible for us to determine exactly what's happening, why it doesn't meet
your criteria, and test our suggestions on fixing it...
See http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
|||Allan,
After you install service pack 3a as Aaron suggested, I suggest you
consider two other things:
* Rwrite the query using ANSI outer join syntax (... left outer join on
...), which, unlike *=, is always unambiguous
* Consider a different data type than FLOAT for data that must be
compared with the = operator. Because FLOAT values are approximate, you
cannot count on tests of equality to be reliable.
Steve Kass
Drew University
Allan Kelly wrote:

>I have a problem with a query. When I omit the TOP clause I get more
>rows returned than I expect (12 rows out of a 119 in the table), but
>when I include TOP I get 5 rows from the same data with the otherwise
>unchanged table.
>Thing is, the query where I include TOP 1000 is the correct result.
>I'm on SQL Server 2000, select @.@.version -
>"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
>00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer
>Edition on Windows NT 5.1 (Build 2600: Service Pack 1) "
>I've cut the query down quite a bit, the table
>create table Test (
>CDMA_PDSN63 FLOAT,
>AVE__CORE_46_CountINTEGER,
>AVE__CORE_46_Sum FLOAT);
>The query is
>SELECT * FROM (
>SELECT
>-- TOP 1000 -- comment this in to get correct results
>D.CDMA_PDSN63, NM.AVE__CORE_46x AVE__CORE_46
>FROM (SELECT DISTINCT CDMA_PDSN63
>FROM(SELECT CDMA_PDSN63 FROM Test ) AS dims
>) AS D,
>(SELECT CDMA_PDSN63,
>CASE when (Sum( AVE__CORE_46_Count))=0 then NULL else
>SUM(AVE__CORE_46_Sum) / SUM( AVE__CORE_46_Count) end AS AVE__CORE_46x
>FROM Test GROUP BY CDMA_PDSN63) AS NM
>WHERE D.CDMA_PDSN63*=NM.CDMA_PDSN63
>) as MainQuery
>WHERE ROUND(MainQuery.AVE__CORE_46,0) >= 120
>--AND MainQuery.AVE__CORE_46 IS NOT NULL - Note 1
>--ORDER BY CDMA_PDSN63 - Note 2
>Note 1: I tried adding this but it makes not difference
>Note 2: this was in the original query, it makes sense to have it but
>again it makes no difference, I've used in on the inner and outer
>select, no change
>I've also tried TOP 1000 in the outer select again no difference.
>Changing the outer join to a regular join fixes the problem, but I
>need the outer join in the original query. (In the original query
>there are many more sub-selects and several more outer join clauses to
>put them back together.)
>If I use small values for TOP n (e.g. 1, 2, ... < 10) the results are
>even stranger, I get a subset of the expected rows, and not the same
>number as n.
>I tried setting a rowcount as well, no difference.
>I've seen several other queries on this newsgroup which talk about
>similar problems but the threads have never been concluded with a
>clear answer.
>Any ideas? Thanks
>allan
>
|||Steve Kass <skass@.drew.edu> wrote in message news:<#Tj3pA0dEHA.1604@.TK2MSFTNGP11.phx.gbl>...
> Allan,
> After you install service pack 3a as Aaron suggested, I suggest you
> consider two other things:
> * Rwrite the query using ANSI outer join syntax (... left outer join on
> ...), which, unlike *=, is always unambiguous
> * Consider a different data type than FLOAT for data that must be
> compared with the = operator. Because FLOAT values are approximate, you
> cannot count on tests of equality to be reliable.
>
Thanks Aaron, Steve,
I rewrote the SQL using ANSI outer join syntax and that fixes the
problem. Great!
Thanks for the reminder to look into the service pack, my code need to
run against MSDE in the final product so I need to check out the
service pack situation there.
allan

Friday, March 30, 2012

increase size of varchar column.. table being replicated..

increase Ram?

increase CPU time

Wednesday, March 28, 2012

Increase columns width of merge replicated table

increase aggregations (design storage) programmatically (was "Please help thank

Incorrect values in RestoreHistory table

Incorrect Values

incorrect value

Incorrect table Definitions

incorrect syntax question

A potentially dangerous Request.Form value was detected from the client (ctl00$ContentPlaceHolder1$CommentBox="<b>test text</b>").

Monday, March 26, 2012

Incorrect syntax near the keyword ELSE.

Incorrect syntax near my_stored_procedure

Friday, March 23, 2012

Incorrect Syntax Near '-'

Incorrect Syntax Near '-'

Incorrect Syntax Near '-'

Incorrect Syntax Error

Wednesday, March 21, 2012

Incorrect Results with t-sql Query in SQL Server 2005

Incorrect Results with t-sql Query in SQL Server 2005

Incorrect Results with t-sql Query in SQL Server 2005

Incorrect results if no TOP clause

sqlserver2000 date

Blog Archive

About Me