My own personal Sybase ASE Frequently asked questions My own personal Sybase FAQ: (note: LOTS of this also works for Microsoft SQL Server) SQL Theory/Database Theory/ERD --- Q: What is the "Halloween problem" in Sybase? A: An anomaly associated with cursor updates, whereby a row seems to appear twice in the result set. This happens when the index key is updated by the clIEnt and the updated index row moves farther down in the result set. Is this still an issue w/ upper versions? BelIEved related to Isolation levels, which are configurable in later versions of Sybase. --- Q: Which is faster, updating records or deleting and re-inserting? A: updating will be faster; only takes half the transactions. However, be aware that doing this over and over on a small table will cause bad contention (unless RLL/max_rows_per_page is turned on). --- Q: Can you use Unions in VIEws? A: Unions in VIEws is a feature added in Sybase 12.5. --- Q: What are non-logged Operations? A: truncate table, fast bcp, use of bcp library routines, writetext to text fIElds, select into, and parallel sorts. You must have "select into/bulkcopy" dboption set to true to do any of these. These Operations eliminate the use of dump transaction in the particular database until a dump database is issued. --- Q: How do I strip the "^M" that MS tends to tack on the end of lines of text, if it gets into a string in my database? A: - Run any one of a number of solutions at the Unix level before dealing with the data in Sybase. See http://www.bossconsulting.com/sysadmin/sysadmin.faq for several possibilitIEs. - use stuff command to strip the final character --- Q: Can you append a string to an existing text fIEld? A: No; you must bring the text fIEld out of the database, do your string manipulation in an external clIEnt and re-insert. You cannot read text fIElds into variables nor pass them in as parameters of a stored procedure. You cannot simply append them like a varchar() fIEld. You can of course append strings to varchar and char columns easily: update boss_test3 set col2 = col2 + 'fgh' where col1=1; --- Q: Can I populate a variable with a where clause and call it dynamically: 1> select * from table 2> where @where_clause A: No, not in straight T-SQL. You can use a case statement or an external program wrapper to get around this. Version 12.0 purportedly adds in the ability to call SQL dynamically through the execute() function. Note: no matter what the version, you can't use dynamic sql in the order by clause. --- Q: How do I do cross-server querIEs? A: You must install CIS and create proxy tables locally to mirror remote tables that you may want to query (similarly to the way one can query Sybase IQ tables conventionally in ASE servers). --- Q: What is the advantage of defining a limit on a varchar(n) fIEld? Why not just define every column as a varchar(255)? A: Several reasons (This is Sybase FAQ #1.2.4) - (from faq): the sum of the fIElds in an index can't be > 256, so you limit y our index creation flexibility - (from faq): data structures should match business requirements, since they end up becoming a Data Dictionary. - 3rd party tools depend on the defined sizes for report generation. - v12.0 and below have row-size limitations at 1962 bytes per row. Defining many varchar(255) fIElds on a table can allow a breach of this limit. --- Q: What are the row-size limitations in Sybase and other competitors? A: - Sybase pre 11.9.2: exactly 1962 bytes per row (86 bytes overhead) - Sybase 11.9.2, 12.0: ~1962: exact number depended on your locking scheme, data only or all pages, the overhead changed slightly per page. - Sybase post 12.5: Configurable page sizes up to 16k pages increase the row size. 16k - some overhead = ~15696bytes. Its probably enough that you'd never hit it. Still defaults to 2K, just like in the old days. (and remember, text fIElds do NOT count in row size calculation) Competitors: - Microsoft SQL Server v6.5 and below: 1962, since still using Sybase's engine. - Microsoft SQL Server v7.0 and up: 8060: Max allowed column size is a varchar(8000). - MySQL: 64K - Oracle 8i and below?: Somewhere around 4mb; they allow 999 varchar2(4000) fIElds max - Oracle 9i: w/ increase of varchar2(x) limit to 4gb, 999x4gb or about 4TB. Oracle defaults to 8K. - DB/2, Informix, others: ?? Q: Should I use declarative constraints or triggers to enforce Referential Integrity (mostly Foreign keys) in my database? A: Not a simple question. Pros and cons to both sides (some of this taken from a discussion on Sybase-L 2/5/02). Answer also in keys.indexes file on Sybase_dba site. Declarative constraints (foreign key constraints) Pro - No SQL coding required, just a simple alter table command. No SQL to program and depend on Developers. - Constraints are read/interpreted by 3rd Party ERD tools, allowing for proper modelling - RI constraints appear in system tables; thus creating documentation as to what RI relationships exist - ANSI Standard, whereas triggers are not. - Better for performance? Arguable: i've heard both are better performing. Con - inflexibility doing table modifications. If you're doing a lot of table mods (adding and dropping columns) FK constraints and their existing relationships make for major headaches. I've always preferred to add/drop tables instead of using alter commands. If you've got any sort of cascading constraint relationship (table A has a foreign key reference in table b, which has a FK reference in table C) you've got to drop the FK constraints manually in table C, then table B just to do your table drop. It can get tedious. This would be easIEr if there was a "cascade constraint" option similar to Oracle's - Do not create indexes on FK fIElds; needs additional step (con in both) - Older versions of Sybase (allegedly) have had FK constraint bugs (though I've never seen it in practice). - Can't do cascading deletes. - Declarative RI have inflexible error messages Triggers Pro - Better for performance? Arguable: i've heard both are better performing. - easily disabled w/ "alter table disable trigger" to turn off RI temporarily - More flexible than constraints; can do more error checking. - Can program in before/after behavior, even though all triggers in Sybase are "after" triggers - Can program in cascading update and delete behavior - Can have more flexible error messages than Constraint RI violations. (Example: a FK relationship to a PK table needs RI for two purposes: to ensure a child row has a parent, and to ensure a parent row can't be deleted if there's an existant child row. The error messages really should distinguish between these two cases). Con - Are NOT read by 3rd party ERD tools, requiring additional maitenance to keep RI documentation updated through sp_primarykey and sp_foreignkey statements - Do not create indexes on FK fIElds; needs additional step (con in both) Q: I've got a huge table (many millions of rows, many GBs) that I need to move from one place to another temporarily. What's the best way? A: methods - bcp: very long (-F, -L options) - insert into select from ... - bcp w/ named pipes: no need for filesystem - SQL Backtrack: object only dump; good option - CIS: move table across network Q: What kinds of joins are available in Sybase? A: Regular and Outer. Must describe. Q: What do the various ERD diagram notations mean? Dots, triangles, etc? A: It seemingly varIEs, from ERD tool to tool. However; these are the basics: - Table/Entity: rectangle w/ rounded corners - FK relationship: line between two tables. - Arrow on a FK line: In simplistic models, the arrow points at the PK/parent table. - Crows feet: indicates a "one-to-many" relationship, with the table at the crows feet side being the "many" table. Also can be interpreted as having the crows feet "point" to the PK/parent table - Perpendicular bar on FK line: indicates mandatory relationship: the FK table MUST have a parent record - Circle on FK line: indicates optional relationship: the FK table MAY have a parent record. --- Q: If you update a row that doesn't exist, will the engine insert it for you? A: Nope. Though, this would be a nice feature (not necessarily default behavior though; how about an insertorupdate statement). --- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- T-SQL Coding Specific --- Q: What is the skeletal code required for a cursor declaration and use in Sybase? A: declare cursor_name cursor for select field1, fIEld2 from table where condition1, condition2 declare @variable1, @variable2 (which must match the fIElds you select) open cursor_name fetch cursor_name into @variable1, @variable2 while (@@sqlstatus = 0) begin perform actions ... fetch cursor_name into @variable1, @variable2 end (cleanup) close cursor_name deallocate cursor cursor_name Q: Can "set rowcount" take a parameter? A: No, it can take only a constant. However if you poplulate a variable then set rowcount [variable] it would work. Setting rowcount=0 returns default to all rows. --- Q: What happens if the parameter data type of a stored procedure doesn't match the column type in the table? A: table scan! If updating, automatic table lock. This is especially touchy when converting to 11.9.2, where the previously unforgiving optimizer really depends on exact data matching. --- Q: I've got duplicate rows in my table. How do I get rid of them? A: Several methods: - Create an index w/ ignore_dup_row option 1> create clustered index temp_index 2> on tablename (col1, col2, col3) with ignore_dup_row 3> go 1> drop index temp_index 2> go - Drop all indexes. create a non-unique clustered index on any column with ignore_dup_row, then drop that index, and recreate the originals. (not sure if you have to drop other indexes) - Create a duplicate table skeletal structure of your target table, with a unique constraint on the columns in question. Select/insert into the new table, letting individual rows error out. Your cloned table should now contain only unique records. - Insert all duplicate rows to temporary table 1. Then take a distinct on those duplicate rows and insert into temp table 2. Delete from target table where rows match temp table 2. Then re-insert temp table 2's data into target. This leaves just one copy of each previously duplicate row in the target table. - Manually; set rowcount 1 and issue multiple delete commands. Typing intensive but effective if you cannot create indexes or if your tables are very large. --- Q: how do I find all the duplicate instances of particular columns? A: select col1,col2,col3,col4,count(1) from table group by col1,col2,col3,col4 having count(1) > 1 --- Q: How do I get distinct instances of a group of columns, since you cannot pass more than one column to the distinct() function? A: You can do this: select cola, colb from table group by cola, colb or select distinct cola, colb from table however you can't do any counts in there. (see next question) --- Q; How do I get a count(distinct(cola,colb)) from a table? A: To Get counts, you need to be trickIEr: Options (from Sybase-L discussion 6/19/03, from Kevin Sherlock ([email protected]) Use a Temp table: select distinct customer_name,postal_street_address into #countme from customers select count(*) from #countme Concatenate strings: select count( distinct convert(char(50),customer_name) + convert(char(50),postal_street_address))) from customers Slight of Hand: (my favorite, of course) select count(sum(1)) from customers group by customer_name,postal_street_address Q: How do I lock an entire table? A: - 11.9.2 and greater: lock table [tablename]. - Previous versions, you'd have to do a hack; 1. begin tran update table set col=col go. 2. You can also do a hack by doing something like this: exec sp_setpglockpromote "table", my_table, 2, 3, 1 go set transaction isolation level 3 go begin tran go select something from my_table where at least 2 pages will be read, triggering promotion to a table lock go commit tran go exec sp_dropglockpromote "table", my_table go 3. Or, try this: begin tran delete table where 1=2 go Q: how get the rownumber returned from a select statement? i.e., print out a row number per row? A: No easy way; the best way would be to use a cursor and a counter var: declare test cursor for select fIEld from table declare @counter int declare @result char(10) select @counter=1 open test fetch test into @result while (@@sqlstatus = 0) begin select @counter,@result select @counter=@counter+1 fetch test into @result end close test deallocate cursor test - The only problem w/ this method is the multiple select statements return lots of "1 row(s) affected." output messages. Q: Is there a Sybase equivalent to Oracle's rownum? A: Not in 11.9.2 and previous. v12.0 and above (being configured for RLL) do have an internal rownum construct but it isn't dependable like Oracle's. In most cases though, you can use combinations of set rowcount X and using temporary tables w/ identity values to get certain "numbers" of rows (i.e., the first 20 rows, or rows number 100-150). See q6.2.12 in the Sybase FAQ for details. --- Q: how can you capture the SQL being sent to a Server? A: 3rd party product or dbcc traceon(3604) go dbcc pss(suid,spid,1) go (the extra "1" spits out a showplan; its optional) Syntax: dbcc pss( suid, spid, printopt = { 0 | 1 | 2 | 3 | 4 | 5 } ) OR apparently there's an undocumented dbcc feature called sqltext dbcc traceon(3604) go dbcc sqltext(spid) go output is limited to the first 400 bytes of text --- Q: How do I count the number of characters in a string? Or, How can you tell exactly how much data is actually entered into a large varchar() fIEld? A: select char_length(rtrim(COLUMN NAME)), COLUMN NAME from TABLE NAME (you need the rtrim if the column is defined as not null). Or try datalength(rtrim(column)) --- Q: How do you count the number of characters in a text fIEld? A: You can't, within Sybase anyway. My favorite way is to read the text variable into perl and use the length() function to get a character count. --- Q: how do I emulate Oracle's replace(string,oldstring,newstring) function? How do I do regular expressions or do string replacements in Tsql? A: nest a stuff function within a charindex or patindex function. E.g.: update xyz set col_1 = stuff(col_1,charindex('~~',col_1),2,char(10)) where col_1 like "%~~%" --- Q: How do I search more than the first 255 characters of a text fIEld? A: set textsize [desired size] --- Q: What is MS-SQL equivalent to syntax "select top" syntax? A: set rowcount (or you could create a cursor and limit the rows returned through set cursor rows X for cursor) --- Q: How do I compare times in Sybase A: use style "8" to convert datetime variables to just hh:mm:ss select convert(varchar(8),getdate(),8) --- Q: How do I get the current date (with a default time)? A: select convert(datetime,(convert(varchar(20),getdate(),101))) This returns 2002-01-01 00:00:00.000 (this converts getdate() with style 101, which returns mm/dd/yyyy, then converts that back to a datetime giving current date with default 12:00:00am time, the same as if you inserted just the date). --- Q: How do I get JUST the date? A: select select (convert(varchar(20),getdate(),1)). Its in string format at this point, but has no extra time fields, just "01/01/02" Q: How do you split up a comma delimited field into two fIElds (ala last,first)? A: declare @my_name varchar(40) select @my_name = "last_name,first_name" select substring(@my_name,1,charindex(',',@my_name)-1), substring(@my_name,charindex(",",@my_name)+1,char_length(@my_name)) --- Q: How can I take a comma-delimited fIEld and populate a quick temp table? A: (from Kenny Lucas) (note; your @CommaDelimitedList must have a trailing comma to work correctly. select @List = '99,141,150,161,' set @x = patindex('%,%',@List) while @x > 0 begin set @Number = substring(@List,1,@x-1) set @List = substring(@List, @x+1,len(@List)-@x) insert into #Temp (FIEld2) values (@Number ) set @x = patindex('%,%',@List) end Note: PatIndex function - Returns the starting position of the first occurrence of a pattern in a specifIEd expression, or zeros if the pattern is not found, on all valid text and character data types. Syntax: PATINDEX('%pattern%', expression) --- Q: Is there an equivalent to ISNUMERIC function in MS-SQL Server? A: No, but you can code a function (from Brian Davignon on Sybase-L 4/3/02 select (1 - sign (patindex ("%[^0-9]%", isnull (@var, "")))) If it returns 1, then at least one letter was found. 0 otherwise. However this only covers strings that contain letters and numbers, not any special chars. AND, it only can tell if something is a positive integer; can't handle negative #s. --- Q: How about an equivalent to ISDATE in MS-SQL Server? A: ??? Q: Is it better to use insert/update or readtext/writetext when working w/ text fIElds? A: (from Sybase-L discussion 5/15/02) - if using a client api, definitly use the built-in text writing capabilitIEs (ct_send_data(), or dbwritetext() etc). - using straight writetext() function in t-sql is unlogged; will corrupt your transaction log string. - You can use regular insert/update statements for text fIElds, but are susceptible to embedded odd characters (quotes, control characters, etc) invalidating your sql call. --- Q: How can I get the last day in a month in a query, not worrying about whether there's 28/30/31 days and/or a leap year? (emulating last_day(sysdate) in Oracle)? A: Posted to Sybase-L by Fred Cathey ([email protected]) 11/13/02 declare @today datetime select @today=convert(binary(4),getdate()) /* this convert strips off the time element */ select dateadd(dd,-(datepart(dd,@today))+1,@today) /* This is the 1st day of the current month */ select dateadd(mm,1,dateadd(dd,-(datepart(dd,@today)),@today)) /* This is the last day of the current month */ OR in one line (as posted to Sybase-L 2/26/03 by ghanshyam kapadia ([email protected]) select dateadd(day,-1, '1 ' + datename(mm,dateadd(mm,1,getDate())) +', ' +datename(yy,getDate())) --- Q: How do I get the last day of the current month? A: Posted to Sybase-L by Dave Ellam ([email protected]), and Paul Flint ([email protected]) dateadd (dd, -1, dateadd (mm, 1, stuff (convert (varchar, @my_date, 106), 1, 2, '01'))) or dateadd(dd, -1 * datepart(dd, @mydate), dateadd(mm, 1, @mydate)) --- Q: Does Sybase have a "between" Operator like other databases for comparison? A: Yes indeed. Between is inclusive. select * from table where a between 1 and 10 is equivalent to select * from table where a >= 1 and a <= 10 --- --- Q: What does the ^ function do? A: Exclusive Or. XOR --- Q: How do you convert a fraction (i.e. 5 1/4) to a numeric? A: from Dave Ellam ([email protected]) on Sybase-L 1/14/03 SELECT CONVERT (NUMERIC, SUBSTRING (FractionString, 1, CHARINDEX (' ', FractionString))) + (CONVERT (NUMERIC, SUBSTRING (FractionString, CHARINDEX (' ', FractionString), CHARINDEX ('/', FractionString) - CHARINDEX (' ', FractionString))) / CONVERT (NUMERIC, SUBSTRING (FractionString, CHARINDEX ('/', FractionString) + 1, 255))) FROM MyTable Q: What is the difference between varchar and nvarchar? A: nvarchar fIElds support foreign character sets (n == national). These are multi-byte character sets that require additional administration internally beyond standard ISO/Ascii sets. --- Q: Is it possible to get @@rowcount and @@error in the same query? A: sure: select @rws = @@rowcount, @err = @@error --- Q: How do you select the first x characters of a fIEld? A: select substring(fIEld,0,desired_limit) from table --- Q: How do I do a query and get a running count of the rows returned? A: Two methods (thanks to Michael Peppler, David Owen): select *, rownum = identity(10) into #tmp from table_x select * from #tmp select x.[primary_key], count(*) as row_number from x, y where x.[primary_key] >= y.[primary_key] group by x.[primary_key] --- Q: How do you emulate the Oracle select a,b,c from (select d,e,f from table) subselect syntax in Sybase? A: ??? perhaps through vIEws or temp tables --- Q: How can you emulate Oracle's "&&varname" prompting in t-sql? A: ??? I don't think you can isql specific --- Q: How do I get isql to use a different editor besides vi? A: isql -E [neweditor] will let you use the editor of choice. Log in using -E method, then in the 1> cmd line type in [neweditor] instead of vi. --- Q: how do I suppress column headings in isql without using sed or some sort of filter? A: -b flag on isql. --- Q: my output is getting split to two lines. How do I force isql to show all data on one line? A: -w flag of isql: isql -Uuser -Ppwd -w9999 --- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- sybperl Q: What is the error mean: "DB-Library error: SYBERPND: Attempt to initiate a new SQL Server Operation with results pending." A: You've still got rows pending from your previous dbsqlexec attempt. You've either attempted to issue a new command mid-result stream or you have exited the previous result-processing loop before you had fetched all the rows. =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Administrative/Operations --- Q: What causes "Infected with 11 error?" A: a bug in Sybase 10 with killing server processes. Unknown in Sybase 11.x Error message still seen in 11.9.2. Suggest searching solved cases in the Sybase tech support site. BelIEved to be a generic error message used to indicate many root causes. --- Q: Is there a way to get around the 2gb device size limit in older versions? A: Yes: Device size limits in Sybase10 is 2gb. Not an issue with Sybase11. If you have a 4gb disk partition and can't change it, create 2 Sybase devices (or several) but specify the vstart=3584000 (# might not be right). --- Q: What are specific issues to know when working w/ tempdb/temporary tables? A: - Tables created like "#name" are vIEwable only by the user who created them (if you look at the actual name of the object in tempdb, the #name is appended by a session id). These tables are dropped out of tempdb when the creating user logs out (if created from isql) or when the transaction is completed (if called from a stored procedure, e.g.). Even if you are logged in as the same user from a different window, you still cannot vIEw the data in the temporary table nor drop it. - tables created as "tempdb..name" are vIEwable by anyone (given proper grant executes) but still get dropped when the creating user logs out/ transaction finishes. - tempdb is recreated from the model database upon every reboot, so if you want bcp/trunc log on chkpt on your temp db you have to enable it on model. - tempdb is read synchronously by the Engine, thus benefits from having its devices placed on file systems. Since it is recreated each boot, there are no recovery issues either. --- Q: Can you turn off logging in tempdb? A: No --- Q: Whats a good way to do index maintenance? A: Create stored procedures in your database starting with di_ and ci_ that contain the DDL to drop and recreate...then you don't have to search for it when you need to perform the action. --- Q: What is a good way to estimate the size of a database dump? A: sp_spaceused, and look for the "data fIEld." --- Q: how do I tell what database objects may be affected by dropping a table? A: sp_depends [table_name] --- Q: How can I insert an image into a table? A: Two methods (one feasable, one not) - straight SQL: insert table(image_col) values (0xHEX_STRING) (where your image has been hex-encoded) - Using Open ClIEnt calls in C/C++/perl/etc: ct_send_data() and ct_data_info() functions handle image insertion. see $Sybase/sample/ctlibrary/getsend.c for code examples. The documentation goes into very severe detail. --- Q: How do I encrypt sensitive data stored inside my Sybase database? A: - dbcc hidetext: undocumented feature of dbcc? - sp_hidetext; deliberately hides the code of stored procs (so that commercial vendors can install code w/o having it stolen) - home grown encryption modules (crypt() function in perl and C, eg) From Sybase L discussion 8/12/03 - Use Java encryption techniques to secure data upon insert - Use clIEnt side encryption, store the results in varbinary columns in database - Protegrity; Sybase partner solution using Open Servers - RSA toolkit to encrypt/decrypt data from front end --- Q: Is there a way to reverse sp_hidetext, once you've done it? A: No. --- Q: I lost/forgot my sa passWord; how do I reset it? A: - su - Sybase - edit the RUN_server file add "-p" at the end of the dataserver line - restart the server. This causes the system to reset the sa passWord and print it out to the errorlog as the server comes back up. Log in as sa with new passWord. --- Q: How do I reset the sa passWord back to NULL once i've set it to something? A: One known step: sp_configure 'upgrade version' (take note of your current setting) sp_configure 'upgrade version', 492 sp_passWord callers_pwd, NULL, sa sp_configure 'upgrade version', orig_number in 12.0: to set any account's passWord to Null; sp_configure "minimum passWord length",0 sp_password [Old PassWord],NULL --- Q: I didn't actually set my sa passWord during init; now I want to change it from Null to something else. How? A: sp_password null, "[passWord]" Q: How do I generate a resource file for automating the creation of a Server through srvbuildres (say, for the purposes of creating an identical server on another Machine?) A: See $Sybase/init/logs for the resource file created when you created (if successful) your last Sybase server (backup, data, etc). --- Q: How do I dump a database that's larger than 2gb (on systems which cannot handle larger files? A: dump database DB to "file1" at BACKUP_SVR stripe on "file2" at BACKUP_SVR stripe on "file3" at BACKUP_SVR Q: Is there a way to determine the last time update statistics was run on a particular table? A: - You can get creation date of Statistics by running optdiag. - You can get the creation date of statistics per column, per table by running select object_name(id), moddate from sysstatistics order by id --- Q: Is there a way to determine the create date of an index? A: 12.0 and below: No, there really isn't... - You can tell the creation date of any RI-causal indexes (constraints) but not the last time they were re-issued. Any clustered indexes or non-clustered indexes: no way to tell. - You can tell the last time statistics were modifIEd in 11.9.2 and higher by using optdiag. This is as close as you can get. You can look at the moddate in sysstatistics. - 12.5 and above: Salvation! indcrdate column added to sysindexes which contains the date the index was last created or rebuilt! --- Q: Is there a good way to tell how often an Index is used? Usage stats for indexes? A: Not as of 12.5. A feature being looked at by Sybase Engineering. 12.0: you can capture query plans, grep through the output to see index usage. But there's no easy way to tell if an index even WAS used during an execution period. --- Q: How do I find out the packet size a Server connection is using? A: network_pktsz in sysprocesses Q: When is a "bug" not really a "bug" A: (as paraphrased from an Eric Miner post 1/10/01 to Sybase-L) When an optimizer inconsistency is reported to Sybase, but its known behavior with workarounds. There's not a bug per se, but behavior that could/should be improved. Always insist on 302/310 trace output. --- Q: What do the segman values mean in Sysusages? A: involves bitwise arithmetic - 0: - 3: data only - 4: log only - 7: data and log - 11: ?? (used by sybsecurity) - 27: ?? (used by dbccdb) --- Q: How do you get a Stored Procedure to execute immediately after a server boots? A Sybase "rc" stored proc? A: ??? There is no known function within Sybase...perhaps an external program called from the Start RC script? Still not reliable..what if Sybase server doesn't come up all the way? --- Q: Whats a good script to use to age-off old log files? A: This shell code snippet works w/o bouncing the Sybase server. Code from Raoul Bantuas ([email protected]), posted to Sybase-L 2/4/1998 errlog=$1 # name of error log, as parameter no. 1 cat $errlog > $errlog.`date +%b%d_%H:%M` # to keep an old copy cat /dev/null > $errlog find $errlog.* -mtime +7 -exec rm {} \; --- Q: How do you "offline" a database? A: pre 12.0, update sysdatabases set status2 = 8 where dbname = "your database name here" (to bring online again, you could try online database or the following query: update sysdatabases set status2 = status2 - 8 where dbname = "your database name here" and status2 & 8 = 8 as of 12.0: offline database [dbname] --- Q: Can you create functions in TSQL, like functions in Oracle? IE. select a, myfunction(b,c) from tablename A: no. Not as of 12.0 Best solution is probably to write a stored procedure to do the function and call it in your proc before you get to this select statement. No way to do it inline. --- Q: What does a "Stack Overflow error" mean? A: Just as it sounds; an internal memory stack used by Sybase has been corrupted somehow and has failed, causing the SQL execution to fail. --- Q: How do I get online help for DBCC commands? A: grant the Sybase_ts_role to a useraccount, then run dbcc traceon (3604) go dbcc help (cmd) go --- Q: Is it better to store images within Sybase image types or just store pointers to the files? A: I prefer pointers to files; it elminates the difficulty of putting images into and out of Sybase, keeps the database size down, and the O/S is far more efficIEnt at storing image files than Sybase. --- Q: What is the maximum size of a blob/image/text fIEld in Sybase? A: No limit; but docs probably say 2gb. --- Q: What is Oracle equivalent of "blob" datatype? "Long" datatype? A: blob == image, long = text. ---
