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
 General SQL Server Forums
 New to SQL Server Programming
 BCP format file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 10/26/2013 :  07:03:49  Show Profile  Reply with Quote
Hello,

I am trying to create a format file from the command below.

EXEC xp_cmdshell 'bcp HBSWMSTEST.schema.UDEF_DISPATCHER_INTERFACE_INVENTORY_SUMMARY_TABLE_TEMP format -c -t -f d:\test.fmt -U***** -P*****'


I am getting the following errors. I would very much appreciated it if someone could point out where I am going wrong.


Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'schema'.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/26/2013 :  07:46:10  Show Profile  Reply with Quote
Can you try this tweak?


EXEC xp_cmdshell 'bcp HBSWMSTEST.schema.UDEF_DISPATCHER_INTERFACE_INVENTORY_SUMMARY_TABLE_TEMP format null -c -t -f d:\test.fmt -S YourServerName -U***** -P*****'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 10/26/2013 :  08:03:03  Show Profile  Reply with Quote
Thanks, I have just tried and the same error is coming up
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/26/2013 :  08:04:02  Show Profile  Reply with Quote
Is schema actual name of your schema? if yes try [schema]
ie

EXEC xp_cmdshell 'bcp HBSWMSTEST.[schema].UDEF_DISPATCHER_INTERFACE_INVENTORY_SUMMARY_TABLE_TEMP format null -c -t -f d:\test.fmt -S YourServerName -U***** -P*****'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 10/26/2013 08:05:32
Go to Top of Page

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 10/26/2013 :  08:19:12  Show Profile  Reply with Quote
That will be the problem. I don't have a schema, thought it was just a function or something. How would I go about creating a schema for the following table.

Or do you create if from the file you are receiving?

CREATE TABLE [dbo].[UDEF_DISPATCHER_INTERFACE_INVENTORY_SUMMARY_TABLE_TEMP](
[Record_Type] [varchar](3) NULL,
[Action] [varchar](1) NULL,
[Client_Id] [varchar](10) NULL,
[Sku_Id] [varchar](50) NULL,
[Site_Id] [varchar](10) NULL,
[Owner_Id] [varchar](10) NULL,
[Supplier_Id] [varchar](15) NULL,
[Batch_Id] [varchar](15) NULL,
[Condition_Id] [varchar](10) NULL,
[Origin_Id] [varchar](10) NULL,
[Lock_Code] [varchar](10) NULL,
[Expiry_Date] [varchar](14) NULL,
[Ce_Under_Bond] [varchar](1) NULL,
[Ce_Coo] [varchar](3) NULL,
[User_Def_Type_1] [varchar](30) NULL,
[User_Def_Type_2] [varchar](30) NULL,
[User_Def_Type_3] [varchar](30) NULL,
[User_Def_Type_4] [varchar](30) NULL,
[User_Def_Type_5] [varchar](30) NULL,
[User_Def_Type_6] [varchar](30) NULL,
[User_Def_Type_7] [varchar](30) NULL,
[User_Def_Type_8] [varchar](30) NULL,
[User_Def_Date_1] [varchar](14) NULL,
[User_Def_Date_2] [varchar](14) NULL,
[User_Def_Date_3] [varchar](14) NULL,
[User_Def_Date_4] [varchar](14) NULL,
[Total_Records] [varchar](9) NULL,
[Total_Qty] [varchar](16) NULL,
[Total_Unlocked] [varchar](16) NULL,
[Total_Locked] [varchar](16) NULL,
[Total_Alloc] [varchar](16) NULL,
[Unlock_Alloc] [varchar](16) NULL,
[Lock_Alloc] [varchar](16) NULL,
[Total_Unalloc] [varchar](16) NULL,
[Unlock_Unalloc] [varchar](16) NULL,
[Lock_Unalloc] [varchar](16) NULL,
[Total_Prerec] [varchar](16) NULL,
[Quantity_Sign] [varchar](1) NULL,
[Total_Suspense] [varchar](16) NULL,
[Total_Replenish] [varchar](16) NULL,
[Sku_Description] [varchar](80) NULL,
[Time_Zone_Name] [varchar](64) NULL,
[Inv_Sum_Qty_Exceeded] [varchar](1000) NULL,
[Tracking_Level] [varchar](8) NULL,
[Archived] [varchar](1000) NULL,
[Condition_Id_Map] [varchar](30) NULL,
[Lock_Code_Map] [varchar](30) NULL,
[Total_Soft_Alloc] [varchar](16) NULL,
[Soft_Alloc_Avail] [varchar](1000) NULL
) ON [PRIMARY]

GO
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 10/26/2013 :  10:11:35  Show Profile  Reply with Quote
In your case the schema is dbo. So change the command to
EXEC xp_cmdshell 'bcp HBSWMSTEST.[dbo].UDEF_DISPATCHER_INTERFACE_INVENTORY_SUMMARY_TABLE_TEMP format null -c -t -f d:\test.fmt -S YourServerName -U***** -P*****'
The name HBSWMSTEST.[dbo].UDEF_DISPATCHER_INTERFACE_INVENTORY_SUMMARY_TABLE_TEMP is what they call 3-part naming. The first part HBSWMSTEST is the name of the database. The second part - dbo - is the schema name, and the third is the name of the table. Think of it as a hierarchy. A table belongs to a schema and a schema belongs to a database. A database may have one or more schemas, and a schema may have zero or more tables.

Some useful info: http://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/
http://technet.microsoft.com/en-us/library/ms177563.aspx
Go to Top of Page

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 10/26/2013 :  10:18:36  Show Profile  Reply with Quote
Perfect, it works!!.. Thanks. Start of file below. What do I need to put for the column which has test qualifiers " around the text?

10.0
49
1 SQLCHAR 0 3 "," 1 Record_Type Latin1_General_CI_AS
2 SQLCHAR 0 1 "," 2 Action Latin1_General_CI_AS
3 SQLCHAR 0 10 "," 3 Client_Id Latin1_General_CI_AS
4 SQLCHAR 0 50 "," 4 Sku_Id Latin1_General_CI_AS
5 SQLCHAR 0 10 "," 5 Site_Id Latin1_General_CI_AS
6 SQLCHAR 0 10 "," 6 Owner_Id Latin1_General_CI_AS
Go to Top of Page

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 10/26/2013 :  10:35:14  Show Profile  Reply with Quote
To be clear example below

Record_Type,"Action",Client_Id,Sku_Id

Go to Top of Page

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 10/26/2013 :  10:38:21  Show Profile  Reply with Quote
I have managed to sort it

1 SQLCHAR 0 3 ",\"" 1 Record_Type Latin1_General_CI_AS
2 SQLCHAR 0 1 "\"," 2 Action Latin1_General_CI_AS
3 SQLCHAR 0 10 "," 3 Client_Id Latin1_General_CI_AS
4 SQLCHAR 0 50 "," 4 Sku_Id Latin1_General_CI_AS
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/26/2013 :  10:57:25  Show Profile  Reply with Quote
This is a good read

http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.08 seconds. Powered By: Snitz Forums 2000