Database

pmarfleet6-Feb-08 1:46

6-Feb-08 1:46

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

Neophyte306-Feb-08 2:16

Neophyte30

6-Feb-08 2:16

COALESCE may give you better performance - probably only an issue if you have very large tables...

Smile | :)

pmarfleet6-Feb-08 2:31

6-Feb-08 2:31

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

Neophyte306-Feb-08 2:56

Neophyte30

6-Feb-08 2:56

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.

pmarfleet6-Feb-08 3:36

6-Feb-08 3:36

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

Neophyte306-Feb-08 3:43

Neophyte30

6-Feb-08 3:43

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).

concat command in stored procedure

kimo code6-Feb-08 2:57

kimo code

6-Feb-08 2:57

Thank's All For Attention It works with Isnull function

Kareem Elhosseny

jithbiz00336-Feb-08 0:21

jithbiz0033

6-Feb-08 0:21

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

pmarfleet6-Feb-08 0:47

6-Feb-08 0:47

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

jithbiz00336-Feb-08 17:39

jithbiz0033

6-Feb-08 17:39

Hi Paul,

Thanks for you suggestion. It works well.

Thanks
Jith

jithbiz00336-Feb-08 23:23

jithbiz0033

6-Feb-08 23:23

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

pmarfleet7-Feb-08 2:24

what is the exact meaning of this statement

7-Feb-08 2:24

Have a look at this article[^].

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

jagan1235-Feb-08 23:57

jagan123

5-Feb-08 23:57

DELETE FROM Table name WHERE CURRENT OF T1Cursorname

Re: what is the exact meaning of this statement

pmarfleet6-Feb-08 0:49

Re: what is the exact meaning of this statement

6-Feb-08 0:49

Google[^] not working where you live ? Hmmm | :|

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

#realJSOP6-Feb-08 3:55

#realJSOP

6-Feb-08 3:55

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

SQL Server SSL Connection

Ed Gadziemski5-Feb-08 16:05

Ed Gadziemski

5-Feb-08 16:05

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?

SQL Server 2005 express issue

ninkk5-Feb-08 8:18

5-Feb-08 8:18

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

Marek Grzenkowicz5-Feb-08 22:02

Marek Grzenkowicz

5-Feb-08 22:02

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?

ninkk5-Feb-08 22:39

5-Feb-08 22:39

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?

Marek Grzenkowicz5-Feb-08 22:48

Marek Grzenkowicz

5-Feb-08 22:48

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?

ninkk6-Feb-08 1:28

6-Feb-08 1:28

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....

ninkk6-Feb-08 1:29

6-Feb-08 1:29

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."