SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Export from Temptable to CSV
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 11/19/2004 :  12:47:01  Show Profile  Visit JimL's Homepage  Reply with Quote
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
15688 Posts

Posted - 11/19/2004 :  13:01:14  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 11/19/2004 :  13:32:15  Show Profile  Visit JimL's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37471 Posts

Posted - 11/19/2004 :  13:34:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 11/19/2004 :  13:42:21  Show Profile  Visit JimL's Homepage  Reply with Quote
Was running it from QA. no luck

Can this be part of my SP?

Jim
Users <> Logic
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 11/19/2004 :  13:45:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37471 Posts

Posted - 11/19/2004 :  13:45:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes, but you'd need to use master.dbo.xp_cmdshell to launch bcp.exe.

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/19/2004 :  13:50:25  Show Profile  Reply with Quote
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-)
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 11/19/2004 :  13:59:15  Show Profile  Visit JimL's Homepage  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 11/19/2004 :  14:07:03  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37471 Posts

Posted - 11/19/2004 :  14:07:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run bcp.exe from the command line. Post the error message here.

Tara
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 11/19/2004 :  14:28:22  Show Profile  Visit JimL's Homepage  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 11/19/2004 :  14:32:37  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37471 Posts

Posted - 11/19/2004 :  14:36:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 11/19/2004 :  14:44:32  Show Profile  Visit JimL's Homepage  Reply with Quote
Ok Daaaaaaaaa Windows command line.

Error

Invalid object name #transpayroll



Jim
Users <> Logic
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37471 Posts

Posted - 11/19/2004 :  14:46:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
And you ran bcp to Export_Test? If so, then you've got something messed up in the stored proc causing that error.

Tara
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 11/19/2004 :  14:49:23  Show Profile  Visit JimL's Homepage  Reply with Quote
SP export_test runs fine.

Looks like the bcp does not like my temp table updates and inserts.

Jim
Users <> Logic
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 11/19/2004 :  14:51:58  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 11/19/2004 :  14:53:47  Show Profile  Visit JimL's Homepage  Reply with Quote
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
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 11/19/2004 :  14:59:50  Show Profile  Visit JimL's Homepage  Reply with Quote
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
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 11/19/2004 :  15:11:24  Show Profile  Visit JimL's Homepage  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000