Hey All,
Thought I'd take a break from Scalar Functions for a while and give Omnibuzz
a break from answering them.
I've searched thru the BOL, and this forum, as well as the help files in
SQL2005. I've gotten to the point where I am exporting the data
correctly..however I am looking for a format..
Here's what I'm attempting.. I need to take certain grabs of data using a
stored procedure.. dump it to an xml file / soap file, and then publish to
another service. for that to happen, i have a specific file format that the
y
have to be in.
So, here's my data...
strike price nominalDate
1000 0.000 2006-04-01
10000 2.850 2006-04-01
10050 0.000 2006-04-01
10150 0.000 2006-04-01
10200 0.000 2006-04-01
Here's the optionformat.xml
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="10"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="10"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="10"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="nominalDate" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="price" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="strike" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
and here's the real format it needs to be in.
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="0.75"/>
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="1.0"/>
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="3.0"/>
Of course, this doesn't include the headers at all, but I'm thinking I can
just add that to the file using the copy command to join the 3 files (copy
txt1.txt + optionformat.xml + txt2.txt uploadfile.xml)
The hardest part is getting it in that format...
Now to have the answer plunked right in front of me would be nice, but I
really need to learn how to do this, and understand the process... The only
other option that i have is to write a small vb program that calls from the
database, formats the data using the FileSystemObject.
If you know of a good resource that explains exporting into formatted xml,
that would be great.
If I'm totally going down the wrong road on this solution, let me know as
well.
Thanks!
~Dan Regalia
--
www.krushradio.com - Internet Radio for the rest of usDaniel Regalia (DanielRegalia@.discussions.microsoft.com) writes:
> Here's what I'm attempting.. I need to take certain grabs of data using
> a stored procedure.. dump it to an xml file / soap file, and then
> publish to another service. for that to happen, i have a specific file
> format that they have to be in.
>...
> Here's the optionformat.xml
><?xml version="1.0"?>
><BCPFORMAT
> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
>...
> and here's the real format it needs to be in.
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="0.75"/>
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="1.0"/>
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="3.0"/>
Ehum, you cannot use a BCP format file to specify an XML format for
the output.
You should probably look into using FOR XML EXPLICIT instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
2012年3月22日星期四
2012年3月11日星期日
bcp import help
I am trying to import data thru a bcp call to pull data from an access database. I am having trouble accessing the access database. Below is the bcp I tried, along with an openrowset attempt. Neither of them are working. Any help would be greatly appreciated.
bcp select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
from Line Report in I:\2007\11\D1107.MDB -q -UXX -PXX
select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'I:\2007\10\D1007.MDB';'XX';'XX', 'Line Report')Are you able to add the mdb as a linked server - you could then simply run SQL statements irectly on the data? I'm afraid I am unable to test this from my current location so I'm not able to check!|||I can add it as a linked server, but then I cant actually query against any of the access tables.
sp_addlinkedserver 'D1107', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'\\ctisvr\stats\2007\11\D1107.MDB'
SELECT *
FROM D1107...Line Report
when I run the select statment I then get:
'OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.]'|||Do you get the same error message for the OPENROWSET query? Also, what version is the Access database created in?
As a side note, you have probably already learned that BCP is used solely for "flat files". Pure ASCII/UNICODE characters.|||Same error. Not sure what version it was created in as I am importing it from a 3rd party vendor.|||Hi.
why not use a DTS package? and test this SELECT *
FROM D1107...[Line Report]|||Did you try the DTS Wizard?
I don't have a full version of Sql Server, only MSDE. So I didn't have the DTS Wizard. Then I looked in an old Office 2000 disk I've got, and found it. I copied over dtswiz.exe and some other .dll and .rll files to my Sql Server\tools\binn folder and it worked.
bcp select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
from Line Report in I:\2007\11\D1107.MDB -q -UXX -PXX
select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'I:\2007\10\D1007.MDB';'XX';'XX', 'Line Report')Are you able to add the mdb as a linked server - you could then simply run SQL statements irectly on the data? I'm afraid I am unable to test this from my current location so I'm not able to check!|||I can add it as a linked server, but then I cant actually query against any of the access tables.
sp_addlinkedserver 'D1107', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'\\ctisvr\stats\2007\11\D1107.MDB'
SELECT *
FROM D1107...Line Report
when I run the select statment I then get:
'OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.]'|||Do you get the same error message for the OPENROWSET query? Also, what version is the Access database created in?
As a side note, you have probably already learned that BCP is used solely for "flat files". Pure ASCII/UNICODE characters.|||Same error. Not sure what version it was created in as I am importing it from a 3rd party vendor.|||Hi.
why not use a DTS package? and test this SELECT *
FROM D1107...[Line Report]|||Did you try the DTS Wizard?
I don't have a full version of Sql Server, only MSDE. So I didn't have the DTS Wizard. Then I looked in an old Office 2000 disk I've got, and found it. I copied over dtswiz.exe and some other .dll and .rll files to my Sql Server\tools\binn folder and it worked.
2012年3月8日星期四
BCP hangs: how to unlock table? without restarting server?
Hi,
Is there anyway to unlock tables that BCP has locked. I
was running the BCP command thru sql analyser, and it hung
and could not cancel it. I Exited sql analyser. Tables
still locked. How do i unlock them.
I think bcp locked a file on the file system as well?
When we go to system activity Enterprise manager, it times
out. If we restart server the the tables are unlocked.
Q1) is there a way to unlock a table without restarting
the SQL server.
We think its being caused by bcp, locking a file on the
system? not sure
Q2) Do anyway have this problem?
Thanks
MIcHi mg,
Please don't post the same question independently in separate newsgroups. I
have answered your question in .programming.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"mg" <dev02@.qleave.qld.gov.au> wrote in message
news:081101c36621$32c60ce0$a601280a@.phx.gbl...
> Hi,
>
> Is there anyway to unlock tables that BCP has locked. I
> was running the BCP command thru sql analyser, and it hung
> and could not cancel it. I Exited sql analyser. Tables
> still locked. How do i unlock them.
> I think bcp locked a file on the file system as well?
> When we go to system activity Enterprise manager, it times
> out. If we restart server the the tables are unlocked.
> Q1) is there a way to unlock a table without restarting
> the SQL server.
> We think its being caused by bcp, locking a file on the
> system? not sure
> Q2) Do anyway have this problem?
> Thanks
> MIc
>|||Thanks Jacco for you help,
BTW. When you say
kill is that a NT command or sql server kill.
>--Original Message--
>Hi mg,
>Please don't post the same question independently in
separate newsgroups. I
>have answered your question in .programming.
>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.
>
>"mg" <dev02@.qleave.qld.gov.au> wrote in message
>news:081101c36621$32c60ce0$a601280a@.phx.gbl...
>> Hi,
>>
>> Is there anyway to unlock tables that BCP has locked. I
>> was running the BCP command thru sql analyser, and it
hung
>> and could not cancel it. I Exited sql analyser. Tables
>> still locked. How do i unlock them.
>> I think bcp locked a file on the file system as well?
>> When we go to system activity Enterprise manager, it
times
>> out. If we restart server the the tables are unlocked.
>> Q1) is there a way to unlock a table without restarting
>> the SQL server.
>> We think its being caused by bcp, locking a file on the
>> system? not sure
>> Q2) Do anyway have this problem?
>> Thanks
>> MIc
>
>.
>
Is there anyway to unlock tables that BCP has locked. I
was running the BCP command thru sql analyser, and it hung
and could not cancel it. I Exited sql analyser. Tables
still locked. How do i unlock them.
I think bcp locked a file on the file system as well?
When we go to system activity Enterprise manager, it times
out. If we restart server the the tables are unlocked.
Q1) is there a way to unlock a table without restarting
the SQL server.
We think its being caused by bcp, locking a file on the
system? not sure
Q2) Do anyway have this problem?
Thanks
MIcHi mg,
Please don't post the same question independently in separate newsgroups. I
have answered your question in .programming.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"mg" <dev02@.qleave.qld.gov.au> wrote in message
news:081101c36621$32c60ce0$a601280a@.phx.gbl...
> Hi,
>
> Is there anyway to unlock tables that BCP has locked. I
> was running the BCP command thru sql analyser, and it hung
> and could not cancel it. I Exited sql analyser. Tables
> still locked. How do i unlock them.
> I think bcp locked a file on the file system as well?
> When we go to system activity Enterprise manager, it times
> out. If we restart server the the tables are unlocked.
> Q1) is there a way to unlock a table without restarting
> the SQL server.
> We think its being caused by bcp, locking a file on the
> system? not sure
> Q2) Do anyway have this problem?
> Thanks
> MIc
>|||Thanks Jacco for you help,
BTW. When you say
kill is that a NT command or sql server kill.
>--Original Message--
>Hi mg,
>Please don't post the same question independently in
separate newsgroups. I
>have answered your question in .programming.
>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.
>
>"mg" <dev02@.qleave.qld.gov.au> wrote in message
>news:081101c36621$32c60ce0$a601280a@.phx.gbl...
>> Hi,
>>
>> Is there anyway to unlock tables that BCP has locked. I
>> was running the BCP command thru sql analyser, and it
hung
>> and could not cancel it. I Exited sql analyser. Tables
>> still locked. How do i unlock them.
>> I think bcp locked a file on the file system as well?
>> When we go to system activity Enterprise manager, it
times
>> out. If we restart server the the tables are unlocked.
>> Q1) is there a way to unlock a table without restarting
>> the SQL server.
>> We think its being caused by bcp, locking a file on the
>> system? not sure
>> Q2) Do anyway have this problem?
>> Thanks
>> MIc
>
>.
>
订阅：
博文 (Atom)