Internet integration: weird

Showing posts with label weird. Show all posts

Friday, March 23, 2012

Invalid Object Name - Weird Error - Help!

I have a really strange problem. It seems a table has gone invisible. It's
listed in SysObjects, but does not show up in the tables list.
When I execte "Select * From Object_Access_Levels", it raises the error "Invalid
Object Name".
If I try to [Drop Table Object_Access_Levels], it returns the error:
"Cannot drop the table 'Object_Access_Levels' because it does not exist in the
system catalog."
If I follow the ID for the table to Syscolumns and SysIndexes, all the records
are there.
This is a complete show-stopper. I cannot continue development until this table
is restored.
Here's a copy of the record from sysobjects:
name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid,
schema_ver,stats_schema_ver,type,userstat,sysstat,indexdel,refdate,version,deltri
g,instrig,updtrig,seltrig,category,cache
"Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,"07/12/2002
02:09pm",0,272,0,"U ",1,115,0,"07/12/2002 02:09pm",0,,,,0,2560,0
Does anyone have a clue how I can fix this problem?
I am using SQL2K with SP3a.
TIA,
-Steve-Who is the owner of the object? I always make sure to create my tables this
way:
CREATE TABLE dbo.Object_Access_Levels
And always reference them with the prefix also:
SELECT * FROM dbo.Object_Access_Levels
DROP TABLE dbo.Object_Access_Levels
You can see the owner name by:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels'
If TABLE_SCHEMA is not dbo, you should drop and re-create the table as dbo,
and always refer to it with the dbo. prefix.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:e50xOxeYEHA.808@.tk2msftngp13.phx.gbl...
>I have a really strange problem. It seems a table has gone invisible.
>It's
> listed in SysObjects, but does not show up in the tables list.
> When I execte "Select * From Object_Access_Levels", it raises the error
> "Invalid
> Object Name".
> If I try to [Drop Table Object_Access_Levels], it returns the error:
> "Cannot drop the table 'Object_Access_Levels' because it does not exist in
> the
> system catalog."
> If I follow the ID for the table to Syscolumns and SysIndexes, all the
> records
> are there.
> This is a complete show-stopper. I cannot continue development until this
> table
> is restored.
> Here's a copy of the record from sysobjects:
> name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid,
> schema_ver,stats_schema_ver,type,userstat,sysstat,indexdel,refdate,version,deltri
> g,instrig,updtrig,seltrig,category,cache
> "Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,"07/12/2002
> 02:09pm",0,272,0,"U ",1,115,0,"07/12/2002 02:09pm",0,,,,0,2560,0
> Does anyone have a clue how I can fix this problem?
> I am using SQL2K with SP3a.
> TIA,
> -Steve-
>|||You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.
>--Original Message--
>I have a really strange problem. It seems a table has
gone invisible. It's
>listed in SysObjects, but does not show up in the tables
list.
>When I execte "Select * From Object_Access_Levels", it
raises the error "Invalid
>Object Name".
>If I try to [Drop Table Object_Access_Levels], it
returns the error:
> "Cannot drop the table 'Object_Access_Levels' because
it does not exist in the
>system catalog."
>If I follow the ID for the table to Syscolumns and
SysIndexes, all the records
>are there.
>This is a complete show-stopper. I cannot continue
development until this table
>is restored.
>Here's a copy of the record from sysobjects:
>name,id,xtype,uid,info,status,base_schema_ver,replinfo,pa
rent_obj,crdate,ftcatid,
>schema_ver,stats_schema_ver,type,userstat,sysstat,indexde
l,refdate,version,deltri
>g,instrig,updtrig,seltrig,category,cache
>"Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,
"07/12/2002
>02:09pm",0,272,0,"U ",1,115,0,"07/12/2002
02:09pm",0,,,,0,2560,0
>Does anyone have a clue how I can fix this problem?
>I am using SQL2K with SP3a.
>TIA,
>-Steve-
>
>.
>|||Thanks Aaron,
The table has been in use for over a year. Everything was fine last week during
development. Then yesterday, I tried to run the app and got the error during the
load.
This SQL command returns an empty result.
[SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels']
But as I stated, the table is listed in the SyObjects, SysIndexes, and
SysColumns.
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
It looks like SQL's internal table got hosed. Is there any way to fix this
without completely rebuilding the database?
-Steve-
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%239t8NTfYEHA.4004@.TK2MSFTNGP10.phx.gbl...
Who is the owner of the object? I always make sure to create my tables this
way:
CREATE TABLE dbo.Object_Access_Levels
And always reference them with the prefix also:
SELECT * FROM dbo.Object_Access_Levels
DROP TABLE dbo.Object_Access_Levels
You can see the owner name by:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels'
If TABLE_SCHEMA is not dbo, you should drop and re-create the table as dbo,
and always refer to it with the dbo. prefix.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi Mark,
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
Is this fixable without rebuilding the database?
Thanks,
-Steve-
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.|||BTW, I just checked the ID 1840725610, it doesn't belong the Object_Access_Levels
table. It belongs to a couple of params. I think this DB is really hosed!
-Steve-
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:%23JR2N$oYEHA.1764@.TK2MSFTNGP10.phx.gbl...
Hi Mark,
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
Is this fixable without rebuilding the database?
Thanks,
-Steve-
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.|||Steve,
Any idea what error message(s) you were getting? If
error 2513, check SQL Books Online for steps to possibly
resolve the inconsistency. Please ensure you have a
backup or copy of the .mdf & .ldf before you do anything.
HTH
Darren Fuller
>--Original Message--
>Hi Mark,
>Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
>Returns this error:
>[Table Corrupt: Object ID 1840725610
(object '1840725610') does not match between
>'SYSCOLUMNS' and 'SYSOBJECTS']
>Is this fixable without rebuilding the database?
>Thanks,
>-Steve-
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
>You may want to run DBCC CHECKCATALOG to check the system
>tables.
>Mark Baekdal
>www.dbghost.com - the only true Database Change Manager
>for SQL Server.
>
>.
>|||After much wasted time, I restored an old copy of the db. I was under the
impression (apparently a false one) that SQL server didn't have these types
corruption problems.
Thanks everyone for your suggestions.
-Steve-|||Your impression is not false, we don't have these types of corruption
problems.
However, hardware can and does introduce all manner of corruptions that
manifest themselves in various ways. I would check your NT event logs and
SQL errorlog for IO susbsystem errors. Before restoring from your backup, I
would have recommended running DBCC CHECKDB to check for other corruptions.
In future, to avoid wasting time, you should call Product Support who will
be able to help you pinpoint the problem very quickly.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:ermVPB6YEHA.1048@.tk2msftngp13.phx.gbl...
> After much wasted time, I restored an old copy of the db. I was under the
> impression (apparently a false one) that SQL server didn't have these
types
> corruption problems.
> Thanks everyone for your suggestions.
> -Steve-
>

Invalid Object Name - Weird Error - Help!

You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.

>--Original Message--
>I have a really strange problem. It seems a table has
gone invisible. It's
>listed in SysObjects, but does not show up in the tables
list.
>When I execte "Select * From Object_Access_Levels", it
raises the error "Invalid
>Object Name".
>If I try to [Drop Table Object_Access_Levels], it
returns the error:
> "Cannot drop the table 'Object_Access_Levels' because
it does not exist in the
>system catalog."
>If I follow the ID for the table to Syscolumns and
SysIndexes, all the records
>are there.
>This is a complete show-stopper. I cannot continue
development until this table
>is restored.
>Here's a copy of the record from sysobjects:
> name,id,xtype,uid,info,status,base_schem
a_ver,replinfo,pa
rent_obj,crdate,ftcatid,
> schema_ver,stats_schema_ver,type,usersta
t,sysstat,indexde
l,refdate,version,deltri
> g,instrig,updtrig,seltrig,category,cache
>"Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,
"07/12/2002
>02:09pm",0,272,0,"U ",1,115,0,"07/12/2002
02:09pm",0,,,,0,2560,0
>Does anyone have a clue how I can fix this problem?
>I am using SQL2K with SP3a.
>TIA,
>-Steve-
>
>.
>Hi Mark,
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not matc
h between
'SYSCOLUMNS' and 'SYSOBJECTS']
Is this fixable without rebuilding the database?
Thanks,
-Steve-
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:26ebb01c46255$b2dbd4b0$a301280a@.phx
.gbl...
You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.|||BTW, I just checked the ID 1840725610, it doesn't belong the Object_Access_L
evels
table. It belongs to a couple of params. I think this DB is really hosed!
-Steve-
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:%23JR2N$oYEHA.1764@.TK2MSFTNGP10.phx.gbl...
Hi Mark,
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not matc
h between
'SYSCOLUMNS' and 'SYSOBJECTS']
Is this fixable without rebuilding the database?
Thanks,
-Steve-
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:26ebb01c46255$b2dbd4b0$a301280a@.phx
.gbl...
You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.|||Steve,
Any idea what error message(s) you were getting? If
error 2513, check SQL Books Online for steps to possibly
resolve the inconsistency. Please ensure you have a
backup or copy of the .mdf & .ldf before you do anything.
HTH
Darren Fuller

>--Original Message--
>Hi Mark,
>Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
>Returns this error:
>[Table Corrupt: Object ID 1840725610
(object '1840725610') does not match between
>'SYSCOLUMNS' and 'SYSOBJECTS']
>Is this fixable without rebuilding the database?
>Thanks,
>-Steve-
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
> news:26ebb01c46255$b2dbd4b0$a301280a@.phx
.gbl...
>You may want to run DBCC CHECKCATALOG to check the system
>tables.
>Mark Baekdal
>www.dbghost.com - the only true Database Change Manager
>for SQL Server.
>
>.
>|||After much wasted time, I restored an old copy of the db. I was under the
impression (apparently a false one) that SQL server didn't have these types
corruption problems.
Thanks everyone for your suggestions.
-Steve-|||Your impression is not false, we don't have these types of corruption
problems.
However, hardware can and does introduce all manner of corruptions that
manifest themselves in various ways. I would check your NT event logs and
SQL errorlog for IO susbsystem errors. Before restoring from your backup, I
would have recommended running DBCC CHECKDB to check for other corruptions.
In future, to avoid wasting time, you should call Product Support who will
be able to help you pinpoint the problem very quickly.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:ermVPB6YEHA.1048@.tk2msftngp13.phx.gbl...
> After much wasted time, I restored an old copy of the db. I was under the
> impression (apparently a false one) that SQL server didn't have these
types
> corruption problems.
> Thanks everyone for your suggestions.
> -Steve-
>

Invalid Object Name - Weird Error - Help!

You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.

>--Original Message--
>I have a really strange problem. It seems a table has
gone invisible. It's
>listed in SysObjects, but does not show up in the tables
list.
>When I execte "Select * From Object_Access_Levels", it
raises the error "Invalid
>Object Name".
>If I try to [Drop Table Object_Access_Levels], it
returns the error:
> "Cannot drop the table 'Object_Access_Levels' because
it does not exist in the
>system catalog."
>If I follow the ID for the table to Syscolumns and
SysIndexes, all the records
>are there.
>This is a complete show-stopper. I cannot continue
development until this table
>is restored.
>Here's a copy of the record from sysobjects:
>name,id,xtype,uid,info,status,base_schema_ver,rep linfo,pa
rent_obj,crdate,ftcatid,
>schema_ver,stats_schema_ver,type,userstat,sysstat ,indexde
l,refdate,version,deltri
>g,instrig,updtrig,seltrig,category,cache
>"Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,
"07/12/2002
>02:09pm",0,272,0,"U ",1,115,0,"07/12/2002
02:09pm",0,,,,0,2560,0
>Does anyone have a clue how I can fix this problem?
>I am using SQL2K with SP3a.
>TIA,
>-Steve-
>
>.
>
Hi Mark,
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
Is this fixable without rebuilding the database?
Thanks,
-Steve-
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.
|||BTW, I just checked the ID 1840725610, it doesn't belong the Object_Access_Levels
table. It belongs to a couple of params. I think this DB is really hosed!
-Steve-
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:%23JR2N$oYEHA.1764@.TK2MSFTNGP10.phx.gbl...
Hi Mark,
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
Is this fixable without rebuilding the database?
Thanks,
-Steve-
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.
|||Steve,
Any idea what error message(s) you were getting? If
error 2513, check SQL Books Online for steps to possibly
resolve the inconsistency. Please ensure you have a
backup or copy of the .mdf & .ldf before you do anything.
HTH
Darren Fuller

>--Original Message--
>Hi Mark,
>Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
>Returns this error:
>[Table Corrupt: Object ID 1840725610
(object '1840725610') does not match between
>'SYSCOLUMNS' and 'SYSOBJECTS']
>Is this fixable without rebuilding the database?
>Thanks,
>-Steve-
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
>You may want to run DBCC CHECKCATALOG to check the system
>tables.
>Mark Baekdal
>www.dbghost.com - the only true Database Change Manager
>for SQL Server.
>
>.
>
|||After much wasted time, I restored an old copy of the db. I was under the
impression (apparently a false one) that SQL server didn't have these types
corruption problems.
Thanks everyone for your suggestions.
-Steve-
|||Your impression is not false, we don't have these types of corruption
problems.
However, hardware can and does introduce all manner of corruptions that
manifest themselves in various ways. I would check your NT event logs and
SQL errorlog for IO susbsystem errors. Before restoring from your backup, I
would have recommended running DBCC CHECKDB to check for other corruptions.
In future, to avoid wasting time, you should call Product Support who will
be able to help you pinpoint the problem very quickly.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:ermVPB6YEHA.1048@.tk2msftngp13.phx.gbl...
> After much wasted time, I restored an old copy of the db. I was under the
> impression (apparently a false one) that SQL server didn't have these
types
> corruption problems.
> Thanks everyone for your suggestions.
> -Steve-
>

Wednesday, March 21, 2012

Invalid length parameter passed to the SUBSTRING function

Hi all,

I am having a weird issue after we upgraded our DB server to SQL 2005.

I have a SP used to extract exchange rate, and a job calls this SP daily. This job worked fine on SQL 2000, and works very well in Management studio if I call this SP seperately, but failed in sql job in 2005.

The error statement pointed to:

select left(@.row, charindex(',', @.row)-1), REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1))

The error message is:

Invalid length parameter passed to the SUBSTRING function.

Anyone knows what's the difference for LEFT function between sql 2000 and 2005?

Thanks

Bill

I would imagine CHARINDEX is either returning a NULL or a 0 (see below). It may be better storing the resulting of the CHARINDEX in a variable before running the query (if it is the same for all values), or testing the value returned before doing the left. Or use ISNULL if it is returning NULL. I could not find any documentation suggesting differences between the function in 2000 vs 2005. Has your database compatibility level changed?

 Clarity Consulting (www.claritycon.com)
 http://blogs.claritycon.com/blogs/the_englishman/default.aspx
 Clarity Consulting (www.claritycon.com)
  
 CHARINDEX link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_left_7910.asp

CHARINDEX ( expression1 , expression2 [ , start_location ] )

Arguments

expression1

Is an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.

expression2

Is an expression, usually a column searched for the specified sequence. expression2 is of the character string data type category.

start_location

Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.

Return Types

int

Remarks

If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.

If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or later. If the database compatibility level is 65 or earlier, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.

If expression1 is not found within expression2, CHARINDEX returns 0.

|||

Hi Shughes,

Thanks for your response.

The problem was not caused by NULL or 0. I set up trace and found that it actually caused by another statement.

-- select @.pos = charindex('United States Dollar', @.sourcedoc)

-- select @.len = len(@.sourcedoc) - @.pos

select @.doc = substring(@.sourcedoc, @.pos, @.len)

-- exec spReportSQLError 'Tracing', @.doc

It seems this statement will generate different result running in SQL job or in management studio.

Here is the trace results:

Error Details:(from SQL Job: wrong data)

Error Date: Feb 6 2006 12:43PM

Error Number: 0

Error Severity: 0

Error State: 0

Error Procedure: None

Error Line: 0

Error Message: No details info.

Other Info: # The daily noon exchange rates for major foreign currencies are published every business day at about 1 # p.m. EST. They are obtained from market or official sources around noon, and show the rates for the # various currencies in Canadian dollars converted from US dollars. The rates are nominal quotations - # neither buying nor selling rates - and are intended for statistical or analytical purposes. Rates # available from financial institutions will differ.

Date (<m>/<d>/<year>),01/27/2006,01/30/

Error Details(Run in management studio(Correct data)):

Error Date: Feb 6 2006 12:43PM

Error Number: 0

Error Severity: 0

Error State: 0

Error Procedure: None

Error Line: 0

Error Message: No details info.

Other Info: United States Dollar,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457

Argentine Peso (Floating Rate),0.3733,0.3729,0.3725,0.3712,0.3716,0.3729,0.3727

Australian Dollar,0.8624,0.8577,0.8660,0.8598,0.8628,0.8591,0.8507

Bahamian Dollar,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457

Brazilian Real,0.5201,0.5181,0.5174,0.5138,0.5132,0.5171,0.5242

Chilean Peso,0.002178,0.002183,0.002173,0.002158,0.002156,0.002171,0.002183

Chinese Renminbi,0.1423,0.1419,0.1419,0.1414,0.1418,0.1423,0.1422

Colombian Peso,0.000506,0.000505,0.000504,0.000503,0.000505,0.000507,0.000507

Croatian Kuna,0.1893,0.1882,0.1895,0.1880,0.1886,0.1883,0.1870

Czech. Republic Koruna,0.04920,0.04875,0.04907,0.04843,0.04849,0.04848,0.04844

Danish Krone,0.1865,0.1854,0.1863,0.1847,0.1853,0.1847,0.1836

East Caribbean Dollar,0.4265,0.4253,0.4252,0.4238,0.4249,0.4264,0.4259

European EURO,1.3919,1.3836,1.3906,1.3786,1.3833,1.3786,1.3713

Fiji Dollar,0.6689,0.6676,0.6640,0.6647,0.6643,0.6688,0.6687

African Financial Community Franc (CFA),0.002122,0.002109,0.002120,0.002102,0.002109,0.002102,0.002091

Pacific Financial Community Franc (CFP),0.01166,0.01159,0.01165,0.01155,0.01159,0.01155,0.01149

Ghanaian Cedi,0.000126,0.000126,0.000126,0.000125,0.000125,0.000126,0.000126

Guatemala Quetzal,0.15058,0.15017,0.15036,0.14988,0.15027,0.15079,0.15060

Honduran Lempira,0.06073,0.06056,0.06054,0.06034,0.06050,0.06071,0.06064

Hong Kong Dollar,0.147927,0.147512,0.147461,0.146989,0.147382,0.147865,0.147674

Hungarian Forint,0.005534,0.005495,0.005526,0.005479,0.005512,0.005502,0.005479

Icelandic Krona,0.01851,0.01840,0.01833,0.01806,0.01812,0.01816,0.01816

Indian Rupee,0.02607,0.02598,0.02602,0.02583,0.02588,0.02598,0.02595

Indonesian Rupiah,0.000122,0.000122,0.000122,0.000122,0.000122,0.000123,0.000124

Israeli New Shekel,0.2476,0.2463,0.2450,0.2443,0.2439,0.2441,0.2435

Jamaican dollar,0.01784,0.01780,0.01781,0.01869,0.01874,0.01783,0.01780

Japanese Yen,0.009793,0.009735,0.009785,0.009674,0.009665,0.009643,0.009632

Malaysian Ringgit,0.3059,0.3051,0.3050,0.3040,0.3046,0.3063,0.3064

Mexican Peso,0.1097,0.1096,0.1095,0.1093,0.1090,0.1093,0.1095

Moroccan Dirham,0.1273,0.1267,0.1271,0.1259,0.1265,0.1265,0.1259

Myanmar (Burma) Kyat,0.1954,0.1942,0.1943,0.1937,0.1937,0.1944,0.1934

Neth. Antilles Guilder,0.6446,0.6429,0.6426,0.6406,0.6422,0.6444,0.6437

New Zealand Dollar,0.7837,0.7802,0.7840,0.7816,0.7879,0.7878,0.7805

Norwegian Krona,0.1722,0.1700,0.1719,0.1707,0.1721,0.1714,0.1704

Pakistan Rupee,0.01917,0.01907,0.01911,0.01904,0.01909,0.01917,0.01914

Panamanian Balboa,1.1474,1.1443,1.1439,1.1402,1.1432,1.1471,1.1457

Peruvian New Sol,0.3460,0.3458,0.3450,0.3449,0.3452,0.3479,0.3484

Philippine Peso,0.02187,0.02181,0.02193,0.02190,0.02196,0.02208,0.02214

Polish Zloty,0.3641,0.3623,0.3641,0.3609,0.3615,0.3605,0.3590

Russian Rouble,0.04098,0.04066,0.04068,0.04051,0.04063,0.04062,0.04056

Singapore Dollar,0.7060,0.7019,0.7050,0.6998,0.7001,0.7015,0.7038

Slovak Koruna,0.03725,0.03706,0.03721,0.03696,0.03702,0.03693,0.03671

Slovenian Tolar,0.005809,0.005778,0.005805,0.005754,0.005775,0.005760,0.005727

South African Rand,0.1867,0.1862,0.1879,0.1864,0.1878,0.1880,0.1874

South Korean Won,0.001182,0.001179,0.001190,0.001185,0.001176,0.001182,0.001190

Sri Lanka Rupee,0.01123,0.01120,0.01120,0.01117,0.01119,0.01123,0.01123

Swedish Krona,0.1507,0.1498,0.1504,0.1491,0.1489,0.1487,0.1474

Swiss Franc,0.8967,0.8894,0.8945,0.8878,0.8900,0.8861,0.8806

Taiwanese New Dollar,0.03588,0.03578,0.03577,0.03565,0.03575,0.03574,0.03570

Thai Baht,0.02941,0.02926,0.02940,0.02904,0.02903,0.02910,0.02906

Trinidad & Tobago Dollar,0.1833,0.1831,0.1845,0.1823,0.1823,0.1840,0.1835

Tunisian Dinar,0.8578,0.8524,0.8570,0.8488,0.8510,0.8500,0.8468

New Turkish Lira,0.8647,0.8643,0.8651,0.8612,0.8634,0.8664,0.8624

Pound Sterling,2.0342,2.0240,2.0377,2.0269,2.0357,2.0213,2.0006

Venezuelan Bolivar,0.000534,0.000533,0.000533,0.000531,0.000532,0.000534,0.000534

I am still trying to figure out how this was caused.

Thanks

Bill

|||? Hi Bill, It's hard to know without knowing the complete code and the data it operates on. But I suspect that this was a potential bug in your code all along, and you just were lucky until now. The SQL Server query optimizer is free to reorganize your query and evaulate it in any order it wants. That can give you great performance benefits - but it may also cause unexpected bugs. I'll illustrate this with the example you posted in your first post (yes, I did see the follow-up, but this code takes a lot less typing <g>). Have a look at this query: SELECT LEFT(MyColumn, CHARINDEX(',', MyColumn) - 1) FROM MyTable WHERE CHARINDEX(',', MyColumn) > 0 You might be inclined to say that this is safe - after all, the WHERE will exclude all rows without a comma, and the LEFT function will evaluate fine for the remaining rows. Right? WRONG!!!! The optimizer is free to evaluate the query in any order it sees fit. So it might decide to do the SELECT first, then use the WHERE to filter the results. And BOOM!! you get an error for the first row with no comma in MyColumn. What might have happened is that in your real query, which is probably more complex than the example above, a new optimizing technique (that was not available to the SQL Server 2000 query optimizer) was chosen to evaluate your query, leading to this result. -- Hugo Kornelis, SQL Server MVP <Bill YU@.discussions.microsoft.com> schreef in bericht news:7d07a949-c4d2-4fa9-af97-bad706de680d@.discussions.microsoft.com... Hi all, I am having a weird issue after we upgraded our DB server to SQL 2005. I have a SP used to extract exchange rate, and a job calls this SP daily. This job worked fine on SQL 2000, and works very well in Management studio if I call this SP seperately, but failed in sql job in 2005. The error statement pointed to: select left(@.row, charindex(',', @.row)-1), REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1)) The error message is: Invalid length parameter passed to the SUBSTRING function. Anyone knows what's the difference for LEFT function between sql 2000 and 2005? Thanks Bill|||

Hi Hugo,

Here are my 3 SPs:

CREATE PROCEDURE uspGetXMLFromHTTP (@.URL varchar(255), @.Method varchar(20)='GET')
AS
BEGIN
set nocount on
declare @.objRef int,@.resultcode int
exec @.resultcode = sp_OACreate 'Msxml2.XMLHTTP.4.0', @.objRef OUT
if @.resultcode = 0
begin
exec @.resultcode = sp_OAMethod @.objRef, 'Open', NULL,@.Method, @.URL, False
exec @.resultcode = sp_OAMethod @.objRef, 'Send',null
execute sp_OAGetProperty @.objRef, 'responseText'
end
exec sp_OADestroy @.objRef
END
GO

CREATE PROCEDURE uspReportSQLError(@.Location Varchar(250) = null, @.TraceInfo varchar(MAX) = null)
AS
BEGIN
set nocount on
declare @.cc varchar(250), @.bcc varchar(250)
declare @.errormsg varchar(Max), @.subject varchar(250)
select @.cc = '',
@.bcc = '',
@.subject = 'SQL Error: Server > [' + @.@.servername + '] > Database > [' + DB_NAME() + ']'
+ isnull((' > Location > [' + isnull(@.Location, ERROR_PROCEDURE()) + ']'), '')

select @.errormsg = 'Error Details:' + char(13) + char(10)
+ '-' + char(13) + char(10)
+ ' Error Date: ' + cast(getdate() as varchar) + char(13) + char(10)
+ ' Error Number: ' + cast(isnull(ERROR_NUMBER(), 0) as varchar) + char(13) + char(10)
+ ' Error Severity: ' + cast(isnull(ERROR_SEVERITY(), 0) as varchar) + char(13) + char(10)
+ ' Error State: ' + cast(isnull(ERROR_STATE(), 0) as varchar) + char(13) + char(10)
+ 'Error Procedure: ' + isnull(ERROR_PROCEDURE(), 'None') + char(13) + char(10)
+ ' Error Line: ' + cast(isnull(ERROR_LINE(), 0) as varchar) + char(13) + char(10)
+ ' Error Message: ' + isnull(ERROR_MESSAGE(), 'No details info.') + char(13) + char(10) + char(13) + char(10)
+ isnull((' Other Info: ' + @.TraceInfo), '')
-- Insert central log table

exec msdb.dbo.sp_send_dbmail @.profile_name = 'SQLError',
@.recipients = 'dba@.builddirect.com',
@.copy_recipients = @.cc,
@.blind_copy_recipients = @.bcc,
@.body = @.errormsg,
@.subject = @.subject,
@.importance = 'High',
@.body_format = 'Text'
set nocount off
END
GO

CREATE PROCEDURE uspExtractExchangeRate
AS
BEGIN
set nocount on
declare @.currency varchar(100), @.rate decimal(12,6), @.pos int, @.len int
declare @.doc varchar(Max), @.row varchar(255), @.reversedrow varchar(255), @.i int, @.j int
declare @.table table(Currency varchar(100), Rate decimal(12,6))
create table #TodayExchangeRate(response varchar(MAX))
begin try
insert #TodayExchangeRate
exec uspGetXMLFromHTTP 'http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv'
select @.doc = response from #TodayExchangeRate
drop table #TodayExchangeRate

select @.pos = charindex('United States Dollar', @.doc)
select @.len = len(@.doc) - @.pos
select @.doc = substring(@.doc, @.pos, @.len)

--exec uspReportSQLError 'Tracing', @.doc

select @.i = 1, @.j = -1
while (1=1)
begin
select @.j = charindex((char(13) + char(10)), @.doc, @.j + 2)
if @.j = 0 break
select @.row = substring(@.doc, @.i, @.j - @.i)
select @.reversedrow = REVERSE(@.row)
select @.currency = left(@.row, charindex(',', @.row)-1)
select @.rate = REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1))
insert @.table(Currency, Rate)
select @.currency, @.rate
select @.i = @.j + 2
end
end try
begin catch
--exec uspReportSQLError
end catch

-- save into exchangeRates table
set nocount off
END
GO

Except resetting @.profile_name in [uspExtractExchangeRate], these SPs are functional.

Hope this help.

Thanks

Bill

|||? Hi Bill, This'll be hard to troubleshoot, since I have no idea what xp_OACreate 'Msxml2.XMLHTTP.4.0' does. And I don't have SQL Server 2005, so I can't run this. However, I do have a trouble-shooting suggestion: add some PRINT (or SELECT) statements to your main procedure to see what happens, what exact data is being returned from the calls to sp_OAMethod and what steps are taken during the string parsing process. Something like this: CREATE PROCEDURE uspExtractExchangeRateASBEGINset nocount ondeclare @.currency varchar(100), @.rate decimal(12,6), @.pos int, @.len intdeclare @.doc varchar(8000), @.row varchar(255), @.reversedrow varchar(255), @.i int, @.j intdeclare @.table table(Currency varchar(100), Rate decimal(12,6))create table #TodayExchangeRate(response varchar(8000))begin tryinsert #TodayExchangeRateexec uspGetXMLFromHTTP 'http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv' select @.doc = response from #TodayExchangeRate SELECT @.doc AS 'After uspGetXMLFromHTTP'drop table #TodayExchangeRate select @.pos = charindex('United States Dollar', @.doc)select @.len = len(@.doc) - @.posselect @.doc = substring(@.doc, @.pos, @.len)SELECT @.doc AS 'After stripping'--exec uspReportSQLError 'Tracing', @.doc select @.i = 1, @.j = -1while (1=1)beginselect @.j = charindex((char(13) + char(10)), @.doc, @.j + 2)if @.j = 0 breakselect @.row = substring(@.doc, @.i, @.j - @.i)select @.reversedrow = REVERSE(@.row) SELECT @.i, @.j, @.row, @.reversedrow select @.currency = left(@.row, charindex(',', @.row)-1)select @.rate = REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1)) SELECT @.currency, @.rateinsert @.table(Currency, Rate)select @.currency, @.rateselect @.i = @.j + 2endSELECT @.i, @.j, 'Completely done' end trybegin catch--exec uspReportSQLErrorend catch -- save into exchangeRates tableset nocount offENDGO Checking the output of this debug-enabled version of the proc might reveal what's going on. -- Hugo Kornelis, SQL Server MVP -- Original Message -- From: Bill YU@.discussions.microsoft.com Newsgroups: microsoft.private.forums.msdn.sqlserver.tsql Sent: Monday, February 06, 2006 11:59 PM Subject: Re: Invalid length parameter passed to the SUBSTRING function Hi Hugo, Here are my 3 SPs: CREATE PROCEDURE uspGetXMLFromHTTP (@.URL varchar(255), @.Method varchar(20)='GET')ASBEGINset nocount ondeclare @.objRef int,@.resultcode intexec @.resultcode = sp_OACreate 'Msxml2.XMLHTTP.4.0', @.objRef OUT if @.resultcode = 0beginexec @.resultcode = sp_OAMethod @.objRef, 'Open', NULL,@.Method, @.URL, False exec @.resultcode = sp_OAMethod @.objRef, 'Send',nullexecute sp_OAGetProperty @.objRef, 'responseText'endexec sp_OADestroy @.objRefENDGO CREATE PROCEDURE uspReportSQLError(@.Location Varchar(250) = null, @.TraceInfo varchar(MAX) = null)ASBEGINset nocount ondeclare @.cc varchar(250), @.bcc varchar(250)declare @.errormsg varchar(Max), @.subject varchar(250)select @.cc = '',@.bcc = '',@.subject = 'SQL Error: Server > [' + @.@.servername + '] > Database > [' + DB_NAME() + ']'+ isnull((' > Location > [' + isnull(@.Location, ERROR_PROCEDURE()) + ']'), '')select @.errormsg = 'Error Details:' + char(13) + char(10)+ '-' + char(13) + char(10)+ ' Error Date: ' + cast(getdate() as varchar) + char(13) + char(10) + ' Error Number: ' + cast(isnull(ERROR_NUMBER(), 0) as varchar) + char(13) + char(10)+ ' Error Severity: ' + cast(isnull(ERROR_SEVERITY(), 0) as varchar) + char(13) + char(10)+ ' Error State: ' + cast(isnull(ERROR_STATE(), 0) as varchar) + char(13) + char(10)+ 'Error Procedure: ' + isnull(ERROR_PROCEDURE(), 'None') + char(13) + char(10)+ ' Error Line: ' + cast(isnull(ERROR_LINE(), 0) as varchar) + char(13) + char(10)+ ' Error Message: ' + isnull(ERROR_MESSAGE(), 'No details info.') + char(13) + char(10) + char(13) + char(10)+ isnull((' Other Info: ' + @.TraceInfo), '')-- Insert central log table exec msdb.dbo.sp_send_dbmail @.profile_name = 'SQLError',@.recipients = 'dba@.builddirect.com',@.copy_recipients = @.cc,@.blind_copy_recipients = @.bcc,@.body = @.errormsg,@.subject = @.subject,@.importance = 'High',@.body_format = 'Text' set nocount offENDGO CREATE PROCEDURE uspExtractExchangeRateASBEGINset nocount ondeclare @.currency varchar(100), @.rate decimal(12,6), @.pos int, @.len intdeclare @.doc varchar(Max), @.row varchar(255), @.reversedrow varchar(255), @.i int, @.j intdeclare @.table table(Currency varchar(100), Rate decimal(12,6))create table #TodayExchangeRate(response varchar(MAX))begin tryinsert #TodayExchangeRateexec uspGetXMLFromHTTP 'http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv' select @.doc = response from #TodayExchangeRatedrop table #TodayExchangeRateselect @.pos = charindex('United States Dollar', @.doc)select @.len = len(@.doc) - @.posselect @.doc = substring(@.doc, @.pos, @.len) --exec uspReportSQLError 'Tracing', @.doc select @.i = 1, @.j = -1while (1=1)beginselect @.j = charindex((char(13) + char(10)), @.doc, @.j + 2)if @.j = 0 breakselect @.row = substring(@.doc, @.i, @.j - @.i)select @.reversedrow = REVERSE(@.row) select @.currency = left(@.row, charindex(',', @.row)-1)select @.rate = REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1))insert @.table(Currency, Rate)select @.currency, @.rateselect @.i = @.j + 2endend trybegin catch--exec uspReportSQLErrorend catch -- save into exchangeRates tableset nocount offENDGO Except resetting @.profile_name in [uspExtractExchangeRate], these SPs are functional. Hope this help. Thanks Bill <Bill YU@.discussions.microsoft.com> schreef in bericht news:3285cccb-7a6a-4451-b618-a8d90cdf19c1@.discussions.microsoft.com... Hi Hugo, Here are my 3 SPs: CREATE PROCEDURE uspGetXMLFromHTTP (@.URL varchar(255), @.Method varchar(20)='GET')ASBEGINset nocount ondeclare @.objRef int,@.resultcode intexec @.resultcode = sp_OACreate 'Msxml2.XMLHTTP.4.0', @.objRef OUT if @.resultcode = 0beginexec @.resultcode = sp_OAMethod @.objRef, 'Open', NULL,@.Method, @.URL, False exec @.resultcode = sp_OAMethod @.objRef, 'Send',nullexecute sp_OAGetProperty @.objRef, 'responseText'endexec sp_OADestroy @.objRefENDGO CREATE PROCEDURE uspReportSQLError(@.Location Varchar(250) = null, @.TraceInfo varchar(MAX) = null)ASBEGINset nocount ondeclare @.cc varchar(250), @.bcc varchar(250)declare @.errormsg varchar(Max), @.subject varchar(250)select @.cc = '',@.bcc = '',@.subject = 'SQL Error: Server > [' + @.@.servername + '] > Database > [' + DB_NAME() + ']'+ isnull((' > Location > [' + isnull(@.Location, ERROR_PROCEDURE()) + ']'), '')select @.errormsg = 'Error Details:' + char(13) + char(10)+ '-' + char(13) + char(10)+ ' Error Date: ' + cast(getdate() as varchar) + char(13) + char(10) + ' Error Number: ' + cast(isnull(ERROR_NUMBER(), 0) as varchar) + char(13) + char(10)+ ' Error Severity: ' + cast(isnull(ERROR_SEVERITY(), 0) as varchar) + char(13) + char(10)+ ' Error State: ' + cast(isnull(ERROR_STATE(), 0) as varchar) + char(13) + char(10)+ 'Error Procedure: ' + isnull(ERROR_PROCEDURE(), 'None') + char(13) + char(10)+ ' Error Line: ' + cast(isnull(ERROR_LINE(), 0) as varchar) + char(13) + char(10)+ ' Error Message: ' + isnull(ERROR_MESSAGE(), 'No details info.') + char(13) + char(10) + char(13) + char(10)+ isnull((' Other Info: ' + @.TraceInfo), '')-- Insert central log table exec msdb.dbo.sp_send_dbmail @.profile_name = 'SQLError',@.recipients = 'dba@.builddirect.com',@.copy_recipients = @.cc,@.blind_copy_recipients = @.bcc,@.body = @.errormsg,@.subject = @.subject,@.importance = 'High',@.body_format = 'Text' set nocount offENDGO CREATE PROCEDURE uspExtractExchangeRateASBEGINset nocount ondeclare @.currency varchar(100), @.rate decimal(12,6), @.pos int, @.len intdeclare @.doc varchar(Max), @.row varchar(255), @.reversedrow varchar(255), @.i int, @.j intdeclare @.table table(Currency varchar(100), Rate decimal(12,6))create table #TodayExchangeRate(response varchar(MAX))begin tryinsert #TodayExchangeRateexec uspGetXMLFromHTTP 'http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv' select @.doc = response from #TodayExchangeRatedrop table #TodayExchangeRateselect @.pos = charindex('United States Dollar', @.doc)select @.len = len(@.doc) - @.posselect @.doc = substring(@.doc, @.pos, @.len) --exec uspReportSQLError 'Tracing', @.doc select @.i = 1, @.j = -1while (1=1)beginselect @.j = charindex((char(13) + char(10)), @.doc, @.j + 2)if @.j = 0 breakselect @.row = substring(@.doc, @.i, @.j - @.i)select @.reversedrow = REVERSE(@.row) select @.currency = left(@.row, charindex(',', @.row)-1)select @.rate = REVERSE(left(@.reversedrow, charindex(',', @.reversedrow)-1))insert @.table(Currency, Rate)select @.currency, @.rateselect @.i = @.j + 2endend trybegin catch--exec uspReportSQLErrorend catch -- save into exchangeRates tableset nocount offENDGO Except resetting @.profile_name in [uspExtractExchangeRate], these SPs are functional. Hope this help. Thanks Bill|||

Why are you using SQL to parse the comma-separated string? This is much more easier to do on the client. You can do any of the following:

1. You can replace the SP with a DTS or SSIS package that imports the CSV information to a table

2. Or you can take the CSV file and import using BCP

3. Or use an ActiveX task from SQLAgent

Any of these approaches will be much more robust and simpler. OLE automation SPs should generally be avoided due to their overhead in using on the server and since you are running this from a job anyway it is better to isolate the process from server.

Btw, the error is probably due to bad data or incorrectly formed row.

|||

Thanks Hugo.

Bill

|||

Hi Umachandar,

This is a legacy task, I run this job from Internal DB server not production, and I did not want to develop and maintain any codes other than sql scripts.

I just happened to have this problem, seems SQL 2005 has a different behavior here.

Thanks

Bill

|||You will have to post a repro script to determine if this is a bug in SQL Server 2005 or not. Otherwise it is hard to tell by just looking at the code since this can be due to bad input. Does the same code run fine in SQL Server 2000 with the same input?|||

Yes,

I run this job for more than two years, never had a problem.

The issue happened after we upgraded to SQL 2005.

Interestingly, if I assign the CSV text to a variable from TEMP table, it works fine.

Now, I have to manually execute the same SP from Management Studio daily.

Bill

Friday, March 23, 2012

Invalid Object Name - Weird Error - Help!

Invalid Object Name - Weird Error - Help!

Invalid Object Name - Weird Error - Help!

Invalid Object Name - Weird Error - Help!

Invalid Object Name - Weird Error - Help!

Wednesday, March 21, 2012

Invalid length parameter passed to the SUBSTRING function

Arguments

Return Types

Remarks

Internet integration

Blog Archive

About Me