Paul post about stored procedure problems in repliacation
- From: "Sam" <sam@xxxxxxxx>
- Date: Wed, 20 Apr 2005 15:52:18 -0400
Lost my question =). I seriously think there is something wrong with adodb
thats causing this.
Wrapper stored procedure with @working directory hardcoded (works)
=======================================================================
If I write a wrapper stored procedure for "sp_adddistpublisher" and don't
pass any parameters but hard code the "@working directory" the code runs and
executes ok.
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "adddistpublishertest"
cmd.CommandType = adCmdStoredProc
cmd.Execute
Wrapper stored procedure with @working directory being passed in (fails)
=======================================================================
If I run the following wrapper code and I don't hardcode the "@working
directory" in the procedure it doesn't work (pass @workingdirectory in):
' This code is a wrapper of the sp_adddistpublisher stored procedure. Takes
@testworking_directory and passes it to sp_adddistpublisher
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "adddistpublishertest"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@testworking_directory",
adVarChar, adParamInput, 255)
cmd("@testworking_directory") = "\\BUSHWACKER\C$\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA"
cmd.Execute
Initial stored procedure sp_adddistpublisher that fails on working directory
=======================================================================
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "sp_adddistpublisher"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@publisher", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@distribution_db", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@security_mode", adInteger,
adParamInput)
cmd.Parameters.Append cmd.CreateParameter("@working_directory", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@trusted", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@thirdparty_flag", adInteger,
adParamInput)
cmd("@publisher") = "BUSHWACKER"
cmd("@distribution_db") = "distribution"
cmd("@security_mode") = 1
cmd("@working_directory") = "\\BUSHWACKER\C$\Program Files\Microsoft SQL
Server\MSSQL\REPLDATA"
cmd("@trusted") = "false"
cmd("@thirdparty_flag") = 0
Other stored procedure that gives a similar problem
========================================================
If I write a wrapper stored procedure and pass the path it gives the same
problem. I think this could be an issue with ado classes that I reference
in my vb. I continued on in the project and found out this is occuring on
another stored procedure. I don't want to expand the debugging we have to
do. But its taking my focus off of the stored procedure being the problem
and blaming it on the vb.
' Add merge publication
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn2
cmd.CommandText = "sp_addmergepublication"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@publication", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@description", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@retention", adInteger,
adParamInput)
cmd.Parameters.Append cmd.CreateParameter("@sync_mode", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@allow_push", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@allow_pull", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@allow_anonymous", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@enabled_for_internet",
adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@centralized_conflicts",
adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@dynamic_filters", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@snapshot_in_defaultfolder",
adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@compress_snapshot", adVarChar,
adParamInput, 5)
cmd.Parameters.Append cmd.CreateParameter("@ftp_port", adInteger,
adParamInput)
cmd.Parameters.Append cmd.CreateParameter("@ftp_login", adVarChar,
adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@conflict_retention", adInteger,
adParamInput)
cmd.Parameters.Append cmd.CreateParameter("@keep_partition_changes",
adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@allow_subscription_copy",
adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@allow_synctoalternate",
adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@add_to_active_directory",
adVarChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("@max_concurrent_merge",
adInteger, adParamInput)
cmd.Parameters.Append
cmd.CreateParameter("@max_concurrent_dynamic_snapshots", adInteger,
adParamInput)
cmd("@publication") = "test"
cmd("@description") = "Merge publication of test"
cmd("@retention") = 0
cmd("@sync_mode") = "native"
cmd("@allow_push") = "true"
cmd("@allow_pull") = "true"
cmd("@allow_anonymous") = "true"
cmd("@enabled_for_internet") = "false"
cmd("@centralized_conflicts") = "true"
cmd("@dynamic_filters") = "false"
cmd("@snapshot_in_defaultfolder") = "true"
cmd("@compress_snapshot") = "true"
cmd("@ftp_port") = 21
cmd("@snapshot_in_defaultfolder") = "true"
cmd("@ftp_login") = "anonymous"
cmd("@conflict_retention") = 14
cmd("@keep_partition_changes") = "false"
cmd("@allow_synctoalternate") = "false"
cmd("@add_to_active_directory") = "false"
cmd("@max_concurrent_merge") = 0
cmd("@max_concurrent_dynamic_snapshots") = 0
cmd.Execute
Sam
www.lunafurniture.com
Online Furniture, bedrooms, livingrooms
.
- Prev by Date: Re: Invalid working directory sp_adddistpublisher
- Next by Date: Replication VIA Modem
- Previous by thread: Ideas
- Next by thread: Replication VIA Modem
- Index(es):
Relevant Pages
|
