Database

Re: Create an Access Database at runtime

29-Apr-04 10:24

Not exactly. If you try to mix ADO and DAO, you normally get a ton of conflicts. More specifically, the DataType enum seems to have been defined in both libraries, and they are not compatible. That is the voice of experience Poke tongue | ;-P

speaking!

ADOX supposedly has a Create function in its catalog class that can be used to create a database at run time. I have not had much luck with it yet. Perhaps Carlos Antollini (of CADOXxxx classes fame) may have an answer? I read that he has an update to his ADOX classes about ready. I sure am looking forward to it.

Santanu Lahiri30-Apr-04 5:55

Sage Accounting Version 10

30-Apr-04 5:55

This seems to work fine with an abbreviated Connection string.
You can not use the full-blown connection string.

I just tested it and it created the database successfully. Probably not the most efficient error reporting, but that you can modify to suit your needs.

The CADOXCatalog comes from Carlos Antollini's ADOX class available on CodeProject website.

------------------------------------------------------------------------
CString sConnectionString = _T("Provider='Microsoft.Jet.OLEDB.4.0';Data Source='%s'");
CString sMsg;
try
{
CADOXCatalog cat;
CString sConnect;
sConnect.Format (sConnectionString, sDBName);
cat.m_pCatalog->Create(_bstr_t((LPCTSTR) sConnect));
sMsg.Format (_T("Database %s created successfully"), sDBName);
}
catch (_com_error& e)
{
TCHAR szBuf[300];
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
sprintf(szBuf, "Source : %s\nDescription : %s", (LPCSTR)bstrSource,(LPCSTR)bstrDescription);
CString sErr = CString (szBuf);
sMsg.Format (_T("Can not create database %s\n%s\n%s"), sCmd, CString (e.ErrorMessage()), sErr);
}
AfxMessageBox (sMsg);

Kryptos Sol29-Apr-04 4:43

Kryptos Sol

29-Apr-04 4:43

Has anyone use connected sucessfully to Sage using there shipped driver?

You can query all of the tables using Excel, but can't using either vb6 or vb.net.

Thanks in advance.

Wayne Taylor
Kryptos Solutions
Cry | :((

exec two stored procedure in a main stored procedure

manasrahfantom29-Apr-04 2:27

manasrahfantom

29-Apr-04 2:27

Hi there

i have a stored procedure like this:

CREATE PROCEDURE SP_Main

AS
SET NOCOUNT ON

BEGIN TRANSACTION

exec SP_Sub_One output

exec SP_Sub_Two output

IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran

return
GO

now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.

now i can put the "begin transation" in the two sub stored procedure's. The problem is

what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"

now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data

how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.

Thanks in advance

Mahmoud Manasrah

Re: exec two stored procedure in a main stored procedure

Jeff Varszegi29-Apr-04 3:11

Re: exec two stored procedure in a main stored procedure

29-Apr-04 3:11

Check out this transaction documentation from the MSDN site.

Note especially this paragraph:

Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback are in fact rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back.

Over time, I've learned to always use named transactions as a matter of course. When I use them in stored procedures, I often name them after the procedure; so in procedure pr_execute_code_a, I'd probably use

BEGIN TRANSACTION peca

This only minimizes the chance of a naming conflict, so you still have to be careful, but you get the idea. With named transactions, you're free to wrap stored procedures in other ones at will and still guarantee data integrity; the inner stored procedures can roll back just their own data if that's appropriate, and the outermost procedure has complete control over its own transaction; it can roll everything back or not.

When I am creating an "open" stored-procedure API, I always make sure that the procedure returns some non-zero value (always 1 if I'm not returning specific error codes) in case of an error, in addition to a RAISERROR call and/or a rollback of a transaction. That way calling code (which may be an enclosing procedure) has enough information to roll back a transaction. You know how to get this from an EXECUTE statement, right?

<br />
DECLARE @returnCode         AS INT<br />
<br><br />
-- ...<br />
-- ...<br />
<br><br />
EXEC @returnCode = pr_DoSomething<br />
--<br />
IF (@returnCode <> 0) BEGIN<br />
   -- roll back your transaction, if appropriate<br />
   -- raise an error, if appropriate<br />
   RETURN(1) -- if appropriate<br />
END -- IF<br />

In your case, if you're the one controlling and using the code and the inner procedures are always called by some other, you may not need transactions in your inner procedures at all, only in the outermost one(s).

Regards,

Jeff Varszegi

EEEP!

manasrahfantom30-Apr-04 21:33

manasrahfantom

30-Apr-04 21:33

Hi Jeff ,

Thank you alot of ur reply, it was reallly great

Wish u luck

Thanks Alot

Mahmoud Manasrah

Re: exec two stored procedure in a main stored procedure

Jeff Varszegi1-May-04 6:49

ADO.NET Deletion from SQL DB problem.

1-May-04 6:49

You're welcome! Thanks for the extra luck-- I could use some right now. Have a great day.

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

Member 105947128-Apr-04 19:52

Member 1059471

28-Apr-04 19:52

I am using a dateset.

Heres the problem:
Lets assume theres a table T, with one row in it. I populate my dataset by using Fill().

In my software I now delete the row (from the dataset only).

If I use the Update() with the dataset, the row will be removed from the DB. This is fine.

But what happens if I do this:
1) Call Fill() to populate the dataset.
2) Delete the row from the dataset.
3) Add a new row to the dataset.
4) Delete this new row again.

The problem is that there will now be two rows in the dataset that is flagged to be deleted when Update() is called. But only one of these will be in the database!

If I call Update() now, the system will try to delete both from the DB, but since only one of them is available in the DB to delete, a Concurrency exception will be thrown (since no rows will be affected when the system tries to delete the nonexistant row).

What is the best practice in these kind of situations?

Thanks for your time.

Transferring Data

ibok2328-Apr-04 5:40

ibok23

28-Apr-04 5:40

I am having to come up with factors that may be involved when you are transferring data from one database architecture to another. In other words, if I wanted to transfer to RDBMS from a network architecture what do I need to look for?

How can I find out this information - if I wanted to research? I have been trying to find it, but I must be putting in the wrong keywords for my search.

Thank you,

ibok23

check login exists

Dpriya28-Apr-04 2:50

Dpriya

28-Apr-04 2:50

Hi,
How do I check whether a login exists for a particular database in MSDE?

Code:
SqlConnection objConn = new SqlConnection(strCon);
objConn.Open();
SqlCommand objCmd = new SqlCommand();
objCmd.Connection = objConn;
//eg: if login 'priya' does not exist in database PRIYA how do i do it
objCmd.CommandText = @" EXEC sp_addlogin @loginame ='abb',@passwd ='abb',@defdb = 'ABB' EXEC sp_grantdbaccess N'abb', N'abb' EXEC sp_addrolemember N'db_owner', N'abb'" ;
objCmd.ExecuteNonQuery();

Thankx in advance
Priya

Re: check login exists

Mazdak28-Apr-04 3:47

Mazdak

28-Apr-04 3:47

One thing that come to my mind now is using sp_who which return information about users and if the given name does not exists, it retun 1.

Mazy

"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji

Re: check login exists

Jeff Martin28-Apr-04 5:33

28-Apr-04 5:33

sp_who only shows logged in users, not all existing users

Jeff Martin
Triple20 Software

Re: check login exists

Jeff Martin28-Apr-04 5:32

28-Apr-04 5:32

2 quick ways to find out if a user exists in SQL Server.

1) check the sysusers table:

select count(uid)
from sysusers
where name = 'NameYouAreSearchingFor'

if it returns 0, the user doesn't exist. But since MS doesn't recommend querying the sys tables directly (so they can change in later versions), you should use #2

2) use the sp_helpuser stored proc

exec sp_helpuser 'NameYouAreSearchingFor'

if it returns something, your user exists.

Jeff Martin
Triple20 Software

check database exists

Dpriya27-Apr-04 2:56

Dpriya

27-Apr-04 2:56

Hi,

How do I check whether a database exists before attaching a database ?
Thankx in Advance.
Priya

Jeff Varszegi27-Apr-04 7:51

27-Apr-04 7:51

IF (NOT(EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE [name] = 'Northwind'))) BEGIN
-- attach your database
END -- IF

This information only applies to SQL Server. This is just off the top of my head-- not using databases today. You might poke around throught the INFORMATION_SCHEMA views to see if there's some view containing that stuff too; Microsoft always recommends using the information schema over accessing system tables directly.

Regards,

Jeff Varszegi

EEEP!

Jeff Martin28-Apr-04 5:35

28-Apr-04 5:35

information_schema.Tables

Big Grin | :-D

Jeff Martin
Triple20 Software

Jeff Varszegi28-Apr-04 7:08

28-Apr-04 7:08

Will this allow checking for a database that doesn't have any tables?

Regards,

Jeff Varszegi

EEEP!

Jeff Martin29-Apr-04 9:02

29-Apr-04 9:02

It should. I haven't tested it, but I don't see why it wouldn't work. information_schema is just a view into the system tables.

Jeff Martin
Triple20 Software

Jeff Varszegi29-Apr-04 9:38

Installing ole db provider for microsoft project without installing microsoft project

29-Apr-04 9:38

I understand that, I'm not a newbie. I was asking you to explain why you think that selecting from INFORMATION_SCHEMA.TABLES is a good way to get a list of databases on the server. It only returns a list of tables from the current database.

I'm really not trying to be snotty, honest. I still think I should say that INFORMATION_SCHEMA is really nothing but a database user; it's the views created with that user that are the collective view into the structure of the database.

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

vinay_mat26-Apr-04 20:39

vinay_mat

26-Apr-04 20:39

I was wondering if their is a way to install the Ole DB provider on a machine which does not have microsoft project in not installed.or is there a way to open a .MPP file on a machine which does not have microsoft project installed?
any help in this regard is most welcome.

Vinay

Linked server from SQL Server 2000 to Sybase 12

Janya26-Apr-04 14:30

Janya

26-Apr-04 14:30

How do I use #temptable or @var functionality through openquery? There is a linked server from SQL Server 2000 to Sybase 12 using Sybase.ASEOLEDBProvider.
Executing an openquery statement from MS SQL will retrieve Sybase data; however, I have no advanced functionality available. When I try to use temporary #tables or declare @vars it returns this error...
Server: Msg 7357, Level 16, State 2, Line 1, Could not process object '…OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Sybase.ASEOLEDBProvider', Query=…

Linked Server configuration:
Provider options: AllowInProcess is checked
Server options: Collation Compatible, Data Access, RPC and RPC Out are checked

Does anyone have a suggestion or alternative for this functionality? Thank you very much for your input.

Janya

How to create linked tables using ADOX?

Santanu Lahiri26-Apr-04 12:06

Followup: How to create linked tables using ADOX?

26-Apr-04 12:06

I need to create a series of linked tables in an Access database using ADOX.
I have the ADOX files from C. Antollini's article.
I tried the following code, everything seems to work okay until the actual append call.

// cat is a catalog defined as per ADOX.h
// ConnectString is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False"

CString sTbl = dlgLst.m_sTable;

CString sConnectSrc, sConnectTrg;
sConnectSrc.Format (ID_ADO_CONNECTSTR, m_sDBFile);
sConnectTrg.Format (ID_ADO_CONNECTSTR, sDBFile);
LPCTSTR lpTblName = sTbl;
LPCTSTR lpConnectSrc = sConnectSrc;
LPCTSTR lpConnectTrg = sConnectTrg;

CADOXCatalog cat;
cat.Open (sConnectSrc); // this is the Database in which the table definision is to be created

_TablePtr tbl;
tbl.CreateInstance(__uuidof(Table)); // Create the instance of a table
tbl->PutName (lpTblName); // give it a local name
tbl->put_ParentCatalog(cat.m_pCatalog); // define the parent catalog

// some debugging info, could also use TRACE
AfxMessageBox (sConnectTrg);
AfxMessageBox (sConnectSrc);

tbl->Properties->GetItem(_T("Jet OLEDB:Create Link"))->Value = true; // make it a linked table
tbl->Properties->GetItem(_T("Jet OLEDB:Link Datasource"))->Value = lpConnectTrg;
tbl->Properties->GetItem(_T("Jet OLEDB:Remote Table Name"))->Value = lpTblName;

// Upto this point everything executes. At least, I do not get any complaints

try
{
cat.m_pCatalog->Tables->Append (_variant_t((IDispatch *)tbl));
cat.m_pCatalog->Tables->Refresh();
}
catch(_com_error &e)
{
TCHAR szBuf[300];
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
sprintf(szBuf, "\n\tSource : %s \n\tDescription : %s \n ", (LPCSTR)bstrSource,(LPCSTR)bstrDescription);
CString sErr = CString (szBuf);
AfxMessageBox (sErr);
}

This is causing an error saying "Not a Valid File Name" from the JET engine. The two file names are correct, and the files are both valid databases. Also, the table to be linked exists in the database being linked to.

I am using VC6 and MDAC 2.8.

Any pointers would be most welcome.

Santanu Lahiri29-Apr-04 10:13