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

Error converting data type varchar to bigint Expand / Collapse
Author
Message
Posted Friday, March 28, 2014 12:40 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:46 AM
Points: 743, Visits: 1,055
Hi,

I'm doing this query:

DECLARE @TESTE BIGINT
DECLARE @DB VARCHAR(100)

SET @DB ='SGCTCENTRAL'

SET @TESTE = ('SELECT MAX(CODIGO) FROM '+@DB+'.DBO.SINCRONISMO')

SELECT @TESTE

I get this message:

Msg 8114, Level 16, State 5, Line 8
Error converting data type varchar to bigint.

Can someone help, please.

Thank you

Post #1556117
Posted Friday, March 28, 2014 12:49 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 3,545, Visits: 7,653
You're setting your variable to 'SELECT MAX(CODIGO) FROM SGCTCENTRAL.DBO.SINCRONISMO' and that is a string value. You need to use dynamic code by using sp_executesql (preferred method) or EXECUTE().
If your query is as simple as this, you don't need to use the @TESTE variable. You could execute the SELECT directly.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1556122
Posted Saturday, March 29, 2014 9:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 23,293, Visits: 32,028
river1 (3/28/2014)
Hi,

I'm doing this query:

DECLARE @TESTE BIGINT
DECLARE @DB VARCHAR(100)

SET @DB ='SGCTCENTRAL'

SET @TESTE = ('SELECT MAX(CODIGO) FROM '+@DB+'.DBO.SINCRONISMO')

SELECT @TESTE

I get this message:

Msg 8114, Level 16, State 5, Line 8
Error converting data type varchar to bigint.

Can someone help, please.

Thank you




declare @SQLCmd nvarchar(max),
@SQLParm nvarchar(max),
@TESTE bigint,
@DB nvarchar(128);

SET @DB = N'SGCTCENTRAL';
set @SQLCmd = N'SELECT @TESTOUT = MAX(CODIGO) FROM '+@DB+'.DBO.SINCRONISMO');
set @SQLParm = N'@TESTOUT bigint';

exec sp_executesql @SQLCmd, @SQLParm, N'@TESTOUT = @TESTE OUTPUT';
select @TESTE


Not sure if I did all of that correctly as I did it from memory and I have not tested in my sandbox database. At least it gives you an idea of how to proceed if it works.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1556222
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse