[strange] date format issue: 2 servers, one query, 2 different results.

I'm running 2 WIN2K SP4 servers with MSSQL Server (v 7.00.1077). Both NT servers seems to have the same setup. I use the ADODB object to connect to MSSQL via OLE DB and notice that the following query generates an error on one server, not the other:

SELECT DATA FROM TABLE_1 WHERE ((TRANSACTION_DATE >='13/11/2003') AND (TRANSACTIONS_DATE <='20/11/2003')) [dates are DD/MM/YYYY]

Query on server_1:
- using OLE DB: correct
- using ODBC: correct
- using Query Analyzer: error <The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value>

On server_2:
- using OLE DB: error <The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value>
- using ODBC: correct
- using Query Analyzer: error <The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value>

Both servers have MDAC 2.8 RTM installed and a <select @.@.language> returns <us_english> via query analyzer and <franais> via ODBC.

I'd like to setup Server 2 so that queries can be executed the same as on server 1.

Any ideas are welcomed!There's a problem with 20/11/2003 in us_english format. Default is: MDY, so it's reading 20th as month, which is why you're getting that error.

Here's an article I found: http://members.lycos.nl/digispy/C2/P56/C2P56A1806.htm

G'luck!
Meera|||Possible there is something to do with locale settings of windows, check the "Region Options" in Control Panel for both servers.|||I'd say the languages are different...and accordingingly each date format is different

Di SELECT * FROM master..syslanguages

I think English is the only one that mdy|||(D/M/Y is a French date format).

I just had to set <French> to user's default language instead of <US_ENGLISH>. This means ODBC settings override the default SQL Server settings as OLE DB uses the default SQL Server setting.sql

[Shared Memory]ConnectionCheckForData error on delete command.

Hello,
I ave a strange error with SQLServer 2000 SP3.
I made a table that contains over 62'000 records. When I delete over
300 records, I have this error:
[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Serveur : Msg 11, Niveau 16, État 1, Ligne 0
Structure of my table:
create table ActiveTimeForProcessing (
ACTIVETIMEFORPROCESSING_PK int identity,
InstanciedClassName int not null,
ActiveTime int not null,
DateFrom datetime not null,
DateTo datetime not null,
constraint PK_ACTIVETIMEFORPROCESSING primary key
(ACTIVETIMEFORPROCESSING_PK)
)
go
alter table ActiveTimeForProcessing
add constraint ActiveTimeForProcessingHasActiveTime foreign key
(ActiveTime)
references ActiveTime (ACTIVETIME_PK)
go
CREATE INDEX [IX_ActiveTimeForProcessing_DateFrom] ON
[dbo].[ActiveTimeForProcessing]([DateFrom]) ON [PRIMARY]
GO
CREATE INDEX [IX_ActiveTimeForProcessing_DateTo] ON
[dbo].[ActiveTimeForProcessing]([DateTo]) ON [PRIMARY]
GO
Something very strange is that on an other server, it work fine...
Thanks to help me.
PascalHi,
Try
1. Run DBCC CHECKDB against the database to make sure it is clean.
2. Check the errorlog for any error that match the time that you get the
error.
Sincerely,
Yih-Yoon Lee
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Thanks a lot, but I tryed you purpose...
Finally I found sollution... Service Pack 3a......... ... ...
Thanks one more.
Pascal
""Jinxin"" <yihyoonl@.online.microsoft.com> a écrit dans le message de news:
ZRYO4j$PDHA.2696@.cpmsftngxa09.phx.gbl...
> Hi,
> Try
> 1. Run DBCC CHECKDB against the database to make sure it is clean.
> 2. Check the errorlog for any error that match the time that you get the
> error.
> Sincerely,
> Yih-Yoon Lee
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

[newbie] Execute SQL task bypassed, why ?

Hi!

I meet a strange behaviour which is probably caused by my SSIS newbie nature.

I have a Execute SQL Task, used to drop and create some temporary tables. It works when invoked manually.

I have chained a Data Flow Task behind, but when I launch the whole process, although the Execute SQL tasks goes green, it is not executed (then the Data Flow fails because the required tables are missing).

Would anyone have any hint on why the execute sql task seems to be bypassed ?

kind regards

Thibaut

hi Thibaut,

how odd! Have you defined a log file for your package? If so, what kind of info is providing to you?

|||

Is that a package that you strated from scratch? If not; make sure there is not an expression or package configuration that changes the SQL Statement or the connection strings. If you are getting green on the SQL task I bet the object is actually being created, perhaps in the wrong side (server, schema, DB, etc)

Rafael Salas

|||I am now restarting the package from scratch, and making it configurable ("keeping it in the dark"). I'll report back if I meet the same issue again.

thanks!|||Hi!

I finally found out what is happening. The Data flow task is using a XML source to load the data into tables which are created by the Execute SQL task.

The XML source was trying to validate the schema provided at run time against tables which are not created yet... I just disabled the validation on the XML source and everything went fine.

thanks for all the replies.

cheers!

Thibaut

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name on Linked Server

Hi Folks,
I'm having a strange situation here between 2 Linked MS SQL 2K servers.
One of the servers died recently and I rebuilt it.
All seems to be working fine, except that when I create views that access tables on the other (linked) server, I can not modify any data, because I get the [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name server.database.dbo.TableName error.
The View does work fine otherwise, ie displays the data in it, I just can't modify.
The Linked Servers are done with the sa user between these 2 servers.

Any ideas what could be causing this error?
Thanks!-- bump --
no one ideas on this?|||I'm also having problems with this - mine is in relation to a labeler printer, it is present in my ODBC and the test is successful. However, when I go to print I get the following error: [microsoft][ODBC SQL Server Driver][SQL SERVER] Invalid object name 'table name' ODBC -- call failed

I can't seem to see what the problem is as my project server works from this client.

[Microsoft][ODBC SQL Server Driver] error

Hi All,
A strange thing has been happening for a month or so when I retrive data via
QA. Sometimes the queries fail to run and I receive the following error
messages.
[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream
[Microsoft][ODBC SQL Server Driver]TDS buffer length too large
[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server
Re-reunning the query does not help only if I re-connect to the server. I
have checked Books Online and the only info is the following.
'The TDS stream from the server is invalid. This error is typically caused
by a problem on the server. Check the SQL Server error log.'
To tell you the truth I have not found anything suspicious.
Could you help me with it? Any help is welcome.
Thanks.
Kolos
Make you have applied the latest service packs. You also
need to apply the service packs on the client where you run
Query Analyzer.
-Sue
On Wed, 28 Feb 2007 01:52:09 -0800, Kolos
<Kolos@.discussions.microsoft.com> wrote:

>Hi All,
>A strange thing has been happening for a month or so when I retrive data via
>QA. Sometimes the queries fail to run and I receive the following error
>messages.
>[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream
>[Microsoft][ODBC SQL Server Driver]TDS buffer length too large
>[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server
>Re-reunning the query does not help only if I re-connect to the server. I
>have checked Books Online and the only info is the following.
>'The TDS stream from the server is invalid. This error is typically caused
>by a problem on the server. Check the SQL Server error log.'
>To tell you the truth I have not found anything suspicious.
>Could you help me with it? Any help is welcome.
>Thanks.
>Kolos

[Microsoft][ODBC SQL Server Driver] error

Hi All,
A strange thing has been happening for a month or so when I retrive data via
QA. Sometimes the queries fail to run and I receive the following error
messages.
[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream
[Microsoft][ODBC SQL Server Driver]TDS buffer length too large
[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL S
erver
Re-reunning the query does not help only if I re-connect to the server. I
have checked Books Online and the only info is the following.
'The TDS stream from the server is invalid. This error is typically caused
by a problem on the server. Check the SQL Server error log.'
To tell you the truth I have not found anything suspicious.
Could you help me with it? Any help is welcome.
Thanks.
KolosMake you have applied the latest service packs. You also
need to apply the service packs on the client where you run
Query Analyzer.
-Sue
On Wed, 28 Feb 2007 01:52:09 -0800, Kolos
<Kolos@.discussions.microsoft.com> wrote:

>Hi All,
>A strange thing has been happening for a month or so when I retrive data vi
a
>QA. Sometimes the queries fail to run and I receive the following error
>messages.
>[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream
>[Microsoft][ODBC SQL Server Driver]TDS buffer length too large
>[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL
Server
>Re-reunning the query does not help only if I re-connect to the server. I
>have checked Books Online and the only info is the following.
>'The TDS stream from the server is invalid. This error is typically caused
>by a problem on the server. Check the SQL Server error log.'
>To tell you the truth I have not found anything suspicious.
>Could you help me with it? Any help is welcome.
>Thanks.
>Kolos

[ask] sqlserver getdate() function doesnt return seconds value

i have a quite strange condition...

when i add some value in database with getdate() function it only returns date and minute not the seconds...

does somebody have an experience about this

What is the data type of the variable or column that you are using? If it is smaldatetime, the accuracy is only of minutes. If you want seconds, you'll need to use datetime.

If you're doing something else, post your code to see if we can help you figure it out.

Don

|||

getdate() actually returns current date & time. Just try to select getdate() and you will see. You must have assign the value of getdate() to smalldatetime.|||

I've changed to datetime and it works :)

weew... i never thought about it... just a little mistake there :)

thx anyway