[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

[help]:SQLServer 2000 setup fails!

Dear All:
I receive the error with _INS5576._MP.exe when i installed sqlserver 2000
pls help me to solve this problem
ThanksWhat is the version of MSSQL server you tried to install?
What is your operating system?
Can you post the error message you got?|||Originally posted by gyuan
What is the version of MSSQL server you tried to install?
What is your operating system?
Can you post the error message you got?

i wanna to install STANDARD version in my windows 2000 server
I receive the error with "application error in _INS5576._MP "

pls help me ,thanks|||In which step did you get the error message?|||Originally posted by gyuan
In which step did you get the error message?

The first step,i click the SETUPSQL.EXE|||What is the Service Pack on your windows 2000 server? When you tried to install the MSSQL server did you close any other applications?|||Did you log on windows using Administrator account?|||Originally posted by gyuan
What is the Service Pack on your windows 2000 server? When you tried to install the MSSQL server did you close any other applications?

I've installed sp3 ,i have closed any other applictions when i install the sqlserver and i use administrator installed it|||Is is solved ? If not, read the following link:

link (http://dbforums.com/arch/175/2002/3/331731)

Also, check your installation log files for more information- sqlstp.log, sqlsp.log, and setup.iss.|||Originally posted by rnealejr
Is is solved ? If not, read the following link:

link (http://dbforums.com/arch/175/2002/3/331731)

Also, check your installation log files for more information- sqlstp.log, sqlsp.log, and setup.iss.

but i didn't find ActiveSync in my computer

can you tell me where is the sqlstp.log ,sqlsp.log,and setup.iss?|||Originally posted by board
but i didn't find ActiveSync in my computer

can you tell me where is the sqlstp.log ,sqlsp.log,and setup.iss?

here is the sqlstp.log

15:23:58 Begin Setup
15:24:28 ?ɡ

15:24:28 End Action: Locked Connectivity Files Check
15:24:28 ??? Microsoft ? (MDAC) ...
15:24:28 D:\NET\SQLSER~1\ENTERP~1\x86\Other\sqlredis.exe /q:a /C:"setupre.exe WARN=1 -s -SMS"
15:24:43 ExitCode: 0
15:24:43 ??? Microsoft ??_ (MSDTC) ...
15:24:43 C:\WINNT\TEMP\SqlSetup\Bin\cldtcstp.exe -SupportDir "C:\WINNT\TEMP\SqlSetup\Bin" -DTCPkg "D:\NET\SQLSER~1\ENTERP~1\x86\Other\dtcsetup.exe" -LogFile "C:\WINNT\sqlstp.log"
15:24:43 Process Exit Code: (0)
15:24:43 InstallMSSearch instance: MSSQLSERVER
15:24:43 Software\Microsoft\Search\Install:Version : 9.107.5512
15:24:43 MSSearch 2.0 or greater version check returned 1
15:24:43 ??? Microsoft ??...
15:24:43 "D:\NET\SQLSER~1\ENTERP~1\x86\FullText\MSSearch\Sea rch\SearchStp.exe" /s /a:SQLServer
15:24:52 Process Exit Code: (0)
15:24:52 /Q:A /T:C:\WINNT\TEMP\ixp001.tmp
15:24:52 ??? HTML
15:24:52 HTML Help installer exit code: 0
15:25:14 End Action InstallPkgs
15:25:14 Begin Action MoveFileData:
15:25:14 Enabled SELFREGISTERBATCH
15:25:14 Enabled CORECOMPONENTHANDLING
15:25:47 Begin Action: MoveFileDataSpecial
15:25:47 End Action: MoveFileDataSpecial
15:25:47 End Action MoveFileData
15:25:47 Begin Action ProcessAfterDataMove:
15:25:47 D:\NET\SQLSER~1\ENTERP~1\x86\Binn\hhcol.exe C:\Program Files\Microsoft SQL Server\80\Tools\Books
15:25:49 End Action ProcessAfterDataMove
15:25:49 Begin Action BuildServer:
15:25:49 C:\WINNT\TEMP\SqlSetup\Bin\scm.exe -Silent 1 -Action 5 -ExePath "C:\Program Files\Microsoft SQL Server\MSSQL\binn\sqlservr.exe" -Service "MSSQLSERVER"
15:25:49 Process Exit Code: (0)
15:25:49 Begin Action: CreateRegistrySetSQL
15:25:49 End Action: CreateRegistrySetSQL
15:25:49 Begin Action: RegWriteSetupEntry
15:25:49 End Action: RegWriteSetupEntry
15:25:49 Begin Action: CreateSer
15:25:49 End Action: CreateSer
15:25:49 Begin Action: SkuIt
15:25:50 End Action: SkuIt
15:25:50 SetFileSecuritySQLAndAdmin for d:\Program Files\Microsoft SQL Server\MSSQL returned: 1350, 1350
15:25:50 SetFileSecuritySQLAndAdmin for C:\Program Files\Microsoft SQL Server\MSSQL returned: 1350, 1350
15:25:50 SetRegSecuritySQLAndAdmin for Software\Microsoft\MSSQLServer\MSSQLServer returned: 0, 0
15:25:50 Begin Action: UpdateSystemPath
15:25:50 Path successfully updated.
15:25:50 %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\Sy stem32\Wbem;C:\Program Files\Microsoft SQL Server\80\Tools\BINN
15:25:50 End Action: UpdateSystemPath
15:25:51 C:\Program Files\Microsoft SQL Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINNT\sqlstp.log" -I MSSQLSERVER -V 1 -M 0 -Q "Chinese_PRC_CI_AS" -H 1179808 -U sa -P
################################################## #############################

?

Chinese_PRC_CI_AS

-m -Q -T4022 -T3659

?

driver={sql server};server=google15;UID=sa;PWD=;database=maste r

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server ??

[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).

driver={sql server};server=google15;UID=sa;PWD=;database=maste r

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server ??

[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).

driver={sql server};server=google15;UID=sa;PWD=;database=maste r

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server ??

[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).

SQL Server ?

################################################## #############################

15:28:02 Process Exit Code: (-1)|||It looks like it is failing when configuring the server. Please post the output from cnfgsvr.out.|||Also, what is the translation on these 2 lines:

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server ??

SQL Server ?|||Originally posted by rnealejr
Also, what is the translation on these 2 lines:

[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server ??

SQL Server ?

The problem had solved , I installed the sp4 for my windows 2000 server
It's ok now!

Thanks

[Help] Snapshot replication & Full Text Indexing

Hi
I have setup snapshot replication in one of my sql database. The article
which i publish, has full text index enabled in the subscription db. So when
the initial snap shot is applied, i get a error message saying "cannot drop
index PK_<<table name>> because it enforces full text key. It really drives
me crazy.
Here are my questions
1. In the Artcile properties, page i specify, under the name conflict
section, leave the table un changed. Then why is it that sql server tries
to drop the table and recreate it.(i checked in the sch table, what ever
option i specify, it is adament, it wants to recreate the table).
2. Is there a way, to publish full text indexed tables, without the initial
snapshot failing ?
I am in "Developer in Distress" mode, can anyone help ?
Thank You
Shrinivas
1 & 2) this should work, could you post the schema of this problem table
here?
"shrini" <senor20@.hotmail.com> wrote in message
news:%23R4xv8vJEHA.620@.tk2msftngp13.phx.gbl...
> Hi
> I have setup snapshot replication in one of my sql database. The article
> which i publish, has full text index enabled in the subscription db. So
when
> the initial snap shot is applied, i get a error message saying "cannot
drop
> index PK_<<table name>> because it enforces full text key. It really
drives
> me crazy.
> Here are my questions
> 1. In the Artcile properties, page i specify, under the name conflict
> section, leave the table un changed. Then why is it that sql server tries
> to drop the table and recreate it.(i checked in the sch table, what ever
> option i specify, it is adament, it wants to recreate the table).
> 2. Is there a way, to publish full text indexed tables, without the
initial
> snapshot failing ?
> I am in "Developer in Distress" mode, can anyone help ?
> Thank You
> Shrinivas
>
|||Hi Hilary
I managed to work around this, by dropping the Full Text index before
applying the snap (by specifying sql in the snap shot tab of properties) and
recreating the index after the snap shot is created. I think this is the
possible way for doing this. Thanks for your reply.
Shrini
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:e3KduPFKEHA.2808@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> 1 & 2) this should work, could you post the schema of this problem table
> here?
> "shrini" <senor20@.hotmail.com> wrote in message
> news:%23R4xv8vJEHA.620@.tk2msftngp13.phx.gbl...
> when
> drop
> drives
tries
> initial
>

@bitmap was not supplied to upd procedure in transactional

Initially when I had setup my transactional replication I had created it with
out taking a snapshot and by synching the databases.
Now I am trying to add new table to one of my existing publication in
production by creating scripts for ins, upd and del in test server and
deploying it to subscriber but it throws an error saying
"sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
Inserts just work fine.
This is what i used to add-
exec sp_addarticle @.publication = 'Repl_Source'
, @.article = TableName
, @.source_table = TableName
, @.sync_object = null
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
how many columns are in your table?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:AF5F5FE4-E43C-48C6-8FF9-9739C7C3DE62@.microsoft.com...
> Initially when I had setup my transactional replication I had created it
> with
> out taking a snapshot and by synching the databases.
> Now I am trying to add new table to one of my existing publication in
> production by creating scripts for ins, upd and del in test server and
> deploying it to subscriber but it throws an error saying
> "sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
> Inserts just work fine.
> This is what i used to add-
> exec sp_addarticle @.publication = 'Repl_Source'
> , @.article = TableName
> , @.source_table = TableName
> , @.sync_object = null
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
>
>
|||18 columns.
If I rememebr correctly I have tested same before a 6 months agao on
different table and i rememeber it working. Not sure what changed.
"Hilary Cotter" wrote:

> how many columns are in your table?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:AF5F5FE4-E43C-48C6-8FF9-9739C7C3DE62@.microsoft.com...
>
>
|||Hi Hilary,
I think this is what thats happenening
- As I have scripted ins, upd and del scripts in dev environment by
generating them by running snapshot in transactional replication, those are
scripted with @.bitmap varaiable.
- Looks like the code I have used to add subscription
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
is trying for ins, upd and del procs with no @.bitmap.
Instead of above code I have used
exec sp_addsubscription
@.publication = Repl_Source,
@.article = TableName,
@.subscriber = SERVERNAME,
@.destination_db = 'Repl_Destination',
@.sync_type = N'automatic',
@.subscription_type = N'pull',
@.update_mode = N'read only'
then when i made data changes it asked for snapshot to be execute dfirst and
it created sp's at destination with out bitmap
As all my other sp's were created during initial setup with bitmap i would
like them to be in sync . rather than some with bitmap variable and some with
out..
Any advise....
"SQL Replication Guy" wrote:
[vbcol=seagreen]
> 18 columns.
>
> If I rememebr correctly I have tested same before a 6 months agao on
> different table and i rememeber it working. Not sure what changed.
> "Hilary Cotter" wrote:
|||return to your publisher and in qa change to the publication database, then
recreate your replication procs by issuing a sp_scriptpublicationcustomprocs
'publicationName'
then copy what you find in the results pane, go to the subscriber, change to
the subscription database, and paste these results into qa and run them.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:C261558E-58A6-4810-A5E1-AB24439B9A9E@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> I think this is what thats happenening
> - As I have scripted ins, upd and del scripts in dev environment by
> generating them by running snapshot in transactional replication, those
> are
> scripted with @.bitmap varaiable.
> - Looks like the code I have used to add subscription
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
> is trying for ins, upd and del procs with no @.bitmap.
>
> Instead of above code I have used
> exec sp_addsubscription
> @.publication = Repl_Source,
> @.article = TableName,
> @.subscriber = SERVERNAME,
> @.destination_db = 'Repl_Destination',
> @.sync_type = N'automatic',
> @.subscription_type = N'pull',
> @.update_mode = N'read only'
> then when i made data changes it asked for snapshot to be execute dfirst
> and
> it created sp's at destination with out bitmap
>
> As all my other sp's were created during initial setup with bitmap i would
> like them to be in sync . rather than some with bitmap variable and some
> with
> out..
> Any advise....
>
> "SQL Replication Guy" wrote:
|||Hi Hilary,
I was trying several options soory could get to try your option but this
worked--
sp_addarticle
@.publication = 'Repl_Source',
@.article = 'TableName',
@.source_object = 'TableName',
@.destination_table = 'TableName',
@.ins_cmd = 'call sp_MSins_TableName,
@.del_cmd = 'call sp_MSdel_TableName',
@.upd_cmd = 'mcall sp_MSupd_TableName', -- MCALL CREATED bitmap in UPD sp
@.type = 'logbased',
@.pre_creation_cmd = 'drop',
@.status = 16,
@.source_owner = 'dbo',
@.vertical_partition = 'false',
@.filter = Null,
@.auto_identity_range = 'false'
and then
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
"SQL Replication Guy" wrote:

> Initially when I had setup my transactional replication I had created it with
> out taking a snapshot and by synching the databases.
> Now I am trying to add new table to one of my existing publication in
> production by creating scripts for ins, upd and del in test server and
> deploying it to subscriber but it throws an error saying
> "sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
> Inserts just work fine.
> This is what i used to add-
> exec sp_addarticle @.publication = 'Repl_Source'
> , @.article = TableName
> , @.source_table = TableName
> , @.sync_object = null
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
>
>