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!

Register Log in
  • Congratulations SkipVought 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

bdmoran

MIS
Nov 7, 2011
87
0
0
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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 !
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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".
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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())')
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
wow, who would of thunk. Thank you again for your help.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
199
Volvere
V
Colin Burton
  • Locked
  • Question
Determine if a file exists on s different server to the SQL Server 1
Replies
2
Views
177
gmmastros
gmmastros
BeatriceC
  • Locked
  • Question
SQL Linked server StoredProcefure job creates temp files
Replies
1
Views
43
pjw001
pjw001
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
102
Chris Miller
Chris Miller
trisoldee
  • Locked
  • Question
Query multiple DB's on same server for Dynamics GP
Replies
1
Views
44
trisoldee
trisoldee

Part and Inventory Search

Sponsor

Back
Top