Confused By Error Msg

I am using a VB aspx page to pass a parameter to a stored procedure and to
get two parameters returned. The input parameter is an integer, one of the
output parameters is also and integer and the other output parameter is an
nvarchar. When I run the execute command I get an error:
Implicit conversion from data type sql_variant to nvarchar is not allowed.
Use the CONVERT function to run this query.
I have no idea where the sql_variant type comes from? Any thoughts?
Here is the portion of my code that makes the call followed by the SP code.
========================================
=========
Dim cmd4 As New OleDb.OleDbCommand("GetAdClicks", CommonClass.myConn)
cmd4.CommandType = CommandType.StoredProcedure
cmd4.Parameters.Add("@.id", SqlDbType.Int)
cmd4.Parameters("@.id").Value = Request.Params("ID")
cmd4.Parameters.Add("@.clickcount", SqlDbType.Int)
cmd4.Parameters("@.clickcount").Direction = ParameterDirection.Output
cmd4.Parameters.Add("@.url", SqlDbType.NVarChar, 100)
cmd4.Parameters("@.url").Direction = ParameterDirection.Output
cmd4.ExecuteNonQuery()
=========== SP Code ====================
ALTER Procedure GetAdClicks
(
@.id int,
@.clickcount int OUTPUT,
@.url nvarchar(100) OUTPUT
)
AS
SELECT @.clickcount = clickcount, @.url=url
FROM adserver
WHERE id=@.idWayne,
What is the data type for the URL column in the adserver table?
EXEC SP_COLUMNS AUTHORS
HTH
Jerry
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:%23%23Zm2BP2FHA.2440@.TK2MSFTNGP10.phx.gbl...
>I am using a VB aspx page to pass a parameter to a stored procedure and to
>get two parameters returned. The input parameter is an integer, one of the
>output parameters is also and integer and the other output parameter is an
>nvarchar. When I run the execute command I get an error:
> Implicit conversion from data type sql_variant to nvarchar is not allowed.
> Use the CONVERT function to run this query.
> I have no idea where the sql_variant type comes from? Any thoughts?
> Here is the portion of my code that makes the call followed by the SP
> code.
> ========================================
=========
> Dim cmd4 As New OleDb.OleDbCommand("GetAdClicks", CommonClass.myConn)
> cmd4.CommandType = CommandType.StoredProcedure
> cmd4.Parameters.Add("@.id", SqlDbType.Int)
> cmd4.Parameters("@.id").Value = Request.Params("ID")
> cmd4.Parameters.Add("@.clickcount", SqlDbType.Int)
> cmd4.Parameters("@.clickcount").Direction = ParameterDirection.Output
> cmd4.Parameters.Add("@.url", SqlDbType.NVarChar, 100)
> cmd4.Parameters("@.url").Direction = ParameterDirection.Output
> cmd4.ExecuteNonQuery()
>
> =========== SP Code ====================
> ALTER Procedure GetAdClicks
> (
> @.id int,
> @.clickcount int OUTPUT,
> @.url nvarchar(100) OUTPUT
> )
> AS
> SELECT @.clickcount = clickcount, @.url=url
> FROM adserver
> WHERE id=@.id
>
>|||It is nvarchar
Wayne
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uj3rHFP2FHA.3788@.tk2msftngp13.phx.gbl...
> Wayne,
> What is the data type for the URL column in the adserver table?
> EXEC SP_COLUMNS AUTHORS
> HTH
> Jerry
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:%23%23Zm2BP2FHA.2440@.TK2MSFTNGP10.phx.gbl...
>|||Wayne,
What happens if you run the sproc from Query Analyzer?
HTH
Jerry
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:OEExaLP2FHA.3448@.TK2MSFTNGP10.phx.gbl...
> It is nvarchar
> Wayne
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uj3rHFP2FHA.3788@.tk2msftngp13.phx.gbl...
>|||Jerry;
When I run it from QA everything seems fine, I get the count as a number and
the url is a string
Wayne
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uXH7iPP2FHA.3108@.tk2msftngp13.phx.gbl...
> Wayne,
> What happens if you run the sproc from Query Analyzer?
> HTH
> Jerry
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:OEExaLP2FHA.3448@.TK2MSFTNGP10.phx.gbl...
>|||Wayne,
Ok...that probably removes SQL as the cause. Might want to follow this up
with a programming NG for the programming language you're using.
HTH
Jerry
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:%23XKQHtP2FHA.3108@.tk2msftngp13.phx.gbl...
> Jerry;
> When I run it from QA everything seems fine, I get the count as a number
> and the url is a string
> Wayne
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uXH7iPP2FHA.3108@.tk2msftngp13.phx.gbl...
>|||OK - will do.
Thanks for the help.
Wayne
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:upfzonY2FHA.3416@.tk2msftngp13.phx.gbl...
> Wayne,
> Ok...that probably removes SQL as the cause. Might want to follow this up
> with a programming NG for the programming language you're using.
> HTH
> Jerry
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:%23XKQHtP2FHA.3108@.tk2msftngp13.phx.gbl...
>

Conflict table cleanup interval

What replication parameter sets the interval in which data in the conflict tables is deleted in SQL Server 2000 SP4?Apparently when the snapshots are generated the conflict tables are cleaned out as part of the process. This was causing my conflicts to disappear each night when the snapshot agent ran.|||

Rerunning snapshot should not be cleaning the tables.

@.conflict_retention governs how long the data will be kept in these tables before purging.

|||If I have data in the conflict tables and then execute the snapshot SQL Agent job the conflict tables are cleaned out. I have the @.conflict_retention value set to 90 days.
The publication is a filtered merge publication.|||

Sorry I missed your earlier comment that this was SQL Server 2000.

This is a known issue in SQL Server 2000 and has been fixed in SQL Server 2005.

Conflict data automatic cleanup

What replication parameter sets the interval in which data in the
conflict tables is deleted in SQL Server 2000 SP4?
The data in my conflict tables appears to be deleted every night.
From BOL: "The Merge Agent deletes data from the conflict table if it is
older than the conflict retention period for the publication, which is
specified using the @.conflict_retention parameter of sp_addmergepublication
(the default is 14 days)."
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||the default is every 14 days. You set it with @.conflict_retention parameter
of sp_addmergepublication
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zebra1024" <jcmackenzie@.gmail.com> wrote in message
news:1163168845.703224.81300@.f16g2000cwb.googlegro ups.com...
> What replication parameter sets the interval in which data in the
> conflict tables is deleted in SQL Server 2000 SP4?
> The data in my conflict tables appears to be deleted every night.
>
|||I thought that was the parameter but my conflict tables seem to be
cleaned out each night even though this parameter is set to 14. Does
generating the snapshots clean out these tables also?
Paul Ibison wrote:
> From BOL: "The Merge Agent deletes data from the conflict table if it is
> older than the conflict retention period for the publication, which is
> specified using the @.conflict_retention parameter of sp_addmergepublication
> (the default is 14 days)."
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I just tested this and apparently generating the snapshots does clean
out the conflict tables.
zebra1024 wrote:[vbcol=seagreen]
> I thought that was the parameter but my conflict tables seem to be
> cleaned out each night even though this parameter is set to 14. Does
> generating the snapshots clean out these tables also?
>
> Paul Ibison wrote:
|||Very interesting - thanks for the update. I'll request a BOL update for
this, as it is not mentioned in the right place (if at all).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .