Hi,

I'm getting the 8152 ("String or binary data would be truncated", and =
sometimes "General error") error when running the EMs "Configure =
Publishing and Distribution Wizard", on executing "Configuring =
Distributor" step. I'm trying to set the server to act as both the =
publisher and its own distributor for merge replication.=20

It's happening on my dev machine, a laptop with 1 GB of memory, and =
plenty of disk space. It's also a domain member. SQL Server and Agent =
services are running under a domain account which also has local admin =
privileges. FWIW, I've tried registering the server in EM using both =
Windows and SQL Server authentication, with no change.

I don't remember experiencing this problem before. Event viewer contains =
nothing of interest, and the profiler trace looks like this:

exec sp_helpreplicationdboption=20
go
exec sp_MSdbuseraccess N'db', N'%'
go
exec sp_helpdistributor=20
go
exec sp_helpdistpublisher @check_user =3D 1
go
exec sp_helpdistributiondb=20
go
exec sp_helpreplicationdboption=20
go
exec sp_MSdbuseraccess N'db', N'%'
go
exec sp_get_distributor
go
exec sp_get_distributor
go
-- 'sp_adddistributor' was found in the text of this event.
-- The text has been replaced with this comment for security reasons.
go
exec sp_dropdistributor @no_checks =3D 1
go
exec sp_helpdistributor=20
go


It's SQL Server 2000 with SP 4, @@version reports the following:

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)=20
May 3 2005 23:18:38=20
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Uninstalling SQL Server, and installing it again didn't help.

Another, maybe important, detail: I have SQL Server 2005 SP 1 installed =
on the same machine (as a named instance). Also several versions of =
Visual Studio, and so on..

I'm pretty sure this has something to do with my local setup (because it =
works nice on several other machines), but I'm really lost here, don't =
know what else to check, where to look, or what to do, and would be very =
thankful for any pointers.

OK, I got it.. Thought I'd post the solution for it here, and possibly =
save somebody else a few hours of hair-pulling, debugging through =
sp_adddistributiondb proc, and things like these..

Remember I said it's my dev machine, and there's several versions of =
Visual Studio installed? Well, it turned out that one of them, VS 2003, =
modified the command prompt in a way that it executes a batch file =
(vsvars32.bat namely) each time it runs. Ths bat contains commands for =
setting up some environment variables VC++ 2003 needs, and also echoes =
several lines of comments on the screen, like this:

Setting environment for using Microsoft Visual Studio .NET 2003 =
tools.
(If you have another version of Visual Studio or Visual C++ =
installed and wish
to use its tools from the command line, run vcvars32.bat for that =
version.)

So, when the mentioned wizard executes sp_adddistributiondb, this proc =
calls sp_MSget_file_existence proc, which in turn calls xp_cmdshell to =
check does the file for distribution database already exists. It goes =
something like this:

create table #text_ret(cmdoutput nvarchar(20) collate =
database_default null)
insert into #text_ret exec @retcode =3D master..xp_cmdshell @command

(where @command is N'if exist "c:\program files\microsoft sql =
server\mssql\data\spin.mdf" echo file_exists'). Of course, there is no =
way that much text could fit into a nvarchar(20) column.. :( And that's =
where it fails.

And the solution was to remove the lines begining with "@ECHO" from the =
vsvars32.bat.. Ahhh..

Dean


[quoted text, click to view]
Hi,

I'm getting the 8152 ("String or binary data would be truncated", and =
sometimes "General error") error when running the EMs "Configure =
Publishing and Distribution Wizard", on executing "Configuring =
Distributor" step. I'm trying to set the server to act as both the =
publisher and its own distributor for merge replication.=20

It's happening on my dev machine, a laptop with 1 GB of memory, and =
plenty of disk space. It's also a domain member. SQL Server and Agent =
services are running under a domain account which also has local admin =
privileges. FWIW, I've tried registering the server in EM using both =
Windows and SQL Server authentication, with no change.

I don't remember experiencing this problem before. Event viewer =
contains nothing of interest, and the profiler trace looks like this:

exec sp_helpreplicationdboption=20
go
exec sp_MSdbuseraccess N'db', N'%'
go
exec sp_helpdistributor=20
go
exec sp_helpdistpublisher @check_user =3D 1
go
exec sp_helpdistributiondb=20
go
exec sp_helpreplicationdboption=20
go
exec sp_MSdbuseraccess N'db', N'%'
go
exec sp_get_distributor
go
exec sp_get_distributor
go
-- 'sp_adddistributor' was found in the text of this event.
-- The text has been replaced with this comment for security reasons.
go
exec sp_dropdistributor @no_checks =3D 1
go
exec sp_helpdistributor=20
go


It's SQL Server 2000 with SP 4, @@version reports the following:

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)=20
May 3 2005 23:18:38=20
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Uninstalling SQL Server, and installing it again didn't help.

Another, maybe important, detail: I have SQL Server 2005 SP 1 =
installed on the same machine (as a named instance). Also several =
versions of Visual Studio, and so on..

I'm pretty sure this has something to do with my local setup (because =
it works nice on several other machines), but I'm really lost here, =
don't know what else to check, where to look, or what to do, and would =
be very thankful for any pointers.