Few questions on Backups for warehouse

I am starting to load a data warehouse for a retention period of 10 years. My database backup plan is as follows -

1. Perform full back on Sunday.
2. Perform differential backup everyday from Mon - Sat.
3. Perform transaction log back-ups every hour on all days.

My recovery mode is going to be BULK-LOGGED at all times. I had a few questions / comments on the Maintenance Plan that I would be creating for the back-ups. My database name is Warehouse.

1. Differential Backups cannot be created via a Maintenance Plan. Only a full-backup gets created. Am I correct?

2. I shall be running Optimizations and Integrity checks prior to full-backup. Is this ok?

3. Remove files (both .BAK and .TRN) older than - I am thinking of having 6 days. I want only one full back-up at a time in the server. What settings can I use? I think the old back-up gets deleted when the new one is successful. What settings in the Maintainence Plan do I have to use to overwrite the previous back-up with the current one?

System Databases -

Should the settings for System Databases be the same as my Warehouse database?

The Maintenance Plan takes care of full-back up and TLOG back-ups. For Differential Backups I have to use the All Tasks from EM and specify the Differential Backup job. Correct?

All kinds of back-ups can occur in the database when it is active. Meaning, I have a job that loads data in the warehouse when a back-up is occurring simultaneously. Am I correct?

I do not intend to shrink the Transaction Log at any time, since it gets backed up every hour I do not expect it to grow to a large size. If I do have to shrink it, then I change the recover mode to Simple, shrink the log and then immediately do a full-backup and after that set the mode back to Bulk-Logged. Is the sequence of steps correct?

Please let me know your inputs.

Thanks,

Vivek1. Incorrect. Please refer to BOL.

2. Sure it's okay. It can be resource intensive so you may consider running during non-peak hours. Also, you should always run DBCC CHECKDB before a backup and if possible, after a backup completes. the sooner you find a problem, the better.

3. Delete all tran and diff backups after a successful full backup completes and has been written to another media (secondary copy) such as tape

IF YOU ARE RUNNING TRAN LOG BACKUPS, YOUR TRAN LOG GETS AUTOMATICALLY TRUNCATED. IN ANY CASE, YOU ARE RUNNING IN BULK MODEL. YOU REALLY SHOULD READ BOL.

FastLoad OLEDB Destination with Oralce

Hi,

why Table or View – fast load option in Data Access mode is not listed when we connect Oracle Database in the OLEDB Destination.

Thanks

Jegan

When you switch data sources using an OLE DB destination, you are most probably using a different kind and version of the OLE DB provider. I'm guessing that in your case, you are using Microsoft's Oracle OLE DB provider? that `-fast load` option is a feature of the underlying OLE DB Provider, which in your case, I'm guessing is Microsoft's OLE DB provider (SQL Native Client). And that is probably not a feature of the underlying OLE DB Provider for Oracle that you are using, and thus, do not show-up as an option.

|||Thanks Deniz.

Fastest Bulk Load

Hi All,

Im bulk loading a ton of data into MSSQL SERVER 2005 Standard Edition. I used to do this process in version 2000. It seems there is some more overhead in 2005. Is there a way to drop logging to almost null to speed up insert?

This is my current sql statment to load data.

EXEC sp_dboption 'my_stuff', 'select into/bulkcopy', 'true'

SET ANSI_WARNINGS OFF

BULK INSERT mystuff.dbo.[v1]

FROM 'c:\myfile.txt'

WITH

(

FIRSTROW = 1,

FORMATFILE = 'c:\scripts\v1.fmt',

MAXERRORS=2000,

ROWS_PER_BATCH=100000

)

Thanks,

Mike

There should not be any significant difference in what gets logged for this between SQL 2000 and SQL 2005. How are you measuring the discrepency?

|||

I used to load this data on a windows 2000 server running sql server 2000. And I could load all of the data into 7-8 hours. Im running 22 scripts that load from 8 files a piece and specfic start and end points in the fmt files. The fmt also jump around through the data. The data files are 25 GB a piece. But now with the New version on a more powerful box the time esimates are going to be days. I guess I need to start looking for other variables if its not the logging.

I should also mention that the data all resides on a NetApp san and we are connected over 4GB fibre.

Thanks,

Mike

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText)
>|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
connectionString, CommandType commandType, String commandText)
See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
> connectionString, CommandType commandType, String commandText)
>
|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest S
P
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying proble
m
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter&
#91;]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[
]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText)
>|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
Rob
Rob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
David Gugick
Imceda Software
www.imceda.com
|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
David Gugick
Imceda Software
www.imceda.com
|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>
|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
RobRob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
David Gugick
Imceda Software
www.imceda.com|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
RobRob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
--
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
--
David Gugick
Imceda Software
www.imceda.com|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
>> Cursors are slow because they are not set-based. Do it in your
>> application and then bulk-load the data. I don't how we would know how to
>> help you assign a sub id since we don't know anything about your
>> requirements. Are you saying the SubID values do not exist in the file?
>> If so, how do you know how to assign them? How do you assign the IDs for
>> that matter?
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>> David,
>> Thanks for the info. I already can sequence the SubID's in my
>> application. I am trying to find a way to cheat (ie faster) by using SQL.
>> In a related topic is there a way to get the select the ROWID?
>> Rob
>>
>> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
>> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
>> Cursors are slow because they are not set-based. Do it in your
>> application and then bulk-load the data. I don't how we would know how
>> to help you assign a sub id since we don't know anything about your
>> requirements. Are you saying the SubID values do not exist in the file?
>> If so, how do you know how to assign them? How do you assign the IDs for
>> that matter?
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>>
>

Fast loading relational data

I am searching for a way to fast load relation data. I know how to load data fast but how can i store relation data fast.
For example :
Table1 ( tabel1Id int identity , name varchar(255) )
Table2 ( tabel2Id int identity , table2Id , name varchar(255))
When i insert 50 records into Table1 i can't get the 50 identity fields back, to insert the related data into Table2.
I think one of the solutions could be returning a selection of Table1 joined with syslockinfo, but i have no idea how to do it.
Does anyone have an idea?

Add uniqueidentifier column to your master table.

When inserting, generate the value by newid() function and insert it alongside your 50 records. Then you can easily find all the records inserted.

|||Thank you for your reply.
I also thought of that. But i also thought that it must be possible to join with syslockinfo.
|||

I would suggest that you correlate the values in the insert. So, say you have to insert an invoice and line items.

invoiceNumber
000000333
000000334

invoiceNumber product amount
000000333 widget 10
000000334 wadget 11
then the invoice insert is easy, and the line item insert is something like:

insert into invoiceLineItem (invoiceId, product, amount)
select invoiceId, product, amount
from invoice
join (select '000000333' as invoiceNumber,'widget' as product,10 as amount
union all
select '000000334','wadget',11) as newInvoiceLines
on invoice.invoiceNumber = newInvoiceLines.invoiceNumber

Of course exactly how you get the bold stuff will depend on the format of your data (like a spreadsheet, or from a user interface, XML, etc. In reality I would have expected you to have to get a productId as well.

fast loading of data to table

Hi Every one,

How can I load or copy say millions of rows to a table in the database faster?

Thanks,
Mejo GeorgeGive a look to BCP, BULK INSERT or Integration Services (SSIS).|||Expanding on Kent's suggestions, you may wish to remove or disable indexing for the duration of the import, and the rebuild the indexes -if usage permits.|||Thanks Kent & Arnie !!|||Along with these suggestions, you could look at changing your recovery model to the bulk-logged model for the duration of your loading.|||Tks

fails using LoadFromSqlServer

hi everyone,

I'm trying to load a dtsx from my front-end app but says the following:

pkg = app.LoadFromSqlServer(ActObject.packageName, "SRVDESASQL2005", "usrSSIS", "ninot", Nothing)

ActObject.packageName = "\SSISdev"

usrSSIS is a sql user which own enough permissions over MSDB

Try returns me: "Specified package could not have been loaded in Sql Server"

Any ideas? I'm stuck.

TIA

ok, works I set this path:"Maintenance Plans\Tru_DevolucionesRecibidas"

but another error raises (there is another post related with) Acquire Connection.

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

Thanks a lot