|
two way this can be done one wizard / command
1. from enterprise manager use import/export
2. sql command "bcp" (for details on syntax & usage you can refer SQL Server Online help)
Regards
KP
|
|
|
|
|
You can create script files if you just want to export the structure. If you want data also, then either you can copy the database files or take a backup, run database create scripts on target and restore backup on target.
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
What is your opinion about using temporary tables in SQL Server stored procedure. Is it a good practice ?
If yes consider the following
1. I am using temporary tables in stored procedure and accessing this procedure from ASP.NET page. If more than one user came to the page at same time, it is showing table already exist error. But I have read that SQL Server will attach some timestamp or some session value with the temporary table name what we given. Then there is no chance or getting this error. But still I am getting the error. Where am I going wrong ?
To solve this error I am passing the session id from page and appending that with the table name. Is it the rite way to overcome this problem ?
If no
1. Then what is the alternate solution for temporary tables ?
I am totally 
|
|
|
|
|
Hi
After using Temporary table, you need to drop the table before using it again. Then you will not get that error.
Harini
|
|
|
|
|
Harini Krishnaswamy wrote: After using Temporary table, you need to drop the table before using it again. Then you will not get that error.
It's not like that harini, Actually I am dropping the table after use
Take the following situation
1. I created temporary table in stored procedure
2. If two users are accessing the procedure at same time it will show error. Because the first called procedure already created temporary table and it is not yet finished it's execution to drop the table. At the same time second procedure is also trying to create the same table and it will show the error. I need to overcome this.
|
|
|
|
|
Hope you are trying to create a table not in tempdb.
Check whether the tbale is already present before executing "CREATE TABLE"
i.e.
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'tblA') CREATE TABLE tblA (col1 varchar(4), col2 ........)
Regards
KP
|
|
|
|
|
are you creating the temporary in "tempdb" ?
Local temporary (#table_name) tables are for current session; global temporary (##table_name)tables for all sessions.
Temporary tables are automatically dropped when they go out of scope.
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
Regards
KP
|
|
|
|
|
Thanks Krish
I am using ##temp. So as you told it will be treated as global temporary table. Is it ? From my procedure I am calling 10 sub procedures. In all these procedures I am using this temporary table. So when will it's scope ends ? After finishing the procedure execution, it's not dropping the table automatically.
|
|
|
|
|
If you call the 10 procedures for the main procedure you don't need the ##temp a local #temp will work and will fix your problem. This assuming that the connection/ session is new on each page view or call to the main procedure.
|
|
|
|
|
DooDooo wrote: What is your opinion about using temporary tables in SQL Server stored procedure. Is it a good practice ?
They can be. Once, by the introduction of temporary tables, reduced the query time from 20 minutes down to 7 seconds.
DooDooo wrote: If more than one user came to the page at same time, it is showing table already exist error.
Are you using the same connection for all pages in you ASP.NET application. You should not share a connection between page requests. A connection object should be created, opened, the query performed then the connection closed and allowed to go out of scope. The connection should not be stored anywhere for later use - only the connection string is needed.
|
|
|
|
|
Colin Angus Mackay wrote: re you using the same connection for all pages in you ASP.NET application
Never. I use separate connection. What I mean is when more than one users coming to same page it is showing error.
Like
I have one aspx page, let it be a.aspx. If you and me are accessing the page at same time it is showing the error. In page load I am calling one procedure that will make temporary table. Then I am getting error. What's your opinion on passing the session id from page to storedprocedure and creating a table in that name. ?
|
|
|
|
|
DooDooo wrote: What's your opinion on passing the session id from page to storedprocedure and creating a table in that name. ?
I don't have an opinion on that topic. I've never tried it. Off the top of my head I can't see any difficulties with it.
|
|
|
|
|
Colin Angus Mackay wrote: Off the top of my head I can't see any difficulties with it.
Thank you
|
|
|
|
|
You can try the same thing with table variables and see if you are having the same problem. At times the table variables are also part of best practice on top of temporary tables.
Sharp
Happy Programming
|
|
|
|
|
i use oledbDataReader for select record from a MS Access database file.
how can i move previous record (or next, last and first record)?
there isn't numeric filed in my table and i don't want use dataset
[i can select first record by "SELECT TOP 1 * FROM table1"]
|
|
|
|
|
hamid_m wrote: use oledbDataReader for select record from a MS Access database file.
how can i move previous record
DataReader is forward only. You can't get the previous record. For this use dataset.
|
|
|
|
|
dataset wast my time
what about recordset?
|
|
|
|
|
hamid_m wrote: what about recordset?
That isn't available in ADO.NET.
If you need to go backwards and you don't want to use a DataSet then you will have to write your own caching mechanism.
Curiously, I've never had the need to be reading rows from a database then needed to re-read some previous row again. What are you doing that requires that you do that?
|
|
|
|
|
i have a table with many records and i have a form that report it to user.
when form loaded, it read last record and show it to user and user have ability to edit, new, delete and move next (disable this case), previous and last and home.
my table is very much so i wast time if i want use dataset(and fill all) therefor i have to use datareader.
|
|
|
|
|
hamid_m wrote: when form loaded, it read last record and show it to user and user have ability to edit, new, delete and move next (disable this case), previous and last and home.
For this situation the Generic Data Classes (that is dataset etc) are ideal. DataReader is one of the *Managed Provider classes* and just reads the rows in forward only direction.
hamid_m wrote: my table is very much so i wast time if i want use dataset(and fill all) therefor i have to use datareader.
Although datasets are slower than datareaders, connecting to the database over and over again, greatly reduces the application's performance. Memory resident data in the form of DataSet and related objects will be a better choice to work with the data. Another good feature of dataset is that they are not specific to any database, and work with any sort of database in the same manner. You should spend a little time to explore DataSet and related classes (that are DataRelation, DataTable, DataView, DataColumn, DataRow etc); other wise the chances of user time wastage by the application may arise.
Regards.
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
thank you uroojkhan, my application will work on network (multi user) so offline databse (dataset) is not perfect for it! is it?
|
|
|
|
|
I have problem to store audio file in database. I am using vb.net & sqlserver. In my project i want to store audio file in database and after I want to retrive this file. I can also want to give some sound effect after retriving(playing from database) the audio file like bass.. and any other. Please help me it's very urgent.
can u give some examples for sql server 2000. I am tried to store audio file using image data type it store data successfully. But in table it looks as some code(number, hex or binary) format not as actual name of that file. and also i can't retrive(play) this file through my vb.net FORM which is just like media player.please help me.it's very urgent.
Thank's  ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
shrikant
|
|
|
|
|
Why did you delete your original question? The one that I answered here[^]
shriku24 wrote: But in table it looks as some code(number, hex or binary) format not as actual name of that file
Because you didn't ask to store the name of the file, you asked to store the file in the database. What you are seeing is the actual file.
shriku24 wrote: i can't retrive(play) this file
I can't help you play the file. This question is in the SQL forum. I can only help you store and retrieve the file from the database. When you retrieve the file from the database you have it already as a raw byte array. From that point onwards you can do anything you want with it.
|
|
|
|
|
Colin Angus Mackay wrote: can only help you store and retrieve the file from the database
Which datatype we need to use for storing files in database ?
|
|
|
|
|
DooDooo wrote: Which datatype we need to use for storing files in database ?
byte[] in .NET
image or varbinary(max) in SQL Server
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
