Best Practices/Provider connecting to an Oracle Database?

Are there generalized best practices with regards to which method/provider to use when accessing an Oracle database? I have used both the "Native OLE DB\Microsoft OLE DB Provider for Oracle" and the "Native OLE DB\Oracle Provider for OLE DB" and both seem to have their own quirks (requirement to convert to Unicode, etc) but I also have heard that I shouldn't be using an "OLE DB" source at all, but to set it up as an ADO .Net connection.

We are just beginning to implement SSIS, and are trying to establish Best Practices/Standards etc.

Are there any gotchas - performance and/or otherwise I should know about?

Thanks in advance!

I'm assuming you've looked at the SSIS Connectivity whitepaper at http://ssis.wik.is/File:Connectivity_White_Paper/Connectivity_and_SQL_Server_Integration_Services_forum_post.doc (Oracle connectivity section).

We have plans to benchmark connectors in the future, when we might be able to share best practices and performance stats.

Best practices for accessing a sql 2005 db on the SAME box as IIS 6.0 serving asp.net 2.0

I am re-posting this from the security Forum where it remains un-answered.OK, Here's the set up.I have a Windows 2003 box, soon to have SSL installedOn it is IIS 6.0, SQL 2005 Standard Edition (5Cal user lic) SOON I'll have a prod enviornemnt where a web app being served by IIS is accessing SQL. I can go into SQL and set up a user account, call it MyAppSQLAcess, and code that into the connectionn string and lock it down to the tables/db it has access to. Or I can do it w/windows authentication, or I can do it a number of other ways, the question is this:What is the best way for an asp.net app being server by IIS 6.0 to access data from SQL 2005 server when they are all on the same BOX? WRT Speed and security? Thanks Dan

You really should take some time to read the information in the link below which is also in SQL 2005 Books Online. It'll help you in making informed decisions.

http://msdn2.microsoft.com/en-us/library/bb283235.aspx

Best of Luck!

|||

Thanks!
Nice link. Here is also another short concise article I found helpful

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

Best approach SQL Reporting Authentication - WorkGroup

Hi there
Situration
1.Development Server is on WorkGroup, not main Domain. (win 2003
server(std))
2.Accessing dev server via \\Main Domain\Administrator
3.SQL 2K (Std)
SQL Reporting
1.Using SQL authentication for shared resource, rather win
authentication.
2.Shared folder, access set to everyone.
Current Situration
1.Have created 5 reports, each has a url within Domino Notes
application. Click on anyone of them (assuming
\\Main Domain\Administrator\) --> reports work ok.
2.Log-on as non-administrator --> click on url, enter site ok, but
popup appears asking for authentication of user
e.g. name, pwd & domain.
Constraints
1.Development Box cannot be joined to main domain i.e. Non-Wins
authentication
2.Host name e.g. http:\\wtdev01\reportserver --> dealt with WINS
server, not host file !
Requirement
Any user clicks on URL for the report, will open the report and be run
without authentication issues.
Tried
1.Custom Authentication (e.g. Form Authentication) - issue cannot use
Standard version of SQL Server ! Needs to be Enterprise. Dead end.
Any ideas.This is where Anonymous access may do the trick for you if you don't need RS
to differentiate the users. If you do, then one of your constraints needs to
go away.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"ImraneA" <i.akram@.weir.co.uk> wrote in message
news:24b9d612.0411010439.7f2e15d4@.posting.google.com...
> Hi there
> Situration
> 1.Development Server is on WorkGroup, not main Domain. (win 2003
> server(std))
> 2.Accessing dev server via \\Main Domain\Administrator
> 3.SQL 2K (Std)
> SQL Reporting
> 1.Using SQL authentication for shared resource, rather win
> authentication.
> 2.Shared folder, access set to everyone.
> Current Situration
> 1.Have created 5 reports, each has a url within Domino Notes
> application. Click on anyone of them (assuming
> \\Main Domain\Administrator\) --> reports work ok.
> 2.Log-on as non-administrator --> click on url, enter site ok, but
> popup appears asking for authentication of user
> e.g. name, pwd & domain.
> Constraints
> 1.Development Box cannot be joined to main domain i.e. Non-Wins
> authentication
> 2.Host name e.g. http:\\wtdev01\reportserver --> dealt with WINS
> server, not host file !
> Requirement
> Any user clicks on URL for the report, will open the report and be run
> without authentication issues.
> Tried
> 1.Custom Authentication (e.g. Form Authentication) - issue cannot use
> Standard version of SQL Server ! Needs to be Enterprise. Dead end.
> Any ideas.|||Give that a try. Thanks.

Being DateTime Formats aware when accessing database

Maybe has been answered many times but here I go:
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.
I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegro ups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>
|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.
|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/...mework ?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegro ups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>
|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.
|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/...mework ?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegr oups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>
|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.

> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/inf...asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegro ups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>
|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.
|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,
|||Yep, I should add to the article that parametizing queries has bunch of advantages, among others
that ADO (or whatever API is used) will handle these things for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegr oups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>

Being DateTime Formats aware when accessing database

Maybe has been answered many times but here I go:
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group... />
work?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group... />
work?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.

> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/in...e.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||Yep, I should add to the article that parametizing queries has bunch of adva
ntages, among others
that ADO (or whatever API is used) will handle these things for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>

Being DateTime Formats aware when accessing database

Maybe has been answered many times but here I go:
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.
> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||Yep, I should add to the article that parametizing queries has bunch of advantages, among others
that ADO (or whatever API is used) will handle these things for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>

BDE Connection via ODBC to MS SQL Server fails

Hi,

I have a strange problem accessing a MS SQL Database from my network.
I'm using an application that is based on BDE which uses ODBC to connect to a MS SQL DB.
When I tried to connect from the network with BDE,I got this error message:

BDE Error : 13059
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).

On the server side I'm using W2003 EE ,MS SQL 2000 Standard with SP4, BDE5.11.
On the client side it's an XP SP2 with MS SQL Client installed, same BDE 5.11
When I create an alias using ODBC and mapping to my server, it connects and the communication with the Database Server succeds:
TESTS COMPLETED SUCCESSFULLY!
When I'm trying to connect using BDE Admin and double clicking on the + sign of the alias I created usind ODBC admin I get the message I listed above.
Any help would be appreciated,

Thanks.May check this http://support.plato.com/kb/tip.asp?psid=23252 link fyi.|||Thank you for replying me...in the meantime i solved the problem.
It was the incorrect settings in the registry for BDE. After erasing the keys from the registry and having reinstalled the BDE everything worked fine.
There are some files used by BDE when connecting to SQL server which are not installed when using Delphi 5 or later :(

However thank you for the usefull link.

best regards,
Sanyi|||Glad the issue is resolved and appreciate your feedback on posting the solution, that helps.