Implicit conversion from datatype......

• Anchelin

  SSCrazy

  Points: 2417

  More actions

  January 29, 2007 at 3:30 am

  #115925

  Hi All

  I get the following error:

  Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.

  My query is as follows:

  DECLARE

  @count1 int

  DECLARE

  @count2 int

  DECLARE

  @ServerName varchar(300)

  DECLARE

  @Query1 nvarchar(2000)

  DECLARE

  @Query2 nvarchar(2000)

  DECLARE

  @Query3 nvarchar(2000)

  set

  @count1 =0

  set

  @count2 =(Select max(ServerID) from Table where Status = 'Active')

  While

  @count1 <= @count2

  begin

  set

  @count1=@count1 +1

  set

  @ServerName =(Select distinct ServerName from Table where Status = 'Active' AND serverID = +@count1)

  set

  @Query2 = 'insert into AnotherTable'

  set

  @Query3 = @Query2 + ' SELECT * FROM OPENQUERY ('+@ServerName +','+''' SET FMTONLY OFF exec master.sp__Sumthing'')'

  Print

  @Query3

  exec

  (@Query3)

  end

   

  Can anyone of you perhaps help me with this one?

  Thanks in advance

  Anchelin

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

• YB

  SSC Enthusiast

  Points: 171

  More actions

  January 31, 2007 at 1:31 pm

  #686954

  Doesn't seem to be a clear reason why you might be getting that error, but I would start by checking the data types of the value being assigned to @count2. If your @serverName value is an IP or has a space or something, you might wanna wrap it with "[ ]". Other thank that. I just tweaked the cod a little bit for readability.

  DECLARE

  @count1 int

  DECLARE @count2 int

  DECLARE @ServerName varchar(300)

  DECLARE

  @Query nvarchar(3000)

  set

  @count1 =0

  set

  @count2 =(Select max(ServerID) from Table where Status = 'Active')

  While

  @count1 <= @count2

  begin

  set

  @count1=@count1 +1

  set

  @ServerName =(Select distinct ServerName from Table where Status = 'Active' AND serverID = +@count1)

  set

  @Query = 'insert into AnotherTable SELECT * FROM OPENQUERY (' + @ServerName + ', SET FMTONLY OFF exec master.sp__Sumthing)'

  --Print @Query

     exec

  (@Query)

  end

   

  Very interested in knowing the outcome of this issue.  Good Luck

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 31, 2007 at 7:14 pm

  #687001

  First When you use INSERT/SELECT you should especify the colum list on the insert table and on the select too.

  That way you know exactly what goes where.

  Secondly your source procedure is returning an sql_variant type which I could guess is coming from one of the serverproperty,databaseproperty(ex) or objectproperty built-in functions in SQL Server and the error *clearly* indicates that you have to convert it to the destination type explicitily.

   

  Cheers,

   

    

  
  * Noel

• SQlLogic

  SSCrazy Eights

  Points: 9686

  More actions

  April 13, 2012 at 12:55 pm

  #1473430

  Hi

  Are you able to solve this error ?I am working on it also . Let me know what steps you took to resolve this please.

  thanks

• DBA_Dom

  SSCrazy

  Points: 2902

  More actions

  April 13, 2012 at 2:12 pm

  #1473494

  Can you post definitions for 'table' and 'another table'. My guess is that they have a SQL_VARIANT column and you are push it into a NVARCHAR variable without explictly converting it.

  http://msdn.microsoft.com/en-us/library/ms187928.aspx

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 13, 2012 at 2:28 pm

  #1473506

  logicinside22 (4/13/2012)

  ---

  Hi

  Are you able to solve this error ?I am working on it also . Let me know what steps you took to resolve this please.

  thanks

  Also asked here: http://www.sqlservercentral.com/Forums/Topic1283379-391-1.aspx

  Please don't post the same question in multiple places, it just results in people answering an already answered question.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Anchelin

  SSCrazy

  Points: 2417

  More actions

  April 16, 2012 at 1:04 am

  #1473864

  Thanks noeld; this did the trick.... well 3 years ago 😀

  Thanks again!

  A

• dmswebdev

  SSC Enthusiast

  Points: 140

  More actions

  May 5, 2014 at 3:04 pm

  #1710882

  I know this is an old post but noeld's answer helped me too. I posted my solution over here: http://www.sqlservercentral.com/Forums/Topic1273528-1063-1.aspx?Update=1

  if anyone is interested.