Classifieds Starter Kit in SQL Server 2005

Hello,

I am trying to get Classifieds Starter Kit work in SQL Server 2005. I did following:

1. Create DBs; The sql scripts came with the starter kit in the App_Data folder.

2. To create aspnetdb: aspnet_regsql.exe -E -S localhost -A mr

3. Open SQL Server Configuration Manager. Select "SQL Server 2005 Network Configuration | Protocols for MSSQLSERVER" then enable the protocols 'TCP/IP' and possibly 'Named Pipes'

4. My connections string <add name="classifiedsConnection" connectionString="Data Source=MyServerName;;User ID=user;Password=pass;Initial Catalog=ClassAds" providerName="System.Data.SqlClient" />

I still get the following error when I debug the application:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

How can I resolve this problem?

Looking at the steps that you have gone through you have set up the system to use the SQLExpress system with the database already attached to the engine, but have set up the location of the database files to use the sql express system as a user instance. To fix it you will have to either attach the database files to the engine using the comand line or the SQL Express management studio, or change your connection string to attach the files at run time.

|||

Thanks for the reply, I am new in this, can you give me an example how I can do it?

Class Reference - Constructor with Arguments?

From the references tab, I see I can create an instance reference, but I do
not see how I can instantiate that class with a constructor that has
arguments. Does it only do the default New() constructor?
I would like something like: m_MyClass = New MyClass(Version as Integer)I have not found a way to do this. I will perform further research on it
and update you once I have more information.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi David,
In the Reference tab of report properties, if we specify a instance name we
can only use the default constructor. If you need something other than the
default constructor, you can instantiate the class in the <Code> property
of the report instead. You'll need to reference the assembly on the
References tab of the report properties, but you would't declare an
instance there.
Instead, you'd declare the instance and initialize it in the Code property:
For example,
Dim m_myClass As MyClass
Protected Overrides Sub OnInit()
m_myClass = new MyClass(1234)
End Sub
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.

Class for DDL

I want to create, alter and delete tables (not records) from ASP code. Does
anybody know of a class (preferably ASP or DLL) that hides the complexity of
the Data Definition Language?
CedricTake a look at the DMO API. However, first consider your reasons for wanting
to do this. DMO is a reasonable choice if you want to retrieve info about DB
objects or if you are developing a general-purpose database utility
(something like Enterprise Manager for example). For deployment of a
database application however, TSQL DDL is far more concise and much easier
to deploy in most environments.
At runtime, in a business process application, there should of course be no
general need to create and change database objects at all. Not if you have
the correct design anyway.
Hope this helps.
David Portas
SQL Server MVP
--|||Look at SQL-DMO in Books Online.
Paul
"Cedric" wrote:

> I want to create, alter and delete tables (not records) from ASP code. Doe
s
> anybody know of a class (preferably ASP or DLL) that hides the complexity
of
> the Data Definition Language?
> Cedric
>
>|||>I want to create, alter and delete tables (not records) from ASP code
Now I re-read that I am pretty alarmed! Why would you want to alter and
delete tables from ASP? Are you really writing a replacement for Enterprise
Manager? If so, you should first take a look at some of the similar third
party tools already available.
David Portas
SQL Server MVP
--

Clarification on DataReader

I want to create a DataList that shows products, which will be onmultiple pages. I have my stored proc to show paged results, whichcontains a return value for more records.
I have found examples of coding the DataReader, defining all theparameters etc, but what about the drag and drop SqlDataSource?? Youcan select the DataSource Mode to be "DataReader". I can put selectparameters in, with input and my return value. I don't know how to thenaccess the return value, or output value if needed, from this? MyDataList references the SqlDataSource, but I don't know how to get thereturn/output value out? This is very frustrating, cause I can't findany info about it anywhere. Always input parameters, but no output.
This is my current SqlDataSource...

<asp:SqlDataSource ID="SqlDataSource1" runat="server"DataSourceMode="DataReader" ConnectionString="<%$ConnectionStrings:Personal %>" SelectCommand="sp_PagedItems"SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="Page" QueryStringField="page" />
<asp:Parameter Name="RecsPerPage" DefaultValue="10" />
<asp:QueryStringParameter Name="CategoryID" QueryStringField="cat"/>
<asp:Parameter Name="RETURN_VALUE" Direction="ReturnValue" Size="1"/>
</SelectParameters>
</asp:SqlDataSource
If I take out the RETURN_VALUE Parameter, my results display in my datalist, but that's useless if I can't access the return value todetermine the remaining number of pages etc. Is my RETURN_VALUEparameter wrong? How do I access that? My stored proc is shown below...

CREATE PROCEDURE sp_PagedItems
(
@.Page int,
@.RecsPerPage int,
@.CategoryID int
)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
No varchar(100),
Name varchar(100),
SDescription varchar(500),
Size varchar(10),
ImageURL varchar(100)
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (No, Name, SDescription, Size, ImageURL)
SELECT No, Name, SDescription, Size, ImageURL FROM Products WHERE CategoryID=@.CategoryID

-- Find out the first and last record we want
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @.LastRec
)
FROM #TempItems
WHERE ID > @.FirstRec AND ID < @.LastRec

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

Hi Micky,

I have a sample that you might want to check out on my blog here:http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

It shows how to build a paged datalist control that provides a product category listing in a very efficient way.

Hope this helps,

Scott

|||Scott,
Thanks for the reply, and I did find your blog, and it works perfect, however...
I don't have SQL 2005, i have SQL Server 2000. That's why I'm trying tofind a solution, or a blog or tutorial that would deal with that.
If i have to code the parameters in code-behind, then that's what I'lldo, but I'm trying to figure out why the SqlDataSource has the featureof DataReader, and putting in parameters, if I don't know how to usethem. No literature so far has shown me how it works. Everyone showsexamples of hand coding the parameters, but like I said, why would thedatasource have then available?|||

Hi Micky,

You can use the Selecting and Selected events on the SQlDataSource to write code that fires before and immediately after the Select method is called on the SqlDataSource. You can then use the eventArgs parameter to these events to either put parameters into the SqlDataSource (before the database is called), or to retrieve the output parameters (after it is called).

Hope this helps,

Scott

Circular reference

I would like to know if there is a problem when I create a Work with several
steps and the last step call first step again?Can you give a specific example of what you mean by work? Some recursive
operations (like a trigger) can only be nested 16 levels deep.
Brian
"Mariano Arias" <Mariano Arias@.discussions.microsoft.com> wrote in message
news:96505317-0C08-4A2F-9DCA-CA1FE75A3F30@.microsoft.com...
> I would like to know if there is a problem when I create a Work with
several steps and the last step call first step again?

Circular reference

I would like to know if there is a problem when I create a Work with several steps and the last step call first step again?
Can you give a specific example of what you mean by work? Some recursive
operations (like a trigger) can only be nested 16 levels deep.
Brian
"Mariano Arias" <Mariano Arias@.discussions.microsoft.com> wrote in message
news:96505317-0C08-4A2F-9DCA-CA1FE75A3F30@.microsoft.com...
> I would like to know if there is a problem when I create a Work with
several steps and the last step call first step again?

Churn Rate - two different date dim''s

I am trying to create a calculated measure in my cube (MDX Exprsssion), but first I want to write the query in MDX so I can test it. I am having issues getting this to be "dynamic"

WITH

MEMBER Measures.[Disconnects] AS

(

(

[New Subscription Date].[Calendar Date].[2007-07-15 00:00:00],

[Transaction Time].[Calendar Date].[2007-07-15 00:00:00]

),

[Measures].[Subscription Cancels]

)

MEMBER Measures.[Subscriptions] AS

(

(

[New Subscription Date].[Calendar Date].[2007-07-15 00:00:00],

[Transaction Time].[Calendar Date].[2007-07-15 00:00:00]

),

[Measures].[Subscriptions New]

)

SELECT {

Measures.[Subscriptions]

,[Measures].[Disconnects]

} ON COLUMNS

FROM [MyCube]

this query above gives me the correct values for the day I have in there, but I want it to work over all days, and so the end users can slice by other dim's as well. Basically I am trying to get the number of disconnects that occured on the same day as that disconnect subscribed. I have a feeling my underlying cube/dw is too aggregated already or something else, but I can get this pretty close to what I want. Does anyone have any insight? I have been looking at this

http://sqljunkies.com/WebLog/mosha/archive/2007/06/01/count_in_flight_mdx.aspx

because it is similar, with the two different date dim's, but I just want to always get results where the two date dim's are the same day...

Thanks

If we just take the first measure (as both can use the same pattern) using link member should make the date range dynamic. I needed to pick one of the dates as the one that you will be analyzing by, so I assumed [Transaction Time] was probably the one.

eg.

MEMBER Measures.[Disconnects] AS

(

LinkMember([Transaction Time].[Calendar Date].CurrentMember,

[New Subscription Date].[Calendar Date]

),

[Measures].[Subscription Cancels]

)

This should work OK when you are looking at specific days for Transaction Time, but if you wanted to look at the sum of all transactions where the cancellation and subscription occurred on the same date for a month, week, quarter etc. , assuming that you had a [Calendar] hierarchy then you would need to do something like the following in order to sum over a set of days.

MEMBER Measures.[Disconnects] AS

SUM(

DESCENDANTS([Transaction Time].[Calendar].CurrentMember,

[Transaction Time].[Calendar].[Calendar Date])

,

(

LinkMember([Transaction Time].[Calendar Date].CurrentMember,

[New Subscription Date].[Calendar Date]

),

[Measures].[Subscription Cancels]

)

)

)

|||

wow yeah, ok I thought maybe LinkMember was the route to go.. thing is, I dont know about the Calendar hierarchy, I dont think mine is set up right, what woudl you suggest as a hierarchy?

When i implement the query using link member, and then say, look at one month, and if i put the date ON ROWS, every day in the month has the same values, and the query takes forever to run

I think the solution above is totally the right track though, maybe I just need to tweak something more...

|||

dart_board wrote:

wow yeah, ok I thought maybe LinkMember was the route to go.. thing is, I dont know about the Calendar hierarchy, I dont think mine is set up right, what woudl you suggest as a hierarchy?

When i implement the query using link member, and then say, look at one month, and if i put the date ON ROWS, every day in the month has the same values, and the query takes forever to run

In regards to the hierarchy, I used Calendar as an example as it is something that most people can understand. When I set up a date hierarchy, personally, I usually set up one (or more) hierarchies and then I usually hide the "raw" attributes that are present in the hierarchy. You don't have to do this, you can leave the attributes separate, but the important thing is to get your attribute relationships set up correctly, which it sounds like might be an issue in your case as this would lead to apparent duplicating values.

It's important the each attribute either has a direct or indirect (not both) relationship to the key attribute. And indirect relationship is where one attribute is related to another to form a sort of chain back to the key attribute. One way of thinking of relationships is that an attribute has a relationship to it's parent. So a TimeID has a relationship to a Date and a Date has a relationship to a month and so on. Notice that Year is at the top level of granularity so it is not related to any other attributes.

eg.

TimeID

- Date

Date

- Month

Month

- Quarter

Quarter

- Year

Year

If you don't create explicit hierarchies in your date dimension, or you want to leave the "raw" attributes visible, you should be able to replace the descendants function with the existing keyword which should reduce the set of Date members down to just those that are related to what ever Time attribute is currently selected. (it figures this out using attribute relationships, so these need to be right). Notice that I have used a naming convention of <dimension>.<Attribute>.<level>.members, this is important as if you just used <dimension>.<attribute>.members, it would include the default "All" member and would return all dates which is not what you want.

MEMBER Measures.[Disconnects] AS

SUM(

EXISTING [Transaction Time].[Date].[Date].Members)

,

(

LinkMember([Transaction Time].[Calendar Date].CurrentMember,

[New Subscription Date].[Calendar Date]

),

[Measures].[Subscription Cancels]

)

)

)

Hope this helps

|||yeah that totally makes sense. I brought up a while ago that our hierarchies weren't set up correctly, but no one would beleive me - The EXISTING seems to work well. My only issue now is that when I try to use BOTH caclulated measures together, one is summed up over all and one is correct, depending on which date I slice on (transaction or new subscription) Its like I can get either one or the other to work, but not both together.

I will keep digging into that, if you have any quick insight that would be awesome. You really know your stuff

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?
Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:

> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It seems
> a bit inpractical to keep several copies of these fields in several tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>
|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>
|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It seems
> a bit inpractical to keep several copies of these fields in several tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Choosing the PK

If you have two fields that together form a unique identifier, would it be
better to use them as PK or is it better to create another field (ex. Auto
Increment) and use that.
Specially if there are many tables that contain a FK to this table. It seems
a bit inpractical to keep several copies of these fields in several tables,
even though they are used as a PK<->FK.
(Assuming the two fields cannot be null)
What are your thoughts from experience?Hi,
I will go with an Identity column with a clustered Index on it. This will
provide you a better data modeling and better data retrieval.
Thanks
Hari
SQL Server MVP
"Ash" wrote:

> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It see
ms
> a bit inpractical to keep several copies of these fields in several tables
,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||Ash,
in most cases I would say that using an identity key column as PK suits your
situation well. Consider extreme case: you have two columns as the
components of the key, each char(4000). In order to do a join, you use the
index that's created by setting up the PK. But each evaluation of each
index page would provide you only the information that the next index
reading should go up or down in pages. On the other hand, if you use an
identity column, with bigint it's only 8 bit, so your index search would be
much shorter. Normal life is not so extreme, but this is to illustrate.
Same principle applies where you do not want to use the identity key column.
If your key components are all narrow, and there are not many columns, AND
the values of those columns are frequently used in the child tables' access,
don't you think you would rather have them in those tables?
DB design and performance go hand in hand, and their optimization depends on
your situation.
hth
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F3C186BB-70D3-4316-A17F-B676D674CC25@.microsoft.com...
> If you have two fields that together form a unique identifier, would it be
> better to use them as PK or is it better to create another field (ex. Auto
> Increment) and use that.
> Specially if there are many tables that contain a FK to this table. It
> seems
> a bit inpractical to keep several copies of these fields in several
> tables,
> even though they are used as a PK<->FK.
> (Assuming the two fields cannot be null)
> What are your thoughts from experience?
>|||I see your point..
So which scenario would the join run faster (I'm talking about > 2,000,000
records in each table)
1)ID is PK, F1 & F2 are unique and index
table1 table2
ID <IDENTITY> ID<IDENTIT>
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.ID=t2.ID AND t1.f1='Somthing' AND
t1.f2='Somthing Else'
2)F1 & F2 are PK and index
table1 table2
F1<CHAR(10)> F1<CHAR(10)>
F2<CHAR(10)> F2<CHAR(10)>
F3<CHAR(10)> F4<CHAR(10)>
SELECT * FROM table1 t1,table2 t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND
t1.f1='Somthing' AND t1.f2='Somthing Else'
Thanks..

Choices of creating database files

Working on a database structure on SQL 2000 server, I have MDF and LDF creat
ed.
I need to create NDF files to use 5 logical drives on the server. All
logical drives are located in SAN storage with RAID10. When I create the ND
F
files , should I create one file on each drive OR create multiple files on
each drive? Which way is better for SQL server performance?
Thanks for recommendation!Mike,
Like so many other concepts, it depends. What is the architecture of your
SAN? If it is of later technology, then your disk allocation may be
"virtuallized" anyway. This means that you really don't have physical
control over which drives your data goes to (within the LUN) - the SAN will
determine that. For example, the HP EVA might span a RAID 10 configuration
across 100 drives in 2K blocks if so configured, even if you are allocating
only 10 GB. If you map 5 logical drives the data corresponding to all 5
drives will be interleaved and spread out over the same 100 drives.
There may be other reasons for separating the NDF files (file backups,
process isolation, etc.). You may want to isolate the log and data for
snapshots, or other reasons, but I think any performance gain would be
nominal.
Also, allocation additional space is usually a straight forward process, but
deleting space usually requires deleting and re-adding the configured space.
If you break up your data files, undoubtedly some will be a larger size than
others and this could result in a maintenance issue. You may be in a
situation where you want to take space from drive A and add it to drive B.
There could be some nominal performance having multiple drives due to SQL
Server having more I/O buffers, but it probably would not be noticable.
Unless you want to have a more sophisticated file backup scheme, I would
start with two drives (for isolation purposes), one for the log and one for
the data, indexes and tempdb.
If your architecture is not of later technology, then it depends (again!).
What kind of SAN are you working with?
-- Bill
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:A2FD70DA-F8C1-4446-AB9F-4C5D73BB60EE@.microsoft.com...
> Working on a database structure on SQL 2000 server, I have MDF and LDF
> created.
> I need to create NDF files to use 5 logical drives on the server. All
> logical drives are located in SAN storage with RAID10. When I create the
> NDF
> files , should I create one file on each drive OR create multiple files on
> each drive? Which way is better for SQL server performance?
> Thanks for recommendation!

Choices of creating database files

Working on a database structure on SQL 2000 server, I have MDF and LDF created.
I need to create NDF files to use 5 logical drives on the server. All
logical drives are located in SAN storage with RAID10. When I create the NDF
files , should I create one file on each drive OR create multiple files on
each drive? Which way is better for SQL server performance?
Thanks for recommendation!
Mike,
Like so many other concepts, it depends. What is the architecture of your
SAN? If it is of later technology, then your disk allocation may be
"virtuallized" anyway. This means that you really don't have physical
control over which drives your data goes to (within the LUN) - the SAN will
determine that. For example, the HP EVA might span a RAID 10 configuration
across 100 drives in 2K blocks if so configured, even if you are allocating
only 10 GB. If you map 5 logical drives the data corresponding to all 5
drives will be interleaved and spread out over the same 100 drives.
There may be other reasons for separating the NDF files (file backups,
process isolation, etc.). You may want to isolate the log and data for
snapshots, or other reasons, but I think any performance gain would be
nominal.
Also, allocation additional space is usually a straight forward process, but
deleting space usually requires deleting and re-adding the configured space.
If you break up your data files, undoubtedly some will be a larger size than
others and this could result in a maintenance issue. You may be in a
situation where you want to take space from drive A and add it to drive B.
There could be some nominal performance having multiple drives due to SQL
Server having more I/O buffers, but it probably would not be noticable.
Unless you want to have a more sophisticated file backup scheme, I would
start with two drives (for isolation purposes), one for the log and one for
the data, indexes and tempdb.
If your architecture is not of later technology, then it depends (again!).
What kind of SAN are you working with?
-- Bill
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:A2FD70DA-F8C1-4446-AB9F-4C5D73BB60EE@.microsoft.com...
> Working on a database structure on SQL 2000 server, I have MDF and LDF
> created.
> I need to create NDF files to use 5 logical drives on the server. All
> logical drives are located in SAN storage with RAID10. When I create the
> NDF
> files , should I create one file on each drive OR create multiple files on
> each drive? Which way is better for SQL server performance?
> Thanks for recommendation!

chng default loca where new DBs created?

Hello,
My workplace is upgrading Microsoft Access from 97 to 2002
which has the Access Data Project file that can create an
addhoc Sql Server DB on the Sql Server computer on the fly
from remote workstations. The wizard in the Access
2002 .adp file does not offer a location option where the
new Sql DB gets created on the Sql Server computer. The
problem is that these new DBs are getting created on the
server computer at a default location of
C:\Program Files\Microsoft Sql Server\MSSQL\Data
The problem is that the C drive on this server is only 12
gigs and will fill up quickly. The main MDFs reside on
the other drive of the server which has hundreds of gigs
of space. Does EM 2000 have a way to change the default
location where new DBs get created? Or is there a
registry setting I could change on the server?
Thanks,
RonIf you want to see it in EM, look at properties for the server, and click
the database tab. The defaults are in there.
Simon Worth
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:745701c5266b$86508ff0$a601280a@.phx.gbl...
> Hello,
> My workplace is upgrading Microsoft Access from 97 to 2002
> which has the Access Data Project file that can create an
> addhoc Sql Server DB on the Sql Server computer on the fly
> from remote workstations. The wizard in the Access
> 2002 .adp file does not offer a location option where the
> new Sql DB gets created on the Sql Server computer. The
> problem is that these new DBs are getting created on the
> server computer at a default location of
> C:\Program Files\Microsoft Sql Server\MSSQL\Data
> The problem is that the C drive on this server is only 12
> gigs and will fill up quickly. The main MDFs reside on
> the other drive of the server which has hundreds of gigs
> of space. Does EM 2000 have a way to change the default
> location where new DBs get created? Or is there a
> registry setting I could change on the server?
> Thanks,
> Ron|||Ron,
In EM, open server properties windows and go to "Databse Settings" tab, at
the botton you can set "New database default location" for data and log.
AMB
"Ron" wrote:

> Hello,
> My workplace is upgrading Microsoft Access from 97 to 2002
> which has the Access Data Project file that can create an
> addhoc Sql Server DB on the Sql Server computer on the fly
> from remote workstations. The wizard in the Access
> 2002 .adp file does not offer a location option where the
> new Sql DB gets created on the Sql Server computer. The
> problem is that these new DBs are getting created on the
> server computer at a default location of
> C:\Program Files\Microsoft Sql Server\MSSQL\Data
> The problem is that the C drive on this server is only 12
> gigs and will fill up quickly. The main MDFs reside on
> the other drive of the server which has hundreds of gigs
> of space. Does EM 2000 have a way to change the default
> location where new DBs get created? Or is there a
> registry setting I could change on the server?
> Thanks,
> Ron
>

Chinese DB

can i create a chinese database with microsoft sql 2000 server standard
edition.
Hi
You can create a database with the non-default collation using the COLLATE
arguement of the CREATE DATABASE statement (see books online) There are
various chineese collations see fn_helpcollations in Books online.
You may want to look at:
http://msdn.microsoft.com/library/de...server2000.asp
John
"kEVin" wrote:

> can i create a chinese database with microsoft sql 2000 server standard
> edition.

Chinese DB

can i create a chinese database with microsoft sql 2000 server standard
edition.Hi
You can create a database with the non-default collation using the COLLATE
arguement of the CREATE DATABASE statement (see books online) There are
various chineese collations see fn_helpcollations in Books online.
You may want to look at:
http://msdn.microsoft.com/library/d...lserver2000.asp
John
"kEVin" wrote:

> can i create a chinese database with microsoft sql 2000 server standard
> edition.

Chinese DB

can i create a chinese database with microsoft sql 2000 server standard
edition.Hi
You can create a database with the non-default collation using the COLLATE
arguement of the CREATE DATABASE statement (see books online) There are
various chineese collations see fn_helpcollations in Books online.
You may want to look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
John
"kEVin" wrote:
> can i create a chinese database with microsoft sql 2000 server standard
> edition.

Chicken and egg

Hi there,

This strikes me as odd but I was trying to create a NULL datetime value for storage in a SQL server table and found that a .NET datetime object defaults to its min value of Jan 1, 1 A.D. OK, now this in itself is not a problem, but the min value for a date in SQL server is Jan 1 1753, so if I just try to save the default value .NET datetime, I get a value out of range exception. Annoying but reasonably easy to remedy.

I was just wondering if anyone knew which min value came first, why, and why the second min value, or at least its default value, wasn't made equal to the other.

Cheers, DanYou could just give your input parameter in SQL a default of NULL and if your date in your .NET code is 1/1/1 then do NOT add a parameter for it (so your NULL default will be used).

Checksum computation help

Please execute the script below to understand the problem -

--
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)

--id & col1 make up the PK.

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')

select *
from test

select *
from test2

--The rows are identical.
--Script A

select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)

--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)

-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.

TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2

insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')

insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')

--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.

Thanks

drop table test
drop table test2
go
--Script B is not correct because you have no keys in tables and, of course, it returns rows - col3s are different. There is relation many to many.|||And did you look up CHECKSUM() in BOL?

I know you're trying to accomplish something...but you got me lost..

It's in the same manner as your previous threads...

Can you give us a "big picture" view of what you're trying to accomplish?

I don't mean to offend, but you need to understan what primary keys are for...sounds like your data model is not fitting in quite right with what you're trying to accomplish...|||I think this would give you an idea of the data. Yesterday when I did the processing I had this view of the table -

ID...County...Univ...Dept.....Status

1...A......XYZ...Accounting...Processed - Good
1...A......ABC...Accounting...Processed - Bad
1...A......XYZ...Marketing...Processed - Good
1...B......PQR...HR............Processed - Good
1...C......XXX...HR............Processed - Bad

I have an index on the Status field coz I can see all Bad records on top.

Today I have in my source system -

ID...County...Univ...Dept

1...A......ABC...Accounting
1...A......XYZ...Accounting
1...A......XYZ...Marketing
1...B......PQR...HR
1...C......XXX...HR
2...C......YYY...Training

I want to process only those records that are new/updated since yesterday's version. I get the above records in a separate table and assign a Status to them as 'Not Processed'. I then compare the two tables. And so because of the problem stated before, I end up processing a record that I have processed the previous day.

So how do I go about this problem? Is there a need for another column in here.

checking validity

Hello there
I've build store proecdure that create dinamic sql sentences for updating
data.
Is there a way to check if the sencence is valid before running it?Roy,shalom
Yes it is
CREATE TABLE #Test (col INT)
INSERT INTO #Test VALUES (1)
DECLARE @.str VARCHAR(50),@.col INT
SET @.col=5
SET @.str='UPDATE #Test SET col='+CAST(@.col AS VARCHAR(10))
--EXEC (@.str)
PRINT (@.str)
SELECT * FROM #Test
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OVHrU1LUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've build store proecdure that create dinamic sql sentences for updating
> data.
> Is there a way to check if the sencence is valid before running it?
>|||[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
Roy Goldhammer (roy@.hotmail.com) writes:
> I've build store proecdure that create dinamic sql sentences for updating
> data.
> Is there a way to check if the sencence is valid before running it?
In SQL 2005 you could embed the query in SET PARSEONLY ON and put it in
a TRY/CATCH handler. But that will not catch all errors, like misspelled
column names or misspelled table names. I guess you can catch these if
you use SET FMTONLY ON instead, but that will produce a result set with
metadata to the client, which is likely confuse it.
Working with dynamic SQL means that you have to test carefully, and by
other means ensure that you do not generate syntax errors at run-time.
A very important tool to achieve this is that you build parameterised
queries that you run with sp_executesql. If you interpolate all values
into the SQL string and run with EXEC(), there are more risk for problems.
Also, make sure that you use quotename for all object names you interpolate
into the string.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Consider SET FMTONLY ON and SET PARSEONLY ON. However, if the programming to
create the T-SQL statements is correct, then this should not be a recuring
problem.
If you are talking about dynamic SQL as in the entire structure of statement
(not just parameters) is created on the fly, then perhaps this programming
would be easier to implment on the application side. A class can be written
that exposes properties for table names, joins, column names, filter
expressions, etc. and then a few hundred lines of C# coding could assemble a
properly formatted select statement.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OVHrU1LUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've build store proecdure that create dinamic sql sentences for updating
> data.
> Is there a way to check if the sencence is valid before running it?
>

Checking the service pack

i am bit confused, when you apply the service pack do you apply it to the
server, to your local(b ecuase you create reports in .net?)You apply it to both, the report server installations and the client (report
designer) installations. It will detect the installed components.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vanessa" <Vanessa@.discussions.microsoft.com> wrote in message
news:9EBFEBE0-E839-41F3-8E44-63742E3AE928@.microsoft.com...
>i am bit confused, when you apply the service pack do you apply it to the
> server, to your local(b ecuase you create reports in .net?)
>|||i dont quite get how you supposed to apply it to both?
"Robert Bruckner [MSFT]" wrote:
> You apply it to both, the report server installations and the client (report
> designer) installations. It will detect the installed components.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Vanessa" <Vanessa@.discussions.microsoft.com> wrote in message
> news:9EBFEBE0-E839-41F3-8E44-63742E3AE928@.microsoft.com...
> >i am bit confused, when you apply the service pack do you apply it to the
> > server, to your local(b ecuase you create reports in .net?)
> >
> >
>
>