SQL Clone
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
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26085 Visits: 17536
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.

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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1589 Visits: 893
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 (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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 Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86037 Visits: 41095
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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