Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DATEADD on Linked Server

Status
Not open for further replies.

bdmoran

MIS
Nov 7, 2011
87
US
Hello,

I have the following DATEADD function I used in a stored procedure:

(downloadedOrders.EditDt > DATEADD(hh, - 1, GETDATE()))


This works great for me but when I try to use it in a linked server I keep getting errors. Here is an example of what I have on my linked server:

select print_ticket_ind, Class_name,Status,acct_id, section_name, event_date, event_time,printed_datetime from openquery(premclub, 'select * from dba.v_ticket where Class_Name = ''PC_Default''and Status = ''Sold'' and print_ticket_ind = ''y''and section_name like ''ST%'' or section_name like ''%view%'' or section_name like ''patron%'' and event_date > ''2011-04-25'' AND printed_datetime > DATEADD('hh, - 1', GETDATE()))''')


Any help would be greatly appreciated!

Thank you,

Brendon
 
This part is wrong.

[tt][red]DATEADD('hh, - 1', GETDATE()))''')[/red][/tt]

Change it to this:

[tt]DATEADD(hh, -1, GETDATE()))''')[/tt]

*** Note, 2 single quotes were removed and the space between the - symbol and the 1 was also removed.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
I am still getting an error. Here is my updated statement:

select print_ticket_ind, Class_name,Status,acct_id, section_name, event_date, event_time,printed_datetime from openquery(premclub, 'select * from dba.v_ticket where Class_Name = ''PC_Default''and Status = ''Sold'' and print_ticket_ind = ''y''and section_name like ''ST%'' or section_name like ''%view%'' or section_name like ''patron%'' and event_date > ''2011-04-25'' AND printed_datetime > DATEADD(hh, -1, GETDATE()))''')


And here is my error message:

OLE DB provider "MSDASQL" for linked server "premclub" returned message "[Sybase][ODBC Driver]Syntax error or access violation".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "premclub" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * from dba.v_ticket where Class_Name = 'PC_Default'and Status = 'Sold' and print_ticket_ind = 'y'and section_name like 'ST%' or section_name like '%view%' or section_name like 'patron%' and event_date > '2011-04-25' AND printed_datetime > DATEADD(hh, -1, GETDATE()))'" for execution against OLE DB provider "MSDASQL" for linked server "premclub".


I have the SQL Server 2005 Express edition installed on my computer ---is this why I would have a permission error?

Thanks for the quick response !
 
sounds like a permission issue to me. You said this was set up as a linked server, correct? What happens when you run this query...

Code:
select top 10 * 
from premclub.YourDatabaseNameHere.dba.v_ticket

This should only return the top 10 rows, so it will perform very quickly (if it works), or generate a more useful error message if it doesn't.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Yes, it is a link server.

And I got this message when I ran the above query...

OLE DB provider "MSDASQL" for linked server "premclub" returned message "Method is not supported by this provider.".
OLE DB provider "MSDASQL" for linked server "premclub" returned message "[Sybase][ODBC Driver]Driver not capable".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "premclub" reported an error. The provider does not support the necessary method.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "premclub". The provider supports the interface, but returns a failure code when it is used.
 
It sounds to me like your linked server is not set up properly. I don't have a lot of experience with sybase, so I may not be the best person to give advice. Sorry.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
That's strange because when I run my query without DATEADD function it works perfectly fine. I appreciate your help. If you can point me in the right direction that would be great. Thanks!
 
Maybe it would be best to calculate that value before using it in the query. Ex...

Code:
Declare @printed_datetime DateTime
Set @printed_datetime = DATEADD(hh, - 1, GETDATE())

select print_ticket_ind, Class_name,Status,acct_id, section_name, event_date, event_time,printed_datetime from openquery(premclub, 'select * from dba.v_ticket where Class_Name = ''PC_Default''and Status = ''Sold'' and print_ticket_ind = ''y''and [!]([/!]section_name like ''ST%'' or section_name like ''%view%'' or section_name like ''patron%''[!])[/!] and event_date > ''2011-04-25'' AND printed_datetime > ''' + Convert(VarChar(20), @printed_datetime) + ')''')

I also recommend that you add the parenthesis to your query like I show above. You need to be extremely careful about the order of operations when you mix and with or in a where clause.

I have no idea if this will work for, but it's worth a try.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
I may be wrong, but aren't there 2 extra ' at the end of the statement ?
GETDATE()))''') < I think it should be a single quote there on the modified sql on post 3

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
No, I stll recieve an error when I use single quotes:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * from dba.v_ticket where Class_Name = 'PC_Default'and Status = 'Sold' and print_ticket_ind = 'y'and section_name like 'ST%' or section_name like '%view%' or section_name like 'patron%' and event_date > '2011-04-25' AND printed_datetime > DATEADD(hh, -1, GETDATE()))" for execution against OLE DB provider "MSDASQL" for linked server "premclub".
 
remove the last ) of GETDATE())) and spaced some of the and's

Code:
select print_ticket_ind, Class_name,Status,acct_id, section_name, event_date, event_time,printed_datetime from openquery(premclub, 'select * from dba.v_ticket where Class_Name = ''PC_Default'' and Status = ''Sold'' and print_ticket_ind = ''y'' and section_name like ''ST%'' or section_name like ''%view%'' or section_name like ''patron%'' and event_date > ''2011-04-25'' AND printed_datetime > DATEADD(hh, -1, GETDATE())''')

I would also check well your conditions - mix of AND and OR without "()" surrounding them normally leads to undesired results due to logic errors

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
try this one...

SQL:
 select print_ticket_ind, Class_name,Status,acct_id, section_name, event_date, event_time,printed_datetime from openquery(premclub, 
 'select * from dba.v_ticket where Class_Name = ''PC_Default'' and Status = ''Sold'' and print_ticket_ind = ''y'' and section_name like ''ST%'' or 
 section_name like ''%view%'' or section_name like ''patron%'' and event_date > ''2011-04-25''
  AND printed_datetime > DATEADD(hh, -1, GETDATE())')

Meganathan
 
Small correction as copied the code and left the extra 2 '' at the end ..

Code:
select print_ticket_ind, Class_name,Status,acct_id, section_name, event_date, event_time,printed_datetime from openquery(premclub, 'select * from dba.v_ticket where Class_Name = ''PC_Default'' and Status = ''Sold'' and print_ticket_ind = ''y'' and section_name like ''ST%'' or section_name like ''%view%'' or section_name like ''patron%'' and event_date > ''2011-04-25'' AND printed_datetime > DATEADD(hh, -1, GETDATE())')

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Another question: I can't seem to join the ticket and item_group table. Is syntax diffrent becasue of the openquery?


select print_ticket_ind, Class_name,Status,acct_id, section_name, event_date, event_time,printed_datetime from openquery(premclub,'select * from dba.v_ticket)
INNER JOIN (''DBA_v_ticket ON DBA_v_item_group.item_id = DBA_v_ticket.event_id'')
where Class_Name = ''BO_PPKGHOLD or BO_GPKGHOLD''and Status = ''Sold'' and section_name like ''ST% or %view% or patron%'' and event_date > ''2011-04-25'' AND printed_datetime > DATEADD(hh, -1, GETDATE())')
 
because that query is now completely wrong.

at the moment you have
openquery(premclub,'select * from dba.v_ticket) - this is the remote query

INNER JOIN (''DBA_v_ticket ON DBA_v_item_group.item_id = DBA_v_ticket.event_id'') - this on its own its wrong as you are really joining the open query with a constant

and then you have a where clause
where Class_Name = ''BO_PPKGHOLD or BO_GPKGHOLD''and Status = ''Sold'' and section_name like ''ST% or %view% or patron%'' and event_date > ''2011-04-25'' AND printed_datetime > DATEADD(hh, -1, GETDATE())')

before putting the remote query on the openquery group, do the query on its own - without any double single quotes and so on.

then when it looks ok add the required single quotes, and add it to the openquery

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Alright I figured out the join:

select class_name, dba.v_item_group.event_date from openquery(premclub,
'select print_ticket_ind, Class_name,Status,acct_id, section_name, dba.v_ticket.event_date, dba.v_ticket.event_time,printed_datetime from dba.v_ticket INNER JOIN DBA.v_item_group ON DBA.v_item_group.item_id = DBA.v_ticket.event_id where Class_Name = ''BO_PPKGHOLD or BO_GPKGHOLD''and Status = ''Sold'' and section_name like ''ST% or %view% or patron%'' and dba.v_ticket.event_date > ''2011-04-25'' AND printed_datetime > DATEADD(hh, -1, GETDATE())')

Any idea why I would receive this error?

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dba.v_item_group.event_date" could not be bound.
 
change select class_name, dba.v_item_group.event_date from openquery .....
to be select class_name, event_date from openquery ....

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
wow, who would of thunk. Thank you again for your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top