In a asp I made using the Paramater object a user can submit a form and = the data will be inserted in the database. But if the user submits a = name in the form O'Reilly then it will error. So I made a VBScript = function which parses the input data and if ' exists in the name I add = an extra ' to the ' in the form data and the user can insert the data. = Trouble is when I return the recordset it looks like this O''Reilly. So = I am wondering if there exists an escape chatacter to avoid this issue? = Thanks.
-- George Hester
__________________________________It seems like you both replace every quote with two quotes and then use a parameter object which
does exactly the same. This is why you get double. You should only need to use the parameter object.
If you post the code (either here or on ASP group) you might get some help...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
In a asp I made using the Paramater object a user can submit a form and the data will be inserted in
the database. But if the user submits a name in the form O'Reilly then it will error. So I made a
VBScript function which parses the input data and if ' exists in the name I add an extra ' to the '
in the form data and the user can insert the data. Trouble is when I return the recordset it looks
like this O''Reilly. So I am wondering if there exists an escape chatacter to avoid this issue?
Thanks.
--
George Hester
__________________________________|||Here is a snippet:
<%
strCompanyName =3D EncodeString(Request.Form("txtCompanyName"))
If strCompanyName <> "" Then
If Len(strCompanyName) =3D 0 Then
'
blnErrorFound =3D True
End If
If Not blnErrorFound Then
Set cmd =3D Server.CreateObject("ADODB.Command")
Set pReturnCode =3D Server.CreateObject("ADODB.Parameter")
Set pCompanyNameDesc =3D Server.CreateObject("ADODB.Parameter")
With cmd
.CommandType =3D adCmdStoredProc
.CommandText =3D "AddSuppliers"
Set .ActiveConnection =3D cn
Set pCompanyNameDesc =3D .CreateParameter("CompanyNameDesc")
With pCompanyNameDesc
.Type =3D adVarWChar
.Size =3D 40
.Direction =3D adParamInput
.Value =3D strCompanyName
End With
.Parameters.Append pCompanyNameDesc
End With
With cn
.BeginTrans
With cmd
.Execute lRecs,, adCmdStoredProc + adExecuteNoRecords
End With
If Err.Number <> 0 Then
.RollbackTrans
Else
.CommitTrans
With cmd
If .Parameters("@.RETURN_CODE").Value =3D 0 Then
strMsg =3D "Company Name"
Response.Write strMsg
sSQL =3D "SELECT * FROM Suppliers ORDER BY SupplierID DESC"
Set rs =3D Server.CreateObject("ADODB.Recordset")
With rs
.CursorType =3D adOpenForwardOnly
.LockType =3D adLockReadOnly
.Open sSQL, cn
Response.Write(.Fields("CompanyName").Value)
End If
End With
End If
End With
End If
End If
%>
I send to strCompanyName the value in a Text Box in a form. The =EncodeString just replaces occurances of '
with '' so that the rs.Open sSQL, cn does not fail if the user entered =say O'Reilly. But the recordset returns
O''Reilly. Yes I can Replace() that out easy enough. I was hoping in =the sSQL string there was an escape in
SQL so that I didn't have to use EncodeString().
-- George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =use a parameter object which
> does exactly the same. This is why you get double. You should only =need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =help...
> > -- > Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =chatacter to avoid this issue?
> Thanks.
> > -- > George Hester
> __________________________________
> >|||I'm sorry. I meant the cmd.Execute will error if I don't replace the ' =in O'Reilly with O''Reilly inserting in the database. I believe.
-- George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =use a parameter object which
> does exactly the same. This is why you get double. You should only =need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =help...
> > -- > Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =chatacter to avoid this issue?
> Thanks.
> > -- > George Hester
> __________________________________
> >|||The ADO command and parameter handling should replace each ' with '' for you. I suggest you run a profiler
trace and see what happens.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:ed2G5u8XEHA.1152@.TK2MSFTNGP09.phx.gbl...
I'm sorry. I meant the cmd.Execute will error if I don't replace the ' in O'Reilly with O''Reilly inserting
in the database. I believe.
--
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then use a parameter object which
> does exactly the same. This is why you get double. You should only need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some help...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape chatacter to avoid this issue?
> Thanks.
> --
> George Hester
> __________________________________
>
Showing posts with label inserted. Show all posts
Showing posts with label inserted. Show all posts
Tuesday, March 27, 2012
does SQL have an escape character
In a asp I made using the Paramater object a user can submit a form and =
the data will be inserted in the database. But if the user submits a =
name in the form O'Reilly then it will error. So I made a VBScript =
function which parses the input data and if ' exists in the name I add =
an extra ' to the ' in the form data and the user can insert the data. =
Trouble is when I return the recordset it looks like this O''Reilly. So =
I am wondering if there exists an escape chatacter to avoid this issue? =
Thanks.
--=20
George Hester
__________________________________
It seems like you both replace every quote with two quotes and then use a parameter object which
does exactly the same. This is why you get double. You should only need to use the parameter object.
If you post the code (either here or on ASP group) you might get some help...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
In a asp I made using the Paramater object a user can submit a form and the data will be inserted in
the database. But if the user submits a name in the form O'Reilly then it will error. So I made a
VBScript function which parses the input data and if ' exists in the name I add an extra ' to the '
in the form data and the user can insert the data. Trouble is when I return the recordset it looks
like this O''Reilly. So I am wondering if there exists an escape chatacter to avoid this issue?
Thanks.
George Hester
__________________________________
|||Here is a snippet:
<%
strCompanyName =3D EncodeString(Request.Form("txtCompanyName"))
If strCompanyName <> "" Then
If Len(strCompanyName) =3D 0 Then
'
blnErrorFound =3D True
End If
If Not blnErrorFound Then
Set cmd =3D Server.CreateObject("ADODB.Command")
Set pReturnCode =3D Server.CreateObject("ADODB.Parameter")
Set pCompanyNameDesc =3D Server.CreateObject("ADODB.Parameter")
With cmd
.CommandType =3D adCmdStoredProc
.CommandText =3D "AddSuppliers"
Set .ActiveConnection =3D cn
Set pCompanyNameDesc =3D .CreateParameter("CompanyNameDesc")
With pCompanyNameDesc
.Type =3D adVarWChar
.Size =3D 40
.Direction =3D adParamInput
.Value =3D strCompanyName
End With
.Parameters.Append pCompanyNameDesc
End With
With cn
.BeginTrans
With cmd
.Execute lRecs,, adCmdStoredProc + adExecuteNoRecords
End With
If Err.Number <> 0 Then
.RollbackTrans
Else
.CommitTrans
With cmd
If .Parameters("@.RETURN_CODE").Value =3D 0 Then
strMsg =3D "Company Name"
Response.Write strMsg
sSQL =3D "SELECT * FROM Suppliers ORDER BY SupplierID DESC"
Set rs =3D Server.CreateObject("ADODB.Recordset")
With rs
.CursorType =3D adOpenForwardOnly
.LockType =3D adLockReadOnly
.Open sSQL, cn
Response.Write(.Fields("CompanyName").Value)
End If
End With
End If
End With
End If
End If
%>
I send to strCompanyName the value in a Text Box in a form. The =
EncodeString just replaces occurances of '
with '' so that the rs.Open sSQL, cn does not fail if the user entered =
say O'Reilly. But the recordset returns
O''Reilly. Yes I can Replace() that out easy enough. I was hoping in =
the sSQL string there was an escape in
SQL so that I didn't have to use EncodeString().
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =
use a parameter object which
> does exactly the same. This is why you get double. You should only =
need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =
help...
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =
and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =
then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =
name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =
return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =
chatacter to avoid this issue?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>
|||I'm sorry. I meant the cmd.Execute will error if I don't replace the ' =
in O'Reilly with O''Reilly inserting in the database. I believe.
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =
use a parameter object which
> does exactly the same. This is why you get double. You should only =
need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =
help...
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =
and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =
then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =
name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =
return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =
chatacter to avoid this issue?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>
|||The ADO command and parameter handling should replace each ' with '' for you. I suggest you run a profiler
trace and see what happens.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:ed2G5u8XEHA.1152@.TK2MSFTNGP09.phx.gbl...
I'm sorry. I meant the cmd.Execute will error if I don't replace the ' in O'Reilly with O''Reilly inserting
in the database. I believe.
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then use a parameter object which
> does exactly the same. This is why you get double. You should only need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some help...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape chatacter to avoid this issue?
> Thanks.
> --
> George Hester
> __________________________________
>
the data will be inserted in the database. But if the user submits a =
name in the form O'Reilly then it will error. So I made a VBScript =
function which parses the input data and if ' exists in the name I add =
an extra ' to the ' in the form data and the user can insert the data. =
Trouble is when I return the recordset it looks like this O''Reilly. So =
I am wondering if there exists an escape chatacter to avoid this issue? =
Thanks.
--=20
George Hester
__________________________________
It seems like you both replace every quote with two quotes and then use a parameter object which
does exactly the same. This is why you get double. You should only need to use the parameter object.
If you post the code (either here or on ASP group) you might get some help...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
In a asp I made using the Paramater object a user can submit a form and the data will be inserted in
the database. But if the user submits a name in the form O'Reilly then it will error. So I made a
VBScript function which parses the input data and if ' exists in the name I add an extra ' to the '
in the form data and the user can insert the data. Trouble is when I return the recordset it looks
like this O''Reilly. So I am wondering if there exists an escape chatacter to avoid this issue?
Thanks.
George Hester
__________________________________
|||Here is a snippet:
<%
strCompanyName =3D EncodeString(Request.Form("txtCompanyName"))
If strCompanyName <> "" Then
If Len(strCompanyName) =3D 0 Then
'
blnErrorFound =3D True
End If
If Not blnErrorFound Then
Set cmd =3D Server.CreateObject("ADODB.Command")
Set pReturnCode =3D Server.CreateObject("ADODB.Parameter")
Set pCompanyNameDesc =3D Server.CreateObject("ADODB.Parameter")
With cmd
.CommandType =3D adCmdStoredProc
.CommandText =3D "AddSuppliers"
Set .ActiveConnection =3D cn
Set pCompanyNameDesc =3D .CreateParameter("CompanyNameDesc")
With pCompanyNameDesc
.Type =3D adVarWChar
.Size =3D 40
.Direction =3D adParamInput
.Value =3D strCompanyName
End With
.Parameters.Append pCompanyNameDesc
End With
With cn
.BeginTrans
With cmd
.Execute lRecs,, adCmdStoredProc + adExecuteNoRecords
End With
If Err.Number <> 0 Then
.RollbackTrans
Else
.CommitTrans
With cmd
If .Parameters("@.RETURN_CODE").Value =3D 0 Then
strMsg =3D "Company Name"
Response.Write strMsg
sSQL =3D "SELECT * FROM Suppliers ORDER BY SupplierID DESC"
Set rs =3D Server.CreateObject("ADODB.Recordset")
With rs
.CursorType =3D adOpenForwardOnly
.LockType =3D adLockReadOnly
.Open sSQL, cn
Response.Write(.Fields("CompanyName").Value)
End If
End With
End If
End With
End If
End If
%>
I send to strCompanyName the value in a Text Box in a form. The =
EncodeString just replaces occurances of '
with '' so that the rs.Open sSQL, cn does not fail if the user entered =
say O'Reilly. But the recordset returns
O''Reilly. Yes I can Replace() that out easy enough. I was hoping in =
the sSQL string there was an escape in
SQL so that I didn't have to use EncodeString().
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =
use a parameter object which
> does exactly the same. This is why you get double. You should only =
need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =
help...
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =
and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =
then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =
name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =
return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =
chatacter to avoid this issue?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>
|||I'm sorry. I meant the cmd.Execute will error if I don't replace the ' =
in O'Reilly with O''Reilly inserting in the database. I believe.
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =
use a parameter object which
> does exactly the same. This is why you get double. You should only =
need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =
help...
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =
and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =
then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =
name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =
return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =
chatacter to avoid this issue?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>
|||The ADO command and parameter handling should replace each ' with '' for you. I suggest you run a profiler
trace and see what happens.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:ed2G5u8XEHA.1152@.TK2MSFTNGP09.phx.gbl...
I'm sorry. I meant the cmd.Execute will error if I don't replace the ' in O'Reilly with O''Reilly inserting
in the database. I believe.
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then use a parameter object which
> does exactly the same. This is why you get double. You should only need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some help...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape chatacter to avoid this issue?
> Thanks.
> --
> George Hester
> __________________________________
>
does SQL have an escape character
In a asp I made using the Paramater object a user can submit a form and =
the data will be inserted in the database. But if the user submits a =
name in the form O'Reilly then it will error. So I made a VBScript =
function which parses the input data and if ' exists in the name I add =
an extra ' to the ' in the form data and the user can insert the data. =
Trouble is when I return the recordset it looks like this O''Reilly. So =
I am wondering if there exists an escape chatacter to avoid this issue? =
Thanks.
--=20
George Hester
__________________________________It seems like you both replace every quote with two quotes and then use a pa
rameter object which
does exactly the same. This is why you get double. You should only need to u
se the parameter object.
If you post the code (either here or on ASP group) you might get some help..
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
In a asp I made using the Paramater object a user can submit a form and the
data will be inserted in
the database. But if the user submits a name in the form O'Reilly then it w
ill error. So I made a
VBScript function which parses the input data and if ' exists in the name I
add an extra ' to the '
in the form data and the user can insert the data. Trouble is when I return
the recordset it looks
like this O''Reilly. So I am wondering if there exists an escape chatacter
to avoid this issue?
Thanks.
George Hester
__________________________________|||Here is a snippet:
<%
strCompanyName =3D EncodeString(Request.Form("txtCompanyName"))
If strCompanyName <> "" Then
If Len(strCompanyName) =3D 0 Then
'
blnErrorFound =3D True
End If
If Not blnErrorFound Then
Set cmd =3D Server.CreateObject("ADODB.Command")
Set pReturnCode =3D Server.CreateObject("ADODB.Parameter")
Set pCompanyNameDesc =3D Server.CreateObject("ADODB.Parameter")
With cmd
.CommandType =3D adCmdStoredProc
.CommandText =3D "AddSuppliers"
Set .ActiveConnection =3D cn
Set pCompanyNameDesc =3D .CreateParameter("CompanyNameDesc")
With pCompanyNameDesc
.Type =3D adVarWChar
.Size =3D 40
.Direction =3D adParamInput
.Value =3D strCompanyName
End With
.Parameters.Append pCompanyNameDesc
End With
With cn
.BeginTrans
With cmd
.Execute lRecs,, adCmdStoredProc + adExecuteNoRecords
End With
If Err.Number <> 0 Then
.RollbackTrans
Else
.CommitTrans
With cmd
If .Parameters("@.RETURN_CODE").Value =3D 0 Then
strMsg =3D "Company Name"
Response.Write strMsg
sSQL =3D "SELECT * FROM Suppliers ORDER BY SupplierID DESC"
Set rs =3D Server.CreateObject("ADODB.Recordset")
With rs
.CursorType =3D adOpenForwardOnly
.LockType =3D adLockReadOnly
.Open sSQL, cn
Response.Write(.Fields("CompanyName").Value)
End If
End With
End If
End With
End If
End If
%>
I send to strCompanyName the value in a Text Box in a form. The =
EncodeString just replaces occurances of '
with '' so that the rs.Open sSQL, cn does not fail if the user entered =
say O'Reilly. But the recordset returns
O''Reilly. Yes I can Replace() that out easy enough. I was hoping in =
the sSQL string there was an escape in
SQL so that I didn't have to use EncodeString().
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =
use a parameter object which
> does exactly the same. This is why you get double. You should only =
need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =
help...
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =
and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =
then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =
name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =
return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =
chatacter to avoid this issue?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>|||I'm sorry. I meant the cmd.Execute will error if I don't replace the ' =
in O'Reilly with O''Reilly inserting in the database. I believe.
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =
use a parameter object which
> does exactly the same. This is why you get double. You should only =
need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =
help...
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =
and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =
then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =
name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =
return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =
chatacter to avoid this issue?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>|||The ADO command and parameter handling should replace each ' with '' for you
. I suggest you run a profiler
trace and see what happens.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:ed2G5u8XEHA.1
152@.TK2MSFTNGP09.phx.gbl...
I'm sorry. I meant the cmd.Execute will error if I don't replace the ' in O
'Reilly with O''Reilly inserting
in the database. I believe.
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then use a
parameter object which
> does exactly the same. This is why you get double. You should only need to
use the parameter object.
> If you post the code (either here or on ASP group) you might get some help
..
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form and th
e data will be inserted in
> the database. But if the user submits a name in the form O'Reilly then it
will error. So I made a
> VBScript function which parses the input data and if ' exists in the name
I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I retu
rn the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape chatacte
r to avoid this issue?
> Thanks.
> --
> George Hester
> __________________________________
>
the data will be inserted in the database. But if the user submits a =
name in the form O'Reilly then it will error. So I made a VBScript =
function which parses the input data and if ' exists in the name I add =
an extra ' to the ' in the form data and the user can insert the data. =
Trouble is when I return the recordset it looks like this O''Reilly. So =
I am wondering if there exists an escape chatacter to avoid this issue? =
Thanks.
--=20
George Hester
__________________________________It seems like you both replace every quote with two quotes and then use a pa
rameter object which
does exactly the same. This is why you get double. You should only need to u
se the parameter object.
If you post the code (either here or on ASP group) you might get some help..
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
In a asp I made using the Paramater object a user can submit a form and the
data will be inserted in
the database. But if the user submits a name in the form O'Reilly then it w
ill error. So I made a
VBScript function which parses the input data and if ' exists in the name I
add an extra ' to the '
in the form data and the user can insert the data. Trouble is when I return
the recordset it looks
like this O''Reilly. So I am wondering if there exists an escape chatacter
to avoid this issue?
Thanks.
George Hester
__________________________________|||Here is a snippet:
<%
strCompanyName =3D EncodeString(Request.Form("txtCompanyName"))
If strCompanyName <> "" Then
If Len(strCompanyName) =3D 0 Then
'
blnErrorFound =3D True
End If
If Not blnErrorFound Then
Set cmd =3D Server.CreateObject("ADODB.Command")
Set pReturnCode =3D Server.CreateObject("ADODB.Parameter")
Set pCompanyNameDesc =3D Server.CreateObject("ADODB.Parameter")
With cmd
.CommandType =3D adCmdStoredProc
.CommandText =3D "AddSuppliers"
Set .ActiveConnection =3D cn
Set pCompanyNameDesc =3D .CreateParameter("CompanyNameDesc")
With pCompanyNameDesc
.Type =3D adVarWChar
.Size =3D 40
.Direction =3D adParamInput
.Value =3D strCompanyName
End With
.Parameters.Append pCompanyNameDesc
End With
With cn
.BeginTrans
With cmd
.Execute lRecs,, adCmdStoredProc + adExecuteNoRecords
End With
If Err.Number <> 0 Then
.RollbackTrans
Else
.CommitTrans
With cmd
If .Parameters("@.RETURN_CODE").Value =3D 0 Then
strMsg =3D "Company Name"
Response.Write strMsg
sSQL =3D "SELECT * FROM Suppliers ORDER BY SupplierID DESC"
Set rs =3D Server.CreateObject("ADODB.Recordset")
With rs
.CursorType =3D adOpenForwardOnly
.LockType =3D adLockReadOnly
.Open sSQL, cn
Response.Write(.Fields("CompanyName").Value)
End If
End With
End If
End With
End If
End If
%>
I send to strCompanyName the value in a Text Box in a form. The =
EncodeString just replaces occurances of '
with '' so that the rs.Open sSQL, cn does not fail if the user entered =
say O'Reilly. But the recordset returns
O''Reilly. Yes I can Replace() that out easy enough. I was hoping in =
the sSQL string there was an escape in
SQL so that I didn't have to use EncodeString().
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =
use a parameter object which
> does exactly the same. This is why you get double. You should only =
need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =
help...
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =
and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =
then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =
name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =
return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =
chatacter to avoid this issue?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>|||I'm sorry. I meant the cmd.Execute will error if I don't replace the ' =
in O'Reilly with O''Reilly inserting in the database. I believe.
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =
use a parameter object which
> does exactly the same. This is why you get double. You should only =
need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =
help...
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =
and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =
then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =
name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =
return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =
chatacter to avoid this issue?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>|||The ADO command and parameter handling should replace each ' with '' for you
. I suggest you run a profiler
trace and see what happens.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:ed2G5u8XEHA.1
152@.TK2MSFTNGP09.phx.gbl...
I'm sorry. I meant the cmd.Execute will error if I don't replace the ' in O
'Reilly with O''Reilly inserting
in the database. I believe.
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then use a
parameter object which
> does exactly the same. This is why you get double. You should only need to
use the parameter object.
> If you post the code (either here or on ASP group) you might get some help
..
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form and th
e data will be inserted in
> the database. But if the user submits a name in the form O'Reilly then it
will error. So I made a
> VBScript function which parses the input data and if ' exists in the name
I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I retu
rn the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape chatacte
r to avoid this issue?
> Thanks.
> --
> George Hester
> __________________________________
>
Thursday, March 22, 2012
Does scope_identity or @@identity always return NULL on Subscriber
I have insert sp's that run on a subscriber. After the insert sql statement
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||No I'm using SQL Server 2000...i may have to move to 2005 sooner than later.
Its hard to believe this can't work because let's say you have an order with
order details tables. You would first create the order on the order table
and get a new Order ID identity, then have to use that identity for the
parent child relationship to the order detail row. This type of scenario
must've been implemented in sql server 2000 with transactional replication
and immediate updates right? How do you retrieve the new ident from the
subscriber without doing maybe a Select max(ident_column) after the insert?
"Paul Ibison" wrote:
> Ben,
> interesting. These identity values are controlled by the publisher rather
> than the subscriber. In fact, you should find that on the subscriber there
> isn't an identity property on the tables for this reason. Are you're using
> SQL Server 2005? If so, you could capture the assigned value using the
> OUTPUT clause.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>|||Ben,
I've just retested this and it depends on your replication setup. If you
have the identity column set to 'Yes (Not for Replication)' then there are
problems, but if it is set to just 'Yes' (the recommended way) then
@.@.identity should return the correct value and scope_identity() will not
return anything. Perhaps this is the source of your issue? Alternatively I
was wondering if you have any user triggers in action?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||I have no triggers on these tables other than the ones created by replication.
Paul, I'm assuming you're asking me if the Publisher has the identity column
set to 'Yes (Not for Replication)'. If so, then no its set to simply "Yes".
In your test environment, was the subscriber on the same server? I tested
this with the subscriber on the same server and the @.@.identity did return the
correct value, but when the subscriber was on another sql server it still
returned null.
so to clarify, my publisher has a identity column with "Yes", and my
subscriber has this column replicated as an int (i.e. no ident column)...the
subscriber column shouldn't have an ident right?
"Paul Ibison" wrote:
> Ben,
> I've just retested this and it depends on your replication setup. If you
> have the identity column set to 'Yes (Not for Replication)' then there are
> problems, but if it is set to just 'Yes' (the recommended way) then
> @.@.identity should return the correct value and scope_identity() will not
> return anything. Perhaps this is the source of your issue? Alternatively I
> was wondering if you have any user triggers in action?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>|||Hi Ben,
Yes - I agree with everything you're saying :)
I'm currently testing your scenario on my home PC (2 databases on same
instance) which catches the @.@.identity (without the NFR attribute). I will
set it up accross servers when I get to work tomorrow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thank you Paul!!! I've been racking my brain around this for almost 2 days.
My intern worse case scenario is to move to 2005 and use the OUTPUT clause
you mentioned or replacing the "Scope_Identity()" code to a Select
Max(Ident_Column) in the insert sp ...which to me feels more like a hack
thanks again!
"Paul Ibison" wrote:
> Hi Ben,
> Yes - I agree with everything you're saying :)
> I'm currently testing your scenario on my home PC (2 databases on same
> instance) which catches the @.@.identity (without the NFR attribute). I will
> set it up accross servers when I get to work tomorrow.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>|||Ben,
Unfortunately we only have 1 instance of SQL Server 2000 on the network and
when I finally got this set up on 2 networked instances of SQL Server 2005
it worked fine - I could pick up both the @.@.identity and the
scope_identity() values. However I noticed that this is implemented
differently and the identity property is on the subscriber in the new
version so this is not really a decent test. I'll try to install SQL Server
2000 on another box later on and test it this way, but I recommend getting a
support engineer (PSS) to test this for you as I won't have the time to set
all this up quickly.
Regards,
Paul Ibison|||thanks for the help Paul. I will do a quick test on 2005 and see if it
works. I'll keep you posted.
"Paul Ibison" wrote:
> Ben,
> Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> when I finally got this set up on 2 networked instances of SQL Server 2005
> it worked fine - I could pick up both the @.@.identity and the
> scope_identity() values. However I noticed that this is implemented
> differently and the identity property is on the subscriber in the new
> version so this is not really a decent test. I'll try to install SQL Server
> 2000 on another box later on and test it this way, but I recommend getting a
> support engineer (PSS) to test this for you as I won't have the time to set
> all this up quickly.
> Regards,
> Paul Ibison
>
>|||Paul, here's my update.
I tried sql server 2005 and Yes Scope_Identity() and @.@.identity worked as
you mentioned but it seems to work differently in 2005.
In 2000 replicated identity columns would be replicated to their base type,
in my case an int. In 2005 it seems to replicate it as an identity column,
which I guess is the reason why the Scope_Identity and @.@.identity work. But
in 2005 it seems like it defaults to Automatic Range Management, but in 2000
I was able to get the identities updating sequentially with no Automatic
Range Management required. Is this feature gone in 2005? I would prefer to
have the immediate updating subscribers get the next identity from the
publisher so that the subscriber and publisher use the same identity
"manager" to get the next identity value (i.e. no identity ranges and no
gaps).
I'm going to do some more testing and keep you up to date.
"Ben Lam" wrote:
> thanks for the help Paul. I will do a quick test on 2005 and see if it
> works. I'll keep you posted.
> "Paul Ibison" wrote:
> > Ben,
> > Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> > when I finally got this set up on 2 networked instances of SQL Server 2005
> > it worked fine - I could pick up both the @.@.identity and the
> > scope_identity() values. However I noticed that this is implemented
> > differently and the identity property is on the subscriber in the new
> > version so this is not really a decent test. I'll try to install SQL Server
> > 2000 on another box later on and test it this way, but I recommend getting a
> > support engineer (PSS) to test this for you as I won't have the time to set
> > all this up quickly.
> > Regards,
> > Paul Ibison
> >
> >
> >
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||No I'm using SQL Server 2000...i may have to move to 2005 sooner than later.
Its hard to believe this can't work because let's say you have an order with
order details tables. You would first create the order on the order table
and get a new Order ID identity, then have to use that identity for the
parent child relationship to the order detail row. This type of scenario
must've been implemented in sql server 2000 with transactional replication
and immediate updates right? How do you retrieve the new ident from the
subscriber without doing maybe a Select max(ident_column) after the insert?
"Paul Ibison" wrote:
> Ben,
> interesting. These identity values are controlled by the publisher rather
> than the subscriber. In fact, you should find that on the subscriber there
> isn't an identity property on the tables for this reason. Are you're using
> SQL Server 2005? If so, you could capture the assigned value using the
> OUTPUT clause.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>|||Ben,
I've just retested this and it depends on your replication setup. If you
have the identity column set to 'Yes (Not for Replication)' then there are
problems, but if it is set to just 'Yes' (the recommended way) then
@.@.identity should return the correct value and scope_identity() will not
return anything. Perhaps this is the source of your issue? Alternatively I
was wondering if you have any user triggers in action?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||I have no triggers on these tables other than the ones created by replication.
Paul, I'm assuming you're asking me if the Publisher has the identity column
set to 'Yes (Not for Replication)'. If so, then no its set to simply "Yes".
In your test environment, was the subscriber on the same server? I tested
this with the subscriber on the same server and the @.@.identity did return the
correct value, but when the subscriber was on another sql server it still
returned null.
so to clarify, my publisher has a identity column with "Yes", and my
subscriber has this column replicated as an int (i.e. no ident column)...the
subscriber column shouldn't have an ident right?
"Paul Ibison" wrote:
> Ben,
> I've just retested this and it depends on your replication setup. If you
> have the identity column set to 'Yes (Not for Replication)' then there are
> problems, but if it is set to just 'Yes' (the recommended way) then
> @.@.identity should return the correct value and scope_identity() will not
> return anything. Perhaps this is the source of your issue? Alternatively I
> was wondering if you have any user triggers in action?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>|||Hi Ben,
Yes - I agree with everything you're saying :)
I'm currently testing your scenario on my home PC (2 databases on same
instance) which catches the @.@.identity (without the NFR attribute). I will
set it up accross servers when I get to work tomorrow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Thank you Paul!!! I've been racking my brain around this for almost 2 days.
My intern worse case scenario is to move to 2005 and use the OUTPUT clause
you mentioned or replacing the "Scope_Identity()" code to a Select
Max(Ident_Column) in the insert sp ...which to me feels more like a hack
thanks again!
"Paul Ibison" wrote:
> Hi Ben,
> Yes - I agree with everything you're saying :)
> I'm currently testing your scenario on my home PC (2 databases on same
> instance) which catches the @.@.identity (without the NFR attribute). I will
> set it up accross servers when I get to work tomorrow.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>|||Ben,
Unfortunately we only have 1 instance of SQL Server 2000 on the network and
when I finally got this set up on 2 networked instances of SQL Server 2005
it worked fine - I could pick up both the @.@.identity and the
scope_identity() values. However I noticed that this is implemented
differently and the identity property is on the subscriber in the new
version so this is not really a decent test. I'll try to install SQL Server
2000 on another box later on and test it this way, but I recommend getting a
support engineer (PSS) to test this for you as I won't have the time to set
all this up quickly.
Regards,
Paul Ibison|||thanks for the help Paul. I will do a quick test on 2005 and see if it
works. I'll keep you posted.
"Paul Ibison" wrote:
> Ben,
> Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> when I finally got this set up on 2 networked instances of SQL Server 2005
> it worked fine - I could pick up both the @.@.identity and the
> scope_identity() values. However I noticed that this is implemented
> differently and the identity property is on the subscriber in the new
> version so this is not really a decent test. I'll try to install SQL Server
> 2000 on another box later on and test it this way, but I recommend getting a
> support engineer (PSS) to test this for you as I won't have the time to set
> all this up quickly.
> Regards,
> Paul Ibison
>
>|||Paul, here's my update.
I tried sql server 2005 and Yes Scope_Identity() and @.@.identity worked as
you mentioned but it seems to work differently in 2005.
In 2000 replicated identity columns would be replicated to their base type,
in my case an int. In 2005 it seems to replicate it as an identity column,
which I guess is the reason why the Scope_Identity and @.@.identity work. But
in 2005 it seems like it defaults to Automatic Range Management, but in 2000
I was able to get the identities updating sequentially with no Automatic
Range Management required. Is this feature gone in 2005? I would prefer to
have the immediate updating subscribers get the next identity from the
publisher so that the subscriber and publisher use the same identity
"manager" to get the next identity value (i.e. no identity ranges and no
gaps).
I'm going to do some more testing and keep you up to date.
"Ben Lam" wrote:
> thanks for the help Paul. I will do a quick test on 2005 and see if it
> works. I'll keep you posted.
> "Paul Ibison" wrote:
> > Ben,
> > Unfortunately we only have 1 instance of SQL Server 2000 on the network and
> > when I finally got this set up on 2 networked instances of SQL Server 2005
> > it worked fine - I could pick up both the @.@.identity and the
> > scope_identity() values. However I noticed that this is implemented
> > differently and the identity property is on the subscriber in the new
> > version so this is not really a decent test. I'll try to install SQL Server
> > 2000 on another box later on and test it this way, but I recommend getting a
> > support engineer (PSS) to test this for you as I won't have the time to set
> > all this up quickly.
> > Regards,
> > Paul Ibison
> >
> >
> >
Does scope_identity or @@identity always return NULL on Subscriber
I have insert sp's that run on a subscriber. After the insert sql statement
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I use scope_identity to get the last inserted identity for child rows for
parent child relationships.
After implementing transactional replication with immediate updates, calling
this sp on the subscriber inserts the row properly but both scope_identity
and @.@.identity returns null.
How do I get the identity value of the last inserted row on a subscriber so
I can use that ID for child rows?Ben,
interesting. These identity values are controlled by the publisher rather
than the subscriber. In fact, you should find that on the subscriber there
isn't an identity property on the tables for this reason. Are you're using
SQL Server 2005? If so, you could capture the assigned value using the
OUTPUT clause.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Sunday, February 19, 2012
DocumentID not returned in Adventureworks
The Large Binary Object example installed with SQL Server 2005 has a table with the DocumentID as the key. When the row is inserted it should return the DocumentID that was created. When you debug the example stored procedure you can see the documentID is created and correct. When you run the sample unmodified it always returns -1 as the documentID. It appears the stored procedure is catching an exception and assigning the documentID a value of -1 as seen here in the Adventureworks LOB sample sored procedure (below).
However, it appears that something with the datbase engine or proce is broken and Microsoft knew this becasue the assignment to documentID upon return from the stored proc is commented out (see below). In fact the whole sample is faked because when you follow it you realize that it is reading into the database one document and writing out one that already existed in the database..
Does anyone know how to get the DocumentID back from the stored procedure that inserted the binary object into the Documents table of Adventureworks?
Thanks in advance.
Portion of Adventure works usp_InsertDocument
RAISERROR ('Insert failed.', 16, 1);
END TRY
BEGIN CATCH
SET @.DocumentID = -1;
RETURN(1);
END CATCH;
END -- END of usp_InsertDocument
;
Portion of LargeBinaryObject.cs
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
using (SqlCommand sprocCommand = new SqlCommand("[Production].[usp_InsertDocument]", sqlConn))
{
sqlConn.Open();
sprocCommand.CommandType = CommandType.StoredProcedure;
// Add time to the title because there is an unique constraint on this column.
sprocCommand.Parameters.Add(new SqlParameter("@.Title", SqlDbType.NVarChar, 50));
sprocCommand.Parameters[0].Value = fileName + DateTime.Now.TimeOfDay.ToString();
sprocCommand.Parameters.Add(new SqlParameter("@.FileName", SqlDbType.NVarChar, 400));
sprocCommand.Parameters[1].Value = fullFileName;
sprocCommand.Parameters.Add(new SqlParameter("@.FileExtension", SqlDbType.NVarChar, 8));
sprocCommand.Parameters[2].Value = fileExtension;
sprocCommand.Parameters.Add(new SqlParameter("@.Status", SqlDbType.TinyInt));
sprocCommand.Parameters[3].Value = 1;
sprocCommand.Parameters.Add(new SqlParameter("@.Document", SqlDbType.Image));
sprocCommand.Parameters[4].Value = bytes;
sprocCommand.Parameters.Add(new SqlParameter("@.DocumentID", SqlDbType.Int));
sprocCommand.Parameters[5].Direction = ParameterDirection.Output;
sprocCommand.ExecuteNonQuery();
//int DocumentID = (int)sprocCommand.Parameters[5].Value;
}
This here is a pretty easy one:
SqlCommand cmd = new SqlCommand("usp_YourProc");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
SqlParameter param_InputoutputTest = new SqlParameter("@.InputoutputTest", SqlDbType.Int);
param_InputoutputTest.Value = "1";
param_InputoutputTest.Direction = ParameterDirection.InputOutput;
SqlParameter param_ReturntTest = new SqlParameter("@.ReturntTest", SqlDbType.Int);
param_ReturntTest.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(param_InputoutputTest);
cmd.Parameters.Add(param_ReturntTest);
conn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine(cmd.Parameters["@.ReturntTest"].Value.ToString());
Console.WriteLine(cmd.Parameters["@.InputoutputTest"].Value.ToString());
conn.Close();
ALTER PROCEDURE usp_YourProc
(
@.InputoutputTest INT OUTPUT
)
AS
SELECT @.InputoutputTest = -1
RETURN 2
HTH, Jens K. Suessmeyer.
-
http://www.sqlserver2005.de
-
Subscribe to:
Posts (Atom)