Sql Case sensitivity: object

Showing posts with label object. Show all posts

Wednesday, March 7, 2012

Chain Linkage Problem

Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
(1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
Ritesh
Hi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:

> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
> (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>
|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:

Chain Linkage Problem

Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev = (1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
RiteshHi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:
> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev => (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
> > Hi All,
> >
> > I am facing this error continously:
> >
> > Error: 8908, Severity: 22, State: 6
> >
> > Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> > linkage
> > mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev => > (1:736799)..
> >
> > And when I run DBCC CHECKTABLE it shows consistency errors.
> > It get fixed using DBCC DBReIndex but re-occur after sometimes.
> >
> > I have tried to find in newsgroup, some of them says it can happen due to
> > NOLOCK hint.
> >
> > Is there any solution for this problem or please help me to identify the
> > root cause of the problem.
> >
> > Thanks
> > Ritesh
> >
> >
> >

Chain Linkage Problem

Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
(1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
RiteshHi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:

> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
> (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
>

Chain Linkage Mismatch Errors

Recently I have been getting a barrage of errors such as:
DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).
and
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable__________________________
____________________________________
________________________________________
_000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/defaul...t
=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION: Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
> (3:178).
> and
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable__________________________
____________________________________
________________________________________
_000100003472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/defaul...Ben-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/defaul...3&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
--
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Chain Linkage Mismatch Errors

Recently I have been getting a barrage of errors such as:
DESCRIPTION:Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).
and
DESCRIPTION:Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION:Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable____________________________________ __________________________________________________ _________________000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default...en-us%3B308886
)
We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default...&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.
>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION:Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
> (3:178).
> and
> DESCRIPTION:Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION:Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable____________________________________ __________________________
_________________________________________000100003 472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/default...en-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/default...&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Chain Linkage Mismatch Errors

Recently I have been getting a barrage of errors such as:
DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev = (3:178).
and
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable_______________________________________________________________________________________________________000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
)
We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION: Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev => (3:178).
> and
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable______________________________________________________________
_________________________________________000100003472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
--
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Sunday, February 19, 2012

CDOSYS object not working

I am now trying to send email using CDOSYS from a stored procedure on a
Windows 2003 server and Sql Server 2000.
I got this code from the net and trying to figure out why it won't work.
It seems to work ok until I do the "send".
I am getting an error:
Source: CDO.Message.1
Description: The transport failed to connect to the server.
Is this the SMTP server or the code that is using the microsoft addresses
(ie. http://schemas.microsoft.com/cdo/co...ation/sendusing)?
My code is:
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%
Declare @.From varchar(100) , @.To varchar(100) , @.Subject varchar(100), @.Body
varchar(4000)
/ ****************************************
**
This stored procedure takes the parameters and sends an e-mail. All the mail
configurations are hard-coded in the stored procedure. Comments are added to
the stored procedure where necessary. References to the CDOSYS objects are
at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp
****************************************
***/
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)
Select @.From = 'tfs@.ftsolutions.com',@.To = 'tfs@.ftsolutions.com', @.Subject =
'Backup Notification', @.Body='The Body of the message'
--***** Create the CDO.Message Object *****
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
print '@.hr after Creating CDO.Message'
print @.hr
--*****Configuring the Message Object *****
-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
print '@.hr after the sendUsing line'
print @.hr
-- This is to configure the Server Name or IP address.
print 'Before the Smtp Server Setup'
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value',
'smtp.earth.com'
print '@.hr After setting up the SMTP server'
print @.hr
-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
print '@.hr after Update'
print @.hr
-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject
print @.hr
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
print @.hr
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL
print @.hr
-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg
GO
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%
All the "print @.hr" lines are all showing 0, except the last one that is
showing: -2147220973.
Thanks,
TomHave you tried using xp_smtp_sendmail instead? I believe it's easier
to use than CDOSYS.
I found these links on Google Groups:
http://www.sqldev.net/xp/sp_smtp_sendmail.htm
http://www.aspfaq.com/2403
HTH
Barry|||"Barry" <barry.oconnor@.manx.net> wrote in message
news:1139940520.616802.88260@.g47g2000cwa.googlegroups.com...
> Have you tried using xp_smtp_sendmail instead? I believe it's easier
> to use than CDOSYS.
I did get it to work if I used the IP address instead of the name address.
I was going to look at sp_smtp_sendmail when Tibor mentioned it. But I did
get CDOSYS working. Is sendmail better?
Thanks,
Tom
> I found these links on Google Groups:
> http://www.sqldev.net/xp/sp_smtp_sendmail.htm
> http://www.aspfaq.com/2403
> HTH
> Barry
>|||Personally, I've never used it. I have read about it and seems quite
good.
I searched Google and I think general opinion is that it's easier to
use and has better scalability.
I guess it's a question of personal preference.
Barry|||I was reading about it also and found that the drawback was that you need an
email client to use it. CDOSYS doesn't need this.|||xp_smtp_sendmail doesn't need an email client. The old x_sendmail does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ezp9AvZMGHA.1536@.TK2MSFTNGP11.phx.gbl...
>I was reading about it also and found that the drawback was that you need a
n email client to use
>it. CDOSYS doesn't need this.
>

Friday, February 10, 2012

Cast COM object error on OleDb Destination (Access 2003)

Trying to do a update/insert from SQL 2005 query to Access 2003 linked table.

In the Script Transformation I get this error.

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Destinatoin Oledb connection is Native OLEDB Jet 4 to Access 2003 database.

Private sqlConn As OleDb.OleDbConnection

Private sqlCmd As OleDb.OleDbCommand

Private sqlParam As OleDb.OleDbParameter

Private connstring As String

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.ConnectionOLE

'sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

connstring = connMgr.ConnectionString

sqlConn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)

End Sub

Any help would be appreciated.

The OLEDB connection manager is not using managed OleDb classes but the native OleBD interfaces. AcquireConnection returns a COM object that is created from IDBCreateSession::CreateSession call.

HTH,
Ovidiu Burlacu

|||

You are correct. The destination connection was using the Native OLE DB for Jet 4.

I created a Data Source using .NET OLE DB provider for Jet 4 and I got connected in the Script Component OK.

New Problem.

My insert OLE DB command worked fine which uses the original destination connection (Native), But my update OLE DB command using same Native connection is erroring out on each input row of the OLE DB Command component.

[UpdateRow [2727]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Numeric field overflow.".

Connection string on Destination OLE DB connection is:

Data Source=C:\Projects\Data\TSLists.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;OLE DB Services=-1;

UPDATE: The table in the Access database I am trying to update is a linked table representing a SharePoint 2003 list. What is interesting is you can push (insert) data into this table, but you can not update data in the same linked table from the same OLE DB connection in SSIS.

CAST a variable into a datetime object

CAST a variable into a datetime object
I need to do a CAST(@.variable_name as datetime)
this won't work because @.variable_name has the following format
'dd/mm/yy hh:mi:ss:mmmAM'
like how do i specify a style for it.
please help..
James : (You can use default values (style 0 or 100) to represent, refer to books online for CAST & CONVERT topic.

HTH.|||My suggestion is to write some code (which could be a transact-sql expression) to convert your chacter date to a standard form. I would strongly suggest using the ISO standard 'yyyy-mm-dd hh:mm:ss.ttt" format. I've seen a euro-to-ISO time function, if you can't find it, I can either find or write one for you.

-PatP|||hi

thanx for the reply.

books online wasn't of much help because it didn't provide any examples of converting a string to datetime. but, I have found the following to work.

convert (datetime, '30/12/04 1:10:30:000PM', 3);

i remember i used to use a combination of convert and cast to get datetime conversion working. I am just surprised that format such as
'dd/mm/yyyy' is not automatically supported in ms sql server. I had to parse the string so that i get dd/mm/yyyy in order for the conversion to work.

thanx for helping.

james : )|||Originally posted by Pat Phelan
My suggestion is to write some code (which could be a transact-sql expression) to convert your chacter date to a standard form. I would strongly suggest using the ISO standard 'yyyy-mm-dd hh:mm:ss.ttt" format. I've seen a euro-to-ISO time function, if you can't find it, I can either find or write one for you.

-PatP

hi, it would be good if you can show me how it's done, i don't know how to do any string manipulation in mssql.

thank you.

james :)

Wednesday, March 7, 2012

Chain Linkage Problem

Chain Linkage Problem

Chain Linkage Problem

Chain Linkage Mismatch Errors

Chain Linkage Mismatch Errors

Chain Linkage Mismatch Errors

Chain Linkage Mismatch Errors

Sunday, February 19, 2012

CDOSYS object not working

Friday, February 10, 2012

Cast COM object error on OleDb Destination (Access 2003)

CAST a variable into a datetime object

Sql Case sensitivity

Blog Archive

About Me