|
yes and infact I've got the solution as well. here it is:
CREATE PROC usp_CONFIGURE_ReplicationSizeForBlobs
@NewSize int = 100000000
/*
* Sets the 'max text repl size' instance wide configuration setting
* that governs the maximum size of an image, text, or ntext column
* in a replicated table.
*
* Example:
exec usp_CONFIGURE_ReplicationSizeForBlobs default
**********************************************************************/
AS
print 'Old size'
exec sp_configure 'max text repl size'
print ' Setting new size'
exec sp_configure 'max text repl size', @NewSize
print 'Reconfiguring'
RECONFIGURE WITH OVERRIDE
print 'New size'
exec sp_configure 'max text repl size'
|
|
|
|
|
Is there any way by which I can run script stored in .sql file on Oracle
using .net code?
Nana
|
|
|
|
|
Hi,
Any Sql query for Export data from MS SQL SERVER to abc.txt file
|
|
|
|
|
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
|
|
|
|
|
command to import sql tables to cd.
|
|
|
|
|
bhawna_k wrote: command to import sql tables to cd.
?? 
|
|
|
|
|
You can copy the database files and attach that in another server. Or use import export wizard and copy the tables from one server to the destination file you want. You can use csv, access files. Then copy the file to CD 
|
|
|
|
|
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?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
