Problem with single quotes

Hi,
I've a column (type: varchar) in a table. I need to add single quotes in the
value while inserting the rows. But I get an error. Please see below:
create table #t1 (address varchar(20))
go
insert into #t1 values ('St John's street')
Error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ')
Since the database is on hosting providers server, I cannot change the
settings of the server. Please help me by providing any suitable solution.
Also, note that I've an ASP script which insert rows into the table.
Thanks in advance.
-VenkatHi Venkat,
To solve the problem you need to generate insert statement like following in
your ASP script:
insert into #t1 values ('St John''s street')
i.e. replace single quote ( ' ) by two single quotes ( '' )
Krish
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq#aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
the
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||Hi
A single quote within a string can be escapped with a second quote.
insert into #t1 (address) values ('St John''s street')
John
"G.V.Reddy" wrote:

> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in t
he
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>
>|||Thank you very much Krish and John.
Since the address is entered by the visitors on the web site, do we need to
check each and every value entered/inserted into the varchar field for the
single quotes? In case the answer is Yes, I think we can do it by writing a
function which replaces a single quote with adding another quote. Is there
any other simple method to integrate this functionality (escaping with
another single quote) into the ASP code?
Thanks in advance.
-Venkat
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
> the value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||G.V.Reddy wrote:
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we
> need to check each and every value entered/inserted into the varchar
> field for the single quotes? In case the answer is Yes, I think we
> can do it by writing a function which replaces a single quote with
> adding another quote. Is there any other simple method to integrate
> this functionality (escaping with another single quote) into the ASP
> code?
> Thanks in advance.
> -Venkat
>
Look into the use of "parameters". I don't know the exact details for *asp*,
but they are something like:
* provide placeholders in you sql string :
insert into #t1 values (?)
* create a parameter, fill it with the "plain" value ("St John's street"), n
o need
to escape quotes, then add that parameter to the command object
* execute the query
Hans Kesting|||Hi
For ASP/ADO check out the SQL Server samples
http://msdn.microsoft.com/library/d...
5ym.asp
John|||replace(strParam, "'","''")|||Yes, a function to "double up" the single quote items will help. It would be
even better to use parameterized command objects instead of concatenated SQL
strings for communicating with the database (I am guessing you are using ADO
in your ASP application). Building concatenated SQL strings leaves you
application open to SQL Injection attacks, which is a severe security issue.
For information on ADO Command objects and parameters, see:
http://msdn.microsoft.com/library/d...rsreference.asp
For information about SQL injection attacks, see:
http://search.microsoft.com/search/.../>
0&s=1&swc=0
http://www.google.com/search?hl=en&q=sql+injection
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:OxZF4q$aFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we need
to
> check each and every value entered/inserted into the varchar field for the
> single quotes? In case the answer is Yes, I think we can do it by writing
a
> function which replaces a single quote with adding another quote. Is there
> any other simple method to integrate this functionality (escaping with
> another single quote) into the ASP code?
> Thanks in advance.
> -Venkat
>
> "G.V.Reddy" <vreddyg@.go.com> wrote in message
> news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
below:
solution.
>
>sql

Problem with sending email

This is the first time I am setting up an operator so that I can get job
notifications to my email. When I create an operator any type in my address
and click test. It gives me the following error:
Problem occoured while attempting to resolve the address 'my email address'.
Mapi error code number -2147221233
Thanks
The first thing would be to check to ensure that SQL Mail has been setup
correctly as typically the MAPI errors occur as a result of an incorrect
configuration. The KB Article
http://support.microsoft.com/default...b;en-us;263556 describes how
to Configure SQL Mail and the KB article
http://support.microsoft.com/kb/315886/EN-US/ details Common SQL Mail
Problems.
- Peter Ward
WARDY IT Solutions
"Amit" wrote:

> This is the first time I am setting up an operator so that I can get job
> notifications to my email. When I create an operator any type in my address
> and click test. It gives me the following error:
> Problem occoured while attempting to resolve the address 'my email address'.
> Mapi error code number -2147221233
> Thanks

Problem with sending email

This is the first time I am setting up an operator so that I can get job
notifications to my email. When I create an operator any type in my address
and click test. It gives me the following error:
Problem occoured while attempting to resolve the address 'my email address'.
Mapi error code number -2147221233
ThanksThe first thing would be to check to ensure that SQL Mail has been setup
correctly as typically the MAPI errors occur as a result of an incorrect
configuration. The KB Article
http://support.microsoft.com/defaul...kb;en-us;263556 describes how
to Configure SQL Mail and the KB article
http://support.microsoft.com/kb/315886/EN-US/ details Common SQL Mail
Problems.
- Peter Ward
WARDY IT Solutions
"Amit" wrote:

> This is the first time I am setting up an operator so that I can get job
> notifications to my email. When I create an operator any type in my addres
s
> and click test. It gives me the following error:
> Problem occoured while attempting to resolve the address 'my email address
'.
> Mapi error code number -2147221233
> Thanks

Problem with sending email

This is the first time I am setting up an operator so that I can get job
notifications to my email. When I create an operator any type in my address
and click test. It gives me the following error:
Problem occoured while attempting to resolve the address 'my email address'.
Mapi error code number -2147221233
ThanksThe first thing would be to check to ensure that SQL Mail has been setup
correctly as typically the MAPI errors occur as a result of an incorrect
configuration. The KB Article
http://support.microsoft.com/default.aspx?scid=kb;en-us;263556 describes how
to Configure SQL Mail and the KB article
http://support.microsoft.com/kb/315886/EN-US/ details Common SQL Mail
Problems.
- Peter Ward
WARDY IT Solutions
"Amit" wrote:
> This is the first time I am setting up an operator so that I can get job
> notifications to my email. When I create an operator any type in my address
> and click test. It gives me the following error:
> Problem occoured while attempting to resolve the address 'my email address'.
> Mapi error code number -2147221233
> Thanks

Problem with restoring a database

Hello!
I use SQL Server 2005. I have a backup taken from a computer that had the
following definition when I started SQL Server
- Server type: Database Engine
- Server name: UHTDEMO5
-Authentication: Windows Authentication
-Username: UHTDEMO5\anvd
The backup file is called backup_2008_01_22.bak.
So I was sitting on server name UHTDEMO5 when the backup on database Utcas
was taken.
I was loggin in as user anvd. The backup was successfully created.
Now to my question I want to restore this backup on my local desktop server
that is called UHT-DEMO1.
I log in locally as the administrator.
I have tried several different things.
1. I have first create a new user database with name Utcas. This works fine.
2. Now I have an empy database with name Utcas.
3. Now I try Utcas->Tasks->Restore->Database.
4. In the field To database I select Utcas
5.In the From device I select and pick my backup set which is called
backup_2008_01_22.bak
6. In the field From device I can now see my backup file.
7. I the field Select the backup sets to restore I check the check box.
8. When I now klick the Ok button I get the following error message
"Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The media set has 2 media families but only
1 is provided. All
members must be provided. (Microsoft.SqlServer.Smo)"
What do I have to do to restore the backup on my server called UHT-DEMO1.
I thought it was easy to restore a backup but it seams to be difficult.
I'm not so used to SQL Server because I have worked with Oracle before.
//TonyIt appears that your original backup was done as a striped backup - i.e.
there was more than one file created at the time of the backup. If so, then
you need to specify all of the files in the backup when you do the restore.
Check out:
http://msdn2.microsoft.com/en-us/library/ms178062.aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ulo3UJQXIHA.4868@.TK2MSFTNGP03.phx.gbl...
Hello!
I use SQL Server 2005. I have a backup taken from a computer that had the
following definition when I started SQL Server
- Server type: Database Engine
- Server name: UHTDEMO5
-Authentication: Windows Authentication
-Username: UHTDEMO5\anvd
The backup file is called backup_2008_01_22.bak.
So I was sitting on server name UHTDEMO5 when the backup on database Utcas
was taken.
I was loggin in as user anvd. The backup was successfully created.
Now to my question I want to restore this backup on my local desktop server
that is called UHT-DEMO1.
I log in locally as the administrator.
I have tried several different things.
1. I have first create a new user database with name Utcas. This works fine.
2. Now I have an empy database with name Utcas.
3. Now I try Utcas->Tasks->Restore->Database.
4. In the field To database I select Utcas
5.In the From device I select and pick my backup set which is called
backup_2008_01_22.bak
6. In the field From device I can now see my backup file.
7. I the field Select the backup sets to restore I check the check box.
8. When I now klick the Ok button I get the following error message
"Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The media set has 2 media families but only
1 is provided. All
members must be provided. (Microsoft.SqlServer.Smo)"
What do I have to do to restore the backup on my server called UHT-DEMO1.
I thought it was easy to restore a backup but it seams to be difficult.
I'm not so used to SQL Server because I have worked with Oracle before.
//Tony|||"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ulo3UJQXIHA.4868@.TK2MSFTNGP03.phx.gbl...
> Hello!
>
> 1. I have first create a new user database with name Utcas. This works
> fine.
> 2. Now I have an empy database with name Utcas.
Just so you know, the above is not necessary.
> 3. Now I try Utcas->Tasks->Restore->Database.
> 4. In the field To database I select Utcas
> 5.In the From device I select and pick my backup set which is called
> backup_2008_01_22.bak
> 6. In the field From device I can now see my backup file.
> 7. I the field Select the backup sets to restore I check the check box.
> 8. When I now klick the Ok button I get the following error message
> "Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
> Additional information:
> System.Data.SqlClient.SqlError: The media set has 2 media families but
> only
> 1 is provided. All
> members must be provided. (Microsoft.SqlServer.Smo)"
Yeah, as Tony said, it was a striped backup (it wrote to two files at once,
in some cases this can double backup and restore speeds.)
Look for somethign like UTCAS_db_20080121-1.bak and UTCAS_db_2008021-2.back
(i.e. most likely the files will include a -1 and -2... but honestly, could
be anything).
You can also check the originating server to see what it specified (if it's
an automatic job, it should list it, otherwise, I think it's
sys.backuphistory or sys.backupmediaset or something like that.)
> What do I have to do to restore the backup on my server called UHT-DEMO1.
> I thought it was easy to restore a backup but it seams to be difficult.
> I'm not so used to SQL Server because I have worked with Oracle before.
> //Tony
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||In addition to the other posts, you might find below useful:
RESTORE LABELONLY FROM ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ulo3UJQXIHA.4868@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I use SQL Server 2005. I have a backup taken from a computer that had the
> following definition when I started SQL Server
> - Server type: Database Engine
> - Server name: UHTDEMO5
> -Authentication: Windows Authentication
> -Username: UHTDEMO5\anvd
> The backup file is called backup_2008_01_22.bak.
> So I was sitting on server name UHTDEMO5 when the backup on database Utcas
> was taken.
> I was loggin in as user anvd. The backup was successfully created.
> Now to my question I want to restore this backup on my local desktop server
> that is called UHT-DEMO1.
> I log in locally as the administrator.
> I have tried several different things.
> 1. I have first create a new user database with name Utcas. This works fine.
> 2. Now I have an empy database with name Utcas.
> 3. Now I try Utcas->Tasks->Restore->Database.
> 4. In the field To database I select Utcas
> 5.In the From device I select and pick my backup set which is called
> backup_2008_01_22.bak
> 6. In the field From device I can now see my backup file.
> 7. I the field Select the backup sets to restore I check the check box.
> 8. When I now klick the Ok button I get the following error message
> "Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
> Additional information:
> System.Data.SqlClient.SqlError: The media set has 2 media families but only
> 1 is provided. All
> members must be provided. (Microsoft.SqlServer.Smo)"
> What do I have to do to restore the backup on my server called UHT-DEMO1.
> I thought it was easy to restore a backup but it seams to be difficult.
> I'm not so used to SQL Server because I have worked with Oracle before.
> //Tony
>