?
Solved

Exists SQL Server

Posted on 2003-12-09
7
Medium Priority
?
431 Views
Last Modified: 2010-07-27
my user can input some like this   SQLDatabaseServer.DataBaseName in a string variable
example
               dbvar :=  SQL-Server.Employee List  
first i need transform dbvar in a correct sql syntax   ----> dbvar := [SQL-Server].[Employee List]

now the big problem is how can i know if [SQL-Server] exists on my lan...

i need execute this
           
doSQL('select name from '+ dbvar+'.dbo.sysobjects where name='+quotedstr('MyTableName'))='MyTableName'

but i have errors like Could not find server 'SQL-Server' or Incorrect syntax near '-' if my dbvar still with SQL-Server

thank you.

0
Comment
Question by:ThoseBug
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Expert Comment

by:Peter_
ID: 9904283
Could be simply that SQL is a reserved word?

According to

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_9oj7.asp

It is, at least in ODBC.

Try naming the server as something else, maybe that will do it?
0
 
LVL 6

Expert Comment

by:bpana
ID: 9923352
hi,

first of all, you have to know all the connection parameters for the SQL Server.
if you know them, you can do:

function checkSQL(aServerName: string; aConnectionString: string): boolean;
var
  conn : TADOConnection;
begin
  Result := False;
  conn := TADOConnection.Create(nil);
  conn.ConnectionString := aConnectionString;
  conn.CommandTimeout := 2;
  try
    try
      conn.Open;
      Result := True;
    except
      ShowMessage('Cannot connect to server: ' + aServerName);
    end;
  finally
    if conn.Connected then
      conn.Close;
  end;
end;

a connectionstring should look like:
'Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=***;User ID=***;Password=***'
where:
Data Source = SQL ServerName
User ID = SQL login
Password = SQL password

Bogdan
0
 

Expert Comment

by:roburobe
ID: 9928470
To check if [SQL-Server] exists on your lan, you can check that if on that server, the SQL Server service is running.

You can use this line of code:
  if serviceRunning('\\'+SQLServer,'mssqlserver') then

putting these 3 little procedures into your source:

//-------------------------------------
// get service status
//
// return status code if successful
// -1 if not
//
// return codes:
//   SERVICE_STOPPED
//   SERVICE_RUNNING
//   SERVICE_PAUSED
//
// following return codes
// are used to indicate that
// the service is in the
// middle of getting to one
// of the above states:
//   SERVICE_START_PENDING
//   SERVICE_STOP_PENDING
//   SERVICE_CONTINUE_PENDING
//   SERVICE_PAUSE_PENDING
//
// sMachine:
//   machine name, ie: \\SERVER
//   empty = local machine
//
// sService
//   service name, ie: Alerter
//
function ServiceGetStatus(
  sMachine,
  sService : string ) : DWord;
var
  //
  // service control
  // manager handle
  schm,
  //
  // service handle
  schs   : SC_Handle;
  //
  // service status
  ss     : TServiceStatus;
  //
  // current service status
  dwStat : Longint;
begin
  dwStat := -1;

  // connect to the service
  // control manager
  schm := OpenSCManager(
    PChar(sMachine),
    Nil,
    SC_MANAGER_CONNECT);

  // if successful...
  if(schm > 0)then
  begin
    // open a handle to
    // the specified service
    schs := OpenService(
      schm,
      PChar(sService),
      // we want to
      // query service status
      SERVICE_QUERY_STATUS);

    // if successful...
    if(schs > 0)then
    begin
      // retrieve the current status
      // of the specified service    
      if(QueryServiceStatus(
           schs,
           ss))then
      begin
        dwStat := ss.dwCurrentState;
      end;

      // close service handle
      CloseServiceHandle(schs);
    end;

    // close service control
    // manager handle
    CloseServiceHandle(schm);
  end;

  Result := dwStat;
end;

//-------------------------------------
// return TRUE if the specified
// service is running, defined by
// the status code SERVICE_RUNNING.
// return FALSE if the service
// is in any other state, including
// any pending states
//
function ServiceRunning(
  sMachine,
  sService : string ) : boolean;
begin
  Result := SERVICE_RUNNING =
    ServiceGetStatus(
      sMachine, sService );
end;


//-------------------------------------
// return TRUE if the specified
// service was stopped, defined by
// the status code SERVICE_STOPPED.
//
function ServiceStopped(
  sMachine,
  sService : string ) : boolean;
begin
  Result := SERVICE_STOPPED =
    ServiceGetStatus(
      sMachine, sService );
end;
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:ThoseBug
ID: 9943640
thank you roburobe
i have error in
 schs   : SC_Handle;
and others variables with a lot of errors...
0
 
LVL 3

Expert Comment

by:Peter_
ID: 9948763
If you are likely to have Enterprise manager (SQL Server tools) installed on the client machine, then you may activate any Enterprise Manager function or menu from within your appliction by using SQLNS and/or SQLDMO.

For a sample on how, see my answer to question below about making an SQL-Server backup:

http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20691981.html

You could use this function to look for a database as an example:

function GetDBNameSpaceObject(
  const aDBName: String): SQLNamespaceObject;
var
  hServer,hDatabases,hDatabase:Integer;
begin
   Result:=nil;
   hServer:=FNameSpace.GetRootItem;
   hDatabases:=FNameSpace.GetFirstChildItem(hServer,SQLNSOBJECTTYPE_DATABASES,'');
   hDatabase:=FNameSpace.GetFirstChildItem(hDatabases,SQLNSOBJECTTYPE_DATABASE,aDBName);
   Result:=FNameSpace.GetSQLNameSpaceObject(hDatabase);
end;

Once imported, these units (SQLNS, SQLDMO) give access to lots of powerful easy to use functions (like browsing all servers in your network).

I don't have sql-server installed where I'm at right now, so I cant provide a full sample for this exact problem. But if you have been using the tools before, then just browse the units and you figure it out. The only downside is that the sql server tools will have to be installed on the client machine or it wont work.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 10025341
PAQed, with points refunded (75)

Computer101
E-E Admin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question