Error 8646! URGENT! HELP!

Hello ALL!
I got strage problem. While perfoming long update i got error 8646 (The inde
x entry for row ID was not found in index ID 2, of table 641098466, in data
base 'MyBase').
At microsoft site i found: 'FIX: Error 644 or 8646 May Occur During a DELETE
or UPDATE Against a Table That Contains a Unicode Column with a Latin1_Gene
ral_BIN Collation'
But my collation is SQL_Latin1_general_CP1251_CI_AS and that table 64109846
6 have not Unicode Columns.
What is wrong?What does DBCC CHECKDB and DBCC CHECKCTALOG say? Any errors? You might just
have some database corruption.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Volhv" <anonymous@.discussions.microsoft.com> wrote in message
news:9E597526-356A-435A-A760-629C1141A3F9@.microsoft.com...
> Hello ALL!
> I got strage problem. While perfoming long update i got error 8646 (The
index entry for row ID was not found in index ID 2, of table 641098466, in
database 'MyBase').
> At microsoft site i found: 'FIX: Error 644 or 8646 May Occur During a
DELETE or UPDATE Against a Table That Contains a Unicode Column with a
Latin1_General_BIN Collation'
> But my collation is SQL_Latin1_general_CP1251_CI_AS and that table
641098466 have not Unicode Columns.
> What is wrong?
>|||All DBCC comand say nothing. After this error i run
dbcc checktable & dbcc dbreindex
After this all work fine until next ocurency of this error|||In addition to Tibor's suggestion,
Be watchful of potential hardware error which may be causing this... AND
since the index ID is = 2, this is a non-clustered index... You might try to
drop and re-create the non-clustered index...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Volhv" <anonymous@.discussions.microsoft.com> wrote in message
news:9E597526-356A-435A-A760-629C1141A3F9@.microsoft.com...
> Hello ALL!
> I got strage problem. While perfoming long update i got error 8646 (The
index entry for row ID was not found in index ID 2, of table 641098466, in
database 'MyBase').
> At microsoft site i found: 'FIX: Error 644 or 8646 May Occur During a
DELETE or UPDATE Against a Table That Contains a Unicode Column with a
Latin1_General_BIN Collation'
> But my collation is SQL_Latin1_general_CP1251_CI_AS and that table
641098466 have not Unicode Columns.
> What is wrong?
>|||Yes its non-clustered index and i done drop and re-create...
Hardware problem...i do not think so. I shrink base, defragment, recreating
and so on. I think real place of this index derfert after this operations a
nd problem occured only in one table. This table at the and of transaction a
nd data move to it via trig
ger.

Error 8162

We get an Error 8162 when one of our SQR programs attempt
to insert records into a table. The error is irratic
since it does not always occur. We have been unable to
determine exactly what this error means or what may be
causing it. The full error message we get is listed below:
"(SQR 5528) ODBC SQL dbexec: SQLExecute error 8162 in
cursor 18: [MICROSOFT] [ODBD SQL Server Driver] [SQL
Server] Formal parameter '@.P21' was defined as OUTPUT but
the actual parameter not declared OUTPUT."
Looks like a mismatch in procedure parameters. Have you tried dropping and
recerating the stored proc and then running it?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error 8162

We get an Error 8162 when one of our SQR programs attempt
to insert records into a table. The error is irratic
since it does not always occur. We have been unable to
determine exactly what this error means or what may be
causing it. The full error message we get is listed below:
"(SQR 5528) ODBC SQL dbexec: SQLExecute error 8162 in
cursor 18: [MICROSOFT] [ODBD SQL Server Driver] [SQL
Server] Formal parameter '@.P21' was defined as OUTPUT but
the actual parameter not declared OUTPUT."Looks like a mismatch in procedure parameters. Have you tried dropping and
recerating the stored proc and then running it?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error 8162

We get an Error 8162 when one of our SQR programs attempt
to insert records into a table. The error is irratic
since it does not always occur. We have been unable to
determine exactly what this error means or what may be
causing it. The full error message we get is listed below:
"(SQR 5528) ODBC SQL dbexec: SQLExecute error 8162 in
cursor 18: [MICROSOFT] [ODBD SQL Server Driver] [SQL
Server] Formal parameter '@.P21' was defined as OUTPUT but
the actual parameter not declared OUTPUT."Looks like a mismatch in procedure parameters. Have you tried dropping and
recerating the stored proc and then running it?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql

Error 8152

I'm trying to add a record to a table and am getting Error 8152:
String or binary data would be truncated.
Here is my statement written with VBA in an Access project.
Both datatypes are nvarchar
Private Sub cmdCreateJob_Click()
Dim strJcn As String
Dim strParcTag As String
Dim strSql As String
strJcn = 999999
strParcTag = Me.cboEquipID.Column(0)
strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
& " VALUES ('strJcn','strParcTag')"
DoCmd.RunSQL strSql
End Sub
Can anyone see what I'm doing wrong? Thanks.Can you post your DDL for table ESR?
Perayu
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:E21FB5F9-31A7-4FA4-89AD-49F37A3CA0E7@.microsoft.com...
> I'm trying to add a record to a table and am getting Error 8152:
> String or binary data would be truncated.
> Here is my statement written with VBA in an Access project.
> Both datatypes are nvarchar
> Private Sub cmdCreateJob_Click()
> Dim strJcn As String
> Dim strParcTag As String
> Dim strSql As String
> strJcn = 999999
> strParcTag = Me.cboEquipID.Column(0)
> strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
> & " VALUES ('strJcn','strParcTag')"
> DoCmd.RunSQL strSql
> End Sub
> Can anyone see what I'm doing wrong? Thanks.
>
>
>|||Check the ESR_JCN column length and strJcn string length
and ESR_PARCTAG column length and strParcTag string length
The data length must be longer than column length
Thanks & Rate the Postings.
-Ravi-
"AkAlan" wrote:

> I'm trying to add a record to a table and am getting Error 8152:
> String or binary data would be truncated.
> Here is my statement written with VBA in an Access project.
> Both datatypes are nvarchar
> Private Sub cmdCreateJob_Click()
> Dim strJcn As String
> Dim strParcTag As String
> Dim strSql As String
> strJcn = 999999
> strParcTag = Me.cboEquipID.Column(0)
> strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
> & " VALUES ('strJcn','strParcTag')"
> DoCmd.RunSQL strSql
> End Sub
> Can anyone see what I'm doing wrong? Thanks.
>
>
>|||It looks like this VB(?) code is wrong: " VALUES ('strJcn','strParcTag')".
You are inserting 'strJcn', not the value of strJcn, to ESR_JCN column. You
need to try something like " VALUES ('" + integerToString(strJcn) + "', '"
+ strParcTag+ "')". But I am not sure what the syntact is for concatinate
the single quots in VB.
Perayu
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:E21FB5F9-31A7-4FA4-89AD-49F37A3CA0E7@.microsoft.com...
> I'm trying to add a record to a table and am getting Error 8152:
> String or binary data would be truncated.
> Here is my statement written with VBA in an Access project.
> Both datatypes are nvarchar
> Private Sub cmdCreateJob_Click()
> Dim strJcn As String
> Dim strParcTag As String
> Dim strSql As String
> strJcn = 999999
> strParcTag = Me.cboEquipID.Column(0)
> strSql = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG)" _
> & " VALUES ('strJcn','strParcTag')"
> DoCmd.RunSQL strSql
> End Sub
> Can anyone see what I'm doing wrong? Thanks.
>
>
>|||Ok I got everything to work up until I add the datOpenDate field. It only
puts 12:00 AM in the record when I put 1 mar 06 in the form. Here is what I
have so far:
I have tried formatting the datefield but no go. Thanks,
strJcn = "'" & NextJCN() & "'"
strParcTag = "'" & Me.cboEquipID.Column(0) & "'"
strPerfWc = "'" & Me.cboPWC & "'"
strRptby = "'" & Me.cboReportedBy.Column(0) & "'"
strDisc = "'" & Me.txtDisc & "'"
datOpenDate = “’” & Me.txtOpenDate & “’”
strSQL = "INSERT INTO ESR
(ESR_JCN,ESR_PARCTAG,ESR_DISC,ESR_RPTBY,
ESR_OPEN_DATE)" _
& " VALUES (" & strJcn & "," & strParcTag & "," & strDisc & "" _
& "," & strRptby & ", " & datOpenDate & " )"
DoCmd.RunSQL strSQL
"Perayu" wrote:

> It looks like this VB(?) code is wrong: " VALUES ('strJcn','strParcTag')"
.
> You are inserting 'strJcn', not the value of strJcn, to ESR_JCN column. Yo
u
> need to try something like " VALUES ('" + integerToString(strJcn) + "', '
"
> + strParcTag+ "')". But I am not sure what the syntact is for concatinate
> the single quots in VB.
> Perayu
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:E21FB5F9-31A7-4FA4-89AD-49F37A3CA0E7@.microsoft.com...
>
>|||It looks like you have typo here: datOpenDate = "'" & Me.txtOpenDate & "'".
"'" should be "'".
Perayu
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:2ABA6908-E32F-4C09-9BC0-BCF089F15C0F@.microsoft.com...
> Ok I got everything to work up until I add the datOpenDate field. It only
> puts 12:00 AM in the record when I put 1 mar 06 in the form. Here is what
> I
> have so far:
> I have tried formatting the datefield but no go. Thanks,
> strJcn = "'" & NextJCN() & "'"
> strParcTag = "'" & Me.cboEquipID.Column(0) & "'"
> strPerfWc = "'" & Me.cboPWC & "'"
> strRptby = "'" & Me.cboReportedBy.Column(0) & "'"
> strDisc = "'" & Me.txtDisc & "'"
> datOpenDate = "'" & Me.txtOpenDate & "'"
> strSQL = "INSERT INTO ESR
> (ESR_JCN,ESR_PARCTAG,ESR_DISC,ESR_RPTBY,
ESR_OPEN_DATE)" _
> & " VALUES (" & strJcn & "," & strParcTag & "," & strDisc & "" _
> & "," & strRptby & ", " & datOpenDate & " )"
> DoCmd.RunSQL strSQL
>
> "Perayu" wrote:
>

Error 8114 Date data

I am trying to pull over data in for a specified date from an ODBC connected
AS/400 table, and I get the following error.
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Here's an example of what the field looks like in the AS/400 table:
1999-08-24 00:00:00.000
I thought that I could just as "where date = '2005-07-01 00:00:00.00'" but
it doesn't work.
Any thoughts'
Thanks in advance.This could be the limitation of the provider you use. Try a different or
newer provider.
Adrian
"aschossler" <aschossler@.discussions.microsoft.com> wrote in message
news:6C6CD9AF-F37E-459E-9A42-64CE221D2E94@.microsoft.com...
>I am trying to pull over data in for a specified date from an ODBC
>connected
> AS/400 table, and I get the following error.
> Server: Msg 8114, Level 16, State 8, Line 1
> Error converting data type DBTYPE_DBTIMESTAMP to datetime.
> Here's an example of what the field looks like in the AS/400 table:
> 1999-08-24 00:00:00.000
> I thought that I could just as "where date = '2005-07-01 00:00:00.00'"
> but
> it doesn't work.
> Any thoughts'
> Thanks in advance.sql

Error 8114 Date data

I am trying to pull over data in for a specified date from an ODBC connected
AS/400 table, and I get the following error.
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Here's an example of what the field looks like in the AS/400 table:
1999-08-24 00:00:00.000
I thought that I could just as "where date = '2005-07-01 00:00:00.00'" but
it doesn't work.
Any thoughts?
Thanks in advance.
This could be the limitation of the provider you use. Try a different or
newer provider.
Adrian
"aschossler" <aschossler@.discussions.microsoft.com> wrote in message
news:6C6CD9AF-F37E-459E-9A42-64CE221D2E94@.microsoft.com...
>I am trying to pull over data in for a specified date from an ODBC
>connected
> AS/400 table, and I get the following error.
> Server: Msg 8114, Level 16, State 8, Line 1
> Error converting data type DBTYPE_DBTIMESTAMP to datetime.
> Here's an example of what the field looks like in the AS/400 table:
> 1999-08-24 00:00:00.000
> I thought that I could just as "where date = '2005-07-01 00:00:00.00'"
> but
> it doesn't work.
> Any thoughts?
> Thanks in advance.

Error 8114 Date data

I am trying to pull over data in for a specified date from an ODBC connected
AS/400 table, and I get the following error.
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Here's an example of what the field looks like in the AS/400 table:
1999-08-24 00:00:00.000
I thought that I could just as "where date = '2005-07-01 00:00:00.00'" but
it doesn't work.
Any thoughts'
Thanks in advance.This could be the limitation of the provider you use. Try a different or
newer provider.
Adrian
"aschossler" <aschossler@.discussions.microsoft.com> wrote in message
news:6C6CD9AF-F37E-459E-9A42-64CE221D2E94@.microsoft.com...
>I am trying to pull over data in for a specified date from an ODBC
>connected
> AS/400 table, and I get the following error.
> Server: Msg 8114, Level 16, State 8, Line 1
> Error converting data type DBTYPE_DBTIMESTAMP to datetime.
> Here's an example of what the field looks like in the AS/400 table:
> 1999-08-24 00:00:00.000
> I thought that I could just as "where date = '2005-07-01 00:00:00.00'"
> but
> it doesn't work.
> Any thoughts'
> Thanks in advance.

error 8102:cannot update identity column

HI

I have 2 SQL Servers 2000 enterprise version. I setup a transactional replication between them. There is a table using identity feature and set to not for replication. On the server A as publication, B as Subscriber. I created table in B manually and set the name conflict in article default to keep existing data. When it replicates at the first time, everything is fine, but if there is some data added to the table in Server A, the replication fails, saying cannot update indentity column.

I read about something on add Set Identity on/off thing. But I dont know which SP I should add this to. Please help. As detail as possible. Thanks.There is a bug in the Managed Range Identity system. In Merge Replication I have found that while creating a publication using range identities it will work only the first time. Changing settings such as Publisher range or subscriber range does not have an effect. Also, deleting the subscriptions and publications DOES NOT remove the constraints from the affected subscriber tables. Also, the entry for the database in the replication distribution DB table MSrepl_identity_range does not get deleted and therefore the next time the publication is run it will not take the correct settings. In order to successfully recreate the subscription you must delete the row(s) from the afore mentioned table and remove all constraints manually. Only then will it work. If the constraints are not removed the subscriber will show the identity range as used up and will demand a re-sync, and the re-sync will have no effect.

Applies to SQL Server 2000 All SPs

You MUST delete all publications on the affected database and subscriptions first, then run SP_RemoveDBReplication DBName on the publisher and subscribers first then follow the steps above. You can sp_help to see what constraints are on the tables.|||I am sorry, if I undertsand you correctly what you are saying is that I cannot have a replication set up for identity enabled table?|||Originally posted by cobraeyez
I am sorry, if I undertsand you correctly what you are saying is that I cannot have a replication set up for identity enabled table?

no, you can set it up. Just keep in mind that there is a bug in SQL Server relating to the identitiy managing subsystem. What you want to do is delete the pub, subscriptions, drop the Check Constraints on all the subscription tables, find the Identity_Range table in the Replication Distribution database and delete all entries for the database that you are working on. Then recreate the publication and subcription and you will be fine.|||hey. thanks that SP_RemoveDBReplication thing works. :)|||ok, maybe I speak too quick, I think the insert works, but the update doesn't . If I modify a record in the publisher the replication failed again.

And what I did for fix the insert record is remove the publications and subscriptions and ran that RemoveReplication command on the DBs, there is no records in MSrepl_indentity_range table for me to delete.|||ok. The article containing the identitiy field is not set to range identitiy. When you set up the publication you need to click the "..." button to the right of the article name, then click the "Identity Range" tab. Enter your values and click OK to start the Identitiy Ranging.|||thanks, you mean the table article property button (...) right? In there, there is no Identity Range Tab. There are 3 tabs in it. General, Command, Snapshot.|||You will only have that tab on articles that have an identitiy column. Are you sure the article has an Identitiy?|||yes, and I am using sql 2000, identity is enabled and set not for replication|||You've got much of suggestions to the question, but I hope this MSDN Link (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01d4.asp) also gives you much insight to resolve.

HTH|||I had a similar problem and it was because I had triggers and DRI on the same table

Error 80070006

Hello everyone,
I have a large table (around 15m rows) I'm trying to Full-Text index using
the Japanese wordbreaker.
When I do a full process, after processing 3-4 million rows, I see a couple
of "Error Fetching URL (80070006 - Invalid handle)" records in the Gather
Log, followed by many many c0000005 records in the log.
At that point, the MSSearch service is still running, but if I try to shut
it down using the sql server service manager or the windows tasks control
panel, it hangs while stopping. I have to kill it using the task manager
process panel.
I tried applying SP4 and recreating the catalog, but I have the same problem.
Any suggestions?
Regards,
Jonathan
does this apply -
http://support.microsoft.com/default...b;en-us;298794
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:23546C67-AAE7-43AB-B435-C255BB43CF0B@.microsoft.com...
> Hello everyone,
> I have a large table (around 15m rows) I'm trying to Full-Text index using
> the Japanese wordbreaker.
> When I do a full process, after processing 3-4 million rows, I see a
couple
> of "Error Fetching URL (80070006 - Invalid handle)" records in the Gather
> Log, followed by many many c0000005 records in the log.
> At that point, the MSSearch service is still running, but if I try to shut
> it down using the sql server service manager or the windows tasks control
> panel, it hangs while stopping. I have to kill it using the task manager
> process panel.
> I tried applying SP4 and recreating the catalog, but I have the same
problem.
> Any suggestions?
> Regards,
> Jonathan
|||Hilary,
Thanks very much for your reply.
"Hilary Cotter" wrote:
> does this apply -
> http://support.microsoft.com/default...b;en-us;298794
I don't think so. I have increased MaxPropStoreCacheSize gradually to 0x0460.
Regards,
Jonathan
|||This is possibly a buffer overrun. How are you storing your data? Is it in
char, varchar, text or image? If Image what is your document format?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:23546C67-AAE7-43AB-B435-C255BB43CF0B@.microsoft.com...
> Hello everyone,
> I have a large table (around 15m rows) I'm trying to Full-Text index using
> the Japanese wordbreaker.
> When I do a full process, after processing 3-4 million rows, I see a
couple
> of "Error Fetching URL (80070006 - Invalid handle)" records in the Gather
> Log, followed by many many c0000005 records in the log.
> At that point, the MSSearch service is still running, but if I try to shut
> it down using the sql server service manager or the windows tasks control
> panel, it hangs while stopping. I have to kill it using the task manager
> process panel.
> I tried applying SP4 and recreating the catalog, but I have the same
problem.
> Any suggestions?
> Regards,
> Jonathan
|||Hi again Hilary,
"Hilary Cotter" wrote:
> This is possibly a buffer overrun. How are you storing your data? Is it in
> char, varchar, text or image? If Image what is your document format?
>
Buffer overrun sounds likely to me.
The data is in two columns: one big nvarchar, and an ntext.
It is Japanese text from web pages (the web pages have been stripped of HTML
tags).
Regards,
Jonathan
|||just out of curiosity, do you get the problem when you index the content
with the neutral word breaker?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:21043DBE-5885-41FC-A68B-462A7A6DEA47@.microsoft.com...
> Hi again Hilary,
>
> "Hilary Cotter" wrote:
> Buffer overrun sounds likely to me.
> The data is in two columns: one big nvarchar, and an ntext.
> It is Japanese text from web pages (the web pages have been stripped of
> HTML
> tags).
> Regards,
> Jonathan
>
|||Hilary,
"Hilary Cotter" wrote:
> just out of curiosity, do you get the problem when you index the content
> with the neutral word breaker?
>
I haven't tried the neutral word breaker. The neutral word breaker doesn't
handle Japanese text very well: most Japanese is written without whitespace
between the words, so I don't think the neutral word breaker index would be
very useful even if it worked.
Jonathan
|||I realize you are loosing the language capabilities you need - however if it
works using the neutral word breaker the problem is with the Japanese word
breaker and you can raise this as a bug with MS. If it fails with the
neutral word breaker something is wrong with your environment which might be
solved with a reapplication of the service pack, or by removing and
reinstalling SQL FTS.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:51A6B6D7-D439-4351-B619-D8645494DDF5@.microsoft.com...
> Hilary,
> "Hilary Cotter" wrote:
> I haven't tried the neutral word breaker. The neutral word breaker
doesn't
> handle Japanese text very well: most Japanese is written without
whitespace
> between the words, so I don't think the neutral word breaker index would
be
> very useful even if it worked.
> Jonathan
|||"Hilary Cotter" wrote:
> I realize you are loosing the language capabilities you need - however if it
> works using the neutral word breaker the problem is with the Japanese word
> breaker and you can raise this as a bug with MS. If it fails with the
> neutral word breaker something is wrong with your environment which might be
> solved with a reapplication of the service pack, or by removing and
> reinstalling SQL FTS.
Hilary,
Your instincts were right on. The problem was in the Windows 2003
wordbreaker -- upgrading to SP1 fixed the problem. (see KB article 885899)
Thanks for your help!
Jonathan
http://kerblog.com/earlyedition/arch...05/27/460.aspx

Error 7410 when doing a SELECT from a Link table in xp_sendmail

Really would appreciate some advice on this problem. I have a cluster, CORPUSGA and a stand alone server CORPUSAPP22. CORPUSAPP22 has a linked server set up to CORPUSGA using 'sa' as the security context. Within a stored procedure I am running a SELECT to CORPUSGA.msdb.dbo.sysjobs.

When this is run from Query Analyzer it is fine and runs under the 'dbo' security context, but when is is run under xp_sendmail that uses the SQL Agent's security context (a domain account called
IntServiceGA) it fails with this message:

ODBC error 7410 (42000) Remote access not allowed for Windows NT user activated by SETUSER.

For the following command:

exec master.dbo.xp_sendmail
@.recipients = 'stelzner_eve@.emc.com;dabas_ravi@.emc.com;bergin_gi llian@.emc.com;lynch_eoin@.e
mc.com;sherman_ nancy@.emc.com',
@.message = @.email,
@.query = @.query_text,
@.no_header = 'true',
@.width=80,
@.subject = 'Job Failed on CORPUSGA ',
@.attach_results = 'true',
@.set_user = 'dbo'

Where @.query_text is something like "select * from CORPUSGA.msdb.dbo.sysjobs"

The IntServiceGA account is identical on both servers and is in the local administrators group on both servers and is in the system administrators role within SQL Server, has access to the database and sysjobs has granted permissions to IntServiceGA (SELECT, INSERT,UPDATE,DELETE).

When I run profiler against this, it tries to execute under IntServiceGA (an Windows authentication account) and then tries to SETUSER to sa . This is what it objects to the switching. I have tried to set up the IntServiceGA as the security context for the linked server too and this does not work, same error.

Any help would be great,
Thanks,
EveWhy do you need @.set_user if SQLAgent service account is already in sysadmin server role?|||I have another stored procedure that uses this and it works with the
@.set_user on this one. However, whether I leave it in or take out the
@.set_user parameter it still fails. Any ideas?

Thanks,
Eve|||Try to run your xp_sendmail after doing SETUSER 'DOMAIN_NAME\IntServiceGA' and see what you get.|||no go, using the SETUSER prior to the xp_sendmail still netted the
same results? Do you know what generates this message- NT or SQL?|||It's NT. It just proved that your SQLAgent account is lacking some rights. Was mail profile setup using SQLAgent service account?|||Yes, the profile was set up on both servers from the
corp\IntServiceGA account that is a local admin on
the both servers. Are there additional policies that need
to be put on for this account that are not in the default?

error 7391 in distributed transaction

I have a procedure running locally on my server A. This procedure does an
insert into a table after getting a return value from a select done on
another server say B . Server B is set up as linked server on server A. th
error i get shows as
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Has anyone seen this before? Is there any solution to this or any setting
that needs to be changed to get this working? any help will be greatly
appreciated.
TIA
Message posted via http://www.webservertalk.comFor distributed transaction you need to have a Primary key on updated table.
So pLease add primary key to resolve your issue. Please let me know if
doesn't work.
Regards,
Jagan Mohan
MCDBA
"ishaan99 via webservertalk.com" wrote:

> I have a procedure running locally on my server A. This procedure does an
> insert into a table after getting a return value from a select done on
> another server say B . Server B is set up as linked server on server A. th
> error i get shows as
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Has anyone seen this before? Is there any solution to this or any setting
> that needs to be changed to get this working? any help will be greatly
> appreciated.
> TIA
> --
> Message posted via http://www.webservertalk.com
>|||I've had this same error, mine only happened during times when transactions
were high on the system, I think there is a setting to increase the number o
f
concurrent distributed transactions, don't know what else you could do, I
know it has to do with MSDTC.
"ishaan99 via webservertalk.com" wrote:

> I have a procedure running locally on my server A. This procedure does an
> insert into a table after getting a return value from a select done on
> another server say B . Server B is set up as linked server on server A. th
> error i get shows as
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Has anyone seen this before? Is there any solution to this or any setting
> that needs to be changed to get this working? any help will be greatly
> appreciated.
> TIA
> --
> Message posted via http://www.webservertalk.com
>sql

Error 7306 when trying to do an update on a linked server

Here is my query run on one server against a Linked server:
UPDATE [Linked-Name].dbname.dbo.[Table name]
SET TimeStamp = '1900-01-01 00:00:00'
WHERE ISDATE(TimeStamp) = 0
I get the following error message:
Could not open table '"dbname"."dbo"."Table name"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work
was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT
VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID
VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate
VALUE=True STATUS=DBPROPSTATUS_CONFLICTING],
[PROPID=DBPROP_IRowsetChange VA...
Please help!
Thanks.What platform is the linked server?
<ryan_willow@.hotmail.com> wrote in message
news:1150831576.021477.128570@.p79g2000cwp.googlegroups.com...
> Here is my query run on one server against a Linked server:
> UPDATE [Linked-Name].dbname.dbo.[Table name]
> SET TimeStamp = '1900-01-01 00:00:00'
> WHERE ISDATE(TimeStamp) = 0
> I get the following error message:
> Could not open table '"dbname"."dbo"."Table name"' from OLE DB provider
> 'SQLOLEDB'. The provider could not support a row lookup position. The
> provider indicates that conflicts occurred with other properties or
> requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
> generated errors. Check each OLE DB status value, if available. No work
> was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT
> VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID
> VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate
> VALUE=True STATUS=DBPROPSTATUS_CONFLICTING],
> [PROPID=DBPROP_IRowsetChange VA...
> Please help!
> Thanks.
>|||SQL Server 2000.
I believe that this is the problem:
http://msdn.microsoft.com/library/d...serr_2_4gqh.asp
i.e. UPDATE, and DELETE statements are not supported on the remote
table if the table does not have a unique index defined on it.
So now I'm here:
I've done DELETEs remotely and had no problem, only errors on UPDATE.
I don't want to modify this established database by defining unique
indices.
How can I get around this?
Mike C# wrote:
> What platform is the linked server?
> <ryan_willow@.hotmail.com> wrote in message
> news:1150831576.021477.128570@.p79g2000cwp.googlegroups.com...|||ryan_willow@.hotmail.com wrote:
> SQL Server 2000.
> I believe that this is the problem:
> http://msdn.microsoft.com/library/d...serr_2_4gqh.asp
> i.e. UPDATE, and DELETE statements are not supported on the remote
> table if the table does not have a unique index defined on it.
> So now I'm here:
> I've done DELETEs remotely and had no problem, only errors on UPDATE.
> I don't want to modify this established database by defining unique
> indices.
> How can I get around this?
> Mike C# wrote:
What is version of MDAC?
and Service Pack?
We have also a problem like this and after updating to latest version
problem was solved
Regards
Amish Shah

Error 6913 error when I save to a typed xml field.

I am getting an 6913 error when I save to a typed xml field.
I have a table called Is.Lead which has the following collums
IsLead_Id Primary Key, int
IsLead_XmlRaw varchar(max)
IsLead_XmlWellFormed untyped Xml data type
IsLead_XmlAHG typed Xml data type xml(CONTENT
dbo.AmericanHomeguideSchemaCollection)
I also have a sproc called Is_uspProcessLeads that among other things,
determines if a an xml file is well formed, and if it is valid. I do
this by using a sql update statement and if there is an error, an entry
is made into a log table. The update statements look like the
following...
UPDATE[Is.Lead]
SETIsLead_XmlWellFormed= IsLead_XmlRaw,
IsLead_LeadStatus_Id= @.LEAD_STATUS_ID_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
UPDATE[Is.Lead]
SETIsLead_XmlAHG= IsLead_XmlRaw -- IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
When I get to the second update statement, I get the following error...
Error Number: 6913
Error Message: XML Validation: Declaration not found for element
'Leads'. Location: /*:Leads[1]
Below is the script on how I created my XSD...
Use Integration
go
CREATE XML SCHEMA COLLECTION
[dbo].[AmericanHomeguideSchemaCollection] AS
N'<?xml version="1.0"?>
<xsd:schema
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
xmlns
="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="Leads">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" name="Source" type="xsd:string" />
<xsd:element minOccurs="0" name="Community_Name"
type="xsd:string" />
<xsd:element minOccurs="0" name="SubdivisionID"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Community_State" />
<xsd:element minOccurs="0" name="Community_Region"
type="xsd:string" />
<xsd:element minOccurs="0" name="Community_City"
type="xsd:string" />
<xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="Street" type="xsd:string" />
<xsd:element minOccurs="0" name="City" type="xsd:string" />
<xsd:element minOccurs="0" name="State" type="xsd:string" />
<xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Phone" type="xsd:string" />
<xsd:element minOccurs="0" name="Email" type="xsd:string" />
<xsd:element minOccurs="0" name="Best_Time_To_Call"
type="xsd:string" />
<xsd:element minOccurs="0" name="Month_Planned_Relocation"
type="xsd:string" />
<xsd:element minOccurs="0" name="Year_Planned_Relocation"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Comments" type="xsd:string" />
<xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>' ;
GO
After I create my schema collection, I verify it by running these two
select statements.
select * from sys.xml_schema_collections
select * from sys.xml_schema_namespaces
sys.xml_schema_collections
xml_collection_idschema_idprincipal_idNamecreate_datemodify_date
14NULLSys2005-09-02 22:30:10.8402005-09-02 22:30:11.013
655631NULLAmericanHomeguideSchemaCollection2005-12-19
10:23:29.2432005-12-19 10:23:29.243
sys.xml_schema_namespaces
xml_collection_idNamexml_namespace_id
1http://www.w3.org/2001/XMLSchema
1
1http://schemas.microsoft.com/sqlserver/2004/sqltypes
2
1http://www.w3.org/XML/1998/namespace
3
65563http://www.ryland.com/Integration/Ex...ricanHomeguide
1
Below is some sample data that I am using for testing...
<?xml version="1.0" encoding="windows-1250"?>
<Leads>
<Lead>
<Source>NHG</Source>
<Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
<SubdivisionID>34621</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Atlanta</Community_City>
<Last_Name> FakeLastName </Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxxxx</Street>
<City>college park</City>
<State>ga</State>
<Zip>30349</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
<Year_Planned_Relocation>2006</Year_Planned_Relocation>
<Comments>NA</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
<Lead>
<Source>NHG</Source>
<Community_Name>Cottage Cove-Ryland Homes</Community_Name>
<SubdivisionID>34605</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Dallas</Community_City>
<Last_Name>FakeLastName</Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxx</Street>
<City>rome</City>
<State>ga</State>
<Zip>30165</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
<Year_Planned_Relocation>2007</Year_Planned_Relocation>
<Comments>No Comments Offered.</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
</Leads>
Your schema declares that the data has a target namespace:
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
But the instance data that you insert does not use the namespace. Since the
namespace is used to bind the names to the schema, the schema validator
cannot find a schema for the names without namespace and raises the
validation error 6913.
You have two options:
Either not use a targetNamespace on your schema or add namespaces to your
XML documents.
Best regards
Michael
"Freddie Tripples" <JohnMarsing@.gmail.com> wrote in message
news:1135022743.002785.162690@.g49g2000cwa.googlegr oups.com...
>I am getting an 6913 error when I save to a typed xml field.
> I have a table called Is.Lead which has the following collums
> IsLead_Id Primary Key, int
> IsLead_XmlRaw varchar(max)
> IsLead_XmlWellFormed untyped Xml data type
> IsLead_XmlAHG typed Xml data type xml(CONTENT
> dbo.AmericanHomeguideSchemaCollection)
> I also have a sproc called Is_uspProcessLeads that among other things,
> determines if a an xml file is well formed, and if it is valid. I do
> this by using a sql update statement and if there is an error, an entry
> is made into a log table. The update statements look like the
> following...
> UPDATE [Is.Lead]
> SET IsLead_XmlWellFormed = IsLead_XmlRaw,
> IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
>
> UPDATE [Is.Lead]
> SET IsLead_XmlAHG = IsLead_XmlRaw -- IsLead_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
> When I get to the second update statement, I get the following error...
> Error Number: 6913
> Error Message: XML Validation: Declaration not found for element
> 'Leads'. Location: /*:Leads[1]
> Below is the script on how I created my XSD...
> Use Integration
> go
> CREATE XML SCHEMA COLLECTION
> [dbo].[AmericanHomeguideSchemaCollection] AS
> N'<?xml version="1.0"?>
> <xsd:schema
> targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
> xmlns
> ="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
> elementFormDefault="qualified"
> attributeFormDefault="unqualified"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
> <xsd:element name="Leads">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" name="Source" type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_Name"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="SubdivisionID"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Community_State" />
> <xsd:element minOccurs="0" name="Community_Region"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_City"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="Street" type="xsd:string" />
> <xsd:element minOccurs="0" name="City" type="xsd:string" />
> <xsd:element minOccurs="0" name="State" type="xsd:string" />
> <xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Phone" type="xsd:string" />
> <xsd:element minOccurs="0" name="Email" type="xsd:string" />
> <xsd:element minOccurs="0" name="Best_Time_To_Call"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Month_Planned_Relocation"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Year_Planned_Relocation"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Comments" type="xsd:string" />
> <xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>' ;
> GO
> After I create my schema collection, I verify it by running these two
> select statements.
> select * from sys.xml_schema_collections
> select * from sys.xml_schema_namespaces
>
> sys.xml_schema_collections
> xml_collection_id schema_id principal_id Name create_date modify_date
> 1 4 NULL Sys 2005-09-02 22:30:10.840 2005-09-02 22:30:11.013
> 65563 1 NULL AmericanHomeguideSchemaCollection 2005-12-19
> 10:23:29.243 2005-12-19 10:23:29.243
>
> sys.xml_schema_namespaces
> xml_collection_id Name xml_namespace_id
> 1 http://www.w3.org/2001/XMLSchema
> 1
> 1 http://schemas.microsoft.com/sqlserver/2004/sqltypes
> 2
> 1 http://www.w3.org/XML/1998/namespace
> 3
> 65563 http://www.ryland.com/Integration/Ex...ricanHomeguide
> 1
>
> Below is some sample data that I am using for testing...
> <?xml version="1.0" encoding="windows-1250"?>
> <Leads>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
> <SubdivisionID>34621</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Atlanta</Community_City>
> <Last_Name> FakeLastName </Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxxxx</Street>
> <City>college park</City>
> <State>ga</State>
> <Zip>30349</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
> <Year_Planned_Relocation>2006</Year_Planned_Relocation>
> <Comments>NA</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Cottage Cove-Ryland Homes</Community_Name>
> <SubdivisionID>34605</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Dallas</Community_City>
> <Last_Name>FakeLastName</Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxx</Street>
> <City>rome</City>
> <State>ga</State>
> <Zip>30165</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
> <Year_Planned_Relocation>2007</Year_Planned_Relocation>
> <Comments>No Comments Offered.</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> </Leads>
>
|||Thanks
*** Sent via Developersdex http://www.codecomments.com ***

Error 6913 error when I save to a typed xml field.

I am getting an 6913 error when I save to a typed xml field.
I have a table called Is.Lead which has the following collums
IsLead_Id Primary Key, int
IsLead_XmlRaw varchar(max)
IsLead_XmlWellFormed untyped Xml data type
IsLead_XmlAHG typed Xml data type xml(CONTENT
dbo.AmericanHomeguideSchemaCollection)
I also have a sproc called Is_uspProcessLeads that among other things,
determines if a an xml file is well formed, and if it is valid. I do
this by using a sql update statement and if there is an error, an entry
is made into a log table. The update statements look like the
following...
UPDATE [Is.Lead]
SET IsLead_XmlWellFormed = IsLead_XmlRaw,
IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
UPDATE [Is.Lead]
SET IsLead_XmlAHG = IsLead_XmlRaw -- IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
When I get to the second update statement, I get the following error...
Error Number: 6913
Error Message: XML Validation: Declaration not found for element
'Leads'. Location: /*:Leads[1]
Below is the script on how I created my XSD...
Use Integration
go
CREATE XML SCHEMA COLLECTION
[dbo].[AmericanHomeguideSchemaCollection] AS
N'<?xml version="1.0"?>
<xsd:schema
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguid
e"
xmlns
="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="Leads">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" name="Source" type="xsd:string" />
<xsd:element minOccurs="0" name="Community_Name"
type="xsd:string" />
<xsd:element minOccurs="0" name="SubdivisionID"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Community_State" />
<xsd:element minOccurs="0" name="Community_Region"
type="xsd:string" />
<xsd:element minOccurs="0" name="Community_City"
type="xsd:string" />
<xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
<xsd:element minOccurs="0" name="Street" type="xsd:string" />
<xsd:element minOccurs="0" name="City" type="xsd:string" />
<xsd:element minOccurs="0" name="State" type="xsd:string" />
<xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Phone" type="xsd:string" />
<xsd:element minOccurs="0" name="Email" type="xsd:string" />
<xsd:element minOccurs="0" name="Best_Time_To_Call"
type="xsd:string" />
<xsd:element minOccurs="0" name="Month_Planned_Relocation"
type="xsd:string" />
<xsd:element minOccurs="0" name="Year_Planned_Relocation"
type="xsd:unsignedShort" />
<xsd:element minOccurs="0" name="Comments" type="xsd:string" />
<xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>' ;
GO
After I create my schema collection, I verify it by running these two
select statements.
select * from sys.xml_schema_collections
select * from sys.xml_schema_namespaces
sys.xml_schema_collections
xml_collection_id schema_id principal_id
Name create_date modify_date
1 4 NULL Sys 2005-09-02 22:30:10.840 2005-09-02 22:30:11.013
65563 1 NULL AmericanHomeguideSchemaColl
ection 2005-12-19
10:23:29.243 2005-12-19 10:23:29.243
sys.xml_schema_namespaces
xml_collection_id Name xml_namespace_id
1 http://www.w3.org/2001/XMLSchema
1
1 http://schemas.microsoft.com/sqlserver/2004/sqltypes
2
1 http://www.w3.org/XML/1998/namespace
3
65563 http://www.ryland.com/Integration/E...ericanHomeguide
1
Below is some sample data that I am using for testing...
<?xml version="1.0" encoding="windows-1250"?>
<Leads>
<Lead>
<Source>NHG</Source>
<Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
<SubdivisionID>34621</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Atlanta</Community_City>
<Last_Name> FakeLastName </Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxxxx</Street>
<City>college park</City>
<State>ga</State>
<Zip>30349</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
<Year_Planned_Relocation>2006</Year_Planned_Relocation>
<Comments>NA</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
<Lead>
<Source>NHG</Source>
<Community_Name>Cottage Cove-Ryland Homes</Community_Name>
<SubdivisionID>34605</SubdivisionID>
<Community_State></Community_State>
<Community_Region>GA</Community_Region>
<Community_City>Dallas</Community_City>
<Last_Name>FakeLastName</Last_Name>
<First_Name>FakeFirstName</First_Name>
<Street>xxxxxxxxxxxxxxx</Street>
<City>rome</City>
<State>ga</State>
<Zip>30165</Zip>
<Phone>999999999</Phone>
<Email>Fake@.Fake.net</Email>
<Best_Time_To_Call>NA</Best_Time_To_Call>
<Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
<Year_Planned_Relocation>2007</Year_Planned_Relocation>
<Comments>No Comments Offered.</Comments>
<DateEntered>2005-11-15</DateEntered>
</Lead>
</Leads>Your schema declares that the data has a target namespace:
targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomeguid
e"
But the instance data that you insert does not use the namespace. Since the
namespace is used to bind the names to the schema, the schema validator
cannot find a schema for the names without namespace and raises the
validation error 6913.
You have two options:
Either not use a targetNamespace on your schema or add namespaces to your
XML documents.
Best regards
Michael
"Freddie Tripples" <JohnMarsing@.gmail.com> wrote in message
news:1135022743.002785.162690@.g49g2000cwa.googlegroups.com...
>I am getting an 6913 error when I save to a typed xml field.
> I have a table called Is.Lead which has the following collums
> IsLead_Id Primary Key, int
> IsLead_XmlRaw varchar(max)
> IsLead_XmlWellFormed untyped Xml data type
> IsLead_XmlAHG typed Xml data type xml(CONTENT
> dbo.AmericanHomeguideSchemaCollection)
> I also have a sproc called Is_uspProcessLeads that among other things,
> determines if a an xml file is well formed, and if it is valid. I do
> this by using a sql update statement and if there is an error, an entry
> is made into a log table. The update statements look like the
> following...
> UPDATE [Is.Lead]
> SET IsLead_XmlWellFormed = IsLead_XmlRaw,
> IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
>
> UPDATE [Is.Lead]
> SET IsLead_XmlAHG = IsLead_XmlRaw -- IsLead_XmlWellFormed
> WHERE IsLead_Id = @.IsLead_Id
> When I get to the second update statement, I get the following error...
> Error Number: 6913
> Error Message: XML Validation: Declaration not found for element
> 'Leads'. Location: /*:Leads[1]
> Below is the script on how I created my XSD...
> Use Integration
> go
> CREATE XML SCHEMA COLLECTION
> [dbo].[AmericanHomeguideSchemaCollection] AS
> N'<?xml version="1.0"?>
> <xsd:schema
> targetNamespace="http://www.ryland.com/Integration/Extracts/AmericanHomegu
ide"
> xmlns
> ="http://www.ryland.com/Integration/Extracts/AmericanHomeguide"
> elementFormDefault="qualified"
> attributeFormDefault="unqualified"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
> <xsd:element name="Leads">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" maxOccurs="unbounded" name="Lead">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element minOccurs="0" name="Source" type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_Name"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="SubdivisionID"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Community_State" />
> <xsd:element minOccurs="0" name="Community_Region"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Community_City"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Last_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="First_Name" type="xsd:string" />
> <xsd:element minOccurs="0" name="Street" type="xsd:string" />
> <xsd:element minOccurs="0" name="City" type="xsd:string" />
> <xsd:element minOccurs="0" name="State" type="xsd:string" />
> <xsd:element minOccurs="0" name="Zip" type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Phone" type="xsd:string" />
> <xsd:element minOccurs="0" name="Email" type="xsd:string" />
> <xsd:element minOccurs="0" name="Best_Time_To_Call"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Month_Planned_Relocation"
> type="xsd:string" />
> <xsd:element minOccurs="0" name="Year_Planned_Relocation"
> type="xsd:unsignedShort" />
> <xsd:element minOccurs="0" name="Comments" type="xsd:string" />
> <xsd:element minOccurs="0" name="DateEntered" type="xsd:date" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>' ;
> GO
> After I create my schema collection, I verify it by running these two
> select statements.
> select * from sys.xml_schema_collections
> select * from sys.xml_schema_namespaces
>
> sys.xml_schema_collections
> xml_collection_id schema_id principal_id Name create_date modify_date
> 1 4 NULL Sys 2005-09-02 22:30:10.840 2005-09-02 22:30:11.013
> 65563 1 NULL AmericanHomeguideSchemaCollection 2005-12-19
> 10:23:29.243 2005-12-19 10:23:29.243
>
> sys.xml_schema_namespaces
> xml_collection_id Name xml_namespace_id
> 1 http://www.w3.org/2001/XMLSchema
> 1
> 1 http://schemas.microsoft.com/sqlserver/2004/sqltypes
> 2
> 1 http://www.w3.org/XML/1998/namespace
> 3
> 65563 http://www.ryland.com/Integration/E...ericanHomeguide
> 1
>
> Below is some sample data that I am using for testing...
> <?xml version="1.0" encoding="windows-1250"?>
> <Leads>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Havens at Walden Park-Ryland Homes</Community_Name>
> <SubdivisionID>34621</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Atlanta</Community_City>
> <Last_Name> FakeLastName </Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxxxx</Street>
> <City>college park</City>
> <State>ga</State>
> <Zip>30349</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2006</Month_Planned_Relocation>
> <Year_Planned_Relocation>2006</Year_Planned_Relocation>
> <Comments>NA</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> <Lead>
> <Source>NHG</Source>
> <Community_Name>Cottage Cove-Ryland Homes</Community_Name>
> <SubdivisionID>34605</SubdivisionID>
> <Community_State></Community_State>
> <Community_Region>GA</Community_Region>
> <Community_City>Dallas</Community_City>
> <Last_Name>FakeLastName</Last_Name>
> <First_Name>FakeFirstName</First_Name>
> <Street>xxxxxxxxxxxxxxx</Street>
> <City>rome</City>
> <State>ga</State>
> <Zip>30165</Zip>
> <Phone>999999999</Phone>
> <Email>Fake@.Fake.net</Email>
> <Best_Time_To_Call>NA</Best_Time_To_Call>
> <Month_Planned_Relocation>/01/2007</Month_Planned_Relocation>
> <Year_Planned_Relocation>2007</Year_Planned_Relocation>
> <Comments>No Comments Offered.</Comments>
> <DateEntered>2005-11-15</DateEntered>
> </Lead>
> </Leads>
>|||Thanks
*** Sent via Developersdex http://www.examnotes.net ***

Error 6522 Trying to Run Assembly from database

Hi,
I am attempting to load an assembly that has been stored in a table as a byte array. I have created a c# class called AssemblyLoader that takes in 2 parameters, the assembly name and the parameters for the assembly (just a query string).
From this it returns the assembly byte array using a simple sql statement within the class using the assembly name to select the assembly bytes to return from the database.

The returned byte array of the assembly is loaded using:
Assembly assembly = Assembly.Load(assemblyBytes);

This seems to be the line that SQL Server 2005 is erroring on. Do I need to add some extra assemblies to the database and if so which ones.

Error:
Msg 6522, Level 16, State 1, Procedure ClientInterface, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'ClientInterface':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at Reflector.runQuery(String query, String parameter)
Thanks for your help.

SQL Server does not allow you to load an assembly from file, nor byte array. You can only call Assembly.Load(), in which case the assembly has to be catalogued in the database.

Niels|||Cheers Niels
I am using Assembly.Load with the Full name of the assembly and it works fine.
Thanks|||So does this mean that you can create a Class Library project, compile it and then simply place the assembly inside SQL 2005 to get access to external objects?
Just to confirm, you are not limited to placing the output of SQL Server Project inside SQL Server 2005?

Thank you,

Lubomir|||Thats pretty much all I have done. I have created 3 seperate Class Library Projects that each extend a common interface so that they all have the method

public static string execute(string data)

and are compiled and added to the database. They are loaded depending on the query I pass in and they go off and get data from different interfaces ie, LDAP, HTTP SOAP etc... and return me a simple string.

N
|||Lubomir,
A SQL CLR Project is just a project type in VS, which makes it easier for the developer to create and deploy assemblies to SQL Server. Under the cover the SQL CLR Project is just calling CREATE ASSEMBLY ... etc, in order to deploy.

Niels

Error 6522 Trying to Run Assembly from database

Hi,
I am attempting to load an assembly that has been stored in a table as a byte array. I have created a c# class called AssemblyLoader that takes in 2 parameters, the assembly name and the parameters for the assembly (just a query string).
From this it returns the assembly byte array using a simple sql statement within the class using the assembly name to select the assembly bytes to return from the database.

The returned byte array of the assembly is loaded using:
Assembly assembly = Assembly.Load(assemblyBytes);

This seems to be the line that SQL Server 2005 is erroring on. Do I need to add some extra assemblies to the database and if so which ones.

Error:
Msg 6522, Level 16, State 1, Procedure ClientInterface, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'ClientInterface':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at Reflector.runQuery(String query, String parameter)
Thanks for your help.

SQL Server does not allow you to load an assembly from file, nor byte array. You can only call Assembly.Load(), in which case the assembly has to be catalogued in the database.

Niels
|||Cheers Niels
I am using Assembly.Load with the Full name of the assembly and it works fine.
Thanks
|||So does this mean that you can create a Class Library project, compile it and then simply place the assembly inside SQL 2005 to get access to external objects?
Just to confirm, you are not limited to placing the output of SQL Server Project inside SQL Server 2005?

Thank you,

Lubomir|||Thats pretty much all I have done. I have created 3 seperate Class Library Projects that each extend a common interface so that they all have the method

public static string execute(string data)

and are compiled and added to the database. They are loaded depending on the query I pass in and they go off and get data from different interfaces ie, LDAP, HTTP SOAP etc... and return me a simple string.

N
|||Lubomir,
A SQL CLR Project is just a project type in VS, which makes it easier for the developer to create and deploy assemblies to SQL Server. Under the cover the SQL CLR Project is just calling CREATE ASSEMBLY ... etc, in order to deploy.

Niels

Error 605

Our SQL Server 2000 database had an error last week:
Table error: Database ID 12, object ID 1909581841, index
ID 0. Chain linkage mismatch. (1:154654)->next = (1:228873), but (1:228873)->prev = (1:228872).
We sort of fixed the problem by running dbcc checkdb with
repair_rebuild clause.
It was fine for a week, then yesterday sql server gave us
an error 605:
Server: Msg 605, Level 21, State 1, Line 1
Attempt to fetch logical page (1:123632) in
database 'afsa_ecaps' belongs to object 'employer', not to
object 'consolidation_activity_log'.
Connection Broken
We ran dbcc checkdb again to fix the problem.
My concern is, why does this keep happening? Any thing I
can do to prevent these totally? Any input will appreciate.
Thanks.
PeterI dealt with this problem for a while. Error 605 is still
on my favorites page in BOL because of the frequency.
Finally I noticed this paragraph:
Finally, be sure that your system does not have write
caching enabled on the disk controller. If you suspect
this to be the problem, contact your hardware vendor.
That fixed it for me. Havent had it since.
>--Original Message--
>Our SQL Server 2000 database had an error last week:
>Table error: Database ID 12, object ID 1909581841, index
>ID 0. Chain linkage mismatch. (1:154654)->next =>(1:228873), but (1:228873)->prev = (1:228872).
>We sort of fixed the problem by running dbcc checkdb with
>repair_rebuild clause.
>It was fine for a week, then yesterday sql server gave us
>an error 605:
>Server: Msg 605, Level 21, State 1, Line 1
>Attempt to fetch logical page (1:123632) in
>database 'afsa_ecaps' belongs to object 'employer', not
to
>object 'consolidation_activity_log'.
>Connection Broken
>We ran dbcc checkdb again to fix the problem.
>My concern is, why does this keep happening? Any thing I
>can do to prevent these totally? Any input will
appreciate.
>Thanks.
>Peter
>.
>|||... Also, make sure you don't compress the database files.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:001401c3c0d3$5dee8110$a401280a@.phx.gbl...
> I dealt with this problem for a while. Error 605 is still
> on my favorites page in BOL because of the frequency.
> Finally I noticed this paragraph:
> Finally, be sure that your system does not have write
> caching enabled on the disk controller. If you suspect
> this to be the problem, contact your hardware vendor.
>
> That fixed it for me. Havent had it since.
>
> >--Original Message--
> >Our SQL Server 2000 database had an error last week:
> >
> >Table error: Database ID 12, object ID 1909581841, index
> >ID 0. Chain linkage mismatch. (1:154654)->next => >(1:228873), but (1:228873)->prev = (1:228872).
> >We sort of fixed the problem by running dbcc checkdb with
> >repair_rebuild clause.
> >
> >It was fine for a week, then yesterday sql server gave us
> >an error 605:
> >Server: Msg 605, Level 21, State 1, Line 1
> >Attempt to fetch logical page (1:123632) in
> >database 'afsa_ecaps' belongs to object 'employer', not
> to
> >object 'consolidation_activity_log'.
> >Connection Broken
> >
> >We ran dbcc checkdb again to fix the problem.
> >
> >My concern is, why does this keep happening? Any thing I
> >can do to prevent these totally? Any input will
> appreciate.
> >
> >Thanks.
> >Peter
> >
> >.
> >

Error 3724 : cannot drop table ...becuase it is published for ....

Dear helper,
I have a database that running on SQL Server 7 and it has been published for
replication for a period of time. After that period i remove the publication
and still the database running correctly without any problem.Then a change in
requirments happend, as a result i need to drop one table form the database.
When i tryed to drop the table an error show up says: "Error 3724: cannot
drop table "..." becuase it is published for replication
Paul,
I try this sp in SQL server 2000 that connect remotely with the database
running in sql server 7. Unfortunately SQLServer2000 could not find this sp
"Paul Ibison" wrote:

> Ayman,
> there is a stored procedure to do this called
> sp_MSunmarkreplinfo which takes a tablename as a
> parameter. Alternatively, setting replinfo to 0 in
> sysobjects for the particular table should do it.
> Finally, running sp_removedbreplication can be used to
> remove all traces of replication in the subscriber
> database.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||OK - I don't have a SQl Server 7.0 box here, but this
sounds feasible. In that case try one of the
alternatives - direct edit of the system table or
sp_removedbreplication with caveats mentioned before.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Error 3409 when trying to link to SQL Server table

Hi,
We are using SQL Server 2000 (latest SP) on Windows Server 2003.
Recently we made database changes where we deleted a number of columns from
the highest level table in our database hierarchy (every other table is
either a child or grandchild of this table).
Since we did that we can no linker establish links from Access 2003 to this
table. When we try, we get the following error message:
Invalid field definition <STAMP1> in definition of index or relationship.
(Error 3409)
STAMP1 is our Primary Key field.
None of the fields we deleted were index fields, and none were keys. We have
been racking our brains trying to find the issue but to no avail. Please
help. Thank you.A long time ago with access 2003 I ran into a similar problem. What I
had to do was to go into the Access application, delete all of the
linked tables, check the queries/reports, and finally relink the
tables given the names used in the queries and reports. After a
meticulous going over, it worked. I know from using it though, it
seems like everytime you make a change to the strcture (an index, PK,
etc) you have to relink the tables from Access.
Let me know what you find out.
-Sean
On Feb 29, 1:09=A0pm, Berean <Ber...@.discussions.microsoft.com> wrote:
> Hi,
> We are using SQL Server 2000 (latest SP) on Windows Server 2003.
> Recently we made database changes where we deleted a number of columns fro=m
> the highest level table in our database hierarchy (every other table is
> either a child or grandchild of this table).
> Since we did that we can no linker establish links from Access 2003 to thi=s
> table. When we try, we get the following error message:
> Invalid field definition <STAMP1> in definition of index or relationship.
> (Error 3409)
> STAMP1 is our Primary Key field.
> None of the fields we deleted were index fields, and none were keys. We ha=ve
> been racking our brains trying to find the issue but to no avail. Please
> help. Thank you.|||Hi Sean,
The effect is happening when anyone tries to link to the SQL Server table,
whether the link was preexisting or not. Even new link attempts generate the
same error.
We have tried rebuilding the indexes on SQL Server and even tried making a
dummy table with the data but no indexes. We could link to the dummy but not
to the one with indexes.
"Berean" wrote:
> Hi,
> We are using SQL Server 2000 (latest SP) on Windows Server 2003.
> Recently we made database changes where we deleted a number of columns from
> the highest level table in our database hierarchy (every other table is
> either a child or grandchild of this table).
> Since we did that we can no linker establish links from Access 2003 to this
> table. When we try, we get the following error message:
> Invalid field definition <STAMP1> in definition of index or relationship.
> (Error 3409)
> STAMP1 is our Primary Key field.
> None of the fields we deleted were index fields, and none were keys. We have
> been racking our brains trying to find the issue but to no avail. Please
> help. Thank you.|||We recently tried rebuilding and renaming all indexes, and are receiving the
same error.
To restate, this is occuring whether we open existing links to Access or try
to build new ones, and only happens on the table we dropped some columns
from.
"Berean" wrote:
> Hi Sean,
> The effect is happening when anyone tries to link to the SQL Server table,
> whether the link was preexisting or not. Even new link attempts generate the
> same error.
> We have tried rebuilding the indexes on SQL Server and even tried making a
> dummy table with the data but no indexes. We could link to the dummy but not
> to the one with indexes.
> "Berean" wrote:
> > Hi,
> >
> > We are using SQL Server 2000 (latest SP) on Windows Server 2003.
> >
> > Recently we made database changes where we deleted a number of columns from
> > the highest level table in our database hierarchy (every other table is
> > either a child or grandchild of this table).
> >
> > Since we did that we can no linker establish links from Access 2003 to this
> > table. When we try, we get the following error message:
> >
> > Invalid field definition <STAMP1> in definition of index or relationship.
> > (Error 3409)
> >
> > STAMP1 is our Primary Key field.
> >
> > None of the fields we deleted were index fields, and none were keys. We have
> > been racking our brains trying to find the issue but to no avail. Please
> > help. Thank you.