Hello I am writing a stored procedure and instead of "hard coding" the size of the procedure parameters I would like to set them to the size of fields in my database.
for example instead of declaring a parameter like this:
@ProjectDescription varchar(500)
I would like to do something like this:
@ProjectDescription varchar(Select size of "ProjectDescription" From Projects);
Thanks for the help!
I just want to know if the searched for item appears in either Field in my table. The search works when i just use 1 Field, but i always get the below error whenever i add the second field to the query..
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'TERM Glossary.LIKE '%c%' Visible= True'.
I looked around and found some info on using UNION ALL but i get the same error. So, both the below queries give me an error.
PHP Code:
SELECT * FROM Glossary
WHERE AKNOWAS LIKE '%c%' OR
TERM LIKE '%c%' Visible= True ORDER BY TERM ASC
PHP Code:
SELECT * FROM Glossary WHERE
Glossary.AKNOWAS LIKE '%c%'
UNION ALL
SELECT * FROM Glossary
WHERE TERM LIKE '%c%' Visible= True ORDER BY TERM ASC
I am working on a project in which a customer wants to be able to list and search their inventory and display the items in a table/grid on a web page.Each item in their inventory has a set of properties - for example, manufacturer, price, serial number, name, etc. The complicated part is that they want an admin to be able to modify/add/delete the set of properties. So for example, they could add the attribute "size." Given that, I think what is needed is 3 tables - one to store the set of properties schema, one for the items, and one to store the actual properties for each item. I know i COULD use alter table statements to add and delete columns, but that doesn't seem like the "right" solution.I would like to be able to write a query such that each row returns the item and all its properties - then i can easily bind to a datagrid. However, what would the query be to do this? I also need to be able to allow the customer to query for items with certain properties - i imagine the sql for that to be similar to this:SELECT * FROM items d WHERE d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "Manufacturer" AND a.Value = "Samsung") AND d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "SerialNumber" AND a.Value = "3223")
View 1 Replies View RelatedHello:
I have designed a CV database with complete CV stored in a TEXT field. There is a keyword search which queries the TEXT field also. The query conditions are defined in T-SQL submitted through an ASP page. There is about 20,000 records now. Now while querying the database for keyword search I am receiving time out errors. Is there any solution other than Index server to rectify this situation. How can I speed up the query execution time. Please advise.
Rgds
pooja
It is possible to find table size and in that table each row size.
View 4 Replies View RelatedHow can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
I am stuck in the following scenario.
Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01
[code]....
I had several databases under sqlserver ce 2.0 (in my Pocket PC) which contained ntext fields. The size of the databases varies from 50,000 to 700,000 records. The size of an ntext field is from 4 bytes to 2 megabytes.
When I recreated my databases under sql server 2005 mobile on my desktop using VS2005 (see my post just under this one), I saw a big difference between the old and new database sizes. This problem was mentioned in one of the posts in this forum and the reply was to replace ntext data with ncharvar type.
Since most of my data was longer than 4000 bytes (which is the limit for nvarchar type), I couldn't use this suggestion. Instead, I changed my ntext type to image type and used a GetByte conversion.
No change! The size of the new database is still 50 % larger than the original. Since the difference is around 300 MB, this is an unacceptable thing.
Now, I either wait from somebody to suggest a new solution (apart from keeping the ntext data in a separate binary file and keep index of the records of this file in the records of sql database) or, most preferably, have
Microsoft solve this problem as soon as possible.
Thanks for any help in advance
Talat
Hello all,
I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.
what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.
does anyone have an idea?
thanks in advance.
Hi All
Thx in advanced for any help anyone can offer me.
I have 2 tables Staff and Class which are as follows
Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .
Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID
LessonTemplateID and TermID link to other tables which i don't need right now.
The Class.TeacherID and Class.AssistantID both link to the Staff.ID
I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.
If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.
Im really stuck on this and just can't figure it out (is it even possible?).
Thx for any help Scott.
Hi all, I am currently moving from access queries to SQL and am using the query Builder In Visual web developerto help me with the SQL Code..This has been easy while just querying the one table adapter but Now I am stuck..I have a table adapter that links to an SQL Server database through a connection in web.config.I have another table adapter that links to DB2 through a different Connetion in web.config.My question is ..How do you query from one table adapter to another.Each table adpater can only query based on the 1 connection ? Thx,Ray..
View 1 Replies View RelatedWe have been asked to keep a log of users querying a particular table.
User's can query any odbc compliant tool, not just one application.
Can we do this w/ sql server 7?
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Hello, everyone:
I have a table like:
ColName
b
b
b
d
d
d
a
a
c
c
c
I use DISTINCT to filter duplicated row. I want to get the return by original order like:
b
d
a
c
However, SQL Server re-order it if using DISTINCT and return like:
a
b
c
d
Can any one have the idea to handle that? Thanks
ZYT
Would appreciate some help with this one. I need to run a query on a known table an arbitrary database. The DB name will be selected at runtime.
What I've tried:
DECLARE @DBName varchar(10)
SET @DBName = 'WBTST'
SELECT TOP 100 PERCENT Col1, Col2
FROM [@DBName].dbo.Table1
GROUP BY Col1
Result:
Invalid object name '@DBName.dbo.UPR00100'.
I've also tried without the square brackets around @DBName in the FROM clause. This results in: Incorrect syntax near '.'.
Thanks for the help
Wes
I am working on an application with a number of tables (which I can't
change) that have data of the form a1, b1, c1... a2, b2, c2...a3, b3,
c3... The tables contain 5 to 10 sets of data in each row.
I want to create a stored procedure to return a cursor with a row for
each set of data for a row of the table. I know I can use unions:
select a1 as a, b1 as b , c1 as c... from sometable where pk=@pk
union
select a2, b2, c2... from sometable where pk=@pk
union
select a3...
Is there a way that is more efficient (eliminates requerying the table and all the unions)?
Hi,
i use this script that show me the size of each table and do the sum of all the table size.
SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
THX
I need help with a query, and I haven't seen anything like this in a SQL book.
I will use the Northwind database for this example. The tables I am using are Products, Order_Detail, and Orders. There is a many to many relationship between Products and Orders, that is resolved by the Order_Detail table. Suppose I want to find out which products are related to each order. How would I query the tables?
Thanks everyone,
Nathan
Folks,While I still have some hair left, can someone help me with thisquery?I have a table "TestRunInfo". Amongst other fields there are"TestRunIndex" (Pri Key), "TesterID", "Duration", and "Status".The Status field links to a Status table, which links the index valueto a more meaningful label "Pass", "Fail" etc...As you may have guessed, there is a record for each test that anindividual tester runs, and with that record is a duration, and status(1,2,3 etc).What Im trying to do, is create a datasheet view, with a single rowfor each testerID, summarising that Testers work as follows:TesterID, Total Duration, Count of passed tests, Count of failed testsSo far I have:Select TesterID, sum(Duration), count(Status) FROM TestRunInfo GROUPBY TesterIDBut this of course purely gives the total number of tests run by thatengineer as the count. I need to break it down. Help? Someone?Please?!?!?TIASteve
View 2 Replies View RelatedI wonder how the query will be executed when querying to views.Is database engine will be extract the tables that are used to create the view and query for the result ?
View 5 Replies View RelatedI have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.
Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?
1. XML
2. Spatial
3. Clustered
4. Non-Clustered
I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.
I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.
I started with a normal select statement selecting each column with a join to the project and then a where clause using
(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...
The rest of the columns(all with OR between them).
The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.
Obviously because I have all the columns in the select statement... So I need something like
Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))
Hi there,
I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.
SELECT * from RemoteDB.TESTDB.dbo.Objects
WHERE Last_Updated > '2008-05-21 10:51:00'
AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)
Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database. This would work just perfectly, no problems.
Now, the local database is actually on a PDA running SQL Server Compact Edition. There is currently no support for creating a linked environment. I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.
Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?
Thanks in advance
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
[code]...
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
I am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?
I've got the files configured to be 20MB before rolling over, the XE is running all the time.
So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?
I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.
I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.
Hello all:
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )
Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..
create table emp (
empid int, empname varchar(100), empage int....)
The table should look like as:
Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==
I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
Waiting for replies........
saby
I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it
I need that query for alter table
in it
reply me soon
All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.
How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)
Thanks!
-Kurt
I want to let a user a fields and select the data type and size from a short list. For example: nvarchar(?), Money, Int, or Bit. These are most likely the only choices for now. There will be a web page that lets them enter a field name and select a data type.
Can someone suggest how the sproc might look? Here is what I have so far.
CREATE PROCEDURE [dbo].AddCustomField ASALTER TABLE MyDataSQL.dbo.tblCustomFields ADD @FieldName @FieldDataType NULLGO
Thank you,
I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.
Table Fields
Tbl_Date_Dimension [Date_Dimension_Year], [Date_Dimension_Period], [Date_Dimension_Fiscal_Week]
Tbl_Report_Level Report_Level_Id
Tbl_Customer Customer_Code
[Sales Fact] [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id
The new table is called Tbl_Sales_Growth
Here's the information on the new table:
Code Block
[Date_Dimension_Year] [int] NOT NULL,
[Date_Dimension_Period] [int] NOT NULL,
[Date_Dimension_Fiscal_Week] [int] NULL,
[Report_Level_Id] [int] NOT NULL,
[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,
[Quantity] [decimal](18, 0) NOT NULL,
[Gross turnover] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
I have some question and would like ask experts:
The largest table in our database eats up above 4G . we do "sp_spaceused" for this table.The length of all columns of this table ( just int, char, varchar, money ,numeric fields types) is about 200 bytes, and the table has around 1,300,000 rows, but the reserved spaced for this table is 4,800,000kb and the data space is around 4,600,00kb.
How can average each row take 3.7kb ( the total size of all columns just 200 bytes)? Any other things I need check?
Any one can give any suggestion what cause this problem? or it is normal?
Thank you very much.
Judy