Passing date variable to OPENQUERY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Passing date variable to OPENQUERY

Author

Topic

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2006-04-24 : 16:45:20

I have a FoxPro database that I set up as a linked server in SQL Server 2000. I need to query one of the FoxPro tables for records within a date range. Using an example found here

http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520

I have been able to get the following SQL to work

DECLARE @Emp varchar(6)
DECLARE @SQLCMD varchar(8000)
SET @Emp = '1257'
SET @SQLCMD = 'SELECT * FROM OPENQUERY(VFP_DB,''SELECT * FROM VFP_Payroll WHERE emp_no = ''''' + @Emp + ''''''')'

EXEC(@SQLCMD)

However, I have been unable to make the jump to passing a date variable. Here is what I'm trying to get to work

DECLARE @StartDate varchar(12)
DECLARE @EndDate varchar(12)
DECLARE @SQLCMD varchar(8000)
SET @StartDate = '{04/17/2006}'
SET @EndDate = '{04/23/2006}'
SET @SQLCMD = 'SELECT * FROM OPENQUERY(VFP_DB,''SELECT * FROM VFP_Payroll WHERE date >= ''''' + @StartDate + ''''''')'

EXEC(@SQLCMD)

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'VFPOLEDB' reported an error.
[OLE/DB provider returned message: Operator/operand type mismatch.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' ICommandText::Execute returned 0x80004005: ].

I eventually need to be able to specify a date range such as
WHERE date >= ''''' + @StartDate + '''''' + AND date <= ''''' + @EndDate + ''''''')'

Any help would be greatly appreciated. Also, I'm not sure if I'm using the correct format for the date values. I'm just using what FoxPro seems to require. And any help understanding the reason for the number of single quotes.

Kevin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-25 : 02:10:12

Try this

DECLARE @StartDate varchar(12)
DECLARE @EndDate varchar(12)
DECLARE @SQLCMD varchar(8000)
SET @StartDate = '20060417'
SET @EndDate = '20060424'
SET @SQLCMD = 'SELECT * FROM OPENQUERY(VFP_DB,''SELECT * FROM VFP_Payroll WHERE date >= ''''' + @StartDate + '''''
and date < ''''' + @EndDate + ''''''')'
EXEC(@SQLCMD)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-04-25 : 05:06:12

Dunno if FoxPro will be happy with a yyyymmdd string.

If not you might like to try your original solution with the "{xxx}" style date notation, but using the ODBC format:

{ d 'yyyy-mm-dd'} such as { d '2000-12-31' }

Kristen

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2006-04-25 : 10:18:46

Thanks for the quick response. It appears the number of quotes is correct. However, when the dates are formatted as

SET @StartDate = '20060417'
SET @EndDate = '20060424'

I receive this error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'VFPOLEDB' reported an error.
[OLE/DB provider returned message: Operator/operand type mismatch.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' ICommandText::Execute returned 0x80004005: ].

When I try this format

SET @StartDate = { d '2006-04-17' }
SET @EndDate = { d '2006-04-24' }

I get the same error as above if I declare them as varchar. If I declare them as datetime, I get this error

Server: Msg 241, Level 16, State 1, Line 6
Syntax error converting datetime from character string.

So is the trick putting the dates into a character string in a format that is accepted by the provider?

Kevin

Kristen
Test

22859 Posts

Posted - 2006-04-25 : 10:39:10

"So is the trick putting the dates into a character string in a format that is accepted by the provider?"

Yes!

"If I declare them as datetime, I get this error"

Yup, 'coz you can't concatenate a String and a DateTime in SQL server - you must first convert the DateTime to a String - however, that will use SQL Servers default format (or a format you specify) so you'll be back to square one of trying to find the format the FoxPro OLE DB Provider wants ...

Perhaps there is documentation on the FoxPro OLE DB Provider that will have the answer?

You are probably limited to trying:

ODBC format like { d '2000-12-31' }

Dates in appropriate Locale format - probably one of:

20061231
2006-12-31
31/12/2006
12/31/2006

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources