Create and manage Store Procedures from inside VS

With a local conn to SQL2000 I can edit sprocs no problem, but if I change the connection to a remote computer (to which I have all permissions) I cannot create or manage stored procedures from within VS IDE? Is there a work around?

I noticed that a procedure written for the localhost accepts CREATE PROCEDURE and changes it to ALTER PROCEDURE. When I script the sprocs from my development machine to the server, those scripts with ALTER in them do not work. I change them to CREATE and they work fine.

I haven't found anything yet on MSDN about this, but will continue to look.

Thanks in advance,

_EHi _E

You may have better luck with this question in the SQL Server Tools - General forum.
Allan|||Thanks, I'll try there. Didn't know if it was an IDE or SQL thing.

_E

Create and Administer SQL Server Agent Jobs via Sprocs?

Hi,

Does anyone know if it is possible to set up stored procedures which will create, modify, and disable SQL Server Agent Jobs?

I have a web based application which I need to enable an administrator to change the frequency which a job runs and encapsulating the modifications in a stored procedure would be the easiest way for me to integrate this, if it's possbible.

Regards, MattHi,
You may use sp_update_jobschedule, sp_update_job etc or if you create your own sp and use tables such as sysjobschedules, sysjobs etc.
Tables and procs are located in MSDB.
Ex for updating scheduled time:
UPDATE msdb.dbo.sysjobschedules
SET active_start_time = 164000
WHERE (job_id = '8A0F1080-D22A-4F82-AE13-68F789989D1D')
AND (name = 'Once')

Try this and let us know how it work

Regards|||Thanks Tommy,

Going to give this a shot this afternoon. I'll let you know how it turns out.

Counting Stored Procedures

I ran out of fingers...
How can I count the number of stored procedures for which the name starts
with "XYZ_"
Thanks!SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_name LIKE 'XYZ[_]%'
AND routine_type = 'PROCEDURE'
David Portas
SQL Server MVP
--|||select count(*) from sysobjects where name like 'xyz\_%' escape '' and
objectproperty(id, 'IsProcedure') = 1
Mike
"Guadala Harry" <GMan@.NoSpam.net> wrote in message
news:eWV2yVBTFHA.3144@.TK2MSFTNGP09.phx.gbl...
> I ran out of fingers...
> How can I count the number of stored procedures for which the name starts
> with "XYZ_"
> Thanks!
>|||Use <dbname>
Go
Select Count(*)
From dbo.sysobjects as S
Where ObjectProperty(S.id, 'IsProcedure') = 1
And ObjectProperty(S.id, 'IsMSShipped') = 0
And Name Like 'XYZ%'
Granted, there aren't any Microsoft shipped stored procs that begin with 'XY
Z',
so you could skip that part of the criteria.
Thomas
"Guadala Harry" <GMan@.NoSpam.net> wrote in message
news:eWV2yVBTFHA.3144@.TK2MSFTNGP09.phx.gbl...
>I ran out of fingers...
> How can I count the number of stored procedures for which the name starts
with
> "XYZ_"
> Thanks!
>|||Can my response. David's response is more portable, using the
INFORMATION_SCHEMA views. I need to remember I'm supposed to use those...
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O8kkPZBTFHA.544@.TK2MSFTNGP15.phx.gbl...
> select count(*) from sysobjects where name like 'xyz\_%' escape '' and
> objectproperty(id, 'IsProcedure') = 1
> Mike
> "Guadala Harry" <GMan@.NoSpam.net> wrote in message
> news:eWV2yVBTFHA.3144@.TK2MSFTNGP09.phx.gbl...
starts
>|||Select * From sysObjects
Where type = 'P'
And name Line 'XYZ_%'
"Guadala Harry" wrote:

> I ran out of fingers...
> How can I count the number of stored procedures for which the name starts
> with "XYZ_"
> Thanks!
>
>|||ditto Mike's comment
"CBretana" wrote:
> Select * From sysObjects
> Where type = 'P'
> And name Line 'XYZ_%'
>
> "Guadala Harry" wrote:
>|||It's because Microsoft doesn't support queries on system tables. The table
structure may be changed in future releases.
For a one time deal though, no problem, as long as you don't include it in
production code.
Ditto CBretana
Just kidding. I know I don't have to remind you. Let's just chalk it up to a
long, hard day... or w. :-)
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:ODF4HcBTFHA.548@.tk2msftngp13.phx.gbl...
> Can my response. David's response is more portable, using the
> INFORMATION_SCHEMA views. I need to remember I'm supposed to use those...
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:O8kkPZBTFHA.544@.TK2MSFTNGP15.phx.gbl...
> starts
>

Counting Records in a Stored Procedure

I am trying to count records in my stored procedure. Can someone please help me.

these are the two procedures I am using

Alter Procedure usp_rptQualityReport As

SELECT
tblRMAData.RMANumber,
tblRMAData.JobName,
tblRMAData.Date,
tblFailureReasons.LintItemID,
tblLineItems.Qty,
tblLineItems.Model,
tblLineItems.ReportDate,
tblFailureReasons.FailureReason,
tblTestComponentFailures.ComponentID,
tblTestComponentFailures.FailureCause
FROM
tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber

WHERE
(((tblFailureReasons.FailureReason) <> N'NONE'))

ORDER BY
tblFailureReasons.FailureReason

Alter Procedure usp_rptQualityReport2 As

exec usp_rtpQualityReport

SELECT
usp_rptQualityReport.RMANumber,
usp_rptQualityReport.JobName,
usp_rptQualityReport.Date,
usp_rptQualityReport.LintItemID,
usp_rptQualityReport.Qty,
usp_rptQualityReport.Model,
usp_rptQualityReport.ReportDate,
usp_rptQualityReport.FailureReason,
usp_rptQualityReport.ComponentID,
usp_rptQualityReport.FailureCause,

(SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason ) AS groupingLevel


FROM usp_rptQualityReport;What abotu this idea?

create procedure test
as
select * from sysobjects
go
create procedure test2
as
exec test
select @.@.rowcount
go
test2|||Are you counting number of lines in your stored procedure or number of records your stored procedure returns?


For number of lines check syscomments, for number of rows either use Profiler of alter your procedure, pass an OUTPUT parameter, and store the value of @.@.ROWCOUNT variable into it immediately after your query and before RETURN (yes, put a RETURN (0) statement at the end of your procedure)|||I am still trying to make this work and made some changes and created a view in SQL. But I still can't get them to count and then rank the records.

I am still having a problem counting the number of each type of Failure Reason.

Here is the Stored Procedure I am using:

Alter Procedure usp_rptQualityReport3 As

SELECT * FROM viewQualityReport

(SELECT COUNT(FailureReason)) AS groupingLevel

WHERE
(((viewQualityReport.FailureReason) <> N'NONE'))

ORDER BY
groupinglevel desc