remove all double quotes from column values using t-sql

Hi,
I am having problem withj double quotes being inserted automatically when i
am inserting data using a CSV file.I am using a C# program to insert the
values.
My coulmn is called whereClause and it is a varchar(50) e.g
'ISNULL(salary,2000)=2000'
but what gets inserted is "'ISNULL(salary,2000)=2000'"
I am using this value in dynamic sql to generate a query such as
exec 'select salary from employee where '+@.whereClause and I get error
because of the automatic double quote insertion.
The CSV file does not have double quotes so I need a solution that will do
either 1 of the following
1)suppress insertion of double quotes in the table in C# OR
2)write query to remove the double quotes using T-SQL
Solution 2 is preferable.
Your help is highly appreciated.
Thanks.if I understood the question correctly, this should work..
declare @.a varchar(30)
set @.a = '"ISNULL(salary,2000)=2000"'
select replace(@.a,'"','')|||Thans for the reply.
But the problem is I have about a thousand rows and each condition is
different and having double quotes eg
id whereClause
1 "ISNULL(salary,5000)=5000"
2 "ISNULL(bonus,400)>600"
3 "IN(600,40)"
I want to remove all double quotes and update the table with proper values i
e
if table contains "ISNULL(salary,5000)=5000" I want to update it as
ISNULL(salary,5000)=5000
Thanks.
"Omnibuzz" wrote:

> if I understood the question correctly, this should work..
> declare @.a varchar(30)
> set @.a = '"ISNULL(salary,2000)=2000"'
> select replace(@.a,'"','')|||Use REPLACE:
create table #x (s varchar(30))
INSERT #x values ('abcdefg')
INSERT #x values ('"hijklmn"')
INSERT #x values ('o"p"q"r"s"t"u')
select * from #x
UPDATE #x
SET S = REPLACE(S,'"','')
select * from #x
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 11:18:02 -0700, tech77
<tech77@.discussions.microsoft.com> wrote:

>Hi,
>I am having problem withj double quotes being inserted automatically when i
>am inserting data using a CSV file.I am using a C# program to insert the
>values.
>My coulmn is called whereClause and it is a varchar(50) e.g
>'ISNULL(salary,2000)=2000'
>but what gets inserted is "'ISNULL(salary,2000)=2000'"
>I am using this value in dynamic sql to generate a query such as
>exec 'select salary from employee where '+@.whereClause and I get error
>because of the automatic double quote insertion.
>The CSV file does not have double quotes so I need a solution that will do
>either 1 of the following
>1)suppress insertion of double quotes in the table in C# OR
>2)write query to remove the double quotes using T-SQL
>Solution 2 is preferable.
>Your help is highly appreciated.
>Thanks.|||Thanks.That worked like a charm!!
"Roy Harvey" wrote:

> Use REPLACE:
> create table #x (s varchar(30))
> INSERT #x values ('abcdefg')
> INSERT #x values ('"hijklmn"')
> INSERT #x values ('o"p"q"r"s"t"u')
> select * from #x
> UPDATE #x
> SET S = REPLACE(S,'"','')
> select * from #x
> Roy Harvey
> Beacon Falls, CT
> On Wed, 5 Apr 2006 11:18:02 -0700, tech77
> <tech77@.discussions.microsoft.com> wrote:
>
>sql

Remote view

I was wondering if i should create a lil program that could run in the tray
showing status of jobs running on a SQL server. By that i mean if they are
enabled, runnable and so on. Are there anyone in here that can lead me to
some info related to that? I want to devlope it using C#.
I have been roaming the net for info about this but so far no luck. Is there
an API on the SQL server that enables me to do this?
Best regards
TrondThe SQL-DMO API should give you what you want for SQL7.0 and 2000.
However, DMO is a COM API so you may prefer instead to develop your
application with SMO, which is DMO's .NET replacement in SQL Server
2005.
http://msdn.microsoft.com/library/d.../>
01_2yi7.asp
David Portas
SQL Server MVP
--

Remote User

Hi, im new to here and SQL server.

I am building a program in vb.net 2005 as a booking system using SQL server as backend. I have just finally managed to get SQL server working on the server, DIY Windows XP Server, but I cannot make remote logins. I have my client computers connected through a workground called JNKVPN. I can connect to all the client computers on the network through the workgroup computers menu off network places. I want to add a remote user account to SQL Server management studio express but none of the client computers are listed as domains.

Is there any way I can get them listed. I have been getting very fustrated with this and i need to have the program i am writing completed by 28th Sept 2007 so this is really delaying me substatially.

Thanks in advance for reply's

Regards
Matt

Hi,

As you said your machines are not on domain network, so you can not add the windows users to your server, rather you may add sql server login(s) in this case. Add the machine(s) to doamains so that they appears or else you need to create a windows login on your sql server machine with the same user id /password as they are on your workgroup machine. I.E. the name of your machine on workgroup is MachA and it has a user "squser1" and password "squser1", you need to create a windows user on your sql server with same username "squser1" and password "squser1" , But the best bet is to add those machine on your domain and then add/map the users to your sql server.

|||

Hi, thanks for your reply.

You have said

Hemantgiri S. Goswami wrote:

Add the machine(s) to doamains so that they appears

This is what I want to know how to do.

In theory should the workgroup computers automatically be added to the listed locations on the add login window or is there a process I must go through to get them on there. As I stated before I can see the other computers in the network places > view workgroup computers.

Regards

Matt

|||I have solved my problem. I was trying to add sql user with windows domain and account details and it was throwing an error at this. This post just illustrates my inexperience with SQL.

Thanks anyway for the reply

Remote User

Hi, im new to here and SQL server.

I am building a program in vb.net 2005 as a booking system using SQL server as backend. I have just finally managed to get SQL server working on the server, DIY Windows XP Server, but I cannot make remote logins. I have my client computers connected through a workground called JNKVPN. I can connect to all the client computers on the network through the workgroup computers menu off network places. I want to add a remote user account to SQL Server management studio express but none of the client computers are listed as domains.

Is there any way I can get them listed. I have been getting very fustrated with this and i need to have the program i am writing completed by 28th Sept 2007 so this is really delaying me substatially.

Thanks in advance for reply's

Regards
Matt

Hi,

As you said your machines are not on domain network, so you can not add the windows users to your server, rather you may add sql server login(s) in this case. Add the machine(s) to doamains so that they appears or else you need to create a windows login on your sql server machine with the same user id /password as they are on your workgroup machine. I.E. the name of your machine on workgroup is MachA and it has a user "squser1" and password "squser1", you need to create a windows user on your sql server with same username "squser1" and password "squser1" , But the best bet is to add those machine on your domain and then add/map the users to your sql server.

|||

Hi, thanks for your reply.

You have said

Hemantgiri S. Goswami wrote:

Add the machine(s) to doamains so that they appears

This is what I want to know how to do.

In theory should the workgroup computers automatically be added to the listed locations on the add login window or is there a process I must go through to get them on there. As I stated before I can see the other computers in the network places > view workgroup computers.

Regards

Matt

|||I have solved my problem. I was trying to add sql user with windows domain and account details and it was throwing an error at this. This post just illustrates my inexperience with SQL.

Thanks anyway for the reply

Remote User

Hi, im new to here and SQL server.

I am building a program in vb.net 2005 as a booking system using SQL server as backend. I have just finally managed to get SQL server working on the server, DIY Windows XP Server, but I cannot make remote logins. I have my client computers connected through a workground called JNKVPN. I can connect to all the client computers on the network through the workgroup computers menu off network places. I want to add a remote user account to SQL Server management studio express but none of the client computers are listed as domains.

Is there any way I can get them listed. I have been getting very fustrated with this and i need to have the program i am writing completed by 28th Sept 2007 so this is really delaying me substatially.

Thanks in advance for reply's

Regards
Matt

Hi,

As you said your machines are not on domain network, so you can not add the windows users to your server, rather you may add sql server login(s) in this case. Add the machine(s) to doamains so that they appears or else you need to create a windows login on your sql server machine with the same user id /password as they are on your workgroup machine. I.E. the name of your machine on workgroup is MachA and it has a user "squser1" and password "squser1", you need to create a windows user on your sql server with same username "squser1" and password "squser1" , But the best bet is to add those machine on your domain and then add/map the users to your sql server.

|||

Hi, thanks for your reply.

You have said

Hemantgiri S. Goswami wrote:

Add the machine(s) to doamains so that they appears

This is what I want to know how to do.

In theory should the workgroup computers automatically be added to the listed locations on the add login window or is there a process I must go through to get them on there. As I stated before I can see the other computers in the network places > view workgroup computers.

Regards

Matt

|||I have solved my problem. I was trying to add sql user with windows domain and account details and it was throwing an error at this. This post just illustrates my inexperience with SQL.

Thanks anyway for the reply

Remote hosted database - SQLServer 2000

Hi all

We have a small database that we host on our own local server. In order to
get the information into the database I have a VB program that collates the
information from a variety of sources and then over our local internal
network writes it to the database tables.

This information is then made available over the internet using the
companies broadband connection. In order to alleviate bandwidth issues we
are looking at paying for someone else to host the database.

What I am hoping to achieve is to keep the VB software, but during the night
upload the data to the remotely hosted SQL server using say a series of
update statements. Does anyone know if this is possible, and will SQL Server
look after the integratory of the data. Is there any issues with the
relatively low speed of then Internet as compared with a local network
environment. The amount of data we are looking at moving is very little,
some where in the region of a few hundred records per night split over about
10 tables.

many thanks

Andy"aaj" <a.b@.c.com> wrote in message
news:40a09390$0$13571$afc38c87@.news.easynet.co.uk. ..
> Hi all
> We have a small database that we host on our own local server. In order to
> get the information into the database I have a VB program that collates
the
> information from a variety of sources and then over our local internal
> network writes it to the database tables.
> This information is then made available over the internet using the
> companies broadband connection. In order to alleviate bandwidth issues we
> are looking at paying for someone else to host the database.
> What I am hoping to achieve is to keep the VB software, but during the
night
> upload the data to the remotely hosted SQL server using say a series of
> update statements. Does anyone know if this is possible, and will SQL
Server
> look after the integratory of the data. Is there any issues with the
> relatively low speed of then Internet as compared with a local network
> environment. The amount of data we are looking at moving is very little,
> some where in the region of a few hundred records per night split over
about
> 10 tables.
> many thanks
> Andy

If you can connect to the server directly, then you can run INSERT/UPDATE
statements as you would normally. For a few hundred records (rows?) this
should work fine. I'm not sure what you mean by "looking after the integrity
of the data". If you want to ensure the data was not modified in
transmission, you can look at some sort of check digit or checksum.

If the amount of data grows, you might need to look at other options, such
as sending a file to the server via FTP, then loading it with bcp.exe or
DTS, but that would depend on the hosting provider's service.

Simon