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 table from one SQL Server to Another

Using ASP.NET code (VB) I need to copy tables from one SQ: Server to another
SQL Server. This is a poor mans safety backup of some critical data. The
source SQL Server is hosted by an ISP where there is no way to access the DB
via EM or from anywhere other than web pages on that ISP web account so DTS
and such are not options.
WayneIf you're going "poor man's" route ...
Setup the source server as a linked server via your local SQL Server EM
using the credentials you use to login. Then while in your local DB, run
something like
select *
into [My Backup]
from [sqlServer.ispName.com].databasename.dbo.table
-- Alex Papadimoulis
"Wayne Wengert" wrote:

> Using ASP.NET code (VB) I need to copy tables from one SQ: Server to anoth
er
> SQL Server. This is a poor mans safety backup of some critical data. The
> source SQL Server is hosted by an ISP where there is no way to access the
DB
> via EM or from anywhere other than web pages on that ISP web account so DT
S
> and such are not options.
> Wayne
>
>|||The source server can only be "seen" from web pages on the ISP account!
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:7C69D8B7-1870-484D-8658-A21E230F2C0C@.microsoft.com...
> If you're going "poor man's" route ...
> Setup the source server as a linked server via your local SQL Server EM
> using the credentials you use to login. Then while in your local DB, run
> something like
> select *
> into [My Backup]
> from [sqlServer.ispName.com].databasename.dbo.table
> -- Alex Papadimoulis
> "Wayne Wengert" wrote:
>
another
the DB
DTS|||Wayne Wengert wrote:
> Using ASP.NET code (VB) I need to copy tables from one SQ: Server to
> another SQL Server. This is a poor mans safety backup of some
> critical data. The source SQL Server is hosted by an ISP where there
> is no way to access the DB via EM or from anywhere other than web
> pages on that ISP web account so DTS and such are not options.
> Wayne
I'm not sure how you can do that. You would probably need access to the
"other" SQL Server from the ISP's SQL Server by adding a linked server,
but I doubt you have those rights based on what you wrote. You could
create an ASP.Net page to display the contents of the table in a
datagrid (control name?), but from there all you have is the data in an
HTML page. I suppose you could package the table as XML and return the
raw XML in the HTML results.
Are you saying that the ISP won't provide you a backup of the database?
It's strange that you have ADO.Net access to the SQL Server from
ASP.Net, but can't access the server using something like the web data
administrator or even Query Analyzer. I guess the SQL Server has no
direct internet access and is behind the web server (which is a secure
way of setting things up). It's just more difficult from a maintenance
standpoint.
Something tells me there is more harmonious solution here... that I'm
probably just not thinking of. I would request from the ISP they give
you a backup of the database.
David Gugick
Imceda Software
www.imceda.com|||Since this is "critical data" ... get a new ISP ;-)
What about creating a page that outputs CSV or XML and then writing a DTS
package (on local) to grab the data from the URL?
-- Alex Papadimoulis
"Wayne Wengert" wrote:

> The source server can only be "seen" from web pages on the ISP account!
> Wayne
> "Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
> news:7C69D8B7-1870-484D-8658-A21E230F2C0C@.microsoft.com...
> another
> the DB
> DTS
>
>|||Alex;
Thanks for the response. I don't get to choose the ISP! I know that if I
have the table structure on the backup server I can write code to read each
row form the source server and "Insert Into" the backup copy. My problem is
that if they add or remove a field from a table on the source server I need
to reflect that change?
I guess that what I am looking for is a way to create the "CREATE TABLE..."
syntax in code?
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:9AECDF77-5D17-44AD-864E-A883FE054F4A@.microsoft.com...
> Since this is "critical data" ... get a new ISP ;-)
> What about creating a page that outputs CSV or XML and then writing a DTS
> package (on local) to grab the data from the URL?
>
> -- Alex Papadimoulis
> "Wayne Wengert" wrote:
>
EM
run
The
access
so|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esSLUXFJFHA.2884@.tk2msftngp13.phx.gbl...
> Wayne Wengert wrote:
> I'm not sure how you can do that. You would probably need access to the
> "other" SQL Server from the ISP's SQL Server by adding a linked server,
> but I doubt you have those rights based on what you wrote. You could
> create an ASP.Net page to display the contents of the table in a
> datagrid (control name?), but from there all you have is the data in an
> HTML page. I suppose you could package the table as XML and return the
> raw XML in the HTML results.
> Are you saying that the ISP won't provide you a backup of the database?
The ISP does nightly backups but their restore services don't give me a warm
fuzzy (-;
I also want to get copies of the tables into an environment where I can use
other tools to see what is going on. The only export they offer is a CSV of
the data rows (no way to export the table structure I see.)

> It's strange that you have ADO.Net access to the SQL Server from
> ASP.Net, but can't access the server using something like the web data
> administrator or even Query Analyzer. I guess the SQL Server has no
> direct internet access and is behind the web server (which is a secure
> way of setting things up). It's just more difficult from a maintenance
> standpoint.
Amen to that!!!!!
> Something tells me there is more harmonious solution here... that I'm
> probably just not thinking of. I would request from the ISP they give
> you a backup of the database.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Oy.
Well, looks like you're just going to have to write it yourself. DDL isn't
very hard (I create my tables like that, instead of using EM). And then
you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
Some quick queries ...
select table_name from information_schema.tables
select column_name, data_type, data_size from information_schema.columns
where table_name = ?
Look thru the results, build your CREATE TABLE statement.
Good luck!
-- Alex
"Wayne Wengert" wrote:

> Alex;
> Thanks for the response. I don't get to choose the ISP! I know that if I
> have the table structure on the backup server I can write code to read eac
h
> row form the source server and "Insert Into" the backup copy. My problem i
s
> that if they add or remove a field from a table on the source server I nee
d
> to reflect that change?
> I guess that what I am looking for is a way to create the "CREATE TABLE...
"
> syntax in code?
> Wayne
> "Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
> news:9AECDF77-5D17-44AD-864E-A883FE054F4A@.microsoft.com...
> EM
> run
> The
> access
> so
>
>|||Thanks for the suggestion and the sample queries. I'll give it a try.
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:4714AABE-11B2-4ABF-A289-BAA9C6092524@.microsoft.com...
> Oy.
> Well, looks like you're just going to have to write it yourself. DDL isn't
> very hard (I create my tables like that, instead of using EM). And then
> you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
> Some quick queries ...
> select table_name from information_schema.tables
> select column_name, data_type, data_size from information_schema.columns
> where table_name = ?
> Look thru the results, build your CREATE TABLE statement.
> Good luck!
> -- Alex
>
> "Wayne Wengert" wrote:
>
each
is
need
TABLE..."
DTS
account!
message
Server
DB,
Server to
data.
account|||Bummer... they don't allow access to "master" SP's. On to Plan C
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:4714AABE-11B2-4ABF-A289-BAA9C6092524@.microsoft.com...
> Oy.
> Well, looks like you're just going to have to write it yourself. DDL isn't
> very hard (I create my tables like that, instead of using EM). And then
> you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
> Some quick queries ...
> select table_name from information_schema.tables
> select column_name, data_type, data_size from information_schema.columns
> where table_name = ?
> Look thru the results, build your CREATE TABLE statement.
> Good luck!
> -- Alex
>
> "Wayne Wengert" wrote:
>
each
is
need
TABLE..."
DTS
account!
message
Server
DB,
Server to
data.
account