| Author |
 Topic  |
|
JimL
SQL Slinging Yak Ranger
USA
1531 Posts |
 Posted - 11/19/2004 : 12:47:01
|
I wish to eliminate my DTS package that performs my CSV conversion.
I have a very complicated SP (3 temp tables deep) and then dump into a real table then run the rest of the package to bulk export to a CSV
then drop the table.
Can I go direct from a temp table to output a CSV with the same SP? I only need to drop one column.
Jim
Users <> Logic |
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 11/19/2004 : 13:01:14
|
You can always use bcp to export from a stored procedure to a CSV or other delimited file:
bcp "exec myDatabase..myProcedure" queryout data.csv -Sserver -T -c -t,
You simply have to include a SELECT statement at the end of the procedure that produces the results you want to export. Books Online has more details about bcp. |
 |
|
|
JimL
SQL Slinging Yak Ranger
USA
1531 Posts |
Posted - 11/19/2004 : 13:32:15
|
Ok What's wrong with
bcp "exec Timeclockplus.dbo.Export_Test" queryout Exporttest.csv -Sserverone -T -c -t
error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'queryout'.
Jim
Users <> Logic |
Edited by - JimL on 11/19/2004 13:34:16 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 11/19/2004 : 13:34:54
|
You are missing the comma after the t switch though. I usually do this for my bcps:
bcp "exec Timeclockplus.dbo.Export_Test" queryout Exporttest.csv -Sserverone -T -c -r\r\n
Are you running it from the command line?
Tara |
Edited by - tkizer on 11/19/2004 13:35:49 |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1531 Posts |
Posted - 11/19/2004 : 13:42:21
|
Was running it from QA. no luck
Can this be part of my SP?
Jim
Users <> Logic |
|
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 11/19/2004 : 13:45:34
|
| bcp is a command line utility. It doesn't make sense to add it to your stored procedure. What you can do is create a job with a CmdExec step that calls bcp. Then you can schedule the job, or run it manually whenever you want to do the export. bcp would still call your stored procedure to return the data. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 11/19/2004 : 13:45:50
|
Yes, but you'd need to use master.dbo.xp_cmdshell to launch bcp.exe.
Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 11/19/2004 : 13:50:25
|
quote:
Originally posted by robvolk
bcp is a command line utility. It doesn't make sense to add it to your stored procedure.
And why not?
I do it all the time...I also use the sproc to archive files to folders after I've imported them...
Brett
8-) |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1531 Posts |
Posted - 11/19/2004 : 13:59:15
|
My brain must be fried.
Anyone got a copy of bcp for big dummies.
I tried creating the job but it still does not like "queryout"
Hey is their a way to create a script from an existing DTS package?
Jim
Users <> Logic |
Edited by - JimL on 11/19/2004 14:01:08 |
|
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 11/19/2004 : 14:07:03
|
It doesn't make sense to call bcp from a stored procedure, if it's going to call the SAME stored procedure. You'll get a procedural loop. Having bcp call a different stored procedure is fine.
My main point is that there's no advantage in calling it from a stored procedure when it can be called from a job. Calling it from a stored procedure also holds the procedure until bcp is finished; calling it as a job does not. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 11/19/2004 : 14:07:42
|
Run bcp.exe from the command line. Post the error message here.
Tara |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1531 Posts |
Posted - 11/19/2004 : 14:28:22
|
Ok now I feel real stupid.
I have never manualy created a job so I am floundering.
Tara what command line?
In the job creator in EM? I do not see a run capability.
Jim
Users <> Logic |
|
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 11/19/2004 : 14:32:37
|
In Enterprise Manager:
-Management Folder
-Jobs
-Create a new job
-Create a new step of type: Operating System (CmdExec)
-In the Command box, type in your bcp command line
-Save everything
Once the job is saved you can run it from EM at any time by right-clicking and choosing "Start Job". You can also use the sp_start_job stored procedure to start a job from QA. Books Online has the details. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 11/19/2004 : 14:36:31
|
Command line -
Start..run..type in cmd and hit enter. You are now at the command line. You should start with this before setting it up as a job in case it errors out. It's easier to troubleshoot from the command line.
Tara |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1531 Posts |
Posted - 11/19/2004 : 14:44:32
|
Ok Daaaaaaaaa Windows command line.
Error
Invalid object name #transpayroll
Jim
Users <> Logic |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 11/19/2004 : 14:46:04
|
And you ran bcp to Export_Test? If so, then you've got something messed up in the stored proc causing that error.
Tara |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1531 Posts |
Posted - 11/19/2004 : 14:49:23
|
SP export_test runs fine.
Looks like the bcp does not like my temp table updates and inserts.
Jim
Users <> Logic |
|
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 11/19/2004 : 14:51:58
|
Put SET NOCOUNT ON at the top of the procedure, see if that fixes it.
I have also seen bcp barf on a temp table in a sproc, you may have to use regular tables or table variables. |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1531 Posts |
Posted - 11/19/2004 : 14:53:47
|
Already have set nocount on
here is the SP
ALTER PROCEDURE dbo.Export_Test
AS
Set nocount on
Declare @week Int
Select @week = -1
Declare @RefDay datetime
Declare @Weekday Int
declare @Weekstart varchar(30)
Declare @Weekend datetime, @SatStart datetime, @Satend datetime,@SunStart datetime, @Sunend datetime
Declare @PeriodStart Varchar(30) ,@PeriodEnd Varchar(30)
Select @RefDay = dateadd(week,@week,Getdate())
SELECT @Weekday = DATEPART(weekday, @REFDAY)
Select @PeriodStart = Convert(Varchar(30),Dateadd(day, - (@Weekday -1) ,@RefDay),101)
Select @PeriodEnd = Convert(Varchar(30),Dateadd(day, 6 ,Cast(@PeriodStart as Datetime)),101)
Select @Weekstart = Convert(Varchar(30),Dateadd(day, - @Weekday ,@RefDay),101) + ' 20:00:00'
Select @Weekend = Dateadd (week,1,Cast(@weekstart as datetime))
Select @Satstart = Dateadd(day,6,Cast(@weekstart as datetime))
Select @Satend = Dateadd(hour, 4,(Dateadd(day,7,Cast(@weekstart as datetime))))
Select @SunStart = Cast(@weekstart as datetime)
Select @Sunend = Dateadd(hour, 3,(Dateadd(day,1,Cast(@weekstart as datetime))))
SELECT dbo.EmployeeList.LastName + ' ' + dbo.EmployeeList.FirstName As EmpName,dbo.EmployeeHours.EmployeeId, dbo.EmployeeList.EmployeeCode , SSN,dbo.EmployeeHours.TimeIn, dbo.EmployeeHours.TimeOut,
dbo.EmployeeHours.JobCode, dbo.EmployeeHours.Rate, dbo.EmployeeHours.ManagerApproval, dbo.EmployeeHours.MissedInPunch,
dbo.EmployeeHours.MissedOutPunch, dbo.MasterJobCodeList.ExportAs, dbo.MasterJobCodeList.JobCodeGroup,
Reghours = Case When Cast(Datediff(mi,dbo.EmployeeHours.TimeIn,dbo.EmployeeHours.TimeOut) as real) / 60 > 8
And (dbo.MasterJobCodeList.JobCodeGroup = 'union' Or dbo.MasterJobCodeList.JobCodeGroup = 'NON-EXEMPT-M')
Then 8.0
Else Cast(Datediff(mi,dbo.EmployeeHours.TimeIn,dbo.EmployeeHours.TimeOut) as Decimal(6,1)) / 60
End ,
OThours = Case When Cast(Datediff(mi,dbo.EmployeeHours.TimeIn,dbo.EmployeeHours.TimeOut) as real) / 60 > 8
And (dbo.MasterJobCodeList.JobCodeGroup = 'union' Or dbo.MasterJobCodeList.JobCodeGroup = 'NON-EXEMPT-M')
Then Round((Cast(Datediff(mi,dbo.EmployeeHours.TimeIn,dbo.EmployeeHours.TimeOut) as Decimal(6,1)) / 60) - 8,1)
Else 0.0
End,
Saturday = Case When dbo.EmployeeHours.TimeIn >= @Satstart And dbo.EmployeeHours.TimeOut <= @Satend
And (dbo.MasterJobCodeList.JobCodeGroup = 'union' Or dbo.MasterJobCodeList.JobCodeGroup = 'NON-EXEMPT-M')
Then 1
Else 0
End,
Sunday = Case When dbo.EmployeeHours.TimeIn >= @SunStart And dbo.EmployeeHours.TimeOut <= @Sunend
And (dbo.MasterJobCodeList.JobCodeGroup = 'union' Or dbo.MasterJobCodeList.JobCodeGroup = 'NON-EXEMPT-M')
Then 1
Else 0
End,
@PeriodStart As PeriodStart, @PeriodEnd As PeriodEnd
Into #TransPayroll
FROM dbo.EmployeeHours INNER JOIN
dbo.MasterJobCodeList ON dbo.EmployeeHours.JobCode = dbo.MasterJobCodeList.JobCode INNER JOIN
dbo.EmployeeList ON dbo.EmployeeHours.EmployeeId = dbo.EmployeeList.EmployeeId
Where dbo.EmployeeHours.TimeIn Between Cast(@weekstart as datetime) and @Weekend
INSERT INTO dbo.#TransPayroll
(EmpName, EmployeeId, EmployeeCode, SSN, JobCode, Rate, ExportAs, PeriodStart, PeriodEnd, Saturday, Sunday, Reghours, JobCodeGroup, TimeIn,
TimeOut, ManagerApproval, MissedInPunch, MissedOutPunch, OThours)
SELECT EmpName, EmployeeId, EmployeeCode, SSN, JobCode, Rate * 1.5 AS Expr1, 'Ovrtime 1.5' AS Expr2, PeriodStart, PeriodEnd, Saturday, Sunday,
OThours, JobCodeGroup, TimeIn, TimeOut, ManagerApproval, MissedInPunch, MissedOutPunch, OThours AS Expr3
FROM dbo.#TransPayroll
WHERE (OThours > 0) AND (Sunday = 0) AND (Saturday = 0)
UPDATE dbo.#TransPayroll
SET Rate = Rate * 1.5, ExportAs = 'Ovrtime 1.5', Reghours = Reghours + OThours
WHERE (Saturday = 1) And (JobCodeGroup = 'union' Or JobCodeGroup = 'NON-EXEMPT-M')
UPDATE dbo.#TransPayroll
SET Rate = Rate * 2, ExportAs = 'Ovrtime 2.0' ,Reghours = Reghours + OThours
WHERE (Sunday = 1) And (JobCodeGroup = 'union' Or JobCodeGroup = 'NON-EXEMPT-M')
SELECT EmpName, EmployeeId, EmployeeCode, SSN, ExportAs,Cast(Rate As Decimal (6,2)) As Rate, CAst(SUM(Reghours) As Decimal(4,1)) AS Hours,
Cast(Rate * SUM(Reghours) As Decimal (6,2)) AS LineTTL, PeriodStart, PeriodEnd,JobCodeGroup
FROM dbo.#TransPayroll
GROUP BY EmpName, EmployeeId, EmployeeCode, SSN, ExportAs, Rate, PeriodStart, PeriodEnd,JobCodeGroup
Order by EmpName,Rate
Drop Table #transPayroll
Jim
Users <> Logic |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1531 Posts |
Posted - 11/19/2004 : 14:59:50
|
Error is
C:\Documents and Settings\System Design>bcp "exec Timeclockplus.dbo.Export_Test"
queryout Exporttest.csv -Sserverone -T -c -r\r\n
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#Tra
nsPayroll'.
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#Tra
nsPayroll'.
Jim
Users <> Logic |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1531 Posts |
Posted - 11/19/2004 : 15:11:24
|
Even if I change it to a regular table I get the same error type.
C:\Documents and Settings\System Design>bcp "exec Timeclockplus.dbo.Export_Test"
queryout Exporttest.csv -Sserverone -T -c -r\r\n
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.
TransPayroll'.
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.
TransPayroll'.
Jim
Users <> Logic |
Edited by - JimL on 11/19/2004 15:14:38 |
|
|
|
Topic |
|
|
|
Export from Temptable to CSV
