Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Export from Temptable to CSV

Author  Topic 

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-19 : 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

15732 Posts

Posted - 2004-11-19 : 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.
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-19 : 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
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 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
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-19 : 13:42:21
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

15732 Posts

Posted - 2004-11-19 : 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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 13:45:50
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 - 2004-11-19 : 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-)
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-19 : 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
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 14:07:42
Run bcp.exe from the command line. Post the error message here.

Tara
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-19 : 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
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 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
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-19 : 14:44:32
Ok Daaaaaaaaa Windows command line.

Error

Invalid object name #transpayroll



Jim
Users <> Logic
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 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
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-19 : 14:49:23
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

15732 Posts

Posted - 2004-11-19 : 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.
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-19 : 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
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-19 : 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
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-19 : 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
Go to Top of Page
    Next Page

- Advertisement -