Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.
NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegr oups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>
|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End
|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegr oups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>
Thursday, March 29, 2012
about a SQL script
Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/U...2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable'
)
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTab
le'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>sql
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/U...2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable'
)
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTab
le'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>sql
About a RS version and previous conditions of use
I own a Windows Small Bussiness 2003 license which includes SQL server 2000
Standard Edition and additionally came with a version of Reporting Services.
I want to learn and use it (Reporting Services) as a beginner but when I
try to install it a message appears indicating that I need to install or
configure previously two products:
a) Visual Studio .Net 2003
b) IIS 5.0
Do I need both of 'em just to begin doing simple reports?
I supposed a simple use like I could obtain through Crystal reports 7.0 or
so on.
Please, help me.
Probably next year we will migrate to a new version of Microsoft SBS
Is it worth to do efforts with the versions I own nowadays or not?
Thanks alot in advance.
--
sanpetusRS 2000 report designer require some copy of VS 2003 to be installed. In the
past VB.net 2003 was the cheapest way to do this (about $100). I don't know
now. Note that the VB 2005 will not work for this.
In RS 2005 it comes with a version of VS 2005 so no additional purchase is
necessary.
RS is a asp.net application and as such it needs IIS. IIS comes with all
servers. It might need to configured though.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sanpetus" <sanpetus@.discussions.microsoft.com> wrote in message
news:14FE57B4-A059-4B0F-8539-C9D64CBAA6B6@.microsoft.com...
>I own a Windows Small Bussiness 2003 license which includes SQL server 2000
> Standard Edition and additionally came with a version of Reporting
> Services.
> I want to learn and use it (Reporting Services) as a beginner but when I
> try to install it a message appears indicating that I need to install or
> configure previously two products:
> a) Visual Studio .Net 2003
> b) IIS 5.0
> Do I need both of 'em just to begin doing simple reports?
> I supposed a simple use like I could obtain through Crystal reports 7.0 or
> so on.
> Please, help me.
> Probably next year we will migrate to a new version of Microsoft SBS
> Is it worth to do efforts with the versions I own nowadays or not?
> Thanks alot in advance.
> --
> sanpetus
Standard Edition and additionally came with a version of Reporting Services.
I want to learn and use it (Reporting Services) as a beginner but when I
try to install it a message appears indicating that I need to install or
configure previously two products:
a) Visual Studio .Net 2003
b) IIS 5.0
Do I need both of 'em just to begin doing simple reports?
I supposed a simple use like I could obtain through Crystal reports 7.0 or
so on.
Please, help me.
Probably next year we will migrate to a new version of Microsoft SBS
Is it worth to do efforts with the versions I own nowadays or not?
Thanks alot in advance.
--
sanpetusRS 2000 report designer require some copy of VS 2003 to be installed. In the
past VB.net 2003 was the cheapest way to do this (about $100). I don't know
now. Note that the VB 2005 will not work for this.
In RS 2005 it comes with a version of VS 2005 so no additional purchase is
necessary.
RS is a asp.net application and as such it needs IIS. IIS comes with all
servers. It might need to configured though.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sanpetus" <sanpetus@.discussions.microsoft.com> wrote in message
news:14FE57B4-A059-4B0F-8539-C9D64CBAA6B6@.microsoft.com...
>I own a Windows Small Bussiness 2003 license which includes SQL server 2000
> Standard Edition and additionally came with a version of Reporting
> Services.
> I want to learn and use it (Reporting Services) as a beginner but when I
> try to install it a message appears indicating that I need to install or
> configure previously two products:
> a) Visual Studio .Net 2003
> b) IIS 5.0
> Do I need both of 'em just to begin doing simple reports?
> I supposed a simple use like I could obtain through Crystal reports 7.0 or
> so on.
> Please, help me.
> Probably next year we will migrate to a new version of Microsoft SBS
> Is it worth to do efforts with the versions I own nowadays or not?
> Thanks alot in advance.
> --
> sanpetus
About a Free MSDE manager
Hello!!
Can you advice me some free MSDE tools (like database manager, query
interface...) that can be found over web?
Thanks in advance for your help!!
Ambros Moreno
From Almeria (Spain)
http://www.aspfaq.com/2442
http://www.aspfaq.com/
(Reverse address to reply.)
"Ambros" <ambros@.sasao.com> wrote in message
news:Obexjnf1EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hello!!
> Can you advice me some free MSDE tools (like database manager, query
> interface...) that can be found over web?
> Thanks in advance for your help!!
> Ambros Moreno
> From Almeria (Spain)
>
|||Thanks for the link Aaron!! is really usefull.!!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en el mensaje
news:eOwVYai1EHA.2156@.TK2MSFTNGP10.phx.gbl...
> http://www.aspfaq.com/2442
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Ambros" <ambros@.sasao.com> wrote in message
> news:Obexjnf1EHA.2804@.TK2MSFTNGP15.phx.gbl...
>
Can you advice me some free MSDE tools (like database manager, query
interface...) that can be found over web?
Thanks in advance for your help!!
Ambros Moreno
From Almeria (Spain)
http://www.aspfaq.com/2442
http://www.aspfaq.com/
(Reverse address to reply.)
"Ambros" <ambros@.sasao.com> wrote in message
news:Obexjnf1EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hello!!
> Can you advice me some free MSDE tools (like database manager, query
> interface...) that can be found over web?
> Thanks in advance for your help!!
> Ambros Moreno
> From Almeria (Spain)
>
|||Thanks for the link Aaron!! is really usefull.!!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en el mensaje
news:eOwVYai1EHA.2156@.TK2MSFTNGP10.phx.gbl...
> http://www.aspfaq.com/2442
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Ambros" <ambros@.sasao.com> wrote in message
> news:Obexjnf1EHA.2804@.TK2MSFTNGP15.phx.gbl...
>
About a DataType
What kind of Datatype can I use in my DB to store info like a letter or Rich
Text, including the formats?
thks
--Depending on the expected length including RTF codes, you can either use any
of
the character types (Char, VarChar, NChar, NVarChar, Text or NText).
Thomas
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:9A7D59EE-EF4C-4F01-B661-9A6C5F0413D8@.microsoft.com...
> What kind of Datatype can I use in my DB to store info like a letter or Ri
ch
> Text, including the formats?
> thks
> --
>|||If the documents size can exceed 8k, Use Image type. If all docs are
guaranteed to be less than 8k you cann use Binary...
"Kenny M." wrote:
> What kind of Datatype can I use in my DB to store info like a letter or Ri
ch
> Text, including the formats?
> thks
> --
>
Text, including the formats?
thks
--Depending on the expected length including RTF codes, you can either use any
of
the character types (Char, VarChar, NChar, NVarChar, Text or NText).
Thomas
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:9A7D59EE-EF4C-4F01-B661-9A6C5F0413D8@.microsoft.com...
> What kind of Datatype can I use in my DB to store info like a letter or Ri
ch
> Text, including the formats?
> thks
> --
>|||If the documents size can exceed 8k, Use Image type. If all docs are
guaranteed to be less than 8k you cann use Binary...
"Kenny M." wrote:
> What kind of Datatype can I use in my DB to store info like a letter or Ri
ch
> Text, including the formats?
> thks
> --
>
about 8KB limit
In SQL2005 with the option ROW_OVERFLOW_DATA the restriction of 8KB by row relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. In this case SQL Server use as best the page size, however I wonder what happen when this option is turned off.
For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page?
There is no such thing called ROW_OVERFLOW_DATA option. You cannot turn it on or off. It is based on the column type. If you have variable length columns, sql server allows you to store rows larger than 8k by pushing variable length column values off-row.
If your row size is 3KB fixed size, you will waste 2KB in each page. There is no way to work around and re-use those 2KB space.
Thanks
Sherry
about 8KB limit
In SQL2005 with the option ROW_OVERFLOW_DATA the restriction of 8KB by row relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. In this case SQL Server use as best the page size, however I wonder what happen when this option is turned off.
For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page?
There is no such thing called ROW_OVERFLOW_DATA option. You cannot turn it on or off. It is based on the column type. If you have variable length columns, sql server allows you to store rows larger than 8k by pushing variable length column values off-row.
If your row size is 3KB fixed size, you will waste 2KB in each page. There is no way to work around and re-use those 2KB space.
Thanks
Sherry
Subscribe to:
Posts (Atom)