Saving Procedures and Views to remote server

I am using SQL Server Management Studio and Visual Studio 2005 and I am a newbie.

Once connected to a remote server and database I am able to use SQL Server Management Studio to modify a table and save it. When I modify a stored procedure or create a new one and attempt to save it I get my local dialog box to save to my system with a new name “SQLQuery6.sql.” How do I cause the procedure to be saved to the remote database? If anyone can help this or where I can find it I will appreciate it. Thanks in advance.

When you 'SAVE' the contents of a Query window, you are making a file copy of the contents -so you are asked where to save that file.

If you want to 'install' your stored procedure or VIEW on a SQL Server, be sure the query window is connected to the Server you want, and then just EXECUTE the [CREATE PROCEDURE...] code.

|||

Thank you

Does this also apply to modifying a stored procedure?

|||

Yes, it applies to modifying a stored procedure as well. Just execute your "alter procedure ..." statement on the SQL Server you want to update.

Jarret

Save The Newbie! SQL help

Hey guys. I have been given the task of converting from oracle to microsoft SQL syntax.

I have this oracle code:
CREATE OR REPLACE PROCEDURE BA_DROP_TABLE (p_TableName IN varchar2) IS
i int;
BEGIN
select count(*) into i from user_tables where table_name = p_TableName;
IF i=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE ' || p_TableName || ' CASCADE CONSTRAINTS';
END IF;
END;
/

I tried to convert it, and this is what I came up with:
if (object_id ('BA_DROP_TABLE') is not null)
drop proc BA_DROP_TABLE
go

CREATE PROCEDURE BA_DROP_TABLE @.p_TableName varchar(30)
AS
DECLARE @.i integer
BEGIN
select @.i = count(*) from user_tables where table_name = p_TableName
IF @.i=1
DROP TABLE p_TableName
END

This doesn't work. I think the problem has something to do with not being able to drop the table because its a parameter. I've heard talk of dynamic sql.

Someone PLEASE help a guy out!

Thanksreplace "DROP TABLE p_TableName" with "exec ('drop table ' + @.p_TableName)

Make sure the user has DROP OBJECTS permission.|||Now I get this error:

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'BA_DROP_TABLE'. The stored procedure will still be created.