|
Hi, I am looking at this too many hours again. In Microsoft SQL via vb6 front end. I use: db.Execute "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuildFileAllOthersCDC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" & _ "drop table [dbo].[BuildFileAllOthersCDC]" In Sybase with the same VB program. I am trying to use: myConnTM.Execute "if exists (select * from sysobjects where name=" 'mytable' " = 1) drop table 'mytable'" I have tried all froms of this, all I can think of, does the "if exists" work with SYBASE SQL? I get an error, "Syntax error near if line one" Will trade gray hairs for suggestions... smile George |
|
Try this: myConnTM.Execute "if exists (select 1 from sysobjects where name= 'mytable' ) drop table mytable" Changes
BTW Is this a SQL Anywhere question or an Adaptive Server Enterprise question? The syntax changes were tested in SQL Anywhere. SQLAnywhere. Here is what I am getting to work: myConnATT.Execute "if exists (select 1 from autotiretech..sysobjects where name= 'EMAIL_LIST' ) drop table EMAIL_LIST" myConnATT.Execute "create table EMAIL_LIST (""Name"" Char)" Notice the double quotes on the column name. I can not get single quote to work for some reason. Can you point me to the values of the '1' after the select statement. I am new to SQLA so some of this takes me a few more hours then it should... smile.
(14 May '12, 23:24)
George
Replies hidden
As to the "if exists (...)" vs. " = 1" tests:
That's what you're about here: You simply want to check if there is at least one row for that table in the system catalog. The "= 1" would be useful if you would count the number of rows, i.e. for something like:
(15 May '12, 03:27)
Volker Barth
BTW: SQL Anywhere has two different SQL dialects for SQL batches, stored procedures and the like: Watcom SQL and Transact SQL (T-SQL), the latter is mostly similar to the dialects of ASE and MS SQL Server. The doc tell the differences en detail here - you just should be aware that one SQL batch should be written in one dialect...
(15 May '12, 03:34)
Volker Barth
I don't have VB6 installed on my machine at this time but the statement myConnATT.Execute "create table EMAIL_LIST (""Name"" Char)" appears to be written correctly. You can also use [NAME] to delimit the identifier name. This assumes that your quoted_identifier database option is set to ON. See http://dcx.sybase.com/index.html#1201/en/dbadmin/quoted-identifier-option.html. Or you could write it as myConnATT.Execute "create table EMAIL_LIST (" & char(34) & "Name & char(34) &" Char)" For troubleshooting statements, I build the value into a string variable and display it in a messagebox or an edit box to see what the value actually looks like. For example (unchecked syntax but I think this is mostly correct) dim stmt as String stmt = "create table EMAIL_LIST (""Name"" Char)" msgbox stmt tb.Text = stmt
(15 May '12, 09:10)
Chris Keating
Thanks, I have this working/understood. I need to do it across two DB's, both SQLAnywhere. Will : Location work on SQLA 10? INSERT INTO iq_table LOCATION 'ase_servername.ase_dbname' { SELECT col1, col2, col3,... FROM owner.ase_table } What I am doing is working with a DB that has read only, I need to grab data, put it together and then export it for the customer. So I have a second DB, just a little problem getting the data from on to the other.... smile
(15 May '12, 11:27)
George
This will not work in a pure SA environment. In SA, you need to implement remote server and proxy tables to achieve this.
(15 May '12, 11:49)
Chris Keating
|
|
What version of SQL Anywhere are you using? Version 11.0.1 and above support the "DROP TABLE IF EXISTS MyTable" syntax, by this omitting the need to check beforehand if the table already exists. |
