|
For a long time, the Tasks-Generate Scripts command in SSMS used to really annoy me, because I had to choose between creating DROP or CREATE scripts, and only had an optional IF EXISTS check.
Now I'm presented with a set of options that allows an IF NOT EXISTS, and DROP and CREATE in the same script. Am I going mad? What could have caused this change?
|
|
|
|
|
No seems quite sensible to me; these are used to stop you getting an errors by creating something that is already created.
|
|
|
|
|
It know it's more sensible, I just don't know when it changed and what updated caused it to change.
|
|
|
|
|
I have an SQL 2005 data table that has these coloumn;
Name Type
EventDate, DateTime
WaysideAddress, Nvarchar(50)
WaysideName, Nvarchar(50)
State, nchar(10)
AlarmMessage, Nvarchar(50)
What I am trying to do is count the total number of distinct AlarmMessage that each WaysideName reported from the table Grouped by EventDate and WaysideName.
The AlarmMessage is all text.
So the results of the query should look like this;
WaysideName|AlarmMessage|Count
Thanks,
|
|
|
|
|
Solved my own question;
SELECT DISTINCT WaysideName, ST, AlarmMessage, COUNT(AlarmMessage) AS Error_Count
FROM AlarmsList
GROUP BY WaysideName, ST, AlarmMessage
ORDER BY Error_Count DESC, ST, WaysideName, AlarmMessage
Thanks Everyone regardless!
|
|
|
|
|
Hi
I want to count the rows in a DataTable but it should ignore blank rows, I am trying something this,
string batchid = ds.Tables["No"].Columns[0].ColumnName;
ds.Tables[0].DefaultView.RowFilter = "" + batchid.Trim() + "=" + string.EmptyString;
int records=ds.Tables[0].Rows.counl;
the above code showing me following error.
Syntax error: Missing operand after '=' operator
Shahzad Aslam
Software Engineer
Email: shehzadaslam@hotmail.com
|
|
|
|
|
You are mixing up code with a string expression.
<br />
ds.Tables[0].DefaultView.RowFilter = "" + batchid.Trim() + "=''";
|
|
|
|
|
This is still not working and giving the following error:
Cannot perform '=' operation on System.Int32 and System.String.
Shahzad Aslam
Software Engineer
|
|
|
|
|
I've just had a very strange experience running a CLR stored proc. The procedure takes its data source connection from the context, and its destination connection string from the source DB. When I first ran the proc, I got back the error, "Keyword not supported: Provider", so I removed the provider clause from the destination connection string.
However, until I restarted my SQL server, I continued to get the same error. My ONLY action that resolved it AFTER correcting the connection string was restarting the server. This is plain scary if updating DATA, never mind code, requires a server restart.
|
|
|
|
|
OK, it turns out the dest. connection string is stored in a static field, and I only set its value from the database the first time it is read. SQL Server just keeps the class loaded 'forever', so every subsequent access returns the first value.
|
|
|
|
|
I wrote a stored procedure that looks like this.
CREATE PROCEDURE dbo.TEST
(
@Name varchar(50),
@Surname varchar(50)
)
DECLARE @ID int
INSERT INTO Table1
(
Name,
Surname
)
VALUES
(
@Name,
@Surname
)
SELECT @ID = SCOPE_IDENTITY()
The problem is that the SCOPE_IDENTITY() does not get the ID value.
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
Illegal Operation wrote: SELECT @ID = SCOPE_IDENTITY()
try SET @ID = (SELECT SCOPE_IDENTITY())
|
|
|
|
|
Does the table have an identity column?
Wout Louwers
|
|
|
|
|
Illegal Operation wrote: DECLARE @ID int
Here in your stored procedure you have Declared @ID as just a local variable. If you need that value to be returned by the procedure then you have declare it as a output parameter.
DECLARE @ID int OUT
[OR]
CREATE PROCEDURE [spTest]<br />
@ID int OUT,<br />
-- Other parameters here<br />
AS<br />
-- Your Insert Query Here<br />
SELECT @ID= SCOPE_IDENTITY()<br />
GO
Regards
- J O H N -
|
|
|
|
|
i was just executing an update statement like this and that error occured..
it usually runs without error.. except for this specific data..
what does that "plan dates are invalid" mean?
thanx in advance again!!
UPDATE AJ_CONTRACTS
SET EXPECTED_SIGNOFF = '06/20/2008',
CONLEN = '20',EXTENDED = 'Y',
CONLEN_UNIT = 'M',
UPDATED_BY='apagustin',
UPDATE_DATE ='10/25/2007'
WHERE (CREW_SEQ_NO = '27979')
|
|
|
|
|
I've not seen that error message before. Try:- The date formats may be incorrect. Try using '20 Jun 2008' and '25 Oct 2007' instead.
- Double-check if there are any triggers defined on the table.
If these don't identify your problem then give us the exact error message (including error numbers).
Regards
Andy
|
|
|
|
|
Try swapping the date.
06/20/2008 to 2008/06/20
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
anthoy wrote: SET EXPECTED_SIGNOFF = '06/20/2008',
You can try this as well:
SET EXPECTED_SIGNOFF = cast('06/20/2008' as date)
It will force the string into a date (you will need to do the same with UPDATE_DATE).
You always pass failure on the way to success.
|
|
|
|
|
thanks for all the help guys.. i really appreciated it... my boss says its ok.. cause my data was just messed up or the field 'EXPECTED_SIGNOFF' was somewhat already filled in..
it was a 'trigger' on that table 'AJ_CONTRACTS' that was causing the error.. maybe you were all right it's just the date format.. cause i was using format date 'dd/mm/yyyy'
|
|
|
|
|
I have a stored procedure that rebuilds the indexes on a clients database.
Useing a Try Catch block I write any errors to a cursor which report any errors
when the procedure has finished running.
<br />
WHILE @@fetch_status = 0<br />
BEGIN<br />
SET @SQL = 'ALTER TABLE '+@TableName+' ADD CONSTRAINT '+@ObjectName+' '+@Fields<br />
BEGIN TRY<br />
EXEC (@SQL)<br />
END TRY<br />
BEGIN CATCH<br />
INSERT INTO ##NonCreate VALUES(' constraint ' + @ObjectName + ' on table ' + @TableName, ERROR_MESSAGE())<br />
END CATCH<br />
END<br />
FETCH NEXT FROM cUniqueConstraintList INTO @TableName, @ObjectName, @Fields<br />
<br />
The stored proceudre works fine and output any errors to the log.
However the message reported by ERROR_MESSAGE() is a bit less than
informative. "Could not create constraint. See previous errors."
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'my_constraint_name' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
My error log is given the Msg 1750 error, I would prefer it gave me the 2714 message, as this is more informative as to why the error has occured.
Does any one know of any way of returning the first message, or point
me in the right direction?
|
|
|
|
|
Hi...
You can get all the info regarding the error....
for more info click Here[^]
Regards,
Sandeep Kumar.V
|
|
|
|
|
One of the first places I referenced.
That example works as the Div by 0 only returns one error message and ERROR_MESSAGE() returns the last error message. But when an error occurs on a Add/Drop table constraint 2 errors occur, and I'm interested in the first message and not the last!
|
|
|
|
|
Hi
I have a table named StudentResult
UserID School Semester Grade Campus Status
1 School1 Summer 10 Campus1 Accepted
2 School1 Summer 10 Campus1 Accepted
3 School1 Summer 10 Campus1 Rejected
4 School1 Summer 10 Campus1 Rejected
5 School1 FALL 10 Campus1 Accepted
6 School1 Summer 12 Campus1 Accepted
7 School1 Summer 12 Campus1 Rejected
8 School1 Summer 12 Campus1 Accepted
9 School1 Summer 12 Campus1 Rejected
10 School1 Summer 12 Campus1 Accepted
11 School1 FALL 12 Campus1 Rejected
12 School1 FALL 12 Campus1 Accepted
13 School1 FALL 11 Campus1 Rejected
14 School1 FALL 11 Campus1 Accepted
15 School1 FALL 11 Campus1 Accepted
16 School1 Summer 11 Campus1 Rejected
17 School1 Summer 11 Campus1 Accepted
18 School1 Summer 11 Campus1 Accepted
I want to take the number of accepted and rejected users from the above table as the result table below.I want to consider grade,Campus and semester for taking count and the result should be like result table.Please help me to write Query for this.
I am using SQL server2000. as database
School Semester Grade Campus NoofAccepted NoofRejected
School1 Summer 10 Campus1 2 2
School1 Fall 10 Campus1 1 0
School1 Summer 11 Campus1 2 1
School1 Fall 11 Campus1 2 1
School1 Summer 12 Campus1 3 2
School1 Fall 12 Campus1 1 1
Thanx
|
|
|
|
|
select School, Semester, Grade, Campus,
sum(CASE WHEN Status = 'Accepted' THEN 1 ELSE 0 END) AS NumOfAccepted,
sum(CASE WHEN Status = 'Rejected' THEN 1 ELSE 0 END) AS NumOfRejected
from StudentResult
group by School, Semester, Grade, Campus
Of course, this is what you have asked for, but not necessarily the best way of going about it... you could always just use count (*) and use the Status as a Column heading also...
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
hihi,i'm new baby in sql.So, I would like ask someone can help me solve a question as posted as below.
1. As a admin, i need to know who is connecting to my sql database.But,I have no idea to get the method to detect who is connecting to my sql database
thank for your help
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
