More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Is it possible to do dynamic SQL before ASE 12 in Sybase?
Again, using CIS, it is possible to fake dynamic SQL. Obviously for this to work, CIS must be enabled. In addition, the local server must be added to sysservers as a remote server. There is a stored procedure, sp_remotesql, that takes as an arguments a remote server and a string, containing SQL.
As before, adding SELF as the 'dummy' server name pointing to the local server as if it were a remote server, we can execute the following:
sp_remotesql "SELF","select * from sysdatabases"
Which will do just what you expect, running the query on the local machine. The stored proc will take 251 (according to its own documentation) arguments of char(255) or varchar(255) arguments, and concatenate them all together. So we can do the following:
1. declare @p1 varchar(255),@p2 varchar(255),@p3 varchar(255), @p4 varchar(255)
2.
3. select @p1 = "select",
4. @p2 = " name ",
5. @p3 = "from ",
6. @p4 = "sysdatabases"
7.
8. exec sp_remotesql "SELF", @p1, @p2, @p3, @p4
9. go
(1 row affected)
name
------------------------------
bug_track
dbschema
master
model
sybsystemprocs
tempdb
(6 rows affected, return status = 0)
Obviously, when the parameters are concatenated, they must form a legal T-SQL statement. If we remove one of the spaces from the above statement, then we see:
1. declare @p1 varchar(255),@p2 varchar(255),@p3 varchar(255), @p4 varchar(255)
2.
3. select @p1 = "select",
4. @p2 = "name ",
5. @p3 = "from ",
6. @p4 = "sysdatabases"
7.
8. exec sp_remotesql "SELF", @p1, @p2, @p3, @p4
9. go
Msg 156, Level 15, State 1
, Line 1
Incorrect syntax near the keyword 'from'.
(1 row affected, return status = 156)
(Continued on next question...)
