DB Access to another server w/NT AUTHORITY

We have a SQL Server 2000 database named acmedb on a Server2003 machine
named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
asp.net 2.0 website on that same server we use the following connection
string...
Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
This connection works fine. But now we want to run the website on SERVERTWO
and access the database on SERVERONE. Using that same connection string we
can't connect. we get: Login failed for user (null). Reason: Not
associated with a trusted SQL Server connection. The server database is
setup for both Windows and SQL server authentication so that is not the
cause.
The two servers are in two different workgroups but SERVERTWO can ping
SERVERONE.
I really don't want to change ownership of the db and have to pass passwords
and UIDs in the connection string. Is there a way to make this work this
way?
Thanks,
T
You get that error because the service account that you use to login to your
SQL Server is not existed on both servers I believe.
You'd be able to connect if you'd be using a SQL Server Login, not NT
AUTHORITY as Windows Authentication. Then you'd need to change your
connection string to use uid and pwd instead of integrated security.
You could also use a method in which you'd create two identical service
accounts on both servers and use this account to login to SQL Server. Then
you would be able to use integrated security in your connection string
again.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
> We have a SQL Server 2000 database named acmedb on a Server2003 machine
> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
> asp.net 2.0 website on that same server we use the following connection
> string...
> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
> This connection works fine. But now we want to run the website on
> SERVERTWO and access the database on SERVERONE. Using that same
> connection string we can't connect. we get: Login failed for user (null).
> Reason: Not associated with a trusted SQL Server connection. The server
> database is setup for both Windows and SQL server authentication so that
> is not the cause.
> The two servers are in two different workgroups but SERVERTWO can ping
> SERVERONE.
> I really don't want to change ownership of the db and have to pass
> passwords and UIDs in the connection string. Is there a way to make this
> work this way?
> Thanks,
> T
>
|||Your problem has little to do with the ownership of the database on
SERVERONE. It is the APS.NET app. That is, the user account used to run the
ASP.NET application does not have access to the database on SERVERONE.
You need to configure the ASP.NET app correctly to access SQL Server.
The simple way would be to use SQL Server security, instead of Windows
security to log into SQL Server, as other other post suggested. So, you add
username/password to the ConnectionString.
However, if you need to use Windows authentication to access SQL Server, you
have to configure ASP.NET APP correctly. The ASP.NET APP cannot use local
account from SERVERTWO to access SQL Server on SERVERONE. Period (unless it
is peer-to-peer network).
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
> We have a SQL Server 2000 database named acmedb on a Server2003 machine
> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
> asp.net 2.0 website on that same server we use the following connection
> string...
> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
> This connection works fine. But now we want to run the website on
> SERVERTWO and access the database on SERVERONE. Using that same
> connection string we can't connect. we get: Login failed for user (null).
> Reason: Not associated with a trusted SQL Server connection. The server
> database is setup for both Windows and SQL server authentication so that
> is not the cause.
> The two servers are in two different workgroups but SERVERTWO can ping
> SERVERONE.
> I really don't want to change ownership of the db and have to pass
> passwords and UIDs in the connection string. Is there a way to make this
> work this way?
> Thanks,
> T
>
|||Ekrem,
when you say...
You could also use a method in which you'd create two identical service
accounts on both servers and use this account to login to SQL Server. Then
you would be able to use integrated security in your connection string
again.
Can you give me an example of "Identical Service Accounts"?
Thanks,
T
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
> You get that error because the service account that you use to login to
> your SQL Server is not existed on both servers I believe.
> You'd be able to connect if you'd be using a SQL Server Login, not NT
> AUTHORITY as Windows Authentication. Then you'd need to change your
> connection string to use uid and pwd instead of integrated security.
> You could also use a method in which you'd create two identical service
> accounts on both servers and use this account to login to SQL Server. Then
> you would be able to use integrated security in your connection string
> again.
> --
> Ekrem nsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
|||For example, create an account like the following on your first server
username = xxx
pass = ooo
create another account on your second server
username = xxx
pass = ooo
Usernames and passwords are the same \ identical. And create a login in your
SQL Server for this account. Then you'll be able to login to your SQL Server
using this account and so using Windows Authentication.
Logon to your SERVERONE Windows using this xxx account and you'll be able to
login to your SQL Server under this account.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
> Ekrem,
> when you say...
> You could also use a method in which you'd create two identical service
> accounts on both servers and use this account to login to SQL Server. Then
> you would be able to use integrated security in your connection string
> again.
> Can you give me an example of "Identical Service Accounts"?
> Thanks,
> T
>
>
> "Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
> news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
>
|||so you are saying that the local account on the SERVERONE database server is
setup for Windows Authentication to the DB. right?
Then the connectionstring on the asp.net app running on SERVERTWO would not
have to include PWDand UID? What would that connectionstring look like?
Would it just have UID but no PWD?
T
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:62A50A48-512B-4F76-9351-1E16678A0108@.microsoft.com...
> For example, create an account like the following on your first server
> username = xxx
> pass = ooo
> create another account on your second server
> username = xxx
> pass = ooo
> Usernames and passwords are the same \ identical. And create a login in
> your SQL Server for this account. Then you'll be able to login to your SQL
> Server using this account and so using Windows Authentication.
> Logon to your SERVERONE Windows using this xxx account and you'll be able
> to login to your SQL Server under this account.
> --
> Ekrem nsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
>
|||So, are you saying that I can't do what I want to do? Whatever I do I will
need PWD and UID in the connectionstring on SERVERTWO?
T
"Norman Yuan" <NoAddress@.NoEmail.fake> wrote in message
news:eDQgkm0NIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Your problem has little to do with the ownership of the database on
> SERVERONE. It is the APS.NET app. That is, the user account used to run
> the ASP.NET application does not have access to the database on SERVERONE.
> You need to configure the ASP.NET app correctly to access SQL Server.
> The simple way would be to use SQL Server security, instead of Windows
> security to log into SQL Server, as other other post suggested. So, you
> add username/password to the ConnectionString.
> However, if you need to use Windows authentication to access SQL Server,
> you have to configure ASP.NET APP correctly. The ASP.NET APP cannot use
> local account from SERVERTWO to access SQL Server on SERVERONE. Period
> (unless it is peer-to-peer network).
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
|||Without knowing details on your system setup (domain network, peer-to-peer),
I cannot say more. Basically, you need some consideration on your ASP.NET
app side: which user account you want to use to run the ASP.NET app,
considersing the application's need to access resources locally or in the
network, and then from security point of view. Then you can tell if the
choosen account has necessary access to the SQL Server database in the other
computer.
So, I am not saying anything other than your ConnectionString shown in your
original post does not work. Again, cannt say more more before knowing more
details on your ASP.NET app configuration, network...
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:eV%230Gq1NIHA.6108@.TK2MSFTNGP03.phx.gbl...
> So, are you saying that I can't do what I want to do? Whatever I do I
> will need PWD and UID in the connectionstring on SERVERTWO?
> T
>
> "Norman Yuan" <NoAddress@.NoEmail.fake> wrote in message
> news:eDQgkm0NIHA.1184@.TK2MSFTNGP04.phx.gbl...
>

DB Access to another server w/NT AUTHORITY

We have a SQL Server 2000 database named acmedb on a Server2003 machine
named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
asp.net 2.0 website on that same server we use the following connection
string...
Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
This connection works fine. But now we want to run the website on SERVERTWO
and access the database on SERVERONE. Using that same connection string we
can't connect. we get: Login failed for user (null). Reason: Not
associated with a trusted SQL Server connection. The server database is
setup for both Windows and SQL server authentication so that is not the
cause.
The two servers are in two different workgroups but SERVERTWO can ping
SERVERONE.
I really don't want to change ownership of the db and have to pass passwords
and UIDs in the connection string. Is there a way to make this work this
way?
Thanks,
TYou get that error because the service account that you use to login to your
SQL Server is not existed on both servers I believe.
You'd be able to connect if you'd be using a SQL Server Login, not NT
AUTHORITY as Windows Authentication. Then you'd need to change your
connection string to use uid and pwd instead of integrated security.
You could also use a method in which you'd create two identical service
accounts on both servers and use this account to login to SQL Server. Then
you would be able to use integrated security in your connection string
again.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
> We have a SQL Server 2000 database named acmedb on a Server2003 machine
> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
> asp.net 2.0 website on that same server we use the following connection
> string...
> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
> This connection works fine. But now we want to run the website on
> SERVERTWO and access the database on SERVERONE. Using that same
> connection string we can't connect. we get: Login failed for user (null).
> Reason: Not associated with a trusted SQL Server connection. The server
> database is setup for both Windows and SQL server authentication so that
> is not the cause.
> The two servers are in two different workgroups but SERVERTWO can ping
> SERVERONE.
> I really don't want to change ownership of the db and have to pass
> passwords and UIDs in the connection string. Is there a way to make this
> work this way?
> Thanks,
> T
>|||Your problem has little to do with the ownership of the database on
SERVERONE. It is the APS.NET app. That is, the user account used to run the
ASP.NET application does not have access to the database on SERVERONE.
You need to configure the ASP.NET app correctly to access SQL Server.
The simple way would be to use SQL Server security, instead of Windows
security to log into SQL Server, as other other post suggested. So, you add
username/password to the ConnectionString.
However, if you need to use Windows authentication to access SQL Server, you
have to configure ASP.NET APP correctly. The ASP.NET APP cannot use local
account from SERVERTWO to access SQL Server on SERVERONE. Period (unless it
is peer-to-peer network).
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
> We have a SQL Server 2000 database named acmedb on a Server2003 machine
> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
> asp.net 2.0 website on that same server we use the following connection
> string...
> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
> This connection works fine. But now we want to run the website on
> SERVERTWO and access the database on SERVERONE. Using that same
> connection string we can't connect. we get: Login failed for user (null).
> Reason: Not associated with a trusted SQL Server connection. The server
> database is setup for both Windows and SQL server authentication so that
> is not the cause.
> The two servers are in two different workgroups but SERVERTWO can ping
> SERVERONE.
> I really don't want to change ownership of the db and have to pass
> passwords and UIDs in the connection string. Is there a way to make this
> work this way?
> Thanks,
> T
>|||Ekrem,
when you say...
You could also use a method in which you'd create two identical service
accounts on both servers and use this account to login to SQL Server. Then
you would be able to use integrated security in your connection string
again.
Can you give me an example of "Identical Service Accounts"?
Thanks,
T
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
> You get that error because the service account that you use to login to
> your SQL Server is not existed on both servers I believe.
> You'd be able to connect if you'd be using a SQL Server Login, not NT
> AUTHORITY as Windows Authentication. Then you'd need to change your
> connection string to use uid and pwd instead of integrated security.
> You could also use a method in which you'd create two identical service
> accounts on both servers and use this account to login to SQL Server. Then
> you would be able to use integrated security in your connection string
> again.
> --
> Ekrem nsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>|||For example, create an account like the following on your first server
username = xxx
pass = ooo
create another account on your second server
username = xxx
pass = ooo
Usernames and passwords are the same \ identical. And create a login in your
SQL Server for this account. Then you'll be able to login to your SQL Server
using this account and so using Windows Authentication.
Logon to your SERVERONE Windows using this xxx account and you'll be able to
login to your SQL Server under this account.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
> Ekrem,
> when you say...
> You could also use a method in which you'd create two identical service
> accounts on both servers and use this account to login to SQL Server. Then
> you would be able to use integrated security in your connection string
> again.
> Can you give me an example of "Identical Service Accounts"?
> Thanks,
> T
>
>
> "Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
> news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
>|||so you are saying that the local account on the SERVERONE database server is
setup for Windows Authentication to the DB. right?
Then the connectionstring on the asp.net app running on SERVERTWO would not
have to include PWDand UID? What would that connectionstring look like?
Would it just have UID but no PWD?
T
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:62A50A48-512B-4F76-9351-1E16678A0108@.microsoft.com...
> For example, create an account like the following on your first server
> username = xxx
> pass = ooo
> create another account on your second server
> username = xxx
> pass = ooo
> Usernames and passwords are the same \ identical. And create a login in
> your SQL Server for this account. Then you'll be able to login to your SQL
> Server using this account and so using Windows Authentication.
> Logon to your SERVERONE Windows using this xxx account and you'll be able
> to login to your SQL Server under this account.
> --
> Ekrem nsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
>|||So, are you saying that I can't do what I want to do? Whatever I do I will
need PWD and UID in the connectionstring on SERVERTWO?
T
"Norman Yuan" <NoAddress@.NoEmail.fake> wrote in message
news:eDQgkm0NIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Your problem has little to do with the ownership of the database on
> SERVERONE. It is the APS.NET app. That is, the user account used to run
> the ASP.NET application does not have access to the database on SERVERONE.
> You need to configure the ASP.NET app correctly to access SQL Server.
> The simple way would be to use SQL Server security, instead of Windows
> security to log into SQL Server, as other other post suggested. So, you
> add username/password to the ConnectionString.
> However, if you need to use Windows authentication to access SQL Server,
> you have to configure ASP.NET APP correctly. The ASP.NET APP cannot use
> local account from SERVERTWO to access SQL Server on SERVERONE. Period
> (unless it is peer-to-peer network).
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>|||You'll use the same conn str. Just create the Windows user accounts and
create a login in sql server for this account and use this windows account
for your web application's windows session.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:ODhkLp1NIHA.5400@.TK2MSFTNGP04.phx.gbl...
> so you are saying that the local account on the SERVERONE database server
> is setup for Windows Authentication to the DB. right?
> Then the connectionstring on the asp.net app running on SERVERTWO would
> not have to include PWDand UID? What would that connectionstring look
> like? Would it just have UID but no PWD?
> T
>
> "Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
> news:62A50A48-512B-4F76-9351-1E16678A0108@.microsoft.com...
>|||Without knowing details on your system setup (domain network, peer-to-peer),
I cannot say more. Basically, you need some consideration on your ASP.NET
app side: which user account you want to use to run the ASP.NET app,
considersing the application's need to access resources locally or in the
network, and then from security point of view. Then you can tell if the
choosen account has necessary access to the SQL Server database in the other
computer.
So, I am not saying anything other than your ConnectionString shown in your
original post does not work. Again, cannt say more more before knowing more
details on your ASP.NET app configuration, network...
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:eV%230Gq1NIHA.6108@.TK2MSFTNGP03.phx.gbl...
> So, are you saying that I can't do what I want to do? Whatever I do I
> will need PWD and UID in the connectionstring on SERVERTWO?
> T
>
> "Norman Yuan" <NoAddress@.NoEmail.fake> wrote in message
> news:eDQgkm0NIHA.1184@.TK2MSFTNGP04.phx.gbl...
>|||I think your solution would only work if the two servers are in the same
domain - right?
T
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:E0E94E38-299C-4100-9DE8-16786A19000A@.microsoft.com...
> You'll use the same conn str. Just create the Windows user accounts and
> create a login in sql server for this account and use this windows account
> for your web application's windows session.
> --
> Ekrem nsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:ODhkLp1NIHA.5400@.TK2MSFTNGP04.phx.gbl...
>

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

DAVID/ASPNET Sql connection problem!

I am having a problem with .net ms sql connections...

My situation: I log onto my computer (Windows XP Pro) using the administrator account.
I have MSDE installed, with NT authorization.
I created a quick connection in ASP.net Web Matrix, and ran the code using the ASP.NET web matrix server - It ran perfectly, so i know the connection string is good, and so is all the other code i used for the connection... I know that it used the administrator account to access the sql server.

Then i copied and pasted the exact working code i had in web matrix to Visual Studio.net, and tried to run it using IIS, but it gave the following error:

Server Error in '/' Application.

Login failed for user 'DAVID\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'DAVID\ASPNET'.

Source Error:

Line 11: Dim myCommand As New SqlCommand(CommandText, myConnection)
Line 12:
Line 13: myConnection.Open()
Line 14:
Line 15: ' Create Instance of Connection and Command Object

I only have MSDE installed on my PC, so i ran the following in DOS:

cd "c:\Program Files\Microsoft SQL Server\80\Tools\Binn"
osql -E -S DAVID -Q "sp_grantlogin 'DAVID\ASPNET'"
osql -E -S DAVID -d myDB -Q "sp_grantdbaccess 'DAVID\ASPNET'"
osql -E -S DAVID -d myDB -Q "sp_addrolemember 'db_owner', 'DAVID\ASPNET'"
pause

So now ASPNET should have full access to the sql server. (Stored Procedures ran fine, and the roles and accounts were created successfully.)

So i ran the ASP.net code in IIS again, but still the same error!

I have even tried re-installing asp.net!
I havent tried re-installing MSDE because i dont want to loose my data, but if i have to, then i guess i have to... :(

What do i do??I'm sure you understood, that your SQL Server did not accept DAVID\ASPNET as a login name?! Either there is indeed not such a user, or maybe you took the back-slash instead the slash. Could you post your connection string?|||I will get the connection string to you asap - its at home, not at work, so only tomorrow...

When installing the .NET framework, the user ASPNET is automatically added. ASP.NET uses this user for all the interaction it makes with the server (in most cases). So anyway - the ASPNET user is there, and my computer name is DAVID, and so is my SQL server instance called DAVID.

Ill try with the forward slash instead of using a back slash, but im pretty sure that i did it the right way... I copied it exactly as the asp error page had it...

datetime problem

my asp .net application is coming along nicely. however, i would like to record the users last login time. i have a datetime field, and when i insert or update using Now() as the data for the field, i simply get 1/1/1900 12:00:00 in the last_login field. Same thing using Today(). any suggestions? am i using the wrong data type? i need to be able to do date comparisons as i plan on connecting my site with a forum, and this would be the easiest way to determine if there were new posts from the user's perspective.

TIA

Use GetDate() in your SQL to input the current time.|||

Unless your business object has a say in what the date/time stamp value is, there is little reason to send it over the network both ways. Might as well have the database provide the value and hand it back to you.

|||

thanks mikesdotnetting! worked like a charm!

|||

Don't forget that getdate(0 and Now() work on different servers which can be in different time zones :)

Datetime parameter / Report Viewer

I have a .net 2.0 web form with a report viewer control (RS 2005) and have hard coded the report server and path. The report has two date time parameters that have a default value set up. When the user clicks the calendar icon to change the date range, the page refreshes without displaying the calendar... I don't remeber having this problem in the past. The parameters function fine in Report Manager. Any idea what could be causing this?

I am having the same problem and ran across this thread. Hopefully it will help you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=591513&SiteID=1

datetime format

Hello:

Hello:
I have some code in a asp.net function in C# like this:

DateTime datetime = DateTime.Now;
SqlCommand CommandEvent = new SqlCommand("spAddNewEvents", Connection);
CommandEvent.Transaction = Trans;
CommandEvent.CommandType = CommandType.StoredProcedure;
*I try First : CommandEvent.Parameters.Add("@.date", datetime);
*I try Secound: CommandEvent.Parameters.Add("@.date", SqlDbType.DateTime,
8).Value = datetime;

and have some storeprocedure with this code

CREATE PROCEDURE dbo.spAddEvents
 (
 @.guid uniqueidentifier,
 @.language char (2),
 @.date as datetime,
 @.eventId as varchar (50),
 @.userid as varchar (20)
 )
AS
 execute('insert into tblEvents'+@.language+'( guid, [date], [id], userid)
values('''+@.guid+''','''+@.date+''','+@.eventId+','''+@.userId+''')')
GO

The problem is when I try to insert a new event. The event insert are fine,
but the datetime's secound in tblEvents always is 00, and I check the
datetime variable to insert and have secound different that 00.

the table definition is Data Type: datetime and Length: 8, how must be?

I run Profiler and a I get this:

exec spAddEvents @.Guid = 'C879D062-C268-4A3E-8D58-1937B7612EC2', @.language = N'ES', @.date = 'May 6 2004 11:29:58:140PM', @.eventId = 6, @.userid = N'anibal'

Best regards.
Owen.That's odd. Try writing your stored procedure without using a dynamic string. In this case, you don't need to use a dynamic statement. It might be causing trouble there. Also, if you're just putting in an insert date (not depending on any logic in your code), use getdate() within your stored procedure and forget about passing in the date. No need to generate that in your code.

datetime default value

Hi I am designing a table with vs.net 2003 and have a column that has a
datetime datatype. Just wondering if anyone knows if there is a way to set
its' default value to the current system time, like Now().
thanks.
--
Paul G
Software engineer.getdate()
Walter
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
> Hi I am designing a table with vs.net 2003 and have a column that has a
> datetime datatype. Just wondering if anyone knows if there is a way to
> set
> its' default value to the current system time, like Now().
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks. I ended up setting a constraint with SQL in the query analyzer
but your method appears much easier.
--
Paul G
Software engineer.
"Walter Mallon" wrote:

> getdate()
> Walter
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
>
>

datetime default value

Hi I am designing a table with vs.net 2003 and have a column that has a
datetime datatype. Just wondering if anyone knows if there is a way to set
its' default value to the current system time, like Now().
thanks.
--
Paul G
Software engineer.getdate()
Walter
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
> Hi I am designing a table with vs.net 2003 and have a column that has a
> datetime datatype. Just wondering if anyone knows if there is a way to
> set
> its' default value to the current system time, like Now().
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks. I ended up setting a constraint with SQL in the query analyzer
but your method appears much easier.
--
Paul G
Software engineer.
"Walter Mallon" wrote:
> getdate()
> Walter
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
> > Hi I am designing a table with vs.net 2003 and have a column that has a
> > datetime datatype. Just wondering if anyone knows if there is a way to
> > set
> > its' default value to the current system time, like Now().
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>

DATETIME Default getDate() query

Hi,
I have created a datagrid within an ASP.NET page that links to an SQL
table - this works just fine. One of the columns retrieves the date which a
particular row was populated. However, the format
which the date is returned is:
19.12.2004 13:03:25
I only want to display the date that the row was populated, not the exact
time as well. When I created the table I used the following code (I've cut
out the rest of the fields):
CREATE TABLE Users
(
Paul,
You can use this as your default:
Default dateadd(d,0,datediff(d,0,getdate()))
It works by finding the number of day boundaries between day 0 (January
1, 1900) and now, and adds that many days back to day 0.
Steve Kass
Drew University
Paul Evans wrote:

>Hi,
>I have created a datagrid within an ASP.NET page that links to an SQL
>table - this works just fine. One of the columns retrieves the date which a
>particular row was populated. However, the format
>which the date is returned is:
>19.12.2004 13:03:25
>I only want to display the date that the row was populated, not the exact
>time as well. When I created the table I used the following code (I've cut
>out the rest of the fields):
>CREATE TABLE Users
>(
> .
> .
> .
> .
> u_entrydate DATETIME Default getDate()
>)
>
>Is this to do with the DATETIME Default getDate() code for creating the
>column? Is there something else I could use to get just the date only, with
>out the time of day the row was populated?
>I'm using a microsoft SQL server.
>Thanks for your time
>Paul Evans
>P.S. or is it a problem with my ASP.NET code?
>
>

DATETIME conversion problem in stored procedure

Hi,

I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .

Stored procedure:

ALTER PROCEDURE dbo.SPTest
@.testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @.testvalue, 'EUR/USD')
RETURN 1

VB.NET code:

Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))

When I run this the ReturnVal is 0.

I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.

hi,

dazfl wrote:

Hi,

I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .

Stored procedure:

ALTER PROCEDURE dbo.SPTest
@.testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @.testvalue, 'EUR/USD')
RETURN 1

usually return values other than 0 (zero) indicate a procedure error.. so, 1 is usually read as error and not "success"..

VB.NET code:

Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))

When I run this the ReturnVal is 0.

I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.

try directly consuming a command and relative parameters, like

Dim cmd As New SqlClient.SqlCommand

With cmd

.CommandText = "schema.procedureName"

.CommandType = CommandType.StoredProcedure

.CommandTimeout = n

.Connection = connection

Dim p As New SqlClient.SqlParameter

With p

.ParameterName = "@.testvalue"

.SqlDbType = SqlDbType.DateTime

.Value = DateTime.Now

.Direction = ParameterDirection.Input

End With

.Parameters.Add(p)

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

so that you can check (1st important addition of the command and parameters behaviour) and validate parameters initialization... more.. the parameter automatically handles this kind of conversions..

regards

Datetime and the missing milliseconds

another datetime problem...

When I send a .net Datetime as a param to a SQL Server sproc, the milliseconds disappear.

I am returning a lastUpdatedDate field from a table in SQL Server to my asp.net page along with updateable table data. The user makes their updates and then submits the form which sends the data to a sproc which makes the update. The procedure makes a check that the lastUpdatedDate submitted back from the form is still the same as the one in the database (to guard against lost updates)

However, when the lastUpdatedDate gets back into SQL server it is missing the milliseconds component - they have been set to 000. I've been tearing my hair out trying to find the point at which the milliseconds disappear and why - but to no avail.

The way I'm doing it is storing the lastUpdatedDate in a .net datetime, passing this into viewstate and then when the user submits the update, retrieving it from ViewState back into the datetime variable and then submitting it to the sproc using a parameter of SqlDbType.Datetime

I have put bits of debug code at every point in the .net processing to look at the datetime and the milliseconds are always there... its only when the value gets picked up in SQL Server that they have disappeared

Any ideas?

MaracatuIs the data type in the data base table DateTime or SmallDateTime? If it's SmallDateTime that might be causing the truncation of the milliseconds (and seconds).|||Its datetime.
The milliseconds go missing when the datetime is retrieved from ViewState after a round trip to the client (bizarrely if the datetime is placed in Viewstate and immediately retrieved and sent to the database no problem occurs).

I have now solved the problem by storing a String value of the datetime, including the milliseconds in ViewState rather than puting the datetime value in there directly.|||Interesting, I now recall having a similar problem with disappearing milliseconds. It wasn't a concern and so I didn't check up on it but I bet that was the reason.

Probably when a datetime is placed into ViewState it serializes it as a string but without milliseconds. But it doesn't actually get serialized until just before the page it sent back to the client so if you stick it into ViewState and pull it back out it's still the original datetime.

Datetime and conversion to smalldatetime.

I am placing DateTime into SQL using an ASP.NET form. The date should be formatted dd/mm/yyyy hh/mm/ss.

I am getting the error below. Is there any way to convert the format of the DateTime function from the ASP.NET end?

Thanks

mes

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value"

DateTimes aren't "formatted". It sounds like you are giving it a string, that you think looks like a date. Give the database a real datetime (Or specify the sqlparameter as being of datetime type), and your problem should go away. If that string format isn't valid for your culture, well... You'll have to fix that separately, or manually convert your "DateTime in a string" to a format that your SQL Server wants.

DateTime & SQL Server

I'm going slowly insane trying to get a Date AND Time value stored in SQL 2000 from ASP.NET 2.0.

The following works fine in Query Analyzer, but fails when run from ASP.NET:

INSERT INTO tbl_WIP_DATA_DateTime (ObjectInstanceID, ObjectPropertyID, PropertyData_DateTime) VALUES (32226,7,'20060120 10:43:44')

Does anyone have any suggestions?

Cheers
Vatic

Is that exactly what you have in your ASP.NET page (hardcoded values) or are you passing the values through parameters? If you are using parameters please post code.|||

This is exactly as it appears in code. The sql query is dynamically created.

|||

I've also tried the following:

Dim objconAsNew SqlConnection(System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString"))
Dim myCommandAs SqlCommand
Dim myParamAs SqlParameter
myCommand =New SqlCommand()
myCommand.Connection = objcon

DataQuery ="INSERT INTO " & myProperty.dataTable &" (ObjectInstanceID, ObjectPropertyID, " & myProperty.DataColumn &") VALUES (@.ObjectInstanceID, @.ObjectPropertyID, @.theDateTime)"

myCommand.CommandText = DataQuery
myCommand.Parameters.Add(New SqlParameter("@.ObjectInstanceID", SqlDbType.Int))
myCommand.Parameters("@.ObjectInstanceID").Value = myObjectInstance.ObjectInstanceID

myCommand.Parameters.Add(New SqlParameter("@.ObjectPropertyID", SqlDbType.Int))
myCommand.Parameters("@.ObjectPropertyID").Value = myProperty.ObjectPropertyID

myCommand.Parameters.Add(New SqlParameter("@.theDateTime", SqlDbType.DateTime))
myCommand.Parameters("@.theDateTime").Value = Now()

objcon.Open()
myCommand.ExecuteNonQuery()
objcon.Close()

Which returns:

The string was not recognized as a valid DateTime. There is a unknown word starting at index 0.Expected type is DateTime

|||

Try:

dim dt as datetime=datetime.parse(Now())

See if that throws you any errors.

datetime

I use Calendar asp.net(c#).It gives me Calendar.SelectedDate.Date is string
I convert it to datetime with Convert.toDateTime but Sql server give me
error...
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated.
How can I solve the error and add date to sql server?
Thanks...You're not using a proper datetime format for SQL Server:
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Selen" <skiyanc@.yahoo.com> wrote in message
news:OLR5VrmCEHA.2656@.TK2MSFTNGP12.phx.gbl...
> I use Calendar asp.net(c#).It gives me Calendar.SelectedDate.Date is
string
> I convert it to datetime with Convert.toDateTime but Sql server give me
> error...
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value. The statement has been terminated.
> How can I solve the error and add date to sql server?
> Thanks...
>|||two options to go forwards
1. Use a neutral date format. These are the ISO and ISO 8601 standard. These
have the CONVERT sytle numbers of 112 and 126 respectively. Check the SQL S
erver documentation on the CONVERT function for details.
2. Change the language of the SQL Server login used to match the string that
is being sent down.

DATEDIFF() function in SQL server

i know this is specifically more an SQL server question than an ADO.NET question, but if anybody can clear this up for me that'd be great.

if i have a filter like the following:

WHERE DATEDIFF(day, H.HitDate, getdate()) = 1

is this going to work over the new year? what i mean is, if i run the query on january 1st, will it get all the rows from december 31st the previous year (and day)?

any help is greatly appreciatedYes, it will.

Prove it to yourself by running this code:

DECLARE @.myThisYearDate datetime
DECLARE @.myNextYearDate datetime
SET @.myThisYearDate = '12/31/2003'
SET @.myNextYearDate = '01/01/2004'
PRINT DATEDIFF(day,@.myThisYearDate,@.myNextYearDate)

Terri|||k, that seems to be working great.

thanks