Re: Connection String for Connection to OLAP Server - Database Discussion Boards

Colin Angus Mackay13-Nov-04 1:51

13-Nov-04 1:51

For a list of databases on the current server:
select [name] from master.dbo.sysdatabases

For each database you can then do
select [name] from sysobjects where type='U'

You can find documentation for both in MSDN:
* sysdatabases[^]
* sysobjects[^]

Do you want to know more?

Select Top 10 from a DataTable ???

luckyShek12-Nov-04 9:56

luckyShek

12-Nov-04 9:56

Hi! I want to select only 10 rows from a dataTable each time, just as a paging from a database("SELECT TOP 10 FROM orders WHERE ID <100 ORDER BY ID"). for example:

DataTable myTable = new DataTable("orders");
DataRow[] rows = myTable.Select("TOP 10 ID < 100");
1)How can I write the correct statement.
2)Can I ues "TOP" key word or there's other way to do it?

Thank you very much.

Stored Procedures

Anonymous12-Nov-04 6:02

Anonymous

12-Nov-04 6:02

Hi everyone

hope someone can help on a quite simple query. I have a stored procedure that simply returns the row count in a table (code at end of message) and some c# code that says if the row count is greater than 1000 do one thing else do something else (poor pseudocode I know, I apologise).
What I want to do is pass the value from the SP back to the main program. Can it be done?

Hope someone can help,

thanks in advance

Scott

OracleCommand cmdImportCount = new OracleCommand();
cmdImportCount.CommandText = "tv.get_clock_number_import_count";
cmdImportCount.CommandType = CommandType.StoredProcedure;

cmdImportCount.Connection = con;

OracleParameter import_count = new OracleParameter();
import_count = cmdImportCount.Parameters.Add("import_count",OracleType.Number);
import_count.Direction = ParameterDirection.Output;

int import_count_limit = 1000;

int current_total = Convert.ToInt32(cmdImportCount.Parameters["import_count"].Value);
if(current_total < import_count_limit)
{
rest of code
}

PROCEDURE get_clock_number_import_count (import_count OUT NUMBER)
IS
BEGIN
SELECT count(1)
INTO import_count
FROM tv_clock_number_import;
END get_clock_number_import_count;

Re: Stored Procedures

Colin Angus Mackay12-Nov-04 6:41

Colin Angus Mackay

12-Nov-04 6:41

First a disclaimer: I've never used Oracle.

One possiblility is make your query something like
SELECT count(*) FROM ...
and removing the output parameter. ^{[see footnote]}
Then in the .NET Application you can use OracleCommand.ExecuteScalar()[^] method to get the value back.

By the way, to do know that the code you supplied doesn't actually run the query, it just sets it up. So... anyway... you would change the line that start int current_total = to read

int current_total = (int)cmdImportCount.ExecuteScalar();

and remove the references in your code to the parameters.

Does this help?

Footnote: I don't know what the count(1) does in Oracle, so I'm presuming that it does the same thing as count(*) in SQL Server

Do you want to know more?

Re: Stored Procedures

Anonymous14-Nov-04 22:38

Anonymous

14-Nov-04 22:38

Hi,

thanks for replying. Got it working now.

I didnt supply the code that fires the event as its only a click on a menu item, didnt think it was that relevant to why the code wasnt working as hoped. Maybe I should have done and will do in the future.

Thanks for your help, much appreciated Wink | ;)

Scott

How to back up database on remote computer??

venadder12-Nov-04 5:07

venadder

12-Nov-04 5:07

Hi,
I am trying to tell MSSQL Server running on a remote computer on LAN to back up a database on my computer. This is done through a application written in c++.
The Idea is to back up the database on the computer where the server is located as well as on the computer running the app.
What I did before this dual requirement was presented was create a stored procedure to backup the database which took the database name and the location to be backed up to as arguments.
It works well as SQL Server backs it on the local drive specified the path.

No if I send in the network address of my computer to do the same, SQL SERVER error log tells me that operating system denied permission ( which is what will be expected ).
I send in a string as \\computername\driverletter$\path\filename.zip

How can I modify this to include the user name and passwrod so that SQL SERVER can back it up on my computer without security problems???

I have tried net use kind of syntax but it does not help.
Please help!! this is really important.

Thanks In advance.

Connection String for Connection to OLAP Server

mysorian11-Nov-04 16:50

mysorian

11-Nov-04 16:50

This high lighted connection string[ without the .2 after MSOLAP] works in VB 6.0, but not in ADO.NET. any comments?
----------------------------------------------
Exception Details: System.Runtime.InteropServices.COMException: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Source Error:

Line 48: txtSrc = txtSrc + "NON EMPTY [Store].[Store Name] on rows"
Line 49: txtSrc = txtSrc + " from Warehouse"
Line 50: olapCat.ActiveConnection = "Provider=MSOLAP.2;Data Source=xphtek;Initial Catalog=FoodMart 2000;"

Re: Connection String for Connection to OLAP Server

David Salter11-Nov-04 23:47

David Salter

11-Nov-04 23:47

You need to set the ActiveConnection to an ADODB.Connection object rather than to a string.

The following site may help.

http://www.devhood.com/Tutorials/tutorial_details.aspx?tutorial_id=640

Store your favourite bookmarks online: my-faves.co.uk

From a Blob to some type of image in Java

Red Sunday11-Nov-04 9:39

Red Sunday

11-Nov-04 9:39

Ok, to create an easy user interface I have created a Microsoft Access database that will store images in an ole object for a jpeg. Laugh | :laugh:

They can actually drag and drop a jpeg and it will convert to an ole object. Now in actual ODBC Java code, I am getting this same OLE object in the form of a blob Java object. How do I type caste this blob object into some type of image that I can display on the screen in the form of a jpeg? Confused | :confused:

Thank you so much,
redsuday69

-----------------
http://www.zachcalvert.com

Inserting

eggie511-Nov-04 8:45

eggie5

11-Nov-04 8:45

I have the following text...

QUALCOMM's

How can I insert this into my sql database?

When I try to do it now I get an SqlException...

---------------------------
Sql Error
---------------------------
ERROR 
System.Data.SqlClient.SqlException: Line 2: Incorrect syntax near 's'.

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at XmlDataTool.XmlParser.Emit(String sqlString) in d:\my documents\visual studio projects\xmldatatool\xmlparser.cs:line 422
---------------------------
OK   
---------------------------

When I remove all the ' I can insert the text, but they seem to be messing it up. What does my column have to be to accept ''s? Right now it is a nvarchar.... because that seemed like the most flexable as to what it will accept...

/\ |_ E X E GG

Re: Inserting

Colin Angus Mackay11-Nov-04 9:12

Colin Angus Mackay

11-Nov-04 9:12

Well, without you suppling the SQL statement you are using it makes it difficult.

That said, are you using a parameterised query? Or are you injecting values into a SQL String to form the command?

Do you want to know more?

Re: Inserting

eggie511-Nov-04 9:15

eggie5

11-Nov-04 9:15

this is my exact sql string

INSERT INTO DEVELOPER (OBJID,LEGAL,DID,ISV,MONTH_YEAR,GMT) VALUES ('1000','\"The Developer Extract XML to you \"as-is.\" asdf makes no representation or warranty to you that the data will be complete, error free, fit for any particular purpose, or provided on an ongoing basis.\nThis information is fsda's .\"','DEVEXTRACT.Toasdfne Networks.082004.xml','Tourmaline Networks','08/01/2004','09/10/2004 21:12:21');

How do I insert the '?

/\ |_ E X E GG

Re: Inserting

Colin Angus Mackay11-Nov-04 9:27

Colin Angus Mackay

11-Nov-04 9:27

So, your .NET code is something like this:

new SqlCommand("INSERT INTO DEVELOPER (OBJID,LEGAL,DID,ISV,MONTH_YEAR,GMT) VALUES ('1000','\"The Developer Extract XML to you \"as-is.\" asdf makes no representation or warranty to you that the data will be complete, error free, fit for any particular purpose, or provided on an ongoing basis.\nThis information is fsda's .\"','DEVEXTRACT.Toasdfne Networks.082004.xml','Tourmaline Networks','08/01/2004','09/10/2004 21:12:21');");

Have you considered using parameterise queries? They even help prevent SQL Injection Attacks[^]

So, if you rewrite your code as:

SqlCommand cmd;
cmd = new SqlCommand("INSERT INTO DEVELOPER "+
    "(OBJID,LEGAL,DID,ISV,MONTH_YEAR,GMT) "+
    "VALUES (@objId, @legal, @did, @isv, @month_year, @gmt)");
cmd.Parameters.Add("@objId", "1000");
cmd.Parameters.Add("@legal", "\"The Developer Extract XML to you "+
    "\"as-is.\" asdf makes no representation or warranty to you that "+
    "the data will be complete, error free, fit for any particular "+ 
    "purpose, or provided on an ongoing basis.\nThis information is "+ 
    "fsda's .\"");
cmd.Parameters.Add(<small>... Hopefully you get the idea ....</small>);

If you use parameters then you don't have to worry about formatting characters that SQL Server recognises messing up the text you are trying to insert.

Also, could I interest you in the Benefits of Stored Procedures[^]??

Does this help?

Do you want to know more?

Re: Inserting

eggie511-Nov-04 9:34

eggie5

11-Nov-04 9:34

I want to keep it like it was though... I don't want to use "parameterised queries" just yet...

do you know why if I leave my code the same with the ' in it it dosn't work and then when I take the ' out it works fine?

thanks for your time..

/\ |_ E X E GG

Re: Inserting

Colin Angus Mackay11-Nov-04 9:38

Colin Angus Mackay

11-Nov-04 9:38

eggie5 wrote:
do you know why if I leave my code the same with the ' in it it dosn't work and then when I take the ' out it works fine?

Because SQL Server is interpreting the apostrophe as a string delimiter.

eggie5 wrote:
I don't want to use "parameterised queries" just yet...

Out of curiosity, why? Of course it is up to you, but if you don't could you let me know the systems you are working on so I don't entrust my credit card details to them.

Do you want to know more?

Re: Inserting

eggie511-Nov-04 9:42

eggie5

11-Nov-04 9:42

oh yeah... so can I go like this??? \' ? will that work?

I'm just messing around with the sql server... i'm not doing anything important.

/\ |_ E X E GG

Re: Inserting

Colin Angus Mackay11-Nov-04 9:48

Colin Angus Mackay

11-Nov-04 9:48

You would double up the apostrophe. E.g.

INSERT MyTable(MyColumn)
VALUES('It''s a lovely day.');

SELECT * FROM MyTable;

The result is

MyColumn                                         
-----------------------------------------------------------
It's a lovely day.

Do you want to know more?

Browse through list of databases on server

RFickling11-Nov-04 3:58

RFickling

11-Nov-04 3:58

Hi,

I would like to allow users of my ADO.NET application to browse through a list of the available databases on a given SQL server. INFORMATION_SCHEMA in SQL Server Books Online doesn't describe how to do this. How can I do this?

TIA,
Royce

Re: Browse through list of databases on server

Colin Angus Mackay11-Nov-04 4:23

Colin Angus Mackay

11-Nov-04 4:23

select [name] from master.dbo.sysdatabases

See MSDN: sysdatabases[^] - also available in the Books On-line with SQL Server.

Do you want to know more?

ConnectionString for Server

Vector711-Nov-04 1:36

Vector7

11-Nov-04 1:36

I want my client app connecting to an external server with IP 1.1.1.1 for example. The database is called "WORK"

I tried to use URI

Uri url = new Uri("http://1.1.1.1/);<br />
connDB = new SqlConnection();<br />
connDB.ConnectionString="data source="+url.Host+" +<br />
    "initial catalog=WORK;" +<br />
    "user id=me;" +<br />
    "pwd=me;" +<br />
    "packet size=4096;" +<br />
    "persist security info=false;";<br />
connDB.Open();

doesn't work...

Also if I am trying to access the local server db, it fails. Even if data source is (local). How can I access external server's db by IP ?

Re: ConnectionString for Server

Daniel Turini11-Nov-04 1:59

Daniel Turini

11-Nov-04 1:59

Vector7 wrote:
doesn't work...
Could you give more details? What is the error message?

Yes, even I am blogging now!

Re: ConnectionString for Server

Vector711-Nov-04 2:15

Vector7

11-Nov-04 2:15

'SQL Server does not exist or access denied'

If I am right, there only can one instance of SQL Server at the same time ?

Re: ConnectionString for Server

Luis Alonso Ramos21-Nov-04 15:39

Luis Alonso Ramos

21-Nov-04 15:39

Vector7 wrote:
If I am right, there only can one instance of SQL Server at the same time ?

Actually there can be many (I think up to 16, but I'm not sure).

Suppose the server is called SERVER. The default instance is unnamed and thus is accessed with SERVER. The other instances are named (say SQLSRV1) and it's accessed with SERVER\SQLSRV1.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Re: ConnectionString for Server

Mekong River11-Nov-04 14:00

Mekong River

11-Nov-04 14:00

Try to copare your code and the library of connection string.[^]. Learn from it and let me know if it is working or not.

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: ConnectionString for Server

Luis Alonso Ramos21-Nov-04 15:37

Luis Alonso Ramos

21-Nov-04 15:37

I don't know if it may be the case, but try removing that http:// prefix in the URL. That prefix indicates it is HTTP protocol (for web pages) and the server address is only the IP.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

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