|
hi all
i want to create query which gives me
1) all Table name of the selected Database
2) all fields name of selected Table
thanks in advance
The Stifler
--
Bugs can neither be created nor be removed from software by a developer. They can only be converted from one form to another. The total number of bugs in the software always remain constant.
|
|
|
|
|
Assuming you are talking about SQL Server
The Stifler wrote: 1) all Table name of the selected Database
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
The Stifler wrote: 2) all fields name of selected Table
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName'
|
|
|
|
|
 Thanks Colin
The Stifler
--
Bugs can neither be created nor be removed from software by a developer. They can only be converted from one form to another. The total number of bugs in the software always remain constant.
|
|
|
|
|
|
hi thanks again for last solution can u please help me
1) How to get all databases name
3) How to get all databases name from selected server .
Thanks in advance
The Stifler
--
Bugs can neither be created nor be removed from software by a developer. They can only be converted from one form to another. The total number of bugs in the software always remain constant.
|
|
|
|
|
The Stifler wrote: 1) How to get all databases name
See sys.databases[^]
e.g. SELECT * FROM sys.databases
The Stifler wrote: 3) How to get all databases name from selected server .
Question 2 appears to be missing.
You must log on to the server in order to enumerate the databases on it.
|
|
|
|
|
Hi
I have been trying to connect to a sql server database that is on my machine. I am using Visual Studio 2005. Every time I try connect I get an error message that states that the Login was invalid. I am getting this error msg.
System.Data.SqlClient.SqlException was unhandled
Class=14
ErrorCode=-2146232060
LineNumber=0
Message="Login failed for user 'sa'."
Number=18456
Procedure=""
Server="chiko-shumba"
Source=".Net SqlClient Data Provider"
State=1
This is connection string that I am using.
cstring = "Persist Security Info=False;User ID=sa;Password=bobsurunlce;Initial Catalog=ymca_data;Server= chiko-shumba"
I created my database using sql server 2000, and I assigned a usere name and password, when I installed sql server. What could I Have done wrong, and what can I do to get round this?
thanks in advance
twsted F8
|
|
|
|
|
twsted f8 wrote: Connecting to SQL express
twsted f8 wrote: I created my database using sql server 2000, and I assigned a usere name and password, when I installed sql server. What could I Have done wrong, and what can I do to get round this?
Okay. So, you created the database in SQL Server 2000, but have since moved it to SQL Server 2005 Express (the is no express edition for SQL Server 2000).
The sa password you set in SQL Server 2000 won't apply in SQL Server 2005 as the login details are managed by the server not the individual databases. So you have to use what ever credentials were set up when you installed SQL Server 2005 Express Edition.
It's been a while since I tried to install that edition but as I recall it doesn't ask you, it just sets up trusted connections automatically. I may be wrong so you might want to check.
|
|
|
|
|
With SQL express you are going to have to allow access via TCP/IP from the SQL Server Configuration Manager.
|
|
|
|
|
Hi,
I have the foll prob.. I need to create a report which will have an a total at the end of each page followed by a grand total at th end of the report. There are no groupings in the data. i am able to achieve individual totals by placing the sum in the page footer but the prob is that on the last page the page total appears after the grand total... any help appreciated
|
|
|
|
|
Haven't tried this myself, but you should be able to toggle the visibility of the page total textbox based on the result of an expression that checks whether the current page number is equal to the total number of pages in the report. If they are equal, hide the textbox. If they are not, show it.
Paul
|
|
|
|
|
Hi,
Thanks for the reply...i'm able to do it like that the prob is that if i use that method the i need to keep another hidden field within the report so that i can get the total sum for the column...i was wondering if i could avoid that...bcos in case i have to take sum of more fields then the number of hidden fields will increase
|
|
|
|
|
Hi,
I am using MSSQL 2000 server.
I need to access tables in the remote server from my local server query analyser.
I know that server's name, its user name, password....
My need is from my local server query analyzer, i need to perform select or update or delete operation.
is it posiible??
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
Yes. Establish a connection to your remote SQL database, and run your queries.
Paul
|
|
|
|
|
Thank you for your reply.
My requirement is I have a User_log table in my Remote DBserver "RDB" and in my Local DBSERVER "LDB" .And databse name is TEST in both server.
I need to insert (or copy) the content from RDB.TEST.User_log to LDB.TEST.User_log , after copying it i need to DELETE the contetnt of RDB.TEST.User_log
I need to perform this operation from my "LDB"
can you help me??
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
If the databases are on different servers, you could define a linked server on one of your servers and then write an INSERT query that copies the data from one server to the other.
I don't really have any experience of defining a linked server myself, but SQL BOL will explain how to do it.
Alternatively, you could use DTS/SSIS to export data from one database and load it into your other database.
Regards
Paul
|
|
|
|
|
Here is the current problem. EasyLanguage is a language developed for programming under TradeStation, which is designed specifically for professional traders.
I have an indicator that is programmed in EasyLanguage that references an Entitlement DLL file. The variables are listed in the Easy Language code (pasted below) and what happens is that each customer is assigned a unique customerid in TradeStation (EasyLanguage). When they insert the indicator, the indicator calls the entitlement.dll. The DLL goes to the specified website and verifies that the customer is authorized to use the indicator. If yes then the indicator is displayed. If not then an error message is generated that states the user is unauthorized to use the indicator.
f you can recreate the DLL that would be great, otherwise I need to recreate the PHP portion.
EasyLanguage Code:
DefineDLLFunc:
"entitlements.dll",
INT,
"GetEntitlements",
LPSTR,
INT,
LPSTR,
LPSTR,
LPSTR,
LPSTR,
LPSTR,
BOOL;
Variables:
EntitlementName("Test"),
HostIP("207.126.53.83 "),
HostPort(80),
HostPath("www.tradershelpdesk.com/entitlement/BSGTS2.php "),
HostPassword (""),
IsEntitled(0),
pErrorMessage(""),
theErrorMessage("Warning ! You are not entitled to use this tool!" + "For test");
if (currentbar=1) then
begin
IsEntitled = GetEntitlements((LPSTR) HostIP,
(INT) HostPort,
(LPSTR) HostPath,
(LPSTR) HostPassword,
(LPSTR) numtostr(customerid,0),
(LPSTR)EntitlementName,
(LPSTR) pErrorMessage,
(BOOL)True);
end;
If(IsEntitled<=0) Then RaiseRunTimeError(theErrorMessage);
inputs:
Price( Close ),
Length( 14 ),
HiAlert( 0 ), { pass in 10 for +10% }
LoAlert( 0 ), { pass in -10 for -10% }
UpColor( Cyan ),
DnColor( Red ) ;
variables:
PctChg( 0 ) ;
PctChg = 100 * PercentChange( Price, Length ) ;
Plot1( PctChg, "%Chg" ) ;
Plot2( HiAlert, "HiAlert" ) ;
Plot3( LoAlert, "LoAlert" ) ;
{ Color criteria }
if PctChg > HiAlert then
SetPlotColor( 1, UpColor )
else if PctChg < LoAlert then
SetPlotColor( 1, DnColor ) ;
{ Alert criteria }
if PctChg crosses over HiAlert then
Alert( "Bullish alert" )
else if PctChg crosses under LoAlert then
Alert( "Bearish alert" ) ;
CONTACT 9884112963
|
|
|
|
|
And what exactly is the problem here? You want us to write this code for you? I'm sorry, but this isn't RentACoder.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O'Hanlon wrote: I'm sorry, but this isn't RentACoder.
 Seems like sometimes people think that 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
I am developing developing a hostel booking system, and I am trying to come up with a query that allows me to find all the rooms that are availiable on a particular day. I have tried using the except statement but it has not work, and I have also tried using a temporary table, but that has not worked. I have also attempted to select all the rooms that are availiable on a particular day and after that I have conducted a left join on the rooms tables, so as to try identify the rooms that have null values. Does any one out there no how this could be done using SQL 2000 or access
I have included a definition of the database below.
--Stores the database for the hostel--
create database Ymcadatabase
on Primary
(Name = 'ymca_data',
Filename = 'c:\YDbase\ymca_data.mdf',
size = 5mb,
Filegrowth = 10%)
Log ON (Name = 'ymca_log',
FILENAME = 'c:\YDbase\ymca_log.ldf',
SIZE = 5mb,
FILEGROWTH = 10%)
GO
The table definitions
use Ymcadatabase
go
create table rooms
(
roomNumber char(5) Not Null,
block char(50) Not Null,
roomStatus char(10) Null,
rate money Not Null,
Primary Key (roomNumber),
)
create table guestInfo
( idNumber char(15) Not null primary key,
guestsName varchar(25) not null,
guestsurname varchar(25) not null,
gender char(10) not null,
physicaladdress varchar(30),
cellnumber varchar (15) ,
telephone varchar(15),
occupation varchar(15),
workNumber varchar(15),
sponsorsName varchar(25),
sponsorstelephone varchar(15),
)
create table users
(
userName char (15) Not Null Primary Key,
password char(8) Not null,
usertype char (10) Not Null,
)
create table sessions
( sessionNumber int Identity Not null Primary Key,
startdate smalldatetime not null,
enddate smalldatetime not null,
sessionType char(15) not null,
balance money not Null
DEFAULT '0',
roomNumber char(5) not null references Rooms (roomNumber),
idNumber char(15) Not null references guestinfo (idnumber),
)
create table accounts
(
receiptNumber int Identity Not Null primary key,
amountpaid money not Null,
datepaid smalldatetime not null,
roomNumber char(5) not null references rooms(roomNumber),
sessionNumber int Not null references sessions(sessionNumber),
paymentType char(10),
userName char(15) Not Null references users (username),
)
I have also provided some sample data below
use Ymcadatabase
go
insert into rooms(roomNumber,rate,block,roomstatus)
values('d1','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d2','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d3','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d4','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d5','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d6','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d7','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d8','$2000','d','blocked')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d9','$2000','d','op')
insert into rooms(roomNumber,rate,block,roomstatus)
values('d10','$2000','d','blocked')
-- insert new guests--
use Ymcadatabase
go
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('1234','chiko','shumba','male')
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('12ex','tatenda','shumba','female')
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('1gg234','tanaka','shumba','female')
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('12ss34','Roxert','Hive','male')
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('1234ds','Pooch','Adams','male')
insert into guestinfo(idNumber,guestsName,guestsurname,gender)
values('1234dsds','Bangolina','Alberts','male')
--inserts new sessions--
use Ymcadatabase
go
insert into sessions(roomNumber,startdate,enddate,sessiontype,balance,idNumber)
values('d1','9/12/1999','9/17/1999','checkdin','2700','1234dsds')
insert into sessions(roomNumber,startdate,enddate,sessiontype,balance,idNumber)
values('d2','9/2/1999','9/9/1999','booking','1500','1234ds')
insert into sessions(roomNumber,startdate,enddate,sessiontype,balance,idNumber)
values('d3','9/1/1999','9/2/1999','reservation','0','12ss34')
insert into sessions(roomNumber,startdate,enddate,sessiontype,balance,idNumber)
values('d4','9/2/1999','9/6/1999','booking','500','1234')
|
|
|
|
|
select * from rooms
where roomNumber not in (
select roomNumber from sessions
where '01092007' between startdate and enddate)
and roomstatus <> 'blocked'
order by roomNumber
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Good morning.
I converted a report from Access2k to SQL Reporting and one of the sql strings that were part of a control is giving me problems and I cannot seem to run it down. I was hoping someone on here could point out the error to me.
The error message I recieve is:
Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals varchar.
The select statement is:
SELECT tblPatients.*, <br />
tlkpProcedures.ProcedureDescription, tblPatients.PatientNumber, tblPatients.PatientHospitalNumber, tblPatients.LastName, tblPatients.FirstName, tblPatients.MiddleInitial, tblPatients.FamiliarName, tblPatients.AddressLine1, tblPatients.AddressLine2, tblPatients.City, tblPatients.State, tblPatients.Country,tblPatients.ZipCode, tblPatients.HomePhone, tblPatients.Employed, tblPatients.WorkTitle, tblPatients.EmployerName, tblPatients.WorkPhone, tblPatients.WorkPhoneExtension, tblPatients.OKToCallWork, tblPatients.PrimaryPolicyHolder, tblPatients.DateOfBirth, tblPatients.Gender, <br />
tblPatients.RaceCode, tblPatients.SocialSecurityNumber, tblPatients.EducationLevel, tblPatients.HeightEstimateInches, <br />
tblPatients.WeightEstimatePounds, tblPatients.OverweightTimeframeID, tblPatients.MaritalStatus, tblPatients.HaveChildren, <br />
tblPatients.NumberofChildren, tblPatients.DateInfoReceived, tblPatients.CompletedBy, tblPatients.HowHeardAbout, tblPatients.SurgeonReputation, <br />
tblPatients.SurgeonOfChoice, tblPatients.ProgramReputation, tblPatients.ReputationSource, tblPatients.PreferredProcedure, <br />
tblPatients.ReasonSurgeonChoiceChanged, tblPatients.CheckForSurgeonChange, tblPatients.PatientPhysician, tblPatients.PatientPsychologist, <br />
tblPatients.CPapBiPap, tblPatients.CurrentPsychologicalCare, tblPatients.CurrentCareExplanation, tblPatients.DenialDate, tblPatients.DenialID, tblPatients.ReferToDate, tblPatients.ReferToID, tblPatients.Notes, tlkpProcedures.ProcedureDescription, tlkpReferralSources.ReferralSource, tlkpSurgeonChangeReasons.SurgeonChangeReasonDescription, tlkpOverweightTimeframe.OverweightTimeframe AS HowLongOverweight, <br />
tlkpMaritalStatus.StatusName, tlkpOutPhysicians.Title, tlkpOutPhysicians.LastName AS PhysicianLastName, <br />
tlkpOutPhysicians.FirstName AS PhysicianFirstName, tlkpOutPhysicians.MiddleInitialName AS PhysicianMiddleName, <br />
tlkpOutPhysicians.OfficeAddressLine1, tlkpOutPhysicians.OfficeAddressLine2, tlkpOutPhysicians.OfficeCity, tlkpOutPhysicians.OfficeState, <br />
tlkpOutPhysicians.OfficeCountry, tlkpOutPhysicians.OfficeZipPostalCode, tlkpOutPhysicians.OfficePhone, tlkpOutPhysicians.OfficeFax, <br />
ISNULL(tlkpBWLSStaff.FirstName, NULL) & ISNULL(tlkpBWLSStaff.MiddleInitialName, NULL) & ISNULL(tlkpBWLSStaff.LastName, NULL) AS PreferredSurgeon<br />
<br />
FROM <br />
<br />
tlkpOutPhysicians RIGHT OUTER JOIN tblPatients LEFT OUTER JOIN<br />
tlkpMaritalStatus ON tblPatients.MaritalStatus = tlkpMaritalStatus.StatusNumber ON tlkpOutPhysicians.PatientPhysicianNumber = tblPatients.PatientPhysician LEFT OUTER JOIN tlkpOverweightTimeframe ON tblPatients.OverweightTimeframeID =tlkpOverweightTimeframe.OverweightTimeframeID<br />
LEFT OUTER JOIN tlkpProcedures ON tblPatients.PreferredProcedure = tlkpProcedures.ProcedureNumber <br />
LEFT OUTER JOIN tlkpBWLSStaff ON tblPatients.SurgeonOfChoice = tlkpBWLSStaff.BWLSStaffNumber LEFT OUTER JOIN tlkpReferralSources ON tblPatients.ReputationSource = tlkpReferralSources.ReferralSourceNumber LEFT OUTER JOIN tlkpSurgeonChangeReasons ON tblPatients.ReasonSurgeonChoiceChanged = tlkpSurgeonChangeReasons.SurgeonChangeReasonID<br />
ORDER BY tblPatients.LastName, tblPatients.FirstName, tblPatients.MiddleInitial
I apologize for the mess code. I tried formatting it better with no luck.
Before the error above I was getting complaints about the table name tlkpOutPhysicians saying there was a syntax error around there, never could figure that out. The table is there along with the rest.
Thanks, hope someone can shed some light on it.
Steve Welborn
Software Engineer
Inrange Consulting
|
|
|
|
|
Try this in the FROM clause:
FROM
tblPatients
LEFT OUTER JOIN tlkpOutPhysicians
ON tblPatients.PatientPhysician = tlkpOutPhysicians.PatientPhysicianNumber
LEFT OUTER JOIN tlkpMaritalStatus
ON tblPatients.MaritalStatus = tlkpMaritalStatus.StatusNumber
LEFT OUTER JOIN tlkpOverweightTimeframe
ON tblPatients.OverweightTimeframeID = tlkpOverweightTimeframe.OverweightTimeframeID
LEFT OUTER JOIN tlkpProcedures
ON tblPatients.PreferredProcedure = tlkpProcedures.ProcedureNumber
LEFT OUTER JOIN tlkpBWLSStaff
ON tblPatients.SurgeonOfChoice = tlkpBWLSStaff.BWLSStaffNumber
LEFT OUTER JOIN tlkpReferralSources
ON tblPatients.ReputationSource = tlkpReferralSources.ReferralSourceNumber
LEFT OUTER JOIN tlkpSurgeonChangeReasons
ON tblPatients.ReasonSurgeonChoiceChanged = tlkpSurgeonChangeReasons.SurgeonChangeReasonID
P.S. You cannot format with TABS on this site. You must use spaces.
|
|
|
|
|
Thank you Michael for the reply and help. However, it's still complaining about line 1 and giving me the same error: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals varchar.
I did replace my FROM section with what you have above. Thanks.
Steve Welborn
Software Engineer
Inrange Consulting
|
|
|
|
|
Hi Steve
The & characters are worrying me a bit. Which database product are you using? Under SQL-Server, you should be using "+" signs. Under Oracle you would normally use the pipe symbol.
Try replacing the entire list of columns in your select with "*". That will tell you whether the error comes from the column-list or from the from-clause. Once you know this, you should be able to narrow the problem down further.
Regards
Andy
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
