Error when trying to update

Hi All
I get this error when i open a specific table from enterprise manager and
try to update values in it.
Data has changed since the results pane was last updated. Do you want to
save your changes now?
Click Yes to save your chnages and update the database.
Click No to discard your changes and refresh the results pane.
Click Cancel to continue editing.
If i click Yes which is of course what i wnated to do from the begining i
get this error:
Row cannot be located for updating.Some values may have been changed since
it was last Read.
So i was wonderring if anybody can give me some help with that.
Thanks in Advance
Naderseems like someone else already made a change to your data or that you are
trying to make a change to a key value that is used by em to locate the
record you want to update
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"Nader Galal" <ngalal@.health-insights.com> wrote in message
news:uNxO%23CD1DHA.3224@.tk2msftngp13.phx.gbl...

quote:

---

> Hi All
> I get this error when i open a specific table from enterprise manager and
> try to update values in it.
> Data has changed since the results pane was last updated. Do you want to
> save your changes now?
> Click Yes to save your chnages and update the database.
> Click No to discard your changes and refresh the results pane.
> Click Cancel to continue editing.
> If i click Yes which is of course what i wnated to do from the begining i
> get this error:
> Row cannot be located for updating.Some values may have been changed since
> it was last Read.
> So i was wonderring if anybody can give me some help with that.
> Thanks in Advance
> Nader
>

---

sql

Error when trying to save or write to table

Hi,
I am doing a tuning exercise on a SQL 2K5 instance, I am encountering
a problem when Profiler goes to create a table. I have profiled
Profiler and have extracted the following code
CREATE TABLE [dbo].[con1] ([RowNumber] int IDENTITY(0,1) PRIMARY KEY,
[EventClass] int NULL,[Duration] bigint NULL,,
[TextData] ntext NULL,[SPID] int NULL,[BinaryData] image NULL,[CPU]
int NULL,[Reads] bigint NULL,[Writes] bigint NULL,[ApplicationName]
nvarchar(128) NULL,[ClientProcessID] int NULL,[DatabaseID] int NULL,
[DatabaseName] nvarchar(128) NULL,[EventSequence] int NULL,
[EventSubClass] int NULL,[HostName] nvarchar(128) NULL,[IntegerData]
int NULL,[IsSystem] int NULL,[LoginName] nvarchar(128) NULL,[LoginSid]
image NULL,[NTDomainName] nvarchar(128) NULL,[NTUserName]
nvarchar(128) NULL,[RequestID] int NULL,[ServerName] nvarchar(128)
NULL,[SessionLoginName] nvarchar(128) NULL,[StartTime] datetime NULL,
[TransactionID] bigint NULL)
As you can see, after Duration (the 3rd column), there is a double
comma inserted which of course is erroring.
This occurs when defining a table to save to prior to running the
trace, and defining a table after running the trace.
I have tried the standard profiler which appears to work fine.
I also updated to SP2 CTP (this is a test server) because it appeared
to be an issue simular to this http://support.microsoft.com/?
kbid=925335.
A quick response would be appreciated as I am a contractor and I am on
tight timescales.
Cheers
Steve
Steve,
I don't know how to fix the error with profiler but I recommend you not do
that in the fist place. Tracing to a table can be a huge performance hit and
can skew your results. It is recommended that you trace directly to a file
using sp_trace_create and then use fn_trace_gettable to retrieve the data.
You can do something like this to put the trace results in a table:
SELECT * INTO YourTable FROM fn_trace_gettable(xxx)
If you already have a trace in profiler you can save it to a file and do the
same.
Andrew J. Kelly SQL MVP
"Steve L" <steve_lawrenson@.tiscali.co.uk> wrote in message
news:1170411376.241148.280690@.v33g2000cwv.googlegr oups.com...
> Hi,
> I am doing a tuning exercise on a SQL 2K5 instance, I am encountering
> a problem when Profiler goes to create a table. I have profiled
> Profiler and have extracted the following code
> CREATE TABLE [dbo].[con1] ([RowNumber] int IDENTITY(0,1) PRIMARY KEY,
> [EventClass] int NULL,[Duration] bigint NULL,,
> [TextData] ntext NULL,[SPID] int NULL,[BinaryData] image NULL,[CPU]
> int NULL,[Reads] bigint NULL,[Writes] bigint NULL,[ApplicationName]
> nvarchar(128) NULL,[ClientProcessID] int NULL,[DatabaseID] int NULL,
> [DatabaseName] nvarchar(128) NULL,[EventSequence] int NULL,
> [EventSubClass] int NULL,[HostName] nvarchar(128) NULL,[IntegerData]
> int NULL,[IsSystem] int NULL,[LoginName] nvarchar(128) NULL,[LoginSid]
> image NULL,[NTDomainName] nvarchar(128) NULL,[NTUserName]
> nvarchar(128) NULL,[RequestID] int NULL,[ServerName] nvarchar(128)
> NULL,[SessionLoginName] nvarchar(128) NULL,[StartTime] datetime NULL,
> [TransactionID] bigint NULL)
> As you can see, after Duration (the 3rd column), there is a double
> comma inserted which of course is erroring.
> This occurs when defining a table to save to prior to running the
> trace, and defining a table after running the trace.
> I have tried the standard profiler which appears to work fine.
> I also updated to SP2 CTP (this is a test server) because it appeared
> to be an issue simular to this http://support.microsoft.com/?
> kbid=925335.
> A quick response would be appreciated as I am a contractor and I am on
> tight timescales.
> Cheers
> Steve
>
|||On 2 Feb, 14:11, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> Steve,
> I don't know how to fix the error with profiler but I recommend you not do
> that in the fist place. Tracing to a table can be a huge performance hit and
> can skew your results. It is recommended that you trace directly to a file
> using sp_trace_create and then use fn_trace_gettable to retrieve the data.
> You can do something like this to put the trace results in a table:
> SELECT * INTO YourTable FROM fn_trace_gettable(xxx)
> If you already have a trace in profiler you can save it to a file and do the
> same.
> --
> Andrew J. Kelly SQL MVP
> "Steve L" <steve_lawren...@.tiscali.co.uk> wrote in message
> news:1170411376.241148.280690@.v33g2000cwv.googlegr oups.com...
>
>
>
>
>
>
> - Show quoted text -
Hi Andrew
thanks for the response, I will use the method above as a workaround
and thanks for that information. It would be nice to know if this is a
recognised issue by MS and what their recommended workarounds are for
it.
Cheers
Steve L

Error when trying to modify a table in Sql Server 2005 beta 2

Hi there.
All the times when I try to modify a table, I receive a COM error and the
modify windows is not showed. Below you can see the exception message. That
way, I am unable to modify a table using Sql Server 2005 beta 2. Any
suggestions?
************** Exception Text **************
System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT
E_FAIL has been returned from a call to a COM component.
at
Microsoft.VisualStudio.DataTools.UI.Grid.IDTGridSt orage.GetCellDataAsString(Int64 nRowIndex, Int32 nColIndex)
at
Microsoft.VisualStudio.DataTools.UI.Grid.DTGridSto rageWrapper.GetCellDataAsString(Int64 nRowIndex, Int32 nColIndex)
at
Microsoft.SqlServer.Management.UI.Grid.GridTextCol umn.DrawCell(Graphics g,
Brush bkBrush, Brush textBrush, Font textFont, Rectangle rect, IGridStorage
storage, Int64 nRowIndex)
at
Microsoft.SqlServer.Management.UI.Grid.GridControl .DoCellPainting(Graphics g,
SolidBrush bkBrush, SolidBrush textBrush, Font textFont, Rectangle cellRect,
GridColumn gridColumn, Int64 rowNumber, Boolean enabledState)
at
Microsoft.SqlServer.Management.UI.Grid.GridControl .PaintOneCell(Graphics g,
Int32 nCol, Int64 nRow, Int32 nEditedCol, Int64 nEditedRow, Rectangle& rCell,
Rectangle& rCurrentCellRect, Rectangle& rEditingCellRect)
at Microsoft.SqlServer.Management.UI.Grid.GridControl .PaintGrid(Graphics g)
at
Microsoft.SqlServer.Management.UI.Grid.GridControl .OnPaint(PaintEventArgs pe)
at System.Windows.Forms.Control.PaintWithErrorHandlin g(PaintEventArgs e,
Int16 layer, Boolean disposeEventArgs)
at System.Windows.Forms.Control.WmPaint(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at Microsoft.SqlServer.Management.UI.Grid.GridControl .WndProc(Message& m)
at Microsoft.VisualStudio.DataTools.UI.Grid.GridContr ol.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage (Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(M essage& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)
Thanks.
Carlos
Senior Developer
www.byteshift.com
Regarding SQL Server 2005, you should probably post to the beta newsgroups
http://communities.microsoft.com/new...r2005&slcid=us
If you want to access the newsgroups via your newsreader just scroll through
the top right hand frame for connection information
Keith
"Carlao" <Carlao@.discussions.microsoft.com> wrote in message
news:0646E034-A0F4-4692-9B3A-48A790A48FAC@.microsoft.com...
> Hi there.
> All the times when I try to modify a table, I receive a COM error and the
> modify windows is not showed. Below you can see the exception message.
That
> way, I am unable to modify a table using Sql Server 2005 beta 2. Any
> suggestions?
> ************** Exception Text **************
> System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT
> E_FAIL has been returned from a call to a COM component.
> at
>
Microsoft.VisualStudio.DataTools.UI.Grid.IDTGridSt orage.GetCellDataAsString(
Int64 nRowIndex, Int32 nColIndex)
> at
>
Microsoft.VisualStudio.DataTools.UI.Grid.DTGridSto rageWrapper.GetCellDataAsS
tring(Int64 nRowIndex, Int32 nColIndex)
> at
> Microsoft.SqlServer.Management.UI.Grid.GridTextCol umn.DrawCell(Graphics g,
> Brush bkBrush, Brush textBrush, Font textFont, Rectangle rect,
IGridStorage
> storage, Int64 nRowIndex)
> at
> Microsoft.SqlServer.Management.UI.Grid.GridControl .DoCellPainting(Graphics
g,
> SolidBrush bkBrush, SolidBrush textBrush, Font textFont, Rectangle
cellRect,
> GridColumn gridColumn, Int64 rowNumber, Boolean enabledState)
> at
> Microsoft.SqlServer.Management.UI.Grid.GridControl .PaintOneCell(Graphics
g,
> Int32 nCol, Int64 nRow, Int32 nEditedCol, Int64 nEditedRow, Rectangle&
rCell,
> Rectangle& rCurrentCellRect, Rectangle& rEditingCellRect)
> at
Microsoft.SqlServer.Management.UI.Grid.GridControl .PaintGrid(Graphics g)
> at
> Microsoft.SqlServer.Management.UI.Grid.GridControl .OnPaint(PaintEventArgs
pe)
> at System.Windows.Forms.Control.PaintWithErrorHandlin g(PaintEventArgs
e,
> Int16 layer, Boolean disposeEventArgs)
> at System.Windows.Forms.Control.WmPaint(Message& m)
> at System.Windows.Forms.Control.WndProc(Message& m)
> at Microsoft.SqlServer.Management.UI.Grid.GridControl .WndProc(Message&
m)
> at
Microsoft.VisualStudio.DataTools.UI.Grid.GridContr ol.WndProc(Message& m)
> at System.Windows.Forms.ControlNativeWindow.OnMessage (Message& m)
> at System.Windows.Forms.ControlNativeWindow.WndProc(M essage& m)
> at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
> IntPtr wparam, IntPtr lparam)
> Thanks.
> Carlos
> --
> Senior Developer
> www.byteshift.com

Error when trying to filter a table

Hi!!
I have 2 tables in a report that runs against the same dataset. And
when I try to filter on a field called T0650_CONDITION_TYPE_ID, I get
an error.
"The processing of filter expression for the table 'table2' cannot be
performed. The comparison failed. Please check the data type retured
by filter expression."
The filter is =Fields!T0650_CONDITION_TYPE_ID.Value = 2
I've searched the internet and found different suggestions to
solutions but none of them works.
I've tried to =2 in the value-field, I have tried to convert, but I
doesn't get it to work.
And I need this filter to just show T0650_CONDITION_TYPE_ID.Value = 1
in one table and T0650_CONDITION_TYPE_ID.Value = 2 in the other.
I have tried to hide, but then I get empty spaces, and it doesn't look
good.
T0650_CONDITION_TYPE_ID is a tinyint.
I'm grateful for any help
BR LottaCould you try
=(CInt(Fields!T0650_CONDITION_TYPE_ID.Value) = 2) ?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lotta" <lotta.svensson@.europeiska.com> wrote in message
news:7ec6061a.0503020012.eb2f8f0@.posting.google.com...
> Hi!!
> I have 2 tables in a report that runs against the same dataset. And
> when I try to filter on a field called T0650_CONDITION_TYPE_ID, I get
> an error.
> "The processing of filter expression for the table 'table2' cannot be
> performed. The comparison failed. Please check the data type retured
> by filter expression."
> The filter is =Fields!T0650_CONDITION_TYPE_ID.Value = 2
> I've searched the internet and found different suggestions to
> solutions but none of them works.
> I've tried to =2 in the value-field, I have tried to convert, but I
> doesn't get it to work.
> And I need this filter to just show T0650_CONDITION_TYPE_ID.Value = 1
> in one table and T0650_CONDITION_TYPE_ID.Value = 2 in the other.
> I have tried to hide, but then I get empty spaces, and it doesn't look
> good.
> T0650_CONDITION_TYPE_ID is a tinyint.
> I'm grateful for any help
> BR Lotta

error when trying to delete record

Windows 2000 Server sp4, SQL Server 2000 sp3
I get the error below when attempting to delete a record from a table called
Monitor directly from Enterprise Manager GUI.
error:
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Obje
ct Name]
'tsvw_KJDKFAJDFKJAKLFDJKLAJDFKJASKFDJKAJ
DF'
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Obje
ct
Name]UserA.Monitor
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Obje
ct Name]
'ctsv_DKFJDKJFKDJFKDKFJDKJFIEJIEJIEJFIEJ
FKJDKJ'
The KDJFKDJFKJDF above is obviously not exact. The actual error contains a
long string of random characters though.. If i'm not mistaken these are
system generated names for views related to replication.
1) could this be replication related?
2) Important note: UserA is NOT the current owner of the Monitor table!
UserA used to own the table but due to replication issues the owner was
changed to dbo a long time ago. ' Can I safely run the sp_changeobjectowner
procedure specifying dbo again or would I need to drop the merge replication
in order to do that?
3) I verified that the tsvw_ect... and the ctsv_ect... do not exist in the
database?
any suggestions about how to get to the bottom of this would be GREATLY
appreciated. Thanks.It seems you have some "invalid" merge triggers (probably from a previous
publication). Did you republish after changing your table owner name? If
you do have invalid triggers, you should consider dropping and recreating
your publication.
A. Drop the subscription
B. Drop the publication.
C. Delete the triggers that reference the non-existant views
D. Recreate the publication and subscription.
E. Run the snapshot.
reference:
How to manually remove a replication in SQL Server 2000 - ID: 324401
http://support.microsoft.com/?id=324401
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||Thank you! I did not completely drop and recreate the merge replication
setup but what you said is exactly what happened. I had an issue removing
repl before due to the owner of the tables not being dbo. I had to manually
clean out repl.. change table owners to dbo.. and recreate the replication.
It looks like one of the triggers from the older repl setup was still there.
I found the old trigger and deleted it. It was obvious it was old because it
referenced the old user name. There were the 3 correct triggers on the table
as well. One each for insert, update, and delete. So what was happening was
this: there was the old delete trigger on this table and it was listed first
so it executed and failed. The correct trigger never fired then. Now that
old trigger is gone and all works fine.
thanks again.
"Fany Vargas [MSFT]" <fanyv@.online.microsoft.com> wrote in message
news:Yj9qhVuzEHA.3388@.cpmsftngxa10.phx.gbl...
> It seems you have some "invalid" merge triggers (probably from a previous
> publication). Did you republish after changing your table owner name? If
> you do have invalid triggers, you should consider dropping and recreating
> your publication.
> A. Drop the subscription
> B. Drop the publication.
> C. Delete the triggers that reference the non-existant views
> D. Recreate the publication and subscription.
> E. Run the snapshot.
>
> reference:
> How to manually remove a replication in SQL Server 2000 - ID: 324401
> http://support.microsoft.com/?id=324401
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>

error when trying to delete record

Windows 2000 Server sp4, SQL Server 2000 sp3
I get the error below when attempting to delete a record from a table called
Monitor directly from Enterprise Manager GUI.
error:
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object Name]
'tsvw_KJDKFAJDFKJAKLFDJKLAJDFKJASKFDJKAJDF'
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object
Name]UserA.Monitor
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object Name]
'ctsv_DKFJDKJFKDJFKDKFJDKJFIEJIEJIEJFIEJFKJDKJ'
The KDJFKDJFKJDF above is obviously not exact. The actual error contains a
long string of random characters though.. If i'm not mistaken these are
system generated names for views related to replication.
1) could this be replication related?
2) Important note: UserA is NOT the current owner of the Monitor table!
UserA used to own the table but due to replication issues the owner was
changed to dbo a long time ago. ? Can I safely run the sp_changeobjectowner
procedure specifying dbo again or would I need to drop the merge replication
in order to do that?
3) I verified that the tsvw_ect... and the ctsv_ect... do not exist in the
database?
any suggestions about how to get to the bottom of this would be GREATLY
appreciated. Thanks.
It seems you have some "invalid" merge triggers (probably from a previous
publication). Did you republish after changing your table owner name? If
you do have invalid triggers, you should consider dropping and recreating
your publication.
A. Drop the subscription
B. Drop the publication.
C. Delete the triggers that reference the non-existant views
D. Recreate the publication and subscription.
E. Run the snapshot.
reference:
How to manually remove a replication in SQL Server 2000 - ID: 324401
http://support.microsoft.com/?id=324401
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Thank you! I did not completely drop and recreate the merge replication
setup but what you said is exactly what happened. I had an issue removing
repl before due to the owner of the tables not being dbo. I had to manually
clean out repl.. change table owners to dbo.. and recreate the replication.
It looks like one of the triggers from the older repl setup was still there.
I found the old trigger and deleted it. It was obvious it was old because it
referenced the old user name. There were the 3 correct triggers on the table
as well. One each for insert, update, and delete. So what was happening was
this: there was the old delete trigger on this table and it was listed first
so it executed and failed. The correct trigger never fired then. Now that
old trigger is gone and all works fine.
thanks again.
"Fany Vargas [MSFT]" <fanyv@.online.microsoft.com> wrote in message
news:Yj9qhVuzEHA.3388@.cpmsftngxa10.phx.gbl...
> It seems you have some "invalid" merge triggers (probably from a previous
> publication). Did you republish after changing your table owner name? If
> you do have invalid triggers, you should consider dropping and recreating
> your publication.
> A. Drop the subscription
> B. Drop the publication.
> C. Delete the triggers that reference the non-existant views
> D. Recreate the publication and subscription.
> E. Run the snapshot.
>
> reference:
> How to manually remove a replication in SQL Server 2000 - ID: 324401
> http://support.microsoft.com/?id=324401
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>

error when trying to delete record

Windows 2000 Server sp4, SQL Server 2000 sp3
I get the error below when attempting to delete a record from a table called
Monitor directly from Enterprise Manager GUI.
error:
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object Name]
'tsvw_KJDKFAJDFKJAKLFDJKLAJDFKJASKFDJKAJDF'
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object
Name]UserA.Monitor
[Microsoft][ODBC SQL Server Driver][SQL Server][Invalid Object Name]
'ctsv_DKFJDKJFKDJFKDKFJDKJFIEJIEJIEJFIEJFKJDKJ'
The KDJFKDJFKJDF above is obviously not exact. The actual error contains a
long string of random characters though.. If i'm not mistaken these are
system generated names for views related to replication.
1) could this be replication related?
2) Important note: UserA is NOT the current owner of the Monitor table!
UserA used to own the table but due to replication issues the owner was
changed to dbo a long time ago. ' Can I safely run the sp_changeobjectowner
procedure specifying dbo again or would I need to drop the merge replication
in order to do that?
3) I verified that the tsvw_ect... and the ctsv_ect... do not exist in the
database?
any suggestions about how to get to the bottom of this would be GREATLY
appreciated. Thanks.It seems you have some "invalid" merge triggers (probably from a previous
publication). Did you republish after changing your table owner name? If
you do have invalid triggers, you should consider dropping and recreating
your publication.
A. Drop the subscription
B. Drop the publication.
C. Delete the triggers that reference the non-existant views
D. Recreate the publication and subscription.
E. Run the snapshot.
reference:
How to manually remove a replication in SQL Server 2000 - ID: 324401
http://support.microsoft.com/?id=324401
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||Thank you! I did not completely drop and recreate the merge replication
setup but what you said is exactly what happened. I had an issue removing
repl before due to the owner of the tables not being dbo. I had to manually
clean out repl.. change table owners to dbo.. and recreate the replication.
It looks like one of the triggers from the older repl setup was still there.
I found the old trigger and deleted it. It was obvious it was old because it
referenced the old user name. There were the 3 correct triggers on the table
as well. One each for insert, update, and delete. So what was happening was
this: there was the old delete trigger on this table and it was listed first
so it executed and failed. The correct trigger never fired then. Now that
old trigger is gone and all works fine.
thanks again.
"Fany Vargas [MSFT]" <fanyv@.online.microsoft.com> wrote in message
news:Yj9qhVuzEHA.3388@.cpmsftngxa10.phx.gbl...
> It seems you have some "invalid" merge triggers (probably from a previous
> publication). Did you republish after changing your table owner name? If
> you do have invalid triggers, you should consider dropping and recreating
> your publication.
> A. Drop the subscription
> B. Drop the publication.
> C. Delete the triggers that reference the non-existant views
> D. Recreate the publication and subscription.
> E. Run the snapshot.
>
> reference:
> How to manually remove a replication in SQL Server 2000 - ID: 324401
> http://support.microsoft.com/?id=324401
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>

Error when starting Incremental Population

Hi all
I have a very large FT catalog on a table.
I have tried to initiate an incremental population
Exec sp_fulltext_catalog 'FT_PATCLAIMS','start_incremental'
and I got the following error :
"Execution of a full-text operation failed. The Gatherer
is shutting down."
I have found no reference to this error on the microsof
site. Has anyone seen this problem before. I would hate to
have to recreate this catalog. It can take a number of
weeks !!!
Thanks
Michael.
Are there any error messages logged in the event log from MSSearch or mssci?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||We had the same issue, it was solved by restarting the 'Microsoft Search'
service.
Cheers
Paul Bell,
DBA UnisysWest
Perth, Western Australia.
"Hilary Cotter" wrote:

> Are there any error messages logged in the event log from MSSearch or mssci?
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||Paul,
What issue was that? What error did you get that was resolved by just
restarting the 'Microsoft Search' service?
Thanks,
John
"Paul Bell" <Paul Bell@.discussions.microsoft.com> wrote in message
news:BC409C32-C77E-4CBC-9BEF-CEC58E6A79BB@.microsoft.com...[vbcol=seagreen]
> We had the same issue, it was solved by restarting the 'Microsoft Search'
> service.
> Cheers
> Paul Bell,
> DBA UnisysWest
> Perth, Western Australia.
> "Hilary Cotter" wrote:
mssci?[vbcol=seagreen]

Error When setting Map Document Label in a group of a table

Hi,

I try to activate the map document control on my report. My Report is composed of a table in which I group by 2 criterias. When I set the document map label of the first entry of the group list then I get an error on the preview :

"An internal error occurred on the report server. See the error log for more details. "

Can someone tell where can I find the error log file ?

thanks in Advance.

Depending on what drive you set up sql server on and what instance you are referring to (if there is more than one), the basic path to the log files is:

C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\LogFiles

You can also view the logs on the reporting services server in the Event Viewer.

|||thanks Simone

Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when rebuild table index

I ran maintenance plan trying to rebuild index for the database tables, and I
got following error.
[22] Database CmpData: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'cliStores'
Rebuilding indexes for table 'cmpItems'
Rebuilding indexes for table 'cmpStores'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
I run following set command.
SET ANSI_DEFAULTS ON
But error remains. Please help. Thanks,
Charts
Sounds like you have a computed column in the table. The maintenance wizard
can not properly handle those. If you have SP4 then try this:
http://support.microsoft.com/?kbid=902388
Otherwise the solution is to create your own scheduled job that issues a
DBCC DBREINDEX for that table (or all of them) and set the proper SET
settings there.
Andrew J. Kelly SQL MVP
"Charts" <Acharts@.newsgroup.nospam> wrote in message
news:6752D209-A390-44C7-95EC-28B2B6BBF135@.microsoft.com...
>I ran maintenance plan trying to rebuild index for the database tables, and
>I
> got following error.
> [22] Database CmpData: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'cliStores'
> Rebuilding indexes for table 'cmpItems'
> Rebuilding indexes for table 'cmpStores'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
> have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> I run following set command.
> SET ANSI_DEFAULTS ON
> But error remains. Please help. Thanks,
> Charts
>
|||Hi Andrew,
You are right, and The table has a computed column. Since my SQL Server is
SP4. I followed the instruction in the article and added -CkDB
-SupportComputedColumn' in the job command. The job command is as follows.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
Server\myservername\LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 -CkDB
-SupportComputedColumn'
Now I got another error, and says.
Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC
SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][Shared
Memory]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
The original command is as follows.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
Server\ myservername \LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '
By the way, is there way to directly add SupportComputedColumn' parameter in
the property of maintenance plan wizard instead of command line?
Thanks for your help.
Charts
"Andrew J. Kelly" wrote:

> Sounds like you have a computed column in the table. The maintenance wizard
> can not properly handle those. If you have SP4 then try this:
> http://support.microsoft.com/?kbid=902388
> Otherwise the solution is to create your own scheduled job that issues a
> DBCC DBREINDEX for that table (or all of them) and set the proper SET
> settings there.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Charts" <Acharts@.newsgroup.nospam> wrote in message
> news:6752D209-A390-44C7-95EC-28B2B6BBF135@.microsoft.com...
>
>
|||I really don't know why you are getting the new error. There is no way to
embed it that I know of. Personally I never recommend the MP wizard. My
recommendation is to simply create your own job that does the reindexing the
way you need it.
Andrew J. Kelly SQL MVP
"Charts" <Acharts@.newsgroup.nospam> wrote in message
news:3E936E12-AA09-4880-BEB1-8F0CA5BE613B@.microsoft.com...[vbcol=seagreen]
> Hi Andrew,
> You are right, and The table has a computed column. Since my SQL Server is
> SP4. I followed the instruction in the article and added -CkDB
> -SupportComputedColumn' in the job command. The job command is as
> follows.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
> Server\myservername\LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 -CkDB
> -SupportComputedColumn'
> Now I got another error, and says.
> Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC
> SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][Shared
> Memory]SQL Server does not exist or access denied.
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Connect()).
> The original command is as follows.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
> Server\ myservername \LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '
> By the way, is there way to directly add SupportComputedColumn' parameter
> in
> the property of maintenance plan wizard instead of command line?
> Thanks for your help.
> Charts
>
> "Andrew J. Kelly" wrote:
|||Charts,
I have the EXACT same problem.. computed column and use of
-SupportComputedColumn is causing ODBC connection issues. Have you
found any resolution? I have been unable to find any help anywhere
including BOL, MSKB etc...
Thanks in advance.
MJM
Charts wrote:[vbcol=seagreen]
> Hi Andrew,
> You are right, and The table has a computed column. Since my SQL Server is
> SP4. I followed the instruction in the article and added -CkDB
> -SupportComputedColumn' in the job command. The job command is as follows.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
> Server\myservername\LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 -CkDB
> -SupportComputedColumn'
> Now I got another error, and says.
> Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC
> SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][Shared
> Memory]SQL Server does not exist or access denied.
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
> The original command is as follows.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
> Server\ myservername \LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '
> By the way, is there way to directly add SupportComputedColumn' parameter in
> the property of maintenance plan wizard instead of command line?
> Thanks for your help.
> Charts
>
> "Andrew J. Kelly" wrote:
|||I have found that the SP4 option -SupportComputedColumn only works on a
default instance not a named instance. I'm contacting MS to see if
that can be supported.
Andrew J. Kelly wrote:[vbcol=seagreen]
> I really don't know why you are getting the new error. There is no way to
> embed it that I know of. Personally I never recommend the MP wizard. My
> recommendation is to simply create your own job that does the reindexing the
> way you need it.
> --
> Andrew J. Kelly SQL MVP
>
> "Charts" <Acharts@.newsgroup.nospam> wrote in message
> news:3E936E12-AA09-4880-BEB1-8F0CA5BE613B@.microsoft.com...

error when loading datas to table

sir,
i am facing an error when loading datas into tables.Can you please give us a description of the actual error.
Peter
"Facts are stupid things."
Ronald Reagan
>--Original Message--
>sir,
> i am facing an error when loading datas into tables.
>.
>

Error when I try to retrieve a HTML String from an table.

Error when I try to retrieve a HTML String from an table.I've got exactly the same problem - and can trigger it.
My stored procedure returns several rows of a single text field. This
works well by and large, until the total combined output length exceeds
1024 characters, then I get exactly this error
I'm completly stumped at the moment.
just in case it helps, using
http://dev/xml?sql=EXEC XML_Page 22420,1
I get..(this works - it's exaclty 1024 chars of valid xml - if I was to
increase a text item returned by stored procedure by just 1 more
character, its still valid - but I get the error)
..
[I cannot not - I still crash these forums - it's just 1024 characters
of valid xml]
..
Any help, links, possible explanations, hints - all very much
appreciated :)
davemcrae
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message337340.html|||(please bear with me as I post this editing and adding 1 line at a time
- and eNSERT pls read as INSERT - this causes forums to crash for me -
also {} are angle brackets)
I hate it when my boss walks in and suggests modifying a value then it
works - still doesnt to my satifaction though.
we have
CREATE PROCEDURE XML_Page
..
CREATE TABLE #XMLOut(XMLData TEXT)
.
eNSERT eNTO #XMLOutVALUES('{page}')
eNSERT eNTO #XMLOut xELECT aTextFieldWithValidXML FROM pageSections
WHERE ..
eNSERT eNTO #XMLOut VALUES('{page}')
.
xELECT XMLData FROM #XMLOut
fROP TABLE #XMLOut
this will fail if the total length exceeds 1024 characters
now change the CREATE TABLE #XMLOut(XMLData TEXT) to read CREATE TABLE
#XMLOut(XMLData VARCHAR(8000))
yes, you can see the problem with my pagesection data :)
but I do NOT get the error, as long as the pageSections are now under
8k, my total length can be unlimted
with a text field, I have no restrictions, but system dies when exceeds
1024 character length
not using a temporary table is worse
if, rather than INSERTS, I just SELECT for output, I get the 1024
character limitation
so for Karthikeyan K you may use a temporary table in your stored
procedure with a field that is Varchar rather than text and as long as
you can keep it under 8k (4k for nvarchar) this limitation wont bite.
Does anyone know of another way? - I really want to keep my sections as
text fields without an 8k limit :)
Whew - that was the most painful post ever - alter DROP to fROP try -
yes it works - paste in next line, fails, alter a character - passes,
next line, so on
davemcrae
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message337340.html|||What is the error message? What exactly does fail? The stored proc or IE?
Best regards
Michael
"davemcrae" <davemcrae.1nd3m3@.mail.mcse.ms> wrote in message
news:davemcrae.1nd3m3@.mail.mcse.ms...
> (please bear with me as I post this editing and adding 1 line at a time
> - and eNSERT pls read as INSERT - this causes forums to crash for me -
> also {} are angle brackets)
> I hate it when my boss walks in and suggests modifying a value then it
> works - still doesnt to my satifaction though.
> we have
> CREATE PROCEDURE XML_Page
> ..
> CREATE TABLE #XMLOut(XMLData TEXT)
> .
> eNSERT eNTO #XMLOutVALUES('{page}')
> eNSERT eNTO #XMLOut xELECT aTextFieldWithValidXML FROM pageSections
> WHERE ..
> eNSERT eNTO #XMLOut VALUES('{page}')
> .
> xELECT XMLData FROM #XMLOut
> fROP TABLE #XMLOut
>
> this will fail if the total length exceeds 1024 characters
> now change the CREATE TABLE #XMLOut(XMLData TEXT) to read CREATE TABLE
> #XMLOut(XMLData VARCHAR(8000))
> yes, you can see the problem with my pagesection data :)
> but I do NOT get the error, as long as the pageSections are now under
> 8k, my total length can be unlimted
> with a text field, I have no restrictions, but system dies when exceeds
> 1024 character length
> not using a temporary table is worse
> if, rather than INSERTS, I just SELECT for output, I get the 1024
> character limitation
> so for Karthikeyan K you may use a temporary table in your stored
> procedure with a field that is Varchar rather than text and as long as
> you can keep it under 8k (4k for nvarchar) this limitation wont bite.
> Does anyone know of another way? - I really want to keep my sections as
> text fields without an 8k limit :)
>
> Whew - that was the most painful post ever - alter DROP to fROP try -
> yes it works - paste in next line, fails, alter a character - passes,
> next line, so on
>
> --
> davemcrae
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message337340.html
>

Error when doing fuzzy lookup

I am trying to run a SSIS package that contains a fuzzy lookup. I am using a flat file with about 7 million records as the input. The reference table has about 2000 records. The package fails after about 40,000 records with the following information:

Warning: 0x8007000E at Data Flow Task, Fuzzy Lookup [228]: Not enough storage is available to complete this operation.
Warning: 0x800470E9 at Data Flow Task, DTS.Pipeline: A call to the ProcessInput method for input 229 on component "Fuzzy Lookup" (228) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Fuzzy Lookup" (228) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x8007000E.
Error: 0xC02020C4 at Data Flow Task, Flat File Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

-

I have tried many things - changing the BufferTempStoragePath path to a drive that has plenty space, changed the MaxInsertCommitSize to 5,000...

What else can I do?

Thanks!

Did you get this on a RTM build?

If yes, is it possible you apply SP1 to see whether that helps?

Thanks

wenyang

|||

Yes, it is on a RTM build.

The server does not have SP1 on it yet. Is there something else that I could try before that is installed?

Thanks!

|||

I know there was a memory issue got fixed in SP1 for FuzzyLookup, in which case the error looks very similar to yours, so I wanted you to try SP1.

For temp work arounds, you may also try one of the following two on your RTM

1) Increase your page file to >10GB

2) Mitigate the problem by splitting the input into smaller batches (e.g. 100k at a time) and tearing down the package in between each batch.

HTH

wenyang

|||

I have installed SP1, and am getting the same errors.

Any other suggestions other than the temp work arounds you mentioned? As I mentioned earlier, the process is stopping after only 40,000 records or so. This seems like a very small number and I'd hate to have to split the input up into smaller pieces than that.

Thanks!

|||

Does increase page file size help? If not, then we probably need to repro in house to investigate further.

Is it possible you log a bug with repro? (bug report place is http://lab.msdn.microsoft.com/productfeedback/default.aspx) We'll get back to you asap.

Thanks

Wenyang

|||

Sorry - I was told that SP1 had been installed before I wrote my previous message. It actually hadn't been installed at that point. Once that was successfully installed on Integration Services, the fuzzy lookup issues seem to have been resolved.

Thanks!

|||

Hi Wenyang,

I have installed SQL Server 2005 Service Pack 2 and unfortunately this error does not seem to have gone away.

Increasing my pagefile does not seemed to have helped the situation either ?

It seems to occur whenever I choose to perform a match on more than 10 columns within a Fuzzy Lookup ?

Can you please advise what I should do next/when this will be fixed ?

Many Thanks,

Gary

Error when doing fuzzy lookup

I am trying to run a SSIS package that contains a fuzzy lookup. I am using a flat file with about 7 million records as the input. The reference table has about 2000 records. The package fails after about 40,000 records with the following information:

Warning: 0x8007000E at Data Flow Task, Fuzzy Lookup [228]: Not enough storage is available to complete this operation.
Warning: 0x800470E9 at Data Flow Task, DTS.Pipeline: A call to the ProcessInput method for input 229 on component "Fuzzy Lookup" (228) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Fuzzy Lookup" (228) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x8007000E.
Error: 0xC02020C4 at Data Flow Task, Flat File Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

-

I have tried many things - changing the BufferTempStoragePath path to a drive that has plenty space, changed the MaxInsertCommitSize to 5,000...

What else can I do?

Thanks!

Did you get this on a RTM build?

If yes, is it possible you apply SP1 to see whether that helps?

Thanks

wenyang

|||

Yes, it is on a RTM build.

The server does not have SP1 on it yet. Is there something else that I could try before that is installed?

Thanks!

|||

I know there was a memory issue got fixed in SP1 for FuzzyLookup, in which case the error looks very similar to yours, so I wanted you to try SP1.

For temp work arounds, you may also try one of the following two on your RTM

1) Increase your page file to >10GB

2) Mitigate the problem by splitting the input into smaller batches (e.g. 100k at a time) and tearing down the package in between each batch.

HTH

wenyang

|||

I have installed SP1, and am getting the same errors.

Any other suggestions other than the temp work arounds you mentioned? As I mentioned earlier, the process is stopping after only 40,000 records or so. This seems like a very small number and I'd hate to have to split the input up into smaller pieces than that.

Thanks!

|||

Does increase page file size help? If not, then we probably need to repro in house to investigate further.

Is it possible you log a bug with repro? (bug report place is http://lab.msdn.microsoft.com/productfeedback/default.aspx) We'll get back to you asap.

Thanks

Wenyang

|||

Sorry - I was told that SP1 had been installed before I wrote my previous message. It actually hadn't been installed at that point. Once that was successfully installed on Integration Services, the fuzzy lookup issues seem to have been resolved.

Thanks!

|||

Hi Wenyang,

I have installed SQL Server 2005 Service Pack 2 and unfortunately this error does not seem to have gone away.

Increasing my pagefile does not seemed to have helped the situation either ?

It seems to occur whenever I choose to perform a match on more than 10 columns within a Fuzzy Lookup ?

Can you please advise what I should do next/when this will be fixed ?

Many Thanks,

Gary

Error when creating a table based function

I'm trying to create a table based function using SMO. The problem is that I get an error saying that I need to set the DataType property on the function before it can be created. However, the documentation clearly says that the DataType property is null for Table based functions. I can't even set this value to null either!

Here's a code snippet that shows what I'm trying to do.

if (returnType.ToLower() != "table")

{

Type clrDataType = Type.GetType(returnType);

returnSqlDataType = GetSqlDataType(clrDataType);

function.FunctionType = Smo.UserDefinedFunctionType.Scalar;

function.DataType = returnSqlDataType;

}

else

{

function.FunctionType = Smo.UserDefinedFunctionType.Table;

function.TableVariableName = xmlNode.Attributes["tableVariableName"].Value;

XmlNode returnTableDescription = GetChildOfParent(xmlNode, "ReturnTableDescription");

XmlNode columnsNode = GetChildOfParent(returnTableDescription, "Columns");

foreach (XmlNode columnNode in columnsNode.ChildNodes)

{

function.Columns.Add(CreateUdfColumn(function, columnNode));

}

}

The xmlNode that is being referenced is an XmlNode that is describing how to create the table. returnType is a variable that is set from one of the xmlNode's attributes.

When I call on function.Create() then I get the error that I posted above. Any help anyone can give is appreciated. One thing I also tried doing was to connect to an already existing Table based function and look at its SMO properties. Sure enough, its DataType property was null.

MSFT: Is this a bug?

Never mind on this post.....it was my mistake. The DataType property not being set wasn't on the function itself, but rather on the columns collection.

Problem solved.

Error when attempting to delete rows

I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.

Occassionally, I will end up with couple rows (out of many) that won't delete. I get the following error when I attempt to delete the "affected" rows:

A problem occurred attempting to delete row 1.
Error Source: Microosft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the
unique or they alter multiple rows (2 rows).

I'm also not able to modify any of the data of the affected rows. There don't seem to be anything really special or unique about these affected rows (compared to the ones I'm able to delete). I've tried rebooting the machine and that did resolve the issue.

This is the second time I've encountered this issue. By any chance, is this a known bug with the beta (it's SQL Server 2005 June CTP)? Or is it some else I'm not aware of?

I've also tried deleting the rows using SQL Server Management, same results.

RichardCan you run SQL Profiler to check the different events executing while you delete these records? There must be something happening in the background that is causing all these.|||

Did you ever get the problem resolved? I am having the same problem.

Thanks, Bob

|||

Does the table have a primary key or unique constraint?

|||I just saw this. I'm sure it was resolved since the post was awhile ago, but that error message can occur if there is a constraint which the deletion violates. A common one might be that a field is set to "NOT NULL".

Error when attempting to delete rows

I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.

Occassionally, I will end up with couple rows (out of many) that won't delete. I get the following error when I attempt to delete the "affected" rows:

A problem occurred attempting to delete row 1.
Error Source: Microosft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the
unique or they alter multiple rows (2 rows).

I'm also not able to modify any of the data of the affected rows. There don't seem to be anything really special or unique about these affected rows (compared to the ones I'm able to delete). I've tried rebooting the machine and that did resolve the issue.

This is the second time I've encountered this issue. By any chance, is this a known bug with the beta (it's SQL Server 2005 June CTP)? Or is it some else I'm not aware of?

I've also tried deleting the rows using SQL Server Management, same results.

Richard
Can you run SQL Profiler to check the different events executing while you delete these records? There must be something happening in the background that is causing all these.|||

Did you ever get the problem resolved? I am having the same problem.

Thanks, Bob

|||

Does the table have a primary key or unique constraint?

|||I just saw this. I'm sure it was resolved since the post was awhile ago, but that error message can occur if there is a constraint which the deletion violates. A common one might be that a field is set to "NOT NULL".

Error when attempting to delete rows

I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.

Occassionally, I will end up with couple rows (out of many) that won't delete. I get the following error when I attempt to delete the "affected" rows:

A problem occurred attempting to delete row 1.
Error Source: Microosft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the
unique or they alter multiple rows (2 rows).

I'm also not able to modify any of the data of the affected rows. There don't seem to be anything really special or unique about these affected rows (compared to the ones I'm able to delete). I've tried rebooting the machine and that did resolve the issue.

This is the second time I've encountered this issue. By any chance, is this a known bug with the beta (it's SQL Server 2005 June CTP)? Or is it some else I'm not aware of?

I've also tried deleting the rows using SQL Server Management, same results.

Richard
Can you run SQL Profiler to check the different events executing while you delete these records? There must be something happening in the background that is causing all these.|||

Did you ever get the problem resolved? I am having the same problem.

Thanks, Bob

|||

Does the table have a primary key or unique constraint?

|||I just saw this. I'm sure it was resolved since the post was awhile ago, but that error message can occur if there is a constraint which the deletion violates. A common one might be that a field is set to "NOT NULL".