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.

Crearting a new table via SPROCS

I supply a parameter @.TEMPTABLE for the table name. When I execute my SPROC with the parameter, execution goes thru by I always end up getting "@.TEMPTABLE" as the name of the table instead of the value of the parameter.

Any ideas would be appreciated.

Thank you.

CREATE PROCEDURE sp_CREATE_TEMP_TABLE
(@.TEMPTABLE varchar(30))
AS
CREATE TABLE @.TEMPTABLE (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY]Hi,

your tablename is "@.temptable" ... the server will not use the var, instead it will name the table...

you need to build an cmd-string and execute it. something like this:

declare @.S nvarchar(1000)
select @.S=
'CREATE TABLE'+@.TEMPTABLE+' (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY] '
exec sp_executesql @.S

markus