Software

Ask something
Discussion Locked

Upsizing from Access to SQL

0 helpful
3 replies

by maryweilage

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

The July 6 edition of the SQL Server e-newsletter
explains how to upsize from Access to SQL Server. Are you trying to upsize Access in SQL Server? What problems have you encountered?

If you aren't subscribed to the free SQL Server e-newsletter, click the following link to automatically sign up:
http://nl.com.com/MiniFormHandler?brand=builder&subs_channel=bldr_front_door&list_id=e046&tag=fb

3 replies

by artful

Before anyone stomps on me, I'd like to post a correction to my text.

The text that reads:

SELECT *
FROM SomeTables
WHERE ColumnOfInterest = 12345 OR ColumnOfInterest is NULL

should read:

SELECT *
FROM SomeTables
WHERE ColumnOfInterest = myParameter OR myParameter is NULL

Second, the sproc supplied should use the parameter instead of a specific date value:

CREATE PROCEDURE dbo.OrdersListSproc1
(@OrderDate DateTime)
AS SELECT dbo.Orders.*
FROM dbo.Orders
WHERE (OrderDate < CONVERT(DATETIME, '1996-08-01 00:00:00', 102))
OR (@OrderDate IS NULL)

should read:

CREATE PROCEDURE dbo.OrdersListSproc1
(@OrderDate DateTime)
AS SELECT dbo.Orders.*
FROM dbo.Orders
WHERE (OrderDate < CONVERT(DATETIME, @OrderDate, 102))
OR (@OrderDate IS NULL)

Sorry about that, dear readers. I should have caught this in the edit, but didn't. My apologies to all.

Arthur

by artful

Before anyone stomps on me, I'd like to post a correction to my text.

The text that reads:

SELECT *
FROM SomeTables
WHERE ColumnOfInterest = 12345 OR ColumnOfInterest is NULL

should read:

SELECT *
FROM SomeTables
WHERE ColumnOfInterest = myParameter OR myParameter is NULL

Second, the sproc supplied should use the parameter instead of a specific date value:

CREATE PROCEDURE dbo.OrdersListSproc1
(@OrderDate DateTime)
AS SELECT dbo.Orders.*
FROM dbo.Orders
WHERE (OrderDate < CONVERT(DATETIME, '1996-08-01 00:00:00', 102))
OR (@OrderDate IS NULL)

should read:

CREATE PROCEDURE dbo.OrdersListSproc1
(@OrderDate DateTime)
AS SELECT dbo.Orders.*
FROM dbo.Orders
WHERE (OrderDate < CONVERT(DATETIME, @OrderDate, 102))
OR (@OrderDate IS NULL)

Sorry about that, dear readers. I should have caught this in the edit, but didn't. My apologies to all.

Arthur

by chasbeard

Mr. Fuller,
Thank you for your many useful submissions to this forum. I have been able to make good use of them. In this one, however, you made a mistake that I feel deserves attention. (By the way, thanks for the obvious correction posts to the text.) In the article, you write that
"Porting such an approach to SQL Server is bad for several reasons: "

and state that one is:
"This approach is subject to SQL injection attacks."

This is true. But the very solution you propose does the same, without even having to have the intent. One simply has to hit the submit button (or some such option) without having placed a value in the control. All order data would be returned, just as you state. I think you would agree that this is not exactly a secure application.

A more proper way of handling this would be to use an "AND" addtion to the SQL Statement, since you write that this was the intent of the original Access program. That is:

SELECT *
FROM SomeTables
WHERE ColumnOfInterest = myParameter AND myParameter is NOT NULL

**OR**

SELECT *
FROM SomeTables
WHERE ColumnOfInterest = myParameter AND ColumnOfInterest is NOT NULL

This would require that the entry be one that the user would have to know. Further security, such as looking at signon info, would probably work with either your or my suggested solution. I realize in the specific example you gave later, only the order date is in question, indicating that some such further security is in place. My suggestion is simply generic. You could use it in the same way that earlier articles have suggested for the prevention of SQL Injection, to keep intentional or unintentional pulling of non-related data.

Thanks, again, for your articles.

This discussion is closed to new replies.