|
I was wondering if someone knows how to use a DBI to exec a sql script and copy the data collect to an Excel Spreadsheet?
|
|
|
|
|
What Database you ues?
If you use sql server,You can do it with a simple export with Intergration Service of SQLServer.
|
|
|
|
|
|
Urgent help Please!
I have a table exist in two database on two different server in sql server. I want to get the data of table from one table and another. When I am using OPENROWSET command it through error. I don't know what it mean. Is there any way to migrate the data. One problem is that I have a column for multi language when i migrate the date the multilingual colum show ??????? and not the original one.
thanks in advance
|
|
|
|
|
It would greatly help if you post the command you tried to use and the error message you got.
|
|
|
|
|
Hello!
I m planning to use PostgreSQL but I have one doubt how can i take a backup.
I check some of website they refer dump command that also for LINUX How can i take backup in XP and how to restore after partition plz help me.
and i want to know where that backup file is stored.
Thanx's in advance.
|
|
|
|
|
PostgreSQL uses a little bit different approach than most of DBMS's do. It creates a file containing SQL commands which can be again executed back to an empty database. This will lead to the situation where the database was when the dump was made.
Dump is taken using pg_dump or pg_dumpall utility and the result will be echoed which then again can be redirected to a file and to a place you choose.
For more info, look: SQL Dump[^]
Addition:
Of course every database can be backed up by taking copies from the files using operating system, but in order to do that, the database engine must be first shut down in order to get a consistent image.
modified on Tuesday, January 20, 2009 12:56 PM
|
|
|
|
|
Ya I tried it's working.
But I didn't find backup file.
Know I am format my hard disk so I'll copy that file in some removable devices after installation of XP I want run that backup file so How can i do that one ???
pass 5 hour back I am trying can't do it plzz help me.
|
|
|
|
|
non-bugging Errors wrote: But I didn't find backup file
What was the command you issued?
|
|
|
|
|
|
pg_dump is a utility, so you run it at command prompt. For example:
pg_dump -Fc -b -v -f "C:\std1.backup" samp
|
|
|
|
|
Thanx's it working with database log-in parameter.
|
|
|
|
|
Good to hear, you're welcome
|
|
|
|
|
Hello everyone,
If in a store procedure, it is using SET XACT_ABORT ON (auto rollback transaction when there is error). Currently, I just rely on the output of the store procedure (when there is any errors inside) to handle success/error at client side (e.g. ADO.Net client), now I want to add RAISEERROr statement to throw the error outside to client. But I can not think of how to add RAISEERROR and fill in the related parameters for this function. Does anyone have any ideas?
Here is my pseudo code.
SET XACT_ABORT ON;
-- DML operations
commit;
thanks in advance,
George
|
|
|
|
|
|
Thanks J4amieC,
My question is different.
Whether RAISEERROR must be used in catch block? Any ideas or comments?
regards,
George
|
|
|
|
|
Using RAISERROR[^].
Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )
|
|
|
|
|
Thanks Hamid!
I like this link you recommended!
regards,
George
|
|
|
|
|
Hi all,
I have a table whick have got 2 fields [id - info], for example :
id info
1 A
2 B
3 C
...
Now, in my program, there is an array of id in program, ex : [1, 3, ...]. Now, I want to query the table to get the corresponding array of objects[id-info] like that : [[1, A], [3, C]...]. I knew some ways, but I think theys are still optimized, as following, 2 ways I knew :
- foreach array of id, get info correspoding the id, then add to the result array => each id, I have to query the DB 1 time => not good.
- generate a big select query with WHERE Condition is like (where id = 1 or id = 2 ...) => query DB 1 time, but the condition expression is complex to verify for DB.
- generate a big select query with Where condition is like (where id in[1, 2, 3]) => similair to 2rd way, not very good.
I think there are still better way, could you help me.
Currently, I am using C# with MSSQL, but I will change to MySQL in several months.
Thanks in advance.
|
|
|
|
|
You could modify the in list to a XML query and use that, but since you're changing ty MySQL that most likely won't work in there. Using IN (value1, value2, ...) is supported by all DBMS although each DBMS may have restrictions in the amount of values. However, that's a working solution and the optimizer tries to find the best permutation for execution.
If the in list is large, this could be a modeling issue, but without anymore info about the problem, it's hard to say.
|
|
|
|
|
Yeah, I am agree, using IN expression is the best way in those three ways. But, in algorithm view, if we can convert the array of Id to a temporary table which have only field "Id", then we inner join two table on "Id". I think if the conversion is possible, that is the best solution, I have not much experience on SQL and programming, I still hope find a better way.
|
|
|
|
|
That's true. If you would have the data in db (in some table format) that would be the best for the query itself. However, how would you get the data to the temporary table. In worst scenario you add it row by row. Little better if you can add them simultaneously, but still you're creating a table and populating it and both of those are time and resource consuming operations. So in overall even if the last query gets simplified, the overall throughput may be worse.
This is exactly why I mentioned the modeling of the db. If you could prevent those dynamic lists or make them significantly smaller, it would have a great impact to your query. But in order to do that, you would have to find common "feature" from the values, which you should then store in lookup tables. I can't say if this is possible at all or how it would be implemented in your case since it would require a whole lot of information about the issue.
One possibility you could use is to create a procedure which gets an array (or user defined table) of values and then uses a those values in join using the original query. I wrote a brief article on that: http://www.codeproject.com/KB/database/TableValuedParameters.aspx[^] but the downside is that it's SQL Server specific.
|
|
|
|
|
Hello everyone,
I am not sure my following code must be put between "begin catch" and "end catch"? Or they could be in any where in a store procedure -- e.g. outside "begin catch" and "end catch"?
IF @@ERROR <> 0
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END -- end of IF @@ERROR <> 0
thanks in advance,
George
|
|
|
|
|
1.
Read the documentation on SQL Books Online about BEGIN CATCH ... END CATCH
edit: I know you're too bone-idle to search the internets, so here you go: http://technet.microsoft.com/en-us/library/ms175976.aspx[^]
2.
Try it out for yourself, observe the behaviour, feel free to ask a question about a specific aspect of the behaviour observed if you need to.
|
|
|
|
|
Thanks J4amieC,
The link looks great! But does not answer all of my questions.
My scenario is different. Some store procedure I am maintaining is simple, just a wrap of a single insert/delete/update statement, and they are not in a transaction -- they also do not wrapped in try/catch.
In this situation, what is your suggested solution to return meaningful error information to client (e.g. an ADO.Net client program in my scenario)?
regards,
George
|
|
|
|