SQL 2012 :: How To Create XML Format File
May 21, 2015
I see this all over for creating a Format file:
bcp Northwind.dbo.Products format nul -c -f "c:Products.fmt" -T
Or this for XML
bcp AdventureWorks.HumanResources.Department format nul -c -x -f department.xml -T -S servername
I get an error saying "cannot open a connection to SQL SERVER"
The SQL Server is local on my laptop.
Here is the exact code I am using:
bcp OrderDb.CompanyDepartment foramt nul -c -x -f c:CompanyDepartment.fmt -t, -T
And I have tried :
bcp OrderDb.CompanyDepartment foramt nul -c -x -f c:CompanyDepartment.xml -t, -T
Also, some say to put the server name at the end, but then I get an unknown argument on command line error
View 1 Replies
ADVERTISEMENT
Feb 16, 2015
I have a date file with no delimiter like bellow
0080970393102312072981103378000004329392643958
0080970393102312072981103378000004329392643958
I just know 5 first number in a line is for example "ID of bank"
or 6th and 7th number in a line is for example "ID of employee"
How can I create a XML format file?
View 2 Replies
View Related
Nov 10, 2014
We have one table where we store all documents in one of the column called "Doc" with varbinary(max) data type.
We want to download those documents from sql table to windows explorer and i wrote BCP in sql 2005. And things were fine.
The format file I used there looks like this,
9.0
1
1 SQLBINARY 0 0 "" 1 Doc ""
Now we are in 2014 and when I try the same code with same format file, it hangs in the middle. So I changed the file to 12.0 instead of 9.0 but still not working.
View 0 Replies
View Related
Jan 22, 2008
I'm using SSIS to do bulk inserts from fixed width files to about 20 tables in my SQL database.
The problem I'm running into is in creating Format Files for the bulk insert task to use. I've gotten the bcp command to create format files that will read csv files, but I can't seem to figure out how to get it to create one for fixed-width.
I know it can be done: http://msdn2.microsoft.com/en-us/library/ms191234.aspx
At the bottom (Section F) it shows an XML format file for reading a fixed-width file. When I manually create one of these to match one of my tables, the bulk insert worked fine.
Closest I've come is with this ( [] bracketed items are correct values, just censored here):
C:Program FilesMicrosoft SQL Server90ToolsBinn>bcp [database].[owner].[table] format
nul -c -f C:TableFMT.xml -x -S[Server] -U[Username] -P[Password]
My question is, what is the bcp command to create this sort of XML format file?
View 1 Replies
View Related
Dec 17, 2007
Hi All,
How to create a text file in UNIX format using Flat File connection manager. By default when we create a connection manager for flat files it is taking "CRLF" as the delimeter.
I beleive the format of the file will be decided based on the control line feed character at the end of each row. There are different control line feeds for different operating systems.
CR - Mac OS (Carraige return)
LF - UNIX (Line Feed)
CRLF - Windows. (Carriage return Line Feed)
Correct me if i am wrong.
Thanks in advance.
View 3 Replies
View Related
Nov 9, 2014
I am trying to load a fixed width text file using `Bulk Insert` and a XML format file. I have used the same process and XML file on another fixed width, except with less columns.
Error
Msg 4857, Level 16, State 1, Line 16
Line 4 in format file "PATHCaddr.xml": Attribute "type" could not be specified for this type.
SQL Server Table
[code lang="sql"]
create table [dbo].[raw_addr](
address_numbervarchar(max),
addr_linelvarchar(max),
addr_line2varchar(max),
street_novarchar(max),
[code]....
View 0 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Feb 27, 2015
I found this from a web site
SELECT * FROM Salesorders;
OUTPUT TO 'C: etssales.csv'
FORMAT TEXT
QUOTE '"'
WITH COLUMN NAMES;
However I do not want the delimiter to be a comma. Instead I plan to use something like ****%****
View 9 Replies
View Related
Dec 11, 2014
I know this is easy but I am having trouble creating a database from a backup file.
I created a database (Test).
Then I selected 'Test', went to Tasks and selected 'Restore Database'.
I selected 'Device' browsed to the backup file.
I choose the destination database to be 'Test'.
There is no backup sets listed to restore. The 'OK' button is disabled.
How do you get the backup list to display? This is a new database. I don't need a backup.
View 5 Replies
View Related
Jul 15, 2015
I need to create a new db from a backup file of an allready existing database (vehicledb) on this server.So i created a new database with name "vehicledb2" .But when i try this, the backup says that this file is used...The backup set holds a backup of a database other than the existing 'Vehicles2' database. Restore of database 'Vehicledb' failed.
View 9 Replies
View Related
Oct 3, 2014
I have a store procedure in MC400 which I can call from SSMS using the below command:
EXEC ('CALL GETENROLLMENT() ')At serverName
Now this command returns two data sets like:
HA HB HC HD HE
1112
112571ABC14
113574ABC16
114577ABC87
DADBDCDD
1115566VG02
1115566VG02
1115566VG02
I want to generate two different XML files from these two datasets.Is there any way this can be achieved in SSIS or t-sql ?
View 3 Replies
View Related
Nov 16, 2006
Hi All,
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Regards,
Kuldeep Chauhan
View 2 Replies
View Related
Jan 2, 2008
Hey All,
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;
SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Thanks for your time,
Hayden.
View 1 Replies
View Related
Jul 23, 2005
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
View 7 Replies
View Related
Jan 16, 2008
I have a txt file with format as following
MailAddress:Kienpt@ifi.local
DomainName:ETH2K
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
I want to use File Flat Connection to analyse format of this file. And i want each record after analysing include 4 fields as folowing:
- MailAddress, DomainName, Date, Operation
(Mail Address and DomainName is same in each record)
Can you help me?
View 1 Replies
View Related
Jan 11, 2006
Im new / novice user to writing SQL scripting. I have two tables and table1 has two columns
uslid and groupid,
uslid1, group1
uslid1, group2
uslid1, group3
uslid3, group1
uslid3, group3 etc
the second table2 has three columns
uslid, name, and location
uslid1, john, bldg 4
uslid2, jane, accounting
uslid3, joe, mail room
. I am trying to do a inner join but not working and not sure if this will work to create new temp table.
example of what i am trying to get in new temp table on one row:
uslid, name, location, groupid1, groupid2, groupid3
uslid1, john, bldg 4, group1, group2, group3
uslid3, joe, mail room, goup1, , group3
Hope i explained this ok. Im using 2003 server sql 2000 ver 7, thank you in advance.
View 1 Replies
View Related
Sep 5, 2007
We use an internal tool that can display ASP pages. I would like to use SQL Reporting Services to create reports in ASP format. The ASP page will access the database to get data. Is this possible possible with Reporting Services?
Ric
View 2 Replies
View Related
Nov 19, 2005
I have an access table that has a primary key (entitled "ID Number"), no duplicates, the field is an integer.And, importantly, the value is set to "increment".The format is "phd"000 - so it starts out phd001, phd002, and so on...How to do this in an SQL table? Can that format be done? Or is it better not to do it via SQL but in coding instead?
View 1 Replies
View Related
Mar 4, 2015
We are using an old version of Numara TrackIT for our helpdesk software, and it doesn't have much in the way of configurable options. There is no way to set validation or formatting on the text fields in the program.
There is a field, WO_TEXT1, Which I would like to be formatted as 6 characters, 3 integers + a period + 2 integers. The first the integers would be padded with zeros on the left, and the last 2 integers would be padded with zeros on the right.
IE, if someone enters 2, it would actually end up being 002.00
If someone enters 3.5 it would end up being 003.50
If someone enters 12.1 it would end up being 012.10
If someone enters 172.80 it would end up being 172.80
I was hoping to achieve this via an update trigger.
Below is the guts of the trigger I created, mostly as a proof of concept.
-- This update properly formats the Estimated Hours field
Update t SET WO_TEXT1 = (SELECT RIGHT('000000' + CONVERT(VARCHAR(6), WO_TEXT1), 6) FROM inserted)
FROM dbo.TASKS as t
Where (EXISTS (SELECT * FROM inserted WHERE WOID = 24773));
I expected that this update trigger would only affect the Work Order with a WOID of 24773. Unfortunately, it updated all 21000 work orders in our system, wiping out all of the actual estimated hours that had been inserted by technicians!
Luckily I had a report that I could quick dump the 300 or so active work order's estimated hours back into the DB from (all the other Work orders are closed, and no one really cares about their estimated hours).
My question is three fold,
1) Why did my trigger update every record in the tasks table instead of just WO 24773?
2) Is using a trigger the best way of accomplishing what I'm trying to do?
3) if a trigger is the best way of accomplishing this, what should my trigger look like?
View 12 Replies
View Related
Jul 23, 2005
I would need to create multiple variables with the same format forfuture update, what I did is listing them separately, is there an easyway to combine them in one step? please see query below. Thanks a lot!--Current Query--SELECT cast(0.0 as money) as balance_1,cast(0.0 as money) as balance_2,cast(0.0 as money) as balance_3,cast(0.0 as money) as balance_4,cast(0.0 as money) as balance_5,cast(0.0 as money) as balance_6,cast(0.0 as money) as balance_7,cast(0.0 as money) as balance_8,account_no,XXXINTO table1FROM accountCan I do something like this? This one didn't work.SELECT balance_1 to balance_8 (cast 0.0 as money),account_no,XXXINTO table1FROM account
View 5 Replies
View Related
Feb 24, 2008
Hello,
I need to create about 1,000 (literately) Excel files that each contain 5 tabs. The data being placed on the tabs will always be the same (meaning the columns are static).
I am fairly advanced at Excel VBA so I can write code that does all the following in Excel (looped 1,000 times):
Open an Excel template
Bring data in from the tables
Filter, then copy-paste the appropriate rows into each tab.
Save the new Excel file.
Email the file to appropriate individual (it is a Microsoft Exchange Server).
As I started this in VBA, I thought that I might be able to do it with SSIS. My concern is I need to have the rows formatted (font, border, etc.) and the number of rows change.
My questions are:
Is it possible to format Excel with SSIS?
Can I email the files even if it is not with an SMTP protocol?
Would SSIS process this data faster then Excel?
Does this approach even make sense? Am I better just doing it with VBA?
Thank you for the help.
-Gumbatman
View 4 Replies
View Related
Nov 23, 1999
I'm trying to create a variable for use in a DTS job for yesterday's date in the string format YYYYMMDD for use in pulling yesterday's material transactions from my ERP system. I've got the Query statement created and working fine, but I have to manually go into the DTS job and insert the proper date manually. I'm looking into an Active-X script inserted into the workflow for the job to create a global variable, but I can't seem to come up with anything that works.
Any help or direction would be appreciated.
Thanx in advance!!
Glenn
View 1 Replies
View Related
Dec 12, 2014
I have sample data like 'J.S.xxxx','J.xxxx.'
Want to separate the . in the middle of word with single space and display results in following way.
J. S. xxxx
J. xxxx
Need the code in optimized way.
View 3 Replies
View Related
Feb 20, 2007
Hi,
I want to retrive the values from the database 'northwind' and then i want to store the backup files in "D:/Sample/north_database.bak" format(local machine).
I retrive the database values in .txt,XML format. Now i want to take in .bak format.
give the Suitable solution for this.
Subashini.G
View 4 Replies
View Related
Apr 24, 2007
Hi,
I am looking for tutorials about how to create dts et dtsx files.
Thanks for your help.
Arioule.
View 1 Replies
View Related
Feb 28, 2014
I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.
, CASE
WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))
END AS 'System Verified'
View 3 Replies
View Related
May 5, 2014
declare @xml table (xmldata xml)
insert @xml select
N'<parseObject name="Motel">
<fields>
<field name="vehicleno" fieldType="int" fieldSize="">
[Code] ....
I want to extract data in in table format
ParseObjectName FieldType FieldSizeGrammar
Motel VehicleNo Int NULL div.biz-page-subheader li > span.i-phone-biz_details-wrap
mapbox-icon span.biz-partno[/size]
View 2 Replies
View Related
Apr 27, 2015
I wanted to implement the feature to send the email in HTML format from SQL. Which option will be more easy and less resource consumption? Like
sp_send_dbmail or sp_OAMethod or anything else?
View 2 Replies
View Related
Aug 21, 2015
I need to handle date format "41981".
View 9 Replies
View Related
Oct 7, 2015
Need getting data into XML format as shown in the last code block.The datatypes and table structures are pretty much fixed, but I can re-hash data into another Temp Table, CTE, etc..This is a server running SQL Server 2012, but I'd guess any version that understands FOR XML PATH should be fine.
Source tables and data
if object_id('Tempdb..#Element1') is not null drop table #Element1;
create table #Element1 (
[Attr1] varchar(10)
,[Attr2] varchar(4)
[code]...
View 9 Replies
View Related
Sep 5, 2006
I have used the copy database wizard, but I realized I had forgotten to shrink the transaction log file. So I canceled the wizard. My database, detached by the wizard, has now disappeared. The mdf file is still there, but when I try to attach it manually I get the "create file encountered operating system error 5 while attempting to open the physical file..." error.
Any way I can recover it?
Thanks.
View 4 Replies
View Related
Jun 25, 2015
what would be the TSQL in trying to create a new table with date-time format ending via a select into like:
select
*
into tblResults_
+
SELECT
CONVERT(VARCHAR(10),GETDATE(),112)
+
'_'
+
REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','_')
from qryResult
View 3 Replies
View Related
Feb 10, 2014
How to convert MM/YYYY to MM/DD/YYYY and also YYYY to 01/01/YYYY.
This column is text column and has many other inputs.
View 4 Replies
View Related
