I have installed this software:
- Windows Server 2003, Enterprise Edition.
- SQL Server 2005, Client tools (Server is in an another machine)
- VirusScan Enterprise 8.0.0
When i execute the next query:
USE AdventureWorks
SELECT * FROM Person.Contact
WHERE ContactID = 1 FOR XML AUTO
VirusScan report me this:
Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
Detected As: Exploit-ObscuredHtml
Detection Type: Trojan
Application: SqlWb.exe
And SQL Server Management Studio:
An error occurred while executing batch. Error message is: Invalid calling
sequence: file stream must be initialized first.
*****
I apreciate any sugestion !
Thank you.I turn off the antivirus and the query work,
I reported the problem to McAfee...
"Ubeimar Vergara" wrote:
> I have installed this software:
> - Windows Server 2003, Enterprise Edition.
> - SQL Server 2005, Client tools (Server is in an another machine)
> - VirusScan Enterprise 8.0.0
> When i execute the next query:
> USE AdventureWorks
> SELECT * FROM Person.Contact
> WHERE ContactID = 1 FOR XML AUTO
> VirusScan report me this:
> Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
> Detected As: Exploit-ObscuredHtml
> Detection Type: Trojan
> Application: SqlWb.exe
> And SQL Server Management Studio:
> An error occurred while executing batch. Error message is: Invalid calling
> sequence: file stream must be initialized first.
> *****
> I apreciate any sugestion !
> Thank you.
Showing posts with label enterprise. Show all posts
Showing posts with label enterprise. Show all posts
Tuesday, March 20, 2012
BUG: using FOR XML AUTO
I have installed this software:
- Windows Server 2003, Enterprise Edition.
- SQL Server 2005, Client tools (Server is in an another machine)
- VirusScan Enterprise 8.0.0
When i execute the next query:
USE AdventureWorks
SELECT * FROM Person.Contact
WHERE ContactID = 1 FOR XML AUTO
VirusScan report me this:
Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
Detected As: Exploit-ObscuredHtml
Detection Type: Trojan
Application: SqlWb.exe
And SQL Server Management Studio:
An error occurred while executing batch. Error message is: Invalid calling
sequence: file stream must be initialized first.
*****
I apreciate any sugestion !
Thank you.TURN OFF anti-virus software on the server!!
A properly configured SQL Server 'should' not need anti-virus software
installed on the server.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ubeimar Vergara" <Ubeimar Vergara@.discussions.microsoft.com> wrote in
message news:9CD7E5A0-C308-44C6-A3EA-A0B4CC2D346F@.microsoft.com...
>I have installed this software:
> - Windows Server 2003, Enterprise Edition.
> - SQL Server 2005, Client tools (Server is in an another machine)
> - VirusScan Enterprise 8.0.0
> When i execute the next query:
> USE AdventureWorks
> SELECT * FROM Person.Contact
> WHERE ContactID = 1 FOR XML AUTO
> VirusScan report me this:
> Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
> Detected As: Exploit-ObscuredHtml
> Detection Type: Trojan
> Application: SqlWb.exe
> And SQL Server Management Studio:
> An error occurred while executing batch. Error message is: Invalid calling
> sequence: file stream must be initialized first.
> *****
> I apreciate any sugestion !
> Thank you.|||"Ubeimar Vergara" <Ubeimar Vergara@.discussions.microsoft.com> wrote in
message news:9CD7E5A0-C308-44C6-A3EA-A0B4CC2D346F@.microsoft.com...
>I have installed this software:
> - Windows Server 2003, Enterprise Edition.
> - SQL Server 2005, Client tools (Server is in an another machine)
> - VirusScan Enterprise 8.0.0
> When i execute the next query:
> USE AdventureWorks
> SELECT * FROM Person.Contact
> WHERE ContactID = 1 FOR XML AUTO
> VirusScan report me this:
> Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
> Detected As: Exploit-ObscuredHtml
> Detection Type: Trojan
> Application: SqlWb.exe
> And SQL Server Management Studio:
> An error occurred while executing batch. Error message is: Invalid calling
> sequence: file stream must be initialized first.
>
Sounds like a bug in your Antivirus program interfering with the operation
of Management Studio on your client computer.
David|||I turn off the antivirus and the query work,
I reported the problem to McAfee...
"Ubeimar Vergara" wrote:
> I have installed this software:
> - Windows Server 2003, Enterprise Edition.
> - SQL Server 2005, Client tools (Server is in an another machine)
> - VirusScan Enterprise 8.0.0
> When i execute the next query:
> USE AdventureWorks
> SELECT * FROM Person.Contact
> WHERE ContactID = 1 FOR XML AUTO
> VirusScan report me this:
> Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
> Detected As: Exploit-ObscuredHtml
> Detection Type: Trojan
> Application: SqlWb.exe
> And SQL Server Management Studio:
> An error occurred while executing batch. Error message is: Invalid calling
> sequence: file stream must be initialized first.
> *****
> I apreciate any sugestion !
> Thank you.sql
- Windows Server 2003, Enterprise Edition.
- SQL Server 2005, Client tools (Server is in an another machine)
- VirusScan Enterprise 8.0.0
When i execute the next query:
USE AdventureWorks
SELECT * FROM Person.Contact
WHERE ContactID = 1 FOR XML AUTO
VirusScan report me this:
Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
Detected As: Exploit-ObscuredHtml
Detection Type: Trojan
Application: SqlWb.exe
And SQL Server Management Studio:
An error occurred while executing batch. Error message is: Invalid calling
sequence: file stream must be initialized first.
*****
I apreciate any sugestion !
Thank you.TURN OFF anti-virus software on the server!!
A properly configured SQL Server 'should' not need anti-virus software
installed on the server.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ubeimar Vergara" <Ubeimar Vergara@.discussions.microsoft.com> wrote in
message news:9CD7E5A0-C308-44C6-A3EA-A0B4CC2D346F@.microsoft.com...
>I have installed this software:
> - Windows Server 2003, Enterprise Edition.
> - SQL Server 2005, Client tools (Server is in an another machine)
> - VirusScan Enterprise 8.0.0
> When i execute the next query:
> USE AdventureWorks
> SELECT * FROM Person.Contact
> WHERE ContactID = 1 FOR XML AUTO
> VirusScan report me this:
> Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
> Detected As: Exploit-ObscuredHtml
> Detection Type: Trojan
> Application: SqlWb.exe
> And SQL Server Management Studio:
> An error occurred while executing batch. Error message is: Invalid calling
> sequence: file stream must be initialized first.
> *****
> I apreciate any sugestion !
> Thank you.|||"Ubeimar Vergara" <Ubeimar Vergara@.discussions.microsoft.com> wrote in
message news:9CD7E5A0-C308-44C6-A3EA-A0B4CC2D346F@.microsoft.com...
>I have installed this software:
> - Windows Server 2003, Enterprise Edition.
> - SQL Server 2005, Client tools (Server is in an another machine)
> - VirusScan Enterprise 8.0.0
> When i execute the next query:
> USE AdventureWorks
> SELECT * FROM Person.Contact
> WHERE ContactID = 1 FOR XML AUTO
> VirusScan report me this:
> Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
> Detected As: Exploit-ObscuredHtml
> Detection Type: Trojan
> Application: SqlWb.exe
> And SQL Server Management Studio:
> An error occurred while executing batch. Error message is: Invalid calling
> sequence: file stream must be initialized first.
>
Sounds like a bug in your Antivirus program interfering with the operation
of Management Studio on your client computer.
David|||I turn off the antivirus and the query work,
I reported the problem to McAfee...
"Ubeimar Vergara" wrote:
> I have installed this software:
> - Windows Server 2003, Enterprise Edition.
> - SQL Server 2005, Client tools (Server is in an another machine)
> - VirusScan Enterprise 8.0.0
> When i execute the next query:
> USE AdventureWorks
> SELECT * FROM Person.Contact
> WHERE ContactID = 1 FOR XML AUTO
> VirusScan report me this:
> Pathname: C:\Documents and Settings\<user>\Local Settings\Temp\tmp5B.tmp
> Detected As: Exploit-ObscuredHtml
> Detection Type: Trojan
> Application: SqlWb.exe
> And SQL Server Management Studio:
> An error occurred while executing batch. Error message is: Invalid calling
> sequence: file stream must be initialized first.
> *****
> I apreciate any sugestion !
> Thank you.sql
BUG: Cannot create subscription as NT AUTHORITY\NETWORK SERVICE
Platform:
Windows 2003 Server Enterprise Edition
SQL Server 2000 Developer Edition SP 3
Reporting Services 2000 Developer Edition SP 2
Hi,
I believe I have encountered a bug in Reporting Services and SQL Server.
To reproduce:
Set the Reporting Service Web Service to run as NT AUTHORITY\NETWORK SERVICE
(I believe this is the default on Windows 2003 server as opposed to the
local ASPNET account) using rsconfig.exe, et al.
Make sure NT AUTHORITY\NETWORK SERVICE has RSExecRole privilege on the
Report Server database, Report Server temp database, master, and msdb.
Then try and create a report subscription. I receive this error:
An internal error occurred on the report server. See the error log for more
details. (rsInternalError) The specified '@.owner_login_name' is invalid
(valid values are returned by sp_helplogins[excluding Windows NT groups]).
My guess is that when the Report Server tries to create a subscription it
does so by invoking sp_add_job/sp_verify_job, this then fails because NT
AUTHORITY\NETWORK SERVICE is a Windows Group. I believe the actual error
above is thrown by the stored procedure, sp_verify_job:
Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
The specified '@.owner_login_name' is invalid (valid values are returned by
sp_helplogins [excluding Windows NT groups]).
or
From sp_verify_job:
-- Now just check that the login id is valid (ie. it exists and isn't an NT
group)
IF (@.owner_sid IS NULL) OR (EXISTS (SELECT *
FROM master.dbo.syslogins
WHERE (sid = @.owner_sid)
AND (isntgroup <> 0)))
BEGIN
-- NOTE: In the following message we quote @.owner_login_name instead of
@.owner_sid
-- since this is the parameter the user passed to the calling SP
(ie. either
-- sp_add_job or sp_update_job)
SELECT @.res_valid_range = FORMATMESSAGE(14203)
RAISERROR(14234, -1, -1, '@.owner_login_name', @.res_valid_range)
RETURN(1) -- Failure
END
Thanks,
Bryan"BDB" <bdb@.reply.to.group.com> wrote in message
news:OJi2X7QnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> Platform:
> Windows 2003 Server Enterprise Edition
> SQL Server 2000 Developer Edition SP 3
> Reporting Services 2000 Developer Edition SP 2
> Hi,
> I believe I have encountered a bug in Reporting Services and SQL Server.
> To reproduce:
> Set the Reporting Service Web Service to run as NT AUTHORITY\NETWORK
> SERVICE (I believe this is the default on Windows 2003 server as opposed
> to the local ASPNET account) using rsconfig.exe, et al.
> Make sure NT AUTHORITY\NETWORK SERVICE has RSExecRole privilege on the
> Report Server database, Report Server temp database, master, and msdb.
> Then try and create a report subscription. I receive this error:
> An internal error occurred on the report server. See the error log for
> more details. (rsInternalError) The specified '@.owner_login_name' is
> invalid (valid values are returned by sp_helplogins[excluding Windows NT
> groups]).
>
> My guess is that when the Report Server tries to create a subscription it
> does so by invoking sp_add_job/sp_verify_job, this then fails because NT
> AUTHORITY\NETWORK SERVICE is a Windows Group. I believe the actual error
> above is thrown by the stored procedure, sp_verify_job:
> Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
> The specified '@.owner_login_name' is invalid (valid values are returned by
> sp_helplogins [excluding Windows NT groups]).
> or
> From sp_verify_job:
> -- Now just check that the login id is valid (ie. it exists and isn't an
> NT group)
> IF (@.owner_sid IS NULL) OR (EXISTS (SELECT *
> FROM master.dbo.syslogins
> WHERE (sid = @.owner_sid)
> AND (isntgroup <> 0)))
> BEGIN
> -- NOTE: In the following message we quote @.owner_login_name instead of
> @.owner_sid
> -- since this is the parameter the user passed to the calling SP
> (ie. either
> -- sp_add_job or sp_update_job)
> SELECT @.res_valid_range = FORMATMESSAGE(14203)
> RAISERROR(14234, -1, -1, '@.owner_login_name', @.res_valid_range)
> RETURN(1) -- Failure
> END
>
> Thanks,
> Bryan
>
This bug was fixed (hacked, IMO) in SQL Server 2000 SP4. I have no idea if
a KB article was ever published.|||We've applied SP4 for SQL and still have the error. How did you "fix" it?
--
Thanks,
CGW
"BDB" wrote:
> "BDB" <bdb@.reply.to.group.com> wrote in message
> news:OJi2X7QnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> > Platform:
> > Windows 2003 Server Enterprise Edition
> > SQL Server 2000 Developer Edition SP 3
> > Reporting Services 2000 Developer Edition SP 2
> >
> > Hi,
> >
> > I believe I have encountered a bug in Reporting Services and SQL Server.
> >
> > To reproduce:
> >
> > Set the Reporting Service Web Service to run as NT AUTHORITY\NETWORK
> > SERVICE (I believe this is the default on Windows 2003 server as opposed
> > to the local ASPNET account) using rsconfig.exe, et al.
> >
> > Make sure NT AUTHORITY\NETWORK SERVICE has RSExecRole privilege on the
> > Report Server database, Report Server temp database, master, and msdb.
> >
> > Then try and create a report subscription. I receive this error:
> >
> > An internal error occurred on the report server. See the error log for
> > more details. (rsInternalError) The specified '@.owner_login_name' is
> > invalid (valid values are returned by sp_helplogins[excluding Windows NT
> > groups]).
> >
> >
> >
> > My guess is that when the Report Server tries to create a subscription it
> > does so by invoking sp_add_job/sp_verify_job, this then fails because NT
> > AUTHORITY\NETWORK SERVICE is a Windows Group. I believe the actual error
> > above is thrown by the stored procedure, sp_verify_job:
> >
> > Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
> > The specified '@.owner_login_name' is invalid (valid values are returned by
> > sp_helplogins [excluding Windows NT groups]).
> >
> > or
> >
> > From sp_verify_job:
> >
> > -- Now just check that the login id is valid (ie. it exists and isn't an
> > NT group)
> > IF (@.owner_sid IS NULL) OR (EXISTS (SELECT *
> > FROM master.dbo.syslogins
> > WHERE (sid = @.owner_sid)
> > AND (isntgroup <> 0)))
> > BEGIN
> > -- NOTE: In the following message we quote @.owner_login_name instead of
> > @.owner_sid
> > -- since this is the parameter the user passed to the calling SP
> > (ie. either
> > -- sp_add_job or sp_update_job)
> > SELECT @.res_valid_range = FORMATMESSAGE(14203)
> > RAISERROR(14234, -1, -1, '@.owner_login_name', @.res_valid_range)
> > RETURN(1) -- Failure
> > END
> >
> >
> > Thanks,
> > Bryan
> >
> This bug was fixed (hacked, IMO) in SQL Server 2000 SP4. I have no idea if
> a KB article was ever published.
>
>
Windows 2003 Server Enterprise Edition
SQL Server 2000 Developer Edition SP 3
Reporting Services 2000 Developer Edition SP 2
Hi,
I believe I have encountered a bug in Reporting Services and SQL Server.
To reproduce:
Set the Reporting Service Web Service to run as NT AUTHORITY\NETWORK SERVICE
(I believe this is the default on Windows 2003 server as opposed to the
local ASPNET account) using rsconfig.exe, et al.
Make sure NT AUTHORITY\NETWORK SERVICE has RSExecRole privilege on the
Report Server database, Report Server temp database, master, and msdb.
Then try and create a report subscription. I receive this error:
An internal error occurred on the report server. See the error log for more
details. (rsInternalError) The specified '@.owner_login_name' is invalid
(valid values are returned by sp_helplogins[excluding Windows NT groups]).
My guess is that when the Report Server tries to create a subscription it
does so by invoking sp_add_job/sp_verify_job, this then fails because NT
AUTHORITY\NETWORK SERVICE is a Windows Group. I believe the actual error
above is thrown by the stored procedure, sp_verify_job:
Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
The specified '@.owner_login_name' is invalid (valid values are returned by
sp_helplogins [excluding Windows NT groups]).
or
From sp_verify_job:
-- Now just check that the login id is valid (ie. it exists and isn't an NT
group)
IF (@.owner_sid IS NULL) OR (EXISTS (SELECT *
FROM master.dbo.syslogins
WHERE (sid = @.owner_sid)
AND (isntgroup <> 0)))
BEGIN
-- NOTE: In the following message we quote @.owner_login_name instead of
@.owner_sid
-- since this is the parameter the user passed to the calling SP
(ie. either
-- sp_add_job or sp_update_job)
SELECT @.res_valid_range = FORMATMESSAGE(14203)
RAISERROR(14234, -1, -1, '@.owner_login_name', @.res_valid_range)
RETURN(1) -- Failure
END
Thanks,
Bryan"BDB" <bdb@.reply.to.group.com> wrote in message
news:OJi2X7QnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> Platform:
> Windows 2003 Server Enterprise Edition
> SQL Server 2000 Developer Edition SP 3
> Reporting Services 2000 Developer Edition SP 2
> Hi,
> I believe I have encountered a bug in Reporting Services and SQL Server.
> To reproduce:
> Set the Reporting Service Web Service to run as NT AUTHORITY\NETWORK
> SERVICE (I believe this is the default on Windows 2003 server as opposed
> to the local ASPNET account) using rsconfig.exe, et al.
> Make sure NT AUTHORITY\NETWORK SERVICE has RSExecRole privilege on the
> Report Server database, Report Server temp database, master, and msdb.
> Then try and create a report subscription. I receive this error:
> An internal error occurred on the report server. See the error log for
> more details. (rsInternalError) The specified '@.owner_login_name' is
> invalid (valid values are returned by sp_helplogins[excluding Windows NT
> groups]).
>
> My guess is that when the Report Server tries to create a subscription it
> does so by invoking sp_add_job/sp_verify_job, this then fails because NT
> AUTHORITY\NETWORK SERVICE is a Windows Group. I believe the actual error
> above is thrown by the stored procedure, sp_verify_job:
> Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
> The specified '@.owner_login_name' is invalid (valid values are returned by
> sp_helplogins [excluding Windows NT groups]).
> or
> From sp_verify_job:
> -- Now just check that the login id is valid (ie. it exists and isn't an
> NT group)
> IF (@.owner_sid IS NULL) OR (EXISTS (SELECT *
> FROM master.dbo.syslogins
> WHERE (sid = @.owner_sid)
> AND (isntgroup <> 0)))
> BEGIN
> -- NOTE: In the following message we quote @.owner_login_name instead of
> @.owner_sid
> -- since this is the parameter the user passed to the calling SP
> (ie. either
> -- sp_add_job or sp_update_job)
> SELECT @.res_valid_range = FORMATMESSAGE(14203)
> RAISERROR(14234, -1, -1, '@.owner_login_name', @.res_valid_range)
> RETURN(1) -- Failure
> END
>
> Thanks,
> Bryan
>
This bug was fixed (hacked, IMO) in SQL Server 2000 SP4. I have no idea if
a KB article was ever published.|||We've applied SP4 for SQL and still have the error. How did you "fix" it?
--
Thanks,
CGW
"BDB" wrote:
> "BDB" <bdb@.reply.to.group.com> wrote in message
> news:OJi2X7QnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> > Platform:
> > Windows 2003 Server Enterprise Edition
> > SQL Server 2000 Developer Edition SP 3
> > Reporting Services 2000 Developer Edition SP 2
> >
> > Hi,
> >
> > I believe I have encountered a bug in Reporting Services and SQL Server.
> >
> > To reproduce:
> >
> > Set the Reporting Service Web Service to run as NT AUTHORITY\NETWORK
> > SERVICE (I believe this is the default on Windows 2003 server as opposed
> > to the local ASPNET account) using rsconfig.exe, et al.
> >
> > Make sure NT AUTHORITY\NETWORK SERVICE has RSExecRole privilege on the
> > Report Server database, Report Server temp database, master, and msdb.
> >
> > Then try and create a report subscription. I receive this error:
> >
> > An internal error occurred on the report server. See the error log for
> > more details. (rsInternalError) The specified '@.owner_login_name' is
> > invalid (valid values are returned by sp_helplogins[excluding Windows NT
> > groups]).
> >
> >
> >
> > My guess is that when the Report Server tries to create a subscription it
> > does so by invoking sp_add_job/sp_verify_job, this then fails because NT
> > AUTHORITY\NETWORK SERVICE is a Windows Group. I believe the actual error
> > above is thrown by the stored procedure, sp_verify_job:
> >
> > Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
> > The specified '@.owner_login_name' is invalid (valid values are returned by
> > sp_helplogins [excluding Windows NT groups]).
> >
> > or
> >
> > From sp_verify_job:
> >
> > -- Now just check that the login id is valid (ie. it exists and isn't an
> > NT group)
> > IF (@.owner_sid IS NULL) OR (EXISTS (SELECT *
> > FROM master.dbo.syslogins
> > WHERE (sid = @.owner_sid)
> > AND (isntgroup <> 0)))
> > BEGIN
> > -- NOTE: In the following message we quote @.owner_login_name instead of
> > @.owner_sid
> > -- since this is the parameter the user passed to the calling SP
> > (ie. either
> > -- sp_add_job or sp_update_job)
> > SELECT @.res_valid_range = FORMATMESSAGE(14203)
> > RAISERROR(14234, -1, -1, '@.owner_login_name', @.res_valid_range)
> > RETURN(1) -- Failure
> > END
> >
> >
> > Thanks,
> > Bryan
> >
> This bug was fixed (hacked, IMO) in SQL Server 2000 SP4. I have no idea if
> a KB article was ever published.
>
>
Sunday, March 11, 2012
Bug in SP4
Hi,
I met a bug in SP4. I did the same test on two machines. One is a windows
2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
develope SP3. Both machines can't run any jobs by sp_start_job stored
procedure after installation of SP4. Same to sp_stop_job. The error is:
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 61
The specified @.job_name ('test1') does not exist.
Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
in SP4.
Bill
Bill,
I just tested this on w2000, SP4, and was able to start a job. Is it maybe
that you were using a non-sysadmin login, so didn't have access to the
msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
then from sysjobs to see.
"Bill Wang" wrote:
> Hi,
> I met a bug in SP4. I did the same test on two machines. One is a windows
> 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> develope SP3. Both machines can't run any jobs by sp_start_job stored
> procedure after installation of SP4. Same to sp_stop_job. The error is:
> Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 61
> The specified @.job_name ('test1') does not exist.
> Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> in SP4.
> Bill
|||Thanks, Mary.
I tested the select from sysjobs and sysjobs_view. No permission issues on
these two table/view. Itested SP4 with sa. Before installation of SP4, I can
run these stored procedure, then install SP4 and run it with same user. It
failed with the error.
Did you install other hotfix after SP4 ?
Thanks!
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
|||Sorry, if you create a new job after SP4, it's OK. But you can't run any jobs
by sp_start_job on old jobs created on SP3.
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
|||I tried this using Dev Edition on XP and didn't see this behaviour. After
upgrading from SP3 to SP4 calls to sp_start_job either from another job or
Query Analyzer ran fine (all jobs owned by sa). What collation is your
sevrer and are your jobs owned by sa or another user? Are you calling
sp_start_job when logged in as a sysadmin?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:13316834-4E2C-4AF2-B838-1CD16C106C7B@.microsoft.com...[vbcol=seagreen]
> Sorry, if you create a new job after SP4, it's OK. But you can't run any
> jobs
> by sp_start_job on old jobs created on SP3.
> Bill
> "Mary" wrote:
I met a bug in SP4. I did the same test on two machines. One is a windows
2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
develope SP3. Both machines can't run any jobs by sp_start_job stored
procedure after installation of SP4. Same to sp_stop_job. The error is:
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 61
The specified @.job_name ('test1') does not exist.
Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
in SP4.
Bill
Bill,
I just tested this on w2000, SP4, and was able to start a job. Is it maybe
that you were using a non-sysadmin login, so didn't have access to the
msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
then from sysjobs to see.
"Bill Wang" wrote:
> Hi,
> I met a bug in SP4. I did the same test on two machines. One is a windows
> 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> develope SP3. Both machines can't run any jobs by sp_start_job stored
> procedure after installation of SP4. Same to sp_stop_job. The error is:
> Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 61
> The specified @.job_name ('test1') does not exist.
> Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> in SP4.
> Bill
|||Thanks, Mary.
I tested the select from sysjobs and sysjobs_view. No permission issues on
these two table/view. Itested SP4 with sa. Before installation of SP4, I can
run these stored procedure, then install SP4 and run it with same user. It
failed with the error.
Did you install other hotfix after SP4 ?
Thanks!
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
|||Sorry, if you create a new job after SP4, it's OK. But you can't run any jobs
by sp_start_job on old jobs created on SP3.
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
|||I tried this using Dev Edition on XP and didn't see this behaviour. After
upgrading from SP3 to SP4 calls to sp_start_job either from another job or
Query Analyzer ran fine (all jobs owned by sa). What collation is your
sevrer and are your jobs owned by sa or another user? Are you calling
sp_start_job when logged in as a sysadmin?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:13316834-4E2C-4AF2-B838-1CD16C106C7B@.microsoft.com...[vbcol=seagreen]
> Sorry, if you create a new job after SP4, it's OK. But you can't run any
> jobs
> by sp_start_job on old jobs created on SP3.
> Bill
> "Mary" wrote:
Bug in SP4
Hi,
I met a bug in SP4. I did the same test on two machines. One is a windows
2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
develope SP3. Both machines can't run any jobs by sp_start_job stored
procedure after installation of SP4. Same to sp_stop_job. The error is:
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 61
The specified @.job_name ('test1') does not exist.
Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
in SP4.
BillBill,
I just tested this on w2000, SP4, and was able to start a job. Is it maybe
that you were using a non-sysadmin login, so didn't have access to the
msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
then from sysjobs to see.
"Bill Wang" wrote:
> Hi,
> I met a bug in SP4. I did the same test on two machines. One is a windows
> 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> develope SP3. Both machines can't run any jobs by sp_start_job stored
> procedure after installation of SP4. Same to sp_stop_job. The error is:
> Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 61
> The specified @.job_name ('test1') does not exist.
> Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> in SP4.
> Bill|||Thanks, Mary.
I tested the select from sysjobs and sysjobs_view. No permission issues on
these two table/view. Itested SP4 with sa. Before installation of SP4, I can
run these stored procedure, then install SP4 and run it with same user. It
failed with the error.
Did you install other hotfix after SP4 ?
Thanks!
Bill
"Mary" wrote:
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
> > Hi,
> > I met a bug in SP4. I did the same test on two machines. One is a windows
> > 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> > develope SP3. Both machines can't run any jobs by sp_start_job stored
> > procedure after installation of SP4. Same to sp_stop_job. The error is:
> >
> > Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> > Line 61
> > The specified @.job_name ('test1') does not exist.
> >
> > Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> > in SP4.
> >
> > Bill|||Sorry, if you create a new job after SP4, it's OK. But you can't run any jobs
by sp_start_job on old jobs created on SP3.
Bill
"Mary" wrote:
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
> > Hi,
> > I met a bug in SP4. I did the same test on two machines. One is a windows
> > 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> > develope SP3. Both machines can't run any jobs by sp_start_job stored
> > procedure after installation of SP4. Same to sp_stop_job. The error is:
> >
> > Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> > Line 61
> > The specified @.job_name ('test1') does not exist.
> >
> > Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> > in SP4.
> >
> > Bill|||I tried this using Dev Edition on XP and didn't see this behaviour. After
upgrading from SP3 to SP4 calls to sp_start_job either from another job or
Query Analyzer ran fine (all jobs owned by sa). What collation is your
sevrer and are your jobs owned by sa or another user? Are you calling
sp_start_job when logged in as a sysadmin?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:13316834-4E2C-4AF2-B838-1CD16C106C7B@.microsoft.com...
> Sorry, if you create a new job after SP4, it's OK. But you can't run any
> jobs
> by sp_start_job on old jobs created on SP3.
> Bill
> "Mary" wrote:
>> Bill,
>> I just tested this on w2000, SP4, and was able to start a job. Is it
>> maybe
>> that you were using a non-sysadmin login, so didn't have access to the
>> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view
>> and
>> then from sysjobs to see.
>> "Bill Wang" wrote:
>> > Hi,
>> > I met a bug in SP4. I did the same test on two machines. One is a
>> > windows
>> > 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL
>> > Server 2000
>> > develope SP3. Both machines can't run any jobs by sp_start_job stored
>> > procedure after installation of SP4. Same to sp_stop_job. The error is:
>> >
>> > Server: Msg 14262, Level 16, State 1, Procedure
>> > sp_verify_job_identifiers,
>> > Line 61
>> > The specified @.job_name ('test1') does not exist.
>> >
>> > Both stored procedures run well in SP3, but not in SP4. I am sure it's
>> > a bug
>> > in SP4.
>> >
>> > Bill
I met a bug in SP4. I did the same test on two machines. One is a windows
2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
develope SP3. Both machines can't run any jobs by sp_start_job stored
procedure after installation of SP4. Same to sp_stop_job. The error is:
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 61
The specified @.job_name ('test1') does not exist.
Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
in SP4.
BillBill,
I just tested this on w2000, SP4, and was able to start a job. Is it maybe
that you were using a non-sysadmin login, so didn't have access to the
msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
then from sysjobs to see.
"Bill Wang" wrote:
> Hi,
> I met a bug in SP4. I did the same test on two machines. One is a windows
> 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> develope SP3. Both machines can't run any jobs by sp_start_job stored
> procedure after installation of SP4. Same to sp_stop_job. The error is:
> Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 61
> The specified @.job_name ('test1') does not exist.
> Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> in SP4.
> Bill|||Thanks, Mary.
I tested the select from sysjobs and sysjobs_view. No permission issues on
these two table/view. Itested SP4 with sa. Before installation of SP4, I can
run these stored procedure, then install SP4 and run it with same user. It
failed with the error.
Did you install other hotfix after SP4 ?
Thanks!
Bill
"Mary" wrote:
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
> > Hi,
> > I met a bug in SP4. I did the same test on two machines. One is a windows
> > 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> > develope SP3. Both machines can't run any jobs by sp_start_job stored
> > procedure after installation of SP4. Same to sp_stop_job. The error is:
> >
> > Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> > Line 61
> > The specified @.job_name ('test1') does not exist.
> >
> > Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> > in SP4.
> >
> > Bill|||Sorry, if you create a new job after SP4, it's OK. But you can't run any jobs
by sp_start_job on old jobs created on SP3.
Bill
"Mary" wrote:
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it maybe
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
> then from sysjobs to see.
> "Bill Wang" wrote:
> > Hi,
> > I met a bug in SP4. I did the same test on two machines. One is a windows
> > 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2000
> > develope SP3. Both machines can't run any jobs by sp_start_job stored
> > procedure after installation of SP4. Same to sp_stop_job. The error is:
> >
> > Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> > Line 61
> > The specified @.job_name ('test1') does not exist.
> >
> > Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
> > in SP4.
> >
> > Bill|||I tried this using Dev Edition on XP and didn't see this behaviour. After
upgrading from SP3 to SP4 calls to sp_start_job either from another job or
Query Analyzer ran fine (all jobs owned by sa). What collation is your
sevrer and are your jobs owned by sa or another user? Are you calling
sp_start_job when logged in as a sysadmin?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:13316834-4E2C-4AF2-B838-1CD16C106C7B@.microsoft.com...
> Sorry, if you create a new job after SP4, it's OK. But you can't run any
> jobs
> by sp_start_job on old jobs created on SP3.
> Bill
> "Mary" wrote:
>> Bill,
>> I just tested this on w2000, SP4, and was able to start a job. Is it
>> maybe
>> that you were using a non-sysadmin login, so didn't have access to the
>> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view
>> and
>> then from sysjobs to see.
>> "Bill Wang" wrote:
>> > Hi,
>> > I met a bug in SP4. I did the same test on two machines. One is a
>> > windows
>> > 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL
>> > Server 2000
>> > develope SP3. Both machines can't run any jobs by sp_start_job stored
>> > procedure after installation of SP4. Same to sp_stop_job. The error is:
>> >
>> > Server: Msg 14262, Level 16, State 1, Procedure
>> > sp_verify_job_identifiers,
>> > Line 61
>> > The specified @.job_name ('test1') does not exist.
>> >
>> > Both stored procedures run well in SP3, but not in SP4. I am sure it's
>> > a bug
>> > in SP4.
>> >
>> > Bill
Bug in SP4
Hi,
I met a bug in SP4. I did the same test on two machines. One is a windows
2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 200
0
develope SP3. Both machines can't run any jobs by sp_start_job stored
procedure after installation of SP4. Same to sp_stop_job. The error is:
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 61
The specified @.job_name ('test1') does not exist.
Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
in SP4.
BillBill,
I just tested this on w2000, SP4, and was able to start a job. Is it maybe
that you were using a non-sysadmin login, so didn't have access to the
msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
then from sysjobs to see.
"Bill Wang" wrote:
> Hi,
> I met a bug in SP4. I did the same test on two machines. One is a windo
ws
> 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2
000
> develope SP3. Both machines can't run any jobs by sp_start_job stored
> procedure after installation of SP4. Same to sp_stop_job. The error is:
> Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 61
> The specified @.job_name ('test1') does not exist.
> Both stored procedures run well in SP3, but not in SP4. I am sure it's a b
ug
> in SP4.
> Bill|||Thanks, Mary.
I tested the select from sysjobs and sysjobs_view. No permission issues on
these two table/view. Itested SP4 with sa. Before installation of SP4, I can
run these stored procedure, then install SP4 and run it with same user. It
failed with the error.
Did you install other hotfix after SP4 ?
Thanks!
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it mayb
e
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view an
d
> then from sysjobs to see.
> "Bill Wang" wrote:
>|||Sorry, if you create a new job after SP4, it's OK. But you can't run any job
s
by sp_start_job on old jobs created on SP3.
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it mayb
e
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view an
d
> then from sysjobs to see.
> "Bill Wang" wrote:
>|||I tried this using Dev Edition on XP and didn't see this behaviour. After
upgrading from SP3 to SP4 calls to sp_start_job either from another job or
Query Analyzer ran fine (all jobs owned by sa). What collation is your
sevrer and are your jobs owned by sa or another user? Are you calling
sp_start_job when logged in as a sysadmin?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:13316834-4E2C-4AF2-B838-1CD16C106C7B@.microsoft.com...[vbcol=seagreen]
> Sorry, if you create a new job after SP4, it's OK. But you can't run any
> jobs
> by sp_start_job on old jobs created on SP3.
> Bill
> "Mary" wrote:
>
I met a bug in SP4. I did the same test on two machines. One is a windows
2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 200
0
develope SP3. Both machines can't run any jobs by sp_start_job stored
procedure after installation of SP4. Same to sp_stop_job. The error is:
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 61
The specified @.job_name ('test1') does not exist.
Both stored procedures run well in SP3, but not in SP4. I am sure it's a bug
in SP4.
BillBill,
I just tested this on w2000, SP4, and was able to start a job. Is it maybe
that you were using a non-sysadmin login, so didn't have access to the
msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view and
then from sysjobs to see.
"Bill Wang" wrote:
> Hi,
> I met a bug in SP4. I did the same test on two machines. One is a windo
ws
> 2003 server wint SQL Server 2000 Enterprise SP3, another is a SQL Server 2
000
> develope SP3. Both machines can't run any jobs by sp_start_job stored
> procedure after installation of SP4. Same to sp_stop_job. The error is:
> Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 61
> The specified @.job_name ('test1') does not exist.
> Both stored procedures run well in SP3, but not in SP4. I am sure it's a b
ug
> in SP4.
> Bill|||Thanks, Mary.
I tested the select from sysjobs and sysjobs_view. No permission issues on
these two table/view. Itested SP4 with sa. Before installation of SP4, I can
run these stored procedure, then install SP4 and run it with same user. It
failed with the error.
Did you install other hotfix after SP4 ?
Thanks!
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it mayb
e
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view an
d
> then from sysjobs to see.
> "Bill Wang" wrote:
>|||Sorry, if you create a new job after SP4, it's OK. But you can't run any job
s
by sp_start_job on old jobs created on SP3.
Bill
"Mary" wrote:
[vbcol=seagreen]
> Bill,
> I just tested this on w2000, SP4, and was able to start a job. Is it mayb
e
> that you were using a non-sysadmin login, so didn't have access to the
> msdb.sysjobs_view (used by sp_start_job)? Try select from sysjobs_view an
d
> then from sysjobs to see.
> "Bill Wang" wrote:
>|||I tried this using Dev Edition on XP and didn't see this behaviour. After
upgrading from SP3 to SP4 calls to sp_start_job either from another job or
Query Analyzer ran fine (all jobs owned by sa). What collation is your
sevrer and are your jobs owned by sa or another user? Are you calling
sp_start_job when logged in as a sysadmin?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:13316834-4E2C-4AF2-B838-1CD16C106C7B@.microsoft.com...[vbcol=seagreen]
> Sorry, if you create a new job after SP4, it's OK. But you can't run any
> jobs
> by sp_start_job on old jobs created on SP3.
> Bill
> "Mary" wrote:
>
Thursday, March 8, 2012
Bug in Enterprise Manager?
Hi all, met with something very scary and want to confirm if anyone
else have met this possible bug with EM?
else have met this possible bug with EM?
Got a production DB, say DB_A with quite a few users in it. this day,
when I look into the EM-->management-->Current Activity-->Locks/Object,
I saw a lot of locking on some objects belonging to a user, say User_A
but the problem is, this User_A doesn't own a thing in DB_A!! And even
if I drop this user from DB_A, the EM GUI still showing there are quite
a locks on objects owned by User_A.
And if use Query Analyzer to direct query on system tables, all are
fine, that is those locks are actually on another User_B, which is
fine.
So I've checked that the problem seems to lie in that User_B has a uid
8 in DB_A whereas User_A has the same uid (8) in master db. Once I
remove User_A from master db, then the EM GUI shows no locking at all
(though in system tables, there are still lockings in User_B's object).
So I guess it's a bug in EM but has anyone met with this kind of case
before?Hi
Not a bug in EM, you have a real security problem.
You user ID's do not match across all your databases. Must have been that a
DB was restored from another server.
You are going to have to remove all the users from the non-system DB's and
then grant them access and permissions again. The only thing you can trust
is syslogins and sysusers in master, model, msdb and tempdb. The rest are
not reliable..
Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"New MSSQL DBA" <boscong88@.gmail.com> wrote in message
news:1121237722.454609.204170@.o13g2000cwo.googlegr oups.com...
> Hi all, met with something very scary and want to confirm if anyone
> else have met this possible bug with EM?
> Got a production DB, say DB_A with quite a few users in it. this day,
> when I look into the EM-->management-->Current Activity-->Locks/Object,
> I saw a lot of locking on some objects belonging to a user, say User_A
> but the problem is, this User_A doesn't own a thing in DB_A!! And even
> if I drop this user from DB_A, the EM GUI still showing there are quite
> a locks on objects owned by User_A.
> And if use Query Analyzer to direct query on system tables, all are
> fine, that is those locks are actually on another User_B, which is
> fine.
> So I've checked that the problem seems to lie in that User_B has a uid
> 8 in DB_A whereas User_A has the same uid (8) in master db. Once I
> remove User_A from master db, then the EM GUI shows no locking at all
> (though in system tables, there are still lockings in User_B's object).
> So I guess it's a bug in EM but has anyone met with this kind of case
> before?|||New MSSQL DBA (boscong88@.gmail.com) writes:
> Hi all, met with something very scary and want to confirm if anyone
> else have met this possible bug with EM?
> Got a production DB, say DB_A with quite a few users in it. this day,
> when I look into the EM-->management-->Current Activity-->Locks/Object,
> I saw a lot of locking on some objects belonging to a user, say User_A
> but the problem is, this User_A doesn't own a thing in DB_A!! And even
> if I drop this user from DB_A, the EM GUI still showing there are quite
> a locks on objects owned by User_A.
Mike is plain wrong when he say there this not a bug in Enterprise Manager.
There is a bug, and I have known about for a long time. In difference to
you, I didn't run into it this way - I found it by looking at the code.
Here is the relevant part from sp_MSset_current_activity (a procedure
that exists only for Enterprise Manager):
select @.stmt ='update ' + @.locktab + ' set [Table] = name,
[ObjOwner] = user_name(uid) from ' + quotename(@.lckdb, '[') +
'.[dbo].[sysobjects] where id = ' +
convert(nvarchar(10), @.lckobjid) + ' and [Database] = ''' + @.lckdb
+ ''' and [ObjID] = ' + convert(nvarchar(10), @.lckobjid)
exec (@.stmt)
The context is that the procedure have first found all processes and locks
and saves these in a temp table. Then it iterates over all locked objects
to translate the names in a look. The bug is the part
user_name(uid)
user_name() works in the current database, but here we examine some
other database. the programmer should have joined with
@.lckdb + '.dbo.sysusers' instead.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||How can uid of users in all the databases be the same??
Say I've got 5 logins, all of them have access to system database and
only 1 of them has access to DB_A and another 1 has access to DB_B,
then how is it possible that the users for these logins in different
database be of the same uid?|||Thanks a lot, that's exactly my guess but I couldn't confirm it before.
Wow, I'd say that's a really junior mistake and as I've searched
Microsoft KB, it hasn't been documented as a bug or whatsoever and they
haven't fix it for such a long time.|||New MSSQL DBA (boscong88@.gmail.com) writes:
> How can uid of users in all the databases be the same??
> Say I've got 5 logins, all of them have access to system database and
> only 1 of them has access to DB_A and another 1 has access to DB_B,
> then how is it possible that the users for these logins in different
> database be of the same uid?
Not really sure what you mean, but uid is a database-specific id, so id
17 in db_A can be user Ture and in db_B it's user Ingvar. Which may or
may not map to the same login.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Wednesday, March 7, 2012
Bug in Database Maintenance Plans Enterprise Manager and MSDE v SQL
I have seen this "bug" many times personally, but cannot seem to find any
posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
Consider
http://support.microsoft.com/default...22&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
If connected to an SQL server... the option works fine. If connected to an
MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
John D. Sullivan
ABELSoft Corporation
(email address munged)
This is not just an MSDE problem. We have had problems with out Enterprise
version not deleting backups. Never resolved it. Wrote scripts to delete
files.
Bob
SuccessWare Software
"John D. Sullivan" <noone@.nothing.com> wrote in message
news:uGWJDreaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/default...22&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>
|||Excellent information that is very relevant. Thank you Mark. It is very
useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
platform. While it is true that SQL 7 has no Database Maintenance Plan
branch under Management in the Enterprise Manager and therefore connecting
to an SQL 2k it is not possible to edit or delete a "plan" but only the
Job/steps... the problem I am citing is a bit different.
What I mean to say is that when creating a new Database Maintenance Plan
using the GUI Enterprise Manager (2000) that takes one through a multi-step
"wizard", it is possible to "check box" that you wish to "Remove files older
than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
service packs) is the engine/server the GUI of the Maintenance Plan Wizard
shows blanks for the qty/units of days/weeks to remove old files.
Workaround: if you go ahead and checkbox to remove old files, manually fill
in a number for qty (you cannot fill in units) and continue through the
wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
xp_sqlmaint (notice this is the extended stored procedure equivalent of
command prompt sqlmaint.exe) will have slight syntax errors in the argument
list. The resulting Job fails to execute. Obviously the -DelBkUps switch
will not have the correct <time value> after it... if you filled in 77 it
will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
job step and manually tweak the command line arguments until they are
correct, then it will work.
By the way, I find viewing the results of job and job steps pretty much
useless. You might see an error number you can lookup. One ends up copying
the entire command and pasting and running it as a query to find out exactly
where/why it is failing. Instead, I find it very useful to check the option
in the Maintenance Plan to write report to a text file..because the error
message(s) in there are far more detailed and useful.
John D. Sullivan
ABELSoft Corporation
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> Yep, this is a bug. Have a look at these articles:
> BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
> ME Computers
> http://support.microsoft.com/?id=278667
> BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
> http://support.microsoft.com/?id=247879
> FIX: SQLMaint.exe Sets Database Status Incorrectly
> http://support.microsoft.com/?id=276234
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> John D. Sullivan wrote:
any[vbcol=seagreen]
Enterprise[vbcol=seagreen]
does[vbcol=seagreen]
Access[vbcol=seagreen]
and[vbcol=seagreen]
http://support.microsoft.com/default...22&Product=sql[vbcol=seagreen]
on[vbcol=seagreen]
for[vbcol=seagreen]
important[vbcol=seagreen]
an[vbcol=seagreen]
step[vbcol=seagreen]
bug[vbcol=seagreen]
posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
Consider
http://support.microsoft.com/default...22&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
If connected to an SQL server... the option works fine. If connected to an
MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
John D. Sullivan
ABELSoft Corporation
(email address munged)
This is not just an MSDE problem. We have had problems with out Enterprise
version not deleting backups. Never resolved it. Wrote scripts to delete
files.
Bob
SuccessWare Software
"John D. Sullivan" <noone@.nothing.com> wrote in message
news:uGWJDreaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/default...22&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>
|||Excellent information that is very relevant. Thank you Mark. It is very
useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
platform. While it is true that SQL 7 has no Database Maintenance Plan
branch under Management in the Enterprise Manager and therefore connecting
to an SQL 2k it is not possible to edit or delete a "plan" but only the
Job/steps... the problem I am citing is a bit different.
What I mean to say is that when creating a new Database Maintenance Plan
using the GUI Enterprise Manager (2000) that takes one through a multi-step
"wizard", it is possible to "check box" that you wish to "Remove files older
than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
service packs) is the engine/server the GUI of the Maintenance Plan Wizard
shows blanks for the qty/units of days/weeks to remove old files.
Workaround: if you go ahead and checkbox to remove old files, manually fill
in a number for qty (you cannot fill in units) and continue through the
wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
xp_sqlmaint (notice this is the extended stored procedure equivalent of
command prompt sqlmaint.exe) will have slight syntax errors in the argument
list. The resulting Job fails to execute. Obviously the -DelBkUps switch
will not have the correct <time value> after it... if you filled in 77 it
will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
job step and manually tweak the command line arguments until they are
correct, then it will work.
By the way, I find viewing the results of job and job steps pretty much
useless. You might see an error number you can lookup. One ends up copying
the entire command and pasting and running it as a query to find out exactly
where/why it is failing. Instead, I find it very useful to check the option
in the Maintenance Plan to write report to a text file..because the error
message(s) in there are far more detailed and useful.
John D. Sullivan
ABELSoft Corporation
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> Yep, this is a bug. Have a look at these articles:
> BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
> ME Computers
> http://support.microsoft.com/?id=278667
> BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
> http://support.microsoft.com/?id=247879
> FIX: SQLMaint.exe Sets Database Status Incorrectly
> http://support.microsoft.com/?id=276234
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> John D. Sullivan wrote:
any[vbcol=seagreen]
Enterprise[vbcol=seagreen]
does[vbcol=seagreen]
Access[vbcol=seagreen]
and[vbcol=seagreen]
http://support.microsoft.com/default...22&Product=sql[vbcol=seagreen]
on[vbcol=seagreen]
for[vbcol=seagreen]
important[vbcol=seagreen]
an[vbcol=seagreen]
step[vbcol=seagreen]
bug[vbcol=seagreen]
Bug in Database Maintenance Plans Enterprise Manager and MSDE v SQL
I have seen this "bug" many times personally, but cannot seem to find any
posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
Consider
http://support.microsoft.com/default.aspx?scid=kb;en-us;295022&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
If connected to an SQL server... the option works fine. If connected to an
MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
John D. Sullivan
ABELSoft Corporation
(email address munged)This is not just an MSDE problem. We have had problems with out Enterprise
version not deleting backups. Never resolved it. Wrote scripts to delete
files.
Bob
SuccessWare Software
"John D. Sullivan" <noone@.nothing.com> wrote in message
news:uGWJDreaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/default.aspx?scid=kb;en-us;295022&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>|||John,
Yep, this is a bug. Have a look at these articles:
BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
ME Computers
http://support.microsoft.com/?id=278667
BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
http://support.microsoft.com/?id=247879
FIX: SQLMaint.exe Sets Database Status Incorrectly
http://support.microsoft.com/?id=276234
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
John D. Sullivan wrote:
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/default.aspx?scid=kb;en-us;295022&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>|||Excellent information that is very relevant. Thank you Mark. It is very
useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
platform. While it is true that SQL 7 has no Database Maintenance Plan
branch under Management in the Enterprise Manager and therefore connecting
to an SQL 2k it is not possible to edit or delete a "plan" but only the
Job/steps... the problem I am citing is a bit different.
What I mean to say is that when creating a new Database Maintenance Plan
using the GUI Enterprise Manager (2000) that takes one through a multi-step
"wizard", it is possible to "check box" that you wish to "Remove files older
than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
service packs) is the engine/server the GUI of the Maintenance Plan Wizard
shows blanks for the qty/units of days/weeks to remove old files.
Workaround: if you go ahead and checkbox to remove old files, manually fill
in a number for qty (you cannot fill in units) and continue through the
wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
xp_sqlmaint (notice this is the extended stored procedure equivalent of
command prompt sqlmaint.exe) will have slight syntax errors in the argument
list. The resulting Job fails to execute. Obviously the -DelBkUps switch
will not have the correct <time value> after it... if you filled in 77 it
will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
job step and manually tweak the command line arguments until they are
correct, then it will work.
By the way, I find viewing the results of job and job steps pretty much
useless. You might see an error number you can lookup. One ends up copying
the entire command and pasting and running it as a query to find out exactly
where/why it is failing. Instead, I find it very useful to check the option
in the Maintenance Plan to write report to a text file..because the error
message(s) in there are far more detailed and useful.
John D. Sullivan
ABELSoft Corporation
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...
> John,
> Yep, this is a bug. Have a look at these articles:
> BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
> ME Computers
> http://support.microsoft.com/?id=278667
> BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
> http://support.microsoft.com/?id=247879
> FIX: SQLMaint.exe Sets Database Status Incorrectly
> http://support.microsoft.com/?id=276234
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> John D. Sullivan wrote:
> > I have seen this "bug" many times personally, but cannot seem to find
any
> > posting on the Internet about it. I have many customers using MSDE
> >
> > The bug is with the Database Maintenance Plans of Microsoft SQL
Enterprise
> > Manager when connecting to MSDE.
> >
> > Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> > include licence for Enterprize Manager (EM)... but any technician who
does
> > have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client
Access
> > Licence) does have Enterprize Manager and can connect and manage MSDE
and
> > SQL engines/servers equally well.
> >
> > Consider
> >
http://support.microsoft.com/default.aspx?scid=kb;en-us;295022&Product=sql
> > Here we see substantiation that one "can" use EM and Maintenance Plans
on
> > MSDE, albeit that you had better be the latest Service Pack since the
> > sqlmaint feature was overlooked in the original release to market.
> >
> > The bug I am reporting is with the "Remove after X Weeks/Days" option
for
> > Database Backup, Transaction Backup (and Log text files). This is
important
> > because unless you can also choose to do so, one will end up filling the
> > hard disk with far too many SQL dumps.
> >
> > If connected to an SQL server... the option works fine. If connected to
an
> > MSDE, the option fails to provie the drop-down for days/weeks and the
> > resulting job created has bugs in the command line options of the job
step
> > which one must then go and manually tweak to be correct (never report a
bug
> > without a workaround eh? ;-P)
> >
> > John D. Sullivan
> > ABELSoft Corporation
> > (email address munged)
> >
> >|||John,
Yep it's annoying. I try not to use maintenance plans for my routine
tasks as I like to have the flexibility of creating my own maintenance
jobs. This may be a workaround you might wish to consider.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
John D. Sullivan wrote:
> Excellent information that is very relevant. Thank you Mark. It is very
> useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
> platform. While it is true that SQL 7 has no Database Maintenance Plan
> branch under Management in the Enterprise Manager and therefore connecting
> to an SQL 2k it is not possible to edit or delete a "plan" but only the
> Job/steps... the problem I am citing is a bit different.
> What I mean to say is that when creating a new Database Maintenance Plan
> using the GUI Enterprise Manager (2000) that takes one through a multi-step
> "wizard", it is possible to "check box" that you wish to "Remove files older
> than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
> manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
> service packs) is the engine/server the GUI of the Maintenance Plan Wizard
> shows blanks for the qty/units of days/weeks to remove old files.
> Workaround: if you go ahead and checkbox to remove old files, manually fill
> in a number for qty (you cannot fill in units) and continue through the
> wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
> xp_sqlmaint (notice this is the extended stored procedure equivalent of
> command prompt sqlmaint.exe) will have slight syntax errors in the argument
> list. The resulting Job fails to execute. Obviously the -DelBkUps switch
> will not have the correct <time value> after it... if you filled in 77 it
> will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
> job step and manually tweak the command line arguments until they are
> correct, then it will work.
> By the way, I find viewing the results of job and job steps pretty much
> useless. You might see an error number you can lookup. One ends up copying
> the entire command and pasting and running it as a query to find out exactly
> where/why it is failing. Instead, I find it very useful to check the option
> in the Maintenance Plan to write report to a text file..because the error
> message(s) in there are far more detailed and useful.
> John D. Sullivan
> ABELSoft Corporation
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...
>>John,
>>Yep, this is a bug. Have a look at these articles:
>>BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
>>ME Computers
>>http://support.microsoft.com/?id=278667
>>BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
>>http://support.microsoft.com/?id=247879
>>FIX: SQLMaint.exe Sets Database Status Incorrectly
>>http://support.microsoft.com/?id=276234
>>
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602.html
>>
>>John D. Sullivan wrote:
>>I have seen this "bug" many times personally, but cannot seem to find
> any
>>posting on the Internet about it. I have many customers using MSDE
>>The bug is with the Database Maintenance Plans of Microsoft SQL
> Enterprise
>>Manager when connecting to MSDE.
>>Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
>>include licence for Enterprize Manager (EM)... but any technician who
> does
>>have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client
> Access
>>Licence) does have Enterprize Manager and can connect and manage MSDE
> and
>>SQL engines/servers equally well.
>>Consider
> http://support.microsoft.com/default.aspx?scid=kb;en-us;295022&Product=sql
>>Here we see substantiation that one "can" use EM and Maintenance Plans
> on
>>MSDE, albeit that you had better be the latest Service Pack since the
>>sqlmaint feature was overlooked in the original release to market.
>>The bug I am reporting is with the "Remove after X Weeks/Days" option
> for
>>Database Backup, Transaction Backup (and Log text files). This is
> important
>>because unless you can also choose to do so, one will end up filling the
>>hard disk with far too many SQL dumps.
>>If connected to an SQL server... the option works fine. If connected to
> an
>>MSDE, the option fails to provie the drop-down for days/weeks and the
>>resulting job created has bugs in the command line options of the job
> step
>>which one must then go and manually tweak to be correct (never report a
> bug
>>without a workaround eh? ;-P)
>>John D. Sullivan
>>ABELSoft Corporation
>>(email address munged)
>>
>
>
>
posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
Consider
http://support.microsoft.com/default.aspx?scid=kb;en-us;295022&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
If connected to an SQL server... the option works fine. If connected to an
MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
John D. Sullivan
ABELSoft Corporation
(email address munged)This is not just an MSDE problem. We have had problems with out Enterprise
version not deleting backups. Never resolved it. Wrote scripts to delete
files.
Bob
SuccessWare Software
"John D. Sullivan" <noone@.nothing.com> wrote in message
news:uGWJDreaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/default.aspx?scid=kb;en-us;295022&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>|||John,
Yep, this is a bug. Have a look at these articles:
BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
ME Computers
http://support.microsoft.com/?id=278667
BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
http://support.microsoft.com/?id=247879
FIX: SQLMaint.exe Sets Database Status Incorrectly
http://support.microsoft.com/?id=276234
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
John D. Sullivan wrote:
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/default.aspx?scid=kb;en-us;295022&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>|||Excellent information that is very relevant. Thank you Mark. It is very
useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
platform. While it is true that SQL 7 has no Database Maintenance Plan
branch under Management in the Enterprise Manager and therefore connecting
to an SQL 2k it is not possible to edit or delete a "plan" but only the
Job/steps... the problem I am citing is a bit different.
What I mean to say is that when creating a new Database Maintenance Plan
using the GUI Enterprise Manager (2000) that takes one through a multi-step
"wizard", it is possible to "check box" that you wish to "Remove files older
than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
service packs) is the engine/server the GUI of the Maintenance Plan Wizard
shows blanks for the qty/units of days/weeks to remove old files.
Workaround: if you go ahead and checkbox to remove old files, manually fill
in a number for qty (you cannot fill in units) and continue through the
wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
xp_sqlmaint (notice this is the extended stored procedure equivalent of
command prompt sqlmaint.exe) will have slight syntax errors in the argument
list. The resulting Job fails to execute. Obviously the -DelBkUps switch
will not have the correct <time value> after it... if you filled in 77 it
will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
job step and manually tweak the command line arguments until they are
correct, then it will work.
By the way, I find viewing the results of job and job steps pretty much
useless. You might see an error number you can lookup. One ends up copying
the entire command and pasting and running it as a query to find out exactly
where/why it is failing. Instead, I find it very useful to check the option
in the Maintenance Plan to write report to a text file..because the error
message(s) in there are far more detailed and useful.
John D. Sullivan
ABELSoft Corporation
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...
> John,
> Yep, this is a bug. Have a look at these articles:
> BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
> ME Computers
> http://support.microsoft.com/?id=278667
> BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
> http://support.microsoft.com/?id=247879
> FIX: SQLMaint.exe Sets Database Status Incorrectly
> http://support.microsoft.com/?id=276234
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> John D. Sullivan wrote:
> > I have seen this "bug" many times personally, but cannot seem to find
any
> > posting on the Internet about it. I have many customers using MSDE
> >
> > The bug is with the Database Maintenance Plans of Microsoft SQL
Enterprise
> > Manager when connecting to MSDE.
> >
> > Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> > include licence for Enterprize Manager (EM)... but any technician who
does
> > have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client
Access
> > Licence) does have Enterprize Manager and can connect and manage MSDE
and
> > SQL engines/servers equally well.
> >
> > Consider
> >
http://support.microsoft.com/default.aspx?scid=kb;en-us;295022&Product=sql
> > Here we see substantiation that one "can" use EM and Maintenance Plans
on
> > MSDE, albeit that you had better be the latest Service Pack since the
> > sqlmaint feature was overlooked in the original release to market.
> >
> > The bug I am reporting is with the "Remove after X Weeks/Days" option
for
> > Database Backup, Transaction Backup (and Log text files). This is
important
> > because unless you can also choose to do so, one will end up filling the
> > hard disk with far too many SQL dumps.
> >
> > If connected to an SQL server... the option works fine. If connected to
an
> > MSDE, the option fails to provie the drop-down for days/weeks and the
> > resulting job created has bugs in the command line options of the job
step
> > which one must then go and manually tweak to be correct (never report a
bug
> > without a workaround eh? ;-P)
> >
> > John D. Sullivan
> > ABELSoft Corporation
> > (email address munged)
> >
> >|||John,
Yep it's annoying. I try not to use maintenance plans for my routine
tasks as I like to have the flexibility of creating my own maintenance
jobs. This may be a workaround you might wish to consider.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
John D. Sullivan wrote:
> Excellent information that is very relevant. Thank you Mark. It is very
> useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
> platform. While it is true that SQL 7 has no Database Maintenance Plan
> branch under Management in the Enterprise Manager and therefore connecting
> to an SQL 2k it is not possible to edit or delete a "plan" but only the
> Job/steps... the problem I am citing is a bit different.
> What I mean to say is that when creating a new Database Maintenance Plan
> using the GUI Enterprise Manager (2000) that takes one through a multi-step
> "wizard", it is possible to "check box" that you wish to "Remove files older
> than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
> manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
> service packs) is the engine/server the GUI of the Maintenance Plan Wizard
> shows blanks for the qty/units of days/weeks to remove old files.
> Workaround: if you go ahead and checkbox to remove old files, manually fill
> in a number for qty (you cannot fill in units) and continue through the
> wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
> xp_sqlmaint (notice this is the extended stored procedure equivalent of
> command prompt sqlmaint.exe) will have slight syntax errors in the argument
> list. The resulting Job fails to execute. Obviously the -DelBkUps switch
> will not have the correct <time value> after it... if you filled in 77 it
> will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
> job step and manually tweak the command line arguments until they are
> correct, then it will work.
> By the way, I find viewing the results of job and job steps pretty much
> useless. You might see an error number you can lookup. One ends up copying
> the entire command and pasting and running it as a query to find out exactly
> where/why it is failing. Instead, I find it very useful to check the option
> in the Maintenance Plan to write report to a text file..because the error
> message(s) in there are far more detailed and useful.
> John D. Sullivan
> ABELSoft Corporation
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...
>>John,
>>Yep, this is a bug. Have a look at these articles:
>>BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
>>ME Computers
>>http://support.microsoft.com/?id=278667
>>BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
>>http://support.microsoft.com/?id=247879
>>FIX: SQLMaint.exe Sets Database Status Incorrectly
>>http://support.microsoft.com/?id=276234
>>
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602.html
>>
>>John D. Sullivan wrote:
>>I have seen this "bug" many times personally, but cannot seem to find
> any
>>posting on the Internet about it. I have many customers using MSDE
>>The bug is with the Database Maintenance Plans of Microsoft SQL
> Enterprise
>>Manager when connecting to MSDE.
>>Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
>>include licence for Enterprize Manager (EM)... but any technician who
> does
>>have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client
> Access
>>Licence) does have Enterprize Manager and can connect and manage MSDE
> and
>>SQL engines/servers equally well.
>>Consider
> http://support.microsoft.com/default.aspx?scid=kb;en-us;295022&Product=sql
>>Here we see substantiation that one "can" use EM and Maintenance Plans
> on
>>MSDE, albeit that you had better be the latest Service Pack since the
>>sqlmaint feature was overlooked in the original release to market.
>>The bug I am reporting is with the "Remove after X Weeks/Days" option
> for
>>Database Backup, Transaction Backup (and Log text files). This is
> important
>>because unless you can also choose to do so, one will end up filling the
>>hard disk with far too many SQL dumps.
>>If connected to an SQL server... the option works fine. If connected to
> an
>>MSDE, the option fails to provie the drop-down for days/weeks and the
>>resulting job created has bugs in the command line options of the job
> step
>>which one must then go and manually tweak to be correct (never report a
> bug
>>without a workaround eh? ;-P)
>>John D. Sullivan
>>ABELSoft Corporation
>>(email address munged)
>>
>
>
>
Bug in Database Maintenance Plans Enterprise Manager and MSDE v SQL
I have seen this "bug" many times personally, but cannot seem to find any
posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
Consider
http://support.microsoft.com/default...22&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
If connected to an SQL server... the option works fine. If connected to an
MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
John D. Sullivan
ABELSoft Corporation
(email address munged)
This is not just an MSDE problem. We have had problems with out Enterprise
version not deleting backups. Never resolved it. Wrote scripts to delete
files.
Bob
SuccessWare Software
"John D. Sullivan" <noone@.nothing.com> wrote in message
news:uGWJDreaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/default...22&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>
|||Excellent information that is very relevant. Thank you Mark. It is very
useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
platform. While it is true that SQL 7 has no Database Maintenance Plan
branch under Management in the Enterprise Manager and therefore connecting
to an SQL 2k it is not possible to edit or delete a "plan" but only the
Job/steps... the problem I am citing is a bit different.
What I mean to say is that when creating a new Database Maintenance Plan
using the GUI Enterprise Manager (2000) that takes one through a multi-step
"wizard", it is possible to "check box" that you wish to "Remove files older
than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
service packs) is the engine/server the GUI of the Maintenance Plan Wizard
shows blanks for the qty/units of days/weeks to remove old files.
Workaround: if you go ahead and checkbox to remove old files, manually fill
in a number for qty (you cannot fill in units) and continue through the
wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
xp_sqlmaint (notice this is the extended stored procedure equivalent of
command prompt sqlmaint.exe) will have slight syntax errors in the argument
list. The resulting Job fails to execute. Obviously the -DelBkUps switch
will not have the correct <time value> after it... if you filled in 77 it
will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
job step and manually tweak the command line arguments until they are
correct, then it will work.
By the way, I find viewing the results of job and job steps pretty much
useless. You might see an error number you can lookup. One ends up copying
the entire command and pasting and running it as a query to find out exactly
where/why it is failing. Instead, I find it very useful to check the option
in the Maintenance Plan to write report to a text file..because the error
message(s) in there are far more detailed and useful.
John D. Sullivan
ABELSoft Corporation
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> Yep, this is a bug. Have a look at these articles:
> BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
> ME Computers
> http://support.microsoft.com/?id=278667
> BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
> http://support.microsoft.com/?id=247879
> FIX: SQLMaint.exe Sets Database Status Incorrectly
> http://support.microsoft.com/?id=276234
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> John D. Sullivan wrote:
any[vbcol=seagreen]
Enterprise[vbcol=seagreen]
does[vbcol=seagreen]
Access[vbcol=seagreen]
and[vbcol=seagreen]
http://support.microsoft.com/default...22&Product=sql[vbcol=seagreen]
on[vbcol=seagreen]
for[vbcol=seagreen]
important[vbcol=seagreen]
an[vbcol=seagreen]
step[vbcol=seagreen]
bug[vbcol=seagreen]
posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
Consider
http://support.microsoft.com/default...22&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
If connected to an SQL server... the option works fine. If connected to an
MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
John D. Sullivan
ABELSoft Corporation
(email address munged)
This is not just an MSDE problem. We have had problems with out Enterprise
version not deleting backups. Never resolved it. Wrote scripts to delete
files.
Bob
SuccessWare Software
"John D. Sullivan" <noone@.nothing.com> wrote in message
news:uGWJDreaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/default...22&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>
|||Excellent information that is very relevant. Thank you Mark. It is very
useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
platform. While it is true that SQL 7 has no Database Maintenance Plan
branch under Management in the Enterprise Manager and therefore connecting
to an SQL 2k it is not possible to edit or delete a "plan" but only the
Job/steps... the problem I am citing is a bit different.
What I mean to say is that when creating a new Database Maintenance Plan
using the GUI Enterprise Manager (2000) that takes one through a multi-step
"wizard", it is possible to "check box" that you wish to "Remove files older
than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
service packs) is the engine/server the GUI of the Maintenance Plan Wizard
shows blanks for the qty/units of days/weeks to remove old files.
Workaround: if you go ahead and checkbox to remove old files, manually fill
in a number for qty (you cannot fill in units) and continue through the
wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
xp_sqlmaint (notice this is the extended stored procedure equivalent of
command prompt sqlmaint.exe) will have slight syntax errors in the argument
list. The resulting Job fails to execute. Obviously the -DelBkUps switch
will not have the correct <time value> after it... if you filled in 77 it
will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
job step and manually tweak the command line arguments until they are
correct, then it will work.
By the way, I find viewing the results of job and job steps pretty much
useless. You might see an error number you can lookup. One ends up copying
the entire command and pasting and running it as a query to find out exactly
where/why it is failing. Instead, I find it very useful to check the option
in the Maintenance Plan to write report to a text file..because the error
message(s) in there are far more detailed and useful.
John D. Sullivan
ABELSoft Corporation
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> Yep, this is a bug. Have a look at these articles:
> BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
> ME Computers
> http://support.microsoft.com/?id=278667
> BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
> http://support.microsoft.com/?id=247879
> FIX: SQLMaint.exe Sets Database Status Incorrectly
> http://support.microsoft.com/?id=276234
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> John D. Sullivan wrote:
any[vbcol=seagreen]
Enterprise[vbcol=seagreen]
does[vbcol=seagreen]
Access[vbcol=seagreen]
and[vbcol=seagreen]
http://support.microsoft.com/default...22&Product=sql[vbcol=seagreen]
on[vbcol=seagreen]
for[vbcol=seagreen]
important[vbcol=seagreen]
an[vbcol=seagreen]
step[vbcol=seagreen]
bug[vbcol=seagreen]
Bug in Database Maintenance Plans Enterprise Manager and MSDE v SQL
I have seen this "bug" many times personally, but cannot seem to find any
posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
Consider
http://support.microsoft.com/default...22&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
If connected to an SQL server... the option works fine. If connected to an
MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
John D. Sullivan
ABELSoft Corporation
(email address munged)
This is not just an MSDE problem. We have had problems with out Enterprise
version not deleting backups. Never resolved it. Wrote scripts to delete
files.
Bob
SuccessWare Software
"John D. Sullivan" <noone@.nothing.com> wrote in message
news:uGWJDreaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/default...22&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>
|||Excellent information that is very relevant. Thank you Mark. It is very
useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
platform. While it is true that SQL 7 has no Database Maintenance Plan
branch under Management in the Enterprise Manager and therefore connecting
to an SQL 2k it is not possible to edit or delete a "plan" but only the
Job/steps... the problem I am citing is a bit different.
What I mean to say is that when creating a new Database Maintenance Plan
using the GUI Enterprise Manager (2000) that takes one through a multi-step
"wizard", it is possible to "check box" that you wish to "Remove files older
than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
service packs) is the engine/server the GUI of the Maintenance Plan Wizard
shows blanks for the qty/units of days/weeks to remove old files.
Workaround: if you go ahead and checkbox to remove old files, manually fill
in a number for qty (you cannot fill in units) and continue through the
wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
xp_sqlmaint (notice this is the extended stored procedure equivalent of
command prompt sqlmaint.exe) will have slight syntax errors in the argument
list. The resulting Job fails to execute. Obviously the -DelBkUps switch
will not have the correct <time value> after it... if you filled in 77 it
will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
job step and manually tweak the command line arguments until they are
correct, then it will work.
By the way, I find viewing the results of job and job steps pretty much
useless. You might see an error number you can lookup. One ends up copying
the entire command and pasting and running it as a query to find out exactly
where/why it is failing. Instead, I find it very useful to check the option
in the Maintenance Plan to write report to a text file..because the error
message(s) in there are far more detailed and useful.
John D. Sullivan
ABELSoft Corporation
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> Yep, this is a bug. Have a look at these articles:
> BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
> ME Computers
> http://support.microsoft.com/?id=278667
> BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
> http://support.microsoft.com/?id=247879
> FIX: SQLMaint.exe Sets Database Status Incorrectly
> http://support.microsoft.com/?id=276234
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> John D. Sullivan wrote:
any[vbcol=seagreen]
Enterprise[vbcol=seagreen]
does[vbcol=seagreen]
Access[vbcol=seagreen]
and[vbcol=seagreen]
http://support.microsoft.com/default...22&Product=sql[vbcol=seagreen]
on[vbcol=seagreen]
for[vbcol=seagreen]
important[vbcol=seagreen]
an[vbcol=seagreen]
step[vbcol=seagreen]
bug[vbcol=seagreen]
posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
Consider
http://support.microsoft.com/default...22&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
If connected to an SQL server... the option works fine. If connected to an
MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
John D. Sullivan
ABELSoft Corporation
(email address munged)
This is not just an MSDE problem. We have had problems with out Enterprise
version not deleting backups. Never resolved it. Wrote scripts to delete
files.
Bob
SuccessWare Software
"John D. Sullivan" <noone@.nothing.com> wrote in message
news:uGWJDreaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/default...22&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>
|||Excellent information that is very relevant. Thank you Mark. It is very
useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
platform. While it is true that SQL 7 has no Database Maintenance Plan
branch under Management in the Enterprise Manager and therefore connecting
to an SQL 2k it is not possible to edit or delete a "plan" but only the
Job/steps... the problem I am citing is a bit different.
What I mean to say is that when creating a new Database Maintenance Plan
using the GUI Enterprise Manager (2000) that takes one through a multi-step
"wizard", it is possible to "check box" that you wish to "Remove files older
than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
service packs) is the engine/server the GUI of the Maintenance Plan Wizard
shows blanks for the qty/units of days/weeks to remove old files.
Workaround: if you go ahead and checkbox to remove old files, manually fill
in a number for qty (you cannot fill in units) and continue through the
wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
xp_sqlmaint (notice this is the extended stored procedure equivalent of
command prompt sqlmaint.exe) will have slight syntax errors in the argument
list. The resulting Job fails to execute. Obviously the -DelBkUps switch
will not have the correct <time value> after it... if you filled in 77 it
will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
job step and manually tweak the command line arguments until they are
correct, then it will work.
By the way, I find viewing the results of job and job steps pretty much
useless. You might see an error number you can lookup. One ends up copying
the entire command and pasting and running it as a query to find out exactly
where/why it is failing. Instead, I find it very useful to check the option
in the Maintenance Plan to write report to a text file..because the error
message(s) in there are far more detailed and useful.
John D. Sullivan
ABELSoft Corporation
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> Yep, this is a bug. Have a look at these articles:
> BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
> ME Computers
> http://support.microsoft.com/?id=278667
> BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
> http://support.microsoft.com/?id=247879
> FIX: SQLMaint.exe Sets Database Status Incorrectly
> http://support.microsoft.com/?id=276234
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> John D. Sullivan wrote:
any[vbcol=seagreen]
Enterprise[vbcol=seagreen]
does[vbcol=seagreen]
Access[vbcol=seagreen]
and[vbcol=seagreen]
http://support.microsoft.com/default...22&Product=sql[vbcol=seagreen]
on[vbcol=seagreen]
for[vbcol=seagreen]
important[vbcol=seagreen]
an[vbcol=seagreen]
step[vbcol=seagreen]
bug[vbcol=seagreen]
Bug in Database Maintenance Plans Enterprise Manager and MSDE v SQL
I have seen this "bug" many times personally, but cannot seem to find any
posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
Consider
http://support.microsoft.com/defaul...022&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
If connected to an SQL server... the option works fine. If connected to an
MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
John D. Sullivan
ABELSoft Corporation
(email address munged)This is not just an MSDE problem. We have had problems with out Enterprise
version not deleting backups. Never resolved it. Wrote scripts to delete
files.
Bob
SuccessWare Software
"John D. Sullivan" <noone@.nothing.com> wrote in message
news:uGWJDreaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/defaul...022&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>|||Excellent information that is very relevant. Thank you Mark. It is very
useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
platform. While it is true that SQL 7 has no Database Maintenance Plan
branch under Management in the Enterprise Manager and therefore connecting
to an SQL 2k it is not possible to edit or delete a "plan" but only the
Job/steps... the problem I am citing is a bit different.
What I mean to say is that when creating a new Database Maintenance Plan
using the GUI Enterprise Manager (2000) that takes one through a multi-step
"wizard", it is possible to "check box" that you wish to "Remove files older
than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
service packs) is the engine/server the GUI of the Maintenance Plan Wizard
shows blanks for the qty/units of days/weeks to remove old files.
Workaround: if you go ahead and checkbox to remove old files, manually fill
in a number for qty (you cannot fill in units) and continue through the
wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
xp_sqlmaint (notice this is the extended stored procedure equivalent of
command prompt sqlmaint.exe) will have slight syntax errors in the argument
list. The resulting Job fails to execute. Obviously the -DelBkUps switch
will not have the correct <time value> after it... if you filled in 77 it
will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
job step and manually tweak the command line arguments until they are
correct, then it will work.
By the way, I find viewing the results of job and job steps pretty much
useless. You might see an error number you can lookup. One ends up copying
the entire command and pasting and running it as a query to find out exactly
where/why it is failing. Instead, I find it very useful to check the option
in the Maintenance Plan to write report to a text file..because the error
message(s) in there are far more detailed and useful.
John D. Sullivan
ABELSoft Corporation
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> Yep, this is a bug. Have a look at these articles:
> BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
> ME Computers
> http://support.microsoft.com/?id=278667
> BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
> http://support.microsoft.com/?id=247879
> FIX: SQLMaint.exe Sets Database Status Incorrectly
> http://support.microsoft.com/?id=276234
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> John D. Sullivan wrote:
any[vbcol=seagreen]
Enterprise[vbcol=seagreen]
does[vbcol=seagreen]
Access[vbcol=seagreen]
and[vbcol=seagreen]
http://support.microsoft.com/defaul...022&Product=sql[vbcol=seagreen]
on[vbcol=seagreen]
for[vbcol=seagreen]
important[vbcol=seagreen]
an[vbcol=seagreen]
step[vbcol=seagreen]
bug[vbcol=seagreen]
posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
Consider
http://support.microsoft.com/defaul...022&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
If connected to an SQL server... the option works fine. If connected to an
MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
John D. Sullivan
ABELSoft Corporation
(email address munged)This is not just an MSDE problem. We have had problems with out Enterprise
version not deleting backups. Never resolved it. Wrote scripts to delete
files.
Bob
SuccessWare Software
"John D. Sullivan" <noone@.nothing.com> wrote in message
news:uGWJDreaEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I have seen this "bug" many times personally, but cannot seem to find any
> posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
> Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> Consider
> http://support.microsoft.com/defaul...022&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an
> MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
> John D. Sullivan
> ABELSoft Corporation
> (email address munged)
>|||Excellent information that is very relevant. Thank you Mark. It is very
useful to know that the sqlmaint.exe tool fails to delete the backups on 9x
platform. While it is true that SQL 7 has no Database Maintenance Plan
branch under Management in the Enterprise Manager and therefore connecting
to an SQL 2k it is not possible to edit or delete a "plan" but only the
Job/steps... the problem I am citing is a bit different.
What I mean to say is that when creating a new Database Maintenance Plan
using the GUI Enterprise Manager (2000) that takes one through a multi-step
"wizard", it is possible to "check box" that you wish to "Remove files older
than" but the boxes for quantity and units (e.g. 4, Weeks) are fine if
manageing an SQL 2000 edition other than MSDE but when MSDE 2000 (all
service packs) is the engine/server the GUI of the Maintenance Plan Wizard
shows blanks for the qty/units of days/weeks to remove old files.
Workaround: if you go ahead and checkbox to remove old files, manually fill
in a number for qty (you cannot fill in units) and continue through the
wizard it WILL create the Job/step(s)... however the job step that EXECUTEs
xp_sqlmaint (notice this is the extended stored procedure equivalent of
command prompt sqlmaint.exe) will have slight syntax errors in the argument
list. The resulting Job fails to execute. Obviously the -DelBkUps switch
will not have the correct <time value> after it... if you filled in 77 it
will say 77 but has no WEEKS or DAYS or what have you. One has to edit the
job step and manually tweak the command line arguments until they are
correct, then it will work.
By the way, I find viewing the results of job and job steps pretty much
useless. You might see an error number you can lookup. One ends up copying
the entire command and pasting and running it as a query to find out exactly
where/why it is failing. Instead, I find it very useful to check the option
in the Maintenance Plan to write report to a text file..because the error
message(s) in there are far more detailed and useful.
John D. Sullivan
ABELSoft Corporation
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OAZ539laEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> Yep, this is a bug. Have a look at these articles:
> BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or
> ME Computers
> http://support.microsoft.com/?id=278667
> BUG: Cannot Edit or Delete Database Maintenance Plan on MSDE Installation
> http://support.microsoft.com/?id=247879
> FIX: SQLMaint.exe Sets Database Status Incorrectly
> http://support.microsoft.com/?id=276234
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> John D. Sullivan wrote:
any[vbcol=seagreen]
Enterprise[vbcol=seagreen]
does[vbcol=seagreen]
Access[vbcol=seagreen]
and[vbcol=seagreen]
http://support.microsoft.com/defaul...022&Product=sql[vbcol=seagreen]
on[vbcol=seagreen]
for[vbcol=seagreen]
important[vbcol=seagreen]
an[vbcol=seagreen]
step[vbcol=seagreen]
bug[vbcol=seagreen]
Bug ?->Can not make a new connection to SQL Server 2005 X64
Can not make a new connection to SQL Server 2005 X64 until the SQL Server
service is restarted.
OS is Windows 2003 X64 Enterprise Edition all updates are applied.
This repeats every few hours. Server protocols enabled are TCP IP and Named
Pipes
Client protocols are Named Piped only.
The following error is thrown MSg 17
"SQL Server does not exist or access is denied"
Since I am not finding solution this problem on many forums I assume this is
a bug.
Thanks
Kiran"SQL Server does not exist or access is denied" could indicate a problem
resolving the name of the instance. Is this a default instance listening on
the default named pipe, or a named instance? The second would require the
SQL Server Browser Service or require connecting to a specific pipe name. So
is the SQL Server Browser Service running?
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:9CED6B9C-9643-43C5-9961-8BD1E00BFA8A@.microsoft.com...
> Can not make a new connection to SQL Server 2005 X64 until the SQL Server
> service is restarted.
> OS is Windows 2003 X64 Enterprise Edition all updates are applied.
> This repeats every few hours. Server protocols enabled are TCP IP and
> Named
> Pipes
> Client protocols are Named Piped only.
> The following error is thrown MSg 17
> "SQL Server does not exist or access is denied"
> Since I am not finding solution this problem on many forums I assume this
> is
> a bug.
> Thanks
> Kiran
>
service is restarted.
OS is Windows 2003 X64 Enterprise Edition all updates are applied.
This repeats every few hours. Server protocols enabled are TCP IP and Named
Pipes
Client protocols are Named Piped only.
The following error is thrown MSg 17
"SQL Server does not exist or access is denied"
Since I am not finding solution this problem on many forums I assume this is
a bug.
Thanks
Kiran"SQL Server does not exist or access is denied" could indicate a problem
resolving the name of the instance. Is this a default instance listening on
the default named pipe, or a named instance? The second would require the
SQL Server Browser Service or require connecting to a specific pipe name. So
is the SQL Server Browser Service running?
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:9CED6B9C-9643-43C5-9961-8BD1E00BFA8A@.microsoft.com...
> Can not make a new connection to SQL Server 2005 X64 until the SQL Server
> service is restarted.
> OS is Windows 2003 X64 Enterprise Edition all updates are applied.
> This repeats every few hours. Server protocols enabled are TCP IP and
> Named
> Pipes
> Client protocols are Named Piped only.
> The following error is thrown MSg 17
> "SQL Server does not exist or access is denied"
> Since I am not finding solution this problem on many forums I assume this
> is
> a bug.
> Thanks
> Kiran
>
Bug ?->Can not make a new connection to SQL Server 2005 X64
Can not make a new connection to SQL Server 2005 X64 until the SQL Server
service is restarted.
OS is Windows 2003 X64 Enterprise Edition all updates are applied.
This repeats every few hours. Server protocols enabled are TCP IP and Named
Pipes
Client protocols are Named Piped only.
The following error is thrown MSg 17
"SQL Server does not exist or access is denied"
Since I am not finding solution this problem on many forums I assume this is
a bug.
Thanks
Kiran
"SQL Server does not exist or access is denied" could indicate a problem
resolving the name of the instance. Is this a default instance listening on
the default named pipe, or a named instance? The second would require the
SQL Server Browser Service or require connecting to a specific pipe name. So
is the SQL Server Browser Service running?
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:9CED6B9C-9643-43C5-9961-8BD1E00BFA8A@.microsoft.com...
> Can not make a new connection to SQL Server 2005 X64 until the SQL Server
> service is restarted.
> OS is Windows 2003 X64 Enterprise Edition all updates are applied.
> This repeats every few hours. Server protocols enabled are TCP IP and
> Named
> Pipes
> Client protocols are Named Piped only.
> The following error is thrown MSg 17
> "SQL Server does not exist or access is denied"
> Since I am not finding solution this problem on many forums I assume this
> is
> a bug.
> Thanks
> Kiran
>
service is restarted.
OS is Windows 2003 X64 Enterprise Edition all updates are applied.
This repeats every few hours. Server protocols enabled are TCP IP and Named
Pipes
Client protocols are Named Piped only.
The following error is thrown MSg 17
"SQL Server does not exist or access is denied"
Since I am not finding solution this problem on many forums I assume this is
a bug.
Thanks
Kiran
"SQL Server does not exist or access is denied" could indicate a problem
resolving the name of the instance. Is this a default instance listening on
the default named pipe, or a named instance? The second would require the
SQL Server Browser Service or require connecting to a specific pipe name. So
is the SQL Server Browser Service running?
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:9CED6B9C-9643-43C5-9961-8BD1E00BFA8A@.microsoft.com...
> Can not make a new connection to SQL Server 2005 X64 until the SQL Server
> service is restarted.
> OS is Windows 2003 X64 Enterprise Edition all updates are applied.
> This repeats every few hours. Server protocols enabled are TCP IP and
> Named
> Pipes
> Client protocols are Named Piped only.
> The following error is thrown MSg 17
> "SQL Server does not exist or access is denied"
> Since I am not finding solution this problem on many forums I assume this
> is
> a bug.
> Thanks
> Kiran
>
Sunday, February 19, 2012
broke sql server...please help
so i installed sql server and i was using it within enterprise manager. Then it broke when i started trying to make my web application connect to my database. So my connection string was:
server=(local);database=capacity;User ID=dbo;trusted_connection=False
i got this error: Login failed for user 'dbo'. Reason: Not associated with a trusted SQL Server connection.
so i tried messing around with the string and found something online that said i should right click the server (local) and go to the security settings and change it to mixed mode, sql server authentication AND windows authentication. Before it was just windows authentication. So i did that...and now i get this error when i try to open up my server in enterprise manager:
A connection could not be established to (local)
Reason: Login failed for user 'samr'. Reason Not associated with a trusted SQL Server Connection..
Please verify SQL Server is running (which it is) and check your SQL server registration properties by right clicking on the local node and try again.
i can right click on it, but whenever i do anything (go to properties, click the '+' icon) i get this message:
Connection failed. Check sql server registration properties.
please help...
should i delete the registration and just start over...?
why couldnt i connect into sql server...?Take a look @.INF: Windows Authentication is the Default Security Mode After a SQL Server 2000 Typical Installation
Thursday, February 16, 2012
Bring back Query Analyser
Hi,
I am using 2005 SQL Management to edit my T-SQL stuff but it reformats all
of my SQL statements (just like Enterprise Manager) making complex
statements impossible to read.
Can anyone tell me if there any options to turn this off and/or an
alternative?
Thanks for any help,
Steve.I am using 2K5 SQL Management, your problem doesn't occur at my site.
Do you use query builder?
Steve Lloyd wrote:
> Hi,
> I am using 2005 SQL Management to edit my T-SQL stuff but it reformats all
> of my SQL statements (just like Enterprise Manager) making complex
> statements impossible to read.
> Can anyone tell me if there any options to turn this off and/or an
> alternative?
> Thanks for any help,
> Steve.|||Hi, thanks for the reply,
Ok, find out a few more things... Stored procedures open using query builder
and I can retain the formatting but Views open Enterpise Manager style and I
can't work out how to stop it...
Thanks again
Steve
<navyzhu@.gmail.com> wrote in message
news:1149757541.865478.194360@.h76g2000cwa.googlegroups.com...
>I am using 2K5 SQL Management, your problem doesn't occur at my site.
> Do you use query builder?
> Steve Lloyd wrote:
>|||Steve Lloyd (steve.remove@.livenowpaylater.this.co.uk) writes:
> Ok, find out a few more things... Stored procedures open using query
> builder and I can retain the formatting but Views open Enterpise Manager
> style and I can't work out how to stop it...
Stop using the Query Designer.
After all, since the subject line says bring back "Query Analyzer", you
should be used to be without the Query Designer as it is not present in
Query Analyzer.
As long as you stick to the regular query editor in Mgmt Studio, there
should be no reformatting.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In addition to Erland's post:
Keep your DDL scripts somewhere safe, and only use them to create/alter/drop
SQL objects.
ML
http://milambda.blogspot.com/|||OK, it think I'm not making myself clear.. I want to be able to edit my
Views, Query Analyzer style, but when I open them in SQL Management they
default to the Query Builder and I can't work out how to open them without.
Sorry for the confusion...
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97DC7DAD2FAEBYazorman@.127.0.0.1...
> Steve Lloyd (steve.remove@.livenowpaylater.this.co.uk) writes:
> Stop using the Query Designer.
> After all, since the subject line says bring back "Query Analyzer", you
> should be used to be without the Query Designer as it is not present in
> Query Analyzer.
> As long as you stick to the regular query editor in Mgmt Studio, there
> should be no reformatting.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Right click on the view
DO NOT select modify view
Instead choose Script View as -->ALTER To--> New Query window
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Steve Lloyd wrote:
> OK, it think I'm not making myself clear.. I want to be able to edit my
> Views, Query Analyzer style, but when I open them in SQL Management they
> default to the Query Builder and I can't work out how to open them without
.
> Sorry for the confusion...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97DC7DAD2FAEBYazorman@.127.0.0.1...|||> Right click on the view
> DO NOT select modify view
> Instead choose Script View as -->ALTER To--> New Query window
This is easily in my top ten of pet peeves in Management Studio. Not just
because Modify does this weird thing with a useless Access-style view
designer that drives me bonkers, but combined with the slowness and the
frequency that I click the wrong menu item, it is maddening.
I was told off the cuff that they're going to add some options to the
immediate right-click menu for views, but I don't know when they'll be
implemented...
I am using 2005 SQL Management to edit my T-SQL stuff but it reformats all
of my SQL statements (just like Enterprise Manager) making complex
statements impossible to read.
Can anyone tell me if there any options to turn this off and/or an
alternative?
Thanks for any help,
Steve.I am using 2K5 SQL Management, your problem doesn't occur at my site.
Do you use query builder?
Steve Lloyd wrote:
> Hi,
> I am using 2005 SQL Management to edit my T-SQL stuff but it reformats all
> of my SQL statements (just like Enterprise Manager) making complex
> statements impossible to read.
> Can anyone tell me if there any options to turn this off and/or an
> alternative?
> Thanks for any help,
> Steve.|||Hi, thanks for the reply,
Ok, find out a few more things... Stored procedures open using query builder
and I can retain the formatting but Views open Enterpise Manager style and I
can't work out how to stop it...
Thanks again
Steve
<navyzhu@.gmail.com> wrote in message
news:1149757541.865478.194360@.h76g2000cwa.googlegroups.com...
>I am using 2K5 SQL Management, your problem doesn't occur at my site.
> Do you use query builder?
> Steve Lloyd wrote:
>|||Steve Lloyd (steve.remove@.livenowpaylater.this.co.uk) writes:
> Ok, find out a few more things... Stored procedures open using query
> builder and I can retain the formatting but Views open Enterpise Manager
> style and I can't work out how to stop it...
Stop using the Query Designer.
After all, since the subject line says bring back "Query Analyzer", you
should be used to be without the Query Designer as it is not present in
Query Analyzer.
As long as you stick to the regular query editor in Mgmt Studio, there
should be no reformatting.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In addition to Erland's post:
Keep your DDL scripts somewhere safe, and only use them to create/alter/drop
SQL objects.
ML
http://milambda.blogspot.com/|||OK, it think I'm not making myself clear.. I want to be able to edit my
Views, Query Analyzer style, but when I open them in SQL Management they
default to the Query Builder and I can't work out how to open them without.
Sorry for the confusion...
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97DC7DAD2FAEBYazorman@.127.0.0.1...
> Steve Lloyd (steve.remove@.livenowpaylater.this.co.uk) writes:
> Stop using the Query Designer.
> After all, since the subject line says bring back "Query Analyzer", you
> should be used to be without the Query Designer as it is not present in
> Query Analyzer.
> As long as you stick to the regular query editor in Mgmt Studio, there
> should be no reformatting.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Right click on the view
DO NOT select modify view
Instead choose Script View as -->ALTER To--> New Query window
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Steve Lloyd wrote:
> OK, it think I'm not making myself clear.. I want to be able to edit my
> Views, Query Analyzer style, but when I open them in SQL Management they
> default to the Query Builder and I can't work out how to open them without
.
> Sorry for the confusion...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97DC7DAD2FAEBYazorman@.127.0.0.1...|||> Right click on the view
> DO NOT select modify view
> Instead choose Script View as -->ALTER To--> New Query window
This is easily in my top ten of pet peeves in Management Studio. Not just
because Modify does this weird thing with a useless Access-style view
designer that drives me bonkers, but combined with the slowness and the
frequency that I click the wrong menu item, it is maddening.
I was told off the cuff that they're going to add some options to the
immediate right-click menu for views, but I don't know when they'll be
implemented...
Tuesday, February 14, 2012
Break Deadlocks Quicker
Is there a parameter within SQL Server 2000 Enterprise
Edition that will break deadlocks quicker and use less
resources?
Thanks,
MikeSQL Server usually detects deadlocks pretty quickly and when it does it
wastes no time killing one of the participants. You can however use a hint
called SET DEADLOCK_PRIORITY to potentially affect who gets killed.
--
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:f55e01c43dcf$fb009320$a301280a@.phx.gbl...
> Is there a parameter within SQL Server 2000 Enterprise
> Edition that will break deadlocks quicker and use less
> resources?
> Thanks,
> Mike
>|||Mike,
you might want to check out
SET LOCK_TIMEOUT
SET DEADLOCK_PRIORITY
in BOL.
HTH,
Paul Ibison
Edition that will break deadlocks quicker and use less
resources?
Thanks,
MikeSQL Server usually detects deadlocks pretty quickly and when it does it
wastes no time killing one of the participants. You can however use a hint
called SET DEADLOCK_PRIORITY to potentially affect who gets killed.
--
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:f55e01c43dcf$fb009320$a301280a@.phx.gbl...
> Is there a parameter within SQL Server 2000 Enterprise
> Edition that will break deadlocks quicker and use less
> resources?
> Thanks,
> Mike
>|||Mike,
you might want to check out
SET LOCK_TIMEOUT
SET DEADLOCK_PRIORITY
in BOL.
HTH,
Paul Ibison
Sunday, February 12, 2012
Brackets appear in column name
I am using Enterprise Manager to create database tables. In the table design view I am trying to create a column called, section. After typing, section, Enterprise Manager automatically puts brackets around the name, ex. [section]. When I view the table by returning all rows the brackets are gone. However, when I go back to desing view the brackets are there. Why is this happening and what affect does it have on my database?
Thanks,
Matt
That is 'normal' and expected behavior when your table or column names contain spaces or other 'invalid' characters.
SQL Server, unlike Access you may have been using, does not allow the use of spaces in object (tables, columns, etc.) names. You will always have to use those 'pesky' square brackets when you write any SQL code specifying the improperly named items.
Other than the extra work always having to use the square brackets when writing code, there is no discernable impact on the database. The database assigns an 'ObjectID' to all tables, columns, etc., and internally, it uses the ObjectID.
|||Arnie,
The column name I am trying to use is, section, with no spaces but Enterprise Manager is automatically putting the brackets on. Any ideas?
Thanks,
Matt
|||I should have included that square brackets are also included around 'reserved words' when used as object names.
You may wish to examine the 'reserved word' list in Books Online, and then avoid using any reserved words in your object names.
As I indicated before, if EM adds the square brackets, you will also be required to also use square brackets (or double quotes) when referring to those improperly named objects.
|||Arnie,
I found the word, section, in the Books Online. It is an ODBC reserved word. Thanks for the help,
Matt
|||Matt,
If that information helped you solve your issue, please mark the post as helpful.
Thanks
Brackets appear in column name
I am using Enterprise Manager to create database tables. In the table design view I am trying to create a column called, section. After typing, section, Enterprise Manager automatically puts brackets around the name, ex. [section]. When I view the table by returning all rows the brackets are gone. However, when I go back to desing view the brackets are there. Why is this happening and what affect does it have on my database?
Thanks,
Matt
That is 'normal' and expected behavior when your table or column names contain spaces or other 'invalid' characters.
SQL Server, unlike Access you may have been using, does not allow the use of spaces in object (tables, columns, etc.) names. You will always have to use those 'pesky' square brackets when you write any SQL code specifying the improperly named items.
Other than the extra work always having to use the square brackets when writing code, there is no discernable impact on the database. The database assigns an 'ObjectID' to all tables, columns, etc., and internally, it uses the ObjectID.
|||Arnie,
The column name I am trying to use is, section, with no spaces but Enterprise Manager is automatically putting the brackets on. Any ideas?
Thanks,
Matt
|||I should have included that square brackets are also included around 'reserved words' when used as object names.
You may wish to examine the 'reserved word' list in Books Online, and then avoid using any reserved words in your object names.
As I indicated before, if EM adds the square brackets, you will also be required to also use square brackets (or double quotes) when referring to those improperly named objects.
|||Arnie,
I found the word, section, in the Books Online. It is an ODBC reserved word. Thanks for the help,
Matt
|||Matt,
If that information helped you solve your issue, please mark the post as helpful.
Thanks
Friday, February 10, 2012
BOOT.INI File for 4GB RAM and SQL 2005
I have 3 questions regarding memory configuration on a Windows Server 2003
SP2 Enterprise Edition with SQL2005 SP2.
1. Can someone advise on the correct boot.ini file configuration to utilize
4GB of local memory? I'm not sure if I need the 3GB switch?
multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
the sp_configure awe enabled config_value=1 and run_value=1 ?
3. Assuming nothing else is to be done to have SQL 2005 utilize more than
the 2GB memory space, how do you verify or validate SQL 2005 is using more
memory?
Thanks in advancezorro
Is it 64 bit or 32 bit?
> 1. Can someone advise on the correct boot.ini file configuration to
> utilize
> 4GB of local memory? I'm not sure if I need the 3GB switch?
You do need 3GB switch
> 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> the sp_configure awe enabled config_value=1 and run_value=1 ?
AWE is for utilyze more than 4GB
Yes, unless you use 64 bit , then you do not need AWE , make sure that an
account SQL Serverc runs under is added to LockPages in Memory Local Group
Policy
> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> the 2GB memory space, how do you verify or validate SQL 2005 is using more
> memory?
Task Manager
"zorro" <zorro@.discussions.microsoft.com> wrote in message
news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
>I have 3 questions regarding memory configuration on a Windows Server 2003
> SP2 Enterprise Edition with SQL2005 SP2.
> 1. Can someone advise on the correct boot.ini file configuration to
> utilize
> 4GB of local memory? I'm not sure if I need the 3GB switch?
> multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
> 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> the sp_configure awe enabled config_value=1 and run_value=1 ?
> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> the 2GB memory space, how do you verify or validate SQL 2005 is using more
> memory?
> Thanks in advance
>|||>> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
>> the 2GB memory space, how do you verify or validate SQL 2005 is using
>> more
>> memory?
> Task Manager
Actually, I would use performance monitor. Task manager tends to
under-report what SQL Server is actually using.|||Aaron
I agreee. I assumed the OP wants to utilize 4GB ONLY , so after adding
3GB switch you can actually observe in TM.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23qL67StfIHA.5088@.TK2MSFTNGP02.phx.gbl...
>> 3. Assuming nothing else is to be done to have SQL 2005 utilize more
>> than
>> the 2GB memory space, how do you verify or validate SQL 2005 is using
>> more
>> memory?
>> Task Manager
> Actually, I would use performance monitor. Task manager tends to
> under-report what SQL Server is actually using.|||Thanks Uri,
1. 32 bit Windows Server 2003 version.
2. To verify 3GB switch is needed for my 5GB physically installed RAM?
3. Also good catch on the LockPages in Memory setting within the gpedit.msc
Thanks,
zorro
"Uri Dimant" wrote:
> zorro
> Is it 64 bit or 32 bit?
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> You do need 3GB switch
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> AWE is for utilyze more than 4GB
> Yes, unless you use 64 bit , then you do not need AWE , make sure that an
> account SQL Serverc runs under is added to LockPages in Memory Local Group
> Policy
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> Task Manager
>
>
> "zorro" <zorro@.discussions.microsoft.com> wrote in message
> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
> >I have 3 questions regarding memory configuration on a Windows Server 2003
> > SP2 Enterprise Edition with SQL2005 SP2.
> >
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> >
> > multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> > Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
> >
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> >
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> >
> > Thanks in advance
> >
> >
>
>|||See comments inline.
Linchi
"Uri Dimant" wrote:
> zorro
> Is it 64 bit or 32 bit?
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> You do need 3GB switch
>
Why?
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> AWE is for utilyze more than 4GB
I keep hearing that AWE is for utilizing more than 4GB. Not sure where that
comes from. Technically, it's not.
> Yes, unless you use 64 bit , then you do not need AWE , make sure that an
> account SQL Serverc runs under is added to LockPages in Memory Local Group
> Policy
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> Task Manager
>
>
> "zorro" <zorro@.discussions.microsoft.com> wrote in message
> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
> >I have 3 questions regarding memory configuration on a Windows Server 2003
> > SP2 Enterprise Edition with SQL2005 SP2.
> >
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> >
> > multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> > Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
> >
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> >
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> >
> > Thanks in advance
> >
> >
>
>|||Hi Linchi
You are right, that technically, it's not. If I have 4GB RAM and want tio
utilize 3GB for SQL Server, so I simple add switch in BOOT.INI and why
bother enabling AWE? What do you think?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:61A4195E-F98E-4AE2-A24F-0BC6AC668D08@.microsoft.com...
> See comments inline.
> Linchi
> "Uri Dimant" wrote:
>> zorro
>> Is it 64 bit or 32 bit?
>> > 1. Can someone advise on the correct boot.ini file configuration to
>> > utilize
>> > 4GB of local memory? I'm not sure if I need the 3GB switch?
>> You do need 3GB switch
> Why?
>> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify
>> > is
>> > the sp_configure awe enabled config_value=1 and run_value=1 ?
>> AWE is for utilyze more than 4GB
> I keep hearing that AWE is for utilizing more than 4GB. Not sure where
> that
> comes from. Technically, it's not.
>> Yes, unless you use 64 bit , then you do not need AWE , make sure that
>> an
>> account SQL Serverc runs under is added to LockPages in Memory Local
>> Group
>> Policy
>> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more
>> > than
>> > the 2GB memory space, how do you verify or validate SQL 2005 is using
>> > more
>> > memory?
>> Task Manager
>>
>>
>> "zorro" <zorro@.discussions.microsoft.com> wrote in message
>> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
>> >I have 3 questions regarding memory configuration on a Windows Server
>> >2003
>> > SP2 Enterprise Edition with SQL2005 SP2.
>> >
>> > 1. Can someone advise on the correct boot.ini file configuration to
>> > utilize
>> > 4GB of local memory? I'm not sure if I need the 3GB switch?
>> >
>> > multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
>> > Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
>> >
>> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify
>> > is
>> > the sp_configure awe enabled config_value=1 and run_value=1 ?
>> >
>> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more
>> > than
>> > the 2GB memory space, how do you verify or validate SQL 2005 is using
>> > more
>> > memory?
>> >
>> > Thanks in advance
>> >
>> >
>>
SP2 Enterprise Edition with SQL2005 SP2.
1. Can someone advise on the correct boot.ini file configuration to utilize
4GB of local memory? I'm not sure if I need the 3GB switch?
multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
the sp_configure awe enabled config_value=1 and run_value=1 ?
3. Assuming nothing else is to be done to have SQL 2005 utilize more than
the 2GB memory space, how do you verify or validate SQL 2005 is using more
memory?
Thanks in advancezorro
Is it 64 bit or 32 bit?
> 1. Can someone advise on the correct boot.ini file configuration to
> utilize
> 4GB of local memory? I'm not sure if I need the 3GB switch?
You do need 3GB switch
> 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> the sp_configure awe enabled config_value=1 and run_value=1 ?
AWE is for utilyze more than 4GB
Yes, unless you use 64 bit , then you do not need AWE , make sure that an
account SQL Serverc runs under is added to LockPages in Memory Local Group
Policy
> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> the 2GB memory space, how do you verify or validate SQL 2005 is using more
> memory?
Task Manager
"zorro" <zorro@.discussions.microsoft.com> wrote in message
news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
>I have 3 questions regarding memory configuration on a Windows Server 2003
> SP2 Enterprise Edition with SQL2005 SP2.
> 1. Can someone advise on the correct boot.ini file configuration to
> utilize
> 4GB of local memory? I'm not sure if I need the 3GB switch?
> multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
> 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> the sp_configure awe enabled config_value=1 and run_value=1 ?
> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> the 2GB memory space, how do you verify or validate SQL 2005 is using more
> memory?
> Thanks in advance
>|||>> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
>> the 2GB memory space, how do you verify or validate SQL 2005 is using
>> more
>> memory?
> Task Manager
Actually, I would use performance monitor. Task manager tends to
under-report what SQL Server is actually using.|||Aaron
I agreee. I assumed the OP wants to utilize 4GB ONLY , so after adding
3GB switch you can actually observe in TM.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23qL67StfIHA.5088@.TK2MSFTNGP02.phx.gbl...
>> 3. Assuming nothing else is to be done to have SQL 2005 utilize more
>> than
>> the 2GB memory space, how do you verify or validate SQL 2005 is using
>> more
>> memory?
>> Task Manager
> Actually, I would use performance monitor. Task manager tends to
> under-report what SQL Server is actually using.|||Thanks Uri,
1. 32 bit Windows Server 2003 version.
2. To verify 3GB switch is needed for my 5GB physically installed RAM?
3. Also good catch on the LockPages in Memory setting within the gpedit.msc
Thanks,
zorro
"Uri Dimant" wrote:
> zorro
> Is it 64 bit or 32 bit?
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> You do need 3GB switch
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> AWE is for utilyze more than 4GB
> Yes, unless you use 64 bit , then you do not need AWE , make sure that an
> account SQL Serverc runs under is added to LockPages in Memory Local Group
> Policy
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> Task Manager
>
>
> "zorro" <zorro@.discussions.microsoft.com> wrote in message
> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
> >I have 3 questions regarding memory configuration on a Windows Server 2003
> > SP2 Enterprise Edition with SQL2005 SP2.
> >
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> >
> > multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> > Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
> >
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> >
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> >
> > Thanks in advance
> >
> >
>
>|||See comments inline.
Linchi
"Uri Dimant" wrote:
> zorro
> Is it 64 bit or 32 bit?
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> You do need 3GB switch
>
Why?
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> AWE is for utilyze more than 4GB
I keep hearing that AWE is for utilizing more than 4GB. Not sure where that
comes from. Technically, it's not.
> Yes, unless you use 64 bit , then you do not need AWE , make sure that an
> account SQL Serverc runs under is added to LockPages in Memory Local Group
> Policy
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> Task Manager
>
>
> "zorro" <zorro@.discussions.microsoft.com> wrote in message
> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
> >I have 3 questions regarding memory configuration on a Windows Server 2003
> > SP2 Enterprise Edition with SQL2005 SP2.
> >
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> >
> > multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> > Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
> >
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> >
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> >
> > Thanks in advance
> >
> >
>
>|||Hi Linchi
You are right, that technically, it's not. If I have 4GB RAM and want tio
utilize 3GB for SQL Server, so I simple add switch in BOOT.INI and why
bother enabling AWE? What do you think?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:61A4195E-F98E-4AE2-A24F-0BC6AC668D08@.microsoft.com...
> See comments inline.
> Linchi
> "Uri Dimant" wrote:
>> zorro
>> Is it 64 bit or 32 bit?
>> > 1. Can someone advise on the correct boot.ini file configuration to
>> > utilize
>> > 4GB of local memory? I'm not sure if I need the 3GB switch?
>> You do need 3GB switch
> Why?
>> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify
>> > is
>> > the sp_configure awe enabled config_value=1 and run_value=1 ?
>> AWE is for utilyze more than 4GB
> I keep hearing that AWE is for utilizing more than 4GB. Not sure where
> that
> comes from. Technically, it's not.
>> Yes, unless you use 64 bit , then you do not need AWE , make sure that
>> an
>> account SQL Serverc runs under is added to LockPages in Memory Local
>> Group
>> Policy
>> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more
>> > than
>> > the 2GB memory space, how do you verify or validate SQL 2005 is using
>> > more
>> > memory?
>> Task Manager
>>
>>
>> "zorro" <zorro@.discussions.microsoft.com> wrote in message
>> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
>> >I have 3 questions regarding memory configuration on a Windows Server
>> >2003
>> > SP2 Enterprise Edition with SQL2005 SP2.
>> >
>> > 1. Can someone advise on the correct boot.ini file configuration to
>> > utilize
>> > 4GB of local memory? I'm not sure if I need the 3GB switch?
>> >
>> > multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
>> > Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
>> >
>> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify
>> > is
>> > the sp_configure awe enabled config_value=1 and run_value=1 ?
>> >
>> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more
>> > than
>> > the 2GB memory space, how do you verify or validate SQL 2005 is using
>> > more
>> > memory?
>> >
>> > Thanks in advance
>> >
>> >
>>
Subscribe to:
Posts (Atom)