Convert when executing query

When I am executing a simple select query in a database with correct index set it takes a long time (over 30 secunds) to execute. In another database with an identical table it runs on less then one second.
The differense I can find is that when I check execution plan on the slow database I get the arguments of the index scan to "convert([table].[column])=Convert([@.1]) and i don't get this in the other database. Does anyone have any idea on why?
Best Regards HalTry updating statistics and recreating indexes on your slow database.

blindman|||I have the update statistics automaticly set to true. Should I do that anyway?
//Hal

Originally posted by blindman
Try updating statistics and recreating indexes on your slow database.

blindman

convert to string to execute ?

need some help converting this to a string which can be executed in a stored
procedure
========================================
========================
SELECT @.List = COALESCE(@.List + ', ', '') + CAST(InvoiceID AS varchar(5))
FROM invoices, Projects
WHERE Projects.ProjectID = Invoices.IDProject + @.WhereClause>> need some help converting this to a string which can be executed in a
The code snippet you posted is not valid SQL. It is risky to use such
undocumented statements which relies on physical implementations that may
break down under various circumstances.
Anith|||what part is not valid :
example from here http://www.sqlteam.com/item.asp?ItemID=2368
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23KAM896aFHA.3840@.tk2msftngp13.phx.gbl...
> The code snippet you posted is not valid SQL. It is risky to use such
> undocumented statements which relies on physical implementations that may
> break down under various circumstances.
> --
> Anith
>|||>> what part is not valid : example from here
That seems like an old article and often people post such stuff here as
well.
First read the first sentence under the CAUSE section at:
http://support.microsoft.com/defaul...kb;en-us;287515
Also see (watch for wrapping):
http://groups-beta.google.com/group...d9?dmode=source
http://groups-beta.google.com/group...77?dmode=source
http://groups-beta.google.com/group...d7?dmode=source
Anith|||These all seem to relate to using "order by" or "group by" clauses ,neither
of which apply to this problem.
the problem I'm sing ot resolve is how to append a whereclause to a
string so it can be executed.
The code works fine if I place the where clause in statically, but returns
null if I try to use it in a dynamic format such as:
...
SET @.SQLString1 = 'SELECT ' + @.List + ' = COALESCE( ' + @.List + ' + '', '',
'''') + CAST(InvoiceID AS varchar(5))'
SET @.SQLString1 = @.SQLString1 + ' FROM invoices, Projects '
SET @.SQLString1 = @.SQLString1 + ' WHERE Projects.ProjectID =
Invoices.IDProject ' + @.WhereClause
SET @.S1 = CAST(@.SQLString1 as NVarchar(1000))
-- return results
-- ----
---
EXECUTE sp_executesql @.S1
return @.list|||This is a terrible idea. You are executing a query without looking at
it, and
if you let a user create the @.WhereClause variable, you are executing a
query
someone else wrote without looking at it. It's about as smart as a
self-serve
cash register nobody watches.
At the very least, one way to find out what this isn't working is to
actually
look at the query instead of executing it and puzzling over the error
message
or wrong results.
Usually that helps.
There are at least two reasons why
EXECUTE sp_executesql @.s1
will never modify the value of @.List, and you should notice at least one
of them: that the query you execute doesn't say @.List in it.
Steve Kass
Drew University
TJS wrote:

>These all seem to relate to using "order by" or "group by" clauses ,neither
>of which apply to this problem.
>
>the problem I'm sing ot resolve is how to append a whereclause to a
>string so it can be executed.
>The code works fine if I place the where clause in statically, but returns
>null if I try to use it in a dynamic format such as:
>...
>SET @.SQLString1 = 'SELECT ' + @.List + ' = COALESCE( ' + @.List + ' + '', '',
>'''') + CAST(InvoiceID AS varchar(5))'
>SET @.SQLString1 = @.SQLString1 + ' FROM invoices, Projects '
>SET @.SQLString1 = @.SQLString1 + ' WHERE Projects.ProjectID =
>Invoices.IDProject ' + @.WhereClause
>SET @.S1 = CAST(@.SQLString1 as NVarchar(1000))
>-- return results
>-- ---
---
>EXECUTE sp_executesql @.S1
>return @.list
>
>|||thanks for replying
Just trying to get a solution and haven't found any other way of getting to
the outcome
I know what values are going into the query, but there are a matrix of
options and I need to build a whereclause to return a correct set of comma
delimited values. When the string @.S1 is executed isn't @.list recognized as
being in there since it was created as part of the string ?
"Steve Kass" <skass@.drew.edu> wrote in message
news:u20DGl%23aFHA.3684@.TK2MSFTNGP12.phx.gbl...
> This is a terrible idea. You are executing a query without looking at it,
> and
> if you let a user create the @.WhereClause variable, you are executing a
> query
> someone else wrote without looking at it. It's about as smart as a
> self-serve
> cash register nobody watches.
> At the very least, one way to find out what this isn't working is to
> actually
> look at the query instead of executing it and puzzling over the error
> message
> or wrong results.
> Usually that helps.
> There are at least two reasons why
> EXECUTE sp_executesql @.s1
> will never modify the value of @.List, and you should notice at least one
> of them: that the query you execute doesn't say @.List in it.
> Steve Kass
> Drew University
> TJS wrote:
>|||
TJS wrote:

>thanks for replying
>Just trying to get a solution and haven't found any other way of getting to
>the outcome
>I know what values are going into the query, but there are a matrix of
>options and I need to build a whereclause to return a correct set of comma
>delimited values. When the string @.S1 is executed isn't @.list recognized a
s
>being in there since it was created as part of the string ?
>
>
No.
SK

>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:u20DGl%23aFHA.3684@.TK2MSFTNGP12.phx.gbl...
>
>
>

Convert string into datetime with timezone

I have tried to execute the following sql statement to convert a string with time zone information to datetime without success:

SELECT CONVERT(datetime, '2007-07-24T14:00:00+07:00', 126);

Is there any advice? I'm SQL Server Express 2005 as follows:

Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16473
Microsoft .NET Framework 2.0.50727.1318
Operating System 6.0.6000

Thanks,

Jong

You can only parse it. SQL doesn't recognize that as a legit date format.

You might try this:

Code Snippet

declare @.strDate varchar(255)

set @.strDate = '2007-07-24T14:00:00+07:00'

select convert(datetime,left(@.strDate,len(@.strDate) - patindex('%[+-]%',reverse(@.strDate))))

YOu have to reverse the string before Patindex becuase you need to allow for + or - and of course, there are dashes after the year.

Alternativly, if you can always rely on the exact format:

Code Snippet

declare @.strDate varchar(255)

set @.strDate = '2007-07-24T14:00:00+07:00'

select convert(datetime,left(@.strDate,19))

Neither of these converts the time to GMT...you would have to further parse and then use DateAdd.|||

Here is an illustration of one possible way to handle this issue.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyDate varchar(30)
)

INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00+07:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00+11:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00-02:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00-11:00' )

SELECT
RowID,
LocalTime = dateadd( hour, cast( substring( MyDate, 20, 3 ) AS int ), convert( datetime, left( MyDate, 19 )))
FROM @.MyTable

RowID LocalTime
-- --
1 2007-07-24 21:00:00.000
2 2007-07-25 01:00:00.000
3 2007-07-24 12:00:00.000
4 2007-07-24 03:00:00.000

|||

rusag2 wrote:

You can only parse it. SQL doesn't recognize that as a legit date format.

I thought SQL Server 2005 should recognize it as instructed in http://msdn2.microsoft.com/en-us/library/ms187928.aspx. I'm not sure if there is any difference between SQL Server 2005 and SQL Server Express 2005.

Anyway, thanks for answering.

|||

SQL Server 2005 Xquery includes support for dateTime values with timezone information. So you can use the built-in xquery functions to do the appropriate conversion. You simply pass the datetime string value to one of the xquery methods like value and do the conversion. Below are some examples:

Code Snippet

select cast('' as xml).value('xs:dateTime("2007-07-24T14:00:00+07:00")', 'datetime')

declare @.d varchar(30);
set @.d = '2007-07-24T14:00:00+07:00'
select cast('' as xml).value('xs:dateTime(sql:variable("@.d"))', 'datetime')

select cast('' as xml).value('xs:dateTime(sql:column("t.d"))', 'datetime')

from (select '2007-07-24T14:00:00+07:00') as t(d)

Convert report in PDF Format

Hi,

I have three differents sub-reports, each one inside a table object.

Each table has a property "page break-before" enable.

When I execute the view-report in HTML 4.0 format, the reports are executed with success, but when I export to PDF format, the property "page break-before" inserts a blank page at the end of each sub-report. How can I do to suppress the blank-pages when exporting to PDF format ?

Hi Nitini-

Without the .rdl I can't be sure, but here are few things to check. In general be aware that the behavior for defining pages is different between HTML and PDF. PDF has a strict restriction on where page breaks are defined, while HTML can be broken (essentially) at any point.

(1) The page break is defined at the table vs. the table group level.
(2) Blank space - often times unintentionally having blank space can lead to extra pages (as blacnk space is preserved).
(3) Note that page breaks in subreports are not used.
(4) Overlapping items, HTML will move items that are overlapping, PDF will not.

Hope that helps,
-JonHP

convert hex string to hex literal

how can i tell sql server to execute the "convert" function using the
literal hex value, 0xC0, rather than the string value '0xC0'?
for example, this works:
declare @.myhexint as int
set @.myhexint = 0xC0
select convert(int, @.myhexint)
but this does not:
declare @.myvarchar as varchar(20)
set @.myvarchar = '0xC0'
select convert(int, @.myvarchar)
i need to somehow convert a varchar representation of a hex string, to a
literal hex value..
tia,
jtI have copied an old response to this same question, below my signature
line.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
Itzik Ben-Gan
Oct 29 2002, 11:44 am show options
Newsgroups: microsoft.public.sqlserver.programming
From: "Itzik Ben-Gan" <itz...@.hi-tech.co.il> -
Date: Tue, 29 Oct 2002 17:27:43 +0200
Local: Tues, Oct 29 2002 7:27 am
Subject: Re: Convert hex (stored as string) to string?
If you're using SQL Server 2000, you can use the following function:
CREATE FUNCTION dbo.fn_chartobin
(
@.hexstr AS varchar(8000)
)
RETURNS varbinary(4000)
AS
BEGIN
IF @.hexstr IS NULL RETURN NULL
DECLARE
@.curbyte AS int,
@.varbin AS varbinary(4000)
IF @.hexstr LIKE '0x%' SET @.hexstr = SUBSTRING(@.hexstr, 3, 8000)
SET @.hexstr =
CASE LEN(@.hexstr) % 2 WHEN 1 THEN '0' ELSE '' END + @.hexstr
SET @.varbin = 0x
SET @.curbyte = LEN(@.hexstr) / 2
WHILE @.curbyte > 0
BEGIN
SET @.varbin =
CAST(
CASE SUBSTRING(@.hexstr, @.curbyte * 2, 1)
WHEN '0' THEN 0x00
WHEN '1' THEN 0x01
WHEN '2' THEN 0x02
WHEN '3' THEN 0x03
WHEN '4' THEN 0x04
WHEN '5' THEN 0x05
WHEN '6' THEN 0x06
WHEN '7' THEN 0x07
WHEN '8' THEN 0x08
WHEN '9' THEN 0x09
WHEN 'A' THEN 0x0A
WHEN 'B' THEN 0x0B
WHEN 'C' THEN 0x0C
WHEN 'D' THEN 0x0D
WHEN 'E' THEN 0x0E
WHEN 'F' THEN 0x0F
END |
CAST(
CASE SUBSTRING(@.hexstr, @.curbyte * 2 - 1, 1)
WHEN '0' THEN 0x00
WHEN '1' THEN 0x10
WHEN '2' THEN 0x20
WHEN '3' THEN 0x30
WHEN '4' THEN 0x40
WHEN '5' THEN 0x50
WHEN '6' THEN 0x60
WHEN '7' THEN 0x70
WHEN '8' THEN 0x80
WHEN '9' THEN 0x90
WHEN 'A' THEN 0xA0
WHEN 'B' THEN 0xB0
WHEN 'C' THEN 0xC0
WHEN 'D' THEN 0xD0
WHEN 'E' THEN 0xE0
WHEN 'F' THEN 0xF0
END AS tinyint) AS binary(1))
+ @.varbin
SET @.curbyte = @.curbyte - 1
END
RETURN @.varbin
END
GO
-- test
SELECT dbo. fn_chartobin('0x2056697361205061796D656E
747878787800')
0x2056697361205061796D656E747878787800
BG
SQL Server MVP|||awesome! just what i needed- thanks a million..
jt
"Jeff A. Stucker (MVP)" <jeff@.mobilize.net> wrote in message
news:utznjZ0FGHA.1424@.TK2MSFTNGP12.phx.gbl...
>I have copied an old response to this same question, below my signature
>line.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Senior Consultant
> www.rapidigm.com
> Itzik Ben-Gan
> Oct 29 2002, 11:44 am show options
> Newsgroups: microsoft.public.sqlserver.programming
> From: "Itzik Ben-Gan" <itz...@.hi-tech.co.il> -
> Date: Tue, 29 Oct 2002 17:27:43 +0200
> Local: Tues, Oct 29 2002 7:27 am
> Subject: Re: Convert hex (stored as string) to string?
>
> If you're using SQL Server 2000, you can use the following function:
>
> CREATE FUNCTION dbo.fn_chartobin
> (
> @.hexstr AS varchar(8000)
> )
> RETURNS varbinary(4000)
> AS
> BEGIN
>
> IF @.hexstr IS NULL RETURN NULL
>
> DECLARE
> @.curbyte AS int,
> @.varbin AS varbinary(4000)
>
> IF @.hexstr LIKE '0x%' SET @.hexstr = SUBSTRING(@.hexstr, 3, 8000)
>
> SET @.hexstr =
> CASE LEN(@.hexstr) % 2 WHEN 1 THEN '0' ELSE '' END + @.hexstr
>
> SET @.varbin = 0x
> SET @.curbyte = LEN(@.hexstr) / 2
>
> WHILE @.curbyte > 0
> BEGIN
> SET @.varbin =
> CAST(
> CASE SUBSTRING(@.hexstr, @.curbyte * 2, 1)
> WHEN '0' THEN 0x00
> WHEN '1' THEN 0x01
> WHEN '2' THEN 0x02
> WHEN '3' THEN 0x03
> WHEN '4' THEN 0x04
> WHEN '5' THEN 0x05
> WHEN '6' THEN 0x06
> WHEN '7' THEN 0x07
> WHEN '8' THEN 0x08
> WHEN '9' THEN 0x09
> WHEN 'A' THEN 0x0A
> WHEN 'B' THEN 0x0B
> WHEN 'C' THEN 0x0C
> WHEN 'D' THEN 0x0D
> WHEN 'E' THEN 0x0E
> WHEN 'F' THEN 0x0F
> END |
> CAST(
> CASE SUBSTRING(@.hexstr, @.curbyte * 2 - 1, 1)
> WHEN '0' THEN 0x00
> WHEN '1' THEN 0x10
> WHEN '2' THEN 0x20
> WHEN '3' THEN 0x30
> WHEN '4' THEN 0x40
> WHEN '5' THEN 0x50
> WHEN '6' THEN 0x60
> WHEN '7' THEN 0x70
> WHEN '8' THEN 0x80
> WHEN '9' THEN 0x90
> WHEN 'A' THEN 0xA0
> WHEN 'B' THEN 0xB0
> WHEN 'C' THEN 0xC0
> WHEN 'D' THEN 0xD0
> WHEN 'E' THEN 0xE0
> WHEN 'F' THEN 0xF0
> END AS tinyint) AS binary(1))
> + @.varbin
> SET @.curbyte = @.curbyte - 1
> END
>
> RETURN @.varbin
>
> END
> GO
>
> -- test
> SELECT dbo. fn_chartobin('0x2056697361205061796D656E
747878787800')
>
> 0x2056697361205061796D656E747878787800
>
> --
> BG
> SQL Server MVP
>|||not longer ago than yesterday i needed something similar..
there is a nice technique here
[url]http://milambda.blogspot.com/2005/08/when-binary-values-come-as-characters.html[/u
rl] -
thanks ML
Peter