Error 8152 while configuring distributor

[Dean]

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.

 

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
go
exec sp_MSdbuseraccess N'db', N'%'
go
exec sp_helpdistributor
go
exec sp_helpdistpublisher @check_user = 1
go
exec sp_helpdistributiondb
go
exec sp_helpreplicationdboption
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 = 1
go
exec sp_helpdistributor
go

 

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

 

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)
 May  3 2005 23:18:38
 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.

 

Dean

 

[Dean]

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 = 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

"Dean" <dvi...@nospam.gmail.com> wrote in message news:O3g6C8ii...@TK2MSFTNGP05.phx.gbl...