Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


problem with Sql _Variant in the code


problem with Sql _Variant in the code

Author
Message
kodalisridevi
kodalisridevi
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
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)
Eric Mamet
Eric  Mamet
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1227 Visits: 870
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...
dmswebdev
dmswebdev
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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"));
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45250 Visits: 39932
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search