Bulk Updates taking a long time

We have a huge table with around 25 Million records. We want to reset two int Columns of all records to 0. Currently its taking around 1.5 hours... What are the best practises we can follow to reduce the total update time.

Initially we used - Update <TableName> set <Column1>=0, <Column2>=0.
Later we modified the query to include a WHERE clause and did the update in batch mode such as

DECLARE @.maxCount Int
DECLARE @.iCount Int
SELECT @.MaxCount = Max(ID) FROM organizationsource
SET @.iCount = 0
WHILE (@.iCount<@.MaxCount)
BEGIN
UPDATE <tableName> set <Column1> = 0, <Column2>=0
WHERE ID between @.iCount and @.iCount+1000000
SET @.iCount = @.iCount + 1000000
END

Can you please suggest some tips to improve the update performance.
Can we do something at the SQL Server level / are there any settings at the database level for performing faster updates.

Thanks,
Loonysan

Hard to determine based on the information you've provided so far:

Is the ID field the primary index?|||

Thanks for your interest.

To Answer your Questions

1) Yes - ID field is the primary key in my table.
2) This table will not be accessed by other applications during the update process.
3) Yeah - I have the Data and Log files in different drives. (Should I keep them in different disks for better performance)
4) We are using SQL Server 2005 :)
5) The code is resides in a Stored Procedure

Thanks,
Loonysan

|||

Ok.

Are the columns you are updating indexed also?

This can slow down updates. If so, drop the index and recreate after the update has happened.

Also - make sure the following is turned off to improve performance:

auto create statistics

bulk load: no error message, but not loading

this board wont let me post all my data at once, so one at a time...
heres how i call the load
****************************
Function Main()
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=localhost;" &
_
"DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:\myschema", "C:\myfile"
oXMLBulkLoad.SchemaGen = True
Set oXMLBulkLoad = Nothing
Main = DTSTaskExecResult_success
End Function
***************************************
salival
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1331465.htmlill try attaching my schema
+---+
| Attachment filename: q.txt |
|Download attachment: http://www.mcse.ms/attachment.php?postid=3575113 |
+---+
salival
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1331465.html

bulk insertion

hi

hi i have common problem.

which is the best way to insert 1000 rows at a time in sql server 2000.

From where you want to insert?

You can use the following approaches

apporach 1:

Use bcp to backup the data on text file (delimited file) - from soruce server

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Use BULK INSERT to reload the data on the target server

http://msdn2.microsoft.com/en-us/library/ms188365.aspx

approach 2:

Use DTS to load the data from one server to another server

http://msdn2.microsoft.com/en-us/library/aa902640(SQL.80).aspx

approach 3:

Use linked server to get the source data on the target server

http://msdn2.microsoft.com/en-us/library/aa213778(SQL.80).aspx

|||

If the data is in a delimited text file, use bcp; it's quick and flexible.

Read up on [bcp] in Books Online.

Bulk Insert Questions

I'm using Bulk Insert for the first time and have a question.

I'm getting an error message about a field being truncated:
Bulk insert data conversion error (truncation) for row 2, column 12
(Depleted)

The data type for the "Depleted" column is Char(1). Looking at the
file, there is indeed only one character in the column. I'm not sure
how to fix this, or what I can do about it. Any suggestions would be
appreciated.

Thanks!
Jennifer

The table:

CREATE TABLE [dbo].[parSalesDetailTemp] (
[parSalesHdrID] [int] NOT NULL ,
[parSalesDetailID] [int] NOT NULL ,
[Before] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[After] [int] NOT NULL ,
[Promo] [money] NOT NULL ,
[PromoBefore] [money] NOT NULL ,
[ItemPrice] [money] NOT NULL ,
[PromoAfter] [money] NOT NULL ,
[POSItem] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitNumber] [int] NOT NULL ,
[Depleted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON
[PRIMARY]
GO

The SQL:

BULK INSERT parSalesDetailTemp
FROM '\\wbhq.com\dfsdv\iDataInt\TLDFiles\Extract\SalesD tl.csv'
WITH (FIELDTERMINATOR =',')

The file contents (1st few rows):

8032753,37312006,0,1,0,0,0,4.39,0,"WB-ML",2,N
8032753,37312007,0,1,0,0,0,4.39,0,"WB-ML",2,N
8032753,37312008,0,2,0,0,0,.00,0,"ML-M-COK",2,NJennifer (J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

I'm using Bulk Insert for the first time and have a question.
>
I'm getting an error message about a field being truncated:
Bulk insert data conversion error (truncation) for row 2, column 12
(Depleted)
>
The data type for the "Depleted" column is Char(1). Looking at the
file, there is indeed only one character in the column. I'm not sure
how to fix this, or what I can do about it. Any suggestions would be
appreciated.

I was able to successfully insert the sample rows you posted.

I can think of two things:

1) There are trailing blanks.
2) The line terminator is not CR-LF, but only CR or only LF.

Since it was the second line that failed, the first seems more likely to me.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Bulk Insert performance

I have a situation where I need to do multiple inserts into the sql mobile db at one time. I am wondering what would be the most efficient method to do this. Right now I am just doing many inserts, but the performance is lacking. I tried to wrap all the inserts into 1 sql command and process it like that, but it does not seem to want to execute. Any help would be appreciated.

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Darren,

What is the best method to import a large amount of data into SQL Mobile? Are you saying that Batching isn't supported? Also, SQL Server will not be installed on our servers because of licensing costs. So this takes out the replication and RDA methods for imports.

In a C# application, I'm reading ASCII files and need to add to three seperate tables. It takes hours to process 100,000+ records. BTW - I need to support over 2 million records. The total file size will be around 1 gig. Since SQL CE supports files up to 4 gig, I'm not expecting this to be a problem. I'm I right here?

I'm creating an Item Lookup function that will run stand alone in our retail chain. The data (SDF file) The data will be refreshed nightly on the server and will be copied down to the device daily. If you have any suggestions you can email me directly @. jhoran@.fheg.follett.com

Here is an example of my code to add to the vendor table. I'm only using 2 fields in this example.

Why is it so slow?

.......

string p1, p2;

// loop through Vendors

int Len = 0,i=0;

try

{

using (StreamReader sr = new StreamReader("VENDOR.EXP"))

{

string line;

Status = "Exporting Vendors";

while ((line = sr.ReadLine()) != null)

{

Len = line.Length;

p1 = line.Substring(0, 9);

p2 = line.Substring(9, (Len - 9));

i = i++;

this.vendorTableAdapter.Insert(p1,p2);

}

catch (System.Exception ex)

{

MessageBox.Show(ex + " Vendor File could not be read");

}

Darren Shaffer wrote:

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Let me see if I can answer your questions:

1. SQL Mobile/Everywhere/Compact Edition databases have a 4GB limit. If you plan to grow one of these databases larger than 128MB (the default maximum), then you need to set the max database size in your connection string to something larger.

2. In terms of your insert statement - using the TableAdapter is equivalent to using one insert statement after the next - you would be able to realize faster insert time (30-50%) with a parameterized insert statement (be sure to call Prepare() on the command one time and then change the parameter values and reuse the command).

3. If you are loading up this large database with vendor lookup data, you might ask yourself if you could perform this load on the desktop versus on device and then deploy the resulting sdf file to device. For databases as large as yours, you will get the sdf file loaded much much faster on the desktop given the CPU and disk speeds available there.

4. With a database of this size on device, be aware that you should have free storage memory in an amount at least equal to the database size to realize an effective tempdb and hence decent performance.

-Darren

|||This was convered a couple of says ago :

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

Using the resultset makes no difference to the insert performance either|||

Thanks Darren,

Pardon my ignorance, but I'm new to C#, VS2005 and SQL, but I've been writing code for decades.

2. Can you send me an example or point me to one, of how to avoid using the tableadapter? I was thinking that it was passing parameters.

3. The largest table will be the item table, but I'm using the same technique on it. We have less than 1000 vendors, but we could have 2.8 million items.

I agree about creating the file on the server and sending it to the device. That is in the design. Is activesync the best way for this or would we be better off to "roll our own"?

4. we are planning on at least 2 gig of memory.

p.s. I liked your Webinar on Mobile device development. It gave me a good jump start on this project. I've been working with mobile computing using C++ v1.52 for a long time, and don't expect that it will take all that long to get up to speed with this.

Thanks again for your help!

|||

Jack,

Instead of using the table adapter, try a parameterized insert. Here is the "pseudo code" to do this:

SqlCeCommand cmd = null
SqlCeParameter param = null

try

cmd = _mysqlceconnection.CreateCommand()
cmd.CommandText = "INSERT INTO tablename VALUES (@.Param1, @.Param2, @.Param3)"

param = New SqlCeParameter("@.Param1", SqlDbType.UniqueIdentifier) // set each parameter's type to match the column in the database
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param2", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param3", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)

cmd.Prepare() // this allows SQL CE to predetermine an execution plan for the insert and cache it

// note you do this one time and it is outside the insertion loop below

While ' loop through your input file line by line, one row per line

cmd.Parameters("@.Param1").Value = firstColumnValueInYourInputFile

cmd.Parameters("@.Param2").Value = secondColumnValueInYourInputFile

cmd.Parameters("@.Param3").Value = thirdColumnValueInYourInputFIle

etc until you have set all the params in the INSERT statement

cmd.ExecuteNonQuery()

End While

I recommend that anytime you are inserting or updating data in SQL Server you use parameterized SQL - the first time your data contains an apostrophe, a comma, an ampersand, etc, you'll see why.

-Darren

Bulk Insert performance

I have a situation where I need to do multiple inserts into the sql mobile db at one time. I am wondering what would be the most efficient method to do this. Right now I am just doing many inserts, but the performance is lacking. I tried to wrap all the inserts into 1 sql command and process it like that, but it does not seem to want to execute. Any help would be appreciated.

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Darren,

What is the best method to import a large amount of data into SQL Mobile? Are you saying that Batching isn't supported? Also, SQL Server will not be installed on our servers because of licensing costs. So this takes out the replication and RDA methods for imports.

In a C# application, I'm reading ASCII files and need to add to three seperate tables. It takes hours to process 100,000+ records. BTW - I need to support over 2 million records. The total file size will be around 1 gig. Since SQL CE supports files up to 4 gig, I'm not expecting this to be a problem. I'm I right here?

I'm creating an Item Lookup function that will run stand alone in our retail chain. The data (SDF file) The data will be refreshed nightly on the server and will be copied down to the device daily. If you have any suggestions you can email me directly @. jhoran@.fheg.follett.com

Here is an example of my code to add to the vendor table. I'm only using 2 fields in this example.

Why is it so slow?

.......

string p1, p2;

// loop through Vendors

int Len = 0,i=0;

try

{

using (StreamReader sr = new StreamReader("VENDOR.EXP"))

{

string line;

Status = "Exporting Vendors";

while ((line = sr.ReadLine()) != null)

{

Len = line.Length;

p1 = line.Substring(0, 9);

p2 = line.Substring(9, (Len - 9));

i = i++;

this.vendorTableAdapter.Insert(p1,p2);

}

catch (System.Exception ex)

{

MessageBox.Show(ex + " Vendor File could not be read");

}

Darren Shaffer wrote:

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Let me see if I can answer your questions:

1. SQL Mobile/Everywhere/Compact Edition databases have a 4GB limit. If you plan to grow one of these databases larger than 128MB (the default maximum), then you need to set the max database size in your connection string to something larger.

2. In terms of your insert statement - using the TableAdapter is equivalent to using one insert statement after the next - you would be able to realize faster insert time (30-50%) with a parameterized insert statement (be sure to call Prepare() on the command one time and then change the parameter values and reuse the command).

3. If you are loading up this large database with vendor lookup data, you might ask yourself if you could perform this load on the desktop versus on device and then deploy the resulting sdf file to device. For databases as large as yours, you will get the sdf file loaded much much faster on the desktop given the CPU and disk speeds available there.

4. With a database of this size on device, be aware that you should have free storage memory in an amount at least equal to the database size to realize an effective tempdb and hence decent performance.

-Darren

|||This was convered a couple of says ago :

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

Using the resultset makes no difference to the insert performance either|||

Thanks Darren,

Pardon my ignorance, but I'm new to C#, VS2005 and SQL, but I've been writing code for decades.

2. Can you send me an example or point me to one, of how to avoid using the tableadapter? I was thinking that it was passing parameters.

3. The largest table will be the item table, but I'm using the same technique on it. We have less than 1000 vendors, but we could have 2.8 million items.

I agree about creating the file on the server and sending it to the device. That is in the design. Is activesync the best way for this or would we be better off to "roll our own"?

4. we are planning on at least 2 gig of memory.

p.s. I liked your Webinar on Mobile device development. It gave me a good jump start on this project. I've been working with mobile computing using C++ v1.52 for a long time, and don't expect that it will take all that long to get up to speed with this.

Thanks again for your help!

|||

Jack,

Instead of using the table adapter, try a parameterized insert. Here is the "pseudo code" to do this:

SqlCeCommand cmd = null
SqlCeParameter param = null

try

cmd = _mysqlceconnection.CreateCommand()
cmd.CommandText = "INSERT INTO tablename VALUES (@.Param1, @.Param2, @.Param3)"

param = New SqlCeParameter("@.Param1", SqlDbType.UniqueIdentifier) // set each parameter's type to match the column in the database
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param2", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param3", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)

cmd.Prepare() // this allows SQL CE to predetermine an execution plan for the insert and cache it

// note you do this one time and it is outside the insertion loop below

While ' loop through your input file line by line, one row per line

cmd.Parameters("@.Param1").Value = firstColumnValueInYourInputFile

cmd.Parameters("@.Param2").Value = secondColumnValueInYourInputFile

cmd.Parameters("@.Param3").Value = thirdColumnValueInYourInputFIle

etc until you have set all the params in the INSERT statement

cmd.ExecuteNonQuery()

End While

I recommend that anytime you are inserting or updating data in SQL Server you use parameterized SQL - the first time your data contains an apostrophe, a comma, an ampersand, etc, you'll see why.

-Darren

BULK Insert Errors

I'm having a little trouble with a Bulk Insert. This is my first time
working with this, so I'm not sure what I'm missing here. I've read
everything I can find about Bulk Insert in BOL and from querying the
archive, but I'm not finding the answer.
This is the error I'm getting when attempting to Bulk Insert the data into a
table in a local MSDE DB named GACT:
Server: Msg 4865, Level 16, State 1, Line 1
Could not bulk insert because the maximum number of errors (10) was
exceeded.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
This is the Table def:
CREATE TABLE [GACT2] (
[id] [uniqueidentifier] NOT NULL ,
[Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
These are a couple of lines from the Import file:
14|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
15|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
And this is my Bulk Insert Query:
Delete from GACT2
GO
BULK INSERT GACT.dbo.GACT2
FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
KEEPNULLS,
KEEPIDENTITY
)
TIA
MattYour problem is with the first field - you are trying to push what appears
to be an int value (14) into a unique identifier column. So your data is
incompatible with the table. You have to get valid data or change your
schema or pipe it to a holding table.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OlnRNS0MHHA.3268@.TK2MSFTNGP04.phx.gbl...
> I'm having a little trouble with a Bulk Insert. This is my first time
> working with this, so I'm not sure what I'm missing here. I've read
> everything I can find about Bulk Insert in BOL and from querying the
> archive, but I'm not finding the answer.
> This is the error I'm getting when attempting to Bulk Insert the data into
> a table in a local MSDE DB named GACT:
> Server: Msg 4865, Level 16, State 1, Line 1
> Could not bulk insert because the maximum number of errors (10) was
> exceeded.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
> This is the Table def:
> CREATE TABLE [GACT2] (
> [id] [uniqueidentifier] NOT NULL ,
> [Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> These are a couple of lines from the Import file:
> 14|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> 15|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> And this is my Bulk Insert Query:
> Delete from GACT2
> GO
> BULK INSERT GACT.dbo.GACT2
> FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> KEEPIDENTITY
> )
> TIA
> Matt
>|||> Your problem is with the first field - you are trying to push what appears
> to be an int value (14) into a unique identifier column. So your data is
> incompatible with the table. You have to get valid data or change your
> schema or pipe it to a holding table.
That's not an issue. I'm creating the output file myself, so I can remove
that field if I need to. What would be the best way to have a unique ID
column? Do I need to leave it null in my import file and let SQL generate
it? Or should I just change the table def for that column to INT(14)
instead?
TIA
Matt|||>> Your problem is with the first field - you are trying to push what
>> appears to be an int value (14) into a unique identifier column. So your
>> data is incompatible with the table. You have to get valid data or change
>> your schema or pipe it to a holding table.
> That's not an issue. I'm creating the output file myself, so I can remove
> that field if I need to. What would be the best way to have a unique ID
> column? Do I need to leave it null in my import file and let SQL generate
> it? Or should I just change the table def for that column to INT(14)
> instead?
I did some testing and ended up changing that field in my table to INT and
now I get past that error. Now I'm getting a new error that says
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 15
(Description).
I have the description field defined as [Description] [varchar] (240)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
The description field in my file is always 240 chars. It CAN'T be any bigger
because that's the number of bytes I'm extracting for that field and writing
to the file. I don't get it. Changing it to [TEXT] helps, but it only
imports up to that field for one row and stops.|||I found it odd that you're using \n alone as a rowterminator. It *looks* to
me like your data may naturally contain carriage returns and/or line feeds,
unless that was a bad copy & paste, so I'd make sure that you use a proper
delimiter that can't appear in your data.
A
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OY3ovA2MHHA.992@.TK2MSFTNGP04.phx.gbl...
>> Your problem is with the first field - you are trying to push what
>> appears to be an int value (14) into a unique identifier column. So your
>> data is incompatible with the table. You have to get valid data or
>> change your schema or pipe it to a holding table.
>> That's not an issue. I'm creating the output file myself, so I can remove
>> that field if I need to. What would be the best way to have a unique ID
>> column? Do I need to leave it null in my import file and let SQL generate
>> it? Or should I just change the table def for that column to INT(14)
>> instead?
> I did some testing and ended up changing that field in my table to INT and
> now I get past that error. Now I'm getting a new error that says
> Server: Msg 4863, Level 16, State 1, Line 1
> Bulk insert data conversion error (truncation) for row 1, column 15
> (Description).
> I have the description field defined as [Description] [varchar] (240)
> COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> The description field in my file is always 240 chars. It CAN'T be any
> bigger because that's the number of bytes I'm extracting for that field
> and writing to the file. I don't get it. Changing it to [TEXT] helps, but
> it only imports up to that field for one row and stops.
>|||>I found it odd that you're using \n alone as a rowterminator. It *looks*
>to me like your data may naturally contain carriage returns and/or line
>feeds, unless that was a bad copy & paste, so I'd make sure that you use a
>proper delimiter that can't appear in your data.
The lines are pretty long, so the copy of it wrapped. Each row of data is on
one line in my file and is terminated with 0D 0A Hex. Is that \n or \r\n?
I've tried both. I also tried adding just a ! at the end of the row and
using "|!" as the terminator, but it didn't change anything either.|||> >I found it odd that you're using \n alone as a rowterminator. It *looks*
> >to me like your data may naturally contain carriage returns and/or line
> >feeds, unless that was a bad copy & paste, so I'd make sure that you use
> >a proper delimiter that can't appear in your data.
> The lines are pretty long, so the copy of it wrapped. Each row of data is
> on one line in my file and is terminated with 0D 0A Hex. Is that \n or
> \r\n? I've tried both. I also tried adding just a ! at the end of the row
> and using "|!" as the terminator, but it didn't change anything either.
Nevermind. It was a standard 1d10t error. I changed the path to where I was
generating the output file in my code, but forgot to change the Bulk Insert
query in QA to reflect that. It works perfectly now.
Thanks for anyone that posted.

Bulk Insert (type mismatch) on datetime field containing NULL

Can anyone help please, I am using bulk insert for the first time.
The statement I am running is:
BULK INSERT Titles
FROM 'c:\Titles.txt'
WITH (FIRSTROW = 3,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
KEEPNULLS,
FORMATFILE = 'c:\Titles.fmt')
Titles.txt contains tab delimited data like:
ID Description StartDate ExpiryDate ParentItemID
-- --
-- -- --
440 Doctor 1 Jan 1997 0:00 NULL NULL
441 Mr 1 Jan 1990 0:00 NULL 1
If I run the bulk insert statement I get the message:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 4
(ExpiryDate)
In the file Titles.txt, if I find and replace NULL with nothing and
then execute the statement the data inserts into the Titles table.
I need to be able to insert without having to do find and replace as I
have hundreds of files to bulk insert.
The format file Titles.fmt looks like this:
8.0
5
1 SQLCHAR 0 12 "\t" 1
ID ""
2 SQLCHAR 0 100 "\t" 2
Description Latin1_General_CI_AS
3 SQLCHAR 0 24 "\t" 3
StartDate ""
4 SQLCHAR 0 24 "\t" 4
ExpiryDate ""
5 SQLCHAR 0 12 "\t" 5
ParentItemID ""If this is not a one time deal, you'd be better off making sure that when
these files are generated, the value for column ExpireDate that is null does
not contain a string NULL.
With the existing data files, personally, I'd write a little utility to
find/replace all the 'NULL' string in the ExpireDate column with an empty
string. This can be esily done with any tool that supports regular
expressions.
Linchi
"rai_sk@.hotmail.com" wrote:
> Can anyone help please, I am using bulk insert for the first time.
> The statement I am running is:
> BULK INSERT Titles
> FROM 'c:\Titles.txt'
> WITH (FIRSTROW = 3,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> FORMATFILE = 'c:\Titles.fmt')
> Titles.txt contains tab delimited data like:
> ID Description StartDate ExpiryDate ParentItemID
> -- --
> -- -- --
> 440 Doctor 1 Jan 1997 0:00 NULL NULL
> 441 Mr 1 Jan 1990 0:00 NULL 1
> If I run the bulk insert statement I get the message:
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 4
> (ExpiryDate)
> In the file Titles.txt, if I find and replace NULL with nothing and
> then execute the statement the data inserts into the Titles table.
> I need to be able to insert without having to do find and replace as I
> have hundreds of files to bulk insert.
> The format file Titles.fmt looks like this:
> 8.0
> 5
> 1 SQLCHAR 0 12 "\t" 1
> ID ""
> 2 SQLCHAR 0 100 "\t" 2
> Description Latin1_General_CI_AS
> 3 SQLCHAR 0 24 "\t" 3
> StartDate ""
> 4 SQLCHAR 0 24 "\t" 4
> ExpiryDate ""
> 5 SQLCHAR 0 12 "\t" 5
> ParentItemID ""
>

Bulk Insert

Hi All,
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
PrabhatIf you have a conn.Timeout value set, it can cause a timeout for long
running operations.
Anith

Bulk Insert

Hi All,
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
PrabhatIf you have a conn.Timeout value set, it can cause a timeout for long
running operations.
--
Anith

Bulk Insert

Hi All,
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
Prabhat
If you have a conn.Timeout value set, it can cause a timeout for long
running operations.
Anith

Building multiple "temp" tables in SQL....

Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that would
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Mark
Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.com/jg_derived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>
|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:

> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exact
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>
|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place?
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>

Building multiple "temp" tables in SQL....

Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that woul
d
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.c...ived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.c...ived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:

> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exa
ct
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>
>

Building multiple "temp" tables in SQL....

Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that would
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.com/jg_derived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
--
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.com/jg_derived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> Hello,
>> Using SQL 2000 and I want to write a query that creates a different
>> "temp"
>> table every time the query is run so that more than one user cannot be
>> accessing the same temp table at the same time.
>> I see two possible ways of doing this.
>> 1.) My app can take the user ID as an input field, and I suppose that I
>> could use this in the query (as a variable) to generate a separate "temp
>> "
>> table for each user based on their ID.
>> For example, I'm Joe and my user ID is 1.
>> In the app I select my user ID as an input field and I click a button.
>> The
>> button runs the query which needs to take that User ID and create a temp
>> table with the ID as part of the table name so that it is different for
>> each
>> user.
>> 2.) If I can just tell the query to create a differentiated "temp" table
>> every time it is run (say with an increment function or something) that
>> would
>> work as well.
>> My goal is simply to make sure that when the query is run that not more
>> than
>> one person is accessing the same temp table at the same time.
>> The table is then truncated or dropped by another process.
>> Any idea if of how this can be done?
>> Thanks much,
>> Mark
>>
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:
> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exact
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> > hi
> >
> > may be you should spend some time to understand what temp tables are,
> > read BOL and
> >
> > http://www.sqlteam.com/article/temporary-tables
> > http://www.sql-server-performance.com/jg_derived_tables.asp
> >
> >
> >
> > regards
> > VT
> > Knowledge is power, share it...
> > http://oneplace4sql.blogspot.com/
> >
> >
> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> >> Hello,
> >>
> >> Using SQL 2000 and I want to write a query that creates a different
> >> "temp"
> >> table every time the query is run so that more than one user cannot be
> >> accessing the same temp table at the same time.
> >>
> >> I see two possible ways of doing this.
> >>
> >> 1.) My app can take the user ID as an input field, and I suppose that I
> >> could use this in the query (as a variable) to generate a separate "temp
> >> "
> >> table for each user based on their ID.
> >>
> >> For example, I'm Joe and my user ID is 1.
> >>
> >> In the app I select my user ID as an input field and I click a button.
> >> The
> >> button runs the query which needs to take that User ID and create a temp
> >> table with the ID as part of the table name so that it is different for
> >> each
> >> user.
> >>
> >> 2.) If I can just tell the query to create a differentiated "temp" table
> >> every time it is run (say with an increment function or something) that
> >> would
> >> work as well.
> >>
> >> My goal is simply to make sure that when the query is run that not more
> >> than
> >> one person is accessing the same temp table at the same time.
> >>
> >> The table is then truncated or dropped by another process.
> >>
> >> Any idea if of how this can be done?
> >>
> >> Thanks much,
> >>
> >> Mark
> >>
> >>
> >
> >
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
--
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>> Key point: temp tables cannot be access by anyone outside of the current
>> connection (unless they are global temp tables). Thus you can use the
>> exact
>> same name (such as #tmp) for every execution and have no worries that
>> they
>> will step on each other's data.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "vt" <vinu.t.1976@.gmail.com> wrote in message
>> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> > hi
>> >
>> > may be you should spend some time to understand what temp tables are,
>> > read BOL and
>> >
>> > http://www.sqlteam.com/article/temporary-tables
>> > http://www.sql-server-performance.com/jg_derived_tables.asp
>> >
>> >
>> >
>> > regards
>> > VT
>> > Knowledge is power, share it...
>> > http://oneplace4sql.blogspot.com/
>> >
>> >
>> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> >> Hello,
>> >>
>> >> Using SQL 2000 and I want to write a query that creates a different
>> >> "temp"
>> >> table every time the query is run so that more than one user cannot be
>> >> accessing the same temp table at the same time.
>> >>
>> >> I see two possible ways of doing this.
>> >>
>> >> 1.) My app can take the user ID as an input field, and I suppose that
>> >> I
>> >> could use this in the query (as a variable) to generate a separate
>> >> "temp
>> >> "
>> >> table for each user based on their ID.
>> >>
>> >> For example, I'm Joe and my user ID is 1.
>> >>
>> >> In the app I select my user ID as an input field and I click a button.
>> >> The
>> >> button runs the query which needs to take that User ID and create a
>> >> temp
>> >> table with the ID as part of the table name so that it is different
>> >> for
>> >> each
>> >> user.
>> >>
>> >> 2.) If I can just tell the query to create a differentiated "temp"
>> >> table
>> >> every time it is run (say with an increment function or something)
>> >> that
>> >> would
>> >> work as well.
>> >>
>> >> My goal is simply to make sure that when the query is run that not
>> >> more
>> >> than
>> >> one person is accessing the same temp table at the same time.
>> >>
>> >> The table is then truncated or dropped by another process.
>> >>
>> >> Any idea if of how this can be done?
>> >>
>> >> Thanks much,
>> >>
>> >> Mark
>> >>
>> >>
>> >
>> >
>>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>> Key point: temp tables cannot be access by anyone outside of the current
>> connection (unless they are global temp tables). Thus you can use the
>> exact
>> same name (such as #tmp) for every execution and have no worries that
>> they
>> will step on each other's data.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "vt" <vinu.t.1976@.gmail.com> wrote in message
>> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> > hi
>> >
>> > may be you should spend some time to understand what temp tables are,
>> > read BOL and
>> >
>> > http://www.sqlteam.com/article/temporary-tables
>> > http://www.sql-server-performance.com/jg_derived_tables.asp
>> >
>> >
>> >
>> > regards
>> > VT
>> > Knowledge is power, share it...
>> > http://oneplace4sql.blogspot.com/
>> >
>> >
>> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> >> Hello,
>> >>
>> >> Using SQL 2000 and I want to write a query that creates a different
>> >> "temp"
>> >> table every time the query is run so that more than one user cannot be
>> >> accessing the same temp table at the same time.
>> >>
>> >> I see two possible ways of doing this.
>> >>
>> >> 1.) My app can take the user ID as an input field, and I suppose that
>> >> I
>> >> could use this in the query (as a variable) to generate a separate
>> >> "temp
>> >> "
>> >> table for each user based on their ID.
>> >>
>> >> For example, I'm Joe and my user ID is 1.
>> >>
>> >> In the app I select my user ID as an input field and I click a button.
>> >> The
>> >> button runs the query which needs to take that User ID and create a
>> >> temp
>> >> table with the ID as part of the table name so that it is different
>> >> for
>> >> each
>> >> user.
>> >>
>> >> 2.) If I can just tell the query to create a differentiated "temp"
>> >> table
>> >> every time it is run (say with an increment function or something)
>> >> that
>> >> would
>> >> work as well.
>> >>
>> >> My goal is simply to make sure that when the query is run that not
>> >> more
>> >> than
>> >> one person is accessing the same temp table at the same time.
>> >>
>> >> The table is then truncated or dropped by another process.
>> >>
>> >> Any idea if of how this can be done?
>> >>
>> >> Thanks much,
>> >>
>> >> Mark
>> >>
>> >>
>> >
>> >
>>
>

Building high available and high performance web sites with MS SQL

Hi all. My first time posting to this newsgroup, but I think I am in the
right place. I am in the discovery stage of building a web site that we
anticipate will have a large amount of traffic. Our design platform is
Windows and Microsoft SQL and the goal is to have web sites and database
servers that are not only high available but also scalable that I can put in
a new server as needed to increase performance.
For the web sites, I know that I can use the NLB services of Windows 2003
standard edition. My lack of knowledge, though, is on the MS SQL side. I
have been reading a lot of info on Microsoft SQL server failover, but from
what I have read this tyipcally entails having two MS SQL servers running in
an active/passive mode.
Does Microsoft SQL Server support the ability to have multiple MS SQL
servers using the same database and appearing as one physically database? If
one fails, the other servers pick up the load, and if we need to add a new
server we simply install it into the cluster?
I understand that I will need enterprise edition to accomplish the goals
above, but are these goals even doable?
I know I can setup a bunch of SQL servers all doing transaction
replication, but that makes each server appear as its own database server and
I feel is not the proper solution for this.
Hope my questions make sense and looking forward to the answers.
You are asking about two goals. First let me addres the availability issue.
Microsoft clustering is a failover technology where a SQL instance can move
between host computers in case of hardware or systems failure. The basic
single-instance, two-node cluster (sometimes incorrectly called
Active/Passive) uses only one host at a time. You can add additional nodes
(host computers) and instances, but the instances do not share information
and are not "brick-type" scalable.
Right now, the way to scale SQL is to replace the host computer with a
larger one. With failover clustering, you can do this with minimal downtime
by replacing one node at a time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
news:7A300D7D-7E43-49DF-A3A7-9A134C55AA22@.microsoft.com...
> Hi all. My first time posting to this newsgroup, but I think I am in the
> right place. I am in the discovery stage of building a web site that we
> anticipate will have a large amount of traffic. Our design platform is
> Windows and Microsoft SQL and the goal is to have web sites and database
> servers that are not only high available but also scalable that I can put
> in
> a new server as needed to increase performance.
> For the web sites, I know that I can use the NLB services of Windows 2003
> standard edition. My lack of knowledge, though, is on the MS SQL side. I
> have been reading a lot of info on Microsoft SQL server failover, but from
> what I have read this tyipcally entails having two MS SQL servers running
> in
> an active/passive mode.
> Does Microsoft SQL Server support the ability to have multiple MS SQL
> servers using the same database and appearing as one physically database?
> If
> one fails, the other servers pick up the load, and if we need to add a new
> server we simply install it into the cluster?
> I understand that I will need enterprise edition to accomplish the goals
> above, but are these goals even doable?
> I know I can setup a bunch of SQL servers all doing transaction
> replication, but that makes each server appear as its own database server
> and
> I feel is not the proper solution for this.
> Hope my questions make sense and looking forward to the answers.
|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ehpN6$aCHHA.4024@.TK2MSFTNGP04.phx.gbl...
> You are asking about two goals. First let me addres the availability
> issue. Microsoft clustering is a failover technology where a SQL instance
> can move between host computers in case of hardware or systems failure.
> The basic single-instance, two-node cluster (sometimes incorrectly called
> Active/Passive) uses only one host at a time.
But it is active/passive... <G>
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
|||What is the purpose of adding extra nodes past the 2 node cluster if
clustering is just for failover? I read about adding extra nodes when
researching and didnt see why that would be advantageous? In case multiple
servers malfunction at the same time?
Ok, now that I know Clustering wont solve my problem, is there another
method of doing database load balancing? Would have lets say 3 servers, each
acting as a publisher/subscriber and then a 2-node cluster acting as a
distributer work well in this situation? I feel in this solution, i would
have 3 sql servers with the same info and then a high available distributer.
Then I assume I would need a way to make the 3 sql servers appear as one
server somehow so the code only has to reference one sql server. Maybe round
robin dns?
Am i making this too difficult? Is there an easier solution? I cant
imagine I am the only one who needs a solution where you can scale Microsoft
SQL servers effectively.
Thanks in advance.
"Geoff N. Hiten" wrote:

> You are asking about two goals. First let me addres the availability issue.
> Microsoft clustering is a failover technology where a SQL instance can move
> between host computers in case of hardware or systems failure. The basic
> single-instance, two-node cluster (sometimes incorrectly called
> Active/Passive) uses only one host at a time. You can add additional nodes
> (host computers) and instances, but the instances do not share information
> and are not "brick-type" scalable.
> Right now, the way to scale SQL is to replace the host computer with a
> larger one. With failover clustering, you can do this with minimal downtime
> by replacing one node at a time.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
> news:7A300D7D-7E43-49DF-A3A7-9A134C55AA22@.microsoft.com...
>
>
|||Flaxen,
More nodes means you can spread more SQL instances around the nodes.
Can't really speak to the load balancing aspect of your question.
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
news:C29A1C86-1103-4983-BDB6-77C2740EDA1C@.microsoft.com...[vbcol=seagreen]
> What is the purpose of adding extra nodes past the 2 node cluster if
> clustering is just for failover? I read about adding extra nodes when
> researching and didnt see why that would be advantageous? In case
> multiple
> servers malfunction at the same time?
> Ok, now that I know Clustering wont solve my problem, is there another
> method of doing database load balancing? Would have lets say 3 servers,
> each
> acting as a publisher/subscriber and then a 2-node cluster acting as a
> distributer work well in this situation? I feel in this solution, i would
> have 3 sql servers with the same info and then a high available
> distributer.
> Then I assume I would need a way to make the 3 sql servers appear as one
> server somehow so the code only has to reference one sql server. Maybe
> round
> robin dns?
> Am i making this too difficult? Is there an easier solution? I cant
> imagine I am the only one who needs a solution where you can scale
> Microsoft
> SQL servers effectively.
> Thanks in advance.
> "Geoff N. Hiten" wrote:
|||There are some thoughts on scaleout options for SQL Server here:
http://msdn2.microsoft.com/en-us/library/aa479364.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
news:C29A1C86-1103-4983-BDB6-77C2740EDA1C@.microsoft.com...[vbcol=seagreen]
> What is the purpose of adding extra nodes past the 2 node cluster if
> clustering is just for failover? I read about adding extra nodes when
> researching and didnt see why that would be advantageous? In case
> multiple
> servers malfunction at the same time?
> Ok, now that I know Clustering wont solve my problem, is there another
> method of doing database load balancing? Would have lets say 3 servers,
> each
> acting as a publisher/subscriber and then a 2-node cluster acting as a
> distributer work well in this situation? I feel in this solution, i would
> have 3 sql servers with the same info and then a high available
> distributer.
> Then I assume I would need a way to make the 3 sql servers appear as one
> server somehow so the code only has to reference one sql server. Maybe
> round
> robin dns?
> Am i making this too difficult? Is there an easier solution? I cant
> imagine I am the only one who needs a solution where you can scale
> Microsoft
> SQL servers effectively.
> Thanks in advance.
> "Geoff N. Hiten" wrote:
|||> What is the purpose of adding extra nodes past the 2 node cluster if
> clustering is just for failover? I read about adding extra nodes when
One reason is cost. If you can live with the assumption that only one node
may fail at a time, you can have three 'active' nodes sharing a common
'passive' node, i.e. 3+1, and save two servers compared to three sets of
two-node clusters. In case two or more nodes fail at the same time,
performance would suffer on a 3+1 cluster. As long as this is made clear to
the business, it is a small(er) probability sceanrio, and it is clearly
spelled out in the SLA, a 3+1 cluster may be attractive to the business, or
whoever writes the check.
Linchi
"Flaxen" wrote:
[vbcol=seagreen]
> What is the purpose of adding extra nodes past the 2 node cluster if
> clustering is just for failover? I read about adding extra nodes when
> researching and didnt see why that would be advantageous? In case multiple
> servers malfunction at the same time?
> Ok, now that I know Clustering wont solve my problem, is there another
> method of doing database load balancing? Would have lets say 3 servers, each
> acting as a publisher/subscriber and then a 2-node cluster acting as a
> distributer work well in this situation? I feel in this solution, i would
> have 3 sql servers with the same info and then a high available distributer.
> Then I assume I would need a way to make the 3 sql servers appear as one
> server somehow so the code only has to reference one sql server. Maybe round
> robin dns?
> Am i making this too difficult? Is there an easier solution? I cant
> imagine I am the only one who needs a solution where you can scale Microsoft
> SQL servers effectively.
> Thanks in advance.
> "Geoff N. Hiten" wrote:
|||There are two technologies that you can use in conjunction with each other,
if desired, but they are TWO separate technologies, each supporting very
different goals.
Failover clustering, a.k.a. server clusters, support redundant hardware that
exposes a single virtual host name. In the case of hardware failure, the
other node picks up the services, and continues to present the same virtual
server to the network. It is a fail-safe, business resumption solution.
Federated servers, on the other hand, is the closest thing you can come to a
NLB solution for database services. A collection of servers host separate
instances of SQL Server, all with a copy of the database schema; however,
the base tables are partitioned vertically, by date, name, or some other
partitioning function, with linked servers to the other systems. Each
database contains copies of common distributed portioned views, that
aggregate the base tables from across the federation.
Now, since each server exposes this same view across the entire federation,
each is able to process data from all systems. A naive solution, would be
to introduce a load balancing algorithm to distribute client connections
equally across this federation. However, to make optimal use of such a
solution, one would need the application to code which server to connect to
based on the data and which partitioned server supported that data locally,
Data Dependent Routing.
Much of the SS2K documentation is still applicable to SS2K5, but I would
search them both as there have been some enhancements and improvements.
Failover Clustering
http://msdn2.microsoft.com/en-gb/library/ms189134.aspx
SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
Scaling Out SQL Server with Data Dependent Routing
http://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx
Federated SQL Server 2000 Servers
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_4fw3.asp
Federated Database Servers
http://msdn2.microsoft.com/en-us/library/ms190381.aspx
Best of luck.
Sincerely,
Anthony Thomas

"Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
news:C29A1C86-1103-4983-BDB6-77C2740EDA1C@.microsoft.com...
> What is the purpose of adding extra nodes past the 2 node cluster if
> clustering is just for failover? I read about adding extra nodes when
> researching and didnt see why that would be advantageous? In case
multiple
> servers malfunction at the same time?
> Ok, now that I know Clustering wont solve my problem, is there another
> method of doing database load balancing? Would have lets say 3 servers,
each
> acting as a publisher/subscriber and then a 2-node cluster acting as a
> distributer work well in this situation? I feel in this solution, i would
> have 3 sql servers with the same info and then a high available
distributer.
> Then I assume I would need a way to make the 3 sql servers appear as one
> server somehow so the code only has to reference one sql server. Maybe
round
> robin dns?
> Am i making this too difficult? Is there an easier solution? I cant
> imagine I am the only one who needs a solution where you can scale
Microsoft[vbcol=seagreen]
> SQL servers effectively.
> Thanks in advance.
> "Geoff N. Hiten" wrote:
issue.[vbcol=seagreen]
move[vbcol=seagreen]
basic[vbcol=seagreen]
nodes[vbcol=seagreen]
information[vbcol=seagreen]
downtime[vbcol=seagreen]
the[vbcol=seagreen]
we[vbcol=seagreen]
is[vbcol=seagreen]
database[vbcol=seagreen]
put[vbcol=seagreen]
2003[vbcol=seagreen]
side. I[vbcol=seagreen]
from[vbcol=seagreen]
running[vbcol=seagreen]
database?[vbcol=seagreen]
new[vbcol=seagreen]
goals[vbcol=seagreen]
server[vbcol=seagreen]
|||that article has some details on "scalable shared databases",
this is how you set that up:
http://support.microsoft.com/default.aspx?scid=kb;en-us;910378
obviously scalable shared databases dissadvantage is that the "update
frequency" is very low (it is as far from live/realtime as you can imagine)
HTH,
Edwin.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ub$pxqcCHHA.1300@.TK2MSFTNGP03.phx.gbl...
> There are some thoughts on scaleout options for SQL Server here:
> http://msdn2.microsoft.com/en-us/library/aa479364.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
> news:C29A1C86-1103-4983-BDB6-77C2740EDA1C@.microsoft.com...
would[vbcol=seagreen]
basic[vbcol=seagreen]
is[vbcol=seagreen]
side.[vbcol=seagreen]
a
>

Building a time sensitive full-text search

I have spent a little time trying to build a time sensitive full-text search on a fairly large database (16M rows, with one of the full text columns an ntext column).

The goal for the search results is to have them time-sensitive - as a basic approach I am taking the relevance of the result, and dividing it by the number of days since the result was entered into the database. this works quite well, but I suspect that I can do something better.

The current approach returns the top 1000 most relevant results and then weights the relevance using the age of the result. It also limits the top 1000 to results that have occurred in the last X weeks.

I am only really interested in the top 100 or so of these weighted results.

As I increase the number of results to use as the starting point, the performance degrades quite quickly, and the results change - there are less relevant search results that actually come to the top of the weighted result.

So...the question is...is there a way to build the time-sensitivity into the relevance itself, or, any other suggestions as to how I could get good time sensitive results from full-text search without sacrificing performance.

thanks in advance

Nick, you've got quite a neat approach there. Unfortunately, the ranking algorithm in SQL Server is not customizable so it is not possible to integrate the value from another column into the ranking value. An alternative workaround would be to encode the date into a special string format which can later be used during full text search to limit the hits in a certain time range by using prefix search. This can reduce the performance degradation that you are experiencing.

Related to the performance, it isn’t clear from your message if you see the performance degrading when you increase the TOP n beyond 1000 or 100 rows?

Also, when you get the initial 1000 most relevant results, are the bottom rows in the initial ranking showing up in the top or bottom 10~20 in the final weighted 100 rows? If they’re showing up in the top 10~20 weighted rows then it could be that the time sensitivity is over weighted which may cause the less relevant results to bubble to the top after being weighted. Of course, you’d be the best judge of the relevance of the results.

I hope this helps.

Thanks

Sara

|||The degradation comes after about 1000 rows, although the FTS is set up with a good amount of RAM for caching, so it does depend a lot on whether the results are cached.

As to the weighting of the time sensitivity, we can get items from the bottom rows in the initial result set showing up at the top of the weighted result set. This is not an unwanted result, but the question of whether the weighting is "correct" is a valid one. I dont pretend to know the answer at present - it feels like a bit of a black art.|||Nick,
In addition to what Sara has asked for, could you post the full output of SELECT @.@.version as for other than SQL Server 2005, the OS platform is an important data point... Could you post your CONTAINSTABLE or FREETEXTABLE code? Do you see the performance degradation if you use the Top_N_Rank parameter?

Yes, this is somewhat of a black art , but ther are other methods of doing this that do not involving joining other tables to get a "best bet" result.

Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/