Default FTData Location

I recently redistributed our database data files from one large VRAID5 disk
(G to 3 VRAID1 disks (T:, U:, V. At this point, I have moved everything
(including SQLAgent output & log files, job logs, tlogs, backups) EXCEPT:
1) FTDATA folder and its contents;
2) distmdl.mdf and distmdl.ldf
We do not use Full Text Searching, nor do we use replication. However, in
the interest of not leaving a ticking time bomb for someone who might later
want to use these features, I'd like to move these to drive T:.
Question 1: Is there an easy way to change the default location for FT
catalogs since I'm not even using full text right now? I found
http://support.microsoft.com/kb/240867/en-us for FTDATA but was hoping for a
simpler solution.
Question 2: How do I tell SQL where distmdl is if I enable replication? The
database doesn't appear to be attached in Ent Manager.
1) http://support.microsoft.com/kb/817296
2) when you create the distribution database you will be prompted where you
want to place the database files.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CarolinaKB" <CarolinaKB@.discussions.microsoft.com> wrote in message
news:AA566260-3CA2-44AB-A910-D2BBEEEF0529@.microsoft.com...
>I recently redistributed our database data files from one large VRAID5 disk
> (G to 3 VRAID1 disks (T:, U:, V. At this point, I have moved
> everything
> (including SQLAgent output & log files, job logs, tlogs, backups) EXCEPT:
> 1) FTDATA folder and its contents;
> 2) distmdl.mdf and distmdl.ldf
> We do not use Full Text Searching, nor do we use replication. However, in
> the interest of not leaving a ticking time bomb for someone who might
> later
> want to use these features, I'd like to move these to drive T:.
> Question 1: Is there an easy way to change the default location for FT
> catalogs since I'm not even using full text right now? I found
> http://support.microsoft.com/kb/240867/en-us for FTDATA but was hoping for
> a
> simpler solution.
> Question 2: How do I tell SQL where distmdl is if I enable replication?
> The
> database doesn't appear to be attached in Ent Manager.
>
>
|||Thanks for the info.
"Hilary Cotter" wrote:

> 1) http://support.microsoft.com/kb/817296
> 2) when you create the distribution database you will be prompted where you
> want to place the database files.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "CarolinaKB" <CarolinaKB@.discussions.microsoft.com> wrote in message
> news:AA566260-3CA2-44AB-A910-D2BBEEEF0529@.microsoft.com...
>
>
|||Okay, one other question. What do I do with the existing distmdl? Do I
simply move the .mdf & .ldf whereever I want to, or delete them?
"Hilary Cotter" wrote:

> 1) http://support.microsoft.com/kb/817296
> 2) when you create the distribution database you will be prompted where you
> want to place the database files.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "CarolinaKB" <CarolinaKB@.discussions.microsoft.com> wrote in message
> news:AA566260-3CA2-44AB-A910-D2BBEEEF0529@.microsoft.com...
>
>
|||do the following sp_helpdistributor and sp_helpdistributiondb. These will
tell you if the distributor is installed and if the distribution database
files you see are the ones in use.
DO NOT DELETE distmdl.mdf or distmdl.ldf as these are the model distribution
database files. If you delete these you won't be able to install replication
again.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CarolinaKB" <CarolinaKB@.discussions.microsoft.com> wrote in message
news:87E3E406-96C8-408E-93D4-6BB86E93331F@.microsoft.com...[vbcol=seagreen]
> Okay, one other question. What do I do with the existing distmdl? Do I
> simply move the .mdf & .ldf whereever I want to, or delete them?
> "Hilary Cotter" wrote:

Default Admin account

I have a bit of problem I was hoping someone could point me in the right direction. I have a SQL Server 2005 database which leverages both the Membership and Roles APIs. When I recreate the database for production release, I simply run an sql file using the sqlcmd utility - no problem. What I need is a way to add a default administrator role, account and assign this new administrator to the administrator role.

Can someone advise on how this is typically handled?

There are two permissions in SQL Server the Server Login in the management section of Management Studio and the database permission in the security section of the database in SQL Server 2005. So you create the two user permissions and make the user DBO(database owner) role. The reason is you don't want the person to be Admin in all the databases in that instance of SQL Server. DBO is for database while SA(system admin) is for server. Hope this helps.|||Your post would be useful but I am not using the entities you suggest. I am using the ASP.NET Membership and Roles API (forms authentication) which in short is way to create application specific user accounts and assign them to application specific roles, etc.

Anyone have any suggestions on how to create a default account? Perhaps Transact-SQL calls using the aspnet_Users_CreateUser stored procedure, etc.?|||

That is covered in the two links below in the first link print out the white paper and read it and then you will understand my original post. The roles comes from the database roles. Hope this helps.

http://www.asp.net/sandbox/samp_profiles.aspx?tabindex=0&tabid=1

http://www.jbwebtech.net/Code/ProfileProvider/

|||Greetings Caddre and thanks so much for your help. I'm not quite following you. Please bear with me as I am new to SQL Server. Thanks.

Your last post is in reference to the Profile Provider. I don't see how that addresses my problem as I'm not using the Profile Provider.

I wonder if we are talking about th same users/roles. It seems that the users/roles you speak of are on a database level and the users/roles I am talking about exist in the aspnet_Membership/aspnet_Roles tables respectively. These users and roles that I have defined are specific to my web application only. My ASP.Net web application needs to use the membership and roles API in order to render the appropriate content based on the logged in user and defined role.

During development I would use the ASP.NET Web Configuration tool which utilizes the membership and roles API to add/remove users & roles and assign accordingly. At no point was I required to modify the roles under the MyDB/Security/Roles folder using Server Management Studio.

I'm a bit confused.|||OK, I figured it out. I realize it could look a little nicer...
Thanks for your help.

DECLARE @.UserNamevarchar(20)

SET @.UserName='administrator'

DECLARE @.Passwordvarchar(20)

SET @.Password='ChangeMeAfterFirstLogOn'

DECLARE @.Rolevarchar(20)

Set @.Role='administrator'

DECLARE @.Appnamevarchar(20)

SET @.Appname='MyApplication'

DECLARE @.FullNamevarchar(50)

SET @.FullName='default admin account'

DECLARE @.TimeUTCdatetime

SET @.TimeUTC=GETUTCDATE()

DECLARE @.Datedatetime

SET @.Date=GETDATE()

DECLARE@.membershipIDreturnUNIQUEIDENTIFIER

SET@.membershipIDreturn=NULL

-- Create the default role

EXEC dbo.aspnet_Roles_CreateRole

@.ApplicationName=@.Appname

,@.RoleName= @.Role

-- Create the default account

EXEC dbo.aspnet_Membership_CreateUser

@.ApplicationName=@.Appname

, @.username= @.UserName

, @.password= @.Password

,@.PasswordSalt= N'DVZTktxeMzDtXR7eik7Cdw=='

, @.Email= N'support@.XXXXX.net'

,@.PasswordQuestion= N'FavoritePet?'

,@.PasswordAnswer='%43Dds2ds22@.@.2134%$@.!BffDddwyikbt'

, @.IsApproved= 1

,@.CurrentTimeUtc= @.TimeUTC

, @.CreateDate= @.Date

,@.UniqueEmail= N''

,@.PasswordFormat= 0

, @.UserId= @.membershipIDreturnOUTPUT

IF(@.membershipIDreturnISNOTNULL)

BEGIN

-- Assign the default account the default role

EXEC dbo.aspnet_UsersInRoles_AddUsersToRoles

@.ApplicationName=@.Appname

,@.UserNames= @.UserName

,@.RoleNames= @.Role

,@.CurrentTimeUtc= @.TimeUTC

END

|||I am glad you got it going, I was trying to help you so your admin do not have the right to walk away with your database.

Decimal values get truncated when using SqlDataReader in C# .net

The values that are being fetched from the database are not being read as is , when there are decimal values with 10 or more digits after the point, the values are truncated (approximated) to 8 digits

The approximation is not consistent in all the cases
Some times depending on the numbers for example 0.434000001 is truncated to 0.434.

This is happening when i read the values from the sql database using the SqlDataReader.GetValue method into an ArrayList in C# .NETLooks like you r datatype is (the flaky) float.

What is it?

Did you try defining it as decimal?

Decimal value

When I use cast:
cast(100*T1.value1/T1.value2 as decimal(18,2))
I always get dot for decimal point, for example 10.2
Is it possible to get comma for decimal point, for example 10,2
Thank you,
SimonSimon,
The expression cast(... as decimal(18,2)) is a number, not a character
string, and its display format depends on how the client application you are
using displays decimal values. If you want to be sure a comma is
displayed, you can have SQL Server send a character string. Since
converting from decimal to character string within SQL Server results
in a . as decimal point, this should work:
replace(cast(... as decimal(18,2)),'.',',')
Note that you will now be returning a character string, and your client
program may again format the display differently than you expect. For
example, strings and numbers may be aligned differently.
Steve Kass
Drew University
simon wrote:

>When I use cast:
>cast(100*T1.value1/T1.value2 as decimal(18,2))
>I always get dot for decimal point, for example 10.2
>Is it possible to get comma for decimal point, for example 10,2
>Thank you,
>Simon
>
>|||In QA go to Tools - Options... - Connections and check "Use regional setting
s
when displaying currency, number, dates and times"
AMB
"simon" wrote:

> When I use cast:
> cast(100*T1.value1/T1.value2 as decimal(18,2))
> I always get dot for decimal point, for example 10.2
> Is it possible to get comma for decimal point, for example 10,2
> Thank you,
> Simon
>|||simon,
You could do:
REPLACE(cast(100*T1.value1/T1.value2 as decimal(18,2)), '.', ',')
But then it will no longer be numeric. You should probably do this sort of
thing in the front end.
-Andy
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:OWjFLSTNFHA.3788@.tk2msftngp13.phx.gbl...
When I use cast:
cast(100*T1.value1/T1.value2 as decimal(18,2))
I always get dot for decimal point, for example 10.2
Is it possible to get comma for decimal point, for example 10,2
Thank you,
Simon

Decimal point question

Hi
What is the function that shows only the N digits after the decimal point
?
For example: 1.4567 will result as 1.45ROUND() will round the value. This has little to do with what is
actually shown on the screen however. Display formatting is controlled
by your client application, not by SQL Server.
David Portas
SQL Server MVP
--

decimal point problem

I want to force two places to the right of the decimal even if its a whole number.

vbScript

calculatedScore = FormatNumber((rsQFinal("sumEarned") / rsQFinal("sumPossible")) * 100, 2)

I'm not savy enought to know the SQL equivalent of the above but I'd like SQL Server to perform the above rather than my vbScript code.

heres what I have so far but I don't know the formatNumber equivalent:

CREATE PROCEDURE quarterFinalGradeSA @.nClass INT, @.nQuarter INT, @.nStudent INT AS

SELECT (SUM(tblScores.score) / SUM(tblAssignments.assignmentTotalPoints)) AS returnValue
...oops I should have given more vbScript

If rsQFinal("sumEarned") = 0 Then
calculatedScore = 0
Else
calculatedScore = FormatNumber((rsQFinal("sumEarned") / rsQFinal("sumPossible")) * 100, 2)
End If

It is possibel for sumEarned to be 0 which won't do well in the division problem, is there a way to do the conditional in the SQL as well.|||I'd use:CREATE PROCEDURE quarterFinalGradeSA @.nClass INT, @.nQuarter INT, @.nStudent INT AS

SELECT (CAST CASE WHEN 0 = SUM(tblAssignments.assignmentTotalPoints) THEN 0.0 ELSE 1e2 * SUM(tblScores.score) / SUM(tblAssignments.assignmentTotalPoints) AS NUMERIC(5, 2)) AS returnValue
...-PatP|||I'd use:CREATE PROCEDURE quarterFinalGradeSA @.nClass INT, @.nQuarter INT, @.nStudent INT AS

SELECT (CAST CASE WHEN 0 = SUM(tblAssignments.assignmentTotalPoints) THEN 0.0 ELSE 1e2 * SUM(tblScores.score) / SUM(tblAssignments.assignmentTotalPoints) AS NUMERIC(5, 2)) AS returnValue
...-PatP

Don't you get bored?

I thougt you had a girlfriend...

:D|||I get to see the girlfriend and kids on weekends (notice you rarely see a post from me on Saturday or Sunday, except for early in the mornings?). During the week I have to "batch" it, either on the road or at the Data Center.

-PatP

Decimal in Design Table

Hi, this is a trivial question.
I am trying to make a 2 point decimal stored in the sql database.
For example,
a field called, "Points", will have values such as 100.00, 107.07, 99.88, 56.01 etc...
and all decimals like 108.009 would round up to 108.01.

Now, in the design table of the sql server.
Column Name Data Type Length Allow Nulls
Points Decimal 9 Yes
------------
Description (blank)
Default Value (blank)
Precision 18
Scale 0
Formula (blank)
The numbers in red are assign by default. How would I alter that to make it a 2 decimal point number?
What should I change to make it work?
Thank youyou can do it in the design view. or you can also do it from query analyzer with an ALTER TABLE stmt. From books on line:

C. Changing the data type of a column

The following example changes a column of a table fromINT toDECIMAL.

	Copy Code
CREATE TABLE doc_exy ( column_a INT ) ;GOINSERT INTO doc_exy (column_a)VALUES (10) ;GOALTER TABLE doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;GODROP TABLE doc_exy ;GO

|||Thank you so much. It's always good to know how to do it in code. However, how do I do it in design view?
|||ChangeScaleto 2.
decimal[(p[,s])] andnumeric[(p[,s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms fordecimal aredec anddec(p,s).numeric is functionally equivalent todecimal.

p (precision)

The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision, which is 38. The default precision is 18.

s(scale)

The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 throughp. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <=s <=p. Maximum storage sizes vary, based on the precision.

|||Thanks. That's the exact information I was looking for.

Decimal formatting

Hi,

we have a decimal data field and we want to format it in the correct langauge specific formatting (decimal point is a "comma" in Germany...). We tried different format strings but we always get a decimal point (really a point). Is there any way to format a decimal the way it's defined in the Windows language settings? Always with 2 digits after the point (so exactly like the currency without the currency symbol)?

Thanks,

Thomas

The "Report" element has a Language property. Make sure the Language property is set to "DE-de" or e.g. to =User!Language (to run the report with the language set on the requesting client).

Then, assuming the field value has a numeric datatype (and is not an already formatted string), you can e.g. set the format property on the textbox to N2 to get the desired formatting.

Check these links for more information on format strings:
* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp

-- Robert

|||

Robert,

Thanks... Works perfectly...

debugging stored procedure

Hi everyone,
For some reason our stored procedure is returning an error when
executed. As part of resolving where the point of failure occurs, I
have defined an OUT variable to return what stage within the stored
procedure execution has reached before failure:
CREATE PROCEDURE sp_generate_invoices @.id char(14), @.retval int = 0 OUT
AS
BEGIN
<code>
SET @.retvalue = 1
<more code>
SET @.retvalue = 2
...
END
RETURN 1
GO
--
My question is, if the stored procedure fails half way through (for
whatever reason for example an overflow error), is the @.retval value
prior to the failure actually returned when the procedure fails? Or is
the default value returned whenever failure occurs?
If not, I am guessing the best method to track where the point of
failure occurs is to include statements like:
insert flowcontrol (datestamp, position)
value (getdate(), '1')
... throughout the code.
Any insight into this most appreciated!
thanks,
peterSimplest way would be to use @.@.ERROR to trap for error conditions and using
PRINT statements to debug your flow.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<mag1kus@.yahoo.com> wrote in message
news:1141009017.876445.302420@.p10g2000cwp.googlegroups.com...
> Hi everyone,
> For some reason our stored procedure is returning an error when
> executed. As part of resolving where the point of failure occurs, I
> have defined an OUT variable to return what stage within the stored
> procedure execution has reached before failure:
> --
> CREATE PROCEDURE sp_generate_invoices @.id char(14), @.retval int = 0 OUT
> AS
> BEGIN
> <code>
> SET @.retvalue = 1
> <more code>
> SET @.retvalue = 2
> ...
> END
> RETURN 1
> GO
> --
> My question is, if the stored procedure fails half way through (for
> whatever reason for example an overflow error), is the @.retval value
> prior to the failure actually returned when the procedure fails? Or is
> the default value returned whenever failure occurs?
> If not, I am guessing the best method to track where the point of
> failure occurs is to include statements like:
> insert flowcontrol (datestamp, position)
> value (getdate(), '1')
> ... throughout the code.
> Any insight into this most appreciated!
> thanks,
> peter
>

debug (step into) sql stored proc from managed code

I am trying to debug sql2000 sp from managed code app with VS.Net 2003 archetect Ed..
It did not stop at the break point within the sql sp.
I did granted execute permission for sp_sdidebug.
Do I need to attach any process?

Is there anything left off by the article?
I referenced msdn article option 2: http://support.microsoft.com/default.aspx?kbid=316549

Thanks.Please use the word "bump" or something such as, "Any help?" so that the moderators recognise the purpose of your single-character message. Otherwise, the message may be deemed meaningless, and will be deleted.|||Sorry, this is really a Visual Studio.NET question. I have no idea.

Terri