Load data from .DAT file

Hi All,

I am using Bulk Insert task to laod data from .dat file to SQL table but getting an error below.

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.".

Any help will be appreciated.

Thanks.

Check out http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=146250&SiteID=1

Some possible solutions are discussed there.

Thanks,
Loonysan

|||

I've tried those solutions but no luck.

There is another .dat file(much bigger) for different table which works fine.......I have setup the same parameters on both files...one works other fails.

|||have you tried to convert the file to another format using excel?|||Try using the OLE DB stage.|||can the ole db destination handle .dat files? i didn't see any mention of that in the documentation.|||

I found the solution. I had a Format file which I fixed it especially for decimal data types. After fixing the format file it ran pretty smooth and fast :)

Thanks everyone who replied.

|||i have the same problem like you, but i don't have solve, why don't you post your solution,
and how to get data from .dat file using by sql statement
thanks for your help
any supporter can help me, can you show me the detail waysql

Load data from .DAT file

Hi All,

I am using Bulk Insert task to laod data from .dat file to SQL table but getting an error below.

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.".

Any help will be appreciated.

Thanks.

Check out http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=146250&SiteID=1

Some possible solutions are discussed there.

Thanks,
Loonysan

|||

I've tried those solutions but no luck.

There is another .dat file(much bigger) for different table which works fine.......I have setup the same parameters on both files...one works other fails.

|||have you tried to convert the file to another format using excel?|||Try using the OLE DB stage.|||can the ole db destination handle .dat files? i didn't see any mention of that in the documentation.|||

I found the solution. I had a Format file which I fixed it especially for decimal data types. After fixing the format file it ran pretty smooth and fast :)

Thanks everyone who replied.

|||i have the same problem like you, but i don't have solve, why don't you post your solution,
and how to get data from .dat file using by sql statement
thanks for your help
any supporter can help me, can you show me the detail way

Load Balancing

need advice for below scenario

currently am having a active / passive cluster sql 2000 server, due to the amount of transactions am moving to new high end servers with sql 2005 cluster.

incase the new cluster also doesnt stands the load what approach i should use similar to load balancing.

with regards

alby peter

The same options are available that would be available for a non-clustered system. Perhaps you can split your data into two separate sets on two separate instances and use distributed partitioned views (DPVs) or perhaps Peer-to-peer transactional replication to work with the data on each instance. Perhaps data dependent routing (DDR) can be used to have your client connect to the instance that likely has the data you want, and linked servers and DPVs can be used to access data that's on the other instance (or split across the two instances).

Don

|||

Dear Don,

thanks for the response.

i read about the above solutions is this type of real time replication in practice for real time servers. in peer to peer transactional replication how many servers could be idle for the best result.

thanks in advance

alby

|||

Replication does have some amount of latency, and though it might only be sub-second, you do need to evaluate your real-time requirements. DPVs access the actual data in its original location so there's no latency there.

I'm not sure what you mean by 'how many servers should be idle' .. I'd think you'd distribute the data across as many instances as are appropriate to meet your retrieval requirements for the given hardware. If your idle servers are 'passive nodes' within a failover cluster, then that depends on the reliability of your hardware and how crippled you'd be if two (or more) of your instances had to run on the same node.

Don

Load Balancing

need advice for below scenario

currently am having a active / passive cluster sql 2000 server, due to the amount of transactions am moving to new high end servers with sql 2005 cluster.

incase the new cluster also doesnt stands the load what approach i should use similar to load balancing.

with regards

alby peter

The same options are available that would be available for a non-clustered system. Perhaps you can split your data into two separate sets on two separate instances and use distributed partitioned views (DPVs) or perhaps Peer-to-peer transactional replication to work with the data on each instance. Perhaps data dependent routing (DDR) can be used to have your client connect to the instance that likely has the data you want, and linked servers and DPVs can be used to access data that's on the other instance (or split across the two instances).

Don

|||

Dear Don,

thanks for the response.

i read about the above solutions is this type of real time replication in practice for real time servers. in peer to peer transactional replication how many servers could be idle for the best result.

thanks in advance

alby

|||

Replication does have some amount of latency, and though it might only be sub-second, you do need to evaluate your real-time requirements. DPVs access the actual data in its original location so there's no latency there.

I'm not sure what you mean by 'how many servers should be idle' .. I'd think you'd distribute the data across as many instances as are appropriate to meet your retrieval requirements for the given hardware. If your idle servers are 'passive nodes' within a failover cluster, then that depends on the reliability of your hardware and how crippled you'd be if two (or more) of your instances had to run on the same node.

Don

sql

Load an XML doc from a file

I'm trying to do something similar to the code below, but I want to load the
XML document from a file on disk (ex.: c:\temp\mydoc.xml) instead of pasting
it into my code. Is there a way to do this in MSSQL 2005?
Thank you.
DECLARE @.idoc int
declare @.xmlDocument xml
set @.xmlDocument = N'<?xml version="1.0"?>
<gpx version="1.1"
creator="GMapToGPX 4.14 - http://www.elsewhere.org/GMapToGPX/"
xmlns="http://www.topografix.com/GPX/1/1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd">
<rte>
<name>Gmaps Pedometer Route</name>
<cmt>Permalink: <![CDATA[Permalink temporarily unavailable.]]>
</cmt>
<rtept lat="32.45358" lon="-110.97512">
<name>Start</name>
<ele>901.73556</ele>
</rtept>
<rtept lat="32.4537" lon="-110.97549">
<name>Turn 1</name>
<ele>902.01902</ele>
</rtept>
<rtept lat="32.45377" lon="-110.97594">
<name>Turn 2</name>
<ele>901.95196</ele>
</rtept>
<rtept lat="32.45376" lon="-110.97653">
<name>Turn 3</name>
<ele>902.22324</ele>
</rtept>
</rte>
</gpx>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xmlDocument, '<gpx
xmlns:gpxns="http://www.topografix.com/GPX/1/1"/>'
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML (@.idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',3)
WITH (lat float, lon float, name varchar(60) 'gpxns:name', ele float
'gpxns:ele')
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
Alain Quesnel wrote:

> declare @.xmlDocument xml
> set @.xmlDocument = N'<?xml version="1.0"?>
One way is like this
SET @.xmlDocument = (
SELECT * FROM OPENROWSET(
BULK 'C:\dir\subdir\subdir\file.xml',
SINGLE_BLOB
) AS x
);
then you can use the variable @.xmlDocument as you have done before with
the stored procedure.
See the documentation here:
<http://msdn2.microsoft.com/en-us/library/ms191184.aspx>

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Load an XML doc from a file

I'm trying to do something similar to the code below, but I want to load the
XML document from a file on disk (ex.: c:\temp\mydoc.xml) instead of pasting
it into my code. Is there a way to do this in MSSQL 2005?
Thank you.
DECLARE @.idoc int
declare @.xmlDocument xml
set @.xmlDocument = N'<?xml version="1.0"?>
<gpx version="1.1"
creator="GMapToGPX 4.14 - http://www.elsewhere.org/GMapToGPX/"
xmlns="http://www.topografix.com/GPX/1/1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd">
<rte>
<name>Gmaps Pedometer Route</name>
<cmt>Permalink: <![CDATA[Permalink temporarily unavailable.]]>
</cmt>
<rtept lat="32.45358" lon="-110.97512">
<name>Start</name>
<ele>901.73556</ele>
</rtept>
<rtept lat="32.4537" lon="-110.97549">
<name>Turn 1</name>
<ele>902.01902</ele>
</rtept>
<rtept lat="32.45377" lon="-110.97594">
<name>Turn 2</name>
<ele>901.95196</ele>
</rtept>
<rtept lat="32.45376" lon="-110.97653">
<name>Turn 3</name>
<ele>902.22324</ele>
</rtept>
</rte>
</gpx>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xmlDocument, '<gpx
xmlns:gpxns="http://www.topografix.com/GPX/1/1"/>'
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML (@.idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',3)
WITH (lat float, lon float, name varchar(60) 'gpxns:name', ele float
'gpxns:ele')
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comAlain Quesnel wrote:

> declare @.xmlDocument xml
> set @.xmlDocument = N'<?xml version="1.0"?>
One way is like this
SET @.xmlDocument = (
SELECT * FROM OPENROWSET(
BULK 'C:\dir\subdir\subdir\file.xml',
SINGLE_BLOB
) AS x
);
then you can use the variable @.xmlDocument as you have done before with
the stored procedure.
See the documentation here:
<http://msdn2.microsoft.com/en-us/library/ms191184.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Works like a charm.
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OLcWpOogHHA.2368@.TK2MSFTNGP04.phx.gbl...
> Alain Quesnel wrote:
>
> One way is like this
> SET @.xmlDocument = (
> SELECT * FROM OPENROWSET(
> BULK 'C:\dir\subdir\subdir\file.xml',
> SINGLE_BLOB
> ) AS x
> );
> then you can use the variable @.xmlDocument as you have done before with
> the stored procedure.
> See the documentation here:
> <http://msdn2.microsoft.com/en-us/library/ms191184.aspx>
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

List remote server and directory

Hi
Trying to work on a script which is able to list files on a remote server
and its drive but unsure of the syntax. Can anyone advise as below doesnt
work.
xp_cmdshell 'dir "\\server::d:\"
Thanks
Hi,
The remote server needs to have a share exposed that you can use to get a
directory listing. The syntax would be:
exec master.dbo.xp_cmdshell 'dir \\myserver\d$'
If the path contains blanks then you need to quote the parameter with
double-quotes:
exec master.dbo.xp_cmdshell 'dir "\\myserver\c$\Program Files"'
RLF
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:C2CC72B2-F903-423A-A499-36E639532789@.microsoft.com...
> Hi
> Trying to work on a script which is able to list files on a remote server
> and its drive but unsure of the syntax. Can anyone advise as below doesnt
> work.
> xp_cmdshell 'dir "\\server::d:\"
>
> Thanks

List remote server and directory

Hi
Trying to work on a script which is able to list files on a remote server
and its drive but unsure of the syntax. Can anyone advise as below doesnt
work.
xp_cmdshell 'dir "\\server::d:\"
ThanksHi,
The remote server needs to have a share exposed that you can use to get a
directory listing. The syntax would be:
exec master.dbo.xp_cmdshell 'dir \\myserver\d$'
If the path contains blanks then you need to quote the parameter with
double-quotes:
exec master.dbo.xp_cmdshell 'dir "\\myserver\c$\Program Files"'
RLF
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:C2CC72B2-F903-423A-A499-36E639532789@.microsoft.com...
> Hi
> Trying to work on a script which is able to list files on a remote server
> and its drive but unsure of the syntax. Can anyone advise as below doesnt
> work.
> xp_cmdshell 'dir "\\server::d:\"
>
> Thanks

List remote server and directory

Hi
Trying to work on a script which is able to list files on a remote server
and its drive but unsure of the syntax. Can anyone advise as below doesnt
work.
xp_cmdshell 'dir "\\server::d:\"
ThanksHi,
The remote server needs to have a share exposed that you can use to get a
directory listing. The syntax would be:
exec master.dbo.xp_cmdshell 'dir \\myserver\d$'
If the path contains blanks then you need to quote the parameter with
double-quotes:
exec master.dbo.xp_cmdshell 'dir "\\myserver\c$\Program Files"'
RLF
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:C2CC72B2-F903-423A-A499-36E639532789@.microsoft.com...
> Hi
> Trying to work on a script which is able to list files on a remote server
> and its drive but unsure of the syntax. Can anyone advise as below doesnt
> work.
> xp_cmdshell 'dir "\\server::d:\"
>
> Thanks

List of Tables and Primary Key Foreign Key Names

Dear All,
I am trying to write some SQL that will give a list of all tables and
Primary Key/ Foreign Key constraints in a database. The code below goes part
of the way but not what I would like. It gives me:
Parent Table: Activities
Child Table: ActivitiesLocation
ForeignKey: 1
PrimaryKey: 17
TotalKeys: 1
(where 1 in the column name in the Activities table that is the Foreign Key
and column name 17 is where the Primary Key is to be found.)
Script:
SELECT TOP 100 PERCENT so1.name AS 'Parent Table', so2.name AS 'Child
Table', sf.fkey AS ForeignKey, sf.rkey AS PrimaryKey, sf.keyno AS TotalKeys
FROM dbo.sysforeignkeys sf INNER JOIN
dbo.sysobjects so1 ON so1.id = sf.rkeyid INNER JOIN
dbo.sysobjects so2 ON so2.id = sf.fkeyid
ORDER BY so1.name
How can I get a list of the column name of column no 17 in the Activies
Table and column no 1 in the ActivitiesLocation table. Is there a better way
to get a listing of the PK/FK for a database along with table names?
Thanks again.
Alastair> I am trying to write some SQL that will give a list of all tables and
> Primary Key/ Foreign Key constraints in a database.
http://www.aspfaq.com/search.asp?q=schema%3A|||Check out the information_schema.key_column_usage view. It should get you
started with what you need.
--Brian
(Please reply to the newsgroups only.)
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:9197A5C9-182B-451B-AE40-05DA1F98E62B@.microsoft.com...
> Dear All,
> I am trying to write some SQL that will give a list of all tables and
> Primary Key/ Foreign Key constraints in a database. The code below goes
> part
> of the way but not what I would like. It gives me:
> Parent Table: Activities
> Child Table: ActivitiesLocation
> ForeignKey: 1
> PrimaryKey: 17
> TotalKeys: 1
> (where 1 in the column name in the Activities table that is the Foreign
> Key
> and column name 17 is where the Primary Key is to be found.)
> Script:
> SELECT TOP 100 PERCENT so1.name AS 'Parent Table', so2.name AS 'Child
> Table', sf.fkey AS ForeignKey, sf.rkey AS PrimaryKey, sf.keyno AS
> TotalKeys
> FROM dbo.sysforeignkeys sf INNER JOIN
> dbo.sysobjects so1 ON so1.id = sf.rkeyid INNER JOIN
> dbo.sysobjects so2 ON so2.id = sf.fkeyid
> ORDER BY so1.name
> How can I get a list of the column name of column no 17 in the Activies
> Table and column no 1 in the ActivitiesLocation table. Is there a better
> way
> to get a listing of the PK/FK for a database along with table names?
> Thanks again.
> Alastair
>|||Thanks Aaron and Brian for both your replies. This group is definately on th
e
ball. Exactly what I wanted.
Alastair
"Aaron Bertrand [SQL Server MVP]" wrote:

> http://www.aspfaq.com/search.asp?q=schema%3A
>
>

List Control Properties

I saw this posting below, and I just want to be clear. I have a data
set with one Column, but many rows. I would like the data to display
horizontaly, then wrap to the next line. My data is actually displayed
in through Image controls. Im pulling the images from an external
path, on the web (http://hostname/images/image.jpg)
Am I understanding correctly, I cannot do that? Is there another
control that I can use instead? If there is can someone point me in
the direction on the properties I need to use?
Thanks,
rwiethorn
*************************************************************
From: Brian Welcker [MSFT] (bwelcker@.online.microsoft.com)
Subject: Re: List Control Shortcomings?
View: Complete Thread (2 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.reportingsvcs
Date: 2004-05-31 12:29:15 PST
No. You can make the entire report multiple columns but you can't make
a
list repeat horizontally.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no
rights.
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:374C89FA-582F-4B82-8A8A-C61A4D58D215@.microsoft.com...
> I have a data set bound to a list control. Is there any way to configure the list control to output horizontally aswell as vertially?
>
> e.g. my dataset has 11 rows, can the first 6 be in 1 column, the next 5 in
another column.
----http://blogs.msdn.com/chrishays/archive/2004/07/23/193292.aspx
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"rwiethorn" <rwiethorn2002@.yahoo.com> wrote in message
news:553a0349.0407301045.6cd21b18@.posting.google.com...
> I saw this posting below, and I just want to be clear. I have a data
> set with one Column, but many rows. I would like the data to display
> horizontaly, then wrap to the next line. My data is actually displayed
> in through Image controls. Im pulling the images from an external
> path, on the web (http://hostname/images/image.jpg)
> Am I understanding correctly, I cannot do that? Is there another
> control that I can use instead? If there is can someone point me in
> the direction on the properties I need to use?
> Thanks,
> rwiethorn
> *************************************************************
> From: Brian Welcker [MSFT] (bwelcker@.online.microsoft.com)
> Subject: Re: List Control Shortcomings?
> View: Complete Thread (2 articles)
> Original Format
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Date: 2004-05-31 12:29:15 PST
>
> No. You can make the entire report multiple columns but you can't make
> a
> list repeat horizontally.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Mark" <anonymous@.discussions.microsoft.com> wrote in message
> news:374C89FA-582F-4B82-8A8A-C61A4D58D215@.microsoft.com...
> > I have a data set bound to a list control. Is there any way to configure
the list control to output horizontally aswell as vertially?
> >
> > e.g. my dataset has 11 rows, can the first 6 be in 1 column, the next 5
in
> another column.
>
> ----
--