Binary column

We have a binary column that stores web passwords.
What is the proper way to access this column?
The sql below is blowing up!
UPDATE AppServices.dbo.DBUsers
SET Password = 'MyPassword'
WHERE UserId ='MyLogin'Here is one way to accomplish this task.
UPDATE AppServices.dbo.DBUsers
SET Password = pwdencrypt('MyPassword')
WHERE UserId ='MyLogin'
pwdencrypt and pwdcompare are two undocumented SQL functions.
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:E269D86B-E84E-45CE-8E33-81D483391080@.microsoft.com...
> We have a binary column that stores web passwords.
> What is the proper way to access this column?
> The sql below is blowing up!
> UPDATE AppServices.dbo.DBUsers
> SET Password = 'MyPassword'
> WHERE UserId ='MyLogin'

BI Accelerator 1.2 Restriction not in 1.1

Please see below a section in the BI Accelerator 1.2
documentation. This is not present in the v1.1
documentation. We would like to know why this restriction
(we are forced to go to lowest level when 2 hierarchies
are included in a cube) was imposed in version 1.2.
o When a cube includes both the primary and based-on
hierarchy of a dimension, you cannot specify the level
when including both these hierarchies in a cube; the
lowest level of the dimension will be included in the
cube.
o You cannot have a based-on hierarchy in a cube
without having the primary hierarchy.
o You can have the primary hierarchy in a cube without
the based-on hierarchy, and you can specify the level for
the cube.
ThanksClearly, the second and third rules make sense. I wondered whether
the first rule was simply that Microsoft's Excel front-end doesn't do
sufficient checking, i.e. that the level you choose for the primary
hierarchy
to be attached to the cube is not higher than the level at which
the primary hierarchy and the "based-on" hierarchy are joined.
It looks to me as though the underlying relation structure that is
generated would support a level higher than the lowest one.
I notice too that when defining a "based-on" level you must
"... select a level from the first hierarchy that is defined for that
dimension ..." (quoted from the development guide). This is a
restriction enforced by the front-end that the underlying database
doesn't need. Without this simplification it would need to check
for circularities - perhaps they chose the easiest option.
Maybe there is a more subtle reason, but it is not obvious.
John Bates
"Joseph Ollero" <jollero@.wizardsrgoup.com> wrote in message
news:c77301c438cf$1bb1cf40$a001280a@.phx.gbl...
> Please see below a section in the BI Accelerator 1.2
> documentation. This is not present in the v1.1
> documentation. We would like to know why this restriction
> (we are forced to go to lowest level when 2 hierarchies
> are included in a cube) was imposed in version 1.2.
>
> o When a cube includes both the primary and based-on
> hierarchy of a dimension, you cannot specify the level
> when including both these hierarchies in a cube; the
> lowest level of the dimension will be included in the
> cube.
> o You cannot have a based-on hierarchy in a cube
> without having the primary hierarchy.
> o You can have the primary hierarchy in a cube without
> the based-on hierarchy, and you can specify the level for
> the cube.
>
> Thanks
>

BI Accelerator 1.2 Restriction not in 1.1

Please see below a section in the BI Accelerator 1.2
documentation. This is not present in the v1.1
documentation. We would like to know why this restriction
(we are forced to go to lowest level when 2 hierarchies
are included in a cube) was imposed in version 1.2.
o When a cube includes both the primary and based-on
hierarchy of a dimension, you cannot specify the level
when including both these hierarchies in a cube; the
lowest level of the dimension will be included in the
cube.
o You cannot have a based-on hierarchy in a cube
without having the primary hierarchy.
o You can have the primary hierarchy in a cube without
the based-on hierarchy, and you can specify the level for
the cube.
Thanks
Clearly, the second and third rules make sense. I wondered whether
the first rule was simply that Microsoft's Excel front-end doesn't do
sufficient checking, i.e. that the level you choose for the primary
hierarchy
to be attached to the cube is not higher than the level at which
the primary hierarchy and the "based-on" hierarchy are joined.
It looks to me as though the underlying relation structure that is
generated would support a level higher than the lowest one.
I notice too that when defining a "based-on" level you must
"... select a level from the first hierarchy that is defined for that
dimension ..." (quoted from the development guide). This is a
restriction enforced by the front-end that the underlying database
doesn't need. Without this simplification it would need to check
for circularities - perhaps they chose the easiest option.
Maybe there is a more subtle reason, but it is not obvious.
John Bates
"Joseph Ollero" <jollero@.wizardsrgoup.com> wrote in message
news:c77301c438cf$1bb1cf40$a001280a@.phx.gbl...
> Please see below a section in the BI Accelerator 1.2
> documentation. This is not present in the v1.1
> documentation. We would like to know why this restriction
> (we are forced to go to lowest level when 2 hierarchies
> are included in a cube) was imposed in version 1.2.
>
> o When a cube includes both the primary and based-on
> hierarchy of a dimension, you cannot specify the level
> when including both these hierarchies in a cube; the
> lowest level of the dimension will be included in the
> cube.
> o You cannot have a based-on hierarchy in a cube
> without having the primary hierarchy.
> o You can have the primary hierarchy in a cube without
> the based-on hierarchy, and you can specify the level for
> the cube.
>
> Thanks
>

between not betweening inclusive

In the sql statements below, both BETWEEN and (>= and <=) are giving unexpected results. I am working with monthly data where in one table the time part of [DateTime]=09:31:00 the result is correct but if the time is 16:00:00 (in another table) the statement returns empty. Also if I change either sql statement to use 16:01:00, it returns the second table correctly but that should not be necessary because both statements below are supposed to be inclusive. Anybody know why this is happening and what to do about it?

select distinct * from [clean].[table2] where

convert(varchar, [DateTime],126) between '2005-11-30' and '2005-12-01' and

convert(varchar, [DateTime],114) between '09:30:00' and '16:00:00'

order by [DateTime]

The following statement has the same problem:

select distinct * from [clean].[table2] where

(convert(varchar,[DateTime],126) >= '2006-06-05' and

convert(varchar,[DateTime],126) <= '2006-11-05') and

(convert(varchar,[DateTime],114) >= '09:30:00' and

convert(varchar,[DateTime],114) <= '16:00:00')

order by [DateTime]

Time also has a milliseconds value. It is quite likely that your time values are in actuallity greater than exactly 16:00:00 -perhaps 16:00:00.001.

If you wish to include the times that are between 16:00:00 and 16:00:01 (meaning all times with any millisecond value, then use 16:00:01.

If you wish to ignore the milliseconds value, you may be better served using the smalldatetime datatype.

|||

rwbogosian wrote:

In the sql statements below, both BETWEEN and (>= and <=) are giving unexpected results. I am working with monthly data where in one table the time part of [DateTime]=09:31:00 the result is correct but if the time is 16:00:00 (in another table) the statement returns empty. Also if I change either sql statement to use 16:01:00, it returns the second table correctly but that should not be necessary because both statements below are supposed to be inclusive. Anybody know why this is happening and what to do about it?

select distinct * from [clean].[table2] where

convert(varchar, [DateTime],126) between '2005-11-30' and '2005-12-01' and

convert(varchar, [DateTime],114) between '09:30:00' and '16:00:00'

order by [DateTime]

The following statement has the same problem:

select distinct * from [clean].[table2] where

(convert(varchar,[DateTime],126) >= '2006-06-05' and

convert(varchar,[DateTime],126) <= '2006-11-05') and

(convert(varchar,[DateTime],114) >= '09:30:00' and

convert(varchar,[DateTime],114) <= '16:00:00')

order by [DateTime]

Why cant you just use

SELECT DISTINCT
*
FROM
clean.table2
WHERE
DateTime >= '2006-06-05 09:30:00' AND DateTime <= '2006-11-05 16:00:00'
|||Your query is doing string compares, which never work with between.

You need to do this:

SELECT DISTINCT * FROM [clean].[table2] WHERE
[DateTime] between CAST('2005-11-30' AS DATETIME) AND CAST('2005-12-01' AS DATETIME) AND
CONVERT(DATETIME,CONVERT(varchar,[DateTime],114)) BETWEEN CONVERT(datetime, '09:30:00') AND CONVERT(datetime, '16:00:00')

This will convert the dates to 1900-01-01 and compare the times correctly.

There is no reason to convert the dates to strings.|||

Changing the statement to use 16:00:01 does work. The actual value in the table is 16:00:00.000 but not even 16:00:00.9999 works; it has to be a full second after the actual time in the database table.

I know in the near future I'm going to have problems when we start analyzing even smaller time frames. While I am happy that changing one second works, shouldn't BETWEEN and (<= or >=) be inclusive. Is this a bug or a feature?

|||Can't use your statement because I can't use anything before 09:30:00 or after 16:00:00 on any of the dates between (inclusive).|||Thanks Tom; that appears to be working.

BETWEEN 20/5/2007 AND 30/5/2007 not returning rows.

Hello,

SQL Server 2005

I have tried and cannot return any rows either though there are records that should be returned, using the sql below.

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN 20/5/2007 AND 30/5/2007

In the database scheduleDate is a dateTime datatype.

In the database I have copied and pasted. 23/05/2007 00:00:00

I tried the following

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'

And got an error message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Many thanks for any help with this,

Steve

First approach doesn't work because 20/5/2007 interpreted as arithmetical expression with value 0. Then with value converted to datetime as 1/1/1901. Try following code:

Code Snippet

declare @.dt datetime

set @.dt = 20/5/2007

select @.dt

You could rewrite second query with explicit convert:

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited')

AND scheduleDate BETWEEN convert('20/5/2007',104) AND convert('30/5/2007',104)

Or use

Code Snippet

SET DATEFORMAT dmy

for setting default dateformat

|||That is because the implicit conversion thinks the date is a US date.

Do this:

.....BETWEEN CONVERT(datetime,'20/5/2007',103) AND CONVERT(datetime,'30/5/2007',103)

or

SET DATEFORMAT dmy
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'
|||

steve_1_rm,

Try using the ISO or ISO8601 format (see function CONVERT in BOL) and SQL Server will interprete correctly the datetime string, no matter the language or setting of dateformat.

SELECT *

FROM Calls

WHERE

clientName IN ('Bankside Engineering Limited')

AND scheduleDate BETWEEN '20070527' AND '20070530'

AMB

|||Hello, Thanks for your, help. but the code above did not return the rows i wanted.I have used the following and with yyyymmdd format SELECT * FROM Calls WHERE [Date] BETWEEN '20070521' AND '20070531' This is the actual value i have in the database and this is what i am trying to get out. 23/05/2007 00:00:00 Many thanks Steve|||

What do you get when you issue

Code Snippet

SELECT CONVERT(varchar(10), [Date], 103) as [Date], *

FROM Calls

WHERE [Date] BETWEEN '20070521' AND '20070531'

Best way to search

I have a stored procedure declared (shown below) The intent of the stored
proc is to return all records where the field values match the criteria
specified in the stored proc parameters. I want to specify some or all of
the parameter values. What I have written works, but I don't think it is
very efficient, any ideas?
CREATE PROCEDURE dbo.pSearch
@.strFirstName varchar(50) = NULL,
@.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
@.iYear int = null
SELECT TOP 50
p.[ID],
np.[Name] as Prefix,
p.[FirstName],
p.[MiddleName],
p.[LastName],
p.[DateOfBirth]
FROM
[Patient] p
JOIN
[NamePrefix] np ON p.NamePrefixID = np.[ID]
WHERE
(@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
(@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
(@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
(@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
(@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
ORDER BY
p.[LastName],
p.[MiddleName],
p.[FirstName]
GOHave a look at
http://www.sommarskog.se/dyn-search.html
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Jeremy Chapman" <nospam@.please.com> wrote in message
news:eSzgjdfhGHA.3424@.TK2MSFTNGP05.phx.gbl...
>I have a stored procedure declared (shown below) The intent of the stored
>proc is to return all records where the field values match the criteria
>specified in the stored proc parameters. I want to specify some or all of
>the parameter values. What I have written works, but I don't think it is
>very efficient, any ideas?
> CREATE PROCEDURE dbo.pSearch
> @.strFirstName varchar(50) = NULL,
> @.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
> @.iYear int = null
>
> SELECT TOP 50
> p.[ID],
> np.[Name] as Prefix,
> p.[FirstName],
> p.[MiddleName],
> p.[LastName],
> p.[DateOfBirth]
> FROM
> [Patient] p
> JOIN
> [NamePrefix] np ON p.NamePrefixID = np.[ID]
> WHERE
> (@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
> (@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
> (@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
> (@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
> (@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
> ORDER BY
> p.[LastName],
> p.[MiddleName],
> p.[FirstName]
> GO
>