attach IDENTITY property to an existing column

Hi All,
Can any body tell me that how we can attach IDENTITY property to an existing int columnyou can not.

you have to...

1. rename the existing table
2. drop the foriegn keys referencing it.
3. drop the constraints on the renamed table.
4. define a new table with the identity property with the orginal table name
--the next 2 can go in any order based on what you decide is best
5. then reapply all constraints on the table, usually doing the primary key first.
6. insert data from the renamed table to the new table. you may want to do a INSERT IDENTITY MyTable ON to insert your existing keys. Just do not forget to turn it off when you are done.
7. drop the renamed table (optional)

you may want to wrap all or part of this in an explicit transaction with appropriate error handlers and print messages and you may not want to drop renamed table until you have committed the transaction without error and your row count after the insert matches the rowcount in the new table.

Attach Db With Same Files As Existing Db

In SQL Server 2000, I would like to attach a Database that is the file of the Database on another system. I made a copy of my Client's Database at his site. Now I would like to attach it to my server and then copy the data from my Client's DB to mine.

The only problem is they have the same file names, so when I try to attach the copy I get an error message. Is there any way to change the name of the data file and log file that a database points to so that I can attach it to my Server?

Thanks in advance for your help!Look up RESTORE DATABASE in Books Online. You need to use the WITH MOVE option.

RESTORE DATABASE database
WITH
MOVE 'logical data file' TO 'somenewfile.mdf',
MOVE 'logical log file' TO 'somenewfile.ldf'

You can find the name of the files by using: RESTORE FILELISTONLY FROM DISK = 'file.bak'

attach db to .net2 project

Hi,

How do I attach an existing sql2000 database to a .net 2 project in the app_data folder?

thanks

Just detach the database from the SQL 2000 instance, then open your project-> right click the App_Data folder in Solution Explorer->choose Add Existing Items->find the database file and add it.sql

Associating a Windows account with a user

Hi,
I need to associate an existing Windows account with an existing SQL Server
2000 user, but have not found the stored procedure for this.
Can somebody guide me on this please?
Thanks in advance,
Juan Dent, M.Sc.Is this the case of an orphanned user, due to moving the db from one domain
to another or something? There is a procedure called sp_change_users_login,
but that will not work with Windows accounts.
Can you simply grant access to that Windows login to SQL Server using
sp_grantlogin, and add associated user in the database using
sp_grantdbaccess? You'll have to apply the permissions manually though.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Juan Dent" <JuanDent@.discussions.microsoft.com> wrote in message
news:5AF26949-D98D-4F0A-9F6E-2DF92469E88C@.microsoft.com...
> Hi,
> I need to associate an existing Windows account with an existing SQL
Server 2000 user, but have not found the stored procedure for this.
> Can somebody guide me on this please?
> --
> Thanks in advance,
> Juan Dent, M.Sc.|||Actually what I have is a database with three users but only one of them is
associated with a Windows account.
Looking in SQLSErver Enterprise Manager, in the Users folder of the database
, it looks something like this:
dbo DENTDEVELOPMENT\JuanDent
isadmin
isuser
See? What I want is to associate the login above with isadmin as well as dbo
.
Is that possible?
Thanks in advance,
Juan Dent, M.Sc.
"Narayana Vyas Kondreddi" wrote:

> Is this the case of an orphanned user, due to moving the db from one domai
n
> to another or something? There is a procedure called sp_change_users_login
,
> but that will not work with Windows accounts.
> Can you simply grant access to that Windows login to SQL Server using
> sp_grantlogin, and add associated user in the database using
> sp_grantdbaccess? You'll have to apply the permissions manually though.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Juan Dent" <JuanDent@.discussions.microsoft.com> wrote in message
> news:5AF26949-D98D-4F0A-9F6E-2DF92469E88C@.microsoft.com...
> Server 2000 user, but have not found the stored procedure for this.
>
>|||No actually what I have, as seen in SQLSERVER Enterprise Manager in the User
's folder is:
dbo DENTDEVELOPMENT\JuanDent
isadmin
isuser
And I want to associate the above login with both isadmin and dbo users.
Is that possible?
Thanks in advance,
Juan Dent, M.Sc.
"Narayana Vyas Kondreddi" wrote:

> Is this the case of an orphanned user, due to moving the db from one domai
n
> to another or something? There is a procedure called sp_change_users_login
,
> but that will not work with Windows accounts.
> Can you simply grant access to that Windows login to SQL Server using
> sp_grantlogin, and add associated user in the database using
> sp_grantdbaccess? You'll have to apply the permissions manually though.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Juan Dent" <JuanDent@.discussions.microsoft.com> wrote in message
> news:5AF26949-D98D-4F0A-9F6E-2DF92469E88C@.microsoft.com...
> Server 2000 user, but have not found the stored procedure for this.
>
>

Associate User to Login

Is there a way to associate an existing user in a database to an existing
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
Gary
This is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (search KB for this).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that the
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user would
> keep the same configuration for that user.
> Thank you,
> Gary
>
|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>
|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>

Associate User to Login

Is there a way to associate an existing user in a database to an existing
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
GaryThis is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (search KB for this).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that the
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user would
> keep the same configuration for that user.
> Thank you,
> Gary
>|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>

Associate User to Login

Is there a way to associate an existing user in a database to an existing
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
GaryThis is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (s
earch KB for this).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl..
.
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them
.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that t
he
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user wou
ld
> keep the same configuration for that user.
> Thank you,
> Gary
>|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>

Assign Sequential Numbers

I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.An IDENTITY column is just what you need. Check the Microsoft SQL Server CREATE TABLE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp) documentation.

-PatP|||Hey...

SQLTeam still down?

USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(100,1), Col2 varchar(25))
GO

INSERT INTO myTable99(Col2)
SELECT 'Brett' UNION ALL
SELECT 'Pat' UNION ALL
SELECT 'Gary'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||I agree with Pat. Here is an exerp from the Create Table subject in Books Online for SQL:

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft SQL Server provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||Originally posted by Brett Kaiser
Hey...

SQLTeam still down? Nah, at least I can see it from here.

-PatP

ASP.NET or Sharepoint for SQL Server 2005 access?

I've got existing data stored on a SQL 2000 Server, and want to move to SQL Server 2005 Std.

I want to design a website with forms to look up, edit and add new data to the SQL server. I have specific pages in mind, i.e. a page for scanning in S/Ns for products, a page for allocating products that are in stock, etc.

Is Sharepoint capable of allowing access to SQL server and letting me design such custom pages? Or should I go with Visual C# and use ASP.NET to create the kind of site that I want?

i do not think that sharepoint alone will help you, you might have to use Infopath forms to use the sharepoint option. Or you could right some custome web parts to plug into the sharepoint system. My advice (My own point of view) is that you would be better creating your own custom solution using asp.net, this way you will have more control of the editing of the data. Also with the way that ASP.Net 2.0 is you can do some really quick development using the build in controls. Remember if you are going to ask some specific questions about asp.net you should direct you browser to http://forums.asp.net as this is where most of the asp teams hang out.

|||

I have configured SharedPoint 2007 server with sql server 2005. In this environment you can create portals, blogs and manage enterprise contents. For your requirements you can simply use SharedPoint 2007 server and sql server 2005 and then can whatever you wnat. Don miss it.

Note: you can write vast programming in this environment also with C# for managing your web pages/outlooks/etc...

Shahidul Haque.

ASP.NET 1.1 connecting to DBF

Hi all,
My problem is :
I have ASP.NET 1.1 app that must connect to existing DBF data files.
I tried to do this with existing ODBC for DBF but I have following problem :
Either if DBF file had appropriate index NTX file, the unknown exception
occured or if index didn't exist than the query seems to return result
without WHERE clause which unfortunately exists.
I fount 3-rd party drivers (Data Direct) but the price (4000$) per processor
is very expensive for me.
Did anyone have similar problem and how he solved it?
Does exist cheaper solution?
Toni
Hi Toni,
Which "ODBC for DBF" are you using? FoxPro files are DBFs but there are
other DBF files that aren't in the exact same format, and your mention of
NTX index files makes me think yours are not FoxPro files (they have CDX
indexes). There is a FoxPro and Visual FoxPro ODBC driver, but I don't think
it will read DBFs that have NTX indexes correctly.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Toni Cvetkovski" <tonic@.semos.com.mk> wrote in message
news:e7pJt3evFHA.3984@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> My problem is :
> I have ASP.NET 1.1 app that must connect to existing DBF data files.
> I tried to do this with existing ODBC for DBF but I have following problem
> : Either if DBF file had appropriate index NTX file, the unknown exception
> occured or if index didn't exist than the query seems to return result
> without WHERE clause which unfortunately exists.
> I fount 3-rd party drivers (Data Direct) but the price (4000$) per
> processor is very expensive for me.
> Did anyone have similar problem and how he solved it?
> Does exist cheaper solution?
> Toni
>
>
>

ASP.NET 1.1 connecting to DBF

Hi all,
My problem is :
I have ASP.NET 1.1 app that must connect to existing DBF data files.
I tried to do this with existing ODBC for DBF but I have following problem :
Either if DBF file had appropriate index NTX file, the unknown exception
occured or if index didn't exist than the query seems to return result
without WHERE clause which unfortunately exists.
I fount 3-rd party drivers (Data Direct) but the price (4000$) per processor
is very expensive for me.
Did anyone have similar problem and how he solved it?
Does exist cheaper solution?
ToniHi Toni,
Which "ODBC for DBF" are you using? FoxPro files are DBFs but there are
other DBF files that aren't in the exact same format, and your mention of
NTX index files makes me think yours are not FoxPro files (they have CDX
indexes). There is a FoxPro and Visual FoxPro ODBC driver, but I don't think
it will read DBFs that have NTX indexes correctly.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Toni Cvetkovski" <tonic@.semos.com.mk> wrote in message
news:e7pJt3evFHA.3984@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> My problem is :
> I have ASP.NET 1.1 app that must connect to existing DBF data files.
> I tried to do this with existing ODBC for DBF but I have following problem
> : Either if DBF file had appropriate index NTX file, the unknown exception
> occured or if index didn't exist than the query seems to return result
> without WHERE clause which unfortunately exists.
> I fount 3-rd party drivers (Data Direct) but the price (4000$) per
> processor is very expensive for me.
> Did anyone have similar problem and how he solved it?
> Does exist cheaper solution?
> Toni
>
>
>