Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Scaler Variable Error !! :( Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 6:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 18, 2013 12:57 AM
Points: 25, Visits: 92
Hi Team,

I an using the sp_exportdata_n to exporting the data into excel but when i am defining my @sql i am getting the below scaler variable errors.

Could you please let me know how we can we rectify me query so that @SQL can be executed while running the below sp.

Msg 102, Level 15, State 1, Procedure spExportData_n, Line 11
Incorrect syntax near 'M_PILOT'.
Msg 137, Level 15, State 2, Procedure spExportData_n, Line 41
Must declare the scalar variable "@sql".
Msg 137, Level 15, State 2, Procedure spExportData_n, Line 55
Must declare the scalar variable "@sql".
Msg 137, Level 15, State 1, Procedure spExportData_n, Line 79
Must declare the scalar variable "@sql".
Msg 137, Level 15, State 2, Procedure spExportData_n, Line 81
Must declare the scalar variable "@sql".



USE [realtime]
GO
/****** Object: StoredProcedure [dbo].[spExportData_n] Script Date: 10/22/2012 15:00:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[spExportData_n]
(
@dbName varchar(100) = 'Realtime',

@sql varchar(8000) = 'select q2.last_Offline_msg,q1.*
from
(
select id,miscellaneous,last_tran_msg_time,term_mode,link_type,ATM_IP,
Region,sscm_hostname,location,
case
when c.loadset_group like 'M_PILOT' then 'R2'
when c.loadset_group like 'EMV Onus Excl Env Deposit' then 'R1\Normal'
when c.loadset_group like 'NCR_BNA_PILOT' then 'BNA R2'
end as "Image_type",
case
when c.opt_timers_profile like 'NDC+' then 'NCR'
when c.opt_timers_profile like 'WINCOR' then 'WINCORE'
end as "M_TYPE"
from term a (nolock)
inner join
SHB_Atm_details b (nolock) on
a.id = b.M_ID --
inner join M_Config c on
a.id = c.M_ID
where term_mode != 2 and term_active = 1
and last_tran_msg_time is not null
--and link_type like '%VSAT%'
) q1 Left outer join
(
select a.id , max(d.last_sent) as last_Offline_msg
from support_event_log d (nolock) inner join term a
on a.short_name = d.entity
and event_id = '109'
group by a.id
) q2
on q1.id=q2.id
order by term_mode desc',
@fullFileName varchar(100) = 'C:\CallCenter_Audit_Report.xls'
)
as
if @sql = '' or @fullFileName = ''
begin
select 0 as ReturnValue -- failure
return
end
-- if DB isn't passed in set it to master
select @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
drop table ##TempExportData
if object_id('##TempExportData2') is not null
drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)

select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))

exec(@dbName + @tempSQL)
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),'
+ column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
when data_type in ('numeric', 'decimal') then ',128'
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
when data_type in ('datetime', 'smalldatetime') then ',120'
else ''
end + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData'

-- execute select query to insert data and column names into new temp table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'

exec (@sql)

-- build full BCP query
DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout'
SET @bcpCommand = @bcpCommand + ' C:\Files\Reports\Call_Center_Audit_Reports\CallCenter_Audit_Report_%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%.xls -c -w -T -U sa -P sa","-CRAW'
EXEC Realtime..xp_cmdshell @bcpCommand
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
set @columnNames =' '
set @columnConvert =' '
set @tempSQL =' '
select 1 as ReturnValue

--%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%
Post #1375375
Posted Monday, October 22, 2012 9:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
With the total lack of information there is little anybody can do to help here. However, your dynamic sql is full of errors. You are not escaping your single quotes at all.

when c.loadset_group like 'M_PILOT' then 'R2'

When that is inside a string you need to escape your quotes.

when c.loadset_group like ''M_PILOT'' then ''R2''

Then I would ask why are you using LIKE instead of = for this? Why NOLOCK hints all over the place? It is impossible to tell if all that code is part of your proc or not because it has no begin - end. From trying to read through this I don't really see a need for dynamic sql at all.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1375504
Posted Monday, October 22, 2012 11:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 18, 2013 12:57 AM
Points: 25, Visits: 92
Thanks Sean,

I got my answer now mt query is running and export is also working fine.
I was just missing the double quotes in inner query.

Regards
NU
Post #1375835
Posted Tuesday, October 23, 2012 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
nitinuniyal (10/22/2012)
Thanks Sean,

I got my answer now mt query is running and export is also working fine.
I was just missing the double quotes in inner query.

Regards
NU


Since you didn't answer about NOLOCK I will suggest you read this article. http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1376038
Posted Tuesday, October 23, 2012 7:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 18, 2013 12:57 AM
Points: 25, Visits: 92
Hi Sean,

We are using nolock only in select statement as recommended by our Application vendor, Our OLTP is too high and any lock even for milisecond can cause major issues.

Though the artical given is good and i am going to test the same with our set of quires.

Thanks for your help so far...

Regards
NU
Post #1376050
Posted Tuesday, October 23, 2012 7:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
nitinuniyal (10/23/2012)
Hi Sean,

We are using nolock only in select statement as recommended by our Application vendor, Our OLTP is too high and any lock even for milisecond can cause major issues.

Though the artical given is good and i am going to test the same with our set of quires.

Thanks for your help so far...

Regards
NU


I know what you mean about you about wait times. It is recommended to instead use isolation. That way you don't have to wait for locks to be released and you don't run the risks that nolock presents. The real challenge is that no amount of testing can always ferret out the issue. When they do show up they are incredibly difficult to replicate and debug. Knowing about the possibility can help but avoiding it is better.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1376066
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse