Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure Error MSG 15007 Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 2, 2013 8:57 AM
Points: 12, Visits: 83
Msg 15007, Level 16, State 1, Procedure sp_defaultdb, Line 41
'@LoginString' is not a valid login or you do not have permission.



Here is the procedure



CREATE Procedure [dbo].[SetDefaultDatabase] 

@LoginString NVARCHAR(50),
@DatabaseString NVARCHAR(50)
As

Begin

Set NoCount On

Exec sp_defaultdb @loginame='@LoginString', @defdb='@DatabaseString'

End



GO

Why would this be generating the message

Here is the return

DECLARE	@return_value int

EXEC @return_value = [dbo].[SetDefaultDatabase]
@LoginString = N'Sample\login',
@DatabaseString = N'ABC'

SELECT 'Return Value' = @return_value

GO

Post #1422254
Posted Wednesday, February 20, 2013 12:53 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 1,468, Visits: 2,771
try taking the single quotes away from around your parameters

CREATE Procedure [dbo].[SetDefaultDatabase] 

@LoginString NVARCHAR(50),
@DatabaseString NVARCHAR(50)
As

Begin

Set NoCount On

Exec sp_defaultdb @loginame=@LoginString, @defdb=@DatabaseString

End



GO



Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1422257
Posted Wednesday, February 20, 2013 12:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
This maybe?


CREATE Procedure [dbo].[SetDefaultDatabase]

@LoginString NVARCHAR(50),
@DatabaseString NVARCHAR(50)
As

Begin

Set NoCount On

Exec sp_defaultdb @loginame = @LoginString, @defdb = @DatabaseString

End

GO





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1422260
Posted Wednesday, February 20, 2013 1:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 2, 2013 8:57 AM
Points: 12, Visits: 83
So when I use SSMS to exexute the procedure and I enter the Login it puts a single ' in front of the Sample/abc when I go to enter the second parameter for the database. So it looks like 'Sample\login; it works if I go back up and manually change it! How can I keep out the single ' before the string?
Post #1422267
Posted Wednesday, February 20, 2013 1:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
This is how it should be executed:

exec dbo.SetDefaultDatabase @LoginString = '[Sample/abc]', @DatabaseString = 'ThisDatabase';



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1422272
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse