case sensitive server - problems running scripts

I have just been given a new SQL Server 2000 box to look after in production. I just tried to run a standard t-sql script I use for setting up backup jobs and so on. However, it failed with a long list of errors - quite a surprise at first since I have run the same script on many other servers wihtout a hitch. On close examination, the problem appears to be that the new serer is setup with a server default collation... Latin1_General_BIN (I think a binary based collation makes this a case sensitive server).

This is quite an urgenet one since I have to get this wrapped up today. I don't think I can change the server's default collation without a lot of red-tape. Is there a quick way to run my scripts in a 'case insenstive' context within Query Analyzer? If so, how?

Thanks in advance,

CliveWe have that problem all of the time with the PeopleSoft servers. They can only run with binary sort orders.

If you find a way to work around the case sensitivity, please let me know!

-PatP|||Ok, well thanks anyway. It seems a bit over the top for a server to be set to case senstive by default. Even the master database is case sensitive as a result. I can't see why the s/w vendor couldn't have left the default server collation alone and just been a bit more selective about what tables/columns actually needed a _BIN collation. Laziness I suppose.

Clive|||Binary sort order os a bit faster than case sensitive, as it bypasses all of the checks for 'A' = 'a'. That aside, you should probably go over the script and change the identifiers (table names, column names) to be the proper case (fortunately this is all lowercase for system tables), and change all of your variables to be the same case throughout. The variables can be doe with find/replace very easily. After that, you would have a new script that would work on Latin1_General_CI_AS, _BIN, and _CS_AS servers.

Since i have a couple of case sensitive servers around, I have had to write all of my own scripts to be able to handle case sensitivity. Even the ones that "should" only run on the case insensitive ones. It is a good habit to get into.|||All of our main production servers are Latin1_General_BIN_437 which is actually what yours probably is. This was recommended back in the old days when it was based on 6.5. Since they reworked the architecture, It doesn't really make any difference on speed. Since it's not the industry standard to use this, it's just a big pain. Anyway, I've learned to use all upper-case for my commands and lower-case for my objects. We have a program at work that will automagically convert all your code for you. I'll see if I can "share" it with you. Saves soooooooo much time.

CASE in T-SQL

I'm upsizing Access to SQL Server 2005.
I need to convert the following query:
SELECT
tblHistory.AutoNumber,
tblHistory.InputDate,
IIf([tblHistory].[TaxType]="111" Or [tblHistory].[taxtype]="222","ABC",IIf([tblHistory].[taxtype]="AAA","AAA","BBB")) AS 1TaxType FROM tblHistory;

CASE in T SQL:
SELECT tblHistory.AutoNumber,
tblHistory.InputDate,
'1TaxType' =
CASE
WHEN [tblHistory].[TaxType] = '111' THEN 'ABC'
WHEN [tblHistory].[TaxType] = '222' THEN 'ABC'
ELSE
CASE
WHEN [tblHistory].[taxtype] = 'AAA' THEN 'AAA'
ELSE 'BBB'
END
END
FROM tblHistory;

Is this correct?

It is fine. But you can simplify it further like:

SELECT h.AutoNumber,
h.InputDate,
CASE
WHEN h.[TaxType] IN ('111', '222') THEN 'ABC'
WHEN h.[taxtype] = 'AAA' THEN 'AAA'
ELSE 'BBB'
END as [1TaxType]
FROM tblHistory as h;

I also modified the SELECT statement syntax to use table aliases which makes it easier to read and change the proprietary column alias syntax also.

Case function in T-SQL

Wondering if possible to use case function in where clause of T-SQL statement. I have this application that needs to get the recordsets based on the day of the date selected that is @.dDay in one of these set of values {0,7,14,21, more). Though, I know that if I use either of this:

datediff(dd,@.startdate,@.today) = @.dDay

or convert(varchar(12), @.startDate, 101) between convert(varchar(12), @.today, 101) and convert(varchar(12), dateadd(dd,@.dDay, @.today), 101)

It will work for only the values that are specific as in number but what of if "more" is selected which means that from that day upward, can i use this T-SQL to accomplish this. PLease help

Any suggestion is welcome. thanks

I think I got confused.. can you explain a bit more..?

|||

i too am confused by what you want but just keep in mind that if you include a function your where clause you will likely disallow the use of indexes on your date field, if there is one

|||

 
SELECT *FROM tblWHERE StartDate>=DATEADD(DAY,DATEDIFF(DAY, 0, @.today), 0)AND StartDate<CASEWHEN @.dday='more'THEN'9999-12-31'ELSEDATEADD(DAY,DATEDIFF(DAY, 0, @.today), @.dday+1)END
 

|||

I mean this; I have a textbox with a dropdownlist control to search my databasein which the user can select either "Today", "7 days", "14 days", and "More". If a user select either of the list item except "More", i think i can easily use the"between" and"and" to get the recordsets or usedatediff function to get the recordsets, but what of if the user select"more" which is not bounded but means "from that day and on", how can one control this by using the same T-SQL statement to return the recordset from the database. I am not ad-hoc method to access my datastore but stored procedure.

So i am looking at usingcase function to make the decision and return the recordsets. Is it possible or is there any simpler way?.

Please Help. Urgent

|||

If I understand you correctly, I would use a second stored procedure for the "More" case. IOW,

select @.SomeDate = datediff(....)

then

select ...... from..... where DateField < @.SomeDate

If the user selects a specific number of days, select proc1, else select proc2

Another option is to use a big if statement with 2 selects (to handle either case), but that won't optimize as well

|||

If that in the case, I would change the listitem in the dropdown with the "more" text to have an extremely large value, like say 100000 (250ish years). Then you don't have to have anything complicated on the SQL side. That way you can still have "more" displayed in the listbox, but when it actually goes to send it to the SqlDatasource, it will send 100000 instead of the word more.

Carry Over Hours Monthend

I was wondering if anyone knew if this was possible in T-SQL. I need to
calculate the total number of hours carried over from a previous month,
based on a 6 hours per day rate. Some examples,
Job has 12 hours total and starts on 2/28/2005 it would carry over 6 hours
into March.
Job has 5 hours total and starts on 2/28/2005 it would carry over 0 hours in
March.
Job has 30 hours total and starts on 2/25/2005 it would carry over 18 hours
(30-12).
Note that I have to ignore wends as they are not work days. Thanks.
Davidhttp://www.aspfaq.com/etiquette.asp?id=5006
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%23TzCw%23ZHFHA.1096@.tk2msftngp13.phx.gbl...
>I was wondering if anyone knew if this was possible in T-SQL. I need to
>calculate the total number of hours carried over from a previous month,
>based on a 6 hours per day rate. Some examples,
> Job has 12 hours total and starts on 2/28/2005 it would carry over 6 hours
> into March.
> Job has 5 hours total and starts on 2/28/2005 it would carry over 0 hours
> in March.
> Job has 30 hours total and starts on 2/25/2005 it would carry over 18
> hours (30-12).
> Note that I have to ignore wends as they are not work days. Thanks.
> David
>|||Roji,
Your link took me to a DDL example. Are you sure that is correct?
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David,
What Roji is trying to tell you is to post some DDL, sample data and
expected result if you need help with this problem.
AMB
"David" wrote:

> Roji,
> Your link took me to a DDL example. Are you sure that is correct?
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>|||Below is what I have done so far:
SELECT dbo.RepairOrder.RecordID, dbo.vw_LaborTotalsAll.TotalHours,
dbo.RepairOrder.RepairStartDate,
dbo.RepairOrder.RepairCompleteDate
FROM dbo.RepairOrder INNER JOIN
dbo.vw_LaborTotalsAll ON dbo.RepairOrder.RecordID
= dbo.vw_LaborTotalsAll.RecordID
WHERE (dbo.RepairOrder.RepairStartDate > CONVERT(DATETIME,
'2005-02-01 00:00:00', 102)) AND (dbo.RepairOrder.RepairCompleteDate IS
NULL) AND
(dbo.RepairOrder.RepairStartDate <
CONVERT(DATETIME, '2005-03-01 00:00:00', 102))
I assume I need something else in WHERE clause as I only need to get
back records that have more hours left than 6 x the number of work days
left in the month.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

carrier return + line feed in a varchar

Hi

how can I split a line into 2 lines in t-sql. etc
Set @.text1 = 'here's line one' + (carriere return + line feed) + 'here's line two'

I will be using it to send a mail from sql server (2000 sp4), so i can build a nice looking mail

> Set @.text1 = 'here's line one' + (carriere return + line feed) + 'here's

> line two'

Set @.text1 = 'here''s one line' + CHAR(13) + CHAR(10) + 'here''s line two';

|||thanks a lot

Caputre T-SQL Error Message in Stroed Proc

I need to capture the exact error message whenever the error occur while exectuing the SQL query. I am able to get the error code and error message from sysmessages table but error message contain some input values like '%ls'. I need to get the exact mess
age.
Any help would be appricated.
Thanks in Advanced
Not possible: http://vyaskn.tripod.com/programming_faq.htm#q7
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jitendra" <Jitendra@.discussions.microsoft.com> wrote in message
news:16CBD76A-C566-407E-A554-945809530259@.microsoft.com...
> I need to capture the exact error message whenever the error occur while
exectuing the SQL query. I am able to get the error code and error message
from sysmessages table but error message contain some input values like
'%ls'. I need to get the exact message.
> Any help would be appricated.
> Thanks in Advanced
|||I dont know if there's a way where the stored proc woudl run to completion
if an error occurs. It would ideally exit and print the message. So unless
you want to say, direct your results (inc error message) to a txt file?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Caputre T-SQL Error Message in Stroed Proc

I need to capture the exact error message whenever the error occur while exe
ctuing the SQL query. I am able to get the error code and error message from
sysmessages table but error message contain some input values like '%ls'. I
need to get the exact mess
age.
Any help would be appricated.
Thanks in AdvancedNot possible: http://vyaskn.tripod.com/programming_faq.htm#q7
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jitendra" <Jitendra@.discussions.microsoft.com> wrote in message
news:16CBD76A-C566-407E-A554-945809530259@.microsoft.com...
> I need to capture the exact error message whenever the error occur while
exectuing the SQL query. I am able to get the error code and error message
from sysmessages table but error message contain some input values like
'%ls'. I need to get the exact message.
> Any help would be appricated.
> Thanks in Advanced|||I dont know if there's a way where the stored proc woudl run to completion
if an error occurs. It would ideally exit and print the message. So unless
you want to say, direct your results (inc error message) to a txt file?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.