CREATE, ALTER, Why Wont It Save?
May 11, 2007
when i create a new stored proc, i say "CREATE PROCEDURE [sprocName]"
but the next time i edit the stored proc, it hasnt changed to "ALTER ..."
I manually change it to ALTER, and run it, and close it, and it reverts back to "CREATE..."
Is there some sort of privelage i have to enable? Has anyone encountered this before?
Help guys, please :-S
Also, if you know of a better place where i can post this thread, please let me know
View 3 Replies
ADVERTISEMENT
May 30, 2007
Can anyone tell me what all steps in creating DTS package , how it saves,
how it re executes... what all steps ... and how it makes??
Thanks
Aravind
View 1 Replies
View Related
May 16, 2007
Hey,
I have an SSIS package that creates a new file and saves results into that file. However, the requirement has it that I need to create this file on a server residing in a different domain. How can I achieve this in an SSIS package when it is scheduled as a job?
Thanks,
Aravind
View 3 Replies
View Related
Nov 13, 2007
I want to use Create a procedure if not exisit and Alter it if exisit.
Can I use Create and Alter keyword together while writing a procedure in SQL Server 2005? If YES than HOW?
View 2 Replies
View Related
Jun 19, 2008
Hi,
Recently, I started to move more databases from SQL Server 2000 to 2005, well the Stored Procedures in 2005 is hard to handle.
First of all, I created the new SP in Object Explorer, the "New Stored Procedure" query just created a new name under the Stored Procedures folder, then I have to re-open it to put in TSQL code.
After I put in the code in the new SP, if I click "Save", the File
Manager comes out to let me to save in a physical location (C: or D: or Network place...). But after I close and open the new SP, the code I put in there is not really in the new SP.
I know that I have to "Execute" the SP in order to save the change. But the problem is I don't want to run the code at this step, the SP is to get a lot of data transactions, I'll setup a "Job" to do this.
So, the question is: Is this the way to create and save a new SP? Is there any other way to save the code changes in the SP WITHOUT "Execute" it?
Please give me any advise, article or links to read.
Thanks.
View 6 Replies
View Related
Apr 19, 2006
I am new to writing SQL code and I read that you can use ALTER statements to create an index for a table. How would I go about doing that? Everything that I have tried in Query Analyzer comes up with an error.
Any help is appreciated.
View 3 Replies
View Related
Nov 8, 2006
Thought I should post in the newbie forum for a while, instead. :-)
I have a couple of scripts that I've generated that drop a couple of system stored procedures and recreate them. I'm not sure why I did it in the first place, but I think it was that it wouldn't let me run an ALTER statement on them. Specifically, I'm now looking at sp_add_operator. I changed it to a 500 character email field instead of whatever it was (100, I think.)
/* Explanation: Why did I do that? SQL Mail is prohibited here, so I'm using CDO_Sysmail to email myself and the developers if a job fails. The list of people to email is determined by the owner of the database, who is also an operator in SQL. I get the list of emails from the email field of the operator properties. Hence, I need a bigger email field. Yes, I now know it would most likely be better to create an ADMIN database on each server for this kind of stuff. (Thanks to Tara for that blogged suggestion.) */
While I will probably go back to the default stored procedure, this got me to thinking: when would it be better to use an ALTER statement on a SProc rather than to do a DROP and CREATE?
Your thoughts, oh SQL gurus?
View 12 Replies
View Related
Jun 28, 2007
Hi,
I want to execute some queries inside a stored procedure, get the data and create from them some strings with formatted parts (bold, italic, underline, different color fonts) and save them in a text field. These strings are going to be displayed in some DBRichText control in a database application built with Delphi. Is there an easy way to create these complex rtf formats in MSSQL
or some ideas regarding this problem?
Best Regards,
Manolis Perrakis
View 1 Replies
View Related
May 17, 2007
Anyone have any compelling arguements for using one over the other?
Thanks
Mercy
View 7 Replies
View Related
Jul 18, 2006
Here is the alter statement that I am trying to use to create a relationship between 2 tables. This does not seem to work on mobile. What am I doing wrong?
ALTER TABLE [SubCategory] CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID])
REFERENCES [Category] ([CategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE
View 3 Replies
View Related
Feb 5, 2015
Without giving dbowner how can we grant permission to user to alter/create procs?
View 3 Replies
View Related
May 9, 2010
Is it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?
I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.
My code:
declare @fileGroupName VARCHAR(50) = (select top 1 name from
sys.filegroups where is_default = 1)
ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName
Is failing:
Incorrect syntax near '@fileGroupName'.
Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?
Q: If using a variable is not possible, is there another way to specify the default filegroup?
View 2 Replies
View Related
May 12, 2015
Is there a way i can grant a windows authenticated user say abcadam privilege:
-> to create, execute and alter the stored procedures that will be created by him only:
Point is we do not want the user to execute alter or modify other stored procedures out there for that DB
View 13 Replies
View Related
Feb 11, 2006
Hello:I didn't find any documentation that notes save point names are casesensitive, but I guess they are...Stored Proc to reproduce:/* START CODE SNIPPET */If Exists (Select * From sysobjects Where Type = 'P' and Name ='TestSaveTran')Drop Procedure dbo.TestSaveTranGoCreate Procedure dbo.TestSaveTranAsBeginDeclare@tranCount int--Transaction HandlingSelect @tranCount = @@TRANCOUNTIf (@tranCount=0)Begin Tran localtranElseSave Tran localtranBegin Try--Simulate Error While ProcessingRAISERROR('Something bad happened', 16, 1)/*If this proc started transaction then commit it,otherwise return and let caller handle transaction*/IF (@tranCount=0)Commit Tran localtranEnd TryBegin Catch--Rollback to save pointRollback Tran LOCALTRAN --<< NOTE case change--Log Error--Reraise ErrorEnd CatchEndGo--Execute Stored ProcExec dbo.TestSaveTran/*Should receive the following message:Cannot roll back LOCALTRAN. No transaction or savepoint of that namewas found.*//* END CODE SNIPPET */What is really strange, if there is a transaction open, then no erroris thrown. So if you execute as so:/* START CODE SNIPPET */Begin Tran--Execute Stored ProcExec dbo.TestSaveTran/* END CODE SNIPPET */There is no "Cannot roll back LOCALTRAN...." message.Questions:1-)Can someone confirm save point names are case sensitve and this isnot happening because of a server setting?2-)Is this a logic error that I am not seeing in the example codeabove?We have changed our code to store the save point name in a variable,which will hopefully mitigate this "problem".Thx.
View 4 Replies
View Related
Apr 1, 2007
Keep in mind this is my first compiled SQL program Stored Procedure(SP), copied from a book by Frasier Visual C++.NET in Visual Studio2005 (Chap12). So far, so theory, except for one bug (feature?)below. At some point I'm sure I'll be able to laugh about this, akinto forgeting a semi-colon in C/C++, but right now it's frustrating(time to sleep on it for a while).Problem--For some reason I get the error when trying to save files where twotables (called Author and Content), linked by a single key, form arelationship.By simple comparison of the source code in the textbook and my program(below) I found the difference: instead of, like in the textbook, theStored Procedure (SP) starting with "CREATE PROCEDURE", it*automatically* is (was somehow) given the name of 'ALTER PROCEDURE'and I cannot change this to "CREATE PROCEDURE" (you get an error in MSVisual Studio 2005 Pro edition of "There is already an object namedXXX in the database", see *|* below). No matter what I do, the SP isalways changed by Visual Studio 2005 to 'ALTER PROCEDURE'!!!(otherwise it simply will not save)Anybody else have this happen? (See below, others have had this happenover the years but it's not clear what the workaround is)Keep in mind this is my first attempt and I have ordered somespecialized books on SQL, but if this is a common problem (and Isuspect it's some sort of bug or quirk in VS2005), please let me know.Frankly I think SQL as done by VS2005 is messed up.Here are two Usenet threads on this problem:(1) http://tinyurl.com/2o956m or,http://groups.google.com/group/micr...1454182ae77d409(2) http://tinyurl.com/2ovybv or,http://groups.google.com/group/micr...9e5428bf0525889The second thread implies this is a bug--any fix?Also this bug might be relate to the fact I've switched (and notrebooted) from Administrator to PowerUser after successfully changingthe permissions in the SQL Server Management Studio Express (see thisthread: http://tinyurl.com/2o5yqa )Regarding this problem I might try again tommorrow to see if rebootinghelps.BTW, in the event I can't get this to work, what other SQL editor/compiler should I use besides MS Visual Studio 2005 for ADO.NET andSQL dB development?RL// source files// error message:'Authors' table saved successfully'Content' table- Unable to create relationship 'FK_Content_Authors'.The ALTER TABLE statement conflicted with the FOREIGN KEY constraint"FK_Content_Authors". The conflict occurred in database "DCV_DB",table "dbo.Authors", column 'AuthorID'.// due to the below no doubt!--CREATE PROCEDURE dbo.InsertAuthor /* THIS IS CORRECT (what I want)'CREATE PROCEDURE' not 'ALTER PROCEDURE'*/(@LastName NVARCHAR(32) = NULL,@FirstName NVARCHAR(32) = NULL)AS/* SET NOCOUNT ON */INSERT INTO Authors (LastName, FirstName)VALUES(@LastName, @FirstName)RETURN--ALTER PROCEDURE dbo.InsertAuthor /* WRONG! I want 'CREATE PROCEDURE'not 'ALTER PROCEDURE' but VS2005 won't save it as such!!!*/(@LastName NVARCHAR(32) = NULL,@FirstName NVARCHAR(32) = NULL)AS/* SET NOCOUNT ON */INSERT INTO Authors (LastName, FirstName)VALUES(@LastName, @FirstName)RETURN--*|* Error message given: when trying to save CREATE PROCEDURE StoredProcedure: "There is already an object named 'InsertAuthor' in the dB
View 11 Replies
View Related
Dec 30, 2004
Does that make sense? I have 2 tables, one is an older version, one newer. The newer version has some additional fields, indexes, etc. I'd love to have a tool to run against them that would generate an ALTER TABLE script to modify the old table so that it matches the new table.
Is there anything like that out there?
View 4 Replies
View Related
May 13, 2007
Hi,
I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".
When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.
How can I use DDL statements via ODBC?
I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.
Thanks for help!
Regards,
Stefan D.
View 14 Replies
View Related
Jul 27, 2006
Every day we are restoring prod DB in Development env. I need to save before restore users stored proc,
restore DB and after create SP from file.
Thanks.
View 3 Replies
View Related
Jul 23, 2005
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Aug 16, 2007
In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.
Is there any way to grant this privilage without granting alter on schema also?
View 1 Replies
View Related
Jun 26, 2007
I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.
I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?
Thanks,
Nanci
View 2 Replies
View Related
Aug 25, 2000
I am using SQL 6.5. Can I create a database and Alter the same
database in one SQL Script?
Thanks in advance.
View 1 Replies
View Related
Jul 20, 2005
I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO
View 2 Replies
View Related
Oct 8, 2007
I am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?
my code:
Alter table customers alter column age
View 7 Replies
View Related
Aug 31, 2007
Hi All,
I am using SQl server studio management to create table.
How to set two attributes as a primary key: composite key. like ( proj_id, emp id) both as primary key.
How to specify the forign key constraint. using alter table
Please give me the example: don't syntax which msdn gives.
Thanks and Regards
Abdul M.G
View 2 Replies
View Related
Aug 14, 2000
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
View 1 Replies
View Related
Sep 7, 2007
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
View 18 Replies
View Related
May 20, 2007
how to save img in sql db ,I need some one to tell me
View 1 Replies
View Related
Jun 19, 2007
Hi...
I want to save xml string in sql, but, i have problem, the length of the string is 19,000 - 24000 chars, and nVarChar can contain 4000 chars, and binary can contain 8000 chars...
so, how can i solve this?
thank you...
View 4 Replies
View Related
Feb 27, 2007
Hello,I created a class in my .Net code and I have an SQL 2005 table with a column of type image (I suppose I should use this type)After I define the class properties I need create a new record and save the class in the database.Can I do it the same way as I would save, for example, a string in a varchar field?Thanks,Miguel
View 1 Replies
View Related
Jul 31, 2007
I'm just wondering if we can save sql aggregate functions into a variable. I have this query:select company, dept, sum(pers) as pers1, sum(amount) as amount1,sum(amount)/sum(pers) as wage from xxtestsumgroup by company, dept instead of calling sum(amount) and sum(pers) again in "sum(amount)/sum(pers) as wage",I would like to save them in some kinda variable in the select clause so it will save process time.Sorry but im new to sql programming. So thx for your understanding.
View 2 Replies
View Related
Jul 16, 2007
I am writing VBA code to save a PDF document to a SQL 2005 database table. Does anyone have any tips on how to do this? I assume the data type in the database will be varbinary(max)??
Thank you,
zzwoodsj
View 7 Replies
View Related
Jan 29, 2004
In Enterprise Manager when I bring up Trigger Properties there is an active button titled "Save as Template". But when I switch to an existing trigger the button is disabled. Oddly, the Help file for the dialog box doesn't even mention this button.
Anybody know what this does, how it works, or why it would be usefull? Any references to The Holy Book would be helpfull.
View 14 Replies
View Related
