Help! T-SQL, Calling multiple stored procs?

I have to load a webpage table up with values from a database as such:

The Webpage Expects a Final result set of rows with 5 columns each built as below:

For each Row Returned by StoredProc1

Write Columns 1-3 = StoredProc2

Write Column 4 = StoredProc3

Write Column 5 = StoredProc4

Next Row

But I am not sure how to write the conditionals to CALL the stored procs i wrote and loop by a return value from StoredProc1.

Any help is much appreciated!

THANKS!

I would prefer to create separate stored proc with entire logic encapsulated within it, if I were you. Loops are fast and typical in client languages, but in SQL it's better not to be overused.|||So create just one big stored proc to do it all?
|||Yes. And calling stored procs from within stored procs is not so fast.|||

Can you be a little more specific about what each stored proc does? I mean, if it is a set of procs you are writing, then I doubt this is the best way to do it. I would be more inclined to write one stored procedure with one SELECT statement that joins 3 larger sets together (perhaps in temp tables) on the common value from a fourth set.

select *

from QueryFromSP1

outer join (QueryFromSP2) as QueryFromSP2
on QueryFromSP1.key = QueryFromSP2.key

...

The queries could be temp tables, or function calls, depending on the need, but a cursor like you have suggested will probably be a dog in performance (and not one of those really fast dogs either. Think hound dog on a hot Mississippi morning dogs.)

Help! Suspect Databases - Hardware Failure

We have numerous suspect databases across multiple serverse due to a hardware
failure on our SAN. The part of the SAN that failed had a combination of data
and log files, but not both for a given database.
Right now we have services shut down on the server. When we bring the server
back up, after fixing our hardware failure, what would be the best steps?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6eca09c69ec9c@.uwe...
> We have numerous suspect databases across multiple serverse due to a
> hardware
> failure on our SAN. The part of the SAN that failed had a combination of
> data
> and log files, but not both for a given database.
> Right now we have services shut down on the server. When we bring the
> server
> back up, after fixing our hardware failure, what would be the best steps?
BEFORE bringing up the server, make complete backups of all files.
Then once you bring up the server, check the logs and find out why the
databases are suspect.
In some cases it may be as simple as the drive letters or paths no longer
being right and once you fix those, the database may come up cleanly.
If it's more complex, you'll have to do different things, but hard to
recommend w/o knowing in advance the exact nature of the problems.
No matter what I'd make sure to do a DBCC checkdb once the database is back
up, just to be sure.
(btw, I've found that it takes pretty bad problems to outright corrupt a SQL
2000/2005 database, so you may luck out.)

> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Thanks Greg. Once the hardware was repaired and we restarted the services (we
had complete backups available) all databases came up cleanly.
If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run after
hours, if there were errors encountered, would I still be informed, even
though I had the "WITH NO_INFOMSGS" option?
Greg D. Moore (Strider) wrote:
>[quoted text clipped - 5 lines]
>BEFORE bringing up the server, make complete backups of all files.
>Then once you bring up the server, check the logs and find out why the
>databases are suspect.
>In some cases it may be as simple as the drive letters or paths no longer
>being right and once you fix those, the database may come up cleanly.
>If it's more complex, you'll have to do different things, but hard to
>recommend w/o knowing in advance the exact nature of the problems.
>No matter what I'd make sure to do a DBCC checkdb once the database is back
>up, just to be sure.
>(btw, I've found that it takes pretty bad problems to outright corrupt a SQL
>2000/2005 database, so you may luck out.)
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6ecae312206bc@.uwe...
> Thanks Greg. Once the hardware was repaired and we restarted the services
> (we
> had complete backups available) all databases came up cleanly.
>
Glad to hear it.

> If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run
> after
> hours, if there were errors encountered, would I still be informed, even
> though I had the "WITH NO_INFOMSGS" option?
I believe so.

>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Help! Suspect Databases - Hardware Failure

We have numerous suspect databases across multiple serverse due to a hardware
failure on our SAN. The part of the SAN that failed had a combination of data
and log files, but not both for a given database.
Right now we have services shut down on the server. When we bring the server
back up, after fixing our hardware failure, what would be the best steps?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6eca09c69ec9c@.uwe...
> We have numerous suspect databases across multiple serverse due to a
> hardware
> failure on our SAN. The part of the SAN that failed had a combination of
> data
> and log files, but not both for a given database.
> Right now we have services shut down on the server. When we bring the
> server
> back up, after fixing our hardware failure, what would be the best steps?
BEFORE bringing up the server, make complete backups of all files.
Then once you bring up the server, check the logs and find out why the
databases are suspect.
In some cases it may be as simple as the drive letters or paths no longer
being right and once you fix those, the database may come up cleanly.
If it's more complex, you'll have to do different things, but hard to
recommend w/o knowing in advance the exact nature of the problems.
No matter what I'd make sure to do a DBCC checkdb once the database is back
up, just to be sure.
(btw, I've found that it takes pretty bad problems to outright corrupt a SQL
2000/2005 database, so you may luck out.)
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Thanks Greg. Once the hardware was repaired and we restarted the services (we
had complete backups available) all databases came up cleanly.
If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run after
hours, if there were errors encountered, would I still be informed, even
though I had the "WITH NO_INFOMSGS" option?
Greg D. Moore (Strider) wrote:
>> We have numerous suspect databases across multiple serverse due to a
>> hardware
>[quoted text clipped - 5 lines]
>> server
>> back up, after fixing our hardware failure, what would be the best steps?
>BEFORE bringing up the server, make complete backups of all files.
>Then once you bring up the server, check the logs and find out why the
>databases are suspect.
>In some cases it may be as simple as the drive letters or paths no longer
>being right and once you fix those, the database may come up cleanly.
>If it's more complex, you'll have to do different things, but hard to
>recommend w/o knowing in advance the exact nature of the problems.
>No matter what I'd make sure to do a DBCC checkdb once the database is back
>up, just to be sure.
>(btw, I've found that it takes pretty bad problems to outright corrupt a SQL
>2000/2005 database, so you may luck out.)
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6ecae312206bc@.uwe...
> Thanks Greg. Once the hardware was repaired and we restarted the services
> (we
> had complete backups available) all databases came up cleanly.
>
Glad to hear it.
> If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run
> after
> hours, if there were errors encountered, would I still be informed, even
> though I had the "WITH NO_INFOMSGS" option?
I believe so.
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||On 06.03.2007 22:11, cbrichards via SQLMonster.com wrote:
> Thanks Greg. Once the hardware was repaired and we restarted the services (we
> had complete backups available) all databases came up cleanly.
> If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run after
> hours, if there were errors encountered, would I still be informed, even
> though I had the "WITH NO_INFOMSGS" option?
Wouldn't it be better to run this first before using the DB again? I
mean if there were issues you'd rather want to fix them before you put
the DB back in production.
Kind regards
robert

Help! Suspect Databases - Hardware Failure

We have numerous suspect databases across multiple serverse due to a hardwar
e
failure on our SAN. The part of the SAN that failed had a combination of dat
a
and log files, but not both for a given database.
Right now we have services shut down on the server. When we bring the server
back up, after fixing our hardware failure, what would be the best steps?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6eca09c69ec9c@.uwe...
> We have numerous suspect databases across multiple serverse due to a
> hardware
> failure on our SAN. The part of the SAN that failed had a combination of
> data
> and log files, but not both for a given database.
> Right now we have services shut down on the server. When we bring the
> server
> back up, after fixing our hardware failure, what would be the best steps?
BEFORE bringing up the server, make complete backups of all files.
Then once you bring up the server, check the logs and find out why the
databases are suspect.
In some cases it may be as simple as the drive letters or paths no longer
being right and once you fix those, the database may come up cleanly.
If it's more complex, you'll have to do different things, but hard to
recommend w/o knowing in advance the exact nature of the problems.
No matter what I'd make sure to do a DBCC checkdb once the database is back
up, just to be sure.
(btw, I've found that it takes pretty bad problems to outright corrupt a SQL
2000/2005 database, so you may luck out.)

> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Thanks Greg. Once the hardware was repaired and we restarted the services (w
e
had complete backups available) all databases came up cleanly.
If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run after
hours, if there were errors encountered, would I still be informed, even
though I had the "WITH NO_INFOMSGS" option?
Greg D. Moore (Strider) wrote:
>[quoted text clipped - 5 lines]
>BEFORE bringing up the server, make complete backups of all files.
>Then once you bring up the server, check the logs and find out why the
>databases are suspect.
>In some cases it may be as simple as the drive letters or paths no longer
>being right and once you fix those, the database may come up cleanly.
>If it's more complex, you'll have to do different things, but hard to
>recommend w/o knowing in advance the exact nature of the problems.
>No matter what I'd make sure to do a DBCC checkdb once the database is back
>up, just to be sure.
>(btw, I've found that it takes pretty bad problems to outright corrupt a SQ
L
>2000/2005 database, so you may luck out.)
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1|||"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6ecae312206bc@.uwe...
> Thanks Greg. Once the hardware was repaired and we restarted the services
> (we
> had complete backups available) all databases came up cleanly.
>
Glad to hear it.

> If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run
> after
> hours, if there were errors encountered, would I still be informed, even
> though I had the "WITH NO_INFOMSGS" option?
I believe so.

>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||On 06.03.2007 22:11, cbrichards via droptable.com wrote:
> Thanks Greg. Once the hardware was repaired and we restarted the services
(we
> had complete backups available) all databases came up cleanly.
> If I were to set a job to execute DBCC CHECKDB WITH NO_INFOMSGS to run aft
er
> hours, if there were errors encountered, would I still be informed, even
> though I had the "WITH NO_INFOMSGS" option?
Wouldn't it be better to run this first before using the DB again? I
mean if there were issues you'd rather want to fix them before you put
the DB back in production.
Kind regards
robertsql

HELP! Lost LDF file, multiple MDF files, no backups!

I am desperate...
I have a database (I didn't build this one...) where there are 4 MDF
files, and in trying to clear out the two LDF files, which where
initially set to autogrow and were larger than the actual database,
and backup of the transaction log didn't shrink it, I detached the
database, deleted the LDF files, and tried to reattach, which, when
there is a single MDF file, it works!! I cannot reattach this
database!!
Here is the error:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
aborted.
Device activation error. The physical file name 'd:\yadayada_01.ldf'
may be incorrect.
Device activation error. The physical file name 'd:\yadayada_02.ldf'
may be incorrect.
I've tried everything I can think of... trying to figure this out
before someone notices (its not a very often used database).
Please help...
Thanks,
CalgaryDataGrl
Hello,
Try using the Create Database with Attach_Rebuild_Log option. See the below
eg:-
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N'D:\Data\Test_data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks
Hari
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171323042.569709.53480@.l53g2000cwa.googlegro ups.com...
>I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
>
|||On 12 Feb, 23:30, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
Please don't delete log files. That's a great way to lose data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||On Feb 13, 5:30 am, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
You should have kept a backup (copy the files into a separate
directory) before making drastic changes so that you can revert to
original state in case things just work out the way you wanted (in
this situation).

HELP! Lost LDF file, multiple MDF files, no backups!

I am desperate...
I have a database (I didn't build this one...) where there are 4 MDF
files, and in trying to clear out the two LDF files, which where
initially set to autogrow and were larger than the actual database,
and backup of the transaction log didn't shrink it, I detached the
database, deleted the LDF files, and tried to reattach, which, when
there is a single MDF file, it works!! I cannot reattach this
database!!
Here is the error:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
aborted.
Device activation error. The physical file name 'd:\yadayada_01.ldf'
may be incorrect.
Device activation error. The physical file name 'd:\yadayada_02.ldf'
may be incorrect.
I've tried everything I can think of... trying to figure this out
before someone notices (its not a very often used database).
Please help...
Thanks,
CalgaryDataGrlHello,
Try using the Create Database with Attach_Rebuild_Log option. See the below
eg:-
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N'D:\Data\Test_data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks
Hari
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171323042.569709.53480@.l53g2000cwa.googlegroups.com...
>I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
>|||On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> Try using the Create Database with Attach_Rebuild_Log option. See the bel
ow
> eg:-
> USE [master]
> GO
> CREATE DATABASE [Test] ON
> (FILENAME = N'D:\Data\Test_data.mdf')
> FOR ATTACH_REBUILD_LOG
> GO
> Thanks
> Hari
>
Hi Hari,
I'm getting an error:
Server: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ATTACH_REBUILD_LOG'.
Any thoughts?
Stacy|||Are you on SQL Server 2005?
You should probably contact product support to help walk you through this
correctly - and you should get yourself a decent backup strategy. Send me
mail through the blog link below if you want help with this.
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171556088.308708.76810@.p10g2000cwp.googlegroups.com...
> On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hi Hari,
> I'm getting an error:
>
> Server: Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near 'ATTACH_REBUILD_LOG'.
> Any thoughts?
> Stacy
>|||On 12 Feb, 23:30, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
Please don't delete log files. That's a great way to lose data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Feb 13, 5:30 am, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
You should have kept a backup (copy the files into a separate
directory) before making drastic changes so that you can revert to
original state in case things just work out the way you wanted (in
this situation).

HELP! Lost LDF file, multiple MDF files, no backups!

I am desperate...
I have a database (I didn't build this one...) where there are 4 MDF
files, and in trying to clear out the two LDF files, which where
initially set to autogrow and were larger than the actual database,
and backup of the transaction log didn't shrink it, I detached the
database, deleted the LDF files, and tried to reattach, which, when
there is a single MDF file, it works!! I cannot reattach this
database!!
Here is the error:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
aborted.
Device activation error. The physical file name 'd:\yadayada_01.ldf'
may be incorrect.
Device activation error. The physical file name 'd:\yadayada_02.ldf'
may be incorrect.
I've tried everything I can think of... trying to figure this out
before someone notices (its not a very often used database).
Please help...
Thanks,
CalgaryDataGrlHello,
Try using the Create Database with Attach_Rebuild_Log option. See the below
eg:-
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N'D:\Data\Test_data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks
Hari
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171323042.569709.53480@.l53g2000cwa.googlegroups.com...
>I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
>|||On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> Try using the Create Database with Attach_Rebuild_Log option. See the below
> eg:-
> USE [master]
> GO
> CREATE DATABASE [Test] ON
> (FILENAME = N'D:\Data\Test_data.mdf')
> FOR ATTACH_REBUILD_LOG
> GO
> Thanks
> Hari
>
Hi Hari,
I'm getting an error:
Server: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ATTACH_REBUILD_LOG'.
Any thoughts?
Stacy|||Are you on SQL Server 2005?
You should probably contact product support to help walk you through this
correctly - and you should get yourself a decent backup strategy. Send me
mail through the blog link below if you want help with this.
--
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171556088.308708.76810@.p10g2000cwp.googlegroups.com...
> On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
>> Hello,
>> Try using the Create Database with Attach_Rebuild_Log option. See the
>> below
>> eg:-
>> USE [master]
>> GO
>> CREATE DATABASE [Test] ON
>> (FILENAME = N'D:\Data\Test_data.mdf')
>> FOR ATTACH_REBUILD_LOG
>> GO
>> Thanks
>> Hari
> Hi Hari,
> I'm getting an error:
>
> Server: Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near 'ATTACH_REBUILD_LOG'.
> Any thoughts?
> Stacy
>|||On 12 Feb, 23:30, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
Please don't delete log files. That's a great way to lose data.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Feb 13, 5:30 am, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
You should have kept a backup (copy the files into a separate
directory) before making drastic changes so that you can revert to
original state in case things just work out the way you wanted (in
this situation).

Help! How to insert multiple rows into Database??

I keep getting this error but it will only insert the 1st row into my database table

The variable name '@.CustId' has already been declared. Variable names must be unique within a query batch or stored procedure.

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim drow As GridViewRow

For Each drow In GridView1.Rows

Dim textBoxText As String = CType(drow.FindControl("Label2"), Label).Text

SqlDataSource2.InsertParameters.Add("CustId", TypeCode.String, Profile.UserName)

SqlDataSource2.InsertParameters.Add("OrderDate", TypeCode.DateTime, DateTime.Now.ToString)

SqlDataSource2.InsertParameters.Add("Total", TypeCode.Double, TotalUnitPrice)

SqlDataSource2.InsertParameters.Add("Quantity", TypeCode.Int32, textBoxText)

SqlDataSource2.Insert()

Next

Response.Redirect("checkout.aspx")

End Sub

since you are operating in a loop, you should clear your parameters collection on each pass through

Dim drowAs GridViewRowFor Each drowIn GridView1.RowsDim textBoxTextAs String =CType(drow.FindControl("Label2"), Label).Text SqlDataSource2.InsertParameters.Clear()'<-- Clear the params first SqlDataSource2.InsertParameters.Add("CustId", TypeCode.String, Profile.UserName) SqlDataSource2.InsertParameters.Add("OrderDate", TypeCode.DateTime, DateTime.Now.ToString) SqlDataSource2.InsertParameters.Add("Total", TypeCode.Double, TotalUnitPrice) SqlDataSource2.InsertParameters.Add("Quantity", TypeCode.Int32, textBoxText) SqlDataSource2.Insert()Next

|||

Try this after your insert:

SqlDataSources2.InsertParameters.Clear();

There is another way to do this. Declare your parameters first, then assign the InsertParameters' default values for each InsertParameter after the insert.

SqlDataSource2.InsertParameters("Parameter1").DefaultValue=valueofParameter1

...

|||Omg .. thanks a lot ! It works... Haha ...

help! H/W spec for SQL Server 2000

Hi,

I have a SQL server 2000 database running in Windows Server 2000. The database only consists of a handful of tables and is taking multiple inserts from @.40 client PCs.

The server exists on two servers with half the clients talking to each. The databases use transactional replication to stay synchronised.

Each PC generates about 100 SQL transactions per busy hour, each being an insert.

The customer is expanding the number of client PCs from 40, through 300 to, ultimately, about 3000. They are asking me what hardware spec is required and want to be told it in a for each additional 50 client applications you need XYZ type of format to allow for gradual growth.

I have to say I am unsure how to go about scaling and configuring the servers!

So, is there such a thing as an automatic configurator which automatically produces a required spec for a given number of transactions and clients? Are there other criteria they/I need to take into account such as number of logins etc? For archiving I am guessing that a CD/RW drive is the way to go?

Also, in terms of design between the two servers they have. With clustering, mirroring, replication etc I am unsure as to which is most appropriate.

The thing I do know is that they are willing to spend serious $$$ for the right design and need 99.9% up time.

They also pull significant amount of reports which I think logically equates to a third server to keep this traffic away from the live database. So, is replication the best way to go? Say replication every 15 minutes to a reports server?

I am also of the opinion that they need to move from Windows 2000 Server to Advanced Server for added scalability but will pursue that elsewhere.

Any input MUCH appreciated.

Out of my depth! :confused:
PaulThe thing I do know is that they are willing to spend serious $$$ for the right design and need 99.9% up time.

Then they better be willing to spend some money. They'll need it.

I would try to get them to move to Advanced Server or wait for 2003 SP1 to come out. Enterprise Edition will be needed for many of the features you are talking about. It's impossible to answer your question without knowing the database. You need to figure out how many rows in the tables will be taken at different levels of users, what the average fill ratio will be on those tables, then how much size you'll need along the way. In addition, you need to figure out how many transactions, and how much those will cost you as the database grows do to increased size of the tables.

I would purchase the Microsoft Press book: Permformance Tuning Manual technical reference if I was you and have a look at the worksheets and formulas in the book.