Hi to All,
I want to know a query by which i can get the whole structure of my DataBase -
eg. it should give me all table names and structure which exists in this particular database .....
Thanks
I have a query that must return information for an invoice. It is not straight forward though. Some things need to be displayed from the if something else is happening...
For example...declare @id int
declare @chargeid int
set @id = 1
set @chargeid = 9
declare @pp bit, @pages int, @pagefee money, @pagecharge money
set @pp = (select perpage from tblmainfees where mainfeesid = @chargeid)
if (@pp = 1)
begin
set @pages = (select pages from tblcharges inner join tblrequest on requestid = fkrequestid where requestid = @id)
if (@pages > 0)
begin
set @pagefee = (select perpagefee from tblmainfees where mainfeesid = @chargeid)
set @pagecharge = (@pages * @pagefee)
end
end
...
select @pagecharge as PerPageCharge
Or should I do some complex query with only one select?
1 SELECT
2 RowNumber,
3 'Source.Dbf, Plan.Dbf',
4 'Source Name is missing for Source Number "' + IsNull(RTrim(f.SOURCE_NUM),'Unknown') + '" in Plan.Dbf table.'
5 FROM
6 SourceDbf f
7 JOIN
8 (
9 SELECT DISTINCT
10 SOURCE_NUM,
11 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME
12 WHEN 2 Then SRC2NAME
13 WHEN 3 THEN SRC3NAME
14 WHEN 4 THEN SRC4NAME
15 WHEN 5 THEN SRC5NAME
16 WHEN 6 THEN SRC6NAME
17 WHEN 7 THEN SRC7NAME
18 WHEN 8 THEN SRC8NAME
19 WHEN 9 THEN SRC9NAME
20 WHEN 10 THEN SRC10NAME
21 WHEN 11 THEN SRC11NAME
22 WHEN 12 THEN SRC12NAME
23 WHEN 13 THEN SRC13NAME
24 WHEN 14 THEN SRC14NAME
25 WHEN 15 THEN SRC15NAME
26 END
27 FROM
28 PlanDBF p
29 Where
30 p.PLAN_NUM = s.PLAN_NUM
31 ) as SourceName
32 FROM
33 SourceDBF s ) c on f.PLAN_NUM = c.PLAN_NUM
i am getting an error on Line 33 and this what the error says...
Msg 207, Level 16, State 1, Line 33Invalid column name 'PLAN_NUM'.
Any help will appreciated..
Regards
Karen
I have tried running this query multiple times with no success I get the following errorIncorrect syntax near '('.I tried with quotes and without quotes around the 10 and also without the brackets around variable. It runs when an integer in entered in the variables place but that is not what I want. What am I doing wrong DECLARE @p AS intSET @p='10'SELECT TOP (@p)* FROM my_tbl order by newid()
View 3 Replies View RelatedstrCommand = "SELECT * FROM tblevents WHERE startingDate=#"&startDate &"# AND eventtitle like '%"&criteria &"%' ORDER BY " &sSortSt I want to find any records that match a certain keyword on a specific date... But nothing comes up even though there is an event matching that criteria on the given date. Do I need brackets around my query or something?
View 6 Replies View RelatedI am trying to sum a column together. This is what I am doing:
SELECT
sum(theoreticalTime= (Select
sum(rcp.theoreticalTime)
From
Traveler_X3 trv Join Recipe_X3 rcp On (trv.RecipeID = rcp.RecipeID)
Where
trv.Oper = rtsA.oper And
trv.RouteGroupID = (
Select
rgp.routeGroupID
From
Route_Group_X3 rgp
Where
rgp.routeGroupName = ( Select
rtl.RouteGroup
From
RouteList_X3 rtl
WHERE
rtl.route = rtsA.route
)
)
))
FROM
Routes_X3 rtsA
where
rtsA.route=( SELECT
lhm.route
FROM
brettb.pdash2.dbo.lothistorymoves lhm,
x3oprs x3o
WHERE
lhm.lot = 'S6D0IQ002A' AND
lhm.oper = x3o.oper AND
lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o WHERE lhm.lot = 'S6D0IQ002A' AND lhm.oper = x3o.oper)
)
and rtsA.seq BETWEEN ( SELECT
seq
FROM
Routes_X3
WHERE
oper =( SELECT
lhm.oper
FROM
brettb.pdash2.dbo.lothistorymoves lhm
WHERE
lhm.lot ='S6D0IQ002A' AND
lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm WHERE lhm.lot = 'S6D0IQ002A')
)
and route=( select top 1
lhm.route
FROM
brettb.pdash2.dbo.lothistorymoves lhm,
x3oprs x3o
WHERE
lhm.lot = 'S6D0IQ002A' AND
lhm.oper = x3o.oper AND
lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o WHERE lhm.lot ='S6D0IQ002A' AND lhm.oper = x3o.oper)
)
)+1 and 219;
What i got working was to put the query in a temp table called #temp1 and then run this:
select sum(theoreticalTime) from #temp1;
but isnt there a way to do this in one query?
Thank you for any help
DECLARE @Filter nvarchar(2048)
DECLARE @EventName nvarchar(2048)
DECLARE @Ticker nvarchar(2048)
DECLARE @Cusip nvarchar(2048)
SET @EventName = 'DIVIDEND'
SET @Ticker = 'IBM'
SET @Cusip = ''
SET @Filter = 'Name like ''' + @EventName + '%'' AND Ticker Like ''' + @Ticker + '%'' AND Cusip like ''' + @Cusip + '%'''
Select * from eventdatadetails
where @Filter
I want to execute the above SELECT statement and the filters in the where clause should come from the @Fitler variable... but query analyzer gives an error. If i replace the data in the @Filter with the variable in the query, everything works fine ...
UPDATE CART SET TOTAL_COUNT = TOTAL_COUNT + 1, SET TOTAL_COUNT1 = TOTAL_COUNT1 + 1, SET DATE1 = 10/30/2006 6:17:02 PM, SET PERSON = luke WHERE CART_ID = 1
I have wacked my head over this query I dont see anything wrong with it
any ideas?
Hello all.
Ive got the following SQL query and i want to return only distinct [ReviewID]'s, however SQL Server is complaining about it. This is the query:
SELECT DISTINCT Top 5 [ReviewID],[ReviewType],[ReviewTypeName],[LoginID],[LoginForename],[LoginSurname],[Approved],[ReviewDate]
,[Stars],[RelatedProductID],[Title],[Copy],[Rating], [Image1], [Image1Width], [Image1Height], [Image1AltText], [Image2], [Image2Width], [Image2Height], [Image2AltText],
[CategoryL4]
FROM [feManagedReview]
This is whats its complaining about:
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Any ideas where im going wrong?
Hi Guys,
I have create a sales delivery printout with the following example information
example below gives a delivery and the two sales orders that delievry is related to.
Two tables are T1 = delivery, T2 = orders
Delivery (T1)
linenum Item delievry Qty Ordernum orderlinenum DeliveryNumber
1 A 10 600 1 5001
2 B 5 600 2 5001
3 c 12 605 1 5001
4 P 1 5001
ORDER (T2) number 600
linemum Item OrderQty orderno
1 A 20 600
2 B 10 600
3 A 5 600
ORDER (T2) number 605
linemum Item OrderQty orderno
1 C 20 605
2 B 20 605
3 F 5 605
I want to print a delivery note with the all items on that delivery (delivered items) plus other items on orders which ware not delivered. so the result of the query should look like below
Result
NO Item delievry Qty Ordernum orderlinenum ORDER QTY
1 A 10 600 1 20
2 B 5 600 2 10
3 c 12 605 1 20
4 P 1
5 A 600 3 5
6 B 605 2 20
7 F 605 3 5
You can ignore the first column of results which is just the row number.
other conditions
1. Some times the delivery is not based on any sales order
2. Delivery can base on 0,1,2,3 or any number of sales orders.
3. result should provide all rows from the delivery and all rows from sales orders except the rows in the delivery.
can any one help?
Cheers
Sonny
I have a sql 2005 database on a server and I want to have a copy of that database on my workstation (running sql 2005 developer). What is the easiest way to do this ? We have DBGhost also.
View 20 Replies View RelatedHi, I'm new to c# and sql2005 express. I'm getting an error on the simplest of activities. From C#, i create a database, create a table, then try to delete the database and get a database busy error. I think its because sql has some connections to the database its not releasing. I have already tried closing the connection, disposing of the object and forcing garbage collection for the c# app to no avail.
can someone explain to me wat is going on?
Here is the only function i am calling for c#. I pass in T-SQL command strings such as 'Ceate Database myDB','Create Table myTbl','Drop Database myDB', etc.. :
private bool ExecuteSQLNonQry(string sqlStr)
{
SqlConnection myConn = new SqlConnection(ConnectionString);
SqlCommand SqlCmd = new SqlCommand(sqlStr, myConn);
try
{
myConn.Open();
SqlCmd.ExecuteNonQuery();
myConn.Close();
return true;
}
catch(Exception e)
{
MessageBox.Show(e.Message.ToString());
return false;
}
}
hi @ll...
Plz Plz help me out.!!!!!!!!
my query is that in my database i've a table say Tb1 and another table called Tb2.
in TB1 there is a field called companyID (the data is for e.g "001")
similarly in TB2 there is a filed called productID (the data is for example "01")
now how can i create a field in another table say TB3 with a field called UserID and the data should be like the companyID from TB1 + productID from Tb2. The data should come like this for example " 001-01 ".
Hi Guys,
What is the equivalent of Describe table of Oracle in SQL server.
I want to see the table fields for a table in SQL server any command?????
TIA
i m working on this query that....." Using Oracle command desc table_name to display the charecterstics of the collection table."
View 4 Replies View RelatedThe title pretty much says it all. How do I get a list of columns from a specified table?
View 1 Replies View RelatedI am more familiar with writing SQL for Oracle than MS SQL Server. Within Oracle there is a simple command, 'Describe', which actually shows the data types, and whether or not an attribute accepts NULLS.
MS SQL Server does appear to support such a command, however I am looking for a way to describe the attributes of tables nonetheless.
In ORACLE, I can use DESCRIBE PS_JOB to see the columns for that record. What is the SQL-Server equivalent?
TIA,
Joe
HELP
Dmitri
Oracle has a similar function, DESC, to diplay the table structure. Can't figure it out in SQL Server.
ddave
Hi,
I have been training in SQL on MySQL, and now that I am on TSQL, I can't seem to find an equivalent to the command:
Code Snippet
DESC table_name;--OR
DESCRIBE table_name;
In MySQL, either command returns a list of the columns of the table, as well specifications about these columns (whether or not they can accept NULL values, their default values, etc.)
In my SSIS package I am trying to connect to a database in Oracle 10.2.0.3 using an Oracle Provider for OLE DB.
I get the following error:
at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)
at System.Data.ProviderBase.DbBuffer.ReadDateTime(Int32 offset)
at System.Data.OleDb.ColumnBinding.Value_DBTIMESTAMP()
at System.Data.OleDb.ColumnBinding.Value()
at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbMetaDataFactory.PrepareCollection(String collectionName, String[] restrictions, DbConnection connection)
at System.Data.ProviderBase.DbMetaDataFactory.GetSchema(DbConnection connection, String collectionName, String[] restrictions)
at System.Data.ProviderBase.DbConnectionInternal.GetSchema(DbConnectionFactory factory, DbConnectionPoolGroup poolGroup, DbConnection outerConnection, String collectionName, String[] restrictions)
at System.Data.OleDb.OleDbConnection.GetSchema(String collectionName, String[] restrictionValues)
at Microsoft.DataWarehouse.Design.OleDbSchema.GetSchema(String collectionName, String[] restrictionValues)
at Microsoft.DataWarehouse.Design.DataSourceConnection.GetSchemaTable(DataTable& dataTable, String collectionName, String[] restrictions)
at Microsoft.DataWarehouse.Design.DataSourceConnection.ConvertGetSchema(DataTable& dataTable, Guid schemaEnum, Object[] restrictions)
at Microsoft.DataWarehouse.Design.DataSourceConnection.GetSchemaTable(DataTable& dataTable, Guid schemaEnum, Object[] restrictions)
at Microsoft.DataTransformationServices.Design.ComboBoxWithTables.GetTableInfos(DataSourceConnection dataSourceConnection, String connectionName)
at Microsoft.DataTransformationServices.Design.ComboBoxWithTables.FillTableOrViewComboBox()
Why this is not working for 10.2?
The same package works fine for Oracle 8.1.7
THANKS FOR YOUR HELP!
<usualDisclaimer>Please forgive me if this is in the wrong group, and if so,what is the right group.</usualDisclaimer>Let me start off by first saying im a newb. Ok, with that out of the way Iam trying really hard and boy have I learned a lot in the last little whilebut I have a question i just can't seem to find a good answer to.Lets say i have a table that simply stores how many times someone has loggedinto a webpage. Is it better to store each login as a new record then countthe records or is it better to simply have one record that updates the totalvalue by incrementing that one field. I have read all manner of articlesand some say one way is better vs. the other but what I can't find is why?If I knew why one way was better than another then I could make and educateddecision and choose the best way that is right for me. Is updating more orless expensive then inserting? Does it matter and is a relevant question?And before anyone comments on my use of all uppercase letters for my tablename I do this so that my table names stand out within the sql serverenterprise manager. In other words system tables are lowercase and mytables are uppercase. People always seem to give me crap for this but neverback it up with a good explanation so far as I can tell its personalpreference? Am I wrong?CREATE TABLE USER_METRICS(-- here i can select all the records and count them up.ID INT IDENTITY(1,1) PRIMARY KEY,Email VARCHAR(250));-- Or this?CREATE TABLE USER_METRICS(-- and of course here can i simply retrieve the value of Total_LoginsID INT IDENTITY(1,1) PRIMARY KEY,Email VARCHAR(250) NOT NULL,Total_Logins INT);Regards,Muhd
View 3 Replies View RelatedHello DBA's
i use this proc to insert & update the records in the databse
update works good untill i change the user id.
userid can be changed where adminuserid is the IDENTITY coulmn in the table.ALTER PROCEDURE [dbo].[spinsertusers]
-- Add the parameters for the stored procedure here
@adminuserid varchar(36),
@userid varchar(15),
@fname varchar(25),
@mname varchar(25),
@lname varchar(25),
@password varchar(15),
@address1 varchar(255),
@address2 varchar(255),
@postcode varchar(15),
@cityidentity varchar(36),
@dob smalldatetime ,
@email varchar(50),
@crtduser varchar(36),
@crtdon datetime,
@isactive char(4),
@mode char(10),
@reccount INT output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Count INT
-- Insert statements for procedure here
IF (@mode='insert')
SET @Count=(SELECT COUNT(*) FROM adminusermaster WHERE userid=@userid)
IF @count=0
--Inserting the Records
BEGIN
INSERT INTO [school].[dbo].[AdminUserMaster]
([AdminUserIdentity]
,[FirstName]
,[MiddleName]
,[LastName]
,[UserID]
,[Password]
,[Address1]
,[Address2]
,[PostCode]
,[CityIdentity]
,[DOB]
,
,[CreatedBy]
,[CreatedOn]
,[IsActive])
VALUES
(
@adminuserid ,
@fname ,
@mname ,
@lname ,
@userid ,
@password,
@address1,
@address2,
@postcode,
@cityidentity ,
@dob,
@email,
@crtduser,
@crtdon ,
@isactive
)
SET @reccount=2
return @reccount
END
-- End of Query
IF (@mode='update')
SET @Count=(SELECT COUNT(*) FROM adminusermaster WHERE userid=@userid)
IF @Count=1
--Query for Update the Records
BEGIN
update AdminUserMaster
set
[FirstName]=@fname ,
[MiddleName] =@mname,
[LastName]=@lname,
[userid]=@userid ,
[Address1]=@address1,
[Address2]=@address2,
[PostCode]=@postcode,
[CityIdentity]=@cityidentity,
[DOB]=@dob,
=@email,
ModifiedBy=@crtduser ,
ModifiedOn=@crtdon ,
isactive='Y'
where
AdminUserIdentity= @adminuserid
set @reccount=3
return @reccount
END
-- End of Query
END
]First of all here is how i created my SQL database and established a connection in visual studio 05. I go into the server explorer and right click on the data connections node and and select "create new SQL database". A window then prompts me for a server name and data base name. Under server name I write "localhostSQLEXPRESS" and under data base name I put in whatever. This totally worked and the database was asigned the extension of .DBO
I can now insert, select and delete information with some simple ADO coding. The only problem is that using this method makes it seamingly impossible to FTP my database onto my host server. I am aware that I can create a data base in the APP folder by simply right clicking it and selecting " new database".
This would simplify the deploying process but I cant seem to establish a connection this way. Ive also noticed that when I use this method that it saves it as a .MDF instead of a .DBO. What is the difference? Why does my .DBO work but not my .MDF in my app folder? Im so confused over this issue that I am problably not even asking the right question. Bottom line is that I have a small web application with a .dbo database that works wonderfully in my isolated visual studio environment and I need a solution to deploy it on the world wide web. Please help with any information I would be very thankfull.
Between having SQL and using SQL server enterprise manager with MSDE
View 1 Replies View RelatedI am not an expert with sp. I am trying to pass the column name as a parameter but I get the following error "Must declare the variable '@strColumnName'."
ALTER PROCEDURE dbo.TempGetDataForDropDown
(
@strColumnNamenvarchar (50)
)
as
EXEC ('SELECT DISTINCT @strColumnName = ' + @strColumnName + ' FROM Customers')
SELECT 'http=' replace (prot,'ww','+')',+'replace(city,'u','+')',+'proj_s tate'+'proj_zip5
INTO :ls_MAP FROM project4 WHERE proj_id=:gv_proj_id;
Hi all,
I've recently created a shopping cart system that uses SQL Server on the backend. The shopping cart will add a record to a table called CartEvents each time anything happens, like view item, update, delete etc. anyhow, the site has been up for about a week now and that particular table has about 4200 records already, I project tens of thousands of records in that table, so how many records is too many records? Whats your experience with tables that have hundreds of thousands of records? How many records can SQL server handle and still perform well?
Thanks
What is ALL,SOME|ANY ? Whats the usage?
Is they are comparison operators?
If not then what kind?
If SOME and ANY are same then where to use SOME and where to use ANY?
Im using windows xp sp2 and MS Sql server 2005 enterprise edition may b and im also havin sql server express
previously when i installed sql server express it took server name on its own and i used to run queries using AdventureWorks database..
Recently i formated my desktop and again installed both the versions im having but neither is taking server name.. and i dint noticed what was the server name before.. im a student home user i just hav to query on adventurework databases on the same computer i cant afford to buy any servers..
so can anyone tell me how can i configure sql so that i can query !
DECLARE @DBName varchar(100);
SET @DBName = 'TestTest';
CREATE DATABASE @DBName ON PRIMARY;
message is: wrong syntax near '@DBName'.
I've been writing a few sql queries over the past few days, and would like to know what the difference is between:
website is null
website like ''
Becasue if they mean different things i want to know how to look them both up at the same time. Obv im after data that is blank or has nothing written in it.