Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

I need expert Sybase Sql help with a query

Posted on 2016-07-25
3
Medium Priority
?
146 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
3 Comments
 
LVL 27

Assisted Solution

by:wilcoxon
wilcoxon earned 600 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 600 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 800 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
In this article, the configuration steps in Zabbix to monitor devices via SNMP will be discussed with some real examples on Cisco Router/Switch, Catalyst Switch, NAS Synology device.
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

971 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