Attach remote DB file: single

Showing posts with label single. Show all posts

Tuesday, March 27, 2012

auto backup jobs wont run - not in single user mode

Hello!
I am trying to get a SQL server that was configured by someone else up
and running the backup and maintainance jobs from within SQL server
2000. when the job runs it backs up all but 3 of the databases, the 3
it doesnt is our ASDB (our arcserv) database, master and model.
the error that shows for the asdb database is ..
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
The one that shows for the others is this...
12] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[13] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Database state cannot be changed
while other users are using the database 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
failed.
[14] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Can anyone advise me on how to resolve this issue?
Thanks in advance!
/ehrenWingnut wrote:
> Hello!
> I am trying to get a SQL server that was configured by someone else up
> and running the backup and maintainance jobs from within SQL server
> 2000. when the job runs it backs up all but 3 of the databases, the 3
> it doesnt is our ASDB (our arcserv) database, master and model.
> the error that shows for the asdb database is ..
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> The one that shows for the others is this...
>
> 12] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> Database needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [13] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Database state cannot be changed
> while other users are using the database 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
> statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
> failed.
> [14] Database msdb: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> Database needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Can anyone advise me on how to resolve this issue?
> Thanks in advance!
> /ehren
>
In your maintenance plan, turn off the "repair problems" option...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, I will give that a try.
On Jan 29, 9:38 am, Tracy McKibben <t...@.realsqlguy.com> wrote:
> Wingnut wrote:
> > Hello!
> > I am trying to get a SQL server that was configured by someone else up
> > and running the backup and maintainance jobs from within SQL server
> > 2000. when the job runs it backs up all but 3 of the databases, the 3
> > it doesnt is our ASDB (our arcserv) database, master and model.
> > the error that shows for the asdb database is ..
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> > processed. Database needs to be in single user mode.
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > The one that shows for the others is this...
> > 12] Database master: Check Data and Index Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> > Database needs to be in single user mode.
> > The following errors were found:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> > processed. Database needs to be in single user mode.
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > [13] Database model: Check Data and Index Linkage...
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Database state cannot be changed
> > while other users are using the database 'msdb'
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
> > statement failed.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
> > failed.
> > [14] Database msdb: Check Data and Index Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> > Database needs to be in single user mode.
> > The following errors were found:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> > processed. Database needs to be in single user mode.
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > Can anyone advise me on how to resolve this issue?
> > Thanks in advance!
> > /ehrenIn your maintenance plan, turn off the "repair problems" option...
> --
> Tracy McKibben
> MCDBAhttp://www.realsqlguy.com- Hide quoted text -- Show quoted text -

auto backup jobs wont run - not in single user mode

Thursday, March 8, 2012

Audit triggers problem

Im using triggers to track changes Insert/Update/Deletes on my DB tables and they work for when i am manually adding/editing and deleting a single records.

The problem arises in that I have an asset/inventory management app that dumps lots of details into my DB tables at once each time its run.
Not all of the tables are updated and data cannot be completely inserted.

This is the trigger i have been using - could someone tell me how to modify it to work.

/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @.TableName to match the table to be audit trailed
*/

ALTER trigger tr_TableName
on dbo.TableName for insert, update, delete
as

declare @.bit int ,
@.field int ,
@.maxfield int ,
@.char int ,
@.fieldname varchar(128) ,
@.TableName varchar(128) ,
@.PKCols varchar(1000) ,
@.sql varchar(2000),
@.UpdateDate varchar(21) ,
@.Action nvarchar(50) ,
@.HostName nvarchar(50),
@.PKFieldName varchar (1000)

IF EXISTS(SELECT * FROM inserted)
IF EXISTS(SELECT * FROM deleted)
--update = inserted and deleted tables both contain data
BEGIN
SET @.Action = 'UPDATE'
SELECT @.DeviceID = (SELECT inserted.DeviceID FROM inserted INNER JOIN deleted ON inserted.deviceID = deleted.deviceid)
END
ELSE

--insert = inserted contains data, deleted does not
BEGIN
SET @.Action = 'INSERT'
select @.DeviceID = (SELECT DeviceID from inserted)
END
ELSE
--delete = deleted contains data, inserted does not
BEGIN
SET @.Action = 'DELETE'
select @.DeviceID = (SELECT DeviceID from deleted)
END

select @.TableName = 'TableName'

-- date
select @.HostName = host_name(),
@.UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114),
--@.DeviceID,
@.PKFieldName=(select top 1 c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @.TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)

-- get list of columns
select * into #ins from inserted
select * into #del from deleted

-- Get primary key columns for full outer join
select @.PKCols = coalesce(@.PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @.TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @.PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @.TableName)
return
end

select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName and ORDINAL_POSITION > @.field
select @.bit = (@.field - 1 )% 8 + 1
select @.bit = power(2,@.bit - 1)
select @.char = ((@.field - 1) / 8) + 1
--if substring(COLUMNS_UPDATED(),@.char, 1) & @.bit > 0
begin
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.TableName and ORDINAL_POSITION = @.field
select @.sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName, DeviceID)'
-- select @.sql = 'insert LITE_Inventory (TableName, FieldName, OldValue, NewValue, UpdateDate, Action, Host, PkFieldName)'
select @.sql = @.sql + ' select ''' + @.TableName + ''''
select @.sql = @.sql + ',''' + @.fieldname + ''''
select @.sql = @.sql + ',convert(varchar(1000),d.' + @.fieldname + ')'
select @.sql = @.sql + ',convert(varchar(1000),i.' + @.fieldname + ')'
select @.sql = @.sql + ',''' + @.UpdateDate + ''''
select @.sql = @.sql + ',''' + @.Action + ''''
select @.sql = @.sql + ',''' + @.HostName + ''''
select @.sql = @.sql + ',''' + @.PKFieldName + ''''
select @.sql = @.sql + ' from #ins i full outer join #del d'
select @.sql = @.sql + @.PKCols
select @.sql = @.sql + ' where i.' + @.fieldname + ' <> d.' + @.fieldname
select @.sql = @.sql + ' or (i.' + @.fieldname + ' is null and d.' + @.fieldname + ' is not null)'
select @.sql = @.sql + ' or (i.' + @.fieldname + ' is not null and d.' + @.fieldname + ' is null)'

exec (@.sql)
end
endYou have to remember that inerted and deleted can have a set of data...not just 1 row...

Tracking chnages should br pretty straighty forward...

If the data is altered just move the whole row to history...|||You have to remember that inerted and deleted can have a set of data...not just 1 row...

Tracking chnages should br pretty straighty forward...

If the data is altered just move the whole row to history...

How do 'move the whole row to history' - im not sure.

The reason im using this trigger is that it automatically inserts data into a single audit table from all the tables where is has been applied. I can then show this audit table to my users though a GUI.|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), ModifiedDate datetime)
CREATE TABLE myAudit99 (AuditAddDate datetime DEFAULT GetDate(), Col1 int, Col2 char(1), ModifiedDate datetime)
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR UPDATE, DELETE
AS
INSERT INTO myAudit99(Col1, Col2, ModifiedDate)
SELECT Col1, Col2, ModifiedDate FROM deleted
GO

INSERT INTO myTable99(Col2)
SELECT 'x'
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

UPDATE myTable99 SET Col2 = 'A', ModifiedDate = GetDate()
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

UPDATE myTable99 SET Col2 = 'Z', ModifiedDate = GetDate()
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

DELETE FROM myTable99 WHERE Col1 = 1
GO
SELECT * FROM myTable99
SELECT * FROM myAudit99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myAudit99
GO

Wednesday, March 7, 2012

audit single user mode

Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! Chuck
My bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck

audit single user mode

Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! ChuckMy bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck

audit single user mode

Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! ChuckMy bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck

Friday, February 24, 2012

Attribute Key Cannot Be Found: Single Underlying Table!

I've spent quite a bit of time searching this topic and have not come up with an answer that would help my situation.

I'm building a cube from a single table. That table is used to build both the attributes and measures. I sometimes get the "attribute key cannot be found" error even though queries prove that the data indeed there. Often all that needs to happen to fix the problem is to simply reprocess the cube without making any changes.

Any ideas as to how I could fix this problem? It causes a problem as the cube is scheduled and that is failing. I can't be rebuilding the cube manually each day.

Hello! Even if you have fact table dimensions this problem can be related to that you must always process the dimensions first and the rest of the cube later. If you use MOLAP for storage the dimensions will be built as separate objects even if you use a single table as source.

If you process the cube and have activated "process related objects" in the advanced settings dialogue you can process the cube and the dimensions will be processed first.

HTH

Thomas Ivarsson

|||Perfect! That you so much for your help, Thomas. That makes good sense. I've set the Integration Service project to Process Related Objects.

Thanks Again,

Robin Sarac

edit: Spelling mistakes...

Thursday, February 16, 2012

attaching DB using single MDF file

Hi there,
I'm trying to attach a DB using a single MDF file. The file was not properly detached as our server had crashed suddenly neither was the a proper backup done..When I try to attach it says
"the file you've specified is not a valid sql server database file" . But another database was restored using a MDF file without any problems ?
Please do get back if anyone may get what the problem likely is ? It'd be of great help. Let me know if my problem was clear or if I've to put it in a different fashion.

Cheers
GaneshOriginally posted by gans_sr
Hi there,
I'm trying to attach a DB using a single MDF file. The file was not properly detached as our server had crashed suddenly neither was the a proper backup done..When I try to attach it says
"the file you've specified is not a valid sql server database file" . But another database was restored using a MDF file without any problems ?
Please do get back if anyone may get what the problem likely is ? It'd be of great help. Let me know if my problem was clear or if I've to put it in a different fashion.

Cheers
Ganesh

what do u mean by
other database was restored using a MDF file without any problems ?
was it the same file which u r trying to restore again??
to me it seems that the database file is corrupted and u will have to look for the most recent backup for the database.|||Harshal,
Its not the same file and its a differnt one . I also reckon it could be corrupt, but do you think it can be repaired by any chance ?
About backup, somehting had happened to hte server it seems , may be some virus and it stopped job schedule on sql server also.
Thanks for any ideas
Ganesh|||Originally posted by gans_sr
Harshal,
Its not the same file and its a differnt one . I also reckon it could be corrupt, but do you think it can be repaired by any chance ?
About backup, somehting had happened to hte server it seems , may be some virus and it stopped job schedule on sql server also.
Thanks for any ideas
Ganesh

In that case u r in a real problem! I haven't heard of repairing a corrupt database file yet.The only solution that I see is to get the latest workable backup from the backup sets and restore them. I hope you have some backups before the job stopped running.
and also once every thing is running, do configure an alert for all the jobs so that their status is mailed to u every time they run.
regards,
Harshal.|||Thanks Harshal .Looks like thats the only option available now.

Thursday, February 9, 2012

Attach text files into sql server table

Is there a way i can attach multiple text files into a single sql server table. Or what are the options for attaching multiple text files into sql server?

Please advise.

Thanks

You can use

BULK INSERT

bcp

Import Export Wizard

See SQL Server 2005 Books Online topics:

BULK INSERT (Transact-SQL)

http://msdn2.microsoft.com/en-us/library/ms188365.aspx

bcp Utility

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

SQL Server Import and Export Wizard F1 Help

http://msdn2.microsoft.com/en-us/library/ms188032.aspx

|||

There is no way to 'attach' a text file to SQL Server. You can, however, import the data from a text file into a SQL Server table -recognizing that any modifications will NOT be available to that text file, they are only in the server table. You can also export data from a SQL Server table to a text file.

But these are not inconsequential activities.

|||I know how to import single text file data into sql server table, but i have multiple text files. So how can i import all those at once?|||

In 2005 you can use SSIS...It has a loop task inside of which you could put an import task.

You could write a script, vbscript, that looped through a directory and used the sqlcmd utility to import each file.

Attach single file (MDF) db...

I have erased LDF file. Now I'm not able to attach the relative MDF database. It WANT the rigth LDF
I'm trying to recover my db without success..
Does anyone could help me?You should not just delete the ldf file, you risk now to use your latest
backup in order to get the database back. But before trying that, you could
try sp_attach_single_file_db and see if it helps.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Giovanni BO" <giovanni.gambaro@.businessobjects.com> wrote in message
news:AAF98018-9293-4279-A421-E8E86ACAFFCE@.microsoft.com...
> I have erased LDF file. Now I'm not able to attach the relative MDF
database. It WANT the rigth LDF.
> I'm trying to recover my db without success...
> Does anyone could help me?

Attach single file (MDF) db...

I have erased LDF file. Now I'm not able to attach the relative MDF database
. It WANT the rigth LDF.
I'm trying to recover my db without success...
Does anyone could help me?You should not just delete the ldf file, you risk now to use your latest
backup in order to get the database back. But before trying that, you could
try sp_attach_single_file_db and see if it helps.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Giovanni BO" <giovanni.gambaro@.businessobjects.com> wrote in message
news:AAF98018-9293-4279-A421-E8E86ACAFFCE@.microsoft.com...
> I have erased LDF file. Now I'm not able to attach the relative MDF
database. It WANT the rigth LDF.
> I'm trying to recover my db without success...
> Does anyone could help me?

Tuesday, March 27, 2012

auto backup jobs wont run - not in single user mode

auto backup jobs wont run - not in single user mode

auto backup jobs wont run - not in single user mode

Thursday, March 8, 2012

Audit triggers problem

Wednesday, March 7, 2012

audit single user mode

audit single user mode

audit single user mode

Friday, February 24, 2012

Attribute Key Cannot Be Found: Single Underlying Table!

Thursday, February 16, 2012

attaching DB using single MDF file

Thursday, February 9, 2012

Attach text files into sql server table

Attach single file (MDF) db...

Attach single file (MDF) db...

Attach remote DB file

Blog Archive

About Me