Changing column from nvarchar to int

I am upgrading an Access database and need to convert some of the fields
from text to a foreign key from another table. This is no problem and I
already have the query batch to do this. I would like to know if it is
possible to change a column type from nvarchar to int.
Thanks,
Drew LaingAs long as all the NVARCHAR values can be converted to an INT, sure.
ALTER TABLE tablename ALTER COLUMN columnname INT
http://www.aspfaq.com/
(Reverse address to reply.)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>|||Drew,
You can do it if the values are numeric or null. If the value can not be
casted, then you will get an error.
Example:
use northwind
go
create table t (
colA varchar(15)
)
go
insert into t values('1')
insert into t values('2')
insert into t values(null)
go
select * from t
go
alter table t
alter column colA int
go
select * from t
go
alter table t
alter column colA varchar(15)
go
insert into t values ('a')
go
-- will fail
alter table t
alter column colA int
go
select * from t
go
drop table t
go
AMB
"Drew" wrote:

> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>
>|||Thanks to both!
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
>I am upgrading an Access database and need to convert some of the fields
>from text to a foreign key from another table. This is no problem and I
>already have the query batch to do this. I would like to know if it is
>possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>

Changing access db to sql db web application.

I am experiencing problems with databases for the web application that i am developing. The problem is that I have transferred the database from access to sql which initially seemed to work ok. I am now however struggling to solve the problems with one of my fields titled 'notes'. The trouble is that in the original database the notes field contained different notes from different dates for each record, and each note is separated by carriage returns. It is the carriage returns that are now proving to be a problem in the sql database but i am not sure what to do. Any advice would be appreciated.
Thanks
Dan
You can use Word through OLE to store the notes in Access then import the Word file into SQL Server Image column. Hope this helps.|||sorry am not too sure how i would go about using word, what is ole?|||OLE is Object linking and embedding it allows one office application to import files to another by dragging and dropping. Try the link below to get started. Hope this helps.
http://office.microsoft.com/en-us/assistance/HA011472041033.aspx#WhyOLE

Changing a column to an Identity column. Please Help...

I have a situation where we converted over a large database. The database
had one table that used autonumber(Access) that we need to preserve the IDs
upon conversion for a table that refereneces the field. So during
conversion I turned off the identity and it brought over the ID column with
the correct values...but now I find that I can't turn the Identity back on
for that column!!!!!
I am in big trouble here...is there some way to take an existing int, no
null column into an identity column'
Please help.
Thanks,
Ron"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:%23cWhTLZBGHA.3580@.TK2MSFTNGP11.phx.gbl...
> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the
> IDs upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column
> with the correct values...but now I find that I can't turn the Identity
> back on for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
First of all, you didn't need to turn it off, you could have used SET
IDENTITY_INSERT.
How did you turn it off? With Enterprise Manager?
You should be able to modify the column to Identity with EM.|||Hi
Did you use "SET IDENTITY_INSERT ON" then if you are still using the same
session then you can use SET IDENTITY_INSERT OFF, other/new sessions will no
t
be affected.
If you removed the IDENTITY property, then you will have to create a new
column with the identity property and move the data into it using the above
commands to allow insertion.
John
"RSH" wrote:

> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the ID
s
> upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column wit
h
> the correct values...but now I find that I can't turn the Identity back on
> for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
>
>|||I tried this but I still get the ERROR:
Msg 8102, Level 16, State 1, Line 2
Cannot update identity column 'ID'.
SET IDENTITY_INSERT [TESTACH].[dbo].[DirectDeposit] ON
GO
UPDATE [TESTACH].[dbo].[DirectDeposit] SET ID = 73986 WHERE DirectDepositID
= 73986
GO
What am I doing wrong'
THANKS!
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uHwK7ZZBGHA.2840@.TK2MSFTNGP12.phx.gbl...
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:%23cWhTLZBGHA.3580@.TK2MSFTNGP11.phx.gbl...
> First of all, you didn't need to turn it off, you could have used SET
> IDENTITY_INSERT.
> How did you turn it off? With Enterprise Manager?
> You should be able to modify the column to Identity with EM.
>sql

Changing a column to an Identity column. Please Help...

RSH wrote:
> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the ID
s
> upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column wit
h
> the correct values...but now I find that I can't turn the Identity back on
> for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
You can't change this property for an existing column and the IDENTITY
value can only be set on INSERT. What this means is that you'll have to
create a new table and INSERT the existing data into it using the SET
IDENTITY_INSERT option. You can then drop the old table and rename the
new one.
If you modify the table in Enterprise Manager (SQL Server 2000) or
Management Studio (2005) then it will make the change for you or will
generate a script to do it.
David Portas
SQL Server MVP
--Okay next question...is there a simple query that will allow me to do
something like...
INSERT * FROM ACHMaster INTO ACH
?
I need someway to perform the insert from one table to the other.
Thanks for all of your help!!!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1135105250.447899.52020@.g43g2000cwa.googlegroups.com...
> RSH wrote:
> You can't change this property for an existing column and the IDENTITY
> value can only be set on INSERT. What this means is that you'll have to
> create a new table and INSERT the existing data into it using the SET
> IDENTITY_INSERT option. You can then drop the old table and rename the
> new one.
> If you modify the table in Enterprise Manager (SQL Server 2000) or
> Management Studio (2005) then it will make the change for you or will
> generate a script to do it.
> --
> David Portas
> SQL Server MVP
> --
>|||INSERT ACH SELECT * FROM ACHMaster
Note: SELECT * shouldn't be used for production queries. For a one-time
fix, however, it should work, provided the tables were created with the
columns in the same order. It is always best to spell out the column list,
both for the INSERT and the SELECT.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:etKU9oZBGHA.2476@.TK2MSFTNGP10.phx.gbl...
> Okay next question...is there a simple query that will allow me to do
> something like...
> INSERT * FROM ACHMaster INTO ACH
> ?
> I need someway to perform the insert from one table to the other.
> Thanks for all of your help!!!!
>
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1135105250.447899.52020@.g43g2000cwa.googlegroups.com...
>|||THANKS ALOT!!!!!!
--Ron
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uybyzPbBGHA.3292@.TK2MSFTNGP09.phx.gbl...
> INSERT ACH SELECT * FROM ACHMaster
> Note: SELECT * shouldn't be used for production queries. For a one-time
> fix, however, it should work, provided the tables were created with the
> columns in the same order. It is always best to spell out the column
> list, both for the INSERT and the SELECT.
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:etKU9oZBGHA.2476@.TK2MSFTNGP10.phx.gbl...
>

Changing "Connection String" in SSIS package ?

Hi!

I create a SSIS Package for ETL on my own machine. During development database was also on my machine. For access to this database an OLE DB connection was defined within a package in BI Development Studio. Everything worked well both in debug mode and testing package itself.

Finally I need to load data to a database on a different machine using this package.

I used several scenaries:

1) simply copied the package-file to estination machine, open it for execution, in section "Connection Managers" I edited connection string manually - changed server name and Initial Catalog. And try to execute.

2) on the destination machine I manually created an OLE DB connection (using Microsoft Data Link) to a different database (test succeded), Changed the extention of the connection file 'udl' for ' txt ' and copied its connection string to the field connection string in section "Connection Managers" (pointed in variant 1) ).

3) use Package Configurations, copied the deployment to destination machine, installed the package the way like written here - http://msdn2.microsoft.com/en-us/library/ms365338.aspx. Changed exported properties - Server name, Initial Catalog and also the whole Connection String. Also try to execute.

In all cases I recieved the same error execution message :

"Errors in the metadata manager. Either the database with ID of " OLD_DATABASE_NAME " does not exist in the server with ID of " NEW_SERVER_NAME " or the user does not have permissions to access the object."

As for access (username/pass) settings they are the same for both of them, I have the same administrative rights on both machines. And more with the same rights the ole db connection made was made manually in variant 2 - succeded!!! So I don't think the problem is here.

As for Error message - I think somewhere the OLD name of database (Initial Catalog) is saved, though I tried to change it. Though the NEW value for the server name is substituted.

Please, help me. I don't know what else can I try. And it is not a single case for my practice. So I think - something wrong in my actions.

Don't store the ConnectionString property AND all of the other properties as configurations. Store EITHER the ConnectionString property OR all of the other properties (my advice would be to store only the ConnectionString property)

-Jamie

|||

Jamie Thomson - why to store only the ConnectionString?

Then config-file will overwrite only Connection String value itself, written in the package. But other properties would not be changed, but they will also been included into the package (the content of the package is independent with what properties I decided to include into configurations. Am I right? ).

Will you explain me? Thanks.

|||

The reply, posted before Jamie Thomson's one, was very helpfull to me. I marked it as it was helpful, but suddenly your post disapeared ? Sorry, I am new to this forum, but anywhere you help me.

I tried to watch the package file in text editor, and find the property to which the old database name value was saved, then include this property to configurations to make it changable at run time.

Thanks, it works!!!!

|||

Was this the post that disappeared?

sachin.rao wrote:

Hi Iris

The only place it could be persisting this information is in the package itself. Is the catalog name being stored in the dtsx file? Open it up in your favourite text editor and check it out.

Sachin

|||

Rafael Salas - yes, it is! Thanks.

Please, tell me what I did wrong and how can I mark a post if it was helpful and/or was a reply/solution to my question.

|||For some reason that post was deleted...only the author or a moderator can do that...I don't know who did it. You should have a buton 'Mark as anserwer'...|||

IrisFresco wrote:

Jamie Thomson - why to store only the ConnectionString?

Then config-file will overwrite only Connection String value itself, written in the package. But other properties would not be changed, but they will also been included into the package (the content of the package is independent with what properties I decided to include into configurations. Am I right? ).

Will you explain me? Thanks.

Because the ConnectionString is made up of allthose individual properties (e.g. CatalogName, Username, password, ServerName etc...). Changing ConnectionString automatically changes allthe other properties. With ConnectionString you only need to maintain one thing however and I see that as an adavantage.

-Jamie

Changes to cloned database are affecting the original

I am trying to use SQL Express to support unit testing of functionality which requires database access.

I created an express database (.mdf) which contains a database snapshot which is the database environment that each test expects to see when it starts. Through the execution of the test the database will be modified arbitrarily and I need to reset the database to the original state before the next test executes.

Here's what I do today:

Before Test:

The original database is copied to a unique location in the temp directory

A unique database name is generated (GUID-based)

I connect to the new database file with:

Server=.\SQLEXPRESS;AttachDbFilename="Path\To\Temp\File";Database="GuidDBName";Initial Catalog="GuidDBName";Trusted_Connection=Yes;User Instance=true

During the test all database access is routed to the temporary database GuidDBName

After Test:

Close all active database connections and clear the connection pool

Connect to the master database and detach the database:

Server=.\SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=true - sp_detach_db 'GuidDBName'

Delete the temporary file

When I do this, everything seems to work correctly but when I re-open the original database it has changed unexpectedly and includes the modifications that were made by the test as it ran.

Can anyone explain why this is happening and/or provide a solution?

Short version - I have no idea what's happening here, I can't reproduce this behavior based on the information you've provided.

Long version - Your connection strings have duplicate information in them and I'm guessing that part of the problem is that you're connecting to different databases that you think you are. I also have question about you're original database; you don't mention how your are opening the original database to discover that it has been modified. I'm guessing you're using Managment Studio, but I'll bet you're looking at the parent instance SQLEXPRESS and not the User Instance you're creating with the connection string you've provided. A User Instance is a completely separate instance of SQL Server.

The duplication you have is that Database= and Initial Catalog= are fundamentally the same thing. (Check out this BOL topic for the gorey details on connection string keywords in SNAC.) On top of that, you don't really need to use either of them when you use AttachDbFilename in combination with User Instance=True since SQL Express will automatically named the database with the file path.

I wrote a little test routine to try and cause this, but I couldn't get it to fail. Here is what I did if you want to compare it to your code solution:

static void Main(string[] args)

{

// Create a new copy of the database

string path1 = "C:\\Users\\mikewa\\Documents\\RANUTest\\Source\\foo.mdf";

string path2 = "C:\\Users\\mikewa\\Documents\\RANUTest\\foo.mdf";

string path2log = "C:\\Users\\mikewa\\Documents\\RANUTest\\foo_log.ldf";

try

{

File.Delete(path2);

File.Delete(path2log);

File.Copy(path1, path2);

}

catch (Exception e)

{

Console.WriteLine(e.ToString());

}

// Attach the temp database to the User Instance

string connectionString = "Server=.\\SQLEXPRESS;AttachDbFilename=" + path2 + ";Integrated Security=True;User Instance=True";

using (SqlConnection cnn = new SqlConnection(connectionString))

{

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "CREATE TABLE dbo.tblFoo (myField int NULL)";

cmd.CommandType = System.Data.CommandType.Text;

cmd.Connection = cnn;

cmd.ExecuteNonQuery();

cnn.Close();

}

SqlConnection.ClearAllPools();

// Now kill the temp database in SQL

string masterConnectionString = "Server=.\\SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=True";

using (SqlConnection cnn = new SqlConnection(masterConnectionString))

{

SqlCommand cmd = new SqlCommand("sp_detach_db '" + path2 + "'");

cmd.CommandType = System.Data.CommandType.Text;

cmd.Connection = cnn;

cnn.Open();

cmd.ExecuteNonQuery();

}

}

As you can see, I've stripped the extra keywords out of my connection string and just used the complete file path, represented by the variable 'path2' as the file name. This automatic naming happens automatically (hence the name) as part of attaching the file to a User Instance. I use the same variable later to detach the file. After I run this, I'm able to examine my source file and see that the modification does not exist (i.e. no table named tblFoo).

Check around to ensure that you don't have multiple copies of your original database laying around. If you're looking at the "original file" in Management Studio while connected to the parent instance, it's possible that you're actually looking at a different file than the one you're copying.

Mike

Changeing Remote ports

I have two instances of SQl 2005 running on a server. One I'm going to allow outside remote access to. But I don't want to do it on the default port. though I have it allowing remote and that seems to be working. I can seem to find where to change the default listening port.

and I scimmed the help and topics I saw. But didn't see one relevant to this question.

Open the Server Configuration Manager > Protocols for [InstanceName] > TCP/IP Properties > TCP Port > Restart the service

Jens K. Suessmeyer.

http://www.sqlserver2005.de

change user1 access changes user2 access

Hello,
I have set up some accounts on Sql Server 2k. I am just
starting out with Security stuff. A problem that I am
having is that when I assign access to one account, user1,
the next account, user2, appears to pick up the same
access to things. Ideally, I want user1 to be able to
connect to tables and user2 to be able to execute stored
procedures. But boths users seem to acquire the same
access when I set one. Then when I reset the other, the
first user now has the same access as the second user. Is
there a way to separate this?
Thanks,
RonApril 7, 2005
When you are giving permissions to User1, are you giving permissions to a
group that User1 belongs to? If you are assigning permissions to User1
through a group membership, then it sounds to me like User2 is in the same
group. If this is the case then make sure User2 is not in any groups that
you are using to assign permission to User1. Hope this helps! :-)
Joseph MCAD
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:020001c53b8a$54890a40$a501280a@.phx.gbl...
> Hello,
> I have set up some accounts on Sql Server 2k. I am just
> starting out with Security stuff. A problem that I am
> having is that when I assign access to one account, user1,
> the next account, user2, appears to pick up the same
> access to things. Ideally, I want user1 to be able to
> connect to tables and user2 to be able to execute stored
> procedures. But boths users seem to acquire the same
> access when I set one. Then when I reset the other, the
> first user now has the same access as the second user. Is
> there a way to separate this?
> Thanks,
> Ron|||The way I am creating new logins is by going to the
Security Icon in EM. I click on New Login. I assign a
name, a password, I select a database to login to, then go
the the Database Access and select the same database again
then in the Permit In Database Role window, I select
public, I click on properties, then I click on Permissions
in the Database Role Type window. Then I click on what
the user should have access to. So all the users have the
same database role on the same database. Do I need to
change the role in order to set the respective permissions
for the respective users? What is the recommended role
for a user who should only look at certain tables? For a
user that should only be able to execute certain SP's?
Thanks
Ron

>--Original Message--
>April 7, 2005
> When you are giving permissions to User1, are you
giving permissions to a
>group that User1 belongs to? If you are assigning
permissions to User1
>through a group membership, then it sounds to me like
User2 is in the same
>group. If this is the case then make sure User2 is not in
any groups that
>you are using to assign permission to User1. Hope this
helps! :-)
>
Joseph MCAD
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:020001c53b8a$54890a40$a501280a@.phx.gbl...
user1,[vbcol=seagreen]
Is[vbcol=seagreen]
>
>.
>|||April 7, 2005
Using the public role is your problem. All users, whether it be User1,
User2, UserX, are members of this role. By granting the public role
permission you are granting All users the permissions. You should create a
new role specifically for this certain login and then grant that role
permissions. Then just add users to the new role. This is why it seems that
the users are tied together. It is recommended by security experts to never
grant permission to the public role, but to instead create custom roles! I
am glad I could be of help! :-)
Joseph MCAD
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:122201c53ba3$b17bdd40$a601280a@.phx.gbl...[vbcol=seagreen]
> The way I am creating new logins is by going to the
> Security Icon in EM. I click on New Login. I assign a
> name, a password, I select a database to login to, then go
> the the Database Access and select the same database again
> then in the Permit In Database Role window, I select
> public, I click on properties, then I click on Permissions
> in the Database Role Type window. Then I click on what
> the user should have access to. So all the users have the
> same database role on the same database. Do I need to
> change the role in order to set the respective permissions
> for the respective users? What is the recommended role
> for a user who should only look at certain tables? For a
> user that should only be able to execute certain SP's?
> Thanks
> Ron
>
> giving permissions to a
> permissions to User1
> User2 is in the same
> any groups that
> helps! :-)
> Joseph MCAD
> message
> user1,
> Is|||Yes, I thought so. Thanks for your reply. Anyway, I did
click on the Role option, but I did not see anywhere for
creating a new or custom role. May I ask how this is
done? This is definitely what I need to do.
Thanks again,
Ron

>--Original Message--
>April 7, 2005
> Using the public role is your problem. All users,
whether it be User1,
>User2, UserX, are members of this role. By granting the
public role
>permission you are granting All users the permissions.
You should create a
>new role specifically for this certain login and then
grant that role
>permissions. Then just add users to the new role. This is
why it seems that
>the users are tied together. It is recommended by
security experts to never
>grant permission to the public role, but to instead
create custom roles! I
>am glad I could be of help! :-)
>
Joseph MCAD
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:122201c53ba3$b17bdd40$a601280a@.phx.gbl...
go[vbcol=seagreen]
again[vbcol=seagreen]
Permissions[vbcol=seagreen]
the[vbcol=seagreen]
permissions[vbcol=seagreen]
a[vbcol=seagreen]
in[vbcol=seagreen]
just[vbcol=seagreen]
stored[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||OK. I found role in Books on line. I think I am starting
to get the idea. So just to make sure, if I want a
different user to have different permissions, that user
would be in a different role? I assume yes. And, if I
only want a user to be able to access only specific
tables, then I can uncheck everything in the permissions
except for the specific table? I also assume that would
be yes. well, here goes.

>--Original Message--
>April 7, 2005
> Using the public role is your problem. All users,
whether it be User1,
>User2, UserX, are members of this role. By granting the
public role
>permission you are granting All users the permissions.
You should create a
>new role specifically for this certain login and then
grant that role
>permissions. Then just add users to the new role. This is
why it seems that
>the users are tied together. It is recommended by
security experts to never
>grant permission to the public role, but to instead
create custom roles! I
>am glad I could be of help! :-)
>
Joseph MCAD
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:122201c53ba3$b17bdd40$a601280a@.phx.gbl...
go[vbcol=seagreen]
again[vbcol=seagreen]
Permissions[vbcol=seagreen]
the[vbcol=seagreen]
permissions[vbcol=seagreen]
a[vbcol=seagreen]
in[vbcol=seagreen]
just[vbcol=seagreen]
stored[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||April 7, 2005
You have the idea. You don't really have to create a role, but it makes
management easier. If you only need One user to have specific permissions,
you can always just assign that user the permissions directly. I always
think that roles are much better though. Tell me how you turn out! :-)
Joseph MCAD
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:039e01c53bae$bbed3340$a501280a@.phx.gbl...[vbcol=seagreen]
> OK. I found role in Books on line. I think I am starting
> to get the idea. So just to make sure, if I want a
> different user to have different permissions, that user
> would be in a different role? I assume yes. And, if I
> only want a user to be able to access only specific
> tables, then I can uncheck everything in the permissions
> except for the specific table? I also assume that would
> be yes. well, here goes.
>
> whether it be User1,
> public role
> You should create a
> grant that role
> why it seems that
> security experts to never
> create custom roles! I
> Joseph MCAD
> message
> go
> again
> Permissions
> the
> permissions
> a
> in
> just
> stored
> the

Change script from MSAccess to SQL access.

I'm using this script I found on the web. After some tweaking here and there, it works great. I'd like to change it to pull data from a SQL source instead of Access. Anyone know how to do this, give me some pointers?

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.

Change password in ODBC..?

Hi all,
I have an Access appl. with linked SQL-server tables.
The user have a SQL-server login from a ODBC datasource.
How can I get the user to change password themselves after a temporary
password?
Kent J.
Hi Kent,
I am not sure if I understand the problem but, if the password of the login
was changed on SQL Server, then you will need to find the ODBC Data Source
Name and update its password too. Go to Control Panel, Adminstrative Tools
and Data Sources (ODBC).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Kent J" wrote:

> Hi all,
> I have an Access appl. with linked SQL-server tables.
> The user have a SQL-server login from a ODBC datasource.
> How can I get the user to change password themselves after a temporary
> password?
> Kent J.
>

Change password in ODBC..?

Hi all,
I have an Access appl. with linked SQL-server tables.
The user have a SQL-server login from a ODBC datasource.
How can I get the user to change password themselves after a temporary
password?
Kent J.Hi Kent,
I am not sure if I understand the problem but, if the password of the login
was changed on SQL Server, then you will need to find the ODBC Data Source
Name and update its password too. Go to Control Panel, Adminstrative Tools
and Data Sources (ODBC).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Kent J" wrote:

> Hi all,
> I have an Access appl. with linked SQL-server tables.
> The user have a SQL-server login from a ODBC datasource.
> How can I get the user to change password themselves after a temporary
> password?
> Kent J.
>