|
Hi,
I tried to clean one of my database table ( having 1.427 billion records) using the delete statement.
But the statement is not completed after 30 hours of execution, so i stopped the query.
now the transaction log file size is 690 GB.
Database is up, but i cant do anything with this table.
I tried this
DBCC OPENTRAN ( dbname ) and it is showing my delete statement as open transaction.
What should i do?
My small attempt...
|
|
|
|
|
Don't know if you still have this case, but if you have explicit transaction, you should rollback it. Note that the rollback may take awhile (even more time than the delete operations).
|
|
|
|
|
Finally i did that.If we have any pending OPEN TRANSACTIONS in the database( SQL Server 2005) , we can ROLLBACK that using the following script.
USE [ dbname ]
Go
BEGIN TRAN
DBCC OPENTRAN
GO
DBCC OPENTRAN
ROLLBACK TRAN
GO
Pending OPEN TRANSACTIONS can be viewed by
DBCC OPENTRAN ( dbname )
My small attempt...
|
|
|
|
|
Hi All
I am writing an sql query for this situation to alalyze my data.
Table: Test
Actual Data
id key task
1 101 Ho
2 101 Dm
3 101 Dm
4 101 Ho
5 101 Ho
6 101 Ho
7 101 Ma
8 101 Ma
9 101 Dm
Required Output
id Key task
1 101 Ho
2 101 Dm
4 101 Ho
7 101 Ma
9 101 Dm
I want to compare the consecutive rows "task" column and want to display only one of those consecutive rows if the "task" column is same.
How can i achieve this in Microsoft SQL Server
I achieved this in Oracle by
SELECT routekey,collectionid,time,speed,heading,culmativedistance,latitude,lathemisns,longitude,longhemisew,satellitesinview,satellitestracked,satellitestatus,hdp,fixquality,snr,checksum,lowsignalenv,nogpssignal,nosatellitesinview,nulldatarpt,gpstrigger
FROM tester
WHERE prev_gpstrigger != gpstrigger
OR prev_gpstrigger IS NULL
ORDER BY collectionid,routekey;
Thanks
|
|
|
|
|
Can you describe more your output result which you want to get?
Or do you need to get only one value from field task.
Example:
Your result want to be like this:
id key task
1 101 Ho
2 101 Dm
7 101 Ma
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
hello,
I'm currently in process of migrating Oracle db and tables to SQL Server 2005. Is there any ideas of best way to do this?
My first thought was to somehow automate the db, table, views and stored procs creation. But then how tdo I run those SQL scrips in SQL Server db...since the commands are slightly different in syntax.
Second, any data migration tools that will extract and map from Oracle to a SQL Server db?
I'm open to any other ideas and thoughts also! Thanks
|
|
|
|
|
On my last job, my first task was to "warehouse" the data in an Ingres database into an SQL Server database.
I wrote an application that got the table and index schema and cobbled up the corresponding SQL Server commands and ran them on the target database.
I then had another application that would copy the data over. This later became a Windows Service that copied over any changes periodically, this was not a one-time copy of the database, but an on-going warehousing process.
Gotchas:
On SQL Server a one-byte value (tinyint) is unsigned, but on Ingres it's signed, so I had to use smallint instead. So check to see what Oracle uses.
The range of the Date type on Ingres was wider than SQL Server's, so I had to compare each Date value with SQL Server's range.
What I did when I found an out-of-range Date value was log it and replace it with SQL Server's min or max as appropriate.
I wound up with the ability to specify range checking for other types as well.
Check Oracle's range of Dates.
As for stored procedures, you're likely stuck. (Yet another reason not to use stored procedures  ).
|
|
|
|
|
I wouldn't do this based on scripts. Instead I would create a program that reads the schema data from Oracle and then constructs create statements to SQL Server.
You could for example first create objects, then load data and then add constraints, triggers etc. This way the data won't get modified on the conversion.
Also there are plenty of tools for this purpose. One (free) for example: SQL Server Migration Assistant for Oracle (SSMA for Oracle)
[^]
|
|
|
|
|
I have a stored procedure that creates a temporary table and populates it by merging data from 2 other tables.
I now need to expose the results to an MS Access 2003 database, and thought about creating a view in SQL Server and adding the view to Access as a linked table.
My problem is that I do not know how I should reference the stored procedure from a view.
Any help is appreciated.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
I'm pretty sure you can't create a view from the output of a stored proc - that's not really the idea of them.
Why not create a view that merges the data from the 2 tables and change the proc so that it selects from that? You can get rid of the temporary table that way.
Regards,
Rob Philpott.
|
|
|
|
|
Thanks for the reply. I have done what you suggested and it works a treat.
Thanks
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
i want to know about the the start of sql query parser project
|
|
|
|
|
For starters, it will be a huge project. Use Google to get info about how parsers are created, for example: Click[^]
|
|
|
|
|
Why? Parsing SQL is best left to the actual database engine you're using.
|
|
|
|
|
Hi all,
Iam using Sql reporting services .iam having a field Invoicereq in one table.I want to group only the invoicereq="y" and get the result..
I have given the group field as invoicereq="y" in reporting services and got the result but i am getting a null value along with it .
(totally only one row but the result produce is 2 rows (one row null))
how to eliminate nuull values?
in where condition i cant use the invoicereq="y" option because i want all th rows from tat table but group tat separate
can anyone help me?
|
|
|
|
|
Exclude the null records from your query using
where
table.column is not null
Another option, if you want the nulls returned is
isnull(table.column, 'null')
To which you could then report on.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.
|
|
|
|
|
i want to use it in a dropdown list where if I select a country value from dropdwonlist I get corresponding state and when I select a state corresponding city (Table in sql server)
|
|
|
|
|
OK, firstly though, how are you doing the GUI (WinForms C#, WebForms etc.) etc... ?
(edit - sorry missed the Sql Server bit)
Regards,
Rob Philpott.
|
|
|
|
|
Hi
In SQL 2005, I am trying to get a csv strin like "1212,1122,1123,1124" out of following xml.
<group>
<id>1212
<id>1122
<id>1123
<id>1124
Please advise how it can be done?
Thanks
Pankaj
|
|
|
|
|
The data you posted isn't in xml format so it's quite hard to say what the correct way would be. But let's suppose you have something like in the following example, then your query could be
declare @testdata xml
set @testdata =
'<Root>
<element>1212</element>
<element>1122</element>
<element>1123</element>
<element>1124</element>
</Root>'
SELECT @testdata.query('for $e in /Root/element return concat(string($e),'', '')')
|
|
|
|
|
Thanks. With little tweak I can get desired result.
|
|
|
|
|
You're welcome 
|
|
|
|
|
Hi,
I am calling a sql server stored procedure from a C# app. I have my C# app writing to the console, but would like my stored procedure to do the same. In Oracle I can do this, but I've not been able to find a way in SQL Server.
Thanks in advance for any sugguestions...
|
|
|
|
|
AFAIK there's no such mechanism than in Oracle to write to console. However, you could for example write to SQL Server errorlog, create a table to write to or send information by email.
|
|
|
|
|
If I raise the error, will it return to C# with the error?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
