Solved

I need expert Sybase Sql help with a query

Posted on 2016-07-25
3
96 Views
Last Modified: 2016-07-28
Can a Sybase expert look at my Sybase sql below. I am building a query and then I need to pass it off to be executed at line 3 below.
The table being truncated is on a linked server, and so I am appending the server name and database name to the exec statement
on line 2 below. However when my query executes I am getting an error message of "Incorrect syntax near the keyword 'exec' ".
I hard coded the prefix parameter on line 1 below, just for illustration of what I am talking about. However that value is actually created
dynamically in the stored procedure, and is not hard coded, like on line 1.


and tell me if it looks correct? I am getting an error message of illegal
1.    SET @prefix = 'POCOAPPSRV.DBTest.dbo'
2.    SET @sqlString =@prefix +  'exec ' + '  "TRUNCATE TABLE ' + @inParamTableName + '"'
3.    EXEC(@sqlString)
0
Comment
Question by:brgdotnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 26

Assisted Solution

by:wilcoxon
wilcoxon earned 150 total points
ID: 41728695
I haven't used linked servers.  The first thing I'd try is hard-coding @prefix into line 2 and seeing if the exec on line 3 still gives an error.  If so, there is something wrong with your syntax created in line 2 (or possibly an exec statement can't deal with anything on a linked server but that seems unlikely).  If not, there is something wrong with the way you are constructing @prefix.
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 150 total points
ID: 41731338
try this.
SET @prefix = 'POCOAPPSRV.DBTest.dbo'
SET @sqlString = 'TRUNCATE TABLE ' + @prefix + '.' + @inParamTableName 
EXEC(@sqlString)

Open in new window

0
 
LVL 1

Accepted Solution

by:
Prashant Gosavi earned 200 total points
ID: 41731350
I assume that by 'linked server' you mean a 'remote server' added using sp_addserver.
You can access tables on the remote server using proxy table or proxy database, and then you can issue queries/commands referring to the proxy names.

In your case, I think that proxy database may work best.

You may need to do something like the foll:

Step 1) Create proxy database (one-time setup):
create database DBTest_proxy on default = "4M" with default_location = 'POCOAPPSRV.DBTest.dbo' for proxy_update

Step 2) Build and run your sql
SET @prefix = 'DBTest_proxy..'
SET @sqlString = 'TRUNCATE TABLE ' + @prefix + @inParamTableName
EXEC(@sqlString)

I hope this helps.
-PG
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Looking for Sybase 12.5.4 EBF ESD#9.1 or 10 patch 5 808
SyBase SQL Query 7 178
sybase optimizer statistics 2 67
Sybase conversion 2 81
A big percent of today’s marketing activity is performed through the online environment. The marketing strategies that have existed a decade ago no longer relate to what’s happening today. We’re currently facing a revolutionary era, called the digit…
Invest in your employees with these five simple steps to improve employee engagement and retention.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question