collation in sql 2005

Does anyone know if there is a command in query analizer for sql server 2005 (Express) that changes the collation of all the char (varchar, nvarchar etc) in a table or in a database. And I don't mean to set the collation when creating a database, but to modify a database's collation seting.

thank you

This information might help:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_3zw3.asp

Buck Woody

Collation

Is it ok to change the database collation from ...Latin1_CP1_AS to
Latin1_General_BIN?
Just issueing the ALTER DABASE ... command is sufficient or I need to change
something else?
Will I be able to retrieve case sensitive data? For ex. if the field name has
the value of 'Bryan Williams' will I retrieve the record if I look for name
like 'bryan*'? or I will get the record only if the query says name like
'Bry%'
Thank you,
-MeIF you change to CASE SENSITIVE, then all queries will be CASE SENSITIVE by
default. Searching for 'bryan' will NOT find 'Bryan'.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Me" <Me@.discussions.microsoft.com> wrote in message
news:4638D07C-339D-4BE6-8264-20DAAED15F52@.microsoft.com...
> Is it ok to change the database collation from ...Latin1_CP1_AS to
> Latin1_General_BIN?
>
> Just issueing the ALTER DABASE ... command is sufficient or I need to
> change
> something else?
> Will I be able to retrieve case sensitive data? For ex. if the field name
> has
> the value of 'Bryan Williams' will I retrieve the record if I look for
> name
> like 'bryan*'? or I will get the record only if the query says name like
> 'Bry%'
> Thank you,
> -Me
>

Collation

Is it ok to change the database collation from ...Latin1_CP1_AS to
Latin1_General_BIN?
Just issueing the ALTER DABASE ... command is sufficient or I need to change
something else?
Will I be able to retrieve case sensitive data? For ex. if the field name has
the value of 'Bryan Williams' will I retrieve the record if I look for name
like 'bryan*'? or I will get the record only if the query says name like
'Bry%'
Thank you,
-Me
IF you change to CASE SENSITIVE, then all queries will be CASE SENSITIVE by
default. Searching for 'bryan' will NOT find 'Bryan'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Me" <Me@.discussions.microsoft.com> wrote in message
news:4638D07C-339D-4BE6-8264-20DAAED15F52@.microsoft.com...
> Is it ok to change the database collation from ...Latin1_CP1_AS to
> Latin1_General_BIN?
>
> Just issueing the ALTER DABASE ... command is sufficient or I need to
> change
> something else?
> Will I be able to retrieve case sensitive data? For ex. if the field name
> has
> the value of 'Bryan Williams' will I retrieve the record if I look for
> name
> like 'bryan*'? or I will get the record only if the query says name like
> 'Bry%'
> Thank you,
> -Me
>

Collation

Is it ok to change the database collation from ...Latin1_CP1_AS to
Latin1_General_BIN?
Just issueing the ALTER DABASE ... command is sufficient or I need to change
something else?
Will I be able to retrieve case sensitive data? For ex. if the field name ha
s
the value of 'Bryan Williams' will I retrieve the record if I look for name
like 'bryan*'? or I will get the record only if the query says name like
'Bry%'
Thank you,
-MeIF you change to CASE SENSITIVE, then all queries will be CASE SENSITIVE by
default. Searching for 'bryan' will NOT find 'Bryan'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Me" <Me@.discussions.microsoft.com> wrote in message
news:4638D07C-339D-4BE6-8264-20DAAED15F52@.microsoft.com...
> Is it ok to change the database collation from ...Latin1_CP1_AS to
> Latin1_General_BIN?
>
> Just issueing the ALTER DABASE ... command is sufficient or I need to
> change
> something else?
> Will I be able to retrieve case sensitive data? For ex. if the field name
> has
> the value of 'Bryan Williams' will I retrieve the record if I look for
> name
> like 'bryan*'? or I will get the record only if the query says name like
> 'Bry%'
> Thank you,
> -Me
>

cmdsql - Create a text file without Informational Messages

Hello all

I have a sql file that I want execute by using the cmdsql command line. But when I create a text file I receive two Informational Messages:

1."Changed database context to 'DataBaseName'."

2.(2 row(s) affected)

How can I ignore these messages in my text file? there is a parametter or something elese to configure to avoir these Informational Messages?

Sorry is not cmdsql is Sqlcmd :)

CmdExec Job Step in Sql Server2000

Thanks for the invitation to post a question, so I will post one.

I need to create a job step that uses cmdExec.

This is the command line I entering:
D:\odbc\TimeClockUpdates\bin\Release\TimeKeepingNo nLogouts.exe

When I run this job the job fails. When I look at the job history, the only information I get is the date and time, user that ran the job and the fact that it failed. I haven't been able to get any CmdExec job to run at all. Can anyone tell me what I'm doing wrong?

Facts.
1. This exact same command is used by my network administrator using windows scheduler on the server. The only reason he wants me to create an sql server job is because it's mostly sql functions.
2. I know the Sql Server agent is running, because I have other jobs that are run.
3. I have verified that I have permission to run the file because I can go to the actual directory and run the exe.
4. Do I need to enclose my command in quotes i.e. D:\odbc\TimeClockUpdates\bin\Release\TimeKeepingN onLogouts.exe
5. the path of the file I need to run is the path on the server and not the path on my local machine.

If you need any other information, please let me know
Thanks for you help
GEMyou should post the code.

cmdexec requires that the security account executing job have sysadmin permissions.|||I wish I knew what code you mean me to post

This is a job I created using enterprise manager to create the job.
1. I entered the Job Name: UpdateEmployeeLogins
2. Job type: Operation System Command (CmdExec)
3. Command: D:\odbc\TimeClockUpdates\bin\Release\TimeKeepingNo nLogouts.exe

I did all this through Enterprise Manager. What code do you want me to post?

Thanks
GEM|||change the command type to t-sql and try this...

EXEC xp_cmdshell 'D:\odbc\TimeClockUpdates\bin\Release\TimeKeepingN o nLogouts.exe'|||I did as you suggested and but the same result. I then ran that command line with query analyzer and got an "Access Denied" Error so that means I must have a problem somewhere with permissions. This doesn't make a whole lot of sense since I can the exe when clicking on it. I will have to get with out network administrator who's not available right now.

Your help was excellent, I hope you'll be around later to help me with this if I need it.

Thanks
GEM|||see my 1st post.|||This is a job I created using enterprise manager to create the job.
1. I entered the Job Name: UpdateEmployeeLogins
2. Job type: Operation System Command (CmdExec)
3. Command: D:\odbc\TimeClockUpdates\bin\Release\TimeKeepingNo nLogouts.exe

This method should work. It should not be necessary to use xp_cmdshell in your job.
As Thrassy stated, this is likely due to a permissions issue regarding the account that the SQL Agent service is running under.|||As everyone else said, make sure that the account executing the command under the SQL service privileges has the appropriate access to the executable. I bet if you look under NTFS security on the target .exe you'll find the problem.

CmdExec job failed

Hi,
I created a job on sql server 2000, using the Operating System Command
option, under the command box, I inserted the command cmd /C
e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get this
error message Executed as user:SEFDBA\sqlseragent.
'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal or
external command, operable program or batch file. Process Exit Code 1.
The step failed. Please help, what am I doing wrong? Thanks.
New SQL Server DBA
Check the path and .bat file spelling? Type the exact same thing in from a
CMD prompt and see if it works.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:50ECA2ED-CAD2-4077-9C6F-BB606D4951F4@.microsoft.com...
> Hi,
> I created a job on sql server 2000, using the Operating System Command
> option, under the command box, I inserted the command cmd /C
> e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get
> this
> error message Executed as user:SEFDBA\sqlseragent.
> 'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal
> or
> external command, operable program or batch file. Process Exit Code 1.
> The step failed. Please help, what am I doing wrong? Thanks.
> --
> New SQL Server DBA

CmdExec job failed

Hi,
I created a job on sql server 2000, using the Operating System Command
option, under the command box, I inserted the command cmd /C
e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get this
error message Executed as user:SEFDBA\sqlseragent.
'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal or
external command, operable program or batch file. Process Exit Code 1.
The step failed. Please help, what am I doing wrong? Thanks.
--
New SQL Server DBACheck the path and .bat file spelling? Type the exact same thing in from a
CMD prompt and see if it works.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:50ECA2ED-CAD2-4077-9C6F-BB606D4951F4@.microsoft.com...
> Hi,
> I created a job on sql server 2000, using the Operating System Command
> option, under the command box, I inserted the command cmd /C
> e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get
> this
> error message Executed as user:SEFDBA\sqlseragent.
> 'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal
> or
> external command, operable program or batch file. Process Exit Code 1.
> The step failed. Please help, what am I doing wrong? Thanks.
> --
> New SQL Server DBA

CmdExec job failed

Hi,
I created a job on sql server 2000, using the Operating System Command
option, under the command box, I inserted the command cmd /C
e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get this
error message Executed as user:SEFDBA\sqlseragent.
'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal or
external command, operable program or batch file. Process Exit Code 1.
The step failed. Please help, what am I doing wrong? Thanks.
--
New SQL Server DBACheck the path and .bat file spelling? Type the exact same thing in from a
CMD prompt and see if it works.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:50ECA2ED-CAD2-4077-9C6F-BB606D4951F4@.microsoft.com...
> Hi,
> I created a job on sql server 2000, using the Operating System Command
> option, under the command box, I inserted the command cmd /C
> e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get
> this
> error message Executed as user:SEFDBA\sqlseragent.
> 'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal
> or
> external command, operable program or batch file. Process Exit Code 1.
> The step failed. Please help, what am I doing wrong? Thanks.
> --
> New SQL Server DBA

CmdExec issue

Hello,
I have a batch file in the operating system, which runs an osql command that
create a stored procedure, a view and a function on a database. It is with
-E trust connect. It works when I just run it on windows.
However, if I run in as a SQL Job by using the CmdExec, it could not create
any stored procedure, view or function. And there is no error return
neither. The account to run the job is the same NT account with local admin
rights on the server.
Does anyone have some idea about this matter?
Many Thanks,
LianneAre you running the Job from a different server?
If so you need to use -U and -P providing a username and password. I
have experienced this problem before, but apparently linked servers do
not pass the user via windows authentication.|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I have a batch file in the operating system, which runs an osql command
> that create a stored procedure, a view and a function on a database. It
> is with -E trust connect. It works when I just run it on windows.
> However, if I run in as a SQL Job by using the CmdExec, it could not
> create any stored procedure, view or function. And there is no error
> return neither. The account to run the job is the same NT account with
> local admin rights on the server.
Did the job fail or succeed?
In case it failed, there is a checkbox in the upper right of the View
History dialog which says "Show job steps" or somesuch. The output from
the job step should have the real error.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The job was running on the physical server. and the job return as
successful. but nothing was created on SQL Server side.
Thanks again,
Lianne
"Johnny D" wrote:

> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Hi John,
I change the -E to -U sa login, but I got this error message:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server
connection.
Any idea?
Many Thanks,
Lianne
"Johnny D" wrote:

> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:605603
Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I change the -E to -U sa login, but I got this error message:
> Login failed for user 'sa'. Reason: Not associated with a trusted SQL
> Server connection.
> Any idea?
That would indicate that SQL authentication is not enabled on the server.
Well, changing the autentication should not matter, so consider that a
dead end.
As for why your job does not work as expected, I'm afraid that with the
information you have provided, all we can offer is wild guesses. What
is the exact command line you are using?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Eriand,
the command line is below:
for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U sa -P
zhongshan -n -d test -i "%%f" >> Createcp.txt
this is the command I put in .bat file. on the same level, I have three sql
files, which creates a view, a user defined function and a stored procedure.
Also, I had set SQL Server running on both (SQL and NT authentication)
If you meed more informations, let me know.
Many Thanks,
Lianne
"Erland Sommarskog" wrote:

> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> That would indicate that SQL authentication is not enabled on the server.
> Well, changing the autentication should not matter, so consider that a
> dead end.
> As for why your job does not work as expected, I'm afraid that with the
> information you have provided, all we can offer is wild guesses. What
> is the exact command line you are using?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> the command line is below:
> for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U
> sa -P zhongshan -n -d test -i "%%f" >> Createcp.txt
> this is the command I put in .bat file. on the same level, I have three
> sql files, which creates a view, a user defined function and a stored
> procedure.
Real stupid question: the BAT file does include a CD to the directory
where you have the SQL, doesn't it?
I composed this file on my machine:
F:
cd \moje dokumenty\WWW\dynsearch
for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale -i "
%%f" >> Createcp.txt
And the I created a job of which the command text was
C:\temp\test.bat
I deleted all stored procedure in Northgale, and then started the job.
When I had completed the job, all my procedures were back.
In job history I saw this for the job step:
Executed as user: KESMETS\sql. ...search
F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Createcp.tx
t & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.txt &
.. Process Exit Code 0. The step succeeded.
A way to get more output from the job step is to go the Advanced tab
for the job step, and there select a output file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thank you very much for your suggection. I did not enclude the Cd line in
the .bat file since it work just fine when I run it through windows. My bad
.
I guess SQL is very piggy on directory specification then.
Thank you again. I appreciate your help very much!
Lianne
"Erland Sommarskog" wrote:

> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> Real stupid question: the BAT file does include a CD to the directory
> where you have the SQL, doesn't it?
> I composed this file on my machine:
> F:
> cd \moje dokumenty\WWW\dynsearch
> for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale
-i "%%f" >> Createcp.txt
> And the I created a job of which the command text was
> C:\temp\test.bat
> I deleted all stored procedure in Northgale, and then started the job.
> When I had completed the job, all my procedures were back.
> In job history I saw this for the job step:
>
> Executed as user: KES?METS?\sql. ...search
> F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Create
cp.txt & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.t
xt &... Process Exit Code 0. The step succeeded.
> A way to get more output from the job step is to go the Advanced tab
> for the job step, and there select a output file.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Clustered Active/Active in Single-user mode

What is the right procedure to start an instance of a clustered Active/Active Sql Server ?
The online-book unspecifically writes:
From a command prompt, enter:
sqlservr.exe -c -m
But some KB Technet articles say do start/stop SQL instance only from cluster administrator!
So how to do it ?
Thanks!
Michele Moglia
For normal operating procedures the proper method is to start from the
Cluster Administator.
Starting from the command prompt with sqlservr.exe -c -m is a trouble
shooting technique. It starts SQL Server as an application in single user
mode and as such would be pretty useless in a production environment.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Ok but if I have to restore the master db of the clustered sql server instance
I have to start the instance in single-user mode so can I start from command prompt
without any fear or problem ?
Thanks
Michele Moglia