|
When using
SELECT ROW_NUMBER() OVER (ORDER BY id) AS [RowNumber]<br />
FROM table where RowNumber > 1
It returns error 'Invalid column name 'RowNumber'. What is the problem here? I'm using SQL Server Express 2005. Thanks.
|
|
|
|
|
You added 'RowNumber' as a column-name alias. Those can be used in the ORDER BY clause, but can't be referenced in the WHERE part. Since you're using SQL 2005, you can bypass this by using a temporary query, selecting into a new CTE first;
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS [RowNumber]
FROM [YourTableNameGoesHere]
) AS CTE_TMP
WHERE [RowNumber] < 10
Enjoy 
I are troll
|
|
|
|
|
I have a procedure running with transaction used in it.
I have used multiple insert in the procedures and finally return the scope_identity() of one of the insert by first setting a variable with scope_identity() and then returning it with select @variable at the end of procedure after commit and in case of RollBack i m returning it as select -9
The issue what am i facing is that sometimes it is skipping the return value in case of Commit Transaction as it is not returning anything to the code.
I am using it like this:
Full Procedure Code with Insert Commands here
set @variable=scope_identity()
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
Select -9
END
COMMIT
select @variable
The procedure is not going in the @@ERROR section and also all the insert values are there in tables after commit but why sometimes it is not returning the value required. I am using ExecuteScaler in the code.
Regards,
Kaushal Arora
Regards,
Kaushal Arora
|
|
|
|
|
You are not setting your variable:
set @variable=scope_identity() <br />
IF @@ERROR <> 0 <br />
BEGIN -- Rollback the transaction <br />
ROLLBACK <br />
Select <big>@variable =</big> -9 <br />
END<br />
COMMIT<br />
select @variable
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I am saying it is committing the records not giving any error and rollback. It is not returning the value in case of committing the records.
|
|
|
|
|
Without the rest of your code its kinda hard to say then.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi All,
I have an existing database with a table schema that I need to change. However it seems that there is a conflict in the conversion. I'm trying to change a ntext column to a nvarchar column. I'm using C# Express 2008.
I've tried doing it on an empty table with the connection closed but I still see this error. What's the best way to tackle this schema change? I don't like the idea of having to recreate the table from scratch. As I understand it there is no way to script an SqlCe database ...
Any pointers would be great.
Thanks,
|
|
|
|
|
wow, you still can't do this in sql2008?!
One approach is to create a whole new column with the required type, then copy the content from the old column into it & delete the old column. Of course, the column names cannot be the same, so you'll need to use sp_rename at some point.
Unfortunately, this can get very tricky when there are other constraints in the database that rely on your column (default value is probably most relevant here). Also, be aware of the 4000 character limit of nvarchar.
There's lots of tools out there that will assist with this kind of change. We use xSqlObject here, which is excellent (www.xsqlsoftware.com/Product/Sql_Schema_Compare.aspx[^])
Best of luck!
"An eye for an eye only ends up making the whole world blind"
|
|
|
|
|
Thanks for this. I came to the same conclusion. Also I'm using SQLCE ... is the nvarchar limit 4000 characters for this version? I think it's actually 255 ...
|
|
|
|
|
A quick google suggests the limit is still 4000... don't know though, i've never used CE
|
|
|
|
|
A Real Work at Home Opportunity as an Independent Advertising Executive. We offer home workers the opportunity to earn extra money from the comfort of their own home. Visit Us at http://www.clicknearn.net/idevaffiliate/idevaffiliate.php?id=2449_9
|
|
|
|
|
|
Hi, a lot of PTC sites on net , you can post your ads in the right place.
Thanks
|
|
|
|
|
Hello
I have two Database.
DB1 And DB2 there are tbl1 And tbl2 two tables in corresponding database.
if any value change in tbl1 table the corresponding value will also update in tbl2 tables.
Can any one help me in this regard.
Thanks in advance
Sujit
|
|
|
|
|
|
hi all,
i am trying to install SQL express 2005 from following code
str = Application.StartupPath & "\SQLEXPR32\setup.exe /qb ADDLOCAL=ALL INSTANCENAME=" & InstanceName & " SECURITYMODE=SQL SAPWD=mypassword DISABLENETWORKPROTOCOLS=0 "<br />
Shell(str, AppWinStyle.Hide, True)
it works well for first time. When i install from above code and uninstall from Control panel and then once again i execute the above code then it does not install the SQL server Express
I think Uninstall from control panel does not completely remove the SQL server Express.Its require some more steps
Whether anybody can suggest me that how can i completely uninstall the SQL server Express from my machine ?
|
|
|
|
|
How i can show relationship between tables as a diagram in MYSQL
|
|
|
|
|
Hello All,
I've been googling and based on many articles I belive what I'm asking of sql2000 triggers is not possible for the particular type of error i'm getting.
I am trying to integrate with an existing application on sql 2000 without affecting it's behaviour. When it inserts/updates/deletes in a few tables I need to perform an I/U/D in a sql 2005 DB through a DB link. This is all set up fine, as well as the trigger to do the insert. I need to be able to put error handling in the trigger so that it does not affect I/U/D of the existing product.
Right now, MSDTC is having a fit because I don't have it configured properly on purpose. I did configure it correctly and then reverted so it would raise errors so I could test error handling.
Nothing I've tried so far will let me handle this error in an insert trigger on sql 2000:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.
Execution stops immediately so I can't even get to an "IF @@ERROR" statement and the transaction is rolled back. I know how to fix this error but I need to be able to handle this type of error so I can use xp_logevent or something similar to monitor. Is it possible - Any ideas?
Does anyone know of a way in sql 2000 to handle this type of error in a trigger on sql 2000?
|
|
|
|
|
Hi Guys,
I am having problem when running the Following SQL script.It takes Pretty long time to return result.
I am having about 500,000 records in my database.
select count(distinct(CR_Cli)),SO_name from Callrecords inner join dbo.StudioOperators
On CR_StudioOperatorID=SO_ID
where datename(month,cr_callstart)='April'
and Cr_cli not in (select Cr_cli from Callrecords where cr_callstart<'2009-03-31')
group by SO_name desc
Is there any way i can write this script?
Thanks you very much.
|
|
|
|
|
In SQL Server Management Studio, place this query in the workspace and click on the icon that has a description of "display estimated execution plan". This will give you an idea of where there may be table scans and where you might be able to add an index to improve your performance.
You should also check to see if your statistics are updated on the tables and indexes involved in this query before running the "display estimated execution plan" as described above.
David
|
|
|
|
|
Thank you so much.It works
|
|
|
|
|
Hi there I am trying to update a field in our contact table. I have a view which contains all of the contacts that i need to run the update for, my question is:
How do i update a contact table but only update the contacts that are in my custom view?
|
|
|
|
|
This example is based on the AdventureWorks database;
UPDATE HumanResources.Employee
SET Title = 'updateFromView'
WHERE EmployeeID IN (SELECT EmployeeID FROM HumanResources.vEmployee)
The select statement gets a list of ID's in the view. You can then update all the records in the table that have this particular ID. You'll need something to identify each record individually for this to work. If there isn't, then try to add the primary key to the view. If there is no primary key defined in the base-table, add an identity to fullfill this role.
Good luck
I are troll
|
|
|
|
|
Hi,
I have a table which looks like the following.
OriginalTable
OriginalTable_Id(pk) OriginalTable_Name(unique)
---------------------------------------------------
1 NewColumnName1
2 NewColumnName2
...
I need to make it look like this table below dynamically:
NewTable
NewTable_Id(pk) NewColumnName1 NewColumnName2 ...
---------------------------------------------------------------
I know I need to create a new tempory Table (using tempDB?), but I don't know how to get the values in the table to be the new column names.
I have this at the moment:
CREATE TABLE NewTable
(
NewTable_Id INT PRIMARY KEY,
???
)
???:
Would it be something like:
SELECT OriginalTable_Name FROM OriginalTable
Any tutorials or advice would be greatly appreciated.
Thanks in advance.
hmmm pie
|
|
|
|
|
Have a look at pivot clause here[^]
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
