Dont understant this error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Dont understant this error

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-07-31 : 17:54:22

ALTER PROCEDURE [dbo].[sp_ClientUser_Dtsx_add]
@ID int,
@Licence varchar(6),
@IsMaster int,
@IsClientMaster int,
@Forename varchar(32),
@Surname varchar(32),
@Position varchar(256),
@Telephone varchar(32),
@Extension varchar(32),
@Emergency varchar(32),
@Mobile varchar(32),
@Fax varchar(32),
@Email varchar(100),
@Password varchar(32),
@locked int,
@PERDAccounts int,
@PERImport int,
@PERManual int,
@PERSubmit int,
@PERSecuritySubmit int,
@Active int,
@CreatedByID int

AS
BEGIN
SET NOCOUNT ON;
DECLARE @ssisPkgFilePath varchar(500)
SET @ssisPkgFilePath = 'UserProfiles_add.dtsx'
IF nullif(ltrim(rtrim(@ssisPkgFilePath)), '') is null
begin
--{
RAISERROR( 'Invalid ''ssisPkgFilePath'' value' /*Message*/, 16 /*Severity*/, 1 /*State (arbitary number)*/ )
--}
end

DECLARE @dtsExecCmd varchar(4000) -- varchar(8000) is the max length allowed for "varchar"

SET @dtsExecCmd = 'dtexec /F "' + ltrim(rtrim(@ssisPkgFilePath)) + '"'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::ID].Properties[Value]";"\"' + (@ID)+ '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Licence].Properties[Value]";"\"' + @Licence + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::IsMaster].Properties[Value]";"\"' + @IsMaster + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::IsClientMaster].Properties[Value]";"\"' + @IsClientMaster + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Forename].Properties[Value]";"\"' + @Forename + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Surname].Properties[Value]";"\"' + @Surname + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Position].Properties[Value]";"\"' + @Position + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Telephone].Properties[Value]";"\"' + @Telephone + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Extension].Properties[Value]";"\"' + @Extension + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Emergency].Properties[Value]";"\"' + @Emergency + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Mobile].Properties[Value]";"\"' + @Mobile + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Fax].Properties[Value]";"\"' + @Fax + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Email].Properties[Value]";"\"' + @Email + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Password].Properties[Value]";"\"' + @Password + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::locked].Properties[Value]";"\"' + @locked + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::PERDAccounts].Properties[Value]";"\"' + @PERDAccounts + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::PERImport].Properties[Value]";"\"' + @PERImport + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::PERManual].Properties[Value]";"\"' + @PERManual + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::PERSubmit].Properties[Value]";"\"' + @PERSubmit + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::PERSecuritySubmit].Properties[Value]";"\"' + @PERSecuritySubmit + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Active].Properties[Value]";"\"' + @Active + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::CreatedByID].Properties[Value]";"\"' + @CreatedByID + '\""'

DECLARE @result INT
PRINT @dtsExecCmd
DECLARE @output TABLE( [ssisOutput] varchar(max) )
INSERT INTO @output
--{
EXEC @result = master..xp_cmdshell @dtsExecCmd
--}

IF (@result is not null and @result<>0) OR EXISTS(select 1 from @output where ssisoutput like '%error%')
begin
SELECT * FROM @output
end
SET NOCOUNT OFF;
END

Conversion failed when converting the varchar value 'dtexec /F "UserProfiles_add.dtsx" /SET "\package.Variables[User::ID].Properties[Value]";"\' to data type int.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-31 : 17:58:21

You'll need to add converts for all of the non varchar data types that you are using in your concatenation for @dtsExecCmd. For instance, you need a convert for @ID, needs to be converted to varchar in order to add it to @dtsExecCmd.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-07-31 : 19:01:34

quote:
Originally posted by tkizer

You'll need to add converts for all of the non varchar data types that you are using in your concatenation for @dtsExecCmd. For instance, you need a convert for @ID, needs to be converted to varchar in order to add it to @dtsExecCmd.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

could you give me an example please

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-31 : 19:05:16

CONVERT(varchar(10), @ID)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-08-01 : 06:08:00

Thank you for your help, that is one problem out of the way, however I now have come up with a different one,
If for instance @position is a NULL how should that be handled in the script?

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-08-01 : 06:21:18

I have tried
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Position].Properties[Value]";"\"' + ISNULL(@Position,'''') + '\""'

however it results in a ' in the field

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-01 : 12:38:43

Remove two of the single quotes in the ISNULL.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-08-01 : 12:48:21

I have tried that but get the following error

The argument "\package.Variables[User::Position].Properties[Value];"" has mismatched quotes.

SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Position].Properties[Value]";"\"' + ISNULL(@Position,'') + '\""'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-01 : 12:51:31

Works fine for me:

set @Position = 'position1'
SET @dtsExecCmd = ' /SET "\package.Variables[User::Position].Properties[Value]";"\"' + ISNULL(@Position,'') + '\""'
print @dtsExecCmd

set @Position = NULL
SET @dtsExecCmd = ' /SET "\package.Variables[User::Position].Properties[Value]";"\"' + ISNULL(@Position,'') + '\""'
print @dtsExecCmd

Please provide sample data and test that shows the issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pete_N
Posting Yak Master

181 Posts

Posted - 2014-08-01 : 14:44:03

Hi, I am calling it from a Sp

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_ClientUser_Dtsx_add]
@ID = 123123,
@Licence = N'986532',
@IsMaster = 0,
@IsClientMaster = 0,
@Forename = N'Harry',
@Surname = N'Potter',
@Position = Null,
@Telephone = '123456',
@Extension = '12',
@Emergency = '123654',
@Mobile = '258369',
@Fax = '741369',
@Email = 'me@me.co.uk',
@Password = 'testpassword',
@locked = 0,
@PERDAccounts = 0,
@PERImport = 0,
@PERManual =0,
@PERSubmit = 0,
@PERSecuritySubmit = 0,
@Active = 1,
@CreatedByID = 1537

SELECT 'Return Value' = @return_value

ALTER PROCEDURE [dbo].[sp_ClientUser_Dtsx_add]
@ID int,
@Licence varchar(6),
@IsMaster int,
@IsClientMaster int,
@Forename varchar(32),
@Surname varchar(32),
@Position varchar(256),
@Telephone varchar(32),
@Extension varchar(32),
@Emergency varchar(32),
@Mobile varchar(32),
@Fax varchar(32),
@Email varchar(100),
@Password varchar(32) ,
@locked int,
@PERDAccounts int,
@PERImport int,
@PERManual int,
@PERSubmit int,
@PERSecuritySubmit int,
@Active int,
@CreatedByID int

AS
BEGIN
SET NOCOUNT ON;
DECLARE @ssisPkgFilePath varchar(500)
SET @ssisPkgFilePath = 'UserProfiles_add.dtsx'
IF nullif(ltrim(rtrim(@ssisPkgFilePath)), '') is null
begin
--{
RAISERROR( 'Invalid ''ssisPkgFilePath'' value' /*Message*/, 16 /*Severity*/, 1 /*State (arbitary number)*/ )
--}
end
DECLARE @dtsExecCmd varchar(8000) -- varchar(8000) is the max length allowed for "varchar"

SET @dtsExecCmd = 'dtexec /F "' + ltrim(rtrim(@ssisPkgFilePath)) + '"'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::ID].Properties[Value]";"\"' + CONVERT(varchar(10), @ID) + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Licence].Properties[Value]";"\"' + @Licence + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::IsMaster].Properties[Value]";"\"' + CONVERT(varchar(10), @IsMaster) + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::IsClientMaster].Properties[Value]";"\"' + CONVERT(varchar(10), @IsClientMaster) + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Forename].Properties[Value]";"\"' + @Forename + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Surname].Properties[Value]";"\"' + @Surname + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Position].Properties[Value]";"\"' + ISNULL(@Position,'') + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Telephone].Properties[Value]";"\"' + ISNULL(@Telephone,'''') + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Extension].Properties[Value]";"\"' + ISNULL(@Extension,'''') + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Emergency].Properties[Value]";"\"' + ISNULL(@Emergency,'''') + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Mobile].Properties[Value]";"\"' + ISNULL(@Mobile ,'''') + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Fax].Properties[Value]";"\"' + ISNULL(@Fax ,'''') + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Email].Properties[Value]";"\"' + @Email + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Password].Properties[Value]";"\"' + @Password + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::locked].Properties[Value]";"\"' + CONVERT(varchar(10), @locked) + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::PERDAccounts].Properties[Value]";"\"' + CONVERT(varchar(10), @PERDAccounts) + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::PERImport].Properties[Value]";"\"' + CONVERT(varchar(10), @PERImport) + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::PERManual].Properties[Value]";"\"' + CONVERT(varchar(10), @PERManual) + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::PERSubmit].Properties[Value]";"\"' + CONVERT(varchar(10), @PERSubmit) + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::PERSecuritySubmit].Properties[Value]";"\"' + CONVERT(varchar(10), @PERSecuritySubmit) + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::Active].Properties[Value]";"\"' + CONVERT(varchar(10), @Active) + '\""'
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::CreatedByID].Properties[Value]";"\"' + CONVERT(varchar(10), @CreatedByID) + '\""'
DECLARE @result INT
DECLARE @output TABLE( [ssisOutput] varchar(max) )
INSERT INTO @output
--{
EXEC @result = master..xp_cmdshell @dtsExecCmd
--}

IF (@result is not null and @result<>0) OR EXISTS(select 1 from @output where ssisoutput like '%error%')
begin
SELECT * FROM @output
end
SET NOCOUNT OFF;
END

Error
Microsoft (R) SQL Server Execute Package Utility
Version 12.0.2000.8 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
NULL
The argument "\package.Variables[User::Position].Properties[Value];"" has mismatched quotes.
NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-01 : 14:58:52

I ran your code with a slight modification so that I can see the @dtsExecCmd string, and it produces this:

dtexec /F "UserProfiles_add.dtsx" /SET "\package.Variables[User::ID].Properties[Value]";"\"123123\"" /SET "\package.Variables[User::Licence].Properties[Value]";"\"986532\"" /SET "\package.Variables[User::IsMaster].Properties[Value]";"\"0\"" /SET "\package.Variables[User::IsClientMaster].Properties[Value]";"\"0\"" /SET "\package.Variables[User::Forename].Properties[Value]";"\"Harry\"" /SET "\package.Variables[User::Surname].Properties[Value]";"\"Potter\"" /SET "\package.Variables[User::Position].Properties[Value]";"\"\"" /SET "\package.Variables[User::Telephone].Properties[Value]";"\"123456\"" /SET "\package.Variables[User::Extension].Properties[Value]";"\"12\"" /SET "\package.Variables[User::Emergency].Properties[Value]";"\"123654\"" /SET "\package.Variables[User::Mobile].Properties[Value]";"\"258369\"" /SET "\package.Variables[User::Fax].Properties[Value]";"\"741369\"" /SET "\package.Variables[User::Email].Properties[Value]";"\"me@me.co.uk\"" /SET "\package.Variables[User::Password].Properties[Value]";"\"testpassword\"" /SET "\package.Variables[User::locked].Properties[Value]";"\"0\"" /SET "\package.Variables[User::PERDAccounts].Properties[Value]";"\"0\"" /SET "\package.Variables[User::PERImport].Properties[Value]";"\"0\"" /SET "\package.Variables[User::PERManual].Properties[Value]";"\"0\"" /SET "\package.Variables[User::PERSubmit].Properties[Value]";"\"0\"" /SET "\package.Variables[User::PERSecuritySubmit].Properties[Value]";"\"0\"" /SET "\package.Variables[User::Active].Properties[Value]";"\"1\"" /SET "\package.Variables[User::CreatedByID].Properties[Value]";"\"1537\""

You'll need to help show us where the issue is in that command so that we know what part of the code to look at.

Here is the slight modification. I changed your stored procedure so that the last 5 lines of code are this:
SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::CreatedByID].Properties[Value]";"\"' + CONVERT(varchar(10), @CreatedByID) + '\""'

print @dtsExecCmd

END

The key part is the print. You need to get that output correct first.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources