Tag Info

Hot answers tagged

79

No. TRUNCATE and DROP are almost identical in behavior and speed, so doing a TRUNCATE right before a DROP is simply unnecessary. Note: When I first posted this answer, there were several other highly rated answers -- including the then-accepted answer -- that made several false claims like: TRUNCATE is not logged; TRUNCATE cannot be rolled back; TRUNCATE ...


32

Testing TRUNCATE then DROP vs just doing the DROP directly shows that the first approach actually has a slight increased logging overhead so may even be mildly counter productive. Looking at the individual log records shows the TRUNCATE ... DROP version is almost identical to the DROP version except has these additional entries. ...


10

Use the PasswordLastSetTime option of the LOGINPROPERTY. SELECT LOGINPROPERTY('YourLoginName', 'PasswordLastSetTime');


7

It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea: can't have filtered foriegn keys can't FK to constants can't have multiple parents Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I ...


7

I don't actually have a sybase system to test with, but according to http://dcx.sybase.com/1200/en/dbreference/sa-split-list-sysproc.html the following solution should work: SELECT * FROM table AS T JOIN sa_split_list('1234,23,56,576,1231,567,122,87876,57553,1216') AS L ON T.id = L.row_value; If the id_list is actually stored in another table ...


5

Don't know about Sybase but in SQL Server you can do like this. select * from yourtable where value not in (select N from (values (@var1), (@var2), (@var3), (@var4), (@var5)) T(N) where N ...


4

First: This isn't easy to implement and it's going to break very easily (by which I mean performance is going to be horrible, and it's a nice little route for SQL injection attacks if you're not very very careful). I strongly advise you to re-think what you're doing because there has to be a better way. Second: The question as it stands doesn't actually ...


3

Ok ! Let me describe how this issue was solved: 1) There was definitely a corruption issue caused by a problem with vmware tools on solaris 10. When the network interface had high transfer/load operations (sample: copy of a 2 GB DB ....), it just stoped working, in the middle of the operation. To put the interface working again, I had to ...


3

Recently I had a similar problem … Here goes all the required steps with comments … ! [On Source] ============================================================================ 1) Checks on Source a. Run (on solaris prompt) On source machine Execute: uname -a In my case the output was: SunOS clusterz1 5.10 Generic_142900-03 ...


3

Much simpler: CONVERT(varchar(8),GETDATE(),112) You can find a full list of CONVERT styles in Sybase BOL, although the explanation of each style isn't particularly clear (and is actually flat wrong for style 12 & 112). The Complete Sybase ASE Reference Guide includes a full list of styles with examples. (Please note that the complete guide is externally ...


3

Rows are not returned in any specific order in SQL (in reality they are likely to be in the same order most of the time) so without an ORDER BY any of the rows could be returned first. So it is non deterministic because this first record can change each time you run the query. Wrapping it in a block doesn't make the answer deterministic, it just hides the ...


3

Short version: It depends. Generally spoken Sybase SQL Server is smart enough to do things the fastest way, though. Long version: Sybase's query processor is, at it's core, very similar to the one used in MS SQL Server. It will create worktables (internal temporary tables; not visible to the user) if the result set is sufficiently large to overflow ...


3

the batch will fail. do all the table creates first, then do all the foreign key constraints AFTER all the tables are created. OR create the tables with constraints in the correct ORDER so that constraints don't fail.


3

The transaction log is where Sybase stores all changes made to a database. It can be dumped separately from the database. Dumping the transaction log basically provides some kind of incremental backup functionality. The first thing you need to figure out is whether you need the transaction log on this database i.e. is it OK for you if all data created since ...


3

As Sybase also has a JDBC driver any Java/JDBC tool should work too (at least the basic stuff). These are the free JDBC based tools that I know of: DbVisualizer (not open source, but has a free version) ExecuteQuery SQL Developer That's not the one from Oracle! It's not open source but free as well SQL Workbench/J Squirrel has already been mentioned. ...


3

Solution is to create procedure with use of dynamic query: CREATE PROCEDURE view_with_parms( in str1 varchar(255), in str2 varchar(50) ) RESULT( myID integer, someName varchar(255)) BEGIN EXECUTE ( 'select myID, someName from ' || str1 || ' order by ' || str2 || ' asc' ); END then: select * from view_with_parms('test_table','myID'); Tested ...


3

Frian, I discussed this algorithm with a colleague last week. I haven't had a chance to test it yet, but we have confidence in it. 1) Convert the start and end times into a number of hours. This can be the HOURS() function or DATEDIFF. The baseline can be anything, but MIN(TimeStampStart) would be most convenient. Round TimeStampStart up and ...


3

I would take answer by Micheael Green a step further and suggest generating a Numbers table. It will help many other algorythms as well. Another handy table is Calendar table with every date for +/- 20 years. You can get numbers from ID column of such table as well. Here is a query I came up with. You can easily wrap it into SP to TVF. It works for me. I ...


2

I was unable to fix the database in place, but I did manage to solve this misconfiguration by: Creating a new database with my optimal device layout Migrating the schema via DDL generation scripts using ddlgen.bat Using bcp to migrate the table data Note: You could use Sybase Central's DDL generator, but it doesn't include table's indexes for some ...


2

I have seen this before. According to the Sybase ASE Documentation: Adaptive Server Enterprise implements dynamic SQL using temporary stored procedures. A temporary stored procedure is created when a SQL statement is prepared, and destroyed when that prepared statement is deallocated...[a]s a consequence of this implementation, an application accessing ...


2

It's bugging me that this question doesn't have at least one answer. So... You could use a mix of primary/foreign keys within the table per Joe Celko. Alternatively, you might want to consider a closure table. Generally, the table referencing itself seems to be the most common method for handling this. Of course, you would have to consider looping. ...


2

Been a while since I've worked in sybase but (from memory) the following sql should point you in the right direction: select * from sysobjects so inner join syscolumns sc on so.id = sc.id where sc.name = 'field name' you can also check out the sybase books online (system tables)


2

Assuming that the rows come in three, with same EntityID and consecutive FieldNum values, and the first row has a date in the MemoText, this will work, too: SELECT eq0.EntityID , eq0.Memotext AS [Date] , eq1.Memotext AS Name1 , eq2.Memotext AS Name2 FROM EQ AS eq0 JOIN EQ AS eq1 ON eq1.EntityID = eq0.EntityID AND ...


2

If you subtract 1 from FieldNum and divide the result by 3 using integral division, you will get the following results for FieldNum values of 1 till 6: FieldNum | (FieldNum - 1) / 3 ---------+------------------- 1 | 0 2 | 0 3 | 0 4 | 1 5 | 1 6 | 1 And if you replace ...


2

No. Sybase doesn't have any equivalent to the INCLUDE statement.


2

Having no experience with Sybase, I suppose I would start by exporting the database schema into a text file, then go through and modify the CREATE TABLE statements manually until I am able to load the schema into MySQL. Next, dump the data from each sybase table into CSV format file and then in the MySQL database, use LOAD DATA INFILE. Depending on your ...


2

SELECT t1.* FROM the_table t1 WHERE t1.trading_id = (SELECT max(t2.trading_id) FROM the_table t2 WHERE t2.subsection = t1.subsection)


2

This should work the same. select ... from yourtable T1 JOIN ( SELECT value FROM yourtable§ EXCEPT SELECT @var1 EXCEPT SELECT @var2 EXCEPT SELECT @var3 EXCEPT SELECT @var4 EXCEPT SELECT @var5 EXCEPT SELECT @var6 ) T2 ON T1.value = T2.value where ...


2

You can use the Sybase utility ddlgen to export the database structure, schema, objects, etc. from there you will have to go through the script files that are generated, and figure out what will run and what won't run. Data can be exported by using the Sybase bcp utility, with whatever delimiters are necessary to import into your new database. Both of ...


2

If it's a dump file, load it into a development database using the load command. Alternatively you can use a working production system to obtain a new dump. You can the transfer it from the sybase db to the ms sql server db using Microsoft's SSMA tool. The download link for SSMA is below. Microsoft SQL Server Migration Assistant v5.3 for MySQL



Only top voted, non community-wiki answers of a minimum length are eligible