locked
Cannot create stored procedure in Azure SQL database RRS feed

  • Question

  • When I try to create a stored procedure using Management Studio

    (Snippet follows

    use

    PPSC_Sky_Data


    go


    /****** Object:  StoredProcedure [dbo].[sp_RegisterProd]    Script Date: 17-02-2015 17:52:50 ******/


    SET

    ANSI_NULLS ON

    GO

    SET

    QUOTED_IDENTIFIER ON

    GO

    CREATE

    proc [dbo].[sp_RegisterProd] @inputMachid nvarchar(50),@inputDomainname nvarchar(255),@inputwsid nvarchar(255),@inputipv4 nvarchar(50),


    @custno

    as nvarchar(50),@email as  nvarchar(50),@telephone as nvarchar(50),@RegType as int

    (end of snippet)

    It fails with message 40508 - (not allowed to switch between databases) then tells me I am not authorized to create an SP in the Master db.

    So....

    How can I create a procedure in AZURE Sql?

    Thanks

    Murray


    M Whipps


    • Edited by 9gwcycn Thursday, February 19, 2015 11:34 PM
    Thursday, February 19, 2015 11:33 PM

Answers

  • Hi Eric,

    I had already tried this before with no success.

    I was unable to understand that because I have never had such an issue in my time working with MS SQL Server.

    This time I was able to key it in with no issues.

    So I guess it was operator error.

    Thanks for your help.

    Murray


    M Whipps

    • Proposed as answer by Michael Amadi Wednesday, February 25, 2015 7:57 PM
    • Marked as answer by 9gwcycn Wednesday, February 25, 2015 8:31 PM
    Tuesday, February 24, 2015 3:40 PM

All replies

  • the error is because of this line  'use PPSC_Sky_Data'.

    connect to your database directly, remove that use statement and  execute your statement and it should be good.

    • Proposed as answer by Eric__Zhang Friday, February 20, 2015 2:27 AM
    Friday, February 20, 2015 1:16 AM
    Answerer
  • Thanks for the suggestion, June but that doesn't work.

    There seems to be no way to use the management console to interact with an Azure server in any way other than minimally. No import/export, no procedure creation.

    I had to go online in Azure and use their tools which is a very cumbersome process.

    You have to manually add every variable. When you add the variable you cannot easily change the type of variable. If you choose nvarchar from the dropdown it doesn't hold. You have to do it many times before it actual takes.

    There is no Run action. Rather the Save does the compile process.

    Can't see how to debug it at all.

    You also can't simply upload table data.

    Murray


    M Whipps


    • Edited by 9gwcycn Friday, February 20, 2015 3:49 PM
    Friday, February 20, 2015 3:48 PM
  • Hi 9gwcycn,

    Creating stored procedures is not allowed in Master Database. You have to switch to Database PPSC_Sky_Data manually, use statement is not supported. After that could you please confirm again? As Creating and altering Stored procedures work fine from my SSMS.

    If you have any feedback on our support, please click here.

    Eric Zhang
    TechNet Community Support


    • Proposed as answer by Michael Amadi Wednesday, February 25, 2015 7:57 PM
    Tuesday, February 24, 2015 9:33 AM
  • Hi Eric,

    I had already tried this before with no success.

    I was unable to understand that because I have never had such an issue in my time working with MS SQL Server.

    This time I was able to key it in with no issues.

    So I guess it was operator error.

    Thanks for your help.

    Murray


    M Whipps

    • Proposed as answer by Michael Amadi Wednesday, February 25, 2015 7:57 PM
    • Marked as answer by 9gwcycn Wednesday, February 25, 2015 8:31 PM
    Tuesday, February 24, 2015 3:40 PM