create and drop table via vb

Hi,
How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how...
The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":

Dim db as ADODB.Connection
'... open connection to database

Dim strCmd As String

strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"

db.Execute strCmd

Thank you in advanceThe code you are using works on an Oracle database. The syntax for SQL Server is "SELECT...INTO...FROM".

SELECT * INTO newTable FROM oldTablesql

Create "Object Type" in sql server

I'm facing problem while creating object type in sql server.
ex:sql query
"create type student as object ( name varchar2 ( 12) , no number ( 5))" is working fine in Oracle where 'student' can be used in any table as datatype, but its not working in sqlserver. Please can you help me how to create object type in sql server.

Thanks and Regrads,

Suzan:

To create a user defined datatype you need to use the sp_addtype procedure. You might want to look this up in books online. There is an example of use on this page:

http://msdn2.microsoft.com/en-us/library/aa259606(SQL.80).aspx

|||

Thanks , but what i am looking for is different.

i am looking for somting in sql server similar to Oracle OBJECT type

CREATE TYPE Pet_t AS OBJECT (
tag_no INTEGER,
name VARCHAR2(60),
MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
RETURN Pet_t
);

is there a way in sql server to do so....

Thanks

|||

If you're using SQL Server 2005 then you can create CLR user-defined types.

Check out this BOL link for more info:

http://msdn2.microsoft.com/en-us/library/ms131120.aspx

Chris

|||

i have found that this is possible using SQL server 2005, but i need a way to do it in sql server 200.

is it possible to do so?

suzan

|||

Nope.. It is not posible in SQL Server 2000.

SQL SERVER 2000 uses UDT as synonyms for pre-defined types (example - PhoneNumber := Varchar(24) ,etc).

The possible alternate solution is storing your data as BINARY. The issue here is you wont retrive your data on SQL statements (not visible on your QA), you have to depend on your UI/BL to retrive and view the data.

Other simple solution may be storing the data as XML.

|||Thanks ...|||Thanks , but do u have a link to where i can find how to store the data as xml

Crash in Execute() method of _ConnectionPtr

Hi All,
I have a piece of code where object of _ConnectionPtr is used. When I
call the Execute() method for this object I get an debug error stating
that "The Value of ESP could be saved...".
If I ignore the message this leads to crash with "Access violation
error".
Following is the code snippet i use.
void ExecuteSQLCmd( unsigned short *sqlCmd)
{
_ConnectionPtr ConnPtr ;
_bstr_t cmd ;
_RecordsetPtr ptrRS("ADODB.Recordset");Ashish choudhari (ashishtchaudhari@.gmail.com) writes:
> I have a piece of code where object of _ConnectionPtr is used. When I
> call the Execute() method for this object I get an debug error stating
> that "The Value of ESP could be saved...".
> If I ignore the message this leads to crash with "Access violation
> error".
> Following is the code snippet i use.
> void ExecuteSQLCmd( unsigned short *sqlCmd)
> {
> _ConnectionPtr ConnPtr ;
> _bstr_t cmd ;
> _RecordsetPtr ptrRS("ADODB.Recordset");
> .
> .
> cmd = sqlCmd ;
> //occassionally crash here
> pRS = ConnPtr->Execute(cmd,NULL,enumtextCmd) ; //enumtextCmd = 1
> }
> The crash at the above mentioned statement occurs sometimes, I am not
> sure why the error is occuring I am using mo.dll version 2.50
Extremely stupid question: should you not assign ConnPtr some value
before using it?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
I did forgot to mention in the above code.
I have used CreateInstance() and Open() method of _ConnectionPtr before
actually calling Execute().
The code looks as follows
void ExecuteSQLCmd( unsigned short *sqlCmd, char *ConnStr)
{
_ConnectionPtr ConnPtr ;
_bstr_t cmd ;
_RecordsetPtr ptrRS("ADODB.Recordset");
CoInitialize(NULL) ;
ConnPtr.CreateInstance(__uuidof( Connection ));
ConnPtr.Open(ConnStr,"","",adoConnectUnSpecified) ; //
adoConnectUnspecified = -1|||
I can't see anything obviously wrong with your code, although
the CoInitialize should be called only once per thread and
before any COM calls, including the line
_RecordsetPtr ptrRS("ADODB.Recordset");
When debugging what does the call stack tell you when you
get the "The Value of ESP could be saved..." message?
Occasional crashes may indicate memory overwrites/array bounds
exceeded etc. Is you app multi-threaded? If so are there thread
safety related issues? There are 3rd party tools available
to help with these kinds of problems (e.g. BoundsChecker).|||Hi,
First, I would like to let you know actually how my code is
implemented.
I have a C++ class "CRdbms". The public member variable "ptrConn" in
class "CRdbms" is an object of class "_ConnectionPtr. The object of
these class "CRdbms" is passed to function "ExecuteSQLCmd()". Before
calling ExecuteSQLCmd(), _ConnectionPtr is initialized. Then the
function ExecuteSQLCmd () is called. ExecuteSQLCmd() looks as follows:
void ExecuteSQLCmd(CRdbms &objRdbms, unsigned short *sqlCmd)
{
bstr_t cmd;
cmd = sqlCmd;
CoInitialize(NULL);
try
{
_RecordsetPtr ptrRs("ADODB.Recordset");
if ( objRdbms.RdbmsCmdTimeOut() != -1 )
{
objRdbms.ptrConn->CommandTimeout = objRdbms.RdbmsCmdTimeOut();
}
//Occassionally crash here
ptrRs = objRdbms.ptrConn->Execute(cmd,
NULL, enumtextCmd) ; //enumtextCmd = 1
}
catch (_com_error &e)
{
status = false;
wsprintf("Code = %08lx\n", e.Error());
wsprintf("Code meaning = %s\n", (char*) e.ErrorMessage());
wsprintf("Source = %s\n", (char*) e.Source());
wsprintf("Description = %s\n", (char*) e.Description());
wsprintf ( objRdbms.errMsg, L"%s", (unsigned short *)
e.Description());
}
CoUninitialize();
}
The application is running in multi-threaded environment. The debug
error is coming will executing Execute() function of "_ConnectionPtr"
and if we ignore the message the crash occurs while results are
returned and assigned to ptrRs. i.e. statement
ptrRs = objRdbms.ptrConn->Execute(cmd, NULL, enumtextCmd) ;
//enumtextCmd = 1
It seems that the memory overwrite is taking place in this situation
but not sure why is it happening so.
Please help me to solve the problem.
Thanks and Regards,
Ashish Choudhari|||
A couple of things to try...
Do you have a single instance of your "CRdbms" class that
is shared between all threads? If so, then you may need
to serialise any access to "objRdbms.ptrConn" (use a
critical section inside your ExecuteSQLCmd function)
or create a new connection per thread.
Try moving the CoInitialize/CoUninitialize so that
they happen once per thread.|||I do not have single instance of "CRdbms" object. It is created one per
thread and not shared between any other thread.
There are multiple functions called by each thread and each function
have CoInitialize and CoUnitilize.
I will try moving them so that it occurs only one per thread.
Thanks for your comments.
Regards,
Ashish Choudhary|||Ashish choudhari (ashishtchaudhari@.gmail.com) writes:
> I do not have single instance of "CRdbms" object. It is created one per
> thread and not shared between any other thread.
> There are multiple functions called by each thread and each function
> have CoInitialize and CoUnitilize.
> I will try moving them so that it occurs only one per thread.
I would not say that I know this stuff too well, but I've written
a DLL in C++ that implements a general interface for running queries
from Perl. When the DLL is loaded the first time, I do:
CoInitializeEx(NULL, COINIT_MULTITHREADED);
And I, eh, never call CoUnitialize.
But I would like to stress that I'm a not regular programmer of COM
application, so the above may very well be an example of poor practice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I modified my code so that CoInitialize is called only once per thread.
Doing this reduced the number of crash locations. But the code still
crash in Open() function of _ConnectionPtr. My Code looks as follows.
bool AdoConnect (CRdbms &objRdbms )
{
bool status = true;
if(FAILED(CoInitialize(NULL)))
{
status = false ;
return status ;
}
try
{
HRESULT hR = objRdbms.Pcn.CreateInstance(__uuidof( Connection ));
if (!SUCCEEDED(hR))
{
_com_issue_error(hR);
status = false;
}
else
{
char *sqlCmd ;
sqlCmd = rdbms.RdbmsConnStr() ;
if (sqlCmd == NULL)
{
printf("Error: No Connection String present") ;
status = false ;
}
else
{
bstr_t cmd(sqlCmd) ;
objRdbms.Pcn->Open ( cmd, "", "", adConnectUnspecified);
status = true;
}
}
}
catch (_com_error &e)
{
if ( status )
{
objRdbms.Pcn.Release();
}
status = false;
printf("Error:\n");
printf("Code = %08lx\n", e.Error());
printf("Code meaning = %s\n", (char*) e.ErrorMessage());
printf("Source = %s\n", (char*) e.Source());
printf("Description = %s\n", (char*) e.Description());
//wcscpy ( rdbms.errMsg, ERR_CONN_FAILED );
wsprintf ( rdbms.errMsg, L"%s", (unsigned short *) e.Description());
}
return status;
}
In the above code SOMETIMES when the statement objRdbms.Pcn->Open() is
execute the application crashes here. Internally, when the function
Open() returns it destroys the object of _bstr_t and it is here it
crashes.
Please do let me know if you have any suggestions.
Thanks and Regards,
Ashish Choudhary

CR8.5 COM documentation

Hi

I'm a VB and CR newbie.

I have been looking for some documentation about which methods I can use on a crystal report object. I have found at lot of examples using google but almost none of them shows more than just creating the object and open the report.

I would like to send a SQL string to the report and execute it.

I'm using VB5

Where can I find the documentation about this?

cheers
Henryjust do an online search for documentation. i do have it, bt its 5.x MB, so i dont think it'll upload here...i cant remember the exact url i found it...
http://www.businessobjects.com
try that...
Also, try the samples in crystal. it should be in ur Crystal installed folder-->
E:\Crystal\Samples\En\Code\Visual Basic
tons of support!
(I can show u how slove ur prob, bt i only know in vc++)