Database Unit Testing: script

Showing posts with label script. Show all posts

Thursday, March 29, 2012

Database space monitoring.

Hi
can any one please help me or guide to some good article to create a script
that has to look into the data file space and log file space for each
database on sql server and if the database id running out of space then it
should automatically increase the log file and data file size.
Thanks in Advance.
Ritesh KumarRitesh
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
If the database is running out of space that is too late. You may want to
create/modify a size of database to be increased that prevents from
auto-grow feature
Actually an idea is to compare sysfiles system table data for time to time.
I'm sure you will find on internet many examples.
"Ritesh Kumar" <mailrembersu@.gmail.com> wrote in message
news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hi
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
> Thanks in Advance.
> Ritesh Kumar
>|||On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ritesh
> > can any one please help me or guide to some good article to create a
> > script that has to look into the data file space and log file space for
> > each database on sql server and if the database id running out of space
> > then it should automatically increase the log file and data file size.
> If the database is running out of space that is too late. You may want to
> create/modify a size of database to be increased that prevents from
> auto-grow feature
> Actually an idea is to compare sysfiles system table data for time to time.
> I'm sure you will find on internet many examples.
> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>
> > Hi
> > can any one please help me or guide to some good article to create a
> > script that has to look into the data file space and log file space for
> > each database on sql server and if the database id running out of space
> > then it should automatically increase the log file and data file size.
> > Thanks in Advance.
> > Ritesh Kumar- Hide quoted text -
> - Show quoted text -
This stored procedure (2005) will give you the free space on all
drives in your server:
exec sys.xp_fixeddrives
This stored procedure will give you the database size and other useful
info.
exec sp_spaceused
Maybe this will help?
Kristina|||Kristina
We should run this sp with the below parameter,because of results that we
get from sp are not always accurate
sp_spaceused @.updateusage = 'TRUE'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
> On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Ritesh
>> > can any one please help me or guide to some good article to create a
>> > script that has to look into the data file space and log file space
>> > for
>> > each database on sql server and if the database id running out of
>> > space
>> > then it should automatically increase the log file and data file size.
>> If the database is running out of space that is too late. You may want to
>> create/modify a size of database to be increased that prevents from
>> auto-grow feature
>> Actually an idea is to compare sysfiles system table data for time to
>> time.
>> I'm sure you will find on internet many examples.
>> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
>> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>>
>> > Hi
>> > can any one please help me or guide to some good article to create a
>> > script that has to look into the data file space and log file space
>> > for
>> > each database on sql server and if the database id running out of
>> > space
>> > then it should automatically increase the log file and data file size.
>> > Thanks in Advance.
>> > Ritesh Kumar- Hide quoted text -
>> - Show quoted text -
> This stored procedure (2005) will give you the free space on all
> drives in your server:
> exec sys.xp_fixeddrives
> This stored procedure will give you the database size and other useful
> info.
> exec sp_spaceused
> Maybe this will help?
> Kristina
>|||On Mar 28, 8:09 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina
> We should run this sp with the below parameter,because of results that we
> get from sp are not always accurate
> sp_spaceused @.updateusage = 'TRUE'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
>
> > On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Ritesh
> >> > can any one please help me or guide to some good article to create a
> >> > script that has to look into the data file space and log file space
> >> > for
> >> > each database on sql server and if the database id running out of
> >> > space
> >> > then it should automatically increase the log file and data file size.
> >> If the database is running out of space that is too late. You may want to
> >> create/modify a size of database to be increased that prevents from
> >> auto-grow feature
> >> Actually an idea is to compare sysfiles system table data for time to
> >> time.
> >> I'm sure you will find on internet many examples.
> >> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> >>news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> >> > Hi
> >> > can any one please help me or guide to some good article to create a
> >> > script that has to look into the data file space and log file space
> >> > for
> >> > each database on sql server and if the database id running out of
> >> > space
> >> > then it should automatically increase the log file and data file size.
> >> > Thanks in Advance.
> >> > Ritesh Kumar- Hide quoted text -
> >> - Show quoted text -
> > This stored procedure (2005) will give you the free space on all
> > drives in your server:
> > exec sys.xp_fixeddrives
> > This stored procedure will give you the database size and other useful
> > info.
> > exec sp_spaceused
> > Maybe this will help?
> > Kristina- Hide quoted text -
> - Show quoted text -
good point! :)sql

Tuesday, March 27, 2012

DataBase Space

Hello, All!
I'm creating a script to collect DataBase Space used and DataBase space Total
and after that I will store this information in a table in my SQL Server.
I've tryed using sp_spaceused but I don't know how to use the result of this
StoreProcedure.
If anyone know how to get this information using either sp_spaceused results
or t-sql script example, it would be great for me.
Thanks
Juliano Horta
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1
> I'm creating a script to collect DataBase Space used and DataBase space
> Total
> and after that I will store this information in a table in my SQL Server.
> I've tryed using sp_spaceused but I don't know how to use the result of
> this
> StoreProcedure.
> If anyone know how to get this information using either sp_spaceused
> results
> or t-sql script example, it would be great for me.
Why don't you look at the source code for sp_spaceused and adapt it for your
own needs?
|||Use the undocumented command DBCC ShowFileStats. It supports the WITH
TABLERESULTS option so you can dump the data into a table and work with it.
This is what Enterprise Mangler uses to populate the file used graphs on the
Taskpad pane.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Juliano H via droptable.com" <u13014@.uwe> wrote in message
news:5b3d9946b1a22@.uwe...
> Hello, All!
> I'm creating a script to collect DataBase Space used and DataBase space
> Total
> and after that I will store this information in a table in my SQL Server.
> I've tryed using sp_spaceused but I don't know how to use the result of
> this
> StoreProcedure.
> If anyone know how to get this information using either sp_spaceused
> results
> or t-sql script example, it would be great for me.
> Thanks
> Juliano Horta
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200602/1
|||Hi,
Try this to see whats in sp_spaceused...
sp_helptext 'sp_spaceused'
Thanks,
Sree
"Geoff N. Hiten" wrote:

> Use the undocumented command DBCC ShowFileStats. It supports the WITH
> TABLERESULTS option so you can dump the data into a table and work with it.
> This is what Enterprise Mangler uses to populate the file used graphs on the
> Taskpad pane.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Juliano H via droptable.com" <u13014@.uwe> wrote in message
> news:5b3d9946b1a22@.uwe...
>
>

DataBase Space

Hello, All!
I'm creating a script to collect DataBase Space used and DataBase space Total
and after that I will store this information in a table in my SQL Server.
I've tryed using sp_spaceused but I don't know how to use the result of this
StoreProcedure.
If anyone know how to get this information using either sp_spaceused results
or t-sql script example, it would be great for me.
Thanks
Juliano Horta
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1> I'm creating a script to collect DataBase Space used and DataBase space
> Total
> and after that I will store this information in a table in my SQL Server.
> I've tryed using sp_spaceused but I don't know how to use the result of
> this
> StoreProcedure.
> If anyone know how to get this information using either sp_spaceused
> results
> or t-sql script example, it would be great for me.
Why don't you look at the source code for sp_spaceused and adapt it for your
own needs?|||Use the undocumented command DBCC ShowFileStats. It supports the WITH
TABLERESULTS option so you can dump the data into a table and work with it.
This is what Enterprise Mangler uses to populate the file used graphs on the
Taskpad pane.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Juliano H via SQLMonster.com" <u13014@.uwe> wrote in message
news:5b3d9946b1a22@.uwe...
> Hello, All!
> I'm creating a script to collect DataBase Space used and DataBase space
> Total
> and after that I will store this information in a table in my SQL Server.
> I've tryed using sp_spaceused but I don't know how to use the result of
> this
> StoreProcedure.
> If anyone know how to get this information using either sp_spaceused
> results
> or t-sql script example, it would be great for me.
> Thanks
> Juliano Horta
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1|||Hi,
Try this to see whats in sp_spaceused...
sp_helptext 'sp_spaceused'
Thanks,
Sree
"Geoff N. Hiten" wrote:
> Use the undocumented command DBCC ShowFileStats. It supports the WITH
> TABLERESULTS option so you can dump the data into a table and work with it.
> This is what Enterprise Mangler uses to populate the file used graphs on the
> Taskpad pane.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Juliano H via SQLMonster.com" <u13014@.uwe> wrote in message
> news:5b3d9946b1a22@.uwe...
> > Hello, All!
> >
> > I'm creating a script to collect DataBase Space used and DataBase space
> > Total
> > and after that I will store this information in a table in my SQL Server.
> > I've tryed using sp_spaceused but I don't know how to use the result of
> > this
> > StoreProcedure.
> > If anyone know how to get this information using either sp_spaceused
> > results
> > or t-sql script example, it would be great for me.
> >
> > Thanks
> >
> > Juliano Horta
> >
> > --
> > Message posted via SQLMonster.com
> > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1
>
>

Wednesday, March 21, 2012

Database setup script

I have MSDE installed on my machine as was wondering what the NETSDK is in the following commands.

@.rem Uncomment the following line for MSDE
@.rem set DBNAME=(local)\NETSDK
set DBNAME=(local)\NETSDK

Thanks,
Bob HIt would appear that NETSDK is the "instance name" of the MSDE installation.

With SQL Server 2000 (and MSDE), multiple instances of SQL Server can be installed. The first uses the "default instance"; that is, to access it, simply reference the name of the server in a connection string. To reference a non-default instance, follow the server with \<instance name>sql

Monday, March 19, 2012

Database script to alarm

Hi,
Somebody has some script to make this requirement?
Databases
When the space that the file of any one of the databases used for the
Biztalk to reach
a quota of occupation in superior record 80% of its maximum size.
FufillyHi
You can do this in many ways such as tools such using Perfmon or Microsoft
Operations Manager (MOM).
Looking at this in a different perspective you could monitor the amount of
free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
job which you force to fails and so alerts an operator. The same technique
used in the stored procedure can be used with sp_helpfile if you want actual
size.
John
"JosÃ© JÃºlio Duarte" wrote:
> Hi,
> Somebody has some script to make this requirement?
> Databases
> When the space that the file of any one of the databases used for the
> Biztalk to reach
> a quota of occupation in superior record 80% of its maximum size.
> Fufilly|||Hello John Bell
I need this information by Database?
Fullfilly
JosÃ© JÃºlio Duarte
"John Bell" wrote:
> Hi
> You can do this in many ways such as tools such using Perfmon or Microsoft
> Operations Manager (MOM).
> Looking at this in a different perspective you could monitor the amount of
> free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
> use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
> job which you force to fails and so alerts an operator. The same technique
> used in the stored procedure can be used with sp_helpfile if you want actual
> size.
> John
> "JosÃ© JÃºlio Duarte" wrote:
> > Hi,
> >
> > Somebody has some script to make this requirement?
> >
> > Databases
> >
> > When the space that the file of any one of the databases used for the
> > Biztalk to reach
> > a quota of occupation in superior record 80% of its maximum size.
> >
> > Fufilly|||Hi
If you are doing this per database you may to try something like this to the
the file sizes:
SET NOCOUNT ON
CREATE TABLE #filesizes ( dbname sysname, [filename] sysname, [sizeKB] int,
type char(4) )
DECLARE @.name sysname
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master..sysdatabases
ORDER BY dbid
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #filesizes ( dbname, [filename], [sizeKB], [type] )
EXEC ( 'SELECT ''' + @.name + ''', name, size*8, CASE WHEN status&0x40=0x40
THEN ''Log'' ELSE ''Data'' END FROM ['+ @.name + ']..sysfiles' )
FETCH NEXT FROM db_cursor INTO @.name
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT * FROM #filesizes
DROP TABLE #filesizes
GO
Another alternative is to set the Alert in the Alerts section found in under
SQL Server Agent, you can set this for each data and log file for each
database using the type: SQL Server Performance Condition Object:
SQLServer:Databases Counter:
Data File(s) Size(KB) or Log File(s) Size(KB) and the instance is each
database.
John
"JosÃ© JÃºlio Duarte" wrote:
> Hello John Bell
> I need this information by Database?
> Fullfilly
> JosÃ© JÃºlio Duarte
> "John Bell" wrote:
> > Hi
> >
> > You can do this in many ways such as tools such using Perfmon or Microsoft
> > Operations Manager (MOM).
> >
> > Looking at this in a different perspective you could monitor the amount of
> > free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
> > use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
> > job which you force to fails and so alerts an operator. The same technique
> > used in the stored procedure can be used with sp_helpfile if you want actual
> > size.
> >
> > John
> >
> > "JosÃ© JÃºlio Duarte" wrote:
> >
> > > Hi,
> > >
> > > Somebody has some script to make this requirement?
> > >
> > > Databases
> > >
> > > When the space that the file of any one of the databases used for the
> > > Biztalk to reach
> > > a quota of occupation in superior record 80% of its maximum size.
> > >
> > > Fufilly|||Hello John Bell
Like the first script i need all information about all database.
Fullfilly
JosÃ© JÃºlio Duarte
"John Bell" wrote:
> Hi
> If you are doing this per database you may to try something like this to the
> the file sizes:
> SET NOCOUNT ON
> CREATE TABLE #filesizes ( dbname sysname, [filename] sysname, [sizeKB] int,
> type char(4) )
> DECLARE @.name sysname
> DECLARE db_cursor CURSOR FOR
> SELECT name
> FROM master..sysdatabases
> ORDER BY dbid
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> INSERT INTO #filesizes ( dbname, [filename], [sizeKB], [type] )
> EXEC ( 'SELECT ''' + @.name + ''', name, size*8, CASE WHEN status&0x40=0x40
> THEN ''Log'' ELSE ''Data'' END FROM ['+ @.name + ']..sysfiles' )
> FETCH NEXT FROM db_cursor INTO @.name
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
> SELECT * FROM #filesizes
> DROP TABLE #filesizes
> GO
> Another alternative is to set the Alert in the Alerts section found in under
> SQL Server Agent, you can set this for each data and log file for each
> database using the type: SQL Server Performance Condition Object:
> SQLServer:Databases Counter:
> Data File(s) Size(KB) or Log File(s) Size(KB) and the instance is each
> database.
> John
> "JosÃ© JÃºlio Duarte" wrote:
> > Hello John Bell
> >
> > I need this information by Database?
> >
> > Fullfilly
> >
> > JosÃ© JÃºlio Duarte
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You can do this in many ways such as tools such using Perfmon or Microsoft
> > > Operations Manager (MOM).
> > >
> > > Looking at this in a different perspective you could monitor the amount of
> > > free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
> > > use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
> > > job which you force to fails and so alerts an operator. The same technique
> > > used in the stored procedure can be used with sp_helpfile if you want actual
> > > size.
> > >
> > > John
> > >
> > > "JosÃ© JÃºlio Duarte" wrote:
> > >
> > > > Hi,
> > > >
> > > > Somebody has some script to make this requirement?
> > > >
> > > > Databases
> > > >
> > > > When the space that the file of any one of the databases used for the
> > > > Biztalk to reach
> > > > a quota of occupation in superior record 80% of its maximum size.
> > > >
> > > > Fufilly|||Hi
Run the script and you will see that it will produce information for each
file on every database.
John
"JosÃ© JÃºlio Duarte" wrote:
> Hello John Bell
> Like the first script i need all information about all database.
> Fullfilly
> JosÃ© JÃºlio Duarte
> "John Bell" wrote:
> > Hi
> >
> > If you are doing this per database you may to try something like this to the
> > the file sizes:
> > SET NOCOUNT ON
> >
> > CREATE TABLE #filesizes ( dbname sysname, [filename] sysname, [sizeKB] int,
> > type char(4) )
> >
> > DECLARE @.name sysname
> > DECLARE db_cursor CURSOR FOR
> > SELECT name
> > FROM master..sysdatabases
> > ORDER BY dbid
> >
> > OPEN db_cursor
> >
> > FETCH NEXT FROM db_cursor INTO @.name
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > INSERT INTO #filesizes ( dbname, [filename], [sizeKB], [type] )
> > EXEC ( 'SELECT ''' + @.name + ''', name, size*8, CASE WHEN status&0x40=0x40
> > THEN ''Log'' ELSE ''Data'' END FROM ['+ @.name + ']..sysfiles' )
> > FETCH NEXT FROM db_cursor INTO @.name
> > END
> > CLOSE db_cursor
> > DEALLOCATE db_cursor
> >
> > SELECT * FROM #filesizes
> >
> > DROP TABLE #filesizes
> > GO
> >
> > Another alternative is to set the Alert in the Alerts section found in under
> > SQL Server Agent, you can set this for each data and log file for each
> > database using the type: SQL Server Performance Condition Object:
> > SQLServer:Databases Counter:
> > Data File(s) Size(KB) or Log File(s) Size(KB) and the instance is each
> > database.
> >
> > John
> >
> > "JosÃ© JÃºlio Duarte" wrote:
> >
> > > Hello John Bell
> > >
> > > I need this information by Database?
> > >
> > > Fullfilly
> > >
> > > JosÃ© JÃºlio Duarte
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You can do this in many ways such as tools such using Perfmon or Microsoft
> > > > Operations Manager (MOM).
> > > >
> > > > Looking at this in a different perspective you could monitor the amount of
> > > > free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
> > > > use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
> > > > job which you force to fails and so alerts an operator. The same technique
> > > > used in the stored procedure can be used with sp_helpfile if you want actual
> > > > size.
> > > >
> > > > John
> > > >
> > > > "JosÃ© JÃºlio Duarte" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Somebody has some script to make this requirement?
> > > > >
> > > > > Databases
> > > > >
> > > > > When the space that the file of any one of the databases used for the
> > > > > Biztalk to reach
> > > > > a quota of occupation in superior record 80% of its maximum size.
> > > > >
> > > > > Fufilly|||Hello Jonh Bell
but this donÂ´t say when the database is 80% full
Result of Exemple script:
dbname filename sizeKB type
Northwind Northwind 3712 Data
Northwind Northwind_log 1280 Log
This is a Cluster SQl Active/Active and another thing this integrate to
alarm with HP Open View.
Regards
JosÃ© JÃºlio duarte
"John Bell" wrote:
> Hi
> Run the script and you will see that it will produce information for each
> file on every database.
> John
> "JosÃ© JÃºlio Duarte" wrote:
> > Hello John Bell
> >
> > Like the first script i need all information about all database.
> >
> > Fullfilly
> >
> > JosÃ© JÃºlio Duarte
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > If you are doing this per database you may to try something like this to the
> > > the file sizes:
> > > SET NOCOUNT ON
> > >
> > > CREATE TABLE #filesizes ( dbname sysname, [filename] sysname, [sizeKB] int,
> > > type char(4) )
> > >
> > > DECLARE @.name sysname
> > > DECLARE db_cursor CURSOR FOR
> > > SELECT name
> > > FROM master..sysdatabases
> > > ORDER BY dbid
> > >
> > > OPEN db_cursor
> > >
> > > FETCH NEXT FROM db_cursor INTO @.name
> > >
> > > WHILE @.@.FETCH_STATUS = 0
> > > BEGIN
> > > INSERT INTO #filesizes ( dbname, [filename], [sizeKB], [type] )
> > > EXEC ( 'SELECT ''' + @.name + ''', name, size*8, CASE WHEN status&0x40=0x40
> > > THEN ''Log'' ELSE ''Data'' END FROM ['+ @.name + ']..sysfiles' )
> > > FETCH NEXT FROM db_cursor INTO @.name
> > > END
> > > CLOSE db_cursor
> > > DEALLOCATE db_cursor
> > >
> > > SELECT * FROM #filesizes
> > >
> > > DROP TABLE #filesizes
> > > GO
> > >
> > > Another alternative is to set the Alert in the Alerts section found in under
> > > SQL Server Agent, you can set this for each data and log file for each
> > > database using the type: SQL Server Performance Condition Object:
> > > SQLServer:Databases Counter:
> > > Data File(s) Size(KB) or Log File(s) Size(KB) and the instance is each
> > > database.
> > >
> > > John
> > >
> > > "JosÃ© JÃºlio Duarte" wrote:
> > >
> > > > Hello John Bell
> > > >
> > > > I need this information by Database?
> > > >
> > > > Fullfilly
> > > >
> > > > JosÃ© JÃºlio Duarte
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > You can do this in many ways such as tools such using Perfmon or Microsoft
> > > > > Operations Manager (MOM).
> > > > >
> > > > > Looking at this in a different perspective you could monitor the amount of
> > > > > free space on the disc http://www.sqldbatips.com/showcode.asp?ID=4 and then
> > > > > use SQLmail, XPSMTP or NET SEND to send an alert to an operator, or set up a
> > > > > job which you force to fails and so alerts an operator. The same technique
> > > > > used in the stored procedure can be used with sp_helpfile if you want actual
> > > > > size.
> > > > >
> > > > > John
> > > > >
> > > > > "JosÃ© JÃºlio Duarte" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > Somebody has some script to make this requirement?
> > > > > >
> > > > > > Databases
> > > > > >
> > > > > > When the space that the file of any one of the databases used for the
> > > > > > Biztalk to reach
> > > > > > a quota of occupation in superior record 80% of its maximum size.
> > > > > >
> > > > > > Fufilly

database script error

I just upgraded from sql7.0 to sql2000.
everything seemed to be fine.
but when I click on master database it shows the following
error;
Internet Explorer error
line: 307
char: 2
error: unspecified error
code: 0
url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
\Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
and asks me if I wish to conntinue running scripts.
if I create a new database and try to view it, it works
fine, so is this a problem because of the upgrade.
I'm using Internet Explorer 5.5
thanks in advance,
DarrinDarrin,
Had that problem sometime back.Dont know what fixed it - IE version or some
SQL Server service pack.Anyways, here is a workaround - try changing the
view to something other than 'taskpad' temporarily and then return back.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Darrin" <darrin.adams@.eei.ericsson.se> wrote in message
news:0ba001c35062$d936cc10$a501280a@.phx.gbl...
> I just upgraded from sql7.0 to sql2000.
> everything seemed to be fine.
> but when I click on master database it shows the following
> error;
> Internet Explorer error
> line: 307
> char: 2
> error: unspecified error
> code: 0
> url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
> \Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
> and asks me if I wish to conntinue running scripts.
>
> if I create a new database and try to view it, it works
> fine, so is this a problem because of the upgrade.
> I'm using Internet Explorer 5.5
> thanks in advance,
> Darrin

Database Script Does Not Account for Dependencies

I don't know if I have missed something here, but I blindly thought that if
I used SSMS to script my database, I could run the script on a new instance
and get a database out of it.
The script fails, and I get an error saying that a stored procedure requires
an object that doesn't exist. That is because the object hasn't been created
yet. It appears later on in the script.
How can I get the database scripting wizard to script objects in the correct
order, that is in an order that takes account of dependencies?
Either that, or does anyone know of a tool/product that does do this?
TIA
CharlesUsually, I put my scripts together manually, because this issue troubled me
and I never got around to trying to find a solution for it. I did try
checking off "Generate scripts for all dependent objects" and that did not
help at all, although it did change the order of the script, just in useless
ways.
If you do find an answer, please let us all know.
"Charles Law" <blank@.nowhere.com> wrote in message
news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>I don't know if I have missed something here, but I blindly thought that if
>I used SSMS to script my database, I could run the script on a new instance
>and get a database out of it.
> The script fails, and I get an error saying that a stored procedure
> requires an object that doesn't exist. That is because the object hasn't
> been created yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>|||Hi Jim
Will do :-)
Charles
"Jim Underwood" <james.underwood_nospam@.fallonclinic.org> wrote in message
news:eEWo$v3BIHA.5868@.TK2MSFTNGP05.phx.gbl...
> Usually, I put my scripts together manually, because this issue troubled
> me and I never got around to trying to find a solution for it. I did try
> checking off "Generate scripts for all dependent objects" and that did not
> help at all, although it did change the order of the script, just in
> useless ways.
> If you do find an answer, please let us all know.
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>>I don't know if I have missed something here, but I blindly thought that
>>if I used SSMS to script my database, I could run the script on a new
>>instance and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires an object that doesn't exist. That is because the object hasn't
>> been created yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>
>|||http://www.red-gate.com/products/sql_packager/index.htm?gclid=CO-Gz5nn-I4CFQGnPAodaUROEw
I never used it myself. If you google there are more (don't miss the
sponsored links).
hth
Quentin
"Charles Law" <blank@.nowhere.com> wrote in message
news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>I don't know if I have missed something here, but I blindly thought that if
>I used SSMS to script my database, I could run the script on a new instance
>and get a database out of it.
> The script fails, and I get an error saying that a stored procedure
> requires an object that doesn't exist. That is because the object hasn't
> been created yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>|||I agree with Jim; do it manually. SQL Server scripting is nice *sometimes*
most of times it causes more problems. Besides nothing like doing it
yourself ;-) more fun, and you get to make sure everything is named how you
like it. Instead of using SQL naming convention for keys and such.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Charles Law" wrote:
> I don't know if I have missed something here, but I blindly thought that if
> I used SSMS to script my database, I could run the script on a new instance
> and get a database out of it.
> The script fails, and I get an error saying that a stored procedure requires
> an object that doesn't exist. That is because the object hasn't been created
> yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the correct
> order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>
>|||On Oct 5, 12:55 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> I don't know if I have missed something here, but I blindly thought that if
> I used SSMS to script my database, I could run the script on a new instance
> and get a database out of it.
> The script fails, and I get an error saying that a stored procedure requires
> an object that doesn't exist. That is because the object hasn't been created
> yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the correct
> order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
I regularly use Red Gate's SQL Compare for such tasks.|||Charles Law wrote:
> [...]
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
You can use the Database Publishing Wizard from
http://www.codeplex.com/sqlhost. It's made by Microsoft, it's free and
it takes care of the dependencies quite good (at least on SQL 2005).
--
Razvan Socol
SQL Server MVP|||Hi Quentin
Thanks for the suggestion. I will take a look.
Charles
"Quentin Ran" <remove_qran2@.yahoo.com> wrote in message
news:uYe4x$5BIHA.4836@.TK2MSFTNGP06.phx.gbl...
> http://www.red-gate.com/products/sql_packager/index.htm?gclid=CO-Gz5nn-I4CFQGnPAodaUROEw
> I never used it myself. If you google there are more (don't miss the
> sponsored links).
> hth
> Quentin
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>>I don't know if I have missed something here, but I blindly thought that
>>if I used SSMS to script my database, I could run the script on a new
>>instance and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires an object that doesn't exist. That is because the object hasn't
>> been created yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>
>|||Hi Mohit
Maintaining a script manually is really not an option. There are too many
objects to control in that way. Perhaps if SQL Server objects could be
properly source controlled then it might be an option, but not at the
moment.
Thanks.
Charles
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:AFF81E72-8292-462D-89FA-667E4CBDDB2B@.microsoft.com...
>I agree with Jim; do it manually. SQL Server scripting is nice *sometimes*
> most of times it causes more problems. Besides nothing like doing it
> yourself ;-) more fun, and you get to make sure everything is named how
> you
> like it. Instead of using SQL naming convention for keys and such.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Charles Law" wrote:
>> I don't know if I have missed something here, but I blindly thought that
>> if
>> I used SSMS to script my database, I could run the script on a new
>> instance
>> and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires
>> an object that doesn't exist. That is because the object hasn't been
>> created
>> yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct
>> order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>|||Hi Alex
I've just downloaded the toolkit trial which contains several utilities, so
I'll have a look at it now.
Cheers.
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:1191640297.834009.175120@.d55g2000hsg.googlegroups.com...
> On Oct 5, 12:55 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> I don't know if I have missed something here, but I blindly thought that
>> if
>> I used SSMS to script my database, I could run the script on a new
>> instance
>> and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires
>> an object that doesn't exist. That is because the object hasn't been
>> created
>> yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct
>> order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
> I regularly use Red Gate's SQL Compare for such tasks.
>|||Hi Razvan
Thanks for the reply.
I have downloaded the wizard and run it. It looks identical to the scripting
wizard built into SS Management Studio. Do you know if it behaves
differently from the built-in version?
Charles
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:uI$0Lq9BIHA.4584@.TK2MSFTNGP06.phx.gbl...
> Charles Law wrote:
>> [...]
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
> You can use the Database Publishing Wizard from
> http://www.codeplex.com/sqlhost. It's made by Microsoft, it's free and
> it takes care of the dependencies quite good (at least on SQL 2005).
> --
> Razvan Socol
> SQL Server MVP|||Charles Law (blank@.nowhere.com) writes:
> Maintaining a script manually is really not an option. There are too many
> objects to control in that way. Perhaps if SQL Server objects could be
> properly source controlled then it might be an option, but not at the
> moment.
It isn't what? We keep all our SQL Server objects under version control
and have done so for many years. There's nothing special with SQL objects.
Source code is source code. Just do it.
The database on the other hand, is just a place were you put your
binaries. (Nevermind that the binaries in this case have a very strong
resemblence to the source code. It's nevertheless to be regarded as
binaries.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland
What do you use as your version control repository? The problem we get is
when someone makes a change to a database object, in the database, this is
not tracked and controlled. Is there a way of 'locking' objects until they
are checked out to the database, where developers can use SSMS to manage
their tables, views and stored procedures?
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C1EF8DD5814Yazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> Maintaining a script manually is really not an option. There are too many
>> objects to control in that way. Perhaps if SQL Server objects could be
>> properly source controlled then it might be an option, but not at the
>> moment.
> It isn't what? We keep all our SQL Server objects under version control
> and have done so for many years. There's nothing special with SQL objects.
> Source code is source code. Just do it.
> The database on the other hand, is just a place were you put your
> binaries. (Nevermind that the binaries in this case have a very strong
> resemblence to the source code. It's nevertheless to be regarded as
> binaries.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Charles Law (blank@.nowhere.com) writes:
> What do you use as your version control repository? The problem we get is
> when someone makes a change to a database object, in the database, this is
> not tracked and controlled. Is there a way of 'locking' objects until they
> are checked out to the database, where developers can use SSMS to manage
> their tables, views and stored procedures?
We use Visual SourceSafe, although we really grew out of it long ago.
Database objects is not any different from any other type of objects.
You check it out when you work with it, you check it in when you are done.
If it is not checked in, it does not exist, it's as simlpe as that.
Someone might be changing things in a development database without checking
things out, but that is completely irrelevant. If it is not checked in,
it does not exist.
And, yes, someone could change a stored procedure in the production
environment without going the SourceSafe route. Just as someone could
change a C++ file, compile a DLL and install it in production without
going through SourceSafe.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland
I suppose the problem I have is that with database objects there is an
intermediate step/object, i.e. the script.
If I were in VS, I could select my objects, add them to VSS, and check them
in and out. In SQL Server, I have to create an intermediate object - the
script file - and put that into VSS. There is no direct control over the
object in the database. So, for example, there is no way for one person to
tell if someone else is working on a SP just by looking in the database.
Perhaps that level of tight integration and control will come in a future
release, but for now, it is not really adequate for our needs.
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> What do you use as your version control repository? The problem we get is
>> when someone makes a change to a database object, in the database, this
>> is
>> not tracked and controlled. Is there a way of 'locking' objects until
>> they
>> are checked out to the database, where developers can use SSMS to manage
>> their tables, views and stored procedures?
> We use Visual SourceSafe, although we really grew out of it long ago.
> Database objects is not any different from any other type of objects.
> You check it out when you work with it, you check it in when you are done.
> If it is not checked in, it does not exist, it's as simlpe as that.
> Someone might be changing things in a development database without
> checking
> things out, but that is completely irrelevant. If it is not checked in,
> it does not exist.
> And, yes, someone could change a stored procedure in the production
> environment without going the SourceSafe route. Just as someone could
> change a C++ file, compile a DLL and install it in production without
> going through SourceSafe.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> If I were in VS, I could select my objects, add them to VSS, and check
> them in and out. In SQL Server, I have to create an intermediate object -
> the script file - and put that into VSS. There is no direct control over
> the object in the database. So, for example, there is no way for one
> person to tell if someone else is working on a SP just by looking in the
> database.
I suggest you change your mindset to view the VSS SQL Server object script
file as the master copy rather than an "intermediate" file. This way, you
need only look in source control to see who is working on an object and can
follow the same sort of practices as you do for application code.
We use VSS as our source control repository (although we've outgrown it like
Erland) and are looking at TFS source control. I've also used ClearCase and
MKS in past positions. I don't think the source control tool is nearly as
important as the processes surrounding it. Sound development and release
management practices are mandatory, especially for larger teams.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles Law" <blank@.nowhere.com> wrote in message
news:OWsJUDaCIHA.972@.TK2MSFTNGP05.phx.gbl...
> Hi Erland
> I suppose the problem I have is that with database objects there is an
> intermediate step/object, i.e. the script.
> If I were in VS, I could select my objects, add them to VSS, and check
> them in and out. In SQL Server, I have to create an intermediate object -
> the script file - and put that into VSS. There is no direct control over
> the object in the database. So, for example, there is no way for one
> person to tell if someone else is working on a SP just by looking in the
> database.
> Perhaps that level of tight integration and control will come in a future
> release, but for now, it is not really adequate for our needs.
> Charles
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
>> Charles Law (blank@.nowhere.com) writes:
>> What do you use as your version control repository? The problem we get
>> is
>> when someone makes a change to a database object, in the database, this
>> is
>> not tracked and controlled. Is there a way of 'locking' objects until
>> they
>> are checked out to the database, where developers can use SSMS to manage
>> their tables, views and stored procedures?
>> We use Visual SourceSafe, although we really grew out of it long ago.
>> Database objects is not any different from any other type of objects.
>> You check it out when you work with it, you check it in when you are
>> done.
>> If it is not checked in, it does not exist, it's as simlpe as that.
>> Someone might be changing things in a development database without
>> checking
>> things out, but that is completely irrelevant. If it is not checked in,
>> it does not exist.
>> And, yes, someone could change a stored procedure in the production
>> environment without going the SourceSafe route. Just as someone could
>> change a C++ file, compile a DLL and install it in production without
>> going through SourceSafe.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||> Sound development and release management practices are mandatory,
> especially for larger teams.
I agree entirely. However, the script _is_ an intermediate file because
developers do not work on it directly. They work in SSMS on its version of
the object. By the same token, developers do not _need_ to check out an
object to work on it, only when they want to check a change into VSS.
If this were C# source, then the developer would have to check out a file
before working on it in VS, because it is the same file.
Procedures are fine, and necessary, but the more they can be enforced the
better. It just seems like an omission, or a gap, in what should be a joined
up process, that SQL Server and VSS do not integrate to the same extent as
VS and VSS.
Charles
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:EA5D139B-B8F6-40AA-9765-44FC573C522B@.microsoft.com...
>> If I were in VS, I could select my objects, add them to VSS, and check
>> them in and out. In SQL Server, I have to create an intermediate object -
>> the script file - and put that into VSS. There is no direct control over
>> the object in the database. So, for example, there is no way for one
>> person to tell if someone else is working on a SP just by looking in the
>> database.
> I suggest you change your mindset to view the VSS SQL Server object script
> file as the master copy rather than an "intermediate" file. This way, you
> need only look in source control to see who is working on an object and
> can follow the same sort of practices as you do for application code.
> We use VSS as our source control repository (although we've outgrown it
> like Erland) and are looking at TFS source control. I've also used
> ClearCase and MKS in past positions. I don't think the source control
> tool is nearly as important as the processes surrounding it. Sound
> development and release management practices are mandatory, especially for
> larger teams.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:OWsJUDaCIHA.972@.TK2MSFTNGP05.phx.gbl...
>> Hi Erland
>> I suppose the problem I have is that with database objects there is an
>> intermediate step/object, i.e. the script.
>> If I were in VS, I could select my objects, add them to VSS, and check
>> them in and out. In SQL Server, I have to create an intermediate object -
>> the script file - and put that into VSS. There is no direct control over
>> the object in the database. So, for example, there is no way for one
>> person to tell if someone else is working on a SP just by looking in the
>> database.
>> Perhaps that level of tight integration and control will come in a future
>> release, but for now, it is not really adequate for our needs.
>> Charles
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
>> Charles Law (blank@.nowhere.com) writes:
>> What do you use as your version control repository? The problem we get
>> is
>> when someone makes a change to a database object, in the database, this
>> is
>> not tracked and controlled. Is there a way of 'locking' objects until
>> they
>> are checked out to the database, where developers can use SSMS to
>> manage
>> their tables, views and stored procedures?
>> We use Visual SourceSafe, although we really grew out of it long ago.
>> Database objects is not any different from any other type of objects.
>> You check it out when you work with it, you check it in when you are
>> done.
>> If it is not checked in, it does not exist, it's as simlpe as that.
>> Someone might be changing things in a development database without
>> checking
>> things out, but that is completely irrelevant. If it is not checked in,
>> it does not exist.
>> And, yes, someone could change a stored procedure in the production
>> environment without going the SourceSafe route. Just as someone could
>> change a C++ file, compile a DLL and install it in production without
>> going through SourceSafe.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>|||Charles Law (blank@.nowhere.com) writes:
> I agree entirely. However, the script _is_ an intermediate file because
> developers do not work on it directly. They work in SSMS on its version of
> the object. By the same token, developers do not _need_ to check out an
> object to work on it, only when they want to check a change into VSS.
> If this were C# source, then the developer would have to check out a file
> before working on it in VS, because it is the same file.
As Dan said, this is a question of mindset. There is no developer in my
shop that would understand what your talking about. For them, a stored
procedure is a file just like C# program is. Of course, it may help
that we don't use QA or SSMS to edit SQL code, but a third-party editor,
so there is still a clear notion of a compile step. But there is not
really any difference even if you work in SSMS. You have a file and
you edit it.
Yes, you can modify an object in the database without checking it out.
Just as you can modify a C# file without checking in out.
> Procedures are fine, and necessary, but the more they can be enforced
> the better. It just seems like an omission, or a gap, in what should be
> a joined up process, that SQL Server and VSS do not integrate to the
> same extent as VS and VSS.
Using the Source Control APIs it's possible to lock objects in SQL
Server with database triggers, so that any attempt to alter it is
met with "Check out first". But how do you know that the developer is
not modifying the object in another database?
By the way, it is possible to connect Mgmt Studio with at least SourceSafe.
(I'm uncertain about TFS). You still work with files, though. Which is
the way it should be. That's all what source code is about. Files.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Again, I'm not really disagreeing, but I see the database as the repository
for all these objects, where we are given convenient tools for editing and
maintaining the objects. We seem to be advancing in one way, having a GUI
that allows us to view and change these objects, but we still have to go
back to a script file if we want to make a permanent change. To me, it's a
bit like having Windows Explorer to look at the contents of a directory, but
opening a command prompt if we want to move a file.
Anyway, what I was really after was some means to simplify the task of
managing database objects, whether that be a database wizard or a scripting
tool, or something else, and something to make it easier to control changes.
Perhaps we will end up creating script files and manually manage a batch
file to run them in the correct order.
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C459CDD66EYazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> I agree entirely. However, the script _is_ an intermediate file because
>> developers do not work on it directly. They work in SSMS on its version
>> of
>> the object. By the same token, developers do not _need_ to check out an
>> object to work on it, only when they want to check a change into VSS.
>> If this were C# source, then the developer would have to check out a file
>> before working on it in VS, because it is the same file.
> As Dan said, this is a question of mindset. There is no developer in my
> shop that would understand what your talking about. For them, a stored
> procedure is a file just like C# program is. Of course, it may help
> that we don't use QA or SSMS to edit SQL code, but a third-party editor,
> so there is still a clear notion of a compile step. But there is not
> really any difference even if you work in SSMS. You have a file and
> you edit it.
> Yes, you can modify an object in the database without checking it out.
> Just as you can modify a C# file without checking in out.
>> Procedures are fine, and necessary, but the more they can be enforced
>> the better. It just seems like an omission, or a gap, in what should be
>> a joined up process, that SQL Server and VSS do not integrate to the
>> same extent as VS and VSS.
> Using the Source Control APIs it's possible to lock objects in SQL
> Server with database triggers, so that any attempt to alter it is
> met with "Check out first". But how do you know that the developer is
> not modifying the object in another database?
> By the way, it is possible to connect Mgmt Studio with at least
> SourceSafe.
> (I'm uncertain about TFS). You still work with files, though. Which is
> the way it should be. That's all what source code is about. Files.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Charles Law (blank@.nowhere.com) writes:
> Again, I'm not really disagreeing, but I see the database as the
> repository for all these objects,
And that is flat wrong in my opinion. In my shop there is no such things
as "the database". There is a unknown number of development and test
databases, all stemming from the same schema. In our case this is amplified
by the fact that our customers have so different configuration, that
you may need a particular configuration when you develop and test a certain
feature.
But even without that, developers may need their own sandboxes where they
can work without disturbing other developers. This does not at least
apply if needs to change tables.
I don't know if you have looked at Visual Studio Team System for
Database Developers, also known as DataDude. As I understand DataDude, you
work solely against a database on your local machine, you cannot work
against a central database.
> Anyway, what I was really after was some means to simplify the task of
> managing database objects, whether that be a database wizard or a
> scripting tool, or something else, and something to make it easier to
> control changes. Perhaps we will end up creating script files and
> manually manage a batch file to run them in the correct order.
Keep in mind that if you work from scripting the database, you probably
get a bunch of junk objects that should not be in the source control
system.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I'm starting to be persuaded. I've been looking at a silhouette of two
candlesticks, and suddenly I see two faces. I think you mentioned that you
use another tool to manage your scripts. What is that tool?
There is a problem that developers - well, the ones here - like the GUI,
draggy, droppy, clicky tools like SSMS, and given the choice between that
and a text editor to edit boring old scripts, you know which they will
choose.
Perhaps what I want, then, is something that manages the scripts (files) in
a visual way (like VS does with source code), and enables changes to be
deployed to the database of choice. Does that sound better?
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C4EFFCF5F73Yazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> Again, I'm not really disagreeing, but I see the database as the
>> repository for all these objects,
> And that is flat wrong in my opinion. In my shop there is no such things
> as "the database". There is a unknown number of development and test
> databases, all stemming from the same schema. In our case this is
> amplified
> by the fact that our customers have so different configuration, that
> you may need a particular configuration when you develop and test a
> certain
> feature.
> But even without that, developers may need their own sandboxes where they
> can work without disturbing other developers. This does not at least
> apply if needs to change tables.
> I don't know if you have looked at Visual Studio Team System for
> Database Developers, also known as DataDude. As I understand DataDude, you
> work solely against a database on your local machine, you cannot work
> against a central database.
>> Anyway, what I was really after was some means to simplify the task of
>> managing database objects, whether that be a database wizard or a
>> scripting tool, or something else, and something to make it easier to
>> control changes. Perhaps we will end up creating script files and
>> manually manage a batch file to run them in the correct order.
> Keep in mind that if you work from scripting the database, you probably
> get a bunch of junk objects that should not be in the source control
> system.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Charles Law (blank@.nowhere.com) writes:
> I'm starting to be persuaded. I've been looking at a silhouette of two
> candlesticks, and suddenly I see two faces. I think you mentioned that you
> use another tool to manage your scripts. What is that tool?
We have a toolset that I have developed over the years, and by now
they have achieved quite a level of complexity. Although they are fairly
streamlined to our needs, so about everyone else would probably find
them quirky. And if you are looking for drag and drop, just forget it -
it's all command-line. (And all Perl.)
Unfortunately this toolset is not available to the public. Maybe I will
be able to make it avilable in some form in the future.
But I can give you some of the essence of the toolset, as it may give
you inspiration to devise your own; of course you would start off with
something very simple.
The core is a file-loading module that reads an SQL file and performs a
lot of processing of it, for instance changing CREATE to ALTER if required.
There is a preprocessor and lots of bells and whistles. But the really
important one is CREATE vs. ALTER, which you can handle in this way:
IF object_id('some_proc') IS NULL
EXEC ('CREATE PROCEDURE some_proc AS PRINT 1')
go
ALTER PROCEDURE some_proc -- real procedure follows here.
You would have this prologue in every file. Or, if you write a simple
file-loader in your favourite language, you can generate that code from
the filename. Assume that procedure name and file name agrees and
whip up all programmers that violate the rule. Of course, you can parse
the code, but parsing T-SQL is messy, so you will avoid it if you can.
Then there is one tool that builds an empty database from SourceSafe
or from a structure on disk. We have a certain organisation with different
types of files in different projects. The DB build tool just loads
files type by type in alphabetic order. To handle dependency between
stored procedure, we load those twice. Since you have views that
refer to each other you will need to find a way to control the order.
One way is to have a file that holds the load order of the views. For a
long time this was never a problem for us, simply because we hardly
used views at all. They have started to pop up, it we handle them with
help of our preprocessor.
Finally, there is a tool that reads SourceSafe between to two labels
and generate a script to load all changed objects. The tricky part
here is of course changed tables. Our tool generates a template to
handle the change which builds on the principle create new table,
move data and referencing FKs over, drop old table. In step one, you
will probably prefer to just insert whatever migration you want manually.
(Although it's quite boring.)
> There is a problem that developers - well, the ones here - like the GUI,
> draggy, droppy, clicky tools like SSMS, and given the choice between that
> and a text editor to edit boring old scripts, you know which they will
> choose.
Then again, there is not much you can point and click with in SSMS for
development. Oh, yeah, there is the Table Designer, but if they use
that one, really make sure they stay away. There are several serious
bugs in the Table Designer when it comes to change an existing table.
> Perhaps what I want, then, is something that manages the scripts (files)
> in a visual way (like VS does with source code), and enables changes to
> be deployed to the database of choice. Does that sound better?
There are certainly some options on the market, although I have not
investigated the options very closely. DataDude that I mention is
definitely worth looking at if you are a fan of Visual Studio.
Red Gate's SQL Compare has no integration with version-control system,
but their most recent version permits you to compare a database with
files on disk. That permits you compare the current development with
what you have in the version-control system, although the checkin-
checkout process would be a two-step process. You can also generate
update scripts from it. SQL Compare does not really align with the
way I like to work, but I'm impressed by the tool as such. Not the
least in comparison with SSMS which have quite a few shortcomings
with scripting. Not the least in performance...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Erland, thanks for bearing with me through this process. I will inwardly
digest and assimilate (makes me sound like the Borg).
I will continue to look into the possibility of a tool that will help here
or, who knows, even create one myself, as you have done.
Many thanks
Charles
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C647B2101DYazorman@.127.0.0.1...
> Charles Law (blank@.nowhere.com) writes:
>> I'm starting to be persuaded. I've been looking at a silhouette of two
>> candlesticks, and suddenly I see two faces. I think you mentioned that
>> you
>> use another tool to manage your scripts. What is that tool?
> We have a toolset that I have developed over the years, and by now
> they have achieved quite a level of complexity. Although they are fairly
> streamlined to our needs, so about everyone else would probably find
> them quirky. And if you are looking for drag and drop, just forget it -
> it's all command-line. (And all Perl.)
> Unfortunately this toolset is not available to the public. Maybe I will
> be able to make it avilable in some form in the future.
> But I can give you some of the essence of the toolset, as it may give
> you inspiration to devise your own; of course you would start off with
> something very simple.
> The core is a file-loading module that reads an SQL file and performs a
> lot of processing of it, for instance changing CREATE to ALTER if
> required.
> There is a preprocessor and lots of bells and whistles. But the really
> important one is CREATE vs. ALTER, which you can handle in this way:
> IF object_id('some_proc') IS NULL
> EXEC ('CREATE PROCEDURE some_proc AS PRINT 1')
> go
> ALTER PROCEDURE some_proc -- real procedure follows here.
> You would have this prologue in every file. Or, if you write a simple
> file-loader in your favourite language, you can generate that code from
> the filename. Assume that procedure name and file name agrees and
> whip up all programmers that violate the rule. Of course, you can parse
> the code, but parsing T-SQL is messy, so you will avoid it if you can.
> Then there is one tool that builds an empty database from SourceSafe
> or from a structure on disk. We have a certain organisation with different
> types of files in different projects. The DB build tool just loads
> files type by type in alphabetic order. To handle dependency between
> stored procedure, we load those twice. Since you have views that
> refer to each other you will need to find a way to control the order.
> One way is to have a file that holds the load order of the views. For a
> long time this was never a problem for us, simply because we hardly
> used views at all. They have started to pop up, it we handle them with
> help of our preprocessor.
> Finally, there is a tool that reads SourceSafe between to two labels
> and generate a script to load all changed objects. The tricky part
> here is of course changed tables. Our tool generates a template to
> handle the change which builds on the principle create new table,
> move data and referencing FKs over, drop old table. In step one, you
> will probably prefer to just insert whatever migration you want manually.
> (Although it's quite boring.)
>> There is a problem that developers - well, the ones here - like the GUI,
>> draggy, droppy, clicky tools like SSMS, and given the choice between that
>> and a text editor to edit boring old scripts, you know which they will
>> choose.
> Then again, there is not much you can point and click with in SSMS for
> development. Oh, yeah, there is the Table Designer, but if they use
> that one, really make sure they stay away. There are several serious
> bugs in the Table Designer when it comes to change an existing table.
>> Perhaps what I want, then, is something that manages the scripts (files)
>> in a visual way (like VS does with source code), and enables changes to
>> be deployed to the database of choice. Does that sound better?
> There are certainly some options on the market, although I have not
> investigated the options very closely. DataDude that I mention is
> definitely worth looking at if you are a fan of Visual Studio.
> Red Gate's SQL Compare has no integration with version-control system,
> but their most recent version permits you to compare a database with
> files on disk. That permits you compare the current development with
> what you have in the version-control system, although the checkin-
> checkout process would be a two-step process. You can also generate
> update scripts from it. SQL Compare does not really align with the
> way I like to work, but I'm impressed by the tool as such. Not the
> least in comparison with SSMS which have quite a few shortcomings
> with scripting. Not the least in performance...
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||On Oct 10, 5:21 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> There are certainly some options on the market, although I have not
> investigated the options very closely. DataDude that I mention is
> definitely worth looking at if you are a fan of Visual Studio.
> Red Gate's SQL Compare has no integration with version-control system,
> but their most recent version permits you to compare a database with
> files on disk. That permits you compare the current development with
> what you have in the version-control system, although the checkin-
> checkout process would be a two-step process. You can also generate
> update scripts from it. SQL Compare does not really align with the
> way I like to work, but I'm impressed by the tool as such. Not the
> least in comparison with SSMS which have quite a few shortcomings
> with scripting. Not the least in performance...
yep. On the other hand it allows you to choose whatever version
colntrol you prefer. I personally prefer Subversion which also easily
integrates with JIRA/Fisheye/Crucible.|||I wrote a database scripting tool that probably can solve your issues.
Regards
Robert Allen Schambach
www.dbconstructor.com
"Charles Law" <blank@.nowhere.com> wrote in message
news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>I don't know if I have missed something here, but I blindly thought that if
>I used SSMS to script my database, I could run the script on a new instance
>and get a database out of it.
> The script fails, and I get an error saying that a stored procedure
> requires an object that doesn't exist. That is because the object hasn't
> been created yet. It appears later on in the script.
> How can I get the database scripting wizard to script objects in the
> correct order, that is in an order that takes account of dependencies?
> Either that, or does anyone know of a tool/product that does do this?
> TIA
> Charles
>|||Hi Robert
Looks very interesting. I will take a look.
Thanks.
Charles
"rallen" <rallen@.dbconstructor.com> wrote in message
news:uUs74pBEIHA.2004@.TK2MSFTNGP06.phx.gbl...
>I wrote a database scripting tool that probably can solve your issues.
> Regards
> Robert Allen Schambach
> www.dbconstructor.com
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
>>I don't know if I have missed something here, but I blindly thought that
>>if I used SSMS to script my database, I could run the script on a new
>>instance and get a database out of it.
>> The script fails, and I get an error saying that a stored procedure
>> requires an object that doesn't exist. That is because the object hasn't
>> been created yet. It appears later on in the script.
>> How can I get the database scripting wizard to script objects in the
>> correct order, that is in an order that takes account of dependencies?
>> Either that, or does anyone know of a tool/product that does do this?
>> TIA
>> Charles
>>
>|||On Oct 16, 9:35 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> Hi Robert
> Looks very interesting. I will take a look.
> Thanks.
> Charles
> "rallen" <ral...@.dbconstructor.com> wrote in message
> news:uUs74pBEIHA.2004@.TK2MSFTNGP06.phx.gbl...
>
> >I wrote a database scripting tool that probably can solve your issues.
> > Regards
> > Robert Allen Schambach
> >www.dbconstructor.com
> > "Charles Law" <bl...@.nowhere.com> wrote in message
> >news:Odetnj3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
> >>I don't know if I have missed something here, but I blindly thought that
> >>if I used SSMS to script my database, I could run the script on a new
> >>instance and get a database out of it.
> >> The script fails, and I get an error saying that a stored procedure
> >> requires an object that doesn't exist. That is because the object hasn't
> >> been created yet. It appears later on in the script.
> >> How can I get the database scripting wizard to script objects in the
> >> correct order, that is in an order that takes account of dependencies?
> >> Either that, or does anyone know of a tool/product that does do this?
> >> TIA
> >> Charles- Hide quoted text -
> - Show quoted text -
Charles,
Please let me know if you need any help using dbConstructor, there
will be better documentation shortly, if you have any problems or
suggestions please drop me a line, use the sites Contact Us screen and
I will gladly give you any assistance necessary.
Regards,
Robert Allen Schambach

Database script creation

In the paste I have used SQL2000 to create db from the Query Analyser. In SQL2005 I can't see Query Analzyer but found under programs --> Microsoft SQL Server 2005 --> Configuration Tools --> Reporting Services Configuration --> Database Setup. It creates a database and I can connect to it from VS.NET. Here is my dilema, I was provided SQL scripts to create tables, stored procs etc. Where and how do I run these scripts to create these things in my newly create db.

Also how do I remove a db in SQL2005?

You can run your scripts from the command line via sqlcmd.exe or what I suggest you really do is download SQL Server Management Studio - Express Edition, that is a lightweight replacement for QA and EM, but for Express|||

Excellent suggestion... SQL Server Management Studio was exactly what I was looking for.

Thanks!

Database Schema Documentation Tool?

I am looking for a database documentation tool/script that I can run against
a particular database to generate a document of all the tables, columns, and
relationships in the database. I have seen a data dictionary that lists the
table names with links to the table details further down the document, and
clicking on the relationships jumps you to that table in the document. That
particular one was generated out of the programming revision control system
they were using into xml and xsl files. I am looking for something that can
generate the information out of the metadata in SQL Server.
Thanks
I think you may be thinking of Enterprise Architect
http://www.sparxsystems.com.au/
Or ER/Studio
http://www.embarcadero.com/products/erstudio/index.html
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||Look at ApexSQL Doc from www.ApexSQL.com
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||I love ApexSQL . See the details from below URL:-
http://www.sql-server-performance.com/apex_sql_doc_spotlight.asp
http://www.apexsql.com/sql_tools_doc.asp
You could try the trial version and use it for a month:-
http://www.apexsql.com/downloads.asp
Thanks
Hari
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OyNRBReFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Look at ApexSQL Doc from www.ApexSQL.com
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Greg Hess" <keadrix@.hotmail.com> wrote in message
> news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>
|||Another effective but much less costly option if you only want
documentation is
SqlSpec from ElsaSoft
Their website at www.elsasoft.org has a trial version and also samples
of the output
T
|||Greg Hess wrote:
> I am looking for a database documentation tool/script that I can run against
> a particular database to generate a document of all the tables, columns, and
> relationships in the database. I have seen a data dictionary that lists the
> table names with links to the table details further down the document, and
> clicking on the relationships jumps you to that table in the document. That
> particular one was generated out of the programming revision control system
> they were using into xml and xsl files. I am looking for something that can
> generate the information out of the metadata in SQL Server.
> Thanks
You might want to try SchemaToDoc for SQL Server
(http://www.schematodoc.com). It exports to a Word doc metadata info
such as primary keys, field info (types, size, nullable, defaults),
indexes, check constraints, foreign key constraints, triggers, views,
stored procedures, and extended properties. It also lets you annotate
your tables and fields and include those comments in the Word doc. An
Enterprise edition can create a series of linked HTML files in addition
to the Word output.
|||Thank you for all your suggestions. After evaluating them I have decided to
go with SqlSpec from Elsasoft (http://www.elsasoft.org/). It does exactly
what I need it to do for a good price.

Database schema ?

Hi!

I need SQL Script that works on database from 2000 in any higher version and generates FULL database schema. By full I mean all tables, all columns , all references , primary keys, foreing keys, contraints, and so on, with column types and their names. When I've said generates I meant just to display a table with all this features and their names, types and but not SQL that I can use it to regenerate such schema only display information about this schema ( hope it's clear now ).

I've found such script but it works only on SQL Server 2005, is it sth like this written for 2000 ?

Jarod

Its better to use DMO/SMO instead of TSQL

Wednesday, March 7, 2012

Database Restore error

Hi,
When I am using following script, I am getting an error message
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE mydb FROM DISK
='\\myServer\c$\backup\mydb_oltp\mydb_ol
tp_db_200608062107.BAK'
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I am setting up the db with single mode and no one else is using the
db.. why am I getting this error?
Thank you,
hjAll right I got it. The cursor was still positioned on mydb in my Query
Analyzer.
I changed that and it worked like a charm... I have to make this work
inside a Python file now.
Thanks all...
hj
Hitesh wrote:
> Hi,
> When I am using following script, I am getting an error message
> ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> RESTORE DATABASE mydb FROM DISK
> ='\\myServer\c$\backup\mydb_oltp\mydb_ol
tp_db_200608062107.BAK'
>
> Server: Msg 3101, Level 16, State 1, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I am setting up the db with single mode and no one else is using the
> db.. why am I getting this error?
> Thank you,
> hj|||Hitesh wrote:
> All right I got it. The cursor was still positioned on mydb in my Query
> Analyzer.
> I changed that and it worked like a charm... I have to make this work
> inside a Python file now.
> Thanks all...
> hj
>
At the beginning of the script, before the ALTER statement, do:
USE master
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I am getting following error inside Python when I use "USE Master"
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database
context
to 'master'. in EXEC
here is the query:
qry_restore = """USE Master
ALTER DATABASE mydbtemp SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE mydbtemp FROM DISK =""" +
restore_path + """
ALTER DATABASE mydbtemptemp SET MULTI_USER"""
hj
Tracy McKibben wrote:
> Hitesh wrote:
> At the beginning of the script, before the ALTER statement, do:
> USE master
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||That's an informational message, not an error message, which
can be ignored. You'd need to handle informational messages
in your app.
-Sue
On 9 Aug 2006 13:07:45 -0700, "Hitesh" <hitesh287@.gmail.com>
wrote:
[vbcol=seagreen]
>I am getting following error inside Python when I use "USE Master"
>[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database
context
>to 'master'. in EXEC
>here is the query:
> qry_restore = """USE Master
> ALTER DATABASE mydbtemp SET SINGLE_USER WITH
>ROLLBACK IMMEDIATE
> RESTORE DATABASE mydbtemp FROM DISK =""" +
>restore_path + """
> ALTER DATABASE mydbtemptemp SET MULTI_USER"""
>hj
>
>Tracy McKibben wrote:

Thursday, March 29, 2012

Database space monitoring.

Tuesday, March 27, 2012

DataBase Space

DataBase Space

Wednesday, March 21, 2012

Database setup script

Monday, March 19, 2012

Database script to alarm

Database script to alarm

database script error

Database Script Does Not Account for Dependencies

Database script creation

Database Schema Documentation Tool?

Database schema ?

Wednesday, March 7, 2012

Database Restore error

Database Unit Testing

Blog Archive

About Me