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

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

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

Does sql express license limits number of user / connections?

I can't find the answer...it just talk about the CPU / ram /database limit. thanks for you help!
Hi,

no current query limit anymore, the query governour went into retirement :-). Only limitations like those you mentioned are applied.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
sql

Does shrinking a user database cause tempdb to grow?

I'm looking at all the possible issues that may have contributed to an
inexplicably large jump in size for my tempdb. I recall reading an article
or posting that stated shrinking a user database will cause tempdb to grow.
Can anyone provide insight on this?Sorry, I misread "tempdb" as "log file" ... Disregard my previous reply
(although, you should still read that article anyway -- it's very
informative). I'm not aware of any condition that would cause tempdb to
grow during a shrink.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>|||Yes:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>|||AFAIK, tempdb is not used for a shrink. The pages are quite simply moved toward the beginning of the
file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rod Bautista" <rod.bautista@.cox.net> wrote in message news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an article
> or posting that stated shrinking a user database will cause tempdb to grow.
> Can anyone provide insight on this?
>|||Thanks Adam and Tibor.
I thought as much...I was certain somebody stated that in a post and the
idea stuck with me. It didn't make any sense for temdb to be affected
during a shrink operation but I had to throw the question out there.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u$OUb8jqEHA.3464@.tk2msftngp13.phx.gbl...
> AFAIK, tempdb is not used for a shrink. The pages are quite simply moved
toward the beginning of the
> file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> > I'm looking at all the possible issues that may have contributed to an
> > inexplicably large jump in size for my tempdb. I recall reading an
article
> > or posting that stated shrinking a user database will cause tempdb to
grow.
> > Can anyone provide insight on this?
> >
> >
>|||I know others have replied with the same answer, but I'll also say that from
the code in shrink there's nothing that touches or affects tempdb.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>

Does shrinking a user database cause tempdb to grow?

I'm looking at all the possible issues that may have contributed to an
inexplicably large jump in size for my tempdb. I recall reading an article
or posting that stated shrinking a user database will cause tempdb to grow.
Can anyone provide insight on this?
Yes:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>
|||Sorry, I misread "tempdb" as "log file" ... Disregard my previous reply
(although, you should still read that article anyway -- it's very
informative). I'm not aware of any condition that would cause tempdb to
grow during a shrink.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>
|||AFAIK, tempdb is not used for a shrink. The pages are quite simply moved toward the beginning of the
file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rod Bautista" <rod.bautista@.cox.net> wrote in message news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an article
> or posting that stated shrinking a user database will cause tempdb to grow.
> Can anyone provide insight on this?
>
|||Thanks Adam and Tibor.
I thought as much...I was certain somebody stated that in a post and the
idea stuck with me. It didn't make any sense for temdb to be affected
during a shrink operation but I had to throw the question out there.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u$OUb8jqEHA.3464@.tk2msftngp13.phx.gbl...
> AFAIK, tempdb is not used for a shrink. The pages are quite simply moved
toward the beginning of the
> file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
article[vbcol=seagreen]
grow.
>
|||I know others have replied with the same answer, but I'll also say that from
the code in shrink there's nothing that touches or affects tempdb.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>

Does RS process sleep?

I tried both SQL2000 & SQL2005.
But both servers RS processes sleep if no users access to them over an hour.
Once it sleeps, the user has to wait 20 seconds to start responding the
request.
Is this normal? Is there anyway to prevent this waiting time?
Please help!!What I do is have a very simple report that auto-refreshes every 5 minutes.
That keeps it alive. There is also an IIS configuration that can be set but
I haven't used it and I don't remember it off the top of my head. The
auto-refresh is pretty easy hack though.
Instead of 5 minutes you could set it longer than than (30 minutes?).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aki Nomura" <anomura@.jtb.com> wrote in message
news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>I tried both SQL2000 & SQL2005.
> But both servers RS processes sleep if no users access to them over an
> hour.
> Once it sleeps, the user has to wait 20 seconds to start responding the
> request.
> Is this normal? Is there anyway to prevent this waiting time?
> Please help!!
>
>|||Auto-refreshes means scheduling report?
Actually I tried an every 3 minutes schedule report.
But it didn't work for my case.
Anyway, I will try the IIS configuration.
Thanks.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> What I do is have a very simple report that auto-refreshes every 5
> minutes. That keeps it alive. There is also an IIS configuration that can
> be set but I haven't used it and I don't remember it off the top of my
> head. The auto-refresh is pretty easy hack though.
> Instead of 5 minutes you could set it longer than than (30 minutes?).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>|||Layout, report tab, autorefreshes checkbox. Once you have a report that
autorefreshes then open it and leave it up. This has worked for me both with
RS 2000 and RS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aki Nomura" <anomura@.jtb.com> wrote in message
news:OzI$fDD$FHA.504@.TK2MSFTNGP09.phx.gbl...
> Auto-refreshes means scheduling report?
> Actually I tried an every 3 minutes schedule report.
> But it didn't work for my case.
> Anyway, I will try the IIS configuration.
> Thanks.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that can
>> be set but I haven't used it and I don't remember it off the top of my
>> head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>|||Yes, it works.
Thanks again.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:egpp%23KE$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Layout, report tab, autorefreshes checkbox. Once you have a report that
> autorefreshes then open it and leave it up. This has worked for me both
> with RS 2000 and RS 2005.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:OzI$fDD$FHA.504@.TK2MSFTNGP09.phx.gbl...
>> Auto-refreshes means scheduling report?
>> Actually I tried an every 3 minutes schedule report.
>> But it didn't work for my case.
>> Anyway, I will try the IIS configuration.
>> Thanks.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that
>> can be set but I haven't used it and I don't remember it off the top of
>> my head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>>
>|||That is called Application Pool in IIS. Go to IIS Manager, under Applocation
Pools node, right click "DefaultAppPool" in which the Reporting Server work
process is running, select properties. On "Performace" tag, you will see, by
default, the app pool will shut down if being idle for 20 min. You can
extend this time to 8x60min 480min, so that the app pool will not shut down
for a regular working day. However, the first report reader of the day, will
hit the delay. You may schedule a dummy report at beginning of a work day
for this.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> What I do is have a very simple report that auto-refreshes every 5
> minutes. That keeps it alive. There is also an IIS configuration that can
> be set but I haven't used it and I don't remember it off the top of my
> head. The auto-refresh is pretty easy hack though.
> Instead of 5 minutes you could set it longer than than (30 minutes?).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aki Nomura" <anomura@.jtb.com> wrote in message
> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>|||Wow, This is what I was looking for.
Thank you.
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:uSWtdkQ$FHA.532@.TK2MSFTNGP15.phx.gbl...
> That is called Application Pool in IIS. Go to IIS Manager, under
> Applocation Pools node, right click "DefaultAppPool" in which the
> Reporting Server work process is running, select properties. On
> "Performace" tag, you will see, by default, the app pool will shut down if
> being idle for 20 min. You can extend this time to 8x60min 480min, so that
> the app pool will not shut down for a regular working day. However, the
> first report reader of the day, will hit the delay. You may schedule a
> dummy report at beginning of a work day for this.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OXDxAt6%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>> What I do is have a very simple report that auto-refreshes every 5
>> minutes. That keeps it alive. There is also an IIS configuration that can
>> be set but I haven't used it and I don't remember it off the top of my
>> head. The auto-refresh is pretty easy hack though.
>> Instead of 5 minutes you could set it longer than than (30 minutes?).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Aki Nomura" <anomura@.jtb.com> wrote in message
>> news:%23TPmKK1%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>>I tried both SQL2000 & SQL2005.
>> But both servers RS processes sleep if no users access to them over an
>> hour.
>> Once it sleeps, the user has to wait 20 seconds to start responding the
>> request.
>> Is this normal? Is there anyway to prevent this waiting time?
>> Please help!!
>>
>>
>

Does not have permission to register endpoint

I came across with the problem that I cannot create an endpoint on Windows Server 2003 (SP1). It's said:

Msg 7850, Level 16, State 1, Line 2

The user 'yyyyy\xxxx' does not have permission to register endpoint 'zzzzzz' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.

Msg 7807, Level 16, State 1, Line 2

An error ('0x80070005') occurred while attempting to register the endpoint 'SecurityServices'.

My code looks loke this:

IF EXISTS (

SELECT name from sys.http_endpoints

WHERE name = 'zzzzzz'

)

DROP ENDPOINT zzzzzz

GO

CREATE ENDPOINT zzzzzz

STATE = STARTED

AS HTTP (

path='/sql/zzzzzz',

AUTHENTICATION=(INTEGRATED),

PORTS = (CLEAR)

)

FOR SOAP(

WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'),

WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),

WSDL = DEFAULT,

BATCHES=ENABLED)

GO

-- End of Script --
The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005.

If anyone has an idea about my problem, please help me !!!

Thank you,
POP

I believe the problem you run into is because when you create an endpoint, you are not executing under the logged in user, but as the account the SQL Server runs under. In your case the account on the remote server most likely have very restricted priviliges. To fix this you need to reserve the namespace you are going to create, before you can actually create it.
To reserve it, you run the un-doc:ed stored procedure sp_reserve_http_namespace. You run it with the macine name (as it will appear in the site param in CREATE ENDPOINT), portnumber and virtual directory name as in the path param in CREATE ENDPOINT.
An example would be:
sp_reserve_http_namespace N'http://Perth:80/emp', which then would have the following CREATE ENPOINT statement:
<<<<<<<<<<<<<<<
create endpoint EmpClass
state = started
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
>>>>>>>>>>>>>>>
Hope this helps!!
Niels

|||Thank you so much Niels (again),

It's works !!!!

At first, I try to use >> sp_reserve_http_namespace N'http://Perth:80/emp'
first but it said >> A reservation for this HTTP namespace (http://Perth:80/emp) already exists

But when I look at your code
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
I don't have the value of the site (yellow highlighted part). Then I add that part and when I run the code on the server, it works !!!!.

Without that line, I can create an end point on my local computer but not on the server (Cluster Servers). So from now on, I'll have that line all the time.

Thank you again Niels,

POP|||It works. Thank you.

Does not have access to the Analysis Services Project1 database

I am trying to deploye but I have a mistake

TITLE: Microsoft SQL Server 2005 Analysis Services

Either the user, myCompter\Administrator, does not have access to the Analysis Services Project1 database, or the database does not exist.

Error 1 Parser: The syntax for the ImpersonationInfo object is incorrect. If the ImpersonateAccount value is used for ImpersonationInfo, then the Account property cannot be empty. 0 0

Did you figure it out. I am faced with the same issue.

Please help.

Does MS Access installation is required for running application that uses Access mdb file

Hi,

I am developing an application that uses Access database (mdb file) to store the user data. The user of this application is not interested in the database file (to view in MS Access Environment). Does the user machine requires MS Access installation to run my application or just some couple of dlls (OleDB driver, Access DB Engine,..) should be enough to run my application?

Thanks,

Rao

No, they don't need Access. They will simply need to have MDAC installed, and chances are it will already be there. If not get it here

http://msdn.microsoft.com/data/ref/mdac/

|||

The Information is helpful.

Thanks

Does it require sa to create dts package?

what privilege does the user has to have to create a dts package?By default, anyone with a login can create a Package as the
public role has execute permissions on sp_add_dtspackage
stored procedure in msdb and the guest account is enabled in
msdb by default so it depends on whether you have changed
this or not.
But they don't need to be a sysadmin to create packages.
It's generally controlled through sp_add_dtspackage.
-Sue
On Thu, 24 Feb 2005 09:35:07 -0800, "sabby"
<sabby@.discussions.microsoft.com> wrote:

>what privilege does the user has to have to create a dts package?|||Thank you so much.
"sabby" wrote:

> what privilege does the user has to have to create a dts package?

Does have as protecting the data of a bank SQL-SERVER?

Does have as protecting the data of a bank SQL-SERVER?
-no, because if the user has access to the files ' * .mdf ', he will simply
be able to enclose them in a server where the user ' sa' doesn't have
password and ready, will have access to the data.
does Sql_Server have safety?
-has, inside of the server of your company, because there ' Administrador'
can protect the accesses and the files, but if somebody has access the this
machine (server), this everything finish (for the safety);
After this chat, was I thinking (?), because it will be then that cannot use
MSACCESS for the database (due to safety), since Sql_server leaves me in the
same position.
I hope sincerely, the result of this chat, be not 100% correct!, because it
reduces like this my deception in relation to the " ALL POWERFUL * "
Sql-Server.Assuming the situation of a bank,
- physical security is just as important as application security. You
should already take the appropriate steps to protect your server physically.
- I do not think you can ever fit a bank's entire database into a removable
storage device for you to attach to another server (which would need just as
large a storage device as the source server)
Do tell, which mainstream database server protects its data files? At the
minimum, you would need to encrypt the contents lest you use a hex editor to
view the data directly, and encryption eats up cpu cycles.
--
Regards
Ray Mond
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:e994$ARHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> Does have as protecting the data of a bank SQL-SERVER?
> -no, because if the user has access to the files ' * .mdf ', he will
simply
> be able to enclose them in a server where the user ' sa' doesn't have
> password and ready, will have access to the data.
> does Sql_Server have safety?
> -has, inside of the server of your company, because there ' Administrador'
> can protect the accesses and the files, but if somebody has access the
this
> machine (server), this everything finish (for the safety);
>
> After this chat, was I thinking (?), because it will be then that cannot
use
> MSACCESS for the database (due to safety), since Sql_server leaves me in
the
> same position.
> I hope sincerely, the result of this chat, be not 100% correct!, because
it
> reduces like this my deception in relation to the " ALL POWERFUL * "
> Sql-Server.
>
>

Does have as protecting the data of a bank SQL-SERVER?

Does have as protecting the data of a bank SQL-SERVER?
-no, because if the user has access to the files ' * .mdf ', he will simply
be able to enclose them in a server where the user ' sa' doesn't have
password and ready, will have access to the data.
does Sql_Server have safety?
-has, inside of the server of your company, because there ' Administrador'
can protect the accesses and the files, but if somebody has access the this
machine (server), this everything finish (for the safety);
After this chat, was I thinking (?), because it will be then that cannot use
MSACCESS for the database (due to safety), since Sql_server leaves me in the
same position.
I hope sincerely, the result of this chat, be not 100% correct!, because it
reduces like this my deception in relation to the " ALL POWERFUL * "
Sql-Server.
Assuming the situation of a bank,
- physical security is just as important as application security. You
should already take the appropriate steps to protect your server physically.
- I do not think you can ever fit a bank's entire database into a removable
storage device for you to attach to another server (which would need just as
large a storage device as the source server)
Do tell, which mainstream database server protects its data files? At the
minimum, you would need to encrypt the contents lest you use a hex editor to
view the data directly, and encryption eats up cpu cycles.
Regards
Ray Mond
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:e994$ARHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> Does have as protecting the data of a bank SQL-SERVER?
> -no, because if the user has access to the files ' * .mdf ', he will
simply
> be able to enclose them in a server where the user ' sa' doesn't have
> password and ready, will have access to the data.
> does Sql_Server have safety?
> -has, inside of the server of your company, because there ' Administrador'
> can protect the accesses and the files, but if somebody has access the
this
> machine (server), this everything finish (for the safety);
>
> After this chat, was I thinking (?), because it will be then that cannot
use
> MSACCESS for the database (due to safety), since Sql_server leaves me in
the
> same position.
> I hope sincerely, the result of this chat, be not 100% correct!, because
it
> reduces like this my deception in relation to the " ALL POWERFUL * "
> Sql-Server.
>
>

Does have as protecting the data of a bank SQL-SERVER?

Does have as protecting the data of a bank SQL-SERVER?
-no, because if the user has access to the files ' * .mdf ', he will simply
be able to enclose them in a server where the user ' sa' doesn't have
password and ready, will have access to the data.
does Sql_Server have safety?
-has, inside of the server of your company, because there ' Administrador'
can protect the accesses and the files, but if somebody has access the this
machine (server), this everything finish (for the safety);
After this chat, was I thinking (?), because it will be then that cannot use
MSACCESS for the database (due to safety), since Sql_server leaves me in the
same position.
I hope sincerely, the result of this chat, be not 100% correct!, because it
reduces like this my deception in relation to the " ALL POWERFUL * "
Sql-Server.Assuming the situation of a bank,
- physical security is just as important as application security. You
should already take the appropriate steps to protect your server physically.
- I do not think you can ever fit a bank's entire database into a removable
storage device for you to attach to another server (which would need just as
large a storage device as the source server)
Do tell, which mainstream database server protects its data files? At the
minimum, you would need to encrypt the contents lest you use a hex editor to
view the data directly, and encryption eats up cpu cycles.
Regards
Ray Mond
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:e994$ARHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> Does have as protecting the data of a bank SQL-SERVER?
> -no, because if the user has access to the files ' * .mdf ', he will
simply
> be able to enclose them in a server where the user ' sa' doesn't have
> password and ready, will have access to the data.
> does Sql_Server have safety?
> -has, inside of the server of your company, because there ' Administrador'
> can protect the accesses and the files, but if somebody has access the
this
> machine (server), this everything finish (for the safety);
>
> After this chat, was I thinking (?), because it will be then that cannot
use
> MSACCESS for the database (due to safety), since Sql_server leaves me in
the
> same position.
> I hope sincerely, the result of this chat, be not 100% correct!, because
it
> reduces like this my deception in relation to the " ALL POWERFUL * "
> Sql-Server.
>
>

does enyone know...

Does enyone know how to get all byte[] from records in database.
When user click on the button to give him all record in some arhive??

Some code?(C#)

Thanx

OgaCould you ask your question in a different way, giving more explanation?|||In datatable I have field with type: "binary data", and I put into it some jpeg, doc, etc files. Then I have a web form with download button, when user click it i wont to give him all filesfrom that datatable.

I hope this explanation is better|||You might find this blog post (as well as the links it contains) to be helpful:

How to serve binary resouces from a database

Does anyone know if guest account must be enabled?

Hi a user is not able to run a windows application that uses SQL
authentication and was just wondering if the guest account have to be enabled
on the SQL Server for SQL authentication to work?
Thanks.
Paul G
Software engineer.
There is a thread from Kimberly:
http://groups.google.de/group/micros...b922f9b1e2a009
HTH, Jens Suessmeyer.
"Paul" wrote:

> Hi a user is not able to run a windows application that uses SQL
> authentication and was just wondering if the guest account have to be enabled
> on the SQL Server for SQL authentication to work?
> Thanks.
> --
> Paul G
> Software engineer.
|||No need for any type of guest account.
Only time I heard about that is if you are in separate domains and you don't have trusts and you are
using the Named Pipes netlib, which depends on the IPC$ share). I don't know if this is still the
case, but in any case moving to the IP netlib should remove that need.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:690F2DCE-4020-48A3-ACE7-36006029871C@.microsoft.com...
> Hi a user is not able to run a windows application that uses SQL
> authentication and was just wondering if the guest account have to be enabled
> on the SQL Server for SQL authentication to work?
> Thanks.
> --
> Paul G
> Software engineer.
|||ok thanks for the information. Sounds like it does not effect SQL login
Paul G
Software engineer.
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> There is a thread from Kimberly:
> http://groups.google.de/group/micros...b922f9b1e2a009
> HTH, Jens Suessmeyer.
> "Paul" wrote:
|||To me it wasn't clear whether you refer to the guest user inside a database, if so see Jens' reply.
Or if you refer to a Gust Windows account, if so, see my reply.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C9EE6BC1-2F9B-4E9B-A585-DABB6A6F22B6@.microsoft.com...[vbcol=seagreen]
> ok thanks for the information. Sounds like it does not effect SQL login
> --
> Paul G
> Software engineer.
>
> "Jens Sü?meyer" wrote:

Does anyone know if guest account must be enabled?

Hi a user is not able to run a windows application that uses SQL
authentication and was just wondering if the guest account have to be enable
d
on the SQL Server for SQL authentication to work?
Thanks.
--
Paul G
Software engineer.There is a thread from Kimberly:
http://groups.google.de/group/micro...8b922f9b1e2a009
HTH, Jens Suessmeyer.
"Paul" wrote:

> Hi a user is not able to run a windows application that uses SQL
> authentication and was just wondering if the guest account have to be enab
led
> on the SQL Server for SQL authentication to work?
> Thanks.
> --
> Paul G
> Software engineer.|||No need for any type of guest account.
Only time I heard about that is if you are in separate domains and you don't
have trusts and you are
using the Named Pipes netlib, which depends on the IPC$ share). I don't know
if this is still the
case, but in any case moving to the IP netlib should remove that need.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:690F2DCE-4020-48A3-ACE7-36006029871C@.microsoft.com...
> Hi a user is not able to run a windows application that uses SQL
> authentication and was just wondering if the guest account have to be enab
led
> on the SQL Server for SQL authentication to work?
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks for the information. Sounds like it does not effect SQL login
--
Paul G
Software engineer.
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> There is a thread from Kimberly:
> http://groups.google.de/group/micro...8b922f9b1e2a009
> HTH, Jens Suessmeyer.
> "Paul" wrote:
>|||To me it wasn't clear whether you refer to the guest user inside a database,
if so see Jens' reply.
Or if you refer to a Gust Windows account, if so, see my reply.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C9EE6BC1-2F9B-4E9B-A585-DABB6A6F22B6@.microsoft.com...[vbcol=seagreen]
> ok thanks for the information. Sounds like it does not effect SQL login
> --
> Paul G
> Software engineer.
>
> "Jens Sü?meyer" wrote:
>

Does anyone know if guest account must be enabled?

Hi a user is not able to run a windows application that uses SQL
authentication and was just wondering if the guest account have to be enabled
on the SQL Server for SQL authentication to work?
Thanks.
--
Paul G
Software engineer.There is a thread from Kimberly:
http://groups.google.de/group/microsoft.public.sqlserver.security/browse_thread/thread/ea9205103023d109/78b922f9b1e2a009?q=guest+account+sql+server+microsoft&rnum=3&hl=de#78b922f9b1e2a009
HTH, Jens Suessmeyer.
"Paul" wrote:
> Hi a user is not able to run a windows application that uses SQL
> authentication and was just wondering if the guest account have to be enabled
> on the SQL Server for SQL authentication to work?
> Thanks.
> --
> Paul G
> Software engineer.|||No need for any type of guest account.
Only time I heard about that is if you are in separate domains and you don't have trusts and you are
using the Named Pipes netlib, which depends on the IPC$ share). I don't know if this is still the
case, but in any case moving to the IP netlib should remove that need.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:690F2DCE-4020-48A3-ACE7-36006029871C@.microsoft.com...
> Hi a user is not able to run a windows application that uses SQL
> authentication and was just wondering if the guest account have to be enabled
> on the SQL Server for SQL authentication to work?
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks for the information. Sounds like it does not effect SQL login
--
Paul G
Software engineer.
"Jens Sü�meyer" wrote:
> There is a thread from Kimberly:
> http://groups.google.de/group/microsoft.public.sqlserver.security/browse_thread/thread/ea9205103023d109/78b922f9b1e2a009?q=guest+account+sql+server+microsoft&rnum=3&hl=de#78b922f9b1e2a009
> HTH, Jens Suessmeyer.
> "Paul" wrote:
> > Hi a user is not able to run a windows application that uses SQL
> > authentication and was just wondering if the guest account have to be enabled
> > on the SQL Server for SQL authentication to work?
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||To me it wasn't clear whether you refer to the guest user inside a database, if so see Jens' reply.
Or if you refer to a Gust Windows account, if so, see my reply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C9EE6BC1-2F9B-4E9B-A585-DABB6A6F22B6@.microsoft.com...
> ok thanks for the information. Sounds like it does not effect SQL login
> --
> Paul G
> Software engineer.
>
> "Jens Sü�meyer" wrote:
>> There is a thread from Kimberly:
>> http://groups.google.de/group/microsoft.public.sqlserver.security/browse_thread/thread/ea9205103023d109/78b922f9b1e2a009?q=guest+account+sql+server+microsoft&rnum=3&hl=de#78b922f9b1e2a009
>> HTH, Jens Suessmeyer.
>> "Paul" wrote:
>> > Hi a user is not able to run a windows application that uses SQL
>> > authentication and was just wondering if the guest account have to be enabled
>> > on the SQL Server for SQL authentication to work?
>> > Thanks.
>> > --
>> > Paul G
>> > Software engineer.

Does a user have EXEC permission for a stored procedure?

Given a user name and a stored procedure name, can someone provide me with
the TSQL code to set a boolean indicating whether that user has EXEC
permission to that proc?
The specific proc I'm interested in is xp_loginconfig.
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc.SQL Server Books Online states that the execute permissions for
xp_loginconfig default to members of the db_owner fixed database role in the
master database and members of the symin fixed server role, but can be
granted to other database users. So if the user is not a member of the dbo
role in master, you can use a simple GRANT statement like:
GRANT EXECUTE ON xp_loginconfig TO <youruser> ;
Anith|||Assuming a proc named 'myproc' and a user 'Jones', here's a snippet that
will check for exec priv.
declare @.name sysname, @.myboolean bit
set @.name = 'Jones'
if exists (select *
from sysprotects
where id = object_id('myproc')
and uid = (select uid from sysusers where name = @.name)
and action = 224
and protecttype <> 206)
set @.myboolean = 1
else
set @.myboolean = 0
select @.myboolean
"Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
news:ADE5D31A-7F11-4782-A560-EDEA7E89D3CA@.microsoft.com...
> Given a user name and a stored procedure name, can someone provide me with
> the TSQL code to set a boolean indicating whether that user has EXEC
> permission to that proc?
> The specific proc I'm interested in is xp_loginconfig.
> Thanks in advance for your help,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.|||Hi Armando,
Thanks for your reply - it certainly gave me some things to try.
First off, if I run:
SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('xp_loginconfig')
)
I get zero back. Also
SELECT COUNT(*) FROM dbo.sysobjects WHERE (id = OBJECT_ID('xp_loginconfig'))
returns zero. So this looks like a dead end.
Now if I run:
SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('myproc'))
I get one back. So far so good. Next I run:
SELECT dbo.sysusers.name FROM dbo.sysprotects INNER JOIN
dbo.sysusers ON dbo.sysprotects.uid = dbo.sysusers.uid
WHERE (dbo.sysprotects.id = OBJECT_ID('myproc'))
which returns a single name, but not 'Jones' - who does have execute
permission.
So this also looks like a dead end.
Any thoughts or suggestions?
Thanks, Hal
"Armando Prato" wrote:

> Assuming a proc named 'myproc' and a user 'Jones', here's a snippet that
> will check for exec priv.
> declare @.name sysname, @.myboolean bit
> set @.name = 'Jones'
> if exists (select *
> from sysprotects
> where id = object_id('myproc')
> and uid = (select uid from sysusers where name = @.name)
> and action = 224
> and protecttype <> 206)
> set @.myboolean = 1
> else
> set @.myboolean = 0
> select @.myboolean
>
> "Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
> news:ADE5D31A-7F11-4782-A560-EDEA7E89D3CA@.microsoft.com...
>
>|||Anith,
Thank you for replying. However, my question is not "how do you grant?",
rather it is "how can you tell?"
Hal
"Anith Sen" wrote:

> SQL Server Books Online states that the execute permissions for
> xp_loginconfig default to members of the db_owner fixed database role in t
he
> master database and members of the symin fixed server role, but can be
> granted to other database users. So if the user is not a member of the dbo
> role in master, you can use a simple GRANT statement like:
> GRANT EXECUTE ON xp_loginconfig TO <youruser> ;
> --
> Anith
>
>|||Couple of things come to mind
Did you run against the master database? Extended stored procs live
in master and are not found in newly created databases. Also, did you
explicitly
grant execute to the user(s) in question?
The query...
SELECT dbo.sysusers.name
FROM dbo.sysprotects
JOIN dbo.sysusers ON (dbo.sysprotects.uid = dbo.sysusers.uid)
WHERE (dbo.sysprotects.id = OBJECT_ID('xp_loginconfig'))
...looks ok. It just sounds like you need to explicitly grant execute
to all users you expect will have this permission outside of the dbo.
Here is the BOL snippet on this xp:
Execute permissions for xp_loginconfig default to members of the db_owner
fixed database role in the master database and members of the symin fixed
server role, but can be granted to other users.
"Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
news:81ED433C-8190-45F1-8FC5-07ED38DDCFD4@.microsoft.com...
> Hi Armando,
> Thanks for your reply - it certainly gave me some things to try.
> First off, if I run:
> SELECT COUNT(*) FROM dbo.sysprotects WHERE (id =
OBJECT_ID('xp_loginconfig'))ed">
> I get zero back. Also
> SELECT COUNT(*) FROM dbo.sysobjects WHERE (id =
OBJECT_ID('xp_loginconfig'))[color=darkr
ed]
> returns zero. So this looks like a dead end.
> Now if I run:
> SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('myproc'))
> I get one back. So far so good. Next I run:
> SELECT dbo.sysusers.name FROM dbo.sysprotects INNER JOIN
> dbo.sysusers ON dbo.sysprotects.uid = dbo.sysusers.uid
> WHERE (dbo.sysprotects.id = OBJECT_ID('myproc'))
> which returns a single name, but not 'Jones' - who does have execute
> permission.
> So this also looks like a dead end.
> Any thoughts or suggestions?
> Thanks, Hal
> "Armando Prato" wrote:
>
that
with|||>> However, my question is not "how do you grant?", rather it is "how can
Have you looked in to the PERMISSIONS() function?
Anith

Does a role exist?

In my proc I have to add a user to a role in another database. I do it
in the following manner:
declare @.IIDdatabase varchar(255)
set @.IIDdatabase = 'IID'
SELECT @.cmd = N'USE ' + CAST(@.IIDdatabase as nvarchar) + @.crlf + N'exec
sp_addrolemember @.rolename = ''Role_MyCustomRole'', @.membername = ''' +
CAST(@.user as nvarchar) + '''' + @.crlf
EXEC @.STATUS = sp_executesql @.cmd
And this seems to work fine. However, how do I find out whether the
role (in the other database) exists to begin with? The difficulty here
is that the name of the database is passed in as a variable.
Thanks.Hi Frank,
I understand that you would like to judge wheter the database role is
existed before your execute your SQL statement.
If I have misunderstood, please let me know.
You may refer to the following stored procedure:
---
/* judge if a database role is existed in a database */
CREATE PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
You can call it like this:
----
--
declare @.isexisted int
exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
if @.isexisted=0
begin
..
end
----
--
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I am sorry, I should have mentioned. The code must work on SQL 2000 as
well as SQL 2005.
Charles Wang[MSFT] wrote:
> Hi Frank,
> I understand that you would like to judge wheter the database role is
> existed before your execute your SQL statement.
> If I have misunderstood, please let me know.
> You may refer to the following stored procedure:
> ---
> /* judge if a database role is existed in a database */
> CREATE PROCEDURE proc_existrole
> (
> @.dbname nvarchar(50)=null,
> @.rolename nvarchar(50)=null,
> @.isexisted int output
> )
> as
> declare @.strSQL nvarchar(200)
> declare @.strCount nvarchar(50)
> begin
> if @.dbname is null or @.rolename is null
> begin
> set @.isexisted = -1
> return
> end
> set @.strCount = N'@.nCount int output'
> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
> if @.isexisted>0
> set @.isexisted = 1
> else
> set @.isexisted = 0
> end
> ----
> --
> You can call it like this:
> ----
> --
> declare @.isexisted int
> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
> if @.isexisted=0
> begin
> ..
> end
> ----
> --
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Frank,
This will still work on both 2000 and 2005, although it is certainly
deprecated.
SELECT name
FROM sysusers
WHERE issqlrole = 1
RLF
"Frank Rizzo" <none@.none.com> wrote in message
news:uEsuyFB5HHA.2108@.TK2MSFTNGP02.phx.gbl...
>I am sorry, I should have mentioned. The code must work on SQL 2000 as
>well as SQL 2005.
> Charles Wang[MSFT] wrote:
>> Hi Frank,
>> I understand that you would like to judge wheter the database role is
>> existed before your execute your SQL statement.
>> If I have misunderstood, please let me know.
>> You may refer to the following stored procedure:
>> ---
>> /* judge if a database role is existed in a database */
>> CREATE PROCEDURE proc_existrole
>> (
>> @.dbname nvarchar(50)=null,
>> @.rolename nvarchar(50)=null,
>> @.isexisted int output
>> )
>> as
>> declare @.strSQL nvarchar(200)
>> declare @.strCount nvarchar(50)
>> begin
>> if @.dbname is null or @.rolename is null
>> begin
>> set @.isexisted = -1
>> return
>> end
>> set @.strCount = N'@.nCount int output'
>> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
>> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
>> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
>> if @.isexisted>0
>> set @.isexisted = 1
>> else
>> set @.isexisted = 0
>> end
>> ----
>> --
>> You can call it like this:
>> ----
>> --
>> declare @.isexisted int
>> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
>> if @.isexisted=0
>> begin
>> ..
>> end
>> ----
>> --
>> Hope this helps. If you have any other questions or concerns, please feel
>> free to let me know.
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> ====================================================== When responding to
>> posts, please "Reply to Group" via your newsreader so that others may
>> learn and benefit from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights. ======================================================|||Hi Frank,
In this case, you can combine Russell's suggestion and mine into one
procedure like this:
----
--
/* judge if a database role is existed in a database */
ALTER PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
declare @.productVersion nchar(1)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
SELECT @.productVersion = SUBSTRING(CAST(SERVERPROPERTY ( 'PRODUCTVERSION' )
AS NVARCHAR(10)),1,1)
IF @.productVersion = '9'
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
END
ELSE
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.dbo.sysusers where issqlrole=1 and name='''+@.rolename + ''''
END
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
Please feel free to let us know if you need further assistance on this
issue. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Frank,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles Wang[MSFT] wrote:
> Hi Frank,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
Thanks, your suggestion worked great.