Stored procedure error

  • 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

  • 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.

  • "You have a data mismatch between what your attempting to insert into the table, and what the table expects"

    hum?

    where?

  • would need to see the definition of SGCTCentral.DBO.SINCRONISMO

  • 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...

  • 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)'

  • It returns this error:

    Msg 8114, Level 16, State 5, Procedure spSGCT_UPDATESINCRONISMO_SGCTLOCAIS, Line 32

    Error converting data type varchar to bigint.

  • 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.

  • 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.

  • @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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.

  • @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)'

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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?

  • 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!

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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