DB access question

I have SQL 2000 server with 2 databases A&B
What would cause the following to occur:
We are connecting ODBC and Querry Analyzer
When person 1 is in DB A
person 2 cannot connect to DB B
When person 2 is in DB B
person 1 cannot connect to DB A
Thanks in advanceWhat error message do you get when person 1 or 2 "can't connect"? Do you
get failed login messages in the SQL errorlog (do you have failed login
logging turned on?)? Have you run SQL profiler against your server when
you're trying to do this (monitoring a whole bunch of security events like
Successful Login, Failed Login, etc., etc)?
--
Cheers,
Mike
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:ODpsdQCzEHA.1404@.TK2MSFTNGP11.phx.gbl...
>I have SQL 2000 server with 2 databases A&B
> What would cause the following to occur:
> We are connecting ODBC and Querry Analyzer
> When person 1 is in DB A
> person 2 cannot connect to DB B
> When person 2 is in DB B
> person 1 cannot connect to DB A
> Thanks in advance
>

Datetime string

Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 =A4W=A4=C8 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 =A4W=A4=C8 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 =A4W=A4=C8 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 =A4W=A4=C8 09:24:17 .
Thanks.http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1146016353.858447.49210@.i40g2000cwc.googlegroups.com...
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 W 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 W 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 W 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 W 09:24:17 .
Thanks.|||Hi
declare @.dt varchar(20)
set @.dt='2006/4/26 10:22:55'
create table #table (c datetime)
insert into #table (c)
select cast(rtrim(y*10000+m*100+d)+' '+ t as datetime)
from
(
select year(@.dt) as y,month(@.dt) as m ,day(@.dt)as d,
right(@.dt, CHARINDEX(' ', REVERSE(@.dt))-1) t
) as der
select * from #table
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1146016353.858447.49210@.i40g2000cwc.googlegroups.com...
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 W 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 W 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 W 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 W 09:24:17 .
Thanks.|||hon123456 (peterhon321@.yahoo.com.hk) writes:
> I got a fieldA which is datetime , when I check the value
> in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
> When I get this field to a ADODB.recordset named rs_A, rs_A
> returns :
> 2006/4/26 W 09:24:17 ,which is date time string format
> with Chinese String.
> When I try to insert '2006/4/26 W 09:24:17' into a datetime
> field, the query analyzer complains
> Syntax error converting datetime from character string.
> When I cast('2006/4/26 W 09:24:17' as datetime), the query
> analyzer also complains
> Syntax error converting datetime from character string.
> My quetions is how to keep the data time value as 2003-10-27
> 10:55:00.000 but not 2006/4/26 W 09:24:17 .
One answer to that particular question, is to change your regional settings
to Swedish, or at least change the datetime format in regional settings. I
would not recommend that though.
What I don't really understand is you need to take a value from an
ADO recordset and paste into Query Analyzer.
When you pass dates to and from SQL Server, you should do so in binary
format. The client API will then convert from/to string format according
to regional settings.
I suspect that you do something like this in your ADO code:
sql = "SELECT ... FROM tbl WHERE datetimecol = '" & rs("dt") & "'"
Don't do that. Run a parameterised query instead. Here is a quick sample
query:
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
" FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
cmd.Parameters.Append
cmd.CreateParameter("@.custid", adWChar, adParamInput, 5, custid)
End If
If shipname <> "" Then
cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
cmd.Parameters.Append cmd.CreateParameter("@.shipname", _
adVarWChar, adParamInput, 40, shipname)
End If
Set rs = cmd.Execute
This example does not includ a datetime parameter, but at least you get
to see the principle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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.

Dates and Differences between Query Analyzer and Enterprise Manager

Hey Everyone,

I am just starting to learn T-SQL and came across something that puzzled me when looking at dates.

If I enter this:
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) AS Expr1

In Enterprise Manager I get this output
12/30/2002 - which is what I assumed that I should get

In Query Analyzer I get this output
2002-12-30 00:00:00.000 - which I did not expect

Does anyone know why there would be a difference and if so, how do I get Query Analyzer to format as 12/31/2002?

Thanks alot,
BrentTry only: SELECT CONVERT(varchar, GETDATE(), 101) AS Expr1|||The style parameter is used when converting to a character type from a datetime - so when you converted it back to a datetime in query analyzer it showed you what you asked for. This is the true format - em will jack with the format but the reality is different. em will see all zeroes for time as no time and only displays slashes (no dashes).