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.
skip locked records in a MERGE statement and output the list of skipped records.
Through the documentation, internet posts and testing, I believe it is NOT possible. MERGE acts like a single atomic DML statement, and therefore cannot avoid locked records.
I can use the READPAST hint, which will skip the row-locked records. However, it could actually insert duplicate keys in certain cases (as it is ignoring records, i would guess), which would not be acceptable.
I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.Here is a short snippet of my code, but it keeps erroring out on me.Try myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text myCommand.ExecuteNonQuery() myTran.Commit()Catch ex As Exception myTran.Rollback() Response.Write(ex.ToString())End Try The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
View 3 Replies View RelatedDo 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
Hi
Is it possible to remove Identity property of a column using ALTER statement in SQL Server 7.0.
Thanks in advance
Rahul
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 RelatedHi 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
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 ]
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.
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
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.
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
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
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.
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.
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?
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
I need to create an FTP task and schedule it. The source file's name is not constant. It changes every day with a date extension appended to the file name, like PO_2002_6_12_PM.zip. How do I create a task task to grab this file from the remote site on a daily basis in a scheduled task? Any code help is appreciated.
Thanks.
Di.
Hello all,
I am trying to add columns to several tables in a singular database. I want to check to see if the column exists in a given table first before executing the alter table command so that I can skip that command if the column already exists, and avoid aborting my script prematurely. I can't seem to find a way to check for the column's existance. Any suggestions?
I've some huge table (over 100GB each), these table contain column of NTEXT, IMAGE. Application team needs to change these data types to VARBINARY(MAX), I've tested the modification in our lab and I noticed that the operation has been almost immediate so, I think that DB Engnine has not converted the existing data in the column but it has simply changed the definition of the column.
Or maybe NTEXT and IMAGE can be transparently converted into VARBINARY(MAX)?
Anyway, I want to be sure that the modified table is "coherent" I don't want that at a given point SQL Server tells me that some data is not readable.
Primary platofrm: Sql12k, 7.0 Ultimate Pro OS
I'm launching the aforementioned statement from one MASTER session windows and I get this message, I am stuck, I though ROLLBACK INMEDIATE go throught any already session open.
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'GFSYSTEM' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Can anyone help me with below error code i am in getting while runninga C program using sqls.Password: passwordSQL Return Code: -25001BATCH_LOG_PATH:Thank you,D
View 1 Replies View RelatedJun,
Thank you for your help but when I put the NOT IN like you suggested I am still getting the error of:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Here is the code again:
--If a request is not equal to the request from Jason's table then populate the table.
if (select REQUEST from TEAM3B_PULL_TOTAL_TST) Not In (select REQUEST from REQUEST_BUS_REQ_TST)
begin
Insert into REQUEST_BUS_REQ_TST (b.request,b.business_req_id,b.bus_test,b.test_cas e_id,b.test_case_source,b.test_case_descr)
select a.request,a.business_req_id,a.bus_test,a.test_case _id,a.test_case_source,a.test_case as test_case_description
from TEAM3B_PULL_TOTAL_TST as A JOIN REQUEST_BUS_REQ_TST AS B ON A.REQUEST = B.REQUEST
Order by b.REQUEST,b.BUSINESS_REQ_ID,b.TEST_CASE_ID
end
Hi,I have a table that lists all attempts at logging in to a server.Columns are:userid, firstname, lastname, login_date, login_timeIf a user attempted to login 5 times, then I want to return the row(with all columns)......BUT I only want to return the row with the 5th time attempted.something like this:select * from mytablejoinselect userid, login_date from mytablegroup by userid, login_date having count(*) > 4BUT, how do I get just the 5th time attempted??Thanks!Skiz
View 2 Replies View RelatedI want to write a query like this:
select ' select count(*) '+ '/*' + table_name + '*/' + char(10) + ' FROM '+table_name...
The result should looks like this:
--------------------------------------------
select count(*) /* ---- emp_name */
FROM emp_name
---------------------------------------------
query result:
COUNT(*)----EMP_NAME
-------------------
3
-------------------------------------------
But, now I can only got
COUNT(*)
-------------------
3
What's wrong with my original code?
Hi All
I am using SQL 2000 on win 2003 server and I have been using the query analyzer and a stored procedure to add records to a particular table.
For some reason the ID column which is an identity column and is seeded to increment by 1, skips numbers every once in a while. I haven't used any deletes and there have been no errors when loading the info.
Has this happened to anyone else? Where do I start looking for possible causes
Thanks
P
Iam extracting data from Excel to SQL Destination. In my excel file there are some null values rows are there, just want to skip those null and rest have to export to SQL server Destination. Anyonce can helpme in this.
View 3 Replies View RelatedHi, i wanted to take all the orderID that fall between a certain date. I was thinking of using this syntax... Dim sql as string = "SELECT orderID " & _
"FROM orders " & _
"WHERE orderDate BETWEEN 11/11/2007 AND 15/11/2007"
in my database i set the date to nvarchar, not date... so will this syntax works??
thx
Hi,
I want the PlanId in 6 tables and is there is a way i can provide a statement...
Update TableName
SET Plan_NUM = '279-072230'Where
Plan_NUM = '120003'
but i need to provide mulitple plan_NUM??
REgards
Karen
my current stored procedure generates the following data
name type hours
Mike A 1
Mike A 2
Mike A 1
Bob A 1
Bob B 3
what i want is this:
name type hours
Mike A 4
Bob B 1
Bob B 3
and here is what i have
select departmet,name,sum(hours),
Case
when.... then .......
when.... then .......
when.... then .......
Else 'A'
End as Type
from tableA
Group by departmet,name
Any thoughts!
Thnks
I'm trying to create a form that allows someone to find an
address from a postal code search in the database. My query works exactly as I’d
like in query builder using the following select:
Select [FIELD_LIST] from addresses WHETE POSTCODE LIKE ‘%’+@POSTCODE+’%’
I then pass the user entered post code to the select statement
which is executed. However, I’m getting some odd behaviour. Assuming there is
an address in the database with the post code
“LS11 0ES”...
If I search for LS11, nothing is returned;
If I search for LS11%, nothing is returned;
If I search for %LS11%, nothing is returned;
If I search for %LS11%%, the data is returned.
If I search for %LS%1%%, the data is returned (as is LS21
0ES etc. etc.).
However, I want the user to be able to enter shorter search
strings and it to pull all the data back out, so they can enter a substring
such as LS and it will pull out all the data without the users needing to enter
the full pattern of % symbols.
If I go into query builder (in visual web developer) and
enter just “LS” it works as I’d want, but not when pulled from a web page.
Any ideas?
Thanks
I am using the following code to query a DB and output an Excel Spreadsheet. For some reason, the first line is always missing.Any help would be greatly appreciated.============================================1 reader = cmd.ExecuteReader()
2 If Not reader.Read() Then
3 Return False
4 Else
5 For i = 0 To reader.FieldCount - 1
6 strLine += reader.GetName(i).ToString & Chr(9)
7 Next
8 objStreamWriter.WriteLine(strLine)
9 strLine = ""
10 With reader
11 While .Read
12 For x = 0 To .FieldCount - 1
13 strLine = strLine & reader.GetValue(x) & Chr(9)
14 Next
15 objStreamWriter.WriteLine(strLine)
16 strLine = ""
17 End While
18 End With
19 End If
I have a table with a text field and a grouping number.
1, A
1, B
1, C
1, D
1, E
2, A
2, C
2, D
2, E
3, A
3, B
3, C
3, F
4, A
4, B
4, D
4, F
4, G
I assemble the text fields into strings by group, using the FOR XML PATH('') function, so that I end up with
1, A-B-C-D-E
2, A-C-D-E
3, A-B-C-F
4, A-B-D-F-G
This all works fine. I now have a requirement to leave out some groups, based on whether the GROUP does or does not contain a certain text. For instance, if I want only GROUPS with 'E', my result set should be
1, A-B-C-D-E
2, A-C-D-E
If I want only those GROUPS that DO NOT have 'E', my result set should be
3, A-B-C-F
4, A-B-D-F-G
I can (and have) put a condition on the assembled string, but it seems to me that it should be possible to make the cut earlier. That is, rather than assembling the string, scanning it with a Like '%E%' condition and discarding what doesn't meet my needs, I would like to stop scanning the incoming GROUP as soon as a mismatch is detected.
If I want all groups that will not contain an 'A', for instance, it is pointless for the query engine to read in 'A', 'B', 'C', 'D', 'E', put them all together into a string, compare it with a wildcard and throw it away. As soon as it sees the 'A', it has enough information to completely skip over the rest of that group. The abbreviated examples here are trivial, but the real code is not.
All I've been able to dream up so far results in skipping the RECORD that contains the 'A', but not the entire group.