Script to store stored procedures in seperate files

How does MicroSoft store the stored procedures in seperate files.
What tools are used.
Cause that's what I like to do too.

Arno de Jong,

The Netherlands.
(SCPTFXR does not have the option to store the stored procedures in
different files
I think)Enterprise Manager will do this for you: Right-click on the database, click
"Generate SQL script", select the option "Create one file per object".

You can do this programmatically using the Script method of the DMO
StoredProcedure object. Call .Script once for each SP with an appropriate
file name.

http://msdn.microsoft.com/library/e...ef_m_s_0n1g.asp

--
David Portas
----
Please reply only to the newsgroup
--

"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bpshql$oie$1@.reader11.wxs.nl...
> How does MicroSoft store the stored procedures in seperate files.
> What tools are used.
> Cause that's what I like to do too.
> Arno de Jong,
> The Netherlands.
> (SCPTFXR does not have the option to store the stored procedures in
> different files
> I think)|||SQL Server stores the text of stored procedures/views/triggers in a
table called syscomments.

you could use SP_HELPTEXT spname to retrieve text for any stored
procedure
you could study the SP_HELPTEXT procedure and it will show you exactly
how microsoft retrieves the TEXT:) ENJOY

Peace:)

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<g8GdnSrn59RfdVyi4p2dnA@.giganews.com>...
> Enterprise Manager will do this for you: Right-click on the database, click
> "Generate SQL script", select the option "Create one file per object".
> You can do this programmatically using the Script method of the DMO
> StoredProcedure object. Call .Script once for each SP with an appropriate
> file name.
> http://msdn.microsoft.com/library/e...ef_m_s_0n1g.asp
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --
> "A.M. de Jong" <arnojo@.wxs.nl> wrote in message
> news:bpshql$oie$1@.reader11.wxs.nl...
> > How does MicroSoft store the stored procedures in seperate files.
> > What tools are used.
> > Cause that's what I like to do too.
> > Arno de Jong,
> > The Netherlands.
> > (SCPTFXR does not have the option to store the stored procedures in
> > different files
> > I think)

Script to cause high CPU

Does anyone have a script that could be run against say AdventureWorks or
Northwind that would peg the CPU to 100% on the servers ?
I want to use it for stress testing. The environment is SQL 2005 and we have
multiple procs.
Thanks
What would be the point of that? What exactly would you be testing for, the
fact you can run at 100% CPU? That would tell you virtually nothing in
regards to your real world system. Here are some tools that can maybe get
you started:
http://support.microsoft.com/default.aspx/kb/231619
http://www.microsoft.com/downloads/details.aspx?FamilyId=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en
http://www.sql-server-performance.com/articles/per/stress_test_part3_p1.aspx
http://sqljunkies.com/WebLog/amachanic/archive/2005/02/09/7597.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"John Doe" <Johndoe@.jd.com> wrote in message
news:eJiq2A9ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Does anyone have a script that could be run against say AdventureWorks or
> Northwind that would peg the CPU to 100% on the servers ?
> I want to use it for stress testing. The environment is SQL 2005 and we
> have multiple procs.
> Thanks

Script to cause high CPU

Does anyone have a script that could be run against say AdventureWorks or
Northwind that would peg the CPU to 100% on the servers ?
I want to use it for stress testing. The environment is SQL 2005 and we have
multiple procs.
ThanksWhat would be the point of that? What exactly would you be testing for, the
fact you can run at 100% CPU? That would tell you virtually nothing in
regards to your real world system. Here are some tools that can maybe get
you started:
http://support.microsoft.com/default.aspx/kb/231619
http://www.microsoft.com/downloads/details.aspx?FamilyId=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en
http://www.sql-server-performance.com/articles/per/stress_test_part3_p1.aspx
http://sqljunkies.com/WebLog/amachanic/archive/2005/02/09/7597.aspx
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"John Doe" <Johndoe@.jd.com> wrote in message
news:eJiq2A9ZIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Does anyone have a script that could be run against say AdventureWorks or
> Northwind that would peg the CPU to 100% on the servers ?
> I want to use it for stress testing. The environment is SQL 2005 and we
> have multiple procs.
> Thanks

SCOPE_IDENTITY() returning the ID value of an inserted record.

There are loads of postings on the net about this problem but none I have found explain the cause.

Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method.

Has anyone come across the solution for this issue?

Thanks

Can you provide your code?|||

Here is the Stored Proc

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[InsertNewEnquiry_Client]

(

@.Salutation

varchar(10),

@.Name

varchar(60),

@.Address1

varchar(60),

@.Address2

varchar(60),

@.Address3

varchar(60),

@.Town

nvarchar(50),

@.PostCode

char(10),

@.County

char(60),

@.Telephone1

char(12),

@.Telephone2

char(12),

@.email

char(30)

)

AS

SETNOCOUNTOFF;

INSERT

INTO tblClient(Salutation,Name, Address1, Address2, Address3, Town, PostCode, County, Telephone1, Telephone2, email)

VALUES

(@.Salutation,@.Name,@.Address1,@.Address2,@.Address3,@.Town,@.PostCode,@.County,@.Telephone1,@.Telephone2,@.email);

SELECT

ClientID, Salutation,Name, Address1, Address2, Address3, PostCode, County, Telephone1, Telephone2, Telephone3, emailFROM tblClientWHERE(ClientID=SCOPE_IDENTITY())|||And can you show us the code which calls this stored procedure?|||int ClientID = Convert.ToInt32(clientTableAdapter.InsertStoredProc(cboClientSalutation.SelectedItem, txtClientName,text ......) );|||

The 1 your getting is most likely the default result of an Insert which is to return the number of records that have been entered.

I think you need to set the ExecuteMode property on your function to scalar.

|||

Actually, I've just looked at your sql again and your selecting an entire record at the end, not the identity on its own.

Simply put SELECT SCOPE_IDENTITY() if you want just the id returned, plus make sure you set the executemode on the function to scalar as I said before.

Hope that helps.