-1

I want to add a timestamp to the outputfile from within a .sql file. Fullpath variable appears to be created correctly but :out throws an error. Run anywhere code is below. Any idea why the error happens, and how to fix it?

 -- control output of test results
Declare @filepath Varchar(200)
set @filepath='C:\Users\XXXXXX\Desktop\Automation Rewrite\Enrollment\Test Results\'
declare @filename varchar(100)

set @filename = 'enrollment_OH_MD_1_' + CONVERT(varchar(12),getdate()) + '.txt'
select @filename
declare @fullpath Varchar(200)
set @fullpath = '"'+ltrim(@filepath+@filename)+'"'
select @fullpath
:out @fullpath

error received: Unable to redirect output to @fullpath. Access to the path 'C:\WINDOWS\system32@fullpath' is denied.

Mike
  • 85
  • 8
  • Whats the error? And how are you running that SQL? – Dale K Jan 21 '19 at 20:35
  • why the downvote? – Mike Jan 22 '19 at 12:39
  • I didn't down vote you, but the error you are getting does not match your script. Why are you writing to the windows system directory? The account that is running SQLCMD does not have access to write there, as it says. – JMabee Jan 22 '19 at 12:48
  • It seems to be related to sqlcmd because if I cut/paste the result of fullpath into a separate :out command, it works. Not sure why it is trying to write to c:\windows when filepath clearly states c:\users... – Mike Jan 22 '19 at 13:11
  • I think you will find the answer in here: https://dba.stackexchange.com/questions/211804/sqlcmd-out-not-working-as-expected After goofing around with it for a while it appears that the SQLCMD variables and TSQL variables are defined and run at different times. Look to the bottom of that link I posted. – JMabee Jan 22 '19 at 13:53
  • The downvote is because there is not enough information in the question to know how to reproduce your issue. I for one have no idea how you are actually executing that SQL... – Dale K Jan 23 '19 at 00:29
  • JM's response points to the answer I needed. Very helpful. Thank you. – Mike Jan 23 '19 at 12:24

1 Answers1

1

I've never seen :out before. out as keyword often uses in the stored procedures Maybe you meant print

 -- control output of test results
Declare @filepath Varchar(200)
set @filepath='C:\Users\XXXXXX\Desktop\Automation Rewrite\Enrollment\Test Results\'
declare @filename varchar(100)

set @filename = 'enrollment_OH_MD_1_' + CONVERT(varchar(12),getdate()) + '.txt'
select @filename
declare @fullpath Varchar(200)
set @fullpath = '"'+ltrim(@filepath+@filename)+'"'
select @fullpath
print @fullpath

Result

sticky bit
  • 36,626
  • 12
  • 31
  • 42
SouXin
  • 1,565
  • 11
  • 17
  • This is not what i was looking for. :Out allows you to direct the output from sql queries. We use it to log results from sql query test cases. We use this in SSMS. See this post for verification: https://stackoverflow.com/questions/6354130/is-there-a-select-into-outfile-equivalent-in-sql-server-management-studio – Mike Jan 22 '19 at 12:18