Solved

I need expert Sybase Sql help with a query

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Employees depend heavily on their PCs, and new threats like ransomware make it even more critical to protect their important data.
Knowing where your website is hosted is as important as the features you receive, the monthly fee, and the support you receive. Due diligence should be done when choosing your next hosting provider.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 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