DB access for web apps

I have custom .net web based application running on IIS 6 which has SQL2000
running in background, access to this application is based on UIDs and
password from "users" table in SQL, my question is regarding web.config
file... this file has user name and password that allow web application talk
to SQL db, what sql role should this account have in order to .net
application work corectly? db owner will do but I'm wondering this is too
much...
TIAFor a qick improvement, membership in the
db_datareader (can select all data from any user table in the database) and
db_datawriter (can modify any data in any user table in the database)
roles should be enough. Then you can study grainer permissions needed.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:5045D447-656F-46C8-A48E-29078FFAA294@.microsoft.com...
> I have custom .net web based application running on IIS 6 which has
SQL2000
> running in background, access to this application is based on UIDs and
> password from "users" table in SQL, my question is regarding web.config
> file... this file has user name and password that allow web application
talk
> to SQL db, what sql role should this account have in order to .net
> application work corectly? db owner will do but I'm wondering this is too
> much...
> TIA|||thanks for response, will this allow execute sp_ ?
"Dejan Sarka" wrote:

> For a qick improvement, membership in the
> db_datareader (can select all data from any user table in the database) an
d
> db_datawriter (can modify any data in any user table in the database)
> roles should be enough. Then you can study grainer permissions needed.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
> "Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
> news:5045D447-656F-46C8-A48E-29078FFAA294@.microsoft.com...
> SQL2000
> talk
>
>|||For stored procedures in your database, you will have to give an explicit
EXECUTE permission to this user. I you are talking about system procedures
to get some info, like sp_help, then the user will be able to execute them
without an explicit permission.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:F1EDD860-343A-421A-A303-2FBB25687147@.microsoft.com...[vbcol=seagreen]
> thanks for response, will this allow execute sp_ ?
> "Dejan Sarka" wrote:
>
and[vbcol=seagreen]
web.config[vbcol=seagreen]
application[vbcol=seagreen]
too[vbcol=seagreen]sql

Datetime Issues

Hi,
I have an IIS query (using VB) that selects information
from a table within SQL Server 2000. When the function is
called, it gives error message "Microsoft OLE DB Provider
for SQL Server error '80040e07'". A Datetime error.
The line that fails is:
SELECT Name, Description, Reason, StartDate, EndDate,
Forename, Surname FROM V_GetPartnerships WHERE
PartnershipId = 2 AND (StartDate <= 'Wednesday, October
29, 2003' AND EndDate >= 'Wednesday, October 29, 2003')
ORDER BY name;
I have run this same line successfully by omitting
the "Wednesday, " section from the date fields, because
it does not fit in with the datetime defaults.
I have checked the outputs of the date formats using a
simple VB script, and they are as expected (i.e. October
29, 2003).
Where is it getting this extended date from'Assuming Startdate and Enddate are DATETIME or SMALLDATETIME columns, try
this:
SELECT [name], [description], reason, startdate, enddate,
forename, surname
FROM V_GetPartnerships
WHERE partnershipid = 2
AND '20031029' BETWEEN startdate AND enddate
ORDER BY name
Always specify dates in the ISO format ('YYYYMMDD') which works
independently of any regional settings.
--
David Portas
--
Please reply only to the newsgroup
--|||This is an SQL database and IIS that previously worked
OK, but has been migrated to new servers.
The date and time selections it is using determines the
query (by the use of VB Combo boxes within the website)
and any changes to this would require extensive re-
writing of the VB codes.
The datetime field works OK when using correct date
terms, as previously tested, it is only when it using
this extended 'Wednesday, October 29, 2003' that is NOT
recognised by the field.
Any ideas where it would be getting this time field from?
I.E. IIS or SQL Server properties etc.
Or is it possible to update SQL to allow for the day,
month date, year datetime fields.
>--Original Message--
>Assuming Startdate and Enddate are DATETIME or
SMALLDATETIME columns, try
>this:
>SELECT [name], [description], reason, startdate, enddate,
> forename, surname
> FROM V_GetPartnerships
> WHERE partnershipid = 2
> AND '20031029' BETWEEN startdate AND enddate
> ORDER BY name
>Always specify dates in the ISO format ('YYYYMMDD')
which works
>independently of any regional settings.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||SQL Server does not understand below "extended" format. Take the time to do it right, use the format
recommended by David, and you will not run into all kind of problems later. Or read about the
supported formats in Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_765d.asp?frame=true
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Martin Powell" <anonymous@.discussions.microsoft.com> wrote in message
news:0c4b01c39e29$26996260$a501280a@.phx.gbl...
> This is an SQL database and IIS that previously worked
> OK, but has been migrated to new servers.
> The date and time selections it is using determines the
> query (by the use of VB Combo boxes within the website)
> and any changes to this would require extensive re-
> writing of the VB codes.
> The datetime field works OK when using correct date
> terms, as previously tested, it is only when it using
> this extended 'Wednesday, October 29, 2003' that is NOT
> recognised by the field.
> Any ideas where it would be getting this time field from?
> I.E. IIS or SQL Server properties etc.
> Or is it possible to update SQL to allow for the day,
> month date, year datetime fields.
> >--Original Message--
> >Assuming Startdate and Enddate are DATETIME or
> SMALLDATETIME columns, try
> >this:
> >
> >SELECT [name], [description], reason, startdate, enddate,
> > forename, surname
> > FROM V_GetPartnerships
> > WHERE partnershipid = 2
> > AND '20031029' BETWEEN startdate AND enddate
> > ORDER BY name
> >
> >Always specify dates in the ISO format ('YYYYMMDD')
> which works
> >independently of any regional settings.
> >
> >--
> >David Portas
> >--
> >Please reply only to the newsgroup
> >--
> >
> >
> >.
> >|||I am aware that SQL does not support the format that is
in use, the issue is that it is getting this format from
somewhere within the system.
This database has been developed over several years and
recently migrated to new servers and this error has only
appears on the new system. It DOES NOT affect the old
system.
Any ideas where it would be getting this time field from?
>--Original Message--
>SQL Server does not understand below "extended" format.
Take the time to do it right, use the format
>recommended by David, and you will not run into all kind
of problems later. Or read about the
>supported formats in Books Online:
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/acdata/ac_8_con_03_765d.asp?frame=true
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Martin Powell" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0c4b01c39e29$26996260$a501280a@.phx.gbl...
>> This is an SQL database and IIS that previously worked
>> OK, but has been migrated to new servers.
>> The date and time selections it is using determines the
>> query (by the use of VB Combo boxes within the website)
>> and any changes to this would require extensive re-
>> writing of the VB codes.
>> The datetime field works OK when using correct date
>> terms, as previously tested, it is only when it using
>> this extended 'Wednesday, October 29, 2003' that is NOT
>> recognised by the field.
>> Any ideas where it would be getting this time field
from?
>> I.E. IIS or SQL Server properties etc.
>> Or is it possible to update SQL to allow for the day,
>> month date, year datetime fields.
>> >--Original Message--
>> >Assuming Startdate and Enddate are DATETIME or
>> SMALLDATETIME columns, try
>> >this:
>> >
>> >SELECT [name], [description], reason, startdate,
enddate,
>> > forename, surname
>> > FROM V_GetPartnerships
>> > WHERE partnershipid = 2
>> > AND '20031029' BETWEEN startdate AND enddate
>> > ORDER BY name
>> >
>> >Always specify dates in the ISO format ('YYYYMMDD')
>> which works
>> >independently of any regional settings.
>> >
>> >--
>> >David Portas
>> >--
>> >Please reply only to the newsgroup
>> >--
>> >
>> >
>> >.
>> >
>
>.
>|||Ahh, I see. I'm no ASP expert, but this is where you have to look (in the application code).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:062801c39e2c$1c858d50$a001280a@.phx.gbl...
> I am aware that SQL does not support the format that is
> in use, the issue is that it is getting this format from
> somewhere within the system.
> This database has been developed over several years and
> recently migrated to new servers and this error has only
> appears on the new system. It DOES NOT affect the old
> system.
> Any ideas where it would be getting this time field from?
> >--Original Message--
> >SQL Server does not understand below "extended" format.
> Take the time to do it right, use the format
> >recommended by David, and you will not run into all kind
> of problems later. Or read about the
> >supported formats in Books Online:
> >http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/acdata/ac_8_con_03_765d.asp?frame=true
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Martin Powell" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:0c4b01c39e29$26996260$a501280a@.phx.gbl...
> >> This is an SQL database and IIS that previously worked
> >> OK, but has been migrated to new servers.
> >>
> >> The date and time selections it is using determines the
> >> query (by the use of VB Combo boxes within the website)
> >> and any changes to this would require extensive re-
> >> writing of the VB codes.
> >>
> >> The datetime field works OK when using correct date
> >> terms, as previously tested, it is only when it using
> >> this extended 'Wednesday, October 29, 2003' that is NOT
> >> recognised by the field.
> >>
> >> Any ideas where it would be getting this time field
> from?
> >> I.E. IIS or SQL Server properties etc.
> >>
> >> Or is it possible to update SQL to allow for the day,
> >> month date, year datetime fields.
> >>
> >> >--Original Message--
> >> >Assuming Startdate and Enddate are DATETIME or
> >> SMALLDATETIME columns, try
> >> >this:
> >> >
> >> >SELECT [name], [description], reason, startdate,
> enddate,
> >> > forename, surname
> >> > FROM V_GetPartnerships
> >> > WHERE partnershipid = 2
> >> > AND '20031029' BETWEEN startdate AND enddate
> >> > ORDER BY name
> >> >
> >> >Always specify dates in the ISO format ('YYYYMMDD')
> >> which works
> >> >independently of any regional settings.
> >> >
> >> >--
> >> >David Portas
> >> >--
> >> >Please reply only to the newsgroup
> >> >--
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||If you are asking why IIS or VB is formatting the date this way then I
really don't know. Presumably it's defined by a regional setting on the
server - it looks like a Windows "Long" date format. This isn't a SQLServer
question so you'll probably get more help posting to an IIS group.
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks Guys,
Will try with IIS, now SQL has been ruled out!
>--Original Message--
>If you are asking why IIS or VB is formatting the date
this way then I
>really don't know. Presumably it's defined by a regional
setting on the
>server - it looks like a Windows "Long" date format.
This isn't a SQLServer
>question so you'll probably get more help posting to an
IIS group.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

DateTime Format -

I have installed the trial version of windows server 2003 on the second hard drive on my computer. I set up IIS and ran my website on it but the problem is when I do something on the site, which has a sqlinsert statement regarding datetime.now it says, "conversion failed when converting datetime from character string"

I think it's to do with the clock on server 2003, the format is like: 11/07/2007 2:39:59 a.m.

I think it should be in formatAM and not a.m.

Any ideas on how to change the time format on a computer?

Or should I just change the Columns in my table to a Nvarcher value or something?

thanks

how is the value coming through? from your application? via now() ?

|||

Hi,

Thanks for your reply

What do you mean via now()?

I'm using VB and if I use something like. sqldatasource1.insertparameters.add("enddate", datetime.now()) it will give the format: 11/07/2007 2:39:59a.m.(which gives the incorrect string error.) when it should be 11/07/2007 2:39:59AM,

It must be to do with the computer clocks date time format, on server 2003 ?

Any ideas?

|||

Hi,

Please run the "Regional and Language Options" in your Control Panel. Click on "Customize", and switch to the Time tab, just to modify the "AM symbol" and "PM symbol" and hava a try.

Good Luck.

|||

store the datetime column in international format or use now.tostring("format eg. MM/dd/yyyy hh:mm:ss etc ")

|||

If the SqlDbType = DateTime then format should not come into it as the output string display is just a human readable format for display use that is not used by SQL when feeding DateTime values into it.

Do you have a snippet of the code? Something like this is what I would expect for a successful date insertion: (example routine)

public static bool InsertDateIntoRandomTable() {bool blSuccess =false;string strComm ="INSERT INTO [RandomTable] " +"(One_Date) VALUES (@.One_Date)"; SqlConnection sqlConn =new SqlConnection(strGlobalSQLConnection); SqlCommand sqlComm =new SqlCommand(strComm, sqlConn); sqlComm.Parameters.Add("@.One_Date", SqlDbType.DateTime).Value = DateTime.Now; sqlConn.Open();if (sqlComm.ExecuteNonQuery() > 0) blSuccess =true; sqlConn.Close();return blSuccess; }

Hope this helps

Mark

|||

Hi,

Thanks for the help guys

I tried what you said and it changed the clock on the computer OK. But strangley, on the website; it is still doing the format 11/11/2006 12:07a.m. instead of 11/11/2006 12:07AM

Is it something to do with IIS settings?

Thanks

|||

Hi,

After you change the time format in Regional and Language Options, you shouldrestartthe Visual Studio and open your application project, build and run the application again. Then check it and explorer the page in your IIS.

Thanks.

|||

Thanks a lot for your help. I tried restarting my computer etc. but, no luck...