July 16, 2015 at 9:20 am
I have the following code below. When I run it, I am getting the following error message:
Msg 105, Level 15, State 1, Line 92
Unclosed quotation mark after the character string '##OutputTable
Can anyone review this code to see if in fact it should work. I am trying the get the output data into a .csv and place it on a local drive.
Thanks.
-- Declare the variables
DECLARE @CMD VARCHAR(4000),
@DelCMD VARCHAR(4000),
@HEADERCMD VARCHAR(4000),
@Combine VARCHAR(4000),
@Path VARCHAR(4000),
@COLUMNS VARCHAR(4000)
-- Set values as appropriate
SET @COLUMNS = ''
--SET @Path = '\\servername\share\outputpath'
SET @Path = 'C:\Users\martynenkoj\Desktop\screenshots'
-- Set up the external commands and queries we'll use through xp_cmdshell
-- Note that they won't execute until we populate the temp tables they refer to
SET @CMD = 'bcp "select * from ##OutputTable" queryout "' + @Path + '\Temp_RawData.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
SET @HEADERCMD = 'bcp "SELECT * from ##cols" queryout "' + @Path + '\Temp_Headers.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
SET @Combine = 'copy "' + @Path + '\Temp_Headers.csv" + "' + @Path + '\Temp_RawData.csv" "' + @Path + '\MyCombinedFile.csv"'
SET @DelCMD = 'del "' + @Path + '\Temp_*.csv"'
-- Create and populate our temp table with the query results
select
cust_code,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then
(SHIPPED * PRICE)
else 0 end) as TenTenSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C') and substring (user_def_fld1, 1, 4) in ('1010')
THEN
SHIPPED * (PRICE - COST)
else 0 end) as TenTenGP,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C')
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C')
then
(SHIPPED * PRICE)
else 0 end) as CYTDSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C')
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C')
THEN
SHIPPED * (PRICE - COST)
else 0 end) as CYTDGP,
SUM (case when year (date_entered) = YEAR(GETDATE()) -1 and (type = 'C') AND
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1))))
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) -1 and (type <> 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1) )))
then
(SHIPPED * PRICE)
else 0 end) as PYTDSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) -1 and (type = 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1))))
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) -1 and (type <> 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1 ))))
THEN
SHIPPED * (PRICE - COST)
else 0 end) as PYTDGP
INTO ##OutputTable
from orders_ALL ords
JOIN ORD_LIST AS OL ON OL.ORDER_NO = ORDS.ORDER_NO AND EXT = ORDER_EXT
join inv_master as im on im.part_no = ol.part_no
--JOIN ARMASTER_ALL AS AR ON CUST_CODE = CUSTOMER_CODE AND ADDRESS_TYPE = 0
where ORDS.status not in ('V') AND
YEAR(date_entered) > YEAR(GETDATE()) - 2
and im.status = 'P' and cust_code <> '80990000'
GROUP BY cust_code
ORDER BY cust_code
-- Generate a list of columns
SELECT @COLUMNS = @COLUMNS + c.name + ','
from tempdb..syscolumns c
join tempdb..sysobjects t
on c.id = t.id
where t.name like '##OutputTable
order by colid
SELECT @COLUMNS as Cols INTO ##Cols
-- Run the two export queries - first for the header, then for the data
exec xp_cmdshell @HEADERCMD
exec xp_cmdshell @CMD
-- Combine the two files into a single file
exec xp_cmdshell @Combine
-- Clean up the two temp files created
exec xp_cmdshell @DelCMD
-- Clean up temp tables
drop table ##cols
drop table ##OutputTable
July 16, 2015 at 9:38 am
The error is telling you everything you need to know.
Unclosed quotation mark after the character string '##OutputTable
Look for an unusual large string literal and define where you should add a quotation mark to define the end of the string.
July 16, 2015 at 9:42 am
SELECT @COLUMNS = @COLUMNS + c.name + ','
from tempdb..syscolumns c
join tempdb..sysobjects t
on c.id = t.id
where t.name like '##OutputTable
order by colid
Unclosed quotation mark after the character string '##OutputTable
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2015 at 9:48 am
I did notice that after I posted. However when closing that, I got the following error message even though these are temp tables.
Msg 2714, Level 16, State 6, Line 22
There is already an object named '##OutputTable' in the database.
July 16, 2015 at 9:49 am
They're global temp tables, they aren't dropped until the connection using them closes and no one else is using them.
Even if they were normal temp tables, they wouldn't get dropped until the connection closes, since that's not a stored proc.
Just drop them and then run your query. The drop at the end didn't run last time because of the error you got.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2015 at 9:53 am
That's because the code stopped and never dropped the temp table.
Drop it before running the code again. Even better would be to have something like this before creating the table:
IF OBJECT_ID('tempdb..##OutputTable') IS NOT NULL
drop table ##OutputTable
Be careful when using global temp tables as you might encounter problems with concurrency.
July 16, 2015 at 10:05 am
Luis is correct.
But also quit using sysobjects and syscolumn views, as they are very obsolete and slow (and possibly even buggy).
Try this code instead:
SELECT @COLUMNS = @COLUMNS + c.name + ','
FROM tempdb.sys.columns c
WHERE
c.object_id = OBJECT_ID('tempdb..##OutputTable')
ORDER BY c.column_id
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 16, 2015 at 10:08 am
So here is my updated code:
-- Declare the variables
DECLARE @CMD VARCHAR(4000),
@DelCMD VARCHAR(4000),
@HEADERCMD VARCHAR(4000),
@Combine VARCHAR(4000),
@Path VARCHAR(4000),
@COLUMNS VARCHAR(4000)
-- Set values as appropriate
SET @COLUMNS = ''
--SET @Path = '\\servername\share\outputpath'
SET @Path = 'C:\Users\martynenkoj\Desktop\screenshots'
-- Set up the external commands and queries we'll use through xp_cmdshell
-- Note that they won't execute until we populate the temp tables they refer to
SET @CMD = 'bcp "select * from ##OutputTable1" queryout "' + @Path + '\Temp_RawData.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
SET @HEADERCMD = 'bcp "SELECT * from ##cols" queryout "' + @Path + '\Temp_Headers.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
SET @Combine = 'copy "' + @Path + '\Temp_Headers.csv" + "' + @Path + '\Temp_RawData.csv" "' + @Path + '\MyCombinedFile.csv"'
SET @DelCMD = 'del "' + @Path + '\Temp_*.csv"'
-- Create and populate our temp table with the query results
select
cust_code,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then (price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then (SHIPPED * PRICE)
else 0 end) as TenTenSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then ((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C') and substring (user_def_fld1, 1, 4) in ('1010')
THEN SHIPPED * (PRICE - COST)
else 0 end) as TenTenGP,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C')
then (price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C')
then (SHIPPED * PRICE)
else 0 end) as CYTDSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C')
then ((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C')
THEN
SHIPPED * (PRICE - COST)
else 0 end) as CYTDGP,
SUM (case when year (date_entered) = YEAR(GETDATE()) -1 and (type = 'C') AND
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1))))
then (price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) -1 and (type <> 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1) )))
then (SHIPPED * PRICE)
else 0 end) as PYTDSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) -1 and (type = 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1))))
then ((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) -1 and (type <> 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1 ))))
THEN SHIPPED * (PRICE - COST)
else 0 end) as PYTDGP
INTO ##OutputTable1
from orders_ALL ords
JOIN ORD_LIST AS OL ON OL.ORDER_NO = ORDS.ORDER_NO AND EXT = ORDER_EXT
join inv_master as im on im.part_no = ol.part_no
--JOIN ARMASTER_ALL AS AR ON CUST_CODE = CUSTOMER_CODE AND ADDRESS_TYPE = 0
where ORDS.status not in ('V') AND
YEAR(date_entered) > YEAR(GETDATE()) - 2
and im.status = 'P' and cust_code <> '80990000'
GROUP BY cust_code
ORDER BY cust_code
-- Generate a list of columns
SELECT @COLUMNS = @COLUMNS + c.name + ','
from tempdb..syscolumns c
join tempdb..sysobjects t
on c.id = t.id
where t.name like '##OutputTable1'
order by colid
SELECT @COLUMNS as Cols INTO ##Cols
-- Run the two export queries - first for the header, then for the data
exec xp_cmdshell @HEADERCMD
exec xp_cmdshell @CMD
-- Combine the two files into a single file
exec xp_cmdshell @Combine
-- Clean up the two temp files we created
exec xp_cmdshell @DelCMD
-- Clean up our temp tables
drop table ##cols
IF OBJECT_ID('tempdb..##OutputTable1') IS NOT NULL
drop table ##OutputTable1
After running it, I get the following in the attachment. What am I missing?
July 16, 2015 at 10:14 am
What's the complete error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2015 at 10:16 am
That's all in the image. doesn't give me any error or anything else.
July 16, 2015 at 10:22 am
This is either a permissions problem because the service account doesn't have permissions to access the path or the path might not exist. Remember that local paths refer to the server, not the computer from which you run the query.
July 16, 2015 at 10:23 am
Does the SQL Server service account have access to the path you specified?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2015 at 10:28 am
Got it to output. Now just have to set up the data to to display as currency if it is, also keep the leading '0s' in the data and also find out why the sort by isn't working.
July 16, 2015 at 11:26 am
Ok all,
Want to thank you for all your help. I have another question though. The output from the code gives me a .csv but the leading 0's are cut off when opening in excel. Is there a way to make that not happen?
July 16, 2015 at 11:36 am
If you can see the leading zeros in the file using notepad or something similar, that might be an excel problem. Be sure to define that column as string when opening the file.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply