problem with Sql _Variant in the code

  • HI All ,

    Below is my script:

    USE [DevSalesSecondDrawer]

    GO

    /****** Object: StoredProcedure [Neg].[UsersCreateStaticsGet] Script Date: 03/27/2012 09:47:59 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER Procedure [Neg].[UsersCreateStaticsGet]

    (@UserID typeSalesID =null,

    @StartDate date ,

    @EndDate date

    )

    As

    Begin

    if LEN(@UserID) = 0

    set @user-id = null

    Select OwnerID,COUNT(*) as JobsCreated

    into #jobsCount

    from Neg.jobs

    where (OwnerID = @user-id or @user-id is null ) and

    (CAST(CONVERT(CHAR(10),CreatedDate,120) as DateTime)

    between CAST (CONVERT (CHAR(10),@StartDate,120)as Date) and CAST (CONVERT(CHAR(10),@EndDate,120)as Date))

    group by OwnerID

    select AlternateOwnerID as OwnerID, COUNT(*) as AlternatesCreated

    into #AlternatesCount

    From Neg.Alternates

    where (AlternateOwnerID = @user-id or @user-id is null ) and

    (CAST(CONVERT(CHAR(10),CreatedDate,120) as DateTime)

    between CAST (CONVERT(CHAR(10), @StartDate,120)as Date) and CAST( CONVERT (CHAR(10), @EndDate,120)as Date))

    group by AlternateOwnerID

    select CreatedByUser as OwnerID , COUNT(*) as ItemsCreated

    into #ItemsCount

    From neg.Items

    where (CreatedByUser = @user-id or @user-id is null ) and

    (CAST (CONVERT(CHAR(10),CreatedDate,120) as DateTime )

    between CAST(CONVERT (CHAR(10),@Startdate,120) AS Date) and CAST(CONVERT (CHAR(10),@EndDate,120)as Date))

    group by CreatedByUser

    --Select * from #JobsCount

    --Select * from #ItemsCount

    --Select * from #alternatesCount

    Select ISNUll (ISNULL(j.OwnerID, a.OwnerID), i.OwnerID )as OwnerID ,j.JobsCreated,a.AlternatesCreated,i.ItemsCreated

    from #JobsCount J

    full outer join #alternatesCount a

    on j.OwnerID=a.OwnerID

    full outer join #ItemsCount i

    on a.OwnerID=i.OwnerID

    --drop table #JobsCount

    --drop table #Itemscount

    --drop table #alternatesCount

    End

    the above stored procedure is running fine SSMS.But , When I am running from the reports. I am getting the error message.

    Implicit conversion from data type sql_variant to date is not allowed. Use the CONVERT function to run this query. (.Net SqlClient Data Provider)

    Please help with this.

  • Please don't cross post. direct all replies here. http://www.sqlservercentral.com/Forums/Topic1273526-8-1.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Not sure if that helps but I had a similar problem and I seem to have fixed it by changing my stored procedure parameter type from DATE to SMALLDATETIME.

    I assume that this is because DATE type did not exist at the time of SSRS 2005 so it must have used SQL Variant...

    The bizarre thing is that it seemed to work for a quite while before it started to show that error...

  • I'm posting because I had this issue and by googling discovered it's a fairly common problem, but no clearcut solutions were described. I puzzled it out from the clues in the error message.

    I had the same issue while modifying old C#.net 1.1 framework in Visual Studio .NET 2003. I needed a new textbox control to collect date only input so I created the column in SQL server 2008 as a Date data type then I realized that this framework version didn't recognize the Date (only) data type so I changed it to smalldatetime. Unbeknowst to me at the time, the code initially created the command line to add the parameter using "variant" instead of DateTime.

    I would suggest anyone who receives this error should search for the word "variant" in their code, then change it to smalldatetime. For example in the following line of code, change the word "variant" to some other data type:

    this.cmdUpdate.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SomeDate", System.Data.SqlDbType.Variant, 4, "SomeDate"));

  • dmswebdev (5/5/2014)


    I'm posting because I had this issue and by googling discovered it's a fairly common problem, but no clearcut solutions were described. I puzzled it out from the clues in the error message.

    I had the same issue while modifying old C#.net 1.1 framework in Visual Studio .NET 2003. I needed a new textbox control to collect date only input so I created the column in SQL server 2008 as a Date data type then I realized that this framework version didn't recognize the Date (only) data type so I changed it to smalldatetime. Unbeknowst to me at the time, the code initially created the command line to add the parameter using "variant" instead of DateTime.

    I would suggest anyone who receives this error should search for the word "variant" in their code, then change it to smalldatetime. For example in the following line of code, change the word "variant" to some other data type:

    this.cmdUpdate.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SomeDate", System.Data.SqlDbType.Variant, 4, "SomeDate"));

    Since the original post was a duplicate post and replies were directed to the other thread, did you check the other thread for an answer?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply