Solved

I need expert Sybase Sql help with a query

Posted on 2016-07-25
3
61 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
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 40

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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…

863 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

21 Experts available now in Live!

Get 1:1 Help Now