Showing posts with label trouble. Show all posts
Showing posts with label trouble. Show all posts

Wednesday, March 28, 2012

HELP! Unable to select database engine in management studio

Hi All,

I am new to SQL Server and having trouble using SQL Server Management Studio. I am unable to select the Database Engine in management studio.

I am able to see the instance of default database engine (MSSQLServer) running in Reporting Services manager as well as in Surface area configuration manager, but it is not visible in the drop down list in Management Studio's "Select Database Engine" menu.

I had removed Sql server 2005 earlier ( I was able to select the database engine in Management Studio then). But when I installed it again, I was unable to install the Sql Server Tools (it said that my Upgrade is blocked). So, I cleaned the Windows Registry of all keys containing 'Sql'. After this I tried installing it again and successfully installed Sql Server 2005 + ALL TOOLS. But this time I am unable to select the database engine in management studio.

Thanks and Regards to ALL

You may need to rebuild the machine from scratch. Did you install any beta version of the 2.0 framework? If so a complete rebuild of the machine may be required.|||

Hi TATWORTH,

Thanks for the reply.

I may not wish to rebuild the machine as it is a triple boot system with 2000/xp/suse 10.1. If I reinstall XP, I may have to reinstall linux...

Is there any alternative?

Regards

|||Try installing SQL Server again - not as a reinstall of the default instance but as a new instance.|||

Ok.

Will reinstall as a seperate instance and tell you.

Regards

Monday, March 26, 2012

HELP! sp_send_dbmail error

I'm having trouble sending an email through DBMail. I'm logged in a a SQL Login user named "ServiceUser". I have added this user to the msdb database and as a rolemember to "DatabaseMailUserRole"

USE [msdb]

GO

CREATEUSER [ServiceUser] FORLOGIN [ServiceUser] WITHDEFAULT_SCHEMA=[guest]

GO

USE [msdb]

GO

EXEC sp_addrolemember N'DatabaseMailUserRole', N'ServiceUser'

GO

When I try to send the email:

exec msdb.dbo.sp_send_dbmail

@.profile_name=@.DatabaseMailProfileToUse,

@.recipients=@.EmailAddress,

@.subject=@.Subject,

@.body=@.MessageBody,

@.mailitem_id=@.MYmailitem_id OUTPUT

I get the following error:

Msg 15404, Level 16, State 10, Procedure xp_logininfo, Line 62

Could not obtain information about Windows NT group/user 'ServiceUser', error code 0xffff0002.

I executed the following on msdb and it didn't help:

execsp_changedbowner'sa'

xp_logininfo also returns the error:

EXECxp_logininfo'ServiceUser'

What is wrong? HELP!

My database mail profile was not public! It needs to be public or set to private for the user that is going to access it.

HELP! sp or other tool to help diagnose recent record deletion?

I'm trying to trouble shoot an app that after two years of running
more or less fine has suddenly had a problem. An average user was
filling out a web form and clicked save, only to have their entire
multi page report form get obliterated. This user has no delete
rights, and even if they had pressed cancel the ASP page would simply
not write that page...but would not delete the entire series of
records.
I am stepping through ASP code and trying to recreate the problem but
I'm relatively new to SQL and not sure what sytem stored procedures or
functions might help me...records of recent deletes (within the last
few hours), userinfo (who really deleted the record)? Etc.
Coincidentally (perhaps) this server was nearly full (aprox 700 megs
left). I know...this is terrible. I've been harping my sysadmin to get
some non DB related stuff off my SQL server and we cleared a few Gigs
off this morning! Anyway, it's very likely the cause...again...could
someone point me to what logs or functions or documentation I should
be looking at that might help me verify this?
Many thanks!!
This might help you -- these guys have some good tools. I recommend,
though, that you restore the database to another area on the server and
compare the data with some custom queries to see what's missing...
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm?gclid=CK-roqn2mIoCFRyVFQodbFaonQ
mahalie@.gmail.com wrote:
> I'm trying to trouble shoot an app that after two years of running
> more or less fine has suddenly had a problem. An average user was
> filling out a web form and clicked save, only to have their entire
> multi page report form get obliterated. This user has no delete
> rights, and even if they had pressed cancel the ASP page would simply
> not write that page...but would not delete the entire series of
> records.
> I am stepping through ASP code and trying to recreate the problem but
> I'm relatively new to SQL and not sure what sytem stored procedures or
> functions might help me...records of recent deletes (within the last
> few hours), userinfo (who really deleted the record)? Etc.
> Coincidentally (perhaps) this server was nearly full (aprox 700 megs
> left). I know...this is terrible. I've been harping my sysadmin to get
> some non DB related stuff off my SQL server and we cleared a few Gigs
> off this morning! Anyway, it's very likely the cause...again...could
> someone point me to what logs or functions or documentation I should
> be looking at that might help me verify this?
> Many thanks!!
>
-Dave Markle
http://www.markleconsulting.com/blog
sql

HELP! sp or other tool to help diagnose recent record deletion?

I'm trying to trouble shoot an app that after two years of running
more or less fine has suddenly had a problem. An average user was
filling out a web form and clicked save, only to have their entire
multi page report form get obliterated. This user has no delete
rights, and even if they had pressed cancel the ASP page would simply
not write that page...but would not delete the entire series of
records.
I am stepping through ASP code and trying to recreate the problem but
I'm relatively new to SQL and not sure what sytem stored procedures or
functions might help me...records of recent deletes (within the last
few hours), userinfo (who really deleted the record)? Etc.
Coincidentally (perhaps) this server was nearly full (aprox 700 megs
left). I know...this is terrible. I've been harping my sysadmin to get
some non DB related stuff off my SQL server and we cleared a few Gigs
off this morning! Anyway, it's very likely the cause...again...could
someone point me to what logs or functions or documentation I should
be looking at that might help me verify this?
Many thanks!!This might help you -- these guys have some good tools. I recommend,
though, that you restore the database to another area on the server and
compare the data with some custom queries to see what's missing...
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm?gclid=CK-roqn2mIoCFRyVFQodbFaonQ
mahalie@.gmail.com wrote:
> I'm trying to trouble shoot an app that after two years of running
> more or less fine has suddenly had a problem. An average user was
> filling out a web form and clicked save, only to have their entire
> multi page report form get obliterated. This user has no delete
> rights, and even if they had pressed cancel the ASP page would simply
> not write that page...but would not delete the entire series of
> records.
> I am stepping through ASP code and trying to recreate the problem but
> I'm relatively new to SQL and not sure what sytem stored procedures or
> functions might help me...records of recent deletes (within the last
> few hours), userinfo (who really deleted the record)? Etc.
> Coincidentally (perhaps) this server was nearly full (aprox 700 megs
> left). I know...this is terrible. I've been harping my sysadmin to get
> some non DB related stuff off my SQL server and we cleared a few Gigs
> off this morning! Anyway, it's very likely the cause...again...could
> someone point me to what logs or functions or documentation I should
> be looking at that might help me verify this?
> Many thanks!!
>
-Dave Markle
http://www.markleconsulting.com/blog

HELP! sp or other tool to help diagnose recent record deletion?

I'm trying to trouble shoot an app that after two years of running
more or less fine has suddenly had a problem. An average user was
filling out a web form and clicked save, only to have their entire
multi page report form get obliterated. This user has no delete
rights, and even if they had pressed cancel the ASP page would simply
not write that page...but would not delete the entire series of
records.
I am stepping through ASP code and trying to recreate the problem but
I'm relatively new to SQL and not sure what sytem stored procedures or
functions might help me...records of recent deletes (within the last
few hours), userinfo (who really deleted the record)? Etc.
Coincidentally (perhaps) this server was nearly full (aprox 700 megs
left). I know...this is terrible. I've been harping my sysadmin to get
some non DB related stuff off my SQL server and we cleared a few Gigs
off this morning! Anyway, it's very likely the cause...again...could
someone point me to what logs or functions or documentation I should
be looking at that might help me verify this?
Many thanks!!This might help you -- these guys have some good tools. I recommend,
though, that you restore the database to another area on the server and
compare the data with some custom queries to see what's missing...
http://www.red-gate.com/products/SQ...br />
odbFaonQ
mahalie@.gmail.com wrote:
> I'm trying to trouble shoot an app that after two years of running
> more or less fine has suddenly had a problem. An average user was
> filling out a web form and clicked save, only to have their entire
> multi page report form get obliterated. This user has no delete
> rights, and even if they had pressed cancel the ASP page would simply
> not write that page...but would not delete the entire series of
> records.
> I am stepping through ASP code and trying to recreate the problem but
> I'm relatively new to SQL and not sure what sytem stored procedures or
> functions might help me...records of recent deletes (within the last
> few hours), userinfo (who really deleted the record)? Etc.
> Coincidentally (perhaps) this server was nearly full (aprox 700 megs
> left). I know...this is terrible. I've been harping my sysadmin to get
> some non DB related stuff off my SQL server and we cleared a few Gigs
> off this morning! Anyway, it's very likely the cause...again...could
> someone point me to what logs or functions or documentation I should
> be looking at that might help me verify this?
> Many thanks!!
>
-Dave Markle
http://www.markleconsulting.com/blog

Friday, March 23, 2012

Help! Removing SQLServer builtin/Administrators

Hi,
I'm having trouble maintaining security on SQLServer as
everyone who is a member of the Local Administrators (on
the system) has full control by default as SQLServer has
builtin/Administrators added by default to its System
Administrators List.
Last time I removed this group, so many things went
wrong. I dont' want the entire local administrators to be
the SQL Admins. So please suggest a way where I can
safely remove the default Built-in\Adminstrators from the
SQLServer security. Any article will be helpful.
ThanksIf you subscribe to SQL Server Professional, I wrote a piece on this:
http://www.pinpub.com/html/main.isx?sub=64&story=783
Briefly, you can add a domain account to the sysadmin role first and then
remove the BUILTIN\Administrators role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:1475301c3c352$a5d28e00$a601280a@.phx
.gbl...
Hi,
I'm having trouble maintaining security on SQLServer as
everyone who is a member of the Local Administrators (on
the system) has full control by default as SQLServer has
builtin/Administrators added by default to its System
Administrators List.
Last time I removed this group, so many things went
wrong. I dont' want the entire local administrators to be
the SQL Admins. So please suggest a way where I can
safely remove the default Built-in\Adminstrators from the
SQLServer security. Any article will be helpful.
Thanks|||Adding to Tom's suggestion, you could also add a domain local group (with
only members you wish to have sysadmin equivalence) and grant that group
sysadmin permission -- then remove the builtin\administrators group. It's
also not a bad idea to reset the 'sa' account password at the same time in
case you need that account to log back in (in mixed mode).
Steve
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:1475301c3c352$a5d28e00$a601280a@.phx
.gbl...
quote:

> Hi,
> I'm having trouble maintaining security on SQLServer as
> everyone who is a member of the Local Administrators (on
> the system) has full control by default as SQLServer has
> builtin/Administrators added by default to its System
> Administrators List.
> Last time I removed this group, so many things went
> wrong. I dont' want the entire local administrators to be
> the SQL Admins. So please suggest a way where I can
> safely remove the default Built-in\Adminstrators from the
> SQLServer security. Any article will be helpful.

Sunday, February 19, 2012

Help with TSQL where clause syntax

Hey everyone I want a where clause based off a variable input and I'm having some trouble with the syntax. My current code(incorrect) is below but it shows what I am aiming for. Does anyone have any suggestions?

Code Snippet

(CASE
WHEN @.inIndustry = 'ALL' THEN
WHERE EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1)
ELSE
WHERE (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1) END)


Maybe this:

Code Snippet

(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE

CASEWHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END--inner case

END)-- outer case

|||Hey. Thanks for the help but I dont see how that would work. I'm trying to make the WHERE clause dynamic in a way. It's all based off whatever the var @.inIndustry is equal to. So if @.inIndustry is equal to 'ALL' then is uses a certain where clause. I dont want to base the equivalence off of the entire statement there.|||

Code Snippet

WHERE(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

WHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END)

= 1

|||Ahh I see how it works now. I plugged it in and it works like a charm. Thanks for the help!
|||

No problem; my pleasure.

Much appreciated if you can mark the solution as the answer Smile

|||Actually it didnt work exactly as it should... This is the code i have at the moment.

Code Snippet


WHERE
(CASE
--MASTER
WHEN @.inIndustry = 'ALL'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)

THEN 1

--ENERGY AND UTILITIES
WHEN @.inIndustry = 'EU' AND acIf.Industry = 'Energy and Utilities'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

-- NONGOV - E&U + OTHER
WHEN (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

ELSE 0
END) = 1

Now comparing original code and the code i wrote aboveI know that it performs the the last case statement no matter what i input for @.inIndustry. Is there any way to fix this?
|||

Nest CASE statements

CASE WHEN @.inIndustry = 'ALL' ..

ELSE

CASE WHEN ...

ELSE

END

END

|||I ended up adding checks to the last statement to make sure @.inIndustry wasn't equal to EU, ALL etc.. and it works fine now.

Thanks for all the help!