June 27, 2012 at 7:41 am
Hi,
I try to execute the following stored procedure:
USE [SGSC]
GO
/****** Object: StoredProcedure [dbo].[spSGCT_UPDATESINCRONISMO_SGCTLOCAIS] Script Date: 06/27/2012 14:33:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSGCT_UPDATESINCRONISMO_SGCTLOCAIS]
(@DATA1 AS varchar(50),@DATA2 AS varchar(50),@NOMEFICHEIRO AS VARCHAR(500),
@CODRF AS VARCHAR(5),@INICIO AS VARCHAR(10),@FIM AS varchar(50), @DB AS VARCHAR(50),
@CLASSIFICACAO AS varchar(50))
AS
BEGIN
DECLARE @USER AS INT,
@STRSQL AS NVARCHAR(4000),
@PARMDEFINITION AS NVARCHAR (4000),
@DATA4 as varchar(50),
@DATA5 as varchar(50)
EXEC sp_SGCT_USERSGSC @DB
SET @STRSQL = 'select @val = codigo from '+@DB+'.dbo.utilizadores where nome =''sgsc'''
SET @PARMDEFINITION = '@Val INT OUTPUT'
EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @USER OUTPUT
set @DATA4 = (select getdate())
set @DATA5 = @DATA4
if @INICIO ='T'
BEGIN
SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
(
'''+@DATA4+''','''+@DATA5+''' ,''I'',
'+@CLASSIFICACAO+','+@user+',NULL,'''+@NOMEFICHEIRO+''',
'''+@CODRF+''',NULL,'+@FIM+',0
)'
END
ELSE
SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
(
'''+@DATA4+''','''+@DATA5+''' ,''I'',
'+@CLASSIFICACAO+','+@user+',NULL,'''+@NOMEFICHEIRO+''',
'''+@CODRF+''','''+@INICIO+''', '+@FIM+',0
)'
EXEC (@STRSQL)
END
I execute it like :
EXEC dbo.spSGCT_UPDATESINCRONISMO_SGCTLOCAIS getdate,
getdate,
'PastaExportacao SGCTlocal - 4.01 - 20120523 112924 - D - 1119_CS.rar',
'4.01',
'T',
1119,
'SGCTCentral',
2
The error returned by Management studio is this:
Msg 245, Level 16, State 1, Procedure spSGCT_UPDATESINCRONISMO_SGCTLOCAIS, Line 28
Conversion failed when converting the varchar value 'INSERT INTO SGCTCentral.DBO.SINCRONISMO VALUES
(
'Jun 27 2012 2:40PM','Jun 27 2012 2:40PM' ,'I',
2,' to data type int.
Can someone help?
Thanks
June 27, 2012 at 7:56 am
You have a data mismatch between what your attempting to insert into the table, and what the table expects. Do an explicit conversion on your inserted value, and it will correct your error.
June 27, 2012 at 7:57 am
"You have a data mismatch between what your attempting to insert into the table, and what the table expects"
hum?
where?
June 27, 2012 at 8:00 am
would need to see the definition of SGCTCentral.DBO.SINCRONISMO
June 27, 2012 at 8:04 am
Hi saw IT.
Definition:
CREATE TABLE [dbo].[SINCRONISMO](
[CODIGO] [bigint] IDENTITY(1,1) NOT NULL,
[DT_INI_PROC] [datetime] NOT NULL,
[DT_FIM_PROC] [datetime] NULL,
[TP_PROCESSO] [char](1) NOT NULL,
[COD_TP_CLASSIFICACAO] [int] NOT NULL,
[UTILIZADOR] [int] NOT NULL,
[COD_SINCRONISMO] [bigint] NULL,
[FICHEIRO] [varchar](255) NOT NULL,
[COD_REP_FISCAL] [varchar](5) NULL,
[COD_SINCRONISMO_INI] [bigint] NULL,
[COD_SINCRONISMO_FIM] [bigint] NULL,
[STATUS] [int] NOT NULL,
CONSTRAINT [PK_SINCRONISMO] PRIMARY KEY CLUSTERED
(
The only different field I can see is the COD_SINCRONISMO_INI
But I put like this:
if @INICIO ='T'
BEGIN
SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
(
'''+@DATA4+''','''+@DATA5+''' ,''I'',
'+@CLASSIFICACAO+','+@user+',NULL,'''+@NOMEFICHEIRO+''',
'''+@CODRF+''',NULL,'+@FIM+',0
)'
END
ELSE
SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
(
'''+@DATA4+''','''+@DATA5+''' ,''I'',
'+@CLASSIFICACAO+','+@user+',NULL,'''+@NOMEFICHEIRO+''',
'''+@CODRF+''','+@INICIO+', '+@FIM+',0
)'
EXEC (@STRSQL)
And i still get the same error...
June 27, 2012 at 8:11 am
you seem to have additional ' which dont seem to be required, try this instead
SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES('+@DATA4+','+@DATA5+',I,'+@CLASSIFICACAO+','+@user+',NULL,'+@NOMEFICHEIRO+','+@CODRF+',NULL,'+@FIM+',0)'
June 27, 2012 at 8:15 am
It returns this error:
Msg 8114, Level 16, State 5, Procedure spSGCT_UPDATESINCRONISMO_SGCTLOCAIS, Line 32
Error converting data type varchar to bigint.
June 27, 2012 at 8:16 am
The procedure is now like this:
USE [SGSC]
GO
/****** Object: StoredProcedure [dbo].[spSGCT_UPDATESINCRONISMO_SGCTLOCAIS] Script Date: 06/27/2012 14:33:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSGCT_UPDATESINCRONISMO_SGCTLOCAIS]
(@DATA1 AS varchar(50),@DATA2 AS varchar(50),@NOMEFICHEIRO AS VARCHAR(500),
@CODRF AS VARCHAR(5),@INICIO AS VARCHAR(10),@FIM AS bigint, @DB AS VARCHAR(50),
@CLASSIFICACAO AS bigint)
AS
BEGIN
DECLARE @USER AS INT,
@STRSQL AS NVARCHAR(4000),
@PARMDEFINITION AS NVARCHAR (4000),
@DATA4 as varchar(50),
@DATA5 as varchar(50)
EXEC sp_SGCT_USERSGSC @DB
SET @STRSQL = 'select @val = codigo from '+@DB+'.dbo.utilizadores where nome =''sgsc'''
SET @PARMDEFINITION = '@Val INT OUTPUT'
EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @USER OUTPUT
set @DATA4 = (select getdate())
set @DATA5 = @DATA4
SET @STRSQL = ''
if @INICIO ='T'
BEGIN
SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
('+@DATA4+','+@DATA5+',I,'+@CLASSIFICACAO+','+@user+',NULL,'+@NOMEFICHEIRO+','+@CODRF+',NULL,'+@FIM+',0)'
--SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
--(
--'''+@DATA4+''','''+@DATA5+''' ,''I'',
--'+@CLASSIFICACAO+','+@user+',NULL,'''+@NOMEFICHEIRO+''',
--'''+@CODRF+''',null,'+@FIM+',0
--)'
print @STRSQL
END
-- ELSE
--SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
--(
--'''+@DATA4+''','''+@DATA5+''' ,''I'',
--'+@CLASSIFICACAO+','+@user+',NULL,'''+@NOMEFICHEIRO+''',
--'''+@CODRF+''','''+@INICIO+''','+@FIM+',0
--)'
-- print @STRSQL
-- EXEC (@STRSQL)
I don't even execute the @STRSQL I just try to print it.
Still I get this error message.
June 27, 2012 at 8:27 am
You have datatype mismatches all over that proc.
COD_TP_CLASSIFICACAO is an INT but your passing in a BIGINT
FICHEIRO is VARCHAR(255) and your passing in a VARCHAR(500)
Also you will need to delcare all variables as VARCHAR if you are passing them into @STRSQL or convert them on the SET @STRSQL= line as its getting confused with implicit conversions converting strings to numbers instead of numbers to strings.
June 27, 2012 at 8:28 am
@river1, here are two useful hints for you:
1. Don't encapsulate a batch of statements into a stored procedure until you've finished debugging them - it's much easier to debug a set of statements.
2. Whenever you are writing/debugging EXEC() or sp_executesql, ALWAYS print the content of the variable FIRST:
PRINT @mysqlstring
and examine the code generated. Copy and paste it into another SSMS window and run it. This is MUCH easier than trying to figure out what's going wrong when a faulty string is executed.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 27, 2012 at 9:19 am
I have done it.
My proc is now as this:
USE [SGSC]
GO
/****** Object: StoredProcedure [dbo].[spSGCT_UPDATESINCRONISMO_SGCTLOCAIS] Script Date: 06/27/2012 16:13:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSGCT_UPDATESINCRONISMO_SGCTLOCAIS]
(@DATA1 AS varchar(50),@DATA2 AS varchar(50),@NOMEFICHEIRO AS VARCHAR(255),
@CODRF AS VARCHAR(5),@INICIO AS VARCHAR(10),@FIM AS varchar(10), @DB AS VARCHAR(50),
@CLASSIFICACAO AS int)
AS
BEGIN
DECLARE @USER AS INT,
@STRSQL AS NVARCHAR(4000),
@PARMDEFINITION AS NVARCHAR (4000),
@DATA4 as varchar(50),
@DATA5 as varchar(50)
EXEC sp_SGCT_USERSGSC @DB
SET @STRSQL = 'select @val = codigo from '+@DB+'.dbo.utilizadores where nome =''sgsc'''
SET @PARMDEFINITION = '@Val INT OUTPUT'
EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @USER OUTPUT
set @DATA4 = (select getdate())
set @DATA5 = @DATA4
SET @STRSQL = ''
if @INICIO ='T'
BEGIN
SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
('+@DATA4+','+@DATA5+',I,'+@CLASSIFICACAO+','+@user+',NULL,'+@NOMEFICHEIRO+','+@CODRF+',NULL,'+@FIM+',0)'
--SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
--(
--'''+@DATA4+''','''+@DATA5+''' ,''I'',
--'+@CLASSIFICACAO+','+@user+',NULL,'''+@NOMEFICHEIRO+''',
--'''+@CODRF+''',null,'+@FIM+',0
--)'
print @STRSQL
END
-- ELSE
--SET @STRSQL ='INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
--(
--'''+@DATA4+''','''+@DATA5+''' ,''I'',
--'+@CLASSIFICACAO+','+@user+',NULL,'''+@NOMEFICHEIRO+''',
--'''+@CODRF+''','''+@INICIO+''','+@FIM+',0
--)'
-- print @STRSQL
-- EXEC (@STRSQL)
END
Still, when I call it like:
EXEC dbo.spSGCT_UPDATESINCRONISMO_SGCTLOCAIS getdate,
getdate,
'PastaExportacao SGCTlocal - 4.01 - 20120523 112924 - D - 1119_CS.rar',
'4.01',
'T',
1119,
'SGCTCentral',
2
I receive this error:
Msg 245, Level 16, State 1, Procedure spSGCT_UPDATESINCRONISMO_SGCTLOCAIS, Line 32
Conversion failed when converting the varchar value 'INSERT INTO SGCTCentral.DBO.SINCRONISMO VALUES
(Jun 27 2012 4:18PM,Jun 27 2012 4:18PM,I,' to data type int.
June 27, 2012 at 9:29 am
@CLASSIFICACAO is declared as an int, you have to cast it as a string type to concatenate it into the string:
SET @STRSQL = 'INSERT INTO ' + @DB + '.DBO.SINCRONISMO VALUES ('
+@DATA4
+','
+@DATA5
+',I,'
+CAST(@CLASSIFICACAO AS VARCHAR(50)) -- use an appropriate value here
+','
+@user
+',NULL,'
+@NOMEFICHEIRO
+','
+@CODRF
+',NULL,'
+@FIM
+',0)'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 27, 2012 at 1:35 pm
ChrisM@Work (6/27/2012)
@CLASSIFICACAO is declared as an int, you have to cast it as a string type to concatenate it into the string:
SET @STRSQL = 'INSERT INTO ' + @DB + '.DBO.SINCRONISMO VALUES ('+@DATA4
+','
+@DATA5
+',I,'
+CAST(@CLASSIFICACAO AS VARCHAR(50)) -- use an appropriate value here
+','
+@user
+',NULL,'
+@NOMEFICHEIRO
+','
+@CODRF
+',NULL,'
+@FIM
+',0)'
@user is declared int, would we convert that also?
June 29, 2012 at 2:03 am
patrickmcginnis59 (6/27/2012)
ChrisM@Work (6/27/2012)
@CLASSIFICACAO is declared as an int, you have to cast it as a string type to concatenate it into the string:
SET @STRSQL = 'INSERT INTO ' + @DB + '.DBO.SINCRONISMO VALUES ('+@DATA4
+','
+@DATA5
+',I,'
+CAST(@CLASSIFICACAO AS VARCHAR(50)) -- use an appropriate value here
+','
+@user
+',NULL,'
+@NOMEFICHEIRO
+','
+@CODRF
+',NULL,'
+@FIM
+',0)'
@user is declared int, would we convert that also?
Of course!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 29, 2012 at 3:43 am
use isnumeric() function for the column on which u get the data conversion error.
-----------------------------------------------------------------------------
संकेत कोकणे
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply