Transact SQL :: ALTER Statement Is Not Getting Executed In A Batch
May 7, 2015
I have written the following code:
SET NOCOUNT ON
DECLARE @RowCount int; SET @RowCount = 0;
Begin Try
Begin Transaction
--------------------------------------------------------
-----Table Name: AlertsStaticRecord_Archive
-----Column Name: AlertID
--------------------------------------------------------
ALTER TABLE [AlertsStaticRecord_Archive] ALTER COLUMN [AlertID] int NOT NULL;
[Code] .....
But, when I execute these batch, I am getting error:
Msg 8111, Level 16, State 1, Line 11
Cannot define PRIMARY KEY constraint on nullable column in table 'AlertsStaticRecord_Archive'.
Msg 1750, Level 16, State 0, Line 11
Could not create constraint. See previous errors.
Because, the first ALTER statement is not getting executed.
View 4 Replies
ADVERTISEMENT
Apr 20, 2015
Im working on Partition purge process, where I need to specify following statement:
SET @cmd = 'ALTER PARTITION FUNCTION ' + @function_name + '() MERGE RANGE (@range)'
EXEC (@cmd);
SET @cmd1 = 'ALTER DATABASE '+ db_name()+ ' REMOVE FILE ' + @partition_file
EXEC (cmd1);
I want to put this statement in Begin Tran /Commit statement but getting error that it is not allowed. "ALTER DATABASE statement not allowed within multi-statement transaction"..what are my options to rollback in case there is a failure.
View 4 Replies
View Related
Nov 5, 2015
I could identify the last or all stored procedure, which was performed on a database, +/- something similar to what the profile of sql server identifies as below ?
View 2 Replies
View Related
May 8, 2007
Hello,
In my database (SQL Server 2005), some data were inserted from a external application.
In order to validate the data , I want to get the SQL statment executed by the application.
Is this possible?
Thanks
Robert
View 3 Replies
View Related
Sep 11, 2013
I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.
When I run it in my SQL2008R2 environment I get the error:
Only functions and some extended stored procedures can be executed from within a function.
The SQL2008R2 server is new. What can I look for?
Here's the code for the function:
BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END
View 4 Replies
View Related
Dec 2, 2005
Can a stored procedure be executed from within a select statement?
Given a store procedure named: sp_proc
I wish to do something like this:
For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.
View 2 Replies
View Related
Jun 21, 2006
I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions.
I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time?
For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?
View 6 Replies
View Related
Nov 29, 2006
Hi everyone,
I wonder how one can see or save the real sql statement that was executed by some stored procedure (including the one that used supplied parameters)?
Just need that for debugging purposes...
thanks!
View 1 Replies
View Related
Sep 2, 2015
I have a stored procedure that attempts to INSERT @BatchSize number of records at a time into a table. Currently, I have @BatchSize set to load 50,000 at a time. The table I am inserting from has a little over 67,000 records.
When I execute the procedure with NOCOUNT left off, the procedure seems to run indefinitely, and the count of records returned surpasses what I have in the source table. However, only 50,000 records are inserted into the table.
Below is my code:
begin try
--error catching variables:
declare @Error_NumberLocal int
,@Error_MessageLocal varchar(4000)
,@Error_SeverityLocal int
,@Error_StateLocal int
,@Error_ProcedureLocal varchar(200)
,@Error_LineLocal int
,@User_NameLocal varchar(200)
[code]....
What is the problem with the looping structure that would cause this issue?
View 6 Replies
View Related
Sep 26, 2005
Hi There,
I want to able to run a batch of sql statments (execute procedures) in SQl Server at a specific time. Just wondering if there is a method for that.
View 2 Replies
View Related
Jun 19, 2015
What is the difference between Batch and Stored Procedure?
View 5 Replies
View Related
Jan 3, 2008
hi all,
I have a function and batch witch consisted of same sql statement, and they will get the same result. but time they take is different, and produce a little different query plans, another significant difference is the estimated numbers. the function is always slower than the batch.
does anyone know why same sql would produce different query plans performance? how do i can to let the function as fast as the batch?
thanks!
View 6 Replies
View Related
Mar 17, 2008
The following exception is thrown with sqljdbc.jar (not with jtds0.9.jar)
com.microsoft.sqlserver.jdbc.SQLServerException: New request is not allowed to start because it should come with valid transaction descriptor.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source)
at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:265)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:288)
While using the query :
if not exists ( select 1 from sysindexes where id = object_id('aaa') and name = 'aaa_pk')create unique nonclustered index aaa_pkon aaa(id)using Statement.executeBatch()at conection.commit()
Sample Code
conn = getConnection();
// create the statement and execute the query
Statement stmt = null;
try
{
conn.setAutoCommit(false);
stmt = conn.createStatement();
for ( String sql : sqlList )
{
stmt.addBatch( sql );
}
results = stmt.executeBatch();
stmt.clearBatch();
conn.commit(); //throws the exception
}
catch ( SQLException e )
{
try
{
conn.rollback();
}
catch ( SQLException e1 )
{
throw new DataSourceException( e1 );
}
throw new DataSourceException( "Error executing sql: %1", e, sqlList.toString() );
}
View 1 Replies
View Related
Sep 19, 2015
There is a table exists in a database name tbDatabaseProviders.
I want to alter this table to add a column DatabaseProvider Also I want to add a default constraint so if nothing than 1 should be added.
Remember, the table already exists so when I write the script query then I want to first check the constraint if it exists then I dont want to add the constrait otherwise I will add it.
I was having problem to first add a constraint with the constraint name so I could find the constraint before finding it if it already exists.
What should I do the above.
View 3 Replies
View Related
May 15, 2000
Do anyone knows the syntax for changing the name of a column in a table with the
alter statement or any other statements????
Thanks in advance,
Vic
View 1 Replies
View Related
Jan 5, 2001
Hi
Is it possible to remove Identity property of a column using ALTER statement in SQL Server 7.0.
Thanks in advance
Rahul
View 2 Replies
View Related
Mar 13, 2002
I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months)
2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!
1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin)
2. SVCSQL service use DomainSVCSQL2000 to run
3. SVCSQL agent use DomainSVCSQL2000 to run
4. DTS use 'osql -E
Where should look for reference to sa ?
Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
View 5 Replies
View Related
Oct 7, 2013
Is there a way to create a Batch file that will run an Update Statement and schedule it to run?I've used bcp to extract data to a txt file before, but i'm not sure if an Update can be performed.I'm using SQL Server 2008 R2 Express Edition so i don't have Server Agent available.
View 7 Replies
View Related
Jul 3, 2000
looking for necessary syntax to alter table id to autoincrement adding identity statement, not sure on syntax for seed an increment, or if it is possible at all.
View 2 Replies
View Related
Oct 13, 2000
Hi List,
I am trying to add a column with Alter Table, like this:
ALTER TABLE myTable ADD
newColumn bit DEFAULT 0 NOT NULL
This works fine with SQL-server 7, but I get this error in 6.5:
'ALTER TABLE only allows columns to be added which can contain nulls. Column 'newColumn' cannot be added to table 'myTable' because it does not allow nulls.'
I also found this in the help files:
'Columns added to a table must be defined as NULL. When a column is added, the initial value for the column will be set to NULL. This restriction forces the ALTER TABLE statement to fail if the bit or timestamp datatypes are used.'
My question, is there any other way to do this on 6.5 ?
Stefan Nilsson
View 2 Replies
View Related
Jul 19, 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
This is the error:
There was an error parsing the query. [ Token line number = 1,Token line offset = 27,Token in error = CONSTRAINT ]
View 1 Replies
View Related
Feb 13, 2004
Just Like these:
-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)
/*
can I do something here to free res used by prior statement?
*/
select * from someOtherTable
--batch end
The Sqls above are written in a procedure to automating test for some select querys.
View 4 Replies
View Related
May 8, 2015
I have a table with ~30M records. I'm trying to add a column to the existing table with default value and have noticed following ... When using alter with default value- (Executes more than 45 min and killed forcefully)
ex:
ALTER TABLE dbo.Table_X Add is_Active BIT CONSTRAINT DF_Table_X_is_Active DEFAULT 'FALSE' NOT NULL
GO
When using update command after adding column with alter (without default value) it completes is 5 min.
ex:
ALTER TABLE dbo.Table_X Add is_Active BIT NULL
GO
UPDATE Table_X SET is_Active = 0 WHERE is_Active IS NULL
GO
Why there is so much of difference in execution times ? I was just trying to understand internal behavior of the SQL in these two scenarios.
View 4 Replies
View Related
Oct 27, 2015
I want to alter my all tables to change the name of all columns.
I need this because all column names were created with space and I want to remove the space for future work .
For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]
View 11 Replies
View Related
May 28, 2002
Hello.
I'm trying to create a batch sql script which first alters some existing tables via the ALTER TABLE command, I then want to alter some existing stored procedures via the ALTER PROCEDURE command within the same batch. I have found that I can encompas the alter table scripts within a conditional IF EXISTS (Begin/End) but not the alter procesdure scripts. I have looked in reference material and have found nothing to suggest this type of operation is not possible. Is this possible? Is this a know bug fixed by a service pack?
Thanks in advance for any replies.
David.
View 3 Replies
View Related
Feb 6, 2008
Hi
I want to delete the Default value for a specific column which is set to Null
I've used
ALTER TABLE SYSTEMS_PATIENT_LOG ALTER COLUMN SYSTEMS_LOGID DROP DEFAULT
It is giving error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DEFAULT'.
plz could any one tell me where I could be wrong
View 3 Replies
View Related
May 27, 2015
I have existing table which is having Set Quoted Identifier Off and Set Ansi Null
Now I want to change those setting so Is there any alter statement for the same?
Also Let's say At my database level If those settings are off and If I convert it to ON then It is not taking effect on existing tables SP which are already build.
View 4 Replies
View Related
Jan 31, 2007
My client's website database is hosted by a third party. I need to alter one of the column definitions for the largest table in the database. Unfortunately, the transaction log fills up if I try to alter the table. I've done all the usual stuff like truncating the log, etc., but the simple fact is that the operation requires more log space than we have available. Therefore, we need to purchase additional disk space for the database.
What I'm looking for is a way to roughly estimate how much log space will be required to alter this table so that we purchase enough but not too much additional space. The table has an identity primary key and 4 other single column indexes: one int, one datetime and two varchar(30) columns.
Any suggestions? Thanks in advance.
View 4 Replies
View Related
May 14, 2002
I have coded an alter statement for adding a column to a temp table inside an sp,but it skips the alter statement while executing the sp! This happens even if I run the same code on query analyzer too. If I use 'GO' statement before the alter command,then it works fine on Query Analyzer.But, I can't use 'GO' in an sp. I am using the sa account. Any ideas on how to fix this??
Thanks.
Di.
View 1 Replies
View Related
Mar 27, 2001
Folks!
What is wrong with my syntax with the following command?:
alter table EmployeeInfo alter column OriginDate smalldatetime not null default getdate()
I'm getting:
Incorrect syntax near the keyword 'default'
Currently, in my table OriginDate is nullable with no default.
Thanks in advance for your help!
APF
View 2 Replies
View Related
May 5, 2008
It is possible to alter multiple columns within a single alter table statement?
I have got the following URL that tells it is not possible to alter multiple columns within in signle alert table statement.
http://www.blogcoward.com/archive/2005/05/09/234.aspx[^]
Does anyone know about that?
Thanks,
Mushq
View 4 Replies
View Related
Feb 26, 2008
Recently I created a DDL Server-scope trigger using the following:
create trigger stop_alter_database on all server
for ALTER_DATABASE
as
rollback;
print 'database change stopped by stop_alter_database';
go
Then I ran the following script:
alter database [test] modify file (name=test', maxsize=2028mb);
The result was:
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
database change stopped by stop_alter_database
The problem is that when I checked the max size of the data file it had changed. So, the statement was never rolled back. Is there something I'm missing because I can't find any documentation or articles that state the inability to rollback alter database statements. Whats going on?
View 2 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