Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts

Tuesday, March 27, 2012

cannot execute sp/access sp properties using gui

I am not able to right click to execute a stored procedure or right click to access the stored procedure properties using Management Studio. Any ideas on what is causing this or if it is supposed to be this way?

Any help is much appreciated.

hi,

does the user you are in the database have enought permissions to do that?

regards

|||

Thanks for the response Andrea,

I login as sa.


Suppose I could mention this is on my local machine. Executing @.@.version returns:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I am the first one to be running on 2005 locally. Everyone else is still at 2000 so I have not been able to compare against other machines running express.

cannot execute sp/access sp properties using gui

I am not able to right click to execute a stored procedure or right click to access the stored procedure properties using Management Studio. Any ideas on what is causing this or if it is supposed to be this way?

Any help is much appreciated.

hi,

does the user you are in the database have enought permissions to do that?

regards

|||

Thanks for the response Andrea,

I login as sa.


Suppose I could mention this is on my local machine. Executing @.@.version returns:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I am the first one to be running on 2005 locally. Everyone else is still at 2000 so I have not been able to compare against other machines running express.

Cannot execute extended stored procedure?

I've written an extended stored procedure using VS 2005/C++. The sp can be
added and executed on my local SQL Server 2000. However, after I copied to
production server. I can sp_addextendedproc it but executing shows error:
Msg 0, Level 16, State 0, Procedure xp_calc, Line 1
Cannot load the DLL C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\XPDLLs\xp_calc.dll, or one of the DLLs it references.
Reason: 126(The specified module could not be found.)
I remember I need to copy some C++ runtime(?) dll to the server when I was
using VS 2003. Which files are needed for VS2005?
Thanks,nick (nick@.discussions.microsoft.com) writes:
> I've written an extended stored procedure using VS 2005/C++. The sp can be
> added and executed on my local SQL Server 2000. However, after I copied to
> production server. I can sp_addextendedproc it but executing shows error:
> Msg 0, Level 16, State 0, Procedure xp_calc, Line 1
> Cannot load the DLL C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\XPDLLs\xp_calc.dll, or one of the DLLs it references.
> Reason: 126(The specified module could not be found.)
> I remember I need to copy some C++ runtime(?) dll to the server when I was
> using VS 2003. Which files are needed for VS2005?
That would be MSVCR80.DLL.
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|||You can find the dependent DLLs using "Dependency Walker"
which can be downloaded from the Microsoft website
or from here
http://www.dependencywalker.com/

Sunday, March 25, 2012

Cannot drop the distribution database 'distribution' because it is currently in use.

All,
I use stored procedures from C#.net to configure and remove
replication.
Whenever, i remove replication i try to first check if any process for
distribution DB (using sp_who2) is remaining in the SQL process space
and kill it.
The possible processes, i knew of using the distribution DB are:
1. SQL .NET data client provider
2. MSSQL EM
3. SQL Query Analyzer
I also checked that no cleanup processes are using it.
But still i get the error "Cannot drop the distribution database
'distribution' because it is currently in use."
Can anyone help in finding out who else is using the Distribution DB?
Please reply asap!
Thanks in advance,
Lavanya
It could be the distribution cleanup agent, the log reader, the distribition
agent or the merge agent. For a user database you would make it single-user
with yourself as the only user then drop it: Alter database distribution set
single_user with rollback immediate; use tempdb; drop database distribution.
For the distributor I'd run sp_dropdistributor followed by
sp_dropdistributiondb rather than the "drop database".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Try this select * From sys.sysprocesses where dbid=db_id('distribution')
in sql 2005 in sql 2000, try this
select * From sysprocesses where dbid=db_id('distribution')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lavanya" <lavanyah@.gmail.com> wrote in message
news:1168258285.713911.183170@.s80g2000cwa.googlegr oups.com...
> All,
> I use stored procedures from C#.net to configure and remove
> replication.
> Whenever, i remove replication i try to first check if any process for
> distribution DB (using sp_who2) is remaining in the SQL process space
> and kill it.
> The possible processes, i knew of using the distribution DB are:
> 1. SQL .NET data client provider
> 2. MSSQL EM
> 3. SQL Query Analyzer
> I also checked that no cleanup processes are using it.
> But still i get the error "Cannot drop the distribution database
> 'distribution' because it is currently in use."
> Can anyone help in finding out who else is using the Distribution DB?
> Please reply asap!
> Thanks in advance,
> Lavanya
>

Cannot drop stored proc

I use sp_rename to rename several stored procedures.
sp_rename 'MyDB.dbo.spRPTKCTicketCount', ''MyDB.dbo.spRPTKCTicketCount_obsol
ete'
The message was:The object was renamed to 'MyDB.dbo.spRPTKCTicketCount'_obso
lete'.
Caution: Changing any part of an object name could break scripts and stored
procedures.
But now, I cannot drop the stored procedure because it was stored in sysobje
cts as 'MyDB.dbo.spRPTKCTicketCount'_obsolete'
So when I use drop 'MyDB.dbo.spRPTKCTicketCount'_obsolete' it return an erro
r saying that
"Server: Msg 166, Level 15, State 1, Line 1
'DROP PROCEDURE' does not allow specifying the database name as a prefix to
the object name"
and if I take the Database name out of it, it says that it cannot find the i
t in the system catalogs.
Please help...Hi Jorge
Can you try putting the name in square brackets?
DROP PROCEDURE [MyDB.dbo.spRPTKCTicketCount_obsolete]
If that doesn't work, see if you can change the name again, to something
simpler.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jorge" <anonymous@.discussions.microsoft.com> wrote in message
news:6A32D11A-2EFE-48BA-B61F-F1004EB1F446@.microsoft.com...
> I use sp_rename to rename several stored procedures.
> sp_rename 'MyDB.dbo.spRPTKCTicketCount',
''MyDB.dbo. spRPTKCTicketCount_obsolete'ed">
> The message was:The object was renamed to
'MyDB.dbo.spRPTKCTicketCount'_obsolete'.
> Caution: Changing any part of an object name could break scripts and
stored procedures.
> But now, I cannot drop the stored procedure because it was stored in
sysobjects as 'MyDB.dbo. spRPTKCTicketCount'_obsolete'red">
> So when I use drop 'MyDB.dbo.spRPTKCTicketCount'_obsolete' it return an
error saying that
> "Server: Msg 166, Level 15, State 1, Line 1
> 'DROP PROCEDURE' does not allow specifying the database name as a prefix
to the object name"
> and if I take the Database name out of it, it says that it cannot find the
it in the system catalogs.
> Please help...
>|||Thank you...the brackets seem to work.
Actually, I'm impressed and surprised. I never thought that the author of Sq
l Server book would respond to me. It's an honor.
Jorge Chirinos
jorge_chirinos@.hotmail.com

Cannot drop database (hanging in loading and marked for replication)

Hi
I have restored a database to the same instance as it was backup, but as a
copy of the original database (I need a couple of Stored Procedures), but
while restoring the database is to hanging with (Loading).
I've tried to drop the database but it says that it cannot be dropped
because it is marked for replication, I've then tried to drop the
replication on the database but it can't, it says that the database cannot
be opened because it is in the middle of a restore.
What to do? I'm pretty stuck...
I've running SQL Server 2000 on a windows 2003 server SP1, the database is
less than 100MB in size.
-Kim
I was having the same problem restoring my database (without replication
issue) and it's stuck loading for an hour, so stoped SQL Server and removed
that datbase mdf and log files..
Then restarted my server and run mentanance task (optmizing & fix intgerity)
on master database..
Then restore my database from the backup
it finished in less than a minute.
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:43f6ec88$1@.news.carlbro.dk...
> Hi
> I have restored a database to the same instance as it was backup, but as a
> copy of the original database (I need a couple of Stored Procedures), but
> while restoring the database is to hanging with (Loading).
> I've tried to drop the database but it says that it cannot be dropped
> because it is marked for replication, I've then tried to drop the
> replication on the database but it can't, it says that the database cannot
> be opened because it is in the middle of a restore.
> What to do? I'm pretty stuck...
> I've running SQL Server 2000 on a windows 2003 server SP1, the database is
> less than 100MB in size.
> -Kim
>
|||Hi,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:ummPctJNGHA.532@.TK2MSFTNGP15.phx.gbl...
>I was having the same problem restoring my database (without replication
>issue) and it's stuck loading for an hour, so stoped SQL Server and removed
>that datbase mdf and log files..
> Then restarted my server and run mentanance task (optmizing & fix
> intgerity) on master database..
> Then restore my database from the backup
> it finished in less than a minute.
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:43f6ec88$1@.news.carlbro.dk...
>

Cannot drop database (hanging in loading and marked for replication)

Hi
I have restored a database to the same instance as it was backup, but as a
copy of the original database (I need a couple of Stored Procedures), but
while restoring the database is to hanging with (Loading).
I've tried to drop the database but it says that it cannot be dropped
because it is marked for replication, I've then tried to drop the
replication on the database but it can't, it says that the database cannot
be opened because it is in the middle of a restore.
What to do? I'm pretty stuck...
I've running SQL Server 2000 on a windows 2003 server SP1, the database is
less than 100MB in size.
-KimI was having the same problem restoring my database (without replication
issue) and it's stuck loading for an hour, so stoped SQL Server and removed
that datbase mdf and log files..
Then restarted my server and run mentanance task (optmizing & fix intgerity)
on master database..
Then restore my database from the backup
it finished in less than a minute.
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:43f6ec88$1@.news.carlbro.dk...
> Hi
> I have restored a database to the same instance as it was backup, but as a
> copy of the original database (I need a couple of Stored Procedures), but
> while restoring the database is to hanging with (Loading).
> I've tried to drop the database but it says that it cannot be dropped
> because it is marked for replication, I've then tried to drop the
> replication on the database but it can't, it says that the database cannot
> be opened because it is in the middle of a restore.
> What to do? I'm pretty stuck...
> I've running SQL Server 2000 on a windows 2003 server SP1, the database is
> less than 100MB in size.
> -Kim
>|||Hi,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:ummPctJNGHA.532@.TK2MSFTNGP15.phx.gbl...
>I was having the same problem restoring my database (without replication
>issue) and it's stuck loading for an hour, so stoped SQL Server and removed
>that datbase mdf and log files..
> Then restarted my server and run mentanance task (optmizing & fix
> intgerity) on master database..
> Then restore my database from the backup
> it finished in less than a minute.
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:43f6ec88$1@.news.carlbro.dk...
>|||"Islamegy":[vbcol=seagreen]
[vbcol=seagreen]
Hi again,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm|||(sorry if this message is posted more than once, I've had trouble submitting
my mail to my newsserver)
"Islamegy":[vbcol=seagreen]
[vbcol=seagreen]
Hi again,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm

Cannot drop database (hanging in loading and marked for replication)

Hi
I have restored a database to the same instance as it was backup, but as a
copy of the original database (I need a couple of Stored Procedures), but
while restoring the database is to hanging with (Loading).
I've tried to drop the database but it says that it cannot be dropped
because it is marked for replication, I've then tried to drop the
replication on the database but it can't, it says that the database cannot
be opened because it is in the middle of a restore.
What to do? I'm pretty stuck...
I've running SQL Server 2000 on a windows 2003 server SP1, the database is
less than 100MB in size.
-KimI was having the same problem restoring my database (without replication
issue) and it's stuck loading for an hour, so stoped SQL Server and removed
that datbase mdf and log files..
Then restarted my server and run mentanance task (optmizing & fix intgerity)
on master database..
Then restore my database from the backup
it finished in less than a minute.
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:43f6ec88$1@.news.carlbro.dk...
> Hi
> I have restored a database to the same instance as it was backup, but as a
> copy of the original database (I need a couple of Stored Procedures), but
> while restoring the database is to hanging with (Loading).
> I've tried to drop the database but it says that it cannot be dropped
> because it is marked for replication, I've then tried to drop the
> replication on the database but it can't, it says that the database cannot
> be opened because it is in the middle of a restore.
> What to do? I'm pretty stuck...
> I've running SQL Server 2000 on a windows 2003 server SP1, the database is
> less than 100MB in size.
> -Kim
>|||Hi,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm
"Islamegy®" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:ummPctJNGHA.532@.TK2MSFTNGP15.phx.gbl...
>I was having the same problem restoring my database (without replication
>issue) and it's stuck loading for an hour, so stoped SQL Server and removed
>that datbase mdf and log files..
> Then restarted my server and run mentanance task (optmizing & fix
> intgerity) on master database..
> Then restore my database from the backup
> it finished in less than a minute.
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:43f6ec88$1@.news.carlbro.dk...
>> Hi
>> I have restored a database to the same instance as it was backup, but as
>> a copy of the original database (I need a couple of Stored Procedures),
>> but while restoring the database is to hanging with (Loading).
>> I've tried to drop the database but it says that it cannot be dropped
>> because it is marked for replication, I've then tried to drop the
>> replication on the database but it can't, it says that the database
>> cannot be opened because it is in the middle of a restore.
>> What to do? I'm pretty stuck...
>> I've running SQL Server 2000 on a windows 2003 server SP1, the database
>> is less than 100MB in size.
>> -Kim
>|||"Islamegy®":
>>I was having the same problem restoring my database (without replication
>>issue) and it's stuck loading for an hour, so stoped SQL Server and
>>removed that datbase mdf and log files..
>> Then restarted my server and run mentanance task (optmizing & fix
>> intgerity) on master database..
>> Then restore my database from the backup
>> it finished in less than a minute.
>> "Kim":
>> I have restored a database to the same instance as it was backup, but as
>> a copy of the original database (I need a couple of Stored Procedures),
>> but while restoring the database is to hanging with (Loading).
>> I've tried to drop the database but it says that it cannot be dropped
>> because it is marked for replication, I've then tried to drop the
>> replication on the database but it can't, it says that the database
>> cannot be opened because it is in the middle of a restore.
>> What to do? I'm pretty stuck...
>> I've running SQL Server 2000 on a windows 2003 server SP1, the database
>> is less than 100MB in size.
>> -Kim
Hi again,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm

Tuesday, March 20, 2012

Cannot Debug SQL 2005 Stored Procedures in VS 2005

I am having trouble in consistently debugging T-SQL stored procedures in SQL
2005 using VS 2005 Pro. I have enabled debugging on the connection in the
Server Explorer and enable CLR Integration in the Surface Area Configuration
tool. At one point I could step into the sproc but now I cannot. What has
gone wrong. How can I be able to consistently debug T-SQL sprocs?
Any input would be appreciated."=?Utf-8?B? RWFnbGVSZWRASGlnaEZseWluZ0JpcmRzLmNvbQ==
?="
<EagleRedHighFlyingBirdscom@.discussions.microsoft.com> wrote in
news:2EC86A08-79A4-44CC-ABDF-72DAE46D1BF0@.microsoft.com:

> I am having trouble in consistently debugging T-SQL stored procedures
> in SQL 2005 using VS 2005 Pro. I have enabled debugging on the
> connection in the Server Explorer and enable CLR Integration in the
> Surface Area Configuration tool. At one point I could step into the
> sproc but now I cannot. What has gone wrong. How can I be able to
> consistently debug T-SQL sprocs?
>
Hmm, in order to debug T-SQL proc's you should not have to do any
enabling at all.
1. Open VS 2005
2. Open Server Explorer
3. Create a connection to the database the proc is in
4. Expand the database and drill down to Stored Procedures, expand.
5. Right click on the proc and choose "Step Into Stored Procedure".
6. Debug
What happens for you when you try the above?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||The debugger starts but when I either step into or step to the next statemen
t
in the proc the proc runs to completion. I have set a breakpoint in the pro
c
only to have it disabled when debugging starts.
"EagleRed@.HighFlyingBirds.com" wrote:

> I am having trouble in consistently debugging T-SQL stored procedures in S
QL
> 2005 using VS 2005 Pro. I have enabled debugging on the connection in the
> Server Explorer and enable CLR Integration in the Surface Area Configurati
on
> tool. At one point I could step into the sproc but now I cannot. What ha
s
> gone wrong. How can I be able to consistently debug T-SQL sprocs?
> Any input would be appreciated.|||Here's some additional information. The disabled breakpoint displays a
tooltip that says the breakpoint will not be hit because the object
containing the breakpoint has not been loaded. What's going on? I have bee
n
able to debug and hit breakpoints, but I cannot do it consistently and right
now it simply isn't working.
"Niels Berglund" wrote:

> "=?Utf-8?B? RWFnbGVSZWRASGlnaEZseWluZ0JpcmRzLmNvbQ==
?="
> <EagleRedHighFlyingBirdscom@.discussions.microsoft.com> wrote in
> news:2EC86A08-79A4-44CC-ABDF-72DAE46D1BF0@.microsoft.com:
>
> Hmm, in order to debug T-SQL proc's you should not have to do any
> enabling at all.
> 1. Open VS 2005
> 2. Open Server Explorer
> 3. Create a connection to the database the proc is in
> 4. Expand the database and drill down to Stored Procedures, expand.
> 5. Right click on the proc and choose "Step Into Stored Procedure".
> 6. Debug
> What happens for you when you try the above?
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>

Cannot debug activated CLR Stored Procedure

Hi

I'm having a problem debugging (with VS2005,) a CLR Stored Procedure which is activated on a queue. I know it is working because it is consuming and processing messages when one appears in the queue.

I can debug it 'manually' by either right-clicking on the SP within VS2005 and selecting 'Step into Stored Procedure' or by attaching to the SQL Server Management Studio process, setting a breakpoint and executing the stored procedure from a Management Studio query window.

However, if I send a message to my queue within Management Studio, my breakpoints are NOT being hit within VS2005, but the message IS reaching my queue and it IS being processed by the SP.

Can anyone help?

Thanks

The only way to debug this is by attaching VS debugger directly to sqlservr.exe (i.e. the database engine) for managed debugging. You will need to install the Visual Studio Remote Debugger on the machine running the engine and you will need to add symbols to your assembly.

Hope that helps,

Rushi

Cannot debug activated CLR Stored Procedure

Hi

I'm having a problem debugging (with VS2005,) a CLR Stored Procedure which is activated on a queue. I know it is working because it is consuming and processing messages when one appears in the queue.

I can debug it 'manually' by either right-clicking on the SP within VS2005 and selecting 'Step into Stored Procedure' or by attaching to the SQL Server Management Studio process, setting a breakpoint and executing the stored procedure from a Management Studio query window.

However, if I send a message to my queue within Management Studio, my breakpoints are NOT being hit within VS2005, but the message IS reaching my queue and it IS being processed by the SP.

Can anyone help?

Thanks

The only way to debug this is by attaching VS debugger directly to sqlservr.exe (i.e. the database engine) for managed debugging. You will need to install the Visual Studio Remote Debugger on the machine running the engine and you will need to add symbols to your assembly.

Hope that helps,

Rushi

Cannot create subscriptions from Management Studio

I cannot create any subscriptions from Management Studio.
Reports have shared Data Source with stored credentials
SQL Agent is up and running
In surface area configuration "Scheduling and delivery" for SSRS are enabled
SSRS Execution account has been set up
E-mail settings are fine
I'm logged in as Admin, able to everything, but...
When I right-click on report/subscriptions folder, both "New subscription"
and "New data driven subscription" are DISABLED.
What's wrong? That's driving me crazy!
DmitryHello Dmitry,
I would like to know, when you try to use the Report Manager Via web
interface,
If you try to use the Report Manager via web and create the subscription,
what's the result?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:sOpkHyeGIHA.4268@.TK2MSFTNGHUB02.phx.gbl...
> Hello Dmitry,
> I would like to know, when you try to use the Report Manager Via web
> interface,
> If you try to use the Report Manager via web and create the subscription,
> what's the result?
I am using custom (forms) security and the admin can only log in through
Management Studio. For security purposes (this is internet-faced
application) no separate login pages for SSRS exist.
Regular users (authenticated and authorized) can see the reports inside web
application via ReportViewer control, which is getting the instance of
IReportServerCredentials.
Regards,
Dmitry|||Hello Dmitry,
Well, since you are using the form authentication, the management studio
may not connect to the reporting services correctly.
I would like to know whether the admin account you use have the proper
permission of reporting services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:bIg%23C9rGIHA.4200@.TK2MSFTNGHUB02.phx.gbl...
> Hello Dmitry,
> Well, since you are using the form authentication, the management studio
> may not connect to the reporting services correctly.
> I would like to know whether the admin account you use have the proper
> permission of reporting services.
Yes, the admin is in any possible role. I already mentioned that in my
initial message. I'm able to do everything that's not subscriptions related.
Subscription-related menu items are simply disabled and I need to know why.
Dmitry.|||Hello Dmitry,
The admin you use is a domain account or a form authentication account?
Have you checked what's the role of this account on the reporting services?
Is it a System admin ?
Also, for the report, does it have the Manage all subscriptions permission?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:2rG$mH3GIHA.540@.TK2MSFTNGHUB02.phx.gbl...
> Hello Dmitry,
> The admin you use is a domain account or a form authentication account?
There's no such account technically. Admin is authenticated by
username/password inside forms security extension and after that it is given
unlimited access in any CheckAccess method...
> Have you checked what's the role of this account on the reporting
> services?
I answered this question already:
System User
System Administrator
Browser
Content Manager
Publisher
Report Builder
> Is it a System admin ?
Yes.
> Also, for the report, does it have the Manage all subscriptions
> permission?
Through Content Manager role, yes
As a matter of fact I have just installed the second instance of RS with
standard security and all the defaults. And I observe the same damn thing:
subscription related menu items are disabled in Management studio, but I can
create subscriptions using the same Windows credentials in the browser.
Which tells me the original issue may be not related to my custom security
extension at all.
Regards,
Dmitry|||Hello Dmitry,
Could you please let me know the config file content?
rsreportserver.config and RSWebApplication.config under the ReportManager
and Reporserver folder is appreicated.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:p$%23GYM5GIHA.5176@.TK2MSFTNGHUB02.phx.gbl...
> Hello Dmitry,
> Could you please let me know the config file content?
> rsreportserver.config and RSWebApplication.config under the ReportManager
> and Reporserver folder is appreicated.
E-mail delivery of those files failed. Please provide valid e-mail address.
The following message to <weilu@.online.microsoft.com> was undeliverable.
The reason for the problem:
5.1.2 - Bad destination host 'DNS Hard Error looking up online.microsoft.com
(MX): NXDomain'|||Hello Dmitry,
Please remove the ONLINE in my display email.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Dmitry,
I get your email.
I would like to suggest you check the web services account you use.
Could you please change it to the network services?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:7mWE3N4HIHA.7444@.TK2MSFTNGHUB02.phx.gbl...
> Hello Dmitry,
> I get your email.
> I would like to suggest you check the web services account you use.
> Could you please change it to the network services?
Please advise WHERE EXACTLY this change to be made.
D.|||Hello Dmitry,
There is an element in the reportserver.config file:
<WebServiceAccount>NT Authority\NetworkService</WebServiceAccount>
Please modify it like this.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:R78DbCCIIHA.7444@.TK2MSFTNGHUB02.phx.gbl...
> Hello Dmitry,
> There is an element in the reportserver.config file:
> <WebServiceAccount>NT Authority\NetworkService</WebServiceAccount>
> Please modify it like this.
It is configured to be running as MYPCNAME\ASPNET
If I modify "like this" I'm getting the following message while trying to
navigate to any folder in Management Studio:
TITLE: Microsoft SQL Server Management Studio
--
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
--
ADDITIONAL INFORMATION:
The Report Server Web Service is unable to access secure information in the
report server. Please verify that the WebServiceAccount is specified
correctly in the report server config file. (rsAccessDeniedToSecureData)
(Report Services SOAP Proxy Source)
--
The Report Server Web Service is unable to access secure information in the
report server. Please verify that the WebServiceAccount is specified
correctly in the report server config file. (rsAccessDeniedToSecureData)
(Microsoft.ReportingServices.Diagnostics)
D.|||Hello Dmitry,
I am performing some test on my side. I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Dima,
I would like to know whether you have applied the latest service pack of
sql server and have you stored the credential for the report.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:K1afiFOLIHA.6908@.TK2MSFTNGHUB02.phx.gbl...
> Hello Dima,
> I would like to know whether you have applied the latest service pack of
> sql server
It is 9.00.3054.00
> and have you stored the credential for the report.
There is no such thing as "stored credential for the report"
If you mean "stored credentials for the DATA SOURCE", yes that's the case.
Dmitry|||Hello Dmitry,
Could you lease send me an email and that I may involve other resources?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:j33I7UmMIHA.4380@.TK2MSFTNGHUB02.phx.gbl...
> Hello Dmitry,
> Could you lease send me an email and that I may involve other resources?
Wei,
I sent you the e-mail a week ago. Where are those "other resourses"?
D.|||Hello,
My colleague is Out of Office. Once he is back, I will escalate this issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Sunday, March 11, 2012

Cannot create a Stored Procedure

Hi,
I am getting a error that the object already exists in the database when I
try to create a Stored Procedure. I have looked in sysobjects table and have
tried 5-6 different names for the Stored Procedure but nothing works. Any
help will be greatly appreciated. Thanks.Can you show the CREATE PROCEDURE code, and the exact error message?
Did you verify that you are in the right database (SELECT DB_NAME()) and
that you are checking sysobjects in the correct database?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
> Hi,
> I am getting a error that the object already exists in the database when I
> try to create a Stored Procedure. I have looked in sysobjects table and
> have
> tried 5-6 different names for the Stored Procedure but nothing works. Any
> help will be greatly appreciated. Thanks.|||This is the Create Procedure Code:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[usp_ServerFullBackup_SystemDatabases]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ServerFullBackup_SystemDatabases]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].usp_ServerFullBackup_SystemDatabases
AS
BEGIN
BACKUP DATABASE [master]
TO DISK = N'\\CurrentWeek\master.BAK'
WITH INIT , NOUNLOAD , NAME = N'master Backup', STATS = 25, FORMAT
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
and this is the error message I get on the query analyzer:
Server: Msg 2714, Level 16, State 5, Procedure
usp_ServerFullBackup_SystemDatabases, Line 5
There is already an object named 'usp_ServerFullBackup_SystemDatabases' in
the database.
and in the enterprise manager I get this message:
Microsoft SQL-DMO (ODBC SQLState: 42S01)
Error 2714: There is already an object named
'usp_ServerFullBackup_SystemDatabases' in the database
I tried querying the sysobjects table and the name of the Stored Proc is not
there. I even tried to create it with a name usp_XXX or usp_YYY and got the
same error message. Thanks.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Can you show the CREATE PROCEDURE code, and the exact error message?
> Did you verify that you are in the right database (SELECT DB_NAME()) and
> that you are checking sysobjects in the correct database?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
> > Hi,
> >
> > I am getting a error that the object already exists in the database when I
> > try to create a Stored Procedure. I have looked in sysobjects table and
> > have
> > tried 5-6 different names for the Stored Procedure but nothing works. Any
> > help will be greatly appreciated. Thanks.
>
>|||I also found out that it's only when I try to create the Stored Procedure in
the master database that I get this message. In all the other databases I am
able to create the Stored Procedure. I have tried different names but nothing
works.
"sharman" wrote:
> This is the Create Procedure Code:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[usp_ServerFullBackup_SystemDatabases]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[usp_ServerFullBackup_SystemDatabases]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE [dbo].usp_ServerFullBackup_SystemDatabases
> AS
> BEGIN
> BACKUP DATABASE [master]
> TO DISK = N'\\CurrentWeek\master.BAK'
> WITH INIT , NOUNLOAD , NAME = N'master Backup', STATS = 25, FORMAT
> END
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> and this is the error message I get on the query analyzer:
> Server: Msg 2714, Level 16, State 5, Procedure
> usp_ServerFullBackup_SystemDatabases, Line 5
> There is already an object named 'usp_ServerFullBackup_SystemDatabases' in
> the database.
> and in the enterprise manager I get this message:
> Microsoft SQL-DMO (ODBC SQLState: 42S01)
> Error 2714: There is already an object named
> 'usp_ServerFullBackup_SystemDatabases' in the database
> I tried querying the sysobjects table and the name of the Stored Proc is not
> there. I even tried to create it with a name usp_XXX or usp_YYY and got the
> same error message. Thanks.
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > Can you show the CREATE PROCEDURE code, and the exact error message?
> >
> > Did you verify that you are in the right database (SELECT DB_NAME()) and
> > that you are checking sysobjects in the correct database?
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.sqlblog.com/
> > http://www.aspfaq.com/5006
> >
> >
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
> > > Hi,
> > >
> > > I am getting a error that the object already exists in the database when I
> > > try to create a Stored Procedure. I have looked in sysobjects table and
> > > have
> > > tried 5-6 different names for the Stored Procedure but nothing works. Any
> > > help will be greatly appreciated. Thanks.
> >
> >
> >|||Could it be that it's a permissions problem? I have no problems creating
a Stored Proc in my master database.
That being said, I always prefer NOT to create any user defined objects
in my master database. I normally create an "administration" database
where I keep all my user objects. I think it too risky to mess to much
around in the Master database..:-).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
sharman wrote:
> I also found out that it's only when I try to create the Stored Procedure in
> the master database that I get this message. In all the other databases I am
> able to create the Stored Procedure. I have tried different names but nothing
> works.
> "sharman" wrote:
>> This is the Create Procedure Code:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[usp_ServerFullBackup_SystemDatabases]')
>> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> drop procedure [dbo].[usp_ServerFullBackup_SystemDatabases]
>> GO
>> SET QUOTED_IDENTIFIER OFF
>> GO
>> SET ANSI_NULLS ON
>> GO
>> CREATE PROCEDURE [dbo].usp_ServerFullBackup_SystemDatabases
>> AS
>> BEGIN
>> BACKUP DATABASE [master]
>> TO DISK = N'\\CurrentWeek\master.BAK'
>> WITH INIT , NOUNLOAD , NAME = N'master Backup', STATS = 25, FORMAT
>> END
>> GO
>> SET QUOTED_IDENTIFIER OFF
>> GO
>> SET ANSI_NULLS ON
>> GO
>> and this is the error message I get on the query analyzer:
>> Server: Msg 2714, Level 16, State 5, Procedure
>> usp_ServerFullBackup_SystemDatabases, Line 5
>> There is already an object named 'usp_ServerFullBackup_SystemDatabases' in
>> the database.
>> and in the enterprise manager I get this message:
>> Microsoft SQL-DMO (ODBC SQLState: 42S01)
>> Error 2714: There is already an object named
>> 'usp_ServerFullBackup_SystemDatabases' in the database
>> I tried querying the sysobjects table and the name of the Stored Proc is not
>> there. I even tried to create it with a name usp_XXX or usp_YYY and got the
>> same error message. Thanks.
>>
>>
>> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Can you show the CREATE PROCEDURE code, and the exact error message?
>> Did you verify that you are in the right database (SELECT DB_NAME()) and
>> that you are checking sysobjects in the correct database?
>> --
>> Aaron Bertrand
>> SQL Server MVP
>> http://www.sqlblog.com/
>> http://www.aspfaq.com/5006
>>
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
>> Hi,
>> I am getting a error that the object already exists in the database when I
>> try to create a Stored Procedure. I have looked in sysobjects table and
>> have
>> tried 5-6 different names for the Stored Procedure but nothing works. Any
>> help will be greatly appreciated. Thanks.
>>

Cannot create a Stored Procedure

Hi,
I am getting a error that the object already exists in the database when I
try to create a Stored Procedure. I have looked in sysobjects table and have
tried 5-6 different names for the Stored Procedure but nothing works. Any
help will be greatly appreciated. Thanks.Can you show the CREATE PROCEDURE code, and the exact error message?
Did you verify that you are in the right database (SELECT DB_NAME()) and
that you are checking sysobjects in the correct database?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
> Hi,
> I am getting a error that the object already exists in the database when I
> try to create a Stored Procedure. I have looked in sysobjects table and
> have
> tried 5-6 different names for the Stored Procedure but nothing works. Any
> help will be greatly appreciated. Thanks.|||This is the Create Procedure Code:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].& #91;usp_ServerFullBackup_SystemDatabases
]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].& #91;usp_ServerFullBackup_SystemDatabases
]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].usp_ServerFullBackup_SystemDatabases
AS
BEGIN
BACKUP DATABASE [master]
TO DISK = N'\\CurrentWeek\master.BAK'
WITH INIT , NOUNLOAD , NAME = N'master Backup', STATS = 25, FORMAT
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
and this is the error message I get on the query analyzer:
Server: Msg 2714, Level 16, State 5, Procedure
usp_ServerFullBackup_SystemDatabases, Line 5
There is already an object named 'usp_ServerFullBackup_SystemDatabases' in
the database.
and in the enterprise manager I get this message:
Microsoft SQL-DMO (ODBC SQLState: 42S01)
Error 2714: There is already an object named
'usp_ServerFullBackup_SystemDatabases' in the database
I tried querying the sysobjects table and the name of the Stored Proc is not
there. I even tried to create it with a name usp_XXX or usp_YYY and got the
same error message. Thanks.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you show the CREATE PROCEDURE code, and the exact error message?
> Did you verify that you are in the right database (SELECT DB_NAME()) and
> that you are checking sysobjects in the correct database?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
>
>|||I also found out that it's only when I try to create the Stored Procedure in
the master database that I get this message. In all the other databases I am
able to create the Stored Procedure. I have tried different names but nothin
g
works.
"sharman" wrote:
[vbcol=seagreen]
> This is the Create Procedure Code:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].& #91;usp_ServerFullBackup_SystemDatabases
]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].& #91;usp_ServerFullBackup_SystemDatabases
]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE [dbo].usp_ServerFullBackup_SystemDatabases
> AS
> BEGIN
> BACKUP DATABASE [master]
> TO DISK = N'\\CurrentWeek\master.BAK'
> WITH INIT , NOUNLOAD , NAME = N'master Backup', STATS = 25, FORMAT
> END
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> and this is the error message I get on the query analyzer:
> Server: Msg 2714, Level 16, State 5, Procedure
> usp_ServerFullBackup_SystemDatabases, Line 5
> There is already an object named 'usp_ServerFullBackup_SystemDatabases' in
> the database.
> and in the enterprise manager I get this message:
> Microsoft SQL-DMO (ODBC SQLState: 42S01)
> Error 2714: There is already an object named
> 'usp_ServerFullBackup_SystemDatabases' in the database
> I tried querying the sysobjects table and the name of the Stored Proc is n
ot
> there. I even tried to create it with a name usp_XXX or usp_YYY and got th
e
> same error message. Thanks.
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Could it be that it's a permissions problem? I have no problems creating
a Stored Proc in my master database.
That being said, I always prefer NOT to create any user defined objects
in my master database. I normally create an "administration" database
where I keep all my user objects. I think it too risky to mess to much
around in the Master database..:-).
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
sharman wrote:[vbcol=seagreen]
> I also found out that it's only when I try to create the Stored Procedure
in
> the master database that I get this message. In all the other databases I
am
> able to create the Stored Procedure. I have tried different names but noth
ing
> works.
> "sharman" wrote:
>

Cannot create a Stored Procedure

Hi,
I am getting a error that the object already exists in the database when I
try to create a Stored Procedure. I have looked in sysobjects table and have
tried 5-6 different names for the Stored Procedure but nothing works. Any
help will be greatly appreciated. Thanks.
Can you show the CREATE PROCEDURE code, and the exact error message?
Did you verify that you are in the right database (SELECT DB_NAME()) and
that you are checking sysobjects in the correct database?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
> Hi,
> I am getting a error that the object already exists in the database when I
> try to create a Stored Procedure. I have looked in sysobjects table and
> have
> tried 5-6 different names for the Stored Procedure but nothing works. Any
> help will be greatly appreciated. Thanks.
|||This is the Create Procedure Code:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[usp_ServerFullBackup_SystemDatabases]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ServerFullBackup_SystemDatabases]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].usp_ServerFullBackup_SystemDatabases
AS
BEGIN
BACKUP DATABASE [master]
TO DISK = N'\\CurrentWeek\master.BAK'
WITH INIT , NOUNLOAD , NAME = N'master Backup', STATS = 25, FORMAT
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
and this is the error message I get on the query analyzer:
Server: Msg 2714, Level 16, State 5, Procedure
usp_ServerFullBackup_SystemDatabases, Line 5
There is already an object named 'usp_ServerFullBackup_SystemDatabases' in
the database.
and in the enterprise manager I get this message:
Microsoft SQL-DMO (ODBC SQLState: 42S01)
Error 2714: There is already an object named
'usp_ServerFullBackup_SystemDatabases' in the database
I tried querying the sysobjects table and the name of the Stored Proc is not
there. I even tried to create it with a name usp_XXX or usp_YYY and got the
same error message. Thanks.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you show the CREATE PROCEDURE code, and the exact error message?
> Did you verify that you are in the right database (SELECT DB_NAME()) and
> that you are checking sysobjects in the correct database?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CB1B4D7C-34A2-4B89-8786-836B57054FDA@.microsoft.com...
>
>
|||I also found out that it's only when I try to create the Stored Procedure in
the master database that I get this message. In all the other databases I am
able to create the Stored Procedure. I have tried different names but nothing
works.
"sharman" wrote:
[vbcol=seagreen]
> This is the Create Procedure Code:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[usp_ServerFullBackup_SystemDatabases]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[usp_ServerFullBackup_SystemDatabases]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE [dbo].usp_ServerFullBackup_SystemDatabases
> AS
> BEGIN
> BACKUP DATABASE [master]
> TO DISK = N'\\CurrentWeek\master.BAK'
> WITH INIT , NOUNLOAD , NAME = N'master Backup', STATS = 25, FORMAT
> END
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> and this is the error message I get on the query analyzer:
> Server: Msg 2714, Level 16, State 5, Procedure
> usp_ServerFullBackup_SystemDatabases, Line 5
> There is already an object named 'usp_ServerFullBackup_SystemDatabases' in
> the database.
> and in the enterprise manager I get this message:
> Microsoft SQL-DMO (ODBC SQLState: 42S01)
> Error 2714: There is already an object named
> 'usp_ServerFullBackup_SystemDatabases' in the database
> I tried querying the sysobjects table and the name of the Stored Proc is not
> there. I even tried to create it with a name usp_XXX or usp_YYY and got the
> same error message. Thanks.
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Could it be that it's a permissions problem? I have no problems creating
a Stored Proc in my master database.
That being said, I always prefer NOT to create any user defined objects
in my master database. I normally create an "administration" database
where I keep all my user objects. I think it too risky to mess to much
around in the Master database..:-).
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
sharman wrote:[vbcol=seagreen]
> I also found out that it's only when I try to create the Stored Procedure in
> the master database that I get this message. In all the other databases I am
> able to create the Stored Procedure. I have tried different names but nothing
> works.
> "sharman" wrote:

Thursday, March 8, 2012

Cannot create a connection to data source 'dataSource1'.

I am getting this error from Report Builder
Cannot create a connection to data source 'dataSource1'.
Back end SQL 2000
Credentials stored securely in the report server
Login and password of the SQL login
The weird thing is pointing to another db on the same server works fine
--
Peter
Peter.freeborn@.halliburton.comHope you have created Report model, If not use report model to access reports
from builder.
If you have already created it check the datasource "test connection" in the
report model project.
If you are accessing directly from builder using "open report server file",
dont do it cause it will ask for datasource which you cannt include. So
create a model and then access.
Amarnath
"Peter" wrote:
> I am getting this error from Report Builder
> Cannot create a connection to data source 'dataSource1'.
> Back end SQL 2000
> Credentials stored securely in the report server
> Login and password of the SQL login
> The weird thing is pointing to another db on the same server works fine
>
> --
> Peter
> Peter.freeborn@.halliburton.com|||I've struck this problem, I did some digging around and it seems that this is
a problem with kerberos security. Basically if you store the credentials in
the report server then the report server can pass the credentials fine,
however on a remote server it can't unless you've set up kerberos and use
windows security.
Thats my understanding of the problem, however the error message doesn't
really lead you in that direction.
HTH
"Peter" wrote:
> I am getting this error from Report Builder
> Cannot create a connection to data source 'dataSource1'.
> Back end SQL 2000
> Credentials stored securely in the report server
> Login and password of the SQL login
> The weird thing is pointing to another db on the same server works fine
>
> --
> Peter
> Peter.freeborn@.halliburton.com

Tuesday, February 14, 2012

Cannot connect to OLAP database using Credential Stored in report server

Hi guys,

I'm having problem regarding connecting to OLAP Database when I use the Credential stored in the Report Server. When I tried using the Credential stored in the Report Server connecting to OLTP database, It works fine.

Below is the error I'm geting when connecting to OLAP Database.

  • An error has occurred during report processing. (rsProcessingAborted)
  • Cannot create a connection to data source 'DS_Project88_OLAP'. (rsErrorOpeningConnection)
  • For more information about this error navigate to the report server on the local server machine, or enable remote errors
  • There are several reasons why the connection could fail. I recommend to check the report server logfile for more information on the exception from the data provider, or enable remote errors (see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230593&SiteID=1) to turn on detailed errors for all users.

    -- Robert

  •