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

Implicit conversion from datatype...... Expand / Collapse
Author
Message
Posted Monday, January 29, 2007 3:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:05 AM
Points: 120, Visits: 792

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Post #340415
Posted Wednesday, January 31, 2007 1:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 23, 2008 1:12 PM
Points: 59, Visits: 2

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

Post #341451
Posted Wednesday, January 31, 2007 7:14 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028

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
Post #341512
Posted Friday, April 13, 2012 12:55 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:44 AM
Points: 321, Visits: 1,269
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


Aim to inspire rather than to teach.
SQL Server DBA
Post #1283366
Posted Friday, April 13, 2012 2:12 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:57 AM
Points: 568, Visits: 69,606
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
Post #1283440
Posted Friday, April 13, 2012 2:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
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

Post #1283452
Posted Monday, April 16, 2012 1:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:05 AM
Points: 120, Visits: 792
Thanks noeld; this did the trick.... well 3 years ago
Thanks again!

A
Post #1283906
Posted Monday, May 5, 2014 3:04 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 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.
Post #1567717
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse