October 22, 2012 at 6:21 am
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%
October 22, 2012 at 9:18 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 11:25 pm
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
October 23, 2012 at 7:30 am
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/[/url]
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 23, 2012 at 7:45 am
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
October 23, 2012 at 7:59 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply