Scaler Variable Error !! :(

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Scaler Variable Error !! :(

Scaler Variable Error !! :(

Rate Topic

Display Mode

Topic Options

Author

Message

nitinuniyal

Posted Monday, October 22, 2012 6:21 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:16 AM
Points: 22, Visits: 81

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

Sean Lange

Posted Monday, October 22, 2012 9:18 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 8,957, Visits: 8,523

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

Post #1375504

nitinuniyal

Posted Monday, October 22, 2012 11:25 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:16 AM
Points: 22, Visits: 81

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

Sean Lange

Posted Tuesday, October 23, 2012 7:30 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 8,957, Visits: 8,523

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

Post #1376038

nitinuniyal

Posted Tuesday, October 23, 2012 7:45 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:16 AM
Points: 22, Visits: 81

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

Sean Lange

Posted Tuesday, October 23, 2012 7:59 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 8,957, Visits: 8,523

nitinuniyal (10/23/2012)

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

Post #1376066

« Prev Topic | Next Topic »

Permissions