SQL Server Queries

[Guest]

I have converted a BE to SQL Server.

1. The Upsizing tools white paper talks about upsizing queries, but in the
SQL Server Environment Manager, I can only find Views, not Queries. Is this
where I would put the queries I want to "Upsize"? (I also realize I could
use stored procedures, but prefer the more direct effort of copying Access
queries SQL and placing it into the SQL pane of a view.)

2. Once I have created a View, how do I reference it in Access?

3. I tried converting the following and got an error about the "=". what
should I use instead?

SELECT SUM(IIf([CountOfID] = [CountOfRaw_Score], 1, 0))
AS Business_Completed
FROM..........

I am trying to count the number of instances where CountOfID is equal to
CountOfRaw_Score.

 

[Sylvain Lafontaine]

The upsizing tool won't be able to translate many of your queries. It is
possible that you will have to translate most of them yourself. There is
now Queries on SQL-Server, only Views, Stored Procedures and User Defined
Functions (UDF). If you want parameters, then you will need to use stored
procedures instead of views.

There is no IIF () function on SQL-Server, you must use the Case statement:

SELECT SUM(Case When [CountOfID] = [CountOfRaw_Score] Then 1 Else 0 End)

 

[[MVP] S.Clark]

1. A view is a SELECT query. A stored procedure is all of the Action
Queries, plus a whole lot more.

2. If you use an Access ADP, using the native SQL objects is quite seamless.
If not, thus an MDB, then you will need to link all the objects. I highly
recommend the ADP route, but this may require endless hours of
re-engineering to make it fit the SQL way of life.

3. Access and SQL don't share all of the same syntax. I don't see anything
outrageous with your code, but I don't have all the supporting information
to verify either. Like where do the fields come from, are any values null,
etc.

 

[Guest]

You and S. Clark have answered my question 1. I will read up more on this.

You answered my question 2. I will switch to the Case construct.

But I still need an answer to my question 3. I am using an MDB front end,
and I have a Form. How do I reference the View as a record source?

I created the query in Access and then just name it as a Record Source.

I migrated the query to a SQL Server View (with a slightly different name to
avoid confusion), but when replace the Record source Access query with the
name of the View, it does not work. Either there is a special way of
referencing the View in the Record source, or I have to adjust another
setting somewhere? Can you tell me which?

The upsizing tool won't be able to translate many of your queries. It is
possible that you will have to translate most of them yourself. There is
now Queries on SQL-Server, only Views, Stored Procedures and User Defined
Functions (UDF). If you want parameters, then you will need to use stored
procedures instead of views.

There is no IIF () function on SQL-Server, you must use the Case statement:

SELECT SUM(Case When [CountOfID] = [CountOfRaw_Score] Then 1 Else 0 End)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have converted a BE to SQL Server.

1. The Upsizing tools white paper talks about upsizing queries, but in the
SQL Server Environment Manager, I can only find Views, not Queries. Is
this
where I would put the queries I want to "Upsize"? (I also realize I could
use stored procedures, but prefer the more direct effort of copying Access
queries SQL and placing it into the SQL pane of a view.)

2. Once I have created a View, how do I reference it in Access?

3. I tried converting the following and got an error about the "=". what
should I use instead?

SELECT SUM(IIf([CountOfID] = [CountOfRaw_Score], 1, 0))
AS Business_Completed
FROM..........

I am trying to count the number of instances where CountOfID is equal to
CountOfRaw_Score.

 

[Guest]

Oops, I reversed 2 and 3 as my question numbers... It is still question 2 I
need an answer to.

The upsizing tool won't be able to translate many of your queries. It is
possible that you will have to translate most of them yourself. There is
now Queries on SQL-Server, only Views, Stored Procedures and User Defined
Functions (UDF). If you want parameters, then you will need to use stored
procedures instead of views.

There is no IIF () function on SQL-Server, you must use the Case statement:

SELECT SUM(Case When [CountOfID] = [CountOfRaw_Score] Then 1 Else 0 End)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have converted a BE to SQL Server.

1. The Upsizing tools white paper talks about upsizing queries, but in the
SQL Server Environment Manager, I can only find Views, not Queries. Is
this
where I would put the queries I want to "Upsize"? (I also realize I could
use stored procedures, but prefer the more direct effort of copying Access
queries SQL and placing it into the SQL pane of a view.)

2. Once I have created a View, how do I reference it in Access?

3. I tried converting the following and got an error about the "=". what
should I use instead?

SELECT SUM(IIf([CountOfID] = [CountOfRaw_Score], 1, 0))
AS Business_Completed
FROM..........

I am trying to count the number of instances where CountOfID is equal to
CountOfRaw_Score.

 

[Sylvain Lafontaine]

You access views as linked tables. See
http://support.microsoft.com/kb/q209123/ if you want to have updatable views
in Access.

With a MDB file and linked tables as the FE, it's harder to access a stored
procedure as the record source of your forms: you must create a passthrough
query and use it as the source of your form, updating its SQL string
property when necessary to pass parameters. Another possibility would be to
create a DAO or an ADO recordset and use it as the source of your form; see
http://support.microsoft.com/?kbid=281998 for more info on that topic.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Oops, I reversed 2 and 3 as my question numbers... It is still question 2
I
need an answer to.

The upsizing tool won't be able to translate many of your queries. It is
possible that you will have to translate most of them yourself. There is
now Queries on SQL-Server, only Views, Stored Procedures and User Defined
Functions (UDF). If you want parameters, then you will need to use
stored
procedures instead of views.

There is no IIF () function on SQL-Server, you must use the Case
statement:

SELECT SUM(Case When [CountOfID] = [CountOfRaw_Score] Then 1 Else 0 End)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have converted a BE to SQL Server.

1. The Upsizing tools white paper talks about upsizing queries, but in
the
SQL Server Environment Manager, I can only find Views, not Queries. Is
this
where I would put the queries I want to "Upsize"? (I also realize I
could
use stored procedures, but prefer the more direct effort of copying
Access
queries SQL and placing it into the SQL pane of a view.)

2. Once I have created a View, how do I reference it in Access?

3. I tried converting the following and got an error about the "=".
what
should I use instead?

SELECT SUM(IIf([CountOfID] = [CountOfRaw_Score], 1, 0))
AS Business_Completed
FROM..........

I am trying to count the number of instances where CountOfID is equal
to
CountOfRaw_Score.