Collation error

hi,

Our SQL servers have been setup to collate American. However, our pc's are setup for South Africa. This is creating a nightmare for us as we use Great Plains, which is an American product with American date format.

We are having difficulties in doing any development due to this issue.

Can someone please advise me as to whether there is another way around this ?

ThanksI hear you with this problem as i have experienced the exact same thing.

The only way that i know to cope with it is to use the collate keyword which 'casts' the tables to a common collation so the join makes sense.

Collations specify the way values are compared (eg Case sensitive, sort order), so be careful when choosing the one to cast to.
UPDATE tblPrepTemplate
SET Reason = 'Do not contact'
FROM tblPrepTemplate A
inner join [Do Not Contact].DoNotContact.dbo.TblDoNotContacts B
ON a.TeleW = b.ContactNumber
collate Latin1_General_CS_AS
WHERE Reason Is Null

It is possible to change the collation of a table as well, for example


CREATE TABLE MyTable (PrimaryKey int PRIMARY KEY, CharCol varchar(10) COLLATE French_CI_AS NOT NULL )
GO

ALTER TABLE MyTable ALTER COLUMN CharCol varchar(10)COLLATE Latin1_General_CI_AS NOT NULL


I don't think that Collations do not affect the date format

Cold backup

Hello,
I'm new in SQL Server database administration, and I'm looking for creating a cold backup, bases stopped.
What is the way the do it ?look at Backup Database in bol (books on-line).
You can also do it by right clicking on the database and selecting all tasks, backup database in enterprise manager.

It doesn't matter whether or not your system is active when you take a backup.

Also make sure the recovery mode is set to simple or you take transaction log backups or the log will grow to fill the disk and then the server will crash.|||Thank U for your help|||What nigelrivett suggested actually is a "HOT" backup (meaning the database is still ACTIVE while you're doing the backup). COLD backup means the databases are down, no connection or activity occurred in order to create a consistency database backup. You should use either one of 2 methods below:

Method #1
1) Put the database in single user mode & DBO only
2) Do the full backup
Method #2
1) use SP_DETACH_DB (see BOL) to disconnect/stop the database.
2) Copy the database's files to another server
3) use SP_ATTACH_DB to re-attach and open the database for user.

Good Luck.|||Method #3

use master
go
exec sp_dboption [database_name], 'off', true
go
--see method #2 step 2
exec sp_dboption [database_name], 'off', false
go

code creating a flat file destination

I'm looking for a manner to create by code a flat file connection manager and a flat file destination.GreetsTry the last post in this thread -http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=359347&SiteID=1|||Thanks!

Cocatinate 2 columns

Hi,
Please help.. I want a level in the dimension such that the concatenation of two columns in the dimension. Can I do that while creating the dimension or I have to do in the database level.
please answer urgent help..
Rajbest way to do it is at the relational level so it is ready for processing.
however, you can concatenate strings in MDX

this is an example of measure concatenation

LINK (http://www.databasejournal.com/features/mssql/article.php/10894_1550061_4)

clustering SQL 2000

Hi,
I am using vmware product and i will be creating clusterd on vmware box and
cluster sql 2000. does anyone have done that and had any issue?
It is close enough to MSCS clustering to make you think it will work. It is
different enough to drive you to drink.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
> Hi,
> I am using vmware product and i will be creating clusterd on vmware box
> and
> cluster sql 2000. does anyone have done that and had any issue?
>
|||I've used it (VMWare) to play with clustering to get a feel for the steps involved etc. I was handed
an image (two images) with the OS clustered already, but I heard that VMWare has good info on how to
set up such. For "trying it out" purposes it worked fine for me (be prepared to increase timeouts
etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It is different enough to
> drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>
|||As Geoff says it's close enough but it never works out the same.
In my case, install scenarios for applying SP4 on a SP4 and RTM (after
failure) cluster which work on VMWare don't on real hardware but it's good
enough to learn the steps.
Nik Marshall-Blank MCSD/MCDBA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It
> is different enough to drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>

clustering SQL 2000

Hi,
I am using vmware product and i will be creating clusterd on vmware box and
cluster sql 2000. does anyone have done that and had any issue?It is close enough to MSCS clustering to make you think it will work. It is
different enough to drive you to drink.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
> Hi,
> I am using vmware product and i will be creating clusterd on vmware box
> and
> cluster sql 2000. does anyone have done that and had any issue?
>|||I've used it (VMWare) to play with clustering to get a feel for the steps involved etc. I was handed
an image (two images) with the OS clustered already, but I heard that VMWare has good info on how to
set up such. For "trying it out" purposes it worked fine for me (be prepared to increase timeouts
etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It is different enough to
> drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>> Hi,
>> I am using vmware product and i will be creating clusterd on vmware box and
>> cluster sql 2000. does anyone have done that and had any issue?
>>
>|||As Geoff says it's close enough but it never works out the same.
In my case, install scenarios for applying SP4 on a SP4 and RTM (after
failure) cluster which work on VMWare don't on real hardware but it's good
enough to learn the steps.
--
Nik Marshall-Blank MCSD/MCDBA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It
> is different enough to drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>> Hi,
>> I am using vmware product and i will be creating clusterd on vmware box
>> and
>> cluster sql 2000. does anyone have done that and had any issue?
>>
>

clustering SQL 2000

Hi,
I am using vmware product and i will be creating clusterd on vmware box and
cluster sql 2000. does anyone have done that and had any issue?It is close enough to MSCS clustering to make you think it will work. It is
different enough to drive you to drink.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
> Hi,
> I am using vmware product and i will be creating clusterd on vmware box
> and
> cluster sql 2000. does anyone have done that and had any issue?
>|||I've used it (VMWare) to play with clustering to get a feel for the steps in
volved etc. I was handed
an image (two images) with the OS clustered already, but I heard that VMWare
has good info on how to
set up such. For "trying it out" purposes it worked fine for me (be prepared
to increase timeouts
etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It
is different enough to
> drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>|||As Geoff says it's close enough but it never works out the same.
In my case, install scenarios for applying SP4 on a SP4 and RTM (after
failure) cluster which work on VMWare don't on real hardware but it's good
enough to learn the steps.
Nik Marshall-Blank MCSD/MCDBA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It
> is different enough to drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>

Clustered index

I have the query below running against a table with no indexes. This is the
only query that ever runs against it so I was planning on creating a
clustered index for it to optimize performance. The table does rarely
receives inserts or updates but it is queried 10 or so times per second.
Right now the performance is pitiful. Also note, I do not have the authority
to change the query, only add an index.
SELECT TOP 25 umBatchID, umDocumentNumber, umLineSequence, DSTINDX,
umTransactionAmount, DEX_ROW_ID from UMPST04 where umBatchID = @.BatchID and
umDocumentNumber = between @.Doc1 and @.Doc2 and umLineSequence between @.Line1
and @.Line2 order by umBatchID asc, umDocumentNumber asc, umLineSequence asc,
DEX_ROW_ID asc
should I create the clustered index based predicate? Meaning index on
umBatchID, umDocumentNumber, umLineSequence
Or based make a convering index to include all the columns in the select
portion
or based on the 'order by' meaning umBatchID, umDocumentNumber,
umLineSequence, DEX_ROW_ID.
Any suggestions are appreciated...The index you suggest should speed that query considerably; only thing would
be to decide the order of umDocumentNumber and umLineSequence in the index
-- the one that will narrow the result set more (if at all) should come
before the other. Guessing from the field names you've probably got the righ
t
order.
A clustered index is effectivly a covering index because it is not seperate
from the table as a nonclustered index is - when a clustered index is used i
t
does not need a pointer back to the original record to do a bookmark lookup;
you can see this in the query plan.
"Dean" wrote:

> I have the query below running against a table with no indexes. This is th
e
> only query that ever runs against it so I was planning on creating a
> clustered index for it to optimize performance. The table does rarely
> receives inserts or updates but it is queried 10 or so times per second.
> Right now the performance is pitiful. Also note, I do not have the authori
ty
> to change the query, only add an index.
>
> SELECT TOP 25 umBatchID, umDocumentNumber, umLineSequence, DSTINDX,
> umTransactionAmount, DEX_ROW_ID from UMPST04 where umBatchID = @.BatchID an
d
> umDocumentNumber = between @.Doc1 and @.Doc2 and umLineSequence between @.Lin
e1
> and @.Line2 order by umBatchID asc, umDocumentNumber asc, umLineSequence as
c,
> DEX_ROW_ID asc
> should I create the clustered index based predicate? Meaning index on
> umBatchID, umDocumentNumber, umLineSequence
> Or based make a convering index to include all the columns in the select
> portion
> or based on the 'order by' meaning umBatchID, umDocumentNumber,
> umLineSequence, DEX_ROW_ID.
> Any suggestions are appreciated...
>