Your question, your audience. Choose who sees your identity—and your question—with question security.
I need expert Sybase Sql help with a query
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)
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)
Asked:
3 Solutions
LVL 27
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.
try this.
SET @prefix = 'POCOAPPSRV.DBTest.dbo'
SET @sqlString = 'TRUNCATE TABLE ' + @prefix + '.' + @inParamTableName
EXEC(@sqlString)
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
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now