Is it same ,except  the provider?

> Is it same ,except  the provider?

SQL server and Access have different capabilities and the SQL dialect has some differences.

> {quote:title=Pero perov wrote:}{quote}
> Is it same ,except  the provider?

As Brian said, there are differences.  For me, the most obvious difference was the way SQL in Access requires dates to be like this #03/17/2009# but in standard SQL, as we use with MS SQL-Server ,we just use '03/17/2009'.

It should be easy to test your SQL in Access and the other database you are using, to see what syntax works and what syntax does not work.  If you are migrating from Access to SQL-Server, the only capability I know of that cannot be migrated with just a syntax change is the "IN <externaldatabase>" capabilities of Access.  However, if you are migrating TO Access, then I would expect to find more difficulties.

On Tue, 17 Mar 2009 06:38:53 -0000, Mike Ver Hagen  wrote:

> As Brian said, there are differences.  For me, the most obvious  
> difference was the way SQL in Access requires dates to be like this  
> #03/17/2009# but in standard SQL, as we use with MS SQL-Server ,we just  
> use '03/17/2009'.

Even though Access and SQLserver require different delimiters round  
"dates", both will accept the date in YYYY-MM-DD format. This can save a  
lot of trouble - especially when the machines running the application and  
database are set up to different locales.

-- 
Paul Scott
Information Management Systems
Macclesfield, UK

Good point.  I'll switch to the more generalized format.  Thanks.

> {quote:title=Paul Scott wrote:}{quote}
> On Tue, 17 Mar 2009 06:38:53 -0000, Mike Ver Hagen  wrote:
> 
> > As Brian said, there are differences.  For me, the most obvious  
> > difference was the way SQL in Access requires dates to be like this  
> > #03/17/2009# but in standard SQL, as we use with MS SQL-Server ,we just  
> > use '03/17/2009'.
> 
> Even though Access and SQLserver require different delimiters round  
> "dates", both will accept the date in YYYY-MM-DD format. This can save a  
> lot of trouble - especially when the machines running the application and  
> database are set up to different locales.
> 
> -- 
> Paul Scott
> Information Management Systems
> Macclesfield, UK

> I'll switch to the more generalized format.

Best is - always to use parameters.

-- 
With best regards,
Dmitry Arefiev
AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL,
  Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX,
  ODBC, InMem - universal high-speed native data access engine

Dmitry,

On Wed, 18 Mar 2009 04:43:29 -0000, Dmitry Arefiev <darefiev@da-soft.com>  
wrote:

>> I'll switch to the more generalized format.
>
> Best is - always to use parameters.

Although I would just point out that parameters are not a panacea - these  
groups have shown several problems with parameters.

Whether these actual problems were in the implementation of the parameter  
replacement mechanism or were incorrect understanding or usage by the  
programmer, the fact remains that parameter replacement is yet another  
"black box" which is very difficult to follow through if/when things go  
wrong.

Personally, I prefer the KISS approach.

-- 
Paul Scott
Information Management Systems
Macclesfield, UK

> Although I would just point out that parameters are not a panacea -
> these groups have shown several problems with parameters.

Well ... If:
- N = number of problems, when app does not use parameters
- M = number of problems, when app does use parameters

Then M > 0, but N >> M 

<g>

-- 
With best regards,
Dmitry Arefiev
AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL,
  Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX,
  ODBC, InMem - universal high-speed native data access engine

Dmitry,

On Wed, 18 Mar 2009 12:33:57 -0000, Dmitry Arefiev <darefiev@da-soft.com>  
wrote:

>> Although I would just point out that parameters are not a panacea -
>> these groups have shown several problems with parameters.
>
> Well ... If:
> - N = number of problems, when app does not use parameters
> - M = number of problems, when app does use parameters
>
> Then M > 0, but N >> M
>
> <g>

True, but that "M > 0" term can still lead to a lot of wasted time - even  
if (especially if) the actual problem is not in the parameter replacement  
(this time <g>). Just being aware that it might possibly be a "parameter  
problem" can blind a programmer to the real faults in his SQL.

I find that building up the complete SQL command into a string variable  
makes it trivial to view the command in the debugger - and then to copy  
and paste it into Management Studio or Access and see /exactly/ what the  
database thinks of it. (Apart from the fiddly dialect changes made by ADO,  
of course!)

Even the supposed speed advantages from reusing "Prepared" commands turned  
out to be minimal and sometimes counter-productive..
http://msdn.microsoft.com/en-us/library/aa260835.aspx (see: "The  
(so-called) Prepared property")

Of course, YMMV :)

-- 
Paul Scott
Information Management Systems
Macclesfield, UK

Will not come into discussion of the parameters, just will say, 
that most of the beginners in a SQL application development
have **a lot** of problems at first with the incorrectly formated
constants in a SQL. The switch to a parameter usage in most 
cases solves the problems ...

> Even the supposed speed advantages from reusing "Prepared" commands
> turned out to be minimal and sometimes counter-productive..

I think, the main benefit of a parameter usage is an ability 
to use a client tool data representation and do not enter into
the details of a DBMS representation.

And there are few more hidden and/or DBMS-dependent 
benefits.

-- 
With best regards,
Dmitry Arefiev
AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL,
  Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX,
  ODBC, InMem - universal high-speed native data access engine

Paul:

I have never been able to use parameters for my most important and complicated queries because they vary A LOT depending on user input.  For instance, if a user has the option of selecting records from among these methods:

All agents
Agents selected in a list in the GUI
Agents with activity in some date range
Agents with activity involving specific other entities such as clients or products
plus sensible combinations of the above

the problem is that not all queries are going to specify the activity-related parameters (the last 2 in that list)

I have a lot of selection-criteria screens that offer selections such as those described above, plus several additional SETS of criteria like that, just for one report.

You don't have to go too far down that road to see that you have thousands of possible combinations (for just one report), each of which would have to be represented as a parameterized query.  I suppose you could build those on-the-fly, and maybe even save them in some databases if you could later detect that the desired combination already exists from an earlier run so you can use it again.

Absent a compelling business case for developing such a mechanism, I share your appreciation for the handiness of straight-up SQL generated by my app.  If one's fall-back interest in parameters is avoidance of SQL-injection, why not just apply screening for that?

mIKE

> {quote:title=Paul Scott wrote:}{quote}
> Dmitry,
> 
> On Wed, 18 Mar 2009 12:33:57 -0000, Dmitry Arefiev <darefiev@da-soft.com>  
> wrote:
> 
> >> Although I would just point out that parameters are not a panacea -
> >> these groups have shown several problems with parameters.
> >
> > Well ... If:
> > - N = number of problems, when app does not use parameters
> > - M = number of problems, when app does use parameters
> >
> > Then M > 0, but N >> M
> >
> > <g>
> 
> True, but that "M > 0" term can still lead to a lot of wasted time - even  
> if (especially if) the actual problem is not in the parameter replacement  
> (this time <g>). Just being aware that it might possibly be a "parameter  
> problem" can blind a programmer to the real faults in his SQL.
> 
> I find that building up the complete SQL command into a string variable  
> makes it trivial to view the command in the debugger - and then to copy  
> and paste it into Management Studio or Access and see /exactly/ what the  
> database thinks of it. (Apart from the fiddly dialect changes made by ADO,  
> of course!)
> 
> Even the supposed speed advantages from reusing "Prepared" commands turned  
> out to be minimal and sometimes counter-productive..
> http://msdn.microsoft.com/en-us/library/aa260835.aspx (see: "The  
> (so-called) Prepared property")
> 
> Of course, YMMV :)
> 
> -- 
> Paul Scott
> Information Management Systems
> Macclesfield, UK