Re: how to ON/OFF identity field through sql script? - Database Discussion Boards

Hi,

I have a database called MyDatabase and I am looking to create another datebase called MyDatabase_Dev with the exact same tables and data in it, I want no primary keys that are lost and no auto numbering that it not set.

I can't remember how to do this. I tried Import data on the MyDatabase_Dev, but I loose primary keys and auto numbering. I am using SQL Server 2005.

Thanks
Brendan

Backup your database, then restore it under a different name.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

I tried that but it gave me an error. But then I tried it again and saw that I did not change the destination file name to my new name, and then it worked.

Thanks

Just another quick question about the database restore. After the new database is restored, I right clicked on the database and clicked on properties. I then clicked on the files tab, and to the right there is a column with a name called "Logical Name". And the logical name is still MyDatabase, the original database that I did the backup from. Wasn't this supposed to have changed when I changed the file names when I did the restore? I don't want it to have an relationship the original database, I want it to be on it's own. I hope you understand.

You can just change this on the file tab if you want to. The physical names of the database files should be different from the original database and follow the name of your new database. The database you have restored from the backup doesn't have any relationship with your original database, so you don't need to worry about this.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

What about copy database wizard??
In your SQL management studio, right click on the database, select Tasks -> Copy database.

Regards,
Mehroz

i m using sqlserver 2000
i have a table that has the primary key set as identity
i want to make that off insert one row and value that column that is set as identity and then again make that on and i want to do that through sql script.

i try this code

CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))
INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)

And have got this error always:
Incorrect syntax near the keyword 'IDENTITY'.

Why I got this?

Not quite clear on what it is you are trying to do, but it isn't allowed. I've included the ALTER COLUMN description from BOL below.

If you need to perform this kind of change you'll need to create a table which matches your customers table (MyCustomers_copy) AND has the new definition for the identity column. Then you'll need to copy all the data from the customers table to the copy table. Then drop the original customers table and rename (sp_rename stored procedure) the copy table to the name of the original customers table.

If all you're trying to do is insert an explicit identity value instead of letting SQL Server do it for you, then you just need to use IDENTITY_INSERT

<br />
SET IDENTITY_INSERT MyCustomers ON<br />
<br />
INSERT INTO MyCustomers(CustID, CompanyName) VALUES(202, 'A. Datum Corporation')<br />
<br />
SET IDENTITY_INSERT MyCustomers OFF<br />
<br />

That will allow you to insert any value you like for CustID. However, if the value already exists you'll get a primary key violation error. Usually this is not used from application code, only for coping data from one database to another.

ALTER TABLE Documentation from BOL:

ALTER COLUMN
Specifies that the named column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or lower. For more information, see sp_dbcmptlevel (Transact-SQL).

The altered column cannot be any one of the following:

A column with a timestamp data type.

The ROWGUIDCOL for the table.

A computed column or used in a computed column.

Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.

Used in statistics generated by the CREATE STATISTICS statement. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.

Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.

Used in a CHECK or UNIQUE constraint. However, changing the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.

Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed.

The data type of text, ntext and image columns can be changed only in the following ways:

text to varchar(max), nvarchar(max), or xml

ntext to varchar(max), nvarchar(max), or xml

image to varbinary(max)

Some data type changes may cause a change in the data. For example, changing an nchar or nvarchar column to char or varchar may cause the conversion of extended characters. For more information, see CAST and CONVERT (Transact-SQL). Reducing the precision or scale of a column may cause data truncation.

The data type of a column of a partitioned table cannot be changed.

Mark's blog: developMENTALmadness.blogspot.com

I've searched everywhere and can't seem to find a solution for this one.

I have a C# program that uses an Access DB. The program works fine on all of the Win XP and Vista machines that I've tried, but on a Windows 2000 Professional machine, I get an error when it tries to open the DB connection created with an OleDBConnection object. I get the same error on WinXP if I change the program properties to run in Windows 2000 compatibility mode.

The error is reported as "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Here's the code snippet that is causing the problem:

<br />
            try<br />
            {<br />
                db = new OleDbConnection(connStr);<br />
                db.Open();<br />
            }<br />
            catch (OleDbException e)<br />
            {<br />
                MessageBox.Show("DB Error: " + e.Errors[0].Message);<br />
            }<br />

The error is generated trying to execute the db.Open() statement.

The database file looks fine using Access 97, 2003 and 2007 on all machines. Apparently it has something to do with the way the connection is created, but I don't understand why it works on a normal XP and Vista machine, but not when run under the Win 2000 compatibility mode. What is different between, say normal XP and running under Win 2000 compatibility on XP?

Any ideas will be appreciated.

The PetroNerd

modified on Saturday, March 15, 2008 11:02 PM

Just in case someone might be interested, I finally found the solution. The Win2000 machine was running MDAC 2.5. Upgrading it to MDAC 2.8 solved the problem, but apparently there is a problem with running on XP with Win 2000 compatibility for test purposes.

It appears that on XP when running under Win 2000 compatibility, MDAC 2.5 (or equivalent) is being used, even though MDAC 2.8 is installed. What that means is that you CAN'T test for Win 2000 compatibility on an XP machine.

Hope someone finds that information useful.

The PetroNerd

Hi,

I need to execute a Stored proc on the online database. The problem is that there are about 0.8 million records and this will take lots of time like 6-8 hrs atleast. If I execute it by cnnecting from here it has a chance of geting cancel b4 completion. I'm using SQL 2005 Express. Is it possible to just execute the SP from here and then it carries on untill its complete. regardless of whther I'm connected or not.

Thanks in advance

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Stored procedure should executed from the server. Is there anyway to reduce the number of records it must chew through?

"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon

you didnt understand my question. I know SPs are supposed to be executed on Server, offcourse that's what they're there for. My question is that when you execute an SP on an online server, it gets stopped when you get disconnected from the internet. How can we avoid this problem?

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Try creating a job. so that the same will be executed at server and need not have an active connection from client.

Regards
KP

I think SQL 2005 EXPRESS doesnt allow you to create a job. Plz tell me If I'm wrong... Many thanks

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Is the remote database also SQL 2005 Express? Also, you mentioned that there was a lot of data involved, is that data already located on the remote SQL installation or is it being copied from your local machine to the remote machine?

Mark's blog: developMENTALmadness.blogspot.com

yes the remote server is also SQL Express 2005, and the data is on it as well. Actually i mistakenly curropted the data in a field so I wrote the SP for that. I've found a useful tool on CP and I've used it to run the SP on the remote Server, Now I hope this gets done as its supposed to be

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Happy to hear you've found your solution.

Mark's blog: developMENTALmadness.blogspot.com

well if it doesnt go too well, can u give me some other alternative?

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

I'm assuming your reference to a tool you found on CP implies you can create executables on the server and run them. Knowing exactly the amount of control you have over the the remote machine would make it easier to point to a single possible solution.

Can you setup a windows task?

If so I would use the sqlcmd tool: http://msdn2.microsoft.com/en-us/library/ms165702.aspx[^]. You could then create a .bat or .cmd file and tell it to execute the stored procedure with a single line of code and reference the file from windows task scheduler and tell it when to execute. Then it will execute without requiring you to be logged into the remote server.

The command would look something like this:

<br />
sqlcmd -S (local) -d databaseName -E -Q "exec storedProcname 'value1', 2, 'value3'"<br />

And the task would run as you or a windows account with permissions to the server/database. Or if you're using SQL Authentication:

<br />
sqlcmd -S (local) -d databaseName -u username -p myp@ssw0rd -Q "exec storedProcname 'value1', 2, 'value3'"<br />

NOTE: the parameter flags are case sensitive (-Q is not the same as -q).

This would be the best solution because any other solution would probably require you to be logged into the machine somehow (terminal services, etc.). This would still work as long as you can disconnect your session w/o it logging you off the machine which would allow any processes you start to continue to run. But setting up a task would be better, especially if for tasks which need to be run more than one time.

Mark's blog: developMENTALmadness.blogspot.com

Mark J. Miller wrote:
Can you setup a windows task?

oh no I'm afraid I can't. and the worse thing is that I can't even chk the status of the job I executed it coz I dont have any permission on the system table that is used to get the status of the job.

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

What kind of access rights do you have on the remote machine? Can you use some sort of remote desktop tool?

In order to do what you describe you will need to somehow start a process on the remote machine from which you can disconnect w/o killing the process which is running on the remote machine.

Otherwise, if you don't have any access other than file copy and dbo access to the database then you'll have to ask the administrator of the machine to run the sqlcmd script for you I mentioned in my last post.

Mark's blog: developMENTALmadness.blogspot.com

Mark J. Miller wrote:
you'll have to ask the administrator of the machine to run the sqlcmd script for you

I think thats what I need to do. ur right!

its a shared server so they wont take the risk of allowing us to run windows services

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

while updating or inserting a record in SQL SERVER, by default the DATETIME variable getting 1/1/1900. i need a trigger, so that whenever UPDATE or INSERT is made in a particular table, and if datetime consist of 1/1/1900, those values should replace as NULL.... can anyone provide the trigger for this? or is there anyother solution?

Currently i m using ASP.NET with C# (.net 2003, 1.1 framework) - KARAN

I'm sorry I can write the whole Trigger for u but I'm gonna give u a few clues to start with . First you need to make an INSTEAD OF trigger for insert and updates

in that you will use the updated and inserted values. if the date in the inserted table matches 1/1/1900 dont issue an insert/update statement. Otherwise execute the insert statement.

I hope this will help you

Rocky

Success is a ladder which you can't climb with your hands in your pockets.