Change local connection string on remote server

Hello,

I have searched every post and tried every connection string there but can't figure out how to connect to my database on my remote server. I am using Visual Developer 2005 Express Edition in C# and uploading it to a Windows server running asp.net 2.0. The domain has not resolved yet.

I am so new to this and have followed many tutorials step by step but none of them address this issue. They just show how to upload it to the server.

Do I need to use the SQL server provided by my host or can the database stay in the App_Data folder?

My local connection string works locally:

<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\add_newSQL.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />

When I uploaded to my server I changed \SQLEXPRESS to (local) as advised in the forum.

<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|\add_newSQL.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

When I debug a page I get the <customErrors mode="Off"/>
error message even thought I have already set it in my remote web.config file which may be causing problems itself:

<configuration>

<appSettings/>

<connectionStrings>

<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|\add_newSQL.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

<system.web>

<customErrors mode="Off" />

<compilation debug="false" />

<authentication mode="Windows" />

</system.web>

</configuration>

Thanks for any help you can offer. I wish I could find someone to hire to do this part for me or teach me how over the phone. Any suggestions?

You are running SQL Express on your development system (your local computer). Are you running SQL Express on the web server?

Thanks,
Bryan

|||Having this same, problem!!!!!|||So the same question to you, as the question wasn′t answered by the original poster, are you running SQL Server Express on the WebServer ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Change in connection string

I have a SSIS Package which I developed on my own my database server.

But now the package is to deployed on 3-4 different computers pointing it to different databases.

I do not want to change the connection string for each and every instance because in future there may be more number of instances running.

I tried using the Configuration file and add that file in to the configuration window but that also did not work.

Please can anybody help me?
We're going to need more information here... You said you tried using a configuration file, "but that also did not work."

How did you set it up? What exactly happened that "did not work"? If running locally for testing, you'll have to get out of the package and reopen it for it to pick up the new configuration if that makes a difference.|||

Phil Brammer wrote:

We're going to need more information here... You said you tried using a configuration file, "but that also did not work."

How did you set it up? What exactly happened that "did not work"? If running locally for testing, you'll have to get out of the package and reopen it for it to pick up the new configuration if that makes a difference.

[Apologies for jumping in on this thread. If I get a reply then I'll delete it later.]

Phil,

Please could you email me - (jamieDOTthomsonATconchangoDOTcom). Just got something I want to ask you offline.

thanks

Jamie

|||I have file named dbConfig.dtsConfig. It had the following code in it

<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=Av-int-sj-db01\instance_b;Initial Catalog=AvaTaxAccount;User ID=avaservice;password=kennwort.2005;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False</ConfiguredValue>
</Configuration>
</DTSConfiguration>

While I double click the package exe the Execute Package Utility pop ups and in that I select the configuration option and add the above file in it.

Then try executing the package. Its gives an error message of the login not found.

Is this the right way to change the connection string while executing the package.

change connectionstring for dataset

hi,

I'm having this application using the express way to create the dategridview by having the query string builder. However, my computer recently crashed and I have no idea to change the connection string. so when I load the whole windows application, I am unable to view the information that are supposed to be in the datagridview. however, it returned an exception.

after finding out the main culprit, I realised that the database is using the old sql server's login. since it is using the old database's login, therefore it is unable to log the information into the datagrid view.

Please help! Thanks.

I'm sorry to hear that your computer crashed. Did you mean to change connection strings for typed DataSet which you created via "Data Source Configuration Wizard"? If so, you only need to change the corresponding Data Connection in the Server Explorer, as all Typed DataSet generated by the wizard use connectionstrings defined in Data Connections. If you want to configure connection string for individual TableAdapter, you need to open the typed DataSet in Design view, and right click on the TableAdapter->choose Configure...->press Previous button untill you rearch the "Choose Your Data Connection" step.

Hope this helps.

|||

thanks for the guide..I'll try and see if it works in other computer..

Cheers,

Joelle

change character in a string

i would like to know ... How to replace a certain character in a string not using a cursor ... or .. a mixed of substring an charindexes ...
i would like to know if there is any centence that do that
thanksTry REPLACE|||I fonud it

its REPLACE

sorry

bye bye

Catching SQL Exceptions for ConnStrings in Web.Config

Hi,

I have a connection string in my web.config - to which I then refer to in my code through all my controls, they're all databound to it.

Anyway - how do I catch any errors - such as when I want to view the website on a train, if I'm working on it.

I don't want it to crash and burn [the site, not the train] - if I dont have access to the sql server.

How can I wrap it in a try block!?- How do i then deal with controls which refer to the connection string?

One solution I thought of - is to programmatically set all the databinding - and not just with the GUI. As that way I can wrap everything in a try{}catch{} block.

Any other - site-wide way of doing this?

Thank you,

R

If it's a connstrings error, then one way would be to try them when the application starts up.

If you are looking for error handling during the select/insert/delete events of a sqldatasource, then check the errors in the selected/inserted/deleted events, and set e.errorhandled property to true (or not if you want the default error handling). Of course, that's not site wide.

|||

i want to capture an error at web.config stage.

so if you have something like this in your web.config:

<add name="DataStoreConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=mehDB;Integrated Security=True;uid=submit;pwd=" providerName="System.Data.SqlClient"/>

then have it produce an error on the site - and not just prevent the website from loading. as the database component althogh significant, only is necessary for logged in users - so not to affect users that are just browsing.

how do i catch the error at such an early stage?

thank you.

catch sql command if value doesnt exist

I have a sql command that is loaded on page load that collects information based on the query string. The query string is a random group of numbers and letters. How do I catch it and direct to an error page if the query can not be found in the database?

Thanks!

if you are trying to get some parameters from the querystring then you can use

dim queryvariable as string =request.querystring("variable")

try

dim sqlquery as string

sqlquery="SELECT column_Name from Table_name where variable= "& queryvariable

//use this sqlquery to check whether it returns some rows or not

catch

response.redirect("pageNotFound.aspx")

end try

|||

I'm pretty sure i did all that.

in page load i'm doing

getUserInfo(Request.QueryString["uid"]);

then the method

protected void getUserInfo(string userid) {string selectCmd ="SELECT * from users WHERE ID = @.id";string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection =new SqlConnection(strConnection); SqlCommand myCommand =new SqlCommand(selectCmd, myConnection); myCommand.Parameters.Add(new SqlParameter("@.id", SqlDbType.VarChar, 10)); myCommand.Parameters["@.id"].Value = userid;try { myConnection.Open(); SqlDataReader datareader = myCommand.ExecuteReader();while (datareader.Read()) { lblFirstName.Text = datareader["firstname"].ToString(); lblLastName.Text = datareader["lastname"].ToString(); lblTeam.Text = datareader["team"].ToString(); lblOffice.Text = datareader["office"].ToString(); } datareader.Close(); myConnection.Close(); }catch { Response.Redirect("~/error.aspx"); }

|||

any ideas?

|||

In your code you aren't checking if the datareader actually contains any data or not, so if no records are being returned nothing happens. 1 simple way to do it is:

1. Declare a boolean variable at the top initialized to False: boolean bolUserFound = False

2. Inside the while loop set the value to true: bolUserFound = True

3. After you close the connection evaluate the variable and if it's still false you know no records were found and you need to redirect to your error page:

if (bolUserFound = False) {

Response.Redirect("~/error.aspx");

}

|||

perfect!

Exactly what i needed...

Casting question

HI all,
Quick question about a trigger i am developing.
I need to take a varchar string variable and convert it and store it in an
integer variable.
How do i write that statement.
Pls keep in mind that this is inside a trigger not inside a SQL statement.
Thanks in advance,
Colin
csmart@.nf.sympatico.caassuming the value is a number value an inplicit conversion will occur
take a look at this
declare @.v varchar(50),@.i int
select @.v ='1212121'
select @.i =@.v -- implicit conversion
select convert(int,@.v),@.i
the only problem you will have is if the value is bigger than an int
can hold or not a number
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||try isnumeric function to check if its a valid numeric

casting not working?

Can anyone see what's wrong with this transformation?

((DT_STR)([IN-DLN]))

I'm trying to cast DLN as a string, but I keep getting a parsing error in the derived column tranformer...

Thanks!

Jim Work
What *is* the error exactly?

You might want to just try:
(DT_STR)[IN-DLN]|||"Attempt to parse the expression "(DT_STR)[IN-DLN]" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or it might be missing part of a required element such as a parenthesis."

Any ideas? Your suggested syntax didn't work, either.
|||Silly me...

(DT_STR,length in bytes,1252)[IN-DLN]|||When looking at the list of available Type Casts, you can see that the DT_STR type cast requires two arguments.|||Thanks as ever, Phil!

Jim Work

Casting DT_WSTR to integers in SSIS

Hello, all.

Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?

(DT_UI4) (SUBSTRING(@.[User::FullDataPath], LEN(@.[User:ataPath]) + 1, 2))

I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:\something\something\2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.

Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.

Thanks for any help.

I wonder if it doesn't like a period in the data when it tries to cast to an integer....

substring("c:\something\something\2.txt",LEN("c:\something\something") + 1, 2) = "2."|||I see that's not the case, as what I wrote.

Workaround: add a second derived column and in that component, do your cast to DT_UI4. In the first, just perform the substring operation.

I'm not sure that it will allow it in one step because it can't parse the data to determine if it's numeric or not based on the substring operation. That is, when trying to convert the output of substring, you have no idea if it's numeric or not. (YOU do, but SSIS doesn't) When you add a second derived column component, casting a column to DT_UI4 should be straightforward because SSIS is going to imply that it's numeric and can be cast.

I don't like it, but that's my guess. You are always welcome to vent this issue over at http://connect.microsoft.com/sqlserver/feedback.

Phil|||

What's the error message?

-Jamie

|||

Hey, Phil.

Thanks for the response. It turns out that my problem was relying on ALL of my variables being populated at runtime. I didn't bother to specify default values for the variables I had added to my package, because I was populating these variables are runtime. This was working, but once I started using those variables for cast operations, the Expression Builder evaluation mechanism was choking.

What I was doing was this:

1. I was getting a variable folder path from a database and storing that in a variable.

2. I used that folder path variable as an expression for the Foreach Loop Container, wherefrom I derived a full file path (spec) in order to process multiple files.

3. But then I got the brilliant idea of using the folder path and the full file spec to derive a numerical value (based on the file name) and I used that value as an input into an Execute SQL Task to do an auxiliary task.

The bottom line is that once I specified bogus default values at design time (which values were replaced anyway at runtime) for the variables upon which my cast expression depended, the Expression Builder evaluation mechanism was able to validate my cast expressions.

This little idiocy of mine has cost me a few hours of work. But, I should say that I saw nothing about runtime evaluations of design time values DURING DESIGN TIME in the SQL Help.

|||

phanoteus wrote:

This little idiocy of mine has cost me a few hours of work. But, I should say that I saw nothing about runtime evaluations of design time values DURING DESIGN TIME in the SQL Help.

Indeed. So I think you're beig a bit harsh on yourself criticising your actions as idiocy. It doesn't strike me as sensible or intuitive that variables have to be initialised to a value that will never get used. But that's just me.

-Jamie

cast from string to DateTime type

HI I am using a C++ builder routine that fills a datacontrol from the result
s
of a query. Just wondering if there is a way to cast from string type to
DateTime as the data is on a Microsoft SQL 2000 server, would like to perfor
m
the cast in the query?
Thanks
--
Paul G
Software engineer.There are two T-SQL functions CAST and CONVERT which you use for type
conversions. SQL Server Books Online has all the details and examples for
using them.
Anith|||Hi thanks for the response, I tried the code below but get the error
missing operator in query expression Cast(StartTime as DateTime)="3/03/2005
"
StartTime is a string column in a table that looks like "3/03/2005 10:11:45"
Select * from tablename
WHERE CAST(StartTime AS DateTime)= "3/03/2005 12:12:00"
"Anith Sen" wrote:

> There are two T-SQL functions CAST and CONVERT which you use for type
> conversions. SQL Server Books Online has all the details and examples for
> using them.
> --
> Anith
>
>|||1. Datetime constants are represented as strings in the SQL language. String
s are surrounded by
single quotes in the SQL Language.
2. Since you have a nationalized datetime format (not recommended!), need to
use CONVERT and use the
3:rd format parameter (the style format). Else your code will fail with a no
n matching language
setting.
3. Since you compare against a string and the value in the table is a string
, why involve the
datetime datatype at all?
4. Why do you store datetimes as strings instead of datetimes?
5. You might want to check out: http://www.karaszi.com/SQLServer/in...ime.asp

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:03322931-FD81-4F7A-940D-51EDD36B9C4C@.microsoft.com...[vbcol=seagreen]
> Hi thanks for the response, I tried the code below but get the error
> missing operator in query expression Cast(StartTime as DateTime)="3/03/200
5 "
> StartTime is a string column in a table that looks like "3/03/2005 10:11:4
5"
> Select * from tablename
> WHERE CAST(StartTime AS DateTime)= "3/03/2005 12:12:00"
> "Anith Sen" wrote:
>

cast from string to DateTime type

HI I am using a C++ builder routine that fills a datacontrol from the results
of a query. Just wondering if there is a way to cast from string type to
DateTime as the data is on a Microsoft SQL 2000 server, would like to perform
the cast in the query?
Thanks
Paul G
Software engineer.
There are two T-SQL functions CAST and CONVERT which you use for type
conversions. SQL Server Books Online has all the details and examples for
using them.
Anith
|||Hi thanks for the response, I tried the code below but get the error
missing operator in query expression Cast(StartTime as DateTime)="3/03/2005 "
StartTime is a string column in a table that looks like "3/03/2005 10:11:45"
Select * from tablename
WHERE CAST(StartTime AS DateTime)= "3/03/2005 12:12:00"
"Anith Sen" wrote:

> There are two T-SQL functions CAST and CONVERT which you use for type
> conversions. SQL Server Books Online has all the details and examples for
> using them.
> --
> Anith
>
>
|||1. Datetime constants are represented as strings in the SQL language. Strings are surrounded by
single quotes in the SQL Language.
2. Since you have a nationalized datetime format (not recommended!), need to use CONVERT and use the
3:rd format parameter (the style format). Else your code will fail with a non matching language
setting.
3. Since you compare against a string and the value in the table is a string, why involve the
datetime datatype at all?
4. Why do you store datetimes as strings instead of datetimes?
5. You might want to check out: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:03322931-FD81-4F7A-940D-51EDD36B9C4C@.microsoft.com...[vbcol=seagreen]
> Hi thanks for the response, I tried the code below but get the error
> missing operator in query expression Cast(StartTime as DateTime)="3/03/2005 "
> StartTime is a string column in a table that looks like "3/03/2005 10:11:45"
> Select * from tablename
> WHERE CAST(StartTime AS DateTime)= "3/03/2005 12:12:00"
> "Anith Sen" wrote:

cast from string to DateTime type

HI I am using a C++ builder routine that fills a datacontrol from the results
of a query. Just wondering if there is a way to cast from string type to
DateTime as the data is on a Microsoft SQL 2000 server, would like to perform
the cast in the query?
Thanks
--
Paul G
Software engineer.There are two T-SQL functions CAST and CONVERT which you use for type
conversions. SQL Server Books Online has all the details and examples for
using them.
--
Anith|||Hi thanks for the response, I tried the code below but get the error
missing operator in query expression Cast(StartTime as DateTime)="3/03/2005 "
StartTime is a string column in a table that looks like "3/03/2005 10:11:45"
Select * from tablename
WHERE CAST(StartTime AS DateTime)= "3/03/2005 12:12:00"
"Anith Sen" wrote:
> There are two T-SQL functions CAST and CONVERT which you use for type
> conversions. SQL Server Books Online has all the details and examples for
> using them.
> --
> Anith
>
>|||1. Datetime constants are represented as strings in the SQL language. Strings are surrounded by
single quotes in the SQL Language.
2. Since you have a nationalized datetime format (not recommended!), need to use CONVERT and use the
3:rd format parameter (the style format). Else your code will fail with a non matching language
setting.
3. Since you compare against a string and the value in the table is a string, why involve the
datetime datatype at all?
4. Why do you store datetimes as strings instead of datetimes?
5. You might want to check out: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:03322931-FD81-4F7A-940D-51EDD36B9C4C@.microsoft.com...
> Hi thanks for the response, I tried the code below but get the error
> missing operator in query expression Cast(StartTime as DateTime)="3/03/2005 "
> StartTime is a string column in a table that looks like "3/03/2005 10:11:45"
> Select * from tablename
> WHERE CAST(StartTime AS DateTime)= "3/03/2005 12:12:00"
> "Anith Sen" wrote:
>> There are two T-SQL functions CAST and CONVERT which you use for type
>> conversions. SQL Server Books Online has all the details and examples for
>> using them.
>> --
>> Anith
>>

Cast from string to date

I created an SSIS package that pulls in legacy data from a DB2 AS400. There is an In Date field that is stored on the AS400 as OdbcType.Date or DATE. However, when I use SSIS to pull in the data, SQL wants it to be a string so I gave up and let SQL have it's way.

Now in my SELECT statement, I have this:

SELECT TLMST.TLNUMBER, TLMST.DOGNAM, BRDMST.BRDesc, TLMST.INDT
FROM TLMST INNER JOIN
BRDMST ON TLMST.BRDCOD = BRDMST.BreedNumber
WHERE (TLMST.INDT >= @.startdate) AND (TLMST.INDT <= @.enddate)
ORDER BY TLMST.INDT, TLMST.TLNUMBER

Because I see the dates stored in TLMST are yyyy-mm-dd format, this is not working. It is not pulling any records. I tried to use the CAST statement but I keep getting errors about casting from a string to a date and data overflow errors.

Should I go back and re-do the SSIS or is there a way to pull the records for this? Thanks for the information

Do a Conversion task in SSIS, with either chopping the values from the string to create a vlid date like yyyymmdd or use the format function with that.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||When you create your source in your data flow, what is the data type SSIS has set for the column. You can find this out by right clicking on the source and selecting advanced editor and then selecting the input and output properties. In there you can expand the input tree to find the data type for the date column.|||The advanced editor is showing the data type as string, however on the AS400 it is of type OdbcType.Date. But that is on the AS400 side which I am not too familiar with.|||In which case follow Jens advice to split the date passed and build a string that is of the correct format

Cast from string OPEN to type Double is not valid.

Hi.. Please help me resolve this error "Cast from string 'OPEN' to type 'Double' is not valid.". Error here If CallStatus = 10 Then ...
Code:
Public Sub UpdateCallStatus()
Dim CALLID, RequestorID, CommentsFromITD, MessageFromITD, MessageToITD, CallStatus, strSQL As String
CALLID = Request.QueryString("CallID")
RequestorID = Session("USER_ID")
CommentsFromITD = lblcomments.Text
MessageFromITD = lblmessage.Text
MessageToITD = txt_desc.Text
CallStatus = Trim(Request.Form(ddl_callstatus.UniqueID))

Dim ObjCmd As SqlCommand
Dim ObjDR As SqlDataReader

Try
If CallStatus = 10 Then
strSQL = "UPDATE CALLS SET STATUS_ID=" & CallStatus & " WHERE CALL_ID= " & CALLID & ""
ObjCmd = New SqlCommand(strSQL, ObjConn)
ObjConn.Open()
ObjDR = ObjCmd.ExecuteScalar()
gbVariables.insertuserevents(CALLID, RequestorID, "Call Closed")
Response.Redirect("UserCallClosed.aspx")
ObjConn.Close()
Else
strSQL = "UPDATE CALLS SET STATUS_ID=" & CallStatus & " WHERE CALL_ID= " & CALLID & ""
ObjCmd = New SqlCommand(strSQL, ObjConn)
ObjConn.Open()
ObjDR = ObjCmd.ExecuteScalar()
ObjConn.Close()

strSQL = "SELECT STATUS_LABEL FROM STATUS WHERE STATUS_ID = " & CallStatus & ""
ObjCmd = New SqlCommand(strSQL, ObjConn)
ObjConn.Open()
ObjDR = ObjCmd.ExecuteScalar()
ObjConn.Close()

gbVariables.insertuserevents(CALLID, RequestorID, CallStatus)
CallStatus = ""
End If
Catch ex As Exception
lblmsg.Text = ex.Message.ToString
End Try
End Sub
Thanks...

Yes, you cannot compare string with Double values.

CallStatus = Trim(Request.Form(ddl_callstatus.UniqueID)) will return you a string ( unique id of the dropdownlist) and you are comparing it with double value (10) in the next if condition, which is wrong.

Check what value are you supposed to get in CallStatus, and with what value should you compare it.

THanks

cast a date to string

Hi,
how to cast a date field to a string:
select case(TrxDate, char(20)) & ' Add me' AS newfield from tb'
Thanks.Hopefully this example gets you started:
select convert(char(20),getdate()) + ' add me' as newcolumn
select convert(char(20),getdate(),101) + ' add me' as newcolumn
select convert(char(20),getdate(),113) + ' add me' as newcolumn
select convert(char(20),getdate(),111) + ' add me' as newcolumn
Keith
"js" <js@.someone@.hotmail.com> wrote in message
news:uyPTlxHBFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> how to cast a date field to a string:
> select case(TrxDate, char(20)) & ' Add me' AS newfield from tb'
> Thanks.
>|||Use function CAST or CONVERT.
Example:
use northwind
go
select orderid, convert(char(8), orderdate, 112) + ' whatever'
from orders
go
AMB
"js" wrote:

> Hi,
> how to cast a date field to a string:
> select case(TrxDate, char(20)) & ' Add me' AS newfield from tb'
> Thanks.
>
>|||Thanks Keith,
But has one requirement: need to format the date using a outside predefined
format(gDateFormat = "dd-mmm-yyyy", can be changed by the user)
How to do that? Do it in a query or in application level? Pls advice.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23xyoY0HBFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hopefully this example gets you started:
> select convert(char(20),getdate()) + ' add me' as newcolumn
> select convert(char(20),getdate(),101) + ' add me' as newcolumn
> select convert(char(20),getdate(),113) + ' add me' as newcolumn
> select convert(char(20),getdate(),111) + ' add me' as newcolumn
>|||Read up on CAST and CONVERT within (SQL Server) Books Online (within the SQL
Server program group). If all the user definable predefined formats map to
styles that can be specified by the user perhaps you could use T-SQL.
The easier approach might be to simply return the datetime from SQL Server
and then convert it as requested within your application's business logic.
Keith
"js" <js@.someone@.hotmail.com> wrote in message
news:OpPLvKIBFHA.824@.TK2MSFTNGP11.phx.gbl...
> Thanks Keith,
> But has one requirement: need to format the date using a outside
predefined
> format(gDateFormat = "dd-mmm-yyyy", can be changed by the user)
> How to do that? Do it in a query or in application level? Pls advice.
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:%23xyoY0HBFHA.1388@.TK2MSFTNGP09.phx.gbl...
>|||Thanks Keith,
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:ubF5ZhIBFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Read up on CAST and CONVERT within (SQL Server) Books Online (within the
> SQL
> Server program group). If all the user definable predefined formats map
> to
> styles that can be specified by the user perhaps you could use T-SQL.
>
What's T-SQL, how to do that? Pls advice.

Case-sensitivity

Hello gurus!
I have to migrate my DB from Oracle to SQL Server 2000. There are some
unique indexes on some String columns of my DB-Schema. In Oracle the unique
index constraint concerns case-sensitivity but SQL Server does not.
So when I have to migrate the two values 'A' and 'a' into a column with a
unique index I got an SQL error concerning the uniqueness of the index.
I already successfully changed the collation mode from case-insensitive to
case-sensitive. Now the collation mode is set to case-sensitive, but the
above example still doesn't work...
Does somebody have an idea how to reach real case-sensitivity in SQL Server?
By the way the database in SQL Server is restored from a file which was
backupped from my SQL Server, when the collation mode was set to
case-insensitive.
Thanks in advance
RobertHow did you change the collation? In SQL Server, the collation of a
database is the default for objects created in that database -- but only a
default. Every object that can have a collation can have a different
collation. Every column, for instance, will maintain its own. Whether
that's the default or something else is up to you... It sounds as though you
may need to change the collation for the column you're indexing. Although
it may be better for you to start over and re-create the database with a
case-sensitive collation to begin with, so that every column has the correct
collation.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> Hello gurus!
> I have to migrate my DB from Oracle to SQL Server 2000. There are some
> unique indexes on some String columns of my DB-Schema. In Oracle the
unique
> index constraint concerns case-sensitivity but SQL Server does not.
> So when I have to migrate the two values 'A' and 'a' into a column with a
> unique index I got an SQL error concerning the uniqueness of the index.
> I already successfully changed the collation mode from case-insensitive to
> case-sensitive. Now the collation mode is set to case-sensitive, but the
> above example still doesn't work...
> Does somebody have an idea how to reach real case-sensitivity in SQL
Server?
> By the way the database in SQL Server is restored from a file which was
> backupped from my SQL Server, when the collation mode was set to
> case-insensitive.
> Thanks in advance
> Robert|||I changed collation with rbuildm.exe. I tried a few SQL collations which are
case-sensitive. Then I created a new database and created a new table. Now
the collation is case-sensitive. That's good.
But it's bad that now also the tablename in the FROM clause of an SQL
Statement have to be written exactly like it was created.
Example:
CREATE TABLE OBJ_STATUS (
obj_status_id int NOT NULL,
name varchar(50) NOT NULL,
note varchar(250) NULL,
)
SELECT * FROM obj_status -> this does not work
SELECT * FROM OBJ_STATUS -> this works
My problem is that I have to migrate an application which was based on
Oracle till now. In Oracle for example the tablename in the FROM clause could
be upper or lower case, it doesn't matter, and when I query a string column
it is case-sensitive.
How can I reach such an 'case-sensitivity status'?
If this is not possible it is not practicable to migrate to SQL Server.
Regards Robert
"Adam Machanic" wrote:
> How did you change the collation? In SQL Server, the collation of a
> database is the default for objects created in that database -- but only a
> default. Every object that can have a collation can have a different
> collation. Every column, for instance, will maintain its own. Whether
> that's the default or something else is up to you... It sounds as though you
> may need to change the collation for the column you're indexing. Although
> it may be better for you to start over and re-create the database with a
> case-sensitive collation to begin with, so that every column has the correct
> collation.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> > Hello gurus!
> >
> > I have to migrate my DB from Oracle to SQL Server 2000. There are some
> > unique indexes on some String columns of my DB-Schema. In Oracle the
> unique
> > index constraint concerns case-sensitivity but SQL Server does not.
> >
> > So when I have to migrate the two values 'A' and 'a' into a column with a
> > unique index I got an SQL error concerning the uniqueness of the index.
> >
> > I already successfully changed the collation mode from case-insensitive to
> > case-sensitive. Now the collation mode is set to case-sensitive, but the
> > above example still doesn't work...
> >
> > Does somebody have an idea how to reach real case-sensitivity in SQL
> Server?
> > By the way the database in SQL Server is restored from a file which was
> > backupped from my SQL Server, when the collation mode was set to
> > case-insensitive.
> >
> > Thanks in advance
> >
> > Robert
>
>|||You've made your entire server case-sensitive. You can control default
collation at various levels:
server
database
column
statement
Look up the COLLATE keyword in Books Online.
You should put your server back to case-insensitive collation and control
the collation at the column level instead.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Robert Möstl" <RobertMstl@.discussions.microsoft.com> wrote in message
news:7DB932A6-846D-41CE-8F9E-44ED68A91681@.microsoft.com...
> I changed collation with rbuildm.exe. I tried a few SQL collations which
are
> case-sensitive. Then I created a new database and created a new table. Now
> the collation is case-sensitive. That's good.
> But it's bad that now also the tablename in the FROM clause of an SQL
> Statement have to be written exactly like it was created.
> Example:
> CREATE TABLE OBJ_STATUS (
> obj_status_id int NOT NULL,
> name varchar(50) NOT NULL,
> note varchar(250) NULL,
> )
> SELECT * FROM obj_status -> this does not work
> SELECT * FROM OBJ_STATUS -> this works
> My problem is that I have to migrate an application which was based on
> Oracle till now. In Oracle for example the tablename in the FROM clause
could
> be upper or lower case, it doesn't matter, and when I query a string
column
> it is case-sensitive.
> How can I reach such an 'case-sensitivity status'?
> If this is not possible it is not practicable to migrate to SQL Server.
> Regards Robert
>
> "Adam Machanic" wrote:
> > How did you change the collation? In SQL Server, the collation of a
> > database is the default for objects created in that database -- but only
a
> > default. Every object that can have a collation can have a different
> > collation. Every column, for instance, will maintain its own. Whether
> > that's the default or something else is up to you... It sounds as though
you
> > may need to change the collation for the column you're indexing.
Although
> > it may be better for you to start over and re-create the database with a
> > case-sensitive collation to begin with, so that every column has the
correct
> > collation.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> > news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> > > Hello gurus!
> > >
> > > I have to migrate my DB from Oracle to SQL Server 2000. There are some
> > > unique indexes on some String columns of my DB-Schema. In Oracle the
> > unique
> > > index constraint concerns case-sensitivity but SQL Server does not.
> > >
> > > So when I have to migrate the two values 'A' and 'a' into a column
with a
> > > unique index I got an SQL error concerning the uniqueness of the
index.
> > >
> > > I already successfully changed the collation mode from
case-insensitive to
> > > case-sensitive. Now the collation mode is set to case-sensitive, but
the
> > > above example still doesn't work...
> > >
> > > Does somebody have an idea how to reach real case-sensitivity in SQL
> > Server?
> > > By the way the database in SQL Server is restored from a file which
was
> > > backupped from my SQL Server, when the collation mode was set to
> > > case-insensitive.
> > >
> > > Thanks in advance
> > >
> > > Robert
> >
> >
> >|||After I changed collation mode to case-sensitive with rebuildm.exe I
generated a new database. Then I created a new table. Now when I want to
query this table the tablename in the FROM clause have to be written just
like it was in the CREATE TABLE statement. Queries on a string column are no
case-sensitive.
My problem is that I have to migrate an existing application based on
Oracle. There the tablenames in the SQL statements are written in upper case
or lower case. In Oracle it doesn't matter.
How can I reach in SQL Server that I can write tablenames in upper or lower
case but although the queries of string columns are case-sensitive?
If this is not possible, I have to cancel this migration project.
Regards,
Robert
"Adam Machanic" wrote:
> How did you change the collation? In SQL Server, the collation of a
> database is the default for objects created in that database -- but only a
> default. Every object that can have a collation can have a different
> collation. Every column, for instance, will maintain its own. Whether
> that's the default or something else is up to you... It sounds as though you
> may need to change the collation for the column you're indexing. Although
> it may be better for you to start over and re-create the database with a
> case-sensitive collation to begin with, so that every column has the correct
> collation.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> > Hello gurus!
> >
> > I have to migrate my DB from Oracle to SQL Server 2000. There are some
> > unique indexes on some String columns of my DB-Schema. In Oracle the
> unique
> > index constraint concerns case-sensitivity but SQL Server does not.
> >
> > So when I have to migrate the two values 'A' and 'a' into a column with a
> > unique index I got an SQL error concerning the uniqueness of the index.
> >
> > I already successfully changed the collation mode from case-insensitive to
> > case-sensitive. Now the collation mode is set to case-sensitive, but the
> > above example still doesn't work...
> >
> > Does somebody have an idea how to reach real case-sensitivity in SQL
> Server?
> > By the way the database in SQL Server is restored from a file which was
> > backupped from my SQL Server, when the collation mode was set to
> > case-insensitive.
> >
> > Thanks in advance
> >
> > Robert
>
>|||Robert Möstl wrote:
> After I changed collation mode to case-sensitive with rebuildm.exe I
> generated a new database. Then I created a new table. Now when I want
> to query this table the tablename in the FROM clause have to be
> written just like it was in the CREATE TABLE statement. Queries on a
> string column are no case-sensitive.
> My problem is that I have to migrate an existing application based on
> Oracle. There the tablenames in the SQL statements are written in
> upper case or lower case. In Oracle it doesn't matter.
> How can I reach in SQL Server that I can write tablenames in upper or
> lower case but although the queries of string columns are
> case-sensitive?
> If this is not possible, I have to cancel this migration project.
> Regards,
> Robert
> "Adam Machanic" wrote:
>> How did you change the collation? In SQL Server, the collation of a
>> database is the default for objects created in that database -- but
>> only a default. Every object that can have a collation can have a
>> different collation. Every column, for instance, will maintain its
>> own. Whether that's the default or something else is up to you...
>> It sounds as though you may need to change the collation for the
>> column you're indexing. Although it may be better for you to start
>> over and re-create the database with a case-sensitive collation to
>> begin with, so that every column has the correct collation.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Robert Möstl" <Robert Mstl@.discussions.microsoft.com> wrote in
>> message news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
>> Hello gurus!
>> I have to migrate my DB from Oracle to SQL Server 2000. There are
>> some unique indexes on some String columns of my DB-Schema. In
>> Oracle the unique index constraint concerns case-sensitivity but
>> SQL Server does not.
>> So when I have to migrate the two values 'A' and 'a' into a column
>> with a unique index I got an SQL error concerning the uniqueness of
>> the index.
>> I already successfully changed the collation mode from
>> case-insensitive to case-sensitive. Now the collation mode is set
>> to case-sensitive, but the above example still doesn't work...
>> Does somebody have an idea how to reach real case-sensitivity in
>> SQL Server? By the way the database in SQL Server is restored from
>> a file which was backupped from my SQL Server, when the collation
>> mode was set to case-insensitive.
>> Thanks in advance
>> Robert
Not sure what collation you used, but when I select
Collation=SQL_Latin1_General_CP1_CS_AS both my table names and column
names are case-sensitive. If you're talking about string comparisons
then they will only match when there's an exact match:
For example:
create table testing (col1 nvarchar(50))
go
insert into testing values (N'ABC123')
insert into testing values (N'aBC123')
insert into testing values (N'ABC')
insert into testing values (N'abc')
select * from testing
go
select * from testing where col1 = N'ABC' -- matches one row
select * from testing where col1 = N'abc' -- matches one row
select * from testing where col1 LIKE N'A%' -- matches two rows
Drop table testing
BTW, you didn't need to rebuild the master database. You could have just
created a new database using the collation you want. Which one are you
using?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Case-sensitivity

Hello gurus!
I have to migrate my DB from Oracle to SQL Server 2000. There are some
unique indexes on some String columns of my DB-Schema. In Oracle the unique
index constraint concerns case-sensitivity but SQL Server does not.
So when I have to migrate the two values 'A' and 'a' into a column with a
unique index I got an SQL error concerning the uniqueness of the index.
I already successfully changed the collation mode from case-insensitive to
case-sensitive. Now the collation mode is set to case-sensitive, but the
above example still doesn't work...
Does somebody have an idea how to reach real case-sensitivity in SQL Server?
By the way the database in SQL Server is restored from a file which was
backupped from my SQL Server, when the collation mode was set to
case-insensitive.
Thanks in advance
Robert
How did you change the collation? In SQL Server, the collation of a
database is the default for objects created in that database -- but only a
default. Every object that can have a collation can have a different
collation. Every column, for instance, will maintain its own. Whether
that's the default or something else is up to you... It sounds as though you
may need to change the collation for the column you're indexing. Although
it may be better for you to start over and re-create the database with a
case-sensitive collation to begin with, so that every column has the correct
collation.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Robert Mstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> Hello gurus!
> I have to migrate my DB from Oracle to SQL Server 2000. There are some
> unique indexes on some String columns of my DB-Schema. In Oracle the
unique
> index constraint concerns case-sensitivity but SQL Server does not.
> So when I have to migrate the two values 'A' and 'a' into a column with a
> unique index I got an SQL error concerning the uniqueness of the index.
> I already successfully changed the collation mode from case-insensitive to
> case-sensitive. Now the collation mode is set to case-sensitive, but the
> above example still doesn't work...
> Does somebody have an idea how to reach real case-sensitivity in SQL
Server?
> By the way the database in SQL Server is restored from a file which was
> backupped from my SQL Server, when the collation mode was set to
> case-insensitive.
> Thanks in advance
> Robert
|||I changed collation with rbuildm.exe. I tried a few SQL collations which are
case-sensitive. Then I created a new database and created a new table. Now
the collation is case-sensitive. That's good.
But it's bad that now also the tablename in the FROM clause of an SQL
Statement have to be written exactly like it was created.
Example:
CREATE TABLE OBJ_STATUS (
obj_status_id int NOT NULL,
name varchar(50) NOT NULL,
note varchar(250) NULL,
)
SELECT * FROM obj_status -> this does not work
SELECT * FROM OBJ_STATUS -> this works
My problem is that I have to migrate an application which was based on
Oracle till now. In Oracle for example the tablename in the FROM clause could
be upper or lower case, it doesn't matter, and when I query a string column
it is case-sensitive.
How can I reach such an 'case-sensitivity status'?
If this is not possible it is not practicable to migrate to SQL Server.
Regards Robert
"Adam Machanic" wrote:

> How did you change the collation? In SQL Server, the collation of a
> database is the default for objects created in that database -- but only a
> default. Every object that can have a collation can have a different
> collation. Every column, for instance, will maintain its own. Whether
> that's the default or something else is up to you... It sounds as though you
> may need to change the collation for the column you're indexing. Although
> it may be better for you to start over and re-create the database with a
> case-sensitive collation to begin with, so that every column has the correct
> collation.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Robert M?stl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> unique
> Server?
>
>
|||You've made your entire server case-sensitive. You can control default
collation at various levels:
server
database
column
statement
Look up the COLLATE keyword in Books Online.
You should put your server back to case-insensitive collation and control
the collation at the column level instead.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Robert Mstl" <RobertMstl@.discussions.microsoft.com> wrote in message
news:7DB932A6-846D-41CE-8F9E-44ED68A91681@.microsoft.com...
> I changed collation with rbuildm.exe. I tried a few SQL collations which
are
> case-sensitive. Then I created a new database and created a new table. Now
> the collation is case-sensitive. That's good.
> But it's bad that now also the tablename in the FROM clause of an SQL
> Statement have to be written exactly like it was created.
> Example:
> CREATE TABLE OBJ_STATUS (
> obj_status_id int NOT NULL,
> name varchar(50) NOT NULL,
> note varchar(250) NULL,
> )
> SELECT * FROM obj_status -> this does not work
> SELECT * FROM OBJ_STATUS -> this works
> My problem is that I have to migrate an application which was based on
> Oracle till now. In Oracle for example the tablename in the FROM clause
could
> be upper or lower case, it doesn't matter, and when I query a string
column[vbcol=seagreen]
> it is case-sensitive.
> How can I reach such an 'case-sensitivity status'?
> If this is not possible it is not practicable to migrate to SQL Server.
> Regards Robert
>
> "Adam Machanic" wrote:
a[vbcol=seagreen]
you[vbcol=seagreen]
Although[vbcol=seagreen]
correct[vbcol=seagreen]
with a[vbcol=seagreen]
index.[vbcol=seagreen]
case-insensitive to[vbcol=seagreen]
the[vbcol=seagreen]
was[vbcol=seagreen]
|||After I changed collation mode to case-sensitive with rebuildm.exe I
generated a new database. Then I created a new table. Now when I want to
query this table the tablename in the FROM clause have to be written just
like it was in the CREATE TABLE statement. Queries on a string column are no
case-sensitive.
My problem is that I have to migrate an existing application based on
Oracle. There the tablenames in the SQL statements are written in upper case
or lower case. In Oracle it doesn't matter.
How can I reach in SQL Server that I can write tablenames in upper or lower
case but although the queries of string columns are case-sensitive?
If this is not possible, I have to cancel this migration project.
Regards,
Robert
"Adam Machanic" wrote:

> How did you change the collation? In SQL Server, the collation of a
> database is the default for objects created in that database -- but only a
> default. Every object that can have a collation can have a different
> collation. Every column, for instance, will maintain its own. Whether
> that's the default or something else is up to you... It sounds as though you
> may need to change the collation for the column you're indexing. Although
> it may be better for you to start over and re-create the database with a
> case-sensitive collation to begin with, so that every column has the correct
> collation.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Robert M?stl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> unique
> Server?
>
>
|||Robert M?stl wrote:[vbcol=seagreen]
> After I changed collation mode to case-sensitive with rebuildm.exe I
> generated a new database. Then I created a new table. Now when I want
> to query this table the tablename in the FROM clause have to be
> written just like it was in the CREATE TABLE statement. Queries on a
> string column are no case-sensitive.
> My problem is that I have to migrate an existing application based on
> Oracle. There the tablenames in the SQL statements are written in
> upper case or lower case. In Oracle it doesn't matter.
> How can I reach in SQL Server that I can write tablenames in upper or
> lower case but although the queries of string columns are
> case-sensitive?
> If this is not possible, I have to cancel this migration project.
> Regards,
> Robert
> "Adam Machanic" wrote:
Not sure what collation you used, but when I select
Collation=SQL_Latin1_General_CP1_CS_AS both my table names and column
names are case-sensitive. If you're talking about string comparisons
then they will only match when there's an exact match:
For example:
create table testing (col1 nvarchar(50))
go
insert into testing values (N'ABC123')
insert into testing values (N'aBC123')
insert into testing values (N'ABC')
insert into testing values (N'abc')
select * from testing
go
select * from testing where col1 = N'ABC' -- matches one row
select * from testing where col1 = N'abc' -- matches one row
select * from testing where col1 LIKE N'A%' -- matches two rows
Drop table testing
BTW, you didn't need to rebuild the master database. You could have just
created a new database using the collation you want. Which one are you
using?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Case-sensitivity

Hello gurus!
I have to migrate my DB from Oracle to SQL Server 2000. There are some
unique indexes on some String columns of my DB-Schema. In Oracle the unique
index constraint concerns case-sensitivity but SQL Server does not.
So when I have to migrate the two values 'A' and 'a' into a column with a
unique index I got an SQL error concerning the uniqueness of the index.
I already successfully changed the collation mode from case-insensitive to
case-sensitive. Now the collation mode is set to case-sensitive, but the
above example still doesn't work...
Does somebody have an idea how to reach real case-sensitivity in SQL Server?
By the way the database in SQL Server is restored from a file which was
backupped from my SQL Server, when the collation mode was set to
case-insensitive.
Thanks in advance
RobertHow did you change the collation? In SQL Server, the collation of a
database is the default for objects created in that database -- but only a
default. Every object that can have a collation can have a different
collation. Every column, for instance, will maintain its own. Whether
that's the default or something else is up to you... It sounds as though you
may need to change the collation for the column you're indexing. Although
it may be better for you to start over and re-create the database with a
case-sensitive collation to begin with, so that every column has the correct
collation.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Robert Mstl" <Robert Mstl@.discussions.microsoft.com> wrote in message
news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> Hello gurus!
> I have to migrate my DB from Oracle to SQL Server 2000. There are some
> unique indexes on some String columns of my DB-Schema. In Oracle the
unique
> index constraint concerns case-sensitivity but SQL Server does not.
> So when I have to migrate the two values 'A' and 'a' into a column with a
> unique index I got an SQL error concerning the uniqueness of the index.
> I already successfully changed the collation mode from case-insensitive to
> case-sensitive. Now the collation mode is set to case-sensitive, but the
> above example still doesn't work...
> Does somebody have an idea how to reach real case-sensitivity in SQL
Server?
> By the way the database in SQL Server is restored from a file which was
> backupped from my SQL Server, when the collation mode was set to
> case-insensitive.
> Thanks in advance
> Robert|||I changed collation with rbuildm.exe. I tried a few SQL collations which are
case-sensitive. Then I created a new database and created a new table. Now
the collation is case-sensitive. That's good.
But it's bad that now also the tablename in the FROM clause of an SQL
Statement have to be written exactly like it was created.
Example:
CREATE TABLE OBJ_STATUS (
obj_status_id int NOT NULL,
name varchar(50) NOT NULL,
note varchar(250) NULL,
)
SELECT * FROM obj_status -> this does not work
SELECT * FROM OBJ_STATUS -> this works
My problem is that I have to migrate an application which was based on
Oracle till now. In Oracle for example the tablename in the FROM clause coul
d
be upper or lower case, it doesn't matter, and when I query a string column
it is case-sensitive.
How can I reach such an 'case-sensitivity status'?
If this is not possible it is not practicable to migrate to SQL Server.
Regards Robert
"Adam Machanic" wrote:

> How did you change the collation? In SQL Server, the collation of a
> database is the default for objects created in that database -- but only a
> default. Every object that can have a collation can have a different
> collation. Every column, for instance, will maintain its own. Whether
> that's the default or something else is up to you... It sounds as though y
ou
> may need to change the collation for the column you're indexing. Although
> it may be better for you to start over and re-create the database with a
> case-sensitive collation to begin with, so that every column has the corre
ct
> collation.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Robert M?stl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> unique
> Server?
>
>|||You've made your entire server case-sensitive. You can control default
collation at various levels:
server
database
column
statement
Look up the COLLATE keyword in Books Online.
You should put your server back to case-insensitive collation and control
the collation at the column level instead.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Robert Mstl" <RobertMstl@.discussions.microsoft.com> wrote in message
news:7DB932A6-846D-41CE-8F9E-44ED68A91681@.microsoft.com...
> I changed collation with rbuildm.exe. I tried a few SQL collations which
are
> case-sensitive. Then I created a new database and created a new table. Now
> the collation is case-sensitive. That's good.
> But it's bad that now also the tablename in the FROM clause of an SQL
> Statement have to be written exactly like it was created.
> Example:
> CREATE TABLE OBJ_STATUS (
> obj_status_id int NOT NULL,
> name varchar(50) NOT NULL,
> note varchar(250) NULL,
> )
> SELECT * FROM obj_status -> this does not work
> SELECT * FROM OBJ_STATUS -> this works
> My problem is that I have to migrate an application which was based on
> Oracle till now. In Oracle for example the tablename in the FROM clause
could
> be upper or lower case, it doesn't matter, and when I query a string
column[vbcol=seagreen]
> it is case-sensitive.
> How can I reach such an 'case-sensitivity status'?
> If this is not possible it is not practicable to migrate to SQL Server.
> Regards Robert
>
> "Adam Machanic" wrote:
>
a[vbcol=seagreen]
you[vbcol=seagreen]
Although[vbcol=seagreen]
correct[vbcol=seagreen]
with a[vbcol=seagreen]
index.[vbcol=seagreen]
case-insensitive to[vbcol=seagreen]
the[vbcol=seagreen]
was[vbcol=seagreen]|||After I changed collation mode to case-sensitive with rebuildm.exe I
generated a new database. Then I created a new table. Now when I want to
query this table the tablename in the FROM clause have to be written just
like it was in the CREATE TABLE statement. Queries on a string column are no
case-sensitive.
My problem is that I have to migrate an existing application based on
Oracle. There the tablenames in the SQL statements are written in upper case
or lower case. In Oracle it doesn't matter.
How can I reach in SQL Server that I can write tablenames in upper or lower
case but although the queries of string columns are case-sensitive?
If this is not possible, I have to cancel this migration project.
Regards,
Robert
"Adam Machanic" wrote:

> How did you change the collation? In SQL Server, the collation of a
> database is the default for objects created in that database -- but only a
> default. Every object that can have a collation can have a different
> collation. Every column, for instance, will maintain its own. Whether
> that's the default or something else is up to you... It sounds as though y
ou
> may need to change the collation for the column you're indexing. Although
> it may be better for you to start over and re-create the database with a
> case-sensitive collation to begin with, so that every column has the corre
ct
> collation.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Robert M?stl" <Robert Mstl@.discussions.microsoft.com> wrote in message
> news:F266E63B-97A9-4E92-8D05-3B9951BD94EB@.microsoft.com...
> unique
> Server?
>
>|||Robert M?stl wrote:[vbcol=seagreen]
> After I changed collation mode to case-sensitive with rebuildm.exe I
> generated a new database. Then I created a new table. Now when I want
> to query this table the tablename in the FROM clause have to be
> written just like it was in the CREATE TABLE statement. Queries on a
> string column are no case-sensitive.
> My problem is that I have to migrate an existing application based on
> Oracle. There the tablenames in the SQL statements are written in
> upper case or lower case. In Oracle it doesn't matter.
> How can I reach in SQL Server that I can write tablenames in upper or
> lower case but although the queries of string columns are
> case-sensitive?
> If this is not possible, I have to cancel this migration project.
> Regards,
> Robert
> "Adam Machanic" wrote:
>
Not sure what collation you used, but when I select
Collation=SQL_Latin1_General_CP1_CS_AS both my table names and column
names are case-sensitive. If you're talking about string comparisons
then they will only match when there's an exact match:
For example:
create table testing (col1 nvarchar(50))
go
insert into testing values (N'ABC123')
insert into testing values (N'aBC123')
insert into testing values (N'ABC')
insert into testing values (N'abc')
select * from testing
go
select * from testing where col1 = N'ABC' -- matches one row
select * from testing where col1 = N'abc' -- matches one row
select * from testing where col1 LIKE N'A%' -- matches two rows
Drop table testing
BTW, you didn't need to rebuild the master database. You could have just
created a new database using the collation you want. Which one are you
using?
David Gugick
Quest Software
www.imceda.com
www.quest.com