Is there database, tables, columns naming convention that I should follow
when I build some information system proposed from Microsoft?
Thanks in advanced
AdnanThere have been many discussions regarding this.
http://groups-beta.google.com/group...ming+convention
-oj
"Adnan Hebibovic" <adnan.hebibovic@.community.nospam> wrote in message
news:et0c85YFFHA.3732@.tk2msftngp13.phx.gbl...
> Is there database, tables, columns naming convention that I should follow
> when I build some information system proposed from Microsoft?
> Thanks in advanced
> Adnan
>|||Yes I know that oj but i will repeat myselft ... "from Microsoft".
"oj" <nospam_ojngo@.home.com> wrote in message
news:OmBDpEZFFHA.3728@.TK2MSFTNGP14.phx.gbl...
> There have been many discussions regarding this.
> http://groups-beta.google.com/group...ming+convention
>
> --
> -oj
>
> "Adnan Hebibovic" <adnan.hebibovic@.community.nospam> wrote in message
> news:et0c85YFFHA.3732@.tk2msftngp13.phx.gbl...
>|||Look up the ISO-11179 Standards first.
I have a new book, SQL PROGRAMMING STYLE, due out the middle of this
year that deals with this topic.
I bothered to research readability, typography, industry standards,
etc. I also get into some common design flaws, heuristics, software
metrics, etc.
I am hoping that people will pick it up and use it as a guide in their
shops. Even if they don't like it, they will have a single set of
rules. And they can yell at me :)|||Thanks
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1108827223.033936.154350@.o13g2000cwo.googlegroups.com...
> Look up the ISO-11179 Standards first.
> I have a new book, SQL PROGRAMMING STYLE, due out the middle of this
> year that deals with this topic.
> I bothered to research readability, typography, industry standards,
> etc. I also get into some common design flaws, heuristics, software
> metrics, etc.
> I am hoping that people will pick it up and use it as a guide in their
> shops. Even if they don't like it, they will have a single set of
> rules. And they can yell at me :)
>sql
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Thursday, March 29, 2012
Database Name as a Parameter
Hello folks,
I am in the process of writing a stored proc that selects data from a set of tables. Since this procedure needs to run in multiple databases, I wanted to store database names in a table and have the proc. retrieve the names dynamically.
In short, I want to execute the following stt:
select 'Conversion 1,
'Query 1',
(select count(*) from @.testDB.student)
(in this case @.testDB could be "parul1.dbo" or "parul2.dbo" etc.
How can this functionality be achieved through dynamic SQL?
Thanks so much!
-Paruldeclare @.sql nvarchar(1000),@.testDB varchar(100)
set @.testDB='parul1'
set @.sql='select ''Conversion 1'',''Query 1'',count(*) from '+@.testDB+'.dbo.student'
print @.sql
exec sp_executesql @.sql|||Thanks, that worked!
I have another question - how can i put the results from "EXEC sp_executesql @.SQLSTRING" into a temp table?
How can I get this to work?
Insert #temp1
select @.stage,
'Query 1',
@.sqlStringQuery1,
EXEC sp_executesql @.sqlStringQuery1
Thanks so much!
-Parul|||create a procedure which takes in DB name as parameter...do the select and the insert inside the procedure.|||Thanks, do you have sample code?|||Insert JUST the EXEC into a temp table first. Then, insert into a second temp table the results of that table joined to whatever else you need to insert.
I am in the process of writing a stored proc that selects data from a set of tables. Since this procedure needs to run in multiple databases, I wanted to store database names in a table and have the proc. retrieve the names dynamically.
In short, I want to execute the following stt:
select 'Conversion 1,
'Query 1',
(select count(*) from @.testDB.student)
(in this case @.testDB could be "parul1.dbo" or "parul2.dbo" etc.
How can this functionality be achieved through dynamic SQL?
Thanks so much!
-Paruldeclare @.sql nvarchar(1000),@.testDB varchar(100)
set @.testDB='parul1'
set @.sql='select ''Conversion 1'',''Query 1'',count(*) from '+@.testDB+'.dbo.student'
print @.sql
exec sp_executesql @.sql|||Thanks, that worked!
I have another question - how can i put the results from "EXEC sp_executesql @.SQLSTRING" into a temp table?
How can I get this to work?
Insert #temp1
select @.stage,
'Query 1',
@.sqlStringQuery1,
EXEC sp_executesql @.sqlStringQuery1
Thanks so much!
-Parul|||create a procedure which takes in DB name as parameter...do the select and the insert inside the procedure.|||Thanks, do you have sample code?|||Insert JUST the EXEC into a temp table first. Then, insert into a second temp table the results of that table joined to whatever else you need to insert.
Database Move
I'm needing to move a database from SQL 7 to SQL 2000. What is the
best way to accomplish this and keep all tables, data, permissions, etc
in place throughout the move.
best way to accomplish this and keep all tables, data, permissions, etc
in place throughout the move.
Should I use BCP, Replication or something else?
Thanks for any help.Hi
http://msdn.microsoft.com/library/d...pgrade_6nqr.asp
http://msdn.microsoft.com/library/d...pgrade_5jw3.asp
(and it's sub-pages)
http://support.microsoft.com/defaul...kb;en-us;261334
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/
"cfmx" <kent@.klhs.net> wrote in message
news:1111854538.017241.304760@.l41g2000cwc.googlegr oups.com...
> I'm needing to move a database from SQL 7 to SQL 2000. What is the
> best way to accomplish this and keep all tables, data, permissions, etc
> in place throughout the move.
> Should I use BCP, Replication or something else?
> Thanks for any help.|||cfmx (kent@.klhs.net) writes:
> I'm needing to move a database from SQL 7 to SQL 2000. What is the
> best way to accomplish this and keep all tables, data, permissions, etc
> in place throughout the move.
> Should I use BCP, Replication or something else?
It depends a little. If the server collation is the same on the two
servers, you can use backup/restore or sp_detach_db/sp_attach_db. The
database will automatically be upgraded to SQL 2000 format (and can
thus not be moved back.) To see if the collations are the same run
sp_helpsort.
If the collations are different, you can still use backup/restore or
detach/attach, since, in difference to SQL7 on SQL 2000 you can mix
collations on the same server. Howver, code that uses temp table and
joins them with permanent tables, will not work if the database collation
is different from the server collation.
For more details see these KB articles:
http://support.microsoft.com/?kbid=224071
http://support.microsoft.com/?kbid=325335
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Database Monitoring
Can anybody help me with the following on my MS SQL Server 2000 database.
1. All tables should have a lastModificationDate column. Any changes and inserts should have the system time updated with a trigger or so. We shouldnt be inserting the value using SQL statements into this column.
2. There shouldnt be any deletes on the table. Any deleted records should be marked as inactive or deleted, so it wont come in queries, but should be physically present in the tables.
3. A modification log table, which will carry the table name, the column identifier, user modified, old value and the timestamp.
Thanks
Kishore1. I dont think u need to have triggers for this - just create ModificationDate field & set it as NOTNULL & Default value as CurrentDate
2. Do u want this also to happen in backend with out frontend access then... go for triggers for delete statement and set that to 1 or 0, but anyway u need to change the 'select' query to check the Falg feild.
3. Do u want this to happen in trigger... I would say pls dont go for that... I doesnt know well about ur appln... but if it have Millions of data and for maintenance purpose u run a backend query(update/delete with trigger attached)... just imagine how this trigger will work...
1. All tables should have a lastModificationDate column. Any changes and inserts should have the system time updated with a trigger or so. We shouldnt be inserting the value using SQL statements into this column.
2. There shouldnt be any deletes on the table. Any deleted records should be marked as inactive or deleted, so it wont come in queries, but should be physically present in the tables.
3. A modification log table, which will carry the table name, the column identifier, user modified, old value and the timestamp.
Thanks
Kishore1. I dont think u need to have triggers for this - just create ModificationDate field & set it as NOTNULL & Default value as CurrentDate
2. Do u want this also to happen in backend with out frontend access then... go for triggers for delete statement and set that to 1 or 0, but anyway u need to change the 'select' query to check the Falg feild.
3. Do u want this to happen in trigger... I would say pls dont go for that... I doesnt know well about ur appln... but if it have Millions of data and for maintenance purpose u run a backend query(update/delete with trigger attached)... just imagine how this trigger will work...
Wednesday, March 21, 2012
Database Migration from MS SQL Server 2000 to DB2 on Z/OS
Hello,
We have to migrate the database from MS SQL 2000 to DB2 on Z/OS, Including the tables,triggers,store procedures,etc...
1. What will be the approach ?
2. Whehter any one did in this area ?
3. What is the pros and cons?
4. What is the best possbile tool can be use.
regs
ArunThis belongs in another forum i.e. MS SQL. Will be transferred.
moderator :cool:
Database menu structure
Hello,
I am upgrading my (simple) cms from one menu layer to two layers. So i have two tables in de my DB
- table_mainmenu
- table_submenu
The submenu records are linked to the mainmenu records on the mainmenu id. So far so good. Heres my problem, i cant figure out how to change the order of the pages...!? What is the theory behind recording the stacking order of the menu items? How can i record this information in the DB?
I just need the theory, i can figure out the details for myself. Thanks in advance!
Corstiaan
p.s. is the the right forum to post this?
So i need only one table? Thats nice. Do you perhaps have a DB example of your theory?
Thanks you very much!
Corstiaan|||
Hmm maybe is would be nice after all if you could provide me with same (code) examples for organizing the parent ID and display order? Very much appreciated!
Corstiaan
I am upgrading my (simple) cms from one menu layer to two layers. So i have two tables in de my DB
- table_mainmenu
- table_submenu
The submenu records are linked to the mainmenu records on the mainmenu id. So far so good. Heres my problem, i cant figure out how to change the order of the pages...!? What is the theory behind recording the stacking order of the menu items? How can i record this information in the DB?
I just need the theory, i can figure out the details for myself. Thanks in advance!
Corstiaan
p.s. is the the right forum to post this?
Actually, you do not need two tables, one table should work, for example, Menu table, you need a parentId coulmn in Menu table so menuitem can seft reference itself, now you not only have two-level but also mutiple-level menu using recursive function. You can have a displayOrder column for order of menuitem.
So i need only one table? Thats nice. Do you perhaps have a DB example of your theory?
Thanks you very much!
Corstiaan|||
See below table structure:
Table Menu:
MenuId
MenuName
ToolTip
Url
DisplayOrder: This is a numeric value for display order on the menu
ParentId : The id of the parent menu item. For the main menu the value is 0.
Also you need use recursion to build submenus in your code, basically you need a Menu base class and inheritance MenuItem class, and MenuBuilder class to build your menu system.
Hmm maybe is would be nice after all if you could provide me with same (code) examples for organizing the parent ID and display order? Very much appreciated!
Corstiaan
Friday, February 17, 2012
Database log truncated: Database
Suddenly the bank me this message came back:
Database log truncated: Database: <nome_banco>
and oh some tables were simply without any registration, what did happen?
How do I do to recover my records?
I thank at once.Frank
What do mean by saing "and oh some tables were simply without any
registration, what did happen?"
Im order to restore your data you should have full backup of you database .
To restore data at point of time you have to backup log file too.
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:uEdn4PMqDHA.2964@.tk2msftngp13.phx.gbl...
>
> Suddenly the bank me this message came back:
> Database log truncated: Database: <nome_banco>
> and oh some tables were simply without any registration, what did happen?
> How do I do to recover my records?
> I thank at once.
>
>
Database log truncated: Database: <nome_banco>
and oh some tables were simply without any registration, what did happen?
How do I do to recover my records?
I thank at once.Frank
What do mean by saing "and oh some tables were simply without any
registration, what did happen?"
Im order to restore your data you should have full backup of you database .
To restore data at point of time you have to backup log file too.
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:uEdn4PMqDHA.2964@.tk2msftngp13.phx.gbl...
>
> Suddenly the bank me this message came back:
> Database log truncated: Database: <nome_banco>
> and oh some tables were simply without any registration, what did happen?
> How do I do to recover my records?
> I thank at once.
>
>
Tuesday, February 14, 2012
Database Limititations
Is there a maximum number of databases, tables, columns
for SQL 2000? I have found much info on max RAM, but
nothing on these 3 characteristics, i.e. databases,
tables, columns.http://www.aspfaq.com/2345
http://www.aspfaq.com/
(Reverse address to reply.)
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx
.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Maximum number of databases: 32,767
Maximum number of objects in a databases: 2,147,483,647 (see here for more
information on this
8dbn.asp" target="_blank">http://msdn.microsoft.com/library/d...br />
8dbn.asp)
Not sure about the number of columns, but since a row size can only be 8 K
you will more than likely max out on row size prior to maxing out on the
number of columns.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx
.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Just ran a test of creating a table with lots of columns and got this
message:
CREATE TABLE failed because column 'c1025' in table 'test' exceeds the
maximum of 1024 columns.
So it looks like 1024 is the limit on the number of columns.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eeKW9ULSEHA.1208@.TK2MSFTNGP09.phx.gbl...
> Maximum number of databases: 32,767
> Maximum number of objects in a databases: 2,147,483,647 (see here for more
> information on this
>
http://msdn.microsoft.com/library/d..._ar_ts_8dbn.asp)[
vbcol=seagreen]
> Not sure about the number of columns, but since a row size can only be 8 K
> you will more than likely max out on row size prior to maxing out on the
> number of columns.
>
> --
> ----[/vbcol]
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
> news:1715901c448b2$cbd24470$a301280a@.phx
.gbl...
>|||Maximum Capacity Specifications:
http://msdn.microsoft.com/library/d...y/en-us/archite
c/8_ar_ts_8dbn.asp
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
for SQL 2000? I have found much info on max RAM, but
nothing on these 3 characteristics, i.e. databases,
tables, columns.http://www.aspfaq.com/2345
http://www.aspfaq.com/
(Reverse address to reply.)
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx
.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Maximum number of databases: 32,767
Maximum number of objects in a databases: 2,147,483,647 (see here for more
information on this
8dbn.asp" target="_blank">http://msdn.microsoft.com/library/d...br />
8dbn.asp)
Not sure about the number of columns, but since a row size can only be 8 K
you will more than likely max out on row size prior to maxing out on the
number of columns.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx
.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Just ran a test of creating a table with lots of columns and got this
message:
CREATE TABLE failed because column 'c1025' in table 'test' exceeds the
maximum of 1024 columns.
So it looks like 1024 is the limit on the number of columns.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eeKW9ULSEHA.1208@.TK2MSFTNGP09.phx.gbl...
> Maximum number of databases: 32,767
> Maximum number of objects in a databases: 2,147,483,647 (see here for more
> information on this
>
http://msdn.microsoft.com/library/d..._ar_ts_8dbn.asp)[
vbcol=seagreen]
> Not sure about the number of columns, but since a row size can only be 8 K
> you will more than likely max out on row size prior to maxing out on the
> number of columns.
>
> --
> ----[/vbcol]
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
> news:1715901c448b2$cbd24470$a301280a@.phx
.gbl...
>|||Maximum Capacity Specifications:
http://msdn.microsoft.com/library/d...y/en-us/archite
c/8_ar_ts_8dbn.asp
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Database Limititations
Is there a maximum number of databases, tables, columns
for SQL 2000? I have found much info on max RAM, but
nothing on these 3 characteristics, i.e. databases,
tables, columns.
http://www.aspfaq.com/2345
http://www.aspfaq.com/
(Reverse address to reply.)
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.
|||Maximum number of databases: 32,767
Maximum number of objects in a databases: 2,147,483,647 (see here for more
information on this
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp)
Not sure about the number of columns, but since a row size can only be 8 K
you will more than likely max out on row size prior to maxing out on the
number of columns.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.
|||Just ran a test of creating a table with lots of columns and got this
message:
CREATE TABLE failed because column 'c1025' in table 'test' exceeds the
maximum of 1024 columns.
So it looks like 1024 is the limit on the number of columns.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eeKW9ULSEHA.1208@.TK2MSFTNGP09.phx.gbl...
> Maximum number of databases: 32,767
> Maximum number of objects in a databases: 2,147,483,647 (see here for more
> information on this
>
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp)
> Not sure about the number of columns, but since a row size can only be 8 K
> you will more than likely max out on row size prior to maxing out on the
> number of columns.
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
> news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
>
|||Maximum Capacity Specifications:
http://msdn.microsoft.com/library/de.../en-us/archite
c/8_ar_ts_8dbn.asp
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
for SQL 2000? I have found much info on max RAM, but
nothing on these 3 characteristics, i.e. databases,
tables, columns.
http://www.aspfaq.com/2345
http://www.aspfaq.com/
(Reverse address to reply.)
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.
|||Maximum number of databases: 32,767
Maximum number of objects in a databases: 2,147,483,647 (see here for more
information on this
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp)
Not sure about the number of columns, but since a row size can only be 8 K
you will more than likely max out on row size prior to maxing out on the
number of columns.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.
|||Just ran a test of creating a table with lots of columns and got this
message:
CREATE TABLE failed because column 'c1025' in table 'test' exceeds the
maximum of 1024 columns.
So it looks like 1024 is the limit on the number of columns.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eeKW9ULSEHA.1208@.TK2MSFTNGP09.phx.gbl...
> Maximum number of databases: 32,767
> Maximum number of objects in a databases: 2,147,483,647 (see here for more
> information on this
>
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp)
> Not sure about the number of columns, but since a row size can only be 8 K
> you will more than likely max out on row size prior to maxing out on the
> number of columns.
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
> news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
>
|||Maximum Capacity Specifications:
http://msdn.microsoft.com/library/de.../en-us/archite
c/8_ar_ts_8dbn.asp
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Database Limititations
Is there a maximum number of databases, tables, columns
for SQL 2000? I have found much info on max RAM, but
nothing on these 3 characteristics, i.e. databases,
tables, columns.http://www.aspfaq.com/2345
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Maximum number of databases: 32,767
Maximum number of objects in a databases: 2,147,483,647 (see here for more
information on this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp)
Not sure about the number of columns, but since a row size can only be 8 K
you will more than likely max out on row size prior to maxing out on the
number of columns.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Just ran a test of creating a table with lots of columns and got this
message:
CREATE TABLE failed because column 'c1025' in table 'test' exceeds the
maximum of 1024 columns.
So it looks like 1024 is the limit on the number of columns.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eeKW9ULSEHA.1208@.TK2MSFTNGP09.phx.gbl...
> Maximum number of databases: 32,767
> Maximum number of objects in a databases: 2,147,483,647 (see here for more
> information on this
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp)
> Not sure about the number of columns, but since a row size can only be 8 K
> you will more than likely max out on row size prior to maxing out on the
> number of columns.
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
> news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> > Is there a maximum number of databases, tables, columns
> > for SQL 2000? I have found much info on max RAM, but
> > nothing on these 3 characteristics, i.e. databases,
> > tables, columns.
>
for SQL 2000? I have found much info on max RAM, but
nothing on these 3 characteristics, i.e. databases,
tables, columns.http://www.aspfaq.com/2345
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Maximum number of databases: 32,767
Maximum number of objects in a databases: 2,147,483,647 (see here for more
information on this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp)
Not sure about the number of columns, but since a row size can only be 8 K
you will more than likely max out on row size prior to maxing out on the
number of columns.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Just ran a test of creating a table with lots of columns and got this
message:
CREATE TABLE failed because column 'c1025' in table 'test' exceeds the
maximum of 1024 columns.
So it looks like 1024 is the limit on the number of columns.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eeKW9ULSEHA.1208@.TK2MSFTNGP09.phx.gbl...
> Maximum number of databases: 32,767
> Maximum number of objects in a databases: 2,147,483,647 (see here for more
> information on this
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp)
> Not sure about the number of columns, but since a row size can only be 8 K
> you will more than likely max out on row size prior to maxing out on the
> number of columns.
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
> news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> > Is there a maximum number of databases, tables, columns
> > for SQL 2000? I have found much info on max RAM, but
> > nothing on these 3 characteristics, i.e. databases,
> > tables, columns.
>
Database level change
I have many tables and in those i require to change some data. Say
from ARCA to ARCAEX. I am sure that the string is unique in the sense
there will be no ARCAABC. So what do i do change by not manually
needing to search in each table and the whole database and still can
be sure that the changes have taken place. Please help
Regards,
RajeshIf that, what I think you problem is, is true, then i think you can
try the next:
Make a cursor loop trough the tables in de sysobjects and make in
every loop a text string that contains a update statement for the
current table and execute that string. That works only when every
table has the same column names ofcourse...
from ARCA to ARCAEX. I am sure that the string is unique in the sense
there will be no ARCAABC. So what do i do change by not manually
needing to search in each table and the whole database and still can
be sure that the changes have taken place. Please help
Regards,
RajeshIf that, what I think you problem is, is true, then i think you can
try the next:
Make a cursor loop trough the tables in de sysobjects and make in
every loop a text string that contains a update statement for the
current table and execute that string. That works only when every
table has the same column names ofcourse...
For SQL Server 2000 something like:
DECLARE @.Table_name sysname
DECLARE @.cmd1 varchar(1000)
DECLARE c_table CURSOR
FOR SELECT [Name], FROM sysobjects WHERE [Name] LIKE ('ARC%') ORDER BY
[Name]
OPEN c_table
FETCH NEXT FROM c_table INTO @.Table_name
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
SELECT @.cmd1 = 'UPDATE ' + @.Table_name + ' SET columnname =
''value'''
EXEC (@.cmd1)
FETCH NEXT FROM c_table INTO @.Table_name
END
CLOSE c_table
DEALLOCATE c_table
raj_chins@.rediffmail.com (Rajesh Garg) wrote in message news:<14215add.0308042113.10c410e5@.posting.google.com>...
> I have many tables and in those i require to change some data. Say
> from ARCA to ARCAEX. I am sure that the string is unique in the sense
> there will be no ARCAABC. So what do i do change by not manually
> needing to search in each table and the whole database and still can
> be sure that the changes have taken place. Please help
> Regards,
> Rajesh|||hi
i have come accross a better solution....we can write a stored proc
and run it whenever required. it may take more time for really big
databases.
*******************************
the stored proc is written
******************************
CREATE PROC SearchAndReplace
(
@.SearchStr nvarchar(100),
@.ReplaceStr nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128),
@.SearchStr2 nvarchar(110), @.SQL nvarchar(4000), @.RCTR int
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
SET @.RCTR = 0
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) >
@.TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@.TableName, 2)
ANDTABLE_NAME= PARSENAME(@.TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
SET @.SQL='UPDATE ' + @.TableName +
' SET ' + @.ColumnName
+ ' = REPLACE(' + @.ColumnName + ', '
+ QUOTENAME(@.SearchStr, '''') + ', ' + QUOTENAME(@.ReplaceStr,
'''') +
') WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
EXEC (@.SQL)
SET @.RCTR = @.RCTR + @.@.ROWCOUNT
END
END
END
SELECT 'Replaced ' + CAST(@.RCTR AS varchar) + ' occurence(s)' AS
'Outcome'
END
************************************************
the stored can be simply called like this
*************************************************
--To replace all occurences of 'ABC' with 'XYZ':
EXEC SearchAndReplace 'ABC', 'XYZ'
GO
napel25@.hotmail.com (EtN) wrote in message news:<ddaf11e.0308050435.21c5971a@.posting.google.com>...
> If that, what I think you problem is, is true, then i think you can
> try the next:
> Make a cursor loop trough the tables in de sysobjects and make in
> every loop a text string that contains a update statement for the
> current table and execute that string. That works only when every
> table has the same column names ofcourse...
> For SQL Server 2000 something like:
> DECLARE @.Table_name sysname
> DECLARE @.cmd1 varchar(1000)
> DECLARE c_table CURSOR
> FOR SELECT [Name], FROM sysobjects WHERE [Name] LIKE ('ARC%') ORDER BY
> [Name]
> OPEN c_table
> FETCH NEXT FROM c_table INTO @.Table_name
> WHILE(@.@.FETCH_STATUS <> -1)
> BEGIN
> SELECT @.cmd1 = 'UPDATE ' + @.Table_name + ' SET columnname =
> ''value'''
> EXEC (@.cmd1)
> FETCH NEXT FROM c_table INTO @.Table_name
> END
> CLOSE c_table
> DEALLOCATE c_table
>
> raj_chins@.rediffmail.com (Rajesh Garg) wrote in message news:<14215add.0308042113.10c410e5@.posting.google.com>...
> > I have many tables and in those i require to change some data. Say
> > from ARCA to ARCAEX. I am sure that the string is unique in the sense
> > there will be no ARCAABC. So what do i do change by not manually
> > needing to search in each table and the whole database and still can
> > be sure that the changes have taken place. Please help
> > Regards,
> > Rajesh
Subscribe to:
Posts (Atom)