Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is syncobj in SQL Server

When I run this script to search particular text in sys.columns and I get a lot of "dbo.syncobj_0x3934443438443332" like rows.

SELECT c.name, s.name + '.' + o.name
FROM sys.columns c
INNER JOIN sys.objects  o ON c.object_id=o.object_id
INNER JOIN sys.schemas  s ON o.schema_id=s.schema_id
WHERE c.name LIKE '%text%'

If I get it right, they are replication objects. Is it so? Can i just throw them away from my query just like o.name NOT LIKE '%syncobj%' or there's another way?

Thank you.

like image 513
hgulyan Avatar asked May 26 '10 04:05

hgulyan


2 Answers

I've found a solution. Doesn't know, if it's the best one or not.

SELECT c.name, s.name + '.' + o.name
FROM sys.columns c
   INNER JOIN sys.objects  o ON c.object_id=o.object_id
   INNER JOIN sys.schemas  s ON o.schema_id=s.schema_id
WHERE c.name LIKE '%text%' AND o.type = 'U'

The result is fine now. As I said syncobj's are replication objects and they don't have a meaning for us. They're used for replication purposes only.

http://www.developmentnow.com/g/114_2007_12_0_0_443938/syncobj-views.htm

EDIT:

Forgot to add, syncobj's are stored in DB as Views, so if you need list of views, you'll probably need to ignore them as I did in my question.

While checking difference between syncobj's and my views, the only difference is is_ms_shipped column. For syncobj it's 1, for others 0. It means that syncobj views are created by system.

P.S. I'll wait for some time and if nobody gives another answer, I'll accept mine.

like image 172
hgulyan Avatar answered Oct 21 '22 11:10

hgulyan


When you create a replication that does not include all the fields or other meta data changes from the original table. If you do a generate script from a publication it will show you how it is created (see below). The view provide a object to generate the bcp extracts during the initial snapshots.

Here is an example

-- Adding the article synchronization object exec sp_articleview @publication = N'publication_data', @article = N'tablename', @view_name = N'syncobj_0x4239373642443436', @filter_clause = N'', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 GO

P.S. I recently had a problem when the I dropped replication, it failed to drop these and then you have to manually drop the system views to reuse a replication script. Giving a error message

Msg 2714, Level 16, State 3: There is already an object named 'syncobj_0x3437324238353830' in the database.

Which caused the bcp to fail during the snapshot.

like image 41
kariato Avatar answered Oct 21 '22 10:10

kariato