Hi, I've read that a SQL Server Express database is limited to GB. If I have a database with about 120 tables which store only simple datatypes (int, money, datetimes, varchar, etc.), no blobs or large files, how can I estimate the amount of data or number of rows it can hold before it runs out?
View 11 RepliesHi, I was trying to run an insert statement which has subquery, But it is returning the error like this..
Subqueries are not allowed in this context. Only scalar expressions are allowed.
--------------
Is there a way to reparse the insert statement without have to assign the subquery to a temp value and insert it?
thanks..
Here is my SQL..
insert into admincriteria values (nextID,(select id from nodetable where description like 'Example - Quality Analytics'),8071,2,'Failures by Customer',2,0,0)
how long does it take to convert a DTS package to ssis package?
View 9 Replies View RelatedI have a ordersystem with repeating orders. Each order has a orderline
with a frequency and a intervaltype (day, week, month and so on) for
indicating how often a task should be done.
When an task/job is done, an equivalent receipt with receiptlines is
generated.
When a new job/task should be suggested, it has to calculate the
shortest next intervallength for each repeating orderline. This
intervallength must then be added to the date of the last receiptline.
I want to to this all in a SPROC. My result should be the one next
orderline that should be carried out with a date for when this is.
RESULT
OrderID TaskID TaskDesct EstDate
5000 GETGROCERIES Pick up groceries for miss Lama 19.12.2003
************************************************************************
Is this possible to do all in a SPROC and not having to do any in .NET
(which this is developed in), and can you help me with some ideas?
************************************************************************
To se my starting point, se the diagram at:
http://www.promotion.no/div/diagram.gif
My mind is stuck and this is so far I've reached:
ALTER PROCEDURE dbo.GetNextOrders
AS
CREATE TABLE #Interval
(
ivID int IDENTITY(1,1),
ivTaskID char(20),
ivTaskDescr nvarchar(200),
ivHour int
)
INSERT INTO #TemporaryTable
SELECT RepID, RepIntName
FROM RepeatInterval
SELECT InvoiceDetails.iID, InvoiceDetails.ReceiptID,
InvoiceDetails.OrderID, InvoiceDetails.TaskID,
InvoiceDetails.TaskDescr, InvoiceDetails.DateRegistered
FROM InvoiceDetails LEFT OUTER JOIN
Orders ON InvoiceDetails.OrderID = Orders.OrderID
WHERE (Orders.DateStart < GETDATE()) AND
(Orders.DateEnd > GETDATE() OR Orders.DateEnd IS NULL)
to do a database repair on SQL server v7.
This is what I did
dbcc checkdb ('mydatabase', REPAIR_REBUILD) with estimateonly
It gave me error ....
estimateonly is not an option.
What did I do wrong? Thank you for your help!!!!!!!!!!
I would like to ask some help for estimating necessary hardware for a database, I can tell you about usage profile the following:
- 2 tables, approximately 100.000 records, the tables joined in 1:1 relation, one record is approximately 500 bytes long
- typical scenario:
- select what returns 1 or zero record
- if it returned 1 record an update performed on this record
- the event logged in a third table (one insert)
- this typical scenario needs to run 100-300 times in every second
Can you tell me any advice on sizing of hardware for this? We can rely on a well designed database structure (proper indexes, etc...)
Thanx in advance
Hello,how can I estimate the size (KB) of a temptable?Thank Yousilas
View 7 Replies View RelatedI was having interesting discussion on estimation of log file with a fellow collegue who happens to be quite knowledgable as well.
He told me if we identify the most frequently hit tables for a database and then (sum their sizes * 1.5) for OLAP we get rough estimate for disk space to be allocated for log file.
I have a table like below,
CREATE TABLE Student
(
Id BIGINT not null
,Name NCHAR(20) not Null
,Branch NVARCHAR (64) null
)
The table contains : 100000 rows .
Getting details of below
1)Number of rows in a data page
2)Total number of pages required for the table
3)Total Table size in KB or MB
4)Total file size in Kb or MB
I have a 2.5TB database that we need to run checkdb on. Is there a formula to figure how log it would take with no options? Doing research, it appears the we are stuck with doing the PHYSICAL_ONLY due to time constraints.
View 7 Replies View RelatedMy client's website database is hosted by a third party. I need to alter one of the column definitions for the largest table in the database. Unfortunately, the transaction log fills up if I try to alter the table. I've done all the usual stuff like truncating the log, etc., but the simple fact is that the operation requires more log space than we have available. Therefore, we need to purchase additional disk space for the database.
What I'm looking for is a way to roughly estimate how much log space will be required to alter this table so that we purchase enough but not too much additional space. The table has an identity primary key and 4 other single column indexes: one int, one datetime and two varchar(30) columns.
Any suggestions? Thanks in advance.
As in title. Is there any tool? I'm asking beceuse, I have some big bases, and processing may take a lot of time (at least few hours), and I'll be glad if it's possibility to know estimate time before query runs. I'm using MsSql 2005 Developer edition.
View 14 Replies View RelatedHow SQL calculates the estimated row count when < or > are used in the WHERE clause of a statement. For example:-
WHERE Created_Datetime_utc > CONVERT(DATETIME,'2014-10-14 10:00:00',102)
I know how the estimated number of rows are calculated when an = is used but I've been googling and cannot find anything about < and >.
I have a table like below,
CREATE TABLE Student
(
Id BIGINT not null
,Name NCHAR(20) not Null
,Branch NVARCHAR (64) null
)
The table contains : 100000 rows .
1)Number of rows in a data page
2)Total number of pages required for the table
3)Total Table size in KB or MB
4)Total file size in Kb or MB
Hi,
I need to estimate the effort required in writing some SSIS packages.
Could anyone provide some pointers to the various factors (E.g. number of tables, source, etc.) which influence the effort estimate for SSIS packages?
Thanks in advance.
Regards,
B@ns
i need to full-text index a table so that i can easily search the text fields of that table.. the table has about 21,000 rows, and i was wondering how long it might take to full-text index it?
thanks
How to estimate the size of datafile while creating the database?
View 2 Replies View RelatedI've been asked to put together an estimation for the performance impact that replication would have on our database server during a particular operation. I know that this depends on a lot of different factors, including:
* Number of articles being replicated
* Types of articles being replicated
* Number of DML transactions that would result in delivery of replicated data
Any way to turn this into a meaningful metric?
What is the best way to forecastestimate space for non-clustered index on a table?
Example :
Table name : Test123
Row : 170000000
Reserved : 18000000 KB
Data : 70000000 KB
Index: 40000000 KB
Note: Test123 already has clustered index and 2 non clustered indexes.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
I have a sql select statement, then I have a datareader and I'm trying to loop through that reader and insert a record into another table each time until there are no more records in the loop. I'm not sure how to do this though without using 2 datareaders. Please help. Thanks
1 ' Retrieve Data from database based on selections chosen in ListBox
2 Dim cmdCommittee As New SqlCommand("Select * from committees_tbl where committee_id in" & _
3 "(" & strCommitteesRemoveLast & ") order by committee_name", conn_Insert)
4
5 ' setup a datareader
6 Dim drCommittee As SqlDataReader = cmdCommittee.ExecuteReader()
7
8
9 ' Loop through datareader and insert rows
10 ' into the xref_person_committees_tbl
11 While drCommittee.Read()
12 Dim strCommitteeName As String = drCommittee("committee_name") 'retrieve committee_name from datareader
13
14 ' Create a sql string
15 Dim strAddCommittee As String = String.Empty
16 strAddCommittee = "Insert into xref_person_committees_tbl (committee_name) values ('" & strCommitteeName & "')"
17 'Response.Write(strAddCommittee & "<br>")
18
19 ' Create a sql command to process the insert
20 Dim sqlAddCommittee As New SqlCommand(strAddCommittee, conn_Insert)
21 Dim drNewCommittee As SqlDataReader = sqlAddCommittee.ExecuteReader()
22
23
24
25
26 End While
27 ' -----------------------------------------------------------------------------------
28
29 drCommittee.Close()
I want to pass the string "select @@servername" in the print statement, it throws error as:=
Msg 1046, Level 15, State 1, Line 3
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 1046, Level 15, State 1, Line 28
Subqueries are not allowed in this context. Only scalar expressions are allowed.
declare @account_name nvarchar(400) = 'Mail Account';
IF exists (select * from msdb.dbo.sysmail_account where name = @account_name)
PRint 'Database Mail Account ' + quotename (@account_name) + 'is already setup in the Server:= ' + (select @@servername);
I mean why such restriction? In such queries results will be only 1 so it does not violate the SET operation. correct??
I want to put some trace in the a UDF, so I put print in the function. IT gave error. Can anyone please explain why this happen. But this work with SPs.
Cheers
Shimit
Hi
Keep getting this message in the Application Event Viewer.
Source: SQLAgent$SHAREPOINT
Category: Alert Engine
Event ID: 324
Description: SQLAgent is not allowed to run.
Can't find any help online. Anyone out there got any ideas?
TIA
Hi everyone,
I was browsing and came across this code with this result set
CREATE TABLE dbo.SalesByQuarter
(
Y INT,
Q INT,
sales INT,
PRIMARY KEY (Y,Q)
)
GO
INSERT dbo.SalesByQuarter(Y,Q,Sales)
SELECT 2003, 2, 479000
UNION SELECT 2003, 3, 321000
UNION SELECT 2003, 4, 324000
UNION SELECT 2004, 1, 612000
UNION SELECT 2004, 2, 524000
UNION SELECT 2004, 3, 342000
UNION SELECT 2004, 4, 357000
UNION SELECT 2005, 1, 734000
GO
SELECT Y,
[1] AS Q1,
[2] AS Q2,
[3] AS Q3,
[4] AS Q4
FROM
(SELECT Y, Q, Sales
FROM SalesByQuarter) s
PIVOT
(
SUM(Sales )
FOR Q IN ([1],[2],[3],[4])
) p
ORDER BY
GO
DROP TABLE dbo.SalesByQuarter
GO
Y Q1 Q2 Q3 Q4
2003 NULL 479000 321000 324000
2004 612000 524000 342000 357000
2005 734000 NULL NULL NULL
I tried to modify it to remove the nulls by changing this line of code
SUM( ISNULL(Sales,0))
I got this error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ISNULL'.
so i tried it like this
and got this error
Msg 195, Level 15, State 1, Line 12
'ISNULL' is not a recognized aggregate function.
My question is why can't i use isnull to change NULL TO 0
Thanx
Slimshim
Could someone please tell WHY it is not allowed to use the following construction in a stored procedure!?
AS IF @taxparent = 0 THEN SET @taxparent = NULL IF @museum = 0 THEN SET @museum = NULL IF @collection = 0 THEN SET @collection = NULL SELECT ID, SpecimenNr, ScientificName, Locality, Taxon FROM QueryView
I get the following error messsages:
Msg 156, Level 15, State 1, Procedure DynamicQuery, Line 9Incorrect syntax near the keyword 'THEN'.Msg 156, Level 15, State 1, Procedure DynamicQuery, Line 10Incorrect syntax near the keyword 'THEN'.Msg 156, Level 15, State 1, Procedure DynamicQuery, Line 11Incorrect syntax near the keyword 'THEN'
Any help is greatly appreciated!
I am using the SQLEXPRESS database, and connect from to locations:
Windows Service
ASP.NET web application.
whenever I start the windows service I get the error: Cannot open user default database. Login failed.
so I am going to visual studio and close the connection. then I close the web browser, and the windows service succeed connect to database.
but then, after windows service is connected, I open the web browser application and fails to login because the login system using the same database of windows service, and since the connection is being used by windows service, login is impossible.
Do you know how I can allow 2 connections at the same time for the same database?
what are the risks to do that? and how do I handle this risks.
thanks alot
Can anyone tell me what is the maximum number of rows that I can code in a T-SQL step if I want to
include this in a job? I do not want to use stored procedure for certain task, so I want to put the same code in a T-SQL step.
Thanks!
Sheila.
Hello,
I need to know what kind of characters are NOT allowed in SQL literals and the way(s) to go around it.
In example:
Code:
' must be replaced by ''
I would greatly appreciate all the help you guys can provide me with.
Thank you for taking the time.
Hi all,
I need to rewrite the following query without a nested query (stupid mysql 4.0.25. Can anyone lend a hand?
SELECT name from `list` where name not in (
SELECT DISTINCT b.name from ` armor ` a,` list` b where b.name = a.name
);
I entered the max 8000 varchar in a column and it will not let me enter more than about 1000. What is the deal?
thanks
Matt
Um, still trying to transpose MySQL into T-SQL.
Inserting info into a table, where one of the columns meets a certain criteria.
insert into employee (/*emp_id,*/ fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (/*null,*/ 'Michael', 'Smith', '2001-06-22',
(select dept_id from department where name = 'Administration'),
'President',
(select branch_id from branch where name = 'Headquarters'));
But I'm getting this error:
Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Any help would be greatly appreciated.
For example I have
CASE (a.t_id)
WHEN (a.t_id in (22,23,27,30,38))
THEN t.desc
ELSE 'N/A'
END 'Column name..',
and that is giving me "incorrect syntax near 'in'" ??