Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

problem with Sql _Variant in the code Expand / Collapse
Author
Message
Posted Tuesday, March 27, 2012 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 2, 2012 9:15 AM
Points: 32, Visits: 122
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 @UserID = null

Select OwnerID,COUNT(*) as JobsCreated
into #jobsCount
from Neg.jobs
where (OwnerID = @UserID or @UserID 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 = @UserID or @UserID 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 = @UserID or @UserID 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.
Post #1273528
Posted Tuesday, March 27, 2012 8:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1273562
Posted Friday, August 31, 2012 4:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:51 PM
Points: 1,191, Visits: 794
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...
Post #1352674
Posted Monday, May 5, 2014 3:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 3:02 PM
Points: 2, Visits: 1
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"));
Post #1567716
Posted Monday, May 5, 2014 3:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:11 PM
Points: 35,777, Visits: 32,450
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1567725
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse