|
Hi All;
I have i 2 table named AcceptedMsg,AcceptedNickName
I Want to Concatenate two column(NickName From AcceptedNickName & Msgtext From AcceptedMsg) from the above table and using left outer join to concatenate with my criteria
but i meet a proplem when the first column is null i want the secound field "column " retrived as its value
but the result in this case null
here is my sql query statement
-------------------------------
select Allname = AcceptedNickName.nickname +':'+''+AcceptedMsg.msgtext from AcceptedMsg
left outer join AcceptedNickName
on AcceptedMsg.phoneno = AcceptedNickName.phoneno
and here is the result :
---------------------------
AllName
---------------
Null |
Kimo:Hi All |................... here meet the condition
Null |
---------------
But I want if sql cant meet the condition Retrived to me
AllName
---------------
are U there |
Kimo:Hi All |
thank you |
---------------
Can any one Help me.
Thanks For All
Kareem Elhosseny
|
|
|
|
|
Use the ISNULL function to return an empty string if AcceptedMsg.msgtext is NULL. This will ensure that the concatenation will still work.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
COALESCE may give you better performance - probably only an issue if you have very large tables...
|
|
|
|
|
Neophyte30 wrote: COALESCE may give you better performance - probably only an issue if you have very large tables...
On what evidence? Please state your source.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I have no source - just something I was told [by a contractor DBA] a long time ago. May even have been server-specific (Sql Server 2000).
Sorry if I offended you.
|
|
|
|
|
I'm not offended. However, if you are going to critisize the opinions of others you need to know what you are talking about and be prepared to back up your opinions with hard facts. Simply stating 'I'm right, you're wrong' isn't good enough, especially if the opinions you spout aren't your own but are based on a half-heard conversion that occured some time in the past.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I wasn't criticising, and certainly wasn't saying you were wrong. Hence the use of the word 'may'. Some more investigation turns up that the performance differences of ISNULL and COALESCE have been debated in the past, ISNULL being the faster/more efficient/whatever. However mention was made (http://sqljunkies.com/WebLog/amachanic/archive/2004/11.aspx[^])
of Standards Conformance, which may have been why I was told to use it (albeit for the wrong reason).
|
|
|
|
|
Thank's All For Attention It works with Isnull function
Kareem Elhosseny
|
|
|
|
|
Hi Guys
I have a stored procedure which has the coding to insert values in tables with different suffix but one prefix. Eg table_fruits, table_vegetables, table_pulses etc., I am passing the suffix in the procedure. I dont know how to concat the suffix to insert the value in the specific table.
create procedure [dbo].[my_proc](
@category varchar(30),
@Name varchar(50))
As
Begin
insert into table_+@category+ value name=@Name;
End
I dont want to follow
if(@category = 'fruits')
insert into table_fruits values name=@Name;
else if(@category = 'vegetables') etc ., etc.,
Is there any way to do this. Kindly help me.
Thanks
Jith
|
|
|
|
|
You will have to build the INSERT statement in a varchar variable and execute it dynamically using the EXECUTE command.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi Paul,
Thanks for you suggestion. It works well.
Thanks
Jith
|
|
|
|
|
Hi Mr. Paul
I have one more doubt. If I want to get a value using SELECT Command in EXECUTE while using Stored Procedure. How to get it
I tried
Statement 1 :
@GetValue = Exec('Select Id from table_'+@Category+' where name = @Name')
Statement 2 :
Exec('Select @GetValue = Id from table_'+@Category+' where name = @Name')
But both are not working,
Thanks
Jith
|
|
|
|
|
Have a look at this article[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
DELETE FROM Table name WHERE CURRENT OF T1Cursorname
|
|
|
|
|
Google[^] not working where you live ? 
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
That's the one website that is still available to Indians after the cable cuts because they don't use it enough to cause bandwidth problems.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
I have a distributed application. The client is a Windows Forms app and it connects to a firewalled SQL Server 2005 instance using SQL Server login over TCP/IP over an Internet connection. Both client and server have static IP addresses. My questions as I go live are:
1. Is a self-signed SSL certificate secure enough for this usage? Since the client is a known statically-addressed entity, it doesn't seem possible for MITM or other hacking to occur.
2. Would it be better to set up a VPN? I played around briefly with VPNs and found them very, very slow.
3. Is there a better secure connection method I am overlooking?
|
|
|
|
|
Hallo,
we have a pc that has Visual Studio and Sql Server 2005 Express installed on it, lets name it SERVER. We did a small project/application on SERVER with Visual Studio that has to be installed on another pc(client pc, lets name it CLIENTPC) and make some sql queries. The Database is placed on SERVER and the small project/application is placed on CLIENTPC. The application makes some queries from CLIENTPC to SERVER.
Our problem is that we get error even from the beginning of the application, when we run it from CLIENTPC on myConnection.Open() method.
We have to notice that the CLIENT machine does not have sql server express installed on it, and it seems that we are missing something here. In sql server 2000, installation of MDAC was enough but in server express 2005 there are no MDAC.
Any idea what we have to do so client could 'see' and 'work' with SERVER database files?
Thanks in advance
|
|
|
|
|
ninkk wrote: Our problem is that we get error even from the beginning of the application, when we run it from CLIENTPC on myConnection.Open() method.
Can you post this error?
|
|
|
|
|
Maybe i was not completely clear. The error we get is the regular windows error, the asks if we want to send this error to microsoft. There is no problem with the code we use, because when we run the application on the SERVER machine that has installed SQL SERVER 2005 express and the database files, everything works perfect. Problem occurs when we try to run the application from a CLIENT machine and it connects to the database based on SERVER machine, but in cliet machine(win XP SP2) we do not have installed sql server express or mdac.
Do we have to install something or to do something else?
|
|
|
|
|
1. How do you connect to the database? Can you post appropriate piece of code?
2. Can you download this tool[^] on the client machine and try if you can connect to the server?
|
|
|
|
|
1.
I connect using myConnection.Open(). Before attempting to connect, myConnection.ConnectionString="Provider=SQLOLEDB.1;Persist Security Info=False;UID=user;PWD=111;Initial Catalog=MyCatalog;Data Source=SERVERPC\SQLEXPRESS"
After this we make myConnection.Open()
This works fine only if it runs on SERVER pc that has the sql server express and database files, and when it runs on pc CLIENT that has nothing does not work, we get windows error.
2.
After trying this tool, it works fine only while executing from the SERVER pc. When i try it from CLIENT pc, we get this error:
"Unable to connect:An error occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure maybe caused by the fact that under the default settings SQL Server does not allow romote connections. )provider: SQL Network Interfaces, erroe: 26 - Error Locationg Server/Instance Specified"
To try is we put on SQL Server tab, on Server, "clientpc\sqlexpress" and login name and password.
Thanks for helping....
|
|
|
|
|
Replace this
"To try is we put on SQL Server tab, on Server, "clientpc\sqlexpress" and login name and password."
with this
"To try it we put on SQL Server tab, on Server textbox, "serverpc\sqlexpress" and login name and password."
|
|
|
|
|
Can you see anything like SQL Server Surface Area Configuration in the Start Menu? I'm not sure if SQL Server Express Edition has this tool.
If yes, check if remote connections to your database are allowed (BTW, this is what the error you posted above suggests to check).
|
|
|
|
|
Yes, i found SQL Server Surface Area Configuration. I clicked on Surface Area Configuration for Services and Connection. From there i set the reomote connections to TCP\Ip for both MSSMLBIZ and SQLEXPRESS. But is still the same, cannot connect. I even set the windows firewall to off, but still not connecting.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
