Trigger issues

I have an issue with the below code.  The problem seems to be that no matter which variable I use @test or @cerpath, if I pass it to the procedure they both house an extra value.  That is the reason I created the test table, and inserted the value?  I don't know how or why this is occurring...below is the code and the here is the value that each variable brings back..

RESULTS FROM SELECTING @certpath and @test
copy \\eincutil1\fsTaskMan\Report\OutputFiles\spoye\ENS_CertReport_CO_txt_einc_1583031.pdf   \\eincfile\public\CertOutput\10\LAIRDPLASTICS\514110.pdf
    514110


ALTER TRIGGER [dbo].[Ens_CertInsert] ON [dbo].[BGTaskHistory]
for INSERT
AS
begin

   IF @@ROWCOUNT = 0 RETURN
   
   SET NOCOUNT ON



declare @conum            nvarchar(10),
            @filename         nvarchar(50),
        @username         nvarchar(30),
        @tasknumber       decimal(18,0),
        @taskname         nvarchar(50),
        @custnum          nvarchar(7),
        @custseq          int,
        @custname         nvarchar(40),
        @certpath         nvarchar(1000),
        @test                    nvarchar(1000),
   
---------------------------------------------set values------------------------------------------
       Select @conum      =  substring(i.taskparms1,7,10),
              @filename   =  i.taskexecutable,
              @username   =  i.requestinguser,
              @tasknumber =  i.tasknumber,
              @taskname   =  i.taskname
     from inserted i
           inner join bgtaskhistory b on b.rowpointer =  i.rowpointer

     select @custnum = cust_num,
            @custseq = cust_seq
     from co where co_num = @conum

      set @custname   = (      select replace(replace(name,' ',''),',','')
                                    from custaddr
                                    where cust_num = @custnum
                                    and cust_seq = @custseq)
----------------------------------------------------------------------------------------------------------------

if (select @taskname) = 'CertPrintingCOTextText'
  begin

     set @conum   = ltrim(@conum)
     set @custnum = ltrim(@custnum)
 -----filename out of system------------------
       set @filename =  @filename + '_einc_' + cast(@tasknumber as nvarchar(10)) + '.pdf '

       
          @copypath         nvarchar(500),
        @copy             nvarchar(1000),
        @certdestination  nvarchar(200),
       
        @output           nvarchar(500),
            
        @directory        nvarchar(1000)





------complete path to the file from and to--------------------
      set @certpath = 'copy \\eincutil1\fsTaskMan\Report\OutputFiles\' + @username + '\' + @filename + '  \\eincfile\public\CertOutput\' + cast(datepart(mm,getdate()) as nvarchar(2)) +'_'+ cast(datepart(yy,getdate()) as nvarchar(4))   +  '\' +  @conum  + '.pdf'

------THE DESTINATION PATH ONLY------------------------------------------
    set @certdestination = '\\eincfile\public\CertOutput\' + @custnum + '\' + @custname + '\' +  cast(datepart(mm,getdate()) as nvarchar(2)) +'_'+ cast(datepart(yy,getdate()) as nvarchar(4))--+  @conum  + '.pdf'


----------------------------Test to see the value in @test-------------------
    set @test = (select 'copy \\eincutil1\fsTaskMan\Report\OutputFiles\' + @username + '\' + @filename +  '  '  + '\\eincfile\public\CertOutput\' + cast(datepart(mm,getdate()) as nvarchar(2)) +'_'+ cast(datepart(yy,getdate()) as nvarchar(4))   +  '\' +  @conum  + '.pdf')
----------------------------------------------------------------------------

------------------Path to file saved in table for end-users--------------------------
    set @output   = '\\eincfile\public\CertOutput\' + @certdestination   +  '\' +  @conum  + '.pdf'



-----table to house directory to see if Directory already exists---------

create table #spoye(directory varchar(500))
insert into #spoye
    EXEC master.dbo.xp_subdirs '\\eincfile\public\CertOutput\'
 
if not exists (select directory from #spoye where directory = @certdestination)
begin
            -------create the directory path----------
            SET @directory = ' mkdir ' + @certdestination
            exec master..xp_cmdshell  @directory, no_output
            -------------------------------------------
end

-----------------------------------------------------

----test table to locate value in the variable @certpath
insert into  test(path)
select  @test

---place top 1 since the @certpath seems to hold the path and then another value of the Customer order number?????
select top 1 path into #tmp
from test


---attempt to pull the path only and set it the @test variable

set @test = (Select top 1 path from #tmp)

-----------call to procedure that will copy the file to the new location and place
----------needed values into table...

                      exec  Ens_Certcopy
                             @conum,
                                           @custnum,
                             @custseq,
                                         @custname,
                             @output,
                             @test------------------used to copy the file...which seems to be incorrect??



  end

end
stevendeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
I can't see the definition of "test" table so don't know what's going on there.

Be sure you don't have any NULLs in any value being concatenated, as that would set the entire result to NULL.

Btw, that trigger's a mess, it needs rewritten to process possible multiple rows being INSERTed at once, not just one row.
stevendeveloperAuthor Commented:
The test table houses a varchar field, used to see what the value is in the variables is all...that is the reason it is called test..as for multiple rows that is not possible in the environment it is in, so the mess will stay a mess, I'm not rewriting it I am a not an expert.
Scott PletcherSenior DBACommented:
So test column is "varchar" and not "nvarchar"?  You're inserting nvarchar, so you might see odd translation issues for certain/non-standard characters.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

stevendeveloperAuthor Commented:
I think the null value is the issue, i need to track it down
stevendeveloperAuthor Commented:
looks like the issue has been addressed, the NULL value comment made me look at the white space(s) also, once  I removed the white space the directories are created and the files copied and renamed.  Thanks

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stevendeveloperAuthor Commented:
The answer from the expert alerted me to the fact that some of the variable had whitespace.  The whitespace caused the file path issues and therefore the files didn't copy to the directory.  I believe I accepted the experts comment also, since I wouldn't have thought to look for white spaces.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.