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 |
|
X002548
Not Just a Number
15586 Posts |
 Posted - 2005-10-24 : 13:25:09
|
This has me baffled..I'm wondering if it's the format file...can you bcp in without the collation if you identify the card as a 7.0 card into 2000...I bcped out thta way....DECLARE @cmd varchar(8000)SELECT @cmd = 'bcp "CompDB.[dbo].[aud_snapshot]" in D:\Privacy\CompDB\DATIN\S0000001.dat -T -S servername -fD:\Privacy\CompDB\FORMATIN\dbo_aud_snapshotIn.fmt -E'EXEC master..xp_cmdshell @cmd Is returningNULLStarting copy...SQLState = 37000, NativeError = 156Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Function'.SQLState = 37000, NativeError = 156Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Function'.SQLState = 37000, NativeError = 156Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Function'.SQLState = 37000, NativeError = 156Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Function'.SQLState = 37000, NativeError = 156Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Function'.SQLState = 37000, NativeError = 156Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Function'.SQLState = 37000, NativeError = 156Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Function'.SQLState = 37000, NativeError = 156Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Function'.SQLState = 37000, NativeError = 156Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Function'.SQLState = 37000, NativeError = 156Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Function'.NULLBCP copy in failedNULL(25 row(s) affected) Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-10-24 : 13:45:37
|
| It's complaining about the text Function somewhere - find that and it'll probably give you a hint.Should be ok if you specify a v7 fornat file.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-24 : 14:08:25
|
what do you mean text function?CREATE TABLE [dbo].[aud_snapshot] ( [EmployeeID] [char] (11) NOT NULL , [SS] [char] (11) NULL , [Grade] [char] (5) NOT NULL , [CompPlan] [char] (10) NOT NULL , [JobFunction] [char] (3) NOT NULL , [JobCode] [char] (5) NOT NULL , [BG] [char] (10) NULL , [PC] [char] (10) NULL , [Function] [char] (10) NULL , [DeptCode] [char] (4) NOT NULL , [DivCode] [char] (5) NOT NULL , [SectionCode] [char] (3) NOT NULL , [FLSAStatus] [char] (1) NOT NULL , [Proration] [decimal](6, 2) NOT NULL , [TotalActiveDays] [int] NOT NULL , [FTEAnnualizedSalary] [money] NOT NULL , [Salary] [money] NOT NULL , [OTEarned] [money] NOT NULL , [ShiftDiffEarned] [money] NOT NULL , [HourlyRate] [money] NOT NULL , [HoursWorked] [int] NOT NULL , [DaysWorked] [int] NOT NULL , [FullOrPartTime] [char] (1) NOT NULL , [Status] [char] (1) NOT NULL , [MeritPool] [money] NOT NULL , [MeritReleased] [money] NOT NULL , [BonusPool] [money] NOT NULL , [BonusReleased] [money] NOT NULL , [LTPool] [money] NOT NULL , [LTReleased] [money] NOT NULL , [DeptID] [char] (8) NOT NULL , [FlatAward] [money] NOT NULL , [GuaranteedAward] [money] NOT NULL , [FundingTarget] [int] NOT NULL , [TotalEarnings] [money] NOT NULL , [NEBonusAward] [money] NOT NULL , [LastYearRating] [int] NULL , [LastYearMerit] [money] NULL , [LastYearLumpSum] [money] NULL , [LastYearBonus] [money] NULL , [LastYearInitialGrant] [money] NULL , [LastYearIncentivePayment] [money] NULL , [Name] [char] (30) NOT NULL , [Other] [money] NOT NULL , [Modified] [datetime] NOT NULL , [SalaryEarned] [money] NOT NULL , [TotalActiveHours] [int] NOT NULL , [BUPGLSCode] [char] (2) NOT NULL , [WorkersComp] [money] NOT NULL , [UpdateFlag] [int] NOT NULL , [LastYearSalary] [money] NULL , [LastYearGrade] [char] (3) NULL , [LastYearSupplementalPayment] [money] NULL , [LastYearOtherPayment] [money] NULL , [LastYearLTGrant] [money] NULL , [LastYearLTPayment] [money] NULL , [SalaryAsOfMarch] [money] NULL , [LastYearFlsa] [char] (1) NULL , [Created] [datetime] NOT NULL , [CreatedBy] [char] (10) NOT NULL , [dtEffective] [datetime] NOT NULL , [dtExpiration] [datetime] NOT NULL , [chStatus] [char] (1) NOT NULL , [dtModified] [datetime] NOT NULL , [vchModifiedID] [varchar] (8) NOT NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[aud_snapshot] WITH NOCHECK ADD CONSTRAINT [DF__aud_snapshot__SS__2E527D1A] DEFAULT ('*') FOR [SS], CONSTRAINT [DF__aud_snaps__Grade__09360704] DEFAULT ('*') FOR [Grade], CONSTRAINT [DF__aud_snaps__CompP__0A2A2B3D] DEFAULT ('*') FOR [CompPlan], CONSTRAINT [DF__aud_snaps__JobFu__0B1E4F76] DEFAULT ('*') FOR [JobFunction], CONSTRAINT [DF__aud_snaps__JobCo__0C1273AF] DEFAULT ('*') FOR [JobCode], CONSTRAINT [DF__aud_snapshot__BG__2F46A153] DEFAULT ('*') FOR [BG], CONSTRAINT [DF__aud_snapshot__PC__303AC58C] DEFAULT ('*') FOR [PC], CONSTRAINT [DF__aud_snaps__Funct__312EE9C5] DEFAULT ('*') FOR [Function], CONSTRAINT [DF__aud_snaps__DeptC__0D0697E8] DEFAULT ('*') FOR [DeptCode], CONSTRAINT [DF__aud_snaps__DivCo__0DFABC21] DEFAULT ('*') FOR [DivCode], CONSTRAINT [DF__aud_snaps__Secti__0EEEE05A] DEFAULT ('*') FOR [SectionCode], CONSTRAINT [DF__aud_snaps__FLSAS__0FE30493] DEFAULT ('*') FOR [FLSAStatus], CONSTRAINT [DF__aud_snaps__Prora__10D728CC] DEFAULT (1) FOR [Proration], CONSTRAINT [DF__aud_snaps__Total__11CB4D05] DEFAULT (365) FOR [TotalActiveDays], CONSTRAINT [DF__aud_snaps__FTEAn__12BF713E] DEFAULT (0) FOR [FTEAnnualizedSalary], CONSTRAINT [DF__aud_snaps__Salar__13B39577] DEFAULT (0) FOR [Salary], CONSTRAINT [DF__aud_snaps__OTEar__14A7B9B0] DEFAULT (0) FOR [OTEarned], CONSTRAINT [DF__aud_snaps__Shift__159BDDE9] DEFAULT (0) FOR [ShiftDiffEarned], CONSTRAINT [DF__aud_snaps__Hourl__16900222] DEFAULT (0) FOR [HourlyRate], CONSTRAINT [DF__aud_snaps__Hours__1784265B] DEFAULT (0) FOR [HoursWorked], CONSTRAINT [DF__aud_snaps__DaysW__18784A94] DEFAULT (0) FOR [DaysWorked], CONSTRAINT [DF__aud_snaps__FullO__196C6ECD] DEFAULT ('*') FOR [FullOrPartTime], CONSTRAINT [DF__aud_snaps__Statu__1A609306] DEFAULT ('A') FOR [Status], CONSTRAINT [DF__aud_snaps__Merit__1B54B73F] DEFAULT (0) FOR [MeritPool], CONSTRAINT [DF__aud_snaps__Merit__1C48DB78] DEFAULT (0) FOR [MeritReleased], CONSTRAINT [DF__aud_snaps__Bonus__1D3CFFB1] DEFAULT (0) FOR [BonusPool], CONSTRAINT [DF__aud_snaps__Bonus__1E3123EA] DEFAULT (0) FOR [BonusReleased], CONSTRAINT [DF__aud_snaps__LTPoo__1F254823] DEFAULT (0) FOR [LTPool], CONSTRAINT [DF__aud_snaps__LTRel__20196C5C] DEFAULT (0) FOR [LTReleased], CONSTRAINT [DF__aud_snaps__DeptI__210D9095] DEFAULT ('*') FOR [DeptID], CONSTRAINT [DF__aud_snaps__FlatA__2201B4CE] DEFAULT (0) FOR [FlatAward], CONSTRAINT [DF__aud_snaps__Guara__22F5D907] DEFAULT (0) FOR [GuaranteedAward], CONSTRAINT [DF__aud_snaps__Fundi__23E9FD40] DEFAULT (0) FOR [FundingTarget], CONSTRAINT [DF__aud_snaps__Total__24DE2179] DEFAULT (0) FOR [TotalEarnings], CONSTRAINT [DF__aud_snaps__NEBon__25D245B2] DEFAULT (0) FOR [NEBonusAward], CONSTRAINT [DF__aud_snaps__LastY__32230DFE] DEFAULT (0) FOR [LastYearRating], CONSTRAINT [DF__aud_snaps__LastY__33173237] DEFAULT (0) FOR [LastYearMerit], CONSTRAINT [DF__aud_snaps__LastY__340B5670] DEFAULT (0) FOR [LastYearLumpSum], CONSTRAINT [DF__aud_snaps__LastY__34FF7AA9] DEFAULT (0) FOR [LastYearBonus], CONSTRAINT [DF__aud_snaps__LastY__35F39EE2] DEFAULT (0) FOR [LastYearInitialGrant], CONSTRAINT [DF__aud_snaps__LastY__26C669EB] DEFAULT (0) FOR [LastYearIncentivePayment], CONSTRAINT [DF__aud_snapsh__Name__27BA8E24] DEFAULT ('*') FOR [Name], CONSTRAINT [DF__aud_snaps__Other__28AEB25D] DEFAULT (0) FOR [Other], CONSTRAINT [DF__aud_snaps__Modif__29A2D696] DEFAULT (getdate()) FOR [Modified], CONSTRAINT [DF__aud_snaps__Salar__2A96FACF] DEFAULT (0) FOR [SalaryEarned], CONSTRAINT [DF__aud_snaps__Total__2B8B1F08] DEFAULT (0) FOR [TotalActiveHours], CONSTRAINT [DF__aud_snaps__BUPGL__332C40D0] DEFAULT ('*') FOR [BUPGLSCode], CONSTRAINT [DF__aud_snaps__Worke__34206509] DEFAULT (0) FOR [WorkersComp], CONSTRAINT [DF__aud_snaps__Updat__35148942] DEFAULT (0) FOR [UpdateFlag], CONSTRAINT [DF__aud_snaps__LastY__36E7C31B] DEFAULT (0) FOR [LastYearSalary], CONSTRAINT [DF__aud_snaps__LastY__37DBE754] DEFAULT ('*') FOR [LastYearGrade], CONSTRAINT [DF__aud_snaps__Creat__2C7F4341] DEFAULT (getdate()) FOR [Created], CONSTRAINT [DF__aud_snaps__Creat__2D73677A] DEFAULT ('CTW') FOR [CreatedBy], CONSTRAINT [DF__aud_snaps__dtEff__2E678BB3] DEFAULT ('01/01/1900') FOR [dtEffective], CONSTRAINT [DF__aud_snaps__dtExp__2F5BAFEC] DEFAULT ('12/31/2999') FOR [dtExpiration], CONSTRAINT [DF__aud_snaps__chSta__304FD425] DEFAULT ('A') FOR [chStatus], CONSTRAINT [DF__aud_snaps__dtMod__3143F85E] DEFAULT (getdate()) FOR [dtModified], CONSTRAINT [DF__aud_snaps__vchMo__32381C97] DEFAULT ('CTW') FOR [vchModifiedID], CONSTRAINT [PK__aud_snapshot] PRIMARY KEY NONCLUSTERED ( [EmployeeID], [dtModified] ) WITH FILLFACTOR = 90 ON [PRIMARY] GOBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-24 : 14:28:08
|
| You have a column named function. Are you able to put square brackets around it in the fmt file?Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-24 : 14:51:28
|
quote:
Originally posted by tduggan
You have a column named function. Are you able to put square brackets around it in the fmt file?Tara
No It didn't work...I renamed it....which begs the question, how should you really handle this....with quotes?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-10-24 : 16:18:08
|
| Have you tried either -q or -Q which automatically quotes the column names to avoid reserved-word problems?Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-24 : 16:23:54
|
| Thank you very much...I've run in to sooooooo many third party vendor products that my company buys to support HR Functions, and they are all a piece of junk...and we use peoplesoft....Just painful...reserved words as a column name....puleazeBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-25 : 00:45:44
|
| will square brackets work?just an idea...--------------------keeping it simple... |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-25 : 08:57:17
|
quote:
Originally posted by jen
will square brackets work?just an idea...--------------------keeping it simple...
No they don't...but as Paul mentions, the parameter -q in the command line for bcp most probably will.I will be testing that out shortly.I do have a problem now with the bcp out and CHAR(13)+CHAR(10) as it creates a separate line in the file.I know that TAB delimited is the way to go, but I have a req to put this crap on OS/390...I went in and did a replace to solve it, and used "`~" to hold the place so I can replace it on the way back in.Obviously this is also painful, and to automate it, I would have to check ever char nchar, varchar and nvarchar columns (I Ignore text and ntext in this process).Any other insights on CRLF, or a best practices on how you would handle this in a fixed width bcp out? (THe answer is gonna be TAB Delimited)Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-25 : 12:31:11
|
| I handle CRLFs with -r\r\n in the bcp command.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-25 : 13:03:56
|
So do I...in the format card...7.0 43 1 SQLCHAR 0 11 "" 1 SS 2 SQLCHAR 0 26 "" 2 Effective_Date 3 SQLCHAR 0 26 "" 3 SRD_FLAG 4 SQLCHAR 0 4 "" 4 Plan_Year 5 SQLCHAR 0 11 "" 5 Employee_ID 6 SQLCHAR 0 10 "" 6 Comp_Plan 7 SQLCHAR 0 26 "" 7 New_Annualized_Salary 8 SQLCHAR 0 26 "" 8 Total_Comp 9 SQLCHAR 0 26 "" 9 New_Hourly_Rate 10 SQLCHAR 0 1 "" 10 Recommended_Rating 11 SQLCHAR 0 26 "" 11 Recommended_Increase 12 SQLCHAR 0 26 "" 12 Recommended_Increase_Pct 13 SQLCHAR 0 26 "" 13 Lump_Sum_Range_Low 14 SQLCHAR 0 26 "" 14 Lump_Sum_Range_High 15 SQLCHAR 0 26 "" 15 Lump_Sum 16 SQLCHAR 0 1 "" 16 Lump_Sum_Check 17 SQLCHAR 0 26 "" 17 Lump_Sum_Pct 18 SQLCHAR 0 3 "" 18 Earnings_Code 19 SQLCHAR 0 20 "" 19 Function_Definition 20 SQLCHAR 0 1 "" 20 On_Disability 21 SQLCHAR 0 1 "" 21 On_Leave 22 SQLCHAR 0 1 "" 22 Salary_Quartile 23 SQLCHAR 0 2 "" 23 Increase_Interval 24 SQLCHAR 0 26 "" 24 Increase_Range_Low 25 SQLCHAR 0 26 "" 25 Increase_Range_High 26 SQLCHAR 0 26 "" 26 Increase_Budget_Credit 27 SQLCHAR 0 26 "" 27 Increase_Budget_Credit_Pct 28 SQLCHAR 0 26 "" 28 Next_Salary_Review_Date 29 SQLCHAR 0 26 "" 29 Budget_Sal_Var_MRP 30 SQLCHAR 0 26 "" 30 New_Sal_Var_MRP 31 SQLCHAR 0 26 "" 31 Variance_Salary_Max 32 SQLCHAR 0 3 "" 32 Reason_Code 33 SQLCHAR 0 1 "" 33 Option_Increase 34 SQLCHAR 0 11 "" 34 User_SS 35 SQLCHAR 0 30 "" 35 User_Name 36 SQLCHAR 0 7 "" 36 User_ID 37 SQLCHAR 0 26 "" 37 Salary_Comp_MRR_Pct 38 SQLCHAR 0 1 "" 38 Salary_Comp_MRR_Ind 39 SQLCHAR 0 26 "" 39 Par_Target 40 SQLCHAR 0 6 "" 40 Market_ID 41 SQLCHAR 0 255 "" 41 Salary_Comment_Mgr 42 SQLCHAR 0 255 "" 42 Salary_Comment_HRC 43 SQLCHAR 0 26 "\r\n" 43 TimeAccess Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-25 : 19:52:22
|
| Are you sure the file terminates lines with a DOS linefeed (\r\n)? If you got it via FTP, and it came from a *nix/BSD/non-Windows server, the line terminator is LF-only (CHAR(10), \r). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-26 : 01:24:42
|
| "Any other insights on CRLF"SQL-DB to SQL-DB we use BCP's NATIVE format, saves all the Format File Grief!, but maybe this needs to be a text file that other applications can use?Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-27 : 09:15:48
|
| Rob, I did a replace on the columns and I got CHAR(13)+CHAR(10) in the varchar column if that's what you mean. I did a replace before the bcp out.This file is then ftp'ed up to the mainframe (don't get me started) and is "sanitized" for data privacy.The file is the brought back down, and bcp'ed back in.I just the replace the replacement chars with CHAR(13)+CHAR(10)This is a mainframe process that requires fixed width file formats. The linebreak caused a new line. In the bcp'ed out .dat file. It had nothing to do with the transmission of the file. When I tried to bcp the file that I had just bcp'ed out, I get the same problems.Thanks for all the input.Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
bcp Error