help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.
Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into sql server 2000 relational tables. The database has simple recovery model.

The total size of data to be load is about 1gb (each file)

source:

Multiple source files with different layout.

Destination:

3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3 column unique index (alternate key) and a detail table that has a DETAIL_ID (IDENTITY) primary key and a foreign key ID to the parent table.

So, for each source file, I need to convert some columns to decimal and separate the data into parent and detail tables.

Here is one way to do this:

For each source file

* use a thread for each file with Microsoft Visual Studio .NET 2003

* run insert query with SP's por each record, and join parent table with details.

* process next record

My app works fine in XP and SQL express, When I run the queries the CPU utilization is consitantly around 90%. This is my development environment.

My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.

When I run the queries the CPU utilization is consitantly around 3%.

I have 2 Questions:

1- Is there a better way to do this?

2- Why XP has utilization around 90%, and Win2003 3%?

Speed is the primary concern.

Thank you for any suggestions.

Using SPs calls is not the fastest way. The quickest way is to generate files including the identity values and then bcp/.bulk insert the data into SQL. You could use SSIS to do this as well.

The reason you should generate you ID values outside of the database is so that when spliting your input file into your tables you can just insert the data straight into the tables. You can let SQL generate the PK identity values of the detail tables.

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp
1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a
3
> column unique index (alternate key) and a detail table that has a DETAIL_I
D
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition
Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

help forms

The below stored procedure works. However, I am trying to use a text box
from a temp form for the where clause.
WHERE Transactions.TransactionID = [forms]![form1]![text0]
I even tried changing first line:
Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
And changed last line to:
WHERE Transactions.TransactionID = @.TID
So far, I haven't been able to have this parameter work from a temp form
with the parameter typed into the text0 box.
any help here? I get ado error near "!" or something to that affect.

this below is what does work fine:

Alter PROCEDURE S3 @.TID varchar (255)
AS
SELECT Transactions.TransactionID, Transactions.AccountID,
Transactions.TransactionNumber, Transactions.TransactionDate,
Transactions.TransactionDescription, Transactions.WithdrawalAmount,
Transactions.DepositAmount
FROM Transactions
WHERE Transactions.TransactionID = @.TID"JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
news:sHfGc.196809$Gx4.122482@.bgtnsc04-news.ops.worldnet.att.net...
> The below stored procedure works. However, I am trying to use a text box
> from a temp form for the where clause.
> WHERE Transactions.TransactionID = [forms]![form1]![text0]
> I even tried changing first line:
> Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
> And changed last line to:
> WHERE Transactions.TransactionID = @.TID
> So far, I haven't been able to have this parameter work from a temp form
> with the parameter typed into the text0 box.
> any help here? I get ado error near "!" or something to that affect.
> this below is what does work fine:
> Alter PROCEDURE S3 @.TID varchar (255)
> AS
> SELECT Transactions.TransactionID, Transactions.AccountID,
> Transactions.TransactionNumber, Transactions.TransactionDate,
> Transactions.TransactionDescription, Transactions.WithdrawalAmount,
> Transactions.DepositAmount
> FROM Transactions
> WHERE Transactions.TransactionID = @.TID

SQL Server is a pure server application - it doesn't know anything about
forms or other presentation of data. You need to capture the parameter value
in your client application (Access?), then pass it to the stored procedure
using a client library such as ADO. If you're unsure how to go about this,
you might want to post in a forum for whichever client application you have.

Simon|||I thought sql server was about sql. You're saying there's no way to pass
data from a form to it as a parameter?
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40e99857$1_2@.news.bluewin.ch...
> "JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
> news:sHfGc.196809$Gx4.122482@.bgtnsc04-news.ops.worldnet.att.net...
> > The below stored procedure works. However, I am trying to use a text
box
> > from a temp form for the where clause.
> > WHERE Transactions.TransactionID = [forms]![form1]![text0]
> > I even tried changing first line:
> > Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
> > And changed last line to:
> > WHERE Transactions.TransactionID = @.TID
> > So far, I haven't been able to have this parameter work from a temp form
> > with the parameter typed into the text0 box.
> > any help here? I get ado error near "!" or something to that affect.
> > this below is what does work fine:
> > Alter PROCEDURE S3 @.TID varchar (255)
> > AS
> > SELECT Transactions.TransactionID, Transactions.AccountID,
> > Transactions.TransactionNumber, Transactions.TransactionDate,
> > Transactions.TransactionDescription, Transactions.WithdrawalAmount,
> > Transactions.DepositAmount
> > FROM Transactions
> > WHERE Transactions.TransactionID = @.TID
> SQL Server is a pure server application - it doesn't know anything about
> forms or other presentation of data. You need to capture the parameter
value
> in your client application (Access?), then pass it to the stored procedure
> using a client library such as ADO. If you're unsure how to go about this,
> you might want to post in a forum for whichever client application you
have.
> Simon|||JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> I thought sql server was about sql. You're saying there's no way to pass
> data from a form to it as a parameter?

You are right SQL Server is about SQL, but forms are not SQL per se.

The difference between Access and SQL Server is that Access is a one-tier
application. That is, the query language knows about the GUI and can
interact with it directly. This makes it very easy to write application,
but it comes with a price: it does not scale well, and is best suited for
single-user applications. Also, since the application accesses the database
directly, that means a lot of network traffic if the database is on the
other machine.

SQL Server is intended for applications with two or more tiers. That is,
there is a client on one machine, which passes queries or remote procedure
calls to the server which sends data back. Since machine that hosts the
database does not have do care about drawing GUI:s it can concentrate
on managing the data. And since the server program works on the server,
only the data that the user actually requests has to travel the network.

There are of course several ways to pass a parameter from a form to SQL
Server, but the point is that once the parameter reaches SQL Server, SQL
Server has no idea where it came from. It could be from an input form, it
could have been a read from a file, or it could be a constant in the calling
client program.

To talk to SQL Server from a client, there is a whole range of client
libraries to choose from. From Access the most commonly used today is
ADO. But really how you this best in Access, is probably a question
for comp.databases.ms-access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

help- expressions appearing on second page?

i have a list which is hidden at the top of my body and then some text boxes afterwards...this should all fit on the first page, but as soon as one of my textboxes has an expression the rest of the data skips to the second page

Right click the list and select properties. If "insert a page break before this list" or "insert a page break after this list" are checked, then uncheck them.

|||

both are unchecked, it is breaking at any textbox containing an expression....so the first page has the first few textboxes that only have text

|||

please i need help with this, why do text boxes with expressions appear on the second page, but only the ones after my list, if i put one before the list it will display on the first page

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?
Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and
I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help bcp import reports row size too large error

Hi
I want to use bcp tool to export a table from one machine to another
machine. And now the export works fine. But when I want to import the
text file generated by the export process to the target database, bcp
reports an error, and the operation fails. The error is
"Cannot sort a row of size 22416, which is greater than the allowable
maximum of 8094."
I think this error happens because my target table row size is too
big. Because I can not change the schema of target table, I do not how
to resolve it. Anybody can help me? Thanks.Hi
Your row size of 22416 is far more than 8094, therefore I would expect that
the row terminator is not being specified correctly. What command did you use
to BCP out the file and how are you trying to load it?
Are the versions of SQL Server on each machine the same or different?
John
"Hailin.Cai@.gmail.com" wrote:
> Hi
> I want to use bcp tool to export a table from one machine to another
> machine. And now the export works fine. But when I want to import the
> text file generated by the export process to the target database, bcp
> reports an error, and the operation fails. The error is
> "Cannot sort a row of size 22416, which is greater than the allowable
> maximum of 8094."
> I think this error happens because my target table row size is too
> big. Because I can not change the schema of target table, I do not how
> to resolve it. Anybody can help me? Thanks.
>|||If you are using Microsoft's BCP utility to extract the data from the
source database, then I assume the source is SQL Server 2005 and is
using something like VARCHAR(MAX) to allow row size greater than the
normal limit.
If the target table can not be changed you have to decide what is the
proper thing to do with the rows that have the problem. Should they
be discarded? Should the problem column(s) be truncated?
Once you know how you want to deal with the problem, I suggest writing
a VIEW on the source system. The view should truncate the output (if
that is the choice), or skip the rows, whatever you have to do so the
output from the view matches the table on the target system. Then BCP
out from the view, rather than the table.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Sep 2007 16:29:51 -0700, Hailin.Cai@.gmail.com wrote:
>Hi
>I want to use bcp tool to export a table from one machine to another
>machine. And now the export works fine. But when I want to import the
>text file generated by the export process to the target database, bcp
>reports an error, and the operation fails. The error is
>"Cannot sort a row of size 22416, which is greater than the allowable
>maximum of 8094."
>I think this error happens because my target table row size is too
>big. Because I can not change the schema of target table, I do not how
>to resolve it. Anybody can help me? Thanks.|||On Sep 28, 9:42 am, "Roy Harvey (SQL Server MVP)"
<roy_har...@.snet.net> wrote:
> If you are using Microsoft's BCP utility to extract the data from the
> source database, then I assume the source is SQL Server 2005 and is
> using something like VARCHAR(MAX) to allow row size greater than the
> normal limit.
> If the target table can not be changed you have to decide what is the
> proper thing to do with the rows that have the problem. Should they
> be discarded? Should the problem column(s) be truncated?
> Once you know how you want to deal with the problem, I suggest writing
> a VIEW on the source system. The view should truncate the output (if
> that is the choice), or skip the rows, whatever you have to do so the
> output from the view matches the table on the target system. Then BCP
> out from the view, rather than the table.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Sep 2007 16:29:51 -0700, Hailin...@.gmail.com wrote:
> >Hi
> >I want to use bcp tool to export a table from one machine to another
> >machine. And now the export works fine. But when I want to import the
> >text file generated by the export process to the target database, bcp
> >reports an error, and the operation fails. The error is
> >"Cannot sort a row of size 22416, which is greater than the allowable
> >maximum of 8094."
> >I think this error happens because my target table row size is too
> >big. Because I can not change the schema of target table, I do not how
> >to resolve it. Anybody can help me? Thanks.- Hide quoted text -
> - Show quoted text -
Thanks for your suggestion. I will try.

help a newbie

i've been a sql server dba for the last six years, but we're just now
going to implement full text searches so i've got some dumb questions.
1) is the only way to make the fulltext searches realtime, ie document
is indexed immediately after being added to the db, is to enable change
tracking and update index in the background and add a timestamp column
to each table to be indexed?
2) once the change tracking is working, is there any reason to schedule
full or incremental population?
3) my catalog has 4 tables in it. is there any way to enable change
tracking and update index in background for the entire catalog or do i
have to go to each table and enable it?
4) is it better to have one catalog per db or one catalog per table?
5) the population really kills my cpu. my development server has dual
p3 at 500mhz and 1 gig of ram. the test db has about 500mb of
text/image data (pdf, doc, ppt, etc) in it. the population takes about
10 minutes to run and the mssdmn process stays at a minimum of 50% cpu.
is this normal? this goes along with #3 because everytime i go enable
change tracking on a table, it starts a repopulation.
6) what's more important for populating fulltext indexes, cpu speed or
disk speed?
7) people can upload any kind of document into these 4 tables including
jpg, gif, bmp. fulltext tries to index these and generates an error in
eventlog. any way to tell it to not index files of those types?
8) backup and recovery is very important here, as it should be
everywhere. what's the best way to restore a db that has fulltext
indexes? restore from backup and rebuild catalogs or try to restore the
catalog files along with the db?
CH,
See answers to your questions inline below starting with [jtkane].
Regards,
John
"ch" <ch@.dontemailme.com> wrote in message
news:408531A7.E2AF907A@.dontemailme.com...
> i've been a sql server dba for the last six years, but we're just now
> going to implement full text searches so i've got some dumb questions.
> 1) is the only way to make the fulltext searches realtime, ie document
> is indexed immediately after being added to the db, is to enable change
> tracking and update index in the background and add a timestamp column
> to each table to be indexed?
[jtkane] - Yes and assuming you're using SQL Server 2000 as CT and UIiB are
new features in SQL Server 2000. Although adding a timestamp column is not
required for CT and UIiB, but recommended as when you enable a populated FT
Catalog, CT will automaticlly run an Incremental Population to re-sync the
table with the FT Catalog, otherwise a Full Population will be executed.

> 2) once the change tracking is working, is there any reason to schedule
> full or incremental population?
[jtkane] - A Full Population, no, unless you have a need to change the
related noise word file, i.e., add or remove noise words. As for
Incremental, yes, depending upon the amount of change, i.e,
inserts/updates/deletes that affect over 60% of the rows in the FT enabled
table. See SQL Server BOL title "Maintaining Full-Text Indexes" for more
info on this.

> 3) my catalog has 4 tables in it. is there any way to enable change
> tracking and update index in background for the entire catalog or do i
> have to go to each table and enable it?
[jtkane] - CT and UIiB are set at the table level, so you would need to
enable each table. Note, this can be easly done in the Enterprise Manager.
However, considering the answer to number 2 above, and if these are large
(>1 million rows) tables, you should do enable these tables one at a time,
until the re-sync Incremental Population has completed and then enable the
next table...

> 4) is it better to have one catalog per db or one catalog per table?
[jtkane] - The answer to this question is that it depends. See the last
paragraph in SQL Server BOL title "Full-text Search Recommendation" for more
info on this question. Additionally, having mutiple tables in one FT
Catalogs can affect the value of RANK, if you use this column in your
CONTAINSTABLE or FREETEXTTABLE queries.

> 5) the population really kills my cpu. my development server has dual
> p3 at 500mhz and 1 gig of ram. the test db has about 500mb of
> text/image data (pdf, doc, ppt, etc) in it. the population takes about
> 10 minutes to run and the mssdmn process stays at a minimum of 50% cpu.
> is this normal? this goes along with #3 because everytime i go enable
> change tracking on a table, it starts a repopulation.
[jtkane] - Yes. This is is normal and expected during either the "shadow
merge" or "Master Merge" processes that the MSSearch service does to merge
new "word lists" into it's file system and then at the end of this process
or at midnight (controllable via a registry key). This process occurs during
either a Full or Incremental Population and at midnight for CT & UIiB
enabled tables.
For multi-proc servers, you can set the CPU affinity of the MSSearch service
(or any other service) via launching the following at the AT command prompt
as the sysadmin of the machine where SQL Server is installed using the
following syntax:
at <current_time_plus_1_min> /interactive taskmgr.exe
then once the TaskMgr is running, right-click on the MSSearch service and
set the CPU to one CPU and then use sp_configure and set the CPU affinity
for SQL Server to the other CPU for your dual proc server. This will ensure
that the MSSearch CPU usage will not affect your SQL Server processes during
it's high CPU usage periods.

> 6) what's more important for populating fulltext indexes, cpu speed or
> disk speed?
[jtkane] - All are important, depending what you are trying to optmize. See
SQL Server BOL title "Full-text Search Recommendation" for more info on this
issue. Note, the amount of L3 cache on the CPU is also important.

> 7) people can upload any kind of document into these 4 tables including
> jpg, gif, bmp. fulltext tries to index these and generates an error in
> eventlog. any way to tell it to not index files of those types?
[jtkane] - Yes. All errors, warnings and informational events for FTS and
MSSearch are recorded in the server's Appliation event log and not SQL
Server's errorlog files.

> 8) backup and recovery is very important here, as it should be
> everywhere. what's the best way to restore a db that has fulltext
> indexes? restore from backup and rebuild catalogs or try to restore the
> catalog files along with the db?
[jtkane] - Yes, I agree. For more info on backing up and restoring FT
Catalogs, see KB article 240867 (Q240867) "INF: How to Move, Copy, and
Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
Note, SQL Server 2005 (codename Yukon) will fully integrate FT Catalogs into
SQL Server's backup and restore commands.

>
>

HELP - Storing Full Text Catalog to Different Location

Hi,
I am building a database install aplication that among all other
stuff, creates and populates full-text catalogs for my database.
All this is done in VB.net and SQL Server 2003
My application works fine, here is the come sample:
oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.connect(strServer, strUserID, strPassword)
' enable full-text indexing for provided database
oSQLServer.Databases(strDBName).EnableFullTextCata logs()
' create full text catalog and add it to the collection
Dim strFullTextCatalogName As String
strFullTextCatalogName = strCatalogPrefix & "_documenttitle"
oFullTextCatalog = CreateObject("SQLDMO.FullTextCatalog")
oFullTextCatalog.Name = strFullTextCatalogName 'name
oFullTextCatalog.RootPath = strCatalogPath 'location
' create full-text catalog on the server and add it to
' the collection of full-text catalogs
oSQLServer.databases(strDBName).fulltextcatalogs.a dd(oFullTextCatalog)
However, I get an System.Runtime.InteropServices.COMException
exception with additional information as:
Additional information:[Microsoft][ODBC SQL Server Driver][SQL
Server]Access is denied to 'C:\Documents and
Settings\username\MyDocuments', or the path is invalid. Full-text
search was not installed properly.
The path to which access is denied is the value of my variable
strCatalogPath that I assign to my full-text catalog RootPath
property. (see code sample above)
How can I fix this error. Please help.
Any help will be appreciated,
_dino_
Dino,
Well, the error message is clear "Access is denied to 'C:\Documents and
Settings\username\MyDocuments', or the path is invalid."
Did you try using sp_fulltext_catalog and create a FT Catalog using the same
path? or perhaps specifically reference 'C:\Program Files\Microsoft SQL
Server\Mssql\Ftdata' enclosed in double quotes? Do the folders "username"
or "MyDocuments" exist?
SQL Server BOL title "sp_fulltext_catalog" for the path states
"[@.path =] 'root_directory'
Is the root directory (not the complete physical path) for a create action.
root_directory is nvarchar(100) and has a default value of NULL, which
indicates the use of the default location specified at setup. This is the
Ftdata subdirectory in the Mssql directory; for example, C:\Program
Files\Microsoft SQL Server\Mssql\Ftdata. The specified root directory must
reside on a drive on the same computer, consist of more than just the drive
letter, and cannot be a relative path. Network drives, removable drives,
floppy disks, and UNC paths are not supported. Full-text catalogs must be
created on a local hard drive associated with an instance of Microsoft SQL
ServerT.
@.path is valid only when action is create. For actions other than create
(stop, rebuild, and so on), @.path must be NULL or omitted."
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:fs05f1lapksvfr4i0lgot578hubm1te3gj@.4ax.com...
> Hi,
> I am building a database install aplication that among all other
> stuff, creates and populates full-text catalogs for my database.
> All this is done in VB.net and SQL Server 2003
> My application works fine, here is the come sample:
> oSQLServer = CreateObject("SQLDMO.SQLServer")
> oSQLServer.connect(strServer, strUserID, strPassword)
> ' enable full-text indexing for provided database
> oSQLServer.Databases(strDBName).EnableFullTextCata logs()
> ' create full text catalog and add it to the collection
> Dim strFullTextCatalogName As String
> strFullTextCatalogName = strCatalogPrefix & "_documenttitle"
> oFullTextCatalog = CreateObject("SQLDMO.FullTextCatalog")
> oFullTextCatalog.Name = strFullTextCatalogName 'name
> oFullTextCatalog.RootPath = strCatalogPath 'location
>
> ' create full-text catalog on the server and add it to
> ' the collection of full-text catalogs
> oSQLServer.databases(strDBName).fulltextcatalogs.a dd(oFullTextCatalog)
> However, I get an System.Runtime.InteropServices.COMException
> exception with additional information as:
> Additional information:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Access is denied to 'C:\Documents and
> Settings\username\MyDocuments', or the path is invalid. Full-text
> search was not installed properly.
> The path to which access is denied is the value of my variable
> strCatalogPath that I assign to my full-text catalog RootPath
> property. (see code sample above)
> How can I fix this error. Please help.
> Any help will be appreciated,
> _dino_
|||Are you utilizing MSDE or SQL Server Express? Full-Text is not installed or
supported on either.
"Dino Buljubasic" wrote:

> Hi,
> I am building a database install aplication that among all other
> stuff, creates and populates full-text catalogs for my database.
> All this is done in VB.net and SQL Server 2003
> My application works fine, here is the come sample:
> oSQLServer = CreateObject("SQLDMO.SQLServer")
> oSQLServer.connect(strServer, strUserID, strPassword)
> ' enable full-text indexing for provided database
> oSQLServer.Databases(strDBName).EnableFullTextCata logs()
> ' create full text catalog and add it to the collection
> Dim strFullTextCatalogName As String
> strFullTextCatalogName = strCatalogPrefix & "_documenttitle"
> oFullTextCatalog = CreateObject("SQLDMO.FullTextCatalog")
> oFullTextCatalog.Name = strFullTextCatalogName 'name
> oFullTextCatalog.RootPath = strCatalogPath 'location
>
> ' create full-text catalog on the server and add it to
> ' the collection of full-text catalogs
> oSQLServer.databases(strDBName).fulltextcatalogs.a dd(oFullTextCatalog)
> However, I get an System.Runtime.InteropServices.COMException
> exception with additional information as:
> Additional information:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Access is denied to 'C:\Documents and
> Settings\username\MyDocuments', or the path is invalid. Full-text
> search was not installed properly.
> The path to which access is denied is the value of my variable
> strCatalogPath that I assign to my full-text catalog RootPath
> property. (see code sample above)
> How can I fix this error. Please help.
> Any help will be appreciated,
> _dino_
>

help

i have a list which is hidden at the top of my body and then some text boxes afterwards...this should all fit on the first page, but as soon as one of my textboxes has an expression the rest of the data skips to the second page

Right click the list and select properties. If "insert a page break before this list" or "insert a page break after this list" are checked, then uncheck them.

|||

both are unchecked, it is breaking at any textbox containing an expression....so the first page has the first few textboxes that only have text

|||

please i need help with this, why do text boxes with expressions appear on the second page, but only the ones after my list, if i put one before the list it will display on the first page

HELP

Help Me
What Can I do... I have this error when i try DBCC CHECKDB. I try to do
REPAIR FAST, but it send me the same text (in spanish) :
Servidor: mensaje 1101, nivel 17, estado 10, línea 3
No se puede asignar una nueva página para la base de datos 'TEMPDB'. No hay
más páginas disponibles en el grupo de archivos DEFAULT. Puede crear espacio
si quita objetos, archivos adicionales o si permite que el archivo crezca.
Servidor: mensaje 8921, nivel 16, estado 1, línea 1
CHECKTABLE terminado. Detectado error al recopilar hechos. Posiblemente
falta espacio para tempdb o hay una tabla de sistema dañada. Consulte los
errores anteriores.
Resultados de DBCC para 'Ventas0503'.
CHECKDB ha encontrado 0 errores de asignación y 1 errores de consistencia no
asociados a ningún objeto determinado.
CHECKDB ha encontrado 0 errores de asignación y 1 errores de consistencia en
la base de datos 'Ventas0503'.
repair_fast es el nivel de reparación mínimo para los errores encontrados
por DBCC CHECKDB (Ventas0503 repair_fast).Perhaps you could provide the general idea of the error message for those of us in America that only speak English.
>--Original Message--
>Help Me
>What Can I do... I have this error when i try DBCC CHECKDB. I try to do
>REPAIR FAST, but it send me the same text (in spanish) :
>Servidor: mensaje 1101, nivel 17, estado 10, l=EDnea 3
>No se puede asignar una nueva p=E1gina para la base de datos 'TEMPDB'. No hay
>m=E1s p=E1ginas disponibles en el grupo de archivos DEFAULT. Puede crear espacio
>si quita objetos, archivos adicionales o si permite que el archivo crezca.
>Servidor: mensaje 8921, nivel 16, estado 1, l=EDnea 1
>CHECKTABLE terminado. Detectado error al recopilar hechos. Posiblemente
>falta espacio para tempdb o hay una tabla de sistema da=F1ada. Consulte los
>errores anteriores.
>Resultados de DBCC para 'Ventas0503'.
>CHECKDB ha encontrado 0 errores de asignaci=F3n y 1 errores de consistencia no
>asociados a ning=FAn objeto determinado.
>CHECKDB ha encontrado 0 errores de asignaci=F3n y 1 errores de consistencia en
>la base de datos 'Ventas0503'.
>repair_fast es el nivel de reparaci=F3n m=EDnimo para los errores encontrados
>por DBCC CHECKDB (Ventas0503 repair_fast).
>
>.
>|||Sorry... I will try to traduce you.
The msg 1101, level 17, status 10 and line 3 said:
It's not possible to assign a new page for TEMPDB database. There aren't
more available pages at the files group DEFAULT. You can create space if you
drop objects, extra files, or if you permit ttha the file grow.
Then The msg 8921, level 16, status 1, line 1 said:
Error detected. There isn't space for TEMPDB or there are a damage system
table. Consult the errors listed before.
Additionaly, I check TEMPDB and all is normal with it.
Help me please. Thanks in advance.
"chris" <anonymous@.discussions.microsoft.com> escribió en el mensaje
news:0c7301c3a552$84256390$a601280a@.phx.gbl...
Perhaps you could provide the general idea of the error
message for those of us in America that only speak English.
>--Original Message--
>Help Me
>What Can I do... I have this error when i try DBCC
CHECKDB. I try to do
>REPAIR FAST, but it send me the same text (in spanish) :
>Servidor: mensaje 1101, nivel 17, estado 10, línea 3
>No se puede asignar una nueva página para la base de
datos 'TEMPDB'. No hay
>más páginas disponibles en el grupo de archivos DEFAULT.
Puede crear espacio
>si quita objetos, archivos adicionales o si permite que
el archivo crezca.
>Servidor: mensaje 8921, nivel 16, estado 1, línea 1
>CHECKTABLE terminado. Detectado error al recopilar
hechos. Posiblemente
>falta espacio para tempdb o hay una tabla de sistema
dañada. Consulte los
>errores anteriores.
>Resultados de DBCC para 'Ventas0503'.
>CHECKDB ha encontrado 0 errores de asignación y 1 errores
de consistencia no
>asociados a ningún objeto determinado.
>CHECKDB ha encontrado 0 errores de asignación y 1 errores
de consistencia en
>la base de datos 'Ventas0503'.
>repair_fast es el nivel de reparación mínimo para los
errores encontrados
>por DBCC CHECKDB (Ventas0503 repair_fast).
>
>.
>|||I believe that CHECKDB nowadays need working space in tempdb. I recommend
that you expand the size of your tempdb. Btw, why do you want to run the
REPAIR_FAST option?
Also, check out below:
http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Salvador De los Reyes" <sdlreyes@.hotmail.com> wrote in message
news:usIg7TVpDHA.1632@.TK2MSFTNGP10.phx.gbl...
> Sorry... I will try to traduce you.
> The msg 1101, level 17, status 10 and line 3 said:
> It's not possible to assign a new page for TEMPDB database. There aren't
> more available pages at the files group DEFAULT. You can create space if
you
> drop objects, extra files, or if you permit ttha the file grow.
> Then The msg 8921, level 16, status 1, line 1 said:
> Error detected. There isn't space for TEMPDB or there are a damage system
> table. Consult the errors listed before.
> Additionaly, I check TEMPDB and all is normal with it.
> Help me please. Thanks in advance.
> "chris" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> news:0c7301c3a552$84256390$a601280a@.phx.gbl...
> Perhaps you could provide the general idea of the error
> message for those of us in America that only speak English.
> >--Original Message--
> >Help Me
> >
> >What Can I do... I have this error when i try DBCC
> CHECKDB. I try to do
> >REPAIR FAST, but it send me the same text (in spanish) :
> >
> >Servidor: mensaje 1101, nivel 17, estado 10, línea 3
> >No se puede asignar una nueva página para la base de
> datos 'TEMPDB'. No hay
> >más páginas disponibles en el grupo de archivos DEFAULT.
> Puede crear espacio
> >si quita objetos, archivos adicionales o si permite que
> el archivo crezca.
> >Servidor: mensaje 8921, nivel 16, estado 1, línea 1
> >CHECKTABLE terminado. Detectado error al recopilar
> hechos. Posiblemente
> >falta espacio para tempdb o hay una tabla de sistema
> dañada. Consulte los
> >errores anteriores.
> >Resultados de DBCC para 'Ventas0503'.
> >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> de consistencia no
> >asociados a ningún objeto determinado.
> >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> de consistencia en
> >la base de datos 'Ventas0503'.
> >repair_fast es el nivel de reparación mínimo para los
> errores encontrados
> >por DBCC CHECKDB (Ventas0503 repair_fast).
> >
> >
> >.
> >
>|||You can also run DBCC CHECKDB with the ESTIMATEONLY option to see if you
have enough tempdb space or not.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eHAeGfVpDHA.964@.TK2MSFTNGP10.phx.gbl...
> I believe that CHECKDB nowadays need working space in tempdb. I recommend
> that you expand the size of your tempdb. Btw, why do you want to run the
> REPAIR_FAST option?
> Also, check out below:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Salvador De los Reyes" <sdlreyes@.hotmail.com> wrote in message
> news:usIg7TVpDHA.1632@.TK2MSFTNGP10.phx.gbl...
> >
> > Sorry... I will try to traduce you.
> >
> > The msg 1101, level 17, status 10 and line 3 said:
> > It's not possible to assign a new page for TEMPDB database. There aren't
> > more available pages at the files group DEFAULT. You can create space if
> you
> > drop objects, extra files, or if you permit ttha the file grow.
> >
> > Then The msg 8921, level 16, status 1, line 1 said:
> > Error detected. There isn't space for TEMPDB or there are a damage
system
> > table. Consult the errors listed before.
> >
> > Additionaly, I check TEMPDB and all is normal with it.
> >
> > Help me please. Thanks in advance.
> >
> > "chris" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> > news:0c7301c3a552$84256390$a601280a@.phx.gbl...
> > Perhaps you could provide the general idea of the error
> > message for those of us in America that only speak English.
> >
> > >--Original Message--
> > >Help Me
> > >
> > >What Can I do... I have this error when i try DBCC
> > CHECKDB. I try to do
> > >REPAIR FAST, but it send me the same text (in spanish) :
> > >
> > >Servidor: mensaje 1101, nivel 17, estado 10, línea 3
> > >No se puede asignar una nueva página para la base de
> > datos 'TEMPDB'. No hay
> > >más páginas disponibles en el grupo de archivos DEFAULT.
> > Puede crear espacio
> > >si quita objetos, archivos adicionales o si permite que
> > el archivo crezca.
> > >Servidor: mensaje 8921, nivel 16, estado 1, línea 1
> > >CHECKTABLE terminado. Detectado error al recopilar
> > hechos. Posiblemente
> > >falta espacio para tempdb o hay una tabla de sistema
> > dañada. Consulte los
> > >errores anteriores.
> > >Resultados de DBCC para 'Ventas0503'.
> > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > de consistencia no
> > >asociados a ningún objeto determinado.
> > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > de consistencia en
> > >la base de datos 'Ventas0503'.
> > >repair_fast es el nivel de reparación mínimo para los
> > errores encontrados
> > >por DBCC CHECKDB (Ventas0503 repair_fast).
> > >
> > >
> > >.
> > >
> >
> >
>|||Good one, Andrew. I totally forgot about that... :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eRMYD9VpDHA.392@.TK2MSFTNGP11.phx.gbl...
> You can also run DBCC CHECKDB with the ESTIMATEONLY option to see if you
> have enough tempdb space or not.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eHAeGfVpDHA.964@.TK2MSFTNGP10.phx.gbl...
> > I believe that CHECKDB nowadays need working space in tempdb. I
recommend
> > that you expand the size of your tempdb. Btw, why do you want to run the
> > REPAIR_FAST option?
> >
> > Also, check out below:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Salvador De los Reyes" <sdlreyes@.hotmail.com> wrote in message
> > news:usIg7TVpDHA.1632@.TK2MSFTNGP10.phx.gbl...
> > >
> > > Sorry... I will try to traduce you.
> > >
> > > The msg 1101, level 17, status 10 and line 3 said:
> > > It's not possible to assign a new page for TEMPDB database. There
aren't
> > > more available pages at the files group DEFAULT. You can create space
if
> > you
> > > drop objects, extra files, or if you permit ttha the file grow.
> > >
> > > Then The msg 8921, level 16, status 1, line 1 said:
> > > Error detected. There isn't space for TEMPDB or there are a damage
> system
> > > table. Consult the errors listed before.
> > >
> > > Additionaly, I check TEMPDB and all is normal with it.
> > >
> > > Help me please. Thanks in advance.
> > >
> > > "chris" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> > > news:0c7301c3a552$84256390$a601280a@.phx.gbl...
> > > Perhaps you could provide the general idea of the error
> > > message for those of us in America that only speak English.
> > >
> > > >--Original Message--
> > > >Help Me
> > > >
> > > >What Can I do... I have this error when i try DBCC
> > > CHECKDB. I try to do
> > > >REPAIR FAST, but it send me the same text (in spanish) :
> > > >
> > > >Servidor: mensaje 1101, nivel 17, estado 10, línea 3
> > > >No se puede asignar una nueva página para la base de
> > > datos 'TEMPDB'. No hay
> > > >más páginas disponibles en el grupo de archivos DEFAULT.
> > > Puede crear espacio
> > > >si quita objetos, archivos adicionales o si permite que
> > > el archivo crezca.
> > > >Servidor: mensaje 8921, nivel 16, estado 1, línea 1
> > > >CHECKTABLE terminado. Detectado error al recopilar
> > > hechos. Posiblemente
> > > >falta espacio para tempdb o hay una tabla de sistema
> > > dañada. Consulte los
> > > >errores anteriores.
> > > >Resultados de DBCC para 'Ventas0503'.
> > > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > > de consistencia no
> > > >asociados a ningún objeto determinado.
> > > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > > de consistencia en
> > > >la base de datos 'Ventas0503'.
> > > >repair_fast es el nivel de reparación mínimo para los
> > > errores encontrados
> > > >por DBCC CHECKDB (Ventas0503 repair_fast).
> > > >
> > > >
> > > >.
> > > >
> > >
> > >
> >
> >
>|||How is that ?
Dbcc checkdb ('NameDB',ESTIMATEONLY) '
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribió en el mensaje
news:eRMYD9VpDHA.392@.TK2MSFTNGP11.phx.gbl...
> You can also run DBCC CHECKDB with the ESTIMATEONLY option to see if you
> have enough tempdb space or not.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eHAeGfVpDHA.964@.TK2MSFTNGP10.phx.gbl...
> > I believe that CHECKDB nowadays need working space in tempdb. I
recommend
> > that you expand the size of your tempdb. Btw, why do you want to run the
> > REPAIR_FAST option?
> >
> > Also, check out below:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Salvador De los Reyes" <sdlreyes@.hotmail.com> wrote in message
> > news:usIg7TVpDHA.1632@.TK2MSFTNGP10.phx.gbl...
> > >
> > > Sorry... I will try to traduce you.
> > >
> > > The msg 1101, level 17, status 10 and line 3 said:
> > > It's not possible to assign a new page for TEMPDB database. There
aren't
> > > more available pages at the files group DEFAULT. You can create space
if
> > you
> > > drop objects, extra files, or if you permit ttha the file grow.
> > >
> > > Then The msg 8921, level 16, status 1, line 1 said:
> > > Error detected. There isn't space for TEMPDB or there are a damage
> system
> > > table. Consult the errors listed before.
> > >
> > > Additionaly, I check TEMPDB and all is normal with it.
> > >
> > > Help me please. Thanks in advance.
> > >
> > > "chris" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> > > news:0c7301c3a552$84256390$a601280a@.phx.gbl...
> > > Perhaps you could provide the general idea of the error
> > > message for those of us in America that only speak English.
> > >
> > > >--Original Message--
> > > >Help Me
> > > >
> > > >What Can I do... I have this error when i try DBCC
> > > CHECKDB. I try to do
> > > >REPAIR FAST, but it send me the same text (in spanish) :
> > > >
> > > >Servidor: mensaje 1101, nivel 17, estado 10, línea 3
> > > >No se puede asignar una nueva página para la base de
> > > datos 'TEMPDB'. No hay
> > > >más páginas disponibles en el grupo de archivos DEFAULT.
> > > Puede crear espacio
> > > >si quita objetos, archivos adicionales o si permite que
> > > el archivo crezca.
> > > >Servidor: mensaje 8921, nivel 16, estado 1, línea 1
> > > >CHECKTABLE terminado. Detectado error al recopilar
> > > hechos. Posiblemente
> > > >falta espacio para tempdb o hay una tabla de sistema
> > > dañada. Consulte los
> > > >errores anteriores.
> > > >Resultados de DBCC para 'Ventas0503'.
> > > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > > de consistencia no
> > > >asociados a ningún objeto determinado.
> > > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > > de consistencia en
> > > >la base de datos 'Ventas0503'.
> > > >repair_fast es el nivel de reparación mínimo para los
> > > errores encontrados
> > > >por DBCC CHECKDB (Ventas0503 repair_fast).
> > > >
> > > >
> > > >.
> > > >
> > >
> > >
> >
> >
>|||Because is suggested by DBCC CHECKDB.
Thks
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
escribió en el mensaje news:eHAeGfVpDHA.964@.TK2MSFTNGP10.phx.gbl...
> I believe that CHECKDB nowadays need working space in tempdb. I recommend
> that you expand the size of your tempdb. Btw, why do you want to run the
> REPAIR_FAST option?
> Also, check out below:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Salvador De los Reyes" <sdlreyes@.hotmail.com> wrote in message
> news:usIg7TVpDHA.1632@.TK2MSFTNGP10.phx.gbl...
> >
> > Sorry... I will try to traduce you.
> >
> > The msg 1101, level 17, status 10 and line 3 said:
> > It's not possible to assign a new page for TEMPDB database. There aren't
> > more available pages at the files group DEFAULT. You can create space if
> you
> > drop objects, extra files, or if you permit ttha the file grow.
> >
> > Then The msg 8921, level 16, status 1, line 1 said:
> > Error detected. There isn't space for TEMPDB or there are a damage
system
> > table. Consult the errors listed before.
> >
> > Additionaly, I check TEMPDB and all is normal with it.
> >
> > Help me please. Thanks in advance.
> >
> > "chris" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> > news:0c7301c3a552$84256390$a601280a@.phx.gbl...
> > Perhaps you could provide the general idea of the error
> > message for those of us in America that only speak English.
> >
> > >--Original Message--
> > >Help Me
> > >
> > >What Can I do... I have this error when i try DBCC
> > CHECKDB. I try to do
> > >REPAIR FAST, but it send me the same text (in spanish) :
> > >
> > >Servidor: mensaje 1101, nivel 17, estado 10, línea 3
> > >No se puede asignar una nueva página para la base de
> > datos 'TEMPDB'. No hay
> > >más páginas disponibles en el grupo de archivos DEFAULT.
> > Puede crear espacio
> > >si quita objetos, archivos adicionales o si permite que
> > el archivo crezca.
> > >Servidor: mensaje 8921, nivel 16, estado 1, línea 1
> > >CHECKTABLE terminado. Detectado error al recopilar
> > hechos. Posiblemente
> > >falta espacio para tempdb o hay una tabla de sistema
> > dañada. Consulte los
> > >errores anteriores.
> > >Resultados de DBCC para 'Ventas0503'.
> > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > de consistencia no
> > >asociados a ningún objeto determinado.
> > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > de consistencia en
> > >la base de datos 'Ventas0503'.
> > >repair_fast es el nivel de reparación mínimo para los
> > errores encontrados
> > >por DBCC CHECKDB (Ventas0503 repair_fast).
> > >
> > >
> > >.
> > >
> >
> >
>|||BooksOnLine has the syntax for all the options:
Dbcc checkdb ('NameDB') WITH ESTIMATEONLY
Andrew J. Kelly
SQL Server MVP
"Salvador De los Reyes" <sdlreyes@.hotmail.com> wrote in message
news:e14sAEYpDHA.976@.tk2msftngp13.phx.gbl...
> How is that ?
> Dbcc checkdb ('NameDB',ESTIMATEONLY) '
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribió en el mensaje
> news:eRMYD9VpDHA.392@.TK2MSFTNGP11.phx.gbl...
> > You can also run DBCC CHECKDB with the ESTIMATEONLY option to see if you
> > have enough tempdb space or not.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:eHAeGfVpDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > I believe that CHECKDB nowadays need working space in tempdb. I
> recommend
> > > that you expand the size of your tempdb. Btw, why do you want to run
the
> > > REPAIR_FAST option?
> > >
> > > Also, check out below:
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Salvador De los Reyes" <sdlreyes@.hotmail.com> wrote in message
> > > news:usIg7TVpDHA.1632@.TK2MSFTNGP10.phx.gbl...
> > > >
> > > > Sorry... I will try to traduce you.
> > > >
> > > > The msg 1101, level 17, status 10 and line 3 said:
> > > > It's not possible to assign a new page for TEMPDB database. There
> aren't
> > > > more available pages at the files group DEFAULT. You can create
space
> if
> > > you
> > > > drop objects, extra files, or if you permit ttha the file grow.
> > > >
> > > > Then The msg 8921, level 16, status 1, line 1 said:
> > > > Error detected. There isn't space for TEMPDB or there are a damage
> > system
> > > > table. Consult the errors listed before.
> > > >
> > > > Additionaly, I check TEMPDB and all is normal with it.
> > > >
> > > > Help me please. Thanks in advance.
> > > >
> > > > "chris" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> > > > news:0c7301c3a552$84256390$a601280a@.phx.gbl...
> > > > Perhaps you could provide the general idea of the error
> > > > message for those of us in America that only speak English.
> > > >
> > > > >--Original Message--
> > > > >Help Me
> > > > >
> > > > >What Can I do... I have this error when i try DBCC
> > > > CHECKDB. I try to do
> > > > >REPAIR FAST, but it send me the same text (in spanish) :
> > > > >
> > > > >Servidor: mensaje 1101, nivel 17, estado 10, línea 3
> > > > >No se puede asignar una nueva página para la base de
> > > > datos 'TEMPDB'. No hay
> > > > >más páginas disponibles en el grupo de archivos DEFAULT.
> > > > Puede crear espacio
> > > > >si quita objetos, archivos adicionales o si permite que
> > > > el archivo crezca.
> > > > >Servidor: mensaje 8921, nivel 16, estado 1, línea 1
> > > > >CHECKTABLE terminado. Detectado error al recopilar
> > > > hechos. Posiblemente
> > > > >falta espacio para tempdb o hay una tabla de sistema
> > > > dañada. Consulte los
> > > > >errores anteriores.
> > > > >Resultados de DBCC para 'Ventas0503'.
> > > > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > > > de consistencia no
> > > > >asociados a ningún objeto determinado.
> > > > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > > > de consistencia en
> > > > >la base de datos 'Ventas0503'.
> > > > >repair_fast es el nivel de reparación mínimo para los
> > > > errores encontrados
> > > > >por DBCC CHECKDB (Ventas0503 repair_fast).
> > > > >
> > > > >
> > > > >.
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I always recommend that you do a log backup and then restore the database
including all subsequent log backups into a new database name in these
cases. Chances are that you get zero data loss. Then you can try the repair.
If that doesn't cut it, you have that other database as an option.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Salvador De los Reyes" <sdlreyes@.hotmail.com> wrote in message
news:%23u0bnFYpDHA.1072@.TK2MSFTNGP09.phx.gbl...
> Because is suggested by DBCC CHECKDB.
> Thks
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> escribió en el mensaje news:eHAeGfVpDHA.964@.TK2MSFTNGP10.phx.gbl...
> > I believe that CHECKDB nowadays need working space in tempdb. I
recommend
> > that you expand the size of your tempdb. Btw, why do you want to run the
> > REPAIR_FAST option?
> >
> > Also, check out below:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Salvador De los Reyes" <sdlreyes@.hotmail.com> wrote in message
> > news:usIg7TVpDHA.1632@.TK2MSFTNGP10.phx.gbl...
> > >
> > > Sorry... I will try to traduce you.
> > >
> > > The msg 1101, level 17, status 10 and line 3 said:
> > > It's not possible to assign a new page for TEMPDB database. There
aren't
> > > more available pages at the files group DEFAULT. You can create space
if
> > you
> > > drop objects, extra files, or if you permit ttha the file grow.
> > >
> > > Then The msg 8921, level 16, status 1, line 1 said:
> > > Error detected. There isn't space for TEMPDB or there are a damage
> system
> > > table. Consult the errors listed before.
> > >
> > > Additionaly, I check TEMPDB and all is normal with it.
> > >
> > > Help me please. Thanks in advance.
> > >
> > > "chris" <anonymous@.discussions.microsoft.com> escribió en el mensaje
> > > news:0c7301c3a552$84256390$a601280a@.phx.gbl...
> > > Perhaps you could provide the general idea of the error
> > > message for those of us in America that only speak English.
> > >
> > > >--Original Message--
> > > >Help Me
> > > >
> > > >What Can I do... I have this error when i try DBCC
> > > CHECKDB. I try to do
> > > >REPAIR FAST, but it send me the same text (in spanish) :
> > > >
> > > >Servidor: mensaje 1101, nivel 17, estado 10, línea 3
> > > >No se puede asignar una nueva página para la base de
> > > datos 'TEMPDB'. No hay
> > > >más páginas disponibles en el grupo de archivos DEFAULT.
> > > Puede crear espacio
> > > >si quita objetos, archivos adicionales o si permite que
> > > el archivo crezca.
> > > >Servidor: mensaje 8921, nivel 16, estado 1, línea 1
> > > >CHECKTABLE terminado. Detectado error al recopilar
> > > hechos. Posiblemente
> > > >falta espacio para tempdb o hay una tabla de sistema
> > > dañada. Consulte los
> > > >errores anteriores.
> > > >Resultados de DBCC para 'Ventas0503'.
> > > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > > de consistencia no
> > > >asociados a ningún objeto determinado.
> > > >CHECKDB ha encontrado 0 errores de asignación y 1 errores
> > > de consistencia en
> > > >la base de datos 'Ventas0503'.
> > > >repair_fast es el nivel de reparación mínimo para los
> > > errores encontrados
> > > >por DBCC CHECKDB (Ventas0503 repair_fast).
> > > >
> > > >
> > > >.
> > > >
> > >
> > >
> >
> >
>