Solved

I need expert Sybase Sql help with a query

Posted on 2016-07-25
3
45 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 26

Assisted Solution

by:wilcoxon
wilcoxon earned 150 total points
Comment Utility
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 40

Assisted Solution

by:Sharath
Sharath earned 150 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now