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.
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 CSVthen 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.JimUsers <> 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. |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-11-19 : 13:32:15
|
Ok What's wrong withbcp "exec Timeclockplus.dbo.Export_Test" queryout Exporttest.csv -Sserverone -T -c -terrorServer: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'queryout'.JimUsers <> Logic |
|
|
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\nAre you running it from the command line?Tara |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-11-19 : 13:42:21
|
Was running it from QA. no luckCan this be part of my SP?JimUsers <> Logic |
|
|
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. |
|
|
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 |
|
|
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...Brett8-) |
|
|
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?JimUsers <> Logic |
|
|
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. |
|
|
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 |
|
|
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. JimUsers <> Logic |
|
|
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 everythingOnce 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
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 |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-11-19 : 14:44:32
|
Ok Daaaaaaaaa Windows command line.ErrorInvalid object name #transpayrollJimUsers <> Logic |
|
|
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 |
|
|
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.JimUsers <> Logic |
|
|
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. |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-11-19 : 14:53:47
|
Already have set nocount onhere is the SPALTER PROCEDURE dbo.Export_Test ASSet nocount onDeclare @week IntSelect @week = -1Declare @RefDay datetimeDeclare @Weekday Intdeclare @Weekstart varchar(30)Declare @Weekend datetime, @SatStart datetime, @Satend datetime,@SunStart datetime, @Sunend datetimeDeclare @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 PeriodEndInto #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.EmployeeIdWhere 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 Expr3FROM dbo.#TransPayrollWHERE (OThours > 0) AND (Sunday = 0) AND (Saturday = 0)UPDATE dbo.#TransPayrollSET Rate = Rate * 1.5, ExportAs = 'Ovrtime 1.5', Reghours = Reghours + OThoursWHERE (Saturday = 1) And (JobCodeGroup = 'union' Or JobCodeGroup = 'NON-EXEMPT-M')UPDATE dbo.#TransPayrollSET Rate = Rate * 2, ExportAs = 'Ovrtime 2.0' ,Reghours = Reghours + OThoursWHERE (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,JobCodeGroupFROM dbo.#TransPayrollGROUP BY EmpName, EmployeeId, EmployeeCode, SSN, ExportAs, Rate, PeriodStart, PeriodEnd,JobCodeGroupOrder by EmpName,RateDrop Table #transPayrollJimUsers <> Logic |
|
|
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\nSQLState = S0002, NativeError = 208Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TransPayroll'.SQLState = S0002, NativeError = 208Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TransPayroll'.JimUsers <> Logic |
|
|
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\nSQLState = S0002, NativeError = 208Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.TransPayroll'.SQLState = S0002, NativeError = 208Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.TransPayroll'.JimUsers <> Logic |
|
|
Next Page
|
|
|
|
|