|
try making Sql Server UDF (User Defined Functions)
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
There are a number of methods to achieve a semblance of this, UDF is one, in the above simplistic sample on you could create a view that calcs the amount column, you could create a table variable to hold the equivalent of the view, this would only be valid for the current proc whereas a view persists to the database.
I am in the habit of creating a view for my transaction tables, creating any calculated columns (amount) and joining to the foreign key tables (an example would be an OrderLine to Product table on the ProductID to get the product name in the view).
|
|
|
|
|
I agree with you View will be a good option
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
I would go with the view approach, a UDF can add a large overhead to a simple query, slowing it right down - much more than you would expect.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
HI
I want to Access SQL Server database from network. What are the configure made in sql server ... If have any step please share with me ... or give proper Url for this scenario...
Thankyou
|
|
|
|
|
What problem are you facing.
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
Take a look at connectionstrings.com.
What you have asked cannot be answered by a simple forum post, you need to do some reading and possibly a specialist site may help, SQLServerCentral.com is very useful.
This search of articles[^] turns up some interesting titles as well
|
|
|
|
|
Hi..
1) I was trying to create a IF Condition in stored procedure where i need to compare if old value and new value is present/not present in the database. Dont know the Syntax to write the above said query in Stored procedure.
2) I have created a variabl name 'add', 'change', 'remove'. The old value and new value are two different columns in the table. What i want is : If old value is null and new value is present then 'add' get incremented by 1, if old value is present and it has been modified in new value and if the old value string does not match with the new value then 'change' get incremented by 1, if old value is present and it has bee deleted in new value then 'remove' get incremented by 1.
Kindly help me out as early as possible as these is where i stuck for the last few days and i need to resolve this soon.
Abhishek
|
|
|
|
|
Congradulations, you found the correct forum to post a database question to. Now remove the one in the ASP.NET before someone answers there.
only two letters away from being an asset
|
|
|
|
|
IF( EXISTS(SELECT ?? FROM [Table] WHERE ??? ) )
And this has had you stuck for days?  
only two letters away from being an asset
|
|
|
|
|
 Can you post what your database table looks like, and when you want to run this "proces" ie. is it when you update a row? How do you currently update the row? Is there just Old Value and NerwValue in the table?
This question has a lot of detail, but is very confusing. As usual, please post what you have so far in the way of code.
|
|
|
|
|
My application currently opens its data base connection when it starts, and closes it when it exits. The application runs for long periods (days or even weeks). The problem lies in that it accesses the data base infrequently. Accesses may be minutes or even hours apart in time. In other instances, there may be several accesses within a few milliseconds of each other.
At some sites, it appears that the data base server (SQL Server 2005) is dropping the connection because my application is not keeping up a steady traffic of operations.
One option would be for me to change my code to open the data base connection, perform the operation, and close the connection, for each and every data base transaction.
This approach seems clumsy, however. Does anyone have any better suggestions?
Software Zen: delete this;
|
|
|
|
|
Gary Wheeler wrote: Does anyone have any better suggestions?
No. AFAIK open-use-close is the normal pattern. And connection pooling takes care of most performance issues that result from frequent opens/closes.
[ADDED] The above applies when using .NET Framework.[/ADDED]
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Local announcement (Antwerp region): Lange Wapper? Neen!
modified on Thursday, October 8, 2009 7:47 AM
|
|
|
|
|
Thanks, Luc!
I had a feeling that was the case, but thought I would make sure.
Software Zen: delete this;
|
|
|
|
|
you're welcome.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Local announcement (Antwerp region): Lange Wapper? Neen!
|
|
|
|
|
will always prefer disconnected architecture means connect to db when their is needs to connect to db mostly in CRUD operations.
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
You could consider some sort of connection pooling strategy. Open-use-close is not very efficient when you are accessing the database very frequently. It is more efficient to pool and reuse open connections in this circumstance. One technique that connection pooling libraries use is to test the connection before returning it from the pool. Then if the connection is bad (e.g. timed out by the database) you can throw it away and open a new one. That way, if your application is making lots of database accesses you avoid the overhead of opening and closing connections all the time. When your application idles and does not access the database for a long time, the connection can be dropped and a new one will be opened, "invisibly" from the point of view of the code using the connection.
You don't say what language of framework your application uses, but there are connection pooling libraries available for .NET, Java, Python, Ruby and many others.
|
|
|
|
|
I'm using C++/MFC (stop laughing  ). The data base activity is an 'add-on' to a process control application. I'm thinking of moving the data base handling off into a separate thread of its own, so that it can do the open/CRUD/close without affecting the other stuff that's going on.
Software Zen: delete this;
|
|
|
|
|
Instantiate the connection once, but open and close it only as needed.
|
|
|
|
|
Hello people!
I am not sure this is the right place for this question or not but i try my luck, sorry if it not. So, the problem, we are writing a VC++ program in which we have to make some BLOB updates. Now, this program is multi-threaded, there are 2 threads which perform DB queries (thorough Oracle XE client) sometimes simulteniously but they both use the same connection, one of these threads performs BLOB updates, the other one never works with BLOBs, it only calls a stored procedure which does some updates on a table (and yes, this table can be updated by both of these threads at the same time, but they never work on the same columns simulteniously). This seems to work well most of the time but (there's always a but, isn't there) time to time we get errors like "The update cannot be performed because the database has changed" or "ORA-22990: LOB locators cannot span transactions Error source: 'OraOLEDB'". Our guess is that the problem is that the same table/row is being updated by the 2 threads, but how to overcome this? The problem is that one of these threads, which doesn't do BLOB updates, has to do its work at specific times, so saying, "while a BLOB update is in progress, don't let anyone else touch the DB" is not a solution, since the BLOB update can take a lot of time and not allowing the other thread to perform its queries will lead to problems.
I have to admit that i am not the "DB guy", but i visit CodeProject frequently and since it has a great forum for DB-related stuff, i thought i ask, maybe it has a pretty obvious and simple solution we don't know about and if so i can then forward it to the DB guys and we can fix the problem.
So, anyone can share her/his wisdom and help? Thanks in advance for any answers.
> The problem with computers is that they do what you tell them to do and not what you want them to do. <
> Sometimes you just have to hate coding to do it well. <
|
|
|
|
|
Good Day All
I have a File Format Defined like this
9.0
4
1 SQLCHAR 0 100 "," 0 ExtraField ""
2 SQLCHAR 0 100 "," 1 Descr SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 2 ABREV SQL_Latin1_General_CP1_CI_AS
and i use it like this
BULK INSERT dbo.TBL_CMPS FROM 'C:\\UNISA_IMPORT\\Final_Import\\Campuses.csv'
WITH (
FORMATFILE = 'C:\Format.DAT',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n' );
and my Table Defination is like this
CREATE TABLE [dbo].[TBL_CMPS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DESCR] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ABREV] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
and my Error is
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "C:\Format.DAT".
Thank you
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I think if you change the column count after 9.0 to 3, it should work.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
i get this
Msg 4862, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\Format.DAT" could not be read. Operating system error code (null).
I have give a Full Permission to Everyone, Admin, ASP.NET
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Try placing return(enter) after SQL_Latin1_General_CP1_CI_AS.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
still the Same Error
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
