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 |
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
 Posted - 2013-10-26 : 07:03:49
|
| 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
52326 Posts |
Posted - 2013-10-26 : 07:46:10
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2013-10-26 : 08:03:03
|
| Thanks, I have just tried and the same error is coming up |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-26 : 08:04:02
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2013-10-26 : 08:19:12
|
| 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 |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-26 : 10:11:35
|
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 |
|
|
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2013-10-26 : 10:18:36
|
| 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.0491 SQLCHAR 0 3 "," 1 Record_Type Latin1_General_CI_AS2 SQLCHAR 0 1 "," 2 Action Latin1_General_CI_AS3 SQLCHAR 0 10 "," 3 Client_Id Latin1_General_CI_AS4 SQLCHAR 0 50 "," 4 Sku_Id Latin1_General_CI_AS5 SQLCHAR 0 10 "," 5 Site_Id Latin1_General_CI_AS6 SQLCHAR 0 10 "," 6 Owner_Id Latin1_General_CI_AS |
|
|
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2013-10-26 : 10:35:14
|
| To be clear example belowRecord_Type,"Action",Client_Id,Sku_Id |
|
|
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2013-10-26 : 10:38:21
|
| I have managed to sort it1 SQLCHAR 0 3 ",\"" 1 Record_Type Latin1_General_CI_AS2 SQLCHAR 0 1 "\"," 2 Action Latin1_General_CI_AS3 SQLCHAR 0 10 "," 3 Client_Id Latin1_General_CI_AS4 SQLCHAR 0 50 "," 4 Sku_Id Latin1_General_CI_AS |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
BCP format file