create stored procedures in every new database

Hi. Is there a way to ensure that every database created on a sql
server contains a specific stored procedure? I have a set of stored
procedures that need to exist in every database on the server. Rather
than constantly checking to see if each database has what's necessary,
I was hoping there was a way to setup a template database that would
contains these sp's, and force every new database to use that as a
starting point. Is anything like this possible? Thanks.
On 9 Sep 2004 13:45:10 -0700, Michael Bosco wrote:

>Hi. Is there a way to ensure that every database created on a sql
>server contains a specific stored procedure? I have a set of stored
>procedures that need to exist in every database on the server. Rather
>than constantly checking to see if each database has what's necessary,
>I was hoping there was a way to setup a template database that would
>contains these sp's, and force every new database to use that as a
>starting point. Is anything like this possible? Thanks.
Hi Michael,
Just create the stored procedure(s) in the model database. That is the
template that will be used for all future new databases.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

create stored procedures in every new database

Hi. Is there a way to ensure that every database created on a sql
server contains a specific stored procedure? I have a set of stored
procedures that need to exist in every database on the server. Rather
than constantly checking to see if each database has what's necessary,
I was hoping there was a way to setup a template database that would
contains these sp's, and force every new database to use that as a
starting point. Is anything like this possible? Thanks.On 9 Sep 2004 13:45:10 -0700, Michael Bosco wrote:
>Hi. Is there a way to ensure that every database created on a sql
>server contains a specific stored procedure? I have a set of stored
>procedures that need to exist in every database on the server. Rather
>than constantly checking to see if each database has what's necessary,
>I was hoping there was a way to setup a template database that would
>contains these sp's, and force every new database to use that as a
>starting point. Is anything like this possible? Thanks.
Hi Michael,
Just create the stored procedure(s) in the model database. That is the
template that will be used for all future new databases.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

create stored procedure in IF-structure

Hi everyone,

I'm currently struggeling in creating some SQL script to create stored procedures. I found the following example on MSDN:

Code Snippet

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@.lastname varchar(30) = 'D%',
@.firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @.firstname
AND au_lname LIKE @.lastname
GO

The thing is, I want to change and use it like this:

Code Snippet

USE pubsIF NOT EXISTS (SELECT name FROM sysobjects


CREATE PROCEDURE ...

USE pubs

GO

ALTER PROCEDURE au_info2 ...

But that does not seem to work.. I get the following error:

Code Snippet

Incorrect syntax near the keyword 'PROCEDURE'

Any idea's? Any help is appreciated!

Kind regards,

Frederik

The CREATE statement needs to be the first statement in the batch so you can't have it in after an IF clause.

I guess you could get round this by doing the following:

IF NOT EXISTS.....

EXEC('CREATE PROCEDURE au_info2 AS.....')

HTH!

|||Very dirty, but it works! I need it to avoid some errors when

replicating and such.. Thx!

Create SQL Server Objects from Command Prompts

Hi

Is there any why to Create SQL Server Objects from Command Prompts like (Databases , Tables, Stored Procedures, …) ??

If you will Install some Applications Like this forums you will see the SQL Server object Created from Command Prompts

How Can I do that .. ??

And thanks with my regarding

FraasHave a look at OSQL in SQL Server BOL

Create SP denied ?

We added a user to a database with db_datareader, db_datawriter and Create Procedure Privilages .. while creating stored procedures using dbo. prefix he gets an error

Server: Msg 2760, Level 16, State 1, Procedure test4, Line 3
Specified owner name 'dbo' either does not exist or you do not have permission to use it.

What is the problem. We don't want him to create procedures with his uid as prefix .. Please helpYou need to put him/her in the dbo_owner role...|||Is it necessary ? If he is dbo he doesn't need to specify dbo. while creating objects . I don't want him to be a dbo|||If yo uwant him to vcreate objetcs as dbo (the owner) he has to have the rights to be in the group...

Or...

He can develop in his own db you set up for him/her...make the dbo in that group (or not) and let them create objects without hard coding the owner...

Then have him give you the scripts and you compile them.

What'dya think?sql

Create report with complex and nested stored procedures

Hi all,

I had developed all my reports using CRXI and stored procedures of MSSQL 2000. Now I am migrating from CRXI to SSRS. But i have a problem because of my report stored procedures. These stored procedures are complex and nested. When i try to use procedures in a report, it gives a error. When i comment out my EXEC line in my procedures, it goes OK.

Please help me to do this ?

It can only mean that the nested stored procedure has issues and needs to be fixed. The stored procedure that you use in SQL Reporting services can use a stored procedure which has nested another stored procedure.

Shyam

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
Thanks for your help
Patrick
On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
---
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
----
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
---
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
---
Thanks for your help
PatrickPFI wrote:
> the procedure exists...
> As CREATE PROCEDURE can not be combined with any other Transact-SQL
> statement in a single batch, I was wondering if there were any way to
> achieve something like this :
> (..)
Unfortunately there is no 'CREATE OR REPLACE' statement in SQL Server.
I think your idea to create a procedure if it doesn't exist and then
alter it to desired form (instead of dropping and creating it) is quite
reasonable.
Of course your script has to be modified. I suggest you use something
like this:
IF OBJECT_ID('Procedure1') IS NULL
EXEC ('CREATE PROCEDURE Procedure1 AS SELECT 1')
GO
ALTER PROCEDURE Procedure1
AS
BEGIN
SELECT 2
RETURN 0
-- (..)
END
Best regards,
Marcin Guzowski
http://guzowski.info|||On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to
desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Create or Alter a procedure only when necessary

Hi,
I'm using scripts to create stored procedures...
The way I'm currently doing it is the following :
---
USE tempdb
GO
IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
DROP PROCEDURE test
GO
CREATE PROCEDURE test AS ...
----
I would like to use the CREATE PROCEDURE statement only if the
procedure does not exist and use ALTER PROCEDURE statement instead if
the procedure exists...
As CREATE PROCEDURE can not be combined with any other Transact-SQL
statement in a single batch, I was wondering if there were any way to
achieve something like this :
---
USE tempdb
GO
IF NOT EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'test' )
CREATE PROCEDURE test AS RETURN 0
GO
ALTER PROCEDURE test...
---
Thanks for your help
PatrickPFI wrote:
> the procedure exists...
> As CREATE PROCEDURE can not be combined with any other Transact-SQL
> statement in a single batch, I was wondering if there were any way to
> achieve something like this :
> (..)
Unfortunately there is no 'CREATE OR REPLACE' statement in SQL Server.
I think your idea to create a procedure if it doesn't exist and then
alter it to desired form (instead of dropping and creating it) is quite
reasonable.
Of course your script has to be modified. I suggest you use something
like this:
IF OBJECT_ID('Procedure1') IS NULL
EXEC ('CREATE PROCEDURE Procedure1 AS SELECT 1')
GO
ALTER PROCEDURE Procedure1
AS
BEGIN
SELECT 2
RETURN 0
-- (..)
END
Best regards,
Marcin Guzowski
http://guzowski.info|||On 2 mar, 20:48, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.info> wrote:
> PFI wrote:
> > theprocedureexists...
> > AsCREATEPROCEDUREcan not be combined with any other Transact-SQL
> > statement in a single batch, I was wondering if there were any way to
> > achieve something like this :
> > (..)
> Unfortunately there is no 'CREATEOR REPLACE' statement in SQL Server.
> I think your idea tocreateaprocedureif it doesn't exist and thenalterit to desired form (instead of dropping and creating it) is quite
> reasonable.
> Of course your script has to be modified. I suggest you use something
> like this:
> IF OBJECT_ID('Procedure1') IS NULL
> EXEC ('CREATEPROCEDUREProcedure1 AS SELECT 1')
> GO
> ALTERPROCEDUREProcedure1
> AS
> BEGIN
> SELECT 2
> RETURN 0
> -- (..)
> END
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
Many thanks for this solution, it works perfectly and this is exactly
what I was looking for...

Create My Own Log (for functionality)

I'm calling stored procedures in a DTS package. The log file only shows that of DTS. In stored procedures I use "Print" to print out error messages the program encouters. I can see the errors when I execute a procedure in Query Analyzer, but they go nowhere when I run a DTS package.

How do I generate a myfunctionality.log with these lines?

Thanks,

LiliHi,

The best way to add Own Log functionality , is to create a log table and use insert statement to log message instead of print. The advantage will be that log will be available for future reference whereas filelog will be over written every time you run package.

best of luck

create multiple stored proc thru script

Hi,

I am using an MSDE database.
During installation, i intend to use a a script file which creates all tables and stored procedures using ADODB in InstallShield.
But i get the error
"CREATE PROCEDURE must be the first statement in a batch update". Moreover "GO" is not recognized by ADODB.
How can i create the stored procedures ?

regards,
henryCall osql to execute the script?|||Hi Brett,

I am using osql to execute the script successfully. But i am still wondering how to do this through ADODB.

Thanks and regards,
henry

Create Multiple Store Procedures in 1 SQL statement

Hi guys , may I know is that possible to create multiple store procedures in 1 time using 1 SQL statement? Thx for the assistance.

Best Regards,

Hans

I can't get your Question..

Need more information

|||

I hope you need to write sp like this way.

/*
sp1
*/
CREATE PROC MultipleStoreProcedures;1
AS
BEGIN
SELECT [CategoryID], [CategoryName], [Description], [Picture]
FROM [Northwind].[dbo].[Categories]
END

GO

/*
sp2
*/
CREATE PROC MultipleStoreProcedures;2
AS
BEGIN
SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]
FROM [Northwind].[dbo].[Customers]
END
GO

**
--execute sp1
exec MultipleStoreProcedures;1
--execute sp2
exec MultipleStoreProcedures;2
**

|||

thx. it helps. :) However, if using 2005 for creating, then the 'GO' statement will be dissappear when u modify the store procedure. But it seems ok anyway. Thx alot.

Best Regards,

Hans

|||

The 'GO' isn't part of a T-SQL statement, it's just a marker that identifies where the end of batch is.
So, the above is, if you save it all as one file, and run it all at the same time, still two batches ( or statements if you will ), but it looks like it's working for you wanted anyway =:o)

/Kenneth

Create from template database?

I have a database that needs to be created over and over (with dozens of pre-populated tables & procedures) for various users of a software application. These databases must remain distinct. Currently, the table/procedure data is maintained in Create and Insert scripts (approx 15,000 lines total) that we run as needed, but maintaining these scripts in unwieldy at best.

Ideally, I would like to create each new database from a template database. This way, we could forget the scripts entirely, and make future modifications directly to the template, and have them automatically bubble into all future databases created from it.

I know this is how the "model" system database works, and think it is great! Make some change in model, and every new database is created with that change. Perfect, except that we cannot guarantee that our software application will be the only one using some instance of SQL (SS2K5), or that our databases will be the only ones inheriting from model.

So, instead of having EVERY new database inherit from model, is there a way to specify that only certain databases inherit from 'model' while others inherit from 'custom_model'? Is there a way to specify a database to use some template in the create process (maybe similar to in postgresql)? Ideas? Thanks, -Matthew_53

Unfortunately, there is no 'conditional' way to use multiple [Model] databases.

However, you can easily create your 'template' database, and when you need another database derived from that template. In the object explorer of SSMS, right-click on the template database and select [Tasks], then select [Copy Database...]. Follow the steps throught the Copy Database Wizard.

|||Right, but is there a way to do this programatically, from within our application, whenever we need to generate a 'new' database?|||Programmatically, you could DETACH the template database, then xcopy it with a new db name, and then re-ATTACH both of them. (Actually, unless you need to continually 'tweak' the template database, it doesn't even need to be attached. Just copy the *.mdf file to a new name and ATTACH the new database.)

Create from template database

I have a database that needs to be created over and over (with dozens of pre-populated tables & procedures) for various users of a software application. These databases must remain distinct. Currently, the table/procedure data is maintained in Create and Insert scripts (approx 15,000 lines total) that we run as needed, but maintaining these scripts in unwieldy at best.

Ideally, I would like to create each new database from a template database. This way, we could forget the scripts entirely, and make future modifications directly to the template, and have them automatically bubble into all future databases created from it.

I know this is how the "model" system database works, and think it is great! Make some change in model, and every new database is created with that change. Perfect, except that we cannot guarantee that our software application will be the only one using some instance of SQL (SS2K5), or that our databases will be the only ones inheriting from model.

So, instead of having EVERY new database inherit from model, is there a way to specify that only certain databases inherit from 'model' while others inherit from 'custom_model'? Is there a way to specify a database to use some template in the create process (similar to in postgresql)? Ideas? Thanks, -Matthew_53

Just thinking out loud really, but you could install a second SQL Server instance (possibly on the same server - limiting the memory available to the second instance, if appropriate) and maintain the model database within that instance.

Your database creation process would then be to create the database on the new instance, detach, then attach to the new instance - you could do this by running just one script per instance or even by setting up a linked server and performing the actions with a single script. If you set the default Data and Log folders to the same folders as the existing instance then you wouldn't even have to move any files around.

If you lock down the security on the second instance you can limit who has access to modify the model database.

Chris

|||Better yet, why not just create the template/model database on the one instance, then detach it and store the data and log files away for safe keeping? Then any time you wanted to clone it, it would only be a matter of duplicating these files into the appropriate data directories, and attaching them with the desired database name.

You could also achieve the same effect using a backup/restore, as long as you're using the WITH MOVE clause during the restore. This way, you could leave the template database online and maintain it easily, at the expense of increased cloning times.

Either approach could be made pretty turn-key with some scripts containing an appropriate mix of BACKUP, RESTORE, sp_attach, sp_detach, xp_cmdshell (to copy files), etc.

Create Extended Procedures in VS 2005

Hi

I am trying to write an extended procedure that accepts a string parameter and returns an integer value. The extended procedure calls a regular stored procedure of a database passing the string parameter as an input. The int value is an OUT parameter to this procedure.

Can I some one suggest where do I get started with respect to this in VS 2005.Why do you want to write an extended stored procedure to call a TSQL SP? This is overkill actually. Extended SPs are meant for computation intensive operations or other logic that cannot be performed efficiently using TSQL. It has it's limitations, performance, reliability and security issues. Or you trying to just learn extended SP programming? If later you can look at the SQL Server samples. You can also look at ODBC/OLEDB samples that will show you how to call SPs.|||The thing is that we need to make DML changes while calling a function. Since normal UDFs dont allow to do it I am trying to call an xp. Since we also need to look at concurrency I am having a stored procedure with transactions taken care. Hence the need of calling T_SQL sp from xp.|||Where do I look for the Extended Stored Procedure DLL Wizard while I open VS 2005 --> Open Project.

I do not see any such wizrd name.

Regards
Imtiaz|||

Use of side-effecting code from UDF is not recommended. It takes lot of work to get it right (dealing with bound connections, concurrency issues, deadlocks, scalability of xps, virtual memory issues depending on how the xp is written etc). Lastly, use of such UDFs in SELECT statement can cause unexpected behavior.

create default tables, procedures, etc. on newly connected remote SQL server

I have a website I'm ready to test on the server it will call home. I just got connected to the remote SQL server that it will be using. As I've been creating the site, I've been using the default SQL Express set-up in Visual Studio. Is there a way to have Visual Studio create all those default tables, procedures, etc. OR is there a way to copy all of that stuff from the SQL Express running on my machine to the remote SQL Server 2005?

-Mathminded

There are many ways to move the database, but the best is Backup and Restore because it moves everything including permissions, you Backup the database locally and put the .bak file in the location below in the remote server and use the Backup and Restore wizard SQL Server will create the file path just make sure the path is correct don't try to create the path because SQL Server gets confused if you do. I have created a FAQ that covers all the ways to move a database, if you use another method remember to also move the permissions. Hope this helps.

C:\Program Files\Microsoft SQL Server\MSSQL\Backup

http://forums.asp.net/thread/1454694.aspx|||

Thanks, Caddre. Before I got your message I had just found this link (http://www.c-sharpcorner.com/uploadfile/dsdaf/104012006083052am/1.aspx) and followed those instructions. It created all the tables, views, and stored procedures that had been created on my local SQL Express installation. From what I understand of the Backup and Restore method, I have to be able to load the backup file on the remote server, right? I don't think I have access to do that. I'm planning to call the database administrator tomorrow, anyway, because the Website Administration Tool in VS2005 says I don't have permission to execute 'aspnet_CheckSchemaVersion' so I'm not able to create new users. Crazy. :-)

For future readers of this thread, if you end up using the link in this message, and you have a problem running the tool like I did, you may be interested in this quote fromhttp://quickstarts.asp.net/QuickStartv20/aspnet/doc/management/tools.aspx :

"To configure and install databases on a SQL server to use these ASP.NET features, you can use theaspnet_regsql tool. This tool can be found in the version-specific framework directory, under theMicrosoft.NET\Framework subdirectory of your Windows system folder."

As always, thank you for your help, Caddre.

-Mathminded

|||

FYI for anyone who runs into the problem of not having permission to execute that stored procedure. I used the Microsoft SQL Server Management Studio Express to add my database user to the aspnet_Membership_FullAccess role and that took care of it. I think the backup and restore method that Caddre mentioned probably takes care of problems like that, but unfortunately I didn't have access to load files on the remote server. If you can do it her way, I suggest doing that since she has about a million times more experience than I do (that's probably an understatement . . . it's probably closer to a billion or so). But, if you're in the situation I was in, this did work for me.

Happy SQLing!

-Mathminded

|||

addition to my previous post:

I had to add my database user to ALL of the roles ending in "_FullAccess" in order to fully use the Website Administration Tool.