sql error: statement

Showing posts with label statement. Show all posts

Sunday, March 11, 2012

"select" statement in a function

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999
Begin
select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN
End
ELSE
if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
Begin
select MIN(lngPersonID) AS PersonID from Persons
where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName
and strMiddleName=@.MiddleName and strLastName=@.LastName
and strNameSuffix=@.NameSuffix and lngSSN=@.SSN
End
ELSE
if @.LastName is not null
BEGIN
select MIN(lngPersonID) as PersonID from Persons
where strLastName=@.LastName
and lngSSN = @.SSN
END
END
return (@.personID)
END

I m having problem with the "Select" function
the error I m getting is
Select statements included within a function cannot return data to a client (error 444)

Cann I use "select" statement in the function? If not what is the alternative?

I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.

ThanksWhy isn't this a stored procedure?|||Brett

It can be, but I guess you answered many of my questions
I still have this question left in me.

Can a stored procedure return a value??
As far as my knowledge goes it returns with a
"select" statement. Is it true?? Is there any way to check the returned value.
I am very much confused with this concept.

Can I make all functions in a sybase database as stored procedures in SQL??

If it doesnot return then I have to make it a function for sure.

Can you please answer these questions?

Thanks|||USE Northwind
GO

CREATE PROC MySproc99 @.HoursToMargarittaville Decimal(15,4) OUTPUT
AS
BEGIN
DECLARE @.Weekend datetime
SELECT @.Weekend = 'Jan 22 2004 17:00:00'
SELECT @.HoursToMargarittaville = DATEDIFF(mi, GetDate(), @.Weekend)/60.00
Return 0
END
GO

DECLARE @.x decimal(15,4), @.rc int
EXEC @.rc = MySproc99 @.x OUTPUT
SELECT @.x, @.rc
GO

DROP PROC mySproc99
GO|||You can use a select function for assignment, but you cannot return the results of a select statement.

Here is your function rewritten. I also cleaned up some of the unnecessary BEGIN/END pairs and used a COALESCE function rather than mutiple IS NULL checks.

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF COALESCE(@.LastName, @.FirstName, @.MiddleName, @.NamePrefix, @.NameSuffix) is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END

Be aware that I think your logic will fail when comparing NULL values, because NULL values cannot be compared to eachother. For example:
-----------
declare @.NULL1 varchar
declare @.NULL2 varchar
if @.NULL1 = @.NULL2 print 'Success' else print 'Failure'
-----------|||Oops. Looked at your code again and I see that you don't allow nulls for the comparison, so COALESCE is not appropriate. Use this instead:

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END|||Yo, blind dude...

You don't think s/he would be better of with a sproc?

especially in light of a (sounds like massive) sybase to sql server conversion?|||Honestly, (MOO of course) I would use a function here, because I imagine this logic could be called by several different procedures. I've never liked using stored procedures that return scalar OUTPUT. I think the implementation is clunky, so since UDFs were introduced they have been my preferred method.|||MOO indeed...

Want to show me what the udf call would look like?|||see if you can not re-code your function to something like

Create function name (@.param...)
Returns Table

AS

Return Seturn Select ... Your SQL Goes here...

See also BOL, In-Line Functions

The only limitation is that you can have a single select sql phrase inside your function.|||I would call the UDF like this: MOO! MOOOOO! MOO MOO MOO MOO MOO!

Whether it would come when I call, I don't know...

"select TOP" with parameter...

Hi there.
Does anyone know of a way to get the following statement to work without
using dynamic SQL?.....
I'm trying to get a "select TOP" to work while passing in a parameter as 'N'
for 'select the first N rows...'
declare @.NumberofRecords integer
select @.NumberofRecords = 5
select top @.NumberofRecords
from MyTable
Any info would be appreciated!len
Lookup SET ROWCOUNT in the BOL
"len" <len@.discussions.microsoft.com> wrote in message
news:3643D907-3483-4B3D-9F9A-B7D45EABF6AB@.microsoft.com...
> Hi there.
> Does anyone know of a way to get the following statement to work without
> using dynamic SQL?.....
> I'm trying to get a "select TOP" to work while passing in a parameter as
> 'N'
> for 'select the first N rows...'
> declare @.NumberofRecords integer
> select @.NumberofRecords = 5
> select top @.NumberofRecords
> from MyTable
> Any info would be appreciated!|||You are not selecting anything so maybe this will work
select top @.NumberofRecords myField1, myField2, etc.
from MyTable
if that still fails try
EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
HTH,
Gerard|||Len,
you can't use a parameter with TOP in SQL 2000. In 2005 it is possible
see this article for more details:
http://support.microsoft.com/defaul...kb;en-us;891605
In 2000 and 7.0 you can use the SET ROWCOUNT option to achieve what you
want.
Try something like
Declare @.num int
SET @.num = 8
SET ROWCOUNT @.num
select * from myTable
Order by columnname
Markus|||I just did a quick check in Qry Analyzer and
EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
works, provided offcourse that @.NumberofRecords is a string
Gerard|||"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1130325402.186088.177800@.g14g2000cwa.googlegroups.com...
>I just did a quick check in Qry Analyzer and
> EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
> works, provided offcourse that @.NumberofRecords is a string
It does work, but that's not a parameterised procedure, it's dynamic SQL.
Plenty of reasons to try and avoid using that if possible.
Dan|||> select top @.NumberofRecords myField1, myField2, etc.
> from MyTable
This will fail because TOP does not take parameters in SQL Server 2000!

> EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
This will fail because (a) @.NumberOfRecords can't implicitly be concatenated
into a string and (b) you didn't leave a space after top, so it will come
out with a syntax error like this:
select top5 myColumn, myColumn2, ...
Please see http://www.sommarskog.se/dynamic_sql.html to understand why
dynamic SQL is not always the best knee-jerk reaction to a problem...|||Yes yes yes,
I know about the limitations/dangers/restrictions with/of dynamic sql,
I was a bit quick of the gun maybe.
And thanks for checking on the punctuation, e.g. the space after the
top.
On your point b)

> works, provided offcourse that @.NumberofRecords is a string
which would, I forgot to mention, require an additional variable and a
CONVERT if the initial variable is an int.

Thursday, March 8, 2012

"Resource is low, some results are dropped"

I've been getting this message in a dialog box intermittently. Most recently
when trying to execute 'select @.@.trancount' after a statement attempted to
insert 100 rows into a table with only 10 rather small, varchar(256) columns
Everything that turned up on a Google search basically said throw more
hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
1039304, available memory = 333208(K) and System Cache = 507972 along with
2% CPU so I don't believe that it's a memory issue or more hardware will
solve the underlying issue.
Does anyone have an answer for this other than throw more hardware at it?
Where do you see this error? Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256) columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
> 1039304, available memory = 333208(K) and System Cache = 507972 along with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Hi,
Looks like you are executing the query in GRID result pane in query
analyzer. Could you change the mode to Text and try.
How to change:-
1. In query analyzer
2. Go to Query menu
3. Select "Result in Text"
4. Execute the query.
Thanks
Hari
SQL Server MVP
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most
> recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256)
> columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a
> 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K)
> of
> 1039304, available memory = 333208(K) and System Cache = 507972 along
> with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Or use the shortcuts CTRL + T for text mode, CTRL + D for grid
http://sqlservercode.blogspot.com/
"Hari Prasad" wrote:

> Hi,
> Looks like you are executing the query in GRID result pane in query
> analyzer. Could you change the mode to Text and try.
>
> How to change:-
> 1. In query analyzer
> 2. Go to Query menu
> 3. Select "Result in Text"
> 4. Execute the query.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
>
>
|||re: Resource is low, some results are dropped
There are a few different reasons this could happen.
1) Not patched up! Make sure you have the latest patches from Microsoft.
Service Packs
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
Security Patches
You can check Technet for the latest patches...
http://www.microsoft.com/technet/security/current.aspx
2) How, and how many, results are returned.
Query Analyzer returns results in one of two ways when you execute SQL Statements (Text or Grid) It can execute and return the most complicated queries from huge databases with large result sets.
However, it has a problem returning multiple results to grid. Each grid requires a certain amount of resources, and if you execute a large number of queries, it eventually will run out memory and drop some results.
I was looping throogh sysobjects and syscolumns, executing a select statement on every column for every table. (Code GEnerator) It was trying to open a grid for every column in my database. Not that Pubs or Northwind would cause it, but my production da
tabase had a lot more objects. My guess is that this requires too many resources to complete.
If I changed the output for executing the queries to text mode, or I printed the information to the screen instead of executing t-SQL I did not get the error.
Try running it in text mode. Menu Bar - Query - Results in grid or Results in Text.
The keyboard shortcuts are Ctrl+D for Grid and Ctrl+T for Text.
Mike Pittser
Database Architect
sql2k5dba@.yahoo.com
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

"Prepare" in SQL Server through ODBC/ADO

Hi,
When using an interface like ADO or ODBC, there is a call named
"Prepare" which supposedly sent the statement to the server which in
turn should parse it, save the query plan and return a handle to the
client so subquent calls just refer to this handle/id and get faster
execution in batches... is this correct?
Well, my question is, when this Prepare or Parse is done, is there any
physical check being performed by SQL Server ? I mean, does it check
the column names, table existance, etc, or does it just check pure
syntax ?According to the ODBC 3.0 SDK, a SQLPrepare call to an ODBC driver sends the
statement to the data source for preparation and stores the returned access
plan identifier for later execution; or returns any errors including
SQLSTATE 42000 (Syntax error or access violation) and SQLSTATE 42S02 (Base
table or view not found). It also notes that some drivers do not return
errors after SQLPrepare is called, but rather when the statement is executed
later. So "SQLPrepare might appear to have succeeded when in fact it has
failed."
It would seem that you can't rely on a SQLPrepare call to return errors,
since not all drivers support that functionality via SQLPrepare.
Thanks,
Mike C.
<craigkenisston@.hotmail.com> wrote in message
news:1109726936.969442.224550@.o13g2000cwo.googlegroups.com...
> Hi,
> When using an interface like ADO or ODBC, there is a call named
> "Prepare" which supposedly sent the statement to the server which in
> turn should parse it, save the query plan and return a handle to the
> client so subquent calls just refer to this handle/id and get faster
> execution in batches... is this correct?
> Well, my question is, when this Prepare or Parse is done, is there any
> physical check being performed by SQL Server ? I mean, does it check
> the column names, table existance, etc, or does it just check pure
> syntax ?
>|||Thank you !!

Saturday, February 25, 2012

"Macro" statement

Is it possible to write a macro statement using Transact-SQL?
Imagine that we have a table named tblA and fields with the almost
same name, for example Field01, Field02,...Field20. (I have named
fields on that way for the better explanation).
Now, suppose that we want to do almost the same update on all of the
fields:
UPDATE tblA
SET Field01 = 100000
UPDATE tblA
SET Field02 = 100000
and so on...
(we must write 20 identical statements). This example is very simple
(please, forget the solution with one statement because it is clear!).
I wrote a simple example because of my next explanation and question.
In some other languages it is not necessarily to write 20 almost
identical statements. I can write something like this:
FOR i: = 1 TO 20
cTemp := CHAR2(i)
REPLACE Field&cTemp with 10000
NEXT i
-- cTemp (using CHAR2 convert function) have a character values: '01',
'02', '03'... etc.
As you can see, with every step through the loop I have changed the
statements using macro Field&cTemp.
Is it possible to write a similar solution in Transact SQL and avoid
20 identical statements?DECLARE @.i int, @.qry varchar(500)
SET @.i=1
WHILE @.i<=20 BEGIN
SET @.Qry='UPDATE tblA SET Field'+Cast(@.i as varchar)+'=100000'
EXEC(@.Qry)
SET @.i=@.i+1
END
This is not the most efficient method but it closely follows your
example(minus the 0 prefix on the first 9 fields). Better would be to build
up the string for a single update to all columns but I'll leave that to you
:)
Mr Tea
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||A couple of questions:
1. Is there a where clause, or is this a single row table?
2. Updating the same row or rows twenty different times is not a very
efficient approach (it will end up taking twenty different log writes!)
3. How are you matching the field with the value?
In general it is far better when it comes to SQL to execute fewer complex
statements than many simpler statements. Building the proper statement and
executing it will be far better. So you could write something like:
--not meant to be compilable, pseudocode only
set @.query = 'UPDATE tblA --hopefully not your real table name'
set @.query = 'SET '
set @.i = 1
while @.i < 20
begin
set @.query = @.query + 'Field' + cast(@.i as varchar(2)) + ' = 100000, '
set @.i = @.i + 1
end
set @.query = @.query + 'WHERE --and your where clause'
exec (@.query)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||Thanks a lot Mr Tea and Mr Davidson
It's work (on my more complex task). :-)
And of course - answers:
1. I have a another table (not this one for update). That table
contains circular nodes of hieararchy. This is the reason why I must
first fullfill columns step-by-step.
2. ...it means: yes, I have a WHERE clause and FROM clause (JOIN with
circular table) also.
3. Yes, .log file is written 20 times but I will sucrifise that. I
work with basic data (corporate hierarchy) - not huge set of rows.
4. Of course, matching values in my example is not so simple. I
matching a values with another one 'macro' that read a data form
another table.
...and all of that because you help me! :-)
So,
Thank you once again
Mr Zaratino|||>> Imagine that we have a table named tblA and fields [sic] with the
almost same name, for example Field01, Field02,...Field20. (I have
named fields [sic] on that way for the better explanation). <<
A column is not a field -- nothing like it at all. Since each column
is a separate attribute of the entity in your data model, it would be
VERY unusual to have such a table if you had a proper data model.
However, if I were writing a 1950's file system (files are made of
records which do have fields), then they would probably be a repeating
group -- and a violation of First Normal Form (1NF).
fields [sic]: <<
In SQL an UPDATE works on entire rows (rows are not records), changing
all the columns at the same time.
UPDATE Foobar
SET x = <value1>,
y = <value2>,
z = <value3>,
etc.
If you want to pass the values as parameters, then you can skip some of
them by passing a NULL and having this SET clause in your UPDATE
statement.
SET x = COALESCE (<value1>, x)
Dynamic SQL generation is considered very poor design; it says you have
no data model and no idea what to do until run time.|||>> That table contains circular nodes of hieararchy. This is the reason
why I must
first fullfill columns step-by-step. <<
Do you mean that you are using an adjacency list model for a hierarchy?
If so, look up the nested set model instead. Otherwise, you are not
usingthe power of a set-oriented language and have re-invented a file
system.|||Yes, Celko - everything that you said is correct, I understand UPDATE
statement; sorry for my confusion about 'fields' and 'columns'.
My congratulation, you recognize that I violate 1NF but there is a
good reason for that. I need that look of table for further purpose
(cube). With table like this the next actions are faster...(sometimes
this is even necessarly).
Thanks,
Zaratino

Friday, February 24, 2012

"IS NOT NULL" is not working

We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?Run the following and post the results:
select
dateofbusiness
, count (*)
from
MyTable
group by
dateofbusiness
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104271245.345995.62690@.f14g2000cwb.googlegroups.com...
We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?|||Try,
where ISDATE(dateofbusiness) <> 0
--
Message posted via http://www.sqlmonster.com|||2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392|||Also try,
"where convert(char(10),dateofbusiness,120) is not null"
and post results,
Jon
--
Message posted via http://www.sqlmonster.com|||OK, so there are none that are not null. Could you please post the DDL for
your table and the exact query you ran?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104272808.874749.162530@.z14g2000cwz.googlegroups.com...
2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392|||And to emphasize futher the same statement with the count(*) function
returned no results. And without the count function and group by it
will return results, however, when keying in on an additional field
such as storeid like "where storeid = 34 and DateofBusiness is not
null" nothing returns. If you filter by store id, i see dateofbusiness
had values in it?
set transaction isolation level read uncommitted
select
dateofbusiness,
count (*)
from
Temp_HstSalesByInterval
where dateofbusiness is not null
group by
dateofbusiness|||John we have already done this as a hotfix to our database,
"rtrim(dateofbusiness) is not null" works, too. However we have over
2000 databases that do not have the hot fix, and other code affecting
the table.
SQL is not doing what its supposed to be doing and I'm looking for an
answer. I don't usually post until....|||Ken,
I did run into this problem in the past and am trying to remember what caused it to happen and how I fixed it. I've tried to replicate the date-time like your example and my query worked just fine.
I thought it had something to do with the hh:mm:ss being all zeros, but I'm not sure that's the problem. I also tried the ansi-null option on the db and it still worked. I also tried the connection object and thought maybe one of the settings was incorrectly set, but that did reproduce your error.
If I find out, I'll post or you can email me at: corncrowe@.aol.com
Jon
--
Message posted via http://www.sqlmonster.com|||CREATE TABLE [dbo].[Temp_HstSalesByInterval] (
[DateOfBusiness] [datetime] NULL ,
[FKStoreId] [int] NULL ,
[FKRevenueId] [int] NULL ,
[Period] [int] NULL ,
[Type] [int] NULL ,
[TypeId] [int] NULL ,
[TypeId2] [int] NULL ,
[Amount] [float] NULL ,
[OpenHour] [int] NULL ,
[lCount] [int] NULL ,
[DestinationServer] [int] NULL
) GO
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
GO
select * from Temp_HstSalesByInterval where fkstoreid = 23 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 23 will return
results and dateofbusiness is populated.|||Ken,
The all zeros in hh:mm:ss is most likely the problem. I asked the other programmer here and we both agreed that we encountered this problem when trying to query a date field with zeros in the hh:mm:ss stamp.
Why or how did you manage to write all zeros on a date/time field? In SQL there is no such thing as 00:00:00?
Sorry,
Jon
--
Message posted via http://www.sqlmonster.com|||Ken,
Maybe we are wrong about the zeros. But threw that out for you,
Jon
--
Message posted via http://www.sqlmonster.com|||> Why or how did you manage to write all zeros on a date/time field? In SQL
there is no such thing as 00:00:00?
Midnight is not a valid time?|||create table lookatdate (d datetime null)
insert lookatdate values ('09/04/2004')
select * from lookatdate
returns 2004-09-04 00:00:00.000|||This never happenend. Jon mistook reading one field as two fields.
09/23/2004 00:00:00:000|||> This never happenend. Jon mistook reading one field as two fields.
> 09/23/2004 00:00:00:000
Okay, maybe another reason to visit the link from FAQ #5006 to generate
insert statements, instead of presenting sample data in heap style...
http://vyaskn.tripod.com/code.htm#inserts|||Well maybe there something to what you are saying, but why does it work
in the other databases on the same server?|||Ken,
I have another suggestion which may sound odd. But consider this:
"Specifies that the Boolean result be negated. The predicate reverses its return values, returning TRUE if the value is not NULL, and FALSE if the value is NULL."
"The WHERE and HAVING clauses in a SELECT statement control the rows from the source tables that are used to build the result set. WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. Those rows meeting the search conditions are said to be qualified to participate in the result set. (Microsoft)"
I am thinking that when you build the resultset with "not null" it evaluates the criteria as "unknown" and thereby won't produce any resultset. But when you trim or otherwise convert the datatype then the query returns a resultset.
Try a couple other approaches to see if you get a resultset:
select * from tblName
where not (datefield) is null
-or-
select somefield where datefield is not null
I am also wondering if building the resultset with the all "*" isn't masking the issue either? But either way, NOT NULL evaluates to a true condition if the value is not null. Otherwise, I think the field "as defined" is unknown.
I tried to replicate your error with a test database and couldn't. I tried several db_options and ansi null defaults and still couldn't reproduce your error. I do vaguely remember this problem, but can't for the life of me remember what cause it or how I fixed it.
Sorry I couldn't be more of a help,
Jon
--
Message posted via http://www.sqlmonster.com|||Ken,
The first suggestion is to make sure that FKstoreid 34 actually has data.
If so, then if the query works on other databases on the same server, that
would suggest something is peculiar to the data in that database.
What happens if you restore that particular db to another SQL server with
the same version and run the query? If it still returns no results, that
would suggest definitely something in the data is causing the issue.
At this stage in SQL Server 2000's lifecyle, it seems unlikely that your
data has uncovered a bug in the query engine, but it's possible. Try adding
an identity column as a primary key and see if that makes a difference.
Maybe grouping on a date column in a heap...?
Ron
--
Ron Talmage
SQL Server MVP
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104273359.147443.196280@.f14g2000cwb.googlegroups.com...
> And to emphasize futher the same statement with the count(*) function
> returned no results. And without the count function and group by it
> will return results, however, when keying in on an additional field
> such as storeid like "where storeid = 34 and DateofBusiness is not
> null" nothing returns. If you filter by store id, i see dateofbusiness
> had values in it?
> set transaction isolation level read uncommitted
> select
> dateofbusiness,
> count (*)
> from
> Temp_HstSalesByInterval
> where dateofbusiness is not null
> group by
> dateofbusiness
>|||Hi Ron,
The store exists. we are not doing any group bys or joins. Its a very
simple select statement.
select * from Temp_HstSalesByInterval where fkstoreid = 34 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 34 (without
above) will return
results and dateofbusiness is populated.|||I'm wondering if it's possible to create a new table and populate it with
the contents of the old one - preferably by bcp'ing the data out and back in
again.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104333999.756145.81940@.f14g2000cwb.googlegroups.com...
Hi Ron,
The store exists. we are not doing any group bys or joins. Its a very
simple select statement.
select * from Temp_HstSalesByInterval where fkstoreid = 34 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 34 (without
above) will return
results and dateofbusiness is populated.|||Hi Jon
select * from tblName
where not (datefield) is null
this produces no records
select somefield where datefield is not null
This will work, but when you add "and fkstoreid = anynumber" nothing
returns. If you query only on fkstoreid only you will see the results
and dateofbusiness is populated. I did an update to this table to set
datetime field with refreshed values and that did not fix it.
The actual select statement lists the fields not (*). both have the
same affect.|||> select somefield where datefield is not null
> This will work, but when you add "and fkstoreid = anynumber" nothing
> returns.
This does not seem possible. How large is your database? I am wondering if
you would be able to post a copy of it online and someone could try to
reproduce the problem on your actual data. I am guessing there is something
else inaccurate here that you're not catching...|||Hi Tom,
We did this with DTS.|||Ken,
Another (way out there) suggestion. Maybe, just maybe, there is a constraint on the fkStoreId between parent and child? I know this sounds weird, but maybe when you consider the where clause is failing when you combine both the fkStoreId and DateofBusiness fields in the same statement.
The condition is returning false for some reason. I know it's really a long shot, but could there be an issue with the Stores table referential integrity? Is the fkStoreId an orphan? I seriously doubt that this is the culprit, but weirder things have been know to happen in SQL world.
I did create your temp table and inserted rows of data, but couldn't reproduce the error. I don't know what the parent table looks like so I could do anything on that thread.
Good luck,
Jon
--
Message posted via http://www.sqlmonster.com|||Hi Jon, there is no contstraint defined on fkstoreid. However manually
dropping the nonunique clustered index(DateofBusiness, FKStoreid,
DestinationServer) columns (all allow nulls) and recreated it and now
not null is working finally! :)
The other db man here swears he ran DBCC REINDEX on the table and that
did not fix the issue. Isn't running DBCC REINDEX the same as dropping
and recreating an index? Any know issues with this?|||This is great news. It's hard to say what happened here. If you have a
copy of the database before you did the DBREINDEX, I'd be tempted to run
DBCC CHECKTABLE and see if it found anything.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104344333.997556.246980@.c13g2000cwb.googlegroups.com...
Hi Jon, there is no contstraint defined on fkstoreid. However manually
dropping the nonunique clustered index(DateofBusiness, FKStoreid,
DestinationServer) columns (all allow nulls) and recreated it and now
not null is working finally! :)
The other db man here swears he ran DBCC REINDEX on the table and that
did not fix the issue. Isn't running DBCC REINDEX the same as dropping
and recreating an index? Any know issues with this?|||Jon, when I recreated the nonunique clustered index DateOfBusiness
order was changed from descending to ascending. "IS NOT NULL" on
dateofbusiness works. When it was set back to descending it doesn't
work.
On the same table in other databases, descending does not have an issue
with the "IS NOT NULL" not working. However, this table has 3 million
records and the tables in the other databases have less than 200,000
records.
Throughing this out to see if you can recreate the issue?|||Ken,
Sorry if you did this already, but have you posted the result of
SELECT @.@.VERSION ? Also, is there anything you can see different with
this particular database, such as a compatibility level setting or
something?
Steve Kass
Drew University
Ken wrote:
>Jon, when I recreated the nonunique clustered index DateOfBusiness
>order was changed from descending to ascending. "IS NOT NULL" on
>dateofbusiness works. When it was set back to descending it doesn't
>work.
>On the same table in other databases, descending does not have an issue
>with the "IS NOT NULL" not working. However, this table has 3 million
>records and the tables in the other databases have less than 200,000
>records.
>Throughing this out to see if you can recreate the issue?
>
>|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
compatability on all databases 8.0|||There has a been a security hotfix issued since then. That would bring the
version to 8.00.818:
http://www.microsoft.com/downloads/details.aspx?FamilyID=9814AE9D-BD44-40C5-ADD3-B8C99618E68D&displaylang=en
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104349480.164105.285990@.c13g2000cwb.googlegroups.com...
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
compatability on all databases 8.0|||Hm. Are the "working" databases on the same instance of SQL Server? If
not, and the others are a later version,
try installing 8.00.818 (or later) and see if the problem goes away.
There are sometimes bug fixes in security hotfixes, since the hotfixes
are cumulative. If that's not the problem, I'll see if I can come up
with some other questions!
SK
Ken wrote:
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
>14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
>Edition on Windows NT 5.2 (Build 3790: )
>compatability on all databases 8.0
>
>|||Ken,
"nonunique clustered index DateOfBusiness order was changed from descending to ascending. "IS NOT NULL" on dateofbusiness works. When it was set back to descending it doesn't work."
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
I was leading toward the index as my next suggestion. The index is a composite and is made up of different datatypes. When you were doing the query the index is backwards (desc) order for date and acs for the other two fields. That's probably why the query never returned anything. I can replicate this error on a test database with 30 million call records using essentially the same logic. I'll let you know later what happens.
I probably wouldn't use mix order for my indices. Maybe that was what I had done in the past and cause a problem? Anyway, glad you were able to correct the problem.
Jon
--
Message posted via http://www.sqlmonster.com|||Hi Jon, please replicate the error. Why does it only happen on tables
with a large number of rows?
My other thought could still be a data issue depending on what gets
evaluated first?|||> My other thought could still be a data issue depending on what gets
> evaluated first?
The WHERE clause does not work that way. Put the individual clauses in any
order, and the result will be the same...|||Hi Tom,
The hotfix does not address the particular problem in any
documentation. We have a lot of servers (clustered) and my boss never
lets me do anything on a guess, only until I have found the problem and
I can qualify it. If I could validate that the problem was server wide
and this occurrance was a documented bug or issue, I would implement
this asap.
I do appreciate your assistance. Your suggestion may be a fix to this,
but I'm on a supertanker thats not easy to steer or deploy changes.|||I hear ya. Nevertheless, the patch is a security hotfix and closes a
vulnerability. As another poster mentioned, fixes are cumulative and it is
possible that your issue may have been addressed there.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104353652.360283.184350@.f14g2000cwb.googlegroups.com...
Hi Tom,
The hotfix does not address the particular problem in any
documentation. We have a lot of servers (clustered) and my boss never
lets me do anything on a guess, only until I have found the problem and
I can qualify it. If I could validate that the problem was server wide
and this occurrance was a documented bug or issue, I would implement
this asap.
I do appreciate your assistance. Your suggestion may be a fix to this,
but I'm on a supertanker thats not easy to steer or deploy changes.|||Ken,
Maybe this newsgroup thread will help. Sorry I didn't remember this,
but it looks to me like the same bug you're seeing, and 8.00.818 fixed it:
http://groups-beta.google.com/groups?hl=en&q=8.00.818+fix+bug+kass+-turkish&qt_s=Search+Groups
SK
Ken wrote:
>Hi Tom,
>The hotfix does not address the particular problem in any
>documentation. We have a lot of servers (clustered) and my boss never
>lets me do anything on a guess, only until I have found the problem and
>I can qualify it. If I could validate that the problem was server wide
>and this occurrance was a documented bug or issue, I would implement
>this asap.
>I do appreciate your assistance. Your suggestion may be a fix to this,
>but I'm on a supertanker thats not easy to steer or deploy changes.
>
>|||Jon, I have a sneaky suspicioun that its in the data. When we move the
same data to another table it produces the same error. If we move or
create different data it does not. Weird.

"IS NOT NULL" is not working

Sunday, February 19, 2012

"INSTEAD OF" Triggers don't seem to work (maybe I misunderstand?)

SQL Server documentation (BOL) states that an "instead of" trigger represent
s
code
that will execute instead of the action requested by the statement that
caused the
trigger to fire. For example, in the case of an "instead of update"
trigger, the
update statment which caused the trigger to fire, would be prevented from
updating the table, simply by virtue of the fact that an "instead of update"
trigger exists. Am I understanding this correctly?
I have an "instead of update trigger" which does not prevent the update
unless I explicitly do a ROLLBACK TRAN. Is this the expected behavior?
TIA,
R. AldersonAn Instead Of trigger should prevent the update. Can you post your code?
Try the following:
Set Nocount on
use pubs
go
Create Table foo (foovalue int)
go
Create Trigger trFoo on foo
Instead Of Update As
Select 'trFoo trigger Fired'
go
Insert Into foo (foovalue) values (1)
Select * from foo
Update foo Set foovalue = 2
Select * from foo
Drop Table foo
Your result should be :
foovalue
--
1
trFoo trigger Fired
foovalue
--
1
showing that the update of foovalue to 2 did not take place.
Tom
"rca" <rca@.discussions.microsoft.com> wrote in message
news:3C9E59C2-8BCB-4A86-A3FA-F2ED58014214@.microsoft.com...
> SQL Server documentation (BOL) states that an "instead of" trigger
> represents
> code
> that will execute instead of the action requested by the statement that
> caused the
> trigger to fire. For example, in the case of an "instead of update"
> trigger, the
> update statment which caused the trigger to fire, would be prevented from
> updating the table, simply by virtue of the fact that an "instead of
> update"
> trigger exists. Am I understanding this correctly?
> I have an "instead of update trigger" which does not prevent the update
> unless I explicitly do a ROLLBACK TRAN. Is this the expected behavior?
> TIA,
> R. Alderson

Thursday, February 16, 2012

"GO" statement in SQK2K

Hi,
I have been using a lot of "GO" statement in my SQL
script (say a file called abc.sql) with SQL7.0
For Example :
--
Select * from A
GO
Update A Set COL1 = Null
GO
Since I upgraded to SQL2K, I have go a SYNTAX
error.
Help Needed
Thanks
EJChewFrom where do you execute the SQL code? QA? Check configuration if someone changed the batch
separator. Can you post the exact errormessage?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in message news:3f94db83@.news.starhub.net.sg...
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>|||ejchew:
just a stab in the dark, but do you have the GO statments located on the
same line as another sql statement? this will result in an error
for example:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects; GO
now try the following queries:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects;
GO
hth
jeff clausius
sourcegear corporation
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in news:3f94db83
@.news.starhub.net.sg:
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>

Monday, February 13, 2012

"Drop User" statement does not seem to work

I restored a copy of the production database onto the staging db server. Som
e
of the production database users which need to be removed did not seem to
work when I issued "drop user [username]" statement. I also tried
"sp_dropuser [username]" and that did not work either. Has anyone encoun
tered
this issue? Thanks.Check out sp_change_users_login in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:FF46C63C-56E8-41F7-B238-2E0DA602E209@.microsoft.com...
I restored a copy of the production database onto the staging db server.
Some
of the production database users which need to be removed did not seem to
work when I issued "drop user [username]" statement. I also tried
"sp_dropuser [username]" and that did not work either. Has anyone
encountered
this issue? Thanks.

"Decode on rowsource for listbox"

Hi.

I have coded the rowsource for my listbox and I need some sort of "decode" function within my SELECT statement. Can someone help?

For example,

Teacher is a checkbox which contains 0 and 1
I need the listbox to show the word "Teacher" if teacher = 1; else show "Assistant"

mylstbox.rowsource = "SELECT Teacher From mytable"

Statement above populates my listbox with 0 or 1. However, I need the word "Teacher" or "Assistant" to show on the listbox.

Help!

Thanks

SHKI found the dolution by using "case when then else"

Saturday, February 11, 2012

"Custom" Report Model Data Source?

Hello,
Is this a true statement: "The only Data Sources available to a Report Model
are SQL Server 2000, SQL Server 2005, and SQL Server Analysis Services 2005"?
I had read somewhere that there was going to be an ability to specify a
"custom" query processors so that you could use data sources in addition to
the items listed above.
Thanks!I don't think so...
Since you can use ADO.Net every supported DataRpovider should work with
it... at least in local reports
"brianpmccullough" <bmccullough11@.comcast.net> wrote in message
news:2A5B524A-176B-4BD2-ACC2-75C9C3DDB2DA@.microsoft.com...
> Hello,
> Is this a true statement: "The only Data Sources available to a Report
> Model
> are SQL Server 2000, SQL Server 2005, and SQL Server Analysis Services
> 2005"?
> I had read somewhere that there was going to be an ability to specify a
> "custom" query processors so that you could use data sources in addition
> to
> the items listed above.
> Thanks!
>

Thursday, February 9, 2012

"Balance Carried Forward" / "Balance Brought Forward"

I have a statement report. There is a table which contains the statement
line details. The printout of this may last several pages. Assume it lasts n
pages.
The bottom of page1 should show the total debits and total credits for page
1 only.
The bottom of page 2 should show the total debits and credits for page 1 and
2 only e.t.c.
I found I cant add Globals!PageNumber as a group to the table.
Neither am I allowed to put the data field in the page footer.
I thought RunningValue function might do it, but this always prints the
grand total for all pages.
Help !
Thanks
Chris BrooksbankPagination and aggregation (I suspect) are in unrelated parts of the RS
code..(I think all of the aggregation stuff is done prior to pagination).
There are no page totals kinds of functions that I am aware of..
Probably the best you can do is to create small groupings, so that a group
will never be larger than a page and total at the group level...
Unless one of the dev guys has a suggestion.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Brooksbank" <NoSpam@.Ta.com> wrote in message
news:OR2sIiBZEHA.2408@.tk2msftngp13.phx.gbl...
> I have a statement report. There is a table which contains the statement
> line details. The printout of this may last several pages. Assume it lasts
n
> pages.
> The bottom of page1 should show the total debits and total credits for
page
> 1 only.
> The bottom of page 2 should show the total debits and credits for page 1
and
> 2 only e.t.c.
> I found I cant add Globals!PageNumber as a group to the table.
> Neither am I allowed to put the data field in the page footer.
> I thought RunningValue function might do it, but this always prints the
> grand total for all pages.
> Help !
> Thanks
> Chris Brooksbank
>

Sunday, March 11, 2012

"select" statement in a function

"select TOP" with parameter...

Thursday, March 8, 2012

"Resource is low, some results are dropped"

"Prepare" in SQL Server through ODBC/ADO

Saturday, February 25, 2012

"Macro" statement

Friday, February 24, 2012

"IS NOT NULL" is not working

"IS NOT NULL" is not working

"IS NOT NULL" is not working

Sunday, February 19, 2012

"INSTEAD OF" Triggers don't seem to work (maybe I misunderstand?)

Thursday, February 16, 2012

"GO" statement in SQK2K

Monday, February 13, 2012

"Drop User" statement does not seem to work

"Decode on rowsource for listbox"

Saturday, February 11, 2012

"Custom" Report Model Data Source?

Thursday, February 9, 2012

"Balance Carried Forward" / "Balance Brought Forward"

sql error

Blog Archive

About Me