Showing posts with label behaviour. Show all posts
Showing posts with label behaviour. Show all posts

Monday, March 19, 2012

Currency sign problem

Hi,
I have a problem with an ASP page pulling data from a SQL2000 server.
Expected behaviour is that when prices are displayed they are displayed in
pounds as this is the default locale of the server. However they are
displayed in dollars.
I've checked the default locale and the user locale of the (windows 2000)
server and these are both British england. The language for the SQL user is
also British english.
Are there any other settings in SQL 2000 server that could affect this?
Thanks,
TimSQL Server displays money data types as numbers. It does not have any =currency symbols. How are you displaying the data within your active =server page? Perhaps you need to make sure that the code which displays =the data is correct. If it is, one more thing to check would be to make =sure that a smart developer has not hard coded the currency display =within the stored procedure or sql statement which returns the data.
-- Keith, SQL Server MVP
"Tim Harrison" <tim_eridge@.hotmail.com> wrote in message =news:OeHqV8uSDHA.2148@.TK2MSFTNGP11.phx.gbl...
> Hi,
> > I have a problem with an ASP page pulling data from a SQL2000 server.
> Expected behaviour is that when prices are displayed they are =displayed in
> pounds as this is the default locale of the server. However they are
> displayed in dollars.
> > I've checked the default locale and the user locale of the (windows =2000)
> server and these are both British england. The language for the SQL =user is
> also British english.
> > Are there any other settings in SQL 2000 server that could affect =this?
> > Thanks,
> > Tim
> >

Sunday, February 19, 2012

CTE behaviour in SQL 2005

I were trying to achive paging through using a CTE etc, but ran into the following weither thing happening. The CTE allows me to use avariable as the ORder By field, although the CTE do not care at all what is in there? Have any one seen this or maybe can explain this?

USE AdventureWorks;

GO

DECLARE @.SortExpression Varchar(50)

Set @.SortExpression = 'SalesPersonID ASC';

WITH Sales_CTE (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by @.SortExpression) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE;

WITH Sales_CTE1 (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by SalesPersonID ASC) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE1

I know your post is a few months; however, I figured I'd post a response incase you or anyone else is interested in a workaround.

I came across the same problem with CTE (common table expressions) and sorting. this code below may work. I don't have adventureWorks installed so my code may cause errors if used exactly. Debugging shouldn't be a problem:

Code Snippet

USE AdventureWorks;

GO

DECLARE @.SortExpression Varchar(50)

Set @.SortExpression = 'SalesPersonID ASC';

DECLARE @.sql1 varchar(4000)

SET @.sql1 = 'SELECT RowNumber, SalesPersonID, NumberOfOrders, MaxDate

FROM

(

SELECT

ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

) tbl1'

exec sp_executesql @.sql1

If you're using a DataSource in .NET and want to implement paging, sorting, AND filtering (for searching or limiting data) then please read on. sp_executesql also supports bind variables. SQL will will reuse the cached execution plan, this SQL won't recalculate the execution plan each time the page, sort, or filter condition(s) change.

This procedure below will do the same as query above, let you page the data (if you have many salesPersonIDs), and will will let you limit which SalesPersonID you're looking at... all with bind variables for faster query execution using sp_executesql.

Code Snippet

CREATE PROCEDURE [dbo].[usp_getSalesOrders]

@.SalesPerson int,

@.startRowIndex int,

@.maximumRows int,

@.SortExpression varchar(20)

AS

BEGIN

SET NOCOUNT ON;

--if a sortExpression isn't passed in, give it a default

IF len(isnull(@.SortExpression, '') = 0

set @.SortExpression = 'SalesPersonID ASC'

DECLARE @.sql nvarchar(4000)

SET @.sql = 'SELECT SalesPersonID, NumberOfOrders, MaxDate

FROM

(

SELECT

ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader with(nolock)

WHERE

(SalesPersonID = @.SalesPerson OR @.SalesPerson IS NULL)

GROUP BY SalesPersonID

) t

WHERE t.RowNumber BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1'

exec sp_executesql @.sql,

N'@.SalesPerson int,@.startRowIndex int,@.maximumRows int',

@.NumOfOrders,

@.startRowIndex,

@.maximumRows;

END

You can further optimize this, and I welcome comments as it'll help to improve my own code.

Hope this helps!

Nick

CTE behaviour in SQL 2005

I were trying to achive paging through using a CTE etc, but ran into the following weither thing happening. The CTE allows me to use avariable as the ORder By field, although the CTE do not care at all what is in there? Have any one seen this or maybe can explain this?

USE AdventureWorks;

GO

DECLARE @.SortExpression Varchar(50)

Set @.SortExpression = 'SalesPersonID ASC';

WITH Sales_CTE (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by @.SortExpression) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE;

WITH Sales_CTE1 (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT

ROW_NUMBER() OVER(Order by SalesPersonID ASC) RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

Select * From Sales_CTE1

I know your post is a few months; however, I figured I'd post a response incase you or anyone else is interested in a workaround.

I came across the same problem with CTE (common table expressions) and sorting. this code below may work. I don't have adventureWorks installed so my code may cause errors if used exactly. Debugging shouldn't be a problem:

Code Snippet

USE AdventureWorks;

GO

DECLARE @.SortExpression Varchar(50)

Set @.SortExpression = 'SalesPersonID ASC';

DECLARE @.sql1 varchar(4000)

SET @.sql1 = 'SELECT RowNumber, SalesPersonID, NumberOfOrders, MaxDate

FROM

(

SELECT

ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

) tbl1'

exec sp_executesql @.sql1

If you're using a DataSource in .NET and want to implement paging, sorting, AND filtering (for searching or limiting data) then please read on. sp_executesql also supports bind variables. SQL will will reuse the cached execution plan, this SQL won't recalculate the execution plan each time the page, sort, or filter condition(s) change.

This procedure below will do the same as query above, let you page the data (if you have many salesPersonIDs), and will will let you limit which SalesPersonID you're looking at... all with bind variables for faster query execution using sp_executesql.

Code Snippet

CREATE PROCEDURE [dbo].[usp_getSalesOrders]

@.SalesPerson int,

@.startRowIndex int,

@.maximumRows int,

@.SortExpression varchar(20)

AS

BEGIN

SET NOCOUNT ON;

--if a sortExpression isn't passed in, give it a default

IF len(isnull(@.SortExpression, '') = 0

set @.SortExpression = 'SalesPersonID ASC'

DECLARE @.sql nvarchar(4000)

SET @.sql = 'SELECT SalesPersonID, NumberOfOrders, MaxDate

FROM

(

SELECT

ROW_NUMBER() OVER(Order by ' + @.SortExpression + ') RowNumber,

SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader with(nolock)

WHERE

(SalesPersonID = @.SalesPerson OR @.SalesPerson IS NULL)

GROUP BY SalesPersonID

) t

WHERE t.RowNumber BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1'

exec sp_executesql @.sql,

N'@.SalesPerson int,@.startRowIndex int,@.maximumRows int',

@.NumOfOrders,

@.startRowIndex,

@.maximumRows;

END

You can further optimize this, and I welcome comments as it'll help to improve my own code.

Hope this helps!

Nick