CODEWITHSUNDEEP
sql-servertransactionsdistributed-transactions
Gustavo
Gustavo

Reputation: 17

Transact SQL Server Error 105

Hey guys I am creating a store procedure but I am getting this error: 105 Unclosed quotation mark after the character string 'Guadalajara,Jal'., and i have looked through several websites, sadly I haven found an answer. Here is my code:

    Alter Procedure Insertar
(
    @Vuelos nvarchar(6),
    @Aero nvarchar(20),
    @Sal nvarchar(40),
    @Ori varchar(50),
    @Des varchar(50),
    @Lle nvarchar(40),
    @Per nvarchar(1),
    @Nom nvarchar(25),
    @Nodo nvarchar(13)
)
AS
    BEGIN
        Set XACT_ABORT ON
        Begin DISTRIBUTED TRANSACTION
            BEGIN TRY
                Insert into Reservaciones values
                (
                  @Vuelos,@Aero,@Sal,@Ori,@Des,@Lle,@Per,@Nom,GETDATE()
                )

                DECLARE @cmd as sysname;
                SET @cmd ='Insert into ['+@Nodo+'].'+'['+@Aero+'].[dbo].[Reservaciones] values'+
                '('''+@Vuelos+''','''+@Aero+''','''+@Sal+''','''+@Ori+''','''+@Des+''','''+@Lle+''','+@Per+','''+@Nom+''',GETDATE())'
                EXEC(@cmd)

                SET @cmd='Update ['+@Nodo+'].['+@Aero+'].[dbo].[Vuelos] Set Dis-='+@Per+' where Vuelo='+@Vuelos;
                EXEC(@cmd)
            END TRY
            BEGIN CATCH
                PRINT ERROR_NUMBER()
                PRINT ERROR_MESSAGE()
                ROLLBACK
            END CATCH
            IF @@TRANCOUNT > 0
                commit
    END

and this is what I input into the stored procedure

EXEC Insertar 'AM 500','Mexicana','2015-05-13 19:00:00.0','Guadalajara,Jalisco,Mexico','San Diego,California,Estados Unidos','2015-05-13 21:21:00.0',3,'gus','Gustavo\Nodo1';

To be more specific the issue is that sql is cutting half of whats in variable @Ori however I have debug and in that variable and it is complete, I have checked and yes that variable does have enough space for the text.

I appreciate any kind of help you guys can provide me.

Upvotes: 0

Views: 3065

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

This is because you are using the wrong datatype for @cmd. sysname datatype has a limited length of 128 Unicode characters.

I would also advise you to try to avoid dynamic sql since it's usually very vulnerable to SQL Injection attacks.

Upvotes: 1

Related Questions