I want to change collation_name of my SqlServer Database by running a script. But I don't know how.
Please help me.
ThanksYou should be able to do this:
ALTER DATABASE myDatabase COLLATE SQL_Latin1_General_CP1_CI_AS
Terrisql
Here is the script
*****************************************************************************************************************
<%@. LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<%
Dim DATA_PATH, Conn, DataRecords, email, user, pass, sendmail
'Maps to database. Change to your database path.
DATA_PATH=Server.Mappath("membersdb.mdb")
' Create and intiate data connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 30
Conn.Open "DBQ=" & DATA_PATH & ";Driver={Microsoft Access Driver (*.mdb)}; DriverId=25;MaxBufferSize=8192;Threads=20;", "admin", "password"
Set DataRecords = Server.CreateObject("ADODB.Recordset")
email=request.form("email")
'The magic query to look for registered members in the database
DataRecords.Open "SELECT * FROM MEMBERS WHERE email = '" & email & "'", Conn, 0, 1
%>
<%
'checks if email address exists in the database before sending a message.
if DataRecords.EOF then
%>
We could not find <%=email%> in our database.
<% Else %>
<%
'sets variables
email = request.form("email")
'chooses username and password from database that correspond to submitted email address.
user = DataRecords.Fields("usernames")
pass = DataRecords.Fields("password")
Set sendmail = Server.CreateObject("CDONTS.NewMail")
'put the webmaster address here
sendmail.From = "Someone@.somewhere.com"
'The mail is sent to the address entered in the previous page.
sendmail.To = email
'Enter the subject of your mail here
sendmail.Subject = "Membership Login Information"
'This is the content of thr message.
sendmail.Body = "Per your request your account login information is: " & vbCrlf & vbCrlf _
& "Username=" & user & vbCrlf _
& "Password=" & pass & vbCrlf
'this sets mail priority.... 0=low 1=normal 2=high
'sendmail.Importance = 3 commented out causes a error for a call or procedure (line 41)
sendmail.Send
%>
We just sent your login information to <%=email%>.
You should receive it shortly.
<%
' Close Data Access Objects and free connection variables
Conn.Close
Set DataRecords = Nothing
Set Conn = Nothing
Set sendmail = Nothing
%><%end if%>
***************************************************************************************************************
And here's my SQL connection stuff
Private Sub Class_Initialize()
ConnectionString = "Provider=SQLNCLI.1;Persist Security Info=False;User ID=sa;Initial Catalog=ABC;Data Source=xx.xx.xx.xx"
User = "usr"
Password = "pswrd"
Set Converter = New clsConverter
Converter.DateFormat = Array("yyyy", "-", "mm", "-", "dd", " ", "HH", ":", "nn", ":", "ss")
Converter.BooleanFormat = Array(1, 0, Empty)
Set objConnection = Server.CreateObject("ADODB.Connection")
Database = "MSSQLServer"
Set Errors = New clsErrors
End Sub
****************************************************************************************************************
sure would appreciate some help!
You can use a different driver in the connection string. Connectionstrings can be found here: www.connectionstrings.com
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||As Jens suggested you should be able to simply replace
Conn.Open "DBQ=" & DATA_PATH & ";Driver={Microsoft Access Driver (*.mdb)}; DriverId=25;MaxBufferSize=8192;Threads=20;", "admin", "password"
with
Conn.Open "Provider=SQLNCLI.1;Persist Security Info=False;User ID=sa;Initial Catalog=ABC;Data Source=xx.xx.xx.xx;Password=<your pwd here>"
Please be careful about storing the password into the script itself, it is not secure.
HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
So, when I use the script below... I've had to work out the Logical
name for the data and the log file and alter the script accordingly.
Creation
----
Create Database MyDatabase ON (NAME = MyDatabase_data, FileName =
'D:\Database\MSSQL\Data\MyDatabase.mdf') LOG ON (NAME = MyDatabase_log,
FileName = 'D:\Database\MSSQL\Data\MyDatabase.ldf') COLLATE
SQL_Latin1_General_CP1_CI_AS
Restore
----
RESTORE FILELISTONLY
from disk =
'D:\Database\MSSQL\BACKUP\2006-08-07\MyDatabase_db_200608072100.BAK'
restore database MyDatabase
from disk =
'D:\Database\MSSQL\BACKUP\2006-08-07\MyDatabase_db_200608072100.BAK'
with REPLACE,
MOVE 'SomeOtherRubbish_Data' TO
'D:\Database\MSSQL\Data\MyDatabase.mdf',
MOVE 'SomeOtherRubbish_Log' TO
'D:\Database\MSSQL\Data\MyDatabase.ldf'
go
When I then look at the properties of the database, it shows the old
convention which I don't want.
So, even though I've been neat creating the database, it gets
overwritten with the odl rubbish name. How can I change the logical
name so that I can have a nice and neat naming convention ?
Oh, Yes I know I added the collation when creating the database, but
that's another thing that we need to address at some point.
Thanks in advanceRyan (ryanofford@.hotmail.com) writes:
Quote:
Originally Posted by
I'm trying to restore about 70 databases onto a new SQL server and
wanted to script the creation and restore. I've done the creation with
no problems, but on the restore, the logical names (of the original
data) are all over the place and were historically wrong.
Quote:
Originally Posted by
So, even though I've been neat creating the database, it gets
overwritten with the odl rubbish name. How can I change the logical
name so that I can have a nice and neat naming convention ?
--
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|||Thanks Erland. I'll give it a try. No major issue on not using the
create script, but thanks for the advice on that.
Ryan
Erland Sommarskog wrote:
Quote:
Originally Posted by
Ryan (ryanofford@.hotmail.com) writes:
Quote:
Originally Posted by
I'm trying to restore about 70 databases onto a new SQL server and
wanted to script the creation and restore. I've done the creation with
no problems, but on the restore, the logical names (of the original
data) are all over the place and were historically wrong.
Quote:
Originally Posted by
So, even though I've been neat creating the database, it gets
overwritten with the odl rubbish name. How can I change the logical
name so that I can have a nice and neat naming convention ?
