|
You need to contact the hosting provider to ascertain whether or not you can have full access - although this sounds like the server instance is not properly set up to accept remote connections to the SQL instance.
|
|
|
|
|
Hello
Kindly tell me is it necessary to use
SET XACT_ABORT ON
in sql transaction or not?
Regards
|
|
|
|
|
try doing some reading [^] before jumping in. The second one seems to be interesting.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Basically, XACT_ABORT is a hint to SQL Server about the behaviour of a transaction if an error occurs. If XACT_ABORT is set to ON then the transaction will be rolled back. Now, why did I say it's a hint? The answer is because if XACT_ABORT is set to OFF, there's no guarantee that the transaction will roll back - it may or may not (depending on circumstances) choose to just roll back the statement that caused the error, allowing other parts to complete - or the entire transaction may be rolled back.
|
|
|
|
|
|
Here is code
<br />
SqlConnection connection = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename="D:\Working Directory\Win Application\Nakoda\Development\Bin\Database\cERP.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True");<br />
<br />
ServerConnection srvConn = new ServerConnection(connection);<br />
srvSql = new Server(srvConn);<br />
<br />
if (openBackupDialog.ShowDialog() == DialogResult.OK)<br />
{<br />
BackupDeviceItem bdi = default(BackupDeviceItem);<br />
bdi = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);<br />
<br />
BackupDeviceItem bdid = default(BackupDeviceItem);<br />
bdid = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);<br />
<br />
string db = cmbDatabase.SelectedItem.ToString();<br />
<br />
Restore rs = default(Restore);<br />
rs = new Restore();<br />
rs.NoRecovery = true;<br />
rs.Devices.Add(bdi);<br />
rs.Database = db;<br />
rs.SqlRestore(srvSql);<br />
<br />
<br />
I Got Exception
Microsoft.SqlServer.Management.Smo.FailedOperationException occurred
HelpLink=http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
Message=Restore failed for Server '\\.\pipe\1FF7F0B1-18D8-42\tsql\query'.
Source=Microsoft.SqlServer.SmoExtended
Operation=Restore
StackTrace:
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at DatabaseBackup.SQL_Backup_Restore.btnRestore_Click(Object sender, EventArgs e) in D:\Working Directory\Win Application\Nakoda\Development\Source\DatabaseBackup\MSSQLBackupRestore.cs:line 186
InnerException: Microsoft.SqlServer.Management.Common.ExecutionFailureException
Message=An exception occurred while executing a Transact-SQL statement or batch.
Source=Microsoft.SqlServer.ConnectionInfo
StackTrace:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
InnerException: System.Data.SqlClient.SqlException
Message=RESTORE cannot process database 'D:\WORKING DIRECTORY\WIN APPLICATION\NAKODA\DEVELOPMENT\BIN\DATABASE\CERP.MDF' because it is in use by this session. It is recommended that the master database be used when performing this operation.
RESTORE DATABASE is terminating abnormally.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=3102
Procedure=""
Server=\\.\pipe\1FF7F0B1-18D8-42\tsql\query
State=1
StackTrace:
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
InnerException:
|
|
|
|
|
kirankkk2009 wrote: Message=RESTORE cannot process database 'D:\WORKING DIRECTORY\WIN APPLICATION\NAKODA\DEVELOPMENT\BIN\DATABASE\CERP.MDF' because it is in use by this session. It is recommended that the master database be used when performing this operation.
So you are connected to the database you want to restore - fail.
Do what the exception advises you to, connect to the master database and restore your database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
After 1000 this will reset counter for specific term to 1 again. Will this stored procedure be 100% reliable to give unique id every time. There are multiple users accessing the web application that may call this stored procedure.
ALTER PROCEDURE [dbo].[get_next_id](@Term int,@ID INT OUTPUT) AS
SET NOCOUNT ON
DECLARE @Current int;
SELECT @Current=[ID] FROM [IdGenerator] where Term = @Term;
IF @Current<1000
BEGIN
update IdGenerator SET @ID = ID, ID = ID + 1 where Term=@Term
END
ELSE
BEGIN
update IdGenerator SET @ID =1,ID = 2 where Term=@Term
END
One option is to use identity column but that is out of question as to generate unique Id I have to add certain text to each number as that is the part of business requirement. Like add 1001 or 1002 or 1003 etc. in front of numbers. This stored procedure will give me the unique number.
Criticize it / Suggest any alternative .
|
|
|
|
|
Criticise - not a problem this is a dumb idea.
Record identifiers are just that they identify the record, the business has no input into how they are created, maintained of contain.
On the other hand a code used by the users to identify a record is completely up to the business and they can ask for any rules they like
Why are you resetting after 1000 and how can that be unique?
Suggest - also not a problem
Have 2 fields, recordID which is an identity field and record code which store the business defined identifier.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft has given you some good advice.
In terms of your original question, yes this will be reliable if you serialize the transactions.
Incidentally, this will reset before 1000, not after. I assume that is what you want, so you are generating your business numbers in the range 1001... to 1999...
|
|
|
|
|
I have a stored procedure that pivots data so that I have a row containing an ID column and a number of other columns which are named by a Product Code.
For example the original table would look something like this :-
OrderId
ProductCode
Quantity
with data like so...
1000, ProdA, 100
1000, ProdB, 200
1001, Prodc, 50
1002, ProdB, 200
Thus my stored procedure would produce a result set of :-
OrderId, ProdA, ProdB, ProdC
----------------------------
1000, 100, 200, 0
1001, 0, 0, 50
1002, 0, 200, 0
So far so good.
Now, the problem is that I need to attach this to the result of a view which contains other related details.
I've researched on the internet, but all the examples I find assume I know the structure (field names) that the Stored Procedure will return. All I know for sure is that there will be an Id column of type BigInt (which is common to both the view results and the Stored Procedure results), and a varible number of columns, with names that change, that are of type Decimal .
Any pointers, gratefully received.
Steve Jowett
-------------------------
Real Programmers don't need comments -- the code is obvious.
|
|
|
|
|
Hi,
You could try inserting the stored Proc's results into a temp table and joining your view from there.
|
|
|
|
|
Q1: Is the "other related data" related to the Order ID or to the Products ?
Q2: I know I could get alot of grief for this but, What about a Temp table ?
Create your Pivot Analysis, store it in a temp table, then process this temp table to add any additional associated data.
I could probably offer a better answer if Q1 was answered.
Regards,
David
|
|
|
|
|
David Mujica wrote: Is the "other related data" related to the Order ID or to the Products
The OrderId is the common key.
David Mujica wrote: Create your Pivot Analysis, store it in a temp table, then process this temp table to add any additional associated data
Can I define a temp table without knowing the column names or the number of columns?
My other thought was to create a .NET Extension and have the produce the data resultset required and return a datatable. (never done it before, but I am will to try.)
Steve Jowett
-------------------------
Real Programmers don't need comments -- the code is obvious.
|
|
|
|
|
While you can join the 2 sets of data (you say the productid is common) by building sub selects and inner joins it will look horrible and will be difficult to support.
Go with the temp table or the table var, I often use a table var for pivot stuff because you are usually using dynamic SQL to build the pivot and therefore building the target table code is trivial. Also pivot data tends to be small in volume!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
More reason not to use stored procedures. Try a table-valued function instead.
|
|
|
|
|
hi to all,
I have an application that requires a lot of calculation, there are a lot of clients that uses that application having separated DB’s. Now I am going to develop another web-application that will get the records of all those databases and on the basis of filter will display the results. I want to ask you a question that
1. Should I change the structure of the application in which all of my clients share a same database and from where I could get all the records I needed and perform calculation on them?
2. Would I keep the all databases separate (as it is condition) and develop a service that will fetch the records from the entire database into one from where I would perform my calculation and display the data.
Do also tell me if there is some other solution to this application architecture.
|
|
|
|
|
What is the rationale for every client to have their own database? Would always be easier to have all of the information in one database but by no means a must - it would depend on the design and needs of the application.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
My vote is for a centralized DB where you store all of the data. This has the advanatage of 1 backup, transactions, no import logic and overall less headaches.
The only advantage of each client having a local DB is that they can operate while disconnected from the network.
Do your clients have a reliable connection to a central DB ?
Good luck.
|
|
|
|
|
Thnx for your reply, but what if the Database grows big coz of the data and the client. Also the backup will also become an issue due to larger size. All the users databases are also managed by us.
|
|
|
|
|
Unless there are very good reasons not to do so, I would go for a central DB. It may take some doing to get there, but it should be worth it. Remember, your app will grow, and maybe the number of clients will increase too, so delaying the decision probably will only make matters worse.
Good reasons not to do so would be special circumstances, such as a relatively slow network. If so, I'd suggest to remedy them.
|
|
|
|
|
adilsardar wrote: Would I keep the all databases separate (as it is condition)
If that's the condition, then yes, keep them separate. Who came up with this restriction, and why?
I can imagine some clients not feeling happy about mingling their valuable data with the data of their competitors. It would also mean that things go over "a line" outside the building, and some customers don't trust SSL.
Do you need live access to their database, or could you suffice with a backup that they send you? Have you explained to them in detail what information you're going to pull from 'their' database?
Do you need much of their data? Often? If so, it might be a better idea to replicate or synchronize the data. If not, could the query run during the night? Could it run at a lower priority when run during the day?
Happy thinking
I are Troll
|
|
|
|
|
The database contains approx 260 tables and what we need is the data from appox 150 tables.
Is ssis packages are good solution if we use separate db's and then sych them.?
|
|
|
|
|
adilsardar wrote: The database contains approx 260 tables and what we need is the data from appox 150 tables.
That's over 50%. How much data would that be, in megabytes? And how often will you run this query?
adilsardar wrote: Is ssis packages are good solution if we use separate db's and then sych them.?
I don't have personal experience with SSIS, but replication sounds good.
I are Troll
|
|
|
|
|
Probably one database.
As for making calculations, it depends on the calculations. Are they such that you can keep a summary table from which to calculate them?
|
|
|
|