Help Backup: master

Showing posts with label master. Show all posts

Friday, March 23, 2012

Hidden Master, Model, MSDB, and Tempdb Databases.

Thanks in advance.
I have an instance of Enterprise Manager for SQL Server 2000 on my
desktop that does not list the above databases. When I attempt to add
any of them, I receive: "Error 5123: CREATE FILE encountered operating
system error 32.". I see all the databases in the server's copy of EM.
My questions are twofold. What is the likely reason my workstation
copy of EM got this way? How do I best fix this? I suspect I may have
to wait for no users to be on the system, but I hope someone knows a
way to fix this without doing so. Also the TempDB does not appear as
an mdf file, which I guess is because it is a temporary construct that
exists in memory only. So I do not even being to know how to make that
appear in my list.
Mark Simmerman
SQL Learner
Napa, CaliforniaRight-click on the Server, select 'Edit SQL Server Registration properties
and make sure 'show system databases and system objects' is checked.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <xxxdbaxxx@.gmail.com> wrote in message
news:1148081170.238568.164540@.38g2000cwa.googlegroups.com...
> Thanks in advance.
> I have an instance of Enterprise Manager for SQL Server 2000 on my
> desktop that does not list the above databases. When I attempt to add
> any of them, I receive: "Error 5123: CREATE FILE encountered operating
> system error 32.". I see all the databases in the server's copy of EM.
>
> My questions are twofold. What is the likely reason my workstation
> copy of EM got this way? How do I best fix this? I suspect I may have
> to wait for no users to be on the system, but I hope someone knows a
> way to fix this without doing so. Also the TempDB does not appear as
> an mdf file, which I guess is because it is a temporary construct that
> exists in memory only. So I do not even being to know how to make that
> appear in my list.
>
> Mark Simmerman
> SQL Learner
> Napa, California
>|||Except for MSDB, those system databases MUST exist on your server, so trying
to 'add' them is pointless. They're already there which is why you're
getting the message you reported.
I presume you also have Query Analyzer, so you could run a query:
exec sp_helpdb
and you should see the system databases listed with all the others.
Right click on the name of your server (or LOCAL) in Enterprise Manager.
Choose 'Edit SQL Server Registration Properties'
Check the box near the button of the dialog box that says 'Show system
databases and system objects'
Click OK
Now when you expand your Databases node in Enterprise Manager, you should
see the system databases.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mark" <xxxdbaxxx@.gmail.com> wrote in message
news:1148081170.238568.164540@.38g2000cwa.googlegroups.com...
> Thanks in advance.
> I have an instance of Enterprise Manager for SQL Server 2000 on my
> desktop that does not list the above databases. When I attempt to add
> any of them, I receive: "Error 5123: CREATE FILE encountered operating
> system error 32.". I see all the databases in the server's copy of EM.
>
> My questions are twofold. What is the likely reason my workstation
> copy of EM got this way? How do I best fix this? I suspect I may have
> to wait for no users to be on the system, but I hope someone knows a
> way to fix this without doing so. Also the TempDB does not appear as
> an mdf file, which I guess is because it is a temporary construct that
> exists in memory only. So I do not even being to know how to make that
> appear in my list.
>
> Mark Simmerman
> SQL Learner
> Napa, California
>|||That was sooooo simple. Thanks, Kalen and Dan.

Wednesday, March 21, 2012

hi need help

hi all......,

in mssql 2000 there's this code
Declare LockObj_Cur CURSOR Fast_FORWARD FOR
Select id, dbId, type
FROM master..SysLocks (NOLOCK)
WHERE spId = @.spId
AND type in (5, 8, 10, 11)

how do i proceed in mssql 2005 so that i can get the same info as given by above piece of code,
i have found out that sys.dm_tran_locks has to be used but i wasnot able to find equivalent of "type" column of syslocks table of mssql 2000 in sys.dm_tran_locks
plz help

According the 2000 BOL, syslocks was renamed after 6.5 to syslockinfo. And the rsc_type column types only went up to 10. Can you state what you are actually looking for?

On my blog, I have the query that I use all of the time (http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!906.entry#postcns!80677FB08B3162E4!906):

select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,

request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then

(select object_name(object_id) from sys.partitions

where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid

But I haven't documented what every column of the dmv's have yet (I am actually working on a project to do so.)

Louis

sql

Monday, March 19, 2012

Hi

Hi
Can anybody tell me what are the possible causes when a client update request to a server fails..
Some of the ones i suppose r
1 Master server down
2 Link Failure
3 Update Request query syntax invalid
Any more.............Login does not exist - permissions issue, database offline/suspect/corruption, locking issues...|||I read your other post about replication concerns - do you have problems now ?|||Hi

thnx a lot sir for responding to my second query...as far as the first is concened i am still facing the same problems...

I understand the concept of master n slave relationship but have certain queries regarding this...

1) In case the master is down i do i configure my sysem so that a present active slave becomes the master or any other way to tackle this situation.How does the clients know about this new server being set as the master .

2)The master is run as a myslq demon . Now the slaves though being servers ..are run as clients of the master server with a definate usernme who has been granted REPLICATION SLAVE PERMISSion set at the master server??

i.e do i run the slaves as mysql -h xxx -u xxxx -p ?? --mysql client ?

3)Is it necessary t take a snapshot of the datadir at the serer , for the first time i set up replication ?/?

Kindly help me out
Thnx
Varun

Friday, March 9, 2012

HELP--cannot shrink the transaction log

Hello:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sYou specify the *logical name of the file*. See the sysfiles table. Also, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> Hello:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s|||I have done both of those things--no success...
PLEASE HELP!!!
childofthe1980s
"Tibor Karaszi" wrote:
> You specify the *logical name of the file*. See the sysfiles table. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> > Hello:
> >
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
> > What is the EXACT syntax? You see, the name of the database is JC and the
> > log file is named GPSJClog.ldf. I have tried to place both names in the
> > syntax of the statement but with no success. I keep getting a message saying
> > that the file name is not in sysfiles.
> >
> > This is what I have tried:
> >
> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> > Please help!
> >
> > childofthe1980s
>|||Can you post the commands you tried to execute?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>I have done both of those things--no success...
> PLEASE HELP!!!
> childofthe1980s
> "Tibor Karaszi" wrote:
>> You specify the *logical name of the file*. See the sysfiles table. Also, see
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
>> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> > Hello:
>> >
>> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>> > analyzer in SQL 2000 against the master database.
>> >
>> > What is the EXACT syntax? You see, the name of the database is JC and the
>> > log file is named GPSJClog.ldf. I have tried to place both names in the
>> > syntax of the statement but with no success. I keep getting a message saying
>> > that the file name is not in sysfiles.
>> >
>> > This is what I have tried:
>> >
>> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> > Please help!
>> >
>> > childofthe1980s
>>|||DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
"Tibor Karaszi" wrote:
> Can you post the commands you tried to execute?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
> >I have done both of those things--no success...
> >
> > PLEASE HELP!!!
> >
> > childofthe1980s
> >
> > "Tibor Karaszi" wrote:
> >
> >> You specify the *logical name of the file*. See the sysfiles table. Also, see
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> >> > Hello:
> >> >
> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >> > analyzer in SQL 2000 against the master database.
> >> >
> >> > What is the EXACT syntax? You see, the name of the database is JC and the
> >> > log file is named GPSJClog.ldf. I have tried to place both names in the
> >> > syntax of the statement but with no success. I keep getting a message saying
> >> > that the file name is not in sysfiles.
> >> >
> >> > This is what I have tried:
> >> >
> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> > Please help!
> >> >
> >> > childofthe1980s
> >>
> >>
>|||Where you IN the database in question? I believe that is required.
Roy Harvey
Beacon Falls, CT
On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
<childofthe1980s@.discussions.microsoft.com> wrote:
>Hello:
>I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>analyzer in SQL 2000 against the master database.
>What is the EXACT syntax? You see, the name of the database is JC and the
>log file is named GPSJClog.ldf. I have tried to place both names in the
>syntax of the statement but with no success. I keep getting a message saying
>that the file name is not in sysfiles.
>This is what I have tried:
>DBCC SHRINKFILE (GPJClog.ldf , 2)
>Please help!
>childofthe1980s|||Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"Roy Harvey" wrote:
> Where you IN the database in question? I believe that is required.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> <childofthe1980s@.discussions.microsoft.com> wrote:
> >Hello:
> >
> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >analyzer in SQL 2000 against the master database.
> >
> >What is the EXACT syntax? You see, the name of the database is JC and the
> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >syntax of the statement but with no success. I keep getting a message saying
> >that the file name is not in sysfiles.
> >
> >This is what I have tried:
> >
> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> >Please help!
> >
> >childofthe1980s
>|||You needed to use the Logical name not the physical one. Drop the .ldf and
you should be golden.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:C3E65A22-82FF-405A-8EC9-B770E5206E4E@.microsoft.com...
> DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
>
> "Tibor Karaszi" wrote:
>> Can you post the commands you tried to execute?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message
>> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>> >I have done both of those things--no success...
>> >
>> > PLEASE HELP!!!
>> >
>> > childofthe1980s
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> You specify the *logical name of the file*. See the sysfiles table.
>> >> Also, see
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> >> > Hello:
>> >> >
>> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE
>> >> > in query
>> >> > analyzer in SQL 2000 against the master database.
>> >> >
>> >> > What is the EXACT syntax? You see, the name of the database is JC
>> >> > and the
>> >> > log file is named GPSJClog.ldf. I have tried to place both names in
>> >> > the
>> >> > syntax of the statement but with no success. I keep getting a
>> >> > message saying
>> >> > that the file name is not in sysfiles.
>> >> >
>> >> > This is what I have tried:
>> >> >
>> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> > Please help!
>> >> >
>> >> > childofthe1980s
>> >>
>> >>
>>|||Not true. You can use either the ID or the LOGICAL name not the physical
name. And hopefully the db is not in FULL recovery mode or you just lost the
log chain with that truncate.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
> it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
>
>
> "Roy Harvey" wrote:
>> Where you IN the database in question? I believe that is required.
>> Roy Harvey
>> Beacon Falls, CT
>> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >Hello:
>> >
>> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >query
>> >analyzer in SQL 2000 against the master database.
>> >
>> >What is the EXACT syntax? You see, the name of the database is JC and
>> >the
>> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >syntax of the statement but with no success. I keep getting a message
>> >saying
>> >that the file name is not in sysfiles.
>> >
>> >This is what I have tried:
>> >
>> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> >Please help!
>> >
>> >childofthe1980s|||No, Andrew, I tried using the logical file name without the ldf and it did
not work either. So, yes, the documentation needs updating.
"Andrew J. Kelly" wrote:
> Not true. You can use either the ID or the LOGICAL name not the physical
> name. And hopefully the db is not in FULL recovery mode or you just lost the
> log chain with that truncate.
> --
> Andrew J. Kelly SQL MVP
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> > Nevermind. I figured it out.
> >
> > I had to run the following scripts against each of the databases:
> >
> > BACKUP LOG JC with TRUNCATE_ONLY
> > DBCC SHRINKFILE (2, 20)
> >
> > You see, the support documentation on the web did not say that you have to
> > take the fileID of the database--2 in the syntax above-- in order to make
> > it
> > work! You get the number 2 from running the following query against the
> > database:
> >
> > select * from sysfiles
> >
> > It would have been nice if this had been in the documentation!
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
> > "Roy Harvey" wrote:
> >
> >> Where you IN the database in question? I believe that is required.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> >> <childofthe1980s@.discussions.microsoft.com> wrote:
> >>
> >> >Hello:
> >> >
> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
> >> >query
> >> >analyzer in SQL 2000 against the master database.
> >> >
> >> >What is the EXACT syntax? You see, the name of the database is JC and
> >> >the
> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >> >syntax of the statement but with no success. I keep getting a message
> >> >saying
> >> >that the file name is not in sysfiles.
> >> >
> >> >This is what I have tried:
> >> >
> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> >Please help!
> >> >
> >> >childofthe1980s
> >>
>
>|||> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
The documentation clearly states that you can use *either* the logical filename or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical filename. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usage of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
USE master
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:04A6393F-154E-4A1A-946B-0768F7B47F85@.microsoft.com...
> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
> "Andrew J. Kelly" wrote:
>> Not true. You can use either the ID or the LOGICAL name not the physical
>> name. And hopefully the db is not in FULL recovery mode or you just lost the
>> log chain with that truncate.
>> --
>> Andrew J. Kelly SQL MVP
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
>> > Nevermind. I figured it out.
>> >
>> > I had to run the following scripts against each of the databases:
>> >
>> > BACKUP LOG JC with TRUNCATE_ONLY
>> > DBCC SHRINKFILE (2, 20)
>> >
>> > You see, the support documentation on the web did not say that you have to
>> > take the fileID of the database--2 in the syntax above-- in order to make
>> > it
>> > work! You get the number 2 from running the following query against the
>> > database:
>> >
>> > select * from sysfiles
>> >
>> > It would have been nice if this had been in the documentation!
>> >
>> > Thanks!
>> >
>> > childofthe1980s
>> >
>> >
>> >
>> >
>> >
>> > "Roy Harvey" wrote:
>> >
>> >> Where you IN the database in question? I believe that is required.
>> >>
>> >> Roy Harvey
>> >> Beacon Falls, CT
>> >>
>> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> >> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hello:
>> >> >
>> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >> >query
>> >> >analyzer in SQL 2000 against the master database.
>> >> >
>> >> >What is the EXACT syntax? You see, the name of the database is JC and
>> >> >the
>> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >> >syntax of the statement but with no success. I keep getting a message
>> >> >saying
>> >> >that the file name is not in sysfiles.
>> >> >
>> >> >This is what I have tried:
>> >> >
>> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> >Please help!
>> >> >
>> >> >childofthe1980s
>> >>
>>

HELP--cannot shrink the transaction log

Wednesday, March 7, 2012

Help: trouble dropping master key

It could be trivial question.

I have trouble dropping master key. when i execute the following query..

open master key decryption by password = 'pass1234$'

alter master key drop encryption by password = 'pass1234$'

close master key

drop master key

I get the following result..

Msg 15558, Level 16, State 1, Line 3

Cannot drop encryption by password '********'.

Msg 15580, Level 16, State 1, Line 5

Cannot drop master key because certificate 'BackofficeServiceAccount' is encrypted by it.

But 'BackofficeServiceAccount' was already dropped.

Can any one help me how to drop master key?

I appreciate any input..

regards...

I realized the script was creating login and certificate with the same name.

Dropping the certificate first, helped dropping the master key...

Monday, February 27, 2012

HELP: problem with using xp_sendmail

below is code snippet (which im assuming is the cause of the problem) from a certain stored procedure

EXEC Master.dbo.xp_sendmail
@.dbuse = 'DanoneSSS',
@.recipients = 'xxx@.yyy.co.jp',
@.subject = 'mmm nnn',
@.message = @.parMessage,
@.query = 'SELECT CHECK_JS_CODE, CHECK_TYPE FROM T_CHECK_LOG WHERE CHECK_STATUS = 0 ORDER BY CHECK_ID',
@.attachments = 'ResultSet.txt',
@.attach_results = TRUE,
@.no_header = TRUE,
@.separator = '',
@.ansi_attachment = TRUE

as in my case, when i execute this particular stored procedure, SQL Server sends me the email AND with the necessary attachment

however, when i create a Job with "EXEC dbo.SOSOStoredProcedure," though the Job executes successfully, no EMAIL is sent to me.

the funny thing is, when i comment out the following lines

@.query = 'SELECT CHECK_JS_CODE, CHECK_TYPE FROM T_CHECK_LOG WHERE CHECK_STATUS = 0 ORDER BY CHECK_ID',
@.attachments = 'ResultSet.txt',
@.attach_results = TRUE,
@.no_header = TRUE,
@.separator = '',
@.ansi_attachment = TRUE

the Job executes successfully with an EMAIL being sent to me

does SQL Server's job CANNOT handle xp_sendmail that returns a RESULT set? or i may be mistaken somewhere...

thanks!Who is the owner of the job and what is the permission level ?|||Originally posted by rnealejr
Who is the owner of the job and what is the permission level ?

thanks for the reply...

but i did getting it working though...

by carefully reading through BO regarding xp_sendmail, it seems that in using xp_sendmail to send file attachments, the specific procedure must be defined in the master database. this is what i have done.

then, defined a permission level for the procedure's properties, and the specific entry in SQL Agent.

i may have gotten it working... but there still may be a gap in my understanding, and i would appreciate if anyone could check on this - Books Online could get so scarce in regards to explanations and examples.

again, thanks...|||the specific procedure must be defined in the master database

This is only done if the sa wants to conceal the behavior of xp_sendmail.
Which if that were the case you would have been unsuccessful regardless of where you ran the stored procedure.

then, defined a permission level for the procedure's properties, and the specific entry in SQL Agent.

Can you elaborate ?|||Alter the SP.
In the same sp, use exec master..xp_cmdshell 'isql "Your query" -o drive:\filename.txt etc'
Then remove the query portion from xp_sendmail.
Run the job again. It would work.|||I beleive (rightly or wrongly) that you are unable to run an open query within an xp_sendmail.

I am trying to run a stored procedure with an open query within it. wrapping it within an xp_sendmail.

Which fails.

Any sugestions on how to get this running.

Thanks

Sunday, February 19, 2012

HELP...Splitting a string in T-SQL

I have a situation where I am querying the master.dbo.sysaltfiles to return
the path to the datafiles. What I am really interested in is the path...not
the filenames.
Example:
select @.DataPath = FileName From master.dbo.sysaltfiles WHERE name =
@.CurrentDB
This returns: M:\Microsoft SQL Server\CurrentDB.mdf
What I need is just the path:
Example:
M:\Microsoft SQL Server\
I was looking for something like VB's split string or something to remove
the filename.
What can I do to achieve the desired result?
Thanks!
RonThere are plenty of string functions in T-SQL that will help with this.
Check out REVERSE, CHARINDEX and SUBSTRING.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:%23Yp4navNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> I have a situation where I am querying the master.dbo.sysaltfiles to
> return the path to the datafiles. What I am really interested in is the
> path...not the filenames.
> Example:
> select @.DataPath = FileName From master.dbo.sysaltfiles WHERE name =
> @.CurrentDB
> This returns: M:\Microsoft SQL Server\CurrentDB.mdf
>
> What I need is just the path:
> Example:
> M:\Microsoft SQL Server\
> I was looking for something like VB's split string or something to remove
> the filename.
> What can I do to achieve the desired result?
>
> Thanks!
> Ron
>|||Excellent. If possible could you give me a sample of how to use them to
achieve what I'm going for?
Thanks,
Ron
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OY9JLgvNGHA.3984@.TK2MSFTNGP14.phx.gbl...
> There are plenty of string functions in T-SQL that will help with this.
> Check out REVERSE, CHARINDEX and SUBSTRING.
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:%23Yp4navNGHA.2884@.TK2MSFTNGP12.phx.gbl...
>|||Here is an example
declare @.c varchar(50)
select @.c ='M:\Microsoft SQL Server\CurrentDB.mdf'
select left(@.c,(len(@.C) -CHARINDEX('',reverse(@.c)))+1)
Just replace @.c with your field name
http://sqlservercode.blogspot.com/|||Thank you so much!
I'm under the gun so I needed to "learn" quickly. I appreciate your help!
Ron
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1140535404.793871.132130@.g47g2000cwa.googlegroups.com...
> Here is an example
> declare @.c varchar(50)
> select @.c ='M:\Microsoft SQL Server\CurrentDB.mdf'
> select left(@.c,(len(@.C) -CHARINDEX('',reverse(@.c)))+1)
> Just replace @.c with your field name
> http://sqlservercode.blogspot.com/
>|||Hi There,
I hope this help to solve the problem.
Select identity(int,1,1) Seq into Seq From sysobjects
Select * from seq
Select Substring(String,1,Max(seq)) From
(
Select * From Seq ,(
Select 'c:\aa\bb\cc\dd\ee' As String) S
Where substring(String,seq,1) = '\'
) SS Group By String
Drop Table Seq
With Warm regards
Jatinder Singh

Friday, March 23, 2012

Hidden Master, Model, MSDB, and Tempdb Databases.

Wednesday, March 21, 2012

hi need help

Monday, March 19, 2012

Hi

Friday, March 9, 2012

HELP--cannot shrink the transaction log

HELP--cannot shrink the transaction log

HELP--cannot shrink the transaction log

Wednesday, March 7, 2012

Help: trouble dropping master key

Monday, February 27, 2012

HELP: problem with using xp_sendmail

Sunday, February 19, 2012

HELP...Splitting a string in T-SQL

Help Backup

Blog Archive

About Me