We've got a problem with our replication. If I try to run the snapshot agent I get the message "Query for data failed".
The detail view of this message shows the following:
Message: Query for data failed
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException()
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.DoWork(WorkItem workItem)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.WorkerThread.AgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Message: Conversion failed when converting from a character string to uniqueidentifier.
Stack: (Source: MSSQLServer, Error number: 8169)
I get also the error "Conversion failed when converting from a character string to uniqueidentifier" in [MSmerge_del_12A4D4A8A66847B3A6C270AA39E04B53] Line 76, if I try to delete a record of this table over the management studio. Some records work an some shows this message.
The table on that the problem depends has about 13.000 rows with 4 uniqueidentifier columns, 1 nvarchar column and 1 image column, where currently the image column is always null.
We use SQL Server 2005 Juni CTP Standard Edition.
How can I solve this problem
Thanks in advane
Markus
Snapshot Agent - Error: Query for data failed
FistFullOfSteel
I am having a similar problem in a view with the error :
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value 'Somevalue' to data type int.
Here is the design: There is a table called TABLE1 that has a char(10) field from a legacy db that is the primary key with int values and character values in the field. I have a view created in another db called VIEW1 that references TABLE1 and in the VIEW1 the PK of TABLE1 is CONVERTED to an int and the where clause makes sure only int's are returned in VIEW1.
VIEW1:
SELECT CONVERT(int, field1) as Field1, other fields...
FROM TABLE1
WHERE isnumeric(field1) = 1
VIEW1 works if I do a select *, but if I try to query VIEW1 with a where clause using Field1, I get the Conversion failed error.
SELECT Field1
FROM VIEW1
WHERE Field1 = 1
This seems like VIEW1 is not filtering out the records in its WHERE clause, and failing on the CONVERT in the select clause. I was under the assumption a view is used to filter out the data you don't want from table(s).
bitsbird
Your particular inquiry is admittedly outside the scope of replication and so you may have better luck posting this to the TSQL\Programming forums. But from what you described, it appears to me that it might be possible that certain values in field1 are either decimal value or numeric values that lie outside the range of int although that theory doesn't quite fit with your observation that "select * from VIEW1" worked fine for you. If you can isolate which subset of the rows in TABLE1 that is causing you problems by putting in more restrictive conditions (e.g. ranges of field1) in the where clause of the query against VIEW1 or the where clause in VIEW1 (make a copy), you are more than likely be able to figure out what the real problem is. Better yet, if you can post a complete but isolated scenario here, we can all gain more experience with how conversion errors can arise in real world scenarios. And if you come to the conclusion that the conversion error should not be triggered in the first place, we will really appreciate if you can open a bug for this at the MSDN Product feedback center.
-Raymond
Trip Volpe
The error message you recevied indicates that the view (what we call the synchronization view or syncrhonization object) that the snapshot agent uses to bulk copy data from the problematic table is performing invalid data conversion on some of the data elements in your table. To confirm that this is indeed the case, you can see if you get the same conversion error if you select directly from the synchronization object. You can find out the synchronization object name for your problematic table article from the sync_object_name and sync_object_owner columns in the result set returned by sp_helpmergearticle when it is executed in the publisher database, and it should be of the form MSmerge_<publisher database>_<table name>_VIEW. Once you have confirmed that the synchronization object is indeed performing invalid data conversion on some of your data elements, you need to consider the following possibilities of why this is so:
1) The filter that you have defined for the problematic table article is performing the invalid data conversion. For example, your filter may be performing a comparison of the form <character column> = <uniqueidentifier column> where SQL Server will perform an implicit conversion of <character column> to uniqueidentifier before performing the comparison. If <character column> has values of the form 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' (where X is a hexadecimal digit) for most of your rows except a few, conversion will only fail for a few specific rows, and this should subsequently explain why you will only hit the data conversion error when deleting specific rows in the problematic table. (Note: the merge replication delete trigger uses the same synchronization object as the snapshot agent for determining the filtering criterion.)
2) Your problematic table has a computed column based on invalid data conversion similar to the one I described in 1).
3) There is a bug in our syncrhonization object generation code where an invalid comparison of the form <character column> = <uniqueidentifier column> is generated in the synchronization object definition.
Since I don't know all the specifics about your scenario, I can only offer the general strategy for isolating which of the three possibilities above is the root cause of your problem. First, you would want to get the definition of the synchronization object by calling sp_helptext. Having the synchronization object definition (which should just be a select statement) allows you to pinpoint which columns are involved in the data conversion error through experimentation with selective removal of various elements in the view definition. For example, you can try removing part of a where clause of the view definition and see if you hit the same data conversion error when you execute the resulting select statement. Next, you would want to pinpoint which rows in your table are clausing the data conversion error using some variant of the binary search method.
If you have determined that either 1) or 2) is the root cause of your problem, you have to either a) change the "malformed" data in your table so the conversion from <string column> to uniqueidentifier is valid for all existing rows, or b) redefine your filter to use a user-defined conversion function that can "convert" all existing value in <string column> to uniqueidentifier. Either approach has drawbacks: a) doesn't help if new "malformed" <string column> value is introduced in the future, and b) can have dramatic performance implication on merge replication. My recommendation would be to do a) and put in additional measures to prevent "malformed" data from being introduced in the future.
If you are convinced that this is a bug in our product after further investigation, we would really appreciate if you can log a bug at betaplace with the isolated steps for reproducing the problem.
-Raymond