May 5, 2008 at 7:35 am
Hi all,
I am not a programer, but learning masses about SQL more an more (its amazing how all the peices are slowly falling together) but I need one final step in my code below.
The template name will stay as it is, but in the 3rd line and in the 7th line of the blow code I want to intorodcue/use the current date as a variable, i.e. the copied file will be named Sales-05.05.2007.xls (I have used todays date but the date must be the date of when the code is run)instead of just Sales.xls, then the data to be exported into the Sales-05.05.2007.xls instead of just Sales.xls.
USE [TouchStar]
GO
exec xp_cmdshell 'COPY C:\SQLData\Templates\Sales.xls C:\SQLData\Exports\Sales.xls'
Go
INSERT into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLData\Exports\Sales.xls;;HDR=YES',
'SELECT LoginID,Active FROM [Sheet1$]')
SELECT Login, Active
FROM dbo.Agent
Where Active = 'True'
ORDER BY Firstname DESC
Go
For example I have tried the following with no luck...
/*
DECLARE @CurrentDate as varchar
SET @CurrentDate = CONVERT(Varchar, GETDATE(), 103))
*/
CREATE PROCEDURE [dbo].[usp_CallStats]
@CurrentDate as Varchar,
As
exec xp_cmdshell 'COPY C:\SQLData\Templates\Sales.xls C:\SQLData\Exports\Sales-@CurrentDate.xls'
Go
Please help, Prompt replies are much appreciated as I want to test today...
Kind Regards
May 5, 2008 at 7:42 am
Try this. If you include the
DECLARE @CurrentDate as varchar
SET @CurrentDate = CONVERT(Varchar, GETDATE(), 103))
*/
CREATE PROCEDURE [dbo].[usp_CallStats]
@CurrentDate as Varchar
Declare @strSQL as String
As
@strSQL = 'COPY C:\SQLData\Templates\Sales.xls C:\SQLData\Exports\Sales-' + @CurrentDate + '.xls'
exec xp_cmdshell @strSQL
Go
May 5, 2008 at 7:51 am
Wow that was quick, Okay, makes some sence, I tried the following;
/*
DECLARE @CurrentDate as varchar
SET @CurrentDate = CONVERT(Varchar, GETDATE(), 103))
Declare @strSQL as String
SET @strSQL = 'COPY C:\SQLData\Templates\Sales.xls C:\SQLData\Exports\Sales-' + @CurrentDate + '.xls'
*/
CREATE PROCEDURE [dbo].[usp_CallStats]
@CurrentDate as Varchar,
@strSQL as String
As
exec xp_cmdshell @strSQL
Go
but I get error:
Msg 2715, Level 16, State 3, Procedure usp_CallStats, Line 7
Column, parameter, or variable #2: Cannot find data type String.
Parameter or variable '@strSQL' has an invalid data type.
May 5, 2008 at 8:04 am
May 5, 2008 at 8:12 am
Well, I'm getting closer,
DECLARE @CurrentDate as varchar
SET @CurrentDate = CONVERT(Varchar, GETDATE(), 103)
Declare @strSQL as Varchar(8000)
SET @strSQL = 'COPY C:\SQLData\Templates\Sales.xls C:\SQLData\Exports\Sales-' + @CurrentDate + '.xls'
exec xp_cmdshell @strSQL
Go
but what i get is a file labelled "Sales-0.xls"
LOL 😉
May 5, 2008 at 8:24 am
The problem is with you DECLARE @CurrentDate as varchar statement...
I looked right past that before. When you declare it as a varchar for all intents it gets declared as a varchar(1). Because of this, your @currentDate variable can only hold 1 character. Declare it as a Varchar(10) and you should be all set.
-Luke.
May 5, 2008 at 8:28 am
Of course how could I be so dumb! 😀
Also I found another error, the convert of 103 puts the date as 05/05/2008 which with windows settings caused errors, so I used 112;
DECLARE @CurrentDate as varchar(10)
SET @CurrentDate = CONVERT(Varchar, GETDATE(), 112)
Declare @strSQL as Varchar(8000)
SET @strSQL = 'COPY C:\SQLData\Templates\Sales.xls C:\SQLData\Exports\Sales-'+ @CurrentDate+'.xls'
exec xp_cmdshell @strSQL
Go
this then produced a file labelled "Sales-20080505.xls", but i could use other formats if needed, see link if needed: http://msdn.microsoft.com/en-us/library/ms187928.aspx
Thanks for your quick help...
May 5, 2008 at 8:42 am
May 5, 2008 at 8:43 am
Just when I think I have it cracked...:ermm:
I am now trying to run my second part of my codewhich now pastes the results into the file which has just been created (below)
DECLARE @ExportDate as varchar(10)
SET @ExportDate = CONVERT(Varchar, GETDATE(), 104)
Declare @ExportLocation as Varchar(8000)
SET @ExportLocation = 'C:\SQLData\Exports\Sales-' + @ExportDate+ '.xls'
INSERT into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=@ExportLocation',
'SELECT LoginID,Active FROM [Sheet1$]')
SELECT Login, Active
FROM dbo.Agent
Where Active = 'True'
ORDER BY Firstname DESC
Go
But get the following:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 5
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The full code I am trying to run is;
Use [Touchstar]
Go
DECLARE @CurrentDate as varchar(10)
SET @CurrentDate = CONVERT(Varchar, GETDATE(), 104)
Declare @strSQL as Varchar(8000)
SET @strSQL = 'COPY C:\SQLData\Templates\Sales.xls C:\SQLData\Exports\Sales-' + @CurrentDate+ '.xls'
exec xp_cmdshell @strSQL
Go
DECLARE @ExportDate as varchar(10)
SET @ExportDate = CONVERT(Varchar, GETDATE(), 104)
Declare @ExportLocation as Varchar(8000)
SET @ExportLocation = 'C:\SQLData\Exports\Sales-' + @ExportDate+ '.xls'
INSERT into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=@ExportLocation',
'SELECT LoginID,Active FROM [Sheet1$]')
SELECT Login, Active
FROM dbo.Agent
Where Active = 'True'
ORDER BY Firstname DESC
Go
I know the issues is around the 'Excel 8.0;Database=@ExportLocation',
May 5, 2008 at 8:52 am
When you are setting your 'Excel 8.0;Database=@ExportLocation', You are having the problem where it is attempting to evaluate your variable as plain text, not the text stored within the variable. You basically, need to escape your string soemwhat like you are doing with your copy command. 'Excel 8.0;Database=' + @ExportLocation,
Granted I don't work with Open Rows Set very often, so this may or may not work.
Additionally, have you given any thought to doing this with an SSIS task as it may be simpler than with direct TSQL, or you may want to look at writing our to your sales.xls table and then renaming it later in your code with the copy command.
-Luke.
May 5, 2008 at 9:09 am
I think your right, It won't let you use variables in this way, so I used your suggested work around which works great;
Use Touchstar
Go
exec xp_cmdshell 'COPY C:\SQLData\Templates\Sales.xls C:\SQLData\Exports\Sales.xls'
Go
INSERT into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLData\Exports\Sales.xls;;HDR=YES',
'SELECT LoginID,Active FROM [Sheet1$]')
SELECT Login, Active
FROM dbo.Agent
Where Active = 'True'
ORDER BY Firstname DESC
Go
DECLARE @CurrentDate as varchar(10)
SET @CurrentDate = CONVERT(Varchar, GETDATE(), 104)
Declare @strSQL as Varchar(8000)
SET @strSQL = 'COPY C:\SQLData\Exports\Sales.xls C:\SQLData\Exports\Sales-' +@CurrentDate+ '.xls'
exec xp_cmdshell @strSQL
Go
exec xp_cmdshell 'DEL C:\SQLData\Exports\Sales.xls'
Go
😀
May 5, 2008 at 9:14 am
May 6, 2008 at 8:03 am
I believe you need to have it wrapped in quotes or double quotes.
SET @ExportLocation = '''C:\SQLData\Exports\Sales-' + @ExportDate+ '.xls'''
or
SET @ExportLocation = '"C:\SQLData\Exports\Sales-' + @ExportDate+ '.xls"'
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply