Thursday, March 29, 2012
Export DB to SQL Script
I am wanting to export a db.
It is unlikely that i will have access to a destination server via a network
or internet
connection. So i am intending to export my db to an sql script.
I have used the generate sql script option previously, but have found it
only exports the db objects. I need to export both the db objects and the
data they contain.
Any helpful suggestions would be appreciated!!Hi
If your database is small enought to fit onto a CD/DVD then you could use a
backup and restore it onto the new server
http://support.microsoft.com/defaul...b;en-us;Q314546
Failing that you should look at the BCP utility.
If you want to script as insert statements then check out
http://vyaskn.tripod.com/code.htm#inserts although I would not recommend it
for large amounts of data.
John
"AJ" wrote:
> How?
> I am wanting to export a db.
> It is unlikely that i will have access to a destination server via a netwo
rk
> or internet
> connection. So i am intending to export my db to an sql script.
> I have used the generate sql script option previously, but have found it
> only exports the db objects. I need to export both the db objects and the
> data they contain.
> Any helpful suggestions would be appreciated!!|||DB Ghost - www.dbghost.com
1. Use the data and schema scripter to script out all your tables and data
into a temporary directory.
2. Use the change manager - option one to build a database using the scripts
above and record the build into a SQL file. This step ensure the SQL is in
the correct dependancy order which Enterprise Manager cannot always do.
"AJ" wrote:
> How?
> I am wanting to export a db.
> It is unlikely that i will have access to a destination server via a netwo
rk
> or internet
> connection. So i am intending to export my db to an sql script.
> I have used the generate sql script option previously, but have found it
> only exports the db objects. I need to export both the db objects and the
> data they contain.
> Any helpful suggestions would be appreciated!!
Monday, March 26, 2012
Export data into a script as "insert into"s
I am using a Web based front end to a SQL Server db installed at my host that only lets me import data into tables either manually (in a 'datasheet' view) or with a SQL query.
Does anybody know if I can export data from my local db as a script such as
insert into TestTable(Id, Name) values ("1", "Test1")
insert into TestTable(Id, Name) values ("2", "Test2")
.
.
.
... so that I can run this script on the Web front end and insert data into the db at my host?
Many thanks in advance.Here's a link to some stored procedures you might find useful. I think the DataAs InsCommand may be what you are looking for.
http://www.mssqlcity.com/Scripts/scrImpExp.htm|||Thanks for your help Fred.
That stored procedure mostly does the job but looks like it needs a few slight mods because some data types aren't implemented (such as text which is written out as 'None Yet')...
Thursday, March 22, 2012
Export a table data in INSERT statement
EM ?
Man
Take a look at http://vyaskn.tripod.com/code/generate_inserts.txt
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eBJf9RU3FHA.1600@.TK2MSFTNGP12.phx.gbl...
> Can I export a table data into a script file contains INSERT statements in
> EM ?
>
Export a table data in INSERT statement
EM ?Man
Take a look at http://vyaskn.tripod.com/code/generate_inserts.txt
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eBJf9RU3FHA.1600@.TK2MSFTNGP12.phx.gbl...
> Can I export a table data into a script file contains INSERT statements in
> EM ?
>sql
Wednesday, March 21, 2012
Export a table data in INSERT statement
EM ?Man
Take a look at http://vyaskn.tripod.com/code/generate_inserts.txt
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eBJf9RU3FHA.1600@.TK2MSFTNGP12.phx.gbl...
> Can I export a table data into a script file contains INSERT statements in
> EM ?
>
Export a table data in a script file
Enterprise Manager or Query Analyser ?Try this
http://vyaskn.tripod.com/code/generate_inserts.txt
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eQbNxGs2FHA.3092@.TK2MSFTNGP10.phx.gbl...
> How can I export a table data as a script file as INSERT statement from
> Enterprise Manager or Query Analyser ?
>
>
Export a table (datas and structure)
I need to generate a script from MS Sql Server 2005 that should contain the
structure and the datas of a table.
This script will be used by another SQL Server (but a 2000 version).
These 2 Sql Servers are not connected, so I can't use the import/export
task.
what would be the best way for this need ?
I thank you in advance
Stan
On Aug 9, 4:34 pm, "Stan SR" <s...@.pasdepam.netsunset.com> wrote:
> Hi,
> I need to generate a script from MS Sql Server 2005 that should contain the
> structure and the datas of a table.
> This script will be used by another SQL Server (but a 2000 version).
> These 2 Sql Servers are not connected, so I can't use the import/export
> task.
> what would be the best way for this need ?
> I thank you in advance
> Stan
I'm not sure if you are interested in any third party tools, but you
could easily script the data features utilizing Red Gate's Data
Compare. It will generate the necessary scripts to populate a table
with all relevant data. If this is a one time shot, you could simply
download the trial and go from there. While you are at it, you could
utilize their Schema Compare and script out your schema as well.
http://www.red-gate.com
Export a table (datas and structure)
I need to generate a script from MS Sql Server 2005 that should contain the
structure and the datas of a table.
This script will be used by another SQL Server (but a 2000 version).
These 2 Sql Servers are not connected, so I can't use the import/export
task.
what would be the best way for this need ?
I thank you in advance
StanOn Aug 9, 4:34 pm, "Stan SR" <s...@.pasdepam.netsunset.com> wrote:
> Hi,
> I need to generate a script from MS Sql Server 2005 that should contain the
> structure and the datas of a table.
> This script will be used by another SQL Server (but a 2000 version).
> These 2 Sql Servers are not connected, so I can't use the import/export
> task.
> what would be the best way for this need ?
> I thank you in advance
> Stan
I'm not sure if you are interested in any third party tools, but you
could easily script the data features utilizing Red Gate's Data
Compare. It will generate the necessary scripts to populate a table
with all relevant data. If this is a one time shot, you could simply
download the trial and go from there. While you are at it, you could
utilize their Schema Compare and script out your schema as well.
http://www.red-gate.com
Export a table (datas and structure)
I need to generate a script from MS Sql Server 2005 that should contain the
structure and the datas of a table.
This script will be used by another SQL Server (but a 2000 version).
These 2 Sql Servers are not connected, so I can't use the import/export
task.
what would be the best way for this need ?
I thank you in advance
StanOn Aug 9, 4:34 pm, "Stan SR" <s...@.pasdepam.netsunset.com> wrote:
> Hi,
> I need to generate a script from MS Sql Server 2005 that should contain th
e
> structure and the datas of a table.
> This script will be used by another SQL Server (but a 2000 version).
> These 2 Sql Servers are not connected, so I can't use the import/export
> task.
> what would be the best way for this need ?
> I thank you in advance
> Stan
I'm not sure if you are interested in any third party tools, but you
could easily script the data features utilizing Red Gate's Data
Compare. It will generate the necessary scripts to populate a table
with all relevant data. If this is a one time shot, you could simply
download the trial and go from there. While you are at it, you could
utilize their Schema Compare and script out your schema as well.
http://www.red-gate.comsql
Monday, March 19, 2012
Explorer Script Error
recieve an Internet Script Error
LINE: 307
Char: 2
Error: Unspecified
Code: 0
URL:
res://C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Binn\Resources\10
33\sqlmmc.rll/Tabs.htmlFor that error, Change the view for the database in
Enterprise Manager to something other than task pad. Then
close Enterprise Manager. The reopen Enterprise Manager and
change the view back to task pad. You should be able to
proceed from there without errors.
-Sue
On Wed, 29 Oct 2003 14:26:04 -0800, "Bob"
<rrowe68@.hotmail.com> wrote:
>Ever time I open Enterprise Manager and select the database to work on, I
>recieve an Internet Script Error
>
>LINE: 307
>Char: 2
>Error: Unspecified
>Code: 0
>URL:
>res://C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Binn\Resources\10
>33\sqlmmc.rll/Tabs.html
>|||Thanks - but if I want to view the task pad - do you know of a fix?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:h6g0qv499idhe0iq9vebftffiuaquuepfh@.4ax.com...
> For that error, Change the view for the database in
> Enterprise Manager to something other than task pad. Then
> close Enterprise Manager. The reopen Enterprise Manager and
> change the view back to task pad. You should be able to
> proceed from there without errors.
> -Sue
> On Wed, 29 Oct 2003 14:26:04 -0800, "Bob"
> <rrowe68@.hotmail.com> wrote:
> >Ever time I open Enterprise Manager and select the database to work on, I
> >recieve an Internet Script Error
> >
> >
> >LINE: 307
> >Char: 2
> >Error: Unspecified
> >Code: 0
> >URL:
>res://C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Binn\Resources\1
0
> >33\sqlmmc.rll/Tabs.html
> >
>|||After changing it to something else other than task pad,
closing Enterprise Manager, you can reopen and then change
the view back to task pad.
I don't think there is any fix other than going through this
routine when you get the error.
-Sue
On Wed, 29 Oct 2003 16:01:43 -0800, "Bob"
<rrowe68@.hotmail.com> wrote:
>Thanks - but if I want to view the task pad - do you know of a fix?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:h6g0qv499idhe0iq9vebftffiuaquuepfh@.4ax.com...
>> For that error, Change the view for the database in
>> Enterprise Manager to something other than task pad. Then
>> close Enterprise Manager. The reopen Enterprise Manager and
>> change the view back to task pad. You should be able to
>> proceed from there without errors.
>> -Sue
>> On Wed, 29 Oct 2003 14:26:04 -0800, "Bob"
>> <rrowe68@.hotmail.com> wrote:
>> >Ever time I open Enterprise Manager and select the database to work on, I
>> >recieve an Internet Script Error
>> >
>> >
>> >LINE: 307
>> >Char: 2
>> >Error: Unspecified
>> >Code: 0
>> >URL:
>>res://C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Binn\Resources\1
>0
>> >33\sqlmmc.rll/Tabs.html
>> >
>|||That did it -- Thanks.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:rsl0qvk2midqee2rms1rmj8kse0egf232t@.4ax.com...
> After changing it to something else other than task pad,
> closing Enterprise Manager, you can reopen and then change
> the view back to task pad.
> I don't think there is any fix other than going through this
> routine when you get the error.
> -Sue
> On Wed, 29 Oct 2003 16:01:43 -0800, "Bob"
> <rrowe68@.hotmail.com> wrote:
> >Thanks - but if I want to view the task pad - do you know of a fix?
> >
> >
> >
> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> >news:h6g0qv499idhe0iq9vebftffiuaquuepfh@.4ax.com...
> >> For that error, Change the view for the database in
> >> Enterprise Manager to something other than task pad. Then
> >> close Enterprise Manager. The reopen Enterprise Manager and
> >> change the view back to task pad. You should be able to
> >> proceed from there without errors.
> >>
> >> -Sue
> >>
> >> On Wed, 29 Oct 2003 14:26:04 -0800, "Bob"
> >> <rrowe68@.hotmail.com> wrote:
> >>
> >> >Ever time I open Enterprise Manager and select the database to work
on, I
> >> >recieve an Internet Script Error
> >> >
> >> >
> >> >LINE: 307
> >> >Char: 2
> >> >Error: Unspecified
> >> >Code: 0
> >> >URL:
> >>
>>res://C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Binn\Resources\
1
> >0
> >> >33\sqlmmc.rll/Tabs.html
> >> >
> >>
> >
>
Explain TSQL running
I found this script in one FIDONET conference and decided to test my servers
available around. The result is more than strange.
-- TSQL
DECLARE @.i INT,@.j INT
SET @.j=0
SET @.i=0
WHILE @.i <= 3000000
BEGIN
IF @.i > 100
SET @.j = @.j+100
IF @.i < 300
SET @.j = @.j-200
IF (@.i%6) = 3
SET @.j = 356
IF @.i%37 = 7
SET @.j = @.j + 343
SET @.i=@.i+1
END
SELECT @.j
I installed MSSQL Server 2000 on another machine on my home LAN, then logged
in from the Enterprise Manager, started the Query Analyzer for the LAN
machine from my local machine and finally started this script - 36 seconds,
the local CPU load was about 100% for one of two processors, totally 50%.
Then I closed the Query Analyzer and reopened it on my local machine - same
script - 5 minutes and 39 seconds, the CPU load was about 5-7%. That's very
strange how MSSQL Server works. If I start the script on my local machine it
looks like it works on the LAN machine, but when I start the same script on
the LAN machine it runs on my own local machine. It can't be a mistake.
Maybe I'm missing something, but I got an illusion if we run some script
it's not necessarily will be running on the machine where it should be.
Finally I started the same script against our production server through the
Internet - I suspect the the CPU load was about 1-2% for this task because
it was finished in 24:31, almost 25 minutes!
I'm not using a SQL Server cluster at home for sure, but I can't understand
that. I tested three times with the same result up to one second. The
machines on my LAN are having almost the same configuraion with only couple
differences - my local machine is a Sager (500P) laptop with 1 gig memory,
(LAN) remote (ASUSTeK P4C800 Deluxe motherboard) machine is a desktop with 2
gigs, the processors in both cases are IP 3.2HT, FBS 800, etc. The laptop
CPU has 512 kbytes internal cache, the LAN machine has 1 meg internal cache.
The other parameters are not significant for this case.
The MSSQL Server settings are same for both cases.
Maybe it's just a bug and I shouldn't pay an attention? But why the Query
Analyzer uses the wrong machine when I start the SQL Script? Configuration
problems? All servers are configured in the Enterprise Manager by their
IPs,SqlPort and it shouldn't be a mistake, my home IPs are static.
The funny thing that I saw the same reports in this FIDONET group, when the
script was started locally the CPU load was in some cases from 1-2 to 5-7%
and the duration of this script was very different, like in my case.
So, bug or feature?!
Just D.
Just D. wrote:
> All,
> I found this script in one FIDONET conference and decided to test my servers
> available around. The result is more than strange.
> -- TSQL
> DECLARE @.i INT,@.j INT
> SET @.j=0
> SET @.i=0
> WHILE @.i <= 3000000
> BEGIN
> IF @.i > 100
> SET @.j = @.j+100
> IF @.i < 300
> SET @.j = @.j-200
> IF (@.i%6) = 3
> SET @.j = 356
> IF @.i%37 = 7
> SET @.j = @.j + 343
> SET @.i=@.i+1
> END
> SELECT @.j
> I installed MSSQL Server 2000 on another machine on my home LAN, then logged
> in from the Enterprise Manager, started the Query Analyzer for the LAN
> machine from my local machine and finally started this script - 36 seconds,
> the local CPU load was about 100% for one of two processors, totally 50%.
> Then I closed the Query Analyzer and reopened it on my local machine - same
> script - 5 minutes and 39 seconds, the CPU load was about 5-7%. That's very
> strange how MSSQL Server works. If I start the script on my local machine it
> looks like it works on the LAN machine, but when I start the same script on
> the LAN machine it runs on my own local machine. It can't be a mistake.
> Maybe I'm missing something, but I got an illusion if we run some script
> it's not necessarily will be running on the machine where it should be.
> Finally I started the same script against our production server through the
> Internet - I suspect the the CPU load was about 1-2% for this task because
> it was finished in 24:31, almost 25 minutes!
> I'm not using a SQL Server cluster at home for sure, but I can't understand
> that. I tested three times with the same result up to one second. The
> machines on my LAN are having almost the same configuraion with only couple
> differences - my local machine is a Sager (500P) laptop with 1 gig memory,
> (LAN) remote (ASUSTeK P4C800 Deluxe motherboard) machine is a desktop with 2
> gigs, the processors in both cases are IP 3.2HT, FBS 800, etc. The laptop
> CPU has 512 kbytes internal cache, the LAN machine has 1 meg internal cache.
> The other parameters are not significant for this case.
> The MSSQL Server settings are same for both cases.
> Maybe it's just a bug and I shouldn't pay an attention? But why the Query
> Analyzer uses the wrong machine when I start the SQL Script? Configuration
> problems? All servers are configured in the Enterprise Manager by their
> IPs,SqlPort and it shouldn't be a mistake, my home IPs are static.
> The funny thing that I saw the same reports in this FIDONET group, when the
> script was started locally the CPU load was in some cases from 1-2 to 5-7%
> and the duration of this script was very different, like in my case.
> So, bug or feature?!
> Just D.
>
Well, can you explain what do you mean 'script runs on a wrong machine'?
It runs on a server to which QA or whatever client you are using, is connected...
I will check tomorrow how it works
|||I've had a try running local on a SQL Server and from a client PC connecting
to the SQL Server.
When running locally, the CPU usage hits 100%, with the SQL Server process
taking about 70% and the ISQLW taking up the rest.
When running from a client PC, the ISQLW process is working between 10% to
40% and the SQL Server process on the server is running between 40% to 60%.
The question is why is ISQLW so busy. What is it doing ? I thought it just
submitted the batch and sat back waiting got the result. And it doesn't
really improve when you put this into a Stored Procedure.
Al
"Andrey" wrote:
> Just D. wrote:
>
> Well, can you explain what do you mean 'script runs on a wrong machine'?
> It runs on a server to which QA or whatever client you are using, is connected...
> I will check tomorrow how it works
>
|||"Al" <Al@.discussions.microsoft.com> wrote in message
news:A47458DC-7634-4875-962F-2B4F72380D3B@.microsoft.com...
> I've had a try running local on a SQL Server and from a client PC
> connecting
> to the SQL Server.
> When running locally, the CPU usage hits 100%, with the SQL Server process
> taking about 70% and the ISQLW taking up the rest.
> When running from a client PC, the ISQLW process is working between 10% to
> 40% and the SQL Server process on the server is running between 40% to
> 60%.
> The question is why is ISQLW so busy. What is it doing ? I thought it just
> submitted the batch and sat back waiting got the result. And it doesn't
> really improve when you put this into a Stored Procedure.
It's really interesting. It must be something with DBNETLIB dlls.
RADl0PASlV
|||Tried with osql, using Named Pipes and TCP/IP and the same results for both
When running the SQL as a stored procedure or as a batch, osql is quite busy.
"RADl0PASlV" wrote:
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:A47458DC-7634-4875-962F-2B4F72380D3B@.microsoft.com...
> It's really interesting. It must be something with DBNETLIB dlls.
> RADl0PASlV
>
>
|||Hi Al,
Are you having only one server registered in EM? Do you have a local and
remote one? It would be interesting to repeat the same that I did - one
local server, one serer on LAN and one server on the Internet.
When you were getting 70% did you try to evaluate the IP traffic? Maybe your
machine was pooling the remote server?
Just D.
" <Al@.discussions.microsoft.com> wrote in message
news:A47458DC-7634-4875-962F-2B4F72380D3B@.microsoft.com...
> I've had a try running local on a SQL Server and from a client PC
> connecting
> to the SQL Server.
> When running locally, the CPU usage hits 100%, with the SQL Server process
> taking about 70% and the ISQLW taking up the rest.
> When running from a client PC, the ISQLW process is working between 10% to
> 40% and the SQL Server process on the server is running between 40% to
> 60%.
> The question is why is ISQLW so busy. What is it doing ? I thought it just
> submitted the batch and sat back waiting got the result. And it doesn't
> really improve when you put this into a Stored Procedure.
|||The cause is the NoCount option.
If this is turned off e.g. you're getting the number of rows effect back,
then the client program (ISQLW or OSQL) is quite busy. When it is turned on,
then the client program isn't eating up any CPU cycles.
In the test across the LAN, the two execution times are 1 minute 40 seconds
for SET NOCOUNT OFF, and 30 seconds for SET NOCOUNT ON.
On face value, this is not suprising, except that the query in question only
returns the row count on the final select statement.
Al
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:A47458DC-7634-4875-962F-2B4F72380D3B@.microsoft.com...
> It's really interesting. It must be something with DBNETLIB dlls.
> RADl0PASlV
>
|||Hi RADl0PASlV,
> It's really interesting. It must be something with DBNETLIB dlls.
Not only that is interesting. I stopped all computers and connections around
and started this test on my machine - the CPU load is 1-2% and that's all!
Why? If I want to do something, then why this MSSQLServer decides itself how
it should use my resources? I have 3.2 HT processor and I allow MSSQL Server
to use both sub-processors, but it doesn't.
Anyway, that's very strange. Placing the same code to the SP doesn't help,
same result, same duration, same CPU Load.
Just D.
Friday, March 9, 2012
Expend all DBs
is about 2 megs. Does anyone know how I can automate a script that will
increase the size to 500 megs for ALL DBs. I want to run this script during
the evening hours.
Ishu
"Tim" <tim@.nospam.com> wrote in message
news:Er_me.6218$sm1.512@.news.cpqcorp.net...
> I support a server that has over 200 databases on 1 instance. Each
database
> is about 2 megs. Does anyone know how I can automate a script that will
> increase the size to 500 megs for ALL DBs. I want to run this script
during
> the evening hours.
> Ishu
>
First off, do you have enough storage to do this? That's about 1 terabyte
if my math is correct.
You could use dynamic SQL to do this.
Select the database name from master.dbo.sysdatabases into a cursor. For
each non-system database, run the ALTER DATABASE command and reset your
sizes.
Rick Sawtell
MCT, MCSD, MCDBA
|||There are couple of ways.
You can create a cursor where you will get
list of databases you need to expand
and then inside cursor use dynamic SQL to
run ALTER DATABASE against each DB.
Another way for example to run a script
that will generate another SQL script with names of DBs in there
Something like
select 'ALTER DATABASE '+name+'...' from master..sysdatabases where ... --
make sure you will exclude system databases fom there
Then execute result script to expand databases.
This way is good as a one time deal.
Regards.
"Tim" wrote:
> I support a server that has over 200 databases on 1 instance. Each database
> is about 2 megs. Does anyone know how I can automate a script that will
> increase the size to 500 megs for ALL DBs. I want to run this script during
> the evening hours.
> Ishu
>
>
Expend all DBs
is about 2 megs. Does anyone know how I can automate a script that will
increase the size to 500 megs for ALL DBs. I want to run this script during
the evening hours.
Ishu"Tim" <tim@.nospam.com> wrote in message
news:Er_me.6218$sm1.512@.news.cpqcorp.net...
> I support a server that has over 200 databases on 1 instance. Each
database
> is about 2 megs. Does anyone know how I can automate a script that will
> increase the size to 500 megs for ALL DBs. I want to run this script
during
> the evening hours.
> Ishu
>
First off, do you have enough storage to do this? That's about 1 terabyte
if my math is correct.
You could use dynamic SQL to do this.
Select the database name from master.dbo.sysdatabases into a cursor. For
each non-system database, run the ALTER DATABASE command and reset your
sizes.
Rick Sawtell
MCT, MCSD, MCDBA|||There are couple of ways.
You can create a cursor where you will get
list of databases you need to expand
and then inside cursor use dynamic SQL to
run ALTER DATABASE against each DB.
Another way for example to run a script
that will generate another SQL script with names of DBs in there
Something like
select 'ALTER DATABASE '+name+'...' from master..sysdatabases where ... --
make sure you will exclude system databases fom there
Then execute result script to expand databases.
This way is good as a one time deal.
Regards.
"Tim" wrote:
> I support a server that has over 200 databases on 1 instance. Each databa
se
> is about 2 megs. Does anyone know how I can automate a script that will
> increase the size to 500 megs for ALL DBs. I want to run this script duri
ng
> the evening hours.
> Ishu
>
>
Expend all DBs
is about 2 megs. Does anyone know how I can automate a script that will
increase the size to 500 megs for ALL DBs. I want to run this script during
the evening hours.
Ishu"Tim" <tim@.nospam.com> wrote in message
news:Er_me.6218$sm1.512@.news.cpqcorp.net...
> I support a server that has over 200 databases on 1 instance. Each
database
> is about 2 megs. Does anyone know how I can automate a script that will
> increase the size to 500 megs for ALL DBs. I want to run this script
during
> the evening hours.
> Ishu
>
First off, do you have enough storage to do this? That's about 1 terabyte
if my math is correct.
You could use dynamic SQL to do this.
Select the database name from master.dbo.sysdatabases into a cursor. For
each non-system database, run the ALTER DATABASE command and reset your
sizes.
Rick Sawtell
MCT, MCSD, MCDBA|||There are couple of ways.
You can create a cursor where you will get
list of databases you need to expand
and then inside cursor use dynamic SQL to
run ALTER DATABASE against each DB.
Another way for example to run a script
that will generate another SQL script with names of DBs in there
Something like
select 'ALTER DATABASE '+name+'...' from master..sysdatabases where ... --
make sure you will exclude system databases fom there
Then execute result script to expand databases.
This way is good as a one time deal.
Regards.
"Tim" wrote:
> I support a server that has over 200 databases on 1 instance. Each database
> is about 2 megs. Does anyone know how I can automate a script that will
> increase the size to 500 megs for ALL DBs. I want to run this script during
> the evening hours.
> Ishu
>
>
Wednesday, March 7, 2012
expanding table with additional column with sequential runs
My current table is a list of names, and a list of associated
lifespans(in months
I would like to add a column (months) that goes from 0 to lifespan.
What table looks like now:
Name Lifespan
a 5
b 3
c 9
...
What I want it to look like:
Name Lifespan Month
a 5 0
a 5 1
...
a 5 5
b 3 0
b 3 1
b 3 2
b 3 3
c 9 0
...
c 9 9
Any help would be greatly appreciated.I don't have sql server installed here to create the script for you but I
can explain what to do - you could populate a lookup table of integers which
has the same number as the max of the lifespan (use a simple while loop to
populate the table, starting at zero). Then do a crossjoin between your base
table and the new lookup table, based on a where condition of lifespan <=lookup table column and pull back the name, lifespan and lookupcolumn.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks, worked quite well.
-scott
Paul Ibison wrote:
> I don't have sql server installed here to create the script for you but I
> can explain what to do - you could populate a lookup table of integers which
> has the same number as the max of the lifespan (use a simple while loop to
> populate the table, starting at zero). Then do a crossjoin between your base
> table and the new lookup table, based on a where condition of lifespan <=> lookup table column and pull back the name, lifespan and lookupcolumn.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
expanding table with additional column with sequential runs
My current table is a list of names, and a list of associated
lifespans(in months
I would like to add a column (months) that goes from 0 to lifespan.
What table looks like now:
Name Lifespan
a 5
b 3
c 9
...
What I want it to look like:
Name Lifespan Month
a 5 0
a 5 1
...
a 5 5
b 3 0
b 3 1
b 3 2
b 3 3
c 9 0
...
c 9 9
Any help would be greatly appreciated.I don't have sql server installed here to create the script for you but I
can explain what to do - you could populate a lookup table of integers which
has the same number as the max of the lifespan (use a simple while loop to
populate the table, starting at zero). Then do a crossjoin between your base
table and the new lookup table, based on a where condition of lifespan <=
lookup table column and pull back the name, lifespan and lookupcolumn.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks, worked quite well.
-scott
Paul Ibison wrote:
> I don't have sql server installed here to create the script for you but I
> can explain what to do - you could populate a lookup table of integers whi
ch
> has the same number as the max of the lifespan (use a simple while loop to
> populate the table, starting at zero). Then do a crossjoin between your ba
se
> table and the new lookup table, based on a where condition of lifespan <=
> lookup table column and pull back the name, lifespan and lookupcolumn.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
Sunday, February 26, 2012
EXIT Installation Scripts that have multiple GO statements
I'm Scripting various Upgrade scripts that set up roles / users / create databases etc and want to Exit the Script unconditionally if for example the user already exists etc.
I can't Use RETURN because it just jumps to the Code following the next GO statement
There is also some USE [Database] Statements
Is there a way to do this or am I missing something fundamental here ?
GWIs there a way to do this or am I missing something fundamental here ?No.
-PatP|||I think conditional statements are the way to go...
If the username exists, do nothing, else do something...
OR
If the username doesn't exist, do something.|||What if he dumped the monolithic type script and removed the batch separators instead calling a series of individual files in series? Sean posted a script to do this recently and I have a similar one but using sqlcmd.
George - I think the problem is that batch separators are required as otherwise the deferred name resolution causes the script to fail as dependant objects do not exist at compile time. Also, some objects (e.g. functions & procs) can be the only statements in a batch.|||this can be done BUT the script must always be run by either SQLCMD or maybe OSQL. Here is a sampling of the file that you would use;
select count(*)
from sys.databases
go
if exists (select * from sys.tables)
begin
exit
end
go
select count(*)
from sys.databases
go
This will always generate an error in Query Analyzer, but SQLCMD will exit as you desire.
EDIT: Note; when you test this script, you only get the results of the first query. The second query is ignored.|||Folks
Thanks for all your input & Apologies for not getting back to you sooner.
In this instance the new system is still in Dev & I'm releasing full versions into UAT / Testing environments (which comprise 2 new interface databases, Linked Servers & multiple security accounts setup) - hence the monolothic script.
I think I'll have to accept Pat's Answer.
Thanks again
GW
exit from script
I need to exit from a script after checking some conditions . for example I need to check if a store procedure does not exist make it other wise go exit . I try following code but became an error
if exists(select ..... )
create store procedure ...
<<error becomes>>
or
if exists(select .... ) return
go
create store procedure .....
go
<<both command runs>>
Moved to T-SQL forum.
|||A good way to check before creation is something like this: Note: RETURN in not required.
The CREATE PROCEDURE code will execute after the PROCEDURE has been deleted.
Code Snippet
IF EXISTS
( SELECT name
FROM sys.Procedures
WHERE name = 'MyProcedureName'
)
DROP PROCEDURE dbo.MyProcedureName
GO
CREATE PROCEDURE dbo.MyProcedureName
( @.Parameters etc )
AS
...
GO
Unfortunately you can't do it here.Create procedure script should not be batched on IF statement.
As per the specification Create Procedure/Function/Trigger/View should be the first statement of your query
The best approach is,
Code Snippet
IF EXISTS (Procedure)
Drop Procedure ...
go
Create Proc ....
|||
If you use GO on your batch you can't stop it. You can only return / stop the current query (which is enclosed by GO).. When you return or use goto, it will automatically fetch the next script..
So always check the object is available, if available drop it & recreate it.
Wednesday, February 15, 2012
Execution of script at login
additional security checks after authentication?
Thanks,
James BrakeNot automatically. Why not call the script in the client code that
opens the connection?
Could you explain what additional checks you would require at
connection time. If the user isn't authorized then why would give them
a login to the database?
--
David Portas
SQL Server MVP
--|||Thanks for your reply.
If I had access to client code, I'd probably use an application role.
But, I do not.
The application gives each user read/write access to all tables
regardless of application security.
I was thinking of checking at login time whether the accessed the
database from the application or not.
James|||Hi
If you write anything such as a audit record when they log in, you could use
a trigger to raiserror. It would not be a very elegant solution though!
John
"James" <jbrake@.aeci.org> wrote in message
news:1103312862.596986.208700@.z14g2000cwz.googlegr oups.com...
> Thanks for your reply.
> If I had access to client code, I'd probably use an application role.
> But, I do not.
> The application gives each user read/write access to all tables
> regardless of application security.
> I was thinking of checking at login time whether the accessed the
> database from the application or not.
> James|||James (jbrake@.aeci.org) writes:
> If I had access to client code, I'd probably use an application role.
> But, I do not.
> The application gives each user read/write access to all tables
> regardless of application security.
> I was thinking of checking at login time whether the accessed the
> database from the application or not.
This may be a case of just poor choice of words from your side, but permit
be to point out that the application cannot really give access to the
tables, unless it is logging in with a user that has such privileges.
But maybe you mean that the application requires the user to have
read/write access to the tables, because it is not using stored procedures?
In such case it may be a difficult task to handle. To prevent updates
you could add triggers on the tables that checks app_name() and rolls
back if the application is not the right one.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp