SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scaler Variable Error !! :(


Scaler Variable Error !! :(

Author
Message
nitinuniyal
nitinuniyal
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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%
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25845 Visits: 17509
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 Modens 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)
nitinuniyal
nitinuniyal
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25845 Visits: 17509
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 Modens 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)
nitinuniyal
nitinuniyal
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25845 Visits: 17509
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search