| Author |
Topic |
|
Johnph
Posting Yak Master
103 Posts |
 Posted - 2012-09-18 : 17:00:47
|
Hello,I am executing the sp_send_dbmail and want the attachment line to be an output of a query.Here is what I've tried:Declare @Test Varchar(500)SELECT @Test = [COLUMN] FROM TABLE --this will always output 1 result-- This is also outputed like this in the column: '\C:\USER\test.txt' EXEC msdb.dbo.sp_send_dbmail @profile_name='TEST',@recipients='FAKE@SQL.com; FAKE2@SQL.com',@subject = 'TEST',@body = 'TEST',@file_attachments = @Test I am getting this message:Msg 22051, Level 16, State 1, Line 0Attachment file '\C:\USER\test.txt' I typed in @file_attachments = '\C:\USER\test.txt' and it works fine.Any ideas? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2012-09-18 : 17:15:26
|
| Yeah it is a server name (I didn't type the actual path out) '\\SERVER\USER\test.txt', removing the begining slash didn't do anything.When I do this, it works:@file_attachments = '\\SERVER\USER\test.txt'When I do this, it gives me the error:@file_attachments = @TestI even tried doing:SELECT @Testand the output is '\\SERVER\USER\test.txt'---------------------------------------------------------------Putting @test on the subject line also works and outputs '\\SERVER\USER\test.txt' |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2012-09-18 : 17:54:14
|
| Let me give that a shot in about 15 minutes, i am riding a train home. You are correct that there is a lot of trailing spaces after the '. Please dont abandon me yet, ill respond as soon as i can. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-18 : 18:19:46
|
quote:
I just might not be back until tomorrow morning.
Slacker.  |
|
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2012-09-18 : 18:52:55
|
| Ok Tara,I declared @test as a string and it worked perfectly. There are many trailing spacings after the ' what is the best way to get rid of them. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2012-09-18 : 19:06:23
|
Ok Tara,Declare @Test Varchar(500)SELECT @Test = RTRIM(COLUMN) FROM TABLE EXEC msdb.dbo.sp_send_dbmail @profile_name='TEST',@recipients='FAKE@SQL.com; FAKE2@SQL.com',@subject = 'TEST',@body = 'TEST',@file_attachments = @TestSame errorI also did:select cast(RTRIM([COLUMN])as varbinary) from TABLEselect cast([COLUMN]as varbinary) from TABLE both outputed the same resultsI also tried:Declare @Test Varchar(500)SELECT @Test = RTRIM([COLUMN]) from TABLESELECT @TEST It outputed the correct path without any trailing spaces, my head hurts. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2012-09-18 : 19:39:47
|
| [code]Declare @Test Varchar(500)SELECT @Test = [COLUMN] FROM TABLE EXEC msdb.dbo.sp_send_dbmail @profile_name='TEST',@recipients='FAKE@SQL.com; FAKE2@SQL.com',@subject = 'TEST',@body = 'TEST',@file_attachments = @Test[/code]this is the exact code that I am running when I do [code]SELECT @TEST[/code]It outputs '\\SERVER\TEST\test.txt' With a lot of trailing spaces. When I RTRIM it, it removes the spaces but still gives me the error |
|
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2012-09-18 : 20:33:42
|
| Hi Tara, I hope you get a chance to view this message. I figured it out...when you use a variable in the @file_attachments it automatically sets your ' ' for you.My query outputed '\\SERVER\TEST\TEST.txt'and when I typed in @file_attachments = @test It read it as ''\\SERVER\TEST\TEST.txt''However, you were able to help me trim and figure out what was wrong, thank you so much! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Declaring Query Results as String for SP