After I install SP1 of SP2 for reporting services, I get the following
error:
The version of the report server database is either in a format that is
not
valid, or it cannot be read. The found version is 'C.0.6.54'. The
expected
version is 'C.0.6.43'. To continue, update the version of the report
server
database and verify access rights. (rsInvalidReportServerDatabase)
I am currently running RS enteprise edition with SQL server 2000
Standard edition.
When I used to have RS std. edition with SQL server 2000 std.edition,
I've applied both service packs and didn't get the error.
Can you help me with this problem?
Thanks,If you already installed SP2, you can not install SP1 afterwards and
you do not need to.
Service Packs are cumulative - they contain everything from prior
service packs.
bevarg wrote:
> After I install SP1 of SP2 for reporting services, I get the following
> error:
> The version of the report server database is either in a format that is
> not
> valid, or it cannot be read. The found version is 'C.0.6.54'. The
> expected
> version is 'C.0.6.43'. To continue, update the version of the report
> server
> database and verify access rights. (rsInvalidReportServerDatabase)
> I am currently running RS enteprise edition with SQL server 2000
> Standard edition.
> When I used to have RS std. edition with SQL server 2000 std.edition,
> I've applied both service packs and didn't get the error.
> Can you help me with this problem?
> Thanks,
Showing posts with label format. Show all posts
Showing posts with label format. Show all posts
Friday, March 30, 2012
Monday, March 19, 2012
Help! How do I create an SQL database on my machine? + Access -> SQL ?
According to my technical support for web hosting, for database format they only use "MS SQL" operating on a windows system, usualy clients create they're sites on their local machines using "MS SQL".
I'm just wondering....
Is 'MS SQL' free? where can I get it?
I also have the option of creating the site round an access databse but I'm worried about the security issues,
do you think it would be wise to use access?
Is it easy to convert a site written in ASP.NET&ACCESS into a site using ASP.NET&"ms sql"?
because then I could code a site in the former on my machine and then change the code to link to an sql server.when my suport persons talking about "MS SQL" does he actually mean "msde"?
I've been having some trouble with that too:
http://www.gotdotnet.com/Community/MessageBoard/Thread.aspx?id=181961&Page=1#183129|||'MS SQL Server' is not free. The good news are that MS has released a scaled down version of MS Sql Server which is. It's called MSDE (MS Desktop Edition) and is ideal for developing on your local machine, as well as OK for low traffic production servers. Download ithere.|||thanks for the reply andre,
that last post went up before I read your replied,
MSDE wont install because it says I need a strong SA password but I have no idea how to set one up for my account.|||more info, I think i downloaded the wrong package file before "sql2kdesksp3" I'm now downloading sql2ksp3 is that the right onw? is the other stuff I need to download to?|||downloading msde2000a now aswell|||its still having the same SAPWD option,
Is there a detailed explanation anywhere on using the SAPWD switch or have I goy my wires crossed.
Friday, February 24, 2012
Help with xp_sendmail needed!
This is a multi-part message in MIME format.
--=_NextPart_000_0006_01C4F641.316CAF50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello,
I am using xp_sendmail SP to send emails to myself, not to a = distribution list. I receive the error: Server: Msg 18031, Level 16, = State 1, Line 0
xp_sendmail: Could not resolve recipient
The server is a MS Windows 2003 running SQL 2000 and Office XP SP3. The = OS, SQL and Office are fully updated. I do not think that this problem = is related to the bug described in Article ID:315666.
May I ask you for some help?
Thank you,
Ovidiu Tanjala
--=_NextPart_000_0006_01C4F641.316CAF50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
I am using xp_sendmail SP to send emails = to myself, not to a distribution list. I receive the error: Server: Msg 18031, = Level 16, State 1, Line 0
xp_sendmail: Could not resolve = recipient
The server is a MS Windows 2003 running = SQL 2000 and Office XP SP3. The OS, SQL and Office are fully updated. I do not think = that this problem is related to the bug described in Article = ID:315666.
May I ask you for some help?
Thank you,
Ovidiu Tanjala =
--=_NextPart_000_0006_01C4F641.316CAF50--Hi Ovidiu,
Are you able to execute xp_stopmail and xp_startmail successfully? Also
please post your xp_sendmail statement, which resulted in this error.
--
Thanks
Yogish
--=_NextPart_000_0006_01C4F641.316CAF50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello,
I am using xp_sendmail SP to send emails to myself, not to a = distribution list. I receive the error: Server: Msg 18031, Level 16, = State 1, Line 0
xp_sendmail: Could not resolve recipient
The server is a MS Windows 2003 running SQL 2000 and Office XP SP3. The = OS, SQL and Office are fully updated. I do not think that this problem = is related to the bug described in Article ID:315666.
May I ask you for some help?
Thank you,
Ovidiu Tanjala
--=_NextPart_000_0006_01C4F641.316CAF50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello,
I am using xp_sendmail SP to send emails = to myself, not to a distribution list. I receive the error: Server: Msg 18031, = Level 16, State 1, Line 0
xp_sendmail: Could not resolve = recipient
The server is a MS Windows 2003 running = SQL 2000 and Office XP SP3. The OS, SQL and Office are fully updated. I do not think = that this problem is related to the bug described in Article = ID:315666.
May I ask you for some help?
Thank you,
Ovidiu Tanjala =
--=_NextPart_000_0006_01C4F641.316CAF50--Hi Ovidiu,
Are you able to execute xp_stopmail and xp_startmail successfully? Also
please post your xp_sendmail statement, which resulted in this error.
--
Thanks
Yogish
Sunday, February 19, 2012
Help with varchar to date
I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived form the
column above. So I tried the following
CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
RETURNS smalldatetime
AS
BEGIN
--want to be sure it is interpreted as dd-mm-yy format
RETURN CONVERT(smalldatetime,
'01-' +
CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END
+ '-' + Right(@.DATETEXT, 2))
END
I then try to execute the following:
UPDATE Credit_Card
SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
WHERE LEN(Expiry_Date) = 5 --jic a bad field
And get the following error:
Syntax error converting character string to smalldatetime data type
The function does return correctly. Can anyone give me some help in getting
this working. ThanksCan you show a simplified example, e.g. your table structure, and 3 or 4
rows of sample data that cause the failure.
This is just one of the dozens of problems with choosing the wrong data
type. Another big one with your function specifically:
You're checking for left(@.datetext,2) but then saying WHEN 1 -- two problems
here, one is that 1 is not a string ('1' would be) and unless it is november
or december, I am sure that Left(@.DateText, 2) yields two characters (only
one of which is the month). How on earth do you distinguish between 11206
(jan 12 06) and 11206 (nov 2 06)? How about 1006 (nov 06) vs. 106 (jan 06)?
Again, some sample data that causes the problem would be useful. But more
importantly, before trying to debug a function, get a query running that
does what you want (but without the convert to smalldatetime). That makes
it much easier to debug and figure out which rows are not producing valid
dates.
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||Harry
> --want to be sure it is interpreted as dd-mm-yy format
Make sure that the format you are converting to is YYYYMMDD
CREATE TABLE Credit_Card (dt VARCHAR(5))
INSERT INTO Credit_Card SELECT '10/06'
SELECT * FROM Credit_Card
UPDATE Credit_Card SET dt= CAST(CONVERT(CHAR(6),GETDATE(),112)+'01'
AS
DATETIME)
It retruns just 'June'
How about to alter the table and change the datatype's column or expand it
to varchar(50) for instance
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Can you show a simplified example, e.g. your table structure, and 3 or 4
> rows of sample data that cause the failure.
> This is just one of the dozens of problems with choosing the wrong data
> type. Another big one with your function specifically:
> You're checking for left(@.datetext,2) but then saying WHEN 1 -- two
> problems
Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
'01'
The function DOES work correctly and returns from a parameter of '06/06'
the result '2006-06-01 00:00:00'
TRY the function!
> here, one is that 1 is not a string ('1' would be) and unless it is
> november or december, I am sure that Left(@.DateText, 2) yields two
> characters (only one of which is the month). How on earth do you
> distinguish between 11206 (jan 12 06) and 11206 (nov 2 06)? How about
> 1006 (nov 06) vs. 106 (jan 06)?
READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
I am prepending '01-' for the day in the function.
The reason I am converting the LEFT 2 characters to a tinyint for use in the
CASE statement
e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
in dd-MMM-yy format
so the convert function will not be
between an Australian date
format and a US format.
> Again, some sample data that causes the problem would be useful. But more
> importantly, before trying to debug a function, get a query running that
> does what you want (but without the convert to smalldatetime). That makes
> it much easier to debug and figure out which rows are not producing valid
> dates.
AGAIN, the function works - READ what I have written. Further to this I have
given you sample data!
"I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived from the
column above. So I tried the following"
PLEASE read and understand the question.
>
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>|||"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
Perhaps I had better explain further:
I have a table with a number of columns, one of which is called
"Expiry_Date" - varchar(5) which stores string values in the format mm/yy
e.g '06/06' or '12/06' as we all see as the expiry date on a credit card. I
now need to know in advance if a crediy card is going to expire. The current
format makes that very difficult. So I am trying the following:
I have added another column called "CardExpiry" which is smalldatetime. I
want to update this column from values contained in the "Expiry_Date"
column. Obviously I have to convert the string value to a smalldatetime
value first. That is why I created the function above. Even though the
function returns a smalldatetime value, sql server (2000) still thinks the
output of the function is a character string.|||> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
CREATE TABLE Credit_Card (Expiry_Date VARCHAR(5),CardExpiry SMALLDATETIME)
INSERT INTO Credit_Card (Expiry_Date) SELECT '10/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '09/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '01/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '02/06'
SELECT * FROM Credit_Card
--Now we are going to update CardExpiry column
UPDATE Credit_Card SET CardExpiry= CAST('20'+RIGHT(Expiry_Date,2)+ LEFT
(Expiry_Date,2) +'01' AS SMALLDATETIME)
SELECT * FROM Credit_Card
DROP TABLE Credit_Card
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:1Jqmg.14185$ap3.3358@.news-server.bigpond.net.au...
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
> I now need to know in advance if a crediy card is going to expire. The
> current format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>|||This alteration to your function shuold clear up the error you are receiving
.
It returns a alpha month, 4 digit year, and the first of the month. Bad and
non-confirming parameters will return NULL. You can then easily find the bad
data.
CREATE FUNCTION dbo.StringToDate
( @.DateText varchar(11) )
RETURNS datetime
AS
BEGIN
IF len( @.DateText ) < 5
RETURN NULL
SET @.DateText =
CASE left( @.DateText, 2 )
WHEN '01' THEN replace( @.DateText, '01/', '01/Jan/' )
WHEN '02' THEN replace( @.DateText, '02/', '01/Feb/' )
WHEN '03' THEN replace( @.DateText, '03/', '01/Mar/' )
WHEN '04' THEN replace( @.DateText, '04/', '01/Apr/' )
WHEN '05' THEN replace( @.DateText, '05/', '01/May/' )
WHEN '06' THEN replace( @.DateText, '06/', '01/Jun/' )
WHEN '07' THEN replace( @.DateText, '07/', '01/Jul/' )
WHEN '08' THEN replace( @.DateText, '08/', '01/Aug/' )
WHEN '09' THEN replace( @.DateText, '09/', '01/Sep/' )
WHEN '10' THEN replace( @.DateText, '10/', '01/Oct/' )
WHEN '11' THEN replace( @.DateText, '11/', '01/Nov/' )
WHEN '12' THEN replace( @.DateText, '12/', '01/Dec/' )
END
IF @.DateText LIKE '%/0%'
RETURN ( replace( @.DateText, '/0', '/200' ))
IF @.DateText LIKE '%/1%'
RETURN ( replace( @.DateText, '/1', '/201' ))
RETURN NULL
END
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message news:qComg.3523$b6.86616
@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
> hold credit card expiry dates in mm/yy format e.g 10/06
>
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
>
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
>
> END
>
> I then try to execute the following:
>
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
>
> And get the following error:
>
> Syntax error converting character string to smalldatetime data type
>
> The function does return correctly. Can anyone give me some help in gettin
g
> this working. Thanks
>
>|||Apparently the function does NOT work -you are getting an error!
I'm now.
Does the function NOT work properly and you are asking for our help,
OR
Does the function work properly and you are posting here for -what was that
reason again?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:Svqmg.14176$ap3.2372@.news-server.bigpond.net.au...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
> '01'
> The function DOES work correctly and returns from a parameter of '06/06'
> the result '2006-06-01 00:00:00'
> TRY the function!
>
> READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
> I am prepending '01-' for the day in the function.
> The reason I am converting the LEFT 2 characters to a tinyint for use in
> the CASE statement
> e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
> in dd-MMM-yy format
> so the convert function will not be between an Australian date
> format and a US format.
>
> AGAIN, the function works - READ what I have written. Further to this I
> have given you sample data!
> "I want to convert a varchar(5) field to smalldatetime. I have a column
> that
> hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived from the
> column above. So I tried the following"
> PLEASE read and understand the question.
>
>|||Could it be that you have bad data in your table, for example an expiry date
of 13/06?
Chris
"Still Love VB6" wrote:
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
I
> now need to know in advance if a crediy card is going to expire. The curre
nt
> format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>
>|||> Perhaps I had better explain further:
Yes, that would be a good start!
> Even though the function returns a smalldatetime value, sql server (2000)
> still thinks the output of the function is a character string.
No, that is not what is happening at all.
You have some "dates" in your table where they aren't really dates. I can
think of hundreds of examples, since you allow varchar(5) in there, there is
no easy way to make them conform to any date format, so your table is
probably full of crap. It may be one row that is causing your function to
fail; it may be all rows! Who knows?
Do you see, now, the importance of sample data!?
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived form the
column above. So I tried the following
CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
RETURNS smalldatetime
AS
BEGIN
--want to be sure it is interpreted as dd-mm-yy format
RETURN CONVERT(smalldatetime,
'01-' +
CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END
+ '-' + Right(@.DATETEXT, 2))
END
I then try to execute the following:
UPDATE Credit_Card
SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
WHERE LEN(Expiry_Date) = 5 --jic a bad field
And get the following error:
Syntax error converting character string to smalldatetime data type
The function does return correctly. Can anyone give me some help in getting
this working. ThanksCan you show a simplified example, e.g. your table structure, and 3 or 4
rows of sample data that cause the failure.
This is just one of the dozens of problems with choosing the wrong data
type. Another big one with your function specifically:
You're checking for left(@.datetext,2) but then saying WHEN 1 -- two problems
here, one is that 1 is not a string ('1' would be) and unless it is november
or december, I am sure that Left(@.DateText, 2) yields two characters (only
one of which is the month). How on earth do you distinguish between 11206
(jan 12 06) and 11206 (nov 2 06)? How about 1006 (nov 06) vs. 106 (jan 06)?
Again, some sample data that causes the problem would be useful. But more
importantly, before trying to debug a function, get a query running that
does what you want (but without the convert to smalldatetime). That makes
it much easier to debug and figure out which rows are not producing valid
dates.
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||Harry
> --want to be sure it is interpreted as dd-mm-yy format
Make sure that the format you are converting to is YYYYMMDD
CREATE TABLE Credit_Card (dt VARCHAR(5))
INSERT INTO Credit_Card SELECT '10/06'
SELECT * FROM Credit_Card
UPDATE Credit_Card SET dt= CAST(CONVERT(CHAR(6),GETDATE(),112)+'01'
AS
DATETIME)
It retruns just 'June'
How about to alter the table and change the datatype's column or expand it
to varchar(50) for instance
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Can you show a simplified example, e.g. your table structure, and 3 or 4
> rows of sample data that cause the failure.
> This is just one of the dozens of problems with choosing the wrong data
> type. Another big one with your function specifically:
> You're checking for left(@.datetext,2) but then saying WHEN 1 -- two
> problems
Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
'01'
The function DOES work correctly and returns from a parameter of '06/06'
the result '2006-06-01 00:00:00'
TRY the function!
> here, one is that 1 is not a string ('1' would be) and unless it is
> november or december, I am sure that Left(@.DateText, 2) yields two
> characters (only one of which is the month). How on earth do you
> distinguish between 11206 (jan 12 06) and 11206 (nov 2 06)? How about
> 1006 (nov 06) vs. 106 (jan 06)?
READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
I am prepending '01-' for the day in the function.
The reason I am converting the LEFT 2 characters to a tinyint for use in the
CASE statement
e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
in dd-MMM-yy format
so the convert function will not be
between an Australian dateformat and a US format.
> Again, some sample data that causes the problem would be useful. But more
> importantly, before trying to debug a function, get a query running that
> does what you want (but without the convert to smalldatetime). That makes
> it much easier to debug and figure out which rows are not producing valid
> dates.
AGAIN, the function works - READ what I have written. Further to this I have
given you sample data!
"I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived from the
column above. So I tried the following"
PLEASE read and understand the question.
>
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>|||"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
Perhaps I had better explain further:
I have a table with a number of columns, one of which is called
"Expiry_Date" - varchar(5) which stores string values in the format mm/yy
e.g '06/06' or '12/06' as we all see as the expiry date on a credit card. I
now need to know in advance if a crediy card is going to expire. The current
format makes that very difficult. So I am trying the following:
I have added another column called "CardExpiry" which is smalldatetime. I
want to update this column from values contained in the "Expiry_Date"
column. Obviously I have to convert the string value to a smalldatetime
value first. That is why I created the function above. Even though the
function returns a smalldatetime value, sql server (2000) still thinks the
output of the function is a character string.|||> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
CREATE TABLE Credit_Card (Expiry_Date VARCHAR(5),CardExpiry SMALLDATETIME)
INSERT INTO Credit_Card (Expiry_Date) SELECT '10/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '09/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '01/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '02/06'
SELECT * FROM Credit_Card
--Now we are going to update CardExpiry column
UPDATE Credit_Card SET CardExpiry= CAST('20'+RIGHT(Expiry_Date,2)+ LEFT
(Expiry_Date,2) +'01' AS SMALLDATETIME)
SELECT * FROM Credit_Card
DROP TABLE Credit_Card
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:1Jqmg.14185$ap3.3358@.news-server.bigpond.net.au...
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
> I now need to know in advance if a crediy card is going to expire. The
> current format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>|||This alteration to your function shuold clear up the error you are receiving
.
It returns a alpha month, 4 digit year, and the first of the month. Bad and
non-confirming parameters will return NULL. You can then easily find the bad
data.
CREATE FUNCTION dbo.StringToDate
( @.DateText varchar(11) )
RETURNS datetime
AS
BEGIN
IF len( @.DateText ) < 5
RETURN NULL
SET @.DateText =
CASE left( @.DateText, 2 )
WHEN '01' THEN replace( @.DateText, '01/', '01/Jan/' )
WHEN '02' THEN replace( @.DateText, '02/', '01/Feb/' )
WHEN '03' THEN replace( @.DateText, '03/', '01/Mar/' )
WHEN '04' THEN replace( @.DateText, '04/', '01/Apr/' )
WHEN '05' THEN replace( @.DateText, '05/', '01/May/' )
WHEN '06' THEN replace( @.DateText, '06/', '01/Jun/' )
WHEN '07' THEN replace( @.DateText, '07/', '01/Jul/' )
WHEN '08' THEN replace( @.DateText, '08/', '01/Aug/' )
WHEN '09' THEN replace( @.DateText, '09/', '01/Sep/' )
WHEN '10' THEN replace( @.DateText, '10/', '01/Oct/' )
WHEN '11' THEN replace( @.DateText, '11/', '01/Nov/' )
WHEN '12' THEN replace( @.DateText, '12/', '01/Dec/' )
END
IF @.DateText LIKE '%/0%'
RETURN ( replace( @.DateText, '/0', '/200' ))
IF @.DateText LIKE '%/1%'
RETURN ( replace( @.DateText, '/1', '/201' ))
RETURN NULL
END
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message news:qComg.3523$b6.86616
@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
> hold credit card expiry dates in mm/yy format e.g 10/06
>
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
>
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
>
> END
>
> I then try to execute the following:
>
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
>
> And get the following error:
>
> Syntax error converting character string to smalldatetime data type
>
> The function does return correctly. Can anyone give me some help in gettin
g
> this working. Thanks
>
>|||Apparently the function does NOT work -you are getting an error!
I'm
now.Does the function NOT work properly and you are asking for our help,
OR
Does the function work properly and you are posting here for -what was that
reason again?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:Svqmg.14176$ap3.2372@.news-server.bigpond.net.au...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
> '01'
> The function DOES work correctly and returns from a parameter of '06/06'
> the result '2006-06-01 00:00:00'
> TRY the function!
>
> READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
> I am prepending '01-' for the day in the function.
> The reason I am converting the LEFT 2 characters to a tinyint for use in
> the CASE statement
> e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
> in dd-MMM-yy format
> so the convert function will not be
between an Australian date> format and a US format.
>
> AGAIN, the function works - READ what I have written. Further to this I
> have given you sample data!
> "I want to convert a varchar(5) field to smalldatetime. I have a column
> that
> hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived from the
> column above. So I tried the following"
> PLEASE read and understand the question.
>
>|||Could it be that you have bad data in your table, for example an expiry date
of 13/06?
Chris
"Still Love VB6" wrote:
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
I
> now need to know in advance if a crediy card is going to expire. The curre
nt
> format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>
>|||> Perhaps I had better explain further:
Yes, that would be a good start!
> Even though the function returns a smalldatetime value, sql server (2000)
> still thinks the output of the function is a character string.
No, that is not what is happening at all.
You have some "dates" in your table where they aren't really dates. I can
think of hundreds of examples, since you allow varchar(5) in there, there is
no easy way to make them conform to any date format, so your table is
probably full of crap. It may be one row that is causing your function to
fail; it may be all rows! Who knows?
Do you see, now, the importance of sample data!?
help with unusual victum messages
This is a multi-part message in MIME format.
--=_NextPart_000_0058_01C43F8A.9695C150
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I am finding some unusual deadlock victum messages in my errorlogs that = I have included in this message. I would appreciate any information = about these messages. I am familiar with the normal deadlock message, = it is the statements in red that I need help with.
thanks
tim
2004-05-21 16:07:58.18 spid4 Deadlock encountered ... Printing deadlock information
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Wait-for graph
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Node:1
2004-05-21 16:07:58.18 spid4 RID: 5:1:536178:4 = CleanCnt:1 Mode: X Flags: 0x2
2004-05-21 16:07:58.18 spid4 Grant List 3::
2004-05-21 16:07:58.18 spid4 Owner:0x68e15860 Mode: X = Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0
2004-05-21 16:07:58.18 spid4 SPID: 54 ECID: 0 Statement Type: = UPDATE Line #: 2
2004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: = dbo.NEXTKEYZZZbIGIp$7;1
2004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: U = SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Node:2
2004-05-21 16:07:58.18 spid4 KEY: 5:1685581043:2 (6401bf0349e3) = CleanCnt:1 Mode: U Flags: 0x0
2004-05-21 16:07:58.18 spid4 Grant List 2::
2004-05-21 16:07:58.18 spid4 Owner:0x70573a40 Mode: U = Flg:0x0 Ref:1 Life:00000000 SPID:51 ECID:0
2004-05-21 16:07:58.18 spid4 SPID: 51 ECID: 0 Statement Type: = UPDATE Line #: 2
2004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: = dbo.NEXTKEYZZZbIGIp$7;1
2004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: U = SPID:54 ECID:0 Ec:(0x7A6B59B0) Value:0x79f4cac0 Cost:(0/148)
2004-05-21 16:07:58.18 spid4 Victim Resource Owner:
2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: U = SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)
2004-05-21 16:11:50.78 spid4 Victim Resource Owner:
2004-05-21 16:11:50.78 spid4 ResType:ExchangeId Stype:'AND' SPID:69 = ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
2004-05-21 16:11:55.78 spid4 Victim Resource Owner:
2004-05-21 16:11:55.78 spid4 ResType:ExchangeId Stype:'AND' SPID:69 = ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
--=_NextPart_000_0058_01C43F8A.9695C150
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I am finding some unusual deadlock = victum messages in my errorlogs that I have included in this message. I would = appreciate any information about these messages. I am familiar with the = normal deadlock message, it is the statements in red that I need help with.
thanks
tim
2004-05-21 16:07:58.18 spid4 Deadlock encountered ... Printing = deadlock information2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Wait-for graph2004-05-21 16:07:58.18 spid4 = 2004-05-21 16:07:58.18 spid4 Node:12004-05-21 = 16:07:58.18 spid4 RID: 5:1:536178:4 &= nbsp; CleanCnt:1 Mode: X Flags: 0x22004-05-21 16:07:58.18 spid4 Grant List 3::2004-05-21 = 16:07:58.18 spid4 Owner:0x68e15860 Mode: X Flg:0x0 Ref:0 Life:02000000 = SPID:54 ECID:02004-05-21 16:07:58.18 = spid4 SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 22004-05-21 = 16:07:58.18 spid4 Input Buf: RPC Event: dbo.NEXTKEYZZZbIGIp$7;12004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 = 16:07:58.18 spid4 ResType:LockOwner = Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 = Cost:(0/0)2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Node:22004-05-21 16:07:58.18 spid4 KEY: 5:1685581043:2 (6401bf0349e3) = CleanCnt:1 Mode: U Flags: 0x02004-05-21 16:07:58.18 = spid4 Grant List 2::2004-05-21 16:07:58.18 spid4 Owner:0x70573a40 Mode: U Flg:0x0 Ref:1 Life:00000000 = SPID:51 ECID:02004-05-21 16:07:58.18 = spid4 SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 22004-05-21 = 16:07:58.18 spid4 Input Buf: RPC Event: dbo.NEXTKEYZZZbIGIp$7;12004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 = 16:07:58.18 spid4 ResType:LockOwner = Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x7A6B59B0) Value:0x79f4cac0 Cost:(0/148)2004-05-21 16:07:58.18 spid4 = Victim Resource Owner:2004-05-21 16:07:58.18 = spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)2004-05-21 = 16:11:50.78 spid4 Victim Resource Owner:2004-05-21 = 16:11:50.78 spid4 ResType:ExchangeId Stype:'AND' = SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)2004-05-21 = 16:11:55.78 spid4 Victim Resource Owner:2004-05-21 = 16:11:55.78 spid4 ResType:ExchangeId Stype:'AND' = SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
--=_NextPart_000_0058_01C43F8A.9695C150--This is a multi-part message in MIME format.
--=_NextPart_000_001F_01C44196.C17DBF40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
Looks like you may have trace flag 1204 set?
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/trblsq=
l/tr_servdatabse_5xrn.asp
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/trblsq=
l/tr_servdatabse_8v5f.asp
John
"Tim Graham" <tgraham3@.metlife.com> wrote in message =news:OtlW4R7PEHA.2704@.TK2MSFTNGP10.phx.gbl...
I am finding some unusual deadlock victum messages in my errorlogs =that I have included in this message. I would appreciate any =information about these messages. I am familiar with the normal =deadlock message, it is the statements in red that I need help with.
thanks
tim
2004-05-21 16:07:58.18 spid4 Deadlock encountered ... Printing deadlock information
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Wait-for graph
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Node:1
2004-05-21 16:07:58.18 spid4 RID: 5:1:536178:4 =CleanCnt:1 Mode: X Flags: 0x2
2004-05-21 16:07:58.18 spid4 Grant List 3::
2004-05-21 16:07:58.18 spid4 Owner:0x68e15860 Mode: X =Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0
2004-05-21 16:07:58.18 spid4 SPID: 54 ECID: 0 Statement Type: =UPDATE Line #: 2
2004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: =dbo.NEXTKEYZZZbIGIp$7;1
2004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: =U SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Node:2
2004-05-21 16:07:58.18 spid4 KEY: 5:1685581043:2 (6401bf0349e3) =CleanCnt:1 Mode: U Flags: 0x0
2004-05-21 16:07:58.18 spid4 Grant List 2::
2004-05-21 16:07:58.18 spid4 Owner:0x70573a40 Mode: U =Flg:0x0 Ref:1 Life:00000000 SPID:51 ECID:0
2004-05-21 16:07:58.18 spid4 SPID: 51 ECID: 0 Statement Type: =UPDATE Line #: 2
2004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: =dbo.NEXTKEYZZZbIGIp$7;1
2004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: =U SPID:54 ECID:0 Ec:(0x7A6B59B0) Value:0x79f4cac0 Cost:(0/148)
2004-05-21 16:07:58.18 spid4 Victim Resource Owner:
2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: U =SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)
2004-05-21 16:11:50.78 spid4 Victim Resource Owner:
2004-05-21 16:11:50.78 spid4 ResType:ExchangeId Stype:'AND' =SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
2004-05-21 16:11:55.78 spid4 Victim Resource Owner:
2004-05-21 16:11:55.78 spid4 ResType:ExchangeId Stype:'AND' =SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
--=_NextPart_000_001F_01C44196.C17DBF40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi
Looks like you may have trace flag =1204 set?
http://msdn.microsoft.com/library/defa=ult.asp?url=3D/library/en-us/trblsql/tr_servdatabse_5xrn.asp
http://msdn.microsoft.com/library/defa=ult.asp?url=3D/library/en-us/trblsql/tr_servdatabse_8v5f.asp
John
"Tim Graham" =wrote in message news:OtlW4R7PEHA.2704=@.TK2MSFTNGP10.phx.gbl...
I am finding some unusual deadlock =victum messages in my errorlogs that I have included in this message. I =would appreciate any information about these messages. I am familiar =with the normal deadlock message, it is the statements in red that I need help with.
thanks
tim
2004-05-21 16:07:58.18 spid4 Deadlock encountered ... Printing =deadlock information2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Wait-for graph2004-05-21 16:07:58.18 spid4 =2004-05-21 16:07:58.18 spid4 Node:12004-05-21 =16:07:58.18 spid4 RID: =5:1:536178:4 &=nbsp; CleanCnt:1 Mode: X Flags: 0x22004-05-21 16:07:58.18 spid4 Grant List 3::2004-05-21 =16:07:58.18 spid4 Owner:0x68e15860 Mode: = X Flg:0x0 Ref:0 =Life:02000000 SPID:54 ECID:02004-05-21 16:07:58.18 spid4 SPID: 54 ECID: 0 =Statement Type: UPDATE Line #: 22004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: dbo.NEXTKEYZZZbIGIp$7;12004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 =16:07:58.18 spid4 ResType:LockOwner =Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 =Node:22004-05-21 16:07:58.18 spid4 KEY: 5:1685581043:2 =(6401bf0349e3) CleanCnt:1 Mode: U Flags: 0x02004-05-21 16:07:58.18 spid4 Grant List 2::2004-05-21 =16:07:58.18 spid4 Owner:0x70573a40 Mode: = U Flg:0x0 Ref:1 =Life:00000000 SPID:51 ECID:02004-05-21 16:07:58.18 spid4 SPID: 51 ECID: 0 =Statement Type: UPDATE Line #: 22004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: dbo.NEXTKEYZZZbIGIp$7;12004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 =16:07:58.18 spid4 ResType:LockOwner =Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x7A6B59B0) Value:0x79f4cac0 Cost:(0/148)2004-05-21 16:07:58.18 spid4 =Victim Resource Owner:2004-05-21 16:07:58.18 =spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)2004-05-21 =16:11:50.78 spid4 Victim Resource Owner:2004-05-21 =16:11:50.78 spid4 ResType:ExchangeId Stype:'AND' =SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)2004-05-21 16:11:55.78 spid4 Victim Resource =Owner:2004-05-21 16:11:55.78 spid4 ResType:ExchangeId =Stype:'AND' SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
--=_NextPart_000_001F_01C44196.C17DBF40--
--=_NextPart_000_0058_01C43F8A.9695C150
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I am finding some unusual deadlock victum messages in my errorlogs that = I have included in this message. I would appreciate any information = about these messages. I am familiar with the normal deadlock message, = it is the statements in red that I need help with.
thanks
tim
2004-05-21 16:07:58.18 spid4 Deadlock encountered ... Printing deadlock information
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Wait-for graph
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Node:1
2004-05-21 16:07:58.18 spid4 RID: 5:1:536178:4 = CleanCnt:1 Mode: X Flags: 0x2
2004-05-21 16:07:58.18 spid4 Grant List 3::
2004-05-21 16:07:58.18 spid4 Owner:0x68e15860 Mode: X = Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0
2004-05-21 16:07:58.18 spid4 SPID: 54 ECID: 0 Statement Type: = UPDATE Line #: 2
2004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: = dbo.NEXTKEYZZZbIGIp$7;1
2004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: U = SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Node:2
2004-05-21 16:07:58.18 spid4 KEY: 5:1685581043:2 (6401bf0349e3) = CleanCnt:1 Mode: U Flags: 0x0
2004-05-21 16:07:58.18 spid4 Grant List 2::
2004-05-21 16:07:58.18 spid4 Owner:0x70573a40 Mode: U = Flg:0x0 Ref:1 Life:00000000 SPID:51 ECID:0
2004-05-21 16:07:58.18 spid4 SPID: 51 ECID: 0 Statement Type: = UPDATE Line #: 2
2004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: = dbo.NEXTKEYZZZbIGIp$7;1
2004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: U = SPID:54 ECID:0 Ec:(0x7A6B59B0) Value:0x79f4cac0 Cost:(0/148)
2004-05-21 16:07:58.18 spid4 Victim Resource Owner:
2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: U = SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)
2004-05-21 16:11:50.78 spid4 Victim Resource Owner:
2004-05-21 16:11:50.78 spid4 ResType:ExchangeId Stype:'AND' SPID:69 = ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
2004-05-21 16:11:55.78 spid4 Victim Resource Owner:
2004-05-21 16:11:55.78 spid4 ResType:ExchangeId Stype:'AND' SPID:69 = ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
--=_NextPart_000_0058_01C43F8A.9695C150
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I am finding some unusual deadlock = victum messages in my errorlogs that I have included in this message. I would = appreciate any information about these messages. I am familiar with the = normal deadlock message, it is the statements in red that I need help with.
thanks
tim
2004-05-21 16:07:58.18 spid4 Deadlock encountered ... Printing = deadlock information2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Wait-for graph2004-05-21 16:07:58.18 spid4 = 2004-05-21 16:07:58.18 spid4 Node:12004-05-21 = 16:07:58.18 spid4 RID: 5:1:536178:4 &= nbsp; CleanCnt:1 Mode: X Flags: 0x22004-05-21 16:07:58.18 spid4 Grant List 3::2004-05-21 = 16:07:58.18 spid4 Owner:0x68e15860 Mode: X Flg:0x0 Ref:0 Life:02000000 = SPID:54 ECID:02004-05-21 16:07:58.18 = spid4 SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 22004-05-21 = 16:07:58.18 spid4 Input Buf: RPC Event: dbo.NEXTKEYZZZbIGIp$7;12004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 = 16:07:58.18 spid4 ResType:LockOwner = Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 = Cost:(0/0)2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Node:22004-05-21 16:07:58.18 spid4 KEY: 5:1685581043:2 (6401bf0349e3) = CleanCnt:1 Mode: U Flags: 0x02004-05-21 16:07:58.18 = spid4 Grant List 2::2004-05-21 16:07:58.18 spid4 Owner:0x70573a40 Mode: U Flg:0x0 Ref:1 Life:00000000 = SPID:51 ECID:02004-05-21 16:07:58.18 = spid4 SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 22004-05-21 = 16:07:58.18 spid4 Input Buf: RPC Event: dbo.NEXTKEYZZZbIGIp$7;12004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 = 16:07:58.18 spid4 ResType:LockOwner = Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x7A6B59B0) Value:0x79f4cac0 Cost:(0/148)2004-05-21 16:07:58.18 spid4 = Victim Resource Owner:2004-05-21 16:07:58.18 = spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)2004-05-21 = 16:11:50.78 spid4 Victim Resource Owner:2004-05-21 = 16:11:50.78 spid4 ResType:ExchangeId Stype:'AND' = SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)2004-05-21 = 16:11:55.78 spid4 Victim Resource Owner:2004-05-21 = 16:11:55.78 spid4 ResType:ExchangeId Stype:'AND' = SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
--=_NextPart_000_0058_01C43F8A.9695C150--This is a multi-part message in MIME format.
--=_NextPart_000_001F_01C44196.C17DBF40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
Looks like you may have trace flag 1204 set?
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/trblsq=
l/tr_servdatabse_5xrn.asp
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/trblsq=
l/tr_servdatabse_8v5f.asp
John
"Tim Graham" <tgraham3@.metlife.com> wrote in message =news:OtlW4R7PEHA.2704@.TK2MSFTNGP10.phx.gbl...
I am finding some unusual deadlock victum messages in my errorlogs =that I have included in this message. I would appreciate any =information about these messages. I am familiar with the normal =deadlock message, it is the statements in red that I need help with.
thanks
tim
2004-05-21 16:07:58.18 spid4 Deadlock encountered ... Printing deadlock information
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Wait-for graph
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Node:1
2004-05-21 16:07:58.18 spid4 RID: 5:1:536178:4 =CleanCnt:1 Mode: X Flags: 0x2
2004-05-21 16:07:58.18 spid4 Grant List 3::
2004-05-21 16:07:58.18 spid4 Owner:0x68e15860 Mode: X =Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0
2004-05-21 16:07:58.18 spid4 SPID: 54 ECID: 0 Statement Type: =UPDATE Line #: 2
2004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: =dbo.NEXTKEYZZZbIGIp$7;1
2004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: =U SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)
2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Node:2
2004-05-21 16:07:58.18 spid4 KEY: 5:1685581043:2 (6401bf0349e3) =CleanCnt:1 Mode: U Flags: 0x0
2004-05-21 16:07:58.18 spid4 Grant List 2::
2004-05-21 16:07:58.18 spid4 Owner:0x70573a40 Mode: U =Flg:0x0 Ref:1 Life:00000000 SPID:51 ECID:0
2004-05-21 16:07:58.18 spid4 SPID: 51 ECID: 0 Statement Type: =UPDATE Line #: 2
2004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: =dbo.NEXTKEYZZZbIGIp$7;1
2004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: =U SPID:54 ECID:0 Ec:(0x7A6B59B0) Value:0x79f4cac0 Cost:(0/148)
2004-05-21 16:07:58.18 spid4 Victim Resource Owner:
2004-05-21 16:07:58.18 spid4 ResType:LockOwner Stype:'OR' Mode: U =SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)
2004-05-21 16:11:50.78 spid4 Victim Resource Owner:
2004-05-21 16:11:50.78 spid4 ResType:ExchangeId Stype:'AND' =SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
2004-05-21 16:11:55.78 spid4 Victim Resource Owner:
2004-05-21 16:11:55.78 spid4 ResType:ExchangeId Stype:'AND' =SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
--=_NextPart_000_001F_01C44196.C17DBF40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi
Looks like you may have trace flag =1204 set?
http://msdn.microsoft.com/library/defa=ult.asp?url=3D/library/en-us/trblsql/tr_servdatabse_5xrn.asp
http://msdn.microsoft.com/library/defa=ult.asp?url=3D/library/en-us/trblsql/tr_servdatabse_8v5f.asp
John
"Tim Graham"
I am finding some unusual deadlock =victum messages in my errorlogs that I have included in this message. I =would appreciate any information about these messages. I am familiar =with the normal deadlock message, it is the statements in red that I need help with.
thanks
tim
2004-05-21 16:07:58.18 spid4 Deadlock encountered ... Printing =deadlock information2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 Wait-for graph2004-05-21 16:07:58.18 spid4 =2004-05-21 16:07:58.18 spid4 Node:12004-05-21 =16:07:58.18 spid4 RID: =5:1:536178:4 &=nbsp; CleanCnt:1 Mode: X Flags: 0x22004-05-21 16:07:58.18 spid4 Grant List 3::2004-05-21 =16:07:58.18 spid4 Owner:0x68e15860 Mode: = X Flg:0x0 Ref:0 =Life:02000000 SPID:54 ECID:02004-05-21 16:07:58.18 spid4 SPID: 54 ECID: 0 =Statement Type: UPDATE Line #: 22004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: dbo.NEXTKEYZZZbIGIp$7;12004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 =16:07:58.18 spid4 ResType:LockOwner =Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)2004-05-21 16:07:58.18 spid4 2004-05-21 16:07:58.18 spid4 =Node:22004-05-21 16:07:58.18 spid4 KEY: 5:1685581043:2 =(6401bf0349e3) CleanCnt:1 Mode: U Flags: 0x02004-05-21 16:07:58.18 spid4 Grant List 2::2004-05-21 =16:07:58.18 spid4 Owner:0x70573a40 Mode: = U Flg:0x0 Ref:1 =Life:00000000 SPID:51 ECID:02004-05-21 16:07:58.18 spid4 SPID: 51 ECID: 0 =Statement Type: UPDATE Line #: 22004-05-21 16:07:58.18 spid4 Input Buf: RPC Event: dbo.NEXTKEYZZZbIGIp$7;12004-05-21 16:07:58.18 spid4 Requested By: 2004-05-21 =16:07:58.18 spid4 ResType:LockOwner =Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x7A6B59B0) Value:0x79f4cac0 Cost:(0/148)2004-05-21 16:07:58.18 spid4 =Victim Resource Owner:2004-05-21 16:07:58.18 =spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x7DD3D9B0) Value:0x705729e0 Cost:(0/0)2004-05-21 =16:11:50.78 spid4 Victim Resource Owner:2004-05-21 =16:11:50.78 spid4 ResType:ExchangeId Stype:'AND' =SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)2004-05-21 16:11:55.78 spid4 Victim Resource =Owner:2004-05-21 16:11:55.78 spid4 ResType:ExchangeId =Stype:'AND' SPID:69 ECID:22 Ec:(0x75A58098) Value:0x800f3988 Cost:(0/2710)
--=_NextPart_000_001F_01C44196.C17DBF40--
Subscribe to:
Posts (Atom)