May 16, 2012 at 6:41 am
Hi All,
I am not a developer but my boss assign some work please help me on below query...
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'
Error :- Must declare the scalar variable "@columnNames".
May 16, 2012 at 6:45 am
you need to declare the variables @columnNames and @columnConvert, take a look at the DECLARE syntax and then define the correct data type which is needed for the variables.
May 16, 2012 at 6:54 am
Hi Green,
Could you please update the query. I am not understand this one...
Thanks in advance...
May 16, 2012 at 6:56 am
This URL will help you understand DELCARE http://msdn.microsoft.com/en-us/library/ms188927.aspx
You will need to do something like this
DECLARE @column1 somedatatype, @column2 somedatetype, @column3 somedatatype
May 16, 2012 at 7:27 am
Hi Anthony,
Thanks for update. Actually below query i need to execute & output should come in Excel format, please help me where need to change. If possible please do the changes & update me.
I am a part of DBA ZERO in query part, please help on below query..
**********************
use master
go
if object_id('spExportData') is not null
drop proc spExportData
go
create proc spExportData
(
@dbName varchar(100) = 'master',
@sql varchar(5000) = '',
@fullFileName varchar(100) = ''
)
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
select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -E -CRAW'
-- execute BCP
Exec master..xp_cmdshell @sql
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
select 1 as ReturnValue -- success
go
declare @sql varchar(6800), @dbName varchar(100), @fullFileName varchar(100)
select @dbName = 'database', @sql = 'select * tablename', @fullFileName = 'C:\test.xls'
exec master..spExportData @dbName, @sql, @fullFileName
May 16, 2012 at 7:33 am
What is the problem you are having with this procedure?
May 16, 2012 at 8:20 am
saidapurs (5/16/2012)
If possible please do the changes & update me.
Just an FYI: This is a bad way to phrase a request for help. It raises the hackles of forum regulars because most people who say that are looking for homework solutions or too lazy to do the work themselves. If you keep using this phrase, people will start quoting you their hourly rates and advising you to find a contractor in your area to come into your office.
That being said, a first glance at your stored procedure doesn't indicate that it would have the same error message as what's in the first post. So what error message are you getting?
Also, you need to post DDL (CREATE TABLE statements) and sample data if you want us to test your proc or help you locate a problem.
EDIT: I just realized your proc is using a system table, so DDL is not needed, but remember for future posting that it will be needed for user tables.
May 16, 2012 at 8:26 am
In addition to what Brandie said... learning to use the code and /code IFCode shortcuts to separater your code from your post is a big help too.
May 16, 2012 at 8:34 am
In addition to what everyone else has said, it would also help if you formatted your code. There are free tools on the web that will help you with this, one is http://poorsql.com/. Reggate has one as well here, http://format-sql.com/.
May 16, 2012 at 9:10 am
one issue with this procedure is it is using global temp tables (##TempExportData) if 2 people are running the report at the same time they will contaminate each others data. using a single # for the temp table eliminates this issue. i also think you can eliminate the executing of the dynamic select statement and build it into your BCP call. also an explination of what the SP does along with a use case will help us diagnose every thing further.
but from your error and posted query it looks like you are trying to run just the query posted for debugging purposes and as such have not declared the variables in the scope of the batch.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 16, 2012 at 9:15 am
Well if we're going to nitpick.... let's get the thing out of Master while we're at it.
May 16, 2012 at 10:10 am
Hi Brandie,
I apriciate your responce & i agree with you but issue is i am not yet wrote any SP in my life. so while adding this once i requested to give me solution.....
Now it is critical for me so i requested....
Thank you
May 16, 2012 at 10:14 am
saidapurs (5/16/2012)
Hi Brandie,I apriciate your responce & i agree with you but issue is i am not yet wrote any SP in my life. so while adding this once i requested to give me solution.....
Now it is critical for me so i requested....
Thank you
Here is the problem. We are volunteers on this site and we provide assistance when we can. If you have something critical that must be done, this really isn't the place to come. If you have critical work, you should be working with your coworkers and/or supervisor.
May 16, 2012 at 10:37 am
saidapurs (5/16/2012)
I apriciate your responce & i agree with you but issue is i am not yet wrote any SP in my life. so while adding this once i requested to give me solution.....
I know you didn't write the stored procedure. You made that clear, which is why we're discussing this and not ignoring you. But there are 3 issues.
1) We won't ever give you a complete solution. We will give you suggestions and point you to articles that will help you learn and grow your skills. But complete solutions are beyond us because we do not know your environment and limitations.
Reason: You may not be a DBA, but if the boss is giving you this proc to fix, he will probably be giving you more. Us doing your work for you, critical or not, will not protect you from repeated emergency fixes, which will lead to you asking us to do more of your work.
2) You haven't told us what the problem is with the full stored procedure. There is no error message posted for it, and no details on why it is not working, or what you're supposed to do with it other than put the data into an Excel sheet.
Just a note, stored procedures don't output to Excel spreadsheets. You're better off creating an SSIS package or learning how to use the bcp utility. The details for both are in SQL Server Books Online.
3) What Lynn said. This is not the place to go for critical problems. We don't respond that fast.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply