> The algorithm seems very unlikely to produce a duplicate, but I will > review it to confirm before trying in production. I'm glad you found an easy solution. As long as you use a primary key to guarantee unique values, I don't see a problem using it. -- Hope this helps. Dan Guzman ...more >>

How to use the bcp command prompt utility to import data into the "Test" table Click Start->Run->cmd. In the command window , type cd c:\ which will take you to the root of c:\. At the C:\ prompt, type bcp Testdb..Test in Test.txt -c -T, and then press Enter. The bcp command ide...more >>

Trying to avoid the pain of making any "real" change, I implemented my own alternative to NEWSEQUENTIALID(), and it ROCKS. Using the function to generate GUID values (during data massaging) to load, BULK INSERT on my development machine took just 23 seconds to load each of two files, one 110,76...more >>

> If we use the sequential integer approach, we only need the GUID in the > index when we hit 4G rows and start duplicating the integer values we > generate (assuming we use negative as well as positive values), right? You don't need the GUID in the index at all if you go with a bigint and le...more >>

Thanks. You confirm our suspicions about the GUID: we seem to have lots of single I/Os when the index gets updated. There's virtually no I/O queuing and the machine does not seem taxed at all. If we use the sequential integer approach, we only need the GUID in the index when we hit 4G rows an...more >>

> The values in the non-clustered index are GUIDs and thus distributed > throughout the index. As Tom said, random GUID values are a real bulk insert performance killer. Buffer efficiency with these random decreases proportionally with table size until you get to the point of requiring at l...more >>

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:eHmpCuTHIHA.4040@TK2MSFTNGP02.phx.gbl... > The fact that you're using GUIDs says much. In SQL 2005, they have a new > function - NEWSEQUENTIALID() - that can alleviate the problems that you get > with NEWID(). That sounds very usef...more >>

The fact that you're using GUIDs says much. In SQL 2005, they have a new function - NEWSEQUENTIALID() - that can alleviate the problems that you get with NEWID(). NEWID() creates random GUIDs, so when you do INSERTs, they're all over the place. I would try lowering the fill factor on the ...more >>

Good questions. Fill factor is 80% (the default?). The write pattern in general is no writes, then bursts every two minutes or so at checkpoint. During bursts, write queue goes to 100 or more. Each burst is about 8 seconds. The read pattern is more consistent and lighter, with read queue ne...more >>

What fill factors do you have? I'm thinking that there may be a lot of page splitting going on. There could also be disk issues, but you did say that things looked OK there - "Perfmon does not indicate any resource being pinned". What is the average disk queue length? Is it > 24? It m...more >>

Thanks for the responses. If the speed of the BULK INSERT were the only criterion, dropping the non-clustered index would be great. Without it, but with the clustered index and data still there, I load at about 20,000 rows/sec. Unfortuntely, re-building an index on a table with 500+ million re...more >>

That was an excellent blog post but it was for SQL 2005. In SQL 2005, improvements were made in bulk copy performance, alleviating the need to drop indexes. The OP says that the system is SQL 2000 SP3a. Looking at the fact that the data file is sorted in clustered index order and there is...more >>

Question/sanity check about BULK INSERT performance. I load a 300,000 row text file into my largest table every day. The file loads into an empty table with the same structure and defaults but no indexes in about 10 seconds, for a rate of 30,000 rows/sec. The same file takes more like 3000 se...more >>

I am new to Analysis Services (and DW in general) and have been getting up to speed using "The Microsoft Data Warehouse Toolkit" [by J. Mundy and W. Thornthwaite] I am using a star schema with a factless fact table because the fact is an event. My question involves a set of boolean flags. T...more >>

Tom > drop indexes. The OP says that the system is SQL 2000 SP3a. Correct, just missed it. > Looking at the fact that the data file is sorted in clustered index order > and there is one nonclustered index, I'd be inclined to drop the > nonclustered index, do the bulk insert and then recrea...more >>

Scott http://sqlblog.com/blogs/linchi_shea/archive/2007/08/27/performance-impact-the-most-optimal-insert-script-can-t-beat-bulkcopy.aspx "Scott Nichol" <snicholnews@scottnichol.com> wrote in message news:uDASsWIHIHA.1548@TK2MSFTNGP05.phx.gbl... > Question/sanity check about BULK INSERT pe...more >>

Hello everyone, We are planning to purchase hardware for Analysis Server. I want to see what configuration everyone is using for the Analysis Server. How much RAM on your server, how many processors, size of the cub, Server edition, etc... Someone indicates that we need 64 bit SQL Server...more >>

I am trying to run an SSIS package from SQL Server 2005 Standard Edition with SP2 and I am getting the following error: 'SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]'. I am storing the packages in the SQL Server database and I have setup cre...more >>

Thanks! -- Krista "Adam Machanic" wrote: > http://msdn2.microsoft.com/en-us/library/ms143432.aspx > > Maximum number of rows per table is "Limited by available storage". Maximum > database size is also a limiting factor, but since that's 524,258 terabytes, > you aren't likely to h...more >>

http://msdn2.microsoft.com/en-us/library/ms143432.aspx Maximum number of rows per table is "Limited by available storage". Maximum database size is also a limiting factor, but since that's 524,258 terabytes, you aren't likely to hit the wall anytime soon. -- Adam Machanic SQL Serve...more >>

How to find out ID in dimension if name is given? for eg. in product dimension ID Product 1 Shoes 2 Computer 3 Clothes If name of product is "Clothes" then How to find count surrogate key is 3? ...more >>

I have a question. Data is in an Analysis Services database, and MDX is not my strongpoint. Imagine a desired result set like this. Not the most complex, i admit: Company Last Years Sales Current Sales Total Adv.Works 10000 4000 14000 Nort...more >>

Unable to start TSQL Debugging. Could not attach to SQL Server Process on 'srvname'. The RPC server is unavailable. I get this error when I try to run a SQL Server Project with a CLR stored Procedure in it. The target DB is SQL Server 2005 and im using VS 2005. I simply create a new SQL Ser...more >>

I am writing a project which must pull all the data from a cube: rollups in a hierarchy, some attribute rollups, some time aggregates etc. and it is not a fast process. Currently I'm using C# and the adomdDataReader and an AdomdCommand objects to iterate through the dataset... the data is ...more >>

Try entering the full name of the server instead of localhost. MC "Frank Doosey" <nospam@rightanswers.com> wrote in message news:Oo9Vj7XFIHA.280@TK2MSFTNGP03.phx.gbl... > Hello, > > I am learning how to use the BI tools with SQL Server 2005. I am following > the tutorials on creatin...more >>

I realized my error, Analysis/Integration/Reporting Services weren't installed. All is well now. "MC" <marko.NOSPAMculo@gmail.com> wrote in message news:eLbBmUZFIHA.284@TK2MSFTNGP02.phx.gbl... > Try entering the full name of the server instead of localhost. > > > MC > > > "Frank Doos...more >>

Hello, I am learning how to use the BI tools with SQL Server 2005. I am following the tutorials on creating an SSAS project. My connection string tests successfully and all SQL Server services are running, but when I try to analyze a cube or deploy the package, I get the following error: ...more >>

What calculations can you do on XML instances? Imagine aggregating XML data (e.g. SUM, AVG...) - how could that work on XML? ML --- Matija Lah, SQL Server MVP http://milambda.blogspot.com/...more >>

For what it's worth - I store XML in the description field. "Bhupesh Mohan" <BhupeshMohan@discussions.microsoft.com> wrote in message news:0C260328-E57C-44F7-A563-58DA24AB0481@microsoft.com... > My target is to get the XML as the Output from the cubes. > > For that, > > I tried to create t...more >>

What is the maximum page size of SQL 2005 tables? TIA! -- Krista...more >>