Fetching data from a Flat file in SQL Server2000

Posted - 11/03/2006 : 08:00:37 AM
----
Hi,
I am new to SQL Server 2000.
I have to write a stored procedure that fetches some of the fields from
two flat files.
Here is my requirement...
There is a flat file by name "File1", Here i have to Fetch all the
Document ids (first 20 characters) from this file.
Then join this file using trim(Document id) with the below file's
Document id (column 17 to 28) to get the following fields:
"File2" - fetch the following fields (by position)
Memberid (column 899 to 908)
Received Date (column 35 to 42)
Verifier (column 51 to 55)
Join the above result set with the following
condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
following fields:
(where result set is the result obtained by joining the flat files)
Sent (loistatus.loisentdate)
Unit (eligibilityorg.univid)
where Enrollkeys, loistatus, eligibilityorg are the tables in the
datase..
Actually I solved this issue using DTS package, but my DBA is not
allowing me to use DTS package.
He is telling me to Try using Stored Procedure to access the flat files
and getting the values.
And also I should not Use any execute or DDL statement inside the
Stored procedure.
So how can i proceed..
Please reply ASAP..One solution is that you can use openrowset for accessing directly the flat
file in your select query itself.
Amarnath
"Praveen" wrote:
> Posted - 11/03/2006 : 08:00:37 AM
>
> ----
> Hi,
> I am new to SQL Server 2000.
> I have to write a stored procedure that fetches some of the fields from
> two flat files.
> Here is my requirement...
> There is a flat file by name "File1", Here i have to Fetch all the
> Document ids (first 20 characters) from this file.
> Then join this file using trim(Document id) with the below file's
> Document id (column 17 to 28) to get the following fields:
> "File2" - fetch the following fields (by position)
> Memberid (column 899 to 908)
> Received Date (column 35 to 42)
> Verifier (column 51 to 55)
> Join the above result set with the following
> condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
> following fields:
> (where result set is the result obtained by joining the flat files)
> Sent (loistatus.loisentdate)
> Unit (eligibilityorg.univid)
> where Enrollkeys, loistatus, eligibilityorg are the tables in the
> datase..
> Actually I solved this issue using DTS package, but my DBA is not
> allowing me to use DTS package.
> He is telling me to Try using Stored Procedure to access the flat files
> and getting the values.
> And also I should not Use any execute or DDL statement inside the
> Stored procedure.
> So how can i proceed..
> Please reply ASAP..
>

Fatal Network Error 4014 when writing big BLOB chunks

Using the SqlClient provider I'm trying to write big datachunks of maybe 20 MB each to SQL server to store in BLOBs using blobColumn.Write(...) using .NET 2.0 dbcommand object calling a Stored procedure

CREATE PROCEDURE [dbo].[putBlobByPK]

(

@.id dKey

, @.value VARBINARY(MAX)

, @.offset bigint

, @.length bigint

, @.ModDttm dModDttm OUT

, @.ModUser dModUser OUT

, @.ModClient dModClient OUT

, @.ModAppl dModAppl OUT

)

....

When doing this I can do this exactly 3 times than the application hangs (for ever).

When looking in the SQL Server log, I find the following to errors:

Error: 4014, Severity: 20, Status: 2.

A fatal error occurred while reading the input stream from the network. The session will be terminated.

I don't get this error on the client! OK, the session died.

What may be the problem?

I write big chunks like this to avoid many writes as the data shall be replicated later using peer to peer replication. And the more writes used for writing the total BLOB the more huge becomes the transaction log of the subscriber database.

TIA

Hannoman

After rebooting all machines (client and server) this didn't happen any more. And the client has a problem with a deferred installation - when starting the test application MSI installer tries to install something but isn't successfull. It seems, that this causes the hanging of the client machine.sql

FAT32 and Status 112

Howdy - -
Is there some kind of problem backing up to FAT32? We have a HUGE
drive, and we get a write failure (status = 112) even though there is
way more than enough space for the backup.
Any ideas?
- - Herb
FAT32 limits file sizes to 4GB. We had this problem with some filegroups
that filled up
even though the disks had adequate space.
"Herb Ray" <herb@.ad-co.com> wrote in message
news:ukhUe.15$yu1.325681@.news.sisna.com...
> Howdy - -
> Is there some kind of problem backing up to FAT32? We have a HUGE
> drive, and we get a write failure (status = 112) even though there is
> way more than enough space for the backup.
> Any ideas?
> - - Herb
|||Armando Prato wrote:
> FAT32 limits file sizes to 4GB. We had this problem with some filegroups
> that filled up
> even though the disks had adequate space.
> "Herb Ray" <herb@.ad-co.com> wrote in message
> news:ukhUe.15$yu1.325681@.news.sisna.com...
>
>
Armando - -
Thanks for your reply. That sounds like it's our problem.
- - Herb

FAT32 and Status 112

Howdy - -
Is there some kind of problem backing up to FAT32? We have a HUGE
drive, and we get a write failure (status = 112) even though there is
way more than enough space for the backup.
Any ideas?
- - HerbFAT32 limits file sizes to 4GB. We had this problem with some filegroups
that filled up
even though the disks had adequate space.
"Herb Ray" <herb@.ad-co.com> wrote in message
news:ukhUe.15$yu1.325681@.news.sisna.com...
> Howdy - -
> Is there some kind of problem backing up to FAT32? We have a HUGE
> drive, and we get a write failure (status = 112) even though there is
> way more than enough space for the backup.
> Any ideas?
> - - Herb|||Armando Prato wrote:
> FAT32 limits file sizes to 4GB. We had this problem with some filegroups
> that filled up
> even though the disks had adequate space.
> "Herb Ray" <herb@.ad-co.com> wrote in message
> news:ukhUe.15$yu1.325681@.news.sisna.com...
>
>
>
Armando - -
Thanks for your reply. That sounds like it's our problem.
- - Herb

FAT32 and Status 112

Howdy - -
Is there some kind of problem backing up to FAT32? We have a HUGE
drive, and we get a write failure (status = 112) even though there is
way more than enough space for the backup.
Any ideas?
- - HerbFAT32 limits file sizes to 4GB. We had this problem with some filegroups
that filled up
even though the disks had adequate space.
"Herb Ray" <herb@.ad-co.com> wrote in message
news:ukhUe.15$yu1.325681@.news.sisna.com...
> Howdy - -
> Is there some kind of problem backing up to FAT32? We have a HUGE
> drive, and we get a write failure (status = 112) even though there is
> way more than enough space for the backup.
> Any ideas?
> - - Herb|||Armando Prato wrote:
> FAT32 limits file sizes to 4GB. We had this problem with some filegroups
> that filled up
> even though the disks had adequate space.
> "Herb Ray" <herb@.ad-co.com> wrote in message
> news:ukhUe.15$yu1.325681@.news.sisna.com...
>>Howdy - -
>>Is there some kind of problem backing up to FAT32? We have a HUGE
>>drive, and we get a write failure (status = 112) even though there is
>>way more than enough space for the backup.
>>Any ideas?
>>- - Herb
>
>
Armando - -
Thanks for your reply. That sounds like it's our problem.
- - Herb

fastest way to open a query?

Hi.I am writing a program in c++ with ado which has to write 2-3 times
per second in a same field.That`s because i need speed.i have to use
sql unfortunately because our webmaster will take data from here.if i
had chance to choose i would choose berkeley db.Anyway here is a piece
of my code.how should i change the open parameters or should i try
something else?
char query[100];
sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
keyValue); //example keyValue = USDGBP
bstr_t bstrQuery(query);
try {
hr = m_pRecSet->Open(_variant_t(bstrQuery),
vNull,
adOpenForwardOnly,
adLockOptimistic,
adCmdText);
if (!m_pRecSet->GetadoEOF()) {
m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
m_pRecSet->Update(vNull, vNull);
m_pRecSet->Close();
}
}
catch( _com_error &e ) {
TRACE( "Error:%08lx.\n", e.Error());
TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
}Try Select EXCHANGE_ID, MARKET_DATA_BID, MARKET_DATA_ASK FROM ...
That will reduce the amount of data being prepared and should speed it up
marginally.
<ozgecolak@.gmail.com> wrote in message
news:1129299784.233691.240390@.f14g2000cwb.googlegroups.com...
> Hi.I am writing a program in c++ with ado which has to write 2-3 times
> per second in a same field.That`s because i need speed.i have to use
> sql unfortunately because our webmaster will take data from here.if i
> had chance to choose i would choose berkeley db.Anyway here is a piece
> of my code.how should i change the open parameters or should i try
> something else?
> char query[100];
> sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
> keyValue); //example keyValue = USDGBP
> bstr_t bstrQuery(query);
> try {
> hr = m_pRecSet->Open(_variant_t(bstrQuery),
> vNull,
> adOpenForwardOnly,
> adLockOptimistic,
> adCmdText);
> if (!m_pRecSet->GetadoEOF()) {
> m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
> m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
> m_pRecSet->Update(vNull, vNull);
> m_pRecSet->Close();
> }
> }
> catch( _com_error &e ) {
> TRACE( "Error:%08lx.\n", e.Error());
> TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
> TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
> TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
> }
>|||Why bring a recordset down to the client at all? You are not reading the
values. You are making a minuimum of one trip to the server (to get a
recordset) and a maximum of two trips (to update the value if it exists).
Get rid of the recordset and limit this to a maximum of one trip to the
server. The two inefficiencies I see here are using inline sql and the
biggie of using a recordset to perform an update.
I would recommend:
1) Use a stored proc. Let SQl Server compile the execution plan saving it
from having to do it on the fly.
CREATE PROC sp_UpdateTicker @.Bid smallmoney, @.ask smallmoney, @.ID char(10)
AS
SET NOCOUNT ON
UPDATE MarketDate
SET MARKET_DATA_BID = @.Bid, MARKET_DATA_ASK = @.Ask
WHERE EXCHANGE_ID = @.ID
GO
2) Just call the ->Execute method on a connection or command object to run
the proc. Pass along the adExecuteNoRecords enum value to make sure that it
is as efficient as possible.
// Create and Configure the Command Object
pCom.CreateInstance(__uuidof(Command));
pCom->ActiveConnection = pConn;
pCom->CommandType = adCmdStoredProc ;
pCom->CommandText = _bstr_t("dbo.sp_UpdateTicker ");
// Append Parameters
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Bid"), adCurrency,
adParamInput, 8, _variant_t(bidValue)));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Ask"), adCurrency,
adParamInput, 8, _variant_t(askValue)));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.ID"), adChar,
adParamInput, 10, s));
// Execute the Command
pCom->Execute(NULL, NULL, adCmdStoredProc, adExecuteNoRecords);
Even if you do not have permission on the database to create a proc, at
least get away from the recordset and pass your inline sql in the
pCom->Execute(NULL, NULL, adCmdText, adExecuteNoRecords);
HTH,
John Scragg
"ozgecolak@.gmail.com" wrote:

> Hi.I am writing a program in c++ with ado which has to write 2-3 times
> per second in a same field.That`s because i need speed.i have to use
> sql unfortunately because our webmaster will take data from here.if i
> had chance to choose i would choose berkeley db.Anyway here is a piece
> of my code.how should i change the open parameters or should i try
> something else?
> char query[100];
> sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
> keyValue); //example keyValue = USDGBP
> bstr_t bstrQuery(query);
> try {
> hr = m_pRecSet->Open(_variant_t(bstrQuery),
> vNull,
> adOpenForwardOnly,
> adLockOptimistic,
> adCmdText);
> if (!m_pRecSet->GetadoEOF()) {
> m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
> m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
> m_pRecSet->Update(vNull, vNull);
> m_pRecSet->Close();
> }
> }
> catch( _com_error &e ) {
> TRACE( "Error:%08lx.\n", e.Error());
> TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
> TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
> TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
> }
>|||thanks.now it`s faster but not as fast as i need.
John Scragg yazdi:
> Why bring a recordset down to the client at all? You are not reading the
> values. You are making a minuimum of one trip to the server (to get a
> recordset) and a maximum of two trips (to update the value if it exists).
> Get rid of the recordset and limit this to a maximum of one trip to the
> server. The two inefficiencies I see here are using inline sql and the
> biggie of using a recordset to perform an update.
> I would recommend:
> 1) Use a stored proc. Let SQl Server compile the execution plan saving it
> from having to do it on the fly.
> CREATE PROC sp_UpdateTicker @.Bid smallmoney, @.ask smallmoney, @.ID char(10)
> AS
> SET NOCOUNT ON
> UPDATE MarketDate
> SET MARKET_DATA_BID = @.Bid, MARKET_DATA_ASK = @.Ask
> WHERE EXCHANGE_ID = @.ID
> GO
> 2) Just call the ->Execute method on a connection or command object to run
> the proc. Pass along the adExecuteNoRecords enum value to make sure that
it
> is as efficient as possible.
> // Create and Configure the Command Object
> pCom.CreateInstance(__uuidof(Command));
> pCom->ActiveConnection = pConn;
> pCom->CommandType = adCmdStoredProc ;
> pCom->CommandText = _bstr_t("dbo.sp_UpdateTicker ");
> // Append Parameters
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Bid"), adCurrency
,
> adParamInput, 8, _variant_t(bidValue)));
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Ask"), adCurrency
,
> adParamInput, 8, _variant_t(askValue)));
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.ID"), adChar,
> adParamInput, 10, s));
> // Execute the Command
> pCom->Execute(NULL, NULL, adCmdStoredProc, adExecuteNoRecords);
> Even if you do not have permission on the database to create a proc, at
> least get away from the recordset and pass your inline sql in the
> pCom->Execute(NULL, NULL, adCmdText, adExecuteNoRecords);
> HTH,
> John Scragg
>
> "ozgecolak@.gmail.com" wrote:
>|||I think you need to test the individual parts. This code takes only
miliseconds to run for me.
Here is a tip. If you do this inside a loop. Declare the command, set its
properties and create all parameters OUTSIDE of the loop. Then just
repeatedly assign the bid, ask and ID values to the parameters inside the
loop and call the execute inside the loop. This will save you any repeated
object creation overhead.
HTH
John
"ozgecolak@.gmail.com" wrote:

> thanks.now it`s faster but not as fast as i need.
> John Scragg yazdi:
>

Faster database

I have a database called MySessions that holds some volatile information for
web sessions.
This database should be configured for fastest read/write operations.I don't
need to back it up (In a catrastrophic failure I just need to fix the
structure, and the data loss is not important).
Which settings should I set? Which recovery model should I use?
Kyle.
SQL Server always operate with full integrity, so there no setting which will reduce integrity of
data and hence potentially increase performance. Simple recovery mode will not reduce amount of work
done for you operations, it will only automatically re-use space in the transaction log files
(instead of waiting for you to perform a log backup). The only exception to this is for
create/rebuild/drop index, SELECT INTO and bulk loading data (under certain circumstances) which can
go in minimally logged mode if simple recovery mode.
So except for that last exception, the only thing you can do is to work at the physical level, like
adventuring the data by using RAID 0 and stuff like that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Bush" <kyle@.bush.com> wrote in message news:eqwPB01IGHA.2900@.TK2MSFTNGP14.phx.gbl...
>I have a database called MySessions that holds some volatile information for web sessions.
> This database should be configured for fastest read/write operations.I don't need to back it up
> (In a catrastrophic failure I just need to fix the structure, and the data loss is not important).
> Which settings should I set? Which recovery model should I use?
> Kyle.
>

Faster database

I have a database called MySessions that holds some volatile information for
web sessions.
This database should be configured for fastest read/write operations.I don't
need to back it up (In a catrastrophic failure I just need to fix the
structure, and the data loss is not important).
Which settings should I set? Which recovery model should I use?
Kyle.SQL Server always operate with full integrity, so there no setting which will reduce integrity of
data and hence potentially increase performance. Simple recovery mode will not reduce amount of work
done for you operations, it will only automatically re-use space in the transaction log files
(instead of waiting for you to perform a log backup). The only exception to this is for
create/rebuild/drop index, SELECT INTO and bulk loading data (under certain circumstances) which can
go in minimally logged mode if simple recovery mode.
So except for that last exception, the only thing you can do is to work at the physical level, like
adventuring the data by using RAID 0 and stuff like that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Bush" <kyle@.bush.com> wrote in message news:eqwPB01IGHA.2900@.TK2MSFTNGP14.phx.gbl...
>I have a database called MySessions that holds some volatile information for web sessions.
> This database should be configured for fastest read/write operations.I don't need to back it up
> (In a catrastrophic failure I just need to fix the structure, and the data loss is not important).
> Which settings should I set? Which recovery model should I use?
> Kyle.
>

Faster database

I have a database called MySessions that holds some volatile information for
web sessions.
This database should be configured for fastest read/write operations.I don't
need to back it up (In a catrastrophic failure I just need to fix the
structure, and the data loss is not important).
Which settings should I set? Which recovery model should I use?
Kyle.SQL Server always operate with full integrity, so there no setting which wil
l reduce integrity of
data and hence potentially increase performance. Simple recovery mode will n
ot reduce amount of work
done for you operations, it will only automatically re-use space in the tran
saction log files
(instead of waiting for you to perform a log backup). The only exception to
this is for
create/rebuild/drop index, SELECT INTO and bulk loading data (under certain
circumstances) which can
go in minimally logged mode if simple recovery mode.
So except for that last exception, the only thing you can do is to work at t
he physical level, like
adventuring the data by using RAID 0 and stuff like that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Bush" <kyle@.bush.com> wrote in message news:eqwPB01IGHA.2900@.TK2MSFTNGP14.phx.gbl...[v
bcol=seagreen]
>I have a database called MySessions that holds some volatile information fo
r web sessions.
> This database should be configured for fastest read/write operations.I don
't need to back it up
> (In a catrastrophic failure I just need to fix the structure, and the data
loss is not important).
> Which settings should I set? Which recovery model should I use?
> Kyle.
>[/vbcol]