How output all Stored Procedures to a table or file

I wish to ultimately have the content of all stored procedures related to a database in a single ASCII file for review. Is that doable? If so, how?
Thanks,
PeterRight-click your database in Enterprise Manager, select All tasks, Generate Sql script, there you can choose All stored procedures and other objects/options. mojza|||Fantastic Mojza - thanks for the help!

Peter|||Along this same line, this is what we do in order to generate script files that we then place into SourceSafe for version control. I am just learning that end of the department's process, but it seems to me I see stored procs at the system level for version control stuff, but need to look farther into that to see what other stuff we might use.

The script-to-VSS solution seems cheesy to me, but without taking this to be a "How do I do it" question, I'd just like to ask, is there an easier way? I can go look in BOL, but just saw the thread and was reminded of our local cheese factory, so thought I'd ask for quick-n-dirty impressions.|||Use VBA with SQL-DMO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_2yi7.asp), possibly within DTS (http://msdn.microsoft.com/library/en-us/dtssql/dts_basic_5zg3.asp)?

-PatP

How often and when should the sp_recompile procedure be used

We are at our slowest this time of year... Having never set the sp_recompile
directive for our procedures, would now be a good time to tell the server to
recompile all the stored procedures? I believe we have added perhaps 30 or
40 indexes since October.
Is this an appropriate method to activate the recompile?
DECLARE @.procname varchar(150)
DECLARE proc_recompile cursor for select name from sysobjects where xtype='P'
OPEN proc_recompile
FETCH NEXT FROM proc_recompile INTO @.procname
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_recompile @.procname
FETCH NEXT FROM proc_recompile INTO @.procname
END
CLOSE proc_recompile
DEALLOCATE proc_recompile
--
Regards,
Jamie
Is the system slow? Blocking problems? Did you upgrade to sql 2005?Do a
database restore?
If not......
SQL recompiles stored procedures for you so....
Just update youre statistics an you wil be fine
Just my 2 cents
Greetz.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"thejamie" wrote:

> We are at our slowest this time of year... Having never set the sp_recompile
> directive for our procedures, would now be a good time to tell the server to
> recompile all the stored procedures? I believe we have added perhaps 30 or
> 40 indexes since October.
> Is this an appropriate method to activate the recompile?
> --
> DECLARE @.procname varchar(150)
> DECLARE proc_recompile cursor for select name from sysobjects where xtype='P'
> OPEN proc_recompile
> FETCH NEXT FROM proc_recompile INTO @.procname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC sp_recompile @.procname
> FETCH NEXT FROM proc_recompile INTO @.procname
> END
> CLOSE proc_recompile
> DEALLOCATE proc_recompile
> --
> --
> Regards,
> Jamie
|||I made the mistake of reading the book. It is an optimization technique.
The impression I get from the book is that this should be done fairly
regularly.
The statistics are updated each week after the indexes are rebuilt. I've
added some new indexes and thought perhaps the query plan may find a better
index for a given stored procedure, use it, and thus, get better results.
To find indexes, I ran the profiler and analyzed the results. The profiler
gave me good suggestions for indexes to add.
Slow time of the year is what I mean - a chance to tweak things without the
system toppling over.
Regards,
Jamie
"Hate_orphaned_users" wrote:
[vbcol=seagreen]
> Is the system slow? Blocking problems? Did you upgrade to sql 2005?Do a
> database restore?
> If not......
> SQL recompiles stored procedures for you so....
> Just update youre statistics an you wil be fine
> Just my 2 cents
> Greetz.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
>
>
> "thejamie" wrote:
|||If you or youre company developed the database\application i would say be my
guest and optimize\tune the database.
If not, be carefull adding indexes based on traces and adding 2 much indexes.
Greetz,
Flying Dutch men.
|||Hi Jamie

> Slow time of the year is what I mean - a chance to tweak things without the
> system toppling over.
> --
Be careful that your changes are not because of un-representative activity
as this could cause more problems rather than solve any. You should really
test any changes using representative workloads before implementing them.
John

How often and when should the sp_recompile procedure be used

We are at our slowest this time of year... Having never set the sp_recompil
e
directive for our procedures, would now be a good time to tell the server to
recompile all the stored procedures? I believe we have added perhaps 30 or
40 indexes since October.
Is this an appropriate method to activate the recompile?
--
DECLARE @.procname varchar(150)
DECLARE proc_recompile cursor for select name from sysobjects where xtype='P
'
OPEN proc_recompile
FETCH NEXT FROM proc_recompile INTO @.procname
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_recompile @.procname
FETCH NEXT FROM proc_recompile INTO @.procname
END
CLOSE proc_recompile
DEALLOCATE proc_recompile
--
--
Regards,
JamieIs the system slow? Blocking problems? Did you upgrade to sql 2005?Do a
database restore?
If not......
SQL recompiles stored procedures for you so....
Just update youre statistics an you wil be fine
Just my 2 cents
Greetz.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"thejamie" wrote:

> We are at our slowest this time of year... Having never set the sp_recomp
ile
> directive for our procedures, would now be a good time to tell the server
to
> recompile all the stored procedures? I believe we have added perhaps 30 o
r
> 40 indexes since October.
> Is this an appropriate method to activate the recompile?
> --
> DECLARE @.procname varchar(150)
> DECLARE proc_recompile cursor for select name from sysobjects where xtype=
'P'
> OPEN proc_recompile
> FETCH NEXT FROM proc_recompile INTO @.procname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC sp_recompile @.procname
> FETCH NEXT FROM proc_recompile INTO @.procname
> END
> CLOSE proc_recompile
> DEALLOCATE proc_recompile
> --
> --
> Regards,
> Jamie|||I made the mistake of reading the book. It is an optimization technique.
The impression I get from the book is that this should be done fairly
regularly.
The statistics are updated each week after the indexes are rebuilt. I've
added some new indexes and thought perhaps the query plan may find a better
index for a given stored procedure, use it, and thus, get better results.
To find indexes, I ran the profiler and analyzed the results. The profiler
gave me good suggestions for indexes to add.
Slow time of the year is what I mean - a chance to tweak things without the
system toppling over.
--
Regards,
Jamie
"Hate_orphaned_users" wrote:
[vbcol=seagreen]
> Is the system slow? Blocking problems? Did you upgrade to sql 2005?Do a
> database restore?
> If not......
> SQL recompiles stored procedures for you so....
> Just update youre statistics an you wil be fine
> Just my 2 cents
> Greetz.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
>
>
> "thejamie" wrote:
>|||If you or youre company developed the database\application i would say be my
guest and optimize\tune the database.
If not, be carefull adding indexes based on traces and adding 2 much indexes
.
Greetz,
Flying Dutch men.|||Hi Jamie

> Slow time of the year is what I mean - a chance to tweak things without th
e
> system toppling over.
> --
Be careful that your changes are not because of un-representative activity
as this could cause more problems rather than solve any. You should really
test any changes using representative workloads before implementing them.
Johnsql

How much performance gained by qualifying tables with dbo. in the database?

Hello Folks:
The great majority of our queries and stored procedures do not
reference the tables with the dbo.table_name format. Due to some
production issues, we're not facing a situation that is forcing us to
redo our database by scripting it out, making a few modificationsm, and
importing the data right back into it. (please don't aske me why!)
I believe now is a perfect opportunity to owner-qualify all references
to the tables with dbo.table_name in the database scripts, and I
strongly believe the performance gain will be significant.
I need to sell this idea to my boss who thinks the risks of
manupulating the production database scripts is far graver than its
benefits, and he wants to now "how much" performance is gained by doing
this.
What do you folks think is a good answer to him? How can I show some
tangible benefits in terms of "gained performance"?
Thanks for you help!Hi Dan
Using the dbo prefix should help cache hits, and the performance gain will
depend on how busy your server is, it could be very little, but then I would
implement it regardless as it is "using best practices". Check out the
current cache misses using SQL profiler. You would also need to add prefixes
for stored procedures, views etc... At the same time you may want to check
the case being consistent.
If you don't use source code control then maybe this is the time start using
it!!
You can gain confidence in the outcome by comparing the two databases.
You should also do a full regression test, and it sounds like your superior
does not have confidence in the outcome of this.
Make sure that you cover all eventualities in the implementation plan and
have backout strategies at each stage. Do some trail runs (on copies of live
data) to get timings and proof that it works.
John
"Dan Gorrison" wrote:
> Hello Folks:
> The great majority of our queries and stored procedures do not
> reference the tables with the dbo.table_name format. Due to some
> production issues, we're not facing a situation that is forcing us to
> redo our database by scripting it out, making a few modificationsm, and
> importing the data right back into it. (please don't aske me why!)
> I believe now is a perfect opportunity to owner-qualify all references
> to the tables with dbo.table_name in the database scripts, and I
> strongly believe the performance gain will be significant.
>
> I need to sell this idea to my boss who thinks the risks of
> manupulating the production database scripts is far graver than its
> benefits, and he wants to now "how much" performance is gained by doing
> this.
>
> What do you folks think is a good answer to him? How can I show some
> tangible benefits in terms of "gained performance"?
>
> Thanks for you help!
>

How MERGE few databases to big one

Hi All,
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
Regards
Actually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>
|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp
|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
..ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>

How MERGE few databases to big one

Hi All,
I have to merge few different databases to one big common database :)
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
--
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database :)
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
--
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>sql

How MERGE few databases to big one

Hi All,
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables i
n
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign key
s
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring ove
r
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups> )... that is, physically move the fil
es
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>

how i can skip error messeges within Procedures

how i can skip error messeges within Procedures
such as can't insert to duplicate keys
thanks
samEither *handle* errors or *avoid* them, but don't *skip* them.
It sounds like you should avoid inserting duplicate keys in the first
place. Example:
INSERT INTO YourTable (c1, c2, ...)
SELECT @.c1, @.c2, ...
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE c1 = @.c1) ;
Error handling:
http://www.sommarskog.se/error-handling-I.html
David Portas
SQL Server MVP
--|||Your insert failed, why would you want to ignore that?
How about preventing the error in the first place?
http://www.aspfaq.com/2509
"Sam" <focus10@.zahav.net.il> wrote in message
news:e6uyl5QmFHA.3648@.TK2MSFTNGP10.phx.gbl...
> how i can skip error messeges within Procedures
> such as can't insert to duplicate keys
> thanks
> sam
>
>|||Can't be done <yet>. All messages in SQL Server get sent to the client.
You cannot stop them....<yet>
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Sam" <focus10@.zahav.net.il> wrote in message
news:e6uyl5QmFHA.3648@.TK2MSFTNGP10.phx.gbl...
> how i can skip error messeges within Procedures
> such as can't insert to duplicate keys
> thanks
> sam
>
>