How to attach db with missing log file?

I jad detached a larg db with multiple empty log files.
While moving the db to another server, a drive array onthe
first one went bad, taking one of my log files with it.
How can I re-attach the db and have it ignore that its
missing a log file?
Thank you in advance!Hi
You may be able to use sp_attach_single_file_db, although this would not be
guaranteed.
John
"Merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:bc7201c40dfc$f3419e20$a001280a@.phx.gbl...
> I jad detached a larg db with multiple empty log files.
> While moving the db to another server, a drive array onthe
> first one went bad, taking one of my log files with it.
> How can I re-attach the db and have it ignore that its
> missing a log file?
> Thank you in advance!|||Thank you for your help. sp_attach_single_file_db is not
allowing me to get past the missing log file, either. is
there any other way?

>--Original Message--
>Hi
>You may be able to use sp_attach_single_file_db, although
this would not be
>guaranteed.
>John
>"Merlin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:bc7201c40dfc$f3419e20$a001280a@.phx.gbl...
onthe
>
>.
>|||Hi
Not that I know off. You may want to call Microsoft PSS if going back to the
last backup is not a viable option.
John
"Merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:d87301c40e01$69052ec0$a601280a@.phx.gbl...
> Thank you for your help. sp_attach_single_file_db is not
> allowing me to get past the missing log file, either. is
> there any other way?
>
>
> this would not be
> message
> onthe|||Not supported etc and make sure you have a copy of the mdf before starting
this. Also replace the relavent database,drive letters and filenames with
your particulars as this answer was for a specific case.
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following (make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names */
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME = 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME = 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the log file
will be small so it will be worth increasing its size
Unfortunately your files will be called fake_Data.MDF and
fake_Log.LDF but you can get round this by detaching the
database properly and then renaming the files and reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:d87301c40e01$69052ec0$a601280a@.phx.gbl...
> Thank you for your help. sp_attach_single_file_db is not
> allowing me to get past the missing log file, either. is
> there any other way?
>
>
> this would not be
> message
> onthe|||I fixed this by running dbcc rebuild_log and then running
dbcc checkdb. all is good now. sp_attach_single_file_db
didnt work for me, it was still looking for the missing
log file before letting me attach the db. Thank you for
the help, I appreciated it!!!

>--Original Message--
>Hi
>You may be able to use sp_attach_single_file_db, although
this would not be
>guaranteed.
>John
>"Merlin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:bc7201c40dfc$f3419e20$a001280a@.phx.gbl...
onthe
>
>.
>sql

How to apply sp4 to multiple instances that have different sp leve

I need to apply sp4 for SqlServer 2000 to the following instances all runnin
g
on the same server. The server is running Windows 2000.
SERVER Name: ABC01DB
SqlServer Instances follow:
Default 8.00.534 SP2 Developer Edition
DEV 8.00.760 SP3 Developer Edition
ABCDEV_APP01 (*) 8.00.194 RTM Developer Edition
ABCDEV_LT01 (*) 8.00.194 RTM Developer Edition
ABCDEV_LT02 (*) 8.00.194 RTM Developer Edition
ABCDEV_QA01 (*) 8.00.194 RTM Developer Edition
ABC_MGR 8.00.760 SP3 Developer Edition
Questions:
1) Do I need to apply any service packs other than sp4 to any of them prior
to applying sp4.
2) Is there any way to apply sp4 to multiple instances simultaneously or
must I apply sp4 separately for each instance.
3) Any suggestions or comments regarding the steps to simplify applying sp4
to all of the above instances.I proceeded with the upgrade and simply applied the sp4 to each instance,
starting with those which had no service packs first, (RTM). Then, I applie
d
it to the Default which was at sp2, and finally to those at sp3.
It all works.
"theWizard1" wrote:

> I need to apply sp4 for SqlServer 2000 to the following instances all runn
ing
> on the same server. The server is running Windows 2000.
> SERVER Name: ABC01DB
> SqlServer Instances follow:
> Default 8.00.534 SP2 Developer Edition
> DEV 8.00.760 SP3 Developer Edition
> ABCDEV_APP01 (*) 8.00.194 RTM Developer Edition
> ABCDEV_LT01 (*) 8.00.194 RTM Developer Edition
> ABCDEV_LT02 (*) 8.00.194 RTM Developer Edition
> ABCDEV_QA01 (*) 8.00.194 RTM Developer Edition
> ABC_MGR 8.00.760 SP3 Developer Edition
> Questions:
> 1) Do I need to apply any service packs other than sp4 to any of them pri
or
> to applying sp4.
> 2) Is there any way to apply sp4 to multiple instances simultaneously or
> must I apply sp4 separately for each instance.
> 3) Any suggestions or comments regarding the steps to simplify applying sp
4
> to all of the above instances.

How to apply sp4 to multiple instances that have different sp leve

I need to apply sp4 for SqlServer 2000 to the following instances all running
on the same server. The server is running Windows 2000.
SERVER Name: ABC01DB
SqlServer Instances follow:
Default 8.00.534 SP2 Developer Edition
DEV 8.00.760 SP3 Developer Edition
ABCDEV_APP01 (*) 8.00.194 RTM Developer Edition
ABCDEV_LT01 (*) 8.00.194 RTM Developer Edition
ABCDEV_LT02 (*) 8.00.194 RTM Developer Edition
ABCDEV_QA01 (*) 8.00.194 RTM Developer Edition
ABC_MGR 8.00.760 SP3 Developer Edition
Questions:
1) Do I need to apply any service packs other than sp4 to any of them prior
to applying sp4.
2) Is there any way to apply sp4 to multiple instances simultaneously or
must I apply sp4 separately for each instance.
3) Any suggestions or comments regarding the steps to simplify applying sp4
to all of the above instances.I proceeded with the upgrade and simply applied the sp4 to each instance,
starting with those which had no service packs first, (RTM). Then, I applied
it to the Default which was at sp2, and finally to those at sp3.
It all works.
"theWizard1" wrote:
> I need to apply sp4 for SqlServer 2000 to the following instances all running
> on the same server. The server is running Windows 2000.
> SERVER Name: ABC01DB
> SqlServer Instances follow:
> Default 8.00.534 SP2 Developer Edition
> DEV 8.00.760 SP3 Developer Edition
> ABCDEV_APP01 (*) 8.00.194 RTM Developer Edition
> ABCDEV_LT01 (*) 8.00.194 RTM Developer Edition
> ABCDEV_LT02 (*) 8.00.194 RTM Developer Edition
> ABCDEV_QA01 (*) 8.00.194 RTM Developer Edition
> ABC_MGR 8.00.760 SP3 Developer Edition
> Questions:
> 1) Do I need to apply any service packs other than sp4 to any of them prior
> to applying sp4.
> 2) Is there any way to apply sp4 to multiple instances simultaneously or
> must I apply sp4 separately for each instance.
> 3) Any suggestions or comments regarding the steps to simplify applying sp4
> to all of the above instances.sql

How to Append Multiple Queries to a Temp Table?

Hello. I'm having some difficulty trying to output the results of two seperate queries into the same temporary table.

Does anyone know if it is possible to use the UNION operator to output the results into a temporary table?

Select * From TableA
UNION
Select * From TableB
<--output result to temporary table here-->

Alternatively, is it possible to output the results of two queries in to the same temporary table without the UNION clause?

The following statement fails on the second SELECT INTO due to the fact that #MyTempTable already exists.

Select * INTO #MyTempTable FROM TableA
Select * INTO #MyTempTable FROM TableB

Thanks in advance.I think if you specify the SELECT * INTO in the top half of your union it ought to do the trick.

Yes this works...

USE NORTHWIND
GO
SELECT * INTO EMPLOYEES2
FROM EMPLOYEES
UNION ALL
SELECT * FROM EMPLOYEES

Points deducted for style though, never use SELECT * in production. Need a column list, do this...

SELECT COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'
ORDER BY ORDINAL_POSITION|||Thanks Thrasymachus - that's exactly what I was looking for.

...and I'm sure you'll be pleased to hear the SELECT * was only for the example's in this thread. :D

how to Allow Multiple access to data base over network

Hello All, I have a running/ woking database, but only one user can
access the data base at a time on the newtork, If more than one user
try accessing the database, it will frezee until the first to grab the
data base logs out. Please how do I solve this problem. I have sql 2000
enterprise edition running on the server.
Regards
Agboola.1default wrote:
> Hello All, I have a running/ woking database, but only one user can
> access the data base at a time on the newtork, If more than one user
> try accessing the database, it will frezee until the first to grab the
> data base logs out. Please how do I solve this problem. I have sql 2000
> enterprise edition running on the server.
> Regards
> Agboola.
>
First, check the database properties to make sure it's not in
Single-User mode. However, that doesn't sound like your problem.
What exactly freezes up? It sounds like your first user is doing
something that is blocking other users. You can confirm that by
querying the sysprocesses table in the master database, or by issuing
the command 'sp_who2' in Query Analyzer.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Looks like you have the database in restricted or single user mode. But you
should receive messages like these, instead of freezing the session:
Database 'Northwind' is already open and can only have one user at a time.
Database 'Northwind' is in restricted mode. Only the database owner and
members of the dbcreator and sysadmin roles can access it.
Try this first: Using Enterprise Manager, right-click your database,
Properties, select the Options tab and make sure the Restrict access option
is not checked.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"1default" wrote:
> Hello All, I have a running/ woking database, but only one user can
> access the data base at a time on the newtork, If more than one user
> try accessing the database, it will frezee until the first to grab the
> data base logs out. Please how do I solve this problem. I have sql 2000
> enterprise edition running on the server.
> Regards
> Agboola.
>

how to Allow Multiple access to data base over network

Hello All, I have a running/ woking database, but only one user can
access the data base at a time on the newtork, If more than one user
try accessing the database, it will frezee until the first to grab the
data base logs out. Please how do I solve this problem. I have sql 2000
enterprise edition running on the server.
Regards
Agboola.1default wrote:
> Hello All, I have a running/ woking database, but only one user can
> access the data base at a time on the newtork, If more than one user
> try accessing the database, it will frezee until the first to grab the
> data base logs out. Please how do I solve this problem. I have sql 2000
> enterprise edition running on the server.
> Regards
> Agboola.
>
First, check the database properties to make sure it's not in
Single-User mode. However, that doesn't sound like your problem.
What exactly freezes up? It sounds like your first user is doing
something that is blocking other users. You can confirm that by
querying the sysprocesses table in the master database, or by issuing
the command 'sp_who2' in Query Analyzer.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Looks like you have the database in restricted or single user mode. But you
should receive messages like these, instead of freezing the session:
Database 'Northwind' is already open and can only have one user at a time.
Database 'Northwind' is in restricted mode. Only the database owner and
members of the dbcreator and sysadmin roles can access it.
Try this first: Using Enterprise Manager, right-click your database,
Properties, select the Options tab and make sure the Restrict access option
is not checked.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"1default" wrote:

> Hello All, I have a running/ woking database, but only one user can
> access the data base at a time on the newtork, If more than one user
> try accessing the database, it will frezee until the first to grab the
> data base logs out. Please how do I solve this problem. I have sql 2000
> enterprise edition running on the server.
> Regards
> Agboola.
>

How to add sproc to multiple databases

Hi all. Lets say I had a script to run (create sproc) and I needed to
run it on several different databases on one sql server. Can someone
give me an idea how I could do that short of changing the "use databse"
line each time?
ThanksSee if this helps:
http://www.mssqlcity.com/FAQ/Devel/sp_msforeachdb.htm
But be warned that this is an undocumented and unsupported system procedure.
ML
http://milambda.blogspot.com/|||You can also consider instead of having multiple copies of the proc,
just put one copy in the master database... make sure it is prefixed
with "sp_" so it can be executed from any database on that server.
I haven't tried this out myself. Be careful of any name collisions
from future service packs or upgrades.|||Personally, while I have done this in the past, I am starting to shy away
from this practice. It does work, but the problem here is the same problem
as DLL hell. So I have a utility procedure named sp_stringParse, or
whatever. I use this in three databases, and I really think it needs to be
improved for a task in database 3. So do I have a sp_stringParse_version1,
sp_stringParse_version2? Maybe, but instead I just put utility procedures
in the database and upgrade them as required in each system. It also saves
me in that if I don't use the new version immediately, I don't have to test
the code in the other databases until I upgrade it.
It is essential however that you use some sort of versioning process with
SourceSafe, CVS, or even just naming the files differently so you can apply
the latest versions as you discover a need to upgrade the proc in another
database.
Just my $.03 worth :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<InfoSponge3000@.gmail.com> wrote in message
news:1137783245.156785.87200@.g43g2000cwa.googlegroups.com...
> You can also consider instead of having multiple copies of the proc,
> just put one copy in the master database... make sure it is prefixed
> with "sp_" so it can be executed from any database on that server.
> I haven't tried this out myself. Be careful of any name collisions
> from future service packs or upgrades.
>

how to add ODBC DSN to multiple computers

Hello. I need to set add an SQL ODBC System DSN to each computer on
our domain.
The suggestions I've seen so far involve setting up a GPO startup
script using VBScript to call an API, or a batch file using odbcconf.
I don't know VB, and I could find very little information about how to
use odbcconf.
I would appreciate it if someone could give me a standard solution, if
there is one. I'm a little new to this, so I wouldn't be surprised if
I were completely off the mark.
thanks,
joseph
If the use of a DSN is not really mandatory for your application, then using
a DSN-Less ODBC connection might be an easier solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Joseph O'Brien" <obrien1984@.hotmail.com> wrote in message
news:1139438690.109709.324280@.g47g2000cwa.googlegr oups.com...
> Hello. I need to set add an SQL ODBC System DSN to each computer on
> our domain.
> The suggestions I've seen so far involve setting up a GPO startup
> script using VBScript to call an API, or a batch file using odbcconf.
> I don't know VB, and I could find very little information about how to
> use odbcconf.
> I would appreciate it if someone could give me a standard solution, if
> there is one. I'm a little new to this, so I wouldn't be surprised if
> I were completely off the mark.
> thanks,
> joseph
>

how to add ODBC DSN to multiple computers

Hello. I need to set add an SQL ODBC System DSN to each computer on
our domain.
The suggestions I've seen so far involve setting up a GPO startup
script using VBScript to call an API, or a batch file using odbcconf.
I don't know VB, and I could find very little information about how to
use odbcconf.
I would appreciate it if someone could give me a standard solution, if
there is one. I'm a little new to this, so I wouldn't be surprised if
I were completely off the mark.
thanks,
josephIf the use of a DSN is not really mandatory for your application, then using
a DSN-Less ODBC connection might be an easier solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Joseph O'Brien" <obrien1984@.hotmail.com> wrote in message
news:1139438690.109709.324280@.g47g2000cwa.googlegroups.com...
> Hello. I need to set add an SQL ODBC System DSN to each computer on
> our domain.
> The suggestions I've seen so far involve setting up a GPO startup
> script using VBScript to call an API, or a batch file using odbcconf.
> I don't know VB, and I could find very little information about how to
> use odbcconf.
> I would appreciate it if someone could give me a standard solution, if
> there is one. I'm a little new to this, so I wouldn't be surprised if
> I were completely off the mark.
> thanks,
> joseph
>

how to add multiple entities in report builder?

I have SQLRS running under MOSS (ie in a report library).
Have created a datasource and use report builder the build a report.
When I *start* a *new* report I see all the entitities (tables) in the
database on the left hand side......I can drag and drop tables/
columns to the report BUT once I have dragged *one* entities columns
to the report all the others disappear......
so I now *only* have one entity on the left hand side with its columns
below.
Is this expected behavour? I thought I would be able to add another
entity and make a join between them...but this doesnt seem to be
possible?
How can i make other entitiies display in the explorer on the left?
Thanks
Al BlakeHi
RB works with Report Model and the relations are set there. If you want to
add any other entity you need to define there at RM. This behaviour is by
design.
Amarnath, MCTS
"scubaal" wrote:
> I have SQLRS running under MOSS (ie in a report library).
> Have created a datasource and use report builder the build a report.
> When I *start* a *new* report I see all the entitities (tables) in the
> database on the left hand side......I can drag and drop tables/
> columns to the report BUT once I have dragged *one* entities columns
> to the report all the others disappear......
> so I now *only* have one entity on the left hand side with its columns
> below.
> Is this expected behavour? I thought I would be able to add another
> entity and make a join between them...but this doesnt seem to be
> possible?
> How can i make other entitiies display in the explorer on the left?
> Thanks
> Al Blake
>

how to add a multi value select prompt

Thanks Wayne for the useful information but i am experiencing another
problem
I am not able to add a prompt in the reportwhich can allow multiple
values,
as in, if i want to write a query
select * from departments where departmentid in (@.PromptName)
i want to see the data for more than 1 departments, but if i specify a
comma separated list in the prompt, it gives me an error.......
please tell me if there is another way of doing so.Check
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/22/200.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gunjan jain" <gunjan@.nagarro.com> wrote in message
news:f8d018ec.0501172039.59a24cd2@.posting.google.com...
> Thanks Wayne for the useful information but i am experiencing another
> problem
> I am not able to add a prompt in the reportwhich can allow multiple
> values,
> as in, if i want to write a query
> select * from departments where departmentid in (@.PromptName)
> i want to see the data for more than 1 departments, but if i specify a
> comma separated list in the prompt, it gives me an error.......
> please tell me if there is another way of doing so.

How to add a line/separator between groups?

Hi,

I have a simple report with multiple grouping. I like to add a separator between groups.

Some data (Group #1)

================= <-- separator line or color row or something

Some data (Group #2)

=================

Thanks.

You could use the footer of a group as the seperator between groups. If there is no footer for the group, you can set it in the properties of the group (rightclick the group and select properties) I think this is what you want!

|||

What I do is merge all the columns in the report for the Group Header & Footer textboxes. Then, for the Group Header textbox, I set the border style of the top of the textbox to Solid, and I do the same for the bottom of the Group Footer textbox.

hth

How to add a line/separator between groups?

Hi,

I have a simple report with multiple grouping. I like to add a separator between groups.

Some data (Group #1)

================= <-- separator line or color row or something

Some data (Group #2)

=================

Thanks.

You could use the footer of a group as the seperator between groups. If there is no footer for the group, you can set it in the properties of the group (rightclick the group and select properties) I think this is what you want!

|||

What I do is merge all the columns in the report for the Group Header & Footer textboxes. Then, for the Group Header textbox, I set the border style of the top of the textbox to Solid, and I do the same for the bottom of the Group Footer textbox.

hth

How to add a group header and footer for a report

I have a report that is being called via stored proc, and i want to group by contract. when report gets generated i get multiple contracts info. but will be grouped/sorted by contract.

please how can i have a group header and also a group footer to show a summary of each contract information with some calculated fields in it.

i may get 100 records related to 10 contracts , 10 rows for each contract.

as soon as the first contract info is shown on the report it has to show a summary related to the first contract in the group footer, and then continue populating the second contract info and so on.

Please i am totally new to reporting and help would be appreciated. thank you all.

Here's what you can do:

Click anywhere on your table while in Layout.
Right click on the box next to your details row (it has 3 horizontal bold bars on it), and select 'Insert Group'.
On the 'General' tab, make sure 'Include group header' and 'Include group footer' are both checked.
In the 'Group on:' section, drop down the box and select your contract field. (Or you can type: =Fields!contract.Value)
On the 'Sorting' tab, again, pick or type your contract field as above.
On the 'Visibility' tab, make sure that the 'Visible' radio button is selected.
Hit 'OK' and now you can add your fields to the group header and footer.

Hope this helps.

Jarret

|||

Did this work for you Reddymade?

Jarret