Database Discussion Boards

DxSolo17-Jun-03 8:15

17-Jun-03 8:15

ok i work for a company and we use msde for our software that we distribute. This means we need to run creation scripts on every new customer. I know there is a tool called osql. we have the msde database using windows auth. where i run into a problem is when i put this on someones machine and try to run osql it asks me for a password. it does not ask for a user name and you can't type anything in for the password so i hit enter and then it tells me that it is invalid so how do i go about fixing this problem?

Re: Sql/osql help

basementman17-Jun-03 14:24

basementman

17-Jun-03 14:24

Why do things the hard way? Why not just create a database with an empty structure, do a sp_detachdb and ship the MDF file as part of your install... Then, write a program that does a sp_attachsinglefiledb and your DONE.

onwards and upwards...

Re: Sql/osql help

DxSolo18-Jun-03 3:15

DxSolo

18-Jun-03 3:15

i have tried that but cant quite get the syntax right could you provide a little more help and by the way thanks

Re: Sql/osql help

basementman18-Jun-03 4:12

basementman

18-Jun-03 4:12

I assume that you looked up sp_detach_db in the Books Online, so....
Assuming your db name is MyDB:

exec sp_detach_db 'MyDB'

then, simply include the MyDB.mdf file on your install and copy it to your install dir (assumes c:\MyApp\Data)

after the install, call a program that connects to MSDE and issues the following command:

exec sp_attach_single_file_db 'MyDB', 'c:\MyApp\Data\MyDB.mdf'

then you are DONE.

Just make sure that MSDE is installed with the same charset and sort order as you are using in-house.

onwards and upwards...

Re: Sql/osql help

DxSolo18-Jun-03 5:06

DxSolo

18-Jun-03 5:06

thanks a billion man

Re: Sql/osql help

Arjan Einbu18-Jun-03 23:20

Arjan Einbu

18-Jun-03 23:20

Look at the help: osql -?

Examples:
osql -Smyserver -Umyusername -Pmypassword for supplying username and password
or
osql -E to use a trusted connection.

Then you can also specify the scriptfile to be executed by adding the -iscriptfile parameter.

These options are case-sensitive.

Several queries in single ODBC command

jpeg17-Jun-03 4:46

jpeg

17-Jun-03 4:46

With ODBC, I can't write :
'CREATE TABLE table1(field1,field2);CREATE TABLE table2(field1,field2);'
because ODBC doesn't accept the ';'. So I have to execute the first statement and then the second.

Therefore, I'd want to know if there is a possibility to execute a multi-statement line command with ODBC : Is there another separator ?

In fact, I write program that can communicate with several database servers.
I've got a file with all queries I need:
[MySQL]
QUERY1:SELECT * FROM table1
QUERY2:INSERT INTO table1( ....)
.....

[MSSQL]
QUERY1: .....
QUERY2: .....
.....

[ORACLE]
QUERY1: .....
QUERY2: .....
.....

The problem is that I can create a table with 'auto-increment' field with only one SQL statement on MySQL and SqlServer but on Oracle, it needs 3 statements to do it ... and I want to have only one line of SQL code.

If you've got a solution to my problem, please help me !

jpeg

Re: Several queries in single ODBC command

basementman17-Jun-03 5:42

basementman

17-Jun-03 5:42

Yes, you can do that for DBMS's that support query batches. MSSQL does query batches, just leave out the semicolon.

onwards and upwards...

ADO 2.5 Interop - Connection.Open Method

Member 44025716-Jun-03 7:57

Member 440257

16-Jun-03 7:57

I read the article Code Project/C# Programming - ADO Data Access through COM Interop in .NET by Kannan Kalyanaraman and it is great.

The only thing that I have not got is the open method of the connection object when I use the Interop Component in C#. What I am coding is:

cnnCustomer.Open(strConn, "", "", (int)ConnectModeEnum.adModeUnknown);

What is wierd is that in VB.Net it works fine. I am missing something here?

XML and Dataset ??

codycaillet16-Jun-03 7:56

codycaillet

16-Jun-03 7:56

Okay..heres what my goal is. I have 2 tables with a parent-child relationship in an Oracle DB. I want the XML file to look like this. (I want to use XSLT to format the data).

<parents>
<parent>
<col1>
<col2>
<children>
<child1>
<att1>AAA

<child1>
<att1>BBB

...

Is there a way to create this type of XML easily using the DataSet. Here's what I tried. I tried adding both tables to a dataset, and then adding the relationship. Then I called the WriteToXml method of the dataset but it did not keep that format. I also have a XSD file for my dataset that I created with that format but no luck.

Any ideas??

error adding variable using INSERT INTO

Ahmed Galal13-Jun-03 13:51

Ahmed Galal

13-Jun-03 13:51

i use ADO in MFC to access my database using SQL
my problem is that i can't add a variable using INSERT INTO
like,
---------
char mon[50]
pRs->Open("INSERT INTO moshtarayat1(elsanf) VALUES(mon)",m_pConn.GetInterfacePt(),adOpenForwardOnly,adLockOptimistic,adCmdText);
-------------
the problem here is that the INSERT INTO statment inserting the varibale name not it contents !

is there anyway to solve this problem ?

Re: error adding variable using INSERT INTO

Nino_113-Jun-03 17:52

Nino_1

13-Jun-03 17:52

SomeOne_SurVive wrote:
Open("INSERT INTO moshtarayat1(elsanf) VALUES(mon)",m_pConn.GetInterfacePt

Are you using a stored procedure to add this value?

Or, are you using a form field within an ASP.NET, or ASP page?

How are you getting the value of this variable?

If using a stored proc, the variable should be declared as @mon.

Please post more details !!!!

HTH

Tony

Re: error adding variable using INSERT INTO

Ahmed Galal13-Jun-03 18:02

Ahmed Galal

13-Jun-03 18:02

after alot of tries, i got an clue as below :

CString mon;
lCommand.Format("INSERT INTO moshtarayat1(dates) VALUES('%s')",(CString)mon);
pRs.CreateInstance(__uuidof(Recordset));
pRs->Open((variant_t)lCommand,m_pConn.GetInterfacePtr(),adOpenForwardOnly,adLockOptimistic,adCmdText);
pRs->Update();

----------------------------------
i didn't try ur way yet, but i'll.

thanks for response.

Re: error adding variable using INSERT INTO

Arjan Einbu14-Jun-03 15:17

Arjan Einbu

14-Jun-03 15:17

Sure it will...
If I read this C++ code right, you're sending the following string to the DB:

INSERT INTO moshtarayat1(elsanf) VALUES(mon)

Aren't you trying to send the contents of the mon variable into the DB?
Copy the value of mon into the SQL string, so that it would look like:

INSERT INTO moshtarayat1(elsanf) VALUES(value_of_mon)

where value_of_mon represents the value of

mon<code>.<br />
<br />
(I'm not sure how you combine strings in C++, so no actual sample code...)

Re: error adding variable using INSERT INTO

Ahmed Galal14-Jun-03 19:25

Ahmed Galal

14-Jun-03 19:25

yea, thats right if i'm adding the contents from the code by myself.
but what about if the user is the one who gonna enters it ?
like i copy the contents from and editbox or somthin.

thanks for response

unable to detect primary key

Itanium13-Jun-03 6:02

Itanium

13-Jun-03 6:02

Hi All!!
I m making a generalized class which can handle all type of databases
like Access , SQLServer etc.
So i m using static interaces to declare DataConnection and DataAdapter.
Problem is that i can't get the PrimarKey of DataTable.
PrimaryKey.Length always returns '0'.
any suggestions....

Thanx in advance

sorry for my bad English.

Retrieving sql-queries from a database

Anders Sandberg13-Jun-03 4:26

Anders Sandberg

13-Jun-03 4:26

Hi!

I'm writing an app in VC++ 6 that uses ADO through the #import directive.

There are an unknown number of predefined queries in the database (access)
witch I need to execute and I wonder if there's a way of retrieving the
names of those queries using ADO.

I'm using a Connection Object and a RecordSet Object to obtain the results
of the queries but how can I obtain the queries?

thanks
/Anders

Re: Retrieving sql-queries from a database

basementman13-Jun-03 5:12

basementman

13-Jun-03 5:12

See the replies in the VC forum

<marquee scrollamount="3" scrolldelay="80" direction="right" width="40%" style="border: 2px inset silver;background-color:yellow;color:green;font-family:Verdana,Arial;font-size:8pt;font-style:italic">onwards and upwards...

Query a dataset

Mike Osbahr12-Jun-03 10:48

Mike Osbahr

12-Jun-03 10:48

I have a table that's being populated by a stored procedure. Once the table is populated I need to know the distinct values in some of its columns. I can use foreach loops, but this seems like it would be slow. Is there any way to query a table in a dataset and place the results into another table or an array?

>>>-----> MikeO

Re: Query a dataset

Rocky Moore16-Jun-03 0:58

Rocky Moore

16-Jun-03 0:58

Don't know of any other method. Microsoft does have ADO.NET HELP extention class that is interesting.

http://support.microsoft.com/search/default.aspx?Catalog=LCID%3D1033%26CDID%3DEN-US-KB%26PRODLISTSRC%3DON&Product=&KeywordType=any&Titles=false&numDays=&maxResults=25&withinResults=&Queryl=datasethelper&Query=datasethelper&QuerySource=gsfxSearch_Query[^]

Rocky Moore <><

Sql Server registeration error

shalender10-Jun-03 19:21

shalender

10-Jun-03 19:21

Hello All,

I am stuck with sql server my friends. The situation is i am installing sql server 7.0 desktop edition on windows 98 on laptop.i installing the sql server it hanged in between the process.After that i am not able to register my server.

Please any body can help me in this situation.

Thanx & Regards,
Shalender Gandhi.

Help with SQL Server permissions

Tony Truong10-Jun-03 7:37

Tony Truong

10-Jun-03 7:37

We're running mixed mode authentication on our sql server. The SQL server grants access to everyone on the domain. What I'd like to do is restrict table drop access on all accounts except sa. Is there a way to do this? If not, is there a way to restrict table drop access on certain tables to so that no one can ever drop these tables?

Re: Help with SQL Server permissions

Nino_110-Jun-03 17:38

Nino_1

10-Jun-03 17:38

USE DATABASENAME<br />
<br />
GRANT SELECT, UPDATE ON TABLENAME<br />
<br />
TO [DOMAINNAME\useraccount]<br />
<br />
GO<br />
<br />
USE DATABASENAME<br />
<br />
DENY DROP TABLE ON TABLENAME<br />
<br />
TO [DOMAINNAME\useraccount]<br />
<br />
GO

Tony

Re: Help with SQL Server permissions

Tony Truong11-Jun-03 8:40

Tony Truong

11-Jun-03 8:40

I tried the 2nd statement, but get the following error:

Privilege DROP <object> may not be granted or revoked.

I then tried the following command to allow create access which should implicitly deny drop access, however I can't seem to modify the views/tables in design mode because it doesn't have the privledges:

USE DATABASENAME
GRANT CREATE FUNCTION, CREATE PROCEDURE, CREATE VIEW
TO [DOMAINNAME\useraccount]
GO

Anyone have any other ideas/workarounds to deny drop table access for all users except sa (but still allow modify and create access)

Re: Help with SQL Server permissions

Nino_112-Jun-03 13:24

Nino_1

12-Jun-03 13:24

T,
I would then look into the ALTER TABLE T-SQL statement.

I have used this many times on permissions and views and it has saved my a.. more than once.

Sorry couldn't be more helpful !

Tony

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.