Core data type question (bigint)

We are trying to work with our developers to upgrade to SQL 2000 from SQL 7 for a critical applicaion and all looks good in testing for the most part. The concern that our developers have is that in order for the application to work on the test SQL 2000 server they had to delete a core data type (bigint) for the application to work. It doesn't appear to have any negative affects and we know for sure that the application database does not need that data type at all.

Can someone verify that there are no requirements for SQL 2000 needing to have this data type? They are worried that something within SQL may rely on it and we would find out the hard way in production possibly.

Edit

Bigint is used when the value you are storing is more than normal int value and it will not create problem. I would create the new database with SQL7.0 compatibilty on the code page for the unknown issues. The following is from the BOL(books online)

"Unless explicitly stated, functions, statements, and system stored procedures that accept int expressions for their parameters have not been changed to support implicit conversion of bigint expressions to those parameters. Thus, SQL Server only implicitly converts bigint to int when the bigint value is within the range supported by the int data type. A conversion error occurs at run time if the bigint expression contains a value outside the range supported by the int data type."

Hope this helps.

|||

Thanks for the quick reply!

Just so I'm sure... We can safely remove the BigInt data type without causing any reprecusions to SQL 2000? Our user databases don't utilize BigInt at all but my concern is in how this may effect the system databases.

We've run this in our test environment for a few weeks but not under heavy load and haven't seen any negative effects but I don't know about long term effect on the core functionality of SQL 2000.

|||

gbollman wrote:

Our user databases don't utilize BigInt at all but my concern is in how this may effect the system databases.

Good to be concerned if your developers used SQL DMO(SQL Server Data Management Object) for your tables. That is the reason I said choose SQL7.0 compatibility in the code page selection in my original post. BTW service pack 4 was released last Friday. In SQL 7.0 upgrade I move the database three or four times in SQL Server 2000 through backup and restore use the restore from device option in the backup wizard. Then do stress testing with your employees. Hope this helps.

Copy SQL Server 6.5 Database

I am trying to copying a version 6.5 database on one serv to another version
6. database on another server. And finally upgrade the 6.5 database to 2000.
As I have never worked with 6.5, how do I perform the copy? We have to
perform this step as the current server is dying.
Otto
Hi
You need to do a backup (DUMP) on the old server, copy the file over and
then a restore (LOAD) on the new 6.5 server.
You can do the dump and restore through Enterprise Manager. SQL 6.5 does not
support attach/detach.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Otto@.ITP" <OttoITP@.discussions.microsoft.com> wrote in message
news:1AA7D978-FACD-4F5F-BE42-A59CBD6D682B@.microsoft.com...
> I am trying to copying a version 6.5 database on one serv to another
version
> 6. database on another server. And finally upgrade the 6.5 database to
2000.
> As I have never worked with 6.5, how do I perform the copy? We have to
> perform this step as the current server is dying.
> Otto
|||Otto,
You will need to run sp_help_revdatabase first on the existing server. This
will give you the order of allocations that were used to create the
database.
Use that information to create the exact same database structure on another
6.5 server. Then do a DATABASE DUMP from the original server and a DATABASE
RESTORE to the target server.
When doing this, remember that the sysusers table points to entries in
syslogins. The chances are very small that your users will map properly.
(I would recommend recording the rights granted to each user using
sp_helprotect, deleting the users, then recreating them with the appropriate
rights on the SS2000 server once you are done.)
RLF
"Otto@.ITP" <OttoITP@.discussions.microsoft.com> wrote in message
news:1AA7D978-FACD-4F5F-BE42-A59CBD6D682B@.microsoft.com...
> I am trying to copying a version 6.5 database on one serv to another
version
> 6. database on another server. And finally upgrade the 6.5 database to
2000.
> As I have never worked with 6.5, how do I perform the copy? We have to
> perform this step as the current server is dying.
> Otto

Copy SQL Server 6.5 Database

I am trying to copying a version 6.5 database on one serv to another version
6. database on another server. And finally upgrade the 6.5 database to 2000.
As I have never worked with 6.5, how do I perform the copy? We have to
perform this step as the current server is dying.
OttoHi
You need to do a backup (DUMP) on the old server, copy the file over and
then a restore (LOAD) on the new 6.5 server.
You can do the dump and restore through Enterprise Manager. SQL 6.5 does not
support attach/detach.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Otto@.ITP" <OttoITP@.discussions.microsoft.com> wrote in message
news:1AA7D978-FACD-4F5F-BE42-A59CBD6D682B@.microsoft.com...
> I am trying to copying a version 6.5 database on one serv to another
version
> 6. database on another server. And finally upgrade the 6.5 database to
2000.
> As I have never worked with 6.5, how do I perform the copy? We have to
> perform this step as the current server is dying.
> Otto|||Otto,
You will need to run sp_help_revdatabase first on the existing server. This
will give you the order of allocations that were used to create the
database.
Use that information to create the exact same database structure on another
6.5 server. Then do a DATABASE DUMP from the original server and a DATABASE
RESTORE to the target server.
When doing this, remember that the sysusers table points to entries in
syslogins. The chances are very small that your users will map properly.
(I would recommend recording the rights granted to each user using
sp_helprotect, deleting the users, then recreating them with the appropriate
rights on the SS2000 server once you are done.)
RLF
"Otto@.ITP" <OttoITP@.discussions.microsoft.com> wrote in message
news:1AA7D978-FACD-4F5F-BE42-A59CBD6D682B@.microsoft.com...
> I am trying to copying a version 6.5 database on one serv to another
version
> 6. database on another server. And finally upgrade the 6.5 database to
2000.
> As I have never worked with 6.5, how do I perform the copy? We have to
> perform this step as the current server is dying.
> Otto

Copy Selected column data from table to another during Upgrade of App

Hi,
I need to write a script that will be called during the database upgrade of my application. This is part of reorg of the tables. The script has to get data for say 4 columns from table A and insert it into another table B. Table B has identity insert column and remaining 4 columns matching the ones to be copied. The data is dependent on user database, hence number of records needs to be copied might be different. Also the columns can have null values.

I tried using bcp Command as follows..
bcp "select colA,colB,colC,colD from A" queryout "c:\temp\A.dat" -t"\t" -r"\n" -c

I'm able to get the dat file, but not the format file. Can anyone tell me how to get it using query file with -c option. Also if there is better option to copy data, kindly let me know.

This is very critical. Appreciate your help.

Thanks,
Ramya.Why not copy the data into a local temporary (or permanent) table?

I presume (perhaps incorrectly) that you want to retain this data to be inserted back into the modified table (or another table) later during the upgrade process.

Regards,

hmscott|||That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..

Thanks,
Ramya.|||Create TableTemp (
ID int IDENTITY(1,1),
ColumnA varchar(10),
ColumnB varchar(10),
ColumnC varchar(10),
ColumnD varchar(10)
)
GO

INSERT INTO TableTemp (ColumnA, ColumnB, ColumnC, ColumnD)
SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM
MySourceTable
GO

ALTER TABLE MySourceTable DROP COLUMN ColumnA
GO

This will leave a permanent copy of the data from MySourceTable in TempTable.

Regards,

hmscott

That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..

Thanks,
Ramya.