creating a user stored proc

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

ThanksCan you post a repro with procedure you are trying to create?|||

there's nothing really special about the proc.. Any proc I create becomes a system proc in the mster db...

e.g.

CREATEPROCEDURE test

AS

BEGIN

print'a'

END

GO

will behave like this.. I don't think this has anything to do with the actual proc I'm trying to use...

Thanks..

|||if you want to create the system stored procedure then use master and create your procedure started with sp_abc other wise create your sp on your desired database.|||There is no supported way to do this in SQL Server 2005. We are considering adding such features that will allow you to deploy SPs in one location and use it in context of multiple databases. For now, you will have to create the SP in each database. For admin type of SPs, you could use dynamic SQL within the SP.|||

I don't actually want to create system procs.. I want to create this proc in the master database and do not want it to be a system proc.. just a normal user proc.. I was able to do so since recently..But I think some thing has gone wrong and now when ever I create a proc, it gets created as a system proc... I did run the sp_MS_upd_sysobj_category with 2 but I understand that it's obsolete now...Any idea how I can turn this off? or atleast how this may have happened?

|||The feature I talked about will allow user SPs to behave like system SPs in terms of resolving object names in context of the db in which the SP is being executed. Anyway, for your problem I am not sure what can be done. The reason why we don't document certain system SPs is because it is for internal use and has severe implications if used incorrectly. I don't know if sp_MS_upd_sysobj_category code has changed in SQL Server 2005 or if it is some other SP call you did. But it looks like you will have to uninstall and install SQL Server or restore master from a last clean backup.

creating a user stored proc

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

ThanksCan you post a repro with procedure you are trying to create?|||

there's nothing really special about the proc.. Any proc I create becomes a system proc in the mster db...

e.g.

CREATE PROCEDURE test

AS

BEGIN

print 'a'

END

GO

will behave like this.. I don't think this has anything to do with the actual proc I'm trying to use...

Thanks..

|||if you want to create the system stored procedure then use master and create your procedure started with sp_abc other wise create your sp on your desired database.|||There is no supported way to do this in SQL Server 2005. We are considering adding such features that will allow you to deploy SPs in one location and use it in context of multiple databases. For now, you will have to create the SP in each database. For admin type of SPs, you could use dynamic SQL within the SP.|||

I don't actually want to create system procs.. I want to create this proc in the master database and do not want it to be a system proc.. just a normal user proc.. I was able to do so since recently..But I think some thing has gone wrong and now when ever I create a proc, it gets created as a system proc... I did run the sp_MS_upd_sysobj_category with 2 but I understand that it's obsolete now...Any idea how I can turn this off? or atleast how this may have happened?

|||The feature I talked about will allow user SPs to behave like system SPs in terms of resolving object names in context of the db in which the SP is being executed. Anyway, for your problem I am not sure what can be done. The reason why we don't document certain system SPs is because it is for internal use and has severe implications if used incorrectly. I don't know if sp_MS_upd_sysobj_category code has changed in SQL Server 2005 or if it is some other SP call you did. But it looks like you will have to uninstall and install SQL Server or restore master from a last clean backup.

Creating a stored proc that references an optional column

Is it possible to create a stored proc which references a non-existent
field without getting the "invalid column name" error?
The field will only exist in one or two production databases (not in
all) but we still want the stored proc to exist in all databases so
that users in the future may add the new field to their own database
(using a Dictionary builder form that exists in the application) and
then be able to run the report (via the stored procedure) that
references the field.
The stored proc checks that the field exists before proceeding.
ThanksIt is possible only if the table does not exist when the procedure is
created. However, this is not a good idea. It would be better to create
two different procedures.
Razvansql

Creating a Stored Proc from a particular template?

Hello,

I'm trying to get to grips with SQL Server 2005.

One of the things I want to do is provide members of my team with a stored procedure template that they can use which has special error handling code in it , etc

I found my way to the template explorer and created a new template that I want to use to create stored procedures in certain circumstances (but not ALL circumstances)

But now I can't figure out how to specify WHICH template to use when creating a stored procedure.

Like, when I click on the Programmability/Stored Procedures node and then right click and select New Stored Procedure... it just uses the Basic Template.. but I'd like to be able to elect to use my alternative template to create the stored proc.

So, what are the correct steps to follow? do i just double click my new template in Template Explorer? And then have to go Query/Specify Values for Template Parameters ? Or what?

If this is the way to do it then it seems very clunky really....

Thanks

>>But now I can't figure out how to specify WHICH template to use when creating a stored procedure.

>>Like, when I click on the Programmability/Stored Procedures node and then right click and select New Stored Procedure... it just uses the Basic Template.. but I'd like to be able to elect to use my alternative template to create the stored proc.

You can create procedures by double-clicking on the template. Or you may just drag-and-drop any template into the body of any query.

Creating a DECLARE variable with SMO

How can I create this stored proc with the SMO StoredProcedure class?

It doesn't want to work and I think it has something to do with the DECLARE statement. Anyone know how I can get this to work

Cheers
Jon

storedProc.TextBody = "DECLARE @.GroupID int"+

"SELECT @.GroupID = GroupID FROM Groups WHERE (GroupName = \"Administrator\")"+

"INSERT INTO gworkshop.Users"+

"(GroupID, Username, Password, Active, Deleted)"+

"VALUES (@.GroupID,@.Username,@.Password,@.Active,@.Deleted)";

What does the rest of your code to create the procedure look like? Are you trying to create it and it's failing? If so, what's the error message?|||

I'm not in work right now so can't paste the code but basically its creating a server then the database name then in the storedprocedure I add parameters like this

StoredProcedure.Parameters.Add(new StoredProcedureParameter(storedprocedure,"@.Name", DataType.VarChar(30)));

The error is something like "There was an error creating stored procedure", then the procedure name

|||

If you are debugging, you can break on the exception it's throwing, then look at the inner exception(s) of that exception to find out exactly what the server is not liking about your stored procedure. Also, you can look at the script of the stored procedure ( use the script function), and look at the syntax it generates for you.

Whenever I create a stored procedure, I always do it like so:

Microsoft.SqlServer.Management.Smo.StoredProcedure proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure(database, procName);

proc.AnsiNullsStatus = true;

proc.QuotedIdentifierStatus = true;

proc.TextMode = false;

// Insert Params

Microsoft.SqlServer.Management.Smo.StoredProcedureParameter param = new Microsoft.SqlServer.Management.Smo.StoredProcedureParameter(proc, "@." + paramName, paramDataType);

// If it's an output

param.IsOutputParameter = true;

proc.Parameters.Add(param);

// Now for the body

proc.TextMode = true;

proc.TextBody = "SELECT * FROM foo";

proc.Create();

|||

It seems that the problem was with the DECLARE @.GroupID

You need to add any parameters to the stored procedure, you can't add them in the SQL

storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Username", DataType.VarChar(30)));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Password", DataType.Binary(30)));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Active", DataType.Bit));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.Deleted", DataType.Bit));
storedProc.Parameters.Add(new StoredProcedureParameter(storedProc, "@.GroupID", DataType.Int));

//storedProc.TextBody = "DECLARE @.GroupID int" +
storedProc.TextBody = "SELECT @.GroupID = GroupID FROM Groups WHERE (GroupName = \"Administrator\")" +
"INSERT INTO gworkshop.Users" +
"(GroupID, Username, Password, Active, Deleted)" +
"VALUES (@.GroupID,@.Username,@.Password,@.Active,@.Deleted)";
storedProc.Create();

CREATE, ALTER, why wont it save?

when i create a new stored proc, i say "CREATE PROCEDURE [sprocName]"

but the next time i edit the stored proc, it hasnt changed to "ALTER ..."

I manually change it to ALTER, and run it, and close it, and it reverts back to "CREATE..."

Is there some sort of privelage i have to enable? Has anyone encountered this before?

Help guys, please :-S

Also, if you know of a better place where i can post this thread, please let me know

The script you are editing is the crate script for the SP. To update the sp you do an alter but the create script will still be a create.

How are you doing this?

In v2000 enterprise manager clicknig on OK updates the SP even though it's a create.

In v2005 a modify from management studion generates an alter script.

It's better to maintain SPs from disk based scripts in a query window. The first statment should be a conditional drop of the SP followed by a create.

if object_id('mysp') is not null -- or something like that

drop proc mysp

go

create proc mysp

as

....

go

|||

thanx Nigel, the IF OBJECT_ID() IS NOT NULL works

now atleast query analyser doesnt tell me:

Server: Msg 2714, Level 16, State 5, Procedure [spName], Line 16
There is already an object named '[spName]' in the database.