With classic asp I would do something like this:
set conn = server.createobject("ADODB.Connection")
conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=databasename "
set rs = conn.execute("select count(*) from myTable")
response.write rs(0)
How can I do this with asp.net in the fewest number of lines?
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
need help
how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute)
i need whan i insert to the another table add 2 more fields (unique number , date_time )
this is the table 1 i select from
ID fname new_date val_holiday
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1
222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3
333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
this is the table 2 i insert into
----------------------------------
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 1 666 15/04/2008 17:03
111 aaaa 16/03/2008 1 666 15/04/2008 17:03
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 666 15/04/2008 17:03
111 aaaa 20/03/2008 1 666 15/04/2008 17:03
111 aaaa 21/03/2008 1 666 15/04/2008 17:03
222 bbb 02/05/2008 3 666 15/04/2008 17:03
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3 666 15/04/2008 17:03
222 bbb 05/05/2008 3 666 15/04/2008 17:03
222 bbb 06/05/2008 3 666 15/04/2008 17:03
222 bbb 07/05/2008 3 666 15/04/2008 17:03
222 bbb 08/05/2008 3 666 15/04/2008 17:03
222 bbb 09/05/2008 3 666 15/04/2008 17:03
333 ccc 03/04/2008 4 666 15/04/2008 17:03
333 ccc 04/04/2008 4 666 15/04/2008 17:03
for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)
next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 1 667 15/04/2008 17:15
111 aaaa 16/03/2008 1 667 15/04/2008 17:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 667 15/04/2008 17:15
.........................
.....................................................................667 15/04/2008 17:15
next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 1 668 15/04/2008 08:15
111 aaaa 16/03/2008 1 668 15/04/2008 08:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 668 15/04/2008 08:15
.........................
.....................................................................668 15/04/2008 08:15
TNX
HI!
1.select count(*) from sales
2.select rows from sysindexes where id=object_id('sales') and indid<2
both queries return number of rows.
can anyone tell me which one is better?
Im am trying to return the number of rows in a table and i only can get a response of true thanks for any help
View 3 Replies View RelatedI try to find the number of rows in a table with this commands:
CountRec = New SqlParameterCountRec.ParameterName = "@countrec"CountRec.SqlDbType = SqlDbType.IntCountRec.Value = 0MyCommand = New Data.SqlClient.SqlCommand()MyCommand.CommandText = "select count(*) as @countrec from Customer;"MyCommand.CommandType = Data.CommandType.TextMyCommand.Connection = MyConnectionMyCommand.Parameters.Add(CountRec)MyCommand.Connection.Open()MyReader = MyCommand.ExecuteReaderiRecordCount = CountRec.Value
This is the result:
Incorrect syntax near '@countrec'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@countrec'.Source Error:
Line 39:
Line 40: MyCommand.Connection.Open()
Line 41: MyReader = MyCommand.ExecuteReader
Line 42: iRecordCount = CountRec.Value
Line 43:
Source File: E:DevelopWebASPwebAccessTimberSalesUserEntry.aspx.vb Line: 41
What to do? I need a complete example to see how it works.
Thanks...
Hi,
What catalog table has the number of rows of a table ?
Is there a limit of how many rows a table can have in SQL compact Edition? I didn't find anything in the documentation, but I get regularly a funny error message "Expression evaluation caused an overflow. [ Name of function (if known) = ]" when I try to create record number 32768 (is equal to 2 to the power of 15).
Where is this limit documented ?
Code Snippet
const string connectionString = "Data Source='" + dbFileName + "'; Max Database Size = 4091; temp file max size = 4091";
using (SqlCeEngine testEngine = new SqlCeEngine(connectionString)) {
testEngine.CreateDatabase();
}
using (SqlCeCommand addMValuesTableComand = testDbConnection.CreateCommand()) {
addMValuesTableComand.CommandText = "CREATE TABLE MValues (MSerieId int, TimeStamp smallint, Value real, PRIMARY KEY(MSerieId, TimeStamp))";
addMValuesTableComand.ExecuteNonQuery();
}
//fill table
StatusLabel.Text = "fill MValues Table";
using (SqlCeCommand fillTableComand = testDbConnection.CreateCommand()) {
int i = 0;
try {
int iterationCount = (int)RecordsCountNumericUpDown.Value;
ProgressBar.Value = 0;
ProgressBar.Maximum = iterationCount;
for (i = 0;i < iterationCount;i++) {
fillTableComand.CommandText = "INSERT MValues VALUES (1, " + i.ToString() + ", " + (i/100.0).ToString() + ")";
fillTableComand.ExecuteNonQuery();
ProgressBar.Value = i+1;
}
} catch (Exception ex) {
ErrorTextBox.Text = "Error occured" + Environment.NewLine +
"Iterations: " + i.ToString() + Environment.NewLine +
"Error Message: " + ex.ToString();
}
}
Hi
I have a database with 150 tables. I want to make a query ranking those tables by table size and/or number of rows.
Is this possible?
Many thanks
ds9
Is it possible to show the number of rows and the range for each partition in a table ?
This shows me the range but not the row count per partition
SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv
ON sprv.function_id=spf.function_id
WHERE (spf.name=N'myDateRangePF')
ORDER BY [ID] ASC
hi friends,
i got a error while retrieving more than 100000 rows (records) from a table .. can any one tell me what is the maximum number of rows retrieve from a database to reports... and how can i overcome this issue...
Hey Guys
i need to add a datetime column to an exisitng table that has like 1.2 million records and its being accessed frequently
but i cant afford to stop the db at all
whenever i do : alter table mytable add Updated_date datetime
it just takes too long and i have to stop executing the query after a couple of mins
I am running sql express 2005 sp2. db size is over 3 gb but still under the 4 gb limit
can u plz advice on how to add this column. its urgent!!
thanks in advance
What is the easiest way to insert a new row in a existing sql table through web developer.net (visual basic)?
E.g. a database called Names and the columns Firstname and Surname and you want to insert "anna","johnsson"?
thank you very much.
Hi, I'm new to SQL Server, and would appreciate some advice on the quickest way to import data from a CSV file.
I've created a database using Visual Web Developer Express, and added a couple of tables. The Help file in SQL Server Express (which is installed on the same PC) indicates that I should use BULK INSERT to populate the table. Only snag is, I could find anywhere to enter the commands! Eventually, I found out about the SQLCMD command which I entered in a Windows Command Window. I successfully connected to the default (SQLEXPRESS) server instance this way, but when I typed USE <my database name> I got an error back saying it couldn't find the database. I know that Visual Web Developer Express by default creates user-specific instances of the database, but I've turned that off (I think!) via the connection string. So, please could someone tell me how I can connect to my database via the SQLCMD command, or alternativley please let me know how else I can bulk inmport data from a CSV file. Many thanks in advance.
im pretty new to SQL and i'm just reading up on full-text searches... i need to do a a full-text search on one table i have in the database.. however I'm reading about full-text indexing/searching and a lot of pages are saying that it uses a lot of resources when searching.. i was wondering how bad is it really? we have about ~100 users who would access the database, probably the peak would be 75 at a time.. would people using a full-text searching slow it down a lot? the servr is a dell poweredge 1750 server, dual 2.8ghz xeon, 1 gig ram.. and also, about the incermental population, if i read right, it populates the catalog each time a item in the table is deleted/inserted/modified.. so would that use a lot of resources as well?
i'm just trying to see if it's worth it to enable full-text indexing for searches on the database if it doesn't slow down the server too much... or are there any better/easier ways to perform searches?
thanks
hi,
i need SP that receive 2 integers ,@NUM_ROWS and @PAGE_NUMBER,
and return the rows in that page.
for example:
SP(4,2) will return 4 rows in page number 2 .
So if i have table with 9 rows i will get rows 5-8,
the first page is rows 1-4 the second page is 5-8 and the 3 page is row 9.
i have to assume that rows can be deleted form that table.
thanks
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
how can I update Table 1 to get desired results?
I have a CTE query against a table with 32K rows that runs fine in 2008R2. I am running it in 2014 Std Ed. against the same data and it runs very slowly. Looking at the execution plan I think I see what's contributing to the slowness.
Note that the "actual number of rows" is some 351M...how is this possible?
the query:
declare @amts table (claim int,allowed decimal(12,2),copay decimal(12,2),deductible decimal(12,2),coins decimal(12,2));
;with unpaid (claimID) as (select claimID from claim where amt+copay + disct+mm + ded=0)
insert @amts
select lineID, sum(rc), sum(copay), sum(deduct),
case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end
from claimln
where status is null
and lineID not in (select claimID from unpaid)
group by lineID
it's like there's some massively recursive process going on?
which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query
View 10 Replies View RelatedIs there a way to control how many Detail Rows are displayed on one page in Table and Matrix controls?
View 1 Replies View RelatedHi,
Can anybody tell how to ristrict the number of rows per page in SSRS?
I have a table in my report.I know we have to use" =Int(RowNumber(Nothing)/25)" in group expression.But I already have 3 group expression in my table.Where to write??
Thanks.
I am trying to sort out the member "Adams" from the committee members, and sum up the total amounts that were donated while he was the committee member.
The Table:
CREATE TABLE contribution_list
(contrib_date DATE NOT NULL,
donor_name VARCHAR(30) NOT NULL,
amount NUMBER(8,2),
program VARCHAR2(30),
committee_member VARCHAR2(20) NOT NULL,
PRIMARY KEY (contrib_date,donor_name,committee_member));
The Code that is giving me errors:(not a group by expression):
CREATE or REPLACE VIEW adams_conrtibution_total as
select program, committee_member, sum(amount)
from contribution_list
where committee_member = 'Adams'
group by program;
How can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now
View 4 Replies View RelatedHi
I have a dataset with 2 columns, a rownumber and a servername - eg
rownumber servername
1 server1
2 server2
....
15 server15
I want to display the servernames in a report so that you get 3 columns - eg
server1 | server2 | server3
server4 | server5 | server6
...
server13 | server14 | server15
I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap
I have also tried using a matrix control but cant find a way to do this.
Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005
Thanks
Anthony
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
How can I update Table 1 to see desired results?
I need to have a script where it ask the user for a value, the script will search for all records that match the value. Then it will display the numbers of records found and ask the user to enter a different value. The rest of the script will use this new value and increment by 1 n times as the number of records found. I started the script where it will ask for "HANDLE" and display the number of records found with that "HANDLE"
declare @HANDLE as varchar(30)
declare @COUNT as varchar(10)
declare @STARTINV as varchar(20)
set @HANDLE = ?C --This is the parameter to search for records with this value
set @STARTINV = ?C --User will input the starting invoice number
SELECT COUNT as OrderCount FROM SHIPHIST
where HANDLE = @HANDLE
I just can't figure out how to proceed to use the entered invoice # and increment by 1 until it reach the number of records found.
This will be the end results:
Count=5 --results from query
STARTINV=00010 --Value entered by user
Handle,Inv_Num
AAABBB,00010
AAABBB,00011
AAABBB,00012
AAABBB,00013
AAABBB,00014
I have a table that I'm loading as part of a control flow that in turn is copied to a target table by using a data flow task. I am doing this because a different set of fields may be used from the source entry to create the target entry based on a field in the source table. That same field may indicate that multiple entries need to be created in the target table from one source table entry for which I use a multi-cast transformation.
The problem I'm having is that no matter how many entries there are in the source table, the OLE DB Source during execution only shows 7,532 entries being taken from the source table. If there are less than 7,532 entries in the source table, everything processes fine. More than 7,532 and the data flow task only takes 7,532 and then seems to hang. It also seems as though only one path of the multi-cast transformation is taken when the conditional split directs a source entry down that path.
Seems like a strange problem I know, but any insight anyone could provide is appreciated. Thanks.
Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?
View 13 Replies View RelatedI have wrote a query that returns product details for all orders that are at stage 6. Is there a way to number the product lines in each order no?!
Eg.
Order 1
Product 1 - 1
Product 2 - 2
Product 3 - 3
--counter restarts
Order 2
Product 1 - 1
Product 2 -2
thanks?
Hi,
I have the following issue:
Here is my query:
The result is below:
(SELECT 'Succesful' AS metric_value,
SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('S Infr', 'S Entitl', 'S Provi') THEN 1 ELSE 0 end) AS S,
SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('Ading', 'Bending Floor', 'Net Ops' ) THEN 1 ELSE 0 end) AS N,
SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('Management NET', 'erprise') THEN 1 ELSE 0 end) AS ENTERP,
SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE '%Sector%' THEN 1 ELSE 0 end) AS TS,
SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE '%NMS%' THEN 1 ELSE 0 end) AS MS,
SUM(CASE WHEN Product_Cat_Tier_3__2_ = 'Fading' ) AR TS,
month(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')) as month
FROM Change
WHERE YEAR(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE()) AND
Product_Cat_Tier_1_2_ = 'Network' AND
Product_Cat_Tier_2__2_ = 'RFC' AND (Change_Request_Status = 11 and Status_Reason = 6000) AND
Product_Cat_Tier_3__2_ NOT IN ('Installation', 'Voice AND Video')
GROUP BY YEAR(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')), month(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM'))
)
=====================
Result
================
metric_value S N ENTERP TS MS AR month
------------ -- -- ------ -- -- -- ----
Succesful 0 1 0 0 0 0 5
Succesful 0 1 1 0 0 0 6
And I want the following output, because now is June so month is equal to 6.
metric_value S N ENTERP TS MS AR month
------------ -- -- ------ -- -- -- ----
Succesful N N N N N N 1
Succesful N N N N N N 2
Succesful N N N N N N 3
Succesful N N N N N N 4
Succesful 0 1 1 0 0 0 5
Succesful 0 1 1 0 0 0 6
WHERE , N = NULL
THANK YOU
Hello!
I try to figure out how to change the number of rows at each page, but I'm stuck.
I have no page break , but I get a number of pages when I view my report. Is there any way to set the number of rows shown at each page?
//Cissi
hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help
SELECT SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM TABLE1WHERE (ID = nn) UNION all
SELECT SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM table2WHERE TABLE2 = nn)
I want to select data from a table with 5.000.000 rows. It's very slowly. Do you now, how I can select only a XY number of rows? I can't use TOP in select query. User see only 20-30 rows on his page, but he can use page_up, page_down. Is possible to something as lazy load?
View 2 Replies View Related