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 stored procedure

I am trying to run a stored procedure on a remote server and insert the
output into a table on the local machine.
The two servers are linked and I am using the code as follows
BEGIN DISTRIBUTED TRAN
insert into ServerA.Database.dbo.drv_total
exec ServerB.master.dbo.xp_fixeddrives
COMMIT TRAN
The MSDTC is started on both machines.
I am getting this error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
Any thoughts?
TIA,
nivek
Hi Nivek,
Are you running on Windows 2003? If so, the following link may apply to you:
http://support.microsoft.com/default...b;en-us;329332
"The problem occurs because Microsoft Distributed Transaction Coordinator
(MS DTC) is not configured for network access. By default, the network access
settings of MS DTC are disabled on new installations of SQL Server 2000 on
computers running Windows Server 2003, Enterprise Edition. "
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"nivek" wrote:

> I am trying to run a stored procedure on a remote server and insert the
> output into a table on the local machine.
> The two servers are linked and I am using the code as follows
> BEGIN DISTRIBUTED TRAN
> insert into ServerA.Database.dbo.drv_total
> exec ServerB.master.dbo.xp_fixeddrives
> COMMIT TRAN
> The MSDTC is started on both machines.
> I am getting this error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
>
> Any thoughts?
>
> TIA,
> nivek
>
>
sql

Remote stored procedure

I am trying to run a stored procedure on a remote server and insert the
output into a table on the local machine.
The two servers are linked and I am using the code as follows
BEGIN DISTRIBUTED TRAN
insert into ServerA.Database.dbo.drv_total
exec ServerB.master.dbo.xp_fixeddrives
COMMIT TRAN
The MSDTC is started on both machines.
I am getting this error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
Any thoughts?
TIA,
nivekHi Nivek,
Are you running on Windows 2003? If so, the following link may apply to you:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
"The problem occurs because Microsoft Distributed Transaction Coordinator
(MS DTC) is not configured for network access. By default, the network access
settings of MS DTC are disabled on new installations of SQL Server 2000 on
computers running Windows Server 2003, Enterprise Edition. "
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"nivek" wrote:
> I am trying to run a stored procedure on a remote server and insert the
> output into a table on the local machine.
> The two servers are linked and I am using the code as follows
> BEGIN DISTRIBUTED TRAN
> insert into ServerA.Database.dbo.drv_total
> exec ServerB.master.dbo.xp_fixeddrives
> COMMIT TRAN
> The MSDTC is started on both machines.
> I am getting this error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
>
> Any thoughts?
>
> TIA,
> nivek
>
>

Remote Server / Linked Server

I have transactional replication working and it created a remote server
to be used for replication. I am not able to insert/delete/update
between the two servers through the "remote server" connection. I need
to use a linked server, do I need to set up this linked server prior to
configuring any replication so a remote server isn't created during
configuration? Is there something else I can do instead of this.
Thanks
sp_serveroption 'remoteServerName','data access',true
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142538888.208897.59030@.e56g2000cwe.googlegro ups.com...
>I have transactional replication working and it created a remote server
> to be used for replication. I am not able to insert/delete/update
> between the two servers through the "remote server" connection. I need
> to use a linked server, do I need to set up this linked server prior to
> configuring any replication so a remote server isn't created during
> configuration? Is there something else I can do instead of this.
> Thanks
>
|||I changed the data access to true, but now I get the following error
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
The security properties are set to: "Be made using the login's current
security context"
I have sysadmin priviledges so I shouldn't be restricted by that. I
can get it to work and pull data, if I change the security properties
to use the sa login and password, but that isn't secure enough. What
options do I have. Thanks for your time.
|||Enter the sa account and password on the linked server.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142872805.003828.93830@.t31g2000cwb.googlegro ups.com...
>I changed the data access to true, but now I get the following error
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> The security properties are set to: "Be made using the login's current
> security context"
> I have sysadmin priviledges so I shouldn't be restricted by that. I
> can get it to work and pull data, if I change the security properties
> to use the sa login and password, but that isn't secure enough. What
> options do I have. Thanks for your time.
>
|||Maybe I didn't state my question correctly. I am wanting to use the
security property of: "Be made using the login's current
security context" so it can limit the users priviledges to the other
server. When I do this I get the following error.
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection
I was able to use the sa login and password, but that allowed everyone
to have unlimited access to the linked server.
How do I get the security property of: "Be made using the login's
current security context" to work without getting the error. Thanks
|||I don't believe you can as you are logging on using Windows Authentication,
and it appears that the remote server is not part of the domain. Perhaps try
pass through authentication. By any chance is the remote server across the
internet?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142879939.773655.304440@.z34g2000cwc.googlegr oups.com...
> Maybe I didn't state my question correctly. I am wanting to use the
> security property of: "Be made using the login's current
> security context" so it can limit the users priviledges to the other
> server. When I do this I get the following error.
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection
>
> I was able to use the sa login and password, but that allowed everyone
> to have unlimited access to the linked server.
>
> How do I get the security property of: "Be made using the login's
> current security context" to work without getting the error. Thanks
>

Remote insert performance

It seems that remote insert takes much more time than "local" insert.
My table MyServer.MyDb.dbo.MyTable has hudreds of milions of rows and
hudreds of GB. It has 3 indexes: clustered (not unique), nonclustered unique
and nonclustered (not unique).
An insert of tens or hundreds is imediate in MyServer. However it takes
minutes (cca. 1 minute per each 100 rows) when inserted with remote insert
--executed at OtherServer
insert MyServer.MyDb.dbo.MyTable select * from #TmpTable
(It does not make diffrence wether the select is from temporary or ordinary
table)
What is the reason? Can I influence it?
Thank you for commentsHi
The Network IO and RPC traffic involved in doing a remote call carry a lot
of overhead. This really slows the process down and is expected.
If you want ot compare it, create a linked server on a server to itself and
run a simular process.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"karuzo" wrote:

> It seems that remote insert takes much more time than "local" insert.
> My table MyServer.MyDb.dbo.MyTable has hudreds of milions of rows and
> hudreds of GB. It has 3 indexes: clustered (not unique), nonclustered uniq
ue
> and nonclustered (not unique).
> An insert of tens or hundreds is imediate in MyServer. However it takes
> minutes (cca. 1 minute per each 100 rows) when inserted with remote insert
> --executed at OtherServer
> insert MyServer.MyDb.dbo.MyTable select * from #TmpTable
> (It does not make diffrence wether the select is from temporary or ordinar
y
> table)
> What is the reason? Can I influence it?
> Thank you for comments

Remote insert

I have an issue i need to understand and have not been able to find an answer yet. It may be something to do with parametised query execution but i`m not sure yet. Below is the scenario

If i do a

insert into server.db.dbo.remotetable
select * from dbo.localtable

and the local select returns say 3 values, 3 inserts will occur on the destination server where as if the insert into references a local table only 1 insert would occur!

Why? Is it possible to get the remote query to behave like a local and do the 3 records in 1 insert?

To test this i've supplied some very simple code. Just create source tbl on local server and destination and log tbl on remote server. Setup a linked server and run the remote insert then do a local insert and look at the log.

All advise gratefully received!

Cheers

Andrew

CREATE TABLE [dbo].[source] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create these on the destination server
CREATE TABLE [dbo].[tbllog] (
[Server] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[tst_Count] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[destination] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [destination_ins] ON [dbo].[destination]
FOR INSERT, UPDATE, DELETE
AS
insert into dbo.tbllog
select server,count(server) from inserted group by server
GO

--Insert some sample data into the source table
insert into source values ('MYSERVER','1/1/2000')
insert into source values ('MYSERVER','1/2/2000')
insert into source values ('MYSERVER','1/3/2000')

--Run the insert from the source db

insert into destinationsrv.destdb.dbo.destination
select * from source

--Switch to the destination server and run select there is only meant to be 1 row!
select * from tbllogNews to me. I'll try it out when I get to my office.|||Thanks blindman|||I'm dealing with the same problem. Has anyone found a solution? I'd hate to resort to calling a remote stored procedure just to pull data across the link.

Surely people have encountered this problem before - I'm able to reproduce it in both Sql Server 2000 and 2005. Or do people normally only use linkedservers for queries?

Remote Database Management through LDAP Tools

Hai All
How to insert and delete records from a table in the remote database?"
I want it to be done through a tool that used LDAP Servers.
I want tools for the LDAP .
Thank u Advance
Regards
Sunil
aboutknowledge@.gmail.comWithout knowing a lot more about what you're trying to do, it is hard to recommend any specific tool or tools. Are you looking for somethng like MIIS?

-PatP|||I should be able to add / delete / modify the records of a table in a database from a remote location:

1. either just by using browser only.
2. Or a tool (preferably that utilizes LDAP servers in it's implementation)

I think this clearly explain the situation.