ThoseBug
asked on
Exists SQL Server
my user can input some like this SQLDatabaseServer.DataBase Name 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('MyTableN ame'))='My TableName'
but i have errors like Could not find server 'SQL-Server' or Incorrect syntax near '-' if my dbvar still with SQL-Server
thank you.
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('MyTableN
but i have errors like Could not find server 'SQL-Server' or Incorrect syntax near '-' if my dbvar still with SQL-Server
thank you.
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;Persi st Security Info=True;Data Source=***;User ID=***;Password=***'
where:
Data Source = SQL ServerName
User ID = SQL login
Password = SQL password
Bogdan
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;Persi
where:
Data Source = SQL ServerName
User ID = SQL login
Password = SQL password
Bogdan
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('\\'+SQLSer ver,'mssql server') 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;
You can use this line of code:
if serviceRunning('\\'+SQLSer
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;
ASKER
thank you roburobe
i have error in
schs : SC_Handle;
and others variables with a lot of errors...
i have error in
schs : SC_Handle;
and others variables with a lot of errors...
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:
https://www.experts-exchange.com/questions/20691981/How-to-use-delphi-to-backup-sqlserver-database.html
You could use this function to look for a database as an example:
function GetDBNameSpaceObject(
const aDBName: String): SQLNamespaceObject;
var
hServer,hDatabases,hDataba se:Integer ;
begin
Result:=nil;
hServer:=FNameSpace.GetRoo tItem;
hDatabases:=FNameSpace.Get FirstChild Item(hServ er,SQLNSOB JECTTYPE_D ATABASES,' ');
hDatabase:=FNameSpace.GetF irstChildI tem(hDatab ases,SQLNS OBJECTTYPE _DATABASE, aDBName);
Result:=FNameSpace.GetSQLN ameSpaceOb ject(hData base);
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.
For a sample on how, see my answer to question below about making an SQL-Server backup:
https://www.experts-exchange.com/questions/20691981/How-to-use-delphi-to-backup-sqlserver-database.html
You could use this function to look for a database as an example:
function GetDBNameSpaceObject(
const aDBName: String): SQLNamespaceObject;
var
hServer,hDatabases,hDataba
begin
Result:=nil;
hServer:=FNameSpace.GetRoo
hDatabases:=FNameSpace.Get
hDatabase:=FNameSpace.GetF
Result:=FNameSpace.GetSQLN
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?