case sensitive to case insensitive

I've just restored a db (which relies on CASE INSENSITIVE) to another db
server which happens to have been set to CASE SENSITIVE.
Is it possible to change the settings of just 1 db within the overall sql
server instance, to allow it to continue as case insensitive.?
How can I get around this problem?
Thanks
Jack
ALTER DATABASE ..,statement allows you to change a collation , however
only on new created objects
Can you re-intsall the instance?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
> I've just restored a db (which relies on CASE INSENSITIVE) to another db
> server which happens to have been set to CASE SENSITIVE.
> Is it possible to change the settings of just 1 db within the overall sql
> server instance, to allow it to continue as case insensitive.?
> How can I get around this problem?
> Thanks
>
>
|||Hi
At the moment I can't reinstall the instance.
Are you saying with the ALTER DATABASE that I can retain the case
insensitive of the 1 db , within the Server which is set at CASE SENSITIVE ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> ALTER DATABASE ..,statement allows you to change a collation , however
> only on new created objects
> Can you re-intsall the instance?
>
>
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>
|||Jack
I said that only new cteated objects will have a new collation
(insensitive)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
> Hi
> At the moment I can't reinstall the instance.
> Are you saying with the ALTER DATABASE that I can retain the case
> insensitive of the 1 db , within the Server which is set at CASE SENSITIVE
> ?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>
|||Do you know of any way apart from reinstalling the server , which would
allow the previously installed objects to retain their collation ?
For example, instead of doing a BACKUP/RESTORE , is it possible to create
the DB with the relevant collation and then transfer the objects over?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> I said that only new cteated objects will have a new collation
> (insensitive)
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
>
|||Jack
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
It is not an easy task , because you need to rebuild indexes on the table
as well

> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
Yes , create a database .. COLLATE ... (see in the bol for details)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RqednUq0eZUz3KvYRVnygA@.bt.com...
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
> Thanks
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>
sql

CASE Question

Hi,

I’ve got an update like this one:

UPDATE PROCESSOS

SET

CDU_TIPOSERVICO =

CASE

WHEN CDU_TIPOSERVICO = 'CSS'THEN CDU_TIPOSERVICO

WHEN CDU_TIPOSERVICO = 'USP'THEN ‘FFF’

WHEN CDU_TIPOSERVICO = 'PES'

ELSE ‘XXX’

END

On the first “when” if the variable is equal to ‘CSS’ I want to do nothing, I mean I want the variable to keep its value, how can I achieve that ?

Should I do -> WHEN CDU_TIPOSERVICO = 'CSS'THEN CDU_TIPOSERVICO

Or, there is another way ?

Thanks !

Your logic is perfectly correct...

Other possible solution - don't run the update statement against the 'CSS',

Code Block

update processos

set

cdu_tiposervico =

case

when cdu_tiposervico = 'USP' then 'fff'

when cdu_tiposervico = 'PES' then 'some value'

else ‘xxx’

end

where

isnull(cdu_tiposervico,'') <> 'CSS'

|||

Hi.

Thanks for your reply.

I didn't understood this part - isnull(cdu_tiposervico,'') <> 'CSS'

why not just where cdu_tiposervico <>'CSS'

What the functionality of the second parameter in the function isnull ?

|||

If there is any null value on "cdu_tiposervico" then the corresponding "cdu_tiposervico" will be updated with 'XXX'.

If you reomove isnull the null value will be retained as NULL.

Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the first
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
Susan
It can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
Anith
|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the first
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capturing Invalid Records for XML Inserts

Greetings. I'm new to Xml inserts into sql server. I've used "OpenXML"
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-ak
Using SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/
|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/

cant use replication

ive installed sql 2005 enterprise and wanted to make a replication between 2 servers but when i rightclick the replication folder in the server management studio express - i can only create a subscriber. ive also tried going through tools but theres nothing in that menu.

ive wondered, is it maybe possible the i cant create a publisher because of the management studio express - do i also need a enterprise edition of the management studio?

ive also checked in add and remove programs - went to mssql 2005 and checked in change components and in there it ses that the replication components are installed =/

wud b gr8 if u cud help me, need this for an egsam i got on the 18th
thank you very very much in beforehand =P

sql server 2005 express edition only supports as subscriber...

May either you have connected to express edition sql server or ssms express may not support...

You may need to install regular client components from EE/SE/DE edtions cd...

Check the following link for SQL Server 2005 Features Comparison...

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||ive got the enterprise odition of mssql its just the management studio that is express.
in this case i need a different version of management studio?
where can i get it from? i havent got a cd - i downloaded it from the microsoft homepage

can someone PLEASE help me out here? even if its a "i doubt that it will work" =/
|||

Hi eniGMa,

You have to configure the Distributor firstly and then the Publisher.

Go to the Instance that you want to set Replication up. Right click on the Replication folder and click "Configure Distribution...".

After configuring the Distribution, configure the Publisher. Go to the Replication folder and click the + sign near it and expand the folder. Right click on the Local Publications folder and then click "New Publication" after setting it up you may configure the necessary Publications to Subscribers.

For more information visit the MSDN:

http://msdn2.microsoft.com/en-us/library/ms151198.aspx

Ekrem ?nsoy

MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE

|||hi, ive checked the msdn page - the only thing i can create is a subscriber, nothing else
and when i click that, it tells me that it needs a publisher
|||

Hi eniGMa,

Replication system consists of Publisher, Distributor and Subscriber.

Publisher is the one what hosts the publications.

Distributor is the one which publishes the publications (from Publisher) to Subscribers.

Subscriber is the one what pulls (or pushed by the Publisher) data from Distributor.

Distributor and Publisher may be host on the same server if there is not huge workload on that SQL Server.

So according to this frame, you need to set Publisher and Distributor first.

You may configure these by help of the MSDN page which i posted before.

And then read my prior first answer again.

Ekrem ?nsoy
MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE

|||thats egsactly my point - the only thing i can create is a subscriber
nothing else (ive read through the rest of the theory)

ive got the enterprise edition of mssql server installed, only thing where it sais express is the management studio (which i cant install from the sql server installation file, so i downloaded it from download.microsoft.com)

when i rightclick the replication folder - subscriber (no publisher and no distributor)
and when i click on tools (same thing)
|||

I see that you misconfigured setup of SQL Server.

Because you can install SSMS (SQL Server Management Studio) from the Components to Install menu which appears when you were setting up SQL Server.

When you see Components to Install dialog box, click Advanced and dig into the items to be installed.

After opening Advanced dialog box, you will see: "Workstation Components, Books Online, and Development Tools" There is SSMS in it. Expand the item and you'll see it.

My concern about your Replication problem is, you may be deselecting or forgetting selecting something when you were setting up the SQL Server. Replication choice is in the "SQL Server Database Services" item in the same Advanced dialog box.

Please check these items carefully while setting up SQL Server.

Try to set SQL Server up again by taking care of these things.

Ekrem ?nsoy

MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE|||that worked - maybe it didnt work on the 1st time because ssms express was installed, when i installed it the 1st time and then i couldnt install it as an additional installation, even if removed afterwards

thnx a LOT!