Using variables in built-in stored procedures

[nancyd1111]

I'm trying to pass in a varible to a stored proc and I'm not having success.

If I don't use a variable, a trace file is created. If I use a variable, the trace file is not created. Any idea why?

This works when I hard code the drive and filename:

exec @rs = sp_trace_create @traceid output, 0, N'\\AUTOPILOT\cdrive\Trace\Trace'

This does NOT work when I pass in a variable as a parameter to the stored proc:
@PlanName varchar(255)
declare @filename varchar(255)

set @filename = '\\AUTOPILOT\cdrive\Trace\Trace_' + @PlanName

exec @rs = sp_trace_create @traceid output, 0, N@filename

PlanName is passed in when calling the stored procedure and filename is comprised of text + PlanName.

The exec state to create the file, returns a value of "12" File Not Created.

Thanks in advance!

 

[NoCoolHandle]

Could
output, 0, N@filename

really be
output, 0, N'@filename

and wouldn't it be better if

@PlanName varchar(255)
declare @filename [red]N[/red]varchar(255)

set @filename = '\\AUTOPILOT\cdrive\Trace\Trace_' + @PlanName

exec @rs = sp_trace_create @traceid output, 0, @filename

 

[amrita418]

The filename variable should of type nvarchar.

Also double check that @PlanName is never null.

Something like this:

declare 
@rs 	  varchar(200),
@traceid  int,
@PlanName varchar(255),
@filename nvarchar(255)

set @PlanName = 'Test'
set @filename = '\\AUTOPILOT\cdrive\Trace\Trace_' + @PlanName
--print @filename

exec @rs =  sp_trace_create @traceid output, 0, @filename

Regards,
AA

 

[NoCoolHandle]

This worked for me...
declare @PlanName varchar(255)
declare @filename Nvarchar(255)
declare @rs int
set @planname = 'text.txt'
set @filename = 'C:\Trace_' + @PlanName

exec sp_trace_create @rs output, 0,N@filename
select @rs

Return code = 0

 

[nancyd1111]

I assume your suggestion of:

output, 0, N'@filename

you really mean to end the quote after filename as:

'@filename' - otherwise, it won't compile.

I also tried your suggestion of Nvarchar(255), but to no avail.

Have you ever used sp_Trace_create, passing in variables?

P.S. How do I color code my text, as you did in your reply of Nvarchar?

 

[gmmastros]

Nancy, Just above the Submit button is a 'Process TGML' link. When you click it, you will see a list of TGML tags.

For red text...

[red][ignore][red]This is red text[/red][/ignore][/red]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

Here is the code I use to create a trace file.

set nocount on 
declare @traceid int, @stoptime datetime, @maxfilesize bigint, @filename nvarchar(245) 
set @stoptime = dateadd(mi, 15, getdate()) 
set @maxfilesize = 50 
set @traceid = 1 
set @filename = N'c:\path\to\file' 
exec sp_trace_create @traceid=@traceid OUTPUT, 
   @options=2, 
   @tracefile=@filename, 
   @maxfilesize=@maxfilesize, 
   @stoptime=@stoptime

The value of 0 for the options variable isn't valid.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[nancyd1111]

To NoCoolHandle,

Your supplied code doesn't really work. Although you get a Return Code = 0, this return code applies to TraceID, but doesn't apply to the success of creating the [red] FILE [/RED].

If you rerun your code and ask for a return value of file creation, you will get Return Code = 12 (File Not Created).

exec [RED]@returnValue[/RED] = sp_trace_create @rs output, 0,N@filename

select @returnValue



Thanks for trying. I'm moving on to mrdenny's suggestion.

 

[nancyd1111]

To mrdenny,

Thanks! You solved the mystery!

The placement of N is what made all the difference:

This will work:
set @filename = [GREEN]N[/GREEN]'c:\path\to\file'
exec @rv = sp_trace_create @rs output, 0,@filename

This will [red]NOT [/red]work:
set @filename = 'c:\path\to\file'
exec @rv = sp_trace_create @rs output, 0,[RED]N[/RED]@filename


Thanks Again!

 

[mrdenny]

not a problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief