different length xact_seqno and transaction_timestamp

Hi NG
In transactional replication I found a different length of
the following varbinary(16) fields:
select xact_seqno from distribution..msrepl_transactions
go
RESULT:
0x000000110000171B0001
but
select transaction_timestamp from MSreplication_subscriptions
RESULT:
0x000000110000171B000100000000
Its an german sql server 2k with sp3
Is this a bug or a feature?
thanks
Thomas
Thomas,
it's a feature :-). As you have probably guessed, the 8 trailing zeros can
be ignored.
Regards,
Paul Ibison
|||MSrepl_commands contains two types of commands 1) sync commands constructed by the Snapshot Agent or snapshot.exe, 2) commands constructed by the log reader.
The long ones are commands constructed by the snapshot.exe binary. These are ones that are used to replicate the schema and related replication metadata to the subscriber(s).
The short ones are ones that are generated by the log reader agent or logread.exe. These are either sql commands with parameters or stored procedure calls with parameters.
You can view them by using sp_replbrowsecmds.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Different behavor with image and varbinary(max)?

Hello,
I am having problem with the different behavior of image and varbinary(max).
I thought these two types are compatible, but seeing some different behavior.

> Cannot insert literal value into "varbinary(max)", but we can with "image".
> "varbinary(max)" and "xml" data type seems to be compatible, but "image" is not.
Can someone tell me if this is expected behavior?
1. If I create table using "image" data type, I can insert string data into
the table.
create table kmimage(c1 image);
insert into kmimage values ('<value> my blob c1 a </value>');
select * from kmimage;
c1
0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
2. If I create table using varbinary(max), I cannot insert string data.
create table kmvarbin(c1 varbinary(max));
insert into kmvarbin values ('<value> my blob c1 a </value>');
==> Implicit conversion from data type varchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query.
No problem using hexadecimal representation.
insert into kmvarbin values
(0x3C76616C75653E206D7920626C6F622063312061203C2F7 6616C75653E);
select * from kmvarbin;
c1
0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
Thank you very much for your help.
KM
> Can someone tell me if this is expected behavior?
This is documented in the SQL 2005 Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm).
Implicit conversion from varchar to image is allowed. Conversion from
varchar to varbinary must be explicit.
Why are you storing character data in a column defined as binary?
Hope this helps.
Dan Guzman
SQL Server MVP
"KM" <KM@.discussions.microsoft.com> wrote in message
news:17377F49-0BBD-4895-8FA6-173FD4214C38@.microsoft.com...
> Hello,
> I am having problem with the different behavior of image and
> varbinary(max).
> I thought these two types are compatible, but seeing some different
> behavior.
>
> Can someone tell me if this is expected behavior?
> 1. If I create table using "image" data type, I can insert string data
> into
> the table.
> create table kmimage(c1 image);
> insert into kmimage values ('<value> my blob c1 a </value>');
> select * from kmimage;
> c1
> --
> 0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
> 2. If I create table using varbinary(max), I cannot insert string data.
> create table kmvarbin(c1 varbinary(max));
> insert into kmvarbin values ('<value> my blob c1 a </value>');
> ==> Implicit conversion from data type varchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query.
> No problem using hexadecimal representation.
> insert into kmvarbin values
> (0x3C76616C75653E206D7920626C6F622063312061203C2F7 6616C75653E);
> select * from kmvarbin;
> c1
> --
> 0x3C76616C75653E206D7920626C6F622063312061203C2F76 616C75653E
> Thank you very much for your help.
> KM
>

Different behavor with image and varbinary(max)?

Hello,
I am having problem with the different behavior of image and varbinary(max).
I thought these two types are compatible, but seeing some different behavior
.
[vbcol=seagreen]
> Cannot insert literal value into "varbinary(max)", but we can with "image"
.
> "varbinary(max)" and "xml" data type seems to be compatible, but "image" is not.[/
vbcol]
Can someone tell me if this is expected behavior?
1. If I create table using "image" data type, I can insert string data into
the table.
create table kmimage(c1 image);
insert into kmimage values ('<value> my blob c1 a </value>');
select * from kmimage;
c1
--
0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
2. If I create table using varbinary(max), I cannot insert string data.
create table kmvarbin(c1 varbinary(max));
insert into kmvarbin values ('<value> my blob c1 a </value>');
==> Implicit conversion from data type varchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query.
No problem using hexadecimal representation.
insert into kmvarbin values
(0x3C76616C75653E206D7920626C6F622063312
061203C2F76616C75653E);
select * from kmvarbin;
c1
--
0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
Thank you very much for your help.
KM> Can someone tell me if this is expected behavior?
This is documented in the SQL 2005 Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5
-6f5a22343ea8.htm).
Implicit conversion from varchar to image is allowed. Conversion from
varchar to varbinary must be explicit.
Why are you storing character data in a column defined as binary?
Hope this helps.
Dan Guzman
SQL Server MVP
"KM" <KM@.discussions.microsoft.com> wrote in message
news:17377F49-0BBD-4895-8FA6-173FD4214C38@.microsoft.com...
> Hello,
> I am having problem with the different behavior of image and
> varbinary(max).
> I thought these two types are compatible, but seeing some different
> behavior.
>
> Can someone tell me if this is expected behavior?
> 1. If I create table using "image" data type, I can insert string data
> into
> the table.
> create table kmimage(c1 image);
> insert into kmimage values ('<value> my blob c1 a </value>');
> select * from kmimage;
> c1
> --
> 0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
> 2. If I create table using varbinary(max), I cannot insert string data.
> create table kmvarbin(c1 varbinary(max));
> insert into kmvarbin values ('<value> my blob c1 a </value>');
> ==> Implicit conversion from data type varchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query.
> No problem using hexadecimal representation.
> insert into kmvarbin values
> (0x3C76616C75653E206D7920626C6F622063312
061203C2F76616C75653E);
> select * from kmvarbin;
> c1
> --
> 0x3C76616C75653E206D7920626C6F6220633120
61203C2F76616C75653E
> Thank you very much for your help.
> KM
>