Best Replication For Me?

To add a column, you can use sp_repladdcolumn and to
remove, sp_repldropcolumn, and have a look at this
article for datatype changes:
http://www.replicationanswers.com/AddColumn.asp.
However, if your changes to the schema are often and
involve datatype changes, you might be better off
reinitializing, in which case this shouldn't affect the
type of replication you'll implement.
BTW, in SQL Server 2005 the Alter Table statement is
allowed on replicated tables within the context of
replication, so things become much easier.
As for changes to the developer side of things, this are
some simple comments off the top of my head: merge
replication and replication with updating subscribers
will generally add a guid column. In the case of merge it
might not if there is one already there with the rowguid
attribute. Apart from that, the trigger firing order can
be important in certain replication types, as again merge
and updating subscribers will add triggers to the
replicated table. Transactional replication will not
itselt change the publisher's tables, but there is a
schema requirement - the published table must have a PK,
so this might change your code. Finally, if your code
expects to work on the subscriber in exactly the same way
as the publisher, this might not work as in some cases
the schema is subtly altered, eg PKs become unique
indexes.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:046a01c53437$7c6b7570$a601280a@.phx.gbl...
> To add a column, you can use sp_repladdcolumn and to
> remove, sp_repldropcolumn, and have a look at this
> article for datatype changes:
> http://www.replicationanswers.com/AddColumn.asp.
> However, if your changes to the schema are often and
> involve datatype changes, you might be better off
> reinitializing, in which case this shouldn't affect the
> type of replication you'll implement.
>
Can you explain this in more detail? I don't follow how reinitializing
allows for the schema and datatype changes.
WB
|||It doesn't Basically what I'm thinking is that the
process to change a datatype is longwinded, in terms of
the processing requirements, and there comes a point when
it would be less work to simply reinitialize. Perhaps
this point is 2 datatype changes - need to check?
Rgds,
Paul Ibison

Best Real Datatype

Hi all,
I have several columns which store currency values (typically up to 4
integer values, plus two decimal places)
Using Enterprise Manager I can set a column as decimal type, but it doesn't
allow me to specify precision) and any values show as the integer amount plu
s
.00 (ie 123.45 shows as 123.00). I converted these fields to money, but
several stored procedures showed a slow-down.
What is the most efficient datatype for storing very low precision real
numbers? What went wrong with my decimal datatype?
Many thanks in advance!Within EM look at the bottom half of the window. You will see a precision
and scale attribute there.
Keith Kratochvil
"GeorgeBR" <GeorgeBR@.discussions.microsoft.com> wrote in message
news:B8499051-76E7-4F2E-8650-971709333F6D@.microsoft.com...
> Hi all,
> I have several columns which store currency values (typically up to 4
> integer values, plus two decimal places)
> Using Enterprise Manager I can set a column as decimal type, but it
> doesn't
> allow me to specify precision) and any values show as the integer amount
> plus
> .00 (ie 123.45 shows as 123.00). I converted these fields to money, but
> several stored procedures showed a slow-down.
> What is the most efficient datatype for storing very low precision real
> numbers? What went wrong with my decimal datatype?
> Many thanks in advance!|||Also, don't use the money type. In addition to the performance issues you
are seeing, you will get rounding errors with money.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e$STqrafGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Within EM look at the bottom half of the window. You will see a precision
> and scale attribute there.
>
> --
> Keith Kratochvil
>
> "GeorgeBR" <GeorgeBR@.discussions.microsoft.com> wrote in message
> news:B8499051-76E7-4F2E-8650-971709333F6D@.microsoft.com...
>

Best practice for handling XML schema hierarchies?

I have a number of tables with columns of xml datatype. Each of these columns are typed against a different XML schema collection. However, each of the XML schema collections contain a hierarchy of schema definitions - and the schemas towards the top of the hierarchy are used by a number of different XML schema collections. I want to define the schemas in such a way that if I need to change a schema towards the top of the hierarchy, I only need to change it in one place.

I understand that it is not possible to reference a schema in one collection from another - is my understanding correct? (If I am wrong, then please disregard the following)

If the schemas must be duplicated in each xml schema collection that needs them, then I am considering the following approach. Are there any better methods available?

- Create a 'reference' xml schema collection that contains all the schemas
- Create a table that relates a schema to all the collections that need it
- Write a stored proc that updates all the individual collections appropriately when the reference collection is updated

Using this method, I would anticipate updating the 'reference' schema and running the stored procedure at a quiet time

I could just use a single collection for everything - but although it would ensure that the contents of a column satisfied a schema - it wouldn't check that it satisfied the correct schema. I guess I could put separate validation on the column to ensure that the right contents had been added, but this seems to run counter to the whole idea of using the XML collections

Any thoughts?

You are correct that you cannot refer to schemas from other schema collections.

You approach of using a master schema collection sounds ok. Alternatively, you could use a special table that contains each one of the schemas in an XML datatype column. That way, you could even perform updates on the schemas programmatically, and you would preserve annotations and comments in the schema as an added bonus.

You then could still do the stored procs.

Note however, that you have to be careful with evolving your schemas in that they should only be gaining new elements and types. Otherwise the schema collection will disallow such updates since the cost of revalidation and potential validation failure of old data was too high for be done implicitly...

Best regards

Michael

|||Thanks Michael - will give it a go

Best practice datatypes

HI all,
Which ould be the best datatype to use. I have possibilities of monetory
values from 0 to 1000000000
I've heard that the money datatype is not readily used. Should I use
numeric. What do you guys use
Thanks
RobertRobert Bravery wrote:
> HI all,
> Which ould be the best datatype to use. I have possibilities of monetory
> values from 0 to 1000000000
> I've heard that the money datatype is not readily used. Should I use
> numeric. What do you guys use
> Thanks
> Robert
Not MONEY or SMALLMONEY. See:
http://groups.google.co.uk/group/mi...718f3de3?hl=en&
DECIMAL or INTEGER should be suitable in your case.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David,
Very informative, I never knew this was the case
Thanks
Robert
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139567259.859890.220900@.g14g2000cwa.googlegroups.com...
> Robert Bravery wrote:
> Not MONEY or SMALLMONEY. See:
>
http://groups.google.co.uk/group/mi...rogramming/msg/
df52c1b2718f3de3?hl=en&
> DECIMAL or INTEGER should be suitable in your case.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

best datatype to save password

Hi,

what is the best datatype to save user's passwordin an encrypted format? Is there any ready datatype for that or i have to send the password enrypted to the database?

Thanks..

If you send it in clear text to the server and then checks for it anyone that has access to the connection between your application and server would be able to have a look at the password. The best practive would be to use a recognized one way hashing algorithm and send only that over the connection.

This way it will be up to your application to hash and match password and snooping on the line will be less interesting for hackers.

|||

dose this mean SQL Server dosen't have a ready encrypted datatype?

if yes, what would be the best way to encrypt if i am using C#?

thanks.

|||

SHA256 would probably be stronger than most expect.
http://msdn2.microsoft.com/en-us/library/system.security.cryptography.sha256.aspx

Store it as varbinary(32)

|||

An SHA256 hash (or any hash, for that matter) of the password alone is completely insecure unless strong passwords or pass phrases are used. Although SHA256 is technically a "one-way" transformation, in reality, it is easy to decode if the plain text is just a word. All that's necessary is to search for the hashed password in a dictionary of the SHA256 hashes of the million most common words. SHA256 is a reasonable choice as a message digest to signal unauthorized changes to the message, but it is not intended or useful for encoding single words. Steve Kass Drew University Andreas Johansson@.discussions.microsoft.com wrote:
> SHA256 would probably be stronger than most expect.
> http://msdn2.microsoft.com/en-us/library/system.security.cryptography.sh
> a256.aspx
>
> Store it as varbinary(32)
>
>

|||

Can nothing but agree, it is important to use strong passwords.

http://en.wikipedia.org/wiki/Password_strength

Best DataType for content system

Hello,
On our corporate website, we will be using email notifications for
various things. I would like for our marketing guy to be able to edit
the email templates over the web so that I don't have to keep updating
them myself for every little change. I want to store the templates
within the database (since they will be small). The templates will be
HTML based and not more than a few thousand bytes in length. Should I
use a VARCHAR or VARBINARY, or what? I would assume VARCHAR, but I'm
not sure.

Thanks,
WillFoehammer (foehammer@.hotmail.com) writes:
> On our corporate website, we will be using email notifications for
> various things. I would like for our marketing guy to be able to edit
> the email templates over the web so that I don't have to keep updating
> them myself for every little change. I want to store the templates
> within the database (since they will be small). The templates will be
> HTML based and not more than a few thousand bytes in length. Should I
> use a VARCHAR or VARBINARY, or what? I would assume VARCHAR, but I'm
> not sure.

Unless you intend to compress the templates to be able handle case
that the marketing guys enters more than 8000 characters, there is
no reason to use VARBINARY, use VARCHAR.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Best datatype ?

One of my users wants to store 20,000 characters in a field. The worst part is he wants to be able to search on that field and he is expecting it to have atleast 10,000 records.
Please let me know what is the best thing to do in this case.
Thanks.I suspect your only choice is to use a text data type. To handle the searching I would setup full text search. Read up on these in Books Online and post back with questions.|||Thanks Paul.

Best data type for a range

I want to know what the best datatype is for a situation like this e.g.if I have a field "age" and the data has a range i.e. 22-30, 31-50 etc, what is the best data type to use for this scenario.

Similarly if I have a field that whereby you use a range for example 1-2 in one record but in another you get an integer value of 0 for instance, what again would be the best datatype.

Many thanks

Integers.

|||

can integers support certain characters such as hypens (-) etc

|||

right not sure how to implement this - the data can't go as 21-30 as it will subract the two values, how I would i add this as a range

|||

If you want the column to stroe values in the form 21-30 etc then varchar(<some length>). The second case also varchar. If I understood your situation correctly.

Or maybe if you are trying to store for each row a lower limit and a higher limit for age, then how about having two columns lower_age_limit and higer_age_limit each perhaps of the tinyint datatype. Or a seperate table altogether for the the age limit, something like tblAgeLimit (id int identity(1,1), lower_limit tinyint, upper_limit tinyint) and then linking the id to the table you need.

|||

Master81:
can integers support certain characters such as hypens (-) etc

No. Sorry - I didn't realise that was the value you wanted to store. You would have to use a varchar.