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 procedure to insert data?

Hello all,

I am having a lot of trouble with stored procedures. Could anyone help me out.

I have a table which contains a number of meetings. What I want to do is search this table, get out all the meetings for today and put them in a seperate table meetings today.

I can select the values, and I can insert the values.

But how do I store the values so that i can pass the results of the select to the insert?

Im also having a lot of trouble with storing date values.

ANy help would be greatly appreciated.

Regards,

Padraic Hickey

if exists (select * from dbo.sysobjects where id = object_id(N'[table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [table2]
SELECT *
INTO table2
FROM table1
WHERE convert(varchar,table1.meetingdate,112)=convert(varchar,@.mydate,112)
|||

Cheers Motley,

I was having a lot of trouble with that one.

If ever i can return the favour.

Padraic

Creating a stored procedure in SQL Server Management Studio

I have a database that I want to add stored procedures but when I go to save the stored procedure it asked me to save it as a .sql file and doesn;t add it to the database.

This is what I'm doing:

right click on database > programability > stored procedures

select "new Stored Procedure"

writting the procedure

then save - this is when i get the save dialogue rather than seeing the sp added to the database.

There must be something very simple that I'm doing wrong but I just can't figure it out, any help would be greatly appreciated.

Damien

You need to execute this command (that begins with CREATE PROCEDURE) instead of saving to disk.

So, you need to press F5 or press the Execute Button from program bar.

|||Thanks

creating a stored procedure-- help

im novice to sqlserver and stored procedures.
Can the php code below be converted to a stored procedure
$y=1;
$query = "Select * From tblNews Order By aOrder";
$result = mysql_query($query,$db_connection);
$NoRows = mysql_num_rows($result);
if ($NoRows != 0 )
{
while ($row = mysql_fetch_array($result))
{
$UpdateQuery = "Update tblNews
Set aOrder= $y
Where ID=".$row["ID"];
mysql_query($UpdateQuery,$db_connection)
;
$y++;
}
}
basically, i want select all from tblNews, order by aOrder
then update aOrder in each record starting at 1 and incrementing by 1
untill all the records have been processed
Can anyone help me please, is it possible
thanks in advance
SteveIf all values in column [aOrder] are diff, then you can try,
update tblNews
set aOrder = (select count(*) from tblNews as a where a.aOrder <=
tblNews.aOrder)
AMB
"ahoy hoy" wrote:

> im novice to sqlserver and stored procedures.
> Can the php code below be converted to a stored procedure
> $y=1;
> $query = "Select * From tblNews Order By aOrder";
> $result = mysql_query($query,$db_connection);
> $NoRows = mysql_num_rows($result);
> if ($NoRows != 0 )
> {
> while ($row = mysql_fetch_array($result))
> {
> $UpdateQuery = "Update tblNews
> Set aOrder= $y
> Where ID=".$row["ID"];
> mysql_query($UpdateQuery,$db_connectio
n);
> $y++;
> }
> }
>
> basically, i want select all from tblNews, order by aOrder
> then update aOrder in each record starting at 1 and incrementing by 1
> untill all the records have been processed
> Can anyone help me please, is it possible
> thanks in advance
> Steve
>|||
Steve,
You could use something along the lines of this... (Un-Tested)
Create Proc TestProcedure
As Begin
Declare @.ID Integer
Declare @.NewOrder Integer
Set @.NewOrder = 1
Declare OrderCursor Cursor For
Select ID From tblNews
Order By aOrder
Open OrderCursor
Fetch Next From OrderCursor Into @.ID
While @.@.Fetch_Status = 0
Begin
Update tblNews
Set aOrder = @.NewOrder
Where ID = @.ID
Set @.NewOrder = @.NewOrder + 1
Fetch Next From OrderCursor Into @.ID
End
Close OrderCursor
Deallocate OrderCursor
End
Go
Although if it is a huge amount of Data and performance is an issue
then I would probably not use a Cursor.
Hope this helps
Barry|||Barry
thank you so much!
i wouldve been trying to figure that out for days, it is exactly what i
needed.
Just needed to use the correct field names and rename Interger to Int,
proc to procedure!
Now i can finish my job
Its only for a small amount of data, 10-20 records
Awesome
Steve :)
Barry wrote:

> Steve,
>
> You could use something along the lines of this... (Un-Tested)
>
> Create Proc TestProcedure
> As Begin
>
> Declare @.ID Integer
> Declare @.NewOrder Integer
> Set @.NewOrder = 1
>
> Declare OrderCursor Cursor For
> Select ID From tblNews
> Order By aOrder
>
> Open OrderCursor
> Fetch Next From OrderCursor Into @.ID
>
> While @.@.Fetch_Status = 0
> Begin
>
> Update tblNews
> Set aOrder = @.NewOrder
> Where ID = @.ID
> Set @.NewOrder = @.NewOrder + 1
> Fetch Next From OrderCursor Into @.ID
> End
> Close OrderCursor
> Deallocate OrderCursor
>
> End
> Go
>
> Although if it is a huge amount of Data and performance is an issue
> then I would probably not use a Cursor.
> Hope this helps
> Barry
>

creating a new user for sp execution say sa (to whom i am in need of creating under my user defi

I am in need of creating a new user for stored procedures execution say sa (to whom i am in need of creating under my user defined db)

I am guessing you are trying to create an explicit user (database principal) for the login SA, correct?

SA and any other member of sysadmin will always be mapped on any database to DBO, even more, SA is a special principal and the DDL will prevent to create a user for it.

If you need this principal for EXECUTE AS in modules, I would recommend either creating a user without login or use digital signatures. You can find some samples on my blog and Laurentiu’s blog:

· http://blogs.msdn.com/raulga/

· http://blogs.msdn.com/lcris/

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Creating A Dataabas Via SQL Script

I have an SQL script file and what it does is it creates a database with tables and stored procedures, the problem that I have is that I dont know how to execute it, I opened it in VS.NET 2003 but there is no option to execute it anyone know how I can accomplish this. thank you all for your time and patience.If you have the SQL Client installed, you can use Query Analyzer. If not, in theory you could use OSQL.exe (for instance, if you have MSDE and do not have the SQL Client installed). Easier would be to install the SQL client, if you can.|||Where can I get a copy of the SQL client? i do have the OSQL.exe but it's command line and I cant seem to find anything in the help file as to how to run database creating procedures. but I do really appreciated the direction that you set me in. thanks.|||The cheapest way (if you are not an MSDN subscriber) is to get the developers edition, about US$40.00 last time I looked.

The osql utility is typically used in these ways:

a.. Users interactively enter Transact-SQL statements in a manner similar
to working on the command prompt. The results are displayed in the command
prompt window.

b.. Users submit an osql job either specifying a single Transact-SQL
statement to execute or pointing the utility to a text file that contains
Transact-SQL statements to execute. The output is usually directed to a text
file, but it also can be displayed in the command prompt window.
The osql utility uses the ODBC database application programming interface
(API). It is a replacement for the isql command prompt utility based on the
DB-Library API. Both utilities are provided with Microsoft SQL ServerT 2000.
The DB-Library API remains at a SQL Server 6.5 level; therefore,
applications that depend on DB-Library, such as isql, do not support some
SQL Server 2000 features. For example, isql cannot access columns defined
with the ntext data type and truncates any char, varchar, nchar, or nvarchar
columns longer than 255 bytes. It also cannot retrieve results as XML
documents. Except for these limitations in isql, both osql and isql support
the same features

Typing OSQL -? gets you this:

E:\Program Files\Microsoft SQL Server\80\Tools\Binn>osql /?
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]

Create Views not Tables

I want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??
Thanks, John :eek:See the GRANT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp) command in Transact-SQL.

-PatP

create views in stored procedures?

Can you create views within a stored procedure?

The short answer is 'Yes'; what exactly do you have in mind because this might not be such a good idea. You might need to use dynamic SQL to get the job done; otherwise, you might get a procedure compile error such as:

Server: Msg 156, Level 15, State 1, Procedure wacko, Line 6
Incorrect syntax near the keyword 'view'.

|||

I've got a report (Access front end) that takes over 2 minutes to run. Access sometimes times out before the report finishes. (I've already adjusted Access's timeout range to the maximum amount.) The report data is based on a view that pulls data from several other views and tables (using unions, outer joins, etc.). I decided the best way to keep the report from timing out in Access was to have the result set queried each night and inserted into a table. The report will now use the table as it's data source. The data doesn't have to be dymnamic as long as it's updated nightly. So, now I'm trying to create a stored procedure that will run nightly, inserting the final results into a table. I'm getting this error message for every line I'm trying to create a view on:

Server: Msg 156, Level 15, State 1, Procedure sp_insert_edirecalltbl, Line 16
Incorrect syntax near the keyword 'VIEW'.

Here's a small portion of the code in my procedure:

BEGIN
CREATE VIEW edirecallview
As SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace
END

BEGIN
select xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
into edirecalltbl
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace
END

Maybe I should use temporary tables instead of views? If I have to, I can use permanant tables instead of views. Ideas?

|||

You can use a permanent table to hold the summary information that you populate each night. The procedure can look like this

CREATE PROCEDURE spxyz

AS

BEGIN

Truncate mySummaryTable;

--<Drop indexes if the data is in millions, otherwise not>

INSERT INTO mySummaryTable(col1, col2, col3)

select a.col1, a.col2, b.col3

from TableA a inner join TableB b on a.pk = b.pk

--<Recreate indexes if you dropped before the insert>

--Thats it

END

|||

I would actually use a CTE (common table expression) instead of a view. This is not available in 2000 just 2005.

WITH cte_edirecallview (xmltrace, xmllname, xmlvetcode, xmltransdate, xmltotalamt, xrawtrace, xrawtransdate, xrawamount, deptrace, deptransdate, depamount, depvouchernum, drawtrace, drawtransdate, drawamount)

AS

(

SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace

)

select xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
into edirecalltbl
FROM cte_edirecallview x FULL OUTER JOIN cte_edirecallview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace

|||That would be so perfect...if only I was using 2005! Thanks, anyway. It's something to look forward to.|||

You can dynamically create the view via Exec('view_definition').

e.g.

Code Snippet

Exec ('create view edirectcalview AS

SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace')

However, in your case, you can just derive the query.

e.g.

Code Snippet

select xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
into edirecalltbl
FROM (

SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace)

x FULL OUTER JOIN (

SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace)

d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace

|||

In addition, in 2000 it was very typical to just give in an use a temporary table for this sort of thing, and to be honest, sometimes it can be better anyhow. It really depends on how well SQL Server happens to optimize your derived table. Sometimes it can manifest itself as multiple queries rather than spooling the rows into some temporary storage and calculating it once:

SELECT top 100 xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount

into #edirecallview
FROM edirecxmlrawview x FULL OUTER JOIN edirecrawdepview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace

select xmltrace,
xmllname,
xmlvetcode,
xmltransdate,
xmltotalamt,
x.rawtrace as xrawtrace,
x.rawtransdate as xrawtransdate,
x.rawamount as xrawamount,
deptrace,
deptransdate,
depamount,
depvouchernum,
d.rawtrace as drawtrace,
d.rawtransdate as drawtransdate,
d.rawamount as drawamount
into edirecalltbl
FROM #edirecallview x FULL OUTER JOIN #edirecallview d
on x.rawtrace = d.rawtrace or
xmltrace = deptrace
order by xmltrace, x.rawtrace, deptrace

This would be one of the steps I might take to optimize your query if it were running slowly (and we had exhausted all plan based optimizations) anyhow.

CREATE via Dynamic SQL into new database?

From a stored procedure running in the context of one database, I would like
to create a set of objects (stored procedures, functions, views, users) into
a newly-created second database, where the name is dynamically determined.
Creating the new database and retrieving its name is no problem, the problem
is executing CREATE FUNCTION, CREATE PROCEDURE, etc. in the context of the
new database.
As you know, executing dynamic SQL 'use database' won't change the context
of an executing procedure. And 'use database; create function ...' doesn't
work, because the create statements need to be in their own batch. I cannot
store the objects in Master, so I can't have them automatically created with
the new database.
Is there a way to copy the objects from an existing (i.e. template) database
to the new one using dynamic SQL? Any way to attach a copy of a template
database file to a new database dynamically?
Or any out-of-the-box ideas?declare @.sql nvarchar(1000)
set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
exec sp_executesql @.sql

> From a stored procedure running in the context of one database, I would
> like to create a set of objects (stored procedures, functions, views,
> users) into a newly-created second database, where the name is dynamically
> determined. Creating the new database and retrieving its name is no
> problem, the problem is executing CREATE FUNCTION, CREATE PROCEDURE, etc.
> in the context of the new database.
> As you know, executing dynamic SQL 'use database' won't change the context
> of an executing procedure. And 'use database; create function ...' doesn't
> work, because the create statements need to be in their own batch. I
> cannot store the objects in Master, so I can't have them automatically
> created with the new database.
> Is there a way to copy the objects from an existing (i.e. template)
> database to the new one using dynamic SQL? Any way to attach a copy of a
> template database file to a new database dynamically?
> Or any out-of-the-box ideas?
new|||here's a real hum-dinger: (this is all on one line)
exec opendatasource('sqloledb', 'data
source=YourServer;uid=UserId;pwd=Passwor
d').YourDatabase.dbo.sp_executesql
N'create table mydatabase.dbo.newtable (myfield1 int)'
You'll want to change the following areas:
YourServer
UserId
Password
YourDatabase
.. and the statement of course

> declare @.sql nvarchar(1000)
> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> exec sp_executesql @.sql
>
>
new|||Thanks, but this isn't the issue. Issue is that from a stored procedure (or
batch, for that matter) running in the context of database A, do:
declare @.DBName varchar(20)
set @.DBName = 'dynamic'
declare @.SQL varchar(200)
set @.SQL = 'use ' + @.DBName + '; create function foo ...'
exec (@.SQL)
Doesn't work because 'create function' must be at the beginning of a batch.
set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
design.
Need to create functions, stored procs etc. in a different,
dynamically-determined database.
"beginthreadex" wrote:

> declare @.sql nvarchar(1000)
> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> exec sp_executesql @.sql
>
> --
> new
>|||LOL ... next it will be sp_cmdshell(osql ... ). :-)
"beginthreadex" wrote:

> here's a real hum-dinger: (this is all on one line)
> exec opendatasource('sqloledb', 'data
> source=YourServer;uid=UserId;pwd=Passwor
d').YourDatabase.dbo.sp_executesql
> N'create table mydatabase.dbo.newtable (myfield1 int)'
> You'll want to change the following areas:
> YourServer
> UserId
> Password
> YourDatabase
> ... and the statement of course
>
> --
> new
>|||The code I provided does execute the code in the other database. Hence, the
"mydatabase" reference. So, here's your code mixed with mine:
declare @.DBName varchar(20)
set @.DBName = 'dynamic'
declare @.SQL varchar(200)
set @.SQL = 'create function [' + @.DBName + '].dbo.foo ...'
exec sp_executesql @.sql
If there is something else that is confusing please let me know. Because I'm
referencing the database name this will run for the context of the other
database.
;)
[vbcol=seagreen]
> Thanks, but this isn't the issue. Issue is that from a stored procedure
> (or batch, for that matter) running in the context of database A, do:
> declare @.DBName varchar(20)
> set @.DBName = 'dynamic'
> declare @.SQL varchar(200)
> set @.SQL = 'use ' + @.DBName + '; create function foo ...'
> exec (@.SQL)
> Doesn't work because 'create function' must be at the beginning of a
> batch.
> set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
> design.
> Need to create functions, stored procs etc. in a different,
> dynamically-determined database.
>|||If you tried it (in s2k), you would realize that you cannot use 3 part
naming for creating procedures or functions. These statements are limited
to accepting an owner name (optional) and an object name.
Try the following statement:
create procedure pubs.dbo.junk as select getdate()|||I deeply apologize! The "Create Table" code does allow for this.
However this DOES work as I have just tested:
exec opendatasource('sqloledb', 'data
source=MySource;uid=MyUID;pwd=MyPWD').pubs.dbo.sp_execsql N'create
procedure dbo.junk as select getdate()'
I know it's not the prettiest, but it DOES work.

> If you tried it (in s2k), you would realize that you cannot use 3 part
> naming for creating procedures or functions. These statements are limited
> to accepting an owner name (optional) and an object name.
> Try the following statement:
> create procedure pubs.dbo.junk as select getdate()
new

CREATE via Dynamic SQL into new database?

From a stored procedure running in the context of one database, I would like
to create a set of objects (stored procedures, functions, views, users) into
a newly-created second database, where the name is dynamically determined.
Creating the new database and retrieving its name is no problem, the problem
is executing CREATE FUNCTION, CREATE PROCEDURE, etc. in the context of the
new database.
As you know, executing dynamic SQL 'use database' won't change the context
of an executing procedure. And 'use database; create function ...' doesn't
work, because the create statements need to be in their own batch. I cannot
store the objects in Master, so I can't have them automatically created with
the new database.
Is there a way to copy the objects from an existing (i.e. template) database
to the new one using dynamic SQL? Any way to attach a copy of a template
database file to a new database dynamically?
Or any out-of-the-box ideas?declare @.sql nvarchar(1000)
set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
exec sp_executesql @.sql
> From a stored procedure running in the context of one database, I would
> like to create a set of objects (stored procedures, functions, views,
> users) into a newly-created second database, where the name is dynamically
> determined. Creating the new database and retrieving its name is no
> problem, the problem is executing CREATE FUNCTION, CREATE PROCEDURE, etc.
> in the context of the new database.
> As you know, executing dynamic SQL 'use database' won't change the context
> of an executing procedure. And 'use database; create function ...' doesn't
> work, because the create statements need to be in their own batch. I
> cannot store the objects in Master, so I can't have them automatically
> created with the new database.
> Is there a way to copy the objects from an existing (i.e. template)
> database to the new one using dynamic SQL? Any way to attach a copy of a
> template database file to a new database dynamically?
> Or any out-of-the-box ideas?
--
new|||here's a real hum-dinger: (this is all on one line)
exec opendatasource('sqloledb', 'data
source=YourServer;uid=UserId;pwd=Password').YourDatabase.dbo.sp_executesql
N'create table mydatabase.dbo.newtable (myfield1 int)'
You'll want to change the following areas:
YourServer
UserId
Password
YourDatabase
... and the statement of course
> declare @.sql nvarchar(1000)
> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> exec sp_executesql @.sql
>
>> From a stored procedure running in the context of one database, I would
>> like to create a set of objects (stored procedures, functions, views,
>> users) into a newly-created second database, where the name is
>> dynamically determined. Creating the new database and retrieving its name
>> is no problem, the problem is executing CREATE FUNCTION, CREATE
>> PROCEDURE, etc. in the context of the new database.
>> As you know, executing dynamic SQL 'use database' won't change the
>> context of an executing procedure. And 'use database; create function
>> ...' doesn't work, because the create statements need to be in their own
>> batch. I cannot store the objects in Master, so I can't have them
>> automatically created with the new database.
>> Is there a way to copy the objects from an existing (i.e. template)
>> database to the new one using dynamic SQL? Any way to attach a copy of a
>> template database file to a new database dynamically?
>> Or any out-of-the-box ideas?
>
--
new|||Thanks, but this isn't the issue. Issue is that from a stored procedure (or
batch, for that matter) running in the context of database A, do:
declare @.DBName varchar(20)
set @.DBName = 'dynamic'
declare @.SQL varchar(200)
set @.SQL = 'use ' + @.DBName + '; create function foo ...'
exec (@.SQL)
Doesn't work because 'create function' must be at the beginning of a batch.
set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
design.
Need to create functions, stored procs etc. in a different,
dynamically-determined database.
"beginthreadex" wrote:
> declare @.sql nvarchar(1000)
> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> exec sp_executesql @.sql
>
> > From a stored procedure running in the context of one database, I would
> > like to create a set of objects (stored procedures, functions, views,
> > users) into a newly-created second database, where the name is dynamically
> > determined. Creating the new database and retrieving its name is no
> > problem, the problem is executing CREATE FUNCTION, CREATE PROCEDURE, etc.
> > in the context of the new database.
> >
> > As you know, executing dynamic SQL 'use database' won't change the context
> > of an executing procedure. And 'use database; create function ...' doesn't
> > work, because the create statements need to be in their own batch. I
> > cannot store the objects in Master, so I can't have them automatically
> > created with the new database.
> >
> > Is there a way to copy the objects from an existing (i.e. template)
> > database to the new one using dynamic SQL? Any way to attach a copy of a
> > template database file to a new database dynamically?
> >
> > Or any out-of-the-box ideas?
> --
> new
>|||LOL ... next it will be sp_cmdshell(osql ... ). :-)
"beginthreadex" wrote:
> here's a real hum-dinger: (this is all on one line)
> exec opendatasource('sqloledb', 'data
> source=YourServer;uid=UserId;pwd=Password').YourDatabase.dbo.sp_executesql
> N'create table mydatabase.dbo.newtable (myfield1 int)'
> You'll want to change the following areas:
> YourServer
> UserId
> Password
> YourDatabase
> ... and the statement of course
>
> > declare @.sql nvarchar(1000)
> > set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> > exec sp_executesql @.sql
> >
> >
> >> From a stored procedure running in the context of one database, I would
> >> like to create a set of objects (stored procedures, functions, views,
> >> users) into a newly-created second database, where the name is
> >> dynamically determined. Creating the new database and retrieving its name
> >> is no problem, the problem is executing CREATE FUNCTION, CREATE
> >> PROCEDURE, etc. in the context of the new database.
> >>
> >> As you know, executing dynamic SQL 'use database' won't change the
> >> context of an executing procedure. And 'use database; create function
> >> ...' doesn't work, because the create statements need to be in their own
> >> batch. I cannot store the objects in Master, so I can't have them
> >> automatically created with the new database.
> >>
> >> Is there a way to copy the objects from an existing (i.e. template)
> >> database to the new one using dynamic SQL? Any way to attach a copy of a
> >> template database file to a new database dynamically?
> >>
> >> Or any out-of-the-box ideas?
> >
> --
> new
>|||The code I provided does execute the code in the other database. Hence, the
"mydatabase" reference. So, here's your code mixed with mine:
declare @.DBName varchar(20)
set @.DBName = 'dynamic'
declare @.SQL varchar(200)
set @.SQL = 'create function [' + @.DBName + '].dbo.foo ...'
exec sp_executesql @.sql
If there is something else that is confusing please let me know. Because I'm
referencing the database name this will run for the context of the other
database.
;)
> Thanks, but this isn't the issue. Issue is that from a stored procedure
> (or batch, for that matter) running in the context of database A, do:
> declare @.DBName varchar(20)
> set @.DBName = 'dynamic'
> declare @.SQL varchar(200)
> set @.SQL = 'use ' + @.DBName + '; create function foo ...'
> exec (@.SQL)
> Doesn't work because 'create function' must be at the beginning of a
> batch.
> set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
> design.
> Need to create functions, stored procs etc. in a different,
> dynamically-determined database.
>
>> declare @.sql nvarchar(1000)
>> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
>> exec sp_executesql @.sql|||If you tried it (in s2k), you would realize that you cannot use 3 part
naming for creating procedures or functions. These statements are limited
to accepting an owner name (optional) and an object name.
Try the following statement:
create procedure pubs.dbo.junk as select getdate()|||Thanks for the response, I really do appreciate it. But CREATE no longer
accepts a DB name reference for functions/procedures - at least in SQL Server
2000.
"beginthreadex" wrote:
> The code I provided does execute the code in the other database. Hence, the
> "mydatabase" reference. So, here's your code mixed with mine:
> declare @.DBName varchar(20)
> set @.DBName = 'dynamic'
> declare @.SQL varchar(200)
> set @.SQL = 'create function [' + @.DBName + '].dbo.foo ...'
> exec sp_executesql @.sql
> If there is something else that is confusing please let me know. Because I'm
> referencing the database name this will run for the context of the other
> database.
> ;)
> > Thanks, but this isn't the issue. Issue is that from a stored procedure
> > (or batch, for that matter) running in the context of database A, do:
> >
> > declare @.DBName varchar(20)
> > set @.DBName = 'dynamic'
> > declare @.SQL varchar(200)
> > set @.SQL = 'use ' + @.DBName + '; create function foo ...'
> > exec (@.SQL)
> >
> > Doesn't work because 'create function' must be at the beginning of a
> > batch.
> >
> > set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
> > design.
> >
> > Need to create functions, stored procs etc. in a different,
> > dynamically-determined database.
> >
> >
> >> declare @.sql nvarchar(1000)
> >> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> >> exec sp_executesql @.sql
>|||I deeply apologize! The "Create Table" code does allow for this.
However this DOES work as I have just tested:
exec opendatasource('sqloledb', 'data
source=MySource;uid=MyUID;pwd=MyPWD').pubs.dbo.sp_execsql N'create
procedure dbo.junk as select getdate()'
I know it's not the prettiest, but it DOES work.
> If you tried it (in s2k), you would realize that you cannot use 3 part
> naming for creating procedures or functions. These statements are limited
> to accepting an owner name (optional) and an object name.
> Try the following statement:
> create procedure pubs.dbo.junk as select getdate()
--
new|||Hello,
I suggest that you refer to the following web site:
http://www.databasejournal.com/features/mssql/article.php/3441031
You may try to use sp_MSforeachdb. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.

CREATE via Dynamic SQL into new database?

From a stored procedure running in the context of one database, I would like
to create a set of objects (stored procedures, functions, views, users) into
a newly-created second database, where the name is dynamically determined.
Creating the new database and retrieving its name is no problem, the problem
is executing CREATE FUNCTION, CREATE PROCEDURE, etc. in the context of the
new database.
As you know, executing dynamic SQL 'use database' won't change the context
of an executing procedure. And 'use database; create function ...' doesn't
work, because the create statements need to be in their own batch. I cannot
store the objects in Master, so I can't have them automatically created with
the new database.
Is there a way to copy the objects from an existing (i.e. template) database
to the new one using dynamic SQL? Any way to attach a copy of a template
database file to a new database dynamically?
Or any out-of-the-box ideas?
declare @.sql nvarchar(1000)
set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
exec sp_executesql @.sql

> From a stored procedure running in the context of one database, I would
> like to create a set of objects (stored procedures, functions, views,
> users) into a newly-created second database, where the name is dynamically
> determined. Creating the new database and retrieving its name is no
> problem, the problem is executing CREATE FUNCTION, CREATE PROCEDURE, etc.
> in the context of the new database.
> As you know, executing dynamic SQL 'use database' won't change the context
> of an executing procedure. And 'use database; create function ...' doesn't
> work, because the create statements need to be in their own batch. I
> cannot store the objects in Master, so I can't have them automatically
> created with the new database.
> Is there a way to copy the objects from an existing (i.e. template)
> database to the new one using dynamic SQL? Any way to attach a copy of a
> template database file to a new database dynamically?
> Or any out-of-the-box ideas?
new
|||here's a real hum-dinger: (this is all on one line)
exec opendatasource('sqloledb', 'data
source=YourServer;uid=UserId;pwd=Password').YourDa tabase.dbo.sp_executesql
N'create table mydatabase.dbo.newtable (myfield1 int)'
You'll want to change the following areas:
YourServer
UserId
Password
YourDatabase
... and the statement of course

> declare @.sql nvarchar(1000)
> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> exec sp_executesql @.sql
>
>
new
|||Thanks, but this isn't the issue. Issue is that from a stored procedure (or
batch, for that matter) running in the context of database A, do:
declare @.DBName varchar(20)
set @.DBName = 'dynamic'
declare @.SQL varchar(200)
set @.SQL = 'use ' + @.DBName + '; create function foo ...'
exec (@.SQL)
Doesn't work because 'create function' must be at the beginning of a batch.
set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
design.
Need to create functions, stored procs etc. in a different,
dynamically-determined database.
"beginthreadex" wrote:

> declare @.sql nvarchar(1000)
> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> exec sp_executesql @.sql
>
> --
> new
>
|||LOL ... next it will be sp_cmdshell(osql ... ). :-)
"beginthreadex" wrote:

> here's a real hum-dinger: (this is all on one line)
> exec opendatasource('sqloledb', 'data
> source=YourServer;uid=UserId;pwd=Password').YourDa tabase.dbo.sp_executesql
> N'create table mydatabase.dbo.newtable (myfield1 int)'
> You'll want to change the following areas:
> YourServer
> UserId
> Password
> YourDatabase
> ... and the statement of course
>
> --
> new
>
|||The code I provided does execute the code in the other database. Hence, the
"mydatabase" reference. So, here's your code mixed with mine:
declare @.DBName varchar(20)
set @.DBName = 'dynamic'
declare @.SQL varchar(200)
set @.SQL = 'create function [' + @.DBName + '].dbo.foo ...'
exec sp_executesql @.sql
If there is something else that is confusing please let me know. Because I'm
referencing the database name this will run for the context of the other
database.
;)
[vbcol=seagreen]
> Thanks, but this isn't the issue. Issue is that from a stored procedure
> (or batch, for that matter) running in the context of database A, do:
> declare @.DBName varchar(20)
> set @.DBName = 'dynamic'
> declare @.SQL varchar(200)
> set @.SQL = 'use ' + @.DBName + '; create function foo ...'
> exec (@.SQL)
> Doesn't work because 'create function' must be at the beginning of a
> batch.
> set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
> design.
> Need to create functions, stored procs etc. in a different,
> dynamically-determined database.
>
|||If you tried it (in s2k), you would realize that you cannot use 3 part
naming for creating procedures or functions. These statements are limited
to accepting an owner name (optional) and an object name.
Try the following statement:
create procedure pubs.dbo.junk as select getdate()
|||I deeply apologize! The "Create Table" code does allow for this.
However this DOES work as I have just tested:
exec opendatasource('sqloledb', 'data
source=MySource;uid=MyUID;pwd=MyPWD').pubs.dbo.sp_ execsql N'create
procedure dbo.junk as select getdate()'
I know it's not the prettiest, but it DOES work.

> If you tried it (in s2k), you would realize that you cannot use 3 part
> naming for creating procedures or functions. These statements are limited
> to accepting an owner name (optional) and an object name.
> Try the following statement:
> create procedure pubs.dbo.junk as select getdate()
new